Can create foreign table as parition

From: Stepan Yankevych <stepya(at)ukr(dot)net>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Can create foreign table as parition
Date: 2019-03-29 12:37:20
Message-ID: 1553862240.275947963.u2imohu9@frv53.fwdcdn.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi Guys. 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. 
The same error while attach partition. 

Browse pgsql-bugs by date

  From Date Subject
Next 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
Previous Message Thomas Munro 2019-03-29 10:12:47 Re: BUG #15720: `executor could not find named tuplestore ABC` in AFTER DELETE trigger referencing OLD TABLE as ABC