On Sun, Jul 11, 2004 at 07:23:13PM -0400, Bruce Momjian wrote:
>
> Were are we on deciding how PREPARE in aborted transactions should behave?
Haven't gotten much further than agreeing that current behaviour is
quirky. It does not follow that we agree it's bad. I would say most
of us agree that
Were are we on deciding how PREPARE in aborted transactions should behave?
---
Greg Sabino Mullane wrote:
[ There is text before PGP section. ]
>
[ PGP not available, raw data follows ]
> -BEGIN PGP SIGNED MESSAGE-
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 t
What was the conclusion for PREPARE done inside transactions?
---
Tom Lane wrote:
> "Jeroen T. Vermeulen" <[EMAIL PROTECTED]> writes:
> > I think we're talking at cross purposes here... If the client doesn't use
> > explici
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
> Other exceptions I can think of are FETCH and DEALLOCATE. DEALLOCATE is
> particularly fun -- don't most of the arguments for making PREPARE
> transactional also apply to DEALLOCATE? Is it actually feasible to roll
> back a DEALLOCATE?
That's w
> Well, the proposal of implementing it like holdable cursors means using
> a Materialize node which, if I understand correctly, means taking the
> whole result set and storing it on memory (or disk).
Would it help to hold the lock for a record that is the current cursor position,
iff this record
On Tue, Jul 06, 2004 at 08:45:52AM +1200, Oliver Jowett wrote:
> Buffering *all* the ResultSet data client-side isn't an option --
> cursors are used specifically to handle resultsets that don't fit into
> heap on the client side. And implementing a disk cache or similar a)
> doesn't work if yo
On Tue, Jul 06, 2004 at 08:45:52AM +1200, Oliver Jowett wrote:
> This is a non-starter for JDBC: it has no control over when an
> application decides to access a ResultSet in a way that results in a
> FETCH of new data.
>From what you're telling me, I'm not sure I like JDBC! Why did they com
Jeroen T. Vermeulen wrote:
That makes me wonder why people want to maintain transactionality w.r.t.
nested transactions but not with "outer" ones. Odd!
Yep.
But then the FETCH should still occur before the transaction as far as I'm
concerned. You fetch a batch (if it fails, you terminate) and *tr
On Tue, Jul 06, 2004 at 12:17:50AM +1200, Oliver Jowett wrote:
> 7.4/7.5's behaviour leaves the cursor state unchanged by the rollback:
>
> DECLARE foo CURSOR WITH HOLD FOR SELECT * FROM sometable
>
> BEGIN
>FETCH FORWARD 10 FROM foo -- returns rows 1..10
> ROLLBACK
>
> BEGIN
>FETCH
Jeroen T. Vermeulen wrote:
Well, I'd say DEALLOCATE is implicitly lumped in with PREPARE. But as for
FETCH, are you referring to cursors that live outside transactions (but get
manipulated inside transactions)? Are those implemented yet and if so, how
does FETCH work there?
I'm thinking WITH HOL
On Mon, Jul 05, 2004 at 11:44:08PM +1200, Oliver Jowett wrote:
> > SQL session - temp tables, session variables, database contents
> > Interchange - encoding & representation
> > Protocol - COPY, bind/execute &c.
> > Connection - socket stuff
> >Transactions come into play at the
Jeroen T. Vermeulen wrote:
So perhaps we should be distinguishing several
layers in a session's state, along the lines of:
SQL session - temp tables, session variables, database contents
Interchange - encoding & representation
Protocol - COPY, bind/execute &c.
Connec
On Mon, Jul 05, 2004 at 10:21:26AM +1200, Oliver Jowett wrote:
> It certainly affects the JDBC driver -- the native String representation
> in Java is UTF-16, so the driver transcodes between that and
> client_encoding for parameterized queries and query results involving
> strings.
Oops, ye
Jeroen T. Vermeulen wrote:
On Sun, Jul 04, 2004 at 02:33:53PM +1200, Oliver Jowett wrote:
Consider SET client_encoding then..
Does that really affect most middleware? In my situation for instance,
what goes through the connection either way is "just bytes" to the
middleware. Its interpretatio
On Sun, Jul 04, 2004 at 06:39:46PM -, Greg Sabino Mullane wrote:
> > There's no actual extra parsing involved, as far as I can see, just
> > pattern matching and the extraction of the variables.
>
> That sounds like "parsing" to me. :)
Depends on your definition, I guess. I would say v
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Jeroen T. Vermeulen wrote:
> There's no actual extra parsing involved, as far as I can see, just
> pattern matching and the extraction of the variables.
That sounds like "parsing" to me. :)
[client handling the tracking of PREPARE names]
> In
On Sun, Jul 04, 2004 at 02:33:53PM +1200, Oliver Jowett wrote:
> Consider SET client_encoding then..
Does that really affect most middleware? In my situation for instance,
what goes through the connection either way is "just bytes" to the
middleware. Its interpretation is a client matter. So
Jeroen T. Vermeulen wrote:
Maybe. OTOH I find it simply disturbing (as a matter of aesthetics, you
could say) that the application can be messing around with the protocol
underneath the middleware it's supposed to live on top of--the middleware
that should expect to be in control of the backend be
On Sat, Jul 03, 2004 at 02:59:58PM +1200, Oliver Jowett wrote:
>
> I think you mean "between 7.2 and 7.3".
Ah, OK. I thought PREPARE had been added in 7.4. My apologies.
> Yes. I see PREPARE/EXECUTE as a SQL-statement-level, connection-local
> way of getting control over reuse of plans that
On Sat, Jul 03, 2004 at 08:20:17AM +0530, Abhijit Menon-Sen wrote:
> But for what it's worth, I strongly dislike the later proposal of making
> prepared statements anonymous, and pattern matching the statement text,
> especially if they reintroduce the need to quote query parameters.
Only in cas
On Sat, Jul 03, 2004 at 08:20:17AM +0530, Abhijit Menon-Sen wrote:
> At 2004-06-24 13:13:42 -0400, [EMAIL PROTECTED] wrote:
> >
> > > This is why I proposed originally to keep the non-transactional
> > > behavior for Parse messages, but transactional for SQL PREPARE.
> > > The latter can be said to
"Jeroen T. Vermeulen" <[EMAIL PROTECTED]> writes:
> I guess the question then becomes: should we see the frontend-backend
> protocol as a transport layer underneath, and conceptually separate from,
> the SQL session? Or should the protocol be allowed to shine through in
> the way SQL itself is sup
At 2004-07-03 08:20:17 +0530, [EMAIL PROTECTED] wrote:
>
> I would not object to changing PREPARE to be transactional while
> leaving Parse messages alone.
That is to say, it wouldn't cause any problems for me. But since it does
seem to be a nuisance for Oliver and Merlin (among others), I agree w
Jeroen T. Vermeulen wrote:
On Sat, Jul 03, 2004 at 12:16:50PM +1200, Oliver Jowett wrote:
I stand by my original statement: making no change does not break
compatibility. Please provide an example of PREPARE/EXECUTE use that
works under 7.3/7.4 but does not work with current 7.5.
Whether the
At 2004-06-24 13:13:42 -0400, [EMAIL PROTECTED] wrote:
>
> > This is why I proposed originally to keep the non-transactional
> > behavior for Parse messages, but transactional for SQL PREPARE.
> > The latter can be said to be inside the transaction and should
> > behave like so. I think this lower
On Sat, Jul 03, 2004 at 12:16:50PM +1200, Oliver Jowett wrote:
> I stand by my original statement: making no change does not break
> compatibility. Please provide an example of PREPARE/EXECUTE use that
> works under 7.3/7.4 but does not work with current 7.5.
Whether the transaction from "7.3/
Jeroen T. Vermeulen wrote:
On Sat, Jul 03, 2004 at 11:17:18AM +1200, Oliver Jowett wrote:
So many things are true. But _not_ doing so also breaks compatibility,
so like I said, there are counterexamples.
This is nonsense. Not changing the current behaviour cannot break
compatibility, almost by d
On Sat, Jul 03, 2004 at 11:17:18AM +1200, Oliver Jowett wrote:
> >So many things are true. But _not_ doing so also breaks compatibility,
> >so like I said, there are counterexamples.
>
> This is nonsense. Not changing the current behaviour cannot break
> compatibility, almost by definition.
A
Jeroen T. Vermeulen wrote:
Just don't tell me that making
PREPARE respect rollbacks would break compatibility
Why not? It's true.
So many things are true. But _not_ doing so also breaks compatibility,
so like I said, there are counterexamples.
This is nonsense. Not changing the current behaviour
On Fri, Jul 02, 2004 at 09:51:38PM -, Greg Sabino Mullane wrote:
> Specifically, your proposal (if I read it correctly) would require me
> to send in the full SQL statement every time, thus negating a major
Well, like I said, it wouldn't actually _require_ this; it would just
allow transact
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
> So again, I'd like to hear any comments about my pattern-matching
> proposal. Is there any problem here that it would not solve?
> Does anyone see a problem in implementing them?
Quoting. Extra parsing work.
Specifically, your proposal (if I
On Fri, Jul 02, 2004 at 11:18:44AM -0400, Merlin Moncure wrote:
> Yes, it would. It would actually make my life a lot easier
> (notwithstanding my minor gripe with ExecParams) because I would no
> longer have to deal with all the complexities surrounding prepared
> statements. This is A Good Th
> > Right now, I'm transitioning to ExexPrepared to skip the string
escaping
> > step on the client side. I would hate to lose that ability.
ExecParams
> > is a little more work to set up (doable, though).
>
> OTOH, if you're taking client code queries and replacing them with
> EXECUTEs (which I
On Fri, Jul 02, 2004 at 08:51:05AM -0400, Merlin Moncure wrote:
> Right now, I'm transitioning to ExexPrepared to skip the string escaping
> step on the client side. I would hate to lose that ability. ExecParams
> is a little more work to set up (doable, though).
OTOH, if you're taking client
On Fri, Jul 02, 2004 at 12:30:07PM +1200, Oliver Jowett wrote:
> >If it's that important, come up with a generic "session-not-transaction"
> >syntax to temporarily escape bracketing.
>
> Do you have a proposal for this? It seems to me that if your argument is
> that "if you want the old behavio
> > The big picture here is that with the current behavior, it is
possible
> > to keep track of existence of prepared statements without wrapping
or
> > even being aware of transaction activity. This is tremendously
useful
> > for handling them in a generic way because transactions and prepared
>
Jeroen T. Vermeulen wrote:
If it's that important, come up with a generic "session-not-transaction"
syntax to temporarily escape bracketing.
Do you have a proposal for this? It seems to me that if your argument is
that "if you want the old behaviour, you could add this extension" means
that you n
On Thu, Jul 01, 2004 at 04:06:06PM -0400, Merlin Moncure wrote:
> The big picture here is that with the current behavior, it is possible
> to keep track of existence of prepared statements without wrapping or
> even being aware of transaction activity. This is tremendously useful
> for handling
Jeroen wrote:
> I see how making PREPARE obey rollbacks would be inconvenient for some
> existing code, but frankly I'm getting a bit worried about the "why
should
> I care whether what I do is committed or not?" argument. I guess one
> could
> say that about lots of statements: "I don't really wa
Sorry for the delay, life tends to get complicated if you leave it
alone for a few days...
I see how making PREPARE obey rollbacks would be inconvenient for some
existing code, but frankly I'm getting a bit worried about the "why should
I care whether what I do is committed or not?" argument. I g
On Sat, Jun 26, 2004 at 09:12:33AM -0400, Merlin Moncure wrote:
> > BEGIN;
> > ... do something ... ;
> > SUBBEGIN;
> > EXECUTE ...;
> > -- if it fails:
> > -- SUBABORT;
> > -- PREPARE ...;
> > -- SUBBEGIN;
> > -- EXEC
> > I would be fine with changing the lifetime if an EXECUTE failure did
not
> > abort the current transaction. Then I could simply watch the return
> > code of the statement execution and prepare the statement on
> > demand...from my point of view, this would actually be the most
elegant
> > scen
On Fri, Jun 25, 2004 at 08:54:57AM -0400, Merlin Moncure wrote:
> I would be fine with changing the lifetime if an EXECUTE failure did not
> abort the current transaction. Then I could simply watch the return
> code of the statement execution and prepare the statement on
> demand...from my point
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
> It occurs to me that a lot of the problem would go away if we allowed
> DEALLOCATE of a nonexistent statement to not be an error (seems like
> a NOTICE would be be plenty). Then you could just unconditionally
> DEALLOCATE anything you were abou
Oliver wrote:
> If PREPAREd statements did DEALLOCATE on transaction rollback, the
> driver would have to track the set of statements that were first
> PREPAREd in the current transaction so it can fix the state on the
> driver side if the transaction rolls back. This is a lot of extra
> complexity
Jeroen T. Vermeulen wrote:
There was also the middleware argument--some intermediate software layer
may be in control of bracketing. But in such cases, can you even rely
on two independent transactions executing in the same session? You'd
need to assume that to make the current semantics work in
On Fri, Jun 25, 2004 at 10:02:29AM -0400, Tom Lane wrote:
> It occurs to me that a lot of the problem would go away if we allowed
> DEALLOCATE of a nonexistent statement to not be an error (seems like
> a NOTICE would be be plenty). Then you could just unconditionally
> DEALLOCATE anything you w
"Jeroen T. Vermeulen" <[EMAIL PROTECTED]> writes:
> 1. Add a "soft" bit to prepared-statement plans
> 2. Add rollback bookkeeping for prepared statements, which sets the bit
> 3. Accept identical re-preparations of "soft" statements, clearing the bit
That sounds awfully ugly :-(
It occurs to me t
On Thu, Jun 24, 2004 at 04:19:36PM -0400, Merlin Moncure wrote:
> I am using PostgreSQL as a backend for legacy COBOL applications and
> have written a driver which maps the COBOL I/O statements to SQL
> statements. To save a little bit on parsing time and for various other
> reasons these SQL s
Jeroen wrote:
> Granted, that's probably going to force the issue. I do wonder
though:
> one of the arguments in favour of the current semantics is that the
> problems can be worked around using nested transactions. Then what
were
> people doing before nested transactions, in Tom's scenario where
On Fri, Jun 25, 2004 at 02:00:12AM -, Greg Sabino Mullane wrote:
> I was originally unhappy with the current situation, but now I think
> it is the best. Any changes will also cause a huge further headache
> for driver/application writers, as we already have a released version
> (and probabl
"Greg Sabino Mullane" <[EMAIL PROTECTED]> writes:
> I was originally unhappy with the current situation, but now I think
> it is the best. Any changes will also cause a huge further headache
> for driver/application writers, as we already have a released version
> (and probably at least one more) w
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
> It seems like we are closing in on an agreement that that is what
> should happen.
I was originally unhappy with the current situation, but now I think
it is the best. Any changes will also cause a huge further headache
for driver/application w
> > I disagree. Lots of people use prepared statements for all kinds of
> > different reasons. A large percentage of them do not need or make
use
> > of explicit transactions. Having to continually rebuild the
statement
> > would be a hassle. The caching mechanism also seems like extra work
for
De : mailto:[EMAIL PROTECTED]
Emission : 24/06/2004 18:59:15
> On Thu, Jun 24, 2004 at 05:11:48PM +0200, Cyril VELTER wrote:
> >
> > Just my 2 cents here. I agree with tom that the curent behevior for the v3
> > protocol is the right one. I use "On demand" preparation. The first time a
> > stat
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> This is why I proposed originally to keep the non-transactional behavior
> for Parse messages, but transactional for SQL PREPARE. The latter can
> be said to be inside the transaction and should behave like so. I think
> this lowers the surprise factor
On Thu, Jun 24, 2004 at 05:11:48PM +0200, Cyril VELTER wrote:
>
> Just my 2 cents here. I agree with tom that the curent behevior for the v3
> protocol is the right one.I use "On demand" preparation. The first time a
> statement is needed for a specific connection, it is prepared and the cli
Just my 2 cents here. I agree with tom that the curent behevior for the v3
protocol is the right one. I use "On demand" preparation. The first time a
statement is needed for a specific connection, it is prepared and the client
keep track of that (reusing the prepared statement for
[ all snipped ]
A problem with redefining the lifetime of a PREPARE'd statement
according to if it was defined within an explicitly managed transaction
or not would be with middlewares such as J2EE / EJB containers. The
container / JDBC driver performs most operations within explicitly
managed
"Jeroen T. Vermeulen" <[EMAIL PROTECTED]> writes:
> I think we're talking at cross purposes here... If the client doesn't use
> explicit transactions, as you say is common, then you're obviously not
> defining prepared statements inside explicit transactions either.
This whole discussion seems to
On Thu, Jun 24, 2004 at 08:51:32AM -0400, Merlin Moncure wrote:
> > When I say "within a transaction" as opposed to outside a transaction,
> I
> > mean of course an explicit transaction. If you want a prepared
> statement
> > to last throughout the session, I'd say it stands to reason that you
>
Jeroen T. Vermeulen wrote:
> Well, except prepared statements apparently; I'm not sure why they are
an
> exception.
>
> When I say "within a transaction" as opposed to outside a transaction,
I
> mean of course an explicit transaction. If you want a prepared
statement
> to last throughout the sess
On Wed, Jun 23, 2004 at 03:26:49PM -0400, Tom Lane wrote:
> > Even if the spec doesn't help, I think a statement prepared within a
> > transaction should definitely be deallocated at the end of the transaction.
>
> Uh, you do realize that Postgres does *everything* within a transaction?
Well, ex
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 ho
-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 u
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
"Jeroen T. Vermeulen" <[EMAIL PROTECTED]> writes:
> Even if the spec doesn't help, I think a statement prepared within a
> transaction should definitely be deallocated at the end of the transaction.
Uh, you do realize that Postgres does *everything* within a transaction?
The above proposal would r
> 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: alread
We were discussing prepared statement support for libpqxx just now (Bruce,
Peter Eisentraut & myself are manning the postgres booth at LinuxTag 2004
in Karlsruhe, Germany), when we ran into a problem that came up two months
ago. That discussion follows:
Post by Alvaro Herrera:
> Hackers,
>
> Is
70 matches
Mail list logo
