I have just finished making a lo_create() function that accepts the
desired large object OID to create, as per recent discussion. This
means we can substantially reduce the complexity of pg_restore's
handling of LOs: we can remove the code that (tediously and rather
klugily) tries to find and update large object references in the
database to refer to their new OIDs. Instead, we just restore
the large objects with their original OIDs to begin with.
It occurs to me that, because this restoration process is fundamentally
noninteractive, there is no longer any reason that we cannot support
backing up large objects in plain PSQL dumps. The dump script for
each LO would look something like
begin;select lo_create(original-OID-of-large-object);select lo_open(original-OID, 131072); -- 131072 = INV_WRITE--
wecan assume the lo_open will return FD 0, since it is the-- only open LOFD in this transactionselect lowrite(0,
'suitablyquoted bytea data');select lowrite(0, 'more quoted bytea data');-- repeat lowrite until all written in
convenientchunkscommit;
This is incredibly attractive, because it immediately fixes the
long-standing gotcha that pg_dumpall doesn't dump large objects.
I propose therefore that we make pg_dump do this, and indeed make
it the default/only behavior --- the -b switch should become a
no-op.
A minor objection to the above is that you couldn't usefully wrap
begin/commit around the whole of a pg_dump script as you can now.
But I think that's a small loss in comparison to the gain. It'd
be possible to avoid that problem by reducing the above to one
SQL command:select lowrite(lo_open(lo_create(original-OID), 131072), 'data');
but I don't think that scales real well to LOs containing many megabytes
of data.
Comments?
regards, tom lane