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

Version 1.1: Add support of transactions and savepoints#6

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to ourterms of service andprivacy statement. We’ll occasionally send you account related emails.

Already on GitHub?Sign in to your account

Merged
Merged
Show file tree
Hide file tree
Changes fromall commits
Commits
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
2 changes: 1 addition & 1 deletion.gitignore
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -38,5 +38,5 @@ lib*.pc
/Debug/
/Release/
/tmp_install/

Dockerfile
pg_variables--1.1.sql
10 changes: 8 additions & 2 deletionsMakefile
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -4,10 +4,13 @@ MODULE_big = pg_variables
OBJS = pg_variables.o pg_variables_record.o $(WIN32RES)

EXTENSION = pg_variables
DATA = pg_variables--1.0.sql
EXTVERSION = 1.1
DATA = pg_variables--1.0.sql pg_variables--1.0--1.1.sql
DATA_built = $(EXTENSION)--$(EXTVERSION).sql

PGFILEDESC = "pg_variables - sessional variables"

REGRESS = pg_variables pg_variables_any
REGRESS = pg_variables pg_variables_any pg_variables_trans

ifdef USE_PGXS
PG_CONFIG = pg_config
Expand All@@ -19,3 +22,6 @@ top_builddir = ../..
include $(top_builddir)/src/Makefile.global
include $(top_srcdir)/contrib/contrib-global.mk
endif

$(EXTENSION)--$(EXTVERSION).sql: $(DATA)
cat $^ > $@
134 changes: 108 additions & 26 deletionsREADME.md
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -8,24 +8,39 @@

The **pg_variables** module provides functions to work with variables of various
types. Created variables live only in the current user session.

Note that the module does **not support transactions and savepoints**. For
example:

By default, created variables are not transactional (i.e. they are not affected
by `BEGIN`, `COMMIT` or `ROLLBACK` statements). This, however, is customizable
by argument `is_transactional` of `pgv_set()`:
```sql
SELECT pgv_set('vars', 'int1', 101);
BEGIN;
SELECT pgv_set('vars', 'int2', 102);
ROLLBACK;

SELECT * FROM pgv_list() order by package, name;
package | name
---------+------
vars | int1
vars | int2
package | name | is_transactional
---------+------+------------------
vars | int1 | f
vars | int2 | f
(2 rows)
```

But if variable created with flag **is_transactional**:
```sql
BEGIN;
SELECT pgv_set('vars', 'trans_int', 101, true);
SAVEPOINT sp1;
SELECT pgv_set('vars', 'trans_int', 102, true);
ROLLBACK TO sp1;
COMMIT;
SELECT pgv_get('vars', 'trans_int', NULL::int);

pgv_get
---------
101
(1 row)
```

## License

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

Function | Returns
-------- | -------
`pgv_set(package text, name text, value anynonarray)` | `void`
`pgv_set(package text, name text, value anynonarray, is_transactional bool default false)` | `void`
`pgv_get(package text, name text, var_type anynonarray, strict bool default true)` | `anynonarray`

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

Function | Returns
-------- | -------
`pgv_set_int(package text, name text, value int)` | `void`
`pgv_set_int(package text, name text, value int, is_transactional bool default false)` | `void`
`pgv_get_int(package text, name text, strict bool default true)` | `int`

### Text variables

Function | Returns
-------- | -------
`pgv_set_text(package text, name text, value text)` | `void`
`pgv_set_text(package text, name text, value text, is_transactional bool default false)` | `void`
`pgv_get_text(package text, name text, strict bool default true)` | `text`

### Numeric variables

Function | Returns
-------- | -------
`pgv_set_numeric(package text, name text, value numeric)` | `void`
`pgv_set_numeric(package text, name text, value numeric, is_transactional bool default false)` | `void`
`pgv_get_numeric(package text, name text, strict bool default true)` | `numeric`

### Timestamp variables

Function | Returns
-------- | -------
`pgv_set_timestamp(package text, name text, value timestamp)` | `void`
`pgv_set_timestamp(package text, name text, value timestamp, is_transactional bool default false)` | `void`
`pgv_get_timestamp(package text, name text, strict bool default true)` | `timestamp`

### Timestamp with timezone variables

Function | Returns
-------- | -------
`pgv_set_timestamptz(package text, name text, value timestamptz)` | `void`
`pgv_set_timestamptz(package text, name text, value timestamptz, is_transactional bool default false)` | `void`
`pgv_get_timestamptz(package text, name text, strict bool default true)` | `timestamptz`

### Date variables

Function | Returns
-------- | -------
`pgv_set_date(package text, name text, value date)` | `void`
`pgv_set_date(package text, name text, value date, is_transactional bool default false)` | `void`
`pgv_get_date(package text, name text, strict bool default true)` | `date`

### Jsonb variables

Function | Returns
-------- | -------
`pgv_set_jsonb(package text, name text, value jsonb)` | `void`
`pgv_set_jsonb(package text, name text, value jsonb, is_transactional bool default false)` | `void`
`pgv_get_jsonb(package text, name text, strict bool default true)` | `jsonb`

## Record variables functions
Expand All@@ -146,7 +161,7 @@ raised.

Function | Returns | Description
-------- | ------- | -----------
`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.
`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.
`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.
`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.
`pgv_select(package text, name text)` | `set of record` | Returns the variable collection records.
Expand All@@ -162,7 +177,7 @@ Function | Returns | Description
`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.
`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.
`pgv_free()` | `void` | Removes all packages and variables.
`pgv_list()` | `table(package text, name text)` | Returns set of records of assigned packages and variables.
`pgv_list()` | `table(package text, name text, is_transactional bool)` | Returns set of records of assigned packages and variables.
`pgv_stats()` | `table(package text, used_memory bigint)` | Returns list of assigned packages and used memory in bytes.

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

SELECT pgv_get('vars', 'int1', NULL::int);
pgv_get_int
pgv_get_int
-------------
101
(1 row)

SELECT pgv_get('vars', 'int2', NULL::int);
pgv_get_int
pgv_get_int
-------------
102
(1 row)
Expand DownExpand Up@@ -239,11 +254,11 @@ You can list packages and variables:

```sql
SELECT * FROM pgv_list() order by package, name;
package | name
---------+------
vars | int1
vars | int2
vars | r1
package | name| is_transactional
---------+------+------------------
vars | int1 | f
vars | int2 | f
vars | r1 | f
(3 rows)
```

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

You can delete variables orhole packages:
You can delete variables orwhole packages:

```sql
SELECT pgv_remove('vars', 'int1');
Expand All@@ -268,3 +283,70 @@ You can delete all packages and variables:
```sql
SELECT pgv_free();
```

If you want variables with support of transactions and savepoints, you should
add flag `is_transactional = true` as the last argument in functions `pgv_set()`
or `pgv_insert()`.
Following use cases describe behavior of transactional variables:
```sql
SELECT pgv_set('pack', 'var_text', 'before transaction block'::text, true);
BEGIN;
SELECT pgv_set('pack', 'var_text', 'before savepoint'::text, true);
SAVEPOINT sp1;
SELECT pgv_set('pack', 'var_text', 'savepoint sp1'::text, true);
SAVEPOINT sp2;
SELECT pgv_set('pack', 'var_text', 'savepoint sp2'::text, true);
RELEASE sp2;
SELECT pgv_get('pack', 'var_text', NULL::text);
pgv_get
---------------
savepoint sp2

ROLLBACK TO sp1;
SELECT pgv_get('pack', 'var_text', NULL::text);
pgv_get
------------------
before savepoint
(1 row)

ROLLBACK;
SELECT pgv_get('pack', 'var_text', NULL::text);
pgv_get
--------------------------
before transaction block

```
If you create variable after `BEGIN` or `SAVEPOINT` statements and than rollback
to previous state - variable will not be exist:
```sql
BEGIN;
SAVEPOINT sp1;
SAVEPOINT sp2;
SELECT pgv_set('pack', 'var_int', 122, true);
RELEASE SAVEPOINT sp2;
SELECT pgv_get('pack', 'var_int', NULL::int);
pgv_get
---------
122
(1 row)

ROLLBACK TO sp1;
SELECT pgv_get('pack','var_int', NULL::int);
ERROR: unrecognized variable "var_int"
COMMIT;
```
If you created transactional variable once, you should use flag `is_transactional`
every time when you want to change variable value by functions `pgv_set()`,
`pgv_insert()` and deprecated setters (i.e. `pgv_set_int()`). If you try to
change this option, you'll get an error:
```sql
SELECT pgv_insert('pack', 'var_record', row(123::int, 'text'::text), true);
pgv_insert
------------

(1 row)

SELECT pgv_insert('pack', 'var_record', row(456::int, 'another text'::text));
ERROR: variable "var_record" already created as TRANSACTIONAL
```
Functions `pgv_update()` and `pgv_delete()` do not require this flag.
102 changes: 55 additions & 47 deletionsexpected/pg_variables.out
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -645,32 +645,40 @@ SELECT pgv_select('vars2', 'j1');
ERROR: variable "j1" requires "jsonb" value
-- Manipulate variables
SELECT * FROM pgv_list() order by package, name;
package | name
---------+----------
vars | d1
vars | d2
vars | dNULL
vars | int1
vars | int2
vars | intNULL
vars | jNULL
vars | num1
vars | num2
vars | numNULL
vars | str1
vars | str2
vars | strNULL
vars | ts1
vars | ts2
vars | tsNULL
vars | tstz1
vars | tstz2
vars | tstzNULL
vars2 | j1
vars2 | j2
vars3 | r1
package | name| is_transactional
---------+----------+------------------
vars | d1 | f
vars | d2 | f
vars | dNULL | f
vars | int1 | f
vars | int2 | f
vars | intNULL | f
vars | jNULL | f
vars | num1 | f
vars | num2 | f
vars | numNULL | f
vars | str1 | f
vars | str2 | f
vars | strNULL | f
vars | ts1 | f
vars | ts2 | f
vars | tsNULL | f
vars | tstz1 | f
vars | tstz2 | f
vars | tstzNULL | f
vars2 | j1 | f
vars2 | j2 | f
vars3 | r1 | f
(22 rows)

SELECT package FROM pgv_stats() order by package;
package
---------
vars
vars2
vars3
(3 rows)

SELECT pgv_remove('vars', 'int3');
ERROR: unrecognized variable "int3"
SELECT pgv_remove('vars', 'int1');
Expand DownExpand Up@@ -702,27 +710,27 @@ SELECT pgv_exists('vars2');
(1 row)

SELECT * FROM pgv_list() order by package, name;
package | name
---------+----------
vars | d1
vars | d2
vars | dNULL
vars | int2
vars | intNULL
vars | jNULL
vars | num1
vars | num2
vars | numNULL
vars | str1
vars | str2
vars | strNULL
vars | ts1
vars | ts2
vars | tsNULL
vars | tstz1
vars | tstz2
vars | tstzNULL
vars3 | r1
package | name| is_transactional
---------+----------+------------------
vars | d1 | f
vars | d2 | f
vars | dNULL | f
vars | int2 | f
vars | intNULL | f
vars | jNULL | f
vars | num1 | f
vars | num2 | f
vars | numNULL | f
vars | str1 | f
vars | str2 | f
vars | strNULL | f
vars | ts1 | f
vars | ts2 | f
vars | tsNULL | f
vars | tstz1 | f
vars | tstz2 | f
vars | tstzNULL | f
vars3 | r1 | f
(19 rows)

SELECT pgv_free();
Expand All@@ -738,7 +746,7 @@ SELECT pgv_exists('vars');
(1 row)

SELECT * FROM pgv_list() order by package, name;
package | name
---------+------
package | name| is_transactional
---------+------+------------------
(0 rows)

Loading
[8]ページ先頭
©2009-2025 Movatter.jp