[HACKERS] Build problem with VS2008 Express Edition
Hi, I managed to get most of 8.3 built with VS2008EE last night. Ii had to change some references to msbuild to vsbuild, which I guess is expected but one compile issue surprised me. I had to change #ifdef IPV6_V6ONLY at backend/libpq/pqcomm.c:386 to: #if defined(IPV6_V6ONLY) (!defined(WIN32) || (_WIN32_WINNT = 0x0501)) because IPPROTO_IPV6 is defined in ws2ipdef.h but the IIPROTO_V6 enum isn't defined in ws2def.h unless you set the version up appropriately. James -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Noob Hints on testing and debugging?
Are there any hints and tips anywhere on practical issues for testing and debugging changes to a backend? (eg how to run it up and feed it SQL ideally without running a postmaster and execing a back end) I'm using VS2008 on XP by choice, and I'd like to investigate supporting something closely related to the Firebird 2.1 trigger on transaction commmit and rollback. James -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Noob Hints on testing and debugging?
James Mansion [EMAIL PROTECTED] writes: Are there any hints and tips anywhere on practical issues for testing and debugging changes to a backend? hackers-list archives has a thread or three... (eg how to run it up and feed it SQL ideally without running a postmaster and execing a back end) Why would you consider that ideal? Such a scenario would have approximately zip to do with the real-world environment your patch would face. What I usually do is fire up a normal psql session and then attach to the backend process with gdb in another window. The only cases where this isn't very useful is where you are trying to debug failure in very early session startup. There are some badly-documented options such as -W that can help even in that kind of situation. I'm using VS2008 on XP by choice, and I'd like to investigate supporting something closely related to the Firebird 2.1 trigger on transaction commmit and rollback. Is there any sanity at all in a trigger-on-rollback? Exactly what would you expect it to be able to accomplish that anyone else could see after the transaction has rolled back? (And no, trigger on commit isn't very much saner.) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [NOVICE] encoding problems
On Tue, Mar 11, 2008 at 12:41:35PM -0400, Tom Lane wrote: If I create the same function on my computer running PostgreSQL 8.3.0 and try the \df+ then the Source Code shows: \x09DECLARE \x09\x09result text; ... That's not an encoding problem, that's an intentional behavioral change in the way that psql formats strings for display. I guess it's a bit annoying if you were hoping that tabs would be useful for pretty-printing purposes. Should we reconsider what's done with a tab in mbprint.c? At the time that patch was introduced there was discussion about this. The problem being that psql has no idea what will actually happen to the cursor when it emits a tab. You can guess but in the end we decided it was more important to have the display not messed up than displaying tabs as is. The other alternative is to convert tabs to spaces on output. Can't remember why we didn't do that. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [HACKERS] Re: [COMMITTERS] pgsql: Add: o Add SQLSTATE severit yto PGconn return status
How about we move it to the wiki. AFAIK we can still lock it down to who can edit it if we want to You should confirm you can get the editing granularity you want before making too many plans here if this is important. The features for locking down things in Mediawiki are very limited. The Wiki philosophy here is that you could revert quite a few bad changes in the time it would take you to lock it down so those bad changes could never happen in the first place. Last time I checked vandalism and bad edits were not a problem on the developer's wiki. It's not. And I personally don't think it would be a problem. But I think it'll be a lot easier to sell to those who prefer textfiles in cvs (hello bruce!) if we can. /Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Noob Hints on testing and debugging?
On Wed, Mar 12, 2008 at 7:21 AM, James Mansion [EMAIL PROTECTED] wrote: Are there any hints and tips anywhere on practical issues for testing and debugging changes to a backend? (eg how to run it up and feed it SQL ideally without running a postmaster and execing a back end) I'm using VS2008 on XP by choice, and I'd like to investigate supporting something closely related to the Firebird 2.1 trigger on transaction commmit and rollback. I've yet to try 2008, but 2005 works beautifully when debugging the server - even stepping into non-core code is simple (I spent some time in the plpgsql debugger plugin for example). The hard part can be attaching the debugger to the appropriate backend. You can either introduce a startup delay (there's a backend command line option for that iirc), or if you want to break at a specific point, I sometimes add a loop to the code along the lines of: int x=0; while (!x) Sleep(100); When the backend hits that point, attach the debugger, break execution, and set x to a value in the locals window. Then you can step through the code from that point. -- Dave Page EnterpriseDB UK Ltd: http://www.enterprisedb.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Add: o Add SQLSTATE severity to PGconn return status
Greg Smith [EMAIL PROTECTED] writes: Will answer my own question now: the main useful protection available here is Mediawiki's full protection, which makes pages so they can only be edited by users with sysops privledges. So in order to make a protected page that, say, mainly Bruce was allowed to edit, he'd have to be elevated to a sysops account. I thought we considered Bruce being the only person who could edit the TODO list more of a bug than a feature. Consider how many times we've had to play broken-telephone trying to explain what the key aspects to express in the items were. It would be simpler to agree that there's a TODO and then have the person who knows what the item is write it or fix Bruce's attempt than to have to go through one person who wasn't necessarily involved in the discussion. That's not to say we don't need designated maintainers to own specific pages. But that's more of a positive assertion that if nobody else is doing something they're responsible for getting it done rather than a negative assertion that nobody else should be helping. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Add: o Add SQLSTATE severity to PGconn return status
On Tue, 11 Mar 2008, Greg Smith wrote: On Tue, 11 Mar 2008, Magnus Hagander wrote: How about we move it to the wiki. AFAIK we can still lock it down to who can edit it if we want to You should confirm you can get the editing granularity you want before making too many plans here if this is important. The features for locking down things in Mediawiki are very limited. Will answer my own question now: the main useful protection available here is Mediawiki's full protection, which makes pages so they can only be edited by users with sysops privledges. So in order to make a protected page that, say, mainly Bruce was allowed to edit, he'd have to be elevated to a sysops account. This would also give him broader powers over the Wiki at large, which presumably the current sysopts would be OK with. And anybody else who was a sysop could do what they wanted to that page as well. Basically there's one relevant level of protection, and you get one group of sysops who can edit any protected page and have access to some other admin features; that's it as far as how finely you can break things down as I understand it. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Add: o Add SQLSTATE severit yto PGconn return status
Magnus Hagander wrote: How about we move it to the wiki. AFAIK we can still lock it down to who can edit it if we want to You should confirm you can get the editing granularity you want before making too many plans here if this is important. The features for locking down things in Mediawiki are very limited. The Wiki philosophy here is that you could revert quite a few bad changes in the time it would take you to lock it down so those bad changes could never happen in the first place. Last time I checked vandalism and bad edits were not a problem on the developer's wiki. It's not. And I personally don't think it would be a problem. But I think it'll be a lot easier to sell to those who prefer textfiles in cvs (hello bruce!) if we can. I don't care who edits it myself, though I can say I get perhaps one patch a year to the TODO list file --- usually I just an email saying remove that item or something. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Add: o Add SQLSTATE severit yto PGconn return status
Bruce Momjian wrote: Magnus Hagander wrote: It's not. And I personally don't think it would be a problem. But I think it'll be a lot easier to sell to those who prefer textfiles in cvs (hello bruce!) if we can. I don't care who edits it myself, though I can say I get perhaps one patch a year to the TODO list file --- usually I just an email saying remove that item or something. Personally I've wished to enter something in the TODO list myself but refrained because it was your area. Having a derived HTML page doesn't make me feel any better -- how should I generate it to ensure that my output is equal to yours? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Add: o Add SQLSTATE severit yto PGconn return status
Alvaro Herrera wrote: Bruce Momjian wrote: Magnus Hagander wrote: It's not. And I personally don't think it would be a problem. But I think it'll be a lot easier to sell to those who prefer textfiles in cvs (hello bruce!) if we can. I don't care who edits it myself, though I can say I get perhaps one patch a year to the TODO list file --- usually I just an email saying remove that item or something. Personally I've wished to enter something in the TODO list myself but refrained because it was your area. Having a derived HTML page doesn't make me feel any better -- how should I generate it to ensure that my output is equal to yours? If you change the text file, I will see the CVS update and update the HTML --- I will never lose a change because my CVS sees your changes. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [NOVICE] encoding problems
Martijn van Oosterhout [EMAIL PROTECTED] writes: The other alternative is to convert tabs to spaces on output. Can't remember why we didn't do that. Yeah. The idea I had was to invent a parameter specifying the number of spaces a tab should expand to --- setting this to zero would give you the current \x09 behavior. I'm not sure how painful it would be to expand tabs properly in mbprint, but it seems possible. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] TODO-list on wiki (was: TODO update about SQLSTATE to PGconn)
On Wed, Mar 12, 2008 at 10:27:06AM -0400, Bruce Momjian wrote: Alvaro Herrera wrote: Bruce Momjian wrote: Magnus Hagander wrote: It's not. And I personally don't think it would be a problem. But I think it'll be a lot easier to sell to those who prefer textfiles in cvs (hello bruce!) if we can. I don't care who edits it myself, though I can say I get perhaps one patch a year to the TODO list file --- usually I just an email saying remove that item or something. Personally I've wished to enter something in the TODO list myself but refrained because it was your area. Having a derived HTML page doesn't make me feel any better -- how should I generate it to ensure that my output is equal to yours? If you change the text file, I will see the CVS update and update the HTML --- I will never lose a change because my CVS sees your changes. That seems like a lot of extra work that should be unnecessary. I asked before for general reactions, so I will now turn that into a formal proposal: Let's move the TODO list to the wiki. Bruce still retains ownership of it and will certainly be doing most of the editing. But people who work on individual items can add/remove items and details as needed directly on the wiki as necessary. Those who still need daily updates can easily grab an RSS feed off the wiki, or use the watch feature (disclaimer: I haven't used the watch feature myself, but I'm told it should work). Can we get a soundoff on this? Good idea, bad idea? (if we decide to do it, we'll have to migrate what's there now, of course. But I'm sure we can find volounteer(s) to help with that so Bruce doesn't have to do all the work.) //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: TODO-list on wiki (was: TODO update about SQLSTATE to PGconn)
Magnus Hagander wrote: If you change the text file, I will see the CVS update and update the HTML --- I will never lose a change because my CVS sees your changes. That seems like a lot of extra work that should be unnecessary. I asked before for general reactions, so I will now turn that into a formal proposal: Let's move the TODO list to the wiki. Bruce still retains ownership of it and will certainly be doing most of the editing. But people who work on individual items can add/remove items and details as needed directly on the wiki as necessary. Those who still need daily updates can easily grab an RSS feed off the wiki, or use the watch feature (disclaimer: I haven't used the watch feature myself, but I'm told it should work). Can we get a soundoff on this? Good idea, bad idea? (if we decide to do it, we'll have to migrate what's there now, of course. But I'm sure we can find volounteer(s) to help with that so Bruce doesn't have to do all the work.) We need it to do a few things: o We need to be able to pull a text and HTML copies for tarballs o Edits have to be quick and easy o I have to be able to make new sections, and move existing items around and between sections o I need to be able to add URLs for items o I need subsections and sub-subsections -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] count(*) performance improvement ideas
I am reading discussion about improving count(*) performance. I have also seen a TODO for this. Many people have suggested TRIGGER based solution to the slow count(*) problem. I looked at the following link which presents the solution neatly. http://www.varlena.com/GeneralBits/120.php But how does that really work for SERIALIZABLE transactions ? If two concurrent transactions INSERT/DELETE rows from a table, the trigger execution of one of the transactions is bound to fail because of concurrent access. Even for READ COMMITTED transactions, the trigger execution would wait if the other transaction has executed the trigger on the same table. Well, I think the READ COMMITTED case can be handled with DEFERRED triggers, but that may require queuing up too many triggers if there are many inserts/deletes in a transaction. Running trigger for every insert/delete seems too expensive. I wonder if we can have a separate counter table (as suggested in the TRIGGER based solution) and track total number of tuples inserted and deleted in a transaction (and all the committed subtransactions). We then execute a single UPDATE at the end of the transaction. With HOT, updating the counter table should not be a big pain since all these updates can potentially be HOT updates. Also since the update of the counter table happens at the commit time, other transactions inserting/deleting from the same user table may need to wait for a very small period on the counter table tuple. This still doesn't solve the serializable transaction problem though. But I am sure we can figure out some solution for that case as well if we agree on the general approach. I am sure this must have been discussed before. So what are the objections ? Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO-list on wiki (was: TODO update about SQLSTATE to PGconn)
On Wed, Mar 12, 2008 at 11:03:13AM -0400, Bruce Momjian wrote: Magnus Hagander wrote: If you change the text file, I will see the CVS update and update the HTML --- I will never lose a change because my CVS sees your changes. That seems like a lot of extra work that should be unnecessary. I asked before for general reactions, so I will now turn that into a formal proposal: Let's move the TODO list to the wiki. Bruce still retains ownership of it and will certainly be doing most of the editing. But people who work on individual items can add/remove items and details as needed directly on the wiki as necessary. Those who still need daily updates can easily grab an RSS feed off the wiki, or use the watch feature (disclaimer: I haven't used the watch feature myself, but I'm told it should work). Can we get a soundoff on this? Good idea, bad idea? (if we decide to do it, we'll have to migrate what's there now, of course. But I'm sure we can find volounteer(s) to help with that so Bruce doesn't have to do all the work.) We need it to do a few things: o We need to be able to pull a text and HTML copies for tarballs That should be doable without too much work. I would, however, like to open a second discussion on wether we actually *need* it. But that's a different discussion than this - if we do need it, we can make that happen. o Edits have to be quick and easy That's the whole idea of a wiki. I don't personally love the markup language, but for the simple kind of stuff that the TODO list deals with (markup-wise), it's very easy to use. o I have to be able to make new sections, and move existing items around and between sections Trivial - again, what a wiki does best. Look at for example http://developer.postgresql.org/index.php/Replication%2C_Clustering%2C_and_Connection_Pooling for a page taht has a bunch of different sections. It uses tables in some and lists in some etc, but it shold give you an idea of how simple it is to craete sections. o I need to be able to add URLs for items You maen links to external sites? Also trivial. Again, what a wiki is designed for more or less. o I need subsections and sub-subsections Should be doable either as wiki sections or as bullet-lists. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [EMAIL PROTECTED]: Re: [GENERAL] array_to_set functions]
Added to TODO: * Add array_accum() and array_to_set() functions for arrays http://archives.postgresql.org/pgsql-hackers/2007-08/msg00464.php --- Merlin Moncure wrote: On 8/14/07, Bruce Momjian [EMAIL PROTECTED] wrote: TODO item? I would say yes...array_accum is virtually an essential function when working with arrays and the suggested array_to_set (and it's built in cousin, _pg_expand_array) really should not be built around generate_series when a C function is faster and will scale much better. array_to_set, as suggested in SQL, is something only a relative expert with PostgreSQL could be expected to write. Thus could generate_series be relieved from providing the only core function for set returning functions in the documentation. IMO, this part of the documentation could use some expansion anyways :) merlin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: TODO-list on wiki (was: TODO update about SQLSTATE to PGconn)
Bruce Momjian [EMAIL PROTECTED] writes: Magnus Hagander wrote: Let's move the TODO list to the wiki. We need it to do a few things: o We need to be able to pull a text and HTML copies for tarballs Why? Even if we think the TODO list needs to appear in tarballs (which is hardly a given if you ask me), why the heck does it have to be in two formats? AFAIK the only reason we bother with an HTML version is to have something to put on the website, and this proposal supersedes that aspect of things. ISTM a plain-text copy would be plenty. Personally I think it would be just fine if we had only the wiki copy and forgot about shipping it in tarballs. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: TODO-list on wiki (was: TODO update about SQLSTATE to PGconn)
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Magnus Hagander wrote: Let's move the TODO list to the wiki. We need it to do a few things: o We need to be able to pull a text and HTML copies for tarballs Why? Even if we think the TODO list needs to appear in tarballs (which is hardly a given if you ask me), why the heck does it have to be in two formats? AFAIK the only reason we bother with an HTML version is to have something to put on the website, and this proposal supersedes that aspect of things. ISTM a plain-text copy would be plenty. Fine with me. Personally I think it would be just fine if we had only the wiki copy and forgot about shipping it in tarballs. The problem with not shipping the TODO file at all is that TODO gives users a list of all known bugs/missing features in that major release. The current web version very soon doesn't match the major release they are running, and I am unsure we want to have historical links for every major release. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: TODO-list on wiki (was: TODO update about SQLSTATE to PGconn)
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Personally I think it would be just fine if we had only the wiki copy and forgot about shipping it in tarballs. The problem with not shipping the TODO file at all is that TODO gives users a list of all known bugs/missing features in that major release. This seems to me to be nonsense. You've never maintained the back-branch versions of the TODO list, so they're out of date anyway --- ie, they don't account for problems discovered post-release. In any case I've always thought that the TODO was developer-oriented documentation, not something users would read. If there's a shortcoming in a feature, it ought to be documented in the SGML manual. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] count(*) performance improvement ideas
Pavan Deolasee [EMAIL PROTECTED] writes: I am sure this must have been discussed before. Indeed. Apparently you didn't find the threads in which the idea of having transactions enter delta entries was discussed? Solves both the locking and the MVCC problems, at the cost that you need to make cleanup passes over the counter table fairly often. I don't see this as material for the core DB but it would make a great contrib module. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: TODO-list on wiki (was: TODO update about SQLSTATE to PGconn)
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Personally I think it would be just fine if we had only the wiki copy and forgot about shipping it in tarballs. The problem with not shipping the TODO file at all is that TODO gives users a list of all known bugs/missing features in that major release. This seems to me to be nonsense. You've never maintained the back-branch versions of the TODO list, so they're out of date anyway --- ie, they don't account for problems discovered post-release. It is a best effort with our limited resources. In any case I've always thought that the TODO was developer-oriented documentation, not something users would read. If there's a shortcoming in a feature, it ought to be documented in the SGML manual. It typically isn't, except for major issues, again due to lack of resources. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] count(*) performance improvement ideas
Tom Lane wrote: Pavan Deolasee [EMAIL PROTECTED] writes: I am sure this must have been discussed before. Indeed. Apparently you didn't find the threads in which the idea of having transactions enter delta entries was discussed? Solves both the locking and the MVCC problems, at the cost that you need to make cleanup passes over the counter table fairly often. I don't see this as material for the core DB but it would make a great contrib module. The TODO list has lots of details on this. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO-list on wiki (was: TODO update about SQLSTATE to PGconn)
Magnus Hagander [EMAIL PROTECTED] writes: On Wed, Mar 12, 2008 at 11:03:13AM -0400, Bruce Momjian wrote: We need it to do a few things: Actually, the part of the current process that a wiki would fail to reproduce is the emails that Bruce sends out about TODO changes. Do we still want those, and if so what would we do about it? Personally I think the current mails are overly verbose --- in particular, quoting (one of the) referenced email messages is good for nothing except archive-bloat. However going over to nothing at all might be too far in the other direction. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] count(*) performance improvement ideas
On Wed, Mar 12, 2008 at 9:01 PM, Tom Lane [EMAIL PROTECTED] wrote: Pavan Deolasee [EMAIL PROTECTED] writes: I am sure this must have been discussed before. Indeed. Apparently you didn't find the threads in which the idea of having transactions enter delta entries was discussed? Solves both the locking and the MVCC problems, at the cost that you need to make cleanup passes over the counter table fairly often. Ok. I would surely look at those threads. Hopefully HOT will considerably solve the counter table cleanup issue. I don't see this as material for the core DB but it would make a great contrib module. I guess we will need some backend hooks to make it really work, no ? At the minimum we need to track the deltas at the transaction level and the ability to do some extra processing at the commit time. May be I should first read those threads and I will find the answers. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO-list on wiki (was: TODO update about SQLSTATE to PGconn)
On Wed, Mar 12, 2008 at 11:36:52AM -0400, Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: On Wed, Mar 12, 2008 at 11:03:13AM -0400, Bruce Momjian wrote: We need it to do a few things: Actually, the part of the current process that a wiki would fail to reproduce is the emails that Bruce sends out about TODO changes. Do we still want those, and if so what would we do about it? Bruce still replies to all emails saying Added to TODO. So you get *two* copies of it every time this time - once on -hackers, -general or whatever, and one on -committers. You will also be able to subscruibe to updates on the wiki. The preferrable way (at least for me, and I'm sure for a lot of folks) using RSS, but also using email. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] count(*) performance improvement ideas
Pavan Deolasee wrote: I am reading discussion about improving count(*) performance. I have also seen a TODO for this. Many people have suggested TRIGGER based solution to the slow count(*) problem. I looked at the following link which presents the solution neatly. http://www.varlena.com/GeneralBits/120.php But how does that really work for SERIALIZABLE transactions ? If two concurrent transactions INSERT/DELETE rows from a table, the trigger execution of one of the transactions is bound to fail because of concurrent access. Even for READ COMMITTED transactions, the trigger execution would wait if the other transaction has executed the trigger on the same table. Well, I think the READ COMMITTED case can be handled with DEFERRED triggers, but that may require queuing up too many triggers if there are many inserts/deletes in a transaction. Running trigger for every insert/delete seems too expensive. I wonder if we can have a separate counter table (as suggested in the TRIGGER based solution) and track total number of tuples inserted and deleted in a transaction (and all the committed subtransactions). We then execute a single UPDATE at the end of the transaction. With HOT, updating the counter table should not be a big pain since all these updates can potentially be HOT updates. Also since the update of the counter table happens at the commit time, other transactions inserting/deleting from the same user table may need to wait for a very small period on the counter table tuple. This still doesn't solve the serializable transaction problem though. But I am sure we can figure out some solution for that case as well if we agree on the general approach. I am sure this must have been discussed before. So what are the objections If you are talking about automatically doing this for every table - I have an objection that the performance impact seems unwarranted against the gain. We are still talking about every insert or update updating some counter table, with the only mitigating factor being that the trigger would be coded deeper into PostgreSQL theoretically making it cheaper? You can already today create a trigger on insert that will append to a summary table of some sort, whose only purpose is to maintain counts. At the simplest, it is as somebody else suggested where you might have the other table only store the primary keys with foreign key references back to the main table for handling deletes and updates. Storing transaction numbers and such might allow the data to be reduced in terms of size (and therefore elapsed time to scan), but it seems complex. If this really is a problem that must be solved - I prefer the suggestion from the past of keeping track of live rows per block for a certain transaction range, and any that fall within this range can check off this block quickly with an exact count, then the exceptional blocks (the ones being changed) can be scanned to be sure. But, it's still pretty complicated to implement right and maintain, for what is probably limited gain. I don't personally buy into the need to do exact count(*) on a whole table quickly. I know people ask for it - but I find these same people either confused, or trying to use this functionality to accomplish some other end, under the assumption that because they can get counts faster from other databases, therefore PostgreSQL should do it as well. I sometimes wonder whether these people would even notice if PostgreSQL translated count(*) on the whole table to query reltuples. :-) Cheers, mark -- Mark Mielke [EMAIL PROTECTED] -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: TODO-list on wiki (was: TODO update about SQLSTATE to PGconn)
On Wed, Mar 12, 2008 at 3:32 PM, Bruce Momjian [EMAIL PROTECTED] wrote: Tom Lane wrote: This seems to me to be nonsense. You've never maintained the back-branch versions of the TODO list, so they're out of date anyway --- ie, they don't account for problems discovered post-release. It is a best effort with our limited resources. Should we outsource it? It is user-facing :-p -- Dave Page EnterpriseDB UK Ltd: http://www.enterprisedb.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO-list on wiki (was: TODO update about SQLSTATE to PGconn)
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Actually, the part of the current process that a wiki would fail to reproduce is the emails that Bruce sends out about TODO changes. Do we still want those, and if so what would we do about it? Magnus said you can subscribe to a changes email, or get an RSS feed of changes. Right, so people who were specifically interested in following TODO changes could still track it. But the current system informs the original submitter of a problem that it's been put on TODO, and that wouldn't happen anymore. Do we care? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO-list on wiki (was: TODO update about SQLSTATE to PGconn)
Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: On Wed, Mar 12, 2008 at 11:03:13AM -0400, Bruce Momjian wrote: We need it to do a few things: Actually, the part of the current process that a wiki would fail to reproduce is the emails that Bruce sends out about TODO changes. Do we still want those, and if so what would we do about it? Magnus said you can subscribe to a changes email, or get an RSS feed of changes. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] count(*) performance improvement ideas
On Wed, Mar 12, 2008 at 9:14 PM, Mark Mielke [EMAIL PROTECTED] wrote: If you are talking about automatically doing this for every table - I have an objection that the performance impact seems unwarranted against the gain. We are still talking about every insert or update updating some counter table, with the only mitigating factor being that the trigger would be coded deeper into PostgreSQL theoretically making it cheaper? No, I am not suggesting that. If you read proposal carefully, its one UPDATE per transaction. With HOT, I am hoping that the counter table may be completely cached in memory and won't bloat much. Also, we can always have a GUC (like pgstats) to control the overhead. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO-list on wiki (was: TODO update about SQLSTATE to PGconn)
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Actually, the part of the current process that a wiki would fail to reproduce is the emails that Bruce sends out about TODO changes. Do we still want those, and if so what would we do about it? Magnus said you can subscribe to a changes email, or get an RSS feed of changes. Right, so people who were specifically interested in following TODO changes could still track it. But the current system informs the original submitter of a problem that it's been put on TODO, and that wouldn't happen anymore. Do we care? I would still send out an email for TODO additions based on the original email. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: TODO-list on wiki (was: TODO update about SQLSTATE to PGconn)
Dave Page wrote: On Wed, Mar 12, 2008 at 3:32 PM, Bruce Momjian [EMAIL PROTECTED] wrote: Tom Lane wrote: This seems to me to be nonsense. You've never maintained the back-branch versions of the TODO list, so they're out of date anyway --- ie, they don't account for problems discovered post-release. It is a best effort with our limited resources. Should we outsource it? It is user-facing :-p If it was a service we could use for free, we could consider it. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] transaction support in pl/slql
i have a request ?? Is there any intension of expanding Transaction support into PL/SQL. this would simplify GUI/client code to know the if a function failed it roll back the transaction. right now i have to pass back some kind error message that i look for to see if the transaction failed to either run the commit or rollback command i know about raise exception within sub blocks but i want a way to roll back to the starting transaction. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] count(*) performance improvement ideas
Pavan Deolasee wrote: On Wed, Mar 12, 2008 at 9:14 PM, Mark Mielke [EMAIL PROTECTED] wrote: If you are talking about automatically doing this for every table - I have an objection that the performance impact seems unwarranted against the gain. We are still talking about every insert or update updating some counter table, with the only mitigating factor being that the trigger would be coded deeper into PostgreSQL theoretically making it cheaper? No, I am not suggesting that. If you read proposal carefully, its one UPDATE per transaction. With HOT, I am hoping that the counter table may be completely cached in memory and won't bloat much. Also, we can always have a GUC (like pgstats) to control the overhead. Fine - once per transaction instead of once per insert. Still, if there is overhead to this (updating a secondary summary table), does it really make sense to have it for every table? Most of my tables do not require count(*) on the whole table (actually - none of them do). For the same reason as I don't want oid, I don't think I would want fast count capabilities to impact my regular queries. Again, I don't think count(*) on the whole table is a particularly useful case. count(*) on particular subsets of the data may be, but of the whole table? If you can make a secondary summary table to be used for count(*) optional, great. If using HOT makes the secondary table more efficient, great. Cheers, mark -- Mark Mielke [EMAIL PROTECTED]
Re: [HACKERS] Re: TODO-list on wiki (was: TODO update about SQLSTATE to PGconn)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 12 Mar 2008 12:10:27 -0400 (EDT) Bruce Momjian [EMAIL PROTECTED] wrote: It is a best effort with our limited resources. Should we outsource it? It is user-facing :-p If it was a service we could use for free, we could consider it. https://launchpad.net/ http://www.sourceforge.net/ Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL political pundit | Mocker of Dolphins -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFH2AW0ATb/zqfZUUQRAkLUAKCKbvUEQtG/KT8XrhrkB0/hdHUXqQCgnDTp VTfGZSIFftAJqo3PSP4XOhE= =N3tJ -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] count(*) performance improvement ideas
Mark Mielke [EMAIL PROTECTED] writes: Fine - once per transaction instead of once per insert. Still, if there is overhead to this (updating a secondary summary table), does it really make sense to have it for every table? We certainly wouldn't accept a patch that imposed this overhead on every table. One of the advantages of framing it as an explicit set of triggers is that then you have a natural way of indicating which table(s) you want the feature for (and are willing to pay the overhead to get it). regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Idea about sql command create table like
David BOURIAUD wrote: Could it be possible to have a syntax that would allow to use create table like to rename fields as well, for example : create table any_lib like (some_lib including constraints) with names (any_code, any_text); What about coding a replace-stuff-function in pl/* ? -- Euler Taveira de Oliveira http://www.timbira.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: TODO-list on wiki (was: TODO update about SQLSTATE to PGconn)
Joshua D. Drake wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 12 Mar 2008 12:39:06 -0400 (EDT) Bruce Momjian [EMAIL PROTECTED] wrote: If it was a service we could use for free, we could consider it. https://launchpad.net/ http://www.sourceforge.net/ Those are a step backward --- they don't have the functionality we currently have, but have more functionality in other areas. First it was a joke. However I would be curious to know which features you think they don't have. If nothing else Launchpad is a customer and I can give them some feedback. Probably the biggest missing feature for the TODO is the ability to summarize, group into labeled sections and subsections, and the ability to move items around, with URL links to more detail. Effectively that is all the TODO list is. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: TODO-list on wiki (was: TODO update about SQLSTATE to PGconn)
Joshua D. Drake wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 12 Mar 2008 12:10:27 -0400 (EDT) Bruce Momjian [EMAIL PROTECTED] wrote: It is a best effort with our limited resources. Should we outsource it? It is user-facing :-p If it was a service we could use for free, we could consider it. https://launchpad.net/ http://www.sourceforge.net/ Those are a step backward --- they don't have the functionality we currently have, but have more functionality in other areas. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: TODO-list on wiki (was: TODO update about SQLSTATE to PGconn)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 12 Mar 2008 12:39:06 -0400 (EDT) Bruce Momjian [EMAIL PROTECTED] wrote: If it was a service we could use for free, we could consider it. https://launchpad.net/ http://www.sourceforge.net/ Those are a step backward --- they don't have the functionality we currently have, but have more functionality in other areas. First it was a joke. However I would be curious to know which features you think they don't have. If nothing else Launchpad is a customer and I can give them some feedback. Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL political pundit | Mocker of Dolphins -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD4DBQFH2AkAATb/zqfZUUQRAg99AJjaI+Ozx+LPRPezEpXLEgVeZyrXAJ962Jsa khsonJQJfNYzmoYbOTHEsQ== =i49i -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] count(*) performance improvement ideas
On Wed, Mar 12, 2008 at 9:53 PM, Mark Mielke [EMAIL PROTECTED] wrote: Fine - once per transaction instead of once per insert. Still, if there is overhead to this (updating a secondary summary table), does it really make sense to have it for every table? Most of my tables do not require count(*) on the whole table (actually - none of them do). For the same reason as I don't want oid, I don't think I would want fast count capabilities to impact my regular queries. Again, I don't think count(*) on the whole table is a particularly useful case. count(*) on particular subsets of the data may be, but of the whole table? ISTM that you are complaining because we never had an *fast* count(*) and adding that now comes at a cost. Had it been there from day one with the same overhead as we are talking about now, nobody would have complained :-) Anyways, your point is taken and it would be great if can make it configurable, if not table level then at least globally. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: TODO-list on wiki (was: TODO update about SQLSTATE to PGconn)
Bruce Momjian wrote: Probably the biggest missing feature for the TODO is the ability to summarize, group into labeled sections and subsections, and the ability to move items around, with URL links to more detail. Effectively that is all the TODO list is. Oh, like a Wiki page. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] count(*) performance improvement ideas
Pavan Deolasee wrote: On Wed, Mar 12, 2008 at 9:53 PM, Mark Mielke [EMAIL PROTECTED] wrote: Fine - once per transaction instead of once per insert. Still, if there is overhead to this (updating a secondary summary table), does it really make sense to have it for every table? Most of my tables do not require count(*) on the whole table (actually - none of them do). For the same reason as I don't want oid, I don't think I would want fast count capabilities to impact my regular queries. Again, I don't think count(*) on the whole table is a particularly useful case. count(*) on particular subsets of the data may be, but of the whole table? ISTM that you are complaining because we never had an *fast* count(*) and adding that now comes at a cost. Had it been there from day one with the same overhead as we are talking about now, nobody would have complained :-) Quite possibly - but, similarly, if PostgreSQL was generally slower due to such things, I might not have chosen PostgreSQL as my favoured db. :-) I think there is a reason PostgreSQL doesn't come with a fast count(*), and it's that the developers leading up to this point shared the opinion that this feature was not critical. I somewhat disagree about my personal reaction, though. I complain about many things, even configurable things, such as LISTEN/NOTIFY. I occasionally wish I had such a function, but given it's implementation, I would choose to use a non-PostgreSQL mechanism for implementation if available. Luckily, it's also only suffer the performance cost if you CHOOSE to use it. Seems like a historically valuable trend. Anyways, your point is taken and it would be great if can make it configurable, if not table level then at least globally If table level, I might even try it out. If global level, I will not be trying it out. I'm only one person - but perhaps this will help point you in a direction favourable to many? If you are REALLY feeling clever, the queries that I would find it to benefit the MOST on, would include WHERE conditions. Just like indexes with WHERE conditions. If you get to this point, I think you are reaching something that will have far more universal benefit to existing applications. CREATE COUNT INDEX blah ON blah WHERE ... I would use this in many places. Cheers, mark -- Mark Mielke [EMAIL PROTECTED]
[HACKERS] Move the developers wiki?
Hackers, As you may have noticed (http://archives.postgresql.org/pgsql-general/2008-03/msg00470.php) we now have a wiki at wiki.postgresql.org that is replacing techdocs which unfortunately never worked quite as we had hoped in it's current form. It has been suggested by members of the web team that as the new site has been setup on a dedicated VM and has been properly integrated with the rest of our online infrastructure (unlike the current developer wiki which is basically a quick 'n' dirty install on an already over-utilised VM), that we should consider moving the developer wiki into a dedicated area on it. Any thoughts on whether thats a good or bad idea? Any objections? -- Dave Page EnterpriseDB UK Ltd: http://www.enterprisedb.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Ideas input sought for this year's SOC page
Hackers, We need to update the SoC page: http://www.postgresql.org/developer/summerofcode ... with new ideas and projects appropriate for PostgreSQL 8.4/8.5. Please make suggestions. Thanks! --Josh -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] transaction support in pl/slql
Justin wrote: i have a request ?? Is there any intension of expanding Transaction support into PL/SQL. this would simplify GUI/client code to know the if a function failed it roll back the transaction. right now i have to pass back some kind error message that i look for to see if the transaction failed to See Autonomous Transactions in the TODO list. --Josh -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL feature requests
FYI, we decided we didn't want this additional capability. --- Florian G. Pflug wrote: Kevin Grittner wrote: On Thu, Aug 23, 2007 at 3:01 PM, in message [EMAIL PROTECTED], Tom Lane [EMAIL PROTECTED] wrote: The only argument I've heard that carries much weight with me is that it eases porting from other DBMS's that allow this. Are there any others besides Oracle? select * from (select f1 from t) In Sybase: com.sybase.jdbc2.jdbc.SybSQLException: The derived table expression is missing a correlation name. Check derived table syntax in the Reference Manual. Error code: 11753 SQL state: Z The really funny thing is that pgsql, mysql and at least sybase *explicitly* dissallow the no-alias case. Which shows that .) This seems to be common source of confusion and errors. .) Aliasless-Subqueries wouldn't lead to ambigous grammras in those databases. Otherwise, you'd expect to get some more generic syntax error, and not the very explicit No alias, but expected one. I agree with Tom - knowing *why* the standard committee disallows that syntax - and why everybody except oracle chose to agree with it would be quite interesting. greetings, Florian Pflug ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [pgsql-www] Move the developers wiki?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 12 Mar 2008 17:16:33 + Dave Page [EMAIL PROTECTED] wrote: Hackers, As you may have noticed (http://archives.postgresql.org/pgsql-general/2008-03/msg00470.php) we now have a wiki at wiki.postgresql.org that is replacing techdocs which unfortunately never worked quite as we had hoped in it's current form. It has been suggested by members of the web team that as the new site has been setup on a dedicated VM and has been properly integrated with the rest of our online infrastructure (unlike the current developer wiki which is basically a quick 'n' dirty install on an already over-utilised VM), that we should consider moving the developer wiki into a dedicated area on it. Any thoughts on whether thats a good or bad idea? Any objections? Not an objection but an observation. The developer wiki seems to be a catchall for contributors to put various stuff on. There is patch status, advocacy policies, release policies etc... Based on your description of wiki.postgresql.org it is supposed to be a replacement for techdocs. To me, they have different purposes. Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL political pundit | Mocker of Dolphins -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFH2BIhATb/zqfZUUQRAr/WAJ9ffj8pvozqRAwALu+nxjOlSw5bkACfdQcH ikknupDqWY/0B3MhcAXhscQ= =E1Cf -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] count(*) performance improvement ideas
Tom Lane [EMAIL PROTECTED] writes: Mark Mielke [EMAIL PROTECTED] writes: Fine - once per transaction instead of once per insert. Still, if there is overhead to this (updating a secondary summary table), does it really make sense to have it for every table? We certainly wouldn't accept a patch that imposed this overhead on every table. If you look at this at the right angle it's actually a degenerate case of materialized views. I think think it would be more useful to approach it from that direction even if it only supported a very limited set of expressions. In an ideal world I would love to be able to do something like: CREATE MATERIALIZED VIEW foo AS (select count(*) from bar) WITH INCREMENTAL UPDATES; and have that automatically create both a heap to store the count and another to store the incremental changes. To do this would require some magic to know what incremental changes means for each aggregate where it's meaningful though. Then it would require some magic in the optimizer to recognize when piece of the query can be satisfied by a materialized view. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [pgsql-www] Move the developers wiki?
On Wed, Mar 12, 2008 at 10:25:53AM -0700, Joshua D. Drake wrote: On Wed, 12 Mar 2008 17:16:33 + Dave Page [EMAIL PROTECTED] wrote: Hackers, As you may have noticed (http://archives.postgresql.org/pgsql-general/2008-03/msg00470.php) we now have a wiki at wiki.postgresql.org that is replacing techdocs which unfortunately never worked quite as we had hoped in it's current form. It has been suggested by members of the web team that as the new site has been setup on a dedicated VM and has been properly integrated with the rest of our online infrastructure (unlike the current developer wiki which is basically a quick 'n' dirty install on an already over-utilised VM), that we should consider moving the developer wiki into a dedicated area on it. Any thoughts on whether thats a good or bad idea? Any objections? Not an objection but an observation. The developer wiki seems to be a catchall for contributors to put various stuff on. There is patch status, advocacy policies, release policies etc... Based on your description of wiki.postgresql.org it is supposed to be a replacement for techdocs. To me, they have different purposes. Well, we could just add a link to a subsection that does those things, no? //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [pgsql-www] Move the developers wiki?
Dave, Any thoughts on whether thats a good or bad idea? Any objections? Well, other than not breaking developer.postgresql.org links, sounds fine to me. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [pgsql-www] Move the developers wiki?
Joshua D. Drake wrote: It has been suggested by members of the web team that as the new site has been setup on a dedicated VM and has been properly integrated with the rest of our online infrastructure (unlike the current developer wiki which is basically a quick 'n' dirty install on an already over-utilised VM), that we should consider moving the developer wiki into a dedicated area on it. Not an objection but an observation. The developer wiki seems to be a catchall for contributors to put various stuff on. There is patch status, advocacy policies, release policies etc... The developer's wiki was the developer's wiki until other people noticed that they had no wiki so they started using the developer's wiki as a catchall place (hey, we advocates have nowhere to wiki on; can we use devel's wiki?). Now that we have a real wiki it makes sense to create separate areas on it for each team. That said, we have links to the developer wiki all over the web. So +1 on moving the developer's wiki to the other one -- but only if the original URLs continue to work (redirecting to the new site). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [pgsql-www] Move the developers wiki?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 12 Mar 2008 18:43:47 +0100 Magnus Hagander [EMAIL PROTECTED] wrote: Based on your description of wiki.postgresql.org it is supposed to be a replacement for techdocs. To me, they have different purposes. Well, we could just add a link to a subsection that does those things, no? Like I said just observation. I like things compartmentalized but there is also certainly an argument for redundant resources etc... Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL political pundit | Mocker of Dolphins -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFH2Bo0ATb/zqfZUUQRAl9/AKCPdHUt+Deut+rvBPPIDUnWAMW1rgCgqkCb pdk7qXtIyAmZqd00cNeJn1k= =3HeX -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: TODO-list on wiki (was: TODO update about SQLSTATE to PGconn)
On Wed, Mar 12, 2008 at 11:32:16AM -0400, Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Personally I think it would be just fine if we had only the wiki copy and forgot about shipping it in tarballs. The problem with not shipping the TODO file at all is that TODO gives users a list of all known bugs/missing features in that major release. This seems to me to be nonsense. You've never maintained the back-branch versions of the TODO list, so they're out of date anyway --- ie, they don't account for problems discovered post-release. It is a best effort with our limited resources. In any case I've always thought that the TODO was developer-oriented documentation, not something users would read. If there's a shortcoming in a feature, it ought to be documented in the SGML manual. It typically isn't, except for major issues, again due to lack of resources. I think you will have to search for a long time to find anybody who actually uses it like that. I'm willing to bet that well over 95% of the people who read the TODO only read it on the website. (potentially excluding the actual patch-contributors, but those aren't included in your argument anyway) //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [pgsql-www] Move the developers wiki?
Joshua D. Drake wrote: Like I said just observation. I like things compartmentalized but there is also certainly an argument for redundant resources etc... Are you saying that you want to keep postgresqldocs.org around? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [pgsql-www] Move the developers wiki?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 12 Mar 2008 15:08:26 -0300 Alvaro Herrera [EMAIL PROTECTED] wrote: Joshua D. Drake wrote: Like I said just observation. I like things compartmentalized but there is also certainly an argument for redundant resources etc... Are you saying that you want to keep postgresqldocs.org around? That actually isn't my decision. Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL political pundit | Mocker of Dolphins -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFH2BycATb/zqfZUUQRAs2kAJ91xt4zNB+UuPYAIEulzoGN2npfjwCfZ8vj QT2+1/GssIaueGFMwGlEnc4= =gBt4 -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: TODO-list on wiki (was: TODO update about SQLSTATE to PGconn)
Magnus Hagander wrote: On Wed, Mar 12, 2008 at 11:32:16AM -0400, Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Personally I think it would be just fine if we had only the wiki copy and forgot about shipping it in tarballs. The problem with not shipping the TODO file at all is that TODO gives users a list of all known bugs/missing features in that major release. This seems to me to be nonsense. You've never maintained the back-branch versions of the TODO list, so they're out of date anyway --- ie, they don't account for problems discovered post-release. It is a best effort with our limited resources. In any case I've always thought that the TODO was developer-oriented documentation, not something users would read. If there's a shortcoming in a feature, it ought to be documented in the SGML manual. It typically isn't, except for major issues, again due to lack of resources. I think you will have to search for a long time to find anybody who actually uses it like that. I'm willing to bet that well over 95% of the people who read the TODO only read it on the website. (potentially excluding the actual patch-contributors, but those aren't included in your argument anyway) We can always remove it from the tarball and see if anyone complains. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [pgsql-www] Move the developers wiki?
On Wed, 12 Mar 2008, Alvaro Herrera wrote: That said, we have links to the developer wiki all over the web. So +1 on moving the developer's wiki to the other one -- but only if the original URLs continue to work (redirecting to the new site). While the default URL formatting on the new wiki is different, it still supports the same one used on the developer's wiki. You can hit the front page with http://wiki.postgresql.org/index.php/Main_Page same way the URL for the front of the developer's wiki is http://developer.postgresql.org/index.php/Main_Page If all the pages get moved over with the same names (which doesn't seem like it would be a problem), just changing developer.postgresql.org to point to wiki.postgresql would seem to be sufficient to keep any existing links working. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: TODO-list on wiki (was: TODO update about SQLSTATE to PGconn)
On Wed, Mar 12, 2008 at 02:10:08PM -0400, Bruce Momjian wrote: Magnus Hagander wrote: On Wed, Mar 12, 2008 at 11:32:16AM -0400, Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Personally I think it would be just fine if we had only the wiki copy and forgot about shipping it in tarballs. The problem with not shipping the TODO file at all is that TODO gives users a list of all known bugs/missing features in that major release. This seems to me to be nonsense. You've never maintained the back-branch versions of the TODO list, so they're out of date anyway --- ie, they don't account for problems discovered post-release. It is a best effort with our limited resources. In any case I've always thought that the TODO was developer-oriented documentation, not something users would read. If there's a shortcoming in a feature, it ought to be documented in the SGML manual. It typically isn't, except for major issues, again due to lack of resources. I think you will have to search for a long time to find anybody who actually uses it like that. I'm willing to bet that well over 95% of the people who read the TODO only read it on the website. (potentially excluding the actual patch-contributors, but those aren't included in your argument anyway) We can always remove it from the tarball and see if anyone complains. I think that's a reasonable default. If we do get complains, we'll figure a way to get it back. It's not hard to get info out of a wiki. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [pgsql-www] Move the developers wiki?
On Wed, 12 Mar 2008, Alvaro Herrera wrote: Are you saying that you want to keep postgresqldocs.org around? One thing at a time. The new PG wiki looks great, seems like the next task is to consider merging the developer's content into there. I was hoping to propose a plan for merging work done on postgresqldocs.org into the new infrastructure in a couple of days, after things settle down a bit. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] count(*) performance improvement ideas
Gregory Stark wrote: In an ideal world I would love to be able to do something like: CREATE MATERIALIZED VIEW foo AS (select count(*) from bar) WITH INCREMENTAL UPDATES; and have that automatically create both a heap to store the count and another to store the incremental changes. To do this would require some magic to know what incremental changes means for each aggregate where it's meaningful though. Then it would require some magic in the optimizer to recognize when piece of the query can be satisfied by a materialized view. Hehe... +5 if you think it can be done in the next 12 - 24 months. :-) Cheers, mark -- Mark Mielke [EMAIL PROTECTED] -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Ideas input sought for this year's SOC page
On Wed, Mar 12, 2008 at 1:19 PM, Josh Berkus [EMAIL PROTECTED] wrote: Please make suggestions. Thanks! Let me look at what's new on the TODO, but I'd also like to see some of the last SoC projects improved, like xlogdump/xlogviewer and some of the standby stuff. We should also ask Simon if he has anything he'd like to see done, because he usually has some pretty good ideas for projects in the realm of SoC. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837 | http://www.enterprisedb.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [pgsql-www] Move the developers wiki?
Greg Smith [EMAIL PROTECTED] writes: If all the pages get moved over with the same names (which doesn't seem like it would be a problem), just changing developer.postgresql.org to point to wiki.postgresql would seem to be sufficient to keep any existing links working. That's not exactly what I'd call keeping existing links working. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Ideas input sought for this year's SOC page
On Wed, Mar 12, 2008 at 2:33 PM, Jonah H. Harris [EMAIL PROTECTED] wrote: On Wed, Mar 12, 2008 at 1:19 PM, Josh Berkus [EMAIL PROTECTED] wrote: Please make suggestions. Thanks! Let me look at what's new on the TODO, but I'd also like to see some of the last SoC projects improved, like xlogdump/xlogviewer and some of the standby stuff. We should also ask Simon if he has anything he'd like to see done, because he usually has some pretty good ideas for projects in the realm of SoC. +1 the read only standby would be what i'd most like to see. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [pgsql-www] Move the developers wiki?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 12 Mar 2008 14:51:06 -0400 Tom Lane [EMAIL PROTECTED] wrote: Greg Smith [EMAIL PROTECTED] writes: If all the pages get moved over with the same names (which doesn't seem like it would be a problem), just changing developer.postgresql.org to point to wiki.postgresql would seem to be sufficient to keep any existing links working. That's not exactly what I'd call keeping existing links working. It is if we point developer. to wiki. Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL political pundit | Mocker of Dolphins -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFH2Cb6ATb/zqfZUUQRAkf+AJsEYgfwL2xrJX/oY6airl9qfIkHMACfbH+S B93fNw8mZZsx0FmJ/AF3di4= =qaHx -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Move the developers wiki?
Dave Page wrote: As you may have noticed (http://archives.postgresql.org/pgsql-general/2008-03/msg00470.php) we now have a wiki at wiki.postgresql.org that is replacing techdocs Victory! :) It has been suggested by members of the web team that as the new site has been setup on a dedicated VM and has been properly integrated with the rest of our online infrastructure (unlike the current developer wiki which is basically a quick 'n' dirty install on an already over-utilised VM), that we should consider moving the developer wiki into a dedicated area on it. Yes, please. One wiki should be enough. A bit of accidental crossover between users, developers, and other groups could even be of advantage. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [pgsql-www] Move the developers wiki?
Joshua D. Drake [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] wrote: That's not exactly what I'd call keeping existing links working. It is if we point developer. to wiki. Will that break anything else (ie, is that webserver serving anything but the wiki)? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [pgsql-www] Move the developers wiki?
On Wed, Mar 12, 2008 at 7:09 PM, Tom Lane [EMAIL PROTECTED] wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] wrote: That's not exactly what I'd call keeping existing links working. It is if we point developer. to wiki. Will that break anything else (ie, is that webserver serving anything but the wiki)? No, thats one of the reasons why I'd like to move developer - this one is not just on a dedicated apache instance, but a dedicated VM. I don't see any reason why what Greg Josh are suggesting couldn't work - it's roughly what I had in mind anyway, except that we'd have to use a URL rewrite on developer to get it to redirect requests to wiki, as that hostname is used for other things so hijacking DNS doesn't sound sensible. -- Dave Page EnterpriseDB UK Ltd: http://www.enterprisedb.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MSVC build system
Added to TODO for Win32: o Convert MSVC build system to remove most batch files http://archives.postgresql.org/pgsql-hackers/2007-08/msg00961.php --- Andrew Dunstan wrote: I want to overhaul the MSVC build system somewhat and want to discuss my plans. The first thing I want to do is get rid of at least most of the .bat files in it altogether and make a single coherent perl-based system. The reasons are: . the XP_EXIT_FIX hack we built in to cater for the XP command processor doesn't actually work on XP - if it's turned on it fails too often (e.g. on build warnings) and if it's turned off if doesn't fail often enough (e.g. on build errors). . it's unnecessarily complex. For example, the vcregress.bat file calls a perl script to create a temporary .bat file which it then calls. . we need perl anyway, so why not write it all in perl? I venture to suggest that the majority of our developers are more at home in perl than in the arcane syntax of the Windows command processor, so we might be less reliant on a tiny group of people (Magnus and Dave, basically) to maintain the build system.Writing it all in perl would give us a chance to make the whole system more coherent and data driven. . the .bat files have serious (from my POV) limitations / errors anyway. e.g. vcregress.bat has a hardcoded temp port (a definite nono for a machine running buildfarm and a departure from the regression make file) and also does not at all honor the MAX_CONNECTIONS environment setting. It also hardcodes the use of the parallel schedule for installcheck, unlike the same named test on Unix. In the longer run I want to make the whole system more data driven, so that it's comparatively easy for someone to add stuff. The whole thing needs proper documentation for developers too. Right now, discovering where to add a new installation file, for example, requires you to dig into the code which can be rather bewildering, I suspect. Anyway, the first part of what I want to achieve is to replace the build.bat and vcregress.bat files with perl equivalents, with the errors/omissions fixed. Along the way, getregress.pl would become redundant. Apart from fixing the issue with using the systems dir command rather than using File::Find, which I will revisit, I think that's all I would do now, given how close we are to Beta. The rest can wait until after we release. I should add that some at least of this is my fault - Magnus showed me at least parts of the system and asked my advice, and I didn't pay close enough attention. It was only when (much later) I started to try to use it seriously that I understood how much work was still needed. Thoughts? cheers andrew ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why is there a tsquery data type?
Added to TODO: * Consider a special data type for regular expressions http://archives.postgresql.org/pgsql-hackers/2007-08/msg01067.php --- Tom Lane wrote: Ron Mayer [EMAIL PROTECTED] writes: Could similar logic argue that we'd want special types for regular expressions too? That seems quite parallel to the tsquery type to me. Yeah, it certainly seems like something we might want to consider in future --- we could get rid of that klugy cache for compiled regexps, for one thing. An implicit cast from text to the regexp type would cover backwards compatibility issues, I think. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SET TRANSACTION not compliant with SQL:2003
Tom's comment on this from the patch queue is that the standard assume autocommit off, which affect some of your analysis below. --- Simon Riggs wrote: The SQL:2003 standard definition of SET TRANSACTION differs in major ways from PostgreSQL's, which produces some interesting behaviour. We currently claim conformance, though this is not accurate. ... SQL2003 If a set transaction statement that does not specify LOCAL is executed, then Case: i) If an SQL-transaction is currently active, then an exception condition is raised: invalid transaction state ? active SQL-transaction. /SQL2003 ... SQL2003 Case: a) If LOCAL is not specified, then let TXN be the next SQL-transaction for the SQL-agent. b) Otherwise, let TXN be the branch of the active SQL-transaction at the current SQL-connection. /SQL2003 The standard behaviour is that SET TRANSACTION defines the mode used in the *next* transaction, not the current one. We should allow this meaning, since programs written to spec will act differently with the current implementation. We currently only change the *current* transaction. Executing within the current transaction is supposed to throw an error; that's probably too late to change, but the standard does give some clues for other errors. Proposed changes: 1. Solo SET TRANSACTION statements produce no WARNING, nor do anything. This isn't the way the SQL:2003 standard specifies it should work. We should take the values from SET TRANSACTION and apply them to the *next* transaction: - these will apply to next TXN, unless specifically overridden during the START TRANSACTION command - these values apply for one transaction only, after which we revert back to the session default. 2. Duplicate calls to SET TRANSACTION are allowed within a transaction. = Should be ERROR: Transaction mode already set. postgres=# begin; BEGIN postgres=# set transaction read only; SET postgres=# set transaction read only; SET postgres=# commit; COMMIT 3. Multiple conflicting calls to SET TRANSACTION are allowed within a transaction. = Should be ERROR: Transaction mode already set. postgres=# begin; BEGIN postgres=# set transaction isolation level read committed; SET postgres=# set transaction isolation level serializable; SET postgres=# commit; COMMIT 4. SET TRANSACTION can be called after a SAVEPOINT, i.e. it can be called in a subtransaction. = Should be ERROR: SET TRANSACTION must not be called in a subtransaction. (Calling SET TRANSACTION ISOLATION LEVEL already throws this error, so change should be small) 5. The standard uses the keyword LOCAL like this: SET LOCAL TRANSACTION ... which in this context means the part of a distributed (two-phased) commit on this database. We should accept, but ignore this keyword. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Low hanging fruit in lazy-XID-assignment patch?
Added to TODO: * Expire published xmin for read-only and idle transactions http://archives.postgresql.org/pgsql-hackers/2007-09/msg00343.php --- Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: As a fallout of this work that I haven't seen made explicit, a session opening a transaction and then sitting around doing nothing will not cause as many problems as it used to -- for example it won't cause VACUUM to be unable to clean up dead rows. Is this correct? Yeah, if you just issue BEGIN and then sit, you won't have acquired either an xid or an xmin, so you don't create a VACUUM problem anymore. If you issue BEGIN, then SELECT, then sit, you'll be publishing an xmin but not an xid, so at that point you become a problem for VACUUM. However, internally you don't have any live snapshots (if you're in READ COMMITTED mode), so eventually we could have you stop publishing an xmin too. That's something for 8.4 though. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Ideas input sought for this year's SOC page
Josh Berkus wrote: Hackers, We need to update the SoC page: http://www.postgresql.org/developer/summerofcode ... with new ideas and projects appropriate for PostgreSQL 8.4/8.5. Please make suggestions. Thanks! --Josh On last year's summer of code page, http://www.postgresql.org/developer/summerofcode There is the following item: * DDL Functions*: Create a SQL-callable function capable of generating DDL scripts for objects within the database. Is there more information available on this? Has anything like this been implemented? I believe SQL standard does not specify such functions. What functions and parameters? What other functionality is required? I have a usable implementation of this functionality, but it needs some more work before it is released. Here is some info: http://developer.postgresql.org/index.php/DDL_Functions Currently it is a part of larger utility package, but I would very much like release it as a stand alone project on pgfoundry. The plan is is to review current functions and move them to a separate namespace before release. This is a good time to make API changes. Suggestions are much appreciated. Thank you Z. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Ideas input sought for this year's SOC page
On Wed, 12 Mar 2008, Josh Berkus wrote: Hackers, We need to update the SoC page: http://www.postgresql.org/developer/summerofcode ... with new ideas and projects appropriate for PostgreSQL 8.4/8.5. Please make suggestions. Thanks! Several our TODO items (http://www.sai.msu.su/~megera/wiki/TODO) can be good gSOC project, for example, research on picksplit algorithm for intarray, ltree, text search. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Ideas input sought for this year's SOC page
On Wed, Mar 12, 2008 at 7:52 PM, Merlin Moncure [EMAIL PROTECTED] wrote: the read only standby would be what i'd most like to see. +1 for this one. Working on setting up a benchfarm could be a good idea too. -- Guillaume -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Ideas input sought for this year's SOC page
Josh Berkus wrote: We need to update the SoC page: http://www.postgresql.org/developer/summerofcode ... with new ideas and projects appropriate for PostgreSQL 8.4/8.5. Many of these items could be suitable: http://wiki.postgresql.org/wiki/XML_Todo -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Maybe some more low-hanging fruit in the latestCompletedXid patch.
Is this a TODO? --- Florian G. Pflug wrote: Tom Lane wrote: Florian G. Pflug [EMAIL PROTECTED] writes: Currently, we do not assume that either the childXids array, nor the xid cache in the proc array are sorted by ascending xid order. I believe that we could simplify the code, further reduce the locking requirements, and enabled a transaction to de-overflow it's xid cache if we assume that those arrays are in ascending xid order. de-overflowing the cache sounds completely unsafe, as other backends need that state to determine whether they need to look into pg_subtrans. We'd only de-overflow if we abort *all* xids that are missing from the xid cache. And only after marking them as aborted in the clog. If someone concurrently checks for an overflow, and already sees the new (non-overflowed) state, than he'll assume the xid is not running if he hasn't found it in the array. Which is correct - we just aborted it. Plus, removing the exclusive lock doesn't depend on de-overflowing. It's just something that seems rather easy to do once the subxid handling is in a state that allows concurrent removal of entries. If it turns out that it's not that easy, than I'll just drop the idea again. I still don't believe you can avoid taking exclusive lock, either; your argument here did not address latestCompletedXid. Sorry, not addressing latestCompletedXid was an oversight :-(. My point is the we only *need* to advance latestCompletedXid on COMMITS. We do so for aborts only to avoid running with unnecessarily low xmins after a transaction ABORT. That corner case can only happen after a toplevel ABORT, though - aborting subxacts cannot change the xmin, because the toplevel xact will have a lower xid than any of it's subtransactions anyway. We can therefore just remember the largest assigned xid for a given transaction, and update latestCompletedXid to that on toplevel commit or abort. That prevents that corner-case too, without updating latestCompletedXid during subxact abort. But the main point remains this: there is no evidence whatsoever that these code paths are sufficiently performance-critical to be worth speeding up by making the code more fragile. The gain will be less than that of the locking improvements done so far. It will be a win for heavy users of plpgsql BEGIN/END/EXCEPTION blocks, though I think. We'll also save some cycles in TransactionIdIsInProgress, because we can use a binary search, but that's just an added bonus. I'm currently trying to code up a patch, since it's easier to judge the correctness of actual code than that of a mere proposals. I'll do some benchmarking when the patch is done to see if it brings measurable benefits. greetings, Florian Pflug ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CSStorm occurred again by postgreSQL8.2
Is this a TODO? Tom's reply was: Nonsense. Main transaction exit also takes an exclusive lock, and is far more likely to be exercised in typical workloads than a subtransaction abort. In any case: there has still not been any evidence presented by anyone that optimizing XidCacheRemoveRunningXids will help one bit. Given the difficulty of measuring any benefit from the last couple of optimizations in this general area, I'm thinking that such evidence will be hard to come by. And we have got way more than enough on our plates already. Can we let go of this for 8.3, please? --- Simon Riggs wrote: On Wed, 2006-09-13 at 21:45 -0400, Tom Lane wrote: Anyway, given that there's this one nonobvious gotcha, there might be others. My recommendation is that we take this off the open-items list for 8.2 and revisit it in the 8.3 cycle when there's more time. Well, its still 8.3 just... As discussed in the other thread Final Thoughts for 8.3 on LWLocking and Scalability, XidCacheRemoveRunningXids() is now the only holder of an X lock during normal processing, so I would like to remove it. Here's how: Currently, we take the lock, remove the subxact and then shuffle down all the other subxactIds so that the subxact cache is contiguous. I propose that we simply zero out the subxact entry without re-arranging the cache; this will be atomic, so we need not acquire an X lock. We then increment ndeletedxids. When we enter a new subxact into the cache, if ndeletedxids 0 we scan the cache to find an InvalidTransactionId that we can use, then decrement ndeletedxids. So ndeletedxids is just a hint, not an absolute requirement. nxids then becomes the number of cache entries and never goes down until EOXact. The subxact cache is no longer in order, but then it doesn't need to be either. When we take a snapshot we will end up taking a copy of zeroed cache entries, so the snapshots will be slightly larger than previously. Though still no larger than the max. The size reduction was not so large as to make a significant difference across the whole array, so scalability is the main issue to resolve. The snapshots will be valid with no change, since InvalidTransactionId will never match against any recorded Xid. I would also like to make the size of the subxact cache configurable with a parameter such as subtransaction_cache_size = 64 (default), valid range 4-256. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Maybe some more low-hanging fruit in the latestCompletedXid patch.
Thanks for the feedback. --- Florian G. Pflug wrote: Bruce Momjian wrote: Is this a TODO? It's for from clear that avoing an exclusive ProcArray lock on subxact abort will bring a measurable performance benefit, so probably not. I've actually coded a prototype for this a few months ago, to check if it would bring any benefit at all, though I ran out of time before I had time to benchmark this, and I probably also lack the hardware for running high-concurrency tests. --- Florian G. Pflug wrote: Tom Lane wrote: Florian G. Pflug [EMAIL PROTECTED] writes: Currently, we do not assume that either the childXids array, nor the xid cache in the proc array are sorted by ascending xid order. I believe that we could simplify the code, further reduce the locking requirements, and enabled a transaction to de-overflow it's xid cache if we assume that those arrays are in ascending xid order. de-overflowing the cache sounds completely unsafe, as other backends need that state to determine whether they need to look into pg_subtrans. We'd only de-overflow if we abort *all* xids that are missing from the xid cache. And only after marking them as aborted in the clog. If someone concurrently checks for an overflow, and already sees the new (non-overflowed) state, than he'll assume the xid is not running if he hasn't found it in the array. Which is correct - we just aborted it. Plus, removing the exclusive lock doesn't depend on de-overflowing. It's just something that seems rather easy to do once the subxid handling is in a state that allows concurrent removal of entries. If it turns out that it's not that easy, than I'll just drop the idea again. I still don't believe you can avoid taking exclusive lock, either; your argument here did not address latestCompletedXid. Sorry, not addressing latestCompletedXid was an oversight :-(. My point is the we only *need* to advance latestCompletedXid on COMMITS. We do so for aborts only to avoid running with unnecessarily low xmins after a transaction ABORT. That corner case can only happen after a toplevel ABORT, though - aborting subxacts cannot change the xmin, because the toplevel xact will have a lower xid than any of it's subtransactions anyway. We can therefore just remember the largest assigned xid for a given transaction, and update latestCompletedXid to that on toplevel commit or abort. That prevents that corner-case too, without updating latestCompletedXid during subxact abort. But the main point remains this: there is no evidence whatsoever that these code paths are sufficiently performance-critical to be worth speeding up by making the code more fragile. The gain will be less than that of the locking improvements done so far. It will be a win for heavy users of plpgsql BEGIN/END/EXCEPTION blocks, though I think. We'll also save some cycles in TransactionIdIsInProgress, because we can use a binary search, but that's just an added bonus. I'm currently trying to code up a patch, since it's easier to judge the correctness of actual code than that of a mere proposals. I'll do some benchmarking when the patch is done to see if it brings measurable benefits. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Maybe some more low-hanging fruit in the latestCompletedXid patch.
Bruce Momjian wrote: Is this a TODO? It's for from clear that avoing an exclusive ProcArray lock on subxact abort will bring a measurable performance benefit, so probably not. I've actually coded a prototype for this a few months ago, to check if it would bring any benefit at all, though I ran out of time before I had time to benchmark this, and I probably also lack the hardware for running high-concurrency tests. --- Florian G. Pflug wrote: Tom Lane wrote: Florian G. Pflug [EMAIL PROTECTED] writes: Currently, we do not assume that either the childXids array, nor the xid cache in the proc array are sorted by ascending xid order. I believe that we could simplify the code, further reduce the locking requirements, and enabled a transaction to de-overflow it's xid cache if we assume that those arrays are in ascending xid order. de-overflowing the cache sounds completely unsafe, as other backends need that state to determine whether they need to look into pg_subtrans. We'd only de-overflow if we abort *all* xids that are missing from the xid cache. And only after marking them as aborted in the clog. If someone concurrently checks for an overflow, and already sees the new (non-overflowed) state, than he'll assume the xid is not running if he hasn't found it in the array. Which is correct - we just aborted it. Plus, removing the exclusive lock doesn't depend on de-overflowing. It's just something that seems rather easy to do once the subxid handling is in a state that allows concurrent removal of entries. If it turns out that it's not that easy, than I'll just drop the idea again. I still don't believe you can avoid taking exclusive lock, either; your argument here did not address latestCompletedXid. Sorry, not addressing latestCompletedXid was an oversight :-(. My point is the we only *need* to advance latestCompletedXid on COMMITS. We do so for aborts only to avoid running with unnecessarily low xmins after a transaction ABORT. That corner case can only happen after a toplevel ABORT, though - aborting subxacts cannot change the xmin, because the toplevel xact will have a lower xid than any of it's subtransactions anyway. We can therefore just remember the largest assigned xid for a given transaction, and update latestCompletedXid to that on toplevel commit or abort. That prevents that corner-case too, without updating latestCompletedXid during subxact abort. But the main point remains this: there is no evidence whatsoever that these code paths are sufficiently performance-critical to be worth speeding up by making the code more fragile. The gain will be less than that of the locking improvements done so far. It will be a win for heavy users of plpgsql BEGIN/END/EXCEPTION blocks, though I think. We'll also save some cycles in TransactionIdIsInProgress, because we can use a binary search, but that's just an added bonus. I'm currently trying to code up a patch, since it's easier to judge the correctness of actual code than that of a mere proposals. I'll do some benchmarking when the patch is done to see if it brings measurable benefits. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] TIMESTAMP and daylight savings time question
It appears that the older versions of PostgreSQL (7.x) do not consider the daylight savings time when using TIMESTAMP WITH TIMEZONE but the most recent versions do (8.x). Can someone tell me the exact PostgreSQL version number where the behavior changed?
Re: [HACKERS] Ideas input sought for this year's SOC page
On Wed, Mar 12, 2008 at 2:44 PM, Peter Eisentraut [EMAIL PROTECTED] wrote: Josh Berkus wrote: We need to update the SoC page: http://www.postgresql.org/developer/summerofcode ... with new ideas and projects appropriate for PostgreSQL 8.4/8.5. Can we add a project to implement advanced and matrix mathematics in PG, with transparency between arrays and matrices/vectors? A GSL, BLAS, LAPACK set of hooks, with operators? I realize there is the excellent plr project, but to be honest I hate the R programming language and I think we could really use something simpler and implemented more directly. (If I were a real programmer, I would take it on, but I can't see being able to follow through on it.) Many of these items could be suitable: http://wiki.postgresql.org/wiki/XML_Todo -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Nasty bug in heap_page_prune
Pavan Deolasee [EMAIL PROTECTED] writes: On Thu, Mar 6, 2008 at 6:53 AM, Tom Lane [EMAIL PROTECTED] wrote: While working on the previously discussed refactoring of heap_page_prune, I came to the realization that its use of CacheInvalidateHeapTuple is not just a PANIC risk but simply wrong :-( The semantics aren't right: inval.c assumes that it's dealing with transactional invalidations, I am not sure how ugly or difficult it would be to teach inval.c to handle non-transactional invalidations. After further study I think it might not be that hard. As things stand, CacheInvalidateHeapTuple accumulates notices into a list that is pushed out at transaction commit. But we could push them out earlier, ie, before making the actual page changes in heap_page_prune. This seems safe since an unnecessary invalidation notice cannot break anything, at worst it causes useless work. My first visualization of how to do this was to extend the current subtransaction handling logic in inval.c, such that we'd build a phony subtransaction for each invocation of heap_page_prune, and then force the messages to be sent at subtransaction commit. However, it looks to me like we don't even need that much mechanism. The problem case only occurs during the first stage of VACUUM FULL, and AFAICS there could never be any other pending inval events at that point. (VACUUM FULL will generate transactional inval events later, when it's doing cross-page tuple moves, but there shouldn't be any during the time that we're running heap_page_prune.) So I think the only mechanism we really need is something to force out pending inval events immediately, that is just AtEOXact_Inval(true) or something pretty close to it. I'm inclined to set this up as having heap_page_prune first call a function named something like BeginNontransactionalInvalidation, then do its CacheInvalidateHeapTuple calls, then call EndNontransactionalInvalidation. In the initial implementation the first would just assert that the inval queue is empty, and the second would push out the queue. If we ever need to generalize things then the code structure would be there to build a phony subtransaction. Obviously this is a bit of a hack, but there's hardly anything about VACUUM FULL that's not a bit of a hack ... I haven't coded this, much less tested it, but it seems like it'd work. Comments? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
