Skip site navigation (1) Skip section navigation (2)

Re: constraint with reference to the same table

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Victor Yegorov <viy(at)nordlb(dot)lv>
Cc: Rudi Starcevic <rudi(at)oasis(dot)net(dot)au>,Postgres Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: constraint with reference to the same table
Date: 2003-05-15 01:24:32
Message-ID: 20030514180750.L52444-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Thu, 15 May 2003, Victor Yegorov wrote:

> * Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> [15.05.2003 03:54]:
> >
> > That can be a win, but if you're actually dropping and adding the
> > constraint again it may not be on large tables since it'll still do a
> > whole bunch of index lookups to check the existing rows when the alter
> > table add constraint happens.  Disabling triggers and re-enabling them is
> > faster but breaks the guarantee of the constraint.
>
> You're right. I thought of big tables after posting the reply. My solution
> is suitable for my case, i.e. not so big tables.

This may become slightly a higher point of balance if we change the alter
table time check to a single query rather than repeated checks as well.

> Returning to the very first question I asked.
> May be it is usefull to implicitly create index on foreign key columns?

Maybe, it seems to me that we've been trying to move away from such
implicit behavior (such as serial columns no longer implicitly being
unique) in general.  I don't personally have a strong feeling on the
subject.


In response to

pgsql-performance by date

Next:From: T. Alex BeamishDate: 2003-05-15 01:45:04
Subject: INNER JOIN vs WHERE
Previous:From: Stephan SzaboDate: 2003-05-15 01:23:27
Subject: Re: constraint with reference to the same table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group