Обсуждение: BUG #14341: insert on conflict (key_column) cann't used with partition table.
BUG #14341: insert on conflict (key_column) cann't used with partition table.
От
digoal@126.com
Дата:
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDM0MQpMb2dnZWQgYnk6ICAg ICAgICAgIFpob3UgRGlnb2FsCkVtYWlsIGFkZHJlc3M6ICAgICAgZGlnb2Fs QDEyNi5jb20KUG9zdGdyZVNRTCB2ZXJzaW9uOiA5LjZyYzEKT3BlcmF0aW5n IHN5c3RlbTogICBDZW50T1MgNi54IHg2NApEZXNjcmlwdGlvbjogICAgICAg IAoKYGBgDQpwb3N0Z3Jlcz0jIFxkIHRlc3QNCiAgICAgICAgICAgICAgIFRh YmxlICJwb3N0Z3Jlcy50ZXN0Ig0KICBDb2x1bW4gIHwgICAgICAgICAgICBU eXBlICAgICAgICAgICAgIHwgTW9kaWZpZXJzIA0KLS0tLS0tLS0tLSstLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLSstLS0tLS0tLS0tLQ0KIGlkICAg ICAgIHwgaW50ZWdlciAgICAgICAgICAgICAgICAgICAgIHwgbm90IG51bGwN CiBpbmZvICAgICB8IHRleHQgICAgICAgICAgICAgICAgICAgICAgICB8IA0K IGNydF90aW1lIHwgdGltZXN0YW1wIHdpdGhvdXQgdGltZSB6b25lIHwgDQpJ bmRleGVzOg0KICAgICJ0ZXN0X3BrZXkiIFBSSU1BUlkgS0VZLCBidHJlZSAo aWQpDQpOdW1iZXIgb2YgY2hpbGQgdGFibGVzOiAxMDAgKFVzZSBcZCsgdG8g bGlzdCB0aGVtLikNCg0KcG9zdGdyZXM9IyBpbnNlcnQgaW50byB0ZXN0KGlk LGluZm8sY3J0X3RpbWUpIHZhbHVlcygxLCBtZDUocmFuZG9tKCk6OnRleHQp LApub3coKSkgb24gY29uZmxpY3QgKGlkKSBkbyB1cGRhdGUgc2V0CmluZm89 ZXhjbHVkZWQuaW5mbyxjcnRfdGltZT1leGNsdWRlZC5jcnRfdGltZTsNCkVS Uk9SOiAgWFgwMDA6IHVuZXhwZWN0ZWQgZmFpbHVyZSB0byBmaW5kIGFyYml0 ZXIgaW5kZXgNCkxPQ0FUSU9OOiAgRXhlY0NoZWNrSW5kZXhDb25zdHJhaW50 cywgZXhlY0luZGV4aW5nLmM6NTk0DQpgYGAKCg==
digoal@126.com writes:
> postgres=# \d test
> Table "postgres.test"
> Column | Type | Modifiers
> ----------+-----------------------------+-----------
> id | integer | not null
> info | text |
> crt_time | timestamp without time zone |
> Indexes:
> "test_pkey" PRIMARY KEY, btree (id)
> Number of child tables: 100 (Use \d+ to list them.)
> postgres=# insert into test(id,info,crt_time) values(1, md5(random()::text),
> now()) on conflict (id) do update set
> info=excluded.info,crt_time=excluded.crt_time;
> ERROR: XX000: unexpected failure to find arbiter index
> LOCATION: ExecCheckIndexConstraints, execIndexing.c:594
Can't reproduce that on the basis of the given information.
regards, tom lane
$ git clone https://github.com/postgrespro/pg_pathman
$ cd pg_pathman
$ export PATH=/home/digoal/pgsql9.6rc1/bin:$PATH
$ make USE_PGXS=1
$ make install USE_PGXS=1
$ vi $PGDATA/postgresql.conf
shared_preload_libraries='pg_pathman'
$ pg_ctl restart -m fast
$ psql
postgres=# create extension pg_pathman;
CREATE EXTENSION$ psql
postgres=# drop table test;
postgres=# create table test(id int primary key, info text, crt_time timestamptz);
CREATE TABLE
postgres=#
postgres=# select create_range_partitions('test'::regclass, 'id', 1, 20000000, 100, false); create_range_partitions
------------------------- 100
(1 row)
postgres=# select disable_parent('test'::regclass); disable_parent
----------------
(1 row)
reconnect
postgres=# explain select * from test where id=1; QUERY PLAN
--------------------------------------------------------------------------------- Append (cost=0.15..2.17 rows=1 width=44) -> Index Scan using test_1_pkey on test_1 (cost=0.15..2.17 rows=1 width=44) Index Cond: (id = 1)
(3 rows)insert into test(id,info,crt_time) values(:id, md5(random()::text), now()) on conflict (id) do update set info=excluded.info,crt_time=excluded.crt_time;--
公益是一辈子的事,I'm Digoal,Just Do It.
公益是一辈子的事,I'm Digoal,Just Do It.
At 2016-09-27 21:04:34, "Tom Lane" <tgl@sss.pgh.pa.us> wrote: >digoal@126.com writes: >> postgres=# \d test >> Table "postgres.test" >> Column | Type | Modifiers >> ----------+-----------------------------+----------- >> id | integer | not null >> info | text | >> crt_time | timestamp without time zone | >> Indexes: >> "test_pkey" PRIMARY KEY, btree (id) >> Number of child tables: 100 (Use \d+ to list them.) > >> postgres=# insert into test(id,info,crt_time) values(1, md5(random()::text), >> now()) on conflict (id) do update set >> info=excluded.info,crt_time=excluded.crt_time; >> ERROR: XX000: unexpected failure to find arbiter index >> LOCATION: ExecCheckIndexConstraints, execIndexing.c:594 > >Can't reproduce that on the basis of the given information. > > regards, tom lane
德哥 <digoal@126.com> writes: > At 2016-09-27 21:04:34, "Tom Lane" <tgl@sss.pgh.pa.us> wrote: >> Can't reproduce that on the basis of the given information. > $ git clone https://github.com/postgrespro/pg_pathman > ... If it requires pathman, and apparently not even the default settings for that, I'd say this is a pathman bug. It looks a bit like the partitions are getting processed despite the plan claiming they aren't. regards, tom lane