Re: PERSISTANT PREPARE (another point of view)

2008/7/20 Milan Oparnica <milan(dot)opa(at)gmail(dot)com>:
> Pavel wrote:
>
>>
>> try to write prototype and show advantages...
>
> Prototype of what, implementation into Postgre or just efficiency of
> PRESISTANT PREPARE idea ?

really prototype

>
>> ...but I see some disadvatage
>> too. Mainly you have to manage some shared memory space for stored
>> plans. It's not easy task - MySQL develepoers can talk. Implemenation
>> on postgresql is little bit dificult - lot of structures that lives in
>> processed memory have to be moved to shared memory.
>>
>
> Is it solved in MySQL or they've just tried ?

http://www.mysqlperformanceblog.com/2006/08/02/mysql-prepared-statements/

Do not forget to close prepared statements - Many memory leaks
reported in MySQL Server turned out to be prepare statements or
cursors which were forgotten to be closed. Watch Com_stmt_prepare and
Com_stmt_close to see if you're closing all prepared statements. In
newer versions you can also use prepared_stmt_count variable to track
number of open statements diretly. You can also adjust
max_prepared_stmt_count variable which limits how many statements can
be open at the same time to avoid overload.

>
> We could have only PREP STATEMENT definition stored in shared memory
> (probably something like stored procedures), and it could be run in local
> processed memory. We could even assume only fetching data would be used
> through PREP STATEMENTS for start, and later introduce data modification. Is
> there some simplified PG algorithm we could use to understand the amount of
> work needed for introducing such feature to PG?

there is some complications with portability - shared memory is slow
on windows :( but probably there isn't problem save plan into shared
memory. Main difficulties is memory maintaining.
>
>> This feature is nice, but question is - who do write it?
>
> With a little help form PG developers and good documentation perhaps I could
> put some programmers from my team on this job. They are mostly C++
> programmers but we have Delphi and Java if needed.

PostgreSQL is solo C code
http://wiki.postgresql.org/wiki/Development_information

>
>> Actually this problem is solved from outside - with pooling.
>>
>
> I'm very interested to learn more about this solution. Can you please send
> me details or some links where I could research this solution ?

pgpool??

>
>
> Thank you for your reply Pavel.

regards
Pavel
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>