Re: [GENERAL] pg_upgrade ?deficiency

Am Samstag, 23. November 2013, 08:44:42 schrieb Kevin Grittner:
> Bruce Momjian <bruce(at)momjian(dot)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