From: | Fabrízio de Royes Mello <fabriziomello(at)gmail(dot)com> |
---|---|
To: | Joe Conway <mail(at)joeconway(dot)com> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: changing primary key col(s) with minimal impact |
Date: | 2014-12-03 23:11:03 |
Message-ID: | CAFcNs+paOaDo1wBm4F8aTER17JFHDH1Xavkt7ATFDp_k9DCBsA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi Joe,
> I have a need to provide a way to change a table's primary key
> columns, in the possible presence of foreign keys, and with concurrent
> use (as much as possible).
>
> The best way I have come up with is roughly:
> a. create the to-be-new-pk index concurrently
> b. "demote" the old pk to be a unique constraint
> c. alter table add constraint new pk using existing index
>
> Step b. involves (in one transaction):
> - --------------------------------------
> * update pg_class row for the table
> relhaspkey false
> * update pg_constraint row for the original pk
> contype = 'u'
> conname = 'some_new_name'
> * update pg_index row for the original pk index
> indisprimary = false
> * alter index original pk rename to some_new_name
> - --------------------------------------
> I have tested this (minimally) and as far as I can tell it works.
>
> Questions:
> 1) any major holes in this approach?
> 2) any better ideas?
> 3) should we consider an ALTER TABLE ALTER CONSTRAINT command
> (or some such) to allow demotion of a PRIMARY KEY to a UNIQUE
> constraint?
>
> Thanks for any thoughts/comments.
>
Hi Joe,
I already did something like it once, but to rebuild a bloated PK index
with minimal locks. I still waiting for 'REINDEX CONCURRENTLY' :-)
At that time I didn't have no trouble with this approach.
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog: http://fabriziomello.github.io
>> Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
>> Github: http://github.com/fabriziomello
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2014-12-03 23:17:51 | Re: postgres_fdw does not see enums |
Previous Message | David Fetter | 2014-12-03 23:05:32 | Re: postgres_fdw does not see enums |