Thread: FK to partitioned table

uhps… please ignore answer below
thought this was some internal mail .. did not see it was coming from the mailing list 😉
cheers
Heiko

 

From: "heiko.onnebrink@metronom.com" <heiko.onnebrink@metronom.com>
Date: Tuesday, 29. June 2021 at 15:45
To: "piotrwlodarczyk89@gmail.com" <piotrwlodarczyk89@gmail.com>, "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>
Subject: Re: [EXT] FK to partitioned table

 

Hi,
good question.. cannot answer straight..

Here some of my bookmarks .. not sure if the question is covered there
https://blog.dbi-services.com/postgresql-partitioning-5-partition-pruning/

https://severalnines.com/database-blog/how-take-advantage-new-partitioning-features-postgresql-11

If you do not find an answer I could connect you with some PostgreSQL experts from our Cybertec support company.. they could try to get answer from the PostgreSQL source code as last resort 😉

cheers
Heiko

P.S. see that my bookmarks seem to be also a bot old.. suggest to do a fresh google for PostgreSQL 13 behaviour.. likely that things changed again as there was much done on partitioning in PG12 & 13.. specially partition pruning was heavily updated..

From: Piotr Włodarczyk <piotrwlodarczyk89@gmail.com>
Date: Tuesday, 29. June 2021 at 14:42
To: "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>
Subject: [EXT] FK to partitioned table

 

Hello geeks,

I have a question I can't find any answer.

First we need some basic structure. So it is:


  CREATE SCHEMA parts
      AUTHORIZATION postgres;

  DROP TABLE IF EXISTS parts.main;

  CREATE TABLE IF NOT EXISTS parts.main
  (
      id bigserial NOT NULL,
      id_ext bigint NOT NULL,
      added timestamp without time zone,
      CONSTRAINT pk PRIMARY KEY (id, id_ext)
  ) PARTITION BY hash (id_ext);

  ALTER TABLE parts.main
      OWNER to postgres;

Then we create a partitions:

  CREATE TABLE IF NOT EXISTS parts.main_hash0 PARTITION OF parts.main FOR VALUES WITH (MODULUS 10, REMAINDER 0);
  CREATE TABLE IF NOT EXISTS parts.main_hash1 PARTITION OF parts.main FOR VALUES WITH (MODULUS 10, REMAINDER 1);
  CREATE TABLE IF NOT EXISTS parts.main_hash2 PARTITION OF parts.main FOR VALUES WITH (MODULUS 10, REMAINDER 2);
  CREATE TABLE IF NOT EXISTS parts.main_hash3 PARTITION OF parts.main FOR VALUES WITH (MODULUS 10, REMAINDER 3);
  CREATE TABLE IF NOT EXISTS parts.main_hash4 PARTITION OF parts.main FOR VALUES WITH (MODULUS 10, REMAINDER 4);
  CREATE TABLE IF NOT EXISTS parts.main_hash5 PARTITION OF parts.main FOR VALUES WITH (MODULUS 10, REMAINDER 5);
  CREATE TABLE IF NOT EXISTS parts.main_hash6 PARTITION OF parts.main FOR VALUES WITH (MODULUS 10, REMAINDER 6);
  CREATE TABLE IF NOT EXISTS parts.main_hash7 PARTITION OF parts.main FOR VALUES WITH (MODULUS 10, REMAINDER 7);
  CREATE TABLE IF NOT EXISTS parts.main_hash8 PARTITION OF parts.main FOR VALUES WITH (MODULUS 10, REMAINDER 8);
  CREATE TABLE IF NOT EXISTS parts.main_hash9 PARTITION OF parts.main FOR VALUES WITH (MODULUS 10, REMAINDER 9);

And finally one table connected by fk to parts.main:

  -- Table: parts.main_additional

  -- DROP TABLE parts.main_additional;

  CREATE TABLE IF NOT EXISTS parts.main_additional
  (
      id bigserial,
      id_main bigint NOT NULL,
      id_ext bigint,
      CONSTRAINT main_additional_pkey PRIMARY KEY (id),
      CONSTRAINT fk_1 FOREIGN KEY (id_ext, id_main)
 REFERENCES parts.main (id_ext, id) MATCH SIMPLE
 ON UPDATE NO ACTION
 ON DELETE NO ACTION
  )

  TABLESPACE pg_default;

  ALTER TABLE parts.main_additional
      OWNER to postgres;


So we have partitioned table parts.main partitioned by hash of id_ext and having PK (id, id_ext).

We have parts.main_addidtional table with some data related to parts.main. As you can see we have FK fk_1

  (...)
       CONSTRAINT fk_1 FOREIGN KEY (id_ext, id_main)
 REFERENCES parts.main (id_ext, id) MATCH SIMPLE
 ON UPDATE NO ACTION
 ON DELETE NO ACTION
  (...)

on parts.main_addidtional table. Because of parts.main is partitioned finnaly postgres create fk_1 as follows:

  (...)
  CONSTRAINT fk_1 FOREIGN KEY (id_ext, id_main)
        REFERENCES parts.main (id_ext, id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT main_additional_id_ext_id_main_fkey FOREIGN KEY (id_ext, id_main)
        REFERENCES parts.main_hash0 (id_ext, id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT main_additional_id_ext_id_main_fkey1 FOREIGN KEY (id_ext, id_main)
        REFERENCES parts.main_hash1 (id_ext, id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT main_additional_id_ext_id_main_fkey2 FOREIGN KEY (id_ext, id_main)
        REFERENCES parts.main_hash2 (id_ext, id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT main_additional_id_ext_id_main_fkey3 FOREIGN KEY (id_ext, id_main)
        REFERENCES parts.main_hash3 (id_ext, id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT main_additional_id_ext_id_main_fkey4 FOREIGN KEY (id_ext, id_main)
        REFERENCES parts.main_hash4 (id_ext, id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT main_additional_id_ext_id_main_fkey5 FOREIGN KEY (id_ext, id_main)
        REFERENCES parts.main_hash5 (id_ext, id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT main_additional_id_ext_id_main_fkey6 FOREIGN KEY (id_ext, id_main)
        REFERENCES parts.main_hash6 (id_ext, id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT main_additional_id_ext_id_main_fkey7 FOREIGN KEY (id_ext, id_main)
        REFERENCES parts.main_hash7 (id_ext, id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT main_additional_id_ext_id_main_fkey8 FOREIGN KEY (id_ext, id_main)
        REFERENCES parts.main_hash8 (id_ext, id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT main_additional_id_ext_id_main_fkey9 FOREIGN KEY (id_ext, id_main)
        REFERENCES parts.main_hash9 (id_ext, id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
  (...)

so one fk to each partition. Now I'm thinking about which fk is used when fk_1 is using? All of them or postgres can "prune" them?

I couldn't find answer in explain analyze:

  explain analyze
  update parts.main_additional
  set id_main = 15 , id_ext = 14
  where id = 4

because of result

  "Update on main_additional  (cost=0.29..8.30 rows=1 width=30) (actual time=0.106..0.107 rows=0 loops=1)"
  "  ->  Index Scan using main_additional_pkey on main_additional  (cost=0.29..8.30 rows=1 width=30) (actual time=0.030..0.047 rows=1 loops=1)"
  "        Index Cond: (id = 4)"
  "Planning Time: 0.089 ms"
  "Trigger for constraint fk_1: time=32.158 calls=1"
  "Execution Time: 32.293 ms"

tels only about using fk_1 onec. I've inserted one row so calls number is 1 or it's 1 because of 1 insert and using only one fk (for exmaple main_additional_id_ext_id_main_fkey8)?

In another words: is postgres using all of fk extending fk_1 (main_additional_id_ext_id_main_fkey, main_additional_id_ext_id_main_fkey1 ... main_additional_id_ext_id_main_fkey9) or can select right one?



--

 

Pozdrawiam

Piotr Włodarczyk

Geschäftsanschrift/Business address: METRO-NOM GmbH, Metro-Straße 12, 40235 Duesseldorf, Germany
Geschäftsführung/Management Board: Thomas Viefhaus

Sitz Düsseldorf, Amtsgericht Düsseldorf, HRB 18232/Registered Office Düsseldorf, Commercial Register of the Düsseldorf Local Court, HRB 18232

Betreffend Mails von *@metronom.com
Die in dieser E-Mail enthaltenen Nachrichten und Anhänge sind ausschließlich für den bezeichneten Adressaten bestimmt. Sie können rechtlich geschützte, vertrauliche Informationen enthalten. Falls Sie nicht der bezeichnete Empfänger oder zum Empfang dieser E-Mail nicht berechtigt sind, ist die Verwendung, Vervielfältigung oder Weitergabe der Nachrichten und Anhänge untersagt. Falls Sie diese E-Mail irrtümlich erhalten haben, informieren Sie bitte unverzüglich den Absender und vernichten Sie die E-Mail.

Regarding mails from *@metronom.com
This e-mail message and any attachment are intended exclusively for the named addressee. They may contain confidential information which may also be protected by professional secrecy. Unless you are the named addressee (or authorised to receive for the addressee) you may not copy or use this message or any attachment or disclose the contents to anyone else. If this e-mail was

Geschäftsanschrift/Business address: METRO-NOM GmbH, Metro-Straße 12, 40235 Duesseldorf, Germany
Geschäftsführung/Management Board: Thomas Viefhaus

Sitz Düsseldorf, Amtsgericht Düsseldorf, HRB 18232/Registered Office Düsseldorf, Commercial Register of the Düsseldorf Local Court, HRB 18232

Betreffend Mails von *@metronom.com
Die in dieser E-Mail enthaltenen Nachrichten und Anhänge sind ausschließlich für den bezeichneten Adressaten bestimmt. Sie können rechtlich geschützte, vertrauliche Informationen enthalten. Falls Sie nicht der bezeichnete Empfänger oder zum Empfang dieser E-Mail nicht berechtigt sind, ist die Verwendung, Vervielfältigung oder Weitergabe der Nachrichten und Anhänge untersagt. Falls Sie diese E-Mail irrtümlich erhalten haben, informieren Sie bitte unverzüglich den Absender und vernichten Sie die E-Mail.

Regarding mails from *@metronom.com
This e-mail message and any attachment are intended exclusively for the named addressee. They may contain confidential information which may also be protected by professional secrecy. Unless you are the named addressee (or authorised to receive for the addressee) you may not copy or use this message or any attachment or disclose the contents to anyone else. If this e-mail was