search

EnterpriseDB / mysql_fdw

PostgreSQL foreign data wrapper for MySQL
Other
521 stars 160 forks source link

cannot `UPDATE` or `DELETE` on fdw foreign table that inherits pgsql table #300

Open n0099 opened 2 months ago

n0099 commented 2 months ago 
CREATE TABLE "base"(i int);
CREATE FOREIGN TABLE IF NOT EXISTS "foreign"()
    INHERITS ("base")
    SERVER mysql
    OPTIONS (dbname 'db', table_name 'remote');
UPDATE "base" SET i = 0 WHERE i = 1;
DELETE FROM "base" WHERE i = 0;

all of them will return

[HV005] ERROR: system attribute "tableoid" can't be fetched from remote relation

but

INSERT INTO "base"(i) VALUES (1);
UPDATE ONLY "base" SET i = 0 WHERE i = 1;
DELETE FROM ONLY "base" WHERE i = 0;

works fine and will only INSERT/UPDATE/DELETEed on the base table not to the foreign one.

surajkharage19 commented 2 months ago

Hi @n0099,

Thanks for reporting this issue. I can reproduce the same at my end. We will check the feasibility of fixing this at our end.