Primary key collision in scope of one trasaction

Question

I have a postgresql database, which heavily relies on events from the outside, e.g. administrator changing / adding some fields or records might trigger a change in overall fields structure in other tables.

There lies the problem, however, as sometimes the fields changed by the trigger function are primary key fields. There is a table, which uses two foreign keys ids as the primary key, as in example below:

#  | PK id1 | PK id2 |  data  |
0  |   1    |   1    |   ab   |
1  |   1    |   2    |   cd   |
2  |   1    |   3    |   ef   |

However, within one transaction (if I may call it such, since, in fact, it is a plpgsql function), the structure might be changed to:

#  | PK id1 | PK id2 |  data  |
0  |   1    |   3    |   ab   |
1  |   1    |   2    |   cd   |
2  |   1    |   1    |   ef   |

Which, as you might have noticed, changed the 0th record's second primary key to 3, and the 2nd's to 1, which is the opposite of what they were before.

It is 100% certain that after the function has taken its effect there will be no collisions whatsoever, but I'm wondering, how can this be implemented?

I could, in fact, use a synthetic primary key as a BIGSERIAL, yet there is still a need for those two ids to be UNIQUE constained, so it wouldn't do the trick, unfortunately.

sticky bit · Accepted Answer · 2020-12-21T11:28:53.247

You can declare a constraint as deferrable, for example a primary key:

CREATE TABLE elbat (id int,
                    nmuloc int,
                    PRIMARY KEY (id)
                                DEFERRABLE);

You can then use SET CONSTRAINTS in a transaction to set deferrable constraints as deferred. That means that they can be violated temporarily during the transaction but must be fulfilled at the transaction's COMMIT.

Let's assume we have some data in our example table:

INSERT INTO elbat (id,
                   nmuloc)
                  VALUES (1,
                          1),
                         (2,
                          2);

We can now switch the IDs like this:

BEGIN TRANSACTION;

SET CONSTRAINTS ALL DEFERRED;

UPDATE elbat
       SET id = 2
       WHERE nmuloc = 1;

SELECT *
       FROM elbat;
       
UPDATE elbat
       SET id = 1
       WHERE nmuloc = 2;
       
COMMIT;

There's no error even though the IDs are both 2 after the first UPDATE.
db<>fiddle

More on that can be found in the documentation, e.g. in CREATE TABLE (or ALTER TABLE) and SET CONSTRAINTS.

Primary key collision in scope of one trasaction

1 Answers1