On Tue, Mar 24, 2015 at 4:28 PM, Fabrízio de Royes Mello < fabriziome...@gmail.com> wrote: > > > > On Mon, Mar 23, 2015 at 12:33 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > > > > =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= <fabriziome...@gmail.com> writes: > > > On Fri, Mar 20, 2015 at 4:37 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > > >>>> We could fix it by, say, having CheckConstraintFetch() sort the > > >>>> constraints by name after loading them. > > > > > Isn't better do this to read pg_constraint in name order? > > > > > - conscan = systable_beginscan(conrel, ConstraintRelidIndexId, true, > > > + conscan = systable_beginscan(conrel, ConstraintNameNspIndexId, true, > > > > Surely not. That would end up having to read *all* of pg_constraint, not > > only the rows applicable to the current relation. > > > > Yeah... you're correct... we need the oid in the index. > > > > We could get the index to do the work for us if we changed it from an > > index on conrelid to one on conrelid, conname. However, seeing that that > > would bloat the index by a factor of sixteen, it hardly sounds like a > > free fix either. > > > > But in this way we can save some cicles as Ashutosh complains... or am I missing something? > > > > I really think that a quick application of qsort is the best-performing > > way to do this. > > > > Something like the attached? > > With current master: > > fabrizio=# create table foo(a integer, b integer); > CREATE TABLE > fabrizio=# alter table foo add constraint aa check(a>0); > ALTER TABLE > fabrizio=# alter table foo add constraint bb check(b>0); > ALTER TABLE > fabrizio=# insert into foo values (0,0); > ERROR: new row for relation "foo" violates check constraint "bb" > DETAIL: Failing row contains (0, 0). > > > With the attached patch: > > fabrizio=# create table foo(a integer, b integer); > CREATE TABLE > fabrizio=# alter table foo add constraint aa check(a>0); > ALTER TABLE > fabrizio=# alter table foo add constraint bb check(b>0); > ALTER TABLE > fabrizio=# insert into foo values (0,0); > ERROR: new row for relation "foo" violates check constraint "aa" > DETAIL: Failing row contains (0, 0). >
Forgot this patch... you've already pushed to the master the qsort for check constraints [1]. Really nice!! Regards, [1] http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e5f455f59fed0632371cddacddd79895b148dc07 -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL >> Timbira: http://www.timbira.com.br >> Blog: http://fabriziomello.github.io >> Linkedin: http://br.linkedin.com/in/fabriziomello >> Twitter: http://twitter.com/fabriziomello >> Github: http://github.com/fabriziomello
