Thread: Update violating constraint
Hi,
I'm trying to do an update on a table that has a unique constraint
on the field, I need to update the table by setting field = field+1
however if this does not perform the updates on the table in a proper
order (from last to first) then the update will cause a violation of the
index *during* the update even though the table would be consistent
after the update completes. So the update fails. How do I get around
this without removing the constraint?
- Naz.
On May 2, 2007, at 23:01 , Naz Gassiep wrote: > I'm trying to do an update on a table that has a unique constraint > on the field, I need to update the table by setting field = field+1 > however if this does not perform the updates on the table in a proper > order (from last to first) then the update will cause a violation > of the > index *during* the update even though the table would be consistent > after the update completes. If field's values are all positive, I generally will do it in two steps: update foo set field = -1 * (field + 1); update foo set field = -1 * field where field < 0; Another way to do it is to add and then remove a large offset: update foo set field = 100000 * (field + 1); update foo set field = field - 100000 where field > 100000; Does either of these help? Michael Glaesemann grzm seespotcode net
Michael Glaesemann wrote: > > On May 2, 2007, at 23:01 , Naz Gassiep wrote: > >> I'm trying to do an update on a table that has a unique constraint >> on the field, I need to update the table by setting field = field+1 >> however if this does not perform the updates on the table in a proper >> order (from last to first) then the update will cause a violation of the >> index *during* the update even though the table would be consistent >> after the update completes. > > If field's values are all positive, I generally will do it in two steps: > > update foo > set field = -1 * (field + 1); > update foo > set field = -1 * field > where field < 0; > > Another way to do it is to add and then remove a large offset: > > update foo > set field = 100000 * (field + 1); > update foo > set field = field - 100000 > where field > 100000; Yes, in fact I actually use option one already in the handling of sql trees, so I'm annoyed with myself for not figuring that out. I don't know why you'd ever use your second option ever, as it virtually guarantees problems at a random point in your DB's growth. Thanks muchly for that!
Naz Gassiep wrote: > Hi, > I'm trying to do an update on a table that has a unique constraint > on the field, I need to update the table by setting field = field+1 > however if this does not perform the updates on the table in a proper > order (from last to first) then the update will cause a violation of the > index *during* the update even though the table would be consistent > after the update completes. So the update fails. How do I get around > this without removing the constraint? I think you're looking for deferrable constraints; see: http://www.postgresql.org/docs/8.2/static/sql-set-constraints.html -- Alban Hertroys alban@magproductions.nl magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World //
Alban Hertroys wrote: > Naz Gassiep wrote: >> Hi, >> I'm trying to do an update on a table that has a unique constraint >> on the field, I need to update the table by setting field = field+1 > I think you're looking for deferrable constraints; see: > > http://www.postgresql.org/docs/8.2/static/sql-set-constraints.html Which won't work with unique constraints unfortunately. That's because they're implemented through a unique index. The work-around is to do: field = -field then field = -field + 1 or similar. -- Richard Huxton Archonet Ltd
On May 2, 2007, at 23:36 , Naz Gassiep wrote: > I don't > know why you'd ever use your second option ever, as it virtually > guarantees problems at a random point in your DB's growth. There may be cases where the values are not all positive so you can't use the -1 * technique, but the offset will work. It depends on the nature of your data. One size doesn't necessarily fit all. Michael Glaesemann grzm seespotcode net
> > update foo > > set field = -1 * (field + 1); > > update foo > > set field = -1 * field > > where field < 0; > > > Yes, in fact I actually use option one already in the handling of sql > trees, so I'm annoyed with myself for not figuring that out. I don't > know why you'd ever use your second option ever, as it virtually > guarantees problems at a random point in your DB's growth. If you are updating a large portion of your tree, you will probably want to throw in a vacuum in between the two updates. This should reduce the bloat caused by dead tuples in both your index and table. Regards, Richard Broersma Jr.
> If you are updating a large portion of your tree, you will probably want to throw in a vacuum in > between the two updates. This should reduce the bloat caused by dead tuples in both your index > and table. ... but that will only work if you can commit the first set of changes before you get to the end result, possibly having an inconsistent state for the duration of the vacuum... if you want all in one transaction, vacuum will not help. Cheers, Csaba.
Richard Huxton wrote: > Alban Hertroys wrote: >> Naz Gassiep wrote: >>> Hi, >>> I'm trying to do an update on a table that has a unique constraint >>> on the field, I need to update the table by setting field = field+1 > >> I think you're looking for deferrable constraints; see: >> >> http://www.postgresql.org/docs/8.2/static/sql-set-constraints.html > > Which won't work with unique constraints unfortunately. That's because > they're implemented through a unique index. I appreciate the complexities involved, but that really ought to work on a single statement. I recall seeing something along these lines on the TODO list some time ago? -- Alban Hertroys alban@magproductions.nl magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World //
Alban Hertroys wrote: > Richard Huxton wrote: > > Alban Hertroys wrote: > >> Naz Gassiep wrote: > >>> Hi, > >>> I'm trying to do an update on a table that has a unique constraint > >>> on the field, I need to update the table by setting field = field+1 > > > >> I think you're looking for deferrable constraints; see: > >> > >> http://www.postgresql.org/docs/8.2/static/sql-set-constraints.html > > > > Which won't work with unique constraints unfortunately. That's because > > they're implemented through a unique index. > > I appreciate the complexities involved, but that really ought to work on > a single statement. I recall seeing something along these lines on the > TODO list some time ago? It is still on the TODO list. If you want it to disappear from there, your best bet is implementing a fix, followed by motivating someone to do it for you. If you don't, bets are someone will do it eventually (which may be too late for your taste). -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera wrote: > Alban Hertroys wrote: > > Richard Huxton wrote: > > > Alban Hertroys wrote: > > >> Naz Gassiep wrote: > > >>> Hi, > > >>> I'm trying to do an update on a table that has a unique constraint > > >>> on the field, I need to update the table by setting field = field+1 > > > > > >> I think you're looking for deferrable constraints; see: > > >> > > >> http://www.postgresql.org/docs/8.2/static/sql-set-constraints.html > > > > > > Which won't work with unique constraints unfortunately. That's because > > > they're implemented through a unique index. > > > > I appreciate the complexities involved, but that really ought to work on > > a single statement. I recall seeing something along these lines on the > > TODO list some time ago? > > It is still on the TODO list. If you want it to disappear from there, > your best bet is implementing a fix, followed by motivating someone to > do it for you. If you don't, bets are someone will do it eventually > (which may be too late for your taste). Yes, TODO has: o Allow DEFERRABLE and end-of-statement UNIQUE constraints? This would allow UPDATE tab SET col = col + 1 to work if col has a unique index. Currently, uniqueness checks are done while the command is being executed, rather than at the end of the statement or transaction. http://people.planetpostgresql.org/greg/index.php?/archives/2006/06/10.html http://archives.postgresql.org/pgsql-hackers/2006-09/msg01458.php -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +