diff options
author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 12:19:15 +0000 |
---|---|---|
committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 12:19:15 +0000 |
commit | 6eb9c5a5657d1fe77b55cc261450f3538d35a94d (patch) | |
tree | 657d8194422a5daccecfd42d654b8a245ef7b4c8 /contrib/tsm_system_rows/expected/tsm_system_rows.out | |
parent | Initial commit. (diff) | |
download | postgresql-13-upstream.tar.xz postgresql-13-upstream.zip |
Adding upstream version 13.4.upstream/13.4upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'contrib/tsm_system_rows/expected/tsm_system_rows.out')
-rw-r--r-- | contrib/tsm_system_rows/expected/tsm_system_rows.out | 83 |
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. |