From 46651ce6fe013220ed397add242004d764fc0153 Mon Sep 17 00:00:00 2001
From: Daniel Baumann <daniel.baumann@progress-linux.org>
Date: Sat, 4 May 2024 14:15:05 +0200
Subject: Adding upstream version 14.5.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
---
 src/test/regress/sql/combocid.sql | 111 ++++++++++++++++++++++++++++++++++++++
 1 file changed, 111 insertions(+)
 create mode 100644 src/test/regress/sql/combocid.sql

(limited to 'src/test/regress/sql/combocid.sql')

diff --git a/src/test/regress/sql/combocid.sql b/src/test/regress/sql/combocid.sql
new file mode 100644
index 0000000..a5cdf6d
--- /dev/null
+++ b/src/test/regress/sql/combocid.sql
@@ -0,0 +1,111 @@
+--
+-- Tests for some likely failure cases with combo cmin/cmax mechanism
+--
+CREATE TEMP TABLE combocidtest (foobar int);
+
+BEGIN;
+
+-- a few dummy ops to push up the CommandId counter
+INSERT INTO combocidtest SELECT 1 LIMIT 0;
+INSERT INTO combocidtest SELECT 1 LIMIT 0;
+INSERT INTO combocidtest SELECT 1 LIMIT 0;
+INSERT INTO combocidtest SELECT 1 LIMIT 0;
+INSERT INTO combocidtest SELECT 1 LIMIT 0;
+INSERT INTO combocidtest SELECT 1 LIMIT 0;
+INSERT INTO combocidtest SELECT 1 LIMIT 0;
+INSERT INTO combocidtest SELECT 1 LIMIT 0;
+INSERT INTO combocidtest SELECT 1 LIMIT 0;
+INSERT INTO combocidtest SELECT 1 LIMIT 0;
+
+INSERT INTO combocidtest VALUES (1);
+INSERT INTO combocidtest VALUES (2);
+
+SELECT ctid,cmin,* FROM combocidtest;
+
+SAVEPOINT s1;
+
+UPDATE combocidtest SET foobar = foobar + 10;
+
+-- here we should see only updated tuples
+SELECT ctid,cmin,* FROM combocidtest;
+
+ROLLBACK TO s1;
+
+-- now we should see old tuples, but with combo CIDs starting at 0
+SELECT ctid,cmin,* FROM combocidtest;
+
+COMMIT;
+
+-- combo data is not there anymore, but should still see tuples
+SELECT ctid,cmin,* FROM combocidtest;
+
+-- Test combo CIDs with portals
+BEGIN;
+
+INSERT INTO combocidtest VALUES (333);
+
+DECLARE c CURSOR FOR SELECT ctid,cmin,* FROM combocidtest;
+
+DELETE FROM combocidtest;
+
+FETCH ALL FROM c;
+
+ROLLBACK;
+
+SELECT ctid,cmin,* FROM combocidtest;
+
+-- check behavior with locked tuples
+BEGIN;
+
+-- a few dummy ops to push up the CommandId counter
+INSERT INTO combocidtest SELECT 1 LIMIT 0;
+INSERT INTO combocidtest SELECT 1 LIMIT 0;
+INSERT INTO combocidtest SELECT 1 LIMIT 0;
+INSERT INTO combocidtest SELECT 1 LIMIT 0;
+INSERT INTO combocidtest SELECT 1 LIMIT 0;
+INSERT INTO combocidtest SELECT 1 LIMIT 0;
+INSERT INTO combocidtest SELECT 1 LIMIT 0;
+INSERT INTO combocidtest SELECT 1 LIMIT 0;
+INSERT INTO combocidtest SELECT 1 LIMIT 0;
+INSERT INTO combocidtest SELECT 1 LIMIT 0;
+
+INSERT INTO combocidtest VALUES (444);
+
+SELECT ctid,cmin,* FROM combocidtest;
+
+SAVEPOINT s1;
+
+-- this doesn't affect cmin
+SELECT ctid,cmin,* FROM combocidtest FOR UPDATE;
+SELECT ctid,cmin,* FROM combocidtest;
+
+-- but this does
+UPDATE combocidtest SET foobar = foobar + 10;
+
+SELECT ctid,cmin,* FROM combocidtest;
+
+ROLLBACK TO s1;
+
+SELECT ctid,cmin,* FROM combocidtest;
+
+COMMIT;
+
+SELECT ctid,cmin,* FROM combocidtest;
+
+-- test for bug reported in
+-- CABRT9RC81YUf1=jsmWopcKJEro=VoeG2ou6sPwyOUTx_qteRsg@mail.gmail.com
+CREATE TABLE IF NOT EXISTS testcase(
+	id int PRIMARY KEY,
+	balance numeric
+);
+INSERT INTO testcase VALUES (1, 0);
+BEGIN;
+SELECT * FROM testcase WHERE testcase.id = 1 FOR UPDATE;
+UPDATE testcase SET balance = balance + 400 WHERE id=1;
+SAVEPOINT subxact;
+UPDATE testcase SET balance = balance - 100 WHERE id=1;
+ROLLBACK TO SAVEPOINT subxact;
+-- should return one tuple
+SELECT * FROM testcase WHERE id = 1 FOR UPDATE;
+ROLLBACK;
+DROP TABLE testcase;
-- 
cgit v1.2.3