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

On Fri, Jan 7, 2011 at 5:17 PM, Florian Pflug <fgp@phlo.org> wrote:
> On Jan7, 2011, at 22:21 , Robert Haas wrote:
>> So suppose you pg_dump a view and and a function that uses the view.
>> In the middle of the dump, someone alters the view and the function in
>> a single transaction and commits it.  You might dump the function
>> before the transaction commits and the view afterward, or visca versa,
>> and the result will be an inconsistent view of the database schema.
>> Allowing pg_dump to take AccessShareLocks on the objects in question
>> would prevent this sort of anomaly, which certainly seems to have some
>> value.
>
> That'd actually work fine I believe. AFAICT, system catalogs are *not*
> accessed with SnapshotNow semantics if accessed via SQL, they're treated
> like any other table in that case.

Oh, hm.  Interesting point.

> 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.

> Another class of failure cases can be constructed from output functions
> which access the catalog. For example,
>
>> CREATE TABLE my_types (a_type regtype);
>> CREATE TYPE my_type AS (id int);
>> INSERT INTO my_types VALUES ('my_type');
>
> T1> BEGIN TRANSACTION ISOLATION SERIALIZABLE;
> T1> SELECT TRUE; -- T1's snapshot is now set
> T1> SELECT * FROM my_types;
>  a_type
> ---------
>  my_type
>
> 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.

rhaas=# create table my_types (a_type regtype);
CREATE TABLE
rhaas=# create type my_type as (id int);
CREATE TYPE
rhaas=# insert into my_types values ('my_type');
INSERT 0 1
rhaas=# select * from my_types;a_type
---------my_type
(1 row)

rhaas=# drop type my_type;
DROP TYPE
rhaas=# select * from my_types;a_type
--------16474
(1 row)

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.

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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company