
> Now, here's a scenario that has us worried:
> 
> BEGIN
>   PREPARE foo AS ...
>   ...			[error]
>   DEALLOCATE foo 	[fails: already aborted by previous error]
> ABORT
> BEGIN
>   PREPARE foo AS ...  	[fails: foo is already defined!]
>   EXECUTE foo		[fails: already aborted by previous error]
> COMMIT			[fails: already aborted by previous
error]

Part of the problem is that PREPARE has no provision to overwrite an
existing plan (CREATE OR REPLACE).  I run into this all the time because
I make heavy use of prepared statements to emulate an ISAM file system.
I have to jump through hoops to keep track of what statements are
already prepared to keep from bouncing the current transaction.

However, at least for me, nested x basically solves this problem.  I'll
just always wrap the prepare statement with a sub-transaction and
commit/rollback as necessary.  This is odd because the rollback does
nothing other than guard the following statements from the prepare
failure to execute.  
So, you do:

BEGIN
  BEGIN
    PREPARE foo AS ...
  COMMIT/ROLLBACK
  ...			[error]
  DEALLOCATE foo 	[fails: already aborted by previous error]
ABORT
BEGIN
  BEGIN
    PREPARE foo AS ...  [fails: foo is already defined!]
  COMMIT/ROLLBACK
  EXECUTE foo		[will now always run if prepare is aborted]
COMMIT			[commit executes]

To me, this is good style and it looks like nested x is going to make
7.5.  I have no opinion on whether rollback should affect
prepare/deallocate.

Merlin

 

Wed, 23 Jun 2004 12:49:15 -0400
"Merlin Moncure" <merlin.moncure@rcsonline.com>
Re: PREPARE and transactions
> Part of the problem is that PREPARE has no provision to overwrite an
> existing plan (CREATE OR REPLACE).  I run into this all the time because
> I make heavy use of prepared statements to emulate an ISAM file system.
> I have to jump through hoops to keep track of what statements are
> already prepared to keep from bouncing the current transaction.

Bruce - TODO?:

* PREPARE OR REPLACE...

This would be an incredibly useful command since there's no way of 
_checking_ in advance that a name is already used as a prepared statement...

Chris


Thu, 24 Jun 2004 09:35:06 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: PREPARE and transactions

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
 
 
Merlin Moncure wrote:
> I have to jump through hoops to keep track of what statements are
> already prepared to keep from bouncing the current transaction.
 
Christopher Kings-Lynne wrote:
> * PREPARE OR REPLACE...
>
> This would be an incredibly useful command since there's no
> way of _checking_ in advance that a name is already used as a
> prepared statement...
 
A check would be nice (and I've asked about it before) but it's
really not a lot of jumping through hoops since each connection has
it's own "namespace" of prepared statements. Since they last until
an explicit deallocate, the simple use of unique names makes it
fairly easy on the application side.
 
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200406232330
 
-----BEGIN PGP SIGNATURE-----
 
iD8DBQFA2krxvJuQZxSWSsgRAhLSAJ9othitQerDlB9+J65rVl3EbRT9+QCeJfzH
vFdWCDLvxU/zkFMLEDjpydU=
=OzCx
-----END PGP SIGNATURE-----



Thu, 24 Jun 2004 03:29:09 -0000
"Greg Sabino Mullane" <greg@turnstep.com>
Re: PREPARE and transactions
Greg Sabino Mullane wrote:
>>* PREPARE OR REPLACE...
>>
>>This would be an incredibly useful command since there's no
>>way of _checking_ in advance that a name is already used as a
>>prepared statement...
> 
>  
> A check would be nice (and I've asked about it before) but it's
> really not a lot of jumping through hoops since each connection has
> it's own "namespace" of prepared statements. Since they last until
> an explicit deallocate, the simple use of unique names makes it
> fairly easy on the application side.

Depends. I've got some report templating code that just replaces some 
parameters and executes sql embedded in the template.

Replacing the parameters is neater if I use prepare/execute, but if the 
sql gets executed again of course I get an error. The only way to know 
if there is a PREPARE is to regexp the sql text - yuck.

Now, you might argue I should make my report code handle prepare 
directly, then I'd know if I'd defined it or not. As it happens, that's 
not the way things stand though.

-- 
   Richard Huxton
   Archonet Ltd

Thu, 24 Jun 2004 08:22:17 +0100
Richard Huxton <dev@archonet.com>
Re: PREPARE and transactions
Christopher Kings-Lynne wrote:
> > Part of the problem is that PREPARE has no provision to overwrite an
> > existing plan (CREATE OR REPLACE).  I run into this all the time because
> > I make heavy use of prepared statements to emulate an ISAM file system.
> > I have to jump through hoops to keep track of what statements are
> > already prepared to keep from bouncing the current transaction.
> 
> Bruce - TODO?:
> 
> * PREPARE OR REPLACE...
> 
> This would be an incredibly useful command since there's no way of 
> _checking_ in advance that a name is already used as a prepared statement...

For me the OR REPLACE is used for cases like CREATE FUNCTION where you
want to presever the oid of the function.  Does OR REPLACE make sense
for functions?  Should you just drop and ignore the error first?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Sat, 10 Jul 2004 23:07:59 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: PREPARE and transactions