Re: Deferred foreign key and two updates block ALTER TABLE

>>>>> "André" == André Hänsel <andre@webkr.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)