On Mon, Sep 4, 2017 at 4:08 PM, amul sul <sula...@gmail.com> wrote: > I've updated patch to use an extended hash function (​Commit # > 81c5e46c490e2426db243eada186995da5bb0ba7) for the partitioning. > > I have done some testing with these patches, everything looks fine, attaching sql and out file for reference.
Thanks & Regards, Rajkumar Raghuwanshi QMG, EnterpriseDB Corporation
hash_partition_test.out
Description: Binary data
--Testing CREATE TABLE...PARTITION BY HASH syntax
--------------------------------------------------
--basic syntax --should pass
CREATE TABLE hp_tbl (a int) PARTITION BY HASH (a);
drop table hp_tbl;
--partition with more than one coloumn --should pass
CREATE TABLE hp_tbl (a int, b int) PARTITION BY HASH (a,b);
drop table hp_tbl;
--partition with expression --should pass
CREATE TABLE hp_tbl (a int, b int) PARTITION BY HASH (abs(a));
drop table hp_tbl;
--partition with airthmatic expression --should pass
CREATE TABLE hp_tbl (a int, b int) PARTITION BY HASH ((a+b));
drop table hp_tbl;
--partition with other data type --should pass
CREATE TABLE hp_tbl (a text, b Date) PARTITION BY HASH (a);
drop table hp_tbl;
CREATE TABLE hp_tbl (a text, b Date) PARTITION BY HASH (b);
drop table hp_tbl;
--Testing CREATE TABLE...PARTITION OF syntax
--------------------------------------------------
CREATE TABLE hp_tbl (a int, b text) PARTITION BY HASH (a);
--basic partition of syntax --should pass
CREATE TABLE hp_tbl_p1 PARTITION OF hp_tbl FOR VALUES WITH (modulus 20, remainder 0);
--trying to attach same partition again --should fail
CREATE TABLE hp_tbl_p1 PARTITION OF hp_tbl FOR VALUES WITH (modulus 20, remainder 0);
--trying to attach with same modulus different remainder --should pass
CREATE TABLE hp_tbl_p2 PARTITION OF hp_tbl FOR VALUES WITH (modulus 20, remainder 1);
--trying to attach with different modulus different remainder --should pass
CREATE TABLE hp_tbl_p3 PARTITION OF hp_tbl FOR VALUES WITH (modulus 40, remainder 2);
--trying to create with a value not factor of previous value --should fail
CREATE TABLE hp_tbl_p4 PARTITION OF hp_tbl FOR VALUES WITH (modulus 45, remainder 0);
-- trying to create with modulus equal to zero --should fail
CREATE TABLE hp_tbl_p4 PARTITION OF hp_tbl FOR VALUES WITH (modulus 0, remainder 1);
-- trying to create with remainder greater or equal than modulus --should fail
CREATE TABLE hp_tbl_p4 PARTITION OF hp_tbl FOR VALUES WITH (modulus 60, remainder 60);
--trying to create like list partition --should fail
CREATE TABLE hp_tbl_p4 PARTITION OF hp_tbl FOR VALUES IN (10);
--trying to create like range partition --should fail
CREATE TABLE hp_tbl_p4 PARTITION OF hp_tbl FOR VALUES FROM (0) TO (10);
DROP TABLE hp_tbl;
--trying to create for list partition --should fail
CREATE TABLE hp_tbl (a int, b text) PARTITION BY LIST (a);
CREATE TABLE hp_tbl_p1 PARTITION OF hp_tbl FOR VALUES WITH (modulus 10, remainder 0);
DROP TABLE hp_tbl;
--trying to create for range partition --should fail
CREATE TABLE hp_tbl (a int, b text) PARTITION BY RANGE (a);
CREATE TABLE hp_tbl_p1 PARTITION OF hp_tbl FOR VALUES WITH (modulus 10, remainder 0);
DROP TABLE hp_tbl;
--check for table description
--------------------------------------------------
CREATE TABLE hp_tbl (a int, b text) PARTITION BY HASH (a);
\d+ hp_tbl
CREATE TABLE hp_tbl_p1 PARTITION OF hp_tbl FOR VALUES WITH (modulus 20, remainder 0);
\d+ hp_tbl;\d+ hp_tbl_p1
CREATE TABLE hp_tbl_p2 PARTITION OF hp_tbl FOR VALUES WITH (modulus 20, remainder 1);
\d+ hp_tbl;\d+ hp_tbl_p1;\d+ hp_tbl_p2
CREATE TABLE hp_tbl_p3 (like hp_tbl);
ALTER TABLE hp_tbl ATTACH PARTITION hp_tbl_p3 FOR VALUES WITH (modulus 20, remainder 2);
\d+ hp_tbl;\d+ hp_tbl_p1;\d+ hp_tbl_p2;\d+ hp_tbl_p3
ALTER TABLE hp_tbl DETACH PARTITION hp_tbl_p3;
\d+ hp_tbl;\d+ hp_tbl_p1;\d+ hp_tbl_p2
DROP TABLE hp_tbl_p3;
DROP TABLE hp_tbl;
--testing TEMP-NESS of Hash partition
--------------------------------------------------
--trying to add temp partition to permanent partiton --should pass
CREATE TABLE hp_tbl (a int, b text) PARTITION BY HASH (a);
CREATE TEMP TABLE hp_tbl_p1 PARTITION OF hp_tbl FOR VALUES WITH (modulus 20, remainder 0);
DROP TABLE hp_tbl;
--trying to add permanent partition to temp partiton --should fail
CREATE TEMP TABLE hp_tbl (a int, b text) PARTITION BY HASH (a);
CREATE TABLE hp_tbl_p1 PARTITION OF hp_tbl FOR VALUES WITH (modulus 20, remainder 0);
DROP TABLE hp_tbl;
--trying to add temp partition to temp partiton --should pass
CREATE TEMP TABLE hp_tbl (a int, b text) PARTITION BY HASH (a);
CREATE TEMP TABLE hp_tbl_p1 PARTITION OF hp_tbl FOR VALUES WITH (modulus 20, remainder 0);
DROP TABLE hp_tbl;
--testing with/without oids for Hash partition
--------------------------------------------------
--when root partition have oid
create table hp_tbl (a int) partition by hash (a) with oids;
--partition can be created with oids --should pass
create table hp_tbl_p1 partition of hp_tbl FOR VALUES WITH (modulus 20, remainder 0) with oids;
\d+ hp_tbl_p1
drop table hp_tbl_p1;
--by default partition should created with oids --should pass
create table hp_tbl_p1 partition of hp_tbl FOR VALUES WITH (modulus 20, remainder 0);
\d+ hp_tbl_p1
drop table hp_tbl_p1;
--without oids if specified, should get override --should pass
create table hp_tbl_p1 partition of hp_tbl FOR VALUES WITH (modulus 20, remainder 0) without oids;
\d+ hp_tbl_p1
drop table hp_tbl_p1;
drop table hp_tbl;
--when root partition does not have oid
create table hp_tbl (a int) partition by hash (a) without oids;
--partition can be created without oids --should pass
create table hp_tbl_p1 partition of hp_tbl FOR VALUES WITH (modulus 20, remainder 0) without oids;
\d+ hp_tbl_p1
drop table hp_tbl_p1;
--by default partition should created without oids --should pass
create table hp_tbl_p1 partition of hp_tbl FOR VALUES WITH (modulus 20, remainder 0);
\d+ hp_tbl_p1
drop table hp_tbl_p1;
--with oids if specified, should get error --should fail
create table hp_tbl_p1 partition of hp_tbl FOR VALUES WITH (modulus 20, remainder 0) with oids;
drop table hp_tbl;
--testing Insert/Update/Delete for Hash partition
--------------------------------------------------
CREATE OR REPLACE FUNCTION dummy_hashint4_test(a int4, seed int8) RETURNS int8 AS
$$ BEGIN RETURN (a + 1 + seed); END; $$ LANGUAGE 'plpgsql' IMMUTABLE;
CREATE OPERATOR CLASS custom_opclass_test FOR TYPE int4 USING HASH AS
OPERATOR 1 = , FUNCTION 2 dummy_hashint4_test(int4, int8);
CREATE TABLE hp_tbl (a int) PARTITION BY HASH (a custom_opclass_test);
CREATE TABLE hp_tbl_p1 PARTITION OF hp_tbl FOR VALUES WITH (modulus 4, remainder 0);
CREATE TABLE hp_tbl_p2 PARTITION OF hp_tbl FOR VALUES WITH (modulus 4, remainder 1);
CREATE TABLE hp_tbl_p3 PARTITION OF hp_tbl FOR VALUES WITH (modulus 4, remainder 2);
--should be in p1 --should pass
INSERT INTO hp_tbl VALUES (0);
INSERT INTO hp_tbl_p1 VALUES (0);
--1 should be in p2 --should pass
INSERT INTO hp_tbl VALUES (1);
INSERT INTO hp_tbl_p2 VALUES (1);
--2 should be in p3 --should pass
INSERT INTO hp_tbl VALUES (2);
INSERT INTO hp_tbl_p3 VALUES (2);
SELECT tableoid::regclass AS partition, a FROM hp_tbl ORDER BY 1,2;
--updates
--updating root - 0+4 = 4 satisfies p1, should be in same partition --should pass
UPDATE hp_tbl SET a = a + 4 WHERE a = 0;
SELECT tableoid::regclass AS partition, a FROM hp_tbl ORDER BY 1,2;
--updating child - 1+4 = 5 satisfies p2, should be in same partition --should pass
UPDATE hp_tbl_p2 SET a = a + 4 WHERE a = 1;
SELECT tableoid::regclass AS partition, a FROM hp_tbl ORDER BY 1,2;
--updating root - 1+2 = 3 not satisfies p3, need to move in other partition --should fail
UPDATE hp_tbl SET a = a + 2 WHERE a = 2;
--updating child - 1+2 = 3 not satisfies p3, need to move in other partition --should fail
UPDATE hp_tbl_p3 SET a = a + 2 WHERE a = 2;
drop table hp_tbl;
--testing attach/detach for Hash partition
--------------------------------------------------
CREATE TABLE hp_tbl (a int) PARTITION BY HASH (a custom_opclass_test);
CREATE TABLE hp_tbl_p1 PARTITION OF hp_tbl FOR VALUES WITH (modulus 4, remainder 0);
CREATE TABLE hp_tbl_p2 (a int);
--attach partition
--trying to attach a valid partition --should pass
ALTER TABLE hp_tbl ATTACH PARTITION hp_tbl_p2 FOR VALUES WITH (modulus 4, remainder 1);
\d+ hp_tbl;\d+ hp_tbl_p2
--trying to attach same partition again --should fail
ALTER TABLE hp_tbl ATTACH PARTITION hp_tbl_p2 FOR VALUES WITH (modulus 4, remainder 1);
--trying to attach partition in a not-partitioned table --should fail
ALTER TABLE hp_tbl_p1 ATTACH PARTITION hp_tbl_p2 FOR VALUES WITH (modulus 4, remainder 2);
--trying to attach partition overlap with existing --should fail
CREATE TABLE hp_tbl_p3 (a int);
ALTER TABLE hp_tbl ATTACH PARTITION hp_tbl_p3 FOR VALUES WITH (modulus 8, remainder 1);
--trying to attach partition not a factor of previos partition --should fail
ALTER TABLE hp_tbl ATTACH PARTITION hp_tbl_p3 FOR VALUES WITH (modulus 5, remainder 1);
--trying to attach partition having data setisfy partition constraint --should pass
insert into hp_tbl_p3 values (6),(10);
ALTER TABLE hp_tbl ATTACH PARTITION hp_tbl_p3 FOR VALUES WITH (modulus 4, remainder 2);
--trying to attach partition having data not setisfy partition constraint --should fail
CREATE TABLE hp_tbl_p4 (a int);
insert into hp_tbl_p4 values (7),(10);
ALTER TABLE hp_tbl ATTACH PARTITION hp_tbl_p4 FOR VALUES WITH (modulus 4, remainder 3);
--trying to attach partition having data after deleting voileted row setisfy partition constraint --should pass
delete from hp_tbl_p4 where a = 10;
ALTER TABLE hp_tbl ATTACH PARTITION hp_tbl_p4 FOR VALUES WITH (modulus 4, remainder 3);
--detach partition
\d+ hp_tbl
-- detach valid partition and after detach table removed from partition description --should pass
ALTER TABLE hp_tbl DETACH PARTITION hp_tbl_p3;
\d+ hp_tbl
--trying to detach table not a part of partition --should fail
CREATE TABLE hp_tbl_p0 (a int);
ALTER TABLE hp_tbl DETACH PARTITION hp_tbl_p0;
--detached partition can b attached again --should pass
ALTER TABLE hp_tbl ATTACH PARTITION hp_tbl_p3 FOR VALUES WITH (modulus 4, remainder 2);
drop table hp_tbl;
drop table hp_tbl_p0;
--test with expression
--------------------------------------------------
CREATE TABLE hp_tbl (a int, b int, c int) PARTITION BY HASH ((b+c) custom_opclass_test);
CREATE TABLE hp_tbl_p1 PARTITION OF hp_tbl FOR VALUES WITH (modulus 4, remainder 0);
CREATE TABLE hp_tbl_p2 PARTITION OF hp_tbl FOR VALUES WITH (modulus 4, remainder 1);
CREATE TABLE hp_tbl_p3 PARTITION OF hp_tbl FOR VALUES WITH (modulus 4, remainder 2);
CREATE TABLE hp_tbl_p4 PARTITION OF hp_tbl FOR VALUES WITH (modulus 4, remainder 3);
\d+ hp_tbl
\d+ hp_tbl_p1
insert into hp_tbl values (1,0,4);
insert into hp_tbl values (2,2,3);
insert into hp_tbl values (3,4,2);
insert into hp_tbl values (4,1,6);
SELECT tableoid::regclass AS partition, a,b,c,b+c as "b+c",trunc((b+c)/4,0) as result, (b+c)%4 as remainder FROM hp_tbl ORDER BY 1,2;
DROP TABLE hp_tbl;
--test with contraints
--------------------------------------------------
CREATE TABLE hp_tbl (a int, b int NOT NULL, c int check(c<7)) PARTITION BY HASH ((b+c) custom_opclass_test);
CREATE TABLE hp_tbl_p1 PARTITION OF hp_tbl FOR VALUES WITH (modulus 4, remainder 0);
CREATE TABLE hp_tbl_p2 PARTITION OF hp_tbl FOR VALUES WITH (modulus 4, remainder 1);
CREATE TABLE hp_tbl_p3 PARTITION OF hp_tbl FOR VALUES WITH (modulus 4, remainder 2);
CREATE TABLE hp_tbl_p4 PARTITION OF hp_tbl FOR VALUES WITH (modulus 4, remainder 3);
\d+ hp_tbl
\d+ hp_tbl_p1
insert into hp_tbl values (1,0,4);
insert into hp_tbl values (2,2,3);
insert into hp_tbl values (3,4,2);
insert into hp_tbl values (4,1,6);
SELECT tableoid::regclass AS partition, a,b,c,b+c as "b+c",trunc((b+c)/4,0) as result, (b+c)%4 as remainder FROM hp_tbl ORDER BY 1,2;
DROP TABLE hp_tbl;
--test with views
--------------------------------------------------
CREATE TABLE hp_tbl (a int) PARTITION BY HASH (a custom_opclass_test);
CREATE TABLE hp_tbl_p1 PARTITION OF hp_tbl FOR VALUES WITH (modulus 4, remainder 0);
CREATE TABLE hp_tbl_p2 PARTITION OF hp_tbl FOR VALUES WITH (modulus 4, remainder 1);
CREATE TABLE hp_tbl_p3 PARTITION OF hp_tbl FOR VALUES WITH (modulus 4, remainder 2);
INSERT INTO hp_tbl VALUES (0);
INSERT INTO hp_tbl VALUES (1);
INSERT INTO hp_tbl VALUES (2);
CREATE VIEW hp_tbl_v AS SELECT * FROM hp_tbl;
CREATE VIEW hp_tbl_p2_v AS SELECT * FROM hp_tbl_p2;
--insert in root partition via view
INSERT INTO hp_tbl_v VALUES (4);
SELECT tableoid::regclass AS partition, * FROM hp_tbl ORDER BY 1,2;
SELECT * FROM hp_tbl_v;
--insert in child partition via view
INSERT INTO hp_tbl_p2_v VALUES (5);
SELECT tableoid::regclass AS partition, * FROM hp_tbl_p2 ORDER BY 1,2;
SELECT * FROM hp_tbl_p2_v;
DROP TABLE hp_tbl CASCADE;
--test with multi-level hash-hash partition
--------------------------------------------------
CREATE TABLE hp_tbl (a int, b int, c int) PARTITION BY HASH (a custom_opclass_test);
CREATE TABLE hp_tbl_p1 PARTITION OF hp_tbl FOR VALUES WITH (modulus 4, remainder 0) PARTITION BY HASH (b custom_opclass_test);
CREATE TABLE hp_tbl_p1_p1 PARTITION OF hp_tbl_p1 FOR VALUES WITH (modulus 4, remainder 0) PARTITION BY HASH (c custom_opclass_test);
CREATE TABLE hp_tbl_p1_p1_p1 PARTITION OF hp_tbl_p1_p1 FOR VALUES WITH (modulus 4, remainder 0);
CREATE TABLE hp_tbl_p2 PARTITION OF hp_tbl FOR VALUES WITH (modulus 4, remainder 1) PARTITION BY HASH (b custom_opclass_test);
CREATE TABLE hp_tbl_p2_p1 PARTITION OF hp_tbl_p2 FOR VALUES WITH (modulus 4, remainder 1);
CREATE TABLE hp_tbl_p2_p2 PARTITION OF hp_tbl_p2 FOR VALUES WITH (modulus 4, remainder 2);
\d+ hp_tbl;\d+ hp_tbl_p1;\d+ hp_tbl_p1_p1;\d+ hp_tbl_p1_p1_p1;\d+ hp_tbl_p2;\d+ hp_tbl_p2_p1;\d+ hp_tbl_p2_p2
INSERT INTO hp_tbl VALUES (1,1,1);
INSERT INTO hp_tbl VALUES (1,2,2);
INSERT INTO hp_tbl VALUES (0,0,0);
SELECT tableoid::regclass AS partition, * FROM hp_tbl ORDER BY 1,2;
DROP TABLE hp_tbl;
--test with multi-level hash-list partition
--------------------------------------------------
CREATE TABLE hp_tbl (a int, b int, c int) PARTITION BY HASH (a custom_opclass_test);
CREATE TABLE hp_tbl_p1 PARTITION OF hp_tbl FOR VALUES WITH (modulus 4, remainder 0) PARTITION BY LIST (a);
CREATE TABLE hp_tbl_p1_p1 PARTITION OF hp_tbl_p1 FOR VALUES IN (0,4);
CREATE TABLE hp_tbl_p1_p2 PARTITION OF hp_tbl_p1 FOR VALUES IN (NULL,8);
CREATE TABLE hp_tbl_p2 PARTITION OF hp_tbl FOR VALUES WITH (modulus 4, remainder 1) PARTITION BY LIST (b);
CREATE TABLE hp_tbl_p2_p1 PARTITION OF hp_tbl_p2 FOR VALUES IN (NULL,1);
CREATE TABLE hp_tbl_p2_p2 PARTITION OF hp_tbl_p2 FOR VALUES IN (13,9);
INSERT INTO hp_tbl VALUES (NULL,NULL,NULL);
INSERT INTO hp_tbl VALUES (4,4,4);
INSERT INTO hp_tbl VALUES (8,8,8);
INSERT INTO hp_tbl VALUES (9,NULL,NULL);
INSERT INTO hp_tbl VALUES (9,9,9);
SELECT tableoid::regclass AS partition, * FROM hp_tbl ORDER BY 1,2;
DROP TABLE hp_tbl;
--test with multi-level hash-range partition
--------------------------------------------------
CREATE TABLE hp_tbl (a int, b int, c int) PARTITION BY HASH (a custom_opclass_test);
CREATE TABLE hp_tbl_p1 PARTITION OF hp_tbl FOR VALUES WITH (modulus 4, remainder 0) PARTITION BY RANGE (a);
CREATE TABLE hp_tbl_p1_p1 PARTITION OF hp_tbl_p1 FOR VALUES FROM (1) TO (6);
CREATE TABLE hp_tbl_p1_p2 PARTITION OF hp_tbl_p1 FOR VALUES FROM (6) TO (11);
CREATE TABLE hp_tbl_p2 PARTITION OF hp_tbl FOR VALUES WITH (modulus 4, remainder 1) PARTITION BY RANGE (b);
CREATE TABLE hp_tbl_p2_p1 PARTITION OF hp_tbl_p2 FOR VALUES FROM (1) TO (6);
CREATE TABLE hp_tbl_p2_p2 PARTITION OF hp_tbl_p2 FOR VALUES FROM (6) TO (11);
INSERT INTO hp_tbl VALUES (4,4,4);
INSERT INTO hp_tbl VALUES (8,8,8);
INSERT INTO hp_tbl VALUES (9,9,9);
SELECT tableoid::regclass AS partition, * FROM hp_tbl ORDER BY 1,2;
DROP TABLE hp_tbl;
--test with multi-level hash-range-list partition
--------------------------------------------------
CREATE TABLE hp_tbl (a int, b int, c int) PARTITION BY HASH (a custom_opclass_test);
CREATE TABLE hp_tbl_p1 PARTITION OF hp_tbl FOR VALUES WITH (modulus 4, remainder 0) PARTITION BY LIST (b);
CREATE TABLE hp_tbl_p1_p1 PARTITION OF hp_tbl_p1 FOR VALUES IN (0,4);
CREATE TABLE hp_tbl_p1_p2 PARTITION OF hp_tbl_p1 FOR VALUES IN (NULL,8);
CREATE TABLE hp_tbl_p2 PARTITION OF hp_tbl FOR VALUES WITH (modulus 4, remainder 1) PARTITION BY RANGE (b);
CREATE TABLE hp_tbl_p2_p1 PARTITION OF hp_tbl_p2 FOR VALUES FROM (1) TO (6);
CREATE TABLE hp_tbl_p2_p2 PARTITION OF hp_tbl_p2 FOR VALUES FROM (6) TO (11);
INSERT INTO hp_tbl VALUES (NULL,NULL,NULL);
INSERT INTO hp_tbl VALUES (4,4,4);
INSERT INTO hp_tbl VALUES (8,8,8);
INSERT INTO hp_tbl VALUES (9,9,9);
SELECT tableoid::regclass AS partition, * FROM hp_tbl ORDER BY 1,2;
DROP TABLE hp_tbl;
--test with foreign tables
--------------------------------------------------
CREATE EXTENSION postgres_fdw;
DO $d$
BEGIN
EXECUTE $$CREATE SERVER server_test FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname '$$||current_database()||$$',
port '$$||current_setting('port')||$$',
use_remote_estimate 'true'
)$$;
END;
$d$;
CREATE USER MAPPING FOR PUBLIC SERVER server_test;
--creating data set for hash partition table
CREATE TABLE hp_tbl (a int, b int) PARTITION BY HASH(a custom_opclass_test);
CREATE TABLE hp_tbl_p1 PARTITION OF hp_tbl FOR VALUES WITH (modulus 4, remainder 0);
CREATE TABLE hp_tbl_p2 (like hp_tbl);
CREATE FOREIGN TABLE fhp_tbl_p2 PARTITION OF hp_tbl FOR VALUES WITH (modulus 4, remainder 1) SERVER server_test OPTIONS (TABLE_NAME 'hp_tbl_p2');
\d+ hp_tbl;\d+ hp_tbl_p2; \d+ fhp_tbl_p2
INSERT INTO hp_tbl VALUES (NULL,NULL);
INSERT INTO hp_tbl VALUES (4,4);
INSERT INTO hp_tbl_p2 VALUES (11,11);
INSERT INTO fhp_tbl_p2 VALUES (22,22);
SELECT tableoid::regclass AS partition, * FROM hp_tbl ORDER BY 1,2;
drop extension postgres_fdw cascade;
DROP TABLE hp_tbl;
DROP TABLE hp_tbl_p2;
DROP OPERATOR CLASS custom_opclass_test USING HASH;
DROP FUNCTION dummy_hashint4_test(int4, int8);
--test with pg dump/restore
--------------------------------------------------
create database regression_backup owner 'edb';
grant all on DATABASE regression_backup to edb;
\c regression_backup
CREATE SCHEMA schema_test_bkp;
SET search_path TO 'schema_test_bkp';
CREATE TABLE schema_test_bkp.hp_tbl (a int, b int, c int) PARTITION BY HASH (a);
-- custom_opclass_test);
CREATE TABLE schema_test_bkp.hp_tbl_p1 PARTITION OF schema_test_bkp.hp_tbl FOR VALUES WITH (modulus 4, remainder 0) PARTITION BY LIST (b);
CREATE TABLE schema_test_bkp.hp_tbl_p1_p1 PARTITION OF schema_test_bkp.hp_tbl_p1 FOR VALUES IN (0,4);
CREATE TABLE schema_test_bkp.hp_tbl_p1_p2 PARTITION OF schema_test_bkp.hp_tbl_p1 FOR VALUES IN (NULL,8);
CREATE TABLE schema_test_bkp.hp_tbl_p2 PARTITION OF schema_test_bkp.hp_tbl FOR VALUES WITH (modulus 4, remainder 1) PARTITION BY RANGE (b);
CREATE TABLE schema_test_bkp.hp_tbl_p2_p1 PARTITION OF schema_test_bkp.hp_tbl_p2 FOR VALUES FROM (1) TO (6);
CREATE TABLE schema_test_bkp.hp_tbl_p2_p2 PARTITION OF schema_test_bkp.hp_tbl_p2 FOR VALUES FROM (6) TO (11);
INSERT INTO schema_test_bkp.hp_tbl VALUES (NULL,NULL,NULL);
INSERT INTO schema_test_bkp.hp_tbl VALUES (4,4,4);
INSERT INTO schema_test_bkp.hp_tbl VALUES (8,8,8);
INSERT INTO schema_test_bkp.hp_tbl VALUES (9,9,9);
--check structure agaist regression_backup
\d+ schema_test_bkp.hp_tbl;\d+ schema_test_bkp.hp_tbl_p1;\d+ schema_test_bkp.hp_tbl_p1_p1;\d+ schema_test_bkp.hp_tbl_p1_p2;\d+ schema_test_bkp.hp_tbl_p2;\d+ schema_test_bkp.hp_tbl_p2_p1;\d+ schema_test_bkp.hp_tbl_p2_p2
--check data agaist regression_backup
SELECT tableoid::regclass AS partition, * FROM schema_test_bkp.hp_tbl ORDER BY 1,2;
--take plain dump of regression_backup
\! ./pg_dump -f dhp_test_test.tar -n schema_test_bkp -Ft -d regression_backup
RESET search_path;
\c postgres
drop database regression_backup;
create database regression_restore owner 'edb';
grant all on DATABASE regression_restore to edb;
--restore plain backup to regression_restore
\! ./pg_restore -d regression_restore dhp_test_test.tar -Ft
\c regression_restore
--check structure agaist regression_backup
\d+ schema_test_bkp.hp_tbl;\d+ schema_test_bkp.hp_tbl_p1;\d+ schema_test_bkp.hp_tbl_p1_p1;\d+ schema_test_bkp.hp_tbl_p1_p2;\d+ schema_test_bkp.hp_tbl_p2;\d+ schema_test_bkp.hp_tbl_p2_p1;\d+ schema_test_bkp.hp_tbl_p2_p2
--check data agaist regression_backup
SELECT tableoid::regclass AS partition, * FROM schema_test_bkp.hp_tbl ORDER BY 1,2;
RESET search_path;
\c postgres
drop database regression_restore;
\! rm dhp_test_test.tar
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
