Constraint problem
Josh Berkus
I don't seem to be able to create a UNIQUE constraint as DEFERRABLE. Is this
not supported?
--
-Josh Berkus
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
Josh Berkus
Folks,
> I don't seem to be able to create a UNIQUE constraint as DEFERRABLE. Is
this
> not supported?
Never mind. I found my way around this.
--
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology jo...@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
Stephan Szabo
> Folks,
>
> I don't seem to be able to create a UNIQUE constraint as DEFERRABLE. Is this
> not supported?
Not currently.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Josh Berkus
Stephan,
> > I don't seem to be able to create a UNIQUE constraint as DEFERRABLE. Is
this
> > not supported?
>
> Not currently.
Hmm ... my workaround didn't work. I need to impose a unique constraint on
an INT column in a table, but make sure that the constraint isn't checked
until the end of an UPDATE statement, so that I can swap values when I need
to.
How can I do this?
--
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology jo...@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majo...@postgresql.org so that your
message can get through to the mailing list cleanly
Stephan Szabo
>
> Stephan,
> > > I don't seem to be able to create a UNIQUE constraint as DEFERRABLE. Is
> this
> > > not supported?
> >
> > Not currently.
>
> Hmm ... my workaround didn't work. I need to impose a unique constraint on
> an INT column in a table, but make sure that the constraint isn't checked
> until the end of an UPDATE statement, so that I can swap values when I need
> to.
>
> How can I do this?
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.
Josh Berkus
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?
--
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology jo...@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
Stephan Szabo
>
> 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.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majo...@postgresql.org)
Christopher Kings-Lynne
update foo set a = -1;
update foo set b = old a value;
update foo set a = old b value;
Sort of thing?
Chris
> -----Original Message-----
> From: pgsql-s...@postgresql.org
> [mailto:pgsql-s...@postgresql.org]On Behalf Of Josh Berkus
> Sent: Friday, 17 May 2002 3:30 AM
> To: Stephan Szabo
> Cc: pgsq...@postgresql.org
> Subject: Re: [SQL] Constraint problem
>
>
>
> Stephan,
> > > I don't seem to be able to create a UNIQUE constraint as
> DEFERRABLE. Is
> this
> > > not supported?
> >
> > Not currently.
>
> Hmm ... my workaround didn't work. I need to impose a unique
> constraint on
> an INT column in a table, but make sure that the constraint isn't checked
> until the end of an UPDATE statement, so that I can swap values
> when I need
> to.
>
> How can I do this?
>
> --
> -Josh Berkus
>
> ______AGLIO DATABASE SOLUTIONS___________________________
> Josh Berkus
> Complete information technology jo...@agliodbs.com
> and data management solutions (415) 565-7293
> for law firms, small businesses fax 621-2533
> and non-profit organizations. San Francisco
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majo...@postgresql.org so that your
> message can get through to the mailing list cleanly
>
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majo...@postgresql.org
Josh Berkus
> 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.
Thanks. I'll give your solution a try and see what the performance hit
is.
Is the Unique Constraint issue on the To-Do list?
Chris: The "holding value" solution won't work for me. I'm usually
changing dozens of values at a time, and they need to stay in the same
sequence while blocks of them move on the scale.
FYI, I'm implementing Joe Celko's "Linear Nested Set Model" of tree
structures.
-Josh Berkus
Manfred Koizar
Berkus") wrote:
>FYI, I'm implementing Joe Celko's "Linear Nested Set Model" of tree
>structures.
UPDATE t SET n=n+1 WHERE ... ?
I just managed to do
BEGIN;
UPDATE t SET n=-n;
UPDATE t SET n=-n+1;
COMMIT;
in my tiny test database. This assumes negative n are not used in
normal processing. And I don't know whether it works by design or by
chance :-)
Servus
Manfred
Stephan Szabo
> Stephan,
>
> > 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.
>
> Thanks. I'll give your solution a try and see what the performance hit
> is.
>
> Is the Unique Constraint issue on the To-Do list?
I'm not sure. I know it's been mentioned in the past, so probably (too
lazy to look right now).