refinery_cli: ERROR: cannot insert multiple commands into a prepared statement

[lkral-navmatix]

Hello, I have decided to test refinery to see if it'd work for my team, but ran into an issue.

When attempting to run refinery migrate, I always receive this error:
ERROR: cannot insert multiple commands into a prepared statement

I have found a Github issue that mentions this issue but it seems to have been resolved: #21

I am running version 0.8.12, built from crates.io.
Database type is set to "Postgres", but note that we use Citus extension on top of PG.
Our migrations are usually wrapped in their own transaction and include function calls.

Example of typical migration:

START TRANSACTION;

SET LOCAL citus.multi_shard_modify_mode TO 'sequential';

CREATE TABLE public.table (
    id int8 NOT NULL GENERATED ALWAYS AS IDENTITY,
    CONSTRAINT table_pk PRIMARY KEY (id)
);
SELECT create_reference_table('public.table');

COMMIT;

I did a cursory glance at the source code and haven't noticed any explicit use of prepared statements. Running these migration scripts by hand works without issues (using DBeaver).

[jxs]

Hi, and thanks for the interest! Can you identify which migration is causing the issue? You can use run_iter for example

[lkral-navmatix]

I can, it is the very first migration we have. Did some stepping through the code and it is correctly called with batch_execute, but it always fails with above mentioned error.

It is fairly large migration rollup, involving creation of multiple different tables, calling Citus functions on them, creating constraints and such.

Flyway seems to have no issues with it.

Probably won't be able to post the exact migration, but will try to create something simpler to trigger in free time.

[jxs]

ok thanks! Would love to be able to address that issue

[lkral-navmatix]

I have created a simple migration which triggers this issue:

SET LOCAL citus.multi_shard_modify_mode TO 'sequential';

CREATE SCHEMA IF NOT EXISTS "public";

-- public.table1
CREATE TABLE public.table1 (
    id int8 NOT NULL GENERATED ALWAYS AS IDENTITY,
    CONSTRAINT table1_pk PRIMARY KEY (id)
);
SELECT create_reference_table('public.table1');

-- public.table2
CREATE TABLE public.table2 (
    id int8 NOT NULL GENERATED ALWAYS AS IDENTITY,
    parent_id int8 NULL,
    CONSTRAINT table2_pk PRIMARY KEY (id)
);
SELECT create_reference_table('public.table2');
ALTER TABLE public.table2 ADD CONSTRAINT table2_parent_fk FOREIGN KEY (parent_id) REFERENCES public.table2(id);

Removing the last ALTER statement allows the migration to apply without issues.
Note that this migration (and much more complex ones) is applied without any issues when using Flyway.
And to reiterate, this isn't on pure PostgreSQL but Citus extension.

[jxs]

Huh! So if you run it against PostgresSQL it works?

[lkral-navmatix]

Can't really try it against just PostgreSQL as the create_reference_table functions are part of the Citus extension.
I just wanted to say that we aren't talking about pure PostgreSQL here and that they way Citus works might be cause of the issue for refinery.

[jxs]

This probably has to do with the driver we use Transaction::batch_execute where Java's postgres driver doesn't have this issue. Can you try reproducing this just using postgres by loading that "migration" into a string and calling Transaction::batch_execute? If you still are able to reproduce it I'd suggest opening an issue upstream, wdyt?