pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jan Wieck <janwieck@Yahoo.com>
To: KuroiNeko <evpopkov@carrier.kiev.ua>
Cc: pgsql-sql@postgresql.org
Subject: Re: Re: Requests for Development
Date: Wed, 15 Nov 2000 05:20:39 -0500 (EST)
Message-ID: <200011151020.FAA01573@jupiter.jw.home> (raw)
In-Reply-To: <3A0BC752.nail1EE1D4VAA@ed.ed>

KuroiNeko wrote:
> > I wonder if there couldn't borrowed some code from Interbase which has
> > full featured stored procedures - at least  it was told to me that it has
> > ...
>
>  Well, I have some hands-on experience  with IB, don't know whether this is
> perfectly relevant, but here goes....
>  Indeed, stored  procedures in  IB can do  what's called  `returning record
> sets' in  this thread. This  is helpfull  when tuples restriction  is based
> upon  condition  that is  not  easy/possible  to  formulate in  SQL  (where
> clause).  On the  other hand,  IB has  two different  ways to  call an  SP:
> execute  procedure  for `singleton'  SPs  and  select for  those  returning
> multiple tuples.
>  However, IB supports  only its own SP language. It's  pretty much complete
> and well thought and implemented, but if you want an SP in PERL, you're out
> of luck.
>  What I'd really  like to see is  `pre-compiled' SPs in PGSQL.  IB has this
> feature (SPs are converted to BLR when DDL statement is executed), not sure
> about PGSQL.  I've noticed  that language-specific errors  in SPs  are only
> reported by  PGSQL when SP is  executed, so I suggest  that interpreter (eg
> for PL/PGSQL) is called each time.

    Not entirely true.

    PL/Tcl  has  "spi_exec" as well as "spi_prepare/spi_execp". A
    function is  only  sourced  into  the  interpreter  once  per
    session  (backend  lifetime) and has a global upvar called GB
    where it could store prepared plans at it's first call. Since
    version  8.0  Tcl  uses  a  bytecode  compiler  and  will not
    interpret the real source text again and again.

    PL/pgSQL parses the entire function body at first  call  (per
    backend).   But  the  SPI querystrings for all the statements
    aren't parsed at that time.  It uses SPI_prepare()  only  for
    expressions and queries that actually get executed, so that a
    huge function that is called only once in a backend, erroring
    out  at  the  first  IF, will not parse most of it's queries.
    This is surely  a  win  for  performance,  but  it  makes  it
    difficult  to  develop.  This  will  change  a  little in the
    future, but I do delay those  changes  because  I  think  the
    changes when tuple sets get supported will be huge anyway and
    complicating the code now wouldn't help.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #





view thread (15+ messages)  latest in thread

Message-ID: <200011151020.FAA01573@jupiter.jw.home>
Permalink:  ../200011151020.FAA01573@jupiter.jw.home/
Also on:    postgresql.org/message-id/200011151020.FAA01573@jupiter.jw.home

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: janwieck@Yahoo.com, evpopkov@carrier.kiev.ua
  Subject: Re: Requests for Development
  In-Reply-To: <200011151020.FAA01573@jupiter.jw.home>
  References: <3A0BC752.nail1EE1D4VAA@ed.ed> <200011151020.FAA01573@jupiter.jw.home>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox