Re: Deferrable check constraints

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Deferrable check constraints
Date: 2016-02-21 02:16:38
Message-ID: 9782.1456020998@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
> I recently wished for deferrable check constraints while doing some
> crash-recovery stress testing.

> I don't know how important they would be for real-world cases, but the
> SQL standard does seem to require them, so I think they would be
> desirable just for that reason.

> There isn't an entry for this on the wiki TODO list. I wanted to add
> an entry, but I can't find any mail threads to link to about why this
> isn't wanted, isn't possible, or isn't easy. Or for that matter, *is*
> easy, but no one has gotten around to it.

It's doable, no doubt, but here's the problem: what use-cases are there
that don't violate the principle that a check constraint should depend
only on the data in the row? And if you want to violate that principle,
what do you have to do to make check constraints actually work in the
face of such violations? In other words, "run the check later in my
transaction" doesn't scratch the surface of what you'd have to do to
enforce a check constraint that may depend on data outside the row.

I suppose there might be cases where you're not trying to do that but just
want to violate the constraint transiently within a transaction, expecting
to update the row again to make it valid before commit. But that doesn't
really sound common enough to justify the work and additional code needed.

regards, tom lane

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Sridhar N Bamandlapally 2016-02-21 02:20:19 Re: JDBC behaviour
Previous Message Jeff Janes 2016-02-21 02:05:06 Deferrable check constraints