Applying migrations returns "cannot insert multiple commands into a prepared statement"

[LehuyH]

Bug report

Describe the bug

Trying to get my local database in-sync with remote. Creating the initial migration was successful but I am unable to apply it since there is a "Error: ERROR: cannot insert multiple commands into a prepared statement (SQLSTATE 42601)"

To Reproduce

supabase-cli 1.7.9 on windows 10

1. Run supabase db remote commit
2. Run supabase db reset (or start)
3. Applying migration ....remote_commit.sql... Error: ERROR: cannot insert multiple commands into a prepared statement (SQLSTATE 42601)

Expected behavior

It applies the migration

Screenshots

System information

• Operating System: Windows_NT
• Node Version: v16.14.0
• Package Manager: [email protected]

Additional context

Starting superbase local with a blank DB, then running the migration file via the SQL editor in the studio works just fine.

[sweatybridge]

Hi @LehuyH , could you try running with supabase db reset --debug and show us the last 50 lines of debug logs here?

[LehuyH]

2022/10/13 20:42:35 PG Recv: {"Type":"CommandComplete","CommandTag":"ALTER TABLE"}
2022/10/13 20:42:35 PG Recv: {"Type":"ParseComplete"}
2022/10/13 20:42:35 PG Recv: {"Type":"BindComplete"}
2022/10/13 20:42:35 PG Recv: {"Type":"NoData"}
2022/10/13 20:42:35 PG Recv: {"Type":"CommandComplete","CommandTag":"ALTER TABLE"}
2022/10/13 20:42:35 PG Recv: {"Type":"ParseComplete"}
2022/10/13 20:42:35 PG Recv: {"Type":"BindComplete"}
2022/10/13 20:42:35 PG Recv: {"Type":"NoData"}
2022/10/13 20:42:35 PG Recv: {"Type":"CommandComplete","CommandTag":"ALTER TABLE"}
2022/10/13 20:42:35 PG Recv: {"Type":"ParseComplete"}
2022/10/13 20:42:35 PG Recv: {"Type":"BindComplete"}
2022/10/13 20:42:35 PG Recv: {"Type":"NoData"}
2022/10/13 20:42:35 PG Recv: {"Type":"CommandComplete","CommandTag":"ALTER TABLE"}
2022/10/13 20:42:35 PG Recv: {"Type":"ParseComplete"}
2022/10/13 20:42:35 PG Recv: {"Type":"BindComplete"}
2022/10/13 20:42:35 PG Recv: {"Type":"NoData"}
2022/10/13 20:42:35 PG Recv: {"Type":"CommandComplete","CommandTag":"ALTER TABLE"}
2022/10/13 20:42:35 PG Recv: {"Type":"ParseComplete"}
2022/10/13 20:42:35 PG Recv: {"Type":"BindComplete"}
2022/10/13 20:42:35 PG Recv: {"Type":"NoData"}
2022/10/13 20:42:35 PG Recv: {"Type":"CommandComplete","CommandTag":"ALTER TABLE"}
2022/10/13 20:42:35 PG Recv: {"Type":"ParseComplete"}
2022/10/13 20:42:35 PG Recv: {"Type":"BindComplete"}
2022/10/13 20:42:35 PG Recv: {"Type":"NoData"}
2022/10/13 20:42:35 PG Recv: {"Type":"CommandComplete","CommandTag":"ALTER TABLE"}
2022/10/13 20:42:35 PG Recv: {"Type":"ParseComplete"}
2022/10/13 20:42:35 PG Recv: {"Type":"BindComplete"}
2022/10/13 20:42:35 PG Recv: {"Type":"NoData"}
2022/10/13 20:42:35 PG Recv: {"Type":"CommandComplete","CommandTag":"ALTER TABLE"}
2022/10/13 20:42:35 PG Recv: {"Type":"ParseComplete"}
2022/10/13 20:42:35 PG Recv: {"Type":"BindComplete"}
2022/10/13 20:42:35 PG Recv: {"Type":"NoData"}
2022/10/13 20:42:35 PG Recv: {"Type":"CommandComplete","CommandTag":"ALTER TABLE"}
2022/10/13 20:42:35 PG Recv: {"Type":"ParseComplete"}
2022/10/13 20:42:35 PG Recv: {"Type":"BindComplete"}
2022/10/13 20:42:35 PG Recv: {"Type":"NoData"}
2022/10/13 20:42:35 PG Recv: {"Type":"CommandComplete","CommandTag":"ALTER TABLE"}
2022/10/13 20:42:35 PG Recv: {"Type":"ParseComplete"}
2022/10/13 20:42:35 PG Recv: {"Type":"BindComplete"}
2022/10/13 20:42:35 PG Recv: {"Type":"NoData"}
2022/10/13 20:42:35 PG Recv: {"Type":"CommandComplete","CommandTag":"ALTER TABLE"}
2022/10/13 20:42:35 PG Recv: {"Type":"ParseComplete"}
2022/10/13 20:42:35 PG Recv: {"Type":"BindComplete"}
2022/10/13 20:42:35 PG Recv: {"Type":"NoData"}
2022/10/13 20:42:35 PG Recv: {"Type":"CommandComplete","CommandTag":"ALTER TABLE"}
2022/10/13 20:42:35 PG Recv: {"Type":"ErrorResponse","Severity":"ERROR","SeverityUnlocalized":"ERROR","Code":"42601","Message":"cannot insert multiple commands into a prepared statement","Detail":"","Hint":"","Position":0,"InternalPosition":0,"InternalQuery":"","Where":"","SchemaName":"","TableName":"","ColumnName":"","DataTypeName":"","ConstraintName":"","File":"postgres.c","Line":1413,"Routine":"exec_parse_message","UnknownFields":null}
2022/10/13 20:42:35 PG Recv: {"Type":"ReadyForQuery","TxStatus":"I"}
2022/10/13 20:42:35 PG Send: {"Type":"Terminate"}
Error: ERROR: cannot insert multiple commands into a prepared statement (SQLSTATE 42601)

[sweatybridge]

Sorry could you help me isolate the problem further by deleting from _remote_commit.sql until the error is gone? I suspect there's an edge case with parsing statements that caused multiple statements to be grouped into one.

[LehuyH]

It seems like the following RLS policy causes the error @sweatybridge

--
-- Name: cards Allow card read if deck is public; Type: POLICY; Schema: public; Owner: supabase_admin
--

CREATE POLICY "Allow card read if deck is public" ON "public"."cards" FOR SELECT USING ((( SELECT "decks"."private"
   FROM "public"."decks"
  WHERE ("decks"."id" = "cards"."deck_id")) = false));

[sweatybridge]

It seems like the following RLS policy causes the error

Thank you for providing this script. Unfortunately I tried to reproduce locally without success.

Here's the sql file I created:

supabase/migrations/20221014075902_remote_commit.sql

create table "public"."decks" (
    "id" bigint generated by default as identity not null,
    "private" boolean not null default false
);

create table "public"."cards" (
    "id" bigint generated by default as identity not null,
    "deck_id" bigint not null
);

CREATE UNIQUE INDEX decks_pkey ON public.decks USING btree (id);

CREATE UNIQUE INDEX cards_pkey ON public.cards USING btree (id);

alter table "public"."decks" add constraint "decks_pkey" PRIMARY KEY using index "decks_pkey";

alter table "public"."cards" add constraint "cards_pkey" PRIMARY KEY using index "cards_pkey";

alter table "public"."cards" add constraint "cards_deck_id_fkey" FOREIGN KEY (deck_id) REFERENCES decks(id) not valid;

alter table "public"."cards" validate constraint "cards_deck_id_fkey";

--
-- Name: cards Allow card read if deck is public; Type: POLICY; Schema: public; Owner: supabase_admin
--

CREATE POLICY "Allow card read if deck is public" ON "public"."cards" FOR SELECT USING ((( SELECT "decks"."private"
   FROM "public"."decks"
  WHERE ("decks"."id" = "cards"."deck_id")) = false));

Both supabase start and supabase db reset succeeded without error:

$ supabase db reset
Resetting database...
Initialising schema...
Applying migration 20221014075902_remote_commit.sql...
Finished supabase db reset on branch main.

Would you be ok with providing us the full *_remote_commit.sql script by emailing [email protected]?

[LehuyH]

I investigated further and added a new RLS policy that causes the problem to recur; it appears that the two statements together are what to blame.

Edited SQL script

create table "public"."decks" (
    "id" bigint generated by default as identity not null,
    "private" boolean not null default false
);

create table "public"."cards" (
    "id" bigint generated by default as identity not null,
    "deck_id" bigint not null
);

CREATE UNIQUE INDEX decks_pkey ON public.decks USING btree (id);

CREATE UNIQUE INDEX cards_pkey ON public.cards USING btree (id);

alter table "public"."decks" add constraint "decks_pkey" PRIMARY KEY using index "decks_pkey";

alter table "public"."cards" add constraint "cards_pkey" PRIMARY KEY using index "cards_pkey";

alter table "public"."cards" add constraint "cards_deck_id_fkey" FOREIGN KEY (deck_id) REFERENCES decks(id) not valid;

alter table "public"."cards" validate constraint "cards_deck_id_fkey";

--
-- Name: cards Allow all for owners of the card's deck; Type: POLICY; Schema: public; Owner: supabase_admin
--

CREATE POLICY "Allow all for owners of the card's deck" ON "public"."cards" USING (("auth"."uid"() IN ( SELECT "decks"."user_id"
   FROM "public"."decks"
  WHERE ("decks"."id" = "cards"."deck_id")))) WITH CHECK (("auth"."uid"() IN ( SELECT "decks"."user_id"
   FROM "public"."decks"
  WHERE ("decks"."id" = "cards"."deck_id"))));


--
-- Name: cards Allow card read if deck is public; Type: POLICY; Schema: public; Owner: supabase_admin
--

CREATE POLICY "Allow card read if deck is public" ON "public"."cards" FOR SELECT USING ((( SELECT "decks"."private"
   FROM "public"."decks"
  WHERE ("decks"."id" = "cards"."deck_id")) = false));

[github-actions[bot]]

:tada: This issue has been resolved in version 1.8.7 :tada:

The release is available on:

• npm package (@latest dist-tag)
• GitHub release

Your semantic-release bot :package::rocket:

[sweatybridge]

I'm able to reproduce the error with your example now. It should be fixed in v1.8.7. Appreciate your help so far.