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

Commite184663

Browse files
committed
plpgsql's exec_eval_simple_expr() now has to take responsibility for
advancing ActiveSnapshot when we are inside a volatile function.Per example from Gaetano Mendola. Add a regression test to catchsimilar problems in future.
1 parent1553be4 commite184663

File tree

3 files changed

+134
-7
lines changed

3 files changed

+134
-7
lines changed

‎src/pl/plpgsql/src/pl_exec.c

Lines changed: 34 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
* procedural language
44
*
55
* IDENTIFICATION
6-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.124 2004/12/11 23:26:51 tgl Exp $
6+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.125 2004/12/19 20:20:17 tgl Exp $
77
*
88
* This software is copyrighted by Jan Wieck - Hamburg.
99
*
@@ -3548,9 +3548,10 @@ exec_eval_simple_expr(PLpgSQL_execstate *estate,
35483548
Oid*rettype)
35493549
{
35503550
Datumretval;
3551-
ExprContext*econtext;
3551+
ExprContext*volatileecontext;
35523552
ParamListInfoparamLI;
35533553
inti;
3554+
SnapshotsaveActiveSnapshot;
35543555

35553556
/*
35563557
* Pass back previously-determined result type.
@@ -3629,13 +3630,39 @@ exec_eval_simple_expr(PLpgSQL_execstate *estate,
36293630
econtext->ecxt_param_list_info=paramLI;
36303631

36313632
/*
3632-
* Now call the executor to evaluate the expression
3633+
* We have to do some of the things SPI_execute_plan would do,
3634+
* in particular adjust ActiveSnapshot if we are in a non-read-only
3635+
* function. Without this, stable functions within the expression
3636+
* would fail to see updates made so far by our own function.
36333637
*/
36343638
SPI_push();
3635-
retval=ExecEvalExprSwitchContext(expr->expr_simple_state,
3636-
econtext,
3637-
isNull,
3638-
NULL);
3639+
saveActiveSnapshot=ActiveSnapshot;
3640+
3641+
PG_TRY();
3642+
{
3643+
MemoryContextoldcontext;
3644+
3645+
oldcontext=MemoryContextSwitchTo(econtext->ecxt_per_tuple_memory);
3646+
if (!estate->readonly_func)
3647+
ActiveSnapshot=CopySnapshot(GetTransactionSnapshot());
3648+
/*
3649+
* Finally we can call the executor to evaluate the expression
3650+
*/
3651+
retval=ExecEvalExpr(expr->expr_simple_state,
3652+
econtext,
3653+
isNull,
3654+
NULL);
3655+
MemoryContextSwitchTo(oldcontext);
3656+
}
3657+
PG_CATCH();
3658+
{
3659+
/* Restore global vars and propagate error */
3660+
ActiveSnapshot=saveActiveSnapshot;
3661+
PG_RE_THROW();
3662+
}
3663+
PG_END_TRY();
3664+
3665+
ActiveSnapshot=saveActiveSnapshot;
36393666
SPI_pop();
36403667

36413668
/*

‎src/test/regress/expected/plpgsql.out

Lines changed: 59 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2032,3 +2032,62 @@ ERROR: insert or update on table "slave" violates foreign key constraint "slave
20322032
DETAIL: Key (f1)=(2) is not present in table "master".
20332033
drop function trap_foreign_key(int);
20342034
drop function trap_foreign_key_2();
2035+
--
2036+
-- Test proper snapshot handling in simple expressions
2037+
--
2038+
create temp table users(login text, id serial);
2039+
NOTICE: CREATE TABLE will create implicit sequence "users_id_seq" for serial column "users.id"
2040+
create function sp_id_user(a_login text) returns int as $$
2041+
declare x int;
2042+
begin
2043+
select into x id from users where login = a_login;
2044+
if found then return x; end if;
2045+
return 0;
2046+
end$$ language plpgsql stable;
2047+
insert into users values('user1');
2048+
select sp_id_user('user1');
2049+
sp_id_user
2050+
------------
2051+
1
2052+
(1 row)
2053+
2054+
select sp_id_user('userx');
2055+
sp_id_user
2056+
------------
2057+
0
2058+
(1 row)
2059+
2060+
create function sp_add_user(a_login text) returns int as $$
2061+
declare my_id_user int;
2062+
begin
2063+
my_id_user = sp_id_user( a_login );
2064+
IF my_id_user > 0 THEN
2065+
RETURN -1; -- error code for existing user
2066+
END IF;
2067+
INSERT INTO users ( login ) VALUES ( a_login );
2068+
my_id_user = sp_id_user( a_login );
2069+
IF my_id_user = 0 THEN
2070+
RETURN -2; -- error code for insertion failure
2071+
END IF;
2072+
RETURN my_id_user;
2073+
end$$ language plpgsql;
2074+
select sp_add_user('user1');
2075+
sp_add_user
2076+
-------------
2077+
-1
2078+
(1 row)
2079+
2080+
select sp_add_user('user2');
2081+
sp_add_user
2082+
-------------
2083+
2
2084+
(1 row)
2085+
2086+
select sp_add_user('user2');
2087+
sp_add_user
2088+
-------------
2089+
-1
2090+
(1 row)
2091+
2092+
drop function sp_add_user(text);
2093+
drop function sp_id_user(text);

‎src/test/regress/sql/plpgsql.sql

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1764,3 +1764,44 @@ commit;-- still fails
17641764

17651765
dropfunction trap_foreign_key(int);
17661766
dropfunction trap_foreign_key_2();
1767+
1768+
--
1769+
-- Test proper snapshot handling in simple expressions
1770+
--
1771+
1772+
create temp table users(logintext, idserial);
1773+
1774+
createfunctionsp_id_user(a_logintext) returnsintas $$
1775+
declare xint;
1776+
begin
1777+
select into x idfrom userswhere login= a_login;
1778+
if found then return x; end if;
1779+
return0;
1780+
end$$ language plpgsql stable;
1781+
1782+
insert into usersvalues('user1');
1783+
1784+
select sp_id_user('user1');
1785+
select sp_id_user('userx');
1786+
1787+
createfunctionsp_add_user(a_logintext) returnsintas $$
1788+
declare my_id_userint;
1789+
begin
1790+
my_id_user= sp_id_user( a_login );
1791+
IF my_id_user>0 THEN
1792+
RETURN-1;-- error code for existing user
1793+
END IF;
1794+
INSERT INTO users ( login )VALUES ( a_login );
1795+
my_id_user= sp_id_user( a_login );
1796+
IF my_id_user=0 THEN
1797+
RETURN-2;-- error code for insertion failure
1798+
END IF;
1799+
RETURN my_id_user;
1800+
end$$ language plpgsql;
1801+
1802+
select sp_add_user('user1');
1803+
select sp_add_user('user2');
1804+
select sp_add_user('user2');
1805+
1806+
dropfunction sp_add_user(text);
1807+
dropfunction sp_id_user(text);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp