pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
pg_upgrade --check doesn't check pg_pltemplate modifications
2+ messages / 2 participants
[nested] [flat]

* pg_upgrade --check doesn't check pg_pltemplate modifications
@ 2022-01-16 13:05 Tomas Barton <barton.tomas@gmail.com>
  2022-01-16 16:43 ` Re: pg_upgrade --check doesn't check pg_pltemplate modifications Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Tomas Barton @ 2022-01-16 13:05 UTC (permalink / raw)
  To: pgsql-bugs

Hi,

when upgrading postgresql 12 -> 13 the pg_upgrade --check doesn't
sufficiently check for pg_pltemplate modifications (at least in version
13.5-1.pgdg110+1).

There's been a discussion on the mailing list
https://postgrespro.com/list/thread-id/2521269 regarding this topic. But it
doesn't seem to be implemented in the upgrade process.

Here's a simple bash script that checks for pg_pltemplate modifications on
each database:

for db in $(psql -tc "SELECT datname FROM pg_database;")
do
  if [[ "${db}" != "template0" ]]; then
    dump=$(pg_dump --schema-only --quote-all-identifiers ${db} | grep
pg_pltemplate)
    if [ ! -z "$dump" ]; then
        echo "ERROR: ${db} contains pg_pltemplate modifications. pg_upgrade
will fail"
        exit 1
    fi
  fi
done
echo "OK"

Any non-default GRANT/REVOKE like these:

REVOKE SELECT ON TABLE "pg_catalog"."pg_pltemplate" FROM PUBLIC;
GRANT SELECT ON TABLE "pg_catalog"."pg_pltemplate" TO "reader";

would break the upgrade process, even though the pg_upgrade --check says:

*Clusters are compatible*

then upgrade (at least with --link) fails

pg_restore: error: could not execute query: ERROR:  relation
"pg_catalog.pg_pltemplate" does not exist

Including such checks in the upgrade process might make the upgrade path
much easier.

Tomas Barton

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: pg_upgrade --check doesn't check pg_pltemplate modifications
  2022-01-16 13:05 pg_upgrade --check doesn't check pg_pltemplate modifications Tomas Barton <barton.tomas@gmail.com>
@ 2022-01-16 16:43 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2022-01-16 16:43 UTC (permalink / raw)
  To: Tomas Barton <barton.tomas@gmail.com>; +Cc: pgsql-bugs

Tomas Barton <barton.tomas@gmail.com> writes:
> Any non-default GRANT/REVOKE like these:

> REVOKE SELECT ON TABLE "pg_catalog"."pg_pltemplate" FROM PUBLIC;
> GRANT SELECT ON TABLE "pg_catalog"."pg_pltemplate" TO "reader";

Uh ... why would you do that?  It seems only academically
different from randomly deleting some catalog entries.

> would break the upgrade process, even though the pg_upgrade --check says:
> *Clusters are compatible*

pg_upgrade has never promised to detect every possible problem.
Considering that pg_pltemplate is gone entirely in v13 and up,
I doubt we're going to want to invest a lot of effort here.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2022-01-16 16:43 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-01-16 13:05 pg_upgrade --check doesn't check pg_pltemplate modifications Tomas Barton <barton.tomas@gmail.com>
2022-01-16 16:43 ` 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