Re: [HACKERS] changing primary key col(s) with minimal impact

[Fabrízio de Royes Mello]

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

[HACKERS] changing primary key col(s) with minimal impact

[Joe Conway]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

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.

Joe

- -- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iQIcBAEBAgAGBQJUf0t5AAoJEDfy90M199hlUT8QAJcobqTmT0fKfBaXbIjXzJLZ
++i0JC/RwDOfnNkUjrEkv+WlQlhwHViKd04JCLWKes8EE81Vv8qUTlQDzphxeHCS
OiPMKSePHzCSphYRwzGp1gurvfcw/Yv/5wQyCcUWBqpa4DYZzUFKkeoH3d2Zzd6z
t6iR8cM21XArFnGMQN1gmAA0swStrm6CdzxydEIY3EoY8tgIGQBjDAEzs/v4bYPG
kEECRYtNu3tiy+ejslB+WWPXd0y+Ty8idRpmgMPMLqIi0RzA8s/p4xhm1INUyr31
fqlN3vaFaPEgXdsuDEqmXt7H0QWyCIwEYelPTh3Zi8RmOOzzeZKNdjaQc6mJHrO7
JFVFMVaw/CRuogui9Q/DMalpbNWjbqoVV4JtPssGK1BBvmpJrEd7QLzFLmz3QSzn
Rdb/UN8RWWAfL50MAztlSpwX/4vPbolvC7yMjg1lGvfm8g0B3qz+iHW3V0G1qX8Y
mxQD3LvnMgUN/m2EUiUr+L+Eh3fEV0M3SbQCii6b+apLjVGe25pHE8zx4QufrJKk
ftX70nRJPaoW/+LBj69n7r7wde3CSpI2/6qbjIXYKu/gutWQEPgxbbd4fxTGICz/
P16y2V4mwKT66Ma3vjz1gwCGcHSShLsJx4PAGFMpR3SE63kDvGE0Zm/RG5u9+z/r
MIkw1EL3cFpTlu+7Jtwj
=pyzW
-END PGP SIGNATURE-


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers