Skip to content

Commit 688d8d6

Browse files
dlepikhovadlepikhova
committed
Recovery sr_plan commits for version_1.1_stable branch
As part of the inclusion of scheduled mirroring, a significant part of the commits in the sr_plan repository was lost. This commit restores all the difference between the state of the code on gitlab and the actual code in the Std and EE branches
1 parent efac7c3 commit 688d8d6

File tree

10 files changed

+98
-537
lines changed

10 files changed

+98
-537
lines changed

‎.gitignore

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -18,7 +18,6 @@ deserialize.c
1818
serialize.c
1919

2020
sr_plan--1.1.sql
21-
sr_plan--1.2.sql
2221
pycparser
2322
/Mako*
2423
/site-packages

‎Makefile

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -6,12 +6,12 @@ OBJS = sr_plan.o $(WIN32RES)
66
PGFILEDESC = "sr_plan - save and read plan"
77

88
EXTENSION = sr_plan
9-
EXTVERSION = 1.2
9+
EXTVERSION = 1.1
1010
DATA_built = sr_plan--$(EXTVERSION).sql
11-
DATA = sr_plan--1.0--1.1.sql sr_plan--1.1--1.2.sql
11+
DATA = sr_plan--1.0--1.1.sql
1212

1313
EXTRA_CLEAN = sr_plan--$(EXTVERSION).sql
14-
REGRESS = sr_plan sr_plan_schema joins explain
14+
REGRESS = security sr_plan sr_plan_schema
1515

1616
ifdef USE_PGXS
1717
ifndef PG_CONFIG

‎README.md

Lines changed: 1 addition & 62 deletions
Original file line numberDiff line numberDiff line change
@@ -41,7 +41,7 @@ Make an example query:
4141
select query_hash from sr_plans where query_hash=10;
4242
```
4343

44-
Disable saving the plan for the query:
44+
Disable saving the query:
4545
```SQL
4646
set sr_plan.write_mode = false;
4747
```
@@ -68,64 +68,3 @@ If we keep the plan for the query and enable it to be used also for the followin
6868
select query_hash from sr_plans where query_hash=1000+_p(11);
6969
select query_hash from sr_plans where query_hash=1000+_p(-5);
7070
```
71-
72-
## EXPLAIN for saved plans
73-
74-
It is possible to see saved plans by using `show_plan` function. It requires
75-
knowing query hash which could be fetched from `sr_plans` table.
76-
77-
Examples:
78-
79-
Show enabled plan for query hash:
80-
81-
```SQL
82-
SELECT show_plan(1);
83-
show_plan
84-
----------------------------------------------
85-
("Seq Scan on public.explain_test")
86-
(" Output: test_attr1, test_attr2")
87-
(" Filter: (explain_test.test_attr1 = 10)")
88-
(3 rows)
89-
```
90-
91-
Get second saved plan by using `index` parameter (ignores `enable` attribute):
92-
93-
```SQL
94-
SELECT show_plan(1, index := 2);
95-
show_plan
96-
----------------------------------------------
97-
("Seq Scan on public.explain_test")
98-
(" Output: test_attr1, test_attr2")
99-
(" Filter: (explain_test.test_attr1 = 10)")
100-
(3 rows)
101-
```
102-
103-
Use another output format (supported formats are `json`, `text`, `xml`, `yaml`):
104-
105-
```SQL
106-
SELECT show_plan(1, format := 'json');
107-
show_plan
108-
------------------------------------------------------
109-
("[ +
110-
{ +
111-
""Plan"": { +
112-
""Node Type"": ""Seq Scan"", +
113-
""Parallel Aware"": false, +
114-
""Relation Name"": ""explain_test"", +
115-
""Schema"": ""public"", +
116-
""Alias"": ""explain_test"", +
117-
""Output"": [""test_attr1"", ""test_attr2""], +
118-
""Filter"": ""(explain_test.test_attr1 = 10)""+
119-
} +
120-
} +
121-
]")
122-
(1 row)
123-
```
124-
125-
## `pg_stat_statements` integration
126-
127-
`sr_plans` table contains `query_id` columns which could be used to make
128-
joins with `pg_stat_statements` tables and views.
129-
130-
Note: in `shared_preload_libraries` list `pg_stat_statements` should be
131-
specified after `sr_plan`.

‎expected/sr_plan.out

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -112,5 +112,4 @@ SELECT * FROM test_table WHERE test_attr1 = 15;
112112
(0 rows)
113113

114114
DROP EXTENSION sr_plan CASCADE;
115-
NOTICE: sr_plan was disabled
116115
DROP TABLE test_table;

‎init.sql

Lines changed: 3 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -5,11 +5,10 @@
55

66
CREATE TABLE sr_plans (
77
query_hash int NOT NULL,
8-
query_id int8 NOT NULL,
98
plan_hash int NOT NULL,
10-
enable boolean NOT NULL,
119
query varchar NOT NULL,
1210
plan text NOT NULL,
11+
enable boolean NOT NULL,
1312

1413
reloids oid[],
1514
index_reloids oid[]
@@ -21,17 +20,10 @@ CREATE INDEX sr_plans_query_index_oids ON sr_plans USING gin(index_reloids);
2120

2221
CREATE FUNCTION _p(anyelement)
2322
RETURNS anyelement
24-
AS 'MODULE_PATHNAME', 'do_nothing'
23+
AS 'MODULE_PATHNAME'
2524
LANGUAGE C STRICT VOLATILE;
2625

27-
CREATE FUNCTION show_plan(query_hash int4,
28-
index int4 default null,
29-
format cstring default null)
30-
RETURNS SETOF RECORD
31-
AS 'MODULE_PATHNAME', 'show_plan'
32-
LANGUAGE C VOLATILE;
33-
34-
CREATE OR REPLACE FUNCTION sr_plan_invalid_table() RETURNS event_trigger
26+
CREATE FUNCTION sr_plan_invalid_table() RETURNS event_trigger
3527
LANGUAGE plpgsql AS $$
3628
DECLARE
3729
obj record;

‎sql/sr_plan.sql

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,6 @@ SELECT * FROM test_table WHERE test_attr1 = 15;
1515

1616
UPDATE sr_plans SET enable = true;
1717

18-
1918
SELECT * FROM test_table WHERE test_attr1 = _p(10);
2019
SELECT * FROM test_table WHERE test_attr1 = _p(15);
2120
SELECT * FROM test_table WHERE test_attr1 = 10;

‎sr_plan--1.0--1.1.sql

Lines changed: 2 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,3 @@
1-
DROP FUNCTION sr_plan_invalid_table() CASCADE;
2-
DROP FUNCTION explain_jsonb_plan(jsonb) CASCADE;
31
DROP TABLE sr_plans CASCADE;
42
CREATE TABLE sr_plans (
53
query_hash int NOT NULL,
@@ -16,25 +14,5 @@ CREATE INDEX sr_plans_query_hash_idx ON sr_plans (query_hash);
1614
CREATE INDEX sr_plans_query_oids ON sr_plans USING gin(reloids);
1715
CREATE INDEX sr_plans_query_index_oids ON sr_plans USING gin(index_reloids);
1816

19-
CREATE OR REPLACE FUNCTION sr_plan_invalid_table() RETURNS event_trigger
20-
LANGUAGE plpgsql AS $$
21-
DECLARE
22-
obj record;
23-
indobj record;
24-
BEGIN
25-
FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
26-
WHERE object_type = 'table' OR object_type = 'index'
27-
LOOP
28-
IF obj.object_type = 'table' THEN
29-
DELETE FROM @extschema@.sr_plans WHERE reloids @> ARRAY[obj.objid];
30-
ELSE
31-
IF obj.object_type = 'index' THEN
32-
DELETE FROM @extschema@.sr_plans WHERE index_reloids @> ARRAY[obj.objid];
33-
END IF;
34-
END IF;
35-
END LOOP;
36-
END
37-
$$;
38-
39-
CREATE EVENT TRIGGER sr_plan_invalid_table ON sql_drop
40-
EXECUTE PROCEDURE sr_plan_invalid_table();
17+
DROP FUNCTION explain_jsonb_plan(jsonb) CASCADE;
18+
DROP FUNCTION sr_plan_invalid_table() CASCADE;

0 commit comments

Comments
 (0)