From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | stepya(at)ukr(dot)net |
Subject: | BUG #15724: Can't create foreign table as partition |
Date: | 2019-03-29 12:55:15 |
Message-ID: | 15724-d5a58fa9472eef4f@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 15724
Logged by: Stepan Yankevych
Email address: stepya(at)ukr(dot)net
PostgreSQL version: 11.1
Operating system: CentOS Linux release 7.5.1804 (Core)
Description:
I have two DB
big_data - PG 11.1
big_data_arch - PG 11.1
On the big_data DB i have following table
CREATE TABLE msg_json
( msg_id bigint NOT NULL DEFAULT
nextval('msg_json_msg_id_seq'::regclass),
date_id integer NOT NULL,
msg json
CONSTRAINT pk_msg_json PRIMARY KEY (date_id, msg_id) ) PARTITION BY
RANGE (date_id)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
CREATE INDEX msg_json_cross_order_id_idx
ON msg_json USING btree
(get_jsn_cross_order_id(msg) COLLATE pg_catalog."default")
TABLESPACE ssd_ts;
CREATE INDEX msg_json_msg_id_idx
ON msg_json USING btree
(msg_id, date_id)
TABLESPACE ssd_ts;
-- Partitions SQL
CREATE TABLE msg_json_20180102 PARTITION OF msg_json
FOR VALUES FROM (20180102) TO (20180103);
CREATE TABLE msg_json_20180103 PARTITION OF msg_json
FOR VALUES FROM (20180103) TO (20180104);
...
until today
On the big_data_arch
I have the same table with the same name, structure and indexes in the
fix_capture schema but containing 2017 year data.
On the big_data DB i run following
CREATE FOREIGN TABLE staging.msg_json PARTITION of msg_json
FOR VALUES FROM (20170101) TO (20180101)
SERVER postgres_big_data_arch
OPTIONS (schema_name 'fix_capture', table_name 'msg_json');
I got error !!!!
SQL Error [42809]: ERROR: cannot create index on foreign table "msg_json"
ERROR: cannot create index on foreign table "msg_json"
ERROR: cannot create index on foreign table "msg_json"
I tried to import foreign table into staging schema and then attach
partition.
Import looks good. Foreign table contains data and i can query it through
fdw.
But running
ALTER TABLE msg_json ATTACH PARTITION staging.msg_json FOR VALUES FROM
(20170101) TO (20180101);
Brings me the same error!!!.
Thanks!
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2019-03-29 13:55:19 | Re: BUG #15724: Can't create foreign table as partition |
Previous Message | Jason Madden | 2019-03-29 12:42:38 | Re: BUG #15720: `executor could not find named tuplestore ABC` in AFTER DELETE trigger referencing OLD TABLE as ABC |