Обсуждение: 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
>