|
1
|
|
|
<?php |
|
2
|
|
|
/** |
|
3
|
|
|
* Test of sending multiple queries in a single pg_query() call. |
|
4
|
|
|
* |
|
5
|
|
|
* The semantics looks like as follows: |
|
6
|
|
|
* - implicit BEGIN is issued before the batch (although, if already intrans, it has no effect) |
|
7
|
|
|
* - implicit BEGIN is issued after each explicit COMMIT or ROLLBACK |
|
8
|
|
|
* - implicit COMMIT is issued after the batch unless there is an explicit BEGIN in this or any of the previous batches with no following explicit COMMIT or ROLLBACK |
|
9
|
|
|
*/ |
|
10
|
|
|
|
|
11
|
|
|
namespace Ivory\Sandpit; |
|
12
|
|
|
|
|
13
|
|
|
$conn = pg_connect('dbname=ivory_scratch user=ivory password=ivory', PGSQL_CONNECT_FORCE_NEW); |
|
14
|
|
|
if ($conn === false) { |
|
15
|
|
|
fprintf(STDERR, "Error connecting to the database\n"); |
|
16
|
|
|
exit(1); |
|
17
|
|
|
} |
|
18
|
|
|
|
|
19
|
|
|
pg_query("CREATE TEMPORARY TABLE tempt (t TEXT); INSERT INTO tempt (t) VALUES ('');"); |
|
20
|
|
|
pg_query("UPDATE tempt SET t = t || 'a'"); |
|
21
|
|
|
printTxStatus($conn); // idle |
|
22
|
|
|
pg_query("UPDATE tempt SET t = t || 'b'; BEGIN; UPDATE tempt SET t = t || 'c'; ROLLBACK; UPDATE tempt SET t = t || 'd'"); // BEGIN has no effect - transaction is already started, ROLLBACK cancels the entire batch |
|
23
|
|
|
|
|
24
|
|
|
printTxStatus($conn); // idle |
|
25
|
|
|
pg_query('BEGIN'); |
|
26
|
|
|
printTxStatus($conn); // intrans |
|
27
|
|
|
pg_query("UPDATE tempt SET t = t || 'e'"); |
|
28
|
|
|
printTxStatus($conn); // intrans |
|
29
|
|
|
pg_query('ROLLBACK'); |
|
30
|
|
|
printTxStatus($conn); // idle |
|
31
|
|
|
pg_query("UPDATE tempt SET t = t || 'f'"); |
|
32
|
|
|
|
|
33
|
|
|
printTxStatus($conn); // idle |
|
34
|
|
|
pg_query("UPDATE tempt SET t = t || 'g'; BEGIN; UPDATE tempt SET t = t || 'h'"); |
|
35
|
|
|
printTxStatus($conn); // intrans |
|
36
|
|
|
pg_query("UPDATE tempt SET t = t || 'i'"); |
|
37
|
|
|
pg_query('ROLLBACK'); |
|
38
|
|
|
printTxStatus($conn); // idle |
|
39
|
|
|
|
|
40
|
|
|
pg_query("UPDATE tempt SET t = t || 'j'"); |
|
41
|
|
|
printTxStatus($conn); // idle |
|
42
|
|
|
pg_query("UPDATE tempt SET t = t || 'k'; BEGIN; UPDATE tempt SET t = t || 'l'; ROLLBACK; UPDATE tempt SET t = t || 'm'"); |
|
43
|
|
|
printTxStatus($conn); // idle |
|
44
|
|
|
pg_query("UPDATE tempt SET t = t || 'n'"); |
|
45
|
|
|
pg_query('ROLLBACK'); // no effect |
|
46
|
|
|
printTxStatus($conn); // idle |
|
47
|
|
|
|
|
48
|
|
|
pg_query("UPDATE tempt SET t = t || 'o'"); |
|
49
|
|
|
pg_query("UPDATE tempt SET t = t || 'p'"); |
|
50
|
|
|
pg_query('ROLLBACK'); // no effect |
|
51
|
|
|
|
|
52
|
|
|
pg_query("UPDATE tempt SET t = t || 'q'"); |
|
53
|
|
|
pg_query("UPDATE tempt SET t = t || 'r'; ROLLBACK; UPDATE tempt SET t = t || 's'; ROLLBACK"); |
|
54
|
|
|
|
|
55
|
|
|
$actual = pg_fetch_result(pg_query('SELECT t FROM tempt'), 0, 0); |
|
56
|
|
|
echo "$actual\n"; // adfjmnopq |
|
57
|
|
|
pg_query('DROP TABLE tempt'); |
|
58
|
|
|
|
|
59
|
|
|
|
|
60
|
|
|
pg_query("CREATE TEMPORARY TABLE tempt (t TEXT); INSERT INTO tempt (t) VALUES (''); UPDATE tempt SET t = t || 'a'; ROLLBACK; UPDATE tempt SET t = t || 'b';"); |
|
61
|
|
|
|
|
62
|
|
|
|
|
63
|
|
|
|
|
64
|
|
|
|
|
65
|
|
|
function printTxStatus($conn) |
|
66
|
|
|
{ |
|
67
|
|
|
$txStat = pg_transaction_status($conn); |
|
68
|
|
|
$txStatuses = [ |
|
69
|
|
|
PGSQL_TRANSACTION_IDLE => 'idle', |
|
70
|
|
|
PGSQL_TRANSACTION_ACTIVE => 'active', |
|
71
|
|
|
PGSQL_TRANSACTION_INTRANS => 'intrans', |
|
72
|
|
|
PGSQL_TRANSACTION_INERROR => 'inerror', |
|
73
|
|
|
PGSQL_TRANSACTION_UNKNOWN => 'unknown', |
|
74
|
|
|
]; |
|
75
|
|
|
echo "transact status: $txStat ({$txStatuses[$txStat]})\n"; |
|
76
|
|
|
} |
|
77
|
|
|
|
ondrej-bouda /
ivory
