6

I can't seem to find a way to achieve this transactionally (or not)

What I need to achieve is non-standard, hence my difficulty finding a solution.

I need to code a data migration tool to "swap" old records with new records in a table, but I have the following requirements / constraints:

  • Don't lose the old records
  • Make all references to old records point to new records (that's not just db foreign keys, it's references out of my control in external services, caches, emails, historic data, bookmarks, you name it)
  • The migration code needs to be schema agnostic, i.e. shall not need to be updated if a new column is added to the table and independent of which other tables reference it.
  • I can't lock the table for more than the acceptable amount of time it takes to update two records.

So my ideal solution is, well, brute swap their primary keys...

Question is how can I swap the primary keys between two records in postgresql. I am having difficulty finding an approach that does not fail with duplicate key exception, i.e. an approach that runs the "validation" for the update transactionally.

I have tried

UPDATE table
SET id = (CASE id WHEN 1 THEN 2 WHEN 2 THEN 1 ELSE id END)
UPDATE table
SET id = CASE id WHEN 1 THEN 2 WHEN 2 THEN 1 END
WHERE id IN (1, 2);

Both failing on duplicate key constraint

I am using PostgreSQL 11.6


SOLVED

Solution for bulk updates for numeric primary keys, thanks to input from the nice people below:

-- disable foreign key constraint validation
BEGIN;
SET session_replication_role='replica';

-- update the pairs of ids to their negative counterparts
WITH query AS ('query to get pairs of ids to swap')
UPDATE table 
SET id = -id 
WHERE id in (query.id1, query.id2);

-- update the pairs of negated ids to their positive counterparts swapped
WITH query AS ('query to get pairs of ids to swap')
UPDATE table 
SET id = CASE id WHEN query.id1 THEN -query.id2
                 WHEN query.id2 THEN -query.id1
                 END
WHERE id in (query.id1, query.id2);

-- enable foreign key constraint validation
SET session_replication_role='original';
COMMIT;
  • 1
    "The migration code needs to be schema agnostic, i.e. shall not need to be updated if a new column is added to the table and independent of which other tables reference it." Drop that requirement - use a stored proc/function and regenerate the function in a schema event trigger. – Ben Mar 17 at 9:37
  • 1
    Then you can leave the PK alone, and change every other column instead. – Ben Mar 17 at 9:38
  • Can you develop on how you'd write such proc/function and regenerate it on a schema event trigger? maybe add an answer? – Pedro Borges Mar 17 at 15:51
4

You could use a deferred constraint. For that you need to drop and re-create the primary key:

CREATE UNIQUE INDEX mytable_primkey ON mytable (id);
ALTER TABLE mytable DROP CONSTRAINT mytable_pkey;
ALTER TABLE mytable ADD PRIMARY KEY USING INDEX mytable_primkey
   DEFERRABLE INITIALLY DEFERRED;

The update itself could then be done like that:

UPDATE mytable SET id = 3 - id WHERE id IN (1, 2);

Here 1 and 2 are used as examples, you can do that with any numbers.

If you cannot afford the down time required for adding a deferred primary key, you could do it with one more update like this;

BEGIN;
UPDATE mytable SET id = 0 WHERE id = 1;
UPDATE mytable SET id = 1 WHERE id = 2;
UPDATE mytable SET id = 2 WHERE id = 0;
COMMIT;

Here 0 is an arbitrary value that is not used as a value for id.

| improve this answer | |
  • 1
    I prefer to have deferrable constraints be initially immediate so they behave normally unless deferred. – Colin 't Hart Mar 16 at 20:25
  • Adding a primary key in a production table with 100M+ records locks the table for an unacceptable long time. This is not feasible. Also I thought it was clear that 1 and 2 are used as examples. – Pedro Borges Mar 16 at 20:28
  • Ok, then try my updated answer. – Laurenz Albe Mar 16 at 20:44
  • Thanks, but that breaks foreign key constraints on the first update, I need it to be an atomic action. – Pedro Borges Mar 16 at 22:39
  • 2
    Foreign keys can be altered to be deferred. – Laurenz Albe Mar 17 at 0:19
4

The only way that I know this is possible is with a deferred constraint.

You will need to drop the primary key,

alter table x drop constraint x_pkey;

and add it again as deferrable:

alter table x add primary key (id) deferrable initially immediate;

Before performing the update you can defer the constraint:

set constraints x_pkey deferred;

and then perform the update and commit.

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=469e5168c84bdbb8361426d4458bcf88

Of course, if you have other tables referring to this table you will need to drop and recreate the foreign keys too.

| improve this answer | |
  • Thanks, but that violates points 3 and 4 of my requirements – Pedro Borges Mar 16 at 20:26

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.