From 5e45211a64149b3c659b90ff2de6fa982a5a93ed Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 4 May 2024 14:17:33 +0200 Subject: Adding upstream version 15.5. Signed-off-by: Daniel Baumann --- .../tsm_system_time/expected/tsm_system_time.out | 100 +++++++++++++++++++++ 1 file changed, 100 insertions(+) create mode 100644 contrib/tsm_system_time/expected/tsm_system_time.out (limited to 'contrib/tsm_system_time/expected') diff --git a/contrib/tsm_system_time/expected/tsm_system_time.out b/contrib/tsm_system_time/expected/tsm_system_time.out new file mode 100644 index 0000000..ac44f30 --- /dev/null +++ b/contrib/tsm_system_time/expected/tsm_system_time.out @@ -0,0 +1,100 @@ +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); + count +------- + 0 +(1 row) + +-- ... and we assume that this will finish before running out of time: +SELECT count(*) FROM test_tablesample TABLESAMPLE system_time (100000); + count +------- + 31 +(1 row) + +-- bad parameters should get through planning, but not execution: +EXPLAIN (COSTS OFF) +SELECT id FROM test_tablesample TABLESAMPLE system_time (-1); + QUERY PLAN +-------------------------------------------------- + Sample Scan on test_tablesample + Sampling: system_time ('-1'::double precision) +(2 rows) + +SELECT id FROM test_tablesample TABLESAMPLE system_time (-1); +ERROR: sample collection time must not be negative +-- fail, this method is not repeatable: +SELECT * FROM test_tablesample TABLESAMPLE system_time (10) REPEATABLE (0); +ERROR: tablesample method system_time does not support REPEATABLE +LINE 1: SELECT * FROM test_tablesample TABLESAMPLE system_time (10) ... + ^ +-- 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; + QUERY PLAN +------------------------------------------------------------------------ + Nested Loop + -> Aggregate + -> Materialize + -> Sample Scan on test_tablesample + Sampling: system_time ('100000'::double precision) + -> Values Scan on "*VALUES*" +(6 rows) + +SELECT * FROM + (VALUES (0),(100000)) v(time), + LATERAL (SELECT COUNT(*) FROM test_tablesample + TABLESAMPLE system_time (100000)) ss; + time | count +--------+------- + 0 | 31 + 100000 | 31 +(2 rows) + +EXPLAIN (COSTS OFF) +SELECT * FROM + (VALUES (0),(100000)) v(time), + LATERAL (SELECT COUNT(*) FROM test_tablesample + TABLESAMPLE system_time (time)) ss; + QUERY PLAN +---------------------------------------------------------------- + Nested Loop + -> Values Scan on "*VALUES*" + -> Aggregate + -> Materialize + -> Sample Scan on test_tablesample + Sampling: system_time ("*VALUES*".column1) +(6 rows) + +SELECT * FROM + (VALUES (0),(100000)) v(time), + LATERAL (SELECT COUNT(*) FROM test_tablesample + TABLESAMPLE system_time (time)) ss; + time | count +--------+------- + 0 | 0 + 100000 | 31 +(2 rows) + +CREATE VIEW vv AS + SELECT * FROM test_tablesample TABLESAMPLE system_time (20); +EXPLAIN (COSTS OFF) SELECT * FROM vv; + QUERY PLAN +-------------------------------------------------- + Sample Scan on test_tablesample + Sampling: system_time ('20'::double precision) +(2 rows) + +DROP EXTENSION tsm_system_time; -- fail, view depends on extension +ERROR: cannot drop extension tsm_system_time because other objects depend on it +DETAIL: view vv depends on function system_time(internal) +HINT: Use DROP ... CASCADE to drop the dependent objects too. -- cgit v1.2.3