pg_upgrade 9.4 -> 9.5 with pg_trgm fails for me

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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" --port 5432 --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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general