Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
cat test_part.sql
drop extension if exists pg_partman;
drop schema if exists partman cascade;
drop table if exists public.t cascade;
CREATE SCHEMA partman;
CREATE EXTENSION pg_partman WITH SCHEMA partman;
create table t(id int, col1 timestamp, primary key(id, col1)) partition by range(col1);
SELECT partman.create_parent( p_parent_table => 'public.t',
p_control => 'col1',
p_type => 'native',
p_interval=> '30 seconds',
p_premake => 2);
UPDATE partman.part_config SET infinite_time_partitions = true, retention = '30 seconds';
call partman.run_maintenance_proc();
\d+ public.t
select pg_sleep(30);
call partman.run_maintenance_proc();
\d+ public.t
drop extension if exists pg_partman;
drop schema if exists partman cascade;
drop table if exists public.t cascade;
postgres@db:~/playground$ cat test_part.sql
drop extension if exists pg_partman;
drop schema if exists partman cascade;
drop table if exists public.t cascade;
CREATE SCHEMA partman;
CREATE EXTENSION pg_partman WITH SCHEMA partman;
create table t(id int, col1 timestamp, primary key(id, col1)) partition by range(col1);
SELECT partman.create_parent( p_parent_table => 'public.t',
p_control => 'col1',
p_type => 'native',
p_interval=> '30 seconds',
p_premake => 2);
UPDATE partman.part_config SET infinite_time_partitions = true, retention = '30 seconds';
call partman.run_maintenance_proc();
\d+ public.t
select pg_sleep(30);
call partman.run_maintenance_proc();
\d+ public.t
drop extension if exists pg_partman;
drop schema if exists partman cascade;
drop table if exists public.t cascade;
#### OUTPUT
psql -Ppager test < test_part.sql
NOTICE: extension "pg_partman" does not exist, skipping
DROP EXTENSION
NOTICE: schema "partman" does not exist, skipping
DROP SCHEMA
NOTICE: table "t" does not exist, skipping
DROP TABLE
CREATE SCHEMA
CREATE EXTENSION
CREATE TABLE
create_parent
---------------
t
(1 row)
UPDATE 1
CALL
Partitioned table "public.t"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+-----------------------------+-----------+----------+---------+---------+-------------+--------------+-------------
id | integer | | not null | | plain | | |
col1 | timestamp without time zone | | not null | | plain | | |
Partition key: RANGE (col1)
Indexes:
"t_pkey" PRIMARY KEY, btree (id, col1)
Partitions: t_p2021_07_08_145930 FOR VALUES FROM ('2021-07-08 14:59:30') TO ('2021-07-08 15:00:00'),
t_p2021_07_08_150000 FOR VALUES FROM ('2021-07-08 15:00:00') TO ('2021-07-08 15:00:30'),
t_p2021_07_08_150030 FOR VALUES FROM ('2021-07-08 15:00:30') TO ('2021-07-08 15:01:00'),
t_p2021_07_08_150100 FOR VALUES FROM ('2021-07-08 15:01:00') TO ('2021-07-08 15:01:30'),
t_p2021_07_08_150130 FOR VALUES FROM ('2021-07-08 15:01:30') TO ('2021-07-08 15:02:00'),
t_default DEFAULT
pg_sleep
----------
(1 row)
CALL
Partitioned table "public.t"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+-----------------------------+-----------+----------+---------+---------+-------------+--------------+-------------
id | integer | | not null | | plain | | |
col1 | timestamp without time zone | | not null | | plain | | |
Partition key: RANGE (col1)
Indexes:
"t_pkey" PRIMARY KEY, btree (id, col1)
Partitions: t_p2021_07_08_150000 FOR VALUES FROM ('2021-07-08 15:00:00') TO ('2021-07-08 15:00:30'), --- NB new partition after 30s
t_p2021_07_08_150030 FOR VALUES FROM ('2021-07-08 15:00:30') TO ('2021-07-08 15:01:00'),
t_p2021_07_08_150100 FOR VALUES FROM ('2021-07-08 15:01:00') TO ('2021-07-08 15:01:30'),
t_p2021_07_08_150130 FOR VALUES FROM ('2021-07-08 15:01:30') TO ('2021-07-08 15:02:00'),
t_p2021_07_08_150200 FOR VALUES FROM ('2021-07-08 15:02:00') TO ('2021-07-08 15:02:30'),
t_default DEFAULT
DROP EXTENSION
NOTICE: drop cascades to table partman.template_public_t
DROP SCHEMA
DROP TABLE
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment