Re: altering foreign key without a table scan

Vincent de Phily <vincent(dot)dephily(at)mobile-devices(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres(dot)consulting(at)comcast(dot)net
p: 305.321.1144