Re: [GENERAL] pg_upgrade ?deficiency - Mailing list pgsql-hackers

Am Samstag, 23. November 2013, 08:44:42 schrieb Kevin Grittner:
> Bruce Momjian <bruce@momjian.us> wrote:
> > I am not a fan of backpatching any of this.
> 
> Here's my problem with that.  Here's setup to create what I don't
> think is all that weird a setup:
> 
> initdb Debug/data
> pg_ctl -D Debug/data -l Debug/data/logfile -w start
> createdb test
> psql test <src/test/regress/sql/matview.sql >/dev/null 2>&1
> psql postgres -c "alter database test set default_transaction_read_only =
> on;" psql postgres -c "alter database postgres set
> default_transaction_read_only = on;"
> 
> The following appears to produce a good backup, since there is no
> error:
> 
> pg_dumpall >~/dumpall.sql
> 
> Let's create a brand new cluster and start it up:
> 
> pg_ctl -D Debug/data -m fast -w stop
> rm -fr Debug/data/*
> initdb Debug/data
> pg_ctl -D Debug/data -l Debug/data/logfile -w start
> 
> Now we attempt to restore what we thought was a good backup:
> 
> psql postgres <~/dumpall.sql
> 
> What we get is:
> 
> SET
> SET
> ERROR:  role "kgrittn" already exists
> ALTER ROLE
> ALTER DATABASE
> REVOKE
> REVOKE
> GRANT
> GRANT
> CREATE DATABASE
> ALTER DATABASE
> You are now connected to database "postgres" as user "kgrittn".
> SET
> SET
> SET
> SET
> SET
> SET
> ERROR:  cannot execute COMMENT in a read-only transaction
> ERROR:  cannot execute CREATE EXTENSION in a read-only transaction
> ERROR:  cannot execute COMMENT in a read-only transaction
> ERROR:  cannot execute REVOKE in a read-only transaction
> ERROR:  cannot execute REVOKE in a read-only transaction
> ERROR:  cannot execute GRANT in a read-only transaction
> ERROR:  cannot execute GRANT in a read-only transaction
> You are now connected to database "template1" as user "kgrittn".
> SET
> SET
> SET
> SET
> SET
> SET
> COMMENT
> CREATE EXTENSION
> COMMENT
> REVOKE
> REVOKE
> GRANT
> GRANT
> You are now connected to database "test" as user "kgrittn".
> SET
> SET
> SET
> SET
> SET
> SET
> ERROR:  cannot execute CREATE SCHEMA in a read-only transaction
> ERROR:  cannot execute ALTER SCHEMA in a read-only transaction
> ERROR:  cannot execute CREATE EXTENSION in a read-only transaction
> ERROR:  cannot execute COMMENT in a read-only transaction
> SET
> SET
> SET
> ERROR:  cannot execute CREATE TABLE in a read-only transaction
> ERROR:  cannot execute ALTER TABLE in a read-only transaction
> ERROR:  cannot execute CREATE VIEW in a read-only transaction
> ERROR:  cannot execute ALTER TABLE in a read-only transaction
> SET
> ERROR:  relation "public.tv" does not exist
> LINE 4:    FROM public.tv
>                 ^
> ERROR:  cannot execute ALTER TABLE in a read-only transaction
> SET
> ERROR:  cannot execute CREATE VIEW in a read-only transaction
> ERROR:  cannot execute ALTER TABLE in a read-only transaction
> ERROR:  relation "tvv" does not exist
> LINE 3:    FROM tvv
>                 ^
> ERROR:  cannot execute ALTER TABLE in a read-only transaction
> ERROR:  cannot execute CREATE VIEW in a read-only transaction
> ERROR:  cannot execute ALTER TABLE in a read-only transaction
> ERROR:  relation "tvvmv" does not exist
> LINE 3:    FROM tvvmv
>                 ^
> ERROR:  cannot execute ALTER TABLE in a read-only transaction
> ERROR:  relation "t" does not exist
> LINE 4:    FROM t
>                 ^
> ERROR:  cannot execute ALTER TABLE in a read-only transaction
> ERROR:  relation "tm" does not exist
> LINE 3:    FROM tm
>                 ^
> ERROR:  cannot execute ALTER TABLE in a read-only transaction
> ERROR:  relation "mvschema.tvm" does not exist
> LINE 3:    FROM mvschema.tvm
>                 ^
> ERROR:  cannot execute ALTER TABLE in a read-only transaction
> ERROR:  relation "t" does not exist
> invalid command \.
> ERROR:  syntax error at or near "1"
> LINE 1: 1 x 2
>         ^
> ERROR:  cannot execute CREATE INDEX in a read-only transaction
> ERROR:  cannot execute CREATE INDEX in a read-only transaction
> ERROR:  cannot execute CREATE INDEX in a read-only transaction
> ERROR:  cannot execute CREATE INDEX in a read-only transaction
> SET
> ERROR:  cannot execute REFRESH MATERIALIZED VIEW in a read-only transaction
> SET
> ERROR:  cannot execute REFRESH MATERIALIZED VIEW in a read-only transaction
> ERROR:  cannot execute REFRESH MATERIALIZED VIEW in a read-only transaction
> ERROR:  cannot execute REFRESH MATERIALIZED VIEW in a read-only transaction
> ERROR:  cannot execute REFRESH MATERIALIZED VIEW in a read-only transaction
> ERROR:  cannot execute REFRESH MATERIALIZED VIEW in a read-only transaction
> ERROR:  cannot execute REVOKE in a read-only transaction
> ERROR:  cannot execute REVOKE in a read-only transaction
> ERROR:  cannot execute GRANT in a read-only transaction
> ERROR:  cannot execute GRANT in a read-only transaction
> 
> If the dump is made with the attached patch, you get this on
> restore:
> 
> SET
> SET
> SET
> ERROR:  role "kgrittn" already exists
> ALTER ROLE
> ALTER DATABASE
> REVOKE
> REVOKE
> GRANT
> GRANT
> CREATE DATABASE
> ALTER DATABASE
> You are now connected to database "postgres" as user "kgrittn".
> SET
> SET
> SET
> SET
> SET
> SET
> SET
> COMMENT
> CREATE EXTENSION
> COMMENT
> REVOKE
> REVOKE
> GRANT
> GRANT
> You are now connected to database "template1" as user "kgrittn".
> SET
> SET
> SET
> SET
> SET
> SET
> SET
> COMMENT
> CREATE EXTENSION
> COMMENT
> REVOKE
> REVOKE
> GRANT
> GRANT
> You are now connected to database "test" as user "kgrittn".
> SET
> SET
> SET
> SET
> SET
> SET
> SET
> CREATE SCHEMA
> ALTER SCHEMA
> CREATE EXTENSION
> COMMENT
> SET
> SET
> SET
> CREATE TABLE
> ALTER TABLE
> CREATE VIEW
> ALTER TABLE
> SET
> SELECT 0
> ALTER TABLE
> SET
> CREATE VIEW
> ALTER TABLE
> SELECT 0
> ALTER TABLE
> CREATE VIEW
> ALTER TABLE
> SELECT 0
> ALTER TABLE
> SELECT 0
> ALTER TABLE
> SELECT 0
> ALTER TABLE
> SELECT 0
> ALTER TABLE
> ALTER TABLE
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> SET
> REFRESH MATERIALIZED VIEW
> SET
> REFRESH MATERIALIZED VIEW
> REFRESH MATERIALIZED VIEW
> REFRESH MATERIALIZED VIEW
> REFRESH MATERIALIZED VIEW
> REFRESH MATERIALIZED VIEW
> REVOKE
> REVOKE
> GRANT
> GRANT
> SET
> SET
> SET
> ERROR:  role "kgrittn" already exists
> ALTER ROLE
> ALTER DATABASE
> REVOKE
> REVOKE
> GRANT
> GRANT
> CREATE DATABASE
> ALTER DATABASE
> You are now connected to database "postgres" as user "kgrittn".
> SET
> SET
> SET
> SET
> SET
> SET
> SET
> COMMENT
> CREATE EXTENSION
> COMMENT
> REVOKE
> REVOKE
> GRANT
> GRANT
> You are now connected to database "template1" as user "kgrittn".
> SET
> SET
> SET
> SET
> SET
> SET
> SET
> COMMENT
> CREATE EXTENSION
> COMMENT
> REVOKE
> REVOKE
> GRANT
> GRANT
> You are now connected to database "test" as user "kgrittn".
> SET
> SET
> SET
> SET
> SET
> SET
> SET
> CREATE SCHEMA
> ALTER SCHEMA
> CREATE EXTENSION
> COMMENT
> SET
> SET
> SET
> CREATE TABLE
> ALTER TABLE
> CREATE VIEW
> ALTER TABLE
> SET
> SELECT 0
> ALTER TABLE
> SET
> CREATE VIEW
> ALTER TABLE
> SELECT 0
> ALTER TABLE
> CREATE VIEW
> ALTER TABLE
> SELECT 0
> ALTER TABLE
> SELECT 0
> ALTER TABLE
> SELECT 0
> ALTER TABLE
> SELECT 0
> ALTER TABLE
> ALTER TABLE
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> CREATE INDEX
> SET
> REFRESH MATERIALIZED VIEW
> SET
> REFRESH MATERIALIZED VIEW
> REFRESH MATERIALIZED VIEW
> REFRESH MATERIALIZED VIEW
> REFRESH MATERIALIZED VIEW
> REFRESH MATERIALIZED VIEW
> REVOKE
> REVOKE
> GRANT
> GRANT
> 
> The cluster is created in the state that was dumped, default read
> only flags and all.
> 
> Are you saying that you find current behavior acceptable in back
> branches?
>

Here is how this came about.

Installation of PG 8.4 (port 5432) on Windows with default settings.
Creation of a test database
Installation of PG 9.3 on Windows (port 5433) with default settings

Starting up pg_upgrade as postgres
--> fails

c:\Windows\Temp>pg_upgrade.exe --old-datadir "C:/Program Files
(x86)/PostgresPlus/8.4SS/data" --new-datadir "C:/Program Files 
(x86)/PostgreSQL/9.3/data" --old-bindir "C:/Program Files 
(x86)/PostgresPlus/8.4SS/bin" --new-bindir "C:/Program F
iles (x86)/PostgreSQL/9.3/bin"
SQL command failed
CREATE TEMPORARY TABLE info_rels (reloid) AS SELECT c.oid FROM 
pg_catalog.pg_cla
ss c JOIN pg_catalog.pg_namespace n        ON c.relnamespace = n.oid LEFT 
OUTER
JOIN pg_catalog.pg_index i         ON c.oid = i.indexrelid WHERE relkind IN 
('r'
, 'm', 'i', 'S') AND  i.indisvalid IS DISTINCT FROM false AND  i.indisready IS 
D
ISTINCT FROM false AND   ((n.nspname !~ '^pg_temp_' AND     n.nspname !~ 
'^pg_to
ast_temp_' AND     n.nspname NOT IN ('pg_catalog', 'information_schema',
'binary_upgrade','pg_toast') AND c.oid >= 16384)   OR (n.nspname = 'pg_catalog' AND     relname IN 
 
('pg_largeob
ject', 'pg_largeobject_loid_pn_index') ));
ERROR:  transaction is read-only
Regards,
Sebastian