citusdata / citusdata/citus

Running DDL EXECUTE in function is incorrectly replicated to workers

Open
#6,608 6 comments 0 reactions 0 assignees View on GitHub
bug
Dominant language
C
Stars
12.8k
Forks
794
Avg merge
2d 14h
Merged PRs (30d)
31

Description

Small reproducible example for what we encountered in a more complex case. Reproduces on PG15 + Citus 11.1 with at least one worker (besides the coordinator).

```
create table t1 (a int, b int);
select create_distributed_table('t1', 'a');

CREATE OR REPLACE FUNCTION exec_func()
RETURNS void
LANGUAGE plpgsql
AS $function$
BEGIN
EXECUTE $$
create index on t1 (a);
create index on t1 (b);
$$;
END
$function$
;

select exec_func();
```

Error:

```
SQL Error [42601]: ERROR: cannot insert multiple commands into a prepared statement
Where: while executing command on worker:5432
SQL statement "

create index on t1 (a);

create index on t1 (b);

"
PL/pgSQL function exec_func() line 3 at EXECUTE
```

Creating one index inside the `EXECUTE` works fine. It fails when creating multiple ones. Seems like it's trying to replicate things inside `EXECUTE` in a prepared statement to the workers, but since it contains two statements this fails?

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.