Re: [HACKERS] Schedule, feature freeze, etc
Matthew T. O'Connor wrote: What about features that have been submitting patches and trying to get included for a few weeks now. Specificially, I'm concerned with pg_autovacuum backend integration. I know people have been busy frying bigger fish, so the patch probably hasn't recieved much review yet, Actually I'd have liked to see pg_autovacuum committed before July 1st, because it appears as the right place for auto serverlog rotation stuff as well. Regards, Andreas ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Adding VERSION to startup log output
The one question that gets asked the most on these lists AFAICS is: **can you tell me what version you're running?** ...and we just had a case of confusing the version itself (which seems to be a reasonably easy mistake to make). This information is not displayed at startup, even at debug5. This info is available in the file PG_VERSION and that it is accessible using SQL...but that is not the same thing as bringing it to the attention of anybody reading the log, or watching startup for the first time. Would anybody object to adding an extra line at startup that shows the version number of the database system? Message suggestion: Starting PostgreSQL Version database system (Nothing cheesy, just informative and neutrally welcoming...) Comments? 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
Re: [HACKERS] Adding column comment to information_schema.columns
I didn't write that (or at least, I didn't mean to write that :-). The SQL standard tells that they will not use trailing underscores, therefore *my* conclusion is that it is future proof (from a standards perspective) if all PostgreSQL extensions use a trailing underscore. Which also gives an indication to the user that it is a PostgreSQL extension. All you need to do is add an oid_ column then you can join the info_schema tables to the catalogs however you like. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] working on support triggers on columns
Glad to see this is being handled by someone. Other things of the top of my head: Add support for psql to display the information. For that he needs to update pg_get_triggerdef() in src/backend/utils/adt/ruleutils.c He should probably also check pg_dump support for them. Add support for tab-completion if needed. *Update all relevant documentation.* Consider a mechanism for adding or dropping columns (e.g. ALTER TRIGGER). Handle dropped columns (via ALTER TABLE) gracefully. Not necessary ISTM - there's no way to alter existing triggers, and I don't see why we should bother with adding a command just for this. Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Adding column comment to information_schema.columns
On Sat, Jul 03, 2004 at 18:02:01 +0800, Christopher Kings-Lynne [EMAIL PROTECTED] wrote: Why are they useful If you want PG specific stuff then use the PG specific catalogs!!! The information schema could be used to provide a more stable interface. The pg catalog changes from release to release and it would be nice to be able to write code which is more future proof. Really? It will have the same problem! That being that when we change stuff in the catalogs, we will need to change it in the info schema as well more than likely... That isn't my expectation. I would expect that in most cases the pg catalog would be changed to include more information, not less and that the old information affected by a change could still be obtained by using a more complicated view. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested Transactions, Abort All
Alvaro Herrera [EMAIL PROTECTED] writes:
If we change the syntax, say by using SUBCOMMIT/SUBABORT for
subtransactions, then using a simple ABORT would abort the whole
transaction tree.
This seems like a non-starter to me. That would make it impossible to write
SQL generic code that could be used from within a transaction or as a
top-level transaction.
Consider for example if I have application code that normally handles
archiving old data (excuse the odd made-up pseudo-code syntax):
archive_table($tab, $date) {
query(
BEGIN
INSERT INTO archive_$tab (select * from $tab where date ?)
DELETE FROM $tab where date ?
END
, $date, $date);
}
Then I later decide I sometimes want to do that along with other jobs together
in a transaction. I can't just do:
query(BEGIN);
archive_table(tab1, date);
archive_table(tab2, date);
other_maintenance_work();
query(END);
Because then the archive_table() function would get an error from trying to use
BEGIN when it would need a SUBBEGIN. And it would not be any better if I
change archive_tab to use SUBBEGIN because I might be using it directly
elsewhere.
This seems like a irregularity in the API that makes sense only from an
implementation point of view. Top level transactions may be very different
from the implementation side, but from the user side they should really be
presented as being exactly the same as successive levels.
I do think a COMMIT ALL and ABORT ALL would be useful, but not for end-users.
I think they would be useful at a lower level. For example a web site could
issue an ABORT ALL at the end of generating the page to ensure any uncommitted
transactions fail and the connection is restored to a usable state for the
next page request.
--
greg
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Adding VERSION to startup log output
Would anybody object to adding an extra line at startup that shows the version number of the database system? Message suggestion: Starting PostgreSQL Version database system (Nothing cheesy, just informative and neutrally welcoming...) Comments? Best Regards, Simon Riggs If you do that, does it make sense to display the full version, instead of what's stored in PG_VERSION? (e.g. 7.3.4 vs. 7.3) Mike ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Creating a selective aggregate ??
Najib Abi Fadel [EMAIL PROTECTED] writes: Any ideas?? Could this be done with aggregates ?? I think you're going to have to write code that reads all the records, keeps state representing the current period start and the last seen end date and outputs data every time it sees a gap like you want. This could be a set returning plpgsql or plperl or plwhatever function, but it may be easier to write it in your application level code. -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] nested-xacts cursors (was Re: Performance with new nested-xacts code)
Alvaro Herrera [EMAIL PROTECTED] writes: Yeah. Another answer would be to reset the executor state if the cursor is modified in a subtransaction that aborts: Reset is no solution --- rewinding the cursor to the beginning still leaves it in a state that is inconsistent with the restored state of the bufmgr refcounts, etc. We have to restore the executor tree to the state it was in when we entered the subtransaction, not any earlier or later state. The problem is that that's a *major* bit of work, and probably impossible to do completely (how are you going to get a user-written SRF to restore state?). But I think it would be the best solution if we can think of a reasonable way to do it. Another idea I've been wondering about involves leaving the cursor's state alone at subtrans abort, and instead trying to fix up the bufmgr etc state to be correct for this situation. This seems not real easy since I'm not sure how we distinguish state changes associated with advancing an outer cursor from those associated with completely-inside-the-subxact operations. But it seems at least theoretically doable without breaking user SRFs. Also, it's possible that we could arrange things so that major cost is incurred only when a subxact actually aborts, rather than in the main-line path of control. (Expending lots of cycles at every subxact start to save state that we might never need really sticks in my craw...) One possible plan of attack for this approach is to abandon the notion that bufmgr per se is responsible for figuring out what to reset its state to. Instead we would insist on doing a proper shutdown of inside-the-transaction portals, and expect that doing so would bring the refcounts to where they oughta be. I think that this would have been an unworkably fragile solution back in the day when the present error recovery approach was designed, because there were too many bugs and we were often recovering from the effects of those bugs as much as anything else. But maybe now we could get away with it. BTW, I've been more or less ignoring the nearby debate about whether cursors ought to roll back at subxact abort or not, because right now I don't know how to implement *either* behavior. Unless we have credible theories about how to implement both, it's a bit useless to debate which is better. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Nested Transaction TODO list
Here's the promised list of things I think we still need to fix to complete the nested-transactions project. I assume you have a private todo list as well --- can we compare notes? Cursors have a whole range of issues, as is already under discussion. Present design for bufmgr and indexscan cleanup may be all wrong. Still need to agree about externally visible behavior (a different stmt than begin/commit for subxacts? What about savepoints?) Also, what about exposing this functionality in plpgsql? Seems like we need some kind of exception handling syntax to make this useful. What does Oracle do? What about reporting transaction state/nesting level to client? I did not like the GUC-variable approach in the original patch, partly on grounds of efficiency and partly because I doubt it works under transaction-failure conditions. I'm inclined to think we need a small protocol change. Perhaps it would work to add an additional field to Z messages that is only sent when nest level 1. Need to think about pg_locks view now that single backend may have multiple XIDs --- do we need more info in pg_locks?? Why does postgres.c discriminate against START TRANSACTION where it now allows BEGIN? Could simplify code by just allowing all TransactionStmt types. Does xact.c not allow a subtrans from TBLOCK_STARTED state? Should fix. I wonder whether we shouldn't rename TopTransactionContext. Any existing references to TopTransactionContext are more than likely wrong given the way its semantics have been subtly changed. (I checked everything in standard distro, but what about external PLs and user-written fns ...) Check order of operations in subtransaction start/commit/abort. Is there a good reason not to be fully consistent with top-level cases? Document where there is. trigger.c: not at all sure about the model for handling trigger firing status. It looks like a subtrans could fire triggers that were pending from an outer xact, which is dubious at best. Comments in htup.h now need work; seems a shame to just throw 'em away but I guess that's what we use CVS for. Couldn't we simplify IsSubTransaction into a check on nest depth? plpgsql: is it okay for simple_eval_estate to live throughout the toplevel xact? subxact abort might release resources that are still ref'd in estate, leading to trouble at top commit? Maybe we need one simple_eval_estate per subxact ... We should think about whether there's not a better way for VACUUM FULL to work than session locks now. xact_redo seems a bit optimistic about alignment? Might be OK, not sure. Need to test on alignment-picky box such as HP... Not sure about logic for OnCommitActions, take another look. DONE/CANCELED logic for triggers looks fishy too, particularly test at line 1946ff (2003ff in my committed patch) TransactionIdIsInProgress needs work/review; comments are off for one thing, and it seems *way* too inefficient. Note it should be possible to skip subtrans search for sufficiently old xacts (hm ... couldn't we skip sinval search too...) catcache mechanism seems unacceptably inefficient as well. Need to optimize on assumption that very few if any entries are pinned. relcache same I think (although frequency with which entries will be pinned is higher, and the total number of entries lower, so this is less obviously sucky) Most if not all of the uses of SubTransXidsHaveCommonAncestor should probably go away, since it's rare to apply this test only once. Better to fold the probe XID to its toplevel parent once instead of N times. More generally, we have replaced cheap tests with expensive ones in many places in tqual.c; this needs more thought. Note TransactionIdIsCurrentTransactionId has become much looser. Need to look at its uses ... (seems okay in a quick look but I'm not totally convinced...) XactLockTableWait --- annoying that it waits for top xact even when subtrans has already aborted; possibly even a recipe for deadlock. Probably better if we make subtransactions take out locks on their own XIDs, same as top level. If these are held till commit/abort same as other locks, I think we can simply revert XactLockTableWait to its prior state and it will do just the right thing. *why* can't you make a subtrans read only? (Probably just temporary until GUC rollback is worked on?) See assign_transaction_read_only. Need to make sure you can't loosen the constraint, though (no r/w subxact of r/o parent), so we do need an assign hook to check that. Seems like subtrans could/should be truncated much sooner than clog. What we need here is a short-circuit test to avoid pursuing parent of a subtrans older than, say, RecentGlobalXmin. If it isn't yet marked committed then it must be aborted, no need to look at parent. But think about race conditions and order of marking subxacts during commit/abort. [older version of same thought] Truncating subtrans log needs more thought --- race conditions about when/whether a subxact is properly
Re: [HACKERS] Nested Transactions, Abort All
Greg Stark [EMAIL PROTECTED] writes: This seems like a irregularity in the API that makes sense only from an implementation point of view. You are attacking a straw man. This does *not* make sense from an implementation point of view --- it's easier to have just one command (and in fact that is what is in CVS tip). The proposal to use different commands was advanced on the grounds that it's a more user-friendly API. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] LinuxTag wrapup
Dear developers, classifying the questions we got those three days in the PostgreSQL booth on LinuxTag, we had three ever repeating topics, two of them non-surprising: - what's the difference to MyS*** - what about win32 native - what about Oracle portability. The third question was asked from serious corporate users, and what I told them about ora2pg and simple procedure migration didn't satisfy them completely: they asked about oracle sql query syntax compatibility. They were quite disappointed when I told them we're ansi standard and after numerous discussions we don't ever intend to implement that oracle stuff. IMHO we should rethink if we could make those people happy. How about a loadable personality (IIRC SAPDB has something like that), to exchange the parser in use with a custom one (by a SET command)? This way we have a pure ansi default, while enabling a way so someone could contribute an oracle style parser. Regards, Andreas BTW, many people I addressed when they rested for a few seconds in front of the booth just said no thanks, I don't have any questions, I'm using PostgreSQL and I'm happy with it. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested Transaction TODO list
On Sat, Jul 03, 2004 at 11:03:33AM -0400, Tom Lane wrote: Here's the promised list of things I think we still need to fix to complete the nested-transactions project. I assume you have a private todo list as well --- can we compare notes? Hmm ... there are a lot of things in your list not in mine. The things that I have not in yours is pretty short: - fix SPI to work on functions not-in-xact-block with TransactionStmt (this is related to the item on subxacts under TBLOCK_STARTED) - fix large objects longevity Some comments: Still need to agree about externally visible behavior (a different stmt than begin/commit for subxacts? What about savepoints?) Also, what about exposing this functionality in plpgsql? Seems like we need some kind of exception handling syntax to make this useful. What does Oracle do? We should offer the savepoint syntax; seems easy to do. I think a lot of things are easier to do if we use a different syntax _and_ allow a subxact to start from TBLOCK_STARTED. What about reporting transaction state/nesting level to client? I did not like the GUC-variable approach in the original patch, partly on grounds of efficiency and partly because I doubt it works under transaction-failure conditions. I'm inclined to think we need a small protocol change. Perhaps it would work to add an additional field to Z messages that is only sent when nest level 1. It's a shame to have to lose backwards compatibility. Why can't we use ParameterStatus? Perhaps having it as a GUC var was a bad idea, but we can do otherwise. Why does postgres.c discriminate against START TRANSACTION where it now allows BEGIN? Could simplify code by just allowing all TransactionStmt types. Oversight. I wonder whether we shouldn't rename TopTransactionContext. Any existing references to TopTransactionContext are more than likely wrong given the way its semantics have been subtly changed. (I checked everything in standard distro, but what about external PLs and user-written fns ...) We need to think about this and interaction with EOXact callbacks -- a non-subxact-aware function could easily break if called inside a subxact. TransactionIdIsInProgress needs work/review; comments are off for one thing, and it seems *way* too inefficient. Note it should be possible to skip subtrans search for sufficiently old xacts (hm ... couldn't we skip sinval search too...) Can we use a cutoff like RecentGlobalXmin here? -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) No reniegues de lo que alguna vez creíste ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested Transaction TODO list
Alvaro Herrera [EMAIL PROTECTED] writes: On Sat, Jul 03, 2004 at 11:03:33AM -0400, Tom Lane wrote: What about reporting transaction state/nesting level to client? I did not like the GUC-variable approach in the original patch, partly on grounds of efficiency and partly because I doubt it works under transaction-failure conditions. I'm inclined to think we need a small protocol change. Perhaps it would work to add an additional field to Z messages that is only sent when nest level 1. It's a shame to have to lose backwards compatibility. I don't like using ParameterStatus because it's not designed for dealing with values that may change many times in a single query. Also it sends strings, which this really isn't. I haven't looked at JDBC, but at least in the libpq code, what we could safely do is extend the existing no transaction/in transaction/in failed transaction field to provide a five-way distinction: those three cases plus in subtransaction/in failed subtransaction. You could not directly tell the depth of your subxact from this, but do you need to? regards, tom lane ---(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] Creating a selective aggregate ??
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I am searching for a way to have the minimum date and maximum date for dates seperated by one week whitout gaps between them. This is not something you can accomplish (easily) with just SQL, so you will need some sort of higher language. Here's a little function I came up with, which might do what you want: CREATE OR REPLACE FUNCTION noweekgaps() RETURNS SETOF TEXT AS ' DECLARE startday TEXT; endday TEXT; lastone INT; mydiff INT; myrec RECORD; BEGIN FOR myrec IN SELECT TO_CHAR(d, \'Mon DD, \') AS z, TO_CHAR(d,\'J\')::integer AS j FROM t ORDER BY d ASC LOOP IF startday IS NULL THEN startday := myrec.z; ELSE mydiff := myrec.j - lastone; IF mydiff 7 THEN RETURN NEXT startday || \' - \' || endday; startday := myrec.z; END IF; END IF; lastone := myrec.j; endday := myrec.z; END LOOP; IF endday IS NOT NULL THEN RETURN NEXT startday || \' - \' || endday; END IF; RETURN; END; ' LANGUAGE plpgsql; - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200407031237 -BEGIN PGP SIGNATURE- iD8DBQFA5uD9vJuQZxSWSsgRAqpLAKDBm0dxgiigfSL6cnhz83pmKV1KLgCgogD2 etbk3BJiWm5bplCEIEFXbbE= =HT+N -END PGP SIGNATURE- ---(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] LinuxTag wrapup
On Sat, 3 Jul 2004, Andreas Pflug wrote: IMHO we should rethink if we could make those people happy. How about a loadable personality (IIRC SAPDB has something like that), to exchange the parser in use with a custom one (by a SET command)? Having two parsers would be a nightmare to maintain. If anything one could have one parser that handles oracle syntax and give errors on such constructs unless some variable is set. The question is how much of the problems that are pure syntax and what needs deeper changes. My guess is that just changing some syntax will not be enough to make many oracle program work. BTW, many people I addressed when they rested for a few seconds in front of the booth just said no thanks, I don't have any questions, I'm using PostgreSQL and I'm happy with it. Then they probably just wanted to chat but didn't know how to start a conversation. A true geek problem?!? :-) -- /Dennis Björklund ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] LinuxTag wrapup
Andreas Pflug [EMAIL PROTECTED] writes: - what about Oracle portability. IMHO we should rethink if we could make those people happy. How about a loadable personality (IIRC SAPDB has something like that), to exchange the parser in use with a custom one (by a SET command)? This way we have a pure ansi default, while enabling a way so someone could contribute an oracle style parser. How about an external tool that helps in translating apps to SQL-standard syntax? Oracle does accept the standard syntax after all. That way we are truly helping people liberate themselves: they can switch to any SQL-compliant database, not only Postgres. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] LinuxTag wrapup
Dennis Bjorklund wrote: Having two parsers would be a nightmare to maintain. Probably. It just came to my mind because one visitor mentioned he would look at the bison stuff to do it himself. I meant to enable him to do so if he likes (and can) without hacking the core product. If anything one could have one parser that handles oracle syntax and give errors on such constructs unless some variable is set. The question is how much of the problems that are pure syntax and what needs deeper changes. My guess is that just changing some syntax will not be enough to make many oracle program work. That's true, it's the question how much can be offered without too much effort. I'm not too deep in oracle stuff, what comes to my mind is - outer join syntax (parser thing) - sequences usage (parser too) - maybe stored procedure call, with a wrapper to convert output parameters to a composite return value. There's certainly no point supporting any weird ddl command, so there's still porting work to be done when migrating. Regards, Andreas ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] LinuxTag wrapup
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: - what about Oracle portability. IMHO we should rethink if we could make those people happy. How about a loadable personality (IIRC SAPDB has something like that), to exchange the parser in use with a custom one (by a SET command)? This way we have a pure ansi default, while enabling a way so someone could contribute an oracle style parser. How about an external tool that helps in translating apps to SQL-standard syntax? Oracle does accept the standard syntax after all. That way we are truly helping people liberate themselves: they can switch to any SQL-compliant database, not only Postgres. Nice idea, but - sources might not be accessible - sources might not be easily readable (esp. if not embedded sql, example pgadmin) or created dynamically. - probably too many non-ansi compliant servers (i.e. pre-9) still in use. Regards, Andreas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] LinuxTag wrapup
Andreas Pflug [EMAIL PROTECTED] writes: How about an external tool that helps in translating apps to SQL-standard syntax? Oracle does accept the standard syntax after all. Nice idea, but - sources might not be accessible - sources might not be easily readable (esp. if not embedded sql, example pgadmin) or created dynamically. - probably too many non-ansi compliant servers (i.e. pre-9) still in use. Well, I am certainly *not* buying into a goal of support any application that has worked with any version of Oracle with zero source code changes. As Dennis already pointed out, the syntax is just the tip of the iceberg. (Look for instance at the thread on pgsql-bugs yesterday, where we concluded that Oracle 8 thinks the way to interpret WHERE charcolumn = intconstant is to cast the column to integer. Talk about bizarre choices...) If we bought into such a goal, even partially, we'd stop making forward progress on our own issues and spend all our time hashing over Oracle compatibility choices. The plain fact is that users who want to migrate off Oracle are going to have to take significant responsibility for porting their own apps, the more so the more they depended on non-standard constructs. We can perhaps help them with tools, but if they want a zero-effort solution they are out of luck. 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] PREPARE and transactions
On Sat, Jul 03, 2004 at 08:20:17AM +0530, Abhijit Menon-Sen wrote: But for what it's worth, I strongly dislike the later proposal of making prepared statements anonymous, and pattern matching the statement text, especially if they reintroduce the need to quote query parameters. Only in cases where you aren't sure your prepared statement name will be visible. And even in those cases, wouldn't PQexecParams() do the job? That'd be like PQexecPrepared() except the query text becomes a kind of replacement for the query name. Jeroen ---(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] LinuxTag wrapup
On Sat, Jul 03, 2004 at 05:59:17PM +0200, Andreas Pflug wrote: classifying the questions we got those three days in the PostgreSQL booth on LinuxTag, we had three ever repeating topics, two of them non-surprising: - what's the difference to MyS*** - what about win32 native - what about Oracle portability. That about covers the important stuff. Some more for the other bucket (although they all came repeatedly): - so how do I pronounce Postgre? - will it support my performance requirements? - are you a company? Can you tell me someone who is? - have a job for me? - do you have drivers for Kylix? - why don't you support product? - what client GUI programming environment do you offer? On the Postgre point, I remarked to some friendly people (who are developing a content management system based on postgres, by the way) that we ought to have something like just call me Postgres posters in our booth. It turned out they had the gear to cut stickers in letter shapes, so a little while later we actually had those words plastered over our booth walls. I think we got most interested passers-by before they had a chance to read it, though. On the last points I eventually learned to stop answering and shoot back the question instead: what, doesn't yours support ODBC? In particular, X.org's Leon Shiman felt that we Postgres people should be especially interested in their work on X. I didn't even see what he was getting at until he mentioned GUI builders. Again, I told him that my personal conviction is that those should be database-agnostic and the very idea that these should be bundled with database servers is a by-product of the need to sell proprietary database licenses, and that any good free GUI builder should build on GUI toolkits rather than on raw X, etc. But like I said, that's just my personal conviction. I definitely think people in our community ought to be willing to work together with the MySQL people, the FireBird people and anybody else in the free world to have world-class GUI development tools; it should be a rising tide that raises all boats. If anyone feels differently, I did make it perfectly clear that I wasn't speaking for anyone. Of course one area where we should care about X, but I completely forgot to mention this to Leon, is that modern graphics hardware can be used to speed up database engines. Hardware detection of collisions or overlaps, for instance, has been shown to be a viciously effective filter for spatial joins in GIS databases. But that's another story! Jeroen ---(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: [Re] Re: [HACKERS] PREPARE and transactions
On Sat, Jul 03, 2004 at 02:59:58PM +1200, Oliver Jowett wrote: I think you mean between 7.2 and 7.3. Ah, OK. I thought PREPARE had been added in 7.4. My apologies. Yes. I see PREPARE/EXECUTE as a SQL-statement-level, connection-local way of getting control over reuse of plans that doesn't otherwise affect the semantics of the query. With the V3 protocol you can also do it at the protocol level rather than the statement level, but it's still the same underlying operation; why should it behave differently? The real basis of this pattern-matching idea I'm proposing is that the naming issue (which is semantic) and the reuse of plans (which is an optimization) could be handled separately. The next question I think is whether the two should really share a namespace, and whether the two types of names should have the same behaviour. The underlying mechanism would remain the same, but once these issues have been decoupled I think SQL names and protocol-level names are easy to see as different things. I'm not too worried, to tell the truth -- the JDBC driver has already moved to using the protocol-level approach, and so long as that doesn't change I'm happy. It just seems to me that the changes you're advocating are going to break more clients than they help (e.g. it would have required nontrivial work on the JDBC driver as of a month ago to deal with the change), and for no convincing reason. Maybe. OTOH I find it simply disturbing (as a matter of aesthetics, you could say) that the application can be messing around with the protocol underneath the middleware it's supposed to live on top of--the middleware that should expect to be in control of the backend below the SQL level. Jeroen ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested Transaction TODO list
Alvaro Herrera [EMAIL PROTECTED] writes: Why does START have a different Node from BEGIN anyway? This seems to be a leftover from when people thought they should behave differently. They are the same now, so there's no point in distinguishing them, or is it? [shrug...] I'd counsel leaving this as-is. We've practically always regretted it when we made the parser discard information about what the user typed. For instance, I was just reminded yesterday that we really ought to distinguish SortClauses created due to user ORDER BY clauses from those created because the parser silently added 'em. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested Transaction TODO list
On Sat, 03 Jul 2004 17:40:23 -0400, Tom Lane [EMAIL PROTECTED] wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Why does START have a different Node from BEGIN anyway? This seems to be a leftover from when people thought they should behave differently. They are the same now, so there's no point in distinguishing them, or is it? [shrug...] I'd counsel leaving this as-is. We've practically always regretted it when we made the parser discard information about what the user typed. For instance, I was just reminded yesterday that we really ought to distinguish SortClauses created due to user ORDER BY clauses from those created because the parser silently added 'em. How about simply documenting semantic equivalences, and making them somewhat more explicit to the user? Regards Haroldo -- Por favor registre [EMAIL PROTECTED] como mi nueva y unica direccion de correo en lugar de la vieja [EMAIL PROTECTED] Please, record [EMAIL PROTECTED] as my new and unique email address instead of ye old [EMAIL PROTECTED] Gracias. Thankyou. ---(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] LinuxTag wrapup
On Sat, 3 Jul 2004, Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: - what about Oracle portability. IMHO we should rethink if we could make those people happy. How about a loadable personality (IIRC SAPDB has something like that), to exchange the parser in use with a custom one (by a SET command)? This way we have a pure ansi default, while enabling a way so someone could contribute an oracle style parser. How about an external tool that helps in translating apps to SQL-standard syntax? Oracle does accept the standard syntax after all. That way we are truly helping people liberate themselves: they can switch to any SQL-compliant database, not only Postgres. I totally agree. After all, oracle provides such tools to their customers. Gavin ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] I am back from Germany and Armenia
I have returned from Germany and Armenia. I will post a summary of my trip soon. I am going to take tomorrow off and start catching up on email starting Monday. I estimate it will take me 3-5 days to catch up. -- 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 Transaction TODO list
Tom Lane wrote: I don't like using ParameterStatus because it's not designed for dealing with values that may change many times in a single query. Also it sends strings, which this really isn't. What about including the new nesting level in the SUBBEGIN/SUBCOMMIT/SUBABORT CommandStatus string? Yes, it's still a string for a numeric nesting level, but that's also how we pass numeric data such as insert/update rowcounts. I haven't looked at JDBC, but at least in the libpq code, what we could safely do is extend the existing no transaction/in transaction/in failed transaction field to provide a five-way distinction: those three cases plus in subtransaction/in failed subtransaction. You could not directly tell the depth of your subxact from this, but do you need to? This will break the existing JDBC driver in nonobvious ways: the current code silently ignores unhandled transaction states in ReadyForQuery, so you could conceivably end up in situations where the driver thinks you're outside a transaction when you're actually inside a subtransaction, and issues spurious BEGINs. It's simple enough to handle the new states, but it *is* an incompatible protocol change. Please bump the protocol version if you do make this change. And if you're going to bump the protocol version, I have some other changes I'd like to see at the same time :) If named SAVEPOINT syntax (along the lines of what Oracle has) is available in 7.5, and there is a mechanism to COMMIT/ROLLBACK the top-level transaction directly, the JDBC driver doesn't actually need to know anything about subtransactions to correctly implement JDBC's commit, rollback, savepoint interface -- except perhaps to deal with the case where the application rolls back a subtransaction which invalidates an earlier SAVEPOINT. Having the nesting level available will probably make savepoint support a bit easier but it's not vital. If we don't have COMMIT/ROLLBACK of top-level transactions, then the JDBC driver needs to know the current nesting level so it knows how many subtransactions to deal with when a top-level commit/rollback is requested via the JDBC API. I'd actually prefer to see plain COMMIT and ROLLBACK continue to operate on the top-level transaction, since doing anything else is going to break pre-7.5 clients (such as older JDBC drivers) that assume that you're entirely outside a transaction after COMMIT/ROLLBACK. If we don't have SAVEPOINT syntax, then the JDBC driver needs to know the current nesting level so it can track which (client-maintained) savepoints are still valid in the face of the application doing subtransaction work itself. The only other thing the JDBC driver uses the transaction state for is to work out when BEGINs are needed when JDBC-level autocommit is off, and when to allow transaction isolation level changes. As far as I can see, in subtransaction is equivalent to in top-level transaction for both of those cases. So overall, knowing that you're in a subtransaction without knowing the nesting level does not seem very useful.. -O ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested Transaction TODO list
Oliver Jowett [EMAIL PROTECTED] writes: Tom Lane wrote: I haven't looked at JDBC, but at least in the libpq code, what we could safely do is extend the existing no transaction/in transaction/in failed transaction field to provide a five-way distinction: those three cases plus in subtransaction/in failed subtransaction. This will break the existing JDBC driver in nonobvious ways: the current code silently ignores unhandled transaction states in ReadyForQuery, Drat. Scratch that plan then. (Still, silently ignoring unrecognized states probably wasn't a good idea for the JDBC code...) regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] LinuxTag wrapup
Jeroen T. Vermeulen wrote: That about covers the important stuff. Some more for the other bucket (although they all came repeatedly): - so how do I pronounce Postgre? ... On the Postgre point, I remarked to some friendly people (who are developing a content management system based on postgres, by the way) that we ought to have something like just call me Postgres posters in our booth. It turned out they had the gear to cut stickers in letter shapes, so a little while later we actually had those words plastered over our booth walls. I think we got most interested passers-by before they had a chance to read it, though. I've argued for years that postgresql.org's front banner should read: Postgres + SQL = PostgreSQL The fact that novices can't pronounce the name correctly is a problem. People will be afraid to raise the possibility as a solution in the enterprise if they think they'll look like a fool pronouncing the name aloud. I remember back in '94 being corrected when talking about Linux in the enterprise - and I was corrected in the wrong direction. Someone needs to poke the propaganda minister with a stick. Mike Mascari ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested Transaction TODO list
Tom Lane wrote: Oliver Jowett [EMAIL PROTECTED] writes: Tom Lane wrote: I haven't looked at JDBC, but at least in the libpq code, what we could safely do is extend the existing no transaction/in transaction/in failed transaction field to provide a five-way distinction: those three cases plus in subtransaction/in failed subtransaction. This will break the existing JDBC driver in nonobvious ways: the current code silently ignores unhandled transaction states in ReadyForQuery, Drat. Scratch that plan then. (Still, silently ignoring unrecognized states probably wasn't a good idea for the JDBC code...) True, but the alternative (screaming and yelling) would also have broken, just more obviously. Actually, thinking about it, that behaviour only changed recently, and from memory the older code completely ignored the transaction state in ReadyForQuery. The new driver probably hasn't spread too far yet. I'll sort out a patch so the driver breaks more obviously if it gets something unexpected. I still don't think that knowing you're in a subtransaction is very useful unless you also know the nesting level. -O ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] LinuxTag wrapup
On Sat, Jul 03, 2004 at 11:33:35PM -0400, Mike Mascari wrote: The fact that novices can't pronounce the name correctly is a problem. People will be afraid to raise the possibility as a solution in the enterprise if they think they'll look like a fool pronouncing the name aloud. I remember back in '94 being corrected when talking about Linux in the enterprise - and I was corrected in the wrong direction. You made me remember that some time ago a non-tech fellow presented me as giving a talk about Postgresol ... the audience had quite a laugh. It seems nobody thought about instructing him on how to pronounce the thing ... it was rather embarrasing anyway. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) En las profundidades de nuestro inconsciente hay una obsesiva necesidad de un universo lógico y coherente. Pero el universo real se halla siempre un paso más allá de la lógica (Irulan) ---(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 Transaction TODO list
On Sat, Jul 03, 2004 at 11:12:56PM -0400, Tom Lane wrote: Oliver Jowett [EMAIL PROTECTED] writes: Tom Lane wrote: I haven't looked at JDBC, but at least in the libpq code, what we could safely do is extend the existing no transaction/in transaction/in failed transaction field to provide a five-way distinction: those three cases plus in subtransaction/in failed subtransaction. This will break the existing JDBC driver in nonobvious ways: the current code silently ignores unhandled transaction states in ReadyForQuery, Drat. Scratch that plan then. (Still, silently ignoring unrecognized states probably wasn't a good idea for the JDBC code...) What about using the command tag of SUBBEGIN c ? -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Hi! I'm a .signature virus! cp me into your .signature file to help me spread! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
