pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17886: Error disabling user triggers on a partitioned table
5+ messages / 4 participants
[nested] [flat]
Commits from this thread:
2023-04-05 b02bf1e460 2624de79ef 0a6aaf0116 4766eef317 Fix another issue with ENABLE/DISABLE TRIGGER on partitioned tables.
* BUG #17886: Error disabling user triggers on a partitioned table
@ 2023-04-04 16:47 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 5+ messages in thread
From: PG Bug reporting form @ 2023-04-04 16:47 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: jazz001319@gmail.com
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17886: Error disabling user triggers on a partitioned table
@ 2023-04-04 16:49 jazzl 0013 <jazz001319@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 5+ messages in thread
From: jazzl 0013 @ 2023-04-04 16:49 UTC (permalink / raw)
To: jazz001319@gmail.com; pgsql-bugs@lists.postgresql.org
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
>
>
Attachments:
EnableDisableTriggerNew.sql (application/octet-stream, 1.0K) download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17886: Error disabling user triggers on a partitioned table
@ 2023-04-04 18:24 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2023-04-04 18:24 UTC (permalink / raw)
To: jazz001319@gmail.com; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> 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
Commit ec0925c22 seems to have been quite snakebit. I already fixed
a deficiency in it in v15/HEAD, but here we have a different symptom
in the older branches. What's happening is that EnableDisableTrigger
is ignoring the child trigger because it has tgisinternal set to true
and the command passes skip_system = true.
I'm inclined to think that in the older branches (pre f4566345c)
we need to do
- if (oldtrig->tgisinternal)
+ if (oldtrig->tgisinternal && !OidIsValid(oldtrig->tgparentid))
{
/* system trigger ... ok to process? */
but I've not tested that. Alvaro, what do you think?
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17886: Error disabling user triggers on a partitioned table
@ 2023-04-05 07:33 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Alvaro Herrera @ 2023-04-05 07:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: jazz001319@gmail.com; pgsql-bugs@lists.postgresql.org
On 2023-Apr-04, Tom Lane wrote:
> PG Bug reporting form <noreply@postgresql.org> writes:
> > 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
>
> Commit ec0925c22 seems to have been quite snakebit.
No kidding :-( Clearly, commit 86f575948c77 ("Allow FOR EACH ROW
triggers on partitioned tables") should have included more tests.
> I already fixed
> a deficiency in it in v15/HEAD, but here we have a different symptom
> in the older branches. What's happening is that EnableDisableTrigger
> is ignoring the child trigger because it has tgisinternal set to true
> and the command passes skip_system = true.
Hmm, right.
> I'm inclined to think that in the older branches (pre f4566345c)
> we need to do
>
> - if (oldtrig->tgisinternal)
> + if (oldtrig->tgisinternal && !OidIsValid(oldtrig->tgparentid))
> {
> /* system trigger ... ok to process? */
>
> but I've not tested that. Alvaro, what do you think?
Oh, that's a nice and clean solution. I tested it (on 13 and 14) and it
does solve the problem, and no regression tests fail, but I didn't try
to break it further. 12 and back are unaffected, for lack of
86f575948c77.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"Nadie está tan esclavizado como el que se cree libre no siéndolo" (Goethe)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17886: Error disabling user triggers on a partitioned table
@ 2023-04-05 16:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2023-04-05 16:57 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: jazz001319@gmail.com; pgsql-bugs@lists.postgresql.org
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> On 2023-Apr-04, Tom Lane wrote:
>> I'm inclined to think that in the older branches (pre f4566345c)
>> we need to do
>> - if (oldtrig->tgisinternal)
>> + if (oldtrig->tgisinternal && !OidIsValid(oldtrig->tgparentid))
>> but I've not tested that. Alvaro, what do you think?
> Oh, that's a nice and clean solution. I tested it (on 13 and 14) and it
> does solve the problem, and no regression tests fail, but I didn't try
> to break it further. 12 and back are unaffected, for lack of
> 86f575948c77.
Pushed, thanks for looking at it.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-04-05 16:57 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-04-04 16:47 BUG #17886: Error disabling user triggers on a partitioned table PG Bug reporting form <noreply@postgresql.org>
2023-04-04 16:49 ` jazzl 0013 <jazz001319@gmail.com>
2023-04-04 18:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-04-05 07:33 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-04-05 16:57 ` Tom Lane <tgl@sss.pgh.pa.us>
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