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

[igalklebanov]

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:

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):

[
	{ 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):

[
	{ 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! 🙏

[samwillis]

Hey 👋

Exited to hear about an official PGlite Kysely dialect!

I know with a normal select Postgres makes no guarantees about ordering (with no order by), and we have had reports of our order being deferent from a standard Postgres. I believe this is likely down to PGlite being essentially a 32bit build of Postgres and so likely has different memory layout. Pages are read in slightly different order and so unordered results are read out differently than on a standard build.

This is slightly different from a returning query, and we should take a look, but I suspect it's a similar issue.

From your experience is this a common pattern in tests, where the order of the results from a returning query are asserted?

One option is to wrap the query as a CTE and add an order by, but understandably that's a mitigation you may not want to roll out everywhere.

[igalklebanov]

That's true.

Had brownfield projects in mind here. Projects that have a lot of tests in place, with assertions on query results - direct or after some processing - that are stable given the conditions are pretty much the same in their test setup.

Not being aligned with their current setup, would require them to make changes to test code and perhaps even source code. PGlite may not be a simple drop-in replacement.

Should this be documented somewhere? so people know of this caveat. "Yeah it's 17.4, but you might need to add some order by's, CTEs, or programmatic sorting to your tests, if you're asserting on results".

[dlight]

Is this just Hyrum's Law, or does Postgres actually guarantees an order for returning queries?