Обсуждение: Short-circuiting FK check for a newly-added field

I need to add a field to a fairly large table. In the same alter
statement I'd like to add a FK constraint on that new field. Is there
any way to avoid the check of the table that the database is doing
right now? The check is pointless because the newly added field is
nothing but NULLs.

This is version 8.1.mumble.
--
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

On Tue, May 20, 2008 at 02:25:15PM -0400, Decibel! wrote:
> I need to add a field to a fairly large table. In the same alter statement
> I'd like to add a FK constraint on that new field. Is there any way to
> avoid the check of the table that the database is doing right now? The
> check is pointless because the newly added field is nothing but NULLs.
>
> This is version 8.1.mumble.

Have you tried making the FK constraint INITIALLY DEFERRED?

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Tue, May 20, 2008 at 02:25:15PM -0400, Decibel! wrote:
> I need to add a field to a fairly large table. In the same alter statement
> I'd like to add a FK constraint on that new field. Is there any way to
> avoid the check of the table that the database is doing right now? The
> check is pointless because the newly added field is nothing but NULLs.
>
I don't see the problem. FK constraints don't fire on NULL values. I
think you might be imagining that a problem exists when it doesn't.

If the FK column is created as NOT NULL, there is a problem. The best
way to handle this case is to add the column (allowing nulls), populate
the columns, then alter the column to make it NOT NULL. You can wrap
that all in a transaction if you like.

> This is version 8.1.mumble.
>
You can get the version using select version().


-Reece

Reece Hart wrote:
> On Tue, May 20, 2008 at 02:25:15PM -0400, Decibel! wrote:
> > I need to add a field to a fairly large table. In the same alter statement
> > I'd like to add a FK constraint on that new field. Is there any way to
> > avoid the check of the table that the database is doing right now? The
> > check is pointless because the newly added field is nothing but NULLs.
> >
> I don't see the problem. FK constraints don't fire on NULL values. I
> think you might be imagining that a problem exists when it doesn't.

The problem is that it does a table scan only to find that all values
are NULL, which is pretty pointless.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.