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

Commitaff8ac7

Browse files
authored
Merge pull request#6 from CherkashinSergey/add_xact_support
Version 1.1: Add support of transactions and savepoints
2 parents08efd62 +a3cefd3 commitaff8ac7

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
ifdefUSE_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 orhole packages:
275+
You can delete variables orwhole 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 createdas 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)
[8]ページ先頭
©2009-2025 Movatter.jp