Hi Rahila,I am not able add a new partition if default partition is further partitionedwith default partition.Consider example below:postgres=# CREATE TABLE test ( a int, b int, c int) PARTITION BY LIST (a);CREATE TABLEpostgres=# CREATE TABLE test_p1 PARTITION OF test FOR VALUES IN(4, 5, 6, 7, 8);CREATE TABLEpostgres=# CREATE TABLE test_pd PARTITION OF test DEFAULT PARTITION BY LIST(b);CREATE TABLEpostgres=# CREATE TABLE test_pd_pd PARTITION OF test_pd DEFAULT;CREATE TABLEpostgres=# INSERT INTO test VALUES (20, 24, 12);INSERT 0 1postgres=# CREATE TABLE test_p2 PARTITION OF test FOR VALUES IN(15);ERROR: could not open file "base/12335/16420": No such file or directoryThanks,Jeevan LadheOn Fri, May 5, 2017 at 11:55 AM, Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com> wrote:Hi Rahila,pg_restore is failing for default partition, dump file still storing old syntax of default partition.create table lpd (a int, b int, c varchar) partition by list(a);create table lpd_d partition of lpd DEFAULT;create database bkp owner 'edb';grant all on DATABASE bkp to edb;--take plain dump of existing database\! ./pg_dump -f lpd_test.sql -Fp -d postgres--restore plain backup to new database bkp\! ./psql -f lpd_test.sql -d bkppsql:lpd_test.sql:63: ERROR: syntax error at or near "DEFAULT"LINE 2: FOR VALUES IN (DEFAULT); ^vi lpd_test.sql---- Name: lpd; Type: TABLE; Schema: public; Owner: edb--CREATE TABLE lpd ( a integer, b integer, c character varying)PARTITION BY LIST (a);ALTER TABLE lpd OWNER TO edb;---- Name: lpd_d; Type: TABLE; Schema: public; Owner: edb--CREATE TABLE lpd_d PARTITION OF lpdFOR VALUES IN (DEFAULT);ALTER TABLE lpd_d OWNER TO edb;Thanks,Rajkumar
Hi Rahila,pg_restore is failing for default partition, dump file still storing old syntax of default partition.create table lpd (a int, b int, c varchar) partition by list(a);create table lpd_d partition of lpd DEFAULT;create database bkp owner 'edb';grant all on DATABASE bkp to edb;--take plain dump of existing database\! ./pg_dump -f lpd_test.sql -Fp -d postgres--restore plain backup to new database bkp\! ./psql -f lpd_test.sql -d bkppsql:lpd_test.sql:63: ERROR: syntax error at or near "DEFAULT"LINE 2: FOR VALUES IN (DEFAULT); ^vi lpd_test.sql---- Name: lpd; Type: TABLE; Schema: public; Owner: edb--CREATE TABLE lpd ( a integer, b integer, c character varying)PARTITION BY LIST (a);ALTER TABLE lpd OWNER TO edb;---- Name: lpd_d; Type: TABLE; Schema: public; Owner: edb--CREATE TABLE lpd_d PARTITION OF lpdFOR VALUES IN (DEFAULT);ALTER TABLE lpd_d OWNER TO edb;Thanks,Rajkumar
pgsql-hackers by date:
Соглашаюсь с условиями обработки персональных данных