Обсуждение: BUG #2945: possibly forgotten SPI_push()/SPI_pop()

The following bug has been logged online:

Bug reference:      2945
Logged by:          Sergiy Vyshnevetskiy
Email address:      serg@vostok.net
PostgreSQL version: 8.2.1
Operating system:   FreeBSD-6 stable
Description:        possibly forgotten SPI_push()/SPI_pop()
Details:

Preparation:

#psql  = serg@[local]:5432 test
create or replace function "ifLow"() returns int immutable strict language
plpgsql as $F$begin return 0; end$F$;
CREATE FUNCTION
#psql  = serg@[local]:5432 test
create or replace function "ifHigh"() returns int immutable strict language
plpgsql as $F$begin return 5; end$F$;
CREATE FUNCTION
#psql  = serg@[local]:5432 test
create domain "D5" as int not null check(value between "ifLow"() and
"ifHigh"());
CREATE DOMAIN
#psql  = serg@[local]:5432 test



This works:

#psql  = serg@[local]:5432 test
create or replace function "D5"(int) returns int immutable strict language
plpgsql as $F$begin if($1<"ifLow"())then return "ifLow"();
elsif($1>"ifHigh"())then return "ifHigh"(); end if; return $1; end$F$;
CREATE FUNCTION
#psql  = serg@[local]:5432 test D5
----
  3
(1 запись)
#psql  = serg@[local]:5432 test
drop function "D5"(int);
DROP FUNCTION
#psql  = serg@[local]:5432 test

This doesn't work:

#psql  = serg@[local]:5432 test
create or replace function "D5"(int) returns "D5" immutable strict language
plpgsql as $F$begin if($1<"ifLow"())then return "ifLow"();
elsif($1>"ifHigh"())then return "ifHigh"(); end if; return $1; end$F$;
CREATE FUNCTION
#psql  = serg@[local]:5432 test
select "D5"(3);
ERROR:  SPI_connect failed: SPI_ERROR_CONNECT
КОНТЕКСТ:  PL/pgSQL function "D5" while casting return value to
function's return type
#psql  = serg@[local]:5432 test


Analysis:

The only difference is return type of "D5" function: int works, "D5"
doesn't.

The error is reported during an attempt to call "ifLow"() at "D5" domain
check evaluation.

Casus belli is calling SPI_connect() when _SPI_curid==-1 and
_SPI_connected==0.

Possible reason is forgotten SPI_push()/SPI_pop() wrapper in domain check
evaluation algorithm.

Hackaround attached.

This hack makes SPI_pusn()/SPI_pop() wrapper optional.

Sergiy Vyshnevetskiy <serg@vostok.net> writes:
> This hack makes SPI_pusn()/SPI_pop() wrapper optional.

We are *certainly* not fixing it like that.

            regards, tom lane

On Tue, 30 Jan 2007, Tom Lane wrote:

> Sergiy Vyshnevetskiy <serg@vostok.net> writes:
>> This hack makes SPI_pusn()/SPI_pop() wrapper optional.
>
> We are *certainly* not fixing it like that.

And you shouln't, unless you are thinking of making SPI_pusn()/SPI_pop()
wrapper optional - a topic for -hackers, not -bugs. That's why I
called it a _HACK_around, not a _WORK_around.

This hack is only for those, who needs a working 8.2.1 right now.

"Sergiy Vyshnevetskiy" <serg@vostok.net> writes:
> Possible reason is forgotten SPI_push()/SPI_pop() wrapper in domain check
> evaluation algorithm.

I think it's really plpgsql's fault, not the domain code.  Try this patch:
http://archives.postgresql.org/pgsql-committers/2007-01/msg00405.php

There might be similar problems elsewhere ...

            regards, tom lane

On Tue, 30 Jan 2007, Tom Lane wrote:

> "Sergiy Vyshnevetskiy" <serg@vostok.net> writes:
>> Possible reason is forgotten SPI_push()/SPI_pop() wrapper in domain check
>> evaluation algorithm.
>
> I think it's really plpgsql's fault, not the domain code.  Try this patch:
> http://archives.postgresql.org/pgsql-committers/2007-01/msg00405.php

Yeap, you nailed the insect.