From: Shinoda, Noriyoshi <noriyoshi.shinoda@hpe.com>
To: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
To: Jaime Casanova <jaime.casanova@2ndquadrant.com>
Cc: Jesper Pedersen <jesper.pedersen@redhat.com>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: RE: unique indexes on partitioned tables
Date: Thu, 8 Mar 2018 14:52:41 +0000
Message-ID: <DF4PR8401MB102060EC2615EC9227CC73F7EEDF0@DF4PR8401MB1020.NAMPRD84.PROD.OUTLOOK.COM> (raw)
In-Reply-To: <7b2f668d-2a8f-5f03-3c5d-959f5b802ff1@lab.ntt.co.jp>
References: <20180219204531.tsblwxwtz332pp7e@alvherre.pgsql>
<7b2f668d-2a8f-5f03-3c5d-959f5b802ff1@lab.ntt.co.jp>
Hi.
I tried this feature with the latest snapshot. When I executed the following SQL statement, multiple primary keys were created on the partition.
Is this the intended behavior?
-- test
postgres=> CREATE TABLE part1(c1 INT PRIMARY KEY, c2 INT, c3 VARCHAR(10)) PARTITION BY RANGE(c1) ;
CREATE TABLE
postgres=> CREATE TABLE part1v1 (LIKE part1) ;
CREATE TABLE
postgres=> ALTER TABLE part1v1 ADD CONSTRAINT pk_part1v1 PRIMARY KEY (c1, c2) ;
ALTER TABLE
postgres=> ALTER TABLE part1 ATTACH PARTITION part1v1 FOR VALUES FROM (100) TO (200) ;
ALTER TABLE
postgres=> \d part1v1
Table "public.part1v1"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
c1 | integer | | not null |
c2 | integer | | not null |
c3 | character varying(10) | | |
Partition of: part1 FOR VALUES FROM (100) TO (200)
Indexes:
"part1v1_pkey" PRIMARY KEY, btree (c1)
"pk_part1v1" PRIMARY KEY, btree (c1, c2)
Regards,
Noriyoshi Shinoda
-----Original Message-----
From: Amit Langote [mailto:Langote_Amit_f8@lab.ntt.co.jp]
Sent: Tuesday, February 20, 2018 6:24 PM
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; Peter Eisentraut <peter.eisentraut@2ndquadrant.com>; Jaime Casanova <jaime.casanova@2ndquadrant.com>
Cc: Jesper Pedersen <jesper.pedersen@redhat.com>; Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: unique indexes on partitioned tables
Hi.
On 2018/02/20 5:45, Alvaro Herrera wrote:
> I pushed this now, with fixes for the last few comments there were.
I noticed with the commit that, while ON CONFLICT (conflict_target) DO UPDATE gives a less surprising error message by catching it in the parser, ON CONFLICT (conflict_target) DO NOTHING will go into the executor without the necessary code to handle the case. Example:
create table p (a int primary key, b text) partition by list (a); create table p12 partition of p for values in (1, 2); create table p3 partition of p (a unique) for values in (3);
insert into p values (1, 'a') on conflict (a) do nothing;
ERROR: unexpected failure to find arbiter index
Attached is a patch to fix that. Actually, there are two -- one that adjusts the partitioned table tests in insert_conflict.sql to have a partitioned unique index and another that fixes the code.
I suppose we'd need to apply this temporarily until we fix the ON CONFLICT
(conflict_target) case to be able to use partitioned indexes.
Thanks,
Amit
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: noriyoshi.shinoda@hpe.com, Langote_Amit_f8@lab.ntt.co.jp, alvherre@alvh.no-ip.org, peter.eisentraut@2ndquadrant.com, jaime.casanova@2ndquadrant.com, jesper.pedersen@redhat.com
Subject: RE: unique indexes on partitioned tables
In-Reply-To: <DF4PR8401MB102060EC2615EC9227CC73F7EEDF0@DF4PR8401MB1020.NAMPRD84.PROD.OUTLOOK.COM>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox