Re: Deferred foreign key and two updates block ALTER TABLE

>>>>> "André" == André Hänsel <andre(at)webkr(dot)de> writes:

André> Hi list,
André> this looks like a bug to me, forgive me if it's expected
André> behavior:

It is expected behavior.

André> Actual result: ERROR: cannot ALTER TABLE "test" because it has
André> pending trigger events SQL state: 55006

What this means is that there are pending trigger events on the table,
and calling those triggers after altering the column type would not be
possible, for example because rewriting the table would change the TIDs
of rows making it impossible for the queued event to locate them.

André> - There is no error if there is just one UPDATE instead of two
André> (this makes me think this might be a bug)

That's actually an optimization; there are cases where a foreign-key
check can be optimized away, which is happening on the first update, but
one of the current conditions for that optimization is that the row or
row version being updated is not new in the current transaction, so a
second or subsequent update, or an update after an insert, will require
the check to actually be queued.

André> - There is no error if both UPDATEs lead to the same result
André> (when the second UPDATE is a no-op, like setting some_column = 1
André> again)

This one I can't currently explain without digging into the code.

André> - There is no error if the foreign key is non-deferrable or
André> currently immediate

This is because these cases don't leave trigger events on the queue
between statements - they fire at the end of the statement.

BTW, this kind of restriction is anticipated in the SQL spec (in SQL
2016, see 4.41.1 "General description of SQL-transactions"):

It is implementation-defined whether or not the execution of an
SQL-data statement is permitted to occur within the same
SQL-transaction as the execution of an SQL-schema statement. If it
does occur, then the effect on any open cursor or deferred constraint
is implementation-defined. There may be additional implementation-
defined restrictions, requirements, and conditions. If any such
restrictions, requirements, or conditions are violated, then an
implementation-defined exception condition or a completion condition
warning with an implementation-defined subclass code is raised.

PG tries hard to allow mixed DDL and DML/queries in transactions, but
where one would invalidate information needed by the other, it has to
throw an error.

--
Andrew (irc:RhodiumToad)