Re: postgres_fdw insert extremely slow - Mailing list pgsql-general

From David G. Johnston
Subject Re: postgres_fdw insert extremely slow
Date
Msg-id CAKFQuwbZHpiAx6L-55DUcJ1Kj=yzUvxCiW6zC06t+BQ1Y_nLyA@mail.gmail.com
Whole thread Raw
In response to Re: postgres_fdw insert extremely slow  (pabloa98 <pabloa98@gmail.com>)
List pgsql-general
On Fri, Nov 27, 2020 at 2:00 PM pabloa98 <pabloa98@gmail.com> wrote:
I would like to suggest for postgres_fdw: If the foreign database is PostgreSQL,

Just to be clear, the "postgres" part of the name means the remote database must be a PostgreSQL database, there is no "if".  Likewise, for the extension mysql_fdw the remote database is "MySQL".
 
the link should just pass through all the CRUD SQL commands to the other database.

If the other database is of a version so different that cannot make sense of the CRUD SQL command, it will generate an error and that's it.

This would be very useful to keep datasets synchronized.


We already offer a tool for that, dblink.


But the generalized behavior of FDW doesn't work to just send a raw SQL command across, even for CRUD.
e.g.,
UPDATE remote_tbl
SET ...
FROM local_tbl
WHERE remote_tbl.col_id=local_tbl.col_id;
or
INSERT INTO remote_tbl SELECT * FROM local_tbl;

Not saying that there isn't room for improvement here but I'm doubting it's simple.

David J.

pgsql-general by date:

Previous
From: pabloa98
Date:
Subject: Re: postgres_fdw insert extremely slow
Next
From: Hemil Ruparel
Date:
Subject: Re: How to debug authentication issues in Postgres