citusdata / citusdata/citus

BUG: can't call parameterized distributed procedures outside an explicit transaction in Citus 12.1.3 (but not 12.1.0?)

Open
#7,597 9 comments 0 reactions 0 assignees View on GitHub
bug cherry-pick-12.1 cherry-pick-13.0
Dominant language
C
Stars
12.8k
Forks
794
Avg merge
2d 14h
Merged PRs (30d)
31

Description

In Citus 12.1.3, when calling a distributed procedure in a batch of SQL statements, the CALL statement appears to execute in a different transaction from the rest of the statements in the batch. Also, it is generally impossible to call distributed procedures with batch parameters as arguments to the procedure. This behavior is not observed in Citus 12.1.0.

### Repro steps ###
First, perform this setup:
```SQL
CREATE TABLE t (p INT NOT NULL, i INT NOT NULL, PRIMARY KEY (p, i));
SELECT create_distributed_table('t', 'p');
CREATE PROCEDURE f(_p INT, _i INT) LANGUAGE plpgsql AS
$$ BEGIN SELECT i / 0 FROM t WHERE p = _p AND i = _i; END; $$;
SELECT create_distributed_function('f(INT,INT)', distribution_arg_name := '_p', colocate_with := 't');
```

Second, call the procedure in a parameterized batch of SQL statements:
```SQL
INSERT INTO t (p, i) VALUES (@0, @1);
CALL f(1, @1);
```
In this batch, I used parameters `@0` = 1 and `@1` = 2, but the specific values don't matter. The batch was sent with the Npgsql library, but any library that allows sending parameterized batches should be able to reproduce this bug. (Don't try putting the statements into a SQL procedure and calling it. That's not likely to reproduce the problem. Use a client library to send the parameterized batch.) Example Npgsql code:
```C#
NpgsqlCommand cmd = connection.CreateCommand();
cmd.CommandText = "INSERT INTO t (p, i) VALUES (@0, @1); CALL f(1, @1);";
cmd.Parameters.Add(new NpgsqlParameter("@0", 1));
cmd.Parameters.Add(new NpgsqlParameter("@1", 2));
cmd.ExecuteNonQuery();
```

**Expected behavior:**
1. A tuple should be inserted into table t.
2. Procedure `f(1, 2)` should be called, and fail with a division by zero error.
3. The transaction should roll back, removing the inserted tuple.

**Actual behavior:**
1. A tuple is inserted into table t.
2. Procedure `f` is _not_ called. Instead, an error occurs: "42P02: there is no parameter $1"
3. The INSERT statement is still committed (i.e. the inserted tuple is not removed).

### Additional Comments ###
- The problem happens in Citus 12.1.3 but not 12.1.0, I _believe_.
- You don't even need the INSERT statement to trigger the error. Any CALL to a distributed procedure with batch parameters (outside of an explicit transaction) will fail! E.g. if the batch is simply `CALL f(1, @0);` it will fail.
- The problem only happens if it's a distributed function. If you don't call `create_distributed_function`, the problem does not occur.
- The problem does not occur when an explicit transaction is used, e.g. `BEGIN; INSERT ...; CALL ...; COMMIT;`
- The problem does not occur if the CALL does not have batch parameters, e.g. `CALL f(1, 2);`
- It doesn't matter whether the function `f` is supposed to succeed or fail. It never gets called in the first place.
- I previously reported #7242 which was also related to parameterized distributed functions called outside explicit transactions. It may be related.
- This is a fairly serious problem that I hope gets fixed soon...

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.