pg_restore --clean vs. large object

Hi,

Since pg_restore --clean doesn't delete existing large objects,
restoring to an existing database with "pg_restore --clean -1"
would fail if backup archive contains large objects. Some DBAs
complain to the behavior because they expect all existing data
conflicting with backup archive will be deleted automatically.

I'd like to improve the behavior if it is not intentional.
The attached is a patch to execute lo_unlink() before lo_create()
in pg_restore. To avoid transaction rollbacks, I added a test
whether the large object exists with an EXISTS query.

SELECT CASE WHEN EXISTS
(SELECT 1 FROM pg_catalog.pg_largeobject WHERE loid = %u)
THEN lo_unlink(%u) END;

Comments welcome.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

On Mon, May 18, 2009 at 3:10 AM, Itagaki
Takahiro<itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp> wrote:
>
> The attached is a patch to execute lo_unlink() before lo_create()
> in pg_restore.

the patch applies almost cleanly (there are only minor and superfluos
hunks), compiles...
it works as expected...

this patch makes me wonder why we dump or restore an object in
pg_largeobject that has been deleted from the user table that had the
oid... but that is another thing...

i think this one could be applied, just as is... there is no need for
docs, because the issue being fixed is not documented... maybe that
should be in doc of older releases?

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec> wrote:

> i think this one could be applied, just as is... there is no need for
> docs, because the issue being fixed is not documented... maybe that
> should be in doc of older releases?

Sure, it was an undocumented behavior. Should we need to add details
of this patch to documentation?

[pg_restore.sgml]
-c
--clean
Clean (drop) database objects before recreating them.
(8.5) Also drop large objects with same oids.
(older) Large objects with same oids are not dropped.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

Itagaki Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp> writes:
> Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec> wrote:
>> i think this one could be applied, just as is... there is no need for
>> docs, because the issue being fixed is not documented... maybe that
>> should be in doc of older releases?

> Sure, it was an undocumented behavior. Should we need to add details
> of this patch to documentation?

The release note entry will be sufficient I think.

regards, tom lane

Itagaki Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp> writes:
> The attached is a patch to execute lo_unlink() before lo_create()
> in pg_restore.

Applied with corrections --- you had failed to ensure that pg_dump and
pg_restore produce the same output. I also took the opportunity to
schema-qualify the calls of lo_xxx functions, just to be on the safe
side. (The code already sets search_path, but why not be sure ...)

regards, tom lane