pg_dump failed to dump sub external partition

[adam8157]

trafficstars

Greenplum version or build

Greenplum Database 5.8.0+dev.27.g06a5f48 build dev

Step to reproduce the behavior

Create the test table

CREATE TABLE m_partition (a int,b date,c text,d int)
DISTRIBUTED BY (a)
PARTITION BY RANGE (b)
SUBPARTITION BY LIST (c)
SUBPARTITION TEMPLATE
(SUBPARTITION usa values ('usa'),
SUBPARTITION apj values ('apj'),
SUBPARTITION eur values ('eur'))
(PARTITION Jan17 START (date '2017-01-01') INCLUSIVE , 
 PARTITION Feb17 START (date '2017-02-01') INCLUSIVE ,
 PARTITION Mar17 START (date '2017-03-01') INCLUSIVE ,
 PARTITION Apr17 START (date '2017-04-01') INCLUSIVE ,
 PARTITION May17 START (date '2017-05-01') INCLUSIVE ,
 PARTITION Jun17 START (date '2017-06-01') INCLUSIVE ,
 PARTITION Jul17 START (date '2017-07-01') INCLUSIVE ,
 PARTITION Aug17 START (date '2017-08-01') INCLUSIVE ,
 PARTITION Sep17 START (date '2017-09-01') INCLUSIVE ,
 PARTITION Oct17 START (date '2017-10-01') INCLUSIVE ,
 PARTITION Nov17 START (date '2017-11-01') INCLUSIVE ,
 PARTITION Dec17 START (date '2017-12-01') INCLUSIVE 
                 END (date '2018-01-01') EXCLUSIVE);

CREATE EXTERNAL TABLE may_eur2 (a int,b date,c text,d int) LOCATION ('gpfdist://127.0.0.1/eur') FORMAT 'text';

ALTER TABLE m_partition
ALTER PARTITION May17
EXCHANGE PARTITION eur WITH TABLE may_eur2 WITHOUT VALIDATION;

Dump the test table

$ pg_dump -s -x -O --gp-syntax -t public.m_partition gpadmin
--
-- Greenplum Database database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: m_partition; Type: TABLE; Schema: public; Owner: -; Tablespace:
--

CREATE TABLE m_partition (
    a integer,
    b date,
    c text,
    d integer
) DISTRIBUTED BY (a) PARTITION BY RANGE(b)
          SUBPARTITION BY LIST(c)
          (
          PARTITION jan17 START ('2017-01-01'::date) END ('2017-02-01'::date) WITH (tablename='m_partition_1_prt_jan17', appendonly=false )
                  (
                  SUBPARTITION usa VALUES('usa') WITH (tablename='m_partition_1_prt_jan17_2_prt_usa', appendonly=false ),
                  SUBPARTITION apj VALUES('apj') WITH (tablename='m_partition_1_prt_jan17_2_prt_apj', appendonly=false ),
                  SUBPARTITION eur VALUES('eur') WITH (tablename='m_partition_1_prt_jan17_2_prt_eur', appendonly=false )
                  ),
          PARTITION feb17 START ('2017-02-01'::date) END ('2017-03-01'::date) WITH (tablename='m_partition_1_prt_feb17', appendonly=false )
                  (
                  SUBPARTITION usa VALUES('usa') WITH (tablename='m_partition_1_prt_feb17_2_prt_usa', appendonly=false ),
                  SUBPARTITION apj VALUES('apj') WITH (tablename='m_partition_1_prt_feb17_2_prt_apj', appendonly=false ),
                  SUBPARTITION eur VALUES('eur') WITH (tablename='m_partition_1_prt_feb17_2_prt_eur', appendonly=false )
                  ),
          PARTITION mar17 START ('2017-03-01'::date) END ('2017-04-01'::date) WITH (tablename='m_partition_1_prt_mar17', appendonly=false )
                  (
                  SUBPARTITION usa VALUES('usa') WITH (tablename='m_partition_1_prt_mar17_2_prt_usa', appendonly=false ),
                  SUBPARTITION apj VALUES('apj') WITH (tablename='m_partition_1_prt_mar17_2_prt_apj', appendonly=false ),
                  SUBPARTITION eur VALUES('eur') WITH (tablename='m_partition_1_prt_mar17_2_prt_eur', appendonly=false )
                  ),
          PARTITION apr17 START ('2017-04-01'::date) END ('2017-05-01'::date) WITH (tablename='m_partition_1_prt_apr17', appendonly=false )
                  (
                  SUBPARTITION usa VALUES('usa') WITH (tablename='m_partition_1_prt_apr17_2_prt_usa', appendonly=false ),
                  SUBPARTITION apj VALUES('apj') WITH (tablename='m_partition_1_prt_apr17_2_prt_apj', appendonly=false ),
                  SUBPARTITION eur VALUES('eur') WITH (tablename='m_partition_1_prt_apr17_2_prt_eur', appendonly=false )
                  ),
          PARTITION may17 START ('2017-05-01'::date) END ('2017-06-01'::date) WITH (tablename='m_partition_1_prt_may17', appendonly=false )
                  (
                  SUBPARTITION usa VALUES('usa') WITH (tablename='m_partition_1_prt_may17_2_prt_usa', appendonly=false ),
                  SUBPARTITION apj VALUES('apj') WITH (tablename='m_partition_1_prt_may17_2_prt_apj', appendonly=false ),
                  SUBPARTITION eur VALUES('eur') WITH (tablename='m_partition_1_prt_may17_2_prt_eur', appendonly=false )
                  ),
          PARTITION jun17 START ('2017-06-01'::date) END ('2017-07-01'::date) WITH (tablename='m_partition_1_prt_jun17', appendonly=false )
                  (
                  SUBPARTITION usa VALUES('usa') WITH (tablename='m_partition_1_prt_jun17_2_prt_usa', appendonly=false ),
                  SUBPARTITION apj VALUES('apj') WITH (tablename='m_partition_1_prt_jun17_2_prt_apj', appendonly=false ),
                  SUBPARTITION eur VALUES('eur') WITH (tablename='m_partition_1_prt_jun17_2_prt_eur', appendonly=false )
                  ),
          PARTITION jul17 START ('2017-07-01'::date) END ('2017-08-01'::date) WITH (tablename='m_partition_1_prt_jul17', appendonly=false )
                  (
                  SUBPARTITION usa VALUES('usa') WITH (tablename='m_partition_1_prt_jul17_2_prt_usa', appendonly=false ),
                  SUBPARTITION apj VALUES('apj') WITH (tablename='m_partition_1_prt_jul17_2_prt_apj', appendonly=false ),
                  SUBPARTITION eur VALUES('eur') WITH (tablename='m_partition_1_prt_jul17_2_prt_eur', appendonly=false )
                  ),
          PARTITION aug17 START ('2017-08-01'::date) END ('2017-09-01'::date) WITH (tablename='m_partition_1_prt_aug17', appendonly=false )
                  (
                  SUBPARTITION usa VALUES('usa') WITH (tablename='m_partition_1_prt_aug17_2_prt_usa', appendonly=false ),
                  SUBPARTITION apj VALUES('apj') WITH (tablename='m_partition_1_prt_aug17_2_prt_apj', appendonly=false ),
                  SUBPARTITION eur VALUES('eur') WITH (tablename='m_partition_1_prt_aug17_2_prt_eur', appendonly=false )
                  ),
          PARTITION sep17 START ('2017-09-01'::date) END ('2017-10-01'::date) WITH (tablename='m_partition_1_prt_sep17', appendonly=false )
                  (
                  SUBPARTITION usa VALUES('usa') WITH (tablename='m_partition_1_prt_sep17_2_prt_usa', appendonly=false ),
                  SUBPARTITION apj VALUES('apj') WITH (tablename='m_partition_1_prt_sep17_2_prt_apj', appendonly=false ),
                  SUBPARTITION eur VALUES('eur') WITH (tablename='m_partition_1_prt_sep17_2_prt_eur', appendonly=false )
                  ),
          PARTITION oct17 START ('2017-10-01'::date) END ('2017-11-01'::date) WITH (tablename='m_partition_1_prt_oct17', appendonly=false )
                  (
                  SUBPARTITION usa VALUES('usa') WITH (tablename='m_partition_1_prt_oct17_2_prt_usa', appendonly=false ),
                  SUBPARTITION apj VALUES('apj') WITH (tablename='m_partition_1_prt_oct17_2_prt_apj', appendonly=false ),
                  SUBPARTITION eur VALUES('eur') WITH (tablename='m_partition_1_prt_oct17_2_prt_eur', appendonly=false )
                  ),
          PARTITION nov17 START ('2017-11-01'::date) END ('2017-12-01'::date) WITH (tablename='m_partition_1_prt_nov17', appendonly=false )
                  (
                  SUBPARTITION usa VALUES('usa') WITH (tablename='m_partition_1_prt_nov17_2_prt_usa', appendonly=false ),
                  SUBPARTITION apj VALUES('apj') WITH (tablename='m_partition_1_prt_nov17_2_prt_apj', appendonly=false ),
                  SUBPARTITION eur VALUES('eur') WITH (tablename='m_partition_1_prt_nov17_2_prt_eur', appendonly=false )
                  ),
          PARTITION dec17 START ('2017-12-01'::date) END ('2018-01-01'::date) WITH (tablename='m_partition_1_prt_dec17', appendonly=false )
                  (
                  SUBPARTITION usa VALUES('usa') WITH (tablename='m_partition_1_prt_dec17_2_prt_usa', appendonly=false ),
                  SUBPARTITION apj VALUES('apj') WITH (tablename='m_partition_1_prt_dec17_2_prt_apj', appendonly=false ),
                  SUBPARTITION eur VALUES('eur') WITH (tablename='m_partition_1_prt_dec17_2_prt_eur', appendonly=false )
                  )
          );
 ALTER TABLE m_partition
SET SUBPARTITION TEMPLATE
          (
          SUBPARTITION usa VALUES('usa') WITH (tablename='m_partition'),
          SUBPARTITION apj VALUES('apj') WITH (tablename='m_partition'),
          SUBPARTITION eur VALUES('eur') WITH (tablename='m_partition')
          )
;
ALTER TABLE m_partition EXCHANGE PARTITION eur WITH TABLE m_partition_1_prt_may17_2_prt_eur_external_partition__ WITHOUT VALIDATION;
DROP TABLE m_partition_1_prt_may17_2_prt_eur_external_partition__;


--
-- Greenplum Database database dump complete
--

Execute the dumped sql

$ psql -h ANOTHER_DB -f test.sql
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
psql:test.sql:110: ERROR:  partition "eur" of relation "m_partition" does not exist
psql:test.sql:111: ERROR:  table "m_partition_1_prt_may17_2_prt_eur_external_partition__" does not exist

Two issues:

1. lost "ALTER PARTITION May17"
2. the temporary external table was not dumped, it's supposed to, because it has the drop statement.

[macroyuyang]

@paul-guo- Paul, have you encountered this before?

[paul-guo-]

Partition dump is known to have issues (We still have PR pending), but this is the first time that I see this.

[macroyuyang]

Thanks, Paul!

[Eulerizeit]

Currently it is a known issue that partitioning and pg_dump is problematic. There are a number of PRs by @jchampio and @danielgustafsson that are working around this issue.

[adam8157]

After the 12 merge, it fails at the pg_dump step: pg_dump: error: invalid number of parents 0 for table "m_partition_1_prt_may17_2_prt_eur"

[ivannovick]

@adam8157 is this resolved?

[adam8157]

@ivannovick seems not

$ pg_dump -s -x -O --gp-syntax -t public.m_partition gpadmin
--
-- Greenplum Database database dump
--

-- Dumped from database version 12beta2
-- Dumped by pg_dump version 12beta2

SET gp_default_storage_options = '';
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

--
-- Name: m_partition; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.m_partition (
    a integer,
    b date,
    c text,
    d integer
)
PARTITION BY RANGE (b) DISTRIBUTED BY (a);


--
-- Greenplum Database database dump complete
--

[dpandhi-git]

@jimmyyih this needs to check as part of upgrade

[jimmyyih]

This is fixed in https://github.com/greenplum-db/gpdb/commit/8ecf11fa6eb936afa6875da76fd82332dfb793d2 for 6X_STABLE. For GPDB main (7X), the issue shouldn't exist due to the new partitioning logic inherited from upstream Postgres (e.g. simply ATTACH PARTITION instead of the complicated ALTER TABLE EXCHANGE PARTITION stuff).

[adam8157]

@jimmyyih I still could reproduce the issue with the latest main branch. 1, it lost all hierarchy, and only has:

CREATE TABLE public.m_partition (
    a integer,
    b date,
    c text,
    d integer
)
PARTITION BY RANGE (b) DISTRIBUTED BY (a);

2, we should be able to dump the table no matter how it was created.

[adam8157]

Oops, I meant to comment but not reopen, feel free to close if I missed something.

[jimmyyih]

@jimmyyih I still could reproduce the issue with the latest main branch. 1, it lost all hierarchy, and only has:

CREATE TABLE public.m_partition (
    a integer,
    b date,
    c text,
    d integer
)
PARTITION BY RANGE (b) DISTRIBUTED BY (a);

2, we should be able to dump the table no matter how it was created.

Take this smaller example (two-level partition table with 2 child partitions where 1 child is an external partition):

CREATE TABLE two_level_partition_table(a int, b int, c int)
DISTRIBUTED BY (a)
PARTITION BY RANGE (b)
SUBPARTITION BY RANGE (c)
SUBPARTITION TEMPLATE (START (1) END (2) EVERY (1))
(START (1) END (3) EVERY (1));
CREATE EXTERNAL TABLE external_table (a int, b int, c int) LOCATION ('file://localhost//tmp/mydata.csv') FORMAT 'TEXT' (DELIMITER '|');
ALTER TABLE two_level_partition_table ALTER PARTITION FOR (1) EXCHANGE PARTITION FOR (1) WITH TABLE external_table WITHOUT VALIDATION;
DROP TABLE external_table;

Running pg_dump -s -x -O --gp-syntax <dbname> gives this output:

CREATE TABLE public.two_level_partition_table (
    a integer,
    b integer,
    c integer
)
PARTITION BY RANGE (b) DISTRIBUTED BY (a);

CREATE TABLE public.two_level_partition_table_1_prt_1 (
    a integer,
    b integer,
    c integer
)
PARTITION BY RANGE (c) DISTRIBUTED BY (a);

CREATE FOREIGN TABLE public.two_level_partition_table_1_prt_1_2_prt_1 (
    a integer,
    b integer,
    c integer
)
SERVER gp_exttable_server
OPTIONS (
    delimiter '|',
    encoding '6',
    escape E'\\',
    execute_on 'ALL_SEGMENTS',
    format 'text',
    format_type 't',
    is_writable 'false',
    location_uris 'file://localhost//tmp/mydata.csv',
    log_errors 'f',
    "null" E'\\N'
);

CREATE TABLE public.two_level_partition_table_1_prt_2 (
    a integer,
    b integer,
    c integer
)
PARTITION BY RANGE (c) DISTRIBUTED BY (a);

CREATE TABLE public.two_level_partition_table_1_prt_2_2_prt_1 (
    a integer,
    b integer,
    c integer
) DISTRIBUTED BY (a);

ALTER TABLE ONLY public.two_level_partition_table ATTACH PARTITION public.two_level_partition_table_1_prt_1 FOR VALUES FROM (1) TO (2);
ALTER TABLE ONLY public.two_level_partition_table_1_prt_1 ATTACH PARTITION public.two_level_partition_table_1_prt_1_2_prt_1 FOR VALUES FROM (1) TO (2);
ALTER TABLE ONLY public.two_level_partition_table ATTACH PARTITION public.two_level_partition_table_1_prt_2 FOR VALUES FROM (2) TO (3);
ALTER TABLE ONLY public.two_level_partition_table_1_prt_2 ATTACH PARTITION public.two_level_partition_table_1_prt_2_2_prt_1 FOR VALUES FROM (1) TO (2);

The 7X dump uses upstream syntax (instead of the old deprecated syntax) of creating the individual tables and stringing them together with ATTACH PARTITION commands. The SQL files will be larger but complicated edge cases like this Github issue are trivialized as you can see in the above dump where the external partition public.two_level_partition_table_1_prt_1_2_prt_1 is simply created and then attached to its subroot.

[adam8157]

Hmm... I probably hit another corner case?

$ psql
psql (12.12)
Type "help" for help.

gpadmin=# CREATE TABLE m_partition (a int,b date,c text,d int)
DISTRIBUTED BY (a)
PARTITION BY RANGE (b)
SUBPARTITION BY LIST (c)
SUBPARTITION TEMPLATE
(SUBPARTITION usa values ('usa'),
SUBPARTITION apj values ('apj'),
SUBPARTITION eur values ('eur'))
(PARTITION Jan17 START (date '2017-01-01') INCLUSIVE ,
 PARTITION Feb17 START (date '2017-02-01') INCLUSIVE ,
 PARTITION Mar17 START (date '2017-03-01') INCLUSIVE ,
 PARTITION Apr17 START (date '2017-04-01') INCLUSIVE ,
 PARTITION May17 START (date '2017-05-01') INCLUSIVE ,
 PARTITION Jun17 START (date '2017-06-01') INCLUSIVE ,
 PARTITION Jul17 START (date '2017-07-01') INCLUSIVE ,
 PARTITION Aug17 START (date '2017-08-01') INCLUSIVE ,
 PARTITION Sep17 START (date '2017-09-01') INCLUSIVE ,
 PARTITION Oct17 START (date '2017-10-01') INCLUSIVE ,
 PARTITION Nov17 START (date '2017-11-01') INCLUSIVE ,
 PARTITION Dec17 START (date '2017-12-01') INCLUSIVE
                 END (date '2018-01-01') EXCLUSIVE);
CREATE TABLE

pg_dump now, and it lost all hierarchy.

$ pg_dump -s -x -O --gp-syntax -t public.m_partition gpadmin
--
-- Greenplum Database database dump
--

-- Dumped from database version 12.12
-- Dumped by pg_dump version 12.12

SET gp_default_storage_options = '';
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: m_partition; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.m_partition (
    a integer,
    b date,
    c text,
    d integer
)
PARTITION BY RANGE (b) DISTRIBUTED BY (a);


--
-- Greenplum Database database dump complete
--

Exchange with an external table.

gpadmin=# CREATE EXTERNAL TABLE may_eur2 (a int,b date,c text,d int) LOCATION ('gpfdist://127.0.0.1/eur') FORMAT 'text';

ALTER TABLE m_partition
ALTER PARTITION May17
EXCHANGE PARTITION eur WITH TABLE may_eur2 WITHOUT VALIDATION;
CREATE EXTERNAL TABLE
NOTICE:  specifying "WITHOUT VALIDATION" acts as no operation. If the new partition is a regular table, validation is performed to make sure all the rows obey partition constraint. If the new partition is external or foreign table,
 no validation is performed.
ALTER TABLE
gpadmin=# \d+ m_partition_1_prt_may17_2_prt_eur
                        Foreign table "public.m_partition_1_prt_may17_2_prt_eur"
 Column |  Type   | Collation | Nullable | Default | FDW options | Storage  | Stats target | Description
--------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
 a      | integer |           |          |         |             | plain    |              |
 b      | date    |           |          |         |             | plain    |              |
 c      | text    |           |          |         |             | extended |              |
 d      | integer |           |          |         |             | plain    |              |
Partition of: m_partition_1_prt_may17 FOR VALUES IN ('eur')
Partition constraint: ((b IS NOT NULL) AND (b >= '2017-05-01'::date) AND (b < '2017-06-01'::date) AND (c IS NOT NULL) AND (c = 'eur'::text))
FDW options: (format 'text', delimiter '        ', "null" E'\\N', escape E'\\', format_type 't', location_uris 'gpfdist://127.0.0.1:8080/eur', execute_on 'ALL_SEGMENTS', log_errors 'f', encoding '6', is_writable 'false')

pg_dump still got nothing.

$ pg_dump -s -x -O --gp-syntax -t public.m_partition gpadmin
--
-- Greenplum Database database dump
--

-- Dumped from database version 12.12
-- Dumped by pg_dump version 12.12

SET gp_default_storage_options = '';
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: m_partition; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.m_partition (
    a integer,
    b date,
    c text,
    d integer
)
PARTITION BY RANGE (b) DISTRIBUTED BY (a);


--
-- Greenplum Database database dump complete
--

[jimmyyih]

Hmm... I probably hit another corner case?

I think it's because you're using the -t flag so only the root is being dumped for your command. I don't think the subroots and child partitions are dumped unless you explicitly specify them as well. This seems to be the same in upstream Postgres as well.

[adam8157]

Oops, my mistake. Thank you.