From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: jazz001319@gmail.com
Subject: BUG #17886: Error disabling user triggers on a partitioned table
Date: Tue, 04 Apr 2023 16:47:33 +0000
Message-ID: <17886-5406d5d828aa4aa3@postgresql.org> (raw)
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
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: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, jazz001319@gmail.com
Subject: Re: BUG #17886: Error disabling user triggers on a partitioned table
In-Reply-To: <17886-5406d5d828aa4aa3@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox