4

I'm using PostgreSQL 9.6 and i have noticed that when i execute an UPDATE on a single column without foreign key it will fire all triggers for other columns that have a value and a foreign key.
I see this thanks to EXPLAIN ANALYZE that gives me "Trigger for constraint" for each one of those other columns.
With massive updates those triggers takes 50% of the execution time

EDIT:

CREATE TABLE parent (
    id INTEGER,
    PRIMARY KEY(id)
);

CREATE TABLE child (
    id INTEGER,
    is_enabled BOOLEAN DEFAULT FALSE,
    parent_id INTEGER references parent(id),
    PRIMARY KEY(id)
);

INSERT INTO parent (id) VALUES (1);
INSERT INTO child (id, is_enabled, parent_id) VALUES (1, FALSE, 1);

UPDATE child SET is_enabled = FALSE WHERE id = 1;
EXPLAIN ANALYZE
UPDATE child SET is_enabled = TRUE WHERE id = 1;

OUTPUT:

Update on child  (cost=0.16..8.17 rows=1 width=15) (actual time=0.017..0.017 rows=0 loops=1)
  ->  Index Scan using child_pkey on child  (cost=0.16..8.17 rows=1 width=15) (actual time=0.004..0.005 rows=1 loops=1)
        Index Cond: (id = 1)
Planning time: 0.033 ms
Trigger for constraint child_parent_id_fkey: time=0.168 calls=1
Execution time: 0.199 ms

Notice Trigger for constraint child_parent_id_fkey even if i haven't changed parent_id

EDIT 2:
It happens when issuing 2 update statements, same transaction, that covers the same row even if nothing is changed and no FK column is used.
Here is a dbfiddle by joanolo

Is this an expected behavior? Should i fill a bug report?
Now i'm trying to avoid to touch same rows twice in the same transaction

  • 2
    Please Edit your question and add the create table statements for the tables in question including all indexes and the UPDATE statement you are running. Formatted text please, no screen shots – a_horse_with_no_name Jul 17 '17 at 13:47
  • 1
    Which minor version of 9.6? I don't get the "Trigger for constraint" in 9.6.3. – ypercubeᵀᴹ Jul 17 '17 at 15:18
  • 1
    @ypercubeᵀᴹ 9.6.3, i think that i have found the cause... it seems to happen when the same row is updated more than once in the same transaction even with no changes at all BEGIN; UPDATE child SET is_enabled = FALSE WHERE id = 1; EXPLAIN ANALYZE UPDATE child SET is_enabled = FALSE WHERE id = 1; COMMIT; – Luca Looz Jul 17 '17 at 15:40
  • 1
    You need to show the trigger create statement.. – Evan Carroll Jul 17 '17 at 17:14
  • 3
    I don't think this classifies as a bug, but asking this on the Postgres mailing list is probably a good idea. – a_horse_with_no_name Jul 18 '17 at 11:40
2

Tom Lane answered on the mailing list

This happens because an UDPATE invalidates the foreign key triggers for a previous INSERT in the same transaction, so postgres fires them when there is an old tuple version in the same transaction.
It doesn't have the data to know that the previous change on that row was an update without fk changes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.