Horton

Re: creating CHECK constraints as NOT VALID

Thom Brown <thom@linux.com>

View thread
From: Thom Brown <thom@linux.com>
To: Alvaro Herrera <alvherre@commandprompt.com>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Date: 2011-06-03T16:47:58Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix pg_get_constraintdef to cope with NOT VALID constraints

On 2 June 2011 17:48, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> Excerpts from Alvaro Herrera's message of mié jun 01 20:56:12 -0400 2011:
>> Excerpts from Thom Brown's message of mié jun 01 19:48:44 -0400 2011:
>>
>> > Is this expected?
>> > [ pg_dump fails to preserve not-valid status of constraints ]
>>
>> Certainly not.
>>
>> > Shouldn't the constraint be dumped as not valid too??
>>
>> Sure, I'll implement that tomorrow.
>
> Actually, it turns out that NOT VALID foreign keys were already buggy
> here, and fixing them automatically fixes this case as well, because the
> fix involves touching pg_get_constraintdef to dump the flag.  This also
> gets it into psql's \d.  Patch attached.
>
> (Maybe the changes in psql's describe.c should be reverted, not sure.)

Nice work Alvaro :)  Shouldn't patches be sent to -hackers instead of
the obsolete -patches list?  Plus I'm a bit confused as to why the
patch looks like an email instead of a patch.

According to the SQL:2011 standard: "The SQL Standard allows you to
turn the checking on and off for CHECK constraints, UNIQUE constraints
and FOREIGN KEYS."

So is it much work to also add the ADD CONSTRAINT UNIQUE (column, ...)
NOT VALID syntax to this too?  This would mean we're completely
covered for this standards requirement.

Cheers

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company