From: | Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> |
---|---|
To: | maxim(dot)boguk(at)gmail(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
Subject: | Re: BUG #15642: UPDATE statements that change a partition key and FDW partitions problem. |
Date: | 2019-02-20 02:30:25 |
Message-ID: | 5C6CBBC1.1050607@lab.ntt.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
(2019/02/19 23:13), PG Bug reporting form wrote:
> The following bug has been logged on the website:
> Update statement which changes value of partition key doesn't doesn't work
> with FDW partitions.
>
> Test script:
> \c postgres
> drop database IF EXISTS test;
> drop database IF EXISTS test0;
> drop database IF EXISTS test1;
> create database test;
> create database test0;
> create database test1;
> \c test0
> create table t (id integer not null, constraint id_check check (id<0));
> \c test1
> create table t (id integer not null, constraint id_check check (id>=0));
> \c test
> create extension postgres_fdw;
> create server if not exists test0 FOREIGN DATA WRAPPER postgres_fdw OPTIONS
> (dbname 'test0');
> create server if not exists test1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS
> (dbname 'test1');
> CREATE USER MAPPING FOR public SERVER test0 OPTIONS (user 'postgres');
> CREATE USER MAPPING FOR public SERVER test1 OPTIONS (user 'postgres');
> CREATE FOREIGN TABLE t0 (id integer not null, constraint id_check check
> (id<0) ) SERVER test0 OPTIONS (table_name 't');
> CREATE FOREIGN TABLE t1 (id integer not null, constraint id_check check
> (id>=0)) SERVER test1 OPTIONS (table_name 't');
> create table t (id integer not null) partition by range (id);
> alter table t ATTACH PARTITION t0 for values from (MINVALUE) to (0);
> alter table t ATTACH PARTITION t1 for values from (0) to (MAXVALUE);
> insert into t (id) values (-1);
> update t set id=2 where id=-1;
>
> Expected: row in t1 partition.
> Reality:
> test=# update t set id=2 where id=-1;
> ERROR: new row for relation "t" violates check constraint "id_check"
> DETAIL: Failing row contains (2).
> CONTEXT: remote SQL command: UPDATE public.t SET id = 2 WHERE ((id =
> (-1)))
>
> I don't know is this case supposed to work or not, and I can't find any
> related notes in documentation.
Unfortunately, this is not supported as documented in the notes section
of the UPDATE reference page [1]:
"Currently, rows cannot be moved from a partition that is a foreign
table to some other partition, but they can be moved into a foreign
table if the foreign data wrapper supports it."
Best regards,
Etsuro Fujita
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2019-02-20 03:50:11 | Re: BUG #15638: pg_basebackup with --wal-method=stream incorrectly generates WAL segment created during backup |
Previous Message | Thomas Munro | 2019-02-20 02:21:05 | Re: BUG #15641: Autoprewarm worker fails to start on Windows with huge pages in use Old PostgreSQL community/pgsql-bugs x |