Thread: Deferred foreign key and two updates block ALTER TABLE

Hi list,

this looks like a bug to me, forgive me if it's expected behavior:

BEGIN;

DROP TABLE IF EXISTS test;
DROP TABLE IF EXISTS test_other;

CREATE TABLE test(
    some_column integer NOT NULL,
    other_id integer NOT NULL
);
CREATE TABLE test_other(
    other_id integer NOT NULL
);
ALTER TABLE test_other ADD PRIMARY KEY (other_id);
ALTER TABLE test ADD FOREIGN KEY (other_id) REFERENCES test_other
DEFERRABLE;

INSERT INTO test_other (other_id) VALUES (1);
INSERT INTO test (some_column, other_id) VALUES (0, 1);

COMMIT;

BEGIN;

SET CONSTRAINTS ALL DEFERRED;

UPDATE test SET some_column = 1 WHERE some_column = 0;
UPDATE test SET some_column = 2 WHERE some_column = 1;
ALTER TABLE test ALTER COLUMN some_column TYPE smallint;

COMMIT;

Expected result: Both transactions can be committed without error

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

Notes:
- It doesn't matter if the UPDATEs are on the same or different colums that
are altered, there is still an error
- There is no error if there is just one UPDATE instead of two (this makes
me think this might be a bug)
- There is no error if both UPDATEs lead to the same result (when the second
UPDATE is a no-op, like setting some_column = 1 again)
- There is no error if the foreign key is non-deferrable or currently
immediate

Regards,
André

>>>>> "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)

On Mon, 21 Jan 2019 at 14:31, André Hänsel <andre@webkr.de> wrote:
> BEGIN;
>
> SET CONSTRAINTS ALL DEFERRED;
>
> UPDATE test SET some_column = 1 WHERE some_column = 0;
> UPDATE test SET some_column = 2 WHERE some_column = 1;
> ALTER TABLE test ALTER COLUMN some_column TYPE smallint;
>
> COMMIT;
>
> Expected result: Both transactions can be committed without error
>
> Actual result: ERROR:  cannot ALTER TABLE "test" because it has pending
> trigger events SQL state: 55006
>
> Notes:
> - It doesn't matter if the UPDATEs are on the same or different colums that
> are altered, there is still an error
> - There is no error if there is just one UPDATE instead of two (this makes
> me think this might be a bug)
> - There is no error if both UPDATEs lead to the same result (when the second
> UPDATE is a no-op, like setting some_column = 1 again)

I don't think this is a bug.  Your 2nd UPDATE updates the row that was
updated by the previous statement, which hits the following case
inside RI_FKey_fk_upd_check_required():

/*
* If the original row was inserted by our own transaction, we
* must fire the trigger whether or not the keys are equal.  This
* is because our UPDATE will invalidate the INSERT so that the
* INSERT RI trigger will not do anything; so we had better do the
* UPDATE check.  (We could skip this if we knew the INSERT
* trigger already fired, but there is no easy way to know that.)
*/
if (TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetXmin(old_row->t_data)))
return true;

The first UPDATE did not require a check since you didn't update any
of the referencing columns.





--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services