[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    Re: [SQL] short-cutting if sum()>constant
From:       Pavel Stehule <pavel.stehule () gmail ! com>
Date:       2009-12-23 18:53:55
Message-ID: 162867790912231053v964d2eckd79c072c873de138 () mail ! gmail ! com
[Download RAW message or body]

2009/12/23 Ivan Sergio Borgonovo <mail@webthatworks.it>:
> On Wed, 23 Dec 2009 11:36:31 -0500
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> Craig Ringer <craig@postnewspapers.com.au> writes:
>> > Pavel Stehule wrote:
>> >> these queries are executed in some special mode, but still it
>> >> is more expensive than C a = a + 1
>>
>> > ... and may have different rules, so you can't just write a
>> > simple "map expressions to C equivalents" arithmetic evaluator.
>
>> Yeah.   As an example, overflow is supposed to be caught in "a + 1",
>> unlike what would happen in C.
>
>> In principle you could map some of the builtin operators into
>> inline code, but it would be a great deal of work and the results
>> would be un-portable.
>
> Tank you all for being so patient...
> I really miss how actually procedural languages works internally.
>
> doesn't pg routinely map between SQL and C?

generally yes, but not directly.

for example: operator + for type integer is wrapped by function int4pl

http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/int.c?rev=1.86

After parsing and transformations, the expression is transformed to
sequence of call functions like int4pl. PostgreSQL doesn't contains C
compiler - so it cannot compile to target code.

>
> What is the difference between
>
> select a+a from data;
> and
> a := a + a;
> in a plpgsql function?

nothing,

you have to know so plpgsql doesn't see expression a+a; An content of
any expression is invisible for plpgsql parser. PLpgSQL doesn't
understand to expressions. PLpgSQL knows so somewhere have to be
expression, or so somewhere have to boolean expression, but own
expression is black box for plpgsql interpret.

>
> plpgsql knows that a are eg. int so it could just use the same C
> code that it uses when it has to sum a+a in sql.

PLpgSQL knows it. But this knowledge isn't enough. You have to have a
real compiler to machine code. But PostgreSQL hasn't real compiler -
it is only set of some specialised interprets. There are SQL
interpret, there are PLpgSQL interpret. Nothing is translated to
machine code.

>
> My guess since I don't even know what to look for to get an idea of
> the internal working of plpgsql is that the interpreter translate
> the code into SQL (sort of...), it sends it to the parser through
> SPI_execute/prepare etc... (so yeah maybe for the "data" it is not
> really sending "text" representation of data) but still... the
> "code" has to be further interpreted...
>

+/-

plpgsql uses cached plans. So SPI_prepare is called only when
expression is first time evaluated (for session).

> So something like:
> a := a + a;
> turns out to be:
> SPI_prepare("SELECT $1 + $2", 2, ...);
> and this is going to be called for every loop.
> while I thought the SQL engine and plpgsql interpreter were nearer
> so that the interpreter could push directly in the SQL engine the
> values of a.
>
> Am I getting nearer?


there are two steps:

if (cached_plan == NULL)
   cached_plan = prepare("SELECT $1 + $2, info_about_types[])

result = exec(cached_plan, values[], nulls[], &isnull)
....
some_like_move_result_to_variable(address_of_a)

Pavel

>
> --
> Ivan Sergio Borgonovo
> http://www.webthatworks.it
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[prev in list] [next in list] [prev in thread] [next in thread]