On Tue, 2009-10-06 at 16:58 +0200, Tom Lane wrote:
Yeah, I have sometimes thought that pg_largeobject shouldn't be
considered a system catalog at all. It's more nearly like a toast
table, ie, it's storing "out of line" user data.
pg_largeobject in it's current form has serious limitations, the biggest
one is that it can't have triggers, and thus it can't be replicated by
trigger based replication like slony.
I ended up rolling my own large object table, modeling exactly the
behavior of pg_largeobject but on the client side, except I can
replicate it... and a few other simple things like easily duplicating an
entry from client side code, and easier control of the large object ID
ranges - BTW, OID is not the best data type for a client visible primary
key, then better BIGINT, oid is unsigned and in Java for example won't
cleanly map to any data type (java long is twice as big as needed and
int is signed and won't work for all OID values - we finally had to use
long, but then BIGINT is a better match). Considering that the postgres
manual says: "using a user-created table's OID column as a primary key
is discouraged", I don't see why use OID as the primary key for a table
which can potentially outgrow the OID range.
The backup is also not a special case now, it just dumps the table. I
don't know what were the reasons of special casing pg_largeobject, but
from a usability POV is fairly bad.