Postgresql – Using IMPORT FOREIGN SCHEMA with Greenplum database

foreign-datagreenplumpostgresql

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?

Best Answer

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.

Related Solutions

Postgresql – Read Committed Isolation Level

The difference lies between a query and a transaction. A transaction can contain any number of queries. To illustrate the difference, I set up a small example:

CREATE TABLE table_to_be_updated (
    id serial PRIMARY KEY,
    other_column text,
    column_changing text
);

INSERT INTO table_to_be_updated (other_column, column_changing)
VALUES
('value', 'old_value'),
('value', 'other_value'),
('nonvalue', 'doesnt matter');

Then run two transactions concurrently (issuing the commands one by one, the middle line wants to depict the timeline):

                                | <-- BEGIN;
                                |
                                |
                                |     UPDATE table_to_be_updated
BEGIN; -----------------------> |     SET column_changing = 'new_value'
                                |     WHERE
                                |         other_column = 'value' AND
                                |         column_changing = 'old_value';
                                |
                                |
SELECT column_changing -------> |     -- update not yet committed
FROM table_to_be_updated        |
WHERE other_column = 'value';   | <-- COMMIT;
                                |
                                |
SELECT column_changing -------> |
FROM table_to_be_updated        |
WHERE other_column = 'value';   |
                                |
                                |
COMMIT; ----------------------> |

Running these in READ COMMITTED isolation level, the first query returns a row with 'old_value', while the second one shows a row with 'new_value'. On an other run, I change the left-hand-side transaction isolation level:

SET transaction ISOLATION LEVEL REPEATABLE READ;

(The command must be the first statement in a transaction.) Now both SELECTs return the same rowset, while a third one after committing both transactions will show the new row.

Postgresql – Inconsistency in repeatable read

Here is my reading of that section. I will admit it is confusing.

Suppose I have two tables:

CREATE TABLE batch (
   id serial not null unique,
   control_code text primary key,
   date_posted date not null default now()
);

CREATE TABLE details (
   batch_id int not null references batch(id),
   description text,
   primary key(batch_id, description)
);

Now, suppose we insert batch and details records in different transactions. Session 1 inserts a batch and starts to insert details but before it finishes, session 2 starts up. Session 2 gets to see the batch heading info, but does not wait on the commit on details to inform the user that no records are found. Now if your batch and details are entirely in the same transaction then this never is a problem.

this would differ from serializable where you would expect to wait for the previous insert to complete and commit or rollback before determining whether to notify the user that no rows were found.

Related Question