Re: [GENERAL] pg_upgrade ?deficiency

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?

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company