Skip site navigation (1) Skip section navigation (2)

Re: PERSISTANT PREPARE (another point of view)

From: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
To: milan(dot)opa(at)gmail(dot)com
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: PERSISTANT PREPARE (another point of view)
Date: 2008-07-22 06:43:41
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-sql
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

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 ?

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

>> 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 ?


> Thank you for your reply Pavel.

> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:

In response to


pgsql-sql by date

Next:From: Scott MarloweDate: 2008-07-22 07:28:51
Subject: Re: PERSISTANT PREPARE (another point of view)
Previous:From: Craig RingerDate: 2008-07-22 02:31:13
Subject: Re: PERSISTANT PREPARE (another point of view)

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group