I have setup a PostgreSQL 12 database and am trying to connect to a Greenplum database in order to create proxy tables. I am able to connect to the Greenplum db, but I get an error when I try to use the IMPORT FOREIGN SCHEMA command.
IMPORT FOREIGN SCHEMA remote_schema FROM SERVER "remote_server" INTO schema_test_1;
ERROR: Greenplum Database does not support REPEATABLE READ transactions. (variable.c:570) CONTEXT: remote SQL command: START TRANSACTION ISOLATION LEVEL REPEATABLE READ SQL state: XX000
I read that REPEATABLE READ is not supported in Greenplum and to use SERIALIZE instead. Is there a way to edit the IMPORT FOREIGN SCHEMA command so that I can replace REPEATABLE READ with SERIALIZE?
I am using PGadmin 4.
I found that I can get commands to work if I write them as complete transactions and include the following before any commands:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Is there a way to set this as the default value for all transactions going through the Foreign Server?
No, that cannot be done. You have to use a less ancient fork of PostgreSQL that has heard of
To make that work, you’d have to modify the PostgreSQL source code, but if you do you should use
SERIALIZABLE only with Greenplum.
Per F.31.3 of PostgreSQL 9.5 docs:
The remote transaction uses SERIALIZABLE isolation level when the
local transaction has SERIALIZABLE isolation level; otherwise it uses
REPEATABLE READ isolation level.
If you change the postgresql.conf file to
default_transaction_isolation = 'SERIALIZABLE'
it will use SERIALIZABLE vice REPEATABLE READ transaction isolation when communicating with foreign servers.
Queries to the foreign server work fine now, including IMPORT FOREIGN SCHEMA.