public inbox for [email protected]  

help / color / mirror / Atom feed

From: yudhi s <[email protected]>
To: David G. Johnston <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: error in trigger creation
Date: Mon, 22 Apr 2024 00:27:44 +0530
Message-ID: <CAEzWdqffAd3_hFjCdkpDFSpYQ6OxjRQzv4ZCnJQZwnCzvbq+-g@mail.gmail.com> (raw)
In-Reply-To: <CAKFQuwawNxB_XEGhGiTmGT6efj8jB+g=a++3G9msmh2_R=OVoQ@mail.gmail.com>
References: <CAEzWdqcimp5dnNOavaSkMCOKW_FVsKC2101g=dFsyjQ-9dA3uw@mail.gmail.com>
	<CAKFQuwa+jpZ-pucWc92OCYcwCnj7C_POg8k=5BvbPZyL97R-Jw@mail.gmail.com>
	<CAEzWdqfqr9e3OpFd5Nhqha3Ggm=+UJdWkgvo7dpAa3W99S2g5Q@mail.gmail.com>
	<CAKFQuwYu8w7BMX_9xEP1t5ULT7pV-qO1Yotn1qtdMuEpWCqhFg@mail.gmail.com>
	<[email protected]>
	<CAEzWdqcs8OYtK9dWbev986FfNO0i9rGDvChZTti2xWL8HuXnbg@mail.gmail.com>
	<CAKFQuwawNxB_XEGhGiTmGT6efj8jB+g=a++3G9msmh2_R=OVoQ@mail.gmail.com>

On Mon, Apr 22, 2024 at 12:02 AM David G. Johnston <
[email protected]> wrote:

>
> I suggest you share a script that demonstrates exactly what you are trying
> to accomplish.  Which event triggers you need to create from the
> application and what the functions those triggers call do.
>
>
We are using pg_partman for automatic partition maintenance however as we
have foreign keys created on the tables, so the partition drop from parent
is taking longer as it scans all the partitions of the child table and also
locks the full child table for that duration(even SELECT query not allowed
during that period). So we are thinking of creating foreign keys on
partitions rather than on tables however there is no direct option for that
to happen through pg_partman.

So we are thinking of first creating the table without any foreign keys and
creating the partitions using pg_partman, then create the below event
trigger which will add the foreign key to the new partitions for all new
future partitions. And we are planning to create such an event trigger for
all such child tables that are partitioned and having FK's.

CREATE OR REPLACE FUNCTION add_partition_foreign_key()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
partition_table TEXT;
parent_table TEXT;
partition_name TEXT;
BEGIN
IF TG_TAG = 'CREATE TABLE' THEN
partition_table := TG_TABLE_NAME;
parent_table := 'parent_table_' || to_char(NEW.partition_key, 'YYYY_MM_DD');

EXECUTE format('ALTER TABLE %I ADD CONSTRAINT fk_%I_%I FOREIGN KEY
(partition_key, id) REFERENCES %I (partition_key, id)', partition_table,
partition_table, parent_table, parent_table);
END IF;
END;
$$;

CREATE EVENT TRIGGER add_partition_foreign_key_trigger
ON ddl_command_end
WHEN TAG IN ('CREATE TABLE')
EXECUTE FUNCTION add_partition_foreign_key();

view thread (7+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: error in trigger creation
  In-Reply-To: <CAEzWdqffAd3_hFjCdkpDFSpYQ6OxjRQzv4ZCnJQZwnCzvbq+-g@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox