CODEWITHSUNDEEP
postgresqlddlpostgresql-11
saadi
saadi

Reputation: 714

dropping a partioned table in postgres

I am trying to understand the behavior of Postgres partitions.
I have a partitioned table and I am trying to know what would be the behaviour if I

If I do a

DROP TABLE table_name;

Will it drop all the partitions too? Or will I need to first drop all the partitions and then drop the table?

Likewise, if I do a

ALTER INDEX RENAME temp_table_name_idx TO table_name_idx;

Will it be enough to rename index/indexes of a partitioned table? Because I came to know that each partition has its own index.

Similarly, if I do

ALTER TABLE table_name RENAME new_table_name;

Will the new_table_name already have all the partitions attached which were attached to table_name?

Upvotes: 1

Views: 4758

Answers (1)

SmartKeyerror
SmartKeyerror

Reputation: 409

We can use a very simple example to test it:

-- create parent partition table and child partition tables
create table foo(a int, b int) partition by range(a);
create table bar_01 partition of foo for values from (1) to (2);
create table bar_02 partition of foo for values from (2) to (3);
create table bar_03 partition of foo for values from (3) to (4);
insert into foo values (1), (2), (3);

The partition table in PostgreSQL is implemented by the Table Inheritance, we can find the inheritance relationship in pg_inherits:

postgres=# select inhrelid::regclass::text, inhparent::regclass::text from pg_inherits ;
 inhrelid | inhparent 
----------+-----------
 bar_01   | foo
 bar_02   | foo
 bar_03   | foo

So if we drop the table, the parent and it's all child tables will be dropped. But if we just rename the parent table, there is nothing happened in the child tables, because PostgreSQL uses OID to save their relationship, not a table name.

But the REINDEX statement will have different results between different versions.

PostgreSQL versions 12 and 13 are not supported reindex partition table, only version 14 supports it. And we can find the document from it's official website:

Reindexing partitioned indexes or partitioned tables is supported with REINDEX INDEX or REINDEX TABLE, respectively. Each partition of the specified partitioned relation is reindexed in a separate transaction. Those commands cannot be used inside a transaction block when working on a partitioned table or index.

sql-reindex

Upvotes: 4

Related Questions