Re: LOCK for non-tables - Mailing list pgsql-hackers

On Jan7, 2011, at 23:56 , Robert Haas wrote:
> On Fri, Jan 7, 2011 at 5:17 PM, Florian Pflug <fgp@phlo.org> wrote:
>> Thus, all objects which are dumped purely by SQL-level inspection of the
>> system catalogs are safe I think. This is true for most objects I guess,
>> with the important exception being dumping a table's contents (but not
>> dumping its structure!). The lock makes sure that the structure we see
>> when inspecting the catalogs is also what "SELECT * FROM table" will return.
>> I dunno if there are any other objects like that, though - but if there
>> are, they could probably use a lock too.
>
> Hmm.  It would seem that to be vulnerable you'd need an object where
> you need to dump both the structure and the contents, and I can't
> think of any.  Or it could apply to an object where you called some
> pg_foo-ish function that did some SnapshotNow magic behind the scenes;
> not sure if there are any of those, and this might not be the right
> fix anyway.

I forgot about sequences earlier. If we dump while someone deletes all
rows and resets the sequence the dump might contain rows and still
reset the sequence. This could cause duplicate key errors on restore.
I haven't checked if this is really possible though - I guess it would
depend on the exact order of these events...

>> Another class of failure cases can be constructed from output functions
>> which access the catalog. For example,
>>
>>
>> <snipped>
>>
>> T2> BEGIN;
>> T2> DELETE FROM my_types WHERE a_type = 'my_type';
>> T2> DROP TYPE my_type;
>> T2> COMMIT;
>> T1> SELECT * FROM my_types;
>>  a_type
>> --------
>>  291919
>
> Well, that happens even if there's no concurrency involved.

Not really, because the DROP TYPE and DELETE FROM my_types is
done within a transaction, so one might expect nobody else to
see the intermediate state. But yeah, I agree, this corner-case isn't
something we have to worry about too much.

> If the conclusion of this discussion is that pg_dump doesn't really
> need to lock anything other than tables, we should update the comments
> to say that, rather than what they say now.

+1

> It's also worth thinking about whether there's any use case for
> locking other types of objects for any reason *other than* pg_dump.  I
> can't immediately think of anything terribly compelling, but I might
> be missing something.

I wonder how such locks would work. Would such locks prevent accessing
these objects? Or just modifications? For example, if I locked a function,
could someone else execute it while I held the lock?

best regards,
Florian Pflug