Update violating constraint

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. +