electric-sql / electric-sql/pglite

Returned rows ordered different than `pg` in `MERGE INTO ... RETURNING` query.

Open
#722 3 comments 0 reactions 0 assignees View on GitHub
compatibility
Dominant language
TypeScript
Stars
16k
Forks
442
Avg merge
20h 19m
Merged PRs (30d)
7

Description

Hey 👋

This one's coming off of me working on a core PGlite dialect in Kysely, and adding it to our tests.

Looks like PGlite is returning the result records of a `MERGE INTO ... RETURNING` query in a different order than `pg` + Docker. Rows are returned as-is from the driver in both cases.

This can be a pain in PGlite adoption as a replacement for that setup in tests.

Full scenario:

```sql
create table "person" ("id" serial primary key, "first_name" varchar(255), "middle_name" varchar(255), "last_name" varchar(255), "gender" varchar(50) not null, "marital_status" varchar(50), "children" integer default 0 not null);
create table "pet" ("id" serial primary key, "name" varchar(255) not null unique, "owner_id" integer not null references "person" ("id") on delete cascade, "species" varchar(50) not null);
create table "toy" ("id" serial primary key, "name" varchar(255) not null, "pet_id" integer not null references "pet" ("id"), "price" double precision not null);
COMMENT ON COLUMN toy.price IS 'Price in USD';
create index "pet_owner_id_index" on "pet" ("owner_id");
insert into "person" ("first_name", "last_name", "gender", "marital_status") values ($1, $2, $3, $4) returning "id";
-- parameters: ["Jennifer","Aniston","female","divorced"]
insert into "pet" ("name", "species", "owner_id") values ($1, $2, $3) returning "id";
-- parameters: ["Catto","cat",1]
insert into "person" ("first_name", "last_name", "gender", "marital_status") values ($1, $2, $3, $4) returning "id";
-- parameters: ["Arnold","Schwarzenegger","male","divorced"]
insert into "pet" ("name", "species", "owner_id") values ($1, $2, $3) returning "id";
-- parameters: ["Doggo","dog",2]
insert into "person" ("first_name", "last_name", "gender", "marital_status") values ($1, $2, $3, $4) returning "id";
-- parameters: ["Sylvester","Stallone","male","married"]
insert into "pet" ("name", "species", "owner_id") values ($1, $2, $3) returning "id";
-- parameters: ["Hammo","hamster",3]
insert into "person" ("first_name", "gender") values ($1, $2);
-- parameters: ["Moshe","other"]
SET session_replication_role = 'replica';
insert into "pet" ("name", "owner_id", "species") values ($1, $2, $3);
-- parameters: ["Ralph",9999,"hamster"]
SET session_replication_role = 'origin';
merge into "pet" using "person" on "pet"."owner_id" = "person"."id" when matched then update set "name" = "person"."first_name" || '''s pet' when not matched then insert ("name", "owner_id", "species") values ("person"."first_name" || '''s pet', "person"."id", $1) when not matched by source then delete returning merge_action() as "action", "pet"."name";
-- parameters: ["hamster"]
```

Expected (how `pg` always returns):

```ts
[
{ action: 'UPDATE', name: "Jennifer's pet" },
{ action: 'UPDATE', name: "Arnold's pet" },
{ action: 'UPDATE', name: "Sylvester's pet" },
{ action: 'DELETE', name: 'Ralph' }, // <-------------
{ action: 'INSERT', name: "Moshe's pet" },
]
```

Actual (how `PGlite` always returns):

```ts
[
{ action: 'UPDATE', name: "Jennifer's pet" },
{ action: 'UPDATE', name: "Arnold's pet" },
{ action: 'UPDATE', name: "Sylvester's pet" },
{ action: 'INSERT', name: "Moshe's pet" },
{ action: 'DELETE', name: 'Ralph' }, // <------------
]
```

PGlite version: `0.3.4`
Node.js version: `22.15.0`
`pg` version: `8.16.3`
PostgreSQL version: `17.4`

Thanks for your continuous efforts to make the web, testing, and local development awesome! 🙏

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.