Re: Large object insert/update and oid use

Louis LeBlanc <db(at)keyslapper(dot)org> writes:
> [ large objects don't work real well in That Other Database ]

Fascinating. I'd have thought they'd use a less flaky design.

> I've been looking at the Postgres docs, and I've found the lo_*()
> routines, which appear to use OIDs to write to the object. Is there
> any 'shifting' of records during insert because of space availability?

No, OIDs are stable identifiers. It sounds like their rowid equates
to what we call a TID or CTID --- that is, a tuple's current physical
location. CTID isn't fixed across updates in Postgres, but OID is.

> And, probably more important, is there a way to do a direct update of
> a large object along with the other fields in a record - without
> having to call the lo_creat() and lo_write() functions?

Well, I guess the question is how large are your BLOBs and what are you
doing with them exactly?

Postgres offers two different ways of dealing with large chunks of data.
One is just to store them as big text or bytea values (which data type
you use depends on whether the data is textual or not). This works well
up to maybe a few megabytes, but it gets unwieldy for larger values,
mainly because there's no easy way to store sections of a value. In
some cases you can use substr() to fetch sections of a large value,
but there's no equivalent means of writing just part of it. Also, if
the data isn't textual then you have to deal with messy quoting rules
when constructing SQL commands. (But as of 7.4 it's possible to
alleviate the quoting problem by using out-of-line parameters instead.)

The other way is to use lo_creat() and related functions. In this case
each large object has its own identity (an OID assigned by lo_creat) and
what you put in your table row is just a reference to the object. The
plus side is that you can read and write a large object in sections
(using lo_read/lo_write/lo_seek), the downside is that you have a more
complicated model of what the database structure is, and lots more
management headaches. For instance you need to explicitly delete a
large object when you don't need it any more --- deleting a table row
doesn't in itself make referenced large objects go away.

Personally I'd avoid the large object facility if there were any chance
of dealing with the data as ordinary wide values instead. It's just
easier.

regards, tom lane