Обсуждение: LargeObject API and OIDs
I'm using PostgreSQL as the backend for a versioning content store, and after reading the JDBC docs, I'm planning on using the LargeObject API to store the actual data. I noticed that large objects are referenced using unsigned 4-byte integers, which for practical purposes should be fine, assuming there's no chance for data corruption if the number is exceeded. However, since a versioning system will have a higher number of entries compared to a normal storage system, I'm curious if there's any chance for data corruption in the case that the DB runs out of OIDs. Ideally, the database would raise an exception, and leave the existing data untouched. From what I've read in the documentation, OIDs aren't guaranteed to be unique, and may cycle. In this case, would the first large object after the limit overwrite the first object? Also, would the number of large objects available be limited by other database objects that use OIDs? The majority of the content stored in the system will be in small files, with the exception of some images, PDFs and so forth. So, if there is a chance for data corruption, I may implement a scheme were only files above some threshold are stored using large objects, and all others are stored in a bytea column. The JDBC docs mention the performance problems with large bytea values, but are there any implementation factors that might affect the threshold I choose? I apologize that this isn't specifically JDBC related, but since I'm using JDBC for all of this, I thought I'd ask here first. best, christian.
Christian Niles <christian@unit12.net> writes: > However, since a versioning system will have a higher number of entries > compared to a normal storage system, I'm curious if there's any chance > for data corruption in the case that the DB runs out of OIDs. Ideally, > the database would raise an exception, and leave the existing data > untouched. From what I've read in the documentation, OIDs aren't > guaranteed to be unique, and may cycle. In this case, would the first > large object after the limit overwrite the first object? No; instead you'd get a failure during lo_create: /* Check for duplicate (shouldn't happen) */ if (LargeObjectExists(file_oid)) elog(ERROR, "large object %u already exists", file_oid); You could deal with this by retrying lo_create until it succeeds. However, if you are expecting more than a few tens of millions of objects, you probably don't want to go this route because the probability of collision will be too high; you could spend a long time iterating to find a free OID. Something involving a bigint identifier would work better. > Also, would > the number of large objects available be limited by other database > objects that use OIDs? No. Although we use just a single OID sequence generator, each different kind of system object has a separate unique index (or other enforcement mechanism), so it doesn't really matter if, say, an OID in use for a large object is also in use for a table. regards, tom lane
On Oct 24, 2004, at 4:26 PM, Tom Lane wrote: > > No; instead you'd get a failure during lo_create: > > /* Check for duplicate (shouldn't happen) */ > if (LargeObjectExists(file_oid)) > elog(ERROR, "large object %u already exists", file_oid); > > You could deal with this by retrying lo_create until it succeeds. > However, if you are expecting more than a few tens of millions of > objects, you probably don't want to go this route because the > probability of collision will be too high; you could spend a long time > iterating to find a free OID. Something involving a bigint identifier > would work better. If i understand correctly, you're implying here using a table that mimics the pg_largeobject table, but uses int/bigint identifiers instead of OID. Are there any optimizations that make the LargeObject API significantly faster? From the documentation, it seems that as long as I use prepared/callable statements, this shouldn't incur much additional overhead. thanks again, christian.
Christian Niles <christian@unit12.net> writes: > On Oct 24, 2004, at 4:26 PM, Tom Lane wrote: >> ... Something involving a bigint identifier >> would work better. > If i understand correctly, you're implying here using a table that > mimics the pg_largeobject table, but uses int/bigint identifiers > instead of OID. I was thinking of just a bigint primary key and a bytea data field. You would of course have to fool with the bytea value instead of using the LargeObject API; dunno how inconvenient this is for you. regards, tom lane
bytea values are a little cumbersome because of their memory requirements, and because the ability to seek will likely prove valuable at times. The system will be installed in places with small IT departments and novice users, so I'm trying to keep the database administration simple, and the effects of user ignorance or mistakes small. Implementing a logical block manager of sorts is starting to look like the best compromise. Thanks for all your input, it's helped me understand the limits of each approach quite well. best, christian. On Oct 25, 2004, at 10:28 AM, Tom Lane wrote: > Christian Niles <christian@unit12.net> writes: >> On Oct 24, 2004, at 4:26 PM, Tom Lane wrote: >>> ... Something involving a bigint identifier >>> would work better. > >> If i understand correctly, you're implying here using a table that >> mimics the pg_largeobject table, but uses int/bigint identifiers >> instead of OID. > > I was thinking of just a bigint primary key and a bytea data field. > You would of course have to fool with the bytea value instead of using > the LargeObject API; dunno how inconvenient this is for you. > > regards, tom lane >