> 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
> -- we can assume the lo_open will return FD 0, since it is the
> -- only open LOFD in this transaction
> select lowrite(0, 'suitably quoted bytea data');
> select lowrite(0, 'more quoted bytea data');
> -- repeat lowrite until all written in convenient chunks
> commit;
>
> This is incredibly attractive, because it immediately fixes the
> long-standing gotcha that pg_dumpall doesn't dump large objects.
I'm strongly in favor of this. Please don't forget to put in COMMENT ON
LARGE OBJECT 131072 IS 'blah'; though, to make LO support complete.
(Currently LOB comments are not dumped and restored ... ever)
> 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.
pg_dump often has little buggy things (ie. non-existent userid's of
dropped users in ACLs (waiting for alvaro's shared dep patch)) that
prevent you from using begin/commit around the whole thing anyway.
Also, people who don't use LOBs (most people) can still put begin/commit.
So, I'm still strongly in favour of this.
Chris