pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRecreate primary key without dropping foreign keys?
10+ messages / 4 participants
[nested] [flat]
* Recreate primary key without dropping foreign keys?
@ 2012-04-16 00:41 Chris Ernst <cernst@zvelo.com>
2012-04-16 04:57 ` Re: Recreate primary key without dropping foreign keys? Frank Lanitz <frank@frank.uvena.de>
0 siblings, 1 reply; 10+ messages in thread
From: Chris Ernst @ 2012-04-16 00:41 UTC (permalink / raw)
To: pgsql-admin
Hi all,
In PostgreSQL 9.1.3, I have a few fairly large tables with bloated
primary key indexes. I'm trying to replace them using newly created
unique indexes as outlined in the docs. Something like:
CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id);
ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX
dist_id_temp_idx;
However, the initial drop of the primary key constraint fails because
there are a whole bunch of foreign keys depending on it.
I've done some searching and haven't found a workable solution. Is
there any way to swap in the new index for the primary key constraint
without dropping all dependent foreign keys? Or am I pretty much stuck
with dropping and recreating all of the foreign keys?
Thanks in advance.
Chris Ernst
Data Operations Engineer
Zvelo, Inc.
http://zvelo.com/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Recreate primary key without dropping foreign keys?
2012-04-16 00:41 Recreate primary key without dropping foreign keys? Chris Ernst <cernst@zvelo.com>
@ 2012-04-16 04:57 ` Frank Lanitz <frank@frank.uvena.de>
2012-04-16 08:32 ` Re: Recreate primary key without dropping foreign keys? Chris Ernst <cernst@zvelo.com>
0 siblings, 1 reply; 10+ messages in thread
From: Frank Lanitz @ 2012-04-16 04:57 UTC (permalink / raw)
To: pgsql-admin
On Sun, 15 Apr 2012 18:41:05 -0600
Chris Ernst <cernst@zvelo.com> wrote:
> Hi all,
>
> In PostgreSQL 9.1.3, I have a few fairly large tables with bloated
> primary key indexes. I'm trying to replace them using newly created
> unique indexes as outlined in the docs. Something like:
>
> CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors
> (dist_id); ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
> ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX
> dist_id_temp_idx;
>
> However, the initial drop of the primary key constraint fails because
> there are a whole bunch of foreign keys depending on it.
>
> I've done some searching and haven't found a workable solution. Is
> there any way to swap in the new index for the primary key constraint
> without dropping all dependent foreign keys? Or am I pretty much
> stuck with dropping and recreating all of the foreign keys?
REINDEX is not working here?
Cheers,
Frank
--
Frank Lanitz <frank@frank.uvena.de>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Recreate primary key without dropping foreign keys?
2012-04-16 00:41 Recreate primary key without dropping foreign keys? Chris Ernst <cernst@zvelo.com>
2012-04-16 04:57 ` Re: Recreate primary key without dropping foreign keys? Frank Lanitz <frank@frank.uvena.de>
@ 2012-04-16 08:32 ` Chris Ernst <cernst@zvelo.com>
2012-04-16 08:39 ` Re: Recreate primary key without dropping foreign keys? Frank Lanitz <frank@frank.uvena.de>
0 siblings, 1 reply; 10+ messages in thread
From: Chris Ernst @ 2012-04-16 08:32 UTC (permalink / raw)
To: pgsql-admin
On 04/15/2012 10:57 PM, Frank Lanitz wrote:
> On Sun, 15 Apr 2012 18:41:05 -0600 Chris Ernst <cernst@zvelo.com>
> wrote:
>
>> Hi all,
>>
>> In PostgreSQL 9.1.3, I have a few fairly large tables with
>> bloated primary key indexes. I'm trying to replace them using
>> newly created unique indexes as outlined in the docs. Something
>> like:
>>
>> CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON
>> distributors (dist_id); ALTER TABLE distributors DROP CONSTRAINT
>> distributors_pkey, ADD CONSTRAINT distributors_pkey PRIMARY KEY
>> USING INDEX dist_id_temp_idx;
>>
>> However, the initial drop of the primary key constraint fails
>> because there are a whole bunch of foreign keys depending on it.
>>
>> I've done some searching and haven't found a workable solution.
>> Is there any way to swap in the new index for the primary key
>> constraint without dropping all dependent foreign keys? Or am I
>> pretty much stuck with dropping and recreating all of the foreign
>> keys?
>
> REINDEX is not working here?
Hi Frank,
Thanks, but REINDEX is not an option as it would take an exclusive
lock on the table for several hours.
For all of the other indexes, I create a new index concurrently, drop
the old and swap in the new. But the primary key is a bit trickier
because I can't drop the primary key index without dropping the
primary key constraint and I can't drop the primary key constraint
without dropping all of the foreign keys that reference that column.
- Chris
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Recreate primary key without dropping foreign keys?
2012-04-16 00:41 Recreate primary key without dropping foreign keys? Chris Ernst <cernst@zvelo.com>
2012-04-16 04:57 ` Re: Recreate primary key without dropping foreign keys? Frank Lanitz <frank@frank.uvena.de>
2012-04-16 08:32 ` Re: Recreate primary key without dropping foreign keys? Chris Ernst <cernst@zvelo.com>
@ 2012-04-16 08:39 ` Frank Lanitz <frank@frank.uvena.de>
2012-04-16 13:54 ` Re: Recreate primary key without dropping foreign keys? Chris Ernst <cernst@zvelo.com>
0 siblings, 1 reply; 10+ messages in thread
From: Frank Lanitz @ 2012-04-16 08:39 UTC (permalink / raw)
To: pgsql-admin
Am 16.04.2012 10:32, schrieb Chris Ernst:
> On 04/15/2012 10:57 PM, Frank Lanitz wrote:
>> On Sun, 15 Apr 2012 18:41:05 -0600 Chris Ernst <cernst@zvelo.com>
>> wrote:
>>
>>> Hi all,
>>>
>>> In PostgreSQL 9.1.3, I have a few fairly large tables with
>>> bloated primary key indexes. I'm trying to replace them using
>>> newly created unique indexes as outlined in the docs. Something
>>> like:
>>>
>>> CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON
>>> distributors (dist_id); ALTER TABLE distributors DROP CONSTRAINT
>>> distributors_pkey, ADD CONSTRAINT distributors_pkey PRIMARY KEY
>>> USING INDEX dist_id_temp_idx;
>>>
>>> However, the initial drop of the primary key constraint fails
>>> because there are a whole bunch of foreign keys depending on it.
>>>
>>> I've done some searching and haven't found a workable solution.
>>> Is there any way to swap in the new index for the primary key
>>> constraint without dropping all dependent foreign keys? Or am I
>>> pretty much stuck with dropping and recreating all of the foreign
>>> keys?
>>
>> REINDEX is not working here?
>
> Hi Frank,
>
> Thanks, but REINDEX is not an option as it would take an exclusive
> lock on the table for several hours.
Well, from my little view I guess all rebuilding index action would
require such, as its the primary key with uniqueness. I'd think of a
complete reinit of the cluster with pg_dump and restoring, but this
would also need a downtime at least for write access.
Why is the index so bloated?
Cheers,
Frank
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Recreate primary key without dropping foreign keys?
2012-04-16 00:41 Recreate primary key without dropping foreign keys? Chris Ernst <cernst@zvelo.com>
2012-04-16 04:57 ` Re: Recreate primary key without dropping foreign keys? Frank Lanitz <frank@frank.uvena.de>
2012-04-16 08:32 ` Re: Recreate primary key without dropping foreign keys? Chris Ernst <cernst@zvelo.com>
2012-04-16 08:39 ` Re: Recreate primary key without dropping foreign keys? Frank Lanitz <frank@frank.uvena.de>
@ 2012-04-16 13:54 ` Chris Ernst <cernst@zvelo.com>
2012-04-17 01:02 ` Re: Recreate primary key without dropping foreign keys? amador alvarez <aalvarez@d2.com>
0 siblings, 1 reply; 10+ messages in thread
From: Chris Ernst @ 2012-04-16 13:54 UTC (permalink / raw)
To: pgsql-admin
On 04/16/2012 02:39 AM, Frank Lanitz wrote:
> Am 16.04.2012 10:32, schrieb Chris Ernst:
>> On 04/15/2012 10:57 PM, Frank Lanitz wrote:
>>> On Sun, 15 Apr 2012 18:41:05 -0600 Chris Ernst <cernst@zvelo.com>
>>> wrote:
>>>
>>>> Hi all,
>>>>
>>>> In PostgreSQL 9.1.3, I have a few fairly large tables with
>>>> bloated primary key indexes. I'm trying to replace them using
>>>> newly created unique indexes as outlined in the docs. Something
>>>> like:
>>>>
>>>> CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON
>>>> distributors (dist_id); ALTER TABLE distributors DROP CONSTRAINT
>>>> distributors_pkey, ADD CONSTRAINT distributors_pkey PRIMARY KEY
>>>> USING INDEX dist_id_temp_idx;
>>>>
>>>> However, the initial drop of the primary key constraint fails
>>>> because there are a whole bunch of foreign keys depending on it.
>>>>
>>>> I've done some searching and haven't found a workable solution.
>>>> Is there any way to swap in the new index for the primary key
>>>> constraint without dropping all dependent foreign keys? Or am I
>>>> pretty much stuck with dropping and recreating all of the foreign
>>>> keys?
>>>
>>> REINDEX is not working here?
>>
>> Hi Frank,
>>
>> Thanks, but REINDEX is not an option as it would take an exclusive
>> lock on the table for several hours.
>
> Well, from my little view I guess all rebuilding index action would
> require such, as its the primary key with uniqueness. I'd think of a
> complete reinit of the cluster with pg_dump and restoring, but this
> would also need a downtime at least for write access.
>
> Why is the index so bloated?
As in my original post, you can create a unique index concurrently and
then replace the primary key index with it. This way, the index
creation doesn't require an exclusive lock. You only need a very brief
exclusive lock to drop and recreate the primary key constraint using the
new index.
However, the index creation is not the issue here. That part is done.
The issue is that there are several foreign keys depending on the
primary key index that I want to drop and replace with the newly built
unique index. I would prefer not to drop and recreate all of the
foreign keys as that would require many hours of down time as well (the
very situation I was trying to avoid by building the index concurrently
and swapping it in).
I believe the index bloat is due to a combination of under aggressive
autovacuum settings and recently deleting about 30% of the table.
- Chris
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Recreate primary key without dropping foreign keys?
2012-04-16 00:41 Recreate primary key without dropping foreign keys? Chris Ernst <cernst@zvelo.com>
2012-04-16 04:57 ` Re: Recreate primary key without dropping foreign keys? Frank Lanitz <frank@frank.uvena.de>
2012-04-16 08:32 ` Re: Recreate primary key without dropping foreign keys? Chris Ernst <cernst@zvelo.com>
2012-04-16 08:39 ` Re: Recreate primary key without dropping foreign keys? Frank Lanitz <frank@frank.uvena.de>
2012-04-16 13:54 ` Re: Recreate primary key without dropping foreign keys? Chris Ernst <cernst@zvelo.com>
@ 2012-04-17 01:02 ` amador alvarez <aalvarez@d2.com>
2012-04-17 02:55 ` Re: Recreate primary key without dropping foreign keys? Chris Ernst <cernst@zvelo.com>
0 siblings, 1 reply; 10+ messages in thread
From: amador alvarez @ 2012-04-17 01:02 UTC (permalink / raw)
To: pgsql-admin
How about deferring the FK's while recreating the PK ?
or using a temporary parallel table to be pointed by the other tables
(FK) and swap it up on the recreation.
Cheers,
A.A
On 04/16/2012 06:54 AM, Chris Ernst wrote:
> On 04/16/2012 02:39 AM, Frank Lanitz wrote:
>> Am 16.04.2012 10:32, schrieb Chris Ernst:
>>> On 04/15/2012 10:57 PM, Frank Lanitz wrote:
>>>> On Sun, 15 Apr 2012 18:41:05 -0600 Chris Ernst<cernst@zvelo.com>
>>>> wrote:
>>>>
>>>>> Hi all,
>>>>>
>>>>> In PostgreSQL 9.1.3, I have a few fairly large tables with
>>>>> bloated primary key indexes. I'm trying to replace them using
>>>>> newly created unique indexes as outlined in the docs. Something
>>>>> like:
>>>>>
>>>>> CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON
>>>>> distributors (dist_id); ALTER TABLE distributors DROP CONSTRAINT
>>>>> distributors_pkey, ADD CONSTRAINT distributors_pkey PRIMARY KEY
>>>>> USING INDEX dist_id_temp_idx;
>>>>>
>>>>> However, the initial drop of the primary key constraint fails
>>>>> because there are a whole bunch of foreign keys depending on it.
>>>>>
>>>>> I've done some searching and haven't found a workable solution.
>>>>> Is there any way to swap in the new index for the primary key
>>>>> constraint without dropping all dependent foreign keys? Or am I
>>>>> pretty much stuck with dropping and recreating all of the foreign
>>>>> keys?
>>>> REINDEX is not working here?
>>> Hi Frank,
>>>
>>> Thanks, but REINDEX is not an option as it would take an exclusive
>>> lock on the table for several hours.
>> Well, from my little view I guess all rebuilding index action would
>> require such, as its the primary key with uniqueness. I'd think of a
>> complete reinit of the cluster with pg_dump and restoring, but this
>> would also need a downtime at least for write access.
>>
>> Why is the index so bloated?
> As in my original post, you can create a unique index concurrently and
> then replace the primary key index with it. This way, the index
> creation doesn't require an exclusive lock. You only need a very brief
> exclusive lock to drop and recreate the primary key constraint using the
> new index.
>
> However, the index creation is not the issue here. That part is done.
> The issue is that there are several foreign keys depending on the
> primary key index that I want to drop and replace with the newly built
> unique index. I would prefer not to drop and recreate all of the
> foreign keys as that would require many hours of down time as well (the
> very situation I was trying to avoid by building the index concurrently
> and swapping it in).
>
> I believe the index bloat is due to a combination of under aggressive
> autovacuum settings and recently deleting about 30% of the table.
>
> - Chris
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Recreate primary key without dropping foreign keys?
2012-04-16 00:41 Recreate primary key without dropping foreign keys? Chris Ernst <cernst@zvelo.com>
2012-04-16 04:57 ` Re: Recreate primary key without dropping foreign keys? Frank Lanitz <frank@frank.uvena.de>
2012-04-16 08:32 ` Re: Recreate primary key without dropping foreign keys? Chris Ernst <cernst@zvelo.com>
2012-04-16 08:39 ` Re: Recreate primary key without dropping foreign keys? Frank Lanitz <frank@frank.uvena.de>
2012-04-16 13:54 ` Re: Recreate primary key without dropping foreign keys? Chris Ernst <cernst@zvelo.com>
2012-04-17 01:02 ` Re: Recreate primary key without dropping foreign keys? amador alvarez <aalvarez@d2.com>
@ 2012-04-17 02:55 ` Chris Ernst <cernst@zvelo.com>
2012-04-17 13:43 ` Re: Recreate primary key without dropping foreign keys? Igor Neyman <ineyman@perceptron.com>
0 siblings, 1 reply; 10+ messages in thread
From: Chris Ernst @ 2012-04-17 02:55 UTC (permalink / raw)
To: pgsql-admin
On 04/16/2012 07:02 PM, amador alvarez wrote:
> How about deferring the FK's while recreating the PK ?
> or using a temporary parallel table to be pointed by the other tables
> (FK) and swap it up on the recreation.
Hmm.. Interesting. But it appears that you have to declare the foreign
key as deferrable at creation. Is there any way to set an existing
foreign key as deferrable?
- Chris
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Recreate primary key without dropping foreign keys?
2012-04-16 00:41 Recreate primary key without dropping foreign keys? Chris Ernst <cernst@zvelo.com>
2012-04-16 04:57 ` Re: Recreate primary key without dropping foreign keys? Frank Lanitz <frank@frank.uvena.de>
2012-04-16 08:32 ` Re: Recreate primary key without dropping foreign keys? Chris Ernst <cernst@zvelo.com>
2012-04-16 08:39 ` Re: Recreate primary key without dropping foreign keys? Frank Lanitz <frank@frank.uvena.de>
2012-04-16 13:54 ` Re: Recreate primary key without dropping foreign keys? Chris Ernst <cernst@zvelo.com>
2012-04-17 01:02 ` Re: Recreate primary key without dropping foreign keys? amador alvarez <aalvarez@d2.com>
2012-04-17 02:55 ` Re: Recreate primary key without dropping foreign keys? Chris Ernst <cernst@zvelo.com>
@ 2012-04-17 13:43 ` Igor Neyman <ineyman@perceptron.com>
2012-04-17 19:36 ` Re: Recreate primary key without dropping foreign keys? amador alvarez <aalvarez@d2.com>
2012-04-18 00:03 ` Re: Recreate primary key without dropping foreign keys? Chris Ernst <cernst@zvelo.com>
0 siblings, 2 replies; 10+ messages in thread
From: Igor Neyman @ 2012-04-17 13:43 UTC (permalink / raw)
To: Chris Ernst <cernst@zvelo.com>; pgsql-admin
> -----Original Message-----
> From: Chris Ernst [mailto:cernst@zvelo.com]
> Sent: Monday, April 16, 2012 10:55 PM
> To: pgsql-admin@postgresql.org
> Subject: Re: Recreate primary key without dropping foreign keys?
>
> On 04/16/2012 07:02 PM, amador alvarez wrote:
> > How about deferring the FK's while recreating the PK ?
> > or using a temporary parallel table to be pointed by the other tables
> > (FK) and swap it up on the recreation.
>
> Hmm.. Interesting. But it appears that you have to declare the
> foreign
> key as deferrable at creation. Is there any way to set an existing
> foreign key as deferrable?
>
> - Chris
May be this (from the docs) would help:
"ADD table_constraint [ NOT VALID ]
This form adds a new constraint to a table using the same syntax as CREATE TABLE, plus the option NOT VALID, which is currently only allowed for foreign key constraints. If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. The constraint will still be enforced against subsequent inserts or updates (that is, they'll fail unless there is a matching row in the referenced table). But the database will not assume that the constraint holds for all rows in the table, until it is validated by using the VALIDATE CONSTRAINT option."
Using this option you can drop and recreate corresponding FKs in a very short time, and start using them, while postponing to run "VALIDATE CONSTRAINT" for later.
It's similar to Oracle's adding FK with "NOCHECK" option, but if IRC there is no need to run "VALIDATE CONSTRAINT" later.
Regards,
Igor Neyman
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Recreate primary key without dropping foreign keys?
2012-04-16 00:41 Recreate primary key without dropping foreign keys? Chris Ernst <cernst@zvelo.com>
2012-04-16 04:57 ` Re: Recreate primary key without dropping foreign keys? Frank Lanitz <frank@frank.uvena.de>
2012-04-16 08:32 ` Re: Recreate primary key without dropping foreign keys? Chris Ernst <cernst@zvelo.com>
2012-04-16 08:39 ` Re: Recreate primary key without dropping foreign keys? Frank Lanitz <frank@frank.uvena.de>
2012-04-16 13:54 ` Re: Recreate primary key without dropping foreign keys? Chris Ernst <cernst@zvelo.com>
2012-04-17 01:02 ` Re: Recreate primary key without dropping foreign keys? amador alvarez <aalvarez@d2.com>
2012-04-17 02:55 ` Re: Recreate primary key without dropping foreign keys? Chris Ernst <cernst@zvelo.com>
2012-04-17 13:43 ` Re: Recreate primary key without dropping foreign keys? Igor Neyman <ineyman@perceptron.com>
@ 2012-04-17 19:36 ` amador alvarez <aalvarez@d2.com>
1 sibling, 0 replies; 10+ messages in thread
From: amador alvarez @ 2012-04-17 19:36 UTC (permalink / raw)
To: pgsql-admin
Unfortunately I checked out that the deferrable option does not let us
drop the PK (postgres8.4) while remaining FK's , I did not try on the
constraint as NOT VALID is not supported by postgres8.
So unless you have a 9 release or you get a try on a parallel table, you
have to follow the manual procedure :
Generate new index
drop FK's
Drop PK
Recreate PK swiching to the new index
Recreate FK's
Can you afford a quick temporary user access to the database?
On 04/17/2012 06:43 AM, Igor Neyman wrote:
>> -----Original Message-----
>> From: Chris Ernst [mailto:cernst@zvelo.com]
>> Sent: Monday, April 16, 2012 10:55 PM
>> To: pgsql-admin@postgresql.org
>> Subject: Re: Recreate primary key without dropping foreign keys?
>>
>> On 04/16/2012 07:02 PM, amador alvarez wrote:
>>> How about deferring the FK's while recreating the PK ?
>>> or using a temporary parallel table to be pointed by the other tables
>>> (FK) and swap it up on the recreation.
>> Hmm.. Interesting. But it appears that you have to declare the
>> foreign
>> key as deferrable at creation. Is there any way to set an existing
>> foreign key as deferrable?
>>
>> - Chris
> May be this (from the docs) would help:
>
> "ADD table_constraint [ NOT VALID ]
>
> This form adds a new constraint to a table using the same syntax as CREATE TABLE, plus the option NOT VALID, which is currently only allowed for foreign key constraints. If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. The constraint will still be enforced against subsequent inserts or updates (that is, they'll fail unless there is a matching row in the referenced table). But the database will not assume that the constraint holds for all rows in the table, until it is validated by using the VALIDATE CONSTRAINT option."
>
> Using this option you can drop and recreate corresponding FKs in a very short time, and start using them, while postponing to run "VALIDATE CONSTRAINT" for later.
>
> It's similar to Oracle's adding FK with "NOCHECK" option, but if IRC there is no need to run "VALIDATE CONSTRAINT" later.
>
> Regards,
> Igor Neyman
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Recreate primary key without dropping foreign keys?
2012-04-16 00:41 Recreate primary key without dropping foreign keys? Chris Ernst <cernst@zvelo.com>
2012-04-16 04:57 ` Re: Recreate primary key without dropping foreign keys? Frank Lanitz <frank@frank.uvena.de>
2012-04-16 08:32 ` Re: Recreate primary key without dropping foreign keys? Chris Ernst <cernst@zvelo.com>
2012-04-16 08:39 ` Re: Recreate primary key without dropping foreign keys? Frank Lanitz <frank@frank.uvena.de>
2012-04-16 13:54 ` Re: Recreate primary key without dropping foreign keys? Chris Ernst <cernst@zvelo.com>
2012-04-17 01:02 ` Re: Recreate primary key without dropping foreign keys? amador alvarez <aalvarez@d2.com>
2012-04-17 02:55 ` Re: Recreate primary key without dropping foreign keys? Chris Ernst <cernst@zvelo.com>
2012-04-17 13:43 ` Re: Recreate primary key without dropping foreign keys? Igor Neyman <ineyman@perceptron.com>
@ 2012-04-18 00:03 ` Chris Ernst <cernst@zvelo.com>
1 sibling, 0 replies; 10+ messages in thread
From: Chris Ernst @ 2012-04-18 00:03 UTC (permalink / raw)
To: Igor Neyman <ineyman@perceptron.com>; +Cc: pgsql-admin
On 04/17/2012 07:43 AM, Igor Neyman wrote:
>> -----Original Message-----
>> From: Chris Ernst [mailto:cernst@zvelo.com]
>> Sent: Monday, April 16, 2012 10:55 PM
>> To: pgsql-admin@postgresql.org
>> Subject: Re: Recreate primary key without dropping foreign keys?
>>
>> On 04/16/2012 07:02 PM, amador alvarez wrote:
>>> How about deferring the FK's while recreating the PK ?
>>> or using a temporary parallel table to be pointed by the other tables
>>> (FK) and swap it up on the recreation.
>>
>> Hmm.. Interesting. But it appears that you have to declare the
>> foreign
>> key as deferrable at creation. Is there any way to set an existing
>> foreign key as deferrable?
>>
>> - Chris
>
> May be this (from the docs) would help:
>
> "ADD table_constraint [ NOT VALID ]
>
> This form adds a new constraint to a table using the same syntax as CREATE TABLE, plus the option NOT VALID, which is currently only allowed for foreign key constraints. If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. The constraint will still be enforced against subsequent inserts or updates (that is, they'll fail unless there is a matching row in the referenced table). But the database will not assume that the constraint holds for all rows in the table, until it is validated by using the VALIDATE CONSTRAINT option."
>
> Using this option you can drop and recreate corresponding FKs in a very short time, and start using them, while postponing to run "VALIDATE CONSTRAINT" for later.
>
> It's similar to Oracle's adding FK with "NOCHECK" option, but if IRC there is no need to run "VALIDATE CONSTRAINT" later.
Hi Igor,
Oooooo... I like the sound of this. I'll give this a shot in the test
environment and report back my findings.
Thanks a bunch!
- Chris
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2012-04-18 00:03 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-04-16 00:41 Recreate primary key without dropping foreign keys? Chris Ernst <cernst@zvelo.com>
2012-04-16 04:57 ` Frank Lanitz <frank@frank.uvena.de>
2012-04-16 08:32 ` Chris Ernst <cernst@zvelo.com>
2012-04-16 08:39 ` Frank Lanitz <frank@frank.uvena.de>
2012-04-16 13:54 ` Chris Ernst <cernst@zvelo.com>
2012-04-17 01:02 ` amador alvarez <aalvarez@d2.com>
2012-04-17 02:55 ` Chris Ernst <cernst@zvelo.com>
2012-04-17 13:43 ` Igor Neyman <ineyman@perceptron.com>
2012-04-17 19:36 ` amador alvarez <aalvarez@d2.com>
2012-04-18 00:03 ` Chris Ernst <cernst@zvelo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox