Обсуждение: Constraint problem
Folks, I don't seem to be able to create a UNIQUE constraint as DEFERRABLE. Is this not supported? -- -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 informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
On Thu, 16 May 2002, Josh Berkus wrote: > Folks, > > I don't seem to be able to create a UNIQUE constraint as DEFERRABLE. Is this > not supported? Not currently.
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 informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
On Thu, 16 May 2002, Josh Berkus wrote: > > 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.
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 informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
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 ' DECLAREr int; BEGINSELECT INTO r count(*) FROM az where a=NEW.a;if (r>1) then RAISE EXCEPTION ''Uniqueness on az(a) violated.'';END IF;returnNEW; 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.
What about using an intermediary value such as -1; 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-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Josh Berkus > Sent: Friday, 17 May 2002 3:30 AM > To: Stephan Szabo > Cc: pgsql-sql@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 josh@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 majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
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? 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
On Thu, 16 May 2002, Josh Berkus wrote: > 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).