Re: Deferred foreign key and two updates block ALTER TABLE

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