[Bug]: Timescale breaking FDW reports accessing the Replica - EXPLAIN statement

[ikalafat]

What type of bug is this?

Unexpected error, Incorrect result

What subsystems and features are affected?

Other, Command processing

What happened?

Hi,

We have updated the timescale extension from 2.16.1 to 2.18.0 (and ran ALTER EXTENSION UPDATE 'timescaledb' to '2.18.0')

After this update, EXPLAIN statements executed on replica servers have started throwing the following error
ERROR: cannot execute EXPLAIN in a read-only transaction

We have a scenario where we gather data across different databases using PostgreSQL FDW extension, which runs EXPLAIN before executing it. In our scenario, we've pointed the Foreign databases to Replica server, to avoid impact on primary servers.
Due to performance improvements that this FDW option gives, I cannot disable use_remote_estimate option when registering foreign databases.

I have looked at the changelog of 2.17 and 2.18 and haven't noticed something that could point me into the right direction. Is there a setting that could revert behavior to older variant?

I can confirm that this error doesn't occur in databases where the Timescale extension is NOT installed, I can run EXPLAIN without issues (on replica server)

This this works as expected on TS 2.16.1.

TimescaleDB version affected

2.18.0

PostgreSQL version used

16.6

What operating system did you use?

Debian 12 x64

What installation method did you use?

Deb/Apt

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

ERROR:  cannot execute EXPLAIN in a read-only transaction

How can we reproduce the bug?

run any EXPLAIN <query> on REPLICA/standby server where TimescaleDB extension is installed - version 2.18.0 

[mkindahl]

@ikalafat Can you please show the full statement with the explain. It is possible to use explain on statements that are not read-only and also using functions that might have mistakenly marked as only for read-only transactions.

[ikalafat]

@mkindahl basically any explain SELECT causes this issue, such as

explain select * from "CoreUser" where CoreUser is one rather simple table.

[mkindahl]

@mkindahl basically any explain SELECT causes this issue, such as

explain select * from "CoreUser" where CoreUser is one rather simple table.

Is CoreUser a hypertable or a normal table?

[ikalafat]

CoreUser is normal table.
Same thing happens with hypertable, explain SELECT fails

just for reference, this query

START TRANSACTION READ ONLY;
EXPLAIN SELECT * FROM "CoreUser";
COMMIT;

also throws error, even on primary server

[mkindahl]

It seems you have either the transaction_read_only or default_transaction_read_only flag set. This can be done either on the server or as a client option. Can you check the value of these GUCs?

mats=# \d saved
                          Table "public.saved"
   Column    |           Type           | Collation | Nullable | Default 
-------------+--------------------------+-----------+----------+---------
 time        | timestamp with time zone |           | not null | 
 device      | integer                  |           |          | 
 temperature | double precision         |           |          | 

mats=# select * from saved;
mats=# start transaction;
START TRANSACTION
mats=*# set transaction_read_only to on;
SET
mats=*# explain select * from saved;
ERROR:  cannot execute EXPLAIN in a read-only transaction
mats=!# rollback;
ROLLBACK
mats=# show transaction_read_only;
 transaction_read_only 
-----------------------
 off
(1 row)

mats=# show default_transaction_read_only ;
 default_transaction_read_only 
-------------------------------
 off
(1 row)

[ikalafat]

On primary

transaction_read_only  OFF
default_transaction_read_only OFF

On replica

default_transaction_read_only  = OFF
transaction_read_only = ON

which is normal for replica/standby servers

I can confirm that no server configuration changes were made except updating the timescale extension version

[mkindahl]

On primary

transaction_read_only  OFF
default_transaction_read_only OFF

On replica

default_transaction_read_only  = OFF
transaction_read_only = ON

which is normal for replica/standby servers

I can confirm that no server configuration changes were made except updating the timescale extension version

I can confirm that this is a bug. I can reproduce it when our hooks are enabled and it does not generate an error in plain PostgreSQL.

[mkindahl]

They need to downgrade until we get a new patch release out.

[ikalafat]

@mkindahl thanks for info. Is there any other workaround except the downgrade? also, should I downgrade to 2.17.2 or back to 2.16.1?

[mkindahl]

@mkindahl thanks for info. Is there any other workaround except the downgrade? also, should I downgrade to 2.17.2 or back to 2.16.1?

This was introduced in 2.18.0, so going back to 2.17.2 is sufficient.

It should work when transaction_read_only is set to off, so running it on the primary should work fine if they can do that. (But I noted that you said you got an error on the primary, so can you check the settings there as well?)

If they want to run an explain on a normal table, I think they run timescaledb_pre_restore() before and timescaledb_post_restore() after to disable our hooks temporarily and get a plan. If they want to get a plan for a hypertable, they unfortunately need to run the explain on the primary.

[mkindahl]

Fix is here: #7637

[mkindahl]

It should work when transaction_read_only is set to off, so running it on the primary should work fine if they can do that. (But I noted that you said you got an error on the primary, so can you check the settings there as well?)

I know you showed the settings above, but it does not make sense that you cannot run the explain there, so please double-check.