Re: [HACKERS] [BUGS] BUG #1118: Misleading Commit message
Bruce Momjian [EMAIL PROTECTED] writes: Do we want to add this to TODO: * Issue an extra message when COMMIT completes a failed transaction No --- it's (a) wordy and (b) not responsive to the original complaint, which was that a client that examines command completion tags will be easily misled. We should either change the command tags or do nothing. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] User Quota Implementation
this leads me to the first question I asked... do you want me to pull the latest cvs and patch it... or distribute my patch for 7.4.3? Tom Lane wrote: [ catching up on this discussion a bit late... ] Alvaro Herrera [EMAIL PROTECTED] writes: You haven't shown us the patch, have you? That was pretty much the point that leapt out at me. For a change of this magnitude, there is absolutely zero chance that we'll accept an implementation sight unseen. Let's see a proof-of-concept patch... regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Jonah H. Harris, UNIX Administrator | phone: 505.224.4814 Albuquerque TVI | fax: 505.224.3014 525 Buena Vista SE | [EMAIL PROTECTED] Albuquerque, New Mexico 87106| http://w3.tvi.edu/~jharris/ All great truths begin as blasphemies. -- George Bernard Shaw ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nested Transactions, Abort All
On Fri, 9 Jul 2004, Mike Rylander wrote: Nested transactions and savepoints serve two different purposes. They have some overlap, but for the most part solve two distinct problems. Then show some examples that illustrait the difference. So far all examples shown that uses subtransactions could just as well have been written using savepoints. I don't agree that they have two different purposes. I don't think so, especially as there has been some talk of implimenting savepoints as a subset of nested transactions. It is not a subset. It's the other way around. Nested transactions are a subset of savepoints Savepoints have more possibilities, you can invalidate older savepoints then the last (with subtransactions you can only commit/rollback the last). If you don't use that then it's exactly the same as subtransactions. The only feature subtransactions have that savepoints doesn't is the lack of names. Every savepoint have a name. If we want an extension it could be to get the database to generate a fresh savepoint name. The client can of course also generate unique savepoint names if it want. That subtransactions do more than savepoints is just smoke an mirrors. So far there have been no example to validate that point of view, and I don't think there will be any. If anyone know of something that you can do with subtransactions and not with savepoints, please speak up. -- /Dennis Björklund ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Nested Transactions, Abort All
On Fri, 9 Jul 2004, Alvaro Herrera wrote: On Fri, Jul 09, 2004 at 11:28:23PM +0200, Dennis Bjorklund wrote: On Fri, 9 Jul 2004, Alvaro Herrera wrote: Yes, we free some things. Granted it's not a lot, but we have stacks for several things that will be always be growing with savepoints, They will not always be growing for savepoints, you can free things when using savepoints just as with subtransactions. I still don't see when I can release a savepoint's state. You showed a particular case, where we can finish a released savepoint that is the innermost transaction. However, as soon as there is another savepoint set after the released savepoint was set, we can't free the second. I mean this: begin; ... work ...; savepoint foo; ... more work ...; savepoint bar; ... yet more ... ; release foo; At this time I can't release savepoint foo because the implementation (nested) requires me to keep it open as long as savepoint bar exists. If I released bar at a later time, I could close both, but not before. According to ANSI 2003, savepoints should be considered in terms of nesting. That is, the spec talks to nesting levels (4.35.2): An SQL-transaction has one or more savepoint levels, exactly one of which is the current savepoint level. The savepoint levels of an SQL-transaction are nested, such that when a new savepoint level NSL is established, the current savepoint level CSL ceases to be current and NSL becomes current. When NSL is destroyed, CSL becomes current again. And: If a rollback statement references a savepoint SS, then all changes made to SQL-data or schema subsequent to the establishment of the savepoint are canceled, all savepoints established since SS was established are destroyed, and the SQL-transaction is restored to its state as it was immediately following the execution of the savepoint statement. This is also relevant: It is implementation-defined whether or not, or how, a rollback statement that references a savepoint specifier affects diagnostics area contents, the contents of SQL descriptor areas, and the status of prepared statements. So, releasing foo would release bar (16.5): 3) The savepoint identified by S and all savepoints established in the current savepoint level subsequent to the establishment of S are destroyed. Also, the spec makes mention of savepoint behaviour in functions (10.4): 2) If, before the completion of the execution of the SQL routine body of R, an attempt is made to execute an SQL-transaction statement that is not a savepoint statement or a release savepoint statement, or is a rollback statement that does not specify a savepoint clause, then an exception condition is raised: SQL routine exception prohibited SQL-statement attempted. It also states that an SQL-invoked function lives in its own savepoint level (4.27): An SQL-invoked procedure may optionally be specified to require a new savepoint level to be established when it is invoked and destroyed on return from the executed routine body. The alternative of not taking a savepoint can also be directly specified with OLD SAVEPOINT LEVEL. When an SQL-invoked function is invoked a new savepoint level is always established. We do not currently support SQL-invoked procedures (that is, routines executed from SQL with CALL procname, which don't need to return a value and which can accept IN OUT and OUT parameters) so we need only deal with the SQL-invoked function case. So, running back to 10.4: 12) If R is an SQL-invoked function or if R is an SQL-invoked procedure and the descriptor of R includes an indication that a new savepoint level is to be established when R is invoked, then the current savepoint level is destroyed. So, any savepoints created during the function are destroyed. What isn't clearly discussed is what they mean by destroy. That is, the 1) ability to reference the savepoint, or 2) all modifications to SQL-data made since the savepoint was created. I cannot see how it could be (2) can be the case. Section 16.5 discusses release savepoint statement whose function is to 'destroy a savepoint': 3) The savepoint identified by S and all savepoints established in the current savepoint level subsequent to the establishment of S are destroyed. It makes no reference to have any effect like rollback. So, I think that we can only release things once we rollback to a savepoint or once we commit. This is not to say we should follow this implementation. I've dug this up to try and present one (reasonably) consistent perspective on it. Thanks, Gavin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested Transactions, Abort All
On Sat, 10 Jul 2004, Gavin Sherry wrote: 3) The savepoint identified by S and all savepoints established in the current savepoint level subsequent to the establishment of S are destroyed. So the standard savepoints are even more like the subtransactions that alvaro have implemented then I realised before. One can not just release an earlier savepoint and keep a later one. Interesting. -- /Dennis Björklund ---(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: [HACKERS] Nested Transactions, Abort All
Simon Riggs wrote: BEGIN display one screen to user - book the flight INSERT INTO ... INSERT INTO ... UPDATE ... SAVEPOINT display another related screen - book the hotel INSERT INTO DELETE UPDATE UPDATE SAVEPOINT offer confirmation screen COMMIT (or ROLLBACK) No, SAVEPOINT is not some kind of intermediate commit, but a point where a rollback can rollback to. HmmmI'm not sure what you mean by No. The SAVEPOINT is somewhere you can ROLLBACK to, yes - exactly what I'm saying. I've not introduced any concept of intermediate commit... Do you agree that my example is valid Oracle SQL? If you name that SAVEPOINT statements, yes. But the ordering of statements makes the second savepoint useless, because it's after all datamodifying statements; the first three are not covered by a savepoint at all, only the toplevel xaction. That's probably not what you wanted. Regards, Andreas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nested Transactions, Abort All
Bruce Momjian wrote: Interesting Oracle doesn't support RELEASE or savepoint levels: T271, SavepointsOracle supports this feature, except: * Oracle does not support RELEASE SAVEPOINT. Yes, and IMHO it's just some housekeeping stuff, informing the backend that you'll never want to rollback to that savepoint any more. If there are no resources to release internally, a noop. * Oracle does not support savepoint levels. The levels are created implicitely, because a rollback to savepoint1 will rollback *all* work done since then. This effectively makes all subsequent savepoints children of the first. The effect of RELEASE SAVEPOINT would be to restrict the tree depth, by concentrating all released savepoints into their parents. This is not a nested transaction. It is autonomous meaning it can commit independent of the outer transaction: I like that too... in 7.6. Regards, Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] bug in DROP TABLESPACE
The latter is relatively trivial as long as you interpret it as just changing the default TS for tables created later in that schema. (On the other hand, you can fake that with a simple UPDATE to pg_namespace, so I'm not sure it qualifies as a must have.) The former is a real mess since it would require moving some of the system catalogs. (At least the nailed-in-cache ones, but I'm not sure we've yet determined which ones *have* to be in the database's default tablespace.) It'll be fairly entertaining to move pg_class in particular, but I doubt you can build such a thing out of the available spare parts at all. My vote would be to postpone any such patch to 7.6 (or 8.1 as the case may be), because it's a nontrivial addition of functionality and we had no prototype as of July 1. It seems to me that we have mixed up two concepts: the tablespaces that a database is IN and the default tablespaces for any schemas created in that tablespace. This will probably cause trouble further down the track :( Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Weird new time zone
You tell us ... what's your real timezone, and what do you get from pushing up the log level to DEBUG4 during postmaster start? OK, I have log level set to debug4 and australian_timezones set to true. The system time zone is set to WST. Attached is the startup log. I should point out that the Casey Antarctic base is in the Australian Antarctic Territory and it is in the same time zone as Perth, Western Australia for me: http://times.clari.net.au/location.php3/Antarctica/Casey http://times.clari.net.au/location.php3/Australia/Perth So I guess in many ways, PostgreSQL is correct - just...weird... I should also point out that with australian_timezones set to false, it still picks Casey Station in Antarctica. Chris postmaster starting DEBUG: Reject TZ Africa/Algiers: at 1089417600 2004-07-10 01:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Luanda: at 1089417600 2004-07-10 01:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Porto-Novo: at 1089417600 2004-07-10 01:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Gaborone: at 1089417600 2004-07-10 02:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Ouagadougou: at 1089417600 2004-07-10 00:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Bujumbura: at 1089417600 2004-07-10 02:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Douala: at 1089417600 2004-07-10 01:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Bangui: at 1089417600 2004-07-10 01:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Ndjamena: at 1089417600 2004-07-10 01:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Kinshasa: at 1089417600 2004-07-10 01:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Lubumbashi: at 1089417600 2004-07-10 02:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Brazzaville: at 1089417600 2004-07-10 01:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Abidjan: at 1089417600 2004-07-10 00:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Djibouti: at 1089417600 2004-07-10 03:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Cairo: at 1089417600 2004-07-10 03:00:00 dst versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Malabo: at 1089417600 2004-07-10 01:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Asmera: at 1089417600 2004-07-10 03:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Addis_Ababa: at 1089417600 2004-07-10 03:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Libreville: at 1089417600 2004-07-10 01:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Banjul: at 1089417600 2004-07-10 00:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Accra: at 1089417600 2004-07-10 00:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Conakry: at 1089417600 2004-07-10 00:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Bissau: at 1089417600 2004-07-10 00:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Nairobi: at 1089417600 2004-07-10 03:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Maseru: at 1089417600 2004-07-10 02:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Monrovia: at 1089417600 2004-07-10 00:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Tripoli: at 1089417600 2004-07-10 02:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Blantyre: at 1089417600 2004-07-10 02:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Bamako: at 1089417600 2004-07-10 00:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Timbuktu: at 1089417600 2004-07-10 00:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Nouakchott: at 1089417600 2004-07-10 00:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Casablanca: at 1089417600 2004-07-10 00:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/El_Aaiun: at 1089417600 2004-07-10 00:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Maputo: at 1089417600 2004-07-10 02:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Windhoek: at 1089417600 2004-07-10 01:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Niamey: at 1089417600 2004-07-10 01:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Lagos: at 1089417600 2004-07-10 01:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Kigali: at 1089417600 2004-07-10 02:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Sao_Tome: at 1089417600 2004-07-10 00:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Dakar: at 1089417600 2004-07-10 00:00:00 std versus 2004-07-10 08:00:00 std DEBUG: Reject TZ Africa/Freetown: at 1089417600 2004-07-10
Re: [HACKERS] bug in DROP TABLESPACE
Christopher Kings-Lynne wrote: It seems to me that we have mixed up two concepts: the tablespaces that a database is IN and the default tablespaces for any schemas created in that tablespace. This will probably cause trouble further down the track :( As long as we interpret schema and database tablespace just as default for future object creation, hopefully not. This leaves moving objects from one tablespace to another up to us tool suppliers, which seems reasonable. Regards, Andreas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nested Transactions, Abort All
Alvaro Herrera wrote: On Sat, Jul 10, 2004 at 09:46:56AM +1200, Oliver Jowett wrote: Alvaro Herrera wrote: We can't actually release anything (commit the subtransactions), because they may be savepoints established after that point, and they are logically inside the previously established ones. At RELEASE we can't really release -- we just lose the name and thus the opportunity to rollback to it. The 2003 draft claims that RELEASE SAVEPOINT invalidates savepoints subsequent to the RELEASE: (that should read subsequent to the released savepoint) In our case, invalidating a savepoint does not mean we can release its resources. We can only do that if it's the latest defined savepoint. I don't understand why this is true if the invalidation comes from a RELEASE statement. I understand the problems with savepoint name reuse invalidating an earlier savepoint -- we do have to keep the earlier txn open in that case. Say I have: SAVEPOINT s1 -- work 1 SAVEPOINT s2 -- work 2 RELEASE SAVEPOINT s1-- Invalidates s1 and s2 Can't we translate that to: begin subtransaction s1 -- work 1 begin subtransaction s2 -- work 2 commit subtransaction s1 -- and implicitly s2 We don't need to keep subtransaction s2 open -- we will never need to roll it back as the RELEASE of s1 invalidates it. What am I missing? -O ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] User Quota Implementation
Jonah H. Harris [EMAIL PROTECTED] writes: this leads me to the first question I asked... do you want me to pull the latest cvs and patch it... or distribute my patch for 7.4.3? Latest CVS, no question. It would be going into 7.6 (or whatever) T the earliest... -Doug ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] plperl vs. plperlu
while playing with the OSCON CD's, I noticed that the current version of plperl installs the same function handler for both, plperl and plperlu. I was wondering how it implements the important security difference or, in case it is not handled and both are in fact the same, who ignored this IMHO important difference in the naming convention of procedural languages. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] plperl vs. plperlu
If trusted, the function is located inside a perl Safe container, a mechanism designed for just this purpose. Try doing something forbidden inside a trusted function (like opening a file) and you will see the error. cheers andrew Jan Wieck wrote: while playing with the OSCON CD's, I noticed that the current version of plperl installs the same function handler for both, plperl and plperlu. I was wondering how it implements the important security difference or, in case it is not handled and both are in fact the same, who ignored this IMHO important difference in the naming convention of procedural languages. Jan ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Recovery Features
On 7/5/2004 6:16 PM, Simon Riggs wrote: On Mon, 2004-07-05 at 22:30, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: ...While recovering, it is very straightforward to simply ignore every record associated with one (or more) transactions. That gives us the ability to recover all apart from txnid X. Don't even *think* of going there. Hmmm... thinking is important, as are differing viewpoints. I value yours and those of everyone else on this list, hence the post. What will happen when transaction Y comes along and wants to modify or delete a row that was inserted by X? There's no chance of staying consistent. I did point out this downside...a few sentences down. **This is awful because: transactions are isolated from each other, but they also provide changes of state that rely on previous committed transactions. If you change the past, you could well invalidate the future. If you blow away a transaction and a later one depends upon it, then you will have broken the recovery chain and will not be able to recover to present time.** Theoretically, this is a disaster area. Practically, Oracle10g provides similar-ish features... IF ... the recovery process would be primary key based, and IF the database definitions would allow for balance type field handling (the log contains value deltas for balance fields instead of overwriting them), THEN this would be a direction I would be looking into. But as things are, the whole recovery is ctid and binary block based. So you would now leave out the ctid based changes to several tuples because of belonging to said transaction. Later on, an original whole block appears in the WAL and overwrites ... so you get what ... partial transactions into the recoverd DB? ...Nobody is shouting YES, so its a dodo... No way! Best regards, Simon Riggs ---(end of broadcast)--- TIP 8: explain analyze is your friend -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Point in Time Recovery
On 7/6/2004 3:58 PM, Simon Riggs wrote: On Tue, 2004-07-06 at 08:38, Zeugswetter Andreas SB SD wrote: - by time - but the time stamp on each xlog record only specifies to the second, which could easily be 10 or more commits (we hope) Should we use a different datatype than time_t for the commit timestamp, one that offers more fine grained differentiation between checkpoints? Imho seconds is really sufficient. If you know a more precise position you will probably know it from backend log or an xlog sniffer. With those you can easily use the TransactionId way. TransactionID and timestamp is only sufficient if the transactions are selected by their commit order. Especially in read committed mode, consider this execution: xid-1: start xid-2: start xid-2: update field x xid-2: commit xid-1: update field y xid-1: commit In this case, the update done by xid-1 depends on the row created by xid-2. So logically xid-2 precedes xid-1, because it made its changes earlier. So you have to apply the log until you find the commit record of the transaction you want apply last, and then stamp all transactions that where in progress at that time as aborted. Jan OK, thanks. I'll just leave the time_t datatype just the way it is. Best Regards, Simon Riggs ---(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 -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [BUGS] BUG #1118: Misleading Commit message
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Do we want to add this to TODO: * Issue an extra message when COMMIT completes a failed transaction No --- it's (a) wordy and (b) not responsive to the original complaint, which was that a client that examines command completion tags will be easily misled. We should either change the command tags or do nothing. I am not excited about changing the command tag. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] plperl vs. plperlu
On 7/10/2004 9:05 AM, Andrew Dunstan wrote: If trusted, the function is located inside a perl Safe container, a mechanism designed for just this purpose. Try doing something forbidden inside a trusted function (like opening a file) and you will see the error. As if I would know perl :-) But yes, I see that it does it in create_sub() now ... thanks for the clearification. Jan cheers andrew Jan Wieck wrote: while playing with the OSCON CD's, I noticed that the current version of plperl installs the same function handler for both, plperl and plperlu. I was wondering how it implements the important security difference or, in case it is not handled and both are in fact the same, who ignored this IMHO important difference in the naming convention of procedural languages. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nested Transactions, Abort All
On Sat, 10 Jul 2004, Mike Rylander wrote: They do, if only to make particular constructs easier to write. This is an opinion, but for example an EXCEPTION framework for plpgsql would be easier to implement and use if it used the nested transactions rather than savepoint syntax: CREATE FUNCTION blah () RETURNS INT LANGUAGE PLPGSQL AS ' BEGIN BEGIN NESTED; do some work... BEGIN NESTED; do other work... EXCEPTION WHEN SQLSTATE = already_exists THEN do alternate work with its own error checking... END NESTED; EXCEPTION WHEN SQLSTATE = fkey_violation THEN ROLLBACK NESTED; END NESTED; END;'; I realize this can be done with nested savepoints and that is what the spec requires, Lets look at what it can look like: BEGIN SAVEPOINT nested; do some work... SAVEPOINT nested2; do other work... EXCEPTION WHEN SQLSTATE = already_exists THEN ROLLBACK TO SAVEPOINT nested2; do alternate work with its own error checking... RELEASE nested2; EXCEPTION WHEN SQLSTATE = fkey_violation THEN ROLLBACK TO SAVEPOINT nested; RELEASE nested; END; Now, in what way is this more complicated? I'm not 100% sure how the exceptions that you used above work. Do that always rollback the transaction thay are in? In one of the exceptions you did a rollback but not in the other. In my example I added a rollback in the first exception handler. Maybe you forgot it there? In any case. I don't see this as any harder then your example. Savepoints have more possibilities, you can invalidate older savepoints then the last (with subtransactions you can only commit/rollback the last). This implies that savepoints are flat. It won't be that way under the covers, but it does give that impression, and flat savepoint space is definitely suited to a different class of problems than nested transactions. First, my claim above was wrong. As Gavin pointed out in another mail, if one have savepoints p1 and p2 and release p1 then also p2 is released. It's possible to implement both kinds of behaviour using Alvaros work, but the standard demands the simpler one where p2 is also released. Now, about the flatness. Savepoints are not flat. They are sort of flat in a savepoint level. But, for example when you call a function you get a new savepoint level. I actually don't want to call it flat at all. The example above does not overwrite the savepoints nested and nested2 that might exist before the call, since this is a higher savepoint level. I'm not sure exactly what it is that defines a new savepoint level, but a function call does and maybe some other things. BTW, I would imagine that savepoints will be implemented as nested transactions with detachable labels... the label can move from a transaction to one of its descendants, and that outer (sub)transaction will be implicitly COMMITed with its parent. Yes. That's my view as well. Alvaro found it easier to implement nested transactions, he forged ahead and did it. Now, because of good design or simple luck, we should be able to implement savepoints fairly easily. I think the difference between them are so small that it's not a big deal at all. In my view savepoints and nested transactions are almost the same thing. The only difference being that the savepoints have names. Savepoints are nested. You can not have savepoints p1 and then p2 and try to only rollback p1. Then you rollback p2 as well, why. Because they are nested. spec WRT savepoints, we actually get to present a richer interface to the user If it's richer or not is the question. And then one have to compare that to the downside of adding a non standard interface. I don't think it is richer at all, but I'd be happy to change my mind if someone can show an example where nested transactions solve something that you can't just as well solve with savepoints. -- /Dennis Björklund ---(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] User Quota Implementation
Jonah H. Harris [EMAIL PROTECTED] writes: You haven't shown us the patch, have you? this leads me to the first question I asked... do you want me to pull the latest cvs and patch it... or distribute my patch for 7.4.3? Well, we will not be applying any such patch to 7.4.*, so if you want to submit something that's likely to get applied then you'll need to update it to CVS tip. But as long as we're at the feedback stage I'd counsel just showing us what you have for 7.4.*. There's no point in doing more work till you have a good reading on whether it will be accepted. (Also, you probably may as well wait till after 7.6 development starts before trying to update the patch... there will be at least one pgindent run before 7.6, and that is likely to break pending patches...) regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Nested Transactions, Abort All
Dennis Bjorklund [EMAIL PROTECTED] writes: On Fri, 9 Jul 2004, Mike Rylander wrote: Nested transactions and savepoints serve two different purposes. They have some overlap, but for the most part solve two distinct problems. Then show some examples that illustrait the difference. So far all examples shown that uses subtransactions could just as well have been written using savepoints. And vice versa. It's a matter of convenience of notation, and I tend to agree with Mike's comment that each wins in some cases. Savepoints have more possibilities, you can invalidate older savepoints then the last Nonsense. Invalidating an older savepoint must invalidate everything after it as well. The fact that the savepoint syntax allows you to express conceptually-ridiculous operations (like that one) is not a point in its favor IMHO. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nested Transactions, Abort All
On Sat, 10 Jul 2004, Tom Lane wrote: Nonsense. Invalidating an older savepoint must invalidate everything after it as well. The fact that the savepoint syntax allows you to express conceptually-ridiculous operations (like that one) is not a point in its favor IMHO. Luckily the standard was written like that! On the other hand, it's not hard to implement the other behaviour either if that is what one wants (and we don't). It would only forget the name of the earlier savepoint. The corresponding transaction in itself have to stay. -- /Dennis Björklund ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Weird new time zone
Christopher Kings-Lynne [EMAIL PROTECTED] writes: Attached is the startup log. I should point out that the Casey Antarctic base is in the Australian Antarctic Territory and it is in the same time zone as Perth, Western Australia for me: Looking in the data files, Zone Antarctica/Casey 0 - zzz 1969 8:00- WST # Western (Aus) Standard Time Zone Australia/Perth 7:43:24 - LMT 1895 Dec 8:00 Aus WST 1943 Jul 8:00 - WST 1974 Oct lastSun 2:00s 8:00 1:00WST 1975 Mar Sun=1 2:00s 8:00 - WST 1983 Oct lastSun 2:00s 8:00 1:00WST 1984 Mar Sun=1 2:00s 8:00 - WST 1991 Nov 17 2:00s 8:00 1:00WST 1992 Mar Sun=1 2:00s 8:00 - WST which if I recall the notation right says that Casey has never observed DST (it's always GMT+8 == WST) while Perth has observed DST in just three years since WWII. So unless the timezone probing code happens to check those particular years, we have no chance of distinguishing the two zones. Perhaps, rather than just probing a few selected years, we had better check every year since 1970 ... regards, tom lane ---(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: [HACKERS] [BUGS] BUG #1118: Misleading Commit message
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Do we want to add this to TODO: * Issue an extra message when COMMIT completes a failed transaction No --- it's (a) wordy and (b) not responsive to the original complaint, which was that a client that examines command completion tags will be easily misled. We should either change the command tags or do nothing. I am not excited about changing the command tag. I was not either to start with, but the more I think about it, the more I think it would be a good idea. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Memory management with C aggregate
Ian Burrell [EMAIL PROTECTED] writes: We have user-defined aggregates written in C running inside the server. We are running into some memory management issues and wondering what is the best way to solve the problem. The state of the aggregates is a structure with a pointer to allocated memory. The structure and memory are allocated in the PortalMemoryContext and freed in the ffunc. We just discovered that the ffunc function can be called multiple times with the same state on certain queries. The double free causes a crash. AFAICS the ffunc should be called only once per aggregated group. Perhaps your code is confused about the handling of groups? If so, the double free is hardly your only worry --- you'll be computing wrong answers anyway till you fix that. From what I could find, the proper way to handle this is let the memory be freed when the memory context is freed. What is the right memory context to be used to allocate the aggregate state? I'd use the memory context identified by fcinfo-flinfo-fn_mcxt. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [BUGS] BUG #1118: Misleading Commit message
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Do we want to add this to TODO: * Issue an extra message when COMMIT completes a failed transaction No --- it's (a) wordy and (b) not responsive to the original complaint, which was that a client that examines command completion tags will be easily misled. We should either change the command tags or do nothing. I am not excited about changing the command tag. I was not either to start with, but the more I think about it, the more I think it would be a good idea. What tag would we use? ABORT? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Update pg_tables, pg_index views to show tablespace name?
On Wednesday 07 July 2004 08:21, [EMAIL PROTECTED] wrote:
Hi,
while playing around with the tablespace stuff I noticed that on can't see
the tablespace a table is in when querying the pg_tables view.
I think the tablespace a table is in is a valuable information and should
be shown there. This also applies to any ohter related view (like
pg_indexes etc.).
I'll try to make a patch if you'd agree that this information is
missing.
ISTM this would be a good idea.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] bug in DROP TABLESPACE
Andreas Pflug [EMAIL PROTECTED] writes: Christopher Kings-Lynne wrote: It seems to me that we have mixed up two concepts: the tablespaces that a database is IN and the default tablespaces for any schemas created in that tablespace. This will probably cause trouble further down the track :( As long as we interpret schema and database tablespace just as default for future object creation, hopefully not. But his point is that we don't --- the database tablespace is not just a default, it is *the place* where the system catalogs are stored, as well as where anything else with pg_class.reltablespace = 0. I think what we have is fine for 7.5. Later on we could look at separating the two concepts. I'm not entirely sure what to call them however. Also it'd be less than clear just what CREATE DATABASE should do with cases where the values are being changed. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nested Transactions, Abort All
On Sat, Jul 10, 2004 at 06:22:00PM +0200, Dennis Bjorklund wrote: On the other hand, it's not hard to implement the other behaviour either if that is what one wants (and we don't). It would only forget the name of the earlier savepoint. The corresponding transaction in itself have to stay. That's why it's absurd. Why allow an operation which isn't really an operation? -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) God is real, unless declared as int ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Nested Transactions, Abort All
On Fri, Jul 09, 2004 at 08:03:36PM +0100, Simon Riggs wrote: On Fri, 2004-07-09 at 16:47, Alvaro Herrera wrote: So this is another reason why we should use COMMIT to close a nested transaction: it may refer to a transaction that is already closed because the user got confused. Sorry! I wanted to say that we SHOULDN'T use commit to close a nested transaction. Rather we want to use a different command just so the confusion does not close the outer transaction, which would not be what the user wanted to do. Could we put two modes of operation in? i.e. if you use SAVEPOINTs/ROLLBACK TO SAVEPOINT, then you're not allowed to use nested transactions (and vice versa - so they are mutually exclusive)... This may be a good idea. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Always assume the user will do much worse than the stupidest thing you can imagine.(Julien PUYDT) ---(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] Recovery Features
On Sat, 2004-07-10 at 15:04, Jan Wieck wrote: On 7/5/2004 6:16 PM, Simon Riggs wrote: On Mon, 2004-07-05 at 22:30, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: ...While recovering, it is very straightforward to simply ignore every record associated with one (or more) transactions. That gives us the ability to recover all apart from txnid X. Don't even *think* of going there. Hmmm... thinking is important, as are differing viewpoints. I value yours and those of everyone else on this list, hence the post. What will happen when transaction Y comes along and wants to modify or delete a row that was inserted by X? There's no chance of staying consistent. I did point out this downside...a few sentences down. **This is awful because: transactions are isolated from each other, but they also provide changes of state that rely on previous committed transactions. If you change the past, you could well invalidate the future. If you blow away a transaction and a later one depends upon it, then you will have broken the recovery chain and will not be able to recover to present time.** Theoretically, this is a disaster area. Practically, Oracle10g provides similar-ish features... IF ... the recovery process would be primary key based, and IF the database definitions would allow for balance type field handling (the log contains value deltas for balance fields instead of overwriting them), THEN this would be a direction I would be looking into. But as things are, the whole recovery is ctid and binary block based. So you would now leave out the ctid based changes to several tuples because of belonging to said transaction. Later on, an original whole block appears in the WAL and overwrites ... so you get what ... partial transactions into the recoverd DB? ...Nobody is shouting YES, so its a dodo... No way! Sorry...I meant this idea is dead, just like the extinct Dodo bird.- I've been trying to be succinct, but that has led to information loss. ...you've come up with an even better reason why the idea is not good. Many thanks, Best Regards, Simon Riggs ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Weird new time zone
Alvaro Herrera [EMAIL PROTECTED] writes: On Sat, Jul 10, 2004 at 12:40:21PM -0400, Tom Lane wrote: Perhaps, rather than just probing a few selected years, we had better check every year since 1970 ... What if we tell the user what the detected timezone is at some point, and tell them that it's only a heuristic? So if somebody gets a wrong timezone, they can select the correct one in postgresql.conf. That's the ultimate fallback in any case. But it would be nice if it just worked in as many cases as possible. (Or should I revert the hack that made it work for *your* timezone? ;-)) I was initially thinking that probing a large number of test times would be expensive, but on second thought I don't see that it would be a problem. On nearly all the entries in the TZ database, we'd reject on the first or second probe time anyway; only very near matches such as in Chris' example would need multiple checks. We could probably check every Sunday from 1970-1-1 to current time without making any visible difference in the speed of postmaster launch ... and I think we might *have* to do that, if there are any cases where similar zones differ only in the specific dates of DST start/end. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [BUGS] BUG #1118: Misleading Commit message
Bruce Momjian [EMAIL PROTECTED] writes: Do we change tags in any other commands? MOVE, FETCH, EXECUTE ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PgSQL 7.4.2 - NaN on Tru64 UNIX
Both patches withdrawn by author. --- Nikola Milutinovic wrote: Hi guys. I have just had some problems resolved while building PostgreSQL 7.4.2 on Tru64 UNIX 5.1B. File ./src/backend/utils/adt/float.c uses NAN on two spots. It ahs a fall-back definition of NAN as: #define NAN 0.0/0.0 This compiles on Tru64 4.0D (the compiler swallows it), but fails on Tru64 UNIX 5.1B. Both basic CC and DTK Compaq CC break on that file complaining on that constant evaluation. The best way to solve it is to use system definition of Infinity Constants. This definition is best placed in src/include/port/osf1.h This will work on both 5.1B and 4.0D. This is the patch: Papa-Legba:/home/r/root/5.1/postgresql-7.4.2# diff -c src/include/port/osf.h_ORIG src/include/port/osf.h *** src/include/port/osf.h_ORIG Sun May 16 14:14:22 2004 --- src/include/port/osf.h Sun May 16 14:17:57 2004 *** *** 5,7 --- 5,17 /*typedef msemaphore slock_t;*/ #include alpha/builtins.h typedef volatile long slock_t; + + /* + * This is for NaN (Not a Number) definition. + * Note that general definition in PostgreSQL is not type specific, IOW, + * PgSQL's definition can be used in all floating point contexts. The + * definition given here is for double NaN, since all sources using it + * are using it in double float context. + */ + #include nan.h + #define NAN DBL_INFINITY The compilation has went smoothly, I'll try to run regression tests. Will let you know if something fails. Nix. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [BUGS] BUG #1118: Misleading Commit message
Bruce Momjian [EMAIL PROTECTED] writes: I am not excited about changing the command tag. I was not either to start with, but the more I think about it, the more I think it would be a good idea. What tag would we use? ABORT? No, ROLLBACK, which is what you get when you give the expected command. regression=# begin; BEGIN regression=# select 1/0; ERROR: division by zero regression=# abort; -- or rollback; ROLLBACK regression=# begin; BEGIN regression=# select 1/0; ERROR: division by zero regression=# commit; COMMIT I think the above is fairly misleading; it would be better to say ROLLBACK to indicate that we had in fact canceled the transaction. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] [BUGS] BUG #1118: Misleading Commit message
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Do we change tags in any other commands? MOVE, FETCH, EXECUTE ... Ah, yes, I remember we changed EXECUTE recently to return the tag of what we executed. How do we modify MOVE/FETCH tags? I can't remember. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] [BUGS] BUG #1118: Misleading Commit message
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: MOVE, FETCH, EXECUTE ... Ah, yes, I remember we changed EXECUTE recently to return the tag of what we executed. How do we modify MOVE/FETCH tags? I can't remember. I was just looking to see what cases ProcessUtility allowed to change the tag. I think that what the code does is just to append the row count, which you could argue isn't changing the tag. But really, is returning UPDATE 0 vs UPDATE 1 any different conceptually from what we are talking about here? It's still using the tag to pass back info about what the command actually did. regards, tom lane ---(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] Nested Transactions, Abort All
On Sat, 10 Jul 2004, Alvaro Herrera wrote: That's why it's absurd. Why allow an operation which isn't really an operation? Same reason why you allow an addition with 0. One can say that it's not really an operation either. One can have many different semantics, here are 3 versions: 1) You release savepoints in any order 2) You release savepoints in reverse order 3) You release any savepoints and later ones then the one you released are automatically released. I don't see any of these as absurd. The ansi spec uses number 3. It might seem absurd to you, given the implementation you have made. -- /Dennis Björklund ---(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: [HACKERS] Nested Transactions, Abort All
People, Are we perhaps getting away from the issues here? The reason for this discussion was to determine the user-level syntax for Alvaro's nested transactions. We can discuss all we want about how he should have maybe implemented some things differently, but we're supposed to start beta-testing in 5 days and the current tangentalism of this discussion is unlikely to produce such a result. What we really have to determine is one of 4 options regarding the syntax for Alvaro's patch: 1) We adopt one of the two PostgreSQL-specific syntaxes suggested by Alvaro, based on SUBBEGIN or BEGIN NESTED. This would probably be the easiest solution, but adds inconsistency with both the standard and other databases. 2) We adopt the syntax of the other databases to really push Nested Transactions (as opposed to Savepoints), namely MSSQL and SyBase. This would aid thousands of DBAs wishing to migrate to PostgreSQL, but would also mean adopting a logically inconsistent syntax which is even further from the standard than Alvaro's proposal. 3) We adopt a slightly mutated form of the SQL3 SAVEPOINT syntax. This would have the twin benefit of both allowing us to improve our standards compliance and make savepoints completely compliant in the next version, as well as helping those wishing to migrate from Oracle to PostgreSQL (currently our largest source of migrations). Its disadvantage is the subtle differences between Alvaro's patch and the standard, which might not be obvious to users and lead to difficult to locate errors. This option also comes in two flavors: a) we implement savepoint names, troubleshooting the namespace and scoping issues, which would really make this a different feature and delay beta testing, or b) we do anonymous savepoints for now, which more-or-less exactly matches the current behavior of Alvaro's patch, and do complaint, named savepoints in the next version. 4) We hold back this patch until the next version. There is some merit in this, due to the lack of consensus on functionality and Alvaro's dissapointing discovery that we will not be able to use savepoints in functions until next version. However, it would also mean effectively dropping a major feature from 7.5 pretty much because we can't make up our minds, and because nobody gave Alvaro adequate feedback when it was more timely. If you couldn't tell, I favor option 3) b)This syntax would look like: BEGIN TRANSACTION; --begin main do stuff; SAVEPOINT; -- begin nested transaction 1 do more stuff; SAVEPOINT; -- begin nested transaction 2 inside NT 1 do stuff; RELEASE SAVEPOINT; -- commit NT 2 do some more stuff; test conditions: if bad: ROLLBACK TO SAVEPOINT; -- rollback NT1, erasing NT2 in the process if good: RELEASE SAVEPOINT; -- commit NT1 and by implication NT2 do some more stuff tests: if problem: ROLLBACK; -- rollback entire transaction, including NT1 and NT2; if good: COMMIT; -- commit entire transaction, including NT1 and/or NT2 if they were good, excluding them if they were rolled back In other words: SAVEPOINT == BEGIN NESTED RELEASE SAVEPOINT == COMMIT NESTED ROLLBACK TO SAVEPOINT == ROLLBACK NESTED If I'm not mistaken, the above matches the functionality already coded by Alvaro. It begins but does not complete our compliance with SQL3 Savepoint syntax, putting us on the right road but making developers aware that there are some differences between our implementation and the standard. Thus developers would be able to adopt the current syntax now, and the same applications would still run when we complete standards-compliant syntax later. HOWEVER, I do still find one major flaw in Alvaro's implementation that I can't seem to get other people on this list to take seriously, or maybe I'm just not understanding the answers. One-half the point of Savepoints/Nested Transactions is the ability to recover from certain kinds of errors (like duplicate keys) inside a transaction and still commit the transaction after the abort condition has been rolled back. But the ability to detect an abort state *from the SQL command line* (or a database port connection) has not been addressed. I've seen some comments about functions to find an abort state from libpq in the text, but I'm not even clear if this has been coded or is just theoretical. Parsing the output of STDERR is *not* adequate. We need to be able to query whether we are in an abort state, or we make NTs absolutely useless to any client application that has connections which cannot, or do not yet, incorporate new libpq functions, something which could take considerable time
Re: [HACKERS] Nested Transactions, Abort All
On Sat, Jul 10, 2004 at 09:46:00PM +0200, Dennis Bjorklund wrote: One can have many different semantics, here are 3 versions: 1) You release savepoints in any order 2) You release savepoints in reverse order 3) You release any savepoints and later ones then the one you released are automatically released. I don't see any of these as absurd. The ansi spec uses number 3. Ah-ha, now I see what I failed to see previously: all later savepoints are also released! This makes a lot more sense. So release is exactly like commit nested, allowing several levels to be committed. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) La fuerza no está en los medios físicos sino que reside en una voluntad indomable (Gandhi) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [BUGS] BUG #1118: Misleading Commit message
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: MOVE, FETCH, EXECUTE ... Ah, yes, I remember we changed EXECUTE recently to return the tag of what we executed. How do we modify MOVE/FETCH tags? I can't remember. I was just looking to see what cases ProcessUtility allowed to change the tag. I think that what the code does is just to append the row count, which you could argue isn't changing the tag. But really, is returning UPDATE 0 vs UPDATE 1 any different conceptually from what we are talking about here? It's still using the tag to pass back info about what the command actually did. Yes, the count is what I remember changing, and as I remember the goal was to have us return MOVE 0 if you do MOVE 1 at the end of a cursor. We already return counts for INSERT/UPDATE/DELETE, but the racial issue with MOVE was that the count returned might not match the count supplied. Therefore, I don't see MOVE/FETCH as the same issue as ROLLBACK. EXECUTE is closer, and I think new for 7.5, but the interesting part there is that you should always get back something different from EXECUTE, while with COMMIT it would change only when you have an aborted transaction. As I remember, the big issue was how often applications are looking and comparing these tags to take actions. I think we should return ROLLBACK on COMMIT failure and we can see if we get any problem reports during beta. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Recovery Features
On 7/10/2004 3:21 PM, Simon Riggs wrote: On Sat, 2004-07-10 at 15:04, Jan Wieck wrote: ...Nobody is shouting YES, so its a dodo... No way! Sorry...I meant this idea is dead, just like the extinct Dodo bird.- I've been trying to be succinct, but that has led to information loss. I know, preserve the Dodo way of life! or did you mean the one from Alice in Wonderland? I was only trying to shout NO in a slightly more polite way :-) Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Nested Transactions, Abort All
On Sat, 10 Jul 2004, Josh Berkus wrote: In other words: SAVEPOINT == BEGIN NESTED RELEASE SAVEPOINT == COMMIT NESTED ROLLBACK TO SAVEPOINT == ROLLBACK NESTED Here it should be: ROLLBACK TO SAVEPOINT == ROLLBACK NESTED; SAVEPOINT; And just to clearify, this is an extension to the spec that we then have to support for a long time. Adding this now and then replacing it with the standard syntax is not very sexy. If we add this we should support it for ever. -- /Dennis Björklund ---(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: [HACKERS] [BUGS] BUG #1118: Misleading Commit message
Bruce Momjian [EMAIL PROTECTED] writes: As I remember, the big issue was how often applications are looking and comparing these tags to take actions. I think we should return ROLLBACK on COMMIT failure and we can see if we get any problem reports during beta. Good enough; I'll make it happen. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested Transactions, Abort All
Josh Berkus wrote: If you couldn't tell, I favor option 3) b)This syntax would look like: BEGIN TRANSACTION; --begin main do stuff; SAVEPOINT; -- begin nested transaction 1 do more stuff; SAVEPOINT; -- begin nested transaction 2 inside NT 1 do stuff; RELEASE SAVEPOINT; -- commit NT 2 do some more stuff; test conditions: if bad: ROLLBACK TO SAVEPOINT; -- rollback NT1, erasing NT2 in the process if good: RELEASE SAVEPOINT; -- commit NT1 and by implication NT2 do some more stuff tests: if problem: ROLLBACK; -- rollback entire transaction, including NT1 and NT2; if good: COMMIT; -- commit entire transaction, including NT1 and/or NT2 if they were good, excluding them if they were rolled back Well, Oracle doesn't suppor RELEASE, so we are matching the standard but perhaps not allowing easy migration from Oracle. In other words: SAVEPOINT == BEGIN NESTED RELEASE SAVEPOINT == COMMIT NESTED ROLLBACK TO SAVEPOINT == ROLLBACK NESTED If I'm not mistaken, the above matches the functionality already coded by Alvaro. It begins but does not complete our compliance with SQL3 Savepoint syntax, putting us on the right road but making developers aware that there are some differences between our implementation and the standard. Thus developers would be able to adopt the current syntax now, and the same applications would still run when we complete standards-compliant syntax later. HOWEVER, I do still find one major flaw in Alvaro's implementation that I can't seem to get other people on this list to take seriously, or maybe I'm just not understanding the answers. One-half the point of Savepoints/Nested Transactions is the ability to recover from certain kinds of errors (like duplicate keys) inside a transaction and still commit the transaction after the abort condition has been rolled back. But the ability to detect an abort state *from the SQL command line* (or a database port connection) has not been addressed. I've seen some comments about functions to find an abort state from libpq in the text, but I'm not even clear if this has been coded or is just theoretical. Parsing the output of STDERR is *not* adequate. We need to be able to query whether we are in an abort state, or we make NTs absolutely useless to any client application that has connections which cannot, or do not yet, incorporate new libpq functions, something which could take considerable time after the 7.5 release. Do we already have an ability to query the SQLSTATE from the command line? If so, what numbers indicate an abort state, if any? Without this issue being addressed, I will change my opinion and vote for option (4) because clearly the NT patch will not be ready for prime-time. Don't we see the error from libpq PQexec() return value and other interfaces? Are you saying how do we detect a failure from a psql script? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Nested Transactions, Abort All
Dennis Bjorklund wrote: On Sat, 10 Jul 2004, Josh Berkus wrote: In other words: SAVEPOINT == BEGIN NESTED RELEASE SAVEPOINT == COMMIT NESTED ROLLBACK TO SAVEPOINT == ROLLBACK NESTED Here it should be: ROLLBACK TO SAVEPOINT == ROLLBACK NESTED; SAVEPOINT; And just to clearify, this is an extension to the spec that we then have to support for a long time. Adding this now and then replacing it with the standard syntax is not very sexy. If we add this we should support it for ever. Just to clarify, this is to allow rolling back to the same savepoint multiple times. If we named savepoints, the new savepoint would be the same name as the one we just rolled back. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Status report
I am still going through my mailbox, trying to address all the open patches so we can move toward beta. Of course, many of the patches I kept need some adjustment to get applied (e.g. configuration file location) so it is slow going. However, we still have PITR unapplied, autovacuum unapplied, and nested transactions don't have savepoint support, so we are still a while away from beta. I think we should start thinking of beta as August 1 rather than mid-July. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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: [HACKERS] Nested Transactions, Abort All
Dennis, Bruce, Just to clarify, this is to allow rolling back to the same savepoint multiple times. If we named savepoints, the new savepoint would be the same name as the one we just rolled back. Hmmm ... yeah, it would be nice to find a way around this so that we don't have non-standard behavior we have to work around once savepoint names are implemented. Suggestions? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested Transactions, Abort All
Bruce Momjian wrote: Dennis Bjorklund wrote: On Sat, 10 Jul 2004, Josh Berkus wrote: In other words: SAVEPOINT == BEGIN NESTED RELEASE SAVEPOINT == COMMIT NESTED ROLLBACK TO SAVEPOINT == ROLLBACK NESTED Here it should be: ROLLBACK TO SAVEPOINT == ROLLBACK NESTED; SAVEPOINT; And just to clearify, this is an extension to the spec that we then have to support for a long time. Adding this now and then replacing it with the standard syntax is not very sexy. If we add this we should support it for ever. Just to clarify, this is to allow rolling back to the same savepoint multiple times. If we named savepoints, the new savepoint would be the same name as the one we just rolled back. Sorry, I confused folks. I should have corrected this line too: ROLLBACK TO SAVEPOINT == ROLLBACK NESTED; BEGIN NESTED; It is not a non-standard behavior. It is only an implementation detail used internally that allows nested transactions to implement savepoints. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested Transactions, Abort All
On Sat, 10 Jul 2004, Bruce Momjian wrote: SAVEPOINT == BEGIN NESTED RELEASE SAVEPOINT == COMMIT NESTED ROLLBACK TO SAVEPOINT == ROLLBACK NESTED Here it should be: ROLLBACK TO SAVEPOINT == ROLLBACK NESTED; SAVEPOINT; And just to clearify, this is an extension to the spec that we then have to support for a long time. Adding this now and then replacing it with the standard syntax is not very sexy. If we add this we should support it for ever. Sorry, I confused folks. I should have corrected this line too: ROLLBACK TO SAVEPOINT == ROLLBACK NESTED; BEGIN NESTED; Hmm, yes. Correct. Luckily, we already had: SAVEPOINT == BEGIN NESTED so it all worked out :-) It is not a non-standard behavior. It is only an implementation detail used internally that allows nested transactions to implement savepoints. The non-standard part I was talking about was the savepoints without names, and that is what we should support for ever if we introduce them. -- /Dennis Björklund ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nested Transactions, Abort All
I just posted a clarification. It isn't a problem. --- Josh Berkus wrote: Dennis, Bruce, Just to clarify, this is to allow rolling back to the same savepoint multiple times. If we named savepoints, the new savepoint would be the same name as the one we just rolled back. Hmmm ... yeah, it would be nice to find a way around this so that we don't have non-standard behavior we have to work around once savepoint names are implemented. Suggestions? -- Josh Berkus Aglio Database Solutions San Francisco -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Nested Transactions, Abort All
Bruce, Well, Oracle doesn't suppor RELEASE, so we are matching the standard but perhaps not allowing easy migration from Oracle. Well, that's Oracle's problem. Considering the amount of influence they had over the standard, there's no excuse for their syntax. Also, if someone converts and Oracle script which does not do RELEASE, it's still ok with us; they just end up nesting multiple levels and not releasing until the main transaction is committed. Don't we see the error from libpq PQexec() return value and other interfaces? As far as I know, DBD::pg does not at this time; it detects an error but does not return the SQLSTATE, and I'm *sure* that PHP 4 does not. I'm sure there are other interfaces in the same boat. And nobody has answered the question of what SQLSTATE ranges indicate an abort state as opposed to something else -- I get the feeling that this is not at all defined. Are you saying how do we detect a failure from a psql script? Right. There are applications out there: shell scripts, ODBC applications, etc., which are unlikely to *ever* have the ability to read states from libpq. These applications need to have the ability to detect an abort *by query* ala T-SQL (e.g. the @@ERROR system variable), so that they can issue the proper ROLLBACKs. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nested Transactions, Abort All
Josh Berkus wrote: Bruce, Well, Oracle doesn't suppor RELEASE, so we are matching the standard but perhaps not allowing easy migration from Oracle. Well, that's Oracle's problem. Considering the amount of influence they had over the standard, there's no excuse for their syntax. Also, if someone converts and Oracle script which does not do RELEASE, it's still ok with us; they just end up nesting multiple levels and not releasing until the main transaction is committed. OK. Don't we see the error from libpq PQexec() return value and other interfaces? As far as I know, DBD::pg does not at this time; it detects an error but does not return the SQLSTATE, and I'm *sure* that PHP 4 does not. I'm sure there are other interfaces in the same boat. And nobody has answered the question of what SQLSTATE ranges indicate an abort state as opposed to something else -- I get the feeling that this is not at all defined. They have no way of reporting a failed query back to the user? How do people program in those environments? Right now any failed query aborts the transaction so it seems it would be pretty easy. Are you saying how do we detect a failure from a psql script? Right. There are applications out there: shell scripts, ODBC applications, etc., which are unlikely to *ever* have the ability to read states from libpq. These applications need to have the ability to detect an abort *by query* ala T-SQL (e.g. the @@ERROR system variable), so that they can issue the proper ROLLBACKs. Well, that involves either creating a conditional capability in the backend, or in psql, neither of which will happen for 7.5. The best we can do is allow COMMIT NESTED INGORE ERROR (or COMMIT NESTED INGORE ROLLBACK) and just let the script keep going. I am thinking of cases where you want to drop an object you aren't sure exists in a transaction. Anything more complicated like issuing a replacement query will have to wait for 7.6. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] Point in Time Recovery
On Sat, 2004-07-10 at 15:17, Jan Wieck wrote: On 7/6/2004 3:58 PM, Simon Riggs wrote: On Tue, 2004-07-06 at 08:38, Zeugswetter Andreas SB SD wrote: - by time - but the time stamp on each xlog record only specifies to the second, which could easily be 10 or more commits (we hope) Should we use a different datatype than time_t for the commit timestamp, one that offers more fine grained differentiation between checkpoints? Imho seconds is really sufficient. If you know a more precise position you will probably know it from backend log or an xlog sniffer. With those you can easily use the TransactionId way. TransactionID and timestamp is only sufficient if the transactions are selected by their commit order. Especially in read committed mode, consider this execution: xid-1: start xid-2: start xid-2: update field x xid-2: commit xid-1: update field y xid-1: commit In this case, the update done by xid-1 depends on the row created by xid-2. So logically xid-2 precedes xid-1, because it made its changes earlier. So you have to apply the log until you find the commit record of the transaction you want apply last, and then stamp all transactions that where in progress at that time as aborted. Agreed. I've implemented this exactly as you say This turns out to be very easy because: - when looking where to stop we only ever stop at commit or aborts - these are the only records that have timestamps anyway... - any record that isn't specifically committed is not updated in the clog and therefore not visible. The clog starts in indeterminate state, 0 and is then updated to either committed or aborted. Aborted and indeterminate are handled similarly in the current code, to allow for crash recovery - PITR doesn't change anything there. So, PITR doesn't do anything that crash recovery doen't already do. Crash recovery makes no attempt to keep track of in-progress transactions and doesn't make a special journey to the clog to specifically mark them as aborted - they just are by default. So, what we mean by stop at a transactionId is stop applying redo at the commit/abort record for that transactionId. It has to be an exact match, not a greater than, for exactly the reason you mention. That means that although we stop at the commit record of transactionId X, we may also have applied records for transactions with later transactionIds e.g. X+1, X+2...etc (without limit or restriction). (I'll even admit that as first, I did think we could get away with the less than test that you are warning me about. Overall, I've spent more time on theory/analysis than on coding, on the idea that you can improve poor code, but wrong code just needs to be thrown away). Timestamps are more vague...When time is used, there might easily be 10+ transactions whose commit/abort records have identical timestamp values. So we either stop at the first or last record depending upon whether we specified inclusive or exclusive on the recovery target value. The hard bit, IMHO, is what we do with the part of the log that we have chosen not to applywhich has been discussed on list in detail also. Thanks for keeping an eye out for possible errors - this one is something I'd thought through and catered for (there are comments in my current latest published code to that effect, although I have not yet finished coding the clean-up-after-stopping part). This implies nothing with regard to other possible errors or oversights and so I very much welcome any questioning of this nature - I am as prone to error as the next man. It's important we get this right. Best regards, Simon Riggs ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nested Transactions, Abort All
Bruce, They have no way of reporting a failed query back to the user? How do people program in those environments? Right now any failed query aborts the transaction so it seems it would be pretty easy. Believe it or not, PHP4 doesn't. This is one of the reasons why coders in other languages don't consider PHP a real programming language; the lack of exception handling. However, given this limitation we can't really use NTs in PHP4 anyway, so it's sort of a moot point. Sorry for bringing it up. To my current knowledge (and hopefully Andrew will speak up if I'm wrong) the DBD::Pg driver reports back a query exception, but not the SQLSTATE. This means that we can detect an abort (assuming all exceptions are aborts) but not what caused the abort, except by parsing the error message for text -- a hazardous approach at best. But you would be right to point out that this is a problem with the DBD::Pg driver. There are, however, other client applications where the problem is more ingrained. I've done an application for ColdFusion recently, and discovered that CF is completely unable to detect even the limited error-reporting capability of ODBC. This means that if CF can't query it, it doesn't exist. Well, that involves either creating a conditional capability in the backend, or in psql, neither of which will happen for 7.5. The best we can do is allow COMMIT NESTED INGORE ERROR (or COMMIT NESTED INGORE ROLLBACK) and just let the script keep going. I am thinking of cases where you want to drop an object you aren't sure exists in a transaction. Anything more complicated like issuing a replacement query will have to wait for 7.6. OK, I didn't realize that it was a difficult thing. I think it should go on the TODO list but you are the judge of what's a quick fix and what's not. (BTW, aside from this limitation, I am *not* in favor of COMMIT NESTED IGNORE ERROR) -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested Transactions, Abort All
Dennis, The non-standard part I was talking about was the savepoints without names, and that is what we should support for ever if we introduce them. I don't have a problem with that idea.Anonymous Savepoints should be easy to support if we are supporting Named (spec) Savepoints. And the two should even integrate easily -- a *lot* more easily than Savepoints and Nested Xacts with a different syntax would. And, it's also a convenient shortcut for the most common case -- transactions with 1 level of nesting and only a couple of non-overlapping savepoints. Of course, if Alvaro can knock out Named Savepoints in a week, then sure, let's go for it. But I've not heard him saying he can. However, this does bring up an important issue; if we implement anonymous savepoints, then should the current implementation accept savepoint names and just ignore them?If not, it makes porting and coding for the spec much more difficult; if so, ported applications could develop subtle erroneous behaviour through wrong rollbacks. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nested Transactions, Abort All
Josh Berkus wrote: Dennis, The non-standard part I was talking about was the savepoints without names, and that is what we should support for ever if we introduce them. I don't have a problem with that idea.Anonymous Savepoints should be easy to support if we are supporting Named (spec) Savepoints. And the two should even integrate easily -- a *lot* more easily than Savepoints and Nested Xacts with a different syntax would. And, it's also a convenient shortcut for the most common case -- transactions with 1 level of nesting and only a couple of non-overlapping savepoints. Of course, if Alvaro can knock out Named Savepoints in a week, then sure, let's go for it. But I've not heard him saying he can. It seems anonymous savepoints really don't buy us anything. They don't match the Oracle behavior, and don't do anything more than nested transactions. I agree we want them, but I don't see the value they add value right now. However, this does bring up an important issue; if we implement anonymous savepoints, then should the current implementation accept savepoint names and just ignore them?If not, it makes porting and coding for the spec much more difficult; if so, ported applications could develop subtle erroneous behaviour through wrong rollbacks. I don't see how we can ignore the savepoint names without having our code work unpredicatably. We could check for the most recent savepoint name and error out if they reference any other name than the most recent savepoint. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nested Transactions, Abort All
Josh Berkus wrote: Bruce, They have no way of reporting a failed query back to the user? How do people program in those environments? Right now any failed query aborts the transaction so it seems it would be pretty easy. Believe it or not, PHP4 doesn't. This is one of the reasons why coders in other languages don't consider PHP a real programming language; the lack of exception handling. However, given this limitation we can't really use NTs in PHP4 anyway, so it's sort of a moot point. Sorry for bringing it up. To my current knowledge (and hopefully Andrew will speak up if I'm wrong) the DBD::Pg driver reports back a query exception, but not the SQLSTATE. This means that we can detect an abort (assuming all exceptions are aborts) but not what caused the abort, except by parsing the error message for text -- a hazardous approach at best. But you would be right to point out that this is a problem with the DBD::Pg driver. There are, however, other client applications where the problem is more ingrained. I've done an application for ColdFusion recently, and discovered that CF is completely unable to detect even the limited error-reporting capability of ODBC. This means that if CF can't query it, it doesn't exist. Well, I don't think we need exception handling to support failed transactions. Don't these function calls return some failure result code? Well, that involves either creating a conditional capability in the backend, or in psql, neither of which will happen for 7.5. The best we can do is allow COMMIT NESTED INGORE ERROR (or COMMIT NESTED INGORE ROLLBACK) and just let the script keep going. I am thinking of cases where you want to drop an object you aren't sure exists in a transaction. Anything more complicated like issuing a replacement query will have to wait for 7.6. OK, I didn't realize that it was a difficult thing. I think it should go on the TODO list but you are the judge of what's a quick fix and what's not. Adding something to psql or the backend like IF (ERROR) ... would be a big job, I would think. (BTW, aside from this limitation, I am *not* in favor of COMMIT NESTED IGNORE ERROR) The syntax was for support of script languages that don't have conditional constructs, like psql scripts, where you want the subxact to commit but if it fails, you don't want that to affect the outer transaction. Are you saying there are very few cases where you don't care if the subxact commits or aborts? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Nested Transactions, Abort All
Josh Berkus wrote: But the ability to detect an abort state *from the SQL command line* (or a database port connection) has not been addressed. This has existed since 7.4. If some interfaces don't expose it, fix those interfaces. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Nested Transactions, Abort All
Bruce Momjian wrote: The syntax was for support of script languages that don't have conditional constructs, like psql scripts, where you want the subxact to commit but if it fails, you don't want that to affect the outer transaction. Are you saying there are very few cases where you don't care if the subxact commits or aborts? Trying to enable nested transaction on something that has no conditionals seems strange to me. If you're writing an app so complicated you so you need NTs, you'd probably not code is as psql script. BTW, do we have real world examples of apps that are waiting to be ported to pgsql, needing nested transactions? Looking at the coding constructions used in those apps could help deciding what semantics would help them. Compiere comes to my mind, being oracle now, so they'd probably prefer named savepoints. Regards, Andreas ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Nested Transactions, Abort All
Bruce Momjian wrote: It seems anonymous savepoints really don't buy us anything. They don't match the Oracle behavior, and don't do anything more than nested transactions. I agree we want them, but I don't see the value they add value right now. The value they add is that they follow the SQL standard, which is a lot better sell than proprietary transaction management scheme. Those people who think they can redefine the SQL standard for purely aesthetic reasons have paid the price over and over again. ---(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] Nested Transactions, Abort All
Bruce, It seems anonymous savepoints really don't buy us anything. They don't match the Oracle behavior, and don't do anything more than nested transactions. I agree we want them, but I don't see the value they add value right now. Anonymous Savepoints == Nested Transactions This issue is whether we're going to use a PostgreSQL-specific, non-standard, syntax for NTs, or use a syntax that puts us on the road to implementing spec-compliant savepoints. Given that the functionality is exactly the same in either case, I don't see why you would want to implement syntax which is 100% Postgres-specific. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nested Transactions, Abort All
Josh Berkus wrote: 3) We adopt a slightly mutated form of the SQL3 SAVEPOINT syntax. This would have the twin benefit of both allowing us to improve our standards compliance and make savepoints completely compliant in the next version, as well as helping those wishing to migrate from Oracle to PostgreSQL (currently our largest source of migrations). Its disadvantage is the subtle differences between Alvaro's patch and the standard, which might not be obvious to users and lead to difficult to locate errors. This option also comes in two flavors: a) we implement savepoint names, troubleshooting the namespace and scoping issues, which would really make this a different feature and delay beta testing, or b) we do anonymous savepoints for now, which more-or-less exactly matches the current behavior of Alvaro's patch, and do complaint, named savepoints in the next version. As Dennis has said, whatever we do now we should support for ever. If we end up with compliant SAVEPOINT (eventually in 7.6+) plus some nonstandard syntax (from 7.5), what is the nonstandard syntax you would prefer to see? I'd prefer a syntax that reflects the primitives actually in use i.e. BEGIN NESTED. [...] In other words: SAVEPOINT == BEGIN NESTED RELEASE SAVEPOINT == COMMIT NESTED ROLLBACK TO SAVEPOINT == ROLLBACK NESTED As pointed out by others ROLLBACK TO SAVEPOINT is actually ROLLBACK NESTED + BEGIN NESTED. This means that if we only have savepoint syntax, there is no way to do a plain rollback of a nested transaction (you have to ROLLBACK TO SAVEPOINT foo; RELEASE SAVEPOINT foo which I find pretty ugly and nonobvious, and it gives you an extra empty subtransaction) But the ability to detect an abort state *from the SQL command line* (or a database port connection) has not been addressed. There is a transaction state indicator in the V3 protocol's ReadyForQuery message. One of the states is in aborted transaction. It's been around since 7.4. Whatever logic is needed for running different/conditional SQL based on transaction state then belongs on the client side, IMO. -O ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [BUGS] BUG #1118: Misleading Commit message
Bruce Momjian wrote: elein wrote: FYI: I'm agreeing w/Tom who is agreeing with me. The tag change should be good. I do hope people are not relying on seeing COMMIT when the transaction rolled back. It does not seem that in this case they would. If it is a problem, hopefully we will hear about it during beta. I will mention is as a backward-compatibility issue in the release notes. The JDBC driver when talking the V2 protocol (unusual for a recent server, but possible) looks for either COMMIT or ROLLBACK when looking for end-of-transaction, so it should be fine. -O ---(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] Weird new time zone
I wrote: I was initially thinking that probing a large number of test times would be expensive, but on second thought I don't see that it would be a problem. On nearly all the entries in the TZ database, we'd reject on the first or second probe time anyway; only very near matches such as in Chris' example would need multiple checks. I've committed changes that replace the original spot check approach with consistently checking every week from 1970 to 2004. This is indeed not much slower than before (the worst difference I saw was from 7 to 14 milliseconds required for identify_system_timezone to process the same case, and I think some of that was measurement noise). This fixes Chris' Australia/Perth problem at least. It occurs to me that with a check this thorough, we might be able to finesse the problem on Windows with the system returning very nonstandard timezone abbreviations. That is, we might simply #ifndef WIN32 the matching of zone names in try_timezone(). However I do not know whether this would yield reasonable results for all the zones supported by Windows. Does anyone want to try it? regards, tom lane ---(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] Nested Transactions, Abort All
Peter Eisentraut wrote: Bruce Momjian wrote: It seems anonymous savepoints really don't buy us anything. They don't match the Oracle behavior, and don't do anything more than nested transactions. I agree we want them, but I don't see the value they add value right now. The value they add is that they follow the SQL standard, which is a lot better sell than proprietary transaction management scheme. Those people who think they can redefine the SQL standard for purely aesthetic reasons have paid the price over and over again. Uh, anonymous savepoints aren't in the standard, so we aren't any closer to the standard with them or without them, and Oracle doesn't have them either. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] nested xacts and phantom Xids
Added to TODO, just so we don't forget later: * Use a phantom command counter for nested subtransactions to reduce tuple overhead --- Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Hmm ... yes, this could be very ugly indeed, but I haven't even looked at the executor code so I can't comment. Are executor nodes copyable? Nope, and even if we had support for that the executor tree per se is just the tip of the iceberg. There's also indexscan status, SRF function internal state, yadda yadda. I think the odds of doing something with all that stuff for 7.5 are exactly zero ... we'd better define a stopgap behavior. Oh, and I've been playing with large objects and I've encountered bugs elsewhere. I'll look at it with the new patch you just posted. Wouldn't surprise me, we've not looked at that yet either. I do feel that we have enough things working that we should commit to nested transactions for 7.5. There will be some things that we have to restrict, such as cursors and perhaps large objects. But it's surely better than no subtransactions at all. regards, tom lane ---(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 -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nested Transactions, Abort All
Josh Berkus wrote: Well, that involves either creating a conditional capability in the backend, or in psql, neither of which will happen for 7.5. The best we can do is allow COMMIT NESTED INGORE ERROR (or COMMIT NESTED INGORE ROLLBACK) and just let the script keep going. I am thinking of cases where you want to drop an object you aren't sure exists in a transaction. Anything more complicated like issuing a replacement query will have to wait for 7.6. OK, I didn't realize that it was a difficult thing. I think it should go on the TODO list but you are the judge of what's a quick fix and what's not. (BTW, aside from this limitation, I am *not* in favor of COMMIT NESTED IGNORE ERROR) OK, no one likes that idea, so let's forget it. Do we want to allow BEGIN NESTED to start a main transaction? Oracle can use SAVEPOINTS all the time because it knows it is always in a transaction, but PostgreSQL is not always. I don't see a downside to allowing it. COMMIT will still commit the entire transaction, of course. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Nested Transactions, Abort All
On Sat, 2004-07-10 at 15:21, Josh Berkus wrote: Bruce, They have no way of reporting a failed query back to the user? How do people program in those environments? Right now any failed query aborts the transaction so it seems it would be pretty easy. Believe it or not, PHP4 doesn't. This is one of the reasons why coders in other languages don't consider PHP a real programming language; the lack of exception handling. However, given this limitation we can't really use NTs in PHP4 anyway, so it's sort of a moot point. Sorry for bringing it up. Uh, I think it can: http://www.php.net/manual/en/function.pg-result-error.php ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Status report
I am still going through my mailbox, trying to address all the open patches so we can move toward beta. Of course, many of the patches I kept need some adjustment to get applied (e.g. configuration file location) so it is slow going. However, we still have PITR unapplied, autovacuum unapplied, and nested transactions don't have savepoint support, so we are still a while away from beta. I think we should start thinking of beta as August 1 rather than mid-July. Plus a bunch of other minor patches :) Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] bug in DROP TABLESPACE
Added to TODO: * Allow changing of already-created database and schema tablespaces --- Christopher Kings-Lynne wrote: Tom already mentioned this just after committing tablespaces: 'Minor DROP TABLESPACE issue' http://www.mail-archive.com/[EMAIL PROTECTED]/msg46540.html In fact, I see that you contributed to the thread :-). I think the result of the thread was to make the error message a little more helpful and that adding empty files to represent schemas would be a pain (think WAL and name collision). Ah, I must have been in a dream state. The other thing we need are these two commands: ALTER DATABASE foo SET TABLESPACE spc; ALTER SCHEMA foo SET TABLESPACE spc; I think these should not be considered new features but essential functionality left out of the original patch. Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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: [HACKERS] Status report
On Sat, 10 Jul 2004, Bruce Momjian wrote: I am still going through my mailbox, trying to address all the open patches so we can move toward beta. Of course, many of the patches I kept need some adjustment to get applied (e.g. configuration file location) so it is slow going. However, we still have PITR unapplied, autovacuum unapplied, and nested transactions don't have savepoint support, so we are still a while away from beta. I think we should start thinking of beta as August 1 rather than mid-July. And so we fall into the hole we dig for ourselves with each release, where the beta period takes almost as long as the development period itself ... originally, this was all supposed to be done for June 1st, but we pushed it back to July 1st since ppl only needed another month ... then, July 1st, we did the feature freeze, but because so much was left to be applied, we decided to leave bundling first beta until mid-July ... If six extra weeks hasn't been enough, I have little faith that adding 'yet another 2 weeks' is going to be enough either ... leave them for 7.6 then ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Status report
My point is that it isn't the patch submitters we are waiting on anymore. It is the backlog of patches that need review/adjustment. --- Marc G. Fournier wrote: On Sat, 10 Jul 2004, Bruce Momjian wrote: I am still going through my mailbox, trying to address all the open patches so we can move toward beta. Of course, many of the patches I kept need some adjustment to get applied (e.g. configuration file location) so it is slow going. However, we still have PITR unapplied, autovacuum unapplied, and nested transactions don't have savepoint support, so we are still a while away from beta. I think we should start thinking of beta as August 1 rather than mid-July. And so we fall into the hole we dig for ourselves with each release, where the beta period takes almost as long as the development period itself ... originally, this was all supposed to be done for June 1st, but we pushed it back to July 1st since ppl only needed another month ... then, July 1st, we did the feature freeze, but because so much was left to be applied, we decided to leave bundling first beta until mid-July ... If six extra weeks hasn't been enough, I have little faith that adding 'yet another 2 weeks' is going to be enough either ... leave them for 7.6 then ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] Status report
On Sat, 10 Jul 2004, Bruce Momjian wrote: My point is that it isn't the patch submitters we are waiting on anymore. It is the backlog of patches that need review/adjustment. Of course, many of the patches I kept need some adjustment to get applied ... ... to me, that indicates that even after review, they will have to go back to the submitter to be adjusted, and sent back, and reviewed, and ... Get in what you feel comfortable adding over the next week, the rest can wait until 7.6 ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Status report
If you get full control of PostgreSQL, you can dictate what will happen. Until then, I will follow the community consensus, which may or may not match your opinion. --- Marc G. Fournier wrote: On Sat, 10 Jul 2004, Bruce Momjian wrote: My point is that it isn't the patch submitters we are waiting on anymore. It is the backlog of patches that need review/adjustment. Of course, many of the patches I kept need some adjustment to get applied ... ... to me, that indicates that even after review, they will have to go back to the submitter to be adjusted, and sent back, and reviewed, and ... Get in what you feel comfortable adding over the next week, the rest can wait until 7.6 ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] PREPARE and transactions
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 explicit transactions, as you say is common, then you're obviously not defining prepared statements inside explicit transactions either. This whole discussion seems to be considering only the case of PREPAREs issued as SQL statements, by a programmer who is fully cognizant of where he's beginning and ending transactions. The issue I was trying to raise at the beginning of the thread was: what about prepared statements created by client libraries (think JDBC for instance) using the V3 protocol Parse message? Rolling back a successful prepare because of a later transaction failure seems like exactly not what such a library would want. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] pg_largeobject and tablespaces
I have added to TODO: * Allow moving system tables to other tablespaces, where possible --- Tom Lane wrote: Christopher Kings-Lynne [EMAIL PROTECTED] writes: With our new tablespace set up, is it ever possible for someone to move pg_largeobject to another tablespace? Assuming that ALTER TABLE SET TABLESPACE gets in, my preferred answer is to apply that operation to pg_largeobject. We do need to think a bit about what the safety constraints on ALTER TABLE SET TABLESPACE should be. To allow the above, we cannot completely forbid moving system catalogs. However, it will not work to allow moving *every* system catalog ... for instance, if you were to move pg_class itself, things would be more than slightly broken, because the backend could not find pg_class to learn where the system catalogs are. It might be that we only need to forbid moving the nailed system relations, but I haven't thought it through yet. If nothing else comes to mind, a reasonable compromise for 7.5 would be to forbid moving any system catalog except pg_largeobject and its indexes ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] plperl (7.5)
The new plperl returns sets by having the function return an array. This requires that the entire array be built before anything is returned. It seems to me that that does not scale very well. The technique of RETURN NEXT; scales much better. For example, you maybe selecting rows, doing a little formating and adding some information and returning the rows as you process them. It the table you are selecting is very large, you still have to hold the results in memory to return them all at once. Am I misunderstanding something or can someone explain the reasoning? In an ideal implementation both techniques would be possible since returning the array is kind of cool ;-) --elein [EMAIL PROTECTED]Varlena, LLCwww.varlena.com PostgreSQL Consulting, Support Training PostgreSQL General Bits http://www.varlena.com/GeneralBits/ = I have always depended on the [QA] of strangers. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested Transactions, Abort All
On Sat, Jul 10, 2004 at 08:25:16PM -0400, Bruce Momjian wrote: Do we want to allow BEGIN NESTED to start a main transaction? I have a better question: do we allow SAVEPOINT (i.e. to establish a savepoint, i.e. to open a nested transaction) within an aborted transaction block? This is allowed in nested transactions, so: begin; select 0/0; -- aborts begin; select 1; -- the usual commands will be ignored till the end commit; commit; -- it really rolls back But in savepoints it's not clear that we want to allow to establish a savepoint, so do you prefer begin; select 0/0; savepoint foo;-- commands will be ignored select 1; -- commands will be ignored release foo; -- commands will be ignored commit; -- it really rolls back Or begin; select 0/0; savepoint foo;-- executes it select 1; -- commands will be ignored release foo; -- executes it commit; -- it really rolls back There is not a lot of difference. This was allowed in nested transactions because we wanted the nesting be to OK when using it in a possibly aborted transaction block, so the user would not commit a transaction that could not have been created. In savepoints it's a nonissue because the command to end the outer xact is different. My opinion is that we should disallow both SAVEPOINT and RELEASE when in an aborted transaction block. Only ROLLBACK TO, ROLLBACK and COMMIT would be allowed. In this scenario, ROLLBACK TO would always return to a non-aborted transaction state, or the target savepoint would not exist and the state would remain the same. There are several places where the code could be made simpler with this. Opinions please? -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Siempre hay que alimentar a los dioses, aunque la tierra esté seca (Orual) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] plperl (7.5)
On Sat, Jul 10, 2004 at 09:18:28PM -0700, elein wrote: The new plperl returns sets by having the function return an array. This requires that the entire array be built before anything is returned. It seems to me that that does not scale very well. The technique of RETURN NEXT; scales much better. I think RETURN NEXT does the same thing anyway ... they just store tuples in a Tuplestore and then the whole thing is returned. So the function actually doesn't return until the whole function is done. The set-returning-function infraestructure actually has a mode on which you can return one tuple per call, but PL/pgSQL uses only the other mode, materializing the whole set before return. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) El conflicto es el camino real hacia la unión ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Nested Transactions, Abort All
On Sat, 10 Jul 2004, Bruce Momjian wrote: Oracle can use SAVEPOINTS all the time because it knows it is always in a transaction, but PostgreSQL is not always. PostgreSQL is also alsways in a transaction. If some use autocommit and go SAVEPOINT foo; RELEASE foo; The first will work and that transaction will end. Then the next is in a new transaction and will fail with an error saying that foo does not exist. That's how it should be. If people don't like or understand autocommit they should not use it. This is especially true in other cases where people do updates that really must be in a single transaction. Your idea is that if we use nested BEGIN/COMMIT one can always issue these even if one does not know if one have done BEGIN before or not. To me this is a problem with autocommit that is solved by not using autocommit. I don't think this is a problem we should solve. -- /Dennis Björklund ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
