From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jasen Betts <jasen@xnet.co.nz>
Cc: Mario Splivalo <mario.splivalo@megafon.hr>
Cc: pgsql-sql@postgresql.org
Subject: Re: Re: CHECK constraints with plpgsql functions - check 'fires' BEFORE data modification?
Date: Mon, 19 Apr 2010 10:20:59 -0400
Message-ID: <13340.1271686859@sss.pgh.pa.us> (raw)
In-Reply-To: <hqhcgl$bij$1@reversiblemaps.ath.cx>
References: <4BCC272C.3020505@megafon.hr>
<hqhcgl$bij$1@reversiblemaps.ath.cx>
Jasen Betts <jasen@xnet.co.nz> writes:
> On 2010-04-19, Mario Splivalo <mario.splivalo@megafon.hr> wrote:
>> The 'proper' way to do this (as suggested by earlier posts on this
>> mailing list) is to use partial UNIQUE indexes, but I have problem with
>> that too: indexes are not part of DDL (no matter that primary key
>> constraints and/or unique constraints use indexes to employ those
>> constraints), and as far as I know there is no 'partial unique
>> constraint' in SQL?
> huh?
I think what Mario is actually complaining about is that partial unique
indexes are not part of the SQL standard, and he wants a solution that
at least gives the illusion that it might be portable to some other
RDBMS in the future.
Unfortunately, an illusion is all it would be. Even presuming that the
other DBMS lets you run plpgsql-equivalent functions in CHECK
constraints, the whole approach is broken by concurrency considerations.
If you have two transactions simultaneously inserting rows that would be
valid given the prior state of the table, but it's *not* valid for them
both to be present, then a CHECK or trigger-based constraint is going to
fail, because neither transaction will see the other's uncommitted row.
At least that's how it works in Postgres. In some other DBMS it might
work differently, but you're right back up against the fact that your
solution is not portable.
Unique constraints (partial or otherwise) deal with the race-condition
problem by doing low-level things that aren't exposed at the SQL level.
So there's simply no way to get the equivalent behavior in pure standard
SQL.
>> And, wouldn't it be better to have CHECK constraints check the data
>> AFTER data-modification?
> no.
Indeed. The race condition is still there. CHECK is meant to handle
constraints on a row's value *in isolation*. If you try to use it to
enforce cross-row conditions, the project will certainly end badly.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, jasen@xnet.co.nz, mario.splivalo@megafon.hr
Subject: Re: Re: CHECK constraints with plpgsql functions - check 'fires' BEFORE data modification?
In-Reply-To: <13340.1271686859@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox