Re: LOCK for non-tables

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. The following confirms this

T1> BEGIN TRANSACTION ISOLATION SERIALIZABLE;
T1> SELECT TRUE; -- T1's snapshot is now set
T2> CREATE TABLE test(id int);
T1> SELECT * FROM test; -- Succeeds
T1> SELECT * FROM pg_class WHERE relname = 'test'; -- Returns 0 rows

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.

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

best regards.
Florian Pflug