"insert [...] on conflict" hangs on conflict on an unmentioned gist index
Hi,
The last insert of the following statements causes a busy loop that does not complete:
create table gist_test (
bar text,
foo text,
constraint gist_test_bar_unique unique (bar),
constraint gist_test_foo_gist_unique exclude using gist (foo with =)
);
insert into gist_test (bar, foo) values ('bar', 'foo');
insert into gist_test (bar, foo) values ('baz', 'foo’) on conflict on constraint gist_test_bar_unique do nothing;
Expected behaviour is a conflict on the gist_test_foo_gist_unique -index.
The issue is present at least in the following PostgreSQL versions:
PostgreSQL 9.5.3 on x86_64-apple-darwin15.4.0, compiled by Apple LLVM version 7.3.0 (clang-703.0.31), 64-bit
PostgreSQL 9.5.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit
Best regards,
- Heikki Rauhala.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Heikki Rauhala <heikki.rauhala@reaktor.fi> writes:
The last insert of the following statements causes a busy loop that does not complete:
create table gist_test (
bar text,
foo text,
constraint gist_test_bar_unique unique (bar),
constraint gist_test_foo_gist_unique exclude using gist (foo with =)
);
insert into gist_test (bar, foo) values ('bar', 'foo');
insert into gist_test (bar, foo) values ('baz', 'foo’) on conflict on constraint gist_test_bar_unique do nothing;
It looks to me like the ON CONFLICT code has exactly zero chance of
working with non-btree (or more generally, non-amcanunique) indexes.
Was this case ever considered during development?
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
I wrote:
It looks to me like the ON CONFLICT code has exactly zero chance of
working with non-btree (or more generally, non-amcanunique) indexes.
Nah, I take that back --- I was expecting this to be handled somewhere
else than it is. It looks like the actual cause is just sloppy
handling of noDupErr in ExecInsertIndexTuples; the attached fixes it
for me.
regards, tom lane
Attachments:
fix-non-arbiter-exclusion-index.patchtext/x-diff; charset=us-ascii; name=fix-non-arbiter-exclusion-index.patchDownload+2-2
On Mon, Jul 4, 2016 at 9:38 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Nah, I take that back --- I was expecting this to be handled somewhere
else than it is. It looks like the actual cause is just sloppy
handling of noDupErr in ExecInsertIndexTuples; the attached fixes it
for me.
I agree that that's all this was; it's uncommon to specify an
exclusion constraint by name with DO NOTHING, so we didn't catch this
until now.
Thanks for looking into this.
--
Peter Geoghegan
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On 4.7.2016, at 20:35, Peter Geoghegan <pg@heroku.com> wrote:
On Mon, Jul 4, 2016 at 9:38 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Nah, I take that back --- I was expecting this to be handled somewhere
else than it is. It looks like the actual cause is just sloppy
handling of noDupErr in ExecInsertIndexTuples; the attached fixes it
for me.I agree that that's all this was; it's uncommon to specify an
exclusion constraint by name with DO NOTHING, so we didn't catch this
until now.
Thanks for the quick response! I tested the patch with the more complex query that initially brought this up, which is more like:
insert into gist_test (bar, foo) values ('baz', 'foo')
on conflict on constraint gist_test_bar_unique
do update set foo = excluded.foo;
The patch works as expected also for that case.
Best regards,
- Heikki.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs