summaryrefslogtreecommitdiffstats
path: root/contrib/tsm_system_rows/expected
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:15:05 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:15:05 +0000
commit46651ce6fe013220ed397add242004d764fc0153 (patch)
tree6e5299f990f88e60174a1d3ae6e48eedd2688b2b /contrib/tsm_system_rows/expected
parentInitial commit. (diff)
downloadpostgresql-14-upstream.tar.xz
postgresql-14-upstream.zip
Adding upstream version 14.5.upstream/14.5upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'contrib/tsm_system_rows/expected')
-rw-r--r--contrib/tsm_system_rows/expected/tsm_system_rows.out83
1 files changed, 83 insertions, 0 deletions
diff --git a/contrib/tsm_system_rows/expected/tsm_system_rows.out b/contrib/tsm_system_rows/expected/tsm_system_rows.out
new file mode 100644
index 0000000..87b4a8f
--- /dev/null
+++ b/contrib/tsm_system_rows/expected/tsm_system_rows.out
@@ -0,0 +1,83 @@
+CREATE EXTENSION tsm_system_rows;
+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;
+SELECT count(*) FROM test_tablesample TABLESAMPLE system_rows (0);
+ count
+-------
+ 0
+(1 row)
+
+SELECT count(*) FROM test_tablesample TABLESAMPLE system_rows (1);
+ count
+-------
+ 1
+(1 row)
+
+SELECT count(*) FROM test_tablesample TABLESAMPLE system_rows (10);
+ count
+-------
+ 10
+(1 row)
+
+SELECT count(*) FROM test_tablesample TABLESAMPLE system_rows (100);
+ count
+-------
+ 31
+(1 row)
+
+-- bad parameters should get through planning, but not execution:
+EXPLAIN (COSTS OFF)
+SELECT id FROM test_tablesample TABLESAMPLE system_rows (-1);
+ QUERY PLAN
+----------------------------------------
+ Sample Scan on test_tablesample
+ Sampling: system_rows ('-1'::bigint)
+(2 rows)
+
+SELECT id FROM test_tablesample TABLESAMPLE system_rows (-1);
+ERROR: sample size must not be negative
+-- fail, this method is not repeatable:
+SELECT * FROM test_tablesample TABLESAMPLE system_rows (10) REPEATABLE (0);
+ERROR: tablesample method system_rows does not support REPEATABLE
+LINE 1: SELECT * FROM test_tablesample TABLESAMPLE system_rows (10) ...
+ ^
+-- but a join should be allowed:
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (VALUES (0),(10),(100)) v(nrows),
+ LATERAL (SELECT count(*) FROM test_tablesample
+ TABLESAMPLE system_rows (nrows)) ss;
+ QUERY PLAN
+----------------------------------------------------------
+ Nested Loop
+ -> Values Scan on "*VALUES*"
+ -> Aggregate
+ -> Sample Scan on test_tablesample
+ Sampling: system_rows ("*VALUES*".column1)
+(5 rows)
+
+SELECT * FROM
+ (VALUES (0),(10),(100)) v(nrows),
+ LATERAL (SELECT count(*) FROM test_tablesample
+ TABLESAMPLE system_rows (nrows)) ss;
+ nrows | count
+-------+-------
+ 0 | 0
+ 10 | 10
+ 100 | 31
+(3 rows)
+
+CREATE VIEW vv AS
+ SELECT count(*) FROM test_tablesample TABLESAMPLE system_rows (20);
+SELECT * FROM vv;
+ count
+-------
+ 20
+(1 row)
+
+DROP EXTENSION tsm_system_rows; -- fail, view depends on extension
+ERROR: cannot drop extension tsm_system_rows because other objects depend on it
+DETAIL: view vv depends on function system_rows(internal)
+HINT: Use DROP ... CASCADE to drop the dependent objects too.