0

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;

returns:

    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.

Update:

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?

1
0

No, that cannot be done. You have to use a less ancient fork of PostgreSQL that has heard of REPEATABLE READ.

To make that work, you'd have to modify the PostgreSQL source code, but if you do you should use SERIALIZABLE only with Greenplum.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.