CREATE TABLE animaux (nom text PRIMARY KEY);
INSERT INTO animaux VALUES ('Éponge');
INSERT INTO animaux VALUES ('Ver de terre');

CREATE TABLE cephalopodes (nb_tentacules integer) INHERITS (animaux);
INSERT INTO cephalopodes VALUES ('Poulpe', 8);

CREATE TABLE vertebres (nb_membres integer) INHERITS (animaux);

CREATE TABLE tetrapodes () INHERITS (vertebres);
ALTER TABLE ONLY tetrapodes ALTER COLUMN nb_membres SET DEFAULT 4 ;

CREATE TABLE poissons (eau_douce boolean) INHERITS (tetrapodes);
INSERT INTO poissons (nom, eau_douce) VALUES ('Requin', false);
INSERT INTO poissons (nom, nb_membres, eau_douce) VALUES ('Anguille', 0, false);

\d+ poissons
                    Table "public.poissons"
   Column   |  Type   | Collation | Nullable | Default | …
------------+---------+-----------+----------+---------+---
 nom        | text    |           | not null |         | …
 nb_membres | integer |           |          | 4       | …
 eau_douce  | boolean |           |          |         | …
Inherits: tetrapodes
Access method: heap

CREATE TABLE reptiles (venimeux boolean) INHERITS (tetrapodes);
INSERT INTO reptiles VALUES ('Crocodile', 4, false);
INSERT INTO reptiles VALUES ('Cobra', 0, true);

CREATE TABLE mammiferes () INHERITS (tetrapodes);

CREATE TABLE cetartiodactyles (
    cornes boolean,
    bosse boolean
) INHERITS (mammiferes);
INSERT INTO cetartiodactyles VALUES ('Girafe', 4, true, false);
INSERT INTO cetartiodactyles VALUES ('Chameau', 4, false, true);

CREATE TABLE primates (debout boolean) INHERITS (mammiferes);
INSERT INTO primates (nom, debout) VALUES ('Chimpanzé', false);
INSERT INTO primates (nom, debout) VALUES ('Homme', true);

\d+ primates

                    Table "public.primates"
   Column   |  Type   | Collation | Nullable | Default | …
------------+---------+-----------+----------+---------+---
 nom        | text    |           | not null |         | …
 nb_membres | integer |           |          | 4       | …
 debout     | boolean |           |          |         | …
Inherits: mammiferes
Access method: heap

SELECT * FROM poissons ;

   nom    | nb_membres | eau_douce
----------+------------+-----------
 Requin   |          4 | f
 Anguille |          0 | f

SELECT * FROM animaux ORDER BY 1 ;

   nom
--------------
 Anguille
 Chameau
 Chimpanzé
 Cobra
 Crocodile
 Éponge
 Girafe
 Homme
 Poulpe
 Requin
 Ver de terre

SELECT * FROM tetrapodes ORDER BY 1 ;

    nom    | nb_membres
-----------+------------
 Anguille  |          0
 Chameau   |          4
 Chimpanzé |          4
 Cobra     |          0
 Crocodile |          4
 Girafe    |          4
 Homme     |          4
 Requin    |          4

EXPLAIN SELECT * FROM tetrapodes ORDER BY 1 ;

                                   QUERY PLAN
---------------------------------------------------------------------------------
 Sort  (cost=420.67..433.12 rows=4982 width=36)
   Sort Key: tetrapodes.nom
   ->  Append  (cost=0.00..114.71 rows=4982 width=36)
         ->  Seq Scan on tetrapodes  (cost=0.00..0.00 rows=1 width=36)
         ->  Seq Scan on poissons  (cost=0.00..22.50 rows=1250 width=36)
         ->  Seq Scan on reptiles  (cost=0.00..22.50 rows=1250 width=36)
         ->  Seq Scan on mammiferes  (cost=0.00..0.00 rows=1 width=36)
         ->  Seq Scan on cetartiodactyles  (cost=0.00..22.30 rows=1230 width=36)
         ->  Seq Scan on primates  (cost=0.00..22.50 rows=1250 width=36)

SELECT * FROM ONLY animaux ;

     nom
--------------
 Éponge
 Ver de terre

CREATE TABLE t3 (c1 integer, c2 text);
CREATE TABLE t3_1 (CHECK (c1 BETWEEN       0 AND  999999)) INHERITS (t3);
CREATE TABLE t3_2 (CHECK (c1 BETWEEN 1000000 AND 1999999)) INHERITS (t3);
CREATE TABLE t3_3 (CHECK (c1 BETWEEN 2000000 AND 2999999)) INHERITS (t3);
CREATE TABLE t3_4 (CHECK (c1 BETWEEN 3000000 AND 3999999)) INHERITS (t3);
CREATE TABLE t3_5 (CHECK (c1 BETWEEN 4000000 AND 4999999)) INHERITS (t3);
CREATE TABLE t3_6 (CHECK (c1 BETWEEN 5000000 AND 5999999)) INHERITS (t3);
CREATE TABLE t3_7 (CHECK (c1 BETWEEN 6000000 AND 6999999)) INHERITS (t3);
CREATE TABLE t3_8 (CHECK (c1 BETWEEN 7000000 AND 7999999)) INHERITS (t3);
CREATE TABLE t3_9 (CHECK (c1 BETWEEN 8000000 AND 8999999)) INHERITS (t3);
CREATE TABLE t3_0 (CHECK (c1 BETWEEN 9000000 AND 9999999)) INHERITS (t3);

-- Fonction et trigger de répartition pour les insertions :

CREATE OR REPLACE FUNCTION insert_into() RETURNS TRIGGER
LANGUAGE plpgsql
AS $FUNC$
BEGIN
  IF NEW.c1    BETWEEN       0 AND  999999 THEN
    INSERT INTO t3_1 VALUES (NEW.*);
  ELSIF NEW.c1 BETWEEN 1000000 AND 1999999 THEN
    INSERT INTO t3_2 VALUES (NEW.*);
  ELSIF NEW.c1 BETWEEN 2000000 AND 2999999 THEN
    INSERT INTO t3_3 VALUES (NEW.*);
  ELSIF NEW.c1 BETWEEN 3000000 AND 3999999 THEN
    INSERT INTO t3_4 VALUES (NEW.*);
  ELSIF NEW.c1 BETWEEN 4000000 AND 4999999 THEN
    INSERT INTO t3_5 VALUES (NEW.*);
  ELSIF NEW.c1 BETWEEN 5000000 AND 5999999 THEN
    INSERT INTO t3_6 VALUES (NEW.*);
  ELSIF NEW.c1 BETWEEN 6000000 AND 6999999 THEN
    INSERT INTO t3_7 VALUES (NEW.*);
  ELSIF NEW.c1 BETWEEN 7000000 AND 7999999 THEN
    INSERT INTO t3_8 VALUES (NEW.*);
  ELSIF NEW.c1 BETWEEN 8000000 AND 8999999 THEN
    INSERT INTO t3_9 VALUES (NEW.*);
  ELSIF NEW.c1 BETWEEN 9000000 AND 9999999 THEN
    INSERT INTO t3_0 VALUES (NEW.*);
  END IF;
  RETURN NULL;
END;
$FUNC$;

CREATE TRIGGER tr_insert_t3
BEFORE INSERT ON t3 FOR EACH ROW
EXECUTE PROCEDURE insert_into();

CREATE TABLE t2 (c1 integer, c2 text) PARTITION BY RANGE (c1);
CREATE TABLE t2_1 PARTITION OF t2 FOR VALUES FROM (      0) TO ( 1000000);
CREATE TABLE t2_2 PARTITION OF t2 FOR VALUES FROM (1000000) TO ( 2000000);
CREATE TABLE t2_3 PARTITION OF t2 FOR VALUES FROM (2000000) TO ( 3000000);
CREATE TABLE t2_4 PARTITION OF t2 FOR VALUES FROM (3000000) TO ( 4000000);
CREATE TABLE t2_5 PARTITION OF t2 FOR VALUES FROM (4000000) TO ( 5000000);
CREATE TABLE t2_6 PARTITION OF t2 FOR VALUES FROM (5000000) TO ( 6000000);
CREATE TABLE t2_7 PARTITION OF t2 FOR VALUES FROM (6000000) TO ( 7000000);
CREATE TABLE t2_8 PARTITION OF t2 FOR VALUES FROM (7000000) TO ( 8000000);
CREATE TABLE t2_9 PARTITION OF t2 FOR VALUES FROM (8000000) TO ( 9000000);
CREATE TABLE t2_0 PARTITION OF t2 FOR VALUES FROM (9000000) TO (10000000);

CREATE TABLE t1(c1 integer, c2 text) PARTITION BY LIST (c1);

CREATE TABLE t1_a PARTITION OF t1 FOR VALUES IN (1, 2, 3);
CREATE TABLE t1_b PARTITION OF t1 FOR VALUES IN (4, 5);

INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2);
INSERT INTO t1 VALUES (5);
SELECT tableoid::regclass, * FROM t1;

 tableoid | c1 | c2
----------+----+----
 t1_a     |  1 |
 t1_a     |  2 |
 t1_b     |  5 |

SELECT * FROM t1_a ;

 c1 | c2
----+----
  1 |
  2 |

INSERT INTO t1 VALUES (0);

ERROR:  no PARTITION OF relation "t1" found for row
DETAIL:  Partition key of the failing row contains (c1) = (0).

INSERT INTO t1 VALUES (6);

ERROR:  no PARTITION OF relation "t1" found for row
DETAIL:  Partition key of the failing row contains (c1) = (6).

CREATE TABLE t2(c1 integer, c2 text) PARTITION BY RANGE (c1);
CREATE TABLE t2_1 PARTITION OF t2 FOR VALUES FROM (1) to (100);
CREATE TABLE t2_2 PARTITION OF t2 FOR VALUES FROM (100) TO (MAXVALUE);

INSERT INTO t2 VALUES (0);

ERROR:  no PARTITION OF relation "t2" found for row
DETAIL:  Partition key of the failing row contains (c1) = (0).

INSERT INTO t2 VALUES (10, 'dix');

INSERT INTO t2 VALUES (100, 'cent');
INSERT INTO t2 VALUES (10000, 'dix mille');
SELECT * FROM t2 ;

  c1   |    c2
-------+-----------
    10 | dix
   100 | cent
 10000 | dix mille
(3 lignes)

SELECT * FROM t2_2 ;

  c1   |    c2
-------+-----------
   100 | cent
 10000 | dix mille
(2 lignes)

SELECT ctid, tableoid::regclass, * FROM t2 ;

 ctid  | tableoid |  c1   |    c2
-------+----------+-------+-----------
 (0,1) | t2_1     |    10 | dix
 (0,1) | t2_2     |   100 | cent
 (0,2) | t2_2     | 10000 | dix mille

CREATE TABLE t3(c1 integer, c2 text) PARTITION BY HASH (c1);
CREATE TABLE t3_1 PARTITION OF t3 FOR VALUES WITH (modulus 3, remainder 0);
CREATE TABLE t3_2 PARTITION OF t3 FOR VALUES WITH (modulus 3, remainder 1);
CREATE TABLE t3_3 PARTITION OF t3 FOR VALUES WITH (modulus 3, remainder 2);

INSERT INTO t3 SELECT generate_series(1, 1000000);

SELECT relname, count(*) FROM t3
JOIN pg_class ON t3.tableoid=pg_class.oid
GROUP BY 1;

 relname | count
---------+--------
 t3_1    | 333263
 t3_2    | 333497
 t3_3    | 333240

CREATE TABLESPACE ts0 LOCATION '/tablespaces/ts0';
CREATE TABLESPACE ts1 LOCATION '/tablespaces/ts1';
CREATE TABLESPACE ts2 LOCATION '/tablespaces/ts2';
CREATE TABLESPACE ts3 LOCATION '/tablespaces/ts3';

CREATE TABLE t2(c1 integer, c2 text, c3 date not null)
       PARTITION BY RANGE (c1, c3);
CREATE TABLE t2_1 PARTITION OF t2
       FOR VALUES FROM (1,'2017-08-10') TO (100, '2017-08-11')
       TABLESPACE ts1;
CREATE TABLE t2_2 PARTITION OF t2
       FOR VALUES FROM (100,'2017-08-11') TO (200, '2017-08-12')
       TABLESPACE ts2;

INSERT INTO t2 VALUES (1, 'test', '2017-08-10');
INSERT INTO t2 VALUES (150, 'test2', '2017-08-11');

INSERT INTO t2 VALUES (0, 'test', '2017-08-10');

ERROR:  no partition of relation "t2" found for row
DÉTAIL : Partition key of the failing row contains (c1, c3) = (0, 2017-08-10).

INSERT INTO t2 VALUES (1, 'test', '2017-08-09');

ERROR:  no partition of relation "t2" found for row
DÉTAIL : Partition key of the failing row contains (c1, c3) = (1, 2017-08-09).

CREATE TABLE t2_0 PARTITION OF t2
       FOR VALUES FROM (MINVALUE, MINVALUE) TO (1,'2017-08-10')
       TABLESPACE ts0;

CREATE TABLE t2_3 PARTITION OF t2
       FOR VALUES FROM (200,'2017-08-12') TO (MAXVALUE, MAXVALUE)
       TABLESPACE ts3;

ANALYZE t2;
SELECT relname,relispartition,relkind,reltuples
FROM pg_class WHERE relname LIKE 't2%';

 relname | relispartition | relkind | reltuples
---------+----------------+---------+-----------
 t2      | f              | p       |         0
 t2_0    | t              | r       |         2
 t2_1    | t              | r       |         1
 t2_2    | t              | r       |         1
 t2_3    | t              | r       |         0

\dt objets*
                     Liste des relations
 Schéma |       Nom       |        Type        | Propriétaire 
--------+-----------------+--------------------+--------------
 public | objets          | table partitionnée | postgres
 public | objets_123      | table partitionnée | postgres
 public | objets_123_2023 | table              | postgres
 public | objets_123_2024 | table              | postgres
 public | objets_45       | table              | postgres

EXPLAIN (COSTS OFF) SELECT * FROM objets
WHERE annee = 2023 ;

                 QUERY PLAN                 
--------------------------------------------
 Append
   ->  Seq Scan on objets_123_2023 objets_1
         Filter: (annee = 2023)
   ->  Seq Scan on objets_45 objets_2
         Filter: (annee = 2023)

EXPLAIN (COSTS OFF) SELECT * FROM objets
WHERE statut = 3 ;

                 QUERY PLAN                 
--------------------------------------------
 Append
   ->  Seq Scan on objets_123_2023 objets_1
         Filter: (statut = 3)
   ->  Seq Scan on objets_123_2024 objets_2
         Filter: (statut = 3)

EXPLAIN (COSTS OFF) SELECT * FROM objets
WHERE statut = 3 AND annee = 2024 ;

                 QUERY PLAN                  
---------------------------------------------
 Seq Scan on objets_123_2024 objets
   Filter: ((statut = 3) AND (annee = 2024))

CREATE TABLE t1(c1 integer, c2 text) PARTITION BY LIST (c1);
CREATE TABLE t1_a PARTITION OF t1 FOR VALUES IN (1, 2, 3);
CREATE TABLE t1_b PARTITION OF t1 FOR VALUES IN (4, 5);

INSERT INTO t1 VALUES (0);

ERROR:  no PARTITION OF relation "t1" found for row
DETAIL:  Partition key of the failing row contains (c1) = (0).

INSERT INTO t1 VALUES (6);

ERROR:  no PARTITION OF relation "t1" found for row
DETAIL:  Partition key of the failing row contains (c1) = (6).

-- partition par défaut
CREATE TABLE t1_defaut PARTITION OF t1 DEFAULT ;
-- on réessaie l'insertion
INSERT INTO t1 VALUES (0);
INSERT INTO t1 VALUES (6);

SELECT tableoid::regclass, * FROM t1;

 tableoid  | c1 | c2
-----------+----+----
 t1_a      |  1 |
 t1_a      |  2 |
 t1_b      |  5 |
 t1_defaut |  0 |
 t1_defaut |  6 |

\set ECHO all
\set timing off

DROP TABLE IF EXISTS t1 ;

-- Une table partitionnée avec partition par défaut
CREATE TABLE t1 (c1 integer, filler char (10)) PARTITION BY LIST (c1);
CREATE TABLE t1_123 PARTITION OF t1 FOR VALUES IN (1, 2, 3);
CREATE TABLE t1_45 PARTITION OF t1 FOR VALUES IN (4, 5);
CREATE TABLE t1_default  PARTITION OF t1 DEFAULT ;

-- Données d'origine
INSERT INTO t1 SELECT 1+mod(i,5) FROM generate_series (1,5000000) i;
-- Les données sont bien dans les partitions t1_123 et t1_45
SELECT tableoid::regclass, c1, count(*) FROM t1
GROUP BY 1,2 ORDER BY c1 ;

-- Création d'une table pour les valeurs 6 à attacher
CREATE TABLE t1_6 (LIKE t1 INCLUDING ALL) ;
INSERT INTO t1_6 SELECT 6 FROM generate_series (1,1000000);

\dt+ t1*

\timing on
-- on attache la table : elle est scannée, ce qui est long
ALTER TABLE t1 ATTACH PARTITION t1_6 FOR VALUES IN (6) ;
-- noter la nouvelle contrainte sur la table
\d+ t1_6
-- on la détache, la contrainte a disparu
ALTER TABLE t1 DETACH PARTITION t1_6 ;
\d+ t1_6

-- on remet manuellement la même contrainte que ci-dessus
-- (ce qui reste long mais ne pose pas de verrou sur t1)
ALTER TABLE t1_6 ADD CONSTRAINT t1_6_ck CHECK(c1 IS NOT NULL AND c1 = 6) ;
\d+ t1_6
-- l'ATTACH est cette fois presque instantané
ALTER TABLE t1 ATTACH PARTITION t1_6 FOR VALUES IN (6) ;

\timing off

-- On insère par erreur des valeurs 7 sans avoir fait la partition
-- (et sans avoir le droit de les enlever de t1 ensuite)
INSERT INTO t1 SELECT 7 FROM generate_series (1,100);
-- Créer la partition échoue avec "constraint for default partition "t1_default" would be violated""
CREATE TABLE t1_7 PARTITION OF t1 FOR VALUES IN (7);

-- Pour corriger cela, au sein d'une transaction,
-- on transfére les données de la partition par défaut
-- vers une nouvelle table qui est ensuite attachée
CREATE TABLE t1_7 (LIKE t1 INCLUDING ALL) ;
ALTER TABLE t1_7 ADD CONSTRAINT t1_7_ck CHECK(c1 IS NOT NULL AND c1 = 7) ;
BEGIN ;
    INSERT INTO t1_7 SELECT * FROM t1_default WHERE c1=7 ;
    DELETE FROM t1_default WHERE c1=7 ;
    ALTER TABLE t1 ATTACH PARTITION t1_7 FOR VALUES IN (7) ;
COMMIT ;

-- Table partitionnée
CREATE TABLE logs (dreception timestamptz, contenu text) PARTITION BY RANGE(dreception);
-- Partition 2018, elle-même partitionnée
CREATE TABLE logs_2018 PARTITION OF logs FOR VALUES FROM ('2018-01-01') TO ('2019-01-01')
                       PARTITION BY range(dreception);
-- Sous-partitions 2018
CREATE TABLE logs_201801 PARTITION OF logs_2018 FOR VALUES FROM ('2018-01-01') TO ('2018-02-01');
CREATE TABLE logs_201802 PARTITION OF logs_2018 FOR VALUES FROM ('2018-02-01') TO ('2018-03-01');
…
-- Idem en 2019
CREATE TABLE logs_2019 PARTITION OF logs FOR VALUES FROM ('2019-01-01') TO ('2020-01-01')
                       PARTITION BY range(dreception);
CREATE TABLE logs_201901 PARTITION OF logs_2019 FOR VALUES FROM ('2019-01-01') TO ('2019-02-01');
…

SELECT pg_partition_root('logs_2019');

 pg_partition_root
-------------------
 logs

SELECT pg_partition_root('logs_201901');

 pg_partition_root
-------------------
 logs

SELECT pg_partition_ancestors('logs_2018');

 pg_partition_ancestors
------------------------
 logs_2018
 logs

SELECT pg_partition_ancestors('logs_201901');

 pg_partition_ancestors
------------------------
 logs_201901
 logs_2019
 logs

SELECT * FROM pg_partition_tree('logs');

    relid    | parentrelid | isleaf | level
-------------+-------------+--------+-------
 logs        |             | f      |     0
 logs_2018   | logs        | f      |     1
 logs_2019   | logs        | f      |     1
 logs_201801 | logs_2018   | t      |     2
 logs_201802 | logs_2018   | t      |     2
 logs_201901 | logs_2019   | t      |     2

=# \dP
                Liste des relations partitionnées
 Schéma |   Nom    | Propriétaire |        Type        |  Table
--------+----------+--------------+--------------------+----------
 public | logs     | postgres     | table partitionnée |
 public | t2       | postgres     | index partitionné  | bigtable

-- On voudrait partitionner ainsi mais le moteur refuse
CREATE TABLE factures_p
                  (id bigint PRIMARY KEY,
                   d timestamptz,
                   id_client int NOT NULL,
                   montant_c int NOT NULL DEFAULT 0)
PARTITION BY RANGE (d);

ERROR:  unique constraint on partitioned table must include all partitioning columns
DÉTAIL : PRIMARY KEY constraint on table "factures_p" lacks column "d" which is part of the partition key.

-- On se rabat sur une clé primaire incluant la date
CREATE TABLE factures_p
                  (id bigint NOT NULL,
                   d timestamptz NOT NULL,
                   id_client int,
                   montant_c int,
                   PRIMARY KEY (id, d)
                   )
PARTITION BY RANGE (d);

CREATE TABLE factures_p_202310 PARTITION OF factures_p
  FOR VALUES FROM ('2023-10-01') TO ('2023-11-01');
CREATE TABLE factures_p_202311 PARTITION OF factures_p
  FOR VALUES FROM ('2023-11-01') TO ('2023-12-01');
ALTER TABLE factures_p_202310 ADD CONSTRAINT factures_p_202310_uq UNIQUE (id);
-- Ces contraintes sécurisent les clés primaire au niveau partition
ALTER TABLE factures_p_202311 ADD CONSTRAINT factures_p_202311_uq UNIQUE (id);
-- Ajout de quelques lignes de 1 à 5 sur les deux partitions
INSERT INTO factures_p (id, d, id_client)
SELECT i, '2023-10-26'::timestamptz+i*interval '2 days', 42 FROM generate_series (1,5) i;

BEGIN ;
-- Ce doublon est accepté car les deux valeurs 3 ne sont pas dans la même partition
INSERT INTO factures_p (id, d, id_client)
SELECT 3, '2023-11-01'::timestamptz-interval '1s', 42 ;
-- Vérification que 3 est en double
SELECT tableoid::regclass, id, d FROM factures_p ORDER BY id ;
ROLLBACK ;

-- Cette table permet de garantir l'unicité dans toutes les partitions
CREATE TABLE factures_ref (id bigint NOT NULL PRIMARY KEY,
                       d timestamptz NOT NULL,
                       UNIQUE (id,d) -- nécessaire pour la contrainte
                       )  ;

INSERT INTO factures_ref SELECT id,d FROM factures_p ;

-- Contrainte depuis la table partitionnée
ALTER TABLE factures_p ADD CONSTRAINT factures_p_id_fk
FOREIGN KEY (id, d)  REFERENCES factures_ref (id,d);

-- Par la suite, il faut insérer chaque nouvelle valeur de `id`
-- dans les deux tables
-- Ce doublon est à présent correctement rejeté :
WITH ins AS (
    INSERT INTO factures_p (id, d, id_client)
    SELECT 3, '2023-11-01'::timestamptz-interval '1s', 42
    RETURNING id,d )
INSERT INTO factures_ref
SELECT id, d FROM ins ;

ERROR:  duplicate key value violates unique constraint "factures_ref_pkey"
DÉTAIL : Key (id)=(3) already exists.

ALTER TABLE lignes_ventes
ADD CONSTRAINT lignes_ventes_ventes_fk
FOREIGN KEY (vente_id) REFERENCES ventes (vente_id) ;

EXPLAIN SELECT * FROM pgbench_accounts WHERE aid + 0 = 123 LIMIT 1 ;

                                   QUERY PLAN
---------------------------------------------------------------------------------
 Limit  (cost=0.00..6.29 rows=1 width=97)
   ->  Append  (cost=0.00..314250.00 rows=50000 width=97)
         ->  Seq Scan on pgbench_accounts_1  (cost=0.00..3140.00 rows=500 width=97)
               Filter: ((aid + 0) = 123)
         ->  Seq Scan on pgbench_accounts_2  (cost=0.00..3140.00 rows=500 width=97)
               Filter: ((aid + 0) = 123)
        …
        …
               Filter: ((aid + 0) = 123)
         ->  Seq Scan on pgbench_accounts_99  (cost=0.00..3140.00 rows=500 width=97)
               Filter: ((aid + 0) = 123)
         ->  Seq Scan on pgbench_accounts_100  (cost=0.00..3140.00 rows=500 width=97)
               Filter: ((aid + 0) = 123)

EXPLAIN SELECT * FROM pgbench_accounts WHERE aid = 123 LIMIT 1 ;

                                   QUERY PLAN
---------------------------------------------------------------------------------
 Limit  (cost=0.29..8.31 rows=1 width=97)
   ->  Index Scan using pgbench_accounts_1_pkey on pgbench_accounts_1 pgbench_accounts  (cost=0.29..8.31 rows=1 width=97)
         Index Cond: (aid = 123)

EXPLAIN (ANALYZE,COSTS OFF)
SELECT * FROM pgbench_accounts WHERE aid = (SELECT (random()*1000000)::int )  ;

                                   QUERY PLAN
---------------------------------------------------------------------------------
 Append (actual time=23.083..23.101 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Result (actual time=0.001..0.002 rows=1 loops=1)
   ->  Index Scan using pgbench_accounts_1_pkey on pgbench_accounts_1 (never executed)
         Index Cond: (aid = $0)
   ->  Index Scan using pgbench_accounts_2_pkey on pgbench_accounts_2 (never executed)
         Index Cond: (aid = $0)
   ->  Index Scan using pgbench_accounts_3_pkey on pgbench_accounts_3 (never executed)
         Index Cond: (aid = $0)
   ->  Index Scan using pgbench_accounts_4_pkey on pgbench_accounts_4 (never executed)
         Index Cond: (aid = $0)
   ->  Index Scan using pgbench_accounts_5_pkey on pgbench_accounts_5 (never executed)
         Index Cond: (aid = $0)
   ->  Index Scan using pgbench_accounts_6_pkey on pgbench_accounts_6 (never executed)
         Index Cond: (aid = $0)
   ->  Index Scan using pgbench_accounts_7_pkey on pgbench_accounts_7 (never executed)
         Index Cond: (aid = $0)
   ->  Index Scan using pgbench_accounts_8_pkey on pgbench_accounts_8 (actual time=23.077..23.080 rows=1 loops=1)
         Index Cond: (aid = $0)
   ->  Index Scan using pgbench_accounts_9_pkey on pgbench_accounts_9 (never executed)
         Index Cond: (aid = $0)
   …
   …
   ->  Index Scan using pgbench_accounts_100_pkey on pgbench_accounts_100 (never executed)
         Index Cond: (aid = $0)
 Planning Time: 1.118 ms
 Execution Time: 23.341 ms

-- Base pgbench de taille 100, non partitionnée
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT * FROM pgbench_accounts WHERE aid = 123 LIMIT 1 ;

                                   QUERY PLAN
---------------------------------------------------------------------------------
 Limit (actual time=0.021..0.022 rows=1 loops=1)
   Buffers: shared hit=4
   ->  Index Scan using pgbench_accounts_pkey on pgbench_accounts (actual time=0.021..0.021 rows=1 loops=1)
         Index Cond: (aid = 123)
         Buffers: shared hit=4
 Planning:
   Buffers: shared hit=70
 Planning Time: 0.358 ms
 Execution Time: 0.063 ms

-- Base pgbench de taille 100, partitionnée en 100 partitions
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT * FROM pgbench_accounts WHERE aid = 123 LIMIT 1 ;

                                   QUERY PLAN
---------------------------------------------------------------------------------
 Limit (actual time=0.015..0.016 rows=1 loops=1)
   Buffers: shared hit=3
   ->  Index Scan using pgbench_accounts_1_pkey on pgbench_accounts_1 pgbench_accounts (actual time=0.015..0.015 rows=1 loops=1)
         Index Cond: (aid = 123)
         Buffers: shared hit=3
 Planning:
   Buffers: shared hit=423
 Planning Time: 1.030 ms
 Execution Time: 0.061 ms

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT * FROM pgbench_accounts_1 WHERE aid = 123 LIMIT 1 ;

                                   QUERY PLAN
---------------------------------------------------------------------------------
 Limit (actual time=0.006..0.007 rows=1 loops=1)
   Buffers: shared hit=3
   ->  Index Scan using pgbench_accounts_1_pkey on pgbench_accounts_1 (actual time=0.006..0.006 rows=1 loops=1)
         Index Cond: (aid = 123)
         Buffers: shared hit=3
 Planning Time: 0.046 ms
 Execution Time: 0.016 ms

EXPLAIN (BUFFERS, COSTS OFF, SUMMARY ON) SELECT *
FROM pgbench_accounts a INNER JOIN pgbench_accounts b USING (aid)
WHERE a.bid = 55 ;

                                   QUERY PLAN
---------------------------------------------------------------------------------
 Gather
   Workers Planned: 4
   ->  Nested Loop
         ->  Parallel Seq Scan on pgbench_accounts a
               Filter: (bid = 55)
         ->  Index Scan using pgbench_accounts_pkey on pgbench_accounts b
               Index Cond: (aid = a.aid)
 Planning:
   Buffers: shared hit=16
 Planning Time: 0.168 ms

 Gather
   Workers Planned: 4
   ->  Parallel Hash Join
         Hash Cond: (b.aid = a.aid)
         ->  Parallel Append
               ->  Parallel Seq Scan on pgbench_accounts_1 b_1
               ->  Parallel Seq Scan on pgbench_accounts_2 b_2
               …
               ->  Parallel Seq Scan on pgbench_accounts_99 b_99
               ->  Parallel Seq Scan on pgbench_accounts_100 b_100
         ->  Parallel Hash
               ->  Parallel Append
                     ->  Parallel Seq Scan on pgbench_accounts_1 a_1
                           Filter: (bid = 55)
                     ->  Parallel Seq Scan on pgbench_accounts_2 a_2
                           Filter: (bid = 55)
                     …
                     ->  Parallel Seq Scan on pgbench_accounts_99 a_99
                           Filter: (bid = 55)
                     ->  Parallel Seq Scan on pgbench_accounts_100 a_100
                           Filter: (bid = 55)
 Planning Time: 5.513 ms

SET enable_partitionwise_join TO on ;

Gather
   Workers Planned: 4
   ->  Parallel Append
         ->  Parallel Hash Join
               Hash Cond: (a_55.aid = b_55.aid)
               ->  Parallel Seq Scan on pgbench_accounts_55 a_55
                     Filter: (bid = 55)
               ->  Parallel Hash
                     ->  Parallel Seq Scan on pgbench_accounts_55 b_55
…
         ->  Nested Loop
               ->  Parallel Seq Scan on pgbench_accounts_100 a_100
                     Filter: (bid = 55)
               ->  Index Scan using pgbench_accounts_100_pkey on pgbench_accounts_100 b_100
                     Index Cond: (aid = a_100.aid)
 Planning:
   Buffers: shared hit=1200
 Planning Time: 12.449 ms

SET enable_partitionwise_aggregate TO on ;

ALTER TABLE pgbench_accounts_8 SET TABLESPACE hdd ;

ANALYZE table_partitionnée ;

-- Maquette rapide sous psql de sharding
-- avec trois bases demosharding_fr , _de, _es
-- et une base globale pour le requêtage

\set timing off
\set ECHO all
\set ON_ERROR_STOP 1

\connect postgres postgres serveur1
DROP DATABASE IF EXISTS demosharding_fr ;
CREATE DATABASE demosharding_fr ;
ALTER DATABASE demosharding_fr SET log_min_duration_statement TO 0 ;
\connect postgres postgres serveur2
DROP DATABASE IF EXISTS demosharding_de ;
CREATE DATABASE demosharding_de ;
ALTER DATABASE demosharding_de SET log_min_duration_statement TO 0 ;
\connect postgres postgres serveur3
DROP DATABASE IF EXISTS demosharding_es ;
CREATE DATABASE demosharding_es ;
ALTER DATABASE demosharding_es SET log_min_duration_statement TO 0 ;

\connect postgres postgres serveur4
DROP DATABASE IF EXISTS demosharding_global ;
CREATE DATABASE demosharding_global ;
ALTER DATABASE demosharding_global SET log_min_duration_statement TO 0 ;

-- Tables identiques sur chaque serveur
\connect demosharding_fr postgres serveur1

CREATE TABLE clients    (id_client  int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
                        nom         text,
                        pays        char (2) DEFAULT 'FR' CHECK (pays = 'FR')
                        ) ;
CREATE TABLE commandes (id_commande  bigint GENERATED ALWAYS AS IDENTITY  PRIMARY KEY,
                        pays         char (2) DEFAULT 'FR'  CHECK (pays = 'FR'),
                        id_client    int REFERENCES clients ,
                        montant      float
                        );

\connect demosharding_de postgres serveur2

CREATE TABLE clients    (id_client  int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
                        nom         text,
                        pays        char (2) DEFAULT 'DE'  CHECK (pays = 'DE')
                        ) ;
CREATE TABLE commandes (id_commande  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
                        pays         char (2) DEFAULT 'DE'  CHECK (pays = 'DE') ,
                        id_client    int  REFERENCES clients,
                        montant      float
                        );

\connect demosharding_es postgres serveur3

CREATE TABLE clients    (id_client  int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
                        nom         text,
                        pays        char (2) DEFAULT 'ES'  CHECK (pays = 'ES')
                        ) ;
CREATE TABLE commandes (id_commande  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
                        pays         char (2) DEFAULT 'ES'  CHECK (pays = 'ES'),
                        id_client    int REFERENCES clients ,
                        montant      float
                        );

-- Tables partitionnées globales
\connect demosharding_global postgres serveur4

CREATE TABLE clients (id_client int, nom text, pays char(2))
PARTITION BY LIST (pays);
CREATE TABLE commandes (id_commande bigint, pays char(2),
                        id_client int, montant float)
PARTITION BY LIST (pays);

-- Serveurs distants (adapter les chaines de connexion)
-- NB : l'option async_capable n existe pas avant PostgreSQL 14
CREATE EXTENSION postgres_fdw ;

CREATE SERVER dist_fr
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', dbname 'demosharding_fr', port '16001',
async_capable 'on', fetch_size '10000') ;

CREATE SERVER dist_de
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', dbname 'demosharding_de', port '16001',
async_capable 'on', fetch_size '10000') ;

CREATE SERVER dist_es
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', dbname 'demosharding_es', port '16001',
async_capable 'on', fetch_size '10000') ;

CREATE USER MAPPING IF NOT EXISTS FOR current_user SERVER dist_fr ;
CREATE USER MAPPING IF NOT EXISTS FOR current_user SERVER dist_de ;
CREATE USER MAPPING IF NOT EXISTS FOR current_user SERVER dist_es ;

-- Les partitions distantes
CREATE FOREIGN TABLE clients_fr PARTITION OF clients FOR VALUES IN ('FR')
SERVER dist_fr OPTIONS (table_name 'clients') ;
CREATE FOREIGN TABLE clients_de PARTITION OF clients FOR VALUES IN ('DE')
SERVER dist_de OPTIONS (table_name 'clients') ;
CREATE FOREIGN TABLE clients_es PARTITION OF clients FOR VALUES IN ('ES')
SERVER dist_es OPTIONS (table_name 'clients') ;

CREATE FOREIGN TABLE commandes_fr PARTITION OF commandes FOR VALUES IN ('FR')
SERVER dist_fr OPTIONS (table_name 'commandes') ;
CREATE FOREIGN TABLE commandes_de PARTITION OF commandes FOR VALUES IN ('DE')
SERVER dist_de OPTIONS (table_name 'commandes') ;
CREATE FOREIGN TABLE commandes_es PARTITION OF commandes FOR VALUES IN ('ES')
SERVER dist_es OPTIONS (table_name 'commandes') ;

-- Alimentations des pays (séparément)

\connect demosharding_fr postgres serveur1

WITH ins_clients AS (
    INSERT INTO clients (nom)
    SELECT md5 (random()::text) FROM generate_series (1,10) i
    WHERE random()<0.8
RETURNING id_client
),
ins_commandes AS (
    INSERT INTO commandes (id_client, montant)
    SELECT c.id_client, random()*j::float
    FROM ins_clients c CROSS JOIN generate_series (1,100000) j
    WHERE random()<0.8
RETURNING *
)
SELECT count(*) FROM  ins_commandes ;

\connect demosharding_de postgres serveur2

\g

\connect demosharding_es postgres serveur3

\g

\connect demosharding_global postgres serveur4

-- Les ANALYZE redescendent sur les partitions
ANALYZE (VERBOSE) clients, commandes ;

-- Pour un plan optimal
SET enable_partitionwise_join TO on ;
SET enable_partitionwise_aggregate TO on ;

-- Requête globale : top 8 des clients
-- Plan disponible sur https://explain.dalibo.com/plan/27f964651518a65g

SELECT pays,
       nom,
       count(DISTINCT id_commande) AS nb_commandes,
       avg(montant) AS montant_avg_commande,
       sum(montant) AS montant_sum
FROM
    commandes INNER JOIN clients USING (pays, id_client)
GROUP BY 1,2
ORDER BY montant_sum DESC
LIMIT 8 ;

…user=postgres,db=demosharding_de,app=postgres_fdw,client=::1
LOG:  duration: 0.384 ms  execute <unnamed>: DECLARE c1 CURSOR FOR
        SELECT id_commande, pays, id_client, montant FROM public.commandes
…user=postgres,db=demosharding_es,app=postgres_fdw,client=::1
LOG:  duration: 0.314 ms  execute <unnamed>: DECLARE c2 CURSOR FOR
        SELECT id_commande, pays, id_client, montant FROM public.commandes
…user=postgres,db=demosharding_fr,app=postgres_fdw,client=::1
LOG:  duration: 0.374 ms  execute <unnamed>: DECLARE c3 CURSOR FOR
        SELECT id_commande, pays, id_client, montant FROM public.commandes
…user=postgres,db=demosharding_de,app=postgres_fdw,client=::1
LOG:  duration: 6.081 ms  statement: FETCH 10000 FROM c1
…user=postgres,db=demosharding_es,app=postgres_fdw,client=::1
LOG:  duration: 5.878 ms  statement: FETCH 10000 FROM c2
…user=postgres,db=demosharding_fr,app=postgres_fdw,client=::1
LOG:  duration: 6.263 ms  statement: FETCH 10000 FROM c3
…user=postgres,db=demosharding_de,app=postgres_fdw,client=::1
LOG:  duration: 2.418 ms  statement: FETCH 10000 FROM c1
…user=postgres,db=demosharding_de,app=postgres_fdw,client=::1
LOG:  duration: 2.397 ms  statement: FETCH 10000 FROM c1
…user=postgres,db=demosharding_es,app=postgres_fdw,client=::1
LOG:  duration: 2.423 ms  statement: FETCH 10000 FROM c2
…user=postgres,db=demosharding_fr,app=postgres_fdw,client=::1
LOG:  duration: 4.381 ms  statement: FETCH 10000 FROM c3

->  Append  (cost=6105.41..110039.12 rows=21 width=60) (actual time=330.325..594.923 rows=21 loops=1)
            ->  Async Foreign Scan  (cost=6105.41..28384.99 rows=6 width=60) (actual time=2.284..2.286 rows=6 loops=1)
                  Output: commandes.pays, clients.nom, (count(DISTINCT commandes.id_commande)), (avg(commandes.montant)), (sum(commandes.montant))
                  Relations: Aggregate on ((public.commandes_de commandes) INNER JOIN (public.clients_de clients))
                  Remote SQL: SELECT r4.pays, r7.nom, count(DISTINCT r4.id_commande), avg(r4.montant), sum(r4.montant) FROM (public.commandes r4 INNER JOIN public.clients r7 ON (((r4.pays = r7.pays)) AND ((r4.id_client = r7.id_client)))) GROUP BY 1, 2
            ->  Async Foreign Scan  (cost=6098.84..28353.37 rows=6 width=60) (actual time=2.077..2.078 rows=6 loops=1)
                  Output: commandes_1.pays, clients_1.nom, (count(DISTINCT commandes_1.id_commande)), (avg(commandes_1.montant)), (sum(commandes_1.montant))
                  Relations: Aggregate on ((public.commandes_es commandes_1) INNER JOIN (public.clients_es clients_1))
                  Remote SQL: SELECT r5.pays, r8.nom, count(DISTINCT r5.id_commande), avg(r5.montant), sum(r5.montant) FROM (public.commandes r5 INNER JOIN public.clients r8 ON (((r5.pays = r8.pays)) AND ((r5.id_client = r8.id_client)))) GROUP BY 1, 2
            ->  Async Foreign Scan  (cost=9102.09..53300.65 rows=9 width=60) (actual time=2.189..2.190 rows=9 loops=1)
                  Output: commandes_2.pays, clients_2.nom, (count(DISTINCT commandes_2.id_commande)), (avg(commandes_2.montant)), (sum(commandes_2.montant))
                  Relations: Aggregate on ((public.commandes_fr commandes_2) INNER JOIN (public.clients_fr clients_2))
                  Remote SQL: SELECT r6.pays, r9.nom, count(DISTINCT r6.id_commande), avg(r6.montant), sum(r6.montant) FROM (public.commandes r6 INNER JOIN public.clients r9 ON (((r6.pays = r9.pays)) AND ((r6.id_client = r9.id_client)))) GROUP BY 1, 2

curl -kL https://dali.bo/tp_cave -o cave.dump
psql -c "CREATE ROLE caviste LOGIN PASSWORD 'caviste'"
psql -c "CREATE DATABASE cave OWNER caviste"
pg_restore -d cave cave.dump
# NB : une erreur sur un schéma 'public' existant est normale

-- Création de lignes en 2001-2005
INSERT INTO stock SELECT vin_id, contenant_id, 2001 + annee % 5, sum(nombre)
FROM stock GROUP BY vin_id, contenant_id, 2001 + annee % 5;
-- purge des lignes prédédentes
DELETE FROM stock WHERE annee<2001;

INSERT INTO stock
SELECT vin_id, contenant_id, 2001 + annee % 5, sum(nombre)
FROM stock
GROUP BY vin_id, contenant_id, 2001 + annee % 5 ;

DELETE FROM stock WHERE annee<2001 ;

ALTER TABLE stock RENAME TO stock_old;
CREATE TABLE stock(LIKE stock_old) PARTITION BY LIST (annee);

CREATE TABLE stock_2001 PARTITION of stock FOR VALUES IN (2001) ;
CREATE TABLE stock_2002 PARTITION of stock FOR VALUES IN (2002) ;
CREATE TABLE stock_2003 PARTITION of stock FOR VALUES IN (2003) ;
CREATE TABLE stock_2004 PARTITION of stock FOR VALUES IN (2004) ;
CREATE TABLE stock_2005 PARTITION of stock FOR VALUES IN (2005) ;

INSERT INTO stock SELECT * FROM stock_old;

ANALYZE;

SELECT count(*) FROM stock_2001;
SELECT count(*) FROM ONLY stock;

EXPLAIN ANALYZE SELECT * FROM stock WHERE annee=2002;

                               QUERY PLAN
------------------------------------------------------------------------------
 Append  (cost=0.00..417.36 rows=18192 width=16) (...)
   ->  Seq Scan on stock_2002  (cost=0.00..326.40 rows=18192 width=16) (...)
         Filter: (annee = 2002)
 Planning Time: 0.912 ms
 Execution Time: 21.518 ms

CREATE UNIQUE INDEX ON stock (vin_id,contenant_id,annee);

EXPLAIN ANALYZE SELECT * FROM stock WHERE vin_id=1725 AND annee=2003 ;

 Append  (cost=0.29..4.36 rows=3 width=16) (...)
   ->  Index Scan using stock_2003_vin_id_contenant_id_annee_idx on stock_2003 (...)
         Index Cond: ((vin_id = 1725) AND (annee = 2003))
 Planning Time: 1.634 ms
 Execution Time: 0.166 ms

UPDATE stock SET annee=2004 WHERE annee=2003 and vin_id=1725 ;

ERROR:  duplicate key value violates unique constraint "stock_2004_vin_id_contenant_id_annee_idx"
DETAIL:  Key (vin_id, contenant_id, annee)=(1725, 1, 2004) already exists.

DELETE FROM stock WHERE annee=2004 and vin_id=1725;

UPDATE stock SET annee=2004 WHERE annee=2003 and vin_id=1725 ;

DROP TABLE stock_2001 ;

INSERT INTO stock (vin_id, contenant_id, annee, nombre) VALUES (1, 1, 2006, 100) ;

ERROR:  no partition of relation "stock" found for row
DETAIL:  Partition key of the failing row contains (annee) = (2006).

CREATE TABLE stock_default PARTITION OF stock DEFAULT ;

INSERT INTO stock (vin_id, contenant_id, annee, nombre) VALUES (1, 1, 2006, 100) ;

CREATE TABLE stock_2006 PARTITION of stock FOR VALUES IN (2006) ;

ERROR:  updated partition constraint for default partition "stock_default"
        would be violated by some row

BEGIN ;

ALTER TABLE stock DETACH PARTITION stock_default;

CREATE TABLE stock_2006 PARTITION of stock FOR VALUES IN (2006) ;

INSERT INTO stock SELECT * FROM stock_default WHERE annee = 2006 ;

DELETE FROM stock_default WHERE annee = 2006 ;

ALTER TABLE stock ATTACH PARTITION stock_default DEFAULT ;

COMMIT ;

$ createdb pgbench
$ /usr/pgsql-14/bin/pgbench -i -s 10 pgbench

$ /usr/pgsql-14/bin/pgbench -n -T3600 -c20 -j2 --debug pgbench

pgbench=# SET lock_timeout TO '10s' ;

SET statement_timeout TO '10s' ;

\timing on
\set ON_ERROR_STOP 1

SET lock_timeout TO '10s' ;
SET statement_timeout TO '10s' ;

BEGIN ;

-- Nouvelle table partitionnée

CREATE TABLE pgbench_accounts_part (LIKE pgbench_accounts INCLUDING ALL)
PARTITION BY HASH (aid) ;

CREATE TABLE pgbench_accounts_1 PARTITION OF pgbench_accounts_part
FOR VALUES WITH (MODULUS 3, REMAINDER 0 ) ;

CREATE TABLE pgbench_accounts_2 PARTITION OF pgbench_accounts_part
FOR VALUES WITH (MODULUS 3, REMAINDER 1 ) ;

CREATE TABLE pgbench_accounts_3 PARTITION OF pgbench_accounts_part
FOR VALUES WITH (MODULUS 3, REMAINDER 2 ) ;

-- Transfert des données

-- Bloquer les accès à la table le temps du transfert
-- (sinon risque de perte de données !)
LOCK TABLE pgbench_accounts ;

-- Copie des données
INSERT INTO pgbench_accounts_part
SELECT * FROM pgbench_accounts ;

-- Substitution par renommage
ALTER TABLE pgbench_accounts RENAME TO pgbench_accounts_old ;
ALTER TABLE pgbench_accounts_part RENAME TO pgbench_accounts ;

-- Contrôle

\d+

-- On ne validera qu'après contrôle
-- (pendant ce temps les sessions concurrentes restent bloquées !)

COMMIT ;

ERROR:  canceling statement due to statement timeout
Time: 10115.506 ms (00:10.116)

                            Liste des relations
 Schéma |         Nom          |        Type        | Propriétaire | Taille  | …
--------+----------------------+--------------------+--------------+---------+--
 public | pgbench_accounts     | table partitionnée | postgres     | 0 bytes |
 public | pgbench_accounts_1   | table              | postgres     | 43 MB   |
 public | pgbench_accounts_2   | table              | postgres     | 43 MB   |
 public | pgbench_accounts_3   | table              | postgres     | 43 MB   |
 public | pgbench_accounts_old | table              | postgres     | 130 MB  |
 public | pgbench_branches     | table              | postgres     | 136 kB  |
 public | pgbench_history      | table              | postgres     | 5168 kB |
 public | pgbench_tellers      | table              | postgres     | 216 kB  |

SELECT aid FROM pgbench_accounts_1 LIMIT 3 ;

 aid
-----
   2
   6
   8

SELECT aid FROM pgbench_accounts_2 LIMIT 3 ;

 aid
-----
   3
   7
  10

SELECT aid FROM pgbench_accounts_3 LIMIT 3 ;

 aid
-----
   1
   9
  11

SELECT relname, n_live_tup
FROM pg_stat_user_tables
WHERE relname LIKE 'pgbench_accounts%' ;

       relname        | n_live_tup
----------------------+------------
 pgbench_accounts_old |    1000002
 pgbench_accounts_1   |     333263
 pgbench_accounts_2   |     333497
 pgbench_accounts_3   |     333240

DROP TABLE pgbench_accounts_old ;

SELECT ( now()+ interval '60s') AS date_frontiere \gset

SELECT :'date_frontiere'::timestamptz ;

\timing on
\set ON_ERROR_STOP 1

SET lock_timeout TO '10s' ;
SET statement_timeout TO '10s' ;

SELECT ( now()+ interval '60s') AS date_frontiere \gset
SELECT :'date_frontiere'::timestamptz ;

BEGIN ;

-- Nouvelle table partitionnée
CREATE TABLE pgbench_history_part (LIKE pgbench_history INCLUDING ALL)
PARTITION BY RANGE (mtime) ;

-- Des partitions pour les prochaines minutes

CREATE TABLE pgbench_history_1
PARTITION OF pgbench_history_part
FOR VALUES FROM (:'date_frontiere'::timestamptz  )
             TO (:'date_frontiere'::timestamptz + interval '1min' ) ;

CREATE TABLE pgbench_history_2
PARTITION OF pgbench_history_part
FOR VALUES FROM (:'date_frontiere'::timestamptz + interval '1min' )
             TO (:'date_frontiere'::timestamptz + interval '2min' ) ;

-- Au cas où le service perdure au-delà des partitions prévues,
-- on débordera dans cette table
CREATE TABLE pgbench_history_default
PARTITION OF pgbench_history_part DEFAULT ;

-- Jusqu'ici pgbench continue de tourner en arrière plan

-- La table devient une simple partition
-- Ce renommage pose un verrou, les sessions pgbench sont bloquées
ALTER TABLE pgbench_history RENAME TO pgbench_history_orig ;

ALTER TABLE pgbench_history_part
ATTACH PARTITION pgbench_history_orig
FOR VALUES FROM (MINVALUE) TO (:'date_frontiere'::timestamptz) ;

-- Contrôle
\dP

-- Substitution de la table partitionnée à celle d'origine.
ALTER TABLE pgbench_history_part RENAME TO pgbench_history ;

-- Contrôle
\d+ pgbench_history


COMMIT ;

SELECT relname, n_live_tup, now()
FROM pg_stat_user_tables
WHERE relname LIKE 'pgbench_history%' ;

\watch 3

                                   Liste des relations
 Schéma |           Nom           |        Type        | Propriétaire | Taille  | …
--------+-------------------------+--------------------+--------------+---------+--
 public | pgbench_accounts        | table partitionnée | postgres     | 0 bytes |
 public | pgbench_accounts_1      | table              | postgres     | 44 MB   |
 public | pgbench_accounts_2      | table              | postgres     | 44 MB   |
 public | pgbench_accounts_3      | table              | postgres     | 44 MB   |
 public | pgbench_branches        | table              | postgres     | 136 kB  |
 public | pgbench_history         | table partitionnée | postgres     | 0 bytes |
 public | pgbench_history_1       | table              | postgres     | 672 kB  |
 public | pgbench_history_2       | table              | postgres     | 0 bytes |
 public | pgbench_history_default | table              | postgres     | 0 bytes |
 public | pgbench_history_orig    | table              | postgres     | 8736 kB |
 public | pgbench_tellers         | table              | postgres     | 216 kB  |

ALTER TABLE pgbench_history
DETACH PARTITION pgbench_history_orig ;

-- On pourrait faire le DROP directement

DROP TABLE pgbench_history_orig ;

SET lock_timeout TO '3s' ;
SET statement_timeout TO '10s' ;

CREATE INDEX ON pgbench_history (aid) ;

ALTER TABLE pgbench_history
ADD CONSTRAINT pgbench_history_aid_fkey FOREIGN KEY (aid) REFERENCES pgbench_accounts ;

pgbench=# \d+ pgbench_history
  Table partitionnée « public.pgbench_history »
 …
Clé de partition : RANGE (mtime)
Index :
    "pgbench_history_aid_idx" btree (aid)
Contraintes de clés étrangères :
    "pgbench_history_aid_fkey" FOREIGN KEY (aid) REFERENCES pgbench_accounts(aid)
Partitions: pgbench_history_1 FOR VALUES FROM ('2020-02-14 17:41:08.298445')
                              TO ('2020-02-14 17:42:08.298445'),
            pgbench_history_2 FOR VALUES FROM ('2020-02-14 17:42:08.298445')
                              TO ('2020-02-14 17:43:08.298445'),
            pgbench_history_default DEFAULT

pgbench=# \d+ pgbench_history_1
   Table « public.pgbench_history_1 »
…
Partition de : pgbench_history FOR VALUES FROM ('2020-02-14 17:41:08.298445')
                                          TO ('2020-02-14 17:42:08.298445')
Contrainte de partition : ((mtime IS NOT NULL)
    AND(mtime >= '2020-02-14 17:41:08.298445'::timestamp without time zone)
    AND (mtime < '2020-02-14 17:42:08.298445'::timestamp without time zone))
Index :
    "pgbench_history_1_aid_idx" btree (aid)
Contraintes de clés étrangères :
    TABLE "pgbench_history" CONSTRAINT "pgbench_history_aid_fkey"
                            FOREIGN KEY (aid) REFERENCES pgbench_accounts(aid)
Méthode d'accès : heap

SET
pgbench=# ALTER TABLE pgbench_history
          ADD CONSTRAINT pgbench_history_aid_fkey FOREIGN KEY (aid)
          REFERENCES pgbench_accounts ;
ERROR:  canceling statement due to statement timeout

SET statement_timeout to '1s' ;
ALTER TABLE pgbench_history_1 ADD CONSTRAINT pgbench_history_aid_fkey
 FOREIGN KEY (aid) REFERENCES pgbench_accounts ;
ALTER TABLE pgbench_history_2 ADD CONSTRAINT pgbench_history_aid_fkey
 FOREIGN KEY (aid) REFERENCES pgbench_accounts ;
ALTER TABLE pgbench_history_default ADD CONSTRAINT pgbench_history_aid_fkey
 FOREIGN KEY (aid) REFERENCES pgbench_accounts ;

ALTER TABLE pgbench_history ADD CONSTRAINT pgbench_history_aid_fkey
 FOREIGN KEY (aid) REFERENCES pgbench_accounts ;

CREATE INDEX CONCURRENTLY ON pgbench_history (aid) ;

ERROR:  cannot create index on partitioned table "pgbench_history" concurrently

CREATE INDEX CONCURRENTLY ON pgbench_history_1 (aid) ;
CREATE INDEX CONCURRENTLY ON pgbench_history_2 (aid) ;
CREATE INDEX CONCURRENTLY ON pgbench_history_default (aid) ;

SELECT 'CREATE INDEX CONCURRENTLY ON ' ||
       c.oid::regclass::text || ' (aid) ; '
FROM pg_class c
WHERE relname like 'pgbench_history%' AND relispartition \gexec

SELECT indexrelid::regclass FROM pg_index WHERE NOT  indisvalid ;

CREATE INDEX ON pgbench_history(aid) ;

pgbench=# \d+ pgbench_history
..
Partition key: RANGE (mtime)
Indexes:
    "pgbench_history_aid_idx" btree (aid)
…