postgres icon indicating copy to clipboard operation
postgres copied to clipboard

Incorrect multi-query results

Open alxivnov opened this issue 9 months ago • 0 comments

  1. Try executing DELETE... ; UPDATE ...; SELECT ...;.
  2. Get result as an array of 2, where first element is an empty array.
  3. Expectation is to get array of 3 results, like [[], [], [...]].
  4. Somehow all empty arrays from DELETE, UPDATE, INSERT get merged into one.
const postgres = require('postgres');
	it('Multi-query', (done) => {
		const db = postgres.default(CONNECTION_STRING);
		const upd = /*sql*/`
			UPDATE "settings"
			SET "_json" = '[]'
			WHERE "setting" = 'test';
			--RETURNING "setting";
		`;
		const del = /*sql*/`
			DELETE FROM "settings"
			WHERE "setting" = 'test';
			--RETURNING "setting";
			`;
		const ins = /*sql*/`
			INSERT INTO "settings" ("package", "setting", "_json")
			VALUES ('test', 'test', '{}')
			ON CONFLICT ("package", "setting")
			DO NOTHING;
			--RETURNING "settings";
		`;
		const sel = /*sql*/`
			SELECT "_json"
			FROM "settings"
			WHERE "setting" = 'test';
		`;
		Promise.allSettled([
			db.unsafe(upd),
			db.unsafe(del),
			db.unsafe(ins),
			db.unsafe(sel),
			db.unsafe(del + del + ins + sel),
		]).then(([upd, del, ins, sel, all]) => {
			let success = upd.value.length == 0
				&& del.value.length == 0
				&& ins.value.length == 0
				&& sel.value.length == 1
				&& all.value.length == 4; // 4 sub-results: 0th, 1st, 2nd = [], 3rd = [{}]
			done(success ? undefined : all.value.length); // actually, all.value = [[], [{}]]
		});
	});

alxivnov avatar Feb 06 '25 11:02 alxivnov