Why the ERROR: duplicate key violates unique constraint "master_pkey" is raised? - Is this a Bug?

As per as I think this is not bug,

in first case

when u update the first row
In first case:

M1= 1+1 =2 conflict with the second row.

in second case:

M1=3+1=4 no conflict with second row

I hope this will help.
With reagrds
Ashish

----- Original Message -----
From: Anoo Sivadasan Pillai
To: pgsql-general@postgresql.org
Cc: Anoo Sivadasan Pillai
Sent: Monday, September 24, 2007 12:50 PM
Subject: [GENERAL] Why the ERROR: duplicate key violates unique constraint "master_pkey" is raised? - Is this a Bug?

I am using "PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)" on Windows 2003 server

While I am trying to update a prmary key It is failing with the following message "ERROR: duplicate key violates unique constraint "master_pkey" "

Can anybody explain why this happens so? Sending the script that I tried.

CREATE TABLE master ( m1 INT primary key , m2 VARCHAR(100)) ;

INSERT INTO master VALUES ( 1, 'm1' ) ;

INSERT INTO master VALUES ( 2, 'm2' ) ;

UPDATE master SET m1 = m1 + 1;

Update fails with the message - ERROR: duplicate key violates unique constraint "master_pkey"

If I insert data in the reverse order it is making no problem. Is this a Bug ?

I tried ,

TRUNCATE TABLE master;

INSERT INTO master VALUES ( 3, 'm3' ) ;

INSERT INTO master VALUES ( 2, 'm2' ) ;

UPDATE master SET m1 = m1 + 1;

It works perfectly.

Anoo S

Visit our Website at www.rmesi.co.in

This message is confidential. You should not copy it or disclose its contents to anyone. You may use and apply the information for the intended purpose only. Internet communications are not secure; therefore, RMESI does not accept legal responsibility for the contents of this message. Any views or opinions presented are those of the author only and not of RMESI. If this email has come to you in error, please delete it, along with any attachments. Please note that RMESI may intercept incoming and outgoing email communications.

Freedom of Information Act 2000
This email and any attachments may contain confidential information belonging to RMESI. Where the email and any attachments do contain information of a confidential nature, including without limitation information relating to trade secrets, special terms or prices these shall be deemed for the purpose of the Freedom of Information Act 2000 as information provided in confidence by RMESI and the disclosure of which would be prejudicial to RMESI's commercial interests.

This email has been scanned for viruses by Trend ScanMail.

Hi Ashish,

Do you mean to say that UPDATE command will work depending on the order
in which data is inserted?

I mean,

The following command will not work

CREATE TABLE master ( m1 INT primary key , m2 VARCHAR(100)) ;

INSERT INTO master VALUES ( 1, 'm1' ) ;

INSERT INTO master VALUES ( 2, 'm2' ) ;

UPDATE master SET m1 = m1 + 1;

But the following command will work

CREATE TABLE master ( m1 INT primary key , m2 VARCHAR(100)) ;

INSERT INTO master VALUES ( 2, 'm2' ) ;

INSERT INTO master VALUES ( 1, 'm1' ) ;

UPDATE master SET m1 = m1 + 1;

Even though both are inserting same data ?

Anoo S

From: Ashish Karalkar [mailto:ashish.karalkar@info-spectrum.com]
Sent: 24 September 2007 13:20
To: Anoo Sivadasan Pillai; pgsql-general@postgresql.org
Cc: Anoo Sivadasan Pillai
Subject: Re: [GENERAL] Why the ERROR: duplicate key violates unique
constraint "master_pkey" is raised? - Is this a Bug?

As per as I think this is not bug,

in first case

when u update the first row

In first case:

M1= 1+1 =2 conflict with the second row.

in second case:

M1=3+1=4 no conflict with second row

I hope this will help.

With reagrds

Ashish

----- Original Message -----

From: Anoo Sivadasan Pillai <mailto:aspillai@in.rm.com>

To: pgsql-general@postgresql.org

Cc: Anoo Sivadasan Pillai <mailto:aspillai@in.rm.com>

Sent: Monday, September 24, 2007 12:50 PM

Subject: [GENERAL] Why the ERROR: duplicate key violates unique
constraint "master_pkey" is raised? - Is this a Bug?

I am using "PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC
gcc.exe (GCC) 3.4.2 (mingw-special)" on Windows 2003 server

While I am trying to update a prmary key It is failing with the
following message "ERROR: duplicate key violates unique constraint
"master_pkey" "

Can anybody explain why this happens so? Sending the script
that I tried.

CREATE TABLE master ( m1 INT primary key , m2 VARCHAR(100)) ;

INSERT INTO master VALUES ( 1, 'm1' ) ;

INSERT INTO master VALUES ( 2, 'm2' ) ;

UPDATE master SET m1 = m1 + 1;

Update fails with the message - ERROR: duplicate key violates
unique constraint "master_pkey"

If I insert data in the reverse order it is making no problem.
Is this a Bug ?

I tried ,

TRUNCATE TABLE master;

INSERT INTO master VALUES ( 3, 'm3' ) ;

INSERT INTO master VALUES ( 2, 'm2' ) ;

UPDATE master SET m1 = m1 + 1;

It works perfectly.

Anoo S

Visit our Website at www.rmesi.co.in

This message is confidential. You should not copy it or disclose
its contents to anyone. You may use and apply the information for the
intended purpose only. Internet communications are not secure;
therefore, RMESI does not accept legal responsibility for the contents
of this message. Any views or opinions presented are those of the author
only and not of RMESI. If this email has come to you in error, please
delete it, along with any attachments. Please note that RMESI may
intercept incoming and outgoing email communications.

Freedom of Information Act 2000
This email and any attachments may contain confidential
information belonging to RMESI. Where the email and any attachments do
contain information of a confidential nature, including without
limitation information relating to trade secrets, special terms or
prices these shall be deemed for the purpose of the Freedom of
Information Act 2000 as information provided in confidence by RMESI and
the disclosure of which would be prejudicial to RMESI's commercial
interests.

This email has been scanned for viruses by Trend ScanMail.
Visit our Website at http://www.rmesi.co.in

This message is confidential. You should not copy it or disclose its contents to anyone. You may use and apply the information for the intended purpose only. Internet communications are not secure; therefore, RMESI does not accept legal responsibility for the contents of this message. Any views or opinions presented are those of the author only and not of RMESI. If this email has come to you in error, please delete it, along with any attachments. Please note that RMESI may intercept incoming and outgoing email communications.

Freedom of Information Act 2000
This email and any attachments may contain confidential information belonging to RMESI. Where the email and any attachments do contain information of a confidential nature, including without limitation information relating to trade secrets, special terms or prices these shall be deemed for the purpose of the Freedom of Information Act 2000 as information provided in confidence by RMESI and the disclosure of which would be prejudicial to RMESI's commercial interests.

This email has been scanned for viruses by Trend ScanMail.

Probably you are using a sequence, and if so you need to update the value of sequence prior to update:

SELECT setval('master_m1_seq',((SELECT id_m1 FROM master ORDER BY 1 DESC LIMIT 1)+1));
----- Original Message -----
From: Anoo Sivadasan Pillai
To: pgsql-general@postgresql.org
Cc: Anoo Sivadasan Pillai
Sent: Monday, September 24, 2007 3:20 AM
Subject: [GENERAL] Why the ERROR: duplicate key violates unique constraint "master_pkey" is raised? - Is this a Bug?

I am using "PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)" on Windows 2003 server

While I am trying to update a prmary key It is failing with the following message "ERROR: duplicate key violates unique constraint "master_pkey" "

Can anybody explain why this happens so? Sending the script that I tried.

CREATE TABLE master ( m1 INT primary key , m2 VARCHAR(100)) ;

INSERT INTO master VALUES ( 1, 'm1' ) ;

INSERT INTO master VALUES ( 2, 'm2' ) ;

UPDATE master SET m1 = m1 + 1;

Update fails with the message - ERROR: duplicate key violates unique constraint "master_pkey"

If I insert data in the reverse order it is making no problem. Is this a Bug ?

I tried ,

TRUNCATE TABLE master;

INSERT INTO master VALUES ( 3, 'm3' ) ;

INSERT INTO master VALUES ( 2, 'm2' ) ;

UPDATE master SET m1 = m1 + 1;

It works perfectly.

Anoo S

Visit our Website at www.rmesi.co.in

This message is confidential. You should not copy it or disclose its contents to anyone. You may use and apply the information for the intended purpose only. Internet communications are not secure; therefore, RMESI does not accept legal responsibility for the contents of this message. Any views or opinions presented are those of the author only and not of RMESI. If this email has come to you in error, please delete it, along with any attachments. Please note that RMESI may intercept incoming and outgoing email communications.

Freedom of Information Act 2000
This email and any attachments may contain confidential information belonging to RMESI. Where the email and any attachments do contain information of a confidential nature, including without limitation information relating to trade secrets, special terms or prices these shall be deemed for the purpose of the Freedom of Information Act 2000 as information provided in confidence by RMESI and the disclosure of which would be prejudicial to RMESI's commercial interests.

This email has been scanned for viruses by Trend ScanMail.

------------------------------------------------------------------------------

No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.488 / Virus Database: 269.13.30/1025 - Release Date: 23/09/2007 13:53

On Mon, 2007-09-24 at 12:50 +0530, Anoo Sivadasan Pillai wrote:

CREATE TABLE master ( m1 INT primary key , m2 VARCHAR(100)) ;
INSERT INTO master VALUES ( 1, 'm1' ) ;
INSERT INTO master VALUES ( 2, 'm2' ) ;
UPDATE master SET m1 = m1 + 1;
Update fails with the message - ERROR: duplicate key violates unique
constraint "master_pkey"

Primary key constraints are not deferred and are not deferrable (try:
\x, then select * from pg_constraint where conname~'master_pkey'). This
means that the constraint is checked immediately for each row updated.
Success will depend on the order in which postgresql decides to visit
rows: if it visits all rows in descending order, you'll be fine, but
that's unlikely and uncontrollable (AFAIK).

Here's a sketch of an easy workaround. You might have to modify it for
your particular range of m1.

begin;
update master set m1=-m1;
update master set m1=-m1+1;
commit;

You could just as easily add N to m1, then subtract (N-1) from m1.
You'll need N>max(m1).

Good luck,
Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Hi,

I am not using any sequences, The following batch can reproduce the
behaviour.

CREATE TABLE master ( m1 INT primary key , m2 int unique ) ;

INSERT INTO master VALUES ( 1, 1 ) ;

INSERT INTO master VALUES ( 2, 2) ;

UPDATE master SET m2 = m2 + 1;

With Cheers,

Anoo S

From: Ardian Xharra [mailto:axharra@boxxo.info]
Sent: 25 September 2007 00:38
To: Anoo Sivadasan Pillai; pgsql-general@postgresql.org
Cc: Anoo Sivadasan Pillai
Subject: Re: [GENERAL] Why the ERROR: duplicate key violates unique
constraint "master_pkey" is raised? - Is this a Bug?

Probably you are using a sequence, and if so you need to update the
value of sequence prior to update:

SELECT setval('master_m1_seq',((SELECT id_m1 FROM master ORDER BY 1 DESC
LIMIT 1)+1));

----- Original Message -----

From: Anoo Sivadasan Pillai <mailto:aspillai@in.rm.com>

To: pgsql-general@postgresql.org

Cc: Anoo Sivadasan Pillai <mailto:aspillai@in.rm.com>

Sent: Monday, September 24, 2007 3:20 AM

Subject: [GENERAL] Why the ERROR: duplicate key violates unique
constraint "master_pkey" is raised? - Is this a Bug?

I am using "PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC
gcc.exe (GCC) 3.4.2 (mingw-special)" on Windows 2003 server

While I am trying to update a prmary key It is failing with the
following message "ERROR: duplicate key violates unique constraint
"master_pkey" "

Can anybody explain why this happens so? Sending the script
that I tried.

CREATE TABLE master ( m1 INT primary key , m2 VARCHAR(100)) ;

INSERT INTO master VALUES ( 1, 'm1' ) ;

INSERT INTO master VALUES ( 2, 'm2' ) ;

UPDATE master SET m1 = m1 + 1;

Update fails with the message - ERROR: duplicate key violates
unique constraint "master_pkey"

If I insert data in the reverse order it is making no problem.
Is this a Bug ?

I tried ,

TRUNCATE TABLE master;

INSERT INTO master VALUES ( 3, 'm3' ) ;

INSERT INTO master VALUES ( 2, 'm2' ) ;

UPDATE master SET m1 = m1 + 1;

It works perfectly.

Anoo S
Visit our Website at http://www.rmesi.co.in

This message is confidential. You should not copy it or disclose its contents to anyone. You may use and apply the information for the intended purpose only. Internet communications are not secure; therefore, RMESI does not accept legal responsibility for the contents of this message. Any views or opinions presented are those of the author only and not of RMESI. If this email has come to you in error, please delete it, along with any attachments. Please note that RMESI may intercept incoming and outgoing email communications.

Freedom of Information Act 2000
This email and any attachments may contain confidential information belonging to RMESI. Where the email and any attachments do contain information of a confidential nature, including without limitation information relating to trade secrets, special terms or prices these shall be deemed for the purpose of the Freedom of Information Act 2000 as information provided in confidence by RMESI and the disclosure of which would be prejudicial to RMESI's commercial interests.

This email has been scanned for viruses by Trend ScanMail.

Hi,

On further testing I found the same behaviour in Unique keys too, The following batch can reproduce the behaviour.

CREATE TABLE master ( m1 INT primary key , m2 int unique ) ;

INSERT INTO master VALUES ( 1, 1 ) ;

INSERT INTO master VALUES ( 2, 2) ;

UPDATE master SET m2 = m2 + 1;

I agree with the statement “Success will depend on the order in which postgresql decides to visit rows: if it visits all rows in descending order, you'll be fine,”

But since you can’t control the order, it is likely that the operation will sometimes and will succeed sometimes, with the same data.. Weird I feel .

The work around given by you will work, Does it mean that whenever we are updating a unique key we have to take this precaution ?

Anoo S

From: Reece Hart [mailto:reece@harts.net]
Sent: 25 September 2007 03:28
To: Anoo Sivadasan Pillai
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Why the ERROR: duplicate key violates uniqueconstraint "master_pkey" is raised? - Is this a Bug?

On Mon, 2007-09-24 at 12:50 +0530, Anoo Sivadasan Pillai wrote:

CREATE TABLE master ( m1 INT primary key , m2 VARCHAR(100)) ;
INSERT INTO master VALUES ( 1, 'm1' ) ;
INSERT INTO master VALUES ( 2, 'm2' ) ;
UPDATE master SET m1 = m1 + 1;
Update fails with the message - ERROR: duplicate key violates unique constraint "master_pkey"

Primary key constraints are not deferred and are not deferrable (try: \x, then select * from pg_constraint where conname~'master_pkey'). This means that the constraint is checked immediately for each row updated. Success will depend on the order in which postgresql decides to visit rows: if it visits all rows in descending order, you'll be fine, but that's unlikely and uncontrollable (AFAIK).

Here's a sketch of an easy workaround. You might have to modify it for your particular range of m1.

begin;
update master set m1=-m1;
update master set m1=-m1+1;
commit;

You could just as easily add N to m1, then subtract (N-1) from m1. You'll need N>max(m1).

Good luck,
Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Visit our Website at http://www.rmesi.co.in

This message is confidential. You should not copy it or disclose its contents to anyone. You may use and apply the information for the intended purpose only. Internet communications are not secure; therefore, RMESI does not accept legal responsibility for the contents of this message. Any views or opinions presented are those of the author only and not of RMESI. If this email has come to you in error, please delete it, along with any attachments. Please note that RMESI may intercept incoming and outgoing email communications.

Freedom of Information Act 2000
This email and any attachments may contain confidential information belonging to RMESI. Where the email and any attachments do contain information of a confidential nature, including without limitation information relating to trade secrets, special terms or prices these shall be deemed for the purpose of the Freedom of Information Act 2000 as information provided in confidence by RMESI and the disclosure of which would be prejudicial to RMESI’s commercial interests.

This email has been scanned for viruses by Trend ScanMail.

On 25/09/2007, Anoo Sivadasan Pillai <aspillai@in.rm.com> wrote:

Hi,

On further testing I found the same behaviour in Unique keys too, The following batch can reproduce the behaviour.

CREATE TABLE master ( m1 INT primary key , m2 int unique ) ;

INSERT INTO master VALUES ( 1, 1 ) ;

INSERT INTO master VALUES ( 2, 2) ;

UPDATE master SET m2 = m2 + 1;

Isn't this expected behavior? When you update the m2 of the first
record, it becomes 2 and violates the unqiue constraint as the second
row already has an m2 value of 2.

I have missed the thread, but this reeks of an unusual DB design. If
your really want the updates to go in reverse order so that keys are
not violated, you can do that in your application -- SELECT the keys
you wish to update sorted in DESC order by m2, and then foreach of
them, just increment the value accordingly. If you wrap this in a
transaction, it'll be pretty fast, depending on how many rows you're
talking about (I've found great speeds on records up to 50,000 for a
live DB).

On Tue, 2007-09-25 at 10:06 +0530, Anoo Sivadasan Pillai wrote:

But since you can’t control the order, it is likely that the operation
will sometimes and will succeed sometimes, with the same data.. Weird
I feel .

Not so weird (IMO). Many databases will return rows unordered unless you
specify otherwise. Once one understands that uniqueness constraints are
evaluated immediately and that rows are visited unordered, it's not so
weird.

The work around given by you will work, Does it mean that whenever we
are updating a unique key we have to take this precaution ?

Probably. I suspect that you're using unique or primary keys to imply
some meaning that would be better stored elsewhere, and definitely NOT
in a primary key. Perhaps you should consider refactoring your design.
You're doing something wrong if you find that you need to update primary
keys routinely.

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

--- Phoenix Kiula <phoenix.kiula@gmail.com> wrote:

Isn't this expected behavior? When you update the m2 of the first
record, it becomes 2 and violates the unique constraint as the second
row already has an m2 value of 2.

Well, it is a limitation PostgreSQL. This type of update is should work without any problem
according to the SQL standard. The Problem with performing two updates is that it double the
amount of dead tuples. I guess that this would be another example where having a small fill
factor would help.

One kind of data model that depends heavily on this type of operation is the Hierarchical Nested
Set data model. Inserting/updating/deleting nodes and branches into the table requires updating
the primary key of a lot of records.

Regards,
Richard Broersma Jr.

It's normal behaviour, because after the first update it will be 2 same values for m2 and you don't want that since you have a unique constraint for that column. try this:
CREATE TABLE master ( m1 INT primary key , m2 int unique ) ;

INSERT INTO master VALUES ( 1, 1 ) ;

INSERT INTO master VALUES ( 2, 3) ;

UPDATE master SET m2 = m2+1 ;
----- Original Message -----
From: Anoo Sivadasan Pillai
To: Ardian Xharra
Cc: pgsql-general@postgresql.org
Sent: Tuesday, September 25, 2007 12:27 AM
Subject: Re: [GENERAL] Why the ERROR: duplicate key violates unique constraint "master_pkey" is raised? - Is this a Bug?

Hi,

I am not using any sequences, The following batch can reproduce the behaviour.

CREATE TABLE master ( m1 INT primary key , m2 int unique ) ;

INSERT INTO master VALUES ( 1, 1 ) ;

INSERT INTO master VALUES ( 2, 2) ;

UPDATE master SET m2 = m2 + 1;

With Cheers,

Anoo S

From: Ardian Xharra [mailto:axharra@boxxo.info]
Sent: 25 September 2007 00:38
To: Anoo Sivadasan Pillai; pgsql-general@postgresql.org
Cc: Anoo Sivadasan Pillai
Subject: Re: [GENERAL] Why the ERROR: duplicate key violates unique constraint "master_pkey" is raised? - Is this a Bug?

Probably you are using a sequence, and if so you need to update the value of sequence prior to update:

SELECT setval('master_m1_seq',((SELECT id_m1 FROM master ORDER BY 1 DESC LIMIT 1)+1));

----- Original Message -----

From: Anoo Sivadasan Pillai

To: pgsql-general@postgresql.org

Cc: Anoo Sivadasan Pillai

Sent: Monday, September 24, 2007 3:20 AM

Subject: [GENERAL] Why the ERROR: duplicate key violates unique constraint "master_pkey" is raised? - Is this a Bug?

I am using "PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)" on Windows 2003 server

While I am trying to update a prmary key It is failing with the following message "ERROR: duplicate key violates unique constraint "master_pkey" "

Can anybody explain why this happens so? Sending the script that I tried.

CREATE TABLE master ( m1 INT primary key , m2 VARCHAR(100)) ;

INSERT INTO master VALUES ( 1, 'm1' ) ;

INSERT INTO master VALUES ( 2, 'm2' ) ;

UPDATE master SET m1 = m1 + 1;

Update fails with the message - ERROR: duplicate key violates unique constraint "master_pkey"

If I insert data in the reverse order it is making no problem. Is this a Bug ?

I tried ,

TRUNCATE TABLE master;

INSERT INTO master VALUES ( 3, 'm3' ) ;

INSERT INTO master VALUES ( 2, 'm2' ) ;

UPDATE master SET m1 = m1 + 1;

It works perfectly.

Anoo S

Visit our Website at www.rmesi.co.in

This message is confidential. You should not copy it or disclose its contents to anyone. You may use and apply the information for the intended purpose only. Internet communications are not secure; therefore, RMESI does not accept legal responsibility for the contents of this message. Any views or opinions presented are those of the author only and not of RMESI. If this email has come to you in error, please delete it, along with any attachments. Please note that RMESI may intercept incoming and outgoing email communications.

Freedom of Information Act 2000
This email and any attachments may contain confidential information belonging to RMESI. Where the email and any attachments do contain information of a confidential nature, including without limitation information relating to trade secrets, special terms or prices these shall be deemed for the purpose of the Freedom of Information Act 2000 as information provided in confidence by RMESI and the disclosure of which would be prejudicial to RMESI's commercial interests.

This email has been scanned for viruses by Trend ScanMail.

------------------------------------------------------------------------------

No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.488 / Virus Database: 269.13.30/1030 - Release Date: 25/09/2007 08:02

On Mon, Sep 24, 2007 at 12:50:22PM +0530, Anoo Sivadasan Pillai wrote:

I am using "PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC
gcc.exe (GCC) 3.4.2 (mingw-special)" on Windows 2003 server

While I am trying to update a prmary key It is failing with the
following message "ERROR: duplicate key violates unique constraint
"master_pkey" "

Can anybody explain why this happens so? Sending the script that I
tried.

CREATE TABLE master ( m1 INT primary key , m2 VARCHAR(100)) ;

INSERT INTO master VALUES ( 1, 'm1' ) ;

INSERT INTO master VALUES ( 2, 'm2' ) ;

UPDATE master SET m1 = m1 + 1;

One way to do this is with Postgres's UPDATE ... FROM construct:

CREATE TABLE foo(i INTEGER PRIMARY KEY);
INSERT INTO foo(i) VALUES (1), (2), (3), (4), (5);
UPDATE foo SET i=foo.i+1
FROM (SELECT i FROM foo ORDER BY i DESC) f
WHERE f.i = foo.i;

While specific to Postgres, this technique avoids a lot of messing
around with boundary conditions :)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate

Ardian Xharra skrev:

*From:* Anoo Sivadasan Pillai <mailto:aspillai@in.rm.com>

I am not using any sequences, The following batch can reproduce the
behaviour.
CREATE TABLE master ( m1 INT primary key , m2 int unique ) ;
INSERT INTO master VALUES ( 1, 1 ) ;
INSERT INTO master VALUES ( 2, 2) ;
UPDATE master SET m2 = m2 + 1;

It's normal behaviour, because after the first update it will be 2 same
values for m2 and you don't want that since you have a unique constraint
for that column.

Please note: This is a bug in Postgresql, not "normal behaviour". From a
conceptual perspective, there is no "after the first update" - the
statement is supposed to be atomic.

Unfortunately, the problem is waiting for someone to get a great idea:

http://svr5.postgresql.org/pgsql-bugs/2007-02/msg00075.php

If you can't wait, you are probably better off working around the
problem. Standard solution is to do:

UPDATE master SET m2 = -m2;
UPDATE master SET m2 = -m2+1;

or something similar.

Nis

Nis Jørgensen wrote:

If you can't wait, you are probably better off working around the
problem. Standard solution is to do:

UPDATE master SET m2 = -m2;
UPDATE master SET m2 = -m2+1;

or something similar.

Would something like

UPDATE master set m2 = master2.m2
FROM (
SELECT m2 +1
FROM master m
WHERE m.master_id = master.master_id
ORDER BY m2 DESC
) master2

work? I think it might be faster (and possibly cause less index bloat)
than doing two consequent updates.

Haven't tested this though...

--
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 skrev:

Nis J�rgensen wrote:

If you can't wait, you are probably better off working around the
problem. Standard solution is to do:

UPDATE master SET m2 = -m2;
UPDATE master SET m2 = -m2+1;

or something similar.

Would something like

UPDATE master set m2 = master2.m2
FROM (
SELECT m2 +1
FROM master m
WHERE m.master_id = master.master_id
ORDER BY m2 DESC
) master2

work? I think it might be faster (and possibly cause less index bloat)
than doing two consequent updates.

I don't understand your query. I don't think you can use a correlated
subquery in that way.

Anyway, tricks like these might work. They might stop working without
warning, if the plan changes. Relying on unspecified behavior is a
recipe for trouble.

Nis

Nis Jørgensen wrote:

Alban Hertroys skrev:

Would something like

UPDATE master set m2 = master2.m2
FROM (
SELECT m2 +1
FROM master m
WHERE m.master_id = master.master_id
ORDER BY m2 DESC
) master2

work? I think it might be faster (and possibly cause less index bloat)
than doing two consequent updates.

I don't understand your query. I don't think you can use a correlated
subquery in that way.

Hmm indeed, it complains something vague: "ERROR: subquery in FROM may
not refer to other relations of same query level". Not sure why?

Effectively it orders the updates descending, so that the new value of
m2 can never be updated to an already existing value, because that has
been updated previously.

The WHERE condition makes the query look a bit more complex than it
actually is, but is necessary of course.

Anyway, tricks like these might work. They might stop working without
warning, if the plan changes. Relying on unspecified behavior is a
recipe for trouble.

If I specifically ask for an ordering, I don't think the planner should
change or ignore that ordering. So I'm not relying on unspecified behaviour.

--
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 skrev:

Nis J�rgensen wrote:

Alban Hertroys skrev:

Would something like

UPDATE master set m2 = master2.m2
FROM (
SELECT m2 +1
FROM master m
WHERE m.master_id = master.master_id
ORDER BY m2 DESC
) master2

work? I think it might be faster (and possibly cause less index bloat)
than doing two consequent updates.

I don't understand your query. I don't think you can use a correlated
subquery in that way.

Hmm indeed, it complains something vague: "ERROR: subquery in FROM may
not refer to other relations of same query level". Not sure why?

As I said, I don't understand what you think it does. What you are doing
is similar to writing

SELECT m2
FROM master, (
SELECT m2
FROM master m
WHERE m.master_id = master.master_id
)

Which doesn' make any sense either.

You probably want

UPDATE master set m2 = master2.m2
FROM (
SELECT m2 +1
FROM master m
ORDER BY m2 DESC
) master2
WHERE master2.master_id = master.master_id

Effectively it orders the updates descending, so that the new value of
m2 can never be updated to an already existing value, because that has
been updated previously.

The WHERE condition makes the query look a bit more complex than it
actually is, but is necessary of course.

Anyway, tricks like these might work. They might stop working without
warning, if the plan changes. Relying on unspecified behavior is a
recipe for trouble.

If I specifically ask for an ordering, I don't think the planner should
change or ignore that ordering. So I'm not relying on unspecified behaviour.

According to the SQL spec, all the updates happen at the same time. Thus
any order this happens in is an implementation detail.

The only places where an "ORDER BY" clause is guaranteed to yield
specific results are those which are documented. - off the top of my
head, I can think of "outputting rows to the client", "LIMIT" and
"DISTINCT ON".

The fact that you stick an "ORDER BY" into a subquery guarantees
nothing. The planner might even see that it has no effect (according to
the spec) and ignore it. For instance this

SELECT *
FROM (SELECT *
FROM mytable
ORDER BY id
)
WHERE some_criteria

is not guaranteed to return an ordered result set. Thus the planner can
ignore the ORDER BY (but might not do so).

Nis

I had to manipulate the headers a bit, as I hadn't noticed the message
that reached me first was from the newsgroup instead of the ML.

Nis Jørgensen wrote:

Alban Hertroys skrev:

As I said, I don't understand what you think it does. What you are doing
is similar to writing

SELECT m2
FROM master, (
SELECT m2
FROM master m
WHERE m.master_id = master.master_id
)

Which doesn' make any sense either.

You probably want

UPDATE master set m2 = master2.m2
FROM (
SELECT m2 +1
FROM master m
ORDER BY m2 DESC
) master2
WHERE master2.master_id = master.master_id

So you do understand.

As I mentioned earlier, I didn't test that query. The extra alias
bothered me as unnecessary, and now I see why - I put the where clause
at the wrong place.

According to the SQL spec, all the updates happen at the same time. Thus
any order this happens in is an implementation detail.

According to the SQL spec the original update statement should have worked.

But it doesn't, so the updates _don't_ all happen at the same time. That
means there is an order in which they occur, and that order is likely to
be manipulatable.

The fact that you stick an "ORDER BY" into a subquery guarantees
nothing. The planner might even see that it has no effect (according to
the spec) and ignore it. For instance this

SELECT *
FROM (SELECT *
FROM mytable
ORDER BY id
)
WHERE some_criteria

is not guaranteed to return an ordered result set. Thus the planner can
ignore the ORDER BY (but might not do so).

You are probably right that there's no way to guarantee that ordering,
but the method I suggested works in at least the version of Postgres I
have available (8.1.8), and they'll also work in database versions that
update atomically.

There _might_ be a small window of future PG versions where the planner
outsmarts this "trick" while it doesn't yet update atomically, but I
believe that to be rather unlikely.

I expect the priorities of the developers to be on atomic updates as
opposed to filtering out explicitly requested but unnecessary ordering.
The latter may be in use by many to massage the planner into picking a
different plan (even though it's not the right way to fix a bad plan of
course).

Here's some proof:

CREATE TABLE update_test (
update_id serial NOT NULL PRIMARY KEY,
num integer NOT NULL UNIQUE
);

INSERT INTO update_test (num) SELECT * FROM generate_series(5, 15);

-- fails
UPDATE update_test SET num = u2.num
FROM (
SELECT update_id, num +1 AS num
FROM update_test
) u2
WHERE update_test.update_id = u2.update_id;

-- succeeds
UPDATE update_test SET num = u2.num
FROM (
SELECT update_id, num +1 AS num
FROM update_test
ORDER BY num DESC
) u2
WHERE update_test.update_id = u2.update_id;

--
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 skrev:

Nis J�rgensen wrote:

Alban Hertroys skrev:

As I said, I don't understand what you think it does. What you are doing
is similar to writing

SELECT m2
FROM master, (
SELECT m2
FROM master m
WHERE m.master_id = master.master_id
)

Which doesn' make any sense either.

You probably want

UPDATE master set m2 = master2.m2
FROM (
SELECT m2 +1
FROM master m
ORDER BY m2 DESC
) master2
WHERE master2.master_id = master.master_id

So you do understand.

As I mentioned earlier, I didn't test that query. The extra alias
bothered me as unnecessary, and now I see why - I put the where clause
at the wrong place.

According to the SQL spec, all the updates happen at the same time. Thus
any order this happens in is an implementation detail.

According to the SQL spec the original update statement should have worked.

But it doesn't, so the updates _don't_ all happen at the same time. That
means there is an order in which they occur, and that order is likely to
be manipulatable.

Yes. No dispute here.

You are probably right that there's no way to guarantee that ordering,
but the method I suggested works in at least the version of Postgres I
have available (8.1.8), and they'll also work in database versions that
update atomically.

It works right now, for the current contents of the table. It might not
work tomorrow, when the planner chooses a different plan.

As an example, I just tried disabling seqscans. After doing this, the
update fails. The plans given for the two cases are estimated to 87 and
97 units, respectively. Do you really want to bet your money on this
plan staying ahead?

Nis