Skip site navigation (1) Skip section navigation (2)

Re: Largeobject Access Controls (r2460)

From: KaiGai Kohei <kaigai(at)ak(dot)jp(dot)nec(dot)com>
To: Takahiro Itagaki <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>
Cc: KaiGai Kohei <kaigai(at)kaigai(dot)gr(dot)jp>, Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec>, Greg Smith <greg(at)2ndquadrant(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Largeobject Access Controls (r2460)
Date: 2010-02-10 00:39:56
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
(2010/02/09 21:18), KaiGai Kohei wrote:
> (2010/02/09 20:16), Takahiro Itagaki wrote:
>> KaiGai Kohei<kaigai(at)ak(dot)jp(dot)nec(dot)com> wrote:
>>>> I don't think this is necessarily a good idea. We might decide to treat
>>>> both things separately in the future and it having them represented
>>>> separately in the dump would prove useful.
>>> I agree. From design perspective, the single section approach is more
>>> simple than dual section, but its change set is larger than the dual.
>> OK.
>> When I tested a custom dump with pg_restore, --clean& 
>> --single-transaction
>> will fail with the new dump format because it always call lo_unlink()
>> even if the large object doesn't exist. It comes from dumpBlobItem:
>> ! dumpBlobItem(Archive *AH, BlobInfo *binfo)
>> ! appendPQExpBuffer(dquery, "SELECT lo_unlink(%s);\n", binfo->;
>> The query in DropBlobIfExists() could avoid errors -- should we use it 
>> here?
>> | SELECT lo_unlink(oid) FROM pg_largeobject_metadata WHERE oid = %s;
> Yes, we can use this query to handle --clean option.
> I'll fix it soon.

The attached patch fixed up the cleanup query as follows:

+   appendPQExpBuffer(dquery,
+                     "SELECT pg_catalog.lo_unlink(oid) "
+                     "FROM pg_catalog.pg_largeobject_metadata "
+                     "WHERE oid = %s;\n", binfo->;

And, I also noticed that lo_create() was not prefixed by "pg_catalog.",
so I also add it.

Rest of parts were not changed.


>> BTW, --clean option is ambiguous if combined with --data-only. Restoring
>> large objects fails for the above reason if previous objects don't exist,
>> but table data are restored *without* truncation of existing data. Will
>> normal users expect TRUNCATE-before-load for --clean& --data-only cases?
>> Present behaviors are;
>> Table data - Appended. (--clean is ignored)
>> Large objects - End with an error if object doesn't exist.
>> IMO, ideal behaviors are:
>> Table data - Truncate existing data and load new ones.
>> Large objects - Work like as MERGE (or REPLACE, UPSERT).
>> Comments?
> In the existing "BLOBS" section, it creates and restores large objects 
> in same time. And, it also unlink existing large object (if exists)
> just before restoring them, when --clean is given.
> In my opinion, when --clean is given, it also should truncate the table
> before restoring, even if --data-only is co-given.
> Thanks,

OSS Platform Development Division, NEC
KaiGai Kohei <kaigai(at)ak(dot)jp(dot)nec(dot)com>

Attachment: pgsql-fix-pg_dump-blob-privs.7.patch
Description: application/octect-stream (26.0 KB)

In response to


pgsql-hackers by date

Next:From: Kris JurkaDate: 2010-02-10 00:47:18
Subject: Re: Avoiding bad prepared-statement plans.
Previous:From: Tom LaneDate: 2010-02-10 00:32:14
Subject: Re: Some belated patch review for "Buffers" explain analyze patch

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group