8000 Version 1.1: Add support of transactions and savepoints by CherkashinSergey · Pull Request #6 · postgrespro/pg_variables · GitHub
[go: up one dir, main page]
Skip to content

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.

Sign up for GitHub

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

Already on GitHub? Sign in to your account

Merged
merged 9 commits into from
Apr 25, 2018
Merged

Version 1.1: Add support of transactions and savepoints #6

Show file tree
Hide file tree
Changes from 3 commits
Commits
Show all changes
9 commits
Select commit Hold shift + click to select a range
5510083
Version 1.1: Add support of transactions and savepoints
CherkashinSergey Apr 13, 2018
81a684b
Extension script generates from init.sql. Refactoring
CherkashinSergey Apr 18, 2018
595a779
Merge branch 'master' into add_xact_support
CherkashinSergey Apr 18, 2018
a86437f
Fix Makefile
CherkashinSergey Apr 20, 2018
430989c
Fix README.md
CherkashinSergey Apr 21, 2018
2fb64f8
Optimize savepoint creation number
CherkashinSergey Apr 21, 2018
9126f9c
Fix removing icons from README.md
CherkashinSergey Apr 23, 2018
e7c12d9
Fix macro
CherkashinSergey Apr 23, 2018
a3cefd3
Optimization of memory context proceedeng. Typos corrected
CherkashinSergey Apr 24, 2018
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
3 changes: 2 additions & 1 deletion .gitignore
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -38,5 +38,6 @@ lib*.pc
/Debug/
/Release/
/tmp_install/

Dockerfile
pg_variables--1.0.sql
pg_variables--1.1.sql
9 changes: 7 additions & 2 deletions Makefile
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -4,10 +4,15 @@ 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--1.1.sql
DATA_built = $(EXTENSION)--$(EXTVERSION).sql
$(EXTENSION)--$(EXTVERSION).sql: init.sql
Copy link
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Это правило нужно переместить в конец Makefile, потому что оно срабатывает по дефолту первым, и сборка ломается.

cat $^ > $@

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 Down
117 changes: 98 additions & 19 deletions README.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -9,7 +9,7 @@
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
Note that the module does **not support transactions and savepoints by default**. For
Copy link
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Я думаю, лучше все предложение целиком выкинуть. Транзакционность просто поддерживается, вот и все.

Copy link
Collaborator Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Если "поддерживается, вот и всё", то она должна по-умолчанию работать. А тут не так.

Copy link
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Не согласен.

Copy link
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Текущая формулировка привлекает внимание к проблеме, которой на самом деле нет.

Copy link
Collaborator Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Хорошо, но мне кажется, что всё равно надо обозначить где-то вначале, что у переменных может быть разное поведение. Может сделать так:
Note that the module can either support and not support transactions and savepoints. For example:
Ну а дальше уже показать на примерах.

Copy link
Collaborator
@funbringer funbringer Apr 19, 2018 •
edited
Loading

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

module can either support and not support transactions

Как раз этот текст меня и смущает. Модуль в любом случае будет поддерживать транзакционность, это пользователь может хотеть или не хотеть использовать данную фичу. Поддержка зависит от кода, а не от настроек.

Я предлагаю написать примерно так:

By default, created variables are not transactional (i.e. they are not affected by begin, commit or rollback statements). This, however, is customizable via the ... options of pgv_set()...

example:

```sql
Expand All @@ -19,13 +19,29 @@ 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**, it does:
```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 Down Expand Up @@ -76,7 +92,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 +101,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 +162,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 +178,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 +192,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 Down Expand Up @@ -239,7 +255,7 @@ You can list packages and variables:

```sql
SELECT * FROM pgv_list() order by package, name;
package | name
package | name
---------+------
vars | int1
vars | int2
Expand All @@ -257,7 +273,7 @@ SELECT * FROM pgv_stats() order by package;
(1 row)
```

You can delete variables or hole packages:
You can delete variables or whole packages:

```sql
SELECT pgv_remove('vars', 'int1');
Expand All @@ -268,3 +284,66 @@ 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` 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 deletions expected/pg_variables.out
View file
Open in desktop
Original file line number Diff line number Diff 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 Down 558 CE16 Expand 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
0