Read Only Transaction Error

[ram1992]

Hi All,

I am facing an error while running some process on primary/master server.

cannot execute DROP SCHEMA in a read-only transaction.

cannot execute DROP EXTENSION in a read-only transaction.

I see that there are a lot of INSERT, UPDATE operation performed successfully on primary.

Any reason wy such error occued on primary node?

Thanks in advance.

[kapil]

Hi ram1992,

Could you please check in postgresql.conf file default_transaction_read_only is set to "on" for cluster. But as per your update that this shouldn't be the case.

Can you check wether the user,database which you have connected has read_only access.

Below are the example.

I have created a user repuser in my local machine and I have provided the read_only access to the repuser by below command.

postgres=# \du+

                                          List of roles

Role name |                         Attributes                         | Member of | Description

-----------+------------------------------------------------------------+-----------+-------------

postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |

repuser   | Replication                                                | {}        |

postgres=# alter user repuser set default_transaction_read_only =on;

ALTER ROLE

Provided the readonly access to the  "repuser", Connected the postgres database with "repuser" and tried to drop the scema and extension and I received the same error.

[postgres@primary data]$ psql -p 5432 -U repuser -d postgres

psql (10.6)

Type "help" for help.

postgres=> drop schema public ;

ERROR:  cannot execute DROP SCHEMA in a read-only transaction

postgres=> drop extension dblink ;

ERROR:  cannot execute DROP EXTENSION in a read-only transaction

postgres=> alter database prod set default_transaction_read_only =on;

ERROR:  cannot execute ALTER DATABASE in a read-only transaction

postgres=> \q

[postgres@primary data]$ psql -p 5432 -U postgres

psql (10.6)

Type "help" for help.

Database readonly access.

Provided the readonly access to the "prod" database and tried to drop the scema and extension and I received the same error.

postgres=# alter database prod set default_transaction_read_only =on;

ALTER DATABASE

postgres=# \c prod

You are now connected to database "prod" as user "postgres".

prod=# drop schema public ;

ERROR:  cannot execute DROP SCHEMA in a read-only transaction

prod=# drop extension dblink ;

ERROR:  cannot execute DROP EXTENSION in a read-only transaction

This could be one of the reasons that your seeing the read-only transaction error on master server. 

Hope this helps you.

[ram1992]

Hi kapil,

From logs I can't find any statement excuted as 'ALTER USER / DATABASE'.

Is there any view or sysytem table to verify the user's previlege.

Thanks

[kapil]

Hi ram1992,

For user you can refer veiw called pg_user and for database their is no view.

Please find the below output for your reference.

edb=# select * from pg_user;
-[ RECORD 1 ]-----+-----------------------------------
usename | enterprisedb
usesysid | 10
usecreatedb | t
usesuper | t
userepl | t
usebypassrls | f
passwd | ********
valuntil |
useaccountstatus | 0
uselockdate |
usepasswordexpire |
useconfig |
-[ RECORD 2 ]-----+-----------------------------------
usename | repuser
usesysid | 16554
usecreatedb | f
usesuper | f
userepl | f
usebypassrls | f
passwd | ********
valuntil |
useaccountstatus | 0
uselockdate |
usepasswordexpire |
useconfig | {default_transaction_read_only=on}

[Din2pg]

Hi Ram,

                    Any failover or switchover happend in few days/time before?.

Canu you please share pg_pool log also and send the session level changes.till not yet restarted..i hope you what am asking!!!

Thanks

Diny.

[ram1992]

Hi Diny,

These are new server which was set up with streaming replication. We don't use pgpool, it's just two servers in sync via replication and also useconfig filed of that user is empty.

Thanks

[Dineshkumar]

Hi Ram,

      Please send your user description like your current user enable in  Read Only Transaction parameter.[default_transaction_read_only=on if ti on plese off]

otherwise

• you can create new user and database set default roles then create  table  and extension inside a schema..then test up to drop.
• please keep on mind again any issue happened or not .please compare new test scenario  and old scenario ..

we want the below details:-

1.your user descriptions

2.user grants and privileges

[ram1992]

Hi Dinesh,

Sorry for the delay respone, the parameter "default_transaction_read_only" has been set to "off" across the instance, also the value is not changed for any user or has been set at session/DB level.

Thanks,

[kapil]

Hi ram1992,

Possible root cause have been shared on the thread regarding the read-only transaction error messages. However if your still seeing the read-only error messages on master cluster, please share the postgres logs to investigate further.