Re: ERROR: prepared statement "dbdpg_p13754_3" does not exist

On Fri, Nov 21, 2008 at 01:05:24PM -0800, Chris Travers wrote:
> Running the sample on my system I noticed something that might explain
> the problem.
> 
> If you configure PostgreSQL to log statements, you can see the same thing.
> 
> In the sample provided, PostgreSQL is not asked to prepare a query.
> If we change the call from prepare_cached to prepare it is.

Are you sure?  prepare and prepare_cached are just distinctions at the
DBI level (caches the prepare name by the query string as a key).
Both statements get prepared.  It's just if you use prepare() the
"plan_name" is forgotten between calls.

For example with prepare() and no "baz" missing column errors:
(hope you have a wide screen)

2008-11-21 13:25:48 PST LOG:  duration: 0.654 ms  parse dbdpg_p19034_1: SELECT id, foo, bar FROM test_table WHERE id = $1
2008-11-21 13:25:48 PST LOG:  duration: 0.040 ms  bind dbdpg_p19034_1: SELECT id, foo, bar FROM test_table WHERE id = $1
2008-11-21 13:25:48 PST DETAIL:  parameters: $1 = '2'
2008-11-21 13:25:48 PST LOG:  duration: 0.008 ms  execute dbdpg_p19034_1: SELECT id, foo, bar FROM test_table WHERE id = $1
2008-11-21 13:25:48 PST DETAIL:  parameters: $1 = '2'
2008-11-21 13:25:48 PST LOG:  duration: 0.029 ms  statement: DEALLOCATE dbdpg_p19034_1
2008-11-21 13:25:48 PST LOG:  duration: 0.134 ms  statement: SELECT version()
2008-11-21 13:25:48 PST LOG:  duration: 0.077 ms  parse dbdpg_p19034_2: SELECT id, foo, bar FROM test_table WHERE id = $1
2008-11-21 13:25:48 PST LOG:  duration: 0.022 ms  bind dbdpg_p19034_2: SELECT id, foo, bar FROM test_table WHERE id = $1
2008-11-21 13:25:48 PST DETAIL:  parameters: $1 = '2'
2008-11-21 13:25:48 PST LOG:  duration: 0.005 ms  execute dbdpg_p19034_2: SELECT id, foo, bar FROM test_table WHERE id = $1
2008-11-21 13:25:48 PST DETAIL:  parameters: $1 = '2'
2008-11-21 13:25:48 PST LOG:  duration: 0.022 ms  statement: DEALLOCATE dbdpg_p19034_2

So two plan names were used:  dbdpg_p19034_1, and dbdpg_p19034_2.

Now if I change to prepare_cached() you see that only one plan_name is
used:

2008-11-21 13:28:02 PST LOG:  duration: 0.609 ms  parse dbdpg_p19067_1: SELECT id, foo, bar FROM test_table WHERE id = $1
2008-11-21 13:28:02 PST LOG:  duration: 0.038 ms  bind dbdpg_p19067_1: SELECT id, foo, bar FROM test_table WHERE id = $1
2008-11-21 13:28:02 PST DETAIL:  parameters: $1 = '2'
2008-11-21 13:28:02 PST LOG:  duration: 0.008 ms  execute dbdpg_p19067_1: SELECT id, foo, bar FROM test_table WHERE id = $1
2008-11-21 13:28:02 PST DETAIL:  parameters: $1 = '2'
2008-11-21 13:28:02 PST LOG:  duration: 0.128 ms  statement: SELECT version()
2008-11-21 13:28:02 PST LOG:  duration: 0.028 ms  bind dbdpg_p19067_1: SELECT id, foo, bar FROM test_table WHERE id = $1
2008-11-21 13:28:02 PST DETAIL:  parameters: $1 = '2'
2008-11-21 13:28:02 PST LOG:  duration: 0.005 ms  execute dbdpg_p19067_1: SELECT id, foo, bar FROM test_table WHERE id = $1
2008-11-21 13:28:02 PST DETAIL:  parameters: $1 = '2'
2008-11-21 13:28:02 PST LOG:  duration: 0.030 ms  statement: DEALLOCATE dbdpg_p19067_1


Now, adding back in the "baz" error:

2008-11-21 13:28:51 PST ERROR:  column "baz" does not exist at character 22
2008-11-21 13:28:51 PST STATEMENT:  SELECT id, foo, bar, baz FROM test_table WHERE id = $1
2008-11-21 13:28:51 PST LOG:  duration: 0.210 ms  statement: SELECT version()
2008-11-21 13:28:51 PST ERROR:  prepared statement "dbdpg_p19079_1" does not exist

dbdpg_p19079_1 got cached when it wasn't valid.










-- 
Bill Moseley
[email protected]
Sent from my iMutt