Reusing a local plan cached for a single-shard INSERT for a multi-shard one causes issues

[onurctirtir]

Can be reproduced at least since Citus >= 12.1.10.

---

Setup steps, from the coordinator;

DROP TABLE IF EXISTS referenced, referencing;

CREATE TABLE referenced (
    shard_key integer not null,
    other_key bigint not null,
    primary key (shard_key, other_key)
);

CREATE TABLE referencing (
    shard_key integer not null,
    other_key bigint not null
);

SELECT create_distributed_table('referenced', 'shard_key');
SELECT create_distributed_table('referencing', 'shard_key', colocate_with => 'referenced');

ALTER TABLE referencing ADD CONSTRAINT fkey FOREIGN KEY (shard_key, other_key) REFERENCES referenced;

-- Inserted shard keys need to be chosen in a way that;
-- * they must belong to different shards
-- * the shards containing them must be present on the same node at the time the prepared statement is executed
--
-- So, choosing 12771 and 0 as shard keys helps reproducing the bug in a 2-worker node (besides coordinator) setup,
-- where, shouldhaveshards for coordinator is false, (i.e., default cluster setup created by citus_dev).
-- In my setup, the shards containing 12771 and 0 are present at worker node with port 9702.
INSERT INTO referenced VALUES (12771, 1), (12771, 2), (0, 2);

Issuing the following from a node that doesn't contain those 2 shards, e.g., coordinator in my setup, doesn't yield a foreign key violation error. However, when we do the same from the node containing those 2 shards, the last prepared statement execution yields a foreign key violation error;

PREPARE prep_stmt (
    bigint, int,
    bigint, int
) AS
INSERT INTO referencing (shard_key, other_key)
VALUES ($1, $2), ($3, $4);

-- execute 7 times to make sure it's cached
EXECUTE prep_stmt(12771, 1, 12771, 2);
EXECUTE prep_stmt(12771, 1, 12771, 2);
EXECUTE prep_stmt(12771, 1, 12771, 2);
EXECUTE prep_stmt(12771, 1, 12771, 2);
EXECUTE prep_stmt(12771, 1, 12771, 2);
EXECUTE prep_stmt(12771, 1, 12771, 2);
EXECUTE prep_stmt(12771, 1, 12771, 2);

EXECUTE prep_stmt(12771, 1, 0, 2);