Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit6fcb337

Browse files
committed
Redesign tablesample method API, and do extensive code review.
The original implementation of TABLESAMPLE modeled the tablesample methodAPI on index access methods, which wasn't a good choice because, withoutspecialized DDL commands, there's no way to build an extension that canimplement a TSM. (Raw inserts into system catalogs are not an acceptablething to do, because we can't undo them during DROP EXTENSION, nor willpg_upgrade behave sanely.) Instead adopt an API more like procedurallanguage handlers or foreign data wrappers, wherein the only SQL-levelsupport object needed is a single handler function identified by havinga special return type. This lets us get rid of the supporting catalogaltogether, so that no custom DDL support is needed for the feature.Adjust the API so that it can support non-constant tablesample arguments(the original coding assumed we could evaluate the argument expressions atExecInitSampleScan time, which is undesirable even if it weren't outrightunsafe), and discourage sampling methods from looking at invisible tuples.Make sure that the BERNOULLI and SYSTEM methods are genuinely repeatablewithin and across queries, as required by the SQL standard, and deal morehonestly with methods that can't support that requirement.Make a full code-review pass over the tablesample additions, and fixassorted bugs, omissions, infelicities, and cosmetic issues (such asfailure to put the added code stanzas in a consistent ordering).Improve EXPLAIN's output of tablesample plans, too.Back-patch to 9.5 so that we don't have to support the original APIin production.
1 parent7d4240d commit6fcb337

File tree

83 files changed

+3116
-2521
lines changed

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

83 files changed

+3116
-2521
lines changed

‎contrib/pg_stat_statements/pg_stat_statements.c

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2297,6 +2297,7 @@ JumbleRangeTable(pgssJumbleState *jstate, List *rtable)
22972297
{
22982298
caseRTE_RELATION:
22992299
APP_JUMB(rte->relid);
2300+
JumbleExpr(jstate, (Node*)rte->tablesample);
23002301
break;
23012302
caseRTE_SUBQUERY:
23022303
JumbleQuery(jstate,rte->subquery);
@@ -2767,6 +2768,15 @@ JumbleExpr(pgssJumbleState *jstate, Node *node)
27672768
JumbleExpr(jstate,rtfunc->funcexpr);
27682769
}
27692770
break;
2771+
caseT_TableSampleClause:
2772+
{
2773+
TableSampleClause*tsc= (TableSampleClause*)node;
2774+
2775+
APP_JUMB(tsc->tsmhandler);
2776+
JumbleExpr(jstate, (Node*)tsc->args);
2777+
JumbleExpr(jstate, (Node*)tsc->repeatable);
2778+
}
2779+
break;
27702780
default:
27712781
/* Only a warning, since we can stumble along anyway */
27722782
elog(WARNING,"unrecognized node type: %d",

‎contrib/tsm_system_rows/Makefile

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,8 +1,8 @@
1-
#src/test/modules/tsm_system_rows/Makefile
1+
#contrib/tsm_system_rows/Makefile
22

33
MODULE_big = tsm_system_rows
44
OBJS = tsm_system_rows.o$(WIN32RES)
5-
PGFILEDESC = "tsm_system_rows -SYSTEMTABLESAMPLE method which accepts number of rows as a limit"
5+
PGFILEDESC = "tsm_system_rows - TABLESAMPLE method which accepts number of rows as a limit"
66

77
EXTENSION = tsm_system_rows
88
DATA = tsm_system_rows--1.0.sql
Lines changed: 74 additions & 22 deletions
Original file line numberDiff line numberDiff line change
@@ -1,31 +1,83 @@
11
CREATE EXTENSION tsm_system_rows;
2-
CREATE TABLE test_tablesample (id int, name text) WITH (fillfactor=10); -- force smaller pages so we don't have to load too much data to get multiple pages
3-
INSERT INTO test_tablesample SELECT i, repeat(i::text, 1000) FROM generate_series(0, 30) s(i) ORDER BY i;
2+
CREATE TABLE test_tablesample (id int, name text);
3+
INSERT INTO test_tablesample SELECT i, repeat(i::text, 1000)
4+
FROM generate_series(0, 30) s(i);
45
ANALYZE test_tablesample;
5-
SELECT count(*) FROM test_tablesample TABLESAMPLE system_rows (1000);
6+
SELECT count(*) FROM test_tablesample TABLESAMPLE system_rows (0);
7+
count
8+
-------
9+
0
10+
(1 row)
11+
12+
SELECT count(*) FROM test_tablesample TABLESAMPLE system_rows (1);
13+
count
14+
-------
15+
1
16+
(1 row)
17+
18+
SELECT count(*) FROM test_tablesample TABLESAMPLE system_rows (10);
19+
count
20+
-------
21+
10
22+
(1 row)
23+
24+
SELECT count(*) FROM test_tablesample TABLESAMPLE system_rows (100);
625
count
726
-------
827
31
928
(1 row)
1029

11-
SELECT id FROM test_tablesample TABLESAMPLE system_rows (8) REPEATABLE (5432);
12-
id
13-
----
14-
7
15-
14
16-
21
17-
28
18-
4
19-
11
20-
18
21-
25
22-
(8 rows)
23-
24-
EXPLAIN SELECT id FROM test_tablesample TABLESAMPLE system_rows (20) REPEATABLE (10);
25-
QUERY PLAN
26-
-----------------------------------------------------------------------------------
27-
Sample Scan (system_rows) on test_tablesample (cost=0.00..80.20 rows=20 width=4)
30+
-- bad parameters should get through planning, but not execution:
31+
EXPLAIN (COSTS OFF)
32+
SELECT id FROM test_tablesample TABLESAMPLE system_rows (-1);
33+
QUERY PLAN
34+
----------------------------------------
35+
Sample Scan on test_tablesample
36+
Sampling: system_rows ('-1'::bigint)
37+
(2 rows)
38+
39+
SELECT id FROM test_tablesample TABLESAMPLE system_rows (-1);
40+
ERROR: sample size must not be negative
41+
-- fail, this method is not repeatable:
42+
SELECT * FROM test_tablesample TABLESAMPLE system_rows (10) REPEATABLE (0);
43+
ERROR: tablesample method system_rows does not support REPEATABLE
44+
LINE 1: SELECT * FROM test_tablesample TABLESAMPLE system_rows (10) ...
45+
^
46+
-- but a join should be allowed:
47+
EXPLAIN (COSTS OFF)
48+
SELECT * FROM
49+
(VALUES (0),(10),(100)) v(nrows),
50+
LATERAL (SELECT count(*) FROM test_tablesample
51+
TABLESAMPLE system_rows (nrows)) ss;
52+
QUERY PLAN
53+
----------------------------------------------------------
54+
Nested Loop
55+
-> Values Scan on "*VALUES*"
56+
-> Aggregate
57+
-> Sample Scan on test_tablesample
58+
Sampling: system_rows ("*VALUES*".column1)
59+
(5 rows)
60+
61+
SELECT * FROM
62+
(VALUES (0),(10),(100)) v(nrows),
63+
LATERAL (SELECT count(*) FROM test_tablesample
64+
TABLESAMPLE system_rows (nrows)) ss;
65+
nrows | count
66+
-------+-------
67+
0 | 0
68+
10 | 10
69+
100 | 31
70+
(3 rows)
71+
72+
CREATE VIEW vv AS
73+
SELECT count(*) FROM test_tablesample TABLESAMPLE system_rows (20);
74+
SELECT * FROM vv;
75+
count
76+
-------
77+
20
2878
(1 row)
2979

30-
-- done
31-
DROP TABLE test_tablesample CASCADE;
80+
DROP EXTENSION tsm_system_rows; -- fail, view depends on extension
81+
ERROR: cannot drop extension tsm_system_rows because other objects depend on it
82+
DETAIL: view vv depends on function system_rows(internal)
83+
HINT: Use DROP ... CASCADE to drop the dependent objects too.
Lines changed: 33 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1,14 +1,39 @@
11
CREATE EXTENSION tsm_system_rows;
22

3-
CREATETABLEtest_tablesample (idint, nametext) WITH (fillfactor=10);-- force smaller pages so we don't have to load too much data to get multiple pages
4-
5-
INSERT INTO test_tablesampleSELECT i, repeat(i::text,1000)FROM generate_series(0,30) s(i)ORDER BY i;
3+
CREATETABLEtest_tablesample (idint, nametext);
4+
INSERT INTO test_tablesampleSELECT i, repeat(i::text,1000)
5+
FROM generate_series(0,30) s(i);
66
ANALYZE test_tablesample;
77

8-
SELECTcount(*)FROM test_tablesample TABLESAMPLE system_rows (1000);
9-
SELECT idFROM test_tablesample TABLESAMPLE system_rows (8) REPEATABLE (5432);
8+
SELECTcount(*)FROM test_tablesample TABLESAMPLE system_rows (0);
9+
SELECTcount(*)FROM test_tablesample TABLESAMPLE system_rows (1);
10+
SELECTcount(*)FROM test_tablesample TABLESAMPLE system_rows (10);
11+
SELECTcount(*)FROM test_tablesample TABLESAMPLE system_rows (100);
12+
13+
-- bad parameters should get through planning, but not execution:
14+
EXPLAIN (COSTS OFF)
15+
SELECT idFROM test_tablesample TABLESAMPLE system_rows (-1);
16+
17+
SELECT idFROM test_tablesample TABLESAMPLE system_rows (-1);
18+
19+
-- fail, this method is not repeatable:
20+
SELECT*FROM test_tablesample TABLESAMPLE system_rows (10) REPEATABLE (0);
21+
22+
-- but a join should be allowed:
23+
EXPLAIN (COSTS OFF)
24+
SELECT*FROM
25+
(VALUES (0),(10),(100)) v(nrows),
26+
LATERAL (SELECTcount(*)FROM test_tablesample
27+
TABLESAMPLE system_rows (nrows)) ss;
28+
29+
SELECT*FROM
30+
(VALUES (0),(10),(100)) v(nrows),
31+
LATERAL (SELECTcount(*)FROM test_tablesample
32+
TABLESAMPLE system_rows (nrows)) ss;
33+
34+
CREATEVIEWvvAS
35+
SELECTcount(*)FROM test_tablesample TABLESAMPLE system_rows (20);
1036

11-
EXPLAINSELECTidFROMtest_tablesample TABLESAMPLE system_rows (20) REPEATABLE (10);
37+
SELECT*FROMvv;
1238

13-
-- done
14-
DROPTABLE test_tablesample CASCADE;
39+
DROP EXTENSION tsm_system_rows;-- fail, view depends on extension
Lines changed: 4 additions & 39 deletions
Original file line numberDiff line numberDiff line change
@@ -1,44 +1,9 @@
1-
/*src/test/modules/tablesample/tsm_system_rows--1.0.sql*/
1+
/*contrib/tsm_system_rows/tsm_system_rows--1.0.sql*/
22

33
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
44
\echo Use"CREATE EXTENSION tsm_system_rows" to load this file. \quit
55

6-
CREATEFUNCTIONtsm_system_rows_init(internal, int4, int4)
7-
RETURNSvoid
8-
AS'MODULE_PATHNAME'
6+
CREATEFUNCTIONsystem_rows(internal)
7+
RETURNStsm_handler
8+
AS'MODULE_PATHNAME','tsm_system_rows_handler'
99
LANGUAGE C STRICT;
10-
11-
CREATEFUNCTIONtsm_system_rows_nextblock(internal)
12-
RETURNS int4
13-
AS'MODULE_PATHNAME'
14-
LANGUAGE C STRICT;
15-
16-
CREATEFUNCTIONtsm_system_rows_nexttuple(internal, int4, int2)
17-
RETURNS int2
18-
AS'MODULE_PATHNAME'
19-
LANGUAGE C STRICT;
20-
21-
CREATEFUNCTIONtsm_system_rows_examinetuple(internal, int4, internal, bool)
22-
RETURNS bool
23-
AS'MODULE_PATHNAME'
24-
LANGUAGE C STRICT;
25-
26-
CREATEFUNCTIONtsm_system_rows_end(internal)
27-
RETURNS void
28-
AS'MODULE_PATHNAME'
29-
LANGUAGE C STRICT;
30-
31-
CREATEFUNCTIONtsm_system_rows_reset(internal)
32-
RETURNS void
33-
AS'MODULE_PATHNAME'
34-
LANGUAGE C STRICT;
35-
36-
CREATEFUNCTIONtsm_system_rows_cost(internal, internal, internal, internal, internal, internal, internal)
37-
RETURNS void
38-
AS'MODULE_PATHNAME'
39-
LANGUAGE C STRICT;
40-
41-
INSERT INTO pg_tablesample_methodVALUES('system_rows', false, true,
42-
'tsm_system_rows_init','tsm_system_rows_nextblock',
43-
'tsm_system_rows_nexttuple','tsm_system_rows_examinetuple',
44-
'tsm_system_rows_end','tsm_system_rows_reset','tsm_system_rows_cost');

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp