prepared statement functioning range

From: 高健 <luckyjackgao(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: prepared statement functioning range
Date: 2013-06-14 08:49:06
Message-ID: CAL454F2x9yTeEFvHToHTK74ZOyC85R5sk49CVw=VHkPsv=abqQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello everybody:

Sorry for disturbing.

I experience the prepared statement of postgresql via psql and have one
question:

In terminal A:

I prepared:

postgres=# prepare test(int) AS

postgres-# select * from customers c where c.cust_id = $*1*;

PREPARE

postgres=#

Then run:

postgres=# execute test(*3*);

cust_id | cust_name

---------+-----------

*3* | Taylor

(*1* row)

postgres=#

In the terminal A , I can found the statement prepared via
pg_prepared_statements:

postgres=# select * from pg_prepared_statements;

name | statement |
prepare_time | parameter_types | from_sql

------+-------------------------------------------------+-------------------------------+-----------------+----------

test | prepare test(int) AS +|
*2013*-*06*-*14* *15*:*58*:*22.796369*+*08* | {integer} | t

| select * from customers c where c.cust_id = $*1*; |
| |

(*1* row)

postgres=#

But in terminal B, I can't see the above statement:

postgres=# select * from pg_prepared_statements;

name | statement | prepare_time | parameter_types | from_sql

------+-----------+--------------+-----------------+----------

(*0* rows)

postgres=#

Even when I run execute in term B, It failed:

postgres=# execute test(3);

ERROR: prepared statement "test" does not exist

postgres=#

So I can draw a conclusion:

Prepared statement is only for use in the same session at which it has
been executed.

It can not be shared via multiple sessions.

That is, when in some special situations ,

if I have to use mulitple connections between client applicaiton and
postgresql database,

I must consider this point if I want to get benifit from prepared
statements.

So I am now thinking about the reason that prepared statement can not
cross over sessions.

Maybe it is because of MVCC control? So in order to make it simple, the
prepared statement is in one session range?

Thanks!

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Albe Laurenz 2013-06-14 10:21:43 Re: prepared statement functioning range
Previous Message 高健 2013-06-14 05:16:39 Re: Why hash join cost calculation need reduction