Re: [HACKERS] Adding support for Default partition in partitioning - Mailing list pgsql-hackers

Hi Rahila,

I am not able add a new partition if default partition is further partitioned

with default partition.

Consider example below:

postgres=# CREATE TABLE test ( a int, b int, c int) PARTITION BY LIST (a);

CREATE TABLE

postgres=# CREATE TABLE test_p1 PARTITION OF test FOR VALUES IN(4, 5, 6, 7, 8);

CREATE TABLE

postgres=# CREATE TABLE test_pd PARTITION OF test DEFAULT PARTITION BY LIST(b);

CREATE TABLE

postgres=# CREATE TABLE test_pd_pd PARTITION OF test_pd DEFAULT;

CREATE TABLE

postgres=# INSERT INTO test VALUES (20, 24, 12);

INSERT 0 1

postgres=# CREATE TABLE test_p2 PARTITION OF test FOR VALUES IN(15);

ERROR:  could not open file "base/12335/16420": No such file or directory

Thanks,

Jeevan Ladhe

On 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 bkp

psql: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 lpd
FOR VALUES IN (DEFAULT);


ALTER TABLE lpd_d OWNER TO edb;

Thanks,

Rajkumar