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> --- contrib/tsm_system_time/sql/tsm_system_time.sql | 51 +++++++++++++++++++++++++ 1 file changed, 51 insertions(+) create mode 100644 contrib/tsm_system_time/sql/tsm_system_time.sql (limited to 'contrib/tsm_system_time/sql') diff --git a/contrib/tsm_system_time/sql/tsm_system_time.sql b/contrib/tsm_system_time/sql/tsm_system_time.sql new file mode 100644 index 0000000..117de16 --- /dev/null +++ b/contrib/tsm_system_time/sql/tsm_system_time.sql @@ -0,0 +1,51 @@ +CREATE EXTENSION tsm_system_time; + +CREATE TABLE test_tablesample (id int, name text); +INSERT INTO test_tablesample SELECT i, repeat(i::text, 1000) + FROM generate_series(0, 30) s(i); +ANALYZE test_tablesample; + +-- It's a bit tricky to test SYSTEM_TIME in a platform-independent way. +-- We can test the zero-time corner case ... +SELECT count(*) FROM test_tablesample TABLESAMPLE system_time (0); +-- ... and we assume that this will finish before running out of time: +SELECT count(*) FROM test_tablesample TABLESAMPLE system_time (100000); + +-- bad parameters should get through planning, but not execution: +EXPLAIN (COSTS OFF) +SELECT id FROM test_tablesample TABLESAMPLE system_time (-1); + +SELECT id FROM test_tablesample TABLESAMPLE system_time (-1); + +-- fail, this method is not repeatable: +SELECT * FROM test_tablesample TABLESAMPLE system_time (10) REPEATABLE (0); + +-- since it's not repeatable, we expect a Materialize node in these plans: +EXPLAIN (COSTS OFF) +SELECT * FROM + (VALUES (0),(100000)) v(time), + LATERAL (SELECT COUNT(*) FROM test_tablesample + TABLESAMPLE system_time (100000)) ss; + +SELECT * FROM + (VALUES (0),(100000)) v(time), + LATERAL (SELECT COUNT(*) FROM test_tablesample + TABLESAMPLE system_time (100000)) ss; + +EXPLAIN (COSTS OFF) +SELECT * FROM + (VALUES (0),(100000)) v(time), + LATERAL (SELECT COUNT(*) FROM test_tablesample + TABLESAMPLE system_time (time)) ss; + +SELECT * FROM + (VALUES (0),(100000)) v(time), + LATERAL (SELECT COUNT(*) FROM test_tablesample + TABLESAMPLE system_time (time)) ss; + +CREATE VIEW vv AS + SELECT * FROM test_tablesample TABLESAMPLE system_time (20); + +EXPLAIN (COSTS OFF) SELECT * FROM vv; + +DROP EXTENSION tsm_system_time; -- fail, view depends on extension -- cgit v1.2.3