Thread: pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me
Hi,
I have attempted a pg_upgrade on Debian using the Debian
wrapper scripts like so:
pg_upgradecluster -v 9.5 9.4 main
(meaning to upgrade a cluster named "main" from 9.4 to 9.5)
which resulted in this:
-----------------------------------------------------------------
pg_upgrade run on Fri Jan 8 11:47:32 2016
-----------------------------------------------------------------
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for reg* system OID user data types ok
Checking for contrib/isn with bigint-passing mismatch ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.
Performing Upgrade
------------------
Analyzing all rows in the new cluster ok
Freezing all rows on the new cluster ok
Deleting files from new pg_clog ok
Copying old pg_clog to new server ok
Setting next transaction ID and epoch for new cluster ok
Deleting files from new pg_multixact/offsets ok
Copying old pg_multixact/offsets to new server ok
Deleting files from new pg_multixact/members ok
Copying old pg_multixact/members to new server ok
Setting next multixact ID and offset for new cluster ok
Resetting WAL archives ok
Setting frozenxid and minmxid counters in new cluster ok
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster
*failure*
Consult the last few lines of "pg_upgrade_dump_512600.log" for
the probable cause of the failure.
-----------------------------------------------------------------
The pg_upgrade_dump_512600.log shows:
command: "/usr/lib/postgresql/9.5/bin/pg_dump" --host "/var/log/postgresql/pg_upgradecluster-9.4-9.5-main.nb6n"
--port5432 --username "postgres" --schema-only --quote-all-identifiers --binary-upgrade --format=custom
--file="pg_upgrade_dump_512600.custom""gnumed_v20" >> "pg_upgrade_dump_512600.log" 2>&1
command: "/usr/lib/postgresql/9.5/bin/pg_restore" --host "/var/log/postgresql/pg_upgradecluster-9.4-9.5-main.nb6n"
--port5433 --username "postgres" --exit-on-error --verbose --dbname "gnumed_v20" "pg_upgrade_dump_512600.custom" >>
"pg_upgrade_dump_512600.log"2>&1
pg_restore: verbinde mit der Datenbank zur Wiederherstellung
pg_restore: erstelle pg_largeobject „pg_largeobject“
pg_restore: erstelle pg_largeobject_metadata „pg_largeobject_metadata“
pg_restore: erstelle SCHEMA „au“
pg_restore: erstelle SCHEMA „audit“
pg_restore: erstelle SCHEMA „bill“
pg_restore: erstelle COMMENT „SCHEMA "bill"“
pg_restore: erstelle SCHEMA „blobs“
pg_restore: erstelle SCHEMA „cfg“
pg_restore: erstelle COMMENT „SCHEMA "cfg"“
pg_restore: erstelle SCHEMA „clin“
pg_restore: erstelle SCHEMA „de_de“
pg_restore: erstelle SCHEMA „dem“
pg_restore: erstelle SCHEMA „gm“
pg_restore: erstelle SCHEMA „i18n“
pg_restore: erstelle SCHEMA „public“
pg_restore: erstelle COMMENT „SCHEMA "public"“
pg_restore: erstelle SCHEMA „ref“
pg_restore: erstelle COMMENT „SCHEMA "ref"“
pg_restore: erstelle SCHEMA „staging“
pg_restore: erstelle COMMENT „SCHEMA "staging"“
pg_restore: erstelle EXTENSION „pg_trgm“
pg_restore: erstelle COMMENT „EXTENSION "pg_trgm"“
pg_restore: erstelle FUNCTION „pg_catalog.gtrgm_in("cstring")“
pg_restore: [Archivierer (DB)] Fehler in Phase PROCESSING TOC:
pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 893; 1255 511230 FUNCTION gtrgm_in("cstring")
postgres
pg_restore: [Archivierer (DB)] could not execute query: ERROR: pg_type OID value not set when in binary upgrade
mode
Die Anweisung war: CREATE FUNCTION "gtrgm_in"("cstring") RETURNS "gtrgm"
LANGUAGE "c" IMMUTABLE STRICT
AS '$libdir/pg_trgm', 'gtrgm_in'...
I do have pg_trgm installed in the 9.4 cluster for use with
the gnumed_vXX databases.
The relevant 9.5 Debian package containing pg_trgm for PG 9.5
(postgresql-contrib-9.5) is installed.
I am running this with a libpq compiled against PG 9.5.
For one thing - does it seem odd that the function would be
named "gtrgm_in" rather than "pgtrgm_in" ?
Anything else that seems off from the information given above?
What other information do I need to provide ?
(For what it's worth, I have also tried the --method=dump way
of using Debian's pg_upgradecluster which internally uses a
dump/restore cycle rather than calling pg_upgrade. That
failed due to ordering problems with table data vs table
constraints.)
Thanks a lot for any advice,
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Fri, Jan 08, 2016 at 12:45:29PM +0100, Karsten Hilbert wrote:
> pg_restore: erstelle EXTENSION „pg_trgm“
> pg_restore: erstelle COMMENT „EXTENSION "pg_trgm"“
> pg_restore: erstelle FUNCTION „pg_catalog.gtrgm_in("cstring")“
> pg_restore: [Archivierer (DB)] Fehler in Phase PROCESSING TOC:
> pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 893; 1255 511230 FUNCTION gtrgm_in("cstring")
postgres
> pg_restore: [Archivierer (DB)] could not execute query: ERROR: pg_type OID value not set when in binary upgrade
mode
> Die Anweisung war: CREATE FUNCTION "gtrgm_in"("cstring") RETURNS "gtrgm"
> LANGUAGE "c" IMMUTABLE STRICT
> AS '$libdir/pg_trgm', 'gtrgm_in'...
>
> For one thing - does it seem odd that the function would be
> named "gtrgm_in" rather than "pgtrgm_in" ?
A bit of searching shows that that seems to be normal.
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Fri, Jan 08, 2016 at 12:45:29PM +0100, Karsten Hilbert wrote:
> pg_restore: erstelle EXTENSION „pg_trgm“
> pg_restore: erstelle COMMENT „EXTENSION "pg_trgm"“
> pg_restore: erstelle FUNCTION „pg_catalog.gtrgm_in("cstring")“
> pg_restore: [Archivierer (DB)] Fehler in Phase PROCESSING TOC:
> pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 893; 1255 511230 FUNCTION gtrgm_in("cstring")
postgres
> pg_restore: [Archivierer (DB)] could not execute query: ERROR: pg_type OID value not set when in binary upgrade
mode
> Die Anweisung war: CREATE FUNCTION "gtrgm_in"("cstring") RETURNS "gtrgm"
> LANGUAGE "c" IMMUTABLE STRICT
> AS '$libdir/pg_trgm', 'gtrgm_in'...
It does sound similar to
http://postgresql.nabble.com/BUG-5942-pg-trgm-sql-has-cyclic-dependency-on-type-gtrgm-creation-td4259677.html
which, however, wouldn't help me in solving the problem.
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On 01/08/2016 03:45 AM, Karsten Hilbert wrote:
> Hi,
>
> I have attempted a pg_upgrade on Debian using the Debian
> wrapper scripts like so:
>
> pg_upgradecluster -v 9.5 9.4 main
>
> (meaning to upgrade a cluster named "main" from 9.4 to 9.5)
>
> which resulted in this:
>
> -----------------------------------------------------------------
> pg_upgrade run on Fri Jan 8 11:47:32 2016
> -----------------------------------------------------------------
>
> Performing Consistency Checks
> -----------------------------
> Checking cluster versions ok
> Checking database user is the install user ok
> Checking database connection settings ok
> Checking for prepared transactions ok
> Checking for reg* system OID user data types ok
> Checking for contrib/isn with bigint-passing mismatch ok
> Creating dump of global objects ok
> Creating dump of database schemas
> ok
> Checking for presence of required libraries ok
> Checking database user is the install user ok
> Checking for prepared transactions ok
>
> If pg_upgrade fails after this point, you must re-initdb the
> new cluster before continuing.
>
> Performing Upgrade
> ------------------
> Analyzing all rows in the new cluster ok
> Freezing all rows on the new cluster ok
> Deleting files from new pg_clog ok
> Copying old pg_clog to new server ok
> Setting next transaction ID and epoch for new cluster ok
> Deleting files from new pg_multixact/offsets ok
> Copying old pg_multixact/offsets to new server ok
> Deleting files from new pg_multixact/members ok
> Copying old pg_multixact/members to new server ok
> Setting next multixact ID and offset for new cluster ok
> Resetting WAL archives ok
> Setting frozenxid and minmxid counters in new cluster ok
> Restoring global objects in the new cluster ok
> Restoring database schemas in the new cluster
>
> *failure*
> Consult the last few lines of "pg_upgrade_dump_512600.log" for
> the probable cause of the failure.
>
> -----------------------------------------------------------------
>
> The pg_upgrade_dump_512600.log shows:
>
> command: "/usr/lib/postgresql/9.5/bin/pg_dump" --host "/var/log/postgresql/pg_upgradecluster-9.4-9.5-main.nb6n"
--port5432 --username "postgres" --schema-only --quote-all-identifiers --binary-upgrade --format=custom
--file="pg_upgrade_dump_512600.custom""gnumed_v20" >> "pg_upgrade_dump_512600.log" 2>&1
>
> command: "/usr/lib/postgresql/9.5/bin/pg_restore" --host
"/var/log/postgresql/pg_upgradecluster-9.4-9.5-main.nb6n"--port 5433 --username "postgres" --exit-on-error --verbose
--dbname"gnumed_v20" "pg_upgrade_dump_512600.custom" >> "pg_upgrade_dump_512600.log" 2>&1
>
> pg_restore: verbinde mit der Datenbank zur Wiederherstellung
> pg_restore: erstelle pg_largeobject „pg_largeobject“
> pg_restore: erstelle pg_largeobject_metadata „pg_largeobject_metadata“
> pg_restore: erstelle SCHEMA „au“
> pg_restore: erstelle SCHEMA „audit“
> pg_restore: erstelle SCHEMA „bill“
> pg_restore: erstelle COMMENT „SCHEMA "bill"“
> pg_restore: erstelle SCHEMA „blobs“
> pg_restore: erstelle SCHEMA „cfg“
> pg_restore: erstelle COMMENT „SCHEMA "cfg"“
> pg_restore: erstelle SCHEMA „clin“
> pg_restore: erstelle SCHEMA „de_de“
> pg_restore: erstelle SCHEMA „dem“
> pg_restore: erstelle SCHEMA „gm“
> pg_restore: erstelle SCHEMA „i18n“
> pg_restore: erstelle SCHEMA „public“
> pg_restore: erstelle COMMENT „SCHEMA "public"“
> pg_restore: erstelle SCHEMA „ref“
> pg_restore: erstelle COMMENT „SCHEMA "ref"“
> pg_restore: erstelle SCHEMA „staging“
> pg_restore: erstelle COMMENT „SCHEMA "staging"“
> pg_restore: erstelle EXTENSION „pg_trgm“
> pg_restore: erstelle COMMENT „EXTENSION "pg_trgm"“
> pg_restore: erstelle FUNCTION „pg_catalog.gtrgm_in("cstring")“
> pg_restore: [Archivierer (DB)] Fehler in Phase PROCESSING TOC:
> pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 893; 1255 511230 FUNCTION gtrgm_in("cstring")
postgres
> pg_restore: [Archivierer (DB)] could not execute query: ERROR: pg_type OID value not set when in binary upgrade
mode
> Die Anweisung war: CREATE FUNCTION "gtrgm_in"("cstring") RETURNS "gtrgm"
> LANGUAGE "c" IMMUTABLE STRICT
> AS '$libdir/pg_trgm', 'gtrgm_in'...
>
>
> I do have pg_trgm installed in the 9.4 cluster for use with
> the gnumed_vXX databases.
>
> The relevant 9.5 Debian package containing pg_trgm for PG 9.5
> (postgresql-contrib-9.5) is installed.
>
> I am running this with a libpq compiled against PG 9.5.
>
> For one thing - does it seem odd that the function would be
> named "gtrgm_in" rather than "pgtrgm_in" ?
>
> Anything else that seems off from the information given above?
>
> What other information do I need to provide ?
>
> (For what it's worth, I have also tried the --method=dump way
> of using Debian's pg_upgradecluster which internally uses a
> dump/restore cycle rather than calling pg_upgrade. That
> failed due to ordering problems with table data vs table
> constraints.)
I thought --method=dump was the default, so this:
pg_upgradecluster -v 9.5 9.4 main
was using that?
At any rate:
http://www.postgresql.org/docs/9.5/interactive/pgupgrade.html
"If an error occurs while restoring the database schema, pg_upgrade will
exit and you will have to revert to the old cluster as outlined in step
16 below. To try pg_upgrade again, you will need to modify the old
cluster so the pg_upgrade schema restore succeeds. If the problem is a
contrib module, you might need to uninstall the contrib module from the
old cluster and install it in the new cluster after the upgrade,
assuming the module is not being used to store user data."
>
> Thanks a lot for any advice,
> Karsten
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 01/08/2016 07:28 AM, Karsten Hilbert wrote: Ccing list > On Fri, Jan 08, 2016 at 07:18:24AM -0800, Adrian Klaver wrote: > >> I thought --method=dump was the default, so this: >> >> pg_upgradecluster -v 9.5 9.4 main >> >> was using that? > > True enough. I did specify the "-m upgrade" though, as > witnessed by the log snippet. Alright then. Just trying to match the output with the command and quiet the nagging voice in the head:) > > Karsten > -- Adrian Klaver adrian.klaver@aklaver.com
On Fri, Jan 08, 2016 at 07:18:24AM -0800, Adrian Klaver wrote: > http://www.postgresql.org/docs/9.5/interactive/pgupgrade.html > > "If an error occurs while restoring the database schema, pg_upgrade will > exit and you will have to revert to the old cluster as outlined in step 16 > below. Thanks for pointing this out. Debian made it fairly easy to pick up the old cluster (they provide quite nice wrappers). I am firmly seated on 9.4 again, which in itself has been doing excellent duty. > To try pg_upgrade again, you will need to modify the old cluster so > the pg_upgrade schema restore succeeds. If the problem is a contrib module, > you might need to uninstall the contrib module from the old cluster and > install it in the new cluster after the upgrade, assuming the module is not > being used to store user data." I am, indeed, using pg_trgm for an index on patients' names so I will likely have to DROP / CREATE EXTENSION for upgrading the cluster, and re-create the index after the upgrade :-( No problem for me but will need meticulous documentation and instructions to end users (GPs, physical therapists ... ;-) Thanks for answering, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Fri, Jan 08, 2016 at 07:41:09AM -0800, Adrian Klaver wrote: > >On Fri, Jan 08, 2016 at 07:18:24AM -0800, Adrian Klaver wrote: > > > >>I thought --method=dump was the default, so this: > >> > >>pg_upgradecluster -v 9.5 9.4 main > >> > >>was using that? > > > >True enough. I did specify the "-m upgrade" though, as > >witnessed by the log snippet. > > Alright then. Just trying to match the output with the command and quiet the > nagging voice in the head:) Absolutely. I should have paid that extra second of *re*-checking before I sent the initial question. Sorry. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Fri, Jan 08, 2016 at 07:41:09AM -0800, Adrian Klaver wrote:
> >>I thought --method=dump was the default, so this:
> >>
> >>pg_upgradecluster -v 9.5 9.4 main
> >>
> >>was using that?
> >
> >True enough. I did specify the "-m upgrade" though, as
> >witnessed by the log snippet.
>
> Alright then. Just trying to match the output with the command and quiet the
> nagging voice in the head:)
For completeness, here's the actual command run
pg_upgradecluster -m upgrade -v 9.5 9.4 main &> pg-upgrade-9_4-9_5.log
(not that that would help along any, I suppose)
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
> [ pg_upgrade failed on pg_trgm ]
Just for completeness, can you tell us which pg_trgm version (1.0
or 1.1) is installed in the 9.4 database?
> (For what it's worth, I have also tried the --method=dump way
> of using Debian's pg_upgradecluster which internally uses a
> dump/restore cycle rather than calling pg_upgrade. That
> failed due to ordering problems with table data vs table
> constraints.)
That seems like an independent bug. Can you provide specifics?
regards, tom lane
On 01/08/2016 07:41 AM, Karsten Hilbert wrote: > On Fri, Jan 08, 2016 at 07:18:24AM -0800, Adrian Klaver wrote: > >> http://www.postgresql.org/docs/9.5/interactive/pgupgrade.html >> >> "If an error occurs while restoring the database schema, pg_upgrade will >> exit and you will have to revert to the old cluster as outlined in step 16 >> below. > > Thanks for pointing this out. Debian made it fairly easy to > pick up the old cluster (they provide quite nice wrappers). I > am firmly seated on 9.4 again, which in itself has been doing > excellent duty. > >> To try pg_upgrade again, you will need to modify the old cluster so >> the pg_upgrade schema restore succeeds. If the problem is a contrib module, >> you might need to uninstall the contrib module from the old cluster and >> install it in the new cluster after the upgrade, assuming the module is not >> being used to store user data." > > I am, indeed, using pg_trgm for an index on patients' names > so I will likely have to DROP / CREATE EXTENSION for > upgrading the cluster, and re-create the index after the > upgrade :-( I do not use pg_trgm, so I have not had occasion to upgrade it. Maybe someone who has can provide a better method. > > No problem for me but will need meticulous documentation and > instructions to end users (GPs, physical therapists ... ;-) > > Thanks for answering, > Karsten > -- Adrian Klaver adrian.klaver@aklaver.com
On Fri, Jan 08, 2016 at 10:45:27AM -0500, Tom Lane wrote: > Karsten Hilbert <Karsten.Hilbert@gmx.net> writes: > > (For what it's worth, I have also tried the --method=dump way > > of using Debian's pg_upgradecluster which internally uses a > > dump/restore cycle rather than calling pg_upgrade. That > > failed due to ordering problems with table data vs table > > constraints.) > > That seems like an independent bug. Can you provide specifics? I will, please bear with me as I'll have to rerun the upgrade to get logs. Thanks, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Fri, Jan 08, 2016 at 10:45:27AM -0500, Tom Lane wrote:
> Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
> > [ pg_upgrade failed on pg_trgm ]
>
> Just for completeness, can you tell us which pg_trgm version (1.0
> or 1.1) is installed in the 9.4 database?
Sure:
(pg_trgm,1.1,"text similarity measurement and index searching based on trigrams")
taken from:
pg_available_extensions
--------------------------------------------------------------------------------------
(moddatetime,1.0,"functions for tracking last modification time")
(pg_freespacemap,1.0,"examine the free space map (FSM)")
(earthdistance,1.0,"calculate great-circle distances on the surface of the Earth")
(test_shm_mq,1.0,"Test code for shared memory message queues")
(tablefunc,1.0,"functions that manipulate whole tables, including crosstab")
(uuid-ossp,1.0,"generate universally unique identifiers (UUIDs)")
(pageinspect,1.2,"inspect the contents of database pages at a low level")
(isn,1.0,"data types for international product numbering standards")
(pgrowlocks,1.1,"show row-level locking information")
(pgagent,3.4,"A PostgreSQL job scheduler")
(tcn,1.0,"Triggered change notifications")
(unaccent,1.0,"text search dictionary that removes accents")
(pg_stat_statements,1.2,"track execution statistics of all SQL statements executed")
(dblink,1.1,"connect to other PostgreSQL databases from within a database")
(insert_username,1.0,"functions for tracking who changed a table")
(fuzzystrmatch,1.0,"determine similarities and distance between strings")
(pg_buffercache,1.0,"examine the shared buffer cache")
(timetravel,1.0,"functions for implementing time travel")
(cube,1.0,"data type for multidimensional cubes")
(pg_trgm,1.1,"text similarity measurement and index searching based on trigrams")
(dict_int,1.0,"text search dictionary template for integers")
(xml2,1.0,"XPath querying and XSLT")
(sslinfo,1.0,"information about SSL certificates")
(btree_gin,1.0,"support for indexing common datatypes in GIN")
(btree_gist,1.0,"support for indexing common datatypes in GiST")
(tsearch2,1.0,"compatibility package for pre-8.3 text search functions")
(test_parser,1.0,"example of a custom parser for full-text search")
(seg,1.0,"data type for representing line segments or floating-point intervals")
(citext,1.0,"data type for case-insensitive character strings")
(intarray,1.0,"functions, operators, and index support for 1-D arrays of integers")
(worker_spi,1.0,"Sample background worker")
(file_fdw,1.0,"foreign-data wrapper for flat file access")
(dict_xsyn,1.0,"text search dictionary template for extended synonym processing")
(intagg,1.0,"integer aggregator and enumerator (obsolete)")
(pgstattuple,1.2,"show tuple-level statistics")
(autoinc,1.0,"functions for autoincrementing fields")
(pg_prewarm,1.0,"prewarm relation data")
(chkpass,1.0,"data type for auto-encrypted passwords")
(pgcrypto,1.1,"cryptographic functions")
(plpgsql,1.0,"PL/pgSQL procedural language")
(postgres_fdw,1.0,"foreign-data wrapper for remote PostgreSQL servers")
(adminpack,1.0,"administrative functions for PostgreSQL")
(hstore,1.3,"data type for storing sets of (key, value) pairs")
(ltree,1.0,"data type for hierarchical tree-like structures")
(lo,1.0,"Large Object maintenance")
(refint,1.0,"functions for implementing referential integrity (obsolete)")
(46 Zeilen)
Diving into the postgresql-contrib-9.5 package shows that it
seems to install the same version (1.1, that is).
4c5dc5fb5743dd4534cc0ad082c075d8 usr/share/postgresql/9.5/extension/pg_trgm--1.0--1.1.sql
5222fd4cbbc5049b8e1bc64817443d7b usr/share/postgresql/9.5/extension/pg_trgm--1.1.sql
aceed02fc9730e6d34000869e6dfa308 usr/share/postgresql/9.5/extension/pg_trgm--unpackaged--1.0.sql
f81af8d3825cb3a1762b9a27d0899b38 usr/share/postgresql/9.5/extension/pg_trgm.control
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
> On Fri, Jan 08, 2016 at 10:45:27AM -0500, Tom Lane wrote:
>> Just for completeness, can you tell us which pg_trgm version (1.0
>> or 1.1) is installed in the 9.4 database?
> Sure:
> (pg_trgm,1.1,"text similarity measurement and index searching based on trigrams")
Hm. I just tried running a pg_upgrade here on a 9.4 database containing
pg_trgm 1.1, and didn't see any particular problem, so there's some
additional factor needed to cause your result. Hard to tell what.
Can you think of anything unusual about the history of your installation?
regards, tom lane
On Fri, Jan 08, 2016 at 11:12:09AM -0500, Tom Lane wrote: > > Sure: > > (pg_trgm,1.1,"text similarity measurement and index searching based on trigrams") > > Hm. I just tried running a pg_upgrade here on a 9.4 database containing > pg_trgm 1.1, and didn't see any particular problem, so there's some > additional factor needed to cause your result. Hard to tell what. > Can you think of anything unusual about the history of your installation? No, other than that that cluster has been upgraded all the way from, I think, 8.4 over several Debian releases ;) Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
> On Fri, Jan 08, 2016 at 11:12:09AM -0500, Tom Lane wrote:
>> Hm. I just tried running a pg_upgrade here on a 9.4 database containing
>> pg_trgm 1.1, and didn't see any particular problem, so there's some
>> additional factor needed to cause your result. Hard to tell what.
>> Can you think of anything unusual about the history of your installation?
> No, other than that that cluster has been upgraded all the
> way from, I think, 8.4 over several Debian releases ;)
A suggestion is to run the pg_upgrade with -r switch, which will leave a
litter of files in your working directory. Some of them will be named
like pg_upgrade_dump_NNN.custom and should be custom-format, schema-only
dumps of your 9.4 installation's databases. If you'd be willing to send
those to me off-list, maybe I could figure out what's happening.
It occurs to me that this might actually be related to the issue you
saw in "dump" mode --- if there's some unresolved circular dependency,
it could cause pg_dump to dump things in an unexpected order, which
could possibly explain the message we're seeing. But that's just a
guess.
regards, tom lane
I wrote:
> A suggestion is to run the pg_upgrade with -r switch, which will leave a
> litter of files in your working directory. Some of them will be named
> like pg_upgrade_dump_NNN.custom and should be custom-format, schema-only
> dumps of your 9.4 installation's databases. If you'd be willing to send
> those to me off-list, maybe I could figure out what's happening.
> It occurs to me that this might actually be related to the issue you
> saw in "dump" mode --- if there's some unresolved circular dependency,
> it could cause pg_dump to dump things in an unexpected order, which
> could possibly explain the message we're seeing. But that's just a
> guess.
BTW, there will also be .log files, which might contain useful information
as well, especially if any of it is bleats from pg_dump about being unable
to break a circular dependency.
regards, tom lane
Again, as the list software doesn't like "config" at the start of a line. Karsten On Fri, Jan 08, 2016 at 05:34:00PM +0100, Karsten Hilbert wrote: > > > (For what it's worth, I have also tried the --method=dump way > > > of using Debian's pg_upgradecluster which internally uses a > > > dump/restore cycle rather than calling pg_upgrade. That > > > failed due to ordering problems with table data vs table > > > constraints.) > > > > That seems like an independent bug. Can you provide specifics? > > Attached the log of > > pg_upgradecluster -v 9.5 9.4 main &> pg-upgrade-9_4-9_5-dump_restore.log > > and here is the function that leads to the schema having a > dependancy on table data: > > create or replace function gm.account_is_dbowner_or_staff(_account name) > returns boolean > language plpgsql > as ' > DECLARE > _is_owner boolean; > BEGIN > -- is _account member of current db group ? > -- PERFORM 1 FROM pg_auth_members > -- WHERE > -- roleid = (SELECT oid FROM pg_roles WHERE rolname = current_database()) > -- AND > -- member = (SELECT oid FROM pg_roles WHERE rolname = _account) > -- ; > -- IF FOUND THEN > -- -- should catch people on staff, gm-dbo, and postgres > -- RETURN TRUE; > -- END IF; > > -- postgres > IF _account = ''postgres'' THEN > RETURN TRUE; > END IF; > > -- on staff list > PERFORM 1 FROM dem.staff WHERE db_user = _account; > IF FOUND THEN > RETURN TRUE; > END IF; > > -- owner > SELECT pg_catalog.pg_get_userbyid(datdba) = _account INTO STRICT _is_owner FROM pg_catalog.pg_database WHERE datname= current_database(); > IF _is_owner IS TRUE THEN > RETURN TRUE; > END IF; > > -- neither > RAISE EXCEPTION > ''gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff'', _account > USING ERRCODE = ''integrity_constraint_violation'' > ; > RETURN FALSE; > END;'; > > The function is used on audit tables: > > alter table audit.audit_fields > drop constraint if exists > audit_audit_fields_sane_modified_by cascade; > > alter table audit.audit_fields > add constraint audit_audit_fields_sane_modified_by check > (gm.account_is_dbowner_or_staff(modified_by) IS TRUE) > ; > > Karsten > -- > GPG key ID E4071346 @ eu.pool.sks-keyservers.net > E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Attachment
Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
> On Fri, Jan 08, 2016 at 11:23:21AM -0500, Tom Lane wrote:
>> A suggestion is to run the pg_upgrade with -r switch, which will leave a
>> litter of files in your working directory. Some of them will be named
>> like pg_upgrade_dump_NNN.custom and should be custom-format, schema-only
>> dumps of your 9.4 installation's databases. If you'd be willing to send
>> those to me off-list, maybe I could figure out what's happening.
> The list stalled the attachment so here as PM.
Well, you shouldn't have tried to send it to the list; there's no need
to memorialize half a megabyte of transient data in the archives.
After digging through this, I figured out the problem: you'd installed
pg_trgm into the pg_catalog schema, whereas when I was testing I'd just
dropped it into the public schema. That confuses pg_dump into not
emitting the shell type that it should emit. It's an easy fix now
that I see the problem.
This bug does *not*, AFAICT, explain any problem you might have with
"dump" transfers, only with pg_upgrade.
regards, tom lane
Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
>> and here is the function that leads to the schema having a
>> dependancy on table data:
Hm. So, by having installed this function as a check constraint, you have
created a data dependency that pg_dump has no way to know about. It's
going to load the tables in some order that's chosen without regard to the
need for dem.staff to be populated first. This is not a pg_dump bug.
In general, embedding lookups of other tables into CHECK constraints
is going to cause you all kinds of grief quite aside from pg_dump
not understanding it, because the backend doesn't really understand it
either. If the other table changes, causing the CHECK expression to
fail, that will *not* cause anything to happen to the table with the
CHECK constraint. It could well be that pg_dump is loading the tables
in the right order by chance, and the reason you're seeing a failure
is that one or more rows have modified_by values corresponding to
people who no longer are in the staff table.
Can you get rid of dem.staff in favor of something like creating a
"staff" role and GRANT'ing that to appropriate users?
Alternatively, maybe you can make the modified_by column be a foreign
key referencing a table of users (it probably couldn't be defined
quite like "staff", but you get the idea). The presence of the foreign
key would be enough to cue pg_dump about load order.
regards, tom lane
On Fri, Jan 08, 2016 at 10:45:27AM -0500, Tom Lane wrote:
> > (For what it's worth, I have also tried the --method=dump way
> > of using Debian's pg_upgradecluster which internally uses a
> > dump/restore cycle rather than calling pg_upgrade. That
> > failed due to ordering problems with table data vs table
> > constraints.)
>
> That seems like an independent bug. Can you provide specifics?
Attached the log of
pg_upgradecluster -v 9.5 9.4 main &> pg-upgrade-9_4-9_5-dump_restore.log
and here is the function that leads to the schema having a
dependancy on table data:
create or replace function gm.account_is_dbowner_or_staff(_account name)
returns boolean
language plpgsql
as '
DECLARE
_is_owner boolean;
BEGIN
-- is _account member of current db group ?
-- PERFORM 1 FROM pg_auth_members
-- WHERE
-- roleid = (SELECT oid FROM pg_roles WHERE rolname = current_database())
-- AND
-- member = (SELECT oid FROM pg_roles WHERE rolname = _account)
-- ;
-- IF FOUND THEN
-- -- should catch people on staff, gm-dbo, and postgres
-- RETURN TRUE;
-- END IF;
-- postgres
IF _account = ''postgres'' THEN
RETURN TRUE;
END IF;
-- on staff list
PERFORM 1 FROM dem.staff WHERE db_user = _account;
IF FOUND THEN
RETURN TRUE;
END IF;
-- owner
SELECT pg_catalog.pg_get_userbyid(datdba) = _account INTO STRICT _is_owner FROM pg_catalog.pg_database WHERE
datname= current_database();
IF _is_owner IS TRUE THEN
RETURN TRUE;
END IF;
-- neither
RAISE EXCEPTION
''gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff'',
_account
USING ERRCODE = ''integrity_constraint_violation''
;
RETURN FALSE;
END;';
The function is used on audit tables:
alter table audit.audit_fields
drop constraint if exists
audit_audit_fields_sane_modified_by cascade;
alter table audit.audit_fields
add constraint audit_audit_fields_sane_modified_by check
(gm.account_is_dbowner_or_staff(modified_by) IS TRUE)
;
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Attachment
On Fri, Jan 08, 2016 at 11:23:21AM -0500, Tom Lane wrote: > A suggestion is to run the pg_upgrade with -r switch, which will leave a > litter of files in your working directory. Some of them will be named > like pg_upgrade_dump_NNN.custom and should be custom-format, schema-only > dumps of your 9.4 installation's databases. If you'd be willing to send > those to me off-list, maybe I could figure out what's happening. I've got all those as Debian does an excellent job of keeping stuff around when needed. Attached. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Attachment
On Fri, Jan 08, 2016 at 12:53:24PM -0500, Tom Lane wrote:
> >> and here is the function that leads to the schema having a
> >> dependancy on table data:
>
> Hm. So, by having installed this function as a check constraint, you have
> created a data dependency that pg_dump has no way to know about. It's
> going to load the tables in some order that's chosen without regard to the
> need for dem.staff to be populated first. This is not a pg_dump bug.
Yes, I agree.
> In general, embedding lookups of other tables into CHECK constraints
> is going to cause you all kinds of grief quite aside from pg_dump
> not understanding it, because the backend doesn't really understand it
> either. If the other table changes, causing the CHECK expression to
> fail, that will *not* cause anything to happen to the table with the
> CHECK constraint. It could well be that pg_dump is loading the tables
> in the right order by chance, and the reason you're seeing a failure
> is that one or more rows have modified_by values corresponding to
> people who no longer are in the staff table.
Not really but for that I need to deliver more information.
The audit.audit_fields table is part of GNUmed's homegrown,
trigger based audit solution:
- tables have audit tables w/o constraints in the audit. schema
- triggers on tables log UPDATEs/DELETEs into the audit tables
- tables being audited (such as dem.staff) INHERIT from audit.audit_fields
- audit.audit_fields is never inserted into directly (only into child tables)
- audit.audit_fields carries the constraint based on gm.is_dbowner_or_staff()
(so, yes, it is even worse: since dem.staff is audited, and
therefore inherits the check constraint, it depends on itself :-o
Table "dem.staff"
Column | Type | Modifiers |
Storage | Stats target | Description
---------------+--------------------------+-----------------------------------------------------------------------+----------+--------------+-----------------------------------------------------------
pk_audit | integer | not null default nextval('audit.audit_fields_pk_audit_seq'::regclass) |
plain | |
row_version | integer | not null default 0 |
plain | |
modified_when | timestamp with time zone | not null default now() |
plain | |
modified_by | name | not null default "current_user"() |
plain | |
pk | integer | not null default nextval('dem.staff_pk_seq'::regclass) |
plain | |
fk_identity | integer | not null |
plain | |
db_user | name | not null default "current_user"() |
plain | |
short_alias | text | not null |
extended| | a short signature unique to this staff member +
| | |
| | to be used in the GUI, actually this is somewhat+
| | |
| | redundant with ext_person_id...
comment | text | |
extended| |
is_active | boolean | not null default true |
plain | |
Indexes:
"staff_pkey" PRIMARY KEY, btree (pk)
"staff_db_user_key" UNIQUE CONSTRAINT, btree (db_user)
"staff_short_alias_key" UNIQUE CONSTRAINT, btree (short_alias)
Foreign-key constraints:
"staff_fk_identity_fkey" FOREIGN KEY (fk_identity) REFERENCES dem.identity(pk) ON UPDATE CASCADE ON DELETE CASCADE
Referenced by:
TABLE "bill.bill_item" CONSTRAINT "bill_item_fk_provider_fkey" FOREIGN KEY (fk_provider) REFERENCES dem.staff(pk)
ONUPDATE CASCADE ON DELETE RESTRICT
TABLE "blobs.doc_obj" CONSTRAINT "doc_obj_fk_intended_reviewer_fkey" FOREIGN KEY (fk_intended_reviewer) REFERENCES
dem.staff(pk)ON UPDATE CASCADE ON DELETE RESTRICT
TABLE "dem.identity" CONSTRAINT "identity_fk_primary_provider_fkey" FOREIGN KEY (fk_primary_provider) REFERENCES
dem.staff(pk)ON UPDATE CASCADE ON DELETE RESTRICT
TABLE "clin.incoming_data_unmatched" CONSTRAINT "incoming_data_unmatched_fk_provider_disambiguated_fkey" FOREIGN
KEY(fk_provider_disambiguated) REFERENCES dem.staff(pk) ON UPDATE CASCADE ON DELETE RESTRICT
TABLE "ref.keyword_expansion" CONSTRAINT "keyword_expansion_fk_staff_fkey" FOREIGN KEY (fk_staff) REFERENCES
dem.staff(pk)
TABLE "dem.message_inbox" CONSTRAINT "provider_inbox_fk_staff_fkey" FOREIGN KEY (fk_staff) REFERENCES dem.staff(pk)
TABLE "clin.review_root" CONSTRAINT "review_root_fk_reviewer_fkey" FOREIGN KEY (fk_reviewer) REFERENCES
dem.staff(pk)ON UPDATE CASCADE ON DELETE RESTRICT
TABLE "clin.reviewed_test_results" CONSTRAINT "reviewed_test_results_fk_reviewer_fkey" FOREIGN KEY (fk_reviewer)
REFERENCESdem.staff(pk) ON UPDATE CASCADE ON DELETE RESTRICT
TABLE "clin.test_result" CONSTRAINT "test_result_fk_intended_reviewer_fkey" FOREIGN KEY (fk_intended_reviewer)
REFERENCESdem.staff(pk) ON UPDATE CASCADE ON DELETE RESTRICT
TABLE "clin.vaccination" CONSTRAINT "vaccination_fk_provider_fkey" FOREIGN KEY (fk_provider) REFERENCES
dem.staff(pk)ON UPDATE CASCADE ON DELETE RESTRICT
Triggers:
zt_del_staff BEFORE DELETE ON dem.staff FOR EACH ROW EXECUTE PROCEDURE audit.ft_del_staff()
zt_ins_staff BEFORE INSERT ON dem.staff FOR EACH ROW EXECUTE PROCEDURE audit.ft_ins_staff()
zt_upd_staff BEFORE UPDATE ON dem.staff FOR EACH ROW EXECUTE PROCEDURE audit.ft_upd_staff()
Inherits: audit.audit_fields
Table "audit.audit_fields"
Column | Type | Modifiers |
Storage| Stats target | Description
---------------+--------------------------+-----------------------------------------------------------------------+---------+--------------+--------------------------------------------------------
pk_audit | integer | not null default nextval('audit.audit_fields_pk_audit_seq'::regclass) |
plain | |
row_version | integer | not null default 0 |
plain | | the version of the row; mainly just a count
modified_when | timestamp with time zone | not null default now() |
plain | | when has this row been committed (created/modified)
modified_by | name | not null default "current_user"() |
plain | | by whom has this row been committed (created/modified)
Indexes:
"audit_fields_pkey" PRIMARY KEY, btree (pk_audit)
Rules:
audit_fields_no_del AS
ON DELETE TO audit.audit_fields DO INSTEAD NOTHING
audit_fields_no_ins AS
ON INSERT TO audit.audit_fields DO INSTEAD NOTHING
audit_fields_no_upd AS
ON UPDATE TO audit.audit_fields DO INSTEAD NOTHING
Child tables: bill.bill,
bill.bill_item,
blobs.doc_desc,
blobs.doc_med,
blobs.lnk_doc2hospital_stay,
blobs.lnk_doc_med2episode,
cfg.report_query,
clin.allergy_state,
clin.clin_diag,
clin.clin_item_type,
clin.clin_root_item,
clin.encounter,
clin.episode,
clin.external_care,
clin.fhx_relation_type,
clin.form_data,
clin.health_issue,
clin.incoming_data_unmatchable,
clin.incoming_data_unmatched,
clin.lnk_code2item_root,
clin.lnk_constraint2vacc_course,
clin.lnk_pat2vaccination_course,
clin.lnk_substance2episode,
clin.lnk_tst2norm,
clin.lnk_type2item,
clin.lnk_vaccination_course2schedule,
clin.lnk_vaccine2inds,
clin.patient,
clin.review_root,
clin.suppressed_hint,
clin.test_org,
clin.test_panel,
clin.test_type,
clin.vaccination_course,
clin.vaccination_course_constraint,
clin.vaccination_definition,
clin.vaccination_schedule,
clin.vacc_indication,
clin.vaccine,
clin.vaccine_batches,
clin.vacc_route,
clin.waiting_list,
de_de.beh_fall_typ,
de_de.lab_test_gnr,
de_de.prax_geb_paid,
dem.address,
dem.gender_label,
dem.identity,
dem.identity_tag,
dem.inbox_item_category,
dem.inbox_item_type,
dem.lnk_identity2ext_id,
dem.lnk_job2person,
dem.lnk_org_unit2comm,
dem.lnk_org_unit2ext_id,
dem.lnk_person2relative,
dem.message_inbox,
dem.occupation,
dem.org,
dem.org_unit,
dem.praxis_branch,
dem.relation_types,
dem.staff,
dem.state,
dem.street,
dem.urb,
gm.access_log,
ref.auto_hint,
ref.branded_drug,
ref.consumable_substance,
ref.data_source,
ref.lnk_substance2brand,
ref.paperwork_templates,
ref.tag_image
> Can you get rid of dem.staff in favor of something like creating a
> "staff" role and GRANT'ing that to appropriate users?
We already use PG roles per app user and group roles to
aggregate roles into permission groups. App user are defined
in ... dem.staff.
> Alternatively, maybe you can make the modified_by column be a foreign
> key
The gm.is_staff_or_dbowner() business is all about a
desperate attempt to overcome the inability to define foreign
keys into system tables.
Would it be an idea to be able to define such foreign keys
but not enforce them / disable them such that pg_dump could
know about them ? More like hints ? I realize this wouldn't
magically make pg_dump know about my hack...
> referencing a table of users (it probably couldn't be defined
> quite like "staff", but you get the idea). The presence of the foreign
> key would be enough to cue pg_dump about load order.
Hm, hm, OK, that might be an idea. It would be a superset of
dem.staff (namely also including "postgres" and the db-owner).
I will pursue this idea.
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Fri, Jan 08, 2016 at 12:38:47PM -0500, Tom Lane wrote: >>> dumps of your 9.4 installation's databases. If you'd be willing to send >>> those to me off-list, maybe I could figure out what's happening. > >> The list stalled the attachment so here as PM. > > Well, you shouldn't have tried to send it to the list; there's no need > to memorialize half a megabyte of transient data in the archives. I hadn't realized that >>> If you'd be willing to send those to me off-list was to be understood as "if so you *should* send them *off*-list". Sorry. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Fri, Jan 08, 2016 at 12:38:47PM -0500, Tom Lane wrote: > After digging through this, I figured out the problem: you'd installed > pg_trgm into the pg_catalog schema, whereas when I was testing I'd just > dropped it into the public schema. That confuses pg_dump into not > emitting the shell type that it should emit. It's an easy fix now > that I see the problem. > > This bug does *not*, AFAICT, explain any problem you might have with > "dump" transfers, only with pg_upgrade. I realize that. Thank you for looking into this issue. I'll rethink the foreign key / staff / check constraint issue meanwhile. Thanks, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Karsten Hilbert <Karsten.Hilbert@gmx.net> writes: > On Fri, Jan 08, 2016 at 12:38:47PM -0500, Tom Lane wrote: >> After digging through this, I figured out the problem: you'd installed >> pg_trgm into the pg_catalog schema, whereas when I was testing I'd just >> dropped it into the public schema. That confuses pg_dump into not >> emitting the shell type that it should emit. It's an easy fix now >> that I see the problem. >> This bug does *not*, AFAICT, explain any problem you might have with >> "dump" transfers, only with pg_upgrade. > I realize that. Thank you for looking into this issue. BTW, the one-liner fix that I'd had in mind when I wrote that does indeed fix this particular problem, but after studying the code I realized that there's a whole bunch of related problems; for instance I believe pg_upgrade would lose domain constraints on a domain type that's in an extension installed into pg_catalog :-(. See http://www.postgresql.org/message-id/19767.1452279786@sss.pgh.pa.us So a fix might take a bit more time than I thought, but hopefully we'll have something in time for next month's update releases. regards, tom lane
On Fri, Jan 08, 2016 at 04:03:51PM -0500, Tom Lane wrote: > BTW, the one-liner fix that I'd had in mind when I wrote that does indeed > fix this particular problem, but after studying the code I realized that > there's a whole bunch of related problems; for instance I believe > pg_upgrade would lose domain constraints on a domain type that's in an > extension installed into pg_catalog :-(. Does this warrant adding a few words to the documentation warning against installing extensions into pg_catalog. ? Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
> On Fri, Jan 08, 2016 at 04:03:51PM -0500, Tom Lane wrote:
>> BTW, the one-liner fix that I'd had in mind when I wrote that does indeed
>> fix this particular problem, but after studying the code I realized that
>> there's a whole bunch of related problems; for instance I believe
>> pg_upgrade would lose domain constraints on a domain type that's in an
>> extension installed into pg_catalog :-(.
> Does this warrant adding a few words to the documentation
> warning against installing extensions into pg_catalog. ?
No, it's just a bug. Although apparently not many people do that, or
we'd have heard complaints before.
regards, tom lane
On 01/08/2016 01:26 PM, Tom Lane wrote: > Karsten Hilbert <Karsten.Hilbert@gmx.net> writes: >> On Fri, Jan 08, 2016 at 04:03:51PM -0500, Tom Lane wrote: >>> BTW, the one-liner fix that I'd had in mind when I wrote that does indeed >>> fix this particular problem, but after studying the code I realized that >>> there's a whole bunch of related problems; for instance I believe >>> pg_upgrade would lose domain constraints on a domain type that's in an >>> extension installed into pg_catalog :-(. > >> Does this warrant adding a few words to the documentation >> warning against installing extensions into pg_catalog. ? > > No, it's just a bug. Although apparently not many people do that, or > we'd have heard complaints before. That dredged up a memory from way back: http://www.postgresql.org/message-id/200411251906.43881.aklaver@comcast.net in particular: http://www.postgresql.org/message-id/20077.1101510670@sss.pgh.pa.us > > regards, tom lane > > -- Adrian Klaver adrian.klaver@aklaver.com
"partial" data constraint - trigger or CONSTRAINT ? was: pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me
From
Karsten Hilbert
Date:
On Fri, Jan 08, 2016 at 12:53:24PM -0500, Tom Lane wrote: > In general, embedding lookups of other tables into CHECK constraints > is going to cause you all kinds of grief quite aside from pg_dump > not understanding it, because the backend doesn't really understand it > either. If the other table changes, causing the CHECK expression to > fail, that will *not* cause anything to happen to the table with the > CHECK constraint. It could well be that pg_dump is loading the tables > in the right order by chance, and the reason you're seeing a failure > is that one or more rows have modified_by values corresponding to > people who no longer are in the staff table. That has pretty much been the very intent of the constraint function: Allowing only "postgres", the owner of the database, or people _currently_ on staff to insert/update table data. There may well be database accounts which used to be associated with staff rows but are no longer listed as staff (because they aren't). There will still be table data associated with those accounts - their former staff entries can be gotten from the audit system (that's why dem.staff itself is being audited). I realize that being able to foreign key into system tables would not have helped with the part where only _current_ staff is to insert into/update data tables. That's why I haven't moaned about it but rather written my own (misguided?) attempt at enforcing such a constraint. Would I be better of rewriting the constraint as an ON INSERT OR UPDATE trigger ? Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Fri, Jan 08, 2016 at 04:26:25PM -0500, Tom Lane wrote:
> Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
> > On Fri, Jan 08, 2016 at 04:03:51PM -0500, Tom Lane wrote:
> >> BTW, the one-liner fix that I'd had in mind when I wrote that does indeed
> >> fix this particular problem, but after studying the code I realized that
> >> there's a whole bunch of related problems; for instance I believe
> >> pg_upgrade would lose domain constraints on a domain type that's in an
> >> extension installed into pg_catalog :-(.
>
> > Does this warrant adding a few words to the documentation
> > warning against installing extensions into pg_catalog. ?
>
> No, it's just a bug. Although apparently not many people do that, or
> we'd have heard complaints before.
For the record, apart from the aforementioned bug, I can
confirm that pg_upgrade will work fine when pg_trgm is
relocated to another schema (I chose "pgtrgm").
Caveats:
1)
One can't use "pg_trgm" as the schema name - PG will tell us
that the pg_ prefix is reserved for system schemata.
2)
One can't (easily ?) use
alter extension ... set schema ...
to relocate pg_trgm from pg_catalog to some other schema
because PG will inform us that pg_catalog is a system catalog:
gnumed_v21=# alter extension pg_trgm set schema pg_catalog;
ALTER EXTENSION
gnumed_v21=# alter extension pg_trgm set schema pgtrgm;
ERROR: cannot remove dependency on schema pg_catalog because it is a system object
gnumed_v21=#
Relocating from pg_catalog requires a
drop extension ... cascade
create extension ... with schema
cycle, followed by recreating GIN indexes as needed (in my case).
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Fri, Jan 08, 2016 at 12:53:24PM -0500, Tom Lane wrote: > Hm. So, by having installed this function as a check constraint, you have > created a data dependency that pg_dump has no way to know about. It's > going to load the tables in some order that's chosen without regard to the > need for dem.staff to be populated first. This is not a pg_dump bug. > > In general, embedding lookups of other tables into CHECK constraints > is going to cause you all kinds of grief quite aside from pg_dump > not understanding it, because the backend doesn't really understand it > either. If the other table changes, causing the CHECK expression to > fail, that will *not* cause anything to happen to the table with the > CHECK constraint. It could well be that pg_dump is loading the tables > in the right order by chance, and the reason you're seeing a failure > is that one or more rows have modified_by values corresponding to > people who no longer are in the staff table. > > Can you get rid of dem.staff in favor of something like creating a > "staff" role and GRANT'ing that to appropriate users? > > Alternatively, maybe you can make the modified_by column be a foreign > key referencing a table of users (it probably couldn't be defined > quite like "staff", but you get the idea). The presence of the foreign > key would be enough to cue pg_dump about load order. Just a crazy thought: If I create a foreign key from *.*.modified_by towards dem.staff.db_user but then DISABLE that FK -- would that still cue in pg_dump to order the tables appropriately ? Thanks, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Adrian Klaver <adrian.klaver@aklaver.com> writes: > On 01/08/2016 01:26 PM, Tom Lane wrote: >> No, it's just a bug. Although apparently not many people do that, or >> we'd have heard complaints before. > That dredged up a memory from way back: > http://www.postgresql.org/message-id/200411251906.43881.aklaver@comcast.net > in particular: > http://www.postgresql.org/message-id/20077.1101510670@sss.pgh.pa.us Well, that was a long time ago. Now that we have extensions, it should be possible for pg_dump to do the right thing with an extension's members whether they're in pg_catalog or not. regards, tom lane
Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
> Just a crazy thought:
> If I create a foreign key from *.*.modified_by towards
> dem.staff.db_user but then DISABLE that FK -- would that still
> cue in pg_dump to order the tables appropriately ?
Hmm, probably. Sounds like a kluge but ...
regards, tom lane