Re: SERIALIZABLE and INSERTs with multiple VALUES

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Kevin Grittner <kgrittn(at)gmail(dot)com>
Cc: Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com>, Peter Geoghegan <pg(at)bowt(dot)ie>, Vitaly Burovoy <vitaly(dot)burovoy(at)gmail(dot)com>, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>, Jason Dusek <jason(dot)dusek(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: SERIALIZABLE and INSERTs with multiple VALUES
Date: 2016-10-23 23:26:25
Message-ID: 22438.1477265185@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I've pushed a simplified (no refactoring) version of the fix proposed
by Thomas and Peter, so that we have some kind of fix in place for
tomorrow's releases. I think further improvement along the lines
suggested by Kevin can wait till later.

I noticed that the ON CONFLICT DO NOTHING and ON CONFLICT DO UPDATE cases
are now not terribly consistent about what to do with self-conflicting
insertions. The examples memorialized in commit a6c0a5b6e are

insert into selfconflict values (1,1), (1,2) on conflict do nothing;
-- succeeds, inserting the first row and ignoring the second

insert into selfconflict values (4,1), (4,2) on conflict(f1) do update set f2 = 0;
ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time
HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

I think that if we believe the first behavior is correct, then the second
behavior is a bug; what that command should do is to insert a single row
containing (4,0). However, that behavior is the same across all isolation
levels, and it was like that before today's patches, so I didn't undertake
to do anything about it right now.

regards, tom lane

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Michael Paquier 2016-10-24 04:53:46 Re: checkpoint write errors ( getting worse )
Previous Message Adrian Klaver 2016-10-23 23:18:29 Re: configure PostgreSQL with PERL: Perl version 5.8 or later is required, but this is .