Re: [Re] Re: [HACKERS] PREPARE and transactions

[Jeroen T. Vermeulen]

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 probably at least one more) with the current behavior. I'd be

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 the
programmer doesn't know where transactions begin?

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 that situation.
What if the middleware does connection pooling, or restores a broken
connection between two transactions?  The latter might happen because of
a timed-out firewall, for instance, when there is a long pause between
two unrelated transactions.

Besides, just the fact that current semantics are completely "out-of-band"
relative to bracketing, I guess it really ought to be any middleware's
responsibility to manage prepared statements.  If the application isn't in
control of transactionality, it seems a little iffy to have it fire off
statements that don't affect database state but can make or break future
transactions.

As for the case where statements are prepared on demand when they are
first executed, wouldn't that be better done in the backend?  It would
save the application this trouble of keeping track of which statements
have been prepared.

Perhaps the real problem is in the SQL syntax...  Imagine a syntax that
doesn't assign a name to a prepared statement, just defines an anonymous
pattern to plan for.  The backend would match against the pattern on the
fly, so introducing prepared statements in a program would involve no
changes apart from the PREPAREs.  Implementations could ignore them if
they cached plans dynamically anyway; they could implement dynamic and
more effective replacement policies for prepared statements, and share
plans between connections.


Jeroen


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [Re] Re: [HACKERS] PREPARE and transactions

[Merlin Moncure]

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 the
> programmer doesn't know where transactions begin?

The trick is that with the current semantics, you don't have to watch
transaction activity, just the prepare statements.  You know if and when
(from the client/driver's point of view) a prepared statement exists
because you created it and don't have to be concerned about the
lifetime.

If you guys change the lifetime, it becomes difficult or impossible to
set a flag on the client which guarantees prepared statement existence.
This means I have to wrap the statement execution with a subtransaction
or run the risk of bouncing a current transaction.  Currently in the
applications I write 70% of all I/O goes through prepared
statements...the reason to do this was to reduce statement turnaround
latency, which is the main driving performance factor in COBOL
applications.

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
scenario.

Merlin

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [HACKERS] PREPARE and transactions

[Jeroen T. Vermeulen]

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 statements are handled as prepared queries.  Each
> COBOL file has a corresponding SQL table in the database and each table
> can have up to 7 prepared statements that the application creates when
> it needs them.  Unless I am misunderstanding things, if you change the
> prepared statement's lifetime, I am forced to prepare a bunch of
> statements all at once instead of when they are needed.  I am prepared
> to do this, however (pun intended).
 
Sounds like a challenging (is that word still fashionable?) job.  Now if
only you weren't dealing with legacy applications, this would be a natural
for stored procedures I guess.  Well okay, maybe you could define stored
procedures on demand but then who would clear them up afterwards...

What if prepared statement semantics were modeled after those of session
variables?  You can change a session variable from within a transaction
and keep the change afterwards, but it does go by transaction rules.

What that leaves us is something that works _almost_ the way things work
now, so your code would work unchanged in the normal case.  The difference
would be that PREPARE would roll back like any other statement.

Taking this one step further, if compatibility with current semantics is
important, I could imagine adding a hack-I admit it's not pretty--that
keeps the statement allocated despite a rollback, but sets a "soft" bit.  
The backend could silently accept identical redefinitions of prepared
statements that have the "soft" bit set.

I think that would be the most compatible way, and probably the easiest 
as well, to turn PREPARE into a regular statement:

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

Deallocation, lookup, execution etc. would not need to change.  There would
still be the risk of "leaking" prepared statements, but that is a problem
of the current semantics anyway.


Jeroen


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] PREPARE and transactions

[Tom Lane]

"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 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 about to PREPARE, if you weren't entirely
sure whether it already existed.

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] PREPARE and transactions

[Jeroen T. Vermeulen]

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 were about to PREPARE, if you weren't entirely
> sure whether it already existed.

That would be an improvement anyway, I think--especially if the backend
could keep deallocated plans around a little longer in case they got
re-prepared soon after.  That way the client can ensure not only that the
statement doesn't exist, but also that it _does_ exist, without incurring
prohibitive cost.  And without going through an "if" construct too.

OTOH the problem then remains that we've got semantically significant work
escaping from transactions, but in all other ways being presented as a
regular bracketed operation.  To me it's a bit like a C function returning
a pointer to one of its own stack variables!


Jeroen


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [HACKERS] Compile failure with SSL

[Tom Lane]

"Dave Page" <[EMAIL PROTECTED]> writes:
> OK, looks like the error below is a Win32 thing. The patch attached
> #ifdef'd out the permissions check on the private key file as it won't
> work under Windows anyway (a similar check in postmaster.c has has
> already been ifdef'd out for the same reason).

Applied.

regards, tom lane

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [Re] Re: [HACKERS] PREPARE and transactions

[Oliver Jowett]

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 that situation.
What if the middleware does connection pooling, or restores a broken
connection between two transactions?  The latter might happen because of
a timed-out firewall, for instance, when there is a long pause between
two unrelated transactions.
The current JDBC driver uses PREPARE/EXECUTE to prepare arbitrary 
queries, requested either directly from the application or via a 
middleware layer. For queries where use of PREPARE/EXECUTE is requested, 
the driver sends a PREPARE/EXECUTE pair on query execution. If the 
PREPARE succeeds, subsequent query executions for the same query send 
only the EXECUTE.

This might all happen either inside or outside a transaction -- the 
mechanics of transaction demarcation are done by the driver, but the 
transaction model used is up to the application and is effectively 
invisible to the driver.

The set of PREPAREd queries is per-connection state, so whatever 
connection pooling etc. logic runs on top of the driver isn't an issue. 
The driver currently stores that state in the statement objects 
allocated by the application -- which are also tied to a particular 
connection -- but there's no reason why the driver couldn't, for 
example, maintain a cache of prepared statements per connection and 
match that against newly requested queries.

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 for no benefit I can see. And it'd get pretty nasty if nested 
transactions were involved..

It's all somewhat moot for the JDBC driver as it's moving to using 
protocol-level Parse/Bind messages instead of PREPARE/EXECUTE statements 
anyway. That said, I would be very unhappy if Parse suddenly became 
transactional to match the behaviour of PREPARE.

-O
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [HACKERS] warning missing

[Gaetano Mendola]

Thomas Hallgren wrote:
Greg,
You don't like Java/C#. I do.
What appear here is that you hate C++.
I'm a C++ developer since long time now, and I can not use JAVA and or C#
just for a couple of reason:
1) Java was supposed to be platform compatible:  in thereality is not really true.
2) I can not use the RAII Idiom, or at least without be a joggler
3) I miss the "const" modifier for methods, and I really can not be sure of what
   happen to my objects when are used around.
Do you want now speak about the missing template feature? Don't say template
are the same of Generics.

Regards
Gaetano Mendola

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [HACKERS] PREPARE and transactions

[Merlin Moncure]

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 for no benefit I can see. And it'd get pretty nasty if
nested
> transactions were involved..
> 
> It's all somewhat moot for the JDBC driver as it's moving to using
> protocol-level Parse/Bind messages instead of PREPARE/EXECUTE
statements
> anyway. That said, I would be very unhappy if Parse suddenly became
> transactional to match the behaviour of PREPARE.

That is precisely my situation.  The more I think about it, granting
prepared statements transactional lifetime would force me to stop using
them, period.  There really is no reasonable way of using transactions
to protect against this that solves the general case.  Not having
parse/bind to fall back on would be a disaster...

Even if I could end up using parse/bind it would be nice to have a
little time to get ready for this.  I would humbly request that the
current behavior be deprecated for one or more released versions.

Merlin

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[HACKERS] xeon processors

[Jaime Casanova]

Hi all,
 
Can anyone tell me if postgresql has problems with xeon processors? 
If so, there is any fix or project of fix it? 
 
Thanx in advance,
 
Jaime Casanova
 Do You Yahoo!?

Todo lo que quieres saber de Estados Unidos, América Latina y el resto del Mundo.
Visíta Yahoo! Noticias.

Re: [HACKERS] warning missing

[Joshua D. Drake]

Hello,
You all are behind... Python is king.
Sincerely,
Joshua D. Drake
Gaetano Mendola wrote:
Thomas Hallgren wrote:
Greg,
You don't like Java/C#. I do.

What appear here is that you hate C++.
I'm a C++ developer since long time now, and I can not use JAVA and or C#
just for a couple of reason:
1) Java was supposed to be platform compatible:  in thereality is not 
really true.
2) I can not use the RAII Idiom, or at least without be a joggler
3) I miss the "const" modifier for methods, and I really can not be sure 
of what
   happen to my objects when are used around.

Do you want now speak about the missing template feature? Don't say 
template
are the same of Generics.


Regards
Gaetano Mendola

---(end of broadcast)---
TIP 8: explain analyze is your friend

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
begin:vcard
fn:Joshua D. Drake
n:Drake;Joshua D.
org:Command Prompt, Inc.
adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA
email;internet:[EMAIL PROTECTED]
title:Consultant
tel;work:503-667-4564
tel;fax:503-210-0034
note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We  provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored  the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl.
x-mozilla-html:FALSE
url:http://www.commandprompt.com/
version:2.1
end:vcard


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] warning missing

[Mike Mascari]

Joshua D. Drake wrote:
Hello,
You all are behind... Python is king.
Just to throw more fuel on the fire. Relvar inheritance is, 
according to Chris Date, one of the two Great Blunders in database 
engineering over the past twenty years.

Multiple Domain Inheritance: Yes
Relation Variable Inheritance: No
I think it'd be a fair statement that Date & Darwen would have the 
relvar inheritance ripped out of PostgreSQL as an experiment gone bad...

Mike Mascari
P.S.: D is the language of the future:
http://www.digitalmars.com/d
Ha!

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [HACKERS] xeon processors

[Joshua D. Drake]

Hello,
I seem to recall that HyperThreading and PostgreSQL != good stuff...
There was a whole bunch of stuff recently on this... google the archives.
Sincerely,
Joshua D. Drake
Jaime Casanova wrote:
Hi all,
 
Can anyone tell me if postgresql has problems with xeon processors?
If so, there is any fix or project of fix it?
 
Thanx in advance,
 
Jaime Casanova

 


*Do You Yahoo!?*
 
Todo lo que quieres saber de Estados Unidos, América Latina y el resto 
del Mundo.
Visíta Yahoo! Noticias 
.

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
begin:vcard
fn:Joshua D. Drake
n:Drake;Joshua D.
org:Command Prompt, Inc.
adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA
email;internet:[EMAIL PROTECTED]
title:Consultant
tel;work:503-667-4564
tel;fax:503-210-0034
note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We  provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored  the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl.
x-mozilla-html:FALSE
url:http://www.commandprompt.com/
version:2.1
end:vcard


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [HACKERS] xeon processors

[Jaime Casanova]

thanx"Joshua D. Drake" <[EMAIL PROTECTED]> wrote:
Hello,I seem to recall that HyperThreading and PostgreSQL != good stuff...There was a whole bunch of stuff recently on this... google the archives.Sincerely,Joshua D. DrakeJaime Casanova wrote:> Hi all,> > Can anyone tell me if postgresql has problems with xeon processors?> If so, there is any fix or project of fix it?> > Thanx in advance,> > Jaime Casanova> > > > > > *Do You Yahoo!?*> > Todo lo que quieres saber de Estados Unidos, América Latina y el resto > del Mundo.> Visíta Yahoo! Noticias > .--
 Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBCPostgresql support, programming shared hosting and dedicated hosting.+1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.comMammoth PostgreSQL Replicator. Integrated Replication for PostgreSQLbegin:vcardfn:Joshua D. Draken:Drake;Joshua D.org:Command Prompt, Inc.adr:;;PO Box 215;Cascade Locks;Oregon;97014;USAemail;internet:[EMAIL PROTECTED]title:Consultanttel;work:503-667-4564tel;fax:503-210-0034note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to
 plPerl.x-mozilla-html:FALSEurl:http://www.commandprompt.com/version:2.1end:vcardDo You Yahoo!?

Todo lo que quieres saber de Estados Unidos, América Latina y el resto del Mundo.
Visíta Yahoo! Noticias.

Re: [HACKERS] PREPARE and transactions

[Greg Sabino Mullane]

-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 about to PREPARE, if you weren't entirely
> sure whether it already existed.
  
This might quell some of the complaints, but I am still looking for a
good example of a case where there is a real problem with the current
system. If you're calling a prepared statement, then you must claim
the responsibility for having created it. And tracking which ones you
have already created is simple in your application, regardless of whether
you are the main application or just middleware. If you create it, you
track it. If an error occurs, you can safely re-use that name. The
error should never occur due to a invalid statement name.
 
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200406252215
-BEGIN PGP SIGNATURE-
 
iD8DBQFA3ODAvJuQZxSWSsgRAun9AKCAy13RU4mJ14J9bihiPVm15kvitACghTKv
GgSrqeg9MRROXEwP+AuLSqM=
=Tq9h
-END PGP SIGNATURE-



---(end of broadcast)---
TIP 8: explain analyze is your friend

[HACKERS] Missing tablespace function

[Christopher Kings-Lynne]

We need has_tablespace_privilege()
http://candle.pha.pa.us/main/writings/pgsql/sgml/functions-misc.html#FUNCTIONS-MISC-ACCESS-TABLE
Chris
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] [COMMITTERS] pgsql-server: Support renaming of tablespaces, and

[Christopher Kings-Lynne]

Support renaming of tablespaces, and changing the owners of
aggregates, conversions, functions, operators, operator classes,
schemas, types, and tablespaces.  Fold the existing implementations
of alter domain owner and alter database owner in with these.
Regression tests weren't appropriate?
Chris
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [Re] Re: [HACKERS] PREPARE and transactions

[Alvaro Herrera]

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 of view, this would actually be the most elegant
> scenario.

BEGIN;
... do something ... ;
SUBBEGIN;
EXECUTE ...;
-- if it fails:
-- SUBABORT;
-- PREPARE ...;
-- SUBBEGIN;
-- EXECUTE ...;
-- can continue as if nothing happened
SUBCOMMIT;
COMMIT;

-- 
Alvaro Herrera ()
"Acepta los honores y aplausos y perderás tu libertad"


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] [COMMITTERS] pgsql-server: Support renaming of tablespaces, and

[Tom Lane]

Christopher Kings-Lynne <[EMAIL PROTECTED]> writes:
> Regression tests weren't appropriate?

Didn't like em ... as you observed, there were parallelization issues,
and in any case I'm not convinced they were testing anything very likely
to break.

regards, tom lane

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [HACKERS] [COMMITTERS] pgsql-server: Support renaming of tablespaces,

[Christopher Kings-Lynne]

Didn't like em ... as you observed, there were parallelization issues,
and in any case I'm not convinced they were testing anything very likely
to break.
Oh well, your call :)
Chris
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] [COMMITTERS] pgsql-server: Support renaming of tablespaces,

[Christopher Kings-Lynne]

Regression tests weren't appropriate?
Didn't like em ... as you observed, there were parallelization issues,
and in any case I'm not convinced they were testing anything very likely
to break.
Either way, I plan to dedicate tomorrow to completing pg_dump support 
for all those new functions, hopefully not too late for freeze.

Chris
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] [PATCHES] nested xacts and phantom Xids

[Alvaro Herrera]

On Sat, Jun 26, 2004 at 12:42:28AM -0400, Alvaro Herrera wrote:

> - discussion whether we want a different syntax for subxacts, like
>   SUBBEGIN/SUBCOMMIT/SUBABORT instead of BEGIN/COMMIT/ABORT.  Please
>   comment on this point.

It has been suggested a couple of times that we should use a different
syntax for subtransactions than for main transactions.  This would for
example allow things like


BEGIN;
do something;
SUBBEGIN;
do something else;
SUBBEGIN;
do more things;
...
COMMIT;

And issue a single COMMIT (or ROLLBACK) to get rid of the whole thing.
(This is suspiciously similar to SAVEPOINTs).  Another nice idea would
be to be able to name subtransactions and rollback to a name, which
would bring savepoints even nearer.  Also maybe a subcommit  would
commit everything within that name (not sure if this is a good idea).

Please comment.

-- 
Alvaro Herrera ()
Este mail se entrega garantizadamente 100% libre de sarcasmo.


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [HACKERS] [PATCHES] nested xacts and phantom Xids

[Dennis Bjorklund]

On Sat, 26 Jun 2004, Alvaro Herrera wrote:

> (This is suspiciously similar to SAVEPOINTs).  Another nice idea would
> be to be able to name subtransactions and rollback to a name, which
> would bring savepoints even nearer.

Sounds exactly like savepoints. What is the difference and why don't we do
savepoints instead?

-- 
/Dennis Björklund


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org