Обсуждение: Bug report for 7.0beta1 in 'CREATE FUNCTION...'

Can someone look into this, and followup with Don? :)

====================
From: Don Baccus <dhogaza@pacifier.com>

Slightly less minor bug.  Forward this to the right place, too.

acs=# create function foo() returns integer as '
acs'# begin
acs'# create table bar(i integer);
acs'# return 1;
acs'# end;' language 'plpgsql';
CREATE
acs=# select foo();
pqReadData() -- backend closed the channel unexpectedly.       This probably means the backend terminated abnormally
  before or while processing the request.
 
The connection to the server was lost. Attempting reset: Failed.
!# 


DML statements apparently aren't meant to be supported in plpgsql,
since any I try crash.  This, again, is PG7.0 beta.
=====================



Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 

> -----Original Message-----
> From: owner-pgsql-hackers@postgresql.org
> [mailto:owner-pgsql-hackers@postgresql.org]On Behalf Of The Hermit
> Hacker
> 
> 
> Can someone look into this, and followup with Don? :)
>

Currently utility commands aren't executable in PL/pgSQL.
In short,it's due the lack of implementation of copyObject()
for UtilityStatements.
However,there's another essential problem.

PL/pgSQL caches prepared plans for fucntions at their
first execution time. Though many oids/numbers ... exist
in the cached plans,they are changed by DML statements
and cached plans would become invalid. Currently once
a plan is cached,it stays in TopMemoryContext forever
and would never be removed/changed.

Jan could give more precise comments on this topic.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp
> ====================
> From: Don Baccus <dhogaza@pacifier.com>
> 
> Slightly less minor bug.  Forward this to the right place, too.
> 
> acs=# create function foo() returns integer as '
> acs'# begin
> acs'# create table bar(i integer);
> acs'# return 1;
> acs'# end;' language 'plpgsql';
> CREATE
> acs=# select foo();
> pqReadData() -- backend closed the channel unexpectedly.
>         This probably means the backend terminated abnormally
>         before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.
> !# 
> 
> 
> DML statements apparently aren't meant to be supported in plpgsql,
> since any I try crash.  This, again, is PG7.0 beta.
> =====================
> 
> 
> 
> Marc G. Fournier                   ICQ#7615664               IRC 
> Nick: Scrappy
> Systems Administrator @ hub.org 
> primary: scrappy@hub.org           secondary: 
> scrappy@{freebsd|postgresql}.org 
> 
> 
> ************
> 

[Charset iso-8859-1 unsupported, filtering to ASCII...]
> > -----Original Message-----
> > From: owner-pgsql-hackers@postgresql.org
> > [mailto:owner-pgsql-hackers@postgresql.org]On Behalf Of The Hermit
> > Hacker
> > 
> > 
> > Can someone look into this, and followup with Don? :)
> >
> 
> Currently utility commands aren't executable in PL/pgSQL.
> In short,it's due the lack of implementation of copyObject()
> for UtilityStatements.
> However,there's another essential problem.
> 

Removed from HISTORY file:

-Allow utility statements in plpgsql (Tom)


--  Bruce Momjian                        |  http://www.op.net/~candle pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

> -----Original Message-----
> From: owner-pgsql-hackers@postgreSQL.org
> [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Hiroshi Inoue
> Sent: Wednesday, March 01, 2000 11:06 AM
> To: The Hermit Hacker
> Cc: pgsql-hackers@postgreSQL.org
> Subject: RE: [HACKERS] Bug report for 7.0beta1 in 'CREATE FUNCTION...'
> 
> 
> > -----Original Message-----
> > From: owner-pgsql-hackers@postgresql.org
> > [mailto:owner-pgsql-hackers@postgresql.org]On Behalf Of The Hermit
> > Hacker
> > 
> > 
> > Can someone look into this, and followup with Don? :)
> >
> 
> Currently utility commands aren't executable in PL/pgSQL.
> In short,it's due the lack of implementation of copyObject()
> for UtilityStatements.
> However,there's another essential problem.
> 
> PL/pgSQL caches prepared plans for fucntions at their
> first execution time. Though many oids/numbers ... exist
> in the cached plans,they are changed by DML statements                                  ^^^^^^^^^^^^^
Oops sorry,DDL not DML statement.

> and cached plans would become invalid. Currently once
> a plan is cached,it stays in TopMemoryContext forever
> and would never be removed/changed.
> 

On Wed, 1 Mar 2000, Hiroshi Inoue wrote:

> > -----Original Message-----
> > From: owner-pgsql-hackers@postgresql.org
> > [mailto:owner-pgsql-hackers@postgresql.org]On Behalf Of The Hermit
> > Hacker
> > 
> > 
> > Can someone look into this, and followup with Don? :)
> >
> 
> Currently utility commands aren't executable in PL/pgSQL.
> In short,it's due the lack of implementation of copyObject()
> for UtilityStatements.
> However,there's another essential problem.

Hmm, I see that copyObject() and freeObject() is really problematic 
routines.

> PL/pgSQL caches prepared plans for fucntions at their
> first execution time. Though many oids/numbers ... exist
> in the cached plans,they are changed by DML statements
> and cached plans would become invalid. Currently once
> a plan is cached,it stays in TopMemoryContext forever
> and would never be removed/changed.
.. another TopMemoryContext feeder :-) The solution is
context-per-plan cache and small change in SPI (SPI_freeplan..).

I believe that it (SPI) will fixed in any next release.

                Karel