Re: altering foreign key without a table scan - Mailing list pgsql-general

From Jerry Sievers
Subject Re: altering foreign key without a table scan
Date
Msg-id 87wreaabr1.fsf@comcast.net
Whole thread Raw
In response to altering foreign key without a table scan  (Vincent de Phily <vincent.dephily@mobile-devices.fr>)
Responses Re: altering foreign key without a table scan  (Vincent de Phily <vincent.dephily@mobile-devices.fr>)
List pgsql-general
Vincent de Phily <vincent.dephily@mobile-devices.fr> writes:

> Hi list,
>
> as part of a db schema update, I'd like to alter the "on update" property of a
> fkey, for example going from :
>> ALTER TABLE ONLY foo ADD CONSTRAINT foo_barid_fkey FOREIGN KEY (barid)
>> REFERENCES bar(id) ON UPDATE CASCADE ON DELETE CASCADE;
> to :
>> ALTER TABLE ONLY foo ADD CONSTRAINT foo_barid_fkey FOREIGN KEY (barid)
>> REFERENCES bar(id) ON UPDATE RESTRICT ON DELETE CASCADE;
>
> I understand I can create the new fkey and drop the old one, but this requires
> a scan of the table (to check that no existing data violates the new fkey)
> which, on this large, heavily-updated, no-downtime table I can't really aford.
>
> The thing is, I know there is no violation by existing data, because of the
> existing fkey. So locking and scaning the table to add the "duplicate" fkey is
> not necessary. In a sense, I'm looking for :
>> ALTER TABLE foo ALTER CONSTRAINT foo_barid_fkey ON UPDATE RESTRICT;
> I'm guessing/wishfull-thinking that some hackery with the system catalog could
> emulate that ?
>
> I'm currently using postgres 8.3 (we want to upgrade, but it's hard to
> schedule).

Two things first...

1. I assume this is same for 8.3
2. Someone from Hackers best to answer if this is safe on live system
   or might require at least a restart.

Your 2 catalog fields of interest are; pg_constraint.(confupdtype|confdeltype)

Changing those for the relevant FKs should satisfy your needs.  I am
not aware of those field values being duplicated anywhere.

Strongly suggest you approach this with caution, as is standard
advice regarding any manual catalog fiddling.

HTH

>
> Thanks in advance.
>
> --
> Vincent de Phily
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 305.321.1144

pgsql-general by date:

Previous
From:
Date:
Subject: Suspicious Bill
Next
From: Chris Travers
Date:
Subject: Re: Using Postgresql as application server