From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Constraint problem
Date: Thu, 16 May 2002 18:12:07 -0700 (PDT)
Message-ID: <20020516175335.N1443-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <200205161349.18499.josh@agliodbs.com>
On Thu, 16 May 2002, Josh Berkus wrote:
>
> Stephan,
>
> > I unfortunately can't think of a good workaround. AFAICT, Unique should
> > wait until after all of the updates but our implementation doesn't
> > currently do so.
> >
> > Hopefully someone will have some ideas, because the best I can think of
> > requires an additional full table scan to check uniqueness and the
> > concurrency issues would be a bear.
>
> Hmm ... I can put up with that, I think. This table doesn't get modified very
> often, and I can afford to table-level lock it during modification.
>
> However, I can't think of how I could implement a BEFORE ... FOR EACH
> STATEMENT trigger that would check for duplicates in the final result. I can
> only deal with one row at a time using the NEW record.
>
> Suggestions?
AFAIK we don't actually have statement triggers right now, anyway, but...
Actually, I guess an after trigger that just checked the new values would
work as well, right? See if there exists more than one row with the new
key value. The after trigger should fire after all the changes are done
and in the worst case you could use constraint triggers and deferment.
The hard part about this really is making it work with concurrent
modifications, and if you're willing to forgo that, it shouldn't be too
bad.
This would degenerate in the case you're modifying a large percentage of
the table, since you'd be doing one indexscan per row modified and in
general you know that you'll need to do all of them (ie there was no
violation).
As a starting point, I did something like this:
create table az(a int);
create or replace function az_uni() returns opaque as '
DECLARE
r int;
BEGIN
SELECT INTO r count(*) FROM az where a=NEW.a;
if (r>1) then
RAISE EXCEPTION ''Uniqueness on az(a) violated.'';
END IF;
return NEW;
END;
' language 'plpgsql';
create trigger az_unit after insert or update on az for each row execute
procedure az_uni();
This still relies on the user doing a lock to get around the fact that it
won't handle concurrent changes, but in really simple tests (insert the
same value twice, do something like key=key+1 where key has sequential
values) in one session it worked.
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: sszabo@megazone23.bigpanda.com, josh@agliodbs.com
Subject: Re: Constraint problem
In-Reply-To: <20020516175335.N1443-100000@megazone23.bigpanda.com>
* 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