pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #16346: pg_upgrade fails on a trigger with a comment
4+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2020-04-08 a9d70c1087 983ed99a60 d050c61137 830471e0dc 7780660520 564c1e6d73 Fix pg_dump/pg_restore to restore event trigger comments later.
* BUG #16346: pg_upgrade fails on a trigger with a comment
@ 2020-04-06 15:00 PG Bug reporting form <noreply@postgresql.org>
2020-04-07 20:10 ` Re: BUG #16346: pg_upgrade fails on a trigger with a comment Hamid Akhtar <hamid.akhtar@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: PG Bug reporting form @ 2020-04-06 15:00 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: exclusion@gmail.com
The following bug has been logged on the website:
Bug reference: 16346
Logged by: Alexander Lakhin
Email address: exclusion@gmail.com
PostgreSQL version: 12.2
Operating system: Ubuntu 18.04
Description:
When using pg_upgrade on a database with the following contents:
CREATE FUNCTION public.test_event_trigger() RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'test_event_trigger: % %', tg_event, tg_tag;
END
$$;
CREATE EVENT TRIGGER regress_event_trigger3 ON ddl_command_start
EXECUTE PROCEDURE public.test_event_trigger();
COMMENT ON EVENT TRIGGER regress_event_trigger3 IS 'test comment';
I get:
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster
postgres
*failure*
Consult the last few lines of "pg_upgrade_dump_14174.log" for
the probable cause of the failure.
Failure, exiting
pg_upgrade_dump_14174.log contains:
command: "/src/postgres/tmp_install/usr/local/pgsql/bin/pg_restore" --host
/src/postgres --port 50432 --username postgres --clean --create
--exit-on-error --verbose --dbname template1 "pg_upgrade_dump_14174.custom"
>> "pg_upgrade_dump_14174.log" 2>&1
pg_restore: connecting to database for restore
pg_restore: dropping DATABASE PROPERTIES postgres
pg_restore: dropping DATABASE postgres
pg_restore: creating DATABASE "postgres"
pg_restore: connecting to new database "postgres"
pg_restore: connecting to database "postgres" as user "postgres"
pg_restore: creating COMMENT "DATABASE "postgres""
pg_restore: creating DATABASE PROPERTIES "postgres"
pg_restore: connecting to new database "postgres"
pg_restore: connecting to database "postgres" as user "postgres"
pg_restore: creating pg_largeobject "pg_largeobject"
pg_restore: creating FUNCTION "public.test_event_trigger()"
pg_restore: creating COMMENT "EVENT TRIGGER "regress_event_trigger3""
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3705; 0 0 COMMENT EVENT TRIGGER
"regress_event_trigger3" postgres
pg_restore: error: could not execute query: ERROR: event trigger
"regress_event_trigger3" does not exist
Command was: COMMENT ON EVENT TRIGGER "regress_event_trigger3" IS 'test
comment';
It looks like the commit 4c40b27b broke this.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #16346: pg_upgrade fails on a trigger with a comment
2020-04-06 15:00 BUG #16346: pg_upgrade fails on a trigger with a comment PG Bug reporting form <noreply@postgresql.org>
@ 2020-04-07 20:10 ` Hamid Akhtar <hamid.akhtar@gmail.com>
2020-04-08 07:21 ` Re: BUG #16346: pg_upgrade fails on a trigger with a comment Hamid Akhtar <hamid.akhtar@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Hamid Akhtar @ 2020-04-07 20:10 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
As you have mentioned, I have verified that indeed commit 4c40b27b broke
this.
In this particular commit moves restoration of materialized views and event
triggers to the last phase. Perhaps, comments should also be moved to this
phase as there may comments on either of these types of objects.
Attached is a patch that resolves this issue. I've verified that it resolve
the upgrade (and restore issue) introduced by 4c40b27b. I'll test this
patch in a little more detail tomorrow.
On Mon, Apr 6, 2020 at 8:26 PM PG Bug reporting form <noreply@postgresql.org>
wrote:
> The following bug has been logged on the website:
>
> Bug reference: 16346
> Logged by: Alexander Lakhin
> Email address: exclusion@gmail.com
> PostgreSQL version: 12.2
> Operating system: Ubuntu 18.04
> Description:
>
> When using pg_upgrade on a database with the following contents:
> CREATE FUNCTION public.test_event_trigger() RETURNS event_trigger
> LANGUAGE plpgsql
> AS $$
> BEGIN
> RAISE NOTICE 'test_event_trigger: % %', tg_event, tg_tag;
> END
> $$;
>
> CREATE EVENT TRIGGER regress_event_trigger3 ON ddl_command_start
> EXECUTE PROCEDURE public.test_event_trigger();
>
> COMMENT ON EVENT TRIGGER regress_event_trigger3 IS 'test comment';
>
> I get:
> Restoring global objects in the new cluster ok
> Restoring database schemas in the new cluster
> postgres
> *failure*
>
> Consult the last few lines of "pg_upgrade_dump_14174.log" for
> the probable cause of the failure.
> Failure, exiting
>
> pg_upgrade_dump_14174.log contains:
> command: "/src/postgres/tmp_install/usr/local/pgsql/bin/pg_restore" --host
> /src/postgres --port 50432 --username postgres --clean --create
> --exit-on-error --verbose --dbname template1 "pg_upgrade_dump_14174.custom"
> >> "pg_upgrade_dump_14174.log" 2>&1
> pg_restore: connecting to database for restore
> pg_restore: dropping DATABASE PROPERTIES postgres
> pg_restore: dropping DATABASE postgres
> pg_restore: creating DATABASE "postgres"
> pg_restore: connecting to new database "postgres"
> pg_restore: connecting to database "postgres" as user "postgres"
> pg_restore: creating COMMENT "DATABASE "postgres""
> pg_restore: creating DATABASE PROPERTIES "postgres"
> pg_restore: connecting to new database "postgres"
> pg_restore: connecting to database "postgres" as user "postgres"
> pg_restore: creating pg_largeobject "pg_largeobject"
> pg_restore: creating FUNCTION "public.test_event_trigger()"
> pg_restore: creating COMMENT "EVENT TRIGGER "regress_event_trigger3""
> pg_restore: while PROCESSING TOC:
> pg_restore: from TOC entry 3705; 0 0 COMMENT EVENT TRIGGER
> "regress_event_trigger3" postgres
> pg_restore: error: could not execute query: ERROR: event trigger
> "regress_event_trigger3" does not exist
> Command was: COMMENT ON EVENT TRIGGER "regress_event_trigger3" IS 'test
> comment';
>
> It looks like the commit 4c40b27b broke this.
>
>
--
Highgo Software (Canada/China/Pakistan)
URL : www.highgo.ca
ADDR: 10318 WHALLEY BLVD, Surrey, BC
CELL：+923335449950 EMAIL: mailto:hamid.akhtar@highgo.ca
SKYPE: engineeredvirus
Attachments:
upgrade_comment_fix_bug16346.patch (application/octet-stream, 1.1K) download
diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index d3f85d6..dd5f543 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -3083,6 +3083,7 @@ _tocEntryRestorePass(TocEntry *te)
strcmp(te->desc, "DEFAULT ACL") == 0)
return RESTORE_PASS_ACL;
if (strcmp(te->desc, "EVENT TRIGGER") == 0 ||
+ strcmp(te->desc, "COMMENT") == 0 ||
strcmp(te->desc, "MATERIALIZED VIEW DATA") == 0)
return RESTORE_PASS_POST_ACL;
return RESTORE_PASS_MAIN;
diff --git a/src/bin/pg_dump/pg_backup_archiver.h b/src/bin/pg_dump/pg_backup_archiver.h
index 67f3474..8aac08f 100644
--- a/src/bin/pg_dump/pg_backup_archiver.h
+++ b/src/bin/pg_dump/pg_backup_archiver.h
@@ -224,7 +224,7 @@ typedef enum
{
RESTORE_PASS_MAIN = 0, /* Main pass (most TOC item types) */
RESTORE_PASS_ACL, /* ACL item types */
- RESTORE_PASS_POST_ACL /* Event trigger and matview refresh items */
+ RESTORE_PASS_POST_ACL /* Event trigger and matview refresh items, and comments */
#define RESTORE_PASS_LAST RESTORE_PASS_POST_ACL
} RestorePass;
* Re: BUG #16346: pg_upgrade fails on a trigger with a comment
2020-04-06 15:00 BUG #16346: pg_upgrade fails on a trigger with a comment PG Bug reporting form <noreply@postgresql.org>
2020-04-07 20:10 ` Re: BUG #16346: pg_upgrade fails on a trigger with a comment Hamid Akhtar <hamid.akhtar@gmail.com>
@ 2020-04-08 07:21 ` Hamid Akhtar <hamid.akhtar@gmail.com>
2020-04-08 15:34 ` Re: BUG #16346: pg_upgrade fails on a trigger with a comment Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Hamid Akhtar @ 2020-04-08 07:21 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
I have tested the patch in a little more detail.
(1) Verified that it fixes the bug
(2) Ran regression tests; all are passing.
To recap, the attached patch moves restoration of comments to the
RESTORE_PASS_POST_ACL. This ensures that comments are
restored in a PASS when essentially all required objects are created
including event triggers and materialized views (and any other db
objects).
This patch is good from my side.
On Wed, Apr 8, 2020 at 1:10 AM Hamid Akhtar <hamid.akhtar@gmail.com> wrote:
> As you have mentioned, I have verified that indeed commit 4c40b27b broke
> this.
>
> In this particular commit moves restoration of materialized views and
> event triggers to the last phase. Perhaps, comments should also be moved to
> this phase as there may comments on either of these types of objects.
>
> Attached is a patch that resolves this issue. I've verified that it
> resolve the upgrade (and restore issue) introduced by 4c40b27b. I'll test
> this patch in a little more detail tomorrow.
>
> On Mon, Apr 6, 2020 at 8:26 PM PG Bug reporting form <
> noreply@postgresql.org> wrote:
>
>> The following bug has been logged on the website:
>>
>> Bug reference: 16346
>> Logged by: Alexander Lakhin
>> Email address: exclusion@gmail.com
>> PostgreSQL version: 12.2
>> Operating system: Ubuntu 18.04
>> Description:
>>
>> When using pg_upgrade on a database with the following contents:
>> CREATE FUNCTION public.test_event_trigger() RETURNS event_trigger
>> LANGUAGE plpgsql
>> AS $$
>> BEGIN
>> RAISE NOTICE 'test_event_trigger: % %', tg_event, tg_tag;
>> END
>> $$;
>>
>> CREATE EVENT TRIGGER regress_event_trigger3 ON ddl_command_start
>> EXECUTE PROCEDURE public.test_event_trigger();
>>
>> COMMENT ON EVENT TRIGGER regress_event_trigger3 IS 'test comment';
>>
>> I get:
>> Restoring global objects in the new cluster ok
>> Restoring database schemas in the new cluster
>> postgres
>> *failure*
>>
>> Consult the last few lines of "pg_upgrade_dump_14174.log" for
>> the probable cause of the failure.
>> Failure, exiting
>>
>> pg_upgrade_dump_14174.log contains:
>> command: "/src/postgres/tmp_install/usr/local/pgsql/bin/pg_restore" --host
>> /src/postgres --port 50432 --username postgres --clean --create
>> --exit-on-error --verbose --dbname template1
>> "pg_upgrade_dump_14174.custom"
>> >> "pg_upgrade_dump_14174.log" 2>&1
>> pg_restore: connecting to database for restore
>> pg_restore: dropping DATABASE PROPERTIES postgres
>> pg_restore: dropping DATABASE postgres
>> pg_restore: creating DATABASE "postgres"
>> pg_restore: connecting to new database "postgres"
>> pg_restore: connecting to database "postgres" as user "postgres"
>> pg_restore: creating COMMENT "DATABASE "postgres""
>> pg_restore: creating DATABASE PROPERTIES "postgres"
>> pg_restore: connecting to new database "postgres"
>> pg_restore: connecting to database "postgres" as user "postgres"
>> pg_restore: creating pg_largeobject "pg_largeobject"
>> pg_restore: creating FUNCTION "public.test_event_trigger()"
>> pg_restore: creating COMMENT "EVENT TRIGGER "regress_event_trigger3""
>> pg_restore: while PROCESSING TOC:
>> pg_restore: from TOC entry 3705; 0 0 COMMENT EVENT TRIGGER
>> "regress_event_trigger3" postgres
>> pg_restore: error: could not execute query: ERROR: event trigger
>> "regress_event_trigger3" does not exist
>> Command was: COMMENT ON EVENT TRIGGER "regress_event_trigger3" IS 'test
>> comment';
>>
>> It looks like the commit 4c40b27b broke this.
>>
>>
>
> --
> Highgo Software (Canada/China/Pakistan)
> URL : www.highgo.ca
> ADDR: 10318 WHALLEY BLVD, Surrey, BC
> CELL：+923335449950 EMAIL: mailto:hamid.akhtar@highgo.ca
> SKYPE: engineeredvirus
>
--
Highgo Software (Canada/China/Pakistan)
URL : www.highgo.ca
ADDR: 10318 WHALLEY BLVD, Surrey, BC
CELL：+923335449950 EMAIL: mailto:hamid.akhtar@highgo.ca
SKYPE: engineeredvirus
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #16346: pg_upgrade fails on a trigger with a comment
2020-04-06 15:00 BUG #16346: pg_upgrade fails on a trigger with a comment PG Bug reporting form <noreply@postgresql.org>
2020-04-07 20:10 ` Re: BUG #16346: pg_upgrade fails on a trigger with a comment Hamid Akhtar <hamid.akhtar@gmail.com>
2020-04-08 07:21 ` Re: BUG #16346: pg_upgrade fails on a trigger with a comment Hamid Akhtar <hamid.akhtar@gmail.com>
@ 2020-04-08 15:34 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2020-04-08 15:34 UTC (permalink / raw)
To: Hamid Akhtar <hamid.akhtar@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
Hamid Akhtar <hamid.akhtar@gmail.com> writes:
> To recap, the attached patch moves restoration of comments to the
> RESTORE_PASS_POST_ACL. This ensures that comments are
> restored in a PASS when essentially all required objects are created
> including event triggers and materialized views (and any other db
> objects).
This is surely not a good idea as it stands, because it delays restore
of *all* object comments to the very end. That's not nice for parallel
restores, and it also has large impact on pg_dump's behavior in cases
that have nothing to do with event triggers; which could cause unforeseen
problems.
The right way is to postpone only event trigger comments. I fixed it
that way and pushed it.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2020-04-08 15:34 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-04-06 15:00 BUG #16346: pg_upgrade fails on a trigger with a comment PG Bug reporting form <noreply@postgresql.org>
2020-04-07 20:10 ` Hamid Akhtar <hamid.akhtar@gmail.com>
2020-04-08 07:21 ` Hamid Akhtar <hamid.akhtar@gmail.com>
2020-04-08 15:34 ` 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