Verbindung von 2 Postgres Datenbanken mit postgres_fdw

Fragen und Probleme rund um die Abfragespache SQL.
Antworten
rcb
Neuer Benutzer
Beiträge: 5
Registriert: Fr 16. Mär 2018, 11:44

Verbindung von 2 Postgres Datenbanken mit postgres_fdw

Beitrag von rcb » Fr 16. Mär 2018, 13:21

Hallo zusammen,

mit Hilfe der Extension postgres_fdw sind 2 Datenbanken (Version 10.3) miteinander verbunden. Die Installation und das prinzipielle Arbeiten haben wunderbar funktioniert.

Folgendes Problem habe ich beim Einfügen von Daten via dem Foreign Data Wrapper:

Auf der Remote-Datenbank werden Tabellen zumeist mit einer Identity-Spalte angelegt, ein Insert erfolgt ohne die Angabe der ID-Spalte:

Code: Alles auswählen

tdb=> CREATE TABLE tdba.test_tbl
tdb-> (   id      INTEGER     NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
tdb(> ,   a       INTEGER     NOT NULL
tdb(> );
CREATE TABLE
tdb=> \d tdba.test_tbl
                              Tabelle »tdba.test_tbl«
 Spalte |   Typ   | Sortierfolge | NULL erlaubt? |           Vorgabewert
--------+---------+--------------+---------------+----------------------------------
 id     | integer |              | not null      | generated by default as identity
 a      | integer |              | not null      |
Indexe:
    "test_tbl_pkey" PRIMARY KEY, btree (id)
tdb=> insert into tdba.test_tbl(a) values(1);
INSERT 0 1
Auf der lokalen Seite wurden die Extension, der Server und das User Mapping angelegt:

Code: Alles auswählen

ldb=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION
ldb=# CREATE SERVER c74pg10 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '172.16.49.122', dbname 'tdb', port '5432');
CREATE SERVER
ldb=# GRANT USAGE ON FOREIGN SERVER c74pg10 TO ldba;
GRANT
ldb=# \c - ldba
Passwort für Benutzer ldba:
Sie sind jetzt verbunden mit der Datenbank »ldb« als Benutzer »ldba«.
ldb=> CREATE USER MAPPING FOR ldba SERVER c74pg10 OPTIONS (user 'tdba', password <>);
CREATE USER MAPPING
Das Remote-Schema wurde lokal importiert, die Daten lassen sich lesen:

Code: Alles auswählen

ldb=> IMPORT FOREIGN SCHEMA tdba FROM SERVER c74pg10 INTO ldba OPTIONS (import_default 'true');
IMPORT FOREIGN SCHEMA
ldb=>
ldb=> \d ldba.*
                            Fremdtabelle »ldba.test_tbl«
 Spalte |   Typ   | Sortierfolge | NULL erlaubt? | Vorgabewert |    FDW-Optionen
--------+---------+--------------+---------------+-------------+--------------------
 id     | integer |              | not null      |             | (column_name 'id')
 a      | integer |              | not null      |             | (column_name 'a')
Server: c74pg10
FDW-Optionen: (schema_name 'tdba', table_name 'test_tbl')

ldb=> select * from ldba.test_tbl;
 id | a
----+---
  1 | 1
(1 Zeile)
Jetzt soll das Insert in die Remote-Datenbank von der lokalen Seite aus ausgeführt werden. Das Insert erfolgt wieder ohne die Angabe der ID-Spalte, damit die Identity Definition greift:

Code: Alles auswählen

ldb=> insert into ldba.test_tbl (a) values (2);
FEHLER:  NULL-Wert in Spalte »id« verletzt Not-Null-Constraint
DETAIL:  Fehlgeschlagene Zeile enthält (null, 2).
KONTEXT:  Remote SQL command: INSERT INTO tdba.test_tbl(id, a) VALUES ($1, $2)
Das Ganze schlägt fehl, da im FDW scheinbar ein Insert mit allen Spalten generiert wird. Gibt es Möglichkeiten, dies so zu beeinflussen, so dass ein Insert wie vorgegeben gesendet wird? Es sollte die zugehörige Sequenz für die Remote-Tabelle verwendet werden.

Vielen Dank für die Unterstützung.

Grüße,
Ralph

akretschmer
Erfahrener Benutzer
Beiträge: 7640
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Verbindung von 2 Postgres Datenbanken mit postgres_fdw

Beitrag von akretschmer » Fr 16. Mär 2018, 14:12

import_default

This option controls whether column DEFAULT expressions are included in the definitions of foreign tables imported from a foreign server. The default is false. If you enable this option, be wary of defaults that might get computed differently on the local server than they would be on the remote server; nextval() is a common source of problems. The IMPORT will fail altogether if an imported default expression uses a function or operator that does not exist locally.


Du wirst da eine lokale Sequence erstellen müssen. Damit bei beidseitigen Inserts es nicht zu Kollisionen kommt, kannst Du die Sequencen mit unterschiedlichen Start- und Incrementwerten erstellen.
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

rcb
Neuer Benutzer
Beiträge: 5
Registriert: Fr 16. Mär 2018, 11:44

Re: Verbindung von 2 Postgres Datenbanken mit postgres_fdw

Beitrag von rcb » Mo 19. Mär 2018, 10:34

Vielen Dank für den Tipp.

Trotzdem noch mal die Frage:

Warum erzeugt der FDW ein Insert mit allen Spalten? Wenn er das Statement so lässt, wie von mir vorgegeben (ohne Spalte id), so sollte meiner Meinung nach der Default (Identity) für die id Spalte greifen und es keine Probleme geben. Oder doch?

Grüße,
Ralph

akretschmer
Erfahrener Benutzer
Beiträge: 7640
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Verbindung von 2 Postgres Datenbanken mit postgres_fdw

Beitrag von akretschmer » Mo 19. Mär 2018, 14:36

es gibt keinen Default (lokal).
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

rcb
Neuer Benutzer
Beiträge: 5
Registriert: Fr 16. Mär 2018, 11:44

Re: Verbindung von 2 Postgres Datenbanken mit postgres_fdw

Beitrag von rcb » Mo 19. Mär 2018, 15:40

Aber warum macht der FDW aus "insert into ldba.test_tbl (a) values (2)" --> "insert into ldba.test_tbl (id, a) values (NULL, 2)" ?

Was ist der Hintergrund für diese Vervollständigung? Warum reicht es nicht aus, dass der Remoteserver die notwendige Defaults einsetzt?

Wenn die Remotetabelle mit GENERATED ALWAYS AS IDENTITY definiert ist, dann funktioniert der Ansatz mit der lokalen Sequenz nicht:

Code: Alles auswählen

ldb=> insert into ldba.test_tbl (a) values (2);
FEHLER:  kann nicht in Spalte »id« einfügen
DETAIL:  Spalte »id« ist eine Identitätsspalte, die als GENERATED ALWAYS definiert ist.
TIP:  Verwenden Sie OVERRIDING SYSTEM VALUE, um diese Einschränkung außer Kraft zu setzen.
KONTEXT:  Remote SQL command: INSERT INTO tdba.test_tbl(id, a) VALUES ($1, $2)
Danke & Grüße,
Ralph

akretschmer
Erfahrener Benutzer
Beiträge: 7640
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Verbindung von 2 Postgres Datenbanken mit postgres_fdw

Beitrag von akretschmer » Mo 19. Mär 2018, 16:48

Irgend etwas MUß ja inserted werden, was also nehmen wenn nicht NULL?

Du kannst die Tabelle (bei mir foo) ändern:

alter table foo alter COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY;

Code: Alles auswählen

andreas@[local]:5433/fdw_db# insert into foo (a) values (2);
ERROR:  duplicate key value violates unique constraint "foo_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  Remote SQL command: INSERT INTO fdw_test.foo(id, a) VALUES ($1, $2)
andreas@[local]:5433/fdw_db!# rollback;
ROLLBACK
andreas@[local]:5433/fdw_db# insert into foo (a) values (2);
INSERT 0 1
andreas@[local]:5433/fdw_db*# select * from foo;
 id | a 
----+---
  1 | 1
  2 | 2
(2 rows)
id=1 war schon vorhanden, daher zuerst der Fehler.


Du kannst aber auch ganz 'normal' eine Sequenz erstellen (auf beiden Seiten) und diese mit untersch. Start- und Offset werten versehen (eine seite Start bei 1, Offset 2), andere Seite Start 2, Offset 2. Dann wird auf der ersten 1,3,5,7,9 generiert, auf der anderen 2,4,6,8, ...

Wenn Du echte globale Sequencen haben willst, dann schaue Dir BDR2 an. Okay, das war jetzt fies, weil BDR2 noch nicht public verfügbar ist, nur für Supportkunden von uns ;-)


Grüße aus Niedersachsen.
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

rcb
Neuer Benutzer
Beiträge: 5
Registriert: Fr 16. Mär 2018, 11:44

Re: Verbindung von 2 Postgres Datenbanken mit postgres_fdw

Beitrag von rcb » Di 20. Mär 2018, 07:47

Das beim Insert alle Spalten mit Wert (vorgegeben, default, NULL) eingefügt werden müssen, ist schon klar. Unklar ist nur, warum das Setzen der fehlenden Spalten mit ihrem Default oder NULL nicht erst auf dem Zielsystem erfolgt.

Ich werde den Ansatz über die lokale und remote Sequenz weiter verfolgen. BDR ist für das Thema hier wahrscheinlich eine Nummer zu groß.

Besten Dank für die Unterstützung,
Ralph

castorp
Erfahrener Benutzer
Beiträge: 1141
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: Verbindung von 2 Postgres Datenbanken mit postgres_fdw

Beitrag von castorp » Di 20. Mär 2018, 09:39

Ich frage mich ob die lokale Sequence vermeidbar ist, wenn man zusätzlich noch einen DBLink verwendet und über den, die andere Sequence "abruft". Einen Versuch wäre das wert.
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

rcb
Neuer Benutzer
Beiträge: 5
Registriert: Fr 16. Mär 2018, 11:44

Re: Verbindung von 2 Postgres Datenbanken mit postgres_fdw

Beitrag von rcb » Di 20. Mär 2018, 11:22

Die Idee funktioniert.

Folgende Implementierung dazu:

Remote Datenbank:

Code: Alles auswählen

tdb=> create view tdba.seq_provider AS select nextval('tdba.test_tbl_id_seq');
CREATE VIEW
tdb=> \d tdba.seq_provider
                   Sicht »tdba.seq_provider«
 Spalte  |  Typ   | Sortierfolge | NULL erlaubt? | Vorgabewert
---------+--------+--------------+---------------+-------------
 nextval | bigint |              |               |
Lokale Datenbank:

Code: Alles auswählen

ldb=> IMPORT FOREIGN SCHEMA tdba LIMIT TO (seq_provider) FROM SERVER c74pg10 INTO ldba;
IMPORT FOREIGN SCHEMA
ldb=> \d ldba.test_tbl
                            Fremdtabelle »ldba.test_tbl«
 Spalte |   Typ   | Sortierfolge | NULL erlaubt? | Vorgabewert |    FDW-Optionen
--------+---------+--------------+---------------+-------------+--------------------
 id     | integer |              | not null      |             | (column_name 'id')
 a      | integer |              | not null      |             | (column_name 'a')
Server: c74pg10
FDW-Optionen: (schema_name 'tdba', table_name 'test_tbl')

ldb=> select * from ldba.test_tbl;
 id | a
----+---
  1 | 1
(1 Zeile)

ldb=>  insert into ldba.test_tbl (id,a) select nextval, 2 from ldba.seq_provider;
INSERT 0 1

ldb=> select * from ldba.test_tbl;
 id | a
----+---
  1 | 1
  4 | 2
(2 Zeilen)
Besten Dank,
Ralph

Antworten