Re: pg_dump/pg_restore vs default_transaction_read_only under PG 13.2

On Sun, 20 Jun 2021 at 22:49, Vijaykumar Jain <
vijaykumarjain(dot)github(at)gmail(dot)com> wrote:

>
>
> On Sun, 20 Jun 2021 at 22:17, Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>
> wrote:
>
>> Dear all,
>>
>> I am testing the pg_restore of a database with
>> default_transaction_read_only=on.
>>
>> It would seem the restore script lacks a
>>
>> SET default_transaction_read_only TO 'off';
>>
>> in the setup section after re-connecting to the DB following
>> the ALTER DATABASE section ?
>>
>> Thanks,
>> Karsten
>> --
>> GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
>>
>>
>>
> i just tested as below.
>
> postgres(at)db:~/playground/logical_replication$ rm -rf example
> postgres(at)db:~/playground/logical_replication$ initdb -D example
> 2>/dev/null >/dev/null
> postgres(at)db:~/playground/logical_replication$ vim example/postgresql.conf
> postgres(at)db:~/playground/logical_replication$ printf
> "default_transaction_read_only=on\n" >> example/postgresql.conf #global
> config setting as on
> postgres(at)db:~/playground/logical_replication$ pg_ctl -D example -l
> logfile start
> waiting for server to start.... done
> server started
> postgres(at)db:~/playground/logical_replication$ psql
> psql (14beta1)
> Type "help" for help.
>
> postgres=# show default_transaction_read_only; -- validate it is on
> default_transaction_read_only
> -------------------------------
> on
> (1 row)
>
> postgres=# \q
> postgres(at)db:~/playground/logical_replication$ pg_dumpall -f dump.sql
> postgres(at)db:~/playground/logical_replication$ grep
> default_transaction_read_only dump.sql -- check what gets dumped
> SET default_transaction_read_only = off;
>
> but this is 14beta1.
>
> Did I simulate your concern correctly ?
>
>
ok i do not know if that is a bug ?
but i am able to reproduce your concern now alter database

postgres(at)db:~/playground/logical_replication$ rm -rf example
postgres(at)db:~/playground/logical_replication$ initdb -D example 2>/dev/null
>/dev/null
postgres(at)db:~/playground/logical_replication$ pg_ctl -D example -l logfile
start
waiting for server to start.... done
server started
postgres(at)db:~/playground/logical_replication$ createdb example
postgres(at)db:~/playground/logical_replication$ psql example -c 'create table
t(id int); insert into t select 1; '
INSERT 0 1
postgres(at)db:~/playground/logical_replication$ psql example -c 'show
default_transaction_read_only;'
default_transaction_read_only
-------------------------------
off
(1 row)

postgres(at)db:~/playground/logical_replication$ psql example -c 'ALTER
DATABASE example SET default_transaction_read_only TO ''on'';'
ALTER DATABASE
postgres(at)db:~/playground/logical_replication$ psql example -c 'show
default_transaction_read_only;'
default_transaction_read_only
-------------------------------
on
(1 row)

postgres(at)db:~/playground/logical_replication$ pg_dumpall -f dump.sql
postgres(at)db:~/playground/logical_replication$ grep
default_transaction_read_only dump.sql
SET default_transaction_read_only = off;
ALTER DATABASE example SET default_transaction_read_only TO 'on';
postgres(at)db:~/playground/logical_replication$ dropdb example
postgres(at)db:~/playground/logical_replication$ psql < dump.sql

ERROR: cannot execute CREATE TABLE in a read-only transaction
ERROR: cannot execute ALTER TABLE in a read-only transaction
ERROR: relation "public.t" does not exist
invalid command \.
You are now connected to database "postgres" as user "postgres".
ERROR: syntax error at or near "1"

so the table did not get restored, as default_transaction_read_only = on.
so this is the same in 14 as well.

you can load sections via pg_restore and skip this TOC
--section=SECTION restore named section (pre-data, data, or
post-data)

maybe you know that, and are just asking if this is a feature or a bug.

--
Thanks,
Vijay
Mumbai, India