summaryrefslogtreecommitdiffstats
path: root/contrib/tsm_system_time/expected
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:17:33 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:17:33 +0000
commit5e45211a64149b3c659b90ff2de6fa982a5a93ed (patch)
tree739caf8c461053357daa9f162bef34516c7bf452 /contrib/tsm_system_time/expected
parentInitial commit. (diff)
downloadpostgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.tar.xz
postgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.zip
Adding upstream version 15.5.upstream/15.5
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'contrib/tsm_system_time/expected')
-rw-r--r--contrib/tsm_system_time/expected/tsm_system_time.out100
1 files changed, 100 insertions, 0 deletions
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.