Skip to content

Commit aff8ac7

Browse files
za-arthurza-arthur
authored
Merge pull request #6 from CherkashinSergey/add_xact_support
Version 1.1: Add support of transactions and savepoints
2 parents 08efd62 + a3cefd3 commit aff8ac7

13 files changed

+2978
-210
lines changed

‎.gitignore

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -38,5 +38,5 @@ lib*.pc
3838
/Debug/
3939
/Release/
4040
/tmp_install/
41-
4241
Dockerfile
42+
pg_variables--1.1.sql

‎Makefile

Lines changed: 8 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4,10 +4,13 @@ MODULE_big = pg_variables
44
OBJS = pg_variables.o pg_variables_record.o $(WIN32RES)
55

66
EXTENSION = pg_variables
7-
DATA = pg_variables--1.0.sql
7+
EXTVERSION = 1.1
8+
DATA = pg_variables--1.0.sql pg_variables--1.0--1.1.sql
9+
DATA_built = $(EXTENSION)--$(EXTVERSION).sql
10+
811
PGFILEDESC = "pg_variables - sessional variables"
912

10-
REGRESS = pg_variables pg_variables_any
13+
REGRESS = pg_variables pg_variables_any pg_variables_trans
1114

1215
ifdef USE_PGXS
1316
PG_CONFIG = pg_config
@@ -19,3 +22,6 @@ top_builddir = ../..
1922
include $(top_builddir)/src/Makefile.global
2023
include $(top_srcdir)/contrib/contrib-global.mk
2124
endif
25+
26+
$(EXTENSION)--$(EXTVERSION).sql: $(DATA)
27+
cat $^ > $@

‎README.md

Lines changed: 108 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -8,24 +8,39 @@
88

99
The **pg_variables** module provides functions to work with variables of various
1010
types. Created variables live only in the current user session.
11-
12-
Note that the module does **not support transactions and savepoints**. For
13-
example:
14-
11+
By default, created variables are not transactional (i.e. they are not affected
12+
by `BEGIN`, `COMMIT` or `ROLLBACK` statements). This, however, is customizable
13+
by argument `is_transactional` of `pgv_set()`:
1514
```sql
1615
SELECT pgv_set('vars', 'int1', 101);
1716
BEGIN;
1817
SELECT pgv_set('vars', 'int2', 102);
1918
ROLLBACK;
2019

2120
SELECT * FROM pgv_list() order by package, name;
22-
package | name
23-
---------+------
24-
vars | int1
25-
vars | int2
21+
package | name | is_transactional
22+
---------+------+------------------
23+
vars | int1 | f
24+
vars | int2 | f
2625
(2 rows)
2726
```
2827

28+
But if variable created with flag **is_transactional**:
29+
```sql
30+
BEGIN;
31+
SELECT pgv_set('vars', 'trans_int', 101, true);
32+
SAVEPOINT sp1;
33+
SELECT pgv_set('vars', 'trans_int', 102, true);
34+
ROLLBACK TO sp1;
35+
COMMIT;
36+
SELECT pgv_get('vars', 'trans_int', NULL::int);
37+
38+
pgv_get
39+
---------
40+
101
41+
(1 row)
42+
```
43+
2944
## License
3045

3146
This module available under the same license as
@@ -76,7 +91,7 @@ ERROR: variable "int1" requires "integer" value
7691

7792
Function | Returns
7893
-------- | -------
79-
`pgv_set(package text, name text, value anynonarray)` | `void`
94+
`pgv_set(package text, name text, value anynonarray, is_transactional bool default false)` | `void`
8095
`pgv_get(package text, name text, var_type anynonarray, strict bool default true)` | `anynonarray`
8196

8297
## **Deprecated** scalar variables functions
@@ -85,49 +100,49 @@ Function | Returns
85100

86101
Function | Returns
87102
-------- | -------
88-
`pgv_set_int(package text, name text, value int)` | `void`
103+
`pgv_set_int(package text, name text, value int, is_transactional bool default false)` | `void`
89104
`pgv_get_int(package text, name text, strict bool default true)` | `int`
90105

91106
### Text variables
92107

93108
Function | Returns
94109
-------- | -------
95-
`pgv_set_text(package text, name text, value text)` | `void`
110+
`pgv_set_text(package text, name text, value text, is_transactional bool default false)` | `void`
96111
`pgv_get_text(package text, name text, strict bool default true)` | `text`
97112

98113
### Numeric variables
99114

100115
Function | Returns
101116
-------- | -------
102-
`pgv_set_numeric(package text, name text, value numeric)` | `void`
117+
`pgv_set_numeric(package text, name text, value numeric, is_transactional bool default false)` | `void`
103118
`pgv_get_numeric(package text, name text, strict bool default true)` | `numeric`
104119

105120
### Timestamp variables
106121

107122
Function | Returns
108123
-------- | -------
109-
`pgv_set_timestamp(package text, name text, value timestamp)` | `void`
124+
`pgv_set_timestamp(package text, name text, value timestamp, is_transactional bool default false)` | `void`
110125
`pgv_get_timestamp(package text, name text, strict bool default true)` | `timestamp`
111126

112127
### Timestamp with timezone variables
113128

114129
Function | Returns
115130
-------- | -------
116-
`pgv_set_timestamptz(package text, name text, value timestamptz)` | `void`
131+
`pgv_set_timestamptz(package text, name text, value timestamptz, is_transactional bool default false)` | `void`
117132
`pgv_get_timestamptz(package text, name text, strict bool default true)` | `timestamptz`
118133

119134
### Date variables
120135

121136
Function | Returns
122137
-------- | -------
123-
`pgv_set_date(package text, name text, value date)` | `void`
138+
`pgv_set_date(package text, name text, value date, is_transactional bool default false)` | `void`
124139
`pgv_get_date(package text, name text, strict bool default true)` | `date`
125140

126141
### Jsonb variables
127142

128143
Function | Returns
129144
-------- | -------
130-
`pgv_set_jsonb(package text, name text, value jsonb)` | `void`
145+
`pgv_set_jsonb(package text, name text, value jsonb, is_transactional bool default false)` | `void`
131146
`pgv_get_jsonb(package text, name text, strict bool default true)` | `jsonb`
132147

133148
## Record variables functions
@@ -146,7 +161,7 @@ raised.
146161

147162
Function | Returns | Description
148163
-------- | ------- | -----------
149-
`pgv_insert(package text, name text, r record)` | `void` | Inserts a record to the variable collection. If package and variable do not exists they will be created. The first column of **r** will be a primary key. If exists a record with the same primary key the error will be raised. If this variable collection has other structure the error will be raised.
164+
`pgv_insert(package text, name text, r record, is_transactional bool default false)` | `void` | Inserts a record to the variable collection. If package and variable do not exists they will be created. The first column of **r** will be a primary key. If exists a record with the same primary key the error will be raised. If this variable collection has other structure the error will be raised.
150165
`pgv_update(package text, name text, r record)` | `boolean` | Updates a record with the corresponding primary key (the first column of **r** is a primary key). Returns **true** if a record was found. If this variable collection has other structure the error will be raised.
151166
`pgv_delete(package text, name text, value anynonarray)` | `boolean` | Deletes a record with the corresponding primary key (the first column of **r** is a primary key). Returns **true** if a record was found.
152167
`pgv_select(package text, name text)` | `set of record` | Returns the variable collection records.
@@ -162,7 +177,7 @@ Function | Returns | Description
162177
`pgv_remove(package text, name text)` | `void` | Removes the variable with the corresponding name. Required package and variable must exists, otherwise the error will be raised.
163178
`pgv_remove(package text)` | `void` | Removes the package and all package variables with the corresponding name. Required package must exists, otherwise the error will be raised.
164179
`pgv_free()` | `void` | Removes all packages and variables.
165-
`pgv_list()` | `table(package text, name text)` | Returns set of records of assigned packages and variables.
180+
`pgv_list()` | `table(package text, name text, is_transactional bool)` | Returns set of records of assigned packages and variables.
166181
`pgv_stats()` | `table(package text, used_memory bigint)` | Returns list of assigned packages and used memory in bytes.
167182

168183
Note that **pgv_stats()** works only with the PostgreSQL 9.6 and newer.
@@ -176,13 +191,13 @@ SELECT pgv_set('vars', 'int1', 101);
176191
SELECT pgv_set('vars', 'int2', 102);
177192

178193
SELECT pgv_get('vars', 'int1', NULL::int);
179-
pgv_get_int
194+
pgv_get_int
180195
-------------
181196
101
182197
(1 row)
183198

184199
SELECT pgv_get('vars', 'int2', NULL::int);
185-
pgv_get_int
200+
pgv_get_int
186201
-------------
187202
102
188203
(1 row)
@@ -239,11 +254,11 @@ You can list packages and variables:
239254

240255
```sql
241256
SELECT * FROM pgv_list() order by package, name;
242-
package | name
243-
---------+------
244-
vars | int1
245-
vars | int2
246-
vars | r1
257+
package | name | is_transactional
258+
---------+------+------------------
259+
vars | int1 | f
260+
vars | int2 | f
261+
vars | r1 | f
247262
(3 rows)
248263
```
249264

@@ -257,7 +272,7 @@ SELECT * FROM pgv_stats() order by package;
257272
(1 row)
258273
```
259274

260-
You can delete variables or hole packages:
275+
You can delete variables or whole packages:
261276

262277
```sql
263278
SELECT pgv_remove('vars', 'int1');
@@ -268,3 +283,70 @@ You can delete all packages and variables:
268283
```sql
269284
SELECT pgv_free();
270285
```
286+
287+
If you want variables with support of transactions and savepoints, you should
288+
add flag `is_transactional = true` as the last argument in functions `pgv_set()`
289+
or `pgv_insert()`.
290+
Following use cases describe behavior of transactional variables:
291+
```sql
292+
SELECT pgv_set('pack', 'var_text', 'before transaction block'::text, true);
293+
BEGIN;
294+
SELECT pgv_set('pack', 'var_text', 'before savepoint'::text, true);
295+
SAVEPOINT sp1;
296+
SELECT pgv_set('pack', 'var_text', 'savepoint sp1'::text, true);
297+
SAVEPOINT sp2;
298+
SELECT pgv_set('pack', 'var_text', 'savepoint sp2'::text, true);
299+
RELEASE sp2;
300+
SELECT pgv_get('pack', 'var_text', NULL::text);
301+
pgv_get
302+
---------------
303+
savepoint sp2
304+
305+
ROLLBACK TO sp1;
306+
SELECT pgv_get('pack', 'var_text', NULL::text);
307+
pgv_get
308+
------------------
309+
before savepoint
310+
(1 row)
311+
312+
ROLLBACK;
313+
SELECT pgv_get('pack', 'var_text', NULL::text);
314+
pgv_get
315+
--------------------------
316+
before transaction block
317+
318+
```
319+
If you create variable after `BEGIN` or `SAVEPOINT` statements and than rollback
320+
to previous state - variable will not be exist:
321+
```sql
322+
BEGIN;
323+
SAVEPOINT sp1;
324+
SAVEPOINT sp2;
325+
SELECT pgv_set('pack', 'var_int', 122, true);
326+
RELEASE SAVEPOINT sp2;
327+
SELECT pgv_get('pack', 'var_int', NULL::int);
328+
pgv_get
329+
---------
330+
122
331+
(1 row)
332+
333+
ROLLBACK TO sp1;
334+
SELECT pgv_get('pack','var_int', NULL::int);
335+
ERROR: unrecognized variable "var_int"
336+
COMMIT;
337+
```
338+
If you created transactional variable once, you should use flag `is_transactional`
339+
every time when you want to change variable value by functions `pgv_set()`,
340+
`pgv_insert()` and deprecated setters (i.e. `pgv_set_int()`). If you try to
341+
change this option, you'll get an error:
342+
```sql
343+
SELECT pgv_insert('pack', 'var_record', row(123::int, 'text'::text), true);
344+
pgv_insert
345+
------------
346+
347+
(1 row)
348+
349+
SELECT pgv_insert('pack', 'var_record', row(456::int, 'another text'::text));
350+
ERROR: variable "var_record" already created as TRANSACTIONAL
351+
```
352+
Functions `pgv_update()` and `pgv_delete()` do not require this flag.

‎expected/pg_variables.out

Lines changed: 55 additions & 47 deletions
Original file line numberDiff line numberDiff line change
@@ -645,32 +645,40 @@ SELECT pgv_select('vars2', 'j1');
645645
ERROR: variable "j1" requires "jsonb" value
646646
-- Manipulate variables
647647
SELECT * FROM pgv_list() order by package, name;
648-
package | name
649-
---------+----------
650-
vars | d1
651-
vars | d2
652-
vars | dNULL
653-
vars | int1
654-
vars | int2
655-
vars | intNULL
656-
vars | jNULL
657-
vars | num1
658-
vars | num2
659-
vars | numNULL
660-
vars | str1
661-
vars | str2
662-
vars | strNULL
663-
vars | ts1
664-
vars | ts2
665-
vars | tsNULL
666-
vars | tstz1
667-
vars | tstz2
668-
vars | tstzNULL
669-
vars2 | j1
670-
vars2 | j2
671-
vars3 | r1
648+
package | name | is_transactional
649+
---------+----------+------------------
650+
vars | d1 | f
651+
vars | d2 | f
652+
vars | dNULL | f
653+
vars | int1 | f
654+
vars | int2 | f
655+
vars | intNULL | f
656+
vars | jNULL | f
657+
vars | num1 | f
658+
vars | num2 | f
659+
vars | numNULL | f
660+
vars | str1 | f
661+
vars | str2 | f
662+
vars | strNULL | f
663+
vars | ts1 | f
664+
vars | ts2 | f
665+
vars | tsNULL | f
666+
vars | tstz1 | f
667+
vars | tstz2 | f
668+
vars | tstzNULL | f
669+
vars2 | j1 | f
670+
vars2 | j2 | f
671+
vars3 | r1 | f
672672
(22 rows)
673673

674+
SELECT package FROM pgv_stats() order by package;
675+
package
676+
---------
677+
vars
678+
vars2
679+
vars3
680+
(3 rows)
681+
674682
SELECT pgv_remove('vars', 'int3');
675683
ERROR: unrecognized variable "int3"
676684
SELECT pgv_remove('vars', 'int1');
@@ -702,27 +710,27 @@ SELECT pgv_exists('vars2');
702710
(1 row)
703711

704712
SELECT * FROM pgv_list() order by package, name;
705-
package | name
706-
---------+----------
707-
vars | d1
708-
vars | d2
709-
vars | dNULL
710-
vars | int2
711-
vars | intNULL
712-
vars | jNULL
713-
vars | num1
714-
vars | num2
715-
vars | numNULL
716-
vars | str1
717-
vars | str2
718-
vars | strNULL
719-
vars | ts1
720-
vars | ts2
721-
vars | tsNULL
722-
vars | tstz1
723-
vars | tstz2
724-
vars | tstzNULL
725-
vars3 | r1
713+
package | name | is_transactional
714+
---------+----------+------------------
715+
vars | d1 | f
716+
vars | d2 | f
717+
vars | dNULL | f
718+
vars | int2 | f
719+
vars | intNULL | f
720+
vars | jNULL | f
721+
vars | num1 | f
722+
vars | num2 | f
723+
vars | numNULL | f
724+
vars | str1 | f
725+
vars | str2 | f
726+
vars | strNULL | f
727+
vars | ts1 | f
728+
vars | ts2 | f
729+
vars | tsNULL | f
730+
vars | tstz1 | f
731+
vars | tstz2 | f
732+
vars | tstzNULL | f
733+
vars3 | r1 | f
726734
(19 rows)
727735

728736
SELECT pgv_free();
@@ -738,7 +746,7 @@ SELECT pgv_exists('vars');
738746
(1 row)
739747

740748
SELECT * FROM pgv_list() order by package, name;
741-
package | name
742-
---------+------
749+
package | name | is_transactional
750+
---------+------+------------------
743751
(0 rows)
744752

0 commit comments

Comments
 (0)