DELETE with subquery doesn't delete rows

[jamatthews]

Hello, sorry to file another bug report. I have a strange issue where deleting using a subquery just... doesn't work. It runs but it doesn't delete rows that it should delete.

I have two tables which are range partitioned partitioned by the same column and I'm trying to delete from one table using a sub-query to the other table.

BEGIN;
DELETE FROM table_1 WHERE partiton_column = 1 AND id IN(
    SELECT id FROM table_2 WHERE partition_column = 1
);
DELETE 0
ROLLBACK;

but using USING

BEGIN;
DELETE FROM table_1
USING table_2
WHERE table_1.partiton_column = 1 AND table_2.partition_column = 1 AND table_1.id = table_2.id;
DELETE 9744
ROLLBACK;

[funbringer]

Hi @jamatthews

Hello, sorry to file another bug report

You don't have to apologize for helping us to make pg_pathman better :)

Could you try the master branch for your test?

[jamatthews]

Master seems to work. Was this issue fixed in this commit? be634f0

[funbringer]

Master seems to work. Was this issue fixed in this commit? be634f0

Precisely, we'll roll out an update ASAP (we have a few more bugfixes that's not quite there yet). As a workaround, you can safely use master until it's ready.

[funbringer]

Released 1.4.2.

[jamatthews]

Hello, thanks for fixing this! I've been really busy with other stuff but we've just updated to pathman 1.4.2. The subquery works perfectly now. However... the USING form of the DELETE query now throws an error:
ERROR: variable not found in subplan target lists

[funbringer]

Hi @jamatthews

Could you open a new issue regarding this problem with DELETE + USING?