From: Mario Splivalo <mario.splivalo@megafon.hr>
To: pgsql-sql@postgresql.org
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jasen Betts <jasen@xnet.co.nz>
Subject: Re: Re: CHECK constraints with plpgsql functions - check 'fires' BEFORE data modification?
Date: Tue, 20 Apr 2010 09:02:21 +0200
Message-ID: <4BCD517D.8090107@megafon.hr> (raw)
In-Reply-To: <13340.1271686859@sss.pgh.pa.us>
References: <4BCC272C.3020505@megafon.hr>
<hqhcgl$bij$1@reversiblemaps.ath.cx>
<13340.1271686859@sss.pgh.pa.us>
Tom Lane wrote:
>
> 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.
Correct. As far as I can see there is no partial unique constraints
defined within the standard, and there are check constraints, so...
Although, especially after this:
> 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.
Thank you for the clarification, it's easy to understand now why using
check constraints is a bad idea for the purpose I wanted to use them.
It's also easy to see why 'proper' way is using partial unique indexes.
Still, one has to wonder why there are no partial unique constraints
defined in SQL standard :)
Mario
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: mario.splivalo@megafon.hr, tgl@sss.pgh.pa.us, jasen@xnet.co.nz
Subject: Re: Re: CHECK constraints with plpgsql functions - check 'fires' BEFORE data modification?
In-Reply-To: <4BCD517D.8090107@megafon.hr>
* 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