Обсуждение: error in simple sql function breaks connection

Dear bug-hunters,

With a nearly current cvs head version I have the following:

psql> SELECT VERSION();
...
PostgreSQL 8.0.0beta2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
3.3.4 (Debian 1:3.3.4-6sarge1)

psql> CREATE TABLE foo(id SERIAL PRIMARY KEY, data TEXT);

psql> CREATE FUNCTION add_data(TEXT) RETURNS boolean LANGUAGE SQL AS
       'BEGIN; INSERT INTO foo(data) VALUES($1); COMMIT; SELECT TRUE;';

psql> SELECT add_data('hello');
FATAL:  EndTransactionBlock: unexpected state BEGIN
CONTEXT:  SQL function "add_data" statement 3
server closed the connection unexpectedly


in log:
Sep  6 18:09:23 sablons postgres[21271]: [4-1] FATAL:  EndTransactionBlock: unexpected state BEGIN
Sep  6 18:09:23 sablons postgres[21271]: [4-2] CONTEXT:  SQL function "add_data" statement 3
Sep  6 18:09:23 sablons postgres[21271]: [5-1] LOG:  disconnection: session time: 0:02:49.02 user=coelho
database=coelhohost=[local] port= 
Sep  6 18:09:23 sablons postgres[21271]: [5-2] CONTEXT:  SQL function "add_data" statement 3

Although the "BEGIN" might not be welcome (well, it was ok, possibly
ignored, with version 7.4), shuting down the connection does not seem to
be the appropriate action anyway.

Have a nice day,

--
Fabien.

Fabien COELHO <coelho@cri.ensmp.fr> writes:
> psql> CREATE FUNCTION add_data(TEXT) RETURNS boolean LANGUAGE SQL AS
>        'BEGIN; INSERT INTO foo(data) VALUES($1); COMMIT; SELECT TRUE;';

> psql> SELECT add_data('hello');
> FATAL:  EndTransactionBlock: unexpected state BEGIN
> CONTEXT:  SQL function "add_data" statement 3
> server closed the connection unexpectedly

> Although the "BEGIN" might not be welcome (well, it was ok, possibly
> ignored, with version 7.4),

I'm surprised the COMMIT didn't dump core on you in older versions.
This could never have worked, because if it actually did try to COMMIT
then the function's execution context would have been yanked out from
underneath it.

Anyway, now it says

regression=# SELECT add_data('hello');
ERROR:  cannot begin/end transactions in SQL functions
CONTEXT:  SQL function "add_data" statement 1

            regards, tom lane