Обсуждение: syntax troubles using "deferrable initially deferred"

"Gauthier, Dave" <dave.gauthier@intel.com> writes:
> create table templates
> (
>   template text,
>   constraint template_not_null_check (template is nit null) deferrable initially deferred);

> ERROR  syntax error at or near "DEFERRABLE"
> LINE 4: ...nt template_not_null_check (template is not null) DEFERRABLE...

Sorry, there are no deferrable check constraints in Postgres.
We only handle deferrable foreign keys (8.5 will have deferrable
unique also).  By and large there isn't any good reason that I
can see for a deferrable check constraint --- you might as well
check the row when it goes in.  (If you are trying something cute
that depends on the timing of the check, be advised that it probably
wouldn't work reliably anyway.)

            regards, tom lane

I believe I can "set constraints" to achieve the same thing?

I just don't want the check to happen until commit time.  The users may make many row changes, the later ones "fixing"
whatthe earlier ones would otherwise flag as problematic with the check. 



-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, September 14, 2009 7:19 PM
To: Gauthier, Dave
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] syntax troubles using "deferrable initially deferred"

"Gauthier, Dave" <dave.gauthier@intel.com> writes:
> create table templates
> (
>   template text,
>   constraint template_not_null_check (template is nit null) deferrable initially deferred);

> ERROR  syntax error at or near "DEFERRABLE"
> LINE 4: ...nt template_not_null_check (template is not null) DEFERRABLE...

Sorry, there are no deferrable check constraints in Postgres.
We only handle deferrable foreign keys (8.5 will have deferrable
unique also).  By and large there isn't any good reason that I
can see for a deferrable check constraint --- you might as well
check the row when it goes in.  (If you are trying something cute
that depends on the timing of the check, be advised that it probably
wouldn't work reliably anyway.)

            regards, tom lane

"Gauthier, Dave" <dave.gauthier@intel.com> writes:
> I believe I can "set constraints" to achieve the same thing?

No, you can't.  There are no deferrable check constraints in Postgres.

> I just don't want the check to happen until commit time.  The users may make many row changes, the later ones
"fixing"what the earlier ones would otherwise flag as problematic with the check. 

Even if it would work, this design implies holding a transaction open
over multiple user interactions, possibly including lunch breaks or
what-have-you.  That's a really bad idea for a number of reasons (see
the archives for elucidation, but locks and vacuum are the key reasons
to avoid very-long-running transactions).  You might consider instead
holding the work-in-progress rows in a temporary table, or something
like that.

If you're really desperate to do it that way, you could consider
testing the conditions in a deferred "constraint trigger" instead of
using CHECK.  But I think you'll find yourself redesigning the system
as soon as you get it into production.

            regards, tom lane