Re: BUG #17886: Error disabling user triggers on a partitioned table

jazzl 0013 <jazz001319@gmail.com>

From: jazzl 0013 <jazz001319@gmail.com>
To: jazz001319@gmail.com, pgsql-bugs@lists.postgresql.org
Date: 2023-04-04T16:49:20Z
Lists: pgsql-bugs

Attachments

The following bug has been logged on the website:
>
> Bug reference:      17886
> Logged by:          DzmitryH
> Email address:      jazz001319@gmail.com
> PostgreSQL version: 14.7
> Operating system:   Linux 883a37b156f7 5.15.0-52-generic #58-Ubuntu SM
> Description:
>
> sequence of steps:
> 1. clean install PostgreSQL 14.7
> 2. Create test database
> 3. Create partiton table and partitions (for example only default
> partiton)
> 4. Create trigger
> 5. Disable user trigger on partitions
>
> verbose step:
> psql (14.7 (Debian 14.7-1.pgdg110+1))
> Type "help" for help.
>
> postgres=# \set VERBOSITY verbose
> postgres=# create database testdb;
> CREATE DATABASE
> postgres=# CREATE TABLE IF NOT EXISTS public.test
> (
>     id bigserial,
>     user_id bigint,
>     type text NOT NULL,
>     status text NOT NULL,
>     details jsonb,
>     created_timestamp timestamp with time zone NOT NULL DEFAULT 'now()',
>         modified_timestamp timestamp with time zone NOT NULL DEFAULT
> 'now()',
>     shard_id integer NOT NULL DEFAULT '1',
>     demo boolean NOT NULL,
>     CONSTRAINT test_pkey PRIMARY KEY (id, created_timestamp)
> ) PARTITION BY RANGE (created_timestamp);
> CREATE TABLE
> postgres=# CREATE TABLE public.test_def PARTITION OF public.test
>     DEFAULT;
> CREATE TABLE
> postgres=# CREATE OR REPLACE FUNCTION
> public.update_last_modified_timestamp()
>     RETURNS trigger
>     LANGUAGE 'plpgsql'
>     COST 100
>     VOLATILE NOT LEAKPROOF
> AS $BODY$
> BEGIN
>   IF NEW != OLD
>   THEN
>     NEW.modified_timestamp := CURRENT_TIMESTAMP;
>   END IF;
>   RETURN NEW;
> END;
> $BODY$;
> CREATE FUNCTION
> postgres=# CREATE TRIGGER trigger_test
>     BEFORE UPDATE
>     ON public.test
>     FOR EACH ROW
>     EXECUTE FUNCTION public.update_last_modified_timestamp();
> CREATE TRIGGER
> postgres=# alter table public.test DISABLE TRIGGER USER;
> ERROR:  42704: trigger "trigger_test" for table "test_def" does not exist
> LOCATION:  EnableDisableTriggerNew, trigger.c:1658
>
> result:
> postgres=# alter table public.test DISABLE TRIGGER USER;
> ERROR:  42704: trigger "trigger_test" for table "test_def" does not exist
> LOCATION:  EnableDisableTriggerNew, trigger.c:1658
>
> Expected Result (Postgresql 15.2 and 14.4 - fine):
> testdb=> alter table public.test DISABLE TRIGGER USER;
> ALTER TABLE
>
>

Commits

  1. Fix another issue with ENABLE/DISABLE TRIGGER on partitioned tables.

  2. Fix ENABLE/DISABLE TRIGGER to handle recursion correctly

  3. Allow FOR EACH ROW triggers on partitioned tables