PostgreSQL BETA1 partition and partition external table test instructions

1. Create a master table

CREATE TABLE Part_tab (c1 int, c2 int,name varchar) PARTITION by RANGE (C1);

2. Create sub-partition table

CREATE TABLE part1 PARTITION of Part_tab for VALUES from (0) to (100);

CREATE TABLE part2 PARTITION of Part_tab for VALUES from (+) to (200);

3. Attach a table that already exists as a sub-partition of the primary table

1). Attach partition (additional table partition)

--the table that is attached to the partition must already exist before the additional partition command is executed

CREATE TABLE Ext_part (c1 int NOT NULL, c2 int,name varchar (20));

Before attaching a partition, the data to be attached to the partition must meet the constraints of the primary table partition column, and the new partition cannot be attached to the primary table if the condition is not met.

ALTER TABLE part_tab ATTACH PARTITION Ext_part for VALUES from (in) to (500);

2). Detach partition (remove partition table)

---Unbind the partition, and the \d+ command shows that the partition table does not contain the removed partition.

ALTER TABLE part_tab DETACH PARTITION Ext_part;

4. Preparing the external server (child table server)

$ psql TestDB

# CREATE TABLE FPART3 (c1 int NOT NULL, c2 int,name varchar (20));

# CREATE TABLE FPART4 (c1 int NOT NULL, c2 int,name varchar (20));

testdb=# \dt

List of relations

Schema | Name |  Type | Owner

--------+--------+-------+----------

Public | Fpart3 | Table | Postgres

Public | Fpart4 | Table | Postgres

(2 rows)

testdb=#

5. Add Extensions

$ psql TestDB

# Create extension POSTGRES_FDW;

# Create server server_remote_226 foreign data wrapper POSTGRES_FDW options (host ' 172.16.3.226 ', Port ' 5432 ', dbname ' testd B ');

# Create user mapping for Postgres server server_remote_226 options (user ' postgres ', password ' 111111 ');

testdb=# \des+

List of foreign servers

Name | Owner | Foreign-data Wrapper | Access Privileges | Type |                     Version | FDW Options

| Description

-------------------+----------+----------------------+-------------------+------+---------+-------------------- ---------------------

------------+-------------

server_remote_226 | Postgres |                   POSTGRES_FDW |      |         | | (Host ' 172.16.3.226 ', Port ' 5432 ', Dbnam

E ' TestDB ') |

(1 row)

testdb=# \deu+

List of user Mappings

Server |              User name | FDW Options

-------------------+-----------+----------------------------------------

server_remote_226 | Postgres | ("User" ' Postgres ', password ' 111111 ')

(1 row)

6. Create an external table

CREATE FOREIGN TABLE part3 PARTITION of Part_tab for VALUES from ($) to SERVER server_remote_226 options (schema_n Ame ' public ', table_name ' fpart3 ');

CREATE FOREIGN TABLE part4 PARTITION of Part_tab for VALUES from (+) to (+) SERVER server_remote_226 options (schema_n Ame ' public ', table_name ' fpart4 ');

testdb=# \det+

List of foreign tables

Schema |      Table |                 Server | FDW Options | Description

--------+-------+-------------------+---------------------------------------------+-------------

Public | Part3 | server_remote_226 | (schema_name ' public ', table_name ' FPART3 ') |

Public | Part4 | server_remote_226 | (schema_name ' public ', table_name ' FPART4 ') |

(2 rows)

testdb=#

7. Inserting test data

--External partition table cannot insert data through main table, need to insert by external table

testdb=# INSERT INTO Part_tab values ("Chris"), (101,101, ' Peter '), (201,201, ' William '), (301,301, ' Feng ');

Error:cannot Route inserted tuples to a foreign table

testdb=#

Data is inserted in the respective server respectively

primary table server, inserting local partition data through the primary table

# INSERT INTO Part_tab values ("Chris"), (101,101, ' Peter ');

INSERT 0 2

testdb=#

External table server, inserted by external table separately

testdb=# INSERT into PART3 values (201,201, ' William ');

INSERT 0 1

testdb=# INSERT into PART4 values (301,301, ' Feng ');

INSERT 0 1

testdb=#

testdb=#

testdb=# select * from Part_tab;

C1 |  C2 | Name

-----+-----+---------

1 | 1 | Chris

101 | 101 | Peter

201 | 201 | William

301 | 301 | Feng

(4 rows)

testdb=#

The external partition table has no constraint on the insertion of partition columns, so that the data can be arbitrarily inserted.

testdb=# INSERT into PART4 values (201,301, ' Feng ');

INSERT 0 1

testdb=# select * from Part_tab;

C1 |  C2 | Name

-----+-----+---------

1 | 1 | Chris

101 | 101 | Peter

201 | 201 | William

301 | 301 | Feng

201 | 301 | Feng

(5 rows)

testdb=#

8. Adding primary keys and constraints

--Primary table partitioning column does not support establishing a PRIMARY KEY constraint

testdb=# ALTER TABLE PART_TAB add constraint Part_tab_c1_pkey primary key (C1);

Error:primary key constraints is not supported on partitioned tables

Line 1:alter table Part_tab add constraint Part_tab_c1_pkey primary ...

^

testdb=#

---constraints, indexes need to be added in the child table

--Primary table server

testdb=# ALTER TABLE PART1 ADD constraint Part1_c1_pkey primary key (C1);

ALTER TABLE

testdb=# ALTER TABLE PART2 add constraint Part2_c1_pkey primary key (C1);

ALTER TABLE

testdb=#

testdb=# CREATE index Idx_part1_c1_c2_name on part1 (c1,c2,name);

CREATE INDEX

testdb=# CREATE index Idx_part2_c1_c2_name on Part2 (c1,c2,name);

CREATE INDEX

testdb=#

testdb=# \d Part1

Table "Public.part1"

Column | Type | Collation | Nullable | Default

--------+-----------------------+-----------+----------+---------

C1 |           Integer | | NOT NULL |

C2 |           Integer |          | |

name |           Character varying (20) |          | |

Partition Of:part_tab for VALUES from (0) to (100)

Indexes:

"Part1_c1_pkey" PRIMARY KEY, Btree (C1)

"Idx_part1_c1_c2_name" Btree (c1, c2, name)

testdb=#

testdb=# \d Part2

Table "Public.part2"

Column | Type | Collation | Nullable | Default

--------+-----------------------+-----------+----------+---------

C1 |           Integer | | NOT NULL |

C2 |           Integer |          | |

name |           Character varying (20) |          | |

Partition Of:part_tab for VALUES from (+) to (200)

Indexes:

"Part2_c1_pkey" PRIMARY KEY, Btree (C1)

"Idx_part2_c1_c2_name" Btree (c1, c2, name)

testdb=#

--Child table server

testdb=# ALTER TABLE FPART3 add constraint Fpart3_c1_pkey primary key (C1);

ALTER TABLE

testdb=# ALTER TABLE FPART4 add constraint Fpart4_c1_pkey primary key (C1);

ALTER TABLE

testdb=# CREATE index Idx_fpart3_c1_c2_name on FPART3 (c1,c2,name);

CREATE INDEX

testdb=# CREATE index Idx_fpart4_c1_c2_name on FPART4 (c1,c2,name);

CREATE INDEX

testdb=#

testdb=# \d Fpart3

Table "Public.fpart3"

Column | Type | Collation | Nullable | Default

--------+-----------------------+-----------+----------+---------

C1 |           Integer | | NOT NULL |

C2 |           Integer |          | |

name |           Character varying (20) |          | |

Indexes:

"Fpart3_c1_pkey" PRIMARY KEY, Btree (C1)

"Idx_fpart3_c1_c2_name" Btree (c1, c2, name)

testdb=#

testdb=# \d Fpart4

Table "Public.fpart4"

Column | Type | Collation | Nullable | Default

--------+-----------------------+-----------+----------+---------

C1 |           Integer | | NOT NULL |

C2 |           Integer |          | |

name |           Character varying (20) |          | |

Indexes:

"Fpart4_c1_pkey" PRIMARY KEY, Btree (C1)

"Idx_fpart4_c1_c2_name" Btree (c1, c2, name)

testdb=#

9. Partition pruning of query statements

testdb=# explain analyze select * from Part_tab where c1=1;

QUERY PLAN

------------------------------------------------------------------------------------------------------

Append (cost=0.00..1.01 Rows=1 width=66) (actual time=0.009..0.009 Rows=1 Loops=1)

Seq Scan on Part1 (cost=0.00..1.01 Rows=1 width=66) (actual time=0.008..0.009 Rows=1 Loops=1)

Filter: (c1 = 1)

Planning time:0.234 MS

Execution time:0.027 ms

(5 rows)

testdb=# explain analyze select * from Part_tab where c1=101;

QUERY PLAN

------------------------------------------------------------------------------------------------------

Append (cost=0.00..1.01 Rows=1 width=66) (actual time=0.025..0.028 Rows=1 Loops=1)

Seq Scan on Part2 (cost=0.00..1.01 Rows=1 width=66) (actual time=0.024..0.026 Rows=1 Loops=1)

Filter: (c1 = 101)

Planning time:0.271 MS

Execution time:0.066 ms

(5 rows)

testdb=# explain analyze select * from Part_tab where c1=201;

QUERY PLAN

--------------------------------------------------------------------------------------------------------------

Append (cost=100.00..121.47 rows=5 width=66) (actual time=2.179..2.180 Rows=1 Loops=1)

-Foreign Scan on Part3 (cost=100.00..121.47 rows=5 width=66) (actual time=2.178..2.178 Rows=1 Loops=1)

Planning time:0.308 MS

Execution time:3.551 ms

(4 rows)

testdb=# explain analyze select * from Part_tab where c1=301;

QUERY PLAN

--------------------------------------------------------------------------------------------------------------

Append (cost=100.00..121.47 rows=5 width=66) (actual time=1.218..1.219 Rows=1 Loops=1)

-Foreign Scan on part4 (cost=100.00..121.47 rows=5 width=66) (actual time=1.217..1.218 Rows=1 Loops=1)

Planning time:0.312 MS

Execution time:2.178 ms

(4 rows)

testdb=# explain analyze select * from Part_tab where c1<201;

QUERY PLAN

--------------------------------------------------------------------------------------------------------------- -

Append (cost=0.00..129.46 rows=305 width=66) (actual time=0.014..2.881 rows=2 Loops=1)

Seq Scan on Part1 (cost=0.00..1.01 Rows=1 width=66) (actual time=0.014..0.015 Rows=1 Loops=1)

Filter: (C1 < 201)

Seq Scan on Part2 (cost=0.00..1.01 Rows=1 width=66) (actual time=0.009..0.009 Rows=1 Loops=1)

Filter: (C1 < 201)

-Foreign Scan on Part3 (cost=100.00..127.44 rows=303 width=66) (actual time=2.855..2.855 rows=0 Loops=1)

Planning time:0.234 MS

Execution time:3.884 ms

(8 rows)

testdb=#

This article is from the "Yiyi" blog, make sure to keep this source http://heyiyi.blog.51cto.com/205455/1927626

PostgreSQL BETA1 partition and partition external table test instructions