Re: BUG #15631: Generated as identity field in a temporary table with on commit drop corrupts system catalogs

On 2019-03-12 04:46, Michael Paquier wrote:
> I can reproduce the issue on a v10 server, for example:
> =# create temporary table foo ( bar int generated by default as identity ) on
> commit drop;
> CREATE TABLE
> =# \q
> $ psql
> =# create temporary table a (b varchar);
> ERROR: XX000: could not open relation with OID 16389

I've been trying to understand why the equivalent case with serial does
not fail even though the code is mostly the same, that is,

create temporary table foo ( bar serial ) on commit drop;

It turns out that there is some funny business going on that has only
been invisible so far.

If you run the above command with serial, the sequence is not temporary
and is not dropped. After the table is dropped (on commit), you still
have stale dependency entries lying around (start from empty instance to
get matching OIDs):

╔═════════╤═══════╤══════════╤════════════╤══════════╤═════════════╤═════════╗
║ classid │ objid │ objsubid │ refclassid │ refobjid │ refobjsubid │
deptype ║
╠═════════╪═══════╪══════════╪════════════╪══════════╪═════════════╪═════════╣
║ 1259 │ 16386 │ 0 │ 2615 │ 16384 │ 0 │ n
║
║ 1259 │ 16386 │ 0 │ 1259 │ 16388 │ 1 │ a
║
╚═════════╧═══════╧══════════╧════════════╧══════════╧═════════════╧═════════╝

(These are sequence -> namespace and sequence -> column.)

You can see that the catalog is faulty at this point by running

select pg_describe_object(refclassid, refobjid, refobjsubid) from pg_depend;

This is all eventually cleaned up because the sequence is in the pg_temp
schema and so will be cleaned up with the schema.

If you run the command with the identity syntax, you get almost the same
stale dependency entries:

╔═════════╤═══════╤══════════╤════════════╤══════════╤═════════════╤═════════╗
║ classid │ objid │ objsubid │ refclassid │ refobjid │ refobjsubid │
deptype ║
╠═════════╪═══════╪══════════╪════════════╪══════════╪═════════════╪═════════╣
║ 1259 │ 16386 │ 0 │ 2615 │ 16384 │ 0 │ n
║
║ 1259 │ 16386 │ 0 │ 1259 │ 16388 │ 1 │ i
║
╚═════════╧═══════╧══════════╧════════════╧══════════╧═════════════╧═════════╝

It's only because of the different deptype that something chokes when it
tries to clean up the temp schema.

Adding a CommandCounterIncrement() somewhere does fix all this. I was
thinking another option for placing this call would be in
ProcessUtilitySlow():

/* Need CCI between commands */
- if (lnext(l) != NULL)
CommandCounterIncrement();

I think we should also make the implicitly created sequence temporary.
Even though the permanent sequence is cleaned up properly, we should
avoid having those sequences write to the WAL.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services