Am Samstag, 23. November 2013, 08:44:42 schrieb Kevin Grittner: > Bruce Momjian <br...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers