UPDATE column without FK fires other FK triggers constraint check
I was analyzing an update function and i have noticed "Trigger for
constraint" entries for foreign keys that i wasn't using or referring.
After some tests it seems that this happens when the same row is covered by
more than 1 update in the same transaction even without any change.
Here is a dbfiddle example:
http://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=368289a7338031b8a7b7a90f0fd25d7c
Is this an expected behavior? Why it happens?
I have initially asked this on dba stackexchange:
https://dba.stackexchange.com/questions/180012/
Luca Looz <luca.looz92@gmail.com> writes:
After some tests it seems that this happens when the same row is covered by
more than 1 update in the same transaction even without any change.
Is this an expected behavior? Why it happens?
Yes, see comment in 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.)
Although this is talking about the BEGIN; INSERT; UPDATE; COMMIT case,
the code has no way to tell that apart from BEGIN; UPDATE; UPDATE; COMMIT.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Thanks for the explanation!
Can these checks be implemented or the data needed is not there and adding
it will only add an overhead for the majority of use cases?
2017-07-19 20:42 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
Show quoted text
Luca Looz <luca.looz92@gmail.com> writes:
After some tests it seems that this happens when the same row is covered
by
more than 1 update in the same transaction even without any change.
Is this an expected behavior? Why it happens?Yes, see comment in 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.)Although this is talking about the BEGIN; INSERT; UPDATE; COMMIT case,
the code has no way to tell that apart from BEGIN; UPDATE; UPDATE; COMMIT.regards, tom lane
Luca Looz <luca.looz92@gmail.com> writes:
Thanks for the explanation!
Can these checks be implemented or the data needed is not there and adding
it will only add an overhead for the majority of use cases?
It's hard to see how to do much better than we're doing without storing
more data on-disk than is there now. Whether that would be a good
tradeoff is dubious.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general