Re: [HACKERS] A smaller default postgresql.conf
On Tue, Aug 19, 2008 at 9:40 PM, Tom Lane [EMAIL PROTECTED] wrote: Robert Treat [EMAIL PROTECTED] writes: I'd still like to see us adopt the proposal from some time ago where we stop commenting out the parameters at all, but short of that, hiding options seems about the worst choice we could make. Well, there seems to be a very substantial body of opinion that says we *do* need to hide uninteresting options. more to the point... not just uninteresting but dangerous for the uninformed ones... i have seen to many people turning off fsync in OLTP systems 'cause someone tolds them that will improve speed... and work_mem setted at 256Mb because that improves a bad query that should be rewritten as something more sanely... -- regards, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. (593) 87171157 -- 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] Extending varlena
Peter Eisentraut wrote: If you replace the third point by maybe partition TOAST tables, replace large object handle by TOAST pointer, and create an API to work on TOAST pointers, how are the two so much different? And why should they be? I can see that there are going to be needs to access large data with interfaces that are not traditional SQL, but at least the storage handling could be the same. That way you would solve the first two points and others for free. I've long wondered why toast and large object storage is not one and the same (indeed). It seems a like a natural solution to marry the two. -- Sincerely, Stephen R. van den Berg. E-mails should be like a lady's skirt: Long enough to cover the subject, and short enough to be interesting. -- 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] A smaller default postgresql.conf
Alvaro Herrera wrote: Dave Page wrote: On Tue, Aug 19, 2008 at 10:03 PM, Tom Lane [EMAIL PROTECTED] wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Hmm, let me suggest providing it as a manpage for postgresql.conf, i.e., you run man postgresql.conf and it gives you this manpage documenting every option. Seems a bit Unix-centric, but +1 for it on Unix machines anyway. Is there any near equivalent on Windows? No. There are helpfiles (which consist of a navigation tree and a bunch of pages), but they're what we use for the main docs. There's nothing akin to a man page. Well, so we provide a reference to the help file and that's it. If there's a way to provide a link in the config file that would automatically open the appropriate help file on click, that would be perfect. It's a text file. You can't put a clickable link in a textfile. You can put a regular http link that the user can copy/paste into his browser, if you want to. AFAIK, you can't create a link that points into a CHM helpfile - you can only point to the helpfile itself, not to a specific section in it. We could just generate a HTML file with the same contents as the man-page though, and link to that - thus not having to link out onto the internet which really shouldn't be accessible from your database server :-) //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] A smaller default postgresql.conf
On Wed, Aug 20, 2008 at 4:40 AM, Joshua Drake [EMAIL PROTECTED] wrote: On Tue, 19 Aug 2008 23:32:34 -0400 Tom Lane [EMAIL PROTECTED] wrote: Bruce Momjian [EMAIL PROTECTED] writes: On idea is for postgresql.conf to merely include other files: include 'sharedmem.conf' include 'compat.conf' ... That would definitely add complexity ... what would it buy in return? I am not arguing for this but if we went down that route it does buy us the ability to compartmentalize the entire conf.. so you have: memory_settings.conf logging.conf maintenance.conf Would make it damn hard for pgAdmin to figure out how to edit the config though. That's why we only support single-file configs atm. -- Dave Page EnterpriseDB UK: 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] Patch: plan invalidation vs stored procedures
Am 19.08.2008 um 20:47 schrieb Tom Lane: Bruce Momjian [EMAIL PROTECTED] writes: Joshua Drake wrote: Is our backpatch policy documented? It does not appear to be in developer FAQ. Seems we need to add it. I'm not sure that I *want* a formal written-down backpatch policy. Whether (and how far) to backpatch has always been a best-judgment call in the past, and we've gotten along fine with that. I think having a formal policy is just likely to lead to even more complaints: either patching or not patching could result in second-guessing by someone who feels he can construe the policy to match the result he prefers. Agreeing to you and some later posters in this thread, I would not vote for a formal policy either. But IMHO there should be a general, informal note about backpatching in developer docs/faqs. A place where you can point to, and a chance for new people to read about the postgres way of handling backpatching. Btw., how backpatching is handled here is one of the reasons I trust my data to postgres. Best Regards Michael -- 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] A smaller default postgresql.conf
Peter Eisentraut wrote: On Tuesday 19 August 2008 19:12:16 Tom Lane wrote: Well, why not just make a one-eighty and say that the default postgresql.conf is *empty* (except for whatever initdb puts into it)? Well, my original implementation of GUC had an empty default configuration file, which was later craptaculated to its current form based on seemingly popular demand. I am very happy to work back toward the empty state, and there appears to be growing support for that. Yeah, +1 from me. Perhaps we should still add some comments about the parameters changed most often, including a link to the documentation of GUC parameters. As a kind of starting point for (new) users. Best Regards Michael Paesold -- 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] Patch: plan invalidation vs stored procedures
Le mercredi 20 août 2008, Tom Lane a écrit : That just begs the question of what's the difference between a bug and a limitation. AFAICS, having such a policy/guideline/whatchacallit in place wouldn't have done a single thing to stop the current flamewar, because the people who want this thing back-patched are insisting that it's a bug, while those who don't are saying it's a long-known limitation. As a person who previously insisted it was a bug, I'd like to take the opportunity to claim that I didn't realize this was a limitation of the design of plan invalidation, which now seems related to DDL operations. Realizing this earlier would have resulted in no mail at all on this thread from here. There's certainly a balance between -hackers readers not doing their homework and people in the know choosing not to re-estate known things... Also, there are a whole lot more considerations in a backpatch decision than just is it a bug. The (estimated) risk of creating new bugs and the extent to which the patch will change behavior that apps might be relying on are two big reasons why we might choose not to back-patch a bug fix. And this way the project works is what leads its users not to fear minor upgrades, which is something I (we all?) highly value. Regards, -- dim signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] Proposed Resource Manager Changes
On Wed, 2008-08-20 at 10:46 +0900, ITAGAKI Takahiro wrote: One thing to worry about is a confliction of RmgrId. We can check conflictions in redo because rmgrs are actually registered, but we might need to check conflictions even in a normal running. Extensions that write own XLog record can use arbitrary RmgrIds without restrictions. That sounds quite hard. I'm putting in a check on the id itself, which will be more than we had before... -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] Patch: plan invalidation vs stored procedures
On Tue, 2008-08-19 at 19:45 -0400, Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: If there is plan invalidation then you just change called1() to return one more field and that's it - no juggling with C) and D) and generally less things that can go wrong. That is a pure flight of fancy. Nope, this is description of real situation when you have to maintain lots and lots of functions. Adjusting a function's API generally requires source-code changes on the caller side too. Adding a column to table does not (even generally) require changing all queries accessing that table, why should adding a column to functions return type do ? There might be a few limited cases where you can avoid that, but that doesn't leave you with much of an argument that this is a critical bug fix. It's a corner case and little more. It is a corner case if you don't have a dynamic system, evolving over time, which relies heavily on functions . It is a complete non-issue if you don't use functions at all. FWIW, given that there will probably always be corner cases. I can see the attraction in Simon's suggestion of providing a way to manually issue a system-wide forced plan flush. That was also what I suggested as one blanket way of solving the bigger issue you brought up, that of not knowing where to stop tracking dependencies for plan invalidation. My thinking was, that this trades one-time inefficiency (replanning all stored plans) against more general but spread in time inefficiency of current patch (sending registration messages around for each function OID you depend on at each time you plan ). Hannu -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Is mdextend really safe?
Earlier we saw some bug reports from someone who had a buffer flush fail do to ENOSPC. We asserted then that that should never happen because when we extend the relation we write out the new blocks so any ENOSPC errors out to happen at that point, not when a buffer is flushed. However looking at mdextend it only writes out the requested block. Any blocks between the end of the table and the requested block are *not* written out. We count on the OS to implicitly fill those blocks with zeros. On Unix that creates a sparse file where the intervening blocks are not allocated. When we later write out those blocks the filesystem then has to allocate space for them. IIRC the bug reports were from Windows. I'm not sure what NTFS's behaviour with sparse files is. Now this only matters if we ever call mdextend on a block which isn't the block immediately following the end of file. Is that true? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- 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] proposal sql: labeled function params
Hello
I understand now why Oracle use = symbol for named params. This isn't
used so operator - so implementation is trivial.
postgres=# create function x(a boolean) returns bool as $$select $1$$
language sql;
CREATE FUNCTION
Time: 5,549 ms
postgres=# select x(a = true);
x
---
t
(1 row)
Time: 0,566 ms
postgres=# select x(a = 0 = 1);
x
---
f
(1 row)
Time: 0,772 ms
postgres=# select x(a = 0 = 1);
x
---
t
(1 row)
Time: 0,633 ms
postgres=# select x(a = 0 = 1);
it could live together with labels
postgres=# select x(a = 0 = 1 as boo);
x
---
t
(1 row)
there are not any conflict. nice (operator = is never used).
I dislike to use AS for named params - it has some unhappy consequences:
a) it merge two features together (named params, labels),
b) when we disable @a, then we should implement only one feature - named params
c) @b isn't compatible with SQL/XML that is implemented now
I don't found any notice about db2 default parameters.
Named params needs different algorithm of searching in pg_proc. There
should be some new problems - like
create function foo(a integer, b integer);
select foo(10,10); -- ok
select foo(a = 10, b =20); -- ok
select foo(b=20, a =20); -- ok
select foo(c=20, 20); -- unknown fce !!!
Regards
Pavel Stehule
real gram implemenation:
param_list:param
{
$$ = list_make1($1);
}
| param_list ',' param
{
$$ = lappend($1, $3);
}
;
param:
a_expr
{
$$ = $1;
}
| param_name POINTER a_expr
{
$$ = $3;
}
| a_expr AS ColLabel
{
$$ = $1;
}
| param_name POINTER a_expr AS ColLabel
{
$$ = $3;
}
;
lexer
identifier {ident_start}{ident_cont}*
typecast::
pointer =
--
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] Is mdextend really safe?
* Gregory Stark: On Unix that creates a sparse file where the intervening blocks are not allocated. When we later write out those blocks the filesystem then has to allocate space for them. This seems to happen relatively rarely. Creating temporary holes like this usually results in heavily fragmented files on the file systems I use, and I don't see this with PostgreSQL. (It's one of my gripes with Berkeley DB.) However, I looked at the code recently and couldn't figure out *why* PostgreSQL's observed behavior is this way. 8-( -- Florian Weimer[EMAIL PROTECTED] BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- 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] Is mdextend really safe?
Gregory Stark napsal(a): On Unix that creates a sparse file where the intervening blocks are not allocated. When we later write out those blocks the filesystem then has to allocate space for them. IIRC the bug reports were from Windows. I'm not sure what NTFS's behaviour with sparse files is. NTFS has sparse file feature, but how it works ... Now this only matters if we ever call mdextend on a block which isn't the block immediately following the end of file. Is that true? I think, that it could happens only during wal log replay, but at the end everything should be OK. Look into ReadBuffer_common there is following code: 00226 /* Substitute proper block number if caller asked for P_NEW */ 00227 if (isExtend) 00228 blockNum = smgrnblocks(smgr, forkNum); Zdenek -- 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] Is mdextend really safe?
Gregory Stark wrote: Now this only matters if we ever call mdextend on a block which isn't the block immediately following the end of file. Is that true? I don't think so. -- Heikki Linnakangas 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] Patch: plan invalidation vs stored procedures
The lack of plan invalidation is limitation that also has two bugs attached to it. I agree that full fledged patch to fix all the isssues should not be done in 8.3. I can't agree that effort to get the bugs fixed already in 8.3 should not be made. I can understand that hackers here have learned to live with these bugs and limitations but not all the users are reading these flame wars here and most of them are not even aware of these bugs until they are hit by them. Sql function bug is such that users probably won't even understand what hit them and how the data got mangled. - If there is nothing that can be done in 8.3 at least warning should be added into the documentation. It will be just one more don't in our long list don'ts for our developers. ERROR: cache lookup failed for function. - Could the plan be marked as invalid so it would fail only once so the next call to the function would get replanned and work again. At least it would be better than losing parts of application for indeterminate time. - Should update pg_proc set proname = proname; be the current solution to the problem or has someone something better to offer. We could scan released code for DROP FUNCTION and generate plan invalidation statement as last item of transaction releasing the code. - Could some less dangerous looking mechanism be added to 8.3 that wouldn't make users not used to PostgreSQL limitations gasp for air when they see the workarounds :) Calling the problem limitation will not make it go away. I am quite sure that new users consider it a bug until thay are converted to perceive it as lmitation. No matter how many time the usage of functions in database is called corner case it does not make it a corner case. In my experience it is quite common practice on all the database systems i have worked with. I do get the impression that Tom who would prefer to get all the pl's out of PostgreSQL and live happily ever after with pure SQL standard. On Wed, Aug 20, 2008 at 11:27 AM, Dimitri Fontaine [EMAIL PROTECTED]wrote: Le mercredi 20 août 2008, Tom Lane a écrit : That just begs the question of what's the difference between a bug and a limitation. AFAICS, having such a policy/guideline/whatchacallit in place wouldn't have done a single thing to stop the current flamewar, because the people who want this thing back-patched are insisting that it's a bug, while those who don't are saying it's a long-known limitation. As a person who previously insisted it was a bug, I'd like to take the opportunity to claim that I didn't realize this was a limitation of the design of plan invalidation, which now seems related to DDL operations. Realizing this earlier would have resulted in no mail at all on this thread from here. There's certainly a balance between -hackers readers not doing their homework and people in the know choosing not to re-estate known things... Also, there are a whole lot more considerations in a backpatch decision than just is it a bug. The (estimated) risk of creating new bugs and the extent to which the patch will change behavior that apps might be relying on are two big reasons why we might choose not to back-patch a bug fix. And this way the project works is what leads its users not to fear minor upgrades, which is something I (we all?) highly value. Regards, -- dim
Re: [HACKERS] Is mdextend really safe?
Gregory Stark [EMAIL PROTECTED] writes: Now this only matters if we ever call mdextend on a block which isn't the block immediately following the end of file. Is that true? Only in hash indexes. 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] proposal sql: labeled function params
Pavel Stehule [EMAIL PROTECTED] writes: I understand now why Oracle use = symbol for named params. This isn't used so operator - so implementation is trivial. You really didn't understand the objection at all, did you? The point is not about whether there is any built-in operator named =. The point is that people might have created user-defined operators named that. 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] A smaller default postgresql.conf
Dave Page wrote: On Wed, Aug 20, 2008 at 4:40 AM, Joshua Drake [EMAIL PROTECTED] wrote: I am not arguing for this but if we went down that route it does buy us the ability to compartmentalize the entire conf.. so you have: memory_settings.conf logging.conf maintenance.conf Would make it damn hard for pgAdmin to figure out how to edit the config though. That's why we only support single-file configs atm. That's why we're working on adding a sourcefile attribute to GUC, so that it's trivial to know the path of the config file each settings comes from. -- 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] Extending varlena
Peter Eisentraut wrote: If you replace the third point by maybe partition TOAST tables, replace large object handle by TOAST pointer, and create an API to work on TOAST pointers, how are the two so much different? And why should they be? The reason they should be different is that (IMHO anyway) you don't want the default behavior of SELECT * FROM ... to include pulling back the entire contents of the blob. Indeed, we *can't* have that be the behavior, unless we want to go back to the proposal that started this thread of making the entire system safe for multi-gigabyte datums. It's certainly possible that the underlying implementation could be just TOAST, but we need some other API at the SQL level. 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] A smaller default postgresql.conf
Alvaro Herrera wrote: Dave Page wrote: On Wed, Aug 20, 2008 at 4:40 AM, Joshua Drake [EMAIL PROTECTED] wrote: I am not arguing for this but if we went down that route it does buy us the ability to compartmentalize the entire conf.. so you have: memory_settings.conf logging.conf maintenance.conf Would make it damn hard for pgAdmin to figure out how to edit the config though. That's why we only support single-file configs atm. That's why we're working on adding a sourcefile attribute to GUC, so that it's trivial to know the path of the config file each settings comes from. Still won't make life very easy for pgadmin, but it will make it slightly less difficult. That said, I think splitting the config out like that will mkae it even more confusing for the newbie user, which is contrary to what we want here. //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] Patch: plan invalidation vs stored procedures
On Wed, Aug 20, 2008 at 03:12:43PM +0300, Asko Oja wrote: - If there is nothing that can be done in 8.3 at least warning should be added into the documentation. It will be just one more don't in our long list don'ts for our developers. I am in favour of that change in the 8.3 branch. ERROR: cache lookup failed for function. - Could the plan be marked as invalid so it would fail only once so the next call to the function would get replanned and work again. At least it would be better than losing parts of application for indeterminate time. That seems to me to be a behaviour change, not a bug fix. I agree that the current behaviour is pretty annoying. That is not the same thing as a bug except in the loosest sense. The system works as specified, and therefore it's not a bug. If the specification is wrong, you need a new specification; that's a bug fix that is usually pronounced major release. - Could some less dangerous looking mechanism be added to 8.3 that wouldn't make users not used to PostgreSQL limitations gasp for air when they see the workarounds :) I think it a very bad idea even to suggest that we start undertaking things like adding mechanisms to minor releases, even with smileys at the end of the sentence. I appreciate (possibly more than many hackers) the limitations that are imposed on users by some of the decisions historically taken by developers in some of the previous major releases. But I very strongly agree with Dimitri: the super-conservative approach to maintenance releases that this project takes is a really big benefit to users, and is ultra important in mission critical environments. Otherwise, it becomes practically impossible to get minor releases into production. If you have to worry about the possibility of major changes between minor versions, you will have to treat every release as a major release. I don't think we have sufficient commercial integration support yet that we can follow the lead of the Linux kernel, where the system vendor has the effective obligation to make sure your kernel actually works. In addition, if someone wants to develop back-patches for 8.3 that give it new functionality otherwise planned for 8.4, I see nothing wrong with them doing so. That's the advantage offered by having the source. But the idea that the new functionality should be patched back by the project because one is impatient is not on. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.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] Patch: plan invalidation vs stored procedures
On Wed, Aug 20, 2008 at 09:16:56AM -0400, Andrew Sullivan wrote: On Wed, Aug 20, 2008 at 03:12:43PM +0300, Asko Oja wrote: - If there is nothing that can be done in 8.3 at least warning should be added into the documentation. It will be just one more don't in our long list don'ts for our developers. I am in favour of that change in the 8.3 branch. +1 ERROR: cache lookup failed for function. - Could the plan be marked as invalid so it would fail only once so the next call to the function would get replanned and work again. At least it would be better than losing parts of application for indeterminate time. That seems to me to be a behaviour change, not a bug fix. I agree that the current behaviour is pretty annoying. That is not the same thing as a bug except in the loosest sense. The system works as specified, and therefore it's not a bug. If the specification is wrong, you need a new specification; that's a bug fix that is usually pronounced major release. - Could some less dangerous looking mechanism be added to 8.3 that wouldn't make users not used to PostgreSQL limitations gasp for air when they see the workarounds :) I think it a very bad idea even to suggest that we start undertaking things like adding mechanisms to minor releases, even with smileys at the end of the sentence. I appreciate (possibly more than many hackers) the limitations that are imposed on users by some of the decisions historically taken by developers in some of the previous major releases. But I very strongly agree with Dimitri: the super-conservative approach to maintenance releases that this project takes is a really big benefit to users, and is ultra important in mission critical environments. Otherwise, it becomes practically impossible to get minor releases into production. If you have to worry about the possibility of major changes between minor versions, you will have to treat every release as a major release. +10 This policy has allowed us to upgrade to new minor releases with a minimum of testing for critical systems and basically none for non- critical systems. We would never upgrade for minor releases if this changes. We do not have the resources to perform full regression tests without having a very big carrot such as the new features a major release contains. Cheers, Ken -- 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] proposal sql: labeled function params
2008/8/20 Tom Lane [EMAIL PROTECTED]: Pavel Stehule [EMAIL PROTECTED] writes: I understand now why Oracle use = symbol for named params. This isn't used so operator - so implementation is trivial. You really didn't understand the objection at all, did you? The point is not about whether there is any built-in operator named =. The point is that people might have created user-defined operators named that. I understand well, so only I don't see better solution. Yes, everyone who used = should have problems, but it is similar with .. new keywords, etc. Probably easy best syntax doesn't exist :(. I haven't idea who use = now and how often, and if this feature is possible in pg, but there are not technical barriers. regards Pavel Stehule 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] A smaller default postgresql.conf
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Well, there seems to be a very substantial body of opinion that says we *do* need to hide uninteresting options. more to the point... not just uninteresting but dangerous for the uninformed ones... i have seen to many people turning off fsync in OLTP systems 'cause someone tolds them that will improve speed... and work_mem setted at 256Mb because that improves a bad query that should be rewritten as something more sanely... It's a conf file - we shouldn't be hiding anything. If someone does not understand a setting, they should not be changing it. It's not our responsibility to child-proof the conf file, and there are so many potential foot guns and dangerous settings it would be futile to try anyway. To clarify my earlier point, I'm strongly in favor of explanations and links for each setting, but will concede the 'recommended settings' per Josh's example, as much as I think it is a good idea. So above each (uncommented!) setting, we'd have: # foobar: Adjusts the foobariness of the database # # This uses units of baz from 1-10, with 10 being the strongest # # Changing this setting requires a reload # This setting may also be changed per session # The default value is 5 # # For more information, please see: # http://postgres.org/doc/foobar.html foobar: 7 - -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200808200923 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkisHpIACgkQvJuQZxSWSsjVKgCeOIICw6YLAAMbnE+TCo9NXVwg YSwAn3imeIz1A25T2nib/rM6C8+dwzdX =wdUL -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] Patch: plan invalidation vs stored procedures
Asko Oja wrote: I do get the impression that Tom who would prefer to get all the pl's out of PostgreSQL and live happily ever after with pure SQL standard. I have not seen the slightest evidence of this, and don't believe it for a minute. I understand some of the frustration you are feeling, but statements like this don't help anything. (And yes, I too have recently been bitten nastily by cached plan problems, and want to see them fixed. I rather like Simon's suggestion of a command or function that would clear the plan cache.) cheers andrew -- 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] Patch: plan invalidation vs stored procedures
Asko Oja escribió: In the first message Martin asked There are probably a lot of details that I have overlooked. I'd be really thankful for some constructive comments and criticism. Especially, what needs to be done to have this in the core. Feedback appreciated. Can we get back to the topic? This is where the interesting questions are: http://archives.postgresql.org/message-id/10333.1219179364%40sss.pgh.pa.us I think the efforts to get the patch in 8.3 are wasted time. Better concentrate on getting something good for everyone in 8.4. -- 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] Patch: plan invalidation vs stored procedures
Thanks for a nice replay Andrew. So best solution for 8.3 is update pg_proc set proname = proname; whenever you need to drop and create functions or some in house patch. Lets get on with 8.4 Asko On Wed, Aug 20, 2008 at 4:16 PM, Andrew Sullivan [EMAIL PROTECTED]wrote: On Wed, Aug 20, 2008 at 03:12:43PM +0300, Asko Oja wrote: - If there is nothing that can be done in 8.3 at least warning should be added into the documentation. It will be just one more don't in our long list don'ts for our developers. I am in favour of that change in the 8.3 branch. ERROR: cache lookup failed for function. - Could the plan be marked as invalid so it would fail only once so the next call to the function would get replanned and work again. At least it would be better than losing parts of application for indeterminate time. That seems to me to be a behaviour change, not a bug fix. I agree that the current behaviour is pretty annoying. That is not the same thing as a bug except in the loosest sense. The system works as specified, and therefore it's not a bug. If the specification is wrong, you need a new specification; that's a bug fix that is usually pronounced major release. - Could some less dangerous looking mechanism be added to 8.3 that wouldn't make users not used to PostgreSQL limitations gasp for air when they see the workarounds :) I think it a very bad idea even to suggest that we start undertaking things like adding mechanisms to minor releases, even with smileys at the end of the sentence. I appreciate (possibly more than many hackers) the limitations that are imposed on users by some of the decisions historically taken by developers in some of the previous major releases. But I very strongly agree with Dimitri: the super-conservative approach to maintenance releases that this project takes is a really big benefit to users, and is ultra important in mission critical environments. Otherwise, it becomes practically impossible to get minor releases into production. If you have to worry about the possibility of major changes between minor versions, you will have to treat every release as a major release. I don't think we have sufficient commercial integration support yet that we can follow the lead of the Linux kernel, where the system vendor has the effective obligation to make sure your kernel actually works. In addition, if someone wants to develop back-patches for 8.3 that give it new functionality otherwise planned for 8.4, I see nothing wrong with them doing so. That's the advantage offered by having the source. But the idea that the new functionality should be patched back by the project because one is impatient is not on. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Volatile functions in subqueries don't prevent subqueries from being evaluated in initplans?
Hm, shouldn't this query notice that random() is volatile and not make the subquery an initplan? postgres=# select i, (select (random()*1000)::integer ) from x limit 5; i | ?column? ---+-- 1 | 677 2 | 677 3 | 677 4 | 677 5 | 677 (5 rows) postgres=# explain select i, (select (random()*1000)::integer ) from x limit 5; QUERY PLAN --- Limit (cost=0.02..0.11 rows=5 width=4) InitPlan - Result (cost=0.00..0.02 rows=1 width=0) - Seq Scan on x (cost=0.00..64.80 rows=3480 width=4) (4 rows) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] make dist does not work in VPATH
Hi, It seems we're neglecting to copy GNUmakefile into the temporary distdir: $ pwd /pgsql/build/83_rel $ make dist rm -rf postgresql-8.3.3* =install= for x in `cd /pgsql/source/83_rel find . -name CVS -prune -o -print`; do \ file=`expr X$x : 'X\./\(.*\)'`; \ if test -d /pgsql/source/83_rel/$file ; then \ mkdir postgresql-8.3.3/$file chmod 777 postgresql-8.3.3/$file; \ else \ ln /pgsql/source/83_rel/$file postgresql-8.3.3/$file /dev/null 21 \ || cp /pgsql/source/83_rel/$file postgresql-8.3.3/$file; \ fi || exit; \ done make -C postgresql-8.3.3 distprep make[1]: entrant dans le répertoire « /home/alvherre/Code/CVS/pgsql/build/83_rel/postgresql-8.3.3 » make[1]: *** Pas de règle pour fabriquer la cible « distprep ». Arrêt. make[1]: quittant le répertoire « /home/alvherre/Code/CVS/pgsql/build/83_rel/postgresql-8.3.3 » make: *** [distdir] Erreur 2 -- 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] Patch: plan invalidation vs stored procedures
Alvaro Herrera [EMAIL PROTECTED] writes: This is where the interesting questions are: http://archives.postgresql.org/message-id/10333.1219179364%40sss.pgh.pa.us Upthread, someone speculated about solving the problem by forcing plan cache flush on *any* catalog change. I think that's probably not acceptable from an efficiency standpoint. But maybe it'd be a good idea to special-case common cases and fall back to a stupid flush for less common cases, rather than invest all the work that'd be needed to track every direct and indirect dependency of every plan. My first thought along these lines is: * track table dependencies exactly (important for efficiency, plus we've got the code already) * track function dependencies exactly (seems function definitions might change often enough to make it important for efficiency; maybe only track PL function dependencies??) * brute-force flush for any other catalog change that could affect plans However I have no hard evidence to back up drawing the line there rather than somewhere else. Anyone have data on what sort of DDL changes are common in their applications? 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] Volatile functions in subqueries don't prevent subqueries from being evaluated in initplans?
Gregory Stark [EMAIL PROTECTED] writes: Hm, shouldn't this query notice that random() is volatile and not make the subquery an initplan? We've never done that in the past; in fact I recall seeing people using subselects deliberately to hide volatility. 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] make dist does not work in VPATH
Is it sensible for make dist to work in a VPATH? Seems like the entire point of that operation is to modify the source tree. 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] Patch: plan invalidation vs stored procedures
David Fetter napsal(a): On Tue, Aug 19, 2008 at 09:50:53PM -0400, Tom Lane wrote: David Fetter [EMAIL PROTECTED] writes: On Tue, Aug 19, 2008 at 07:45:16PM -0400, Tom Lane wrote: FWIW, given that there will probably always be corner cases. I can see the attraction in Simon's suggestion of providing a way to manually issue a system-wide forced plan flush. Would that require a system-wide plan cache to implement? No, just a function that can issue a suitable sinval message. plancache.c would already respond in the desired way to a relcache inval message with OID = 0, though likely it'll be cleaner to invent an sinval message type specifically for the purpose. One thing to think about is whether the flush should be truly system-wide or just database-wide. I can see a lot more uses for the latter than the former --- I don't think there's a reason for cached plans to depend on any contents of the shared catalogs. They might during an on-line upgrade. At this moment we have offline catalog upgrade. On-line old catalog processing is nice idea but amount of work and impact is too high to do it. Catalog is usually small and its offline upgrade is fast. Zdenek -- 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] A smaller default postgresql.conf
On Wednesday 20 August 2008 02:22:26 Jaime Casanova wrote:
On Tue, Aug 19, 2008 at 9:40 PM, Tom Lane [EMAIL PROTECTED] wrote:
Robert Treat [EMAIL PROTECTED] writes:
I'd still like to see us adopt the proposal from some time ago where
we stop commenting out the parameters at all, but short of that,
hiding options seems about the worst choice we could make.
Well, there seems to be a very substantial body of opinion that says
we *do* need to hide uninteresting options.
more to the point... not just uninteresting but dangerous for the
uninformed ones...
i have seen to many people turning off fsync in OLTP systems 'cause
someone tolds them that will improve speed...
and work_mem setted at 256Mb because that improves a bad query that
should be rewritten as something more sanely...
This is a false argument, because people can just as easily be told you
should add fsync=off into you blank postgresql.conf and then they are in the
same boat.
Most of the newbie confusion comes from a lack of understanding of how and
what to tune. Adding a full on tuning guide into the docs would help with
that. Untill then, we should point them to
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
--
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] A smaller default postgresql.conf
Greg Sabino Mullane [EMAIL PROTECTED] writes: # foobar: Adjusts the foobariness of the database # # This uses units of baz from 1-10, with 10 being the strongest # # Changing this setting requires a reload # This setting may also be changed per session # The default value is 5 # # For more information, please see: # http://postgres.org/doc/foobar.html foobar: 7 So your plan is that postgresql.conf will be approximately two thousand lines long, before the user has ever touched it at all? (Two hundred or so GUC variables and ten lines of comments for each one) This seems entirely nuts. Duplicating the whole contents of config.sgml in another place is pointless as well as maintenance-intensive. And it *still* wouldn't be enough information for people to know how to twiddle many of the variables; there are other parts of the SGML docs that contain relevant info as well. I could see having *one* comment at the top of the file giving pointers to where to read the documentation. 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] make dist does not work in VPATH
Tom Lane wrote: Is it sensible for make dist to work in a VPATH? Seems like the entire point of that operation is to modify the source tree. Actually the point AFAICS is to generate a tarball. Why wouldn't it work in a VPATH build? -- 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] Patch: plan invalidation vs stored procedures
On Wed, 2008-08-20 at 08:50 -0400, Andrew Dunstan wrote: Asko Oja wrote: I do get the impression that Tom who would prefer to get all the pl's out of PostgreSQL and live happily ever after with pure SQL standard. I have not seen the slightest evidence of this, and don't believe it for a minute. I understand some of the frustration you are feeling, but statements like this don't help anything. Claiming that problems with functions are a corner case seems to indicate that kind of attitude. OTOH, it may still be, that building really large and complex live (evolving) databases using postgreSQL is also still a corner case, so any bug/limitation that manifests itself when doing DDL under 24/7 database carrying big loads is a corner case (And yes, I too have recently been bitten nastily by cached plan problems, and want to see them fixed. I rather like Simon's suggestion of a command or function that would clear the plan cache.) I guess this would be more robust. Mostly we use _dependencies_ to forbid stuff or to do DROP CASCADE, that is, to enforce user-visible behaviour. Cache invalidation seems much lighter and safer operations. We could even add an option to do a global cache invalidation at the end of any transaction which does DDL. That would of course need automatic re-planning the invalidated queries and keeping some intermediate form of query (with original * expanded to col lists, maybe something else, basically the same as is currently saved for view's) in order to do so. - Hannu -- 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] window function v03 against HEAD
2008/8/1 David Fetter [EMAIL PROTECTED]: On Thu, Jul 31, 2008 at 11:00:15PM +0900, Hitoshi Harada wrote: 2008/7/31 David Fetter [EMAIL PROTECTED]: Sorry about that. Apparently, at least the way things are set up, there's a *lot* of history you can rewind. Further changes should move pretty quickly :) Thankfully, I succeeded to learn about git and started to use it on my local. Then I read through the -hackers list about it but some points are still not clear. I guess we'll all learn at once :) - Am I eligible to push git.postgresql.org/git/~davidfetter/window_functions? You will be as soon as I can arrange it. I may move or re-create that repository. Please send me a username and an RSA public key so I can give you git-shell access. I've pushed my work into David's git hosting. http://git.postgresql.org/?p=~davidfetter/window_functions/.git;a=summary The DISTNCT fix which overlaps much of my patch were merged. And some bugs were fixed while adding regression test for window functions. Documantation is still out of order, because some points are uncertain, including window function design. Up to now, I started to think about new pg catalog for them (e.g. pg_wfunc). If anyone is interested this patch please clone source tree from git.postgresql.org and send me patch along with comments. Regards, -- Hitoshi Harada window_functions.patch.tgz Description: GNU Zip compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Questions about HeapTupleHeaderData
I try to understand why HeapTupleHeaderData structure has t_datum member. This is use only on few places and from my point of view this information should be stored in the HeapTupleData structure or split HeapTupleHeaderData it into two structures (DatumTupleHeaderData). The idea behind my question is that I need process different versions of HeapTupleHeaders for different page layouts and I try to discover how to deal with the union. Thanks Zdenek -- 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] Questions about HeapTupleHeaderData
Zdenek Kotala [EMAIL PROTECTED] writes: I try to understand why HeapTupleHeaderData structure has t_datum member. This is use only on few places and from my point of view this information should be stored in the HeapTupleData structure or split HeapTupleHeaderData it into two structures (DatumTupleHeaderData). Then (a) we'd have two struct definitions that have to stay in sync and (b) we'd have to cast between HeapTupleHeader and DatumTupleHeader pointer types in a bunch of places, adding notational messiness with no advantage. 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] Patch: plan invalidation vs stored procedures
On Wed, Aug 20, 2008 at 05:03:19PM +0300, Asko Oja wrote: Lets get on with 8.4 Oh, I shoulda mentioned that, too -- I completely support doing this work for 8.4. (I can think of more than one case where this feature alone would be worth the upgrade.) A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.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] A smaller default postgresql.conf
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 So your plan is that postgresql.conf will be approximately two thousand lines long, before the user has ever touched it at all? (Two hundred or so GUC variables and ten lines of comments for each one) Sure, why not? Clarity should always trump brevity. The only people who gain from a comment-less file are the ones who are already expert in it. Besides, the file is already long enough to require use of an editor's find function. This seems entirely nuts. Duplicating the whole contents of config.sgml in another place is pointless as well as maintenance-intensive. And it *still* wouldn't be enough information for people to know how to twiddle many of the variables; there are other parts of the SGML docs that contain relevant info as well. No, not the entire contents - these would be shorter hints. Right now we already partially 'duplicate' due to the mishmash of having some vars explained. Some are explained, some are not, and some have poorly-placed end-of-line comments. A short explanation should be enough to tell people if they need to bother with it or not, or remind them of what it is. For full details, they can go to the provided URL. To add some more fuel to the fire, are those in the no-comments, bare-bone camp going to argue for cleaning up pg_hba.conf as well? I could see having *one* comment at the top of the file giving pointers to where to read the documentation. I think this is the one thing everyone agrees on so far. :) - -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200808201143 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkisPOcACgkQvJuQZxSWSsjoFACfezftBPQHZ63B0BgTVhDcePZb I0oAoKZhuL+oRJguXQCGsER9P52WmTiJ =aIpg -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] A smaller default postgresql.conf
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 One more benefit of a small file is that it makes it easier to ask someone please attach a copy of your postgresql.conf file; rather than please send the output of grep -v '^[]*#' postgresql.conf | grep = or worse Can you recall what you changed? Er, what's so hard about asking them to attach the file, no matter the size? Seems safer to ask the average Joe to just attach the entire file rather than worrying about if they know how to use grep or getting the syntax of that command string correct. Can you recall what you changed? Introduce them to 'ci -l' and you've made a friend for life. :) - -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200808201151 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkisPe8ACgkQvJuQZxSWSshcRgCg73XeQhsUsASiqv6FKQSYO9ms OboAoND1f5dvpLasLsJdqknx9g9mIUkL =djcQ -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] make dist does not work in VPATH
Alvaro Herrera wrote: It seems we're neglecting to copy GNUmakefile into the temporary distdir: make -C postgresql-8.3.3 distprep make[1]: entrant dans le répertoire « /home/alvherre/Code/CVS/pgsql/build/83_rel/postgresql-8.3.3 » make[1]: *** Pas de règle pour fabriquer la cible « distprep ». Arrêt. make[1]: quittant le répertoire « /home/alvherre/Code/CVS/pgsql/build/83_rel/postgresql-8.3.3 » make: *** [distdir] Erreur 2 Hmm. When you do an in-tree build, the distdir copy contains a configured source tree. When you do an out-of-tree build, the distdir copy only the bare source tree, because the configure output files are in the build tree, which is not copied. Fix option 1 would be to copy the build tree as well, if it is different from the source tree. Since the build tree contains a bunch of symlinks back to the source tree, this would probably need some careful file handling to not overwrite the real files with symlinks or something like that. Fix option 2 is to rerun configure before make distprep. -- 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] A smaller default postgresql.conf
On Wed, 20 Aug 2008 15:49:39 - Greg Sabino Mullane [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Sure, why not? Clarity should always trump brevity. The only people who gain from a comment-less file are the ones who are already expert in it. You are right, Clarity always trumps brevity but then again, some things are only clear if they are brief. Point being, the documentation in the postgresql.conf isn't really useful and to make it useful; would make the configuration file itself not useful. A succinct file with directed links that are explicitly telling you, Don't be stupid read the docs is a very good idea. To add some more fuel to the fire, are those in the no-comments, bare-bone camp going to argue for cleaning up pg_hba.conf as well? Well I would. I find the pg_hba.conf obnoxious. Especially if we pushed the documentation to a man page and windows help. Sincerely, Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- 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] A smaller default postgresql.conf
Greg Sabino Mullane [EMAIL PROTECTED] writes: So your plan is that postgresql.conf will be approximately two thousand lines long, before the user has ever touched it at all? (Two hundred or so GUC variables and ten lines of comments for each one) Sure, why not? Clarity should always trump brevity. The only people who gain from a comment-less file are the ones who are already expert in it. I don't think that having to guess which parts of a 2000-line file represent local changes, and which are just defaults, is much of an improvement in clarity. Besides, the file is already long enough to require use of an editor's find function. This entire discussion is about fixing that ;-) 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] A smaller default postgresql.conf
On Wed, Aug 20, 2008 at 09:08:02AM -0700, Joshua D. Drake wrote: On Wed, 20 Aug 2008 15:49:39 - Greg Sabino Mullane [EMAIL PROTECTED] wrote: Sure, why not? Clarity should always trump brevity. The only people who gain from a comment-less file are the ones who are already expert in it. You are right, Clarity always trumps brevity but then again, some things are only clear if they are brief. Point being, the documentation in the postgresql.conf isn't really useful and to make it useful; would make the configuration file itself not useful. A succinct file with directed links that are explicitly telling you, Don't be stupid read the docs is a very good idea. And we're back to man pages and CHM files. How big a project would that latter be? Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [FINALLY] the TODO list has migrated to Wiki
Hi, Thanks to Brendan Jurd, who spent a lot of effort in creating useful Mediawiki templates, we now have moved the TODO list to the Wiki site. The new official location for the TODO list is here: http://wiki.postgresql.org/wiki/Todo:Todo I hereby kindly request the WWW team to update any references to point to the new address; perhaps install a redirection in http://www.postgresql.org/docs/faqs.TODO.html to the new location. The move has been approved by Bruce, the current maintainer. I hope that he continues to maintain the new version. While many details have been sorted out during the move, being a wiki there is nothing set in stone. Feel free to do changes like improve the markup or the templates so that things look better (after appropriate discussion), but if you intend to make changes like mark items completed, add new items, or remove items, please email pgsql-hackers as has been Bruce's tradition. -- 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] SeqScan costs
On Aug 18, 2008, at 11:49 AM, Tom Lane wrote: Perhaps what's also needed here is to measure just how accurate the cpu_* costs are. Perhaps they need to be raised somewhat if we're underestimating the cost of digging through 200 tuples on a heap page and the benefit of a binary search on the index tuples. Possibly. I doubt anyone's ever taken a hard look at the cpu_xxx values. Josh Berkus indicated at PGCon that he's had luck *decreasing* the CPU costs, but IIRC that was mostly on OLAP systems. It seems we need some real data here. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] [pgsql-www] [FINALLY] the TODO list has migrated to Wiki
On Wed, 20 Aug 2008 13:12:15 -0400 Alvaro Herrera [EMAIL PROTECTED] wrote: The move has been approved by Bruce, the current maintainer. I hope that he continues to maintain the new version. This is great! I only have one small request. The font is really small and I have pretty good eyesight. Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- 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] [FINALLY] the TODO list has migrated to Wiki
Joshua Drake wrote: On Wed, 20 Aug 2008 13:12:15 -0400 Alvaro Herrera [EMAIL PROTECTED] wrote: The move has been approved by Bruce, the current maintainer. I hope that he continues to maintain the new version. This is great! I only have one small request. The font is really small and I have pretty good eyesight. How does it compare to the font size of the previous version of the TODO list? -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://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] [FINALLY] the TODO list has migrated to Wiki
Alvaro Herrera wrote: Hi, Thanks to Brendan Jurd, who spent a lot of effort in creating useful Mediawiki templates, we now have moved the TODO list to the Wiki site. The new official location for the TODO list is here: http://wiki.postgresql.org/wiki/Todo:Todo I hereby kindly request the WWW team to update any references to point to the new address; perhaps install a redirection in http://www.postgresql.org/docs/faqs.TODO.html to the new location. The move has been approved by Bruce, the current maintainer. I hope that he continues to maintain the new version. While many details have been sorted out during the move, being a wiki there is nothing set in stone. Feel free to do changes like improve the markup or the templates so that things look better (after appropriate discussion), but if you intend to make changes like mark items completed, add new items, or remove items, please email pgsql-hackers as has been Bruce's tradition. The wiki has a similar appearance compared to the old TODO html file, and allows easy editing, either per TODO section or the entire file; the markup is also simple. This should allow other people to maintain the TODO list, as they have done with the commit fest list. I believe TODO and TODO.html files should now be removed from CVS. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://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] Improving non-joinable EXISTS subqueries
Kevin Grittner [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] wrote: [ complicated scheme for improving planning of EXISTS ] So I'd be very happy to see this work done, not because I can't find a workaround, but because trying to teach all the programmers tricky hand-optimizations is a losing battle, and if I lose that battle the queries degenerate into spaghetti-land. I spent some time looking at this, and soon grew rather discouraged: even the very first step of what I'd had in mind, which was to delay replacement of uplevel Vars with Params until late in the planning process, looks like it will destabilize large amounts of code that aren't particularly related to the problem at hand. (Most of the planner blithely assumes that it will never see an uplevel Var, and tends to just treat any Var as being of the current query level.) So I backed off and thought some more, and eventually came to this conclusion: when we have an EXISTS that could be done both ways, why not just generate plans for both ways, and leave the decision which to use until later? Like maybe even execution time? We have speculated in the past about having alternative plans that could be conditionally executed based on information not available at planning time. This could be seen as a first experiment in that direction. I am not thinking of a general-purpose AlternativePlan kind of execution node, because SubPlans aren't actually part of the main plan-node tree, but an AlternativeSubPlans expression node type might work. The two issues that would obviously have to be faced to make this work are: 1. While the planner is estimating evaluation costs of the qual conditions for the upper query, which EXISTS implementation do we assume will be used? It might be that we could still use my original idea of providing cost_qual_eval() with some context about the likely number of calls, but what I'm thinking at the moment is that it's not worth the trouble, because it isn't going to matter that much. Either possibility is expensive enough compared to ordinary qual conditions that the planner will be driven in the direction of plans that minimize the number of EXISTS evaluations, and that's all that we really care about. So I'd be inclined to just use the numbers for the base (non hashed) implementation and be done with it. 2. How will the executor make the decision which to use? Well, it's got access to the overall rowcount estimates that the planner made. What I'm thinking of doing is having the AlternativeSubPlans node look at the rowcount estimate of its immediate parent Plan node. This is actually exactly the right number for a subplan in the targetlist of the Plan node. For a subplan in the qual list, it's an underestimate, but probably not an enormous underestimate. (Assuming that the subplan is at the end of the qual list, which is where it'd normally be, the expected number of calls of the subplan would be the output rowcount estimate divided by the estimated selectivity of the subplan qual --- but at present the latter is always 0.5 ...) Another technique that we could play with is to have the AlternativeSubPlans node track the actual number of calls it gets, and switch from the retail implementation to the hashed implementation if that exceeds a threshold. This'd provide some robustness in the face of bad estimates, although of course it's not optimal compared to having made the right choice to start with. Thoughts? 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] [FINALLY] the TODO list has migrated to Wiki
Alvaro Herrera wrote: Hi, Thanks to Brendan Jurd, who spent a lot of effort in creating useful Mediawiki templates, we now have moved the TODO list to the Wiki site. Yay! Thanks to Brendan for helping out with that! The new official location for the TODO list is here: http://wiki.postgresql.org/wiki/Todo:Todo I hereby kindly request the WWW team to update any references to point to the new address; perhaps install a redirection in http://www.postgresql.org/docs/faqs.TODO.html to the new location. Done on both accounts. The move has been approved by Bruce, the current maintainer. I hope that he continues to maintain the new version. And let's keep the version in CVS around for a couple of days to let things settle before we do a cvs remove on it.. //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] SHOW ALL doesn't actually SHOW ALL
Folks, I've noticed that neither SHOW ALL nor SELECT ... FROM pg_settings shows the value of custom GUCs, even though SHOW will do so for any given one. For example: SHOW plperl.use_strict; plperl.use_strict --- true (1 row) SELECT * FROM pg_settings WHERE name = 'plperl.use_strict'; name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val --+-+--+--+++-+-++-+- (0 rows) Is this a bug? Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] [FINALLY] the TODO list has migrated to Wiki
On Wed, Aug 20, 2008 at 10:26:11AM -0700, Joshua D. Drake wrote: On Wed, 20 Aug 2008 13:12:15 -0400 Alvaro Herrera [EMAIL PROTECTED] wrote: The move has been approved by Bruce, the current maintainer. I hope that he continues to maintain the new version. This is great! I only have one small request. The font is really small and I have pretty good eyesight. Fixed :) Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] SHOW ALL doesn't actually SHOW ALL
David Fetter [EMAIL PROTECTED] writes: I've noticed that neither SHOW ALL nor SELECT ... FROM pg_settings shows the value of custom GUCs, even though SHOW will do so for any given one. Yeah, that's intentional, because what the code is designed to do is allow GUC values for a user-written module to be specified before the user-written module has been loaded. It's expecting the user-written code to come along and issue a DefineCustomXXXVariable call so that it will know how (or whether) to display the variable. This ties back into previous discussions about how using this facility for random user-set values is an abuse, and we ought to instead provide some way of explicitly declaring user variables. 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] [FINALLY] the TODO list has migrated to Wiki
On Wed, 20 Aug 2008 10:53:57 -0700 David Fetter [EMAIL PROTECTED] wrote: This is great! I only have one small request. The font is really small and I have pretty good eyesight. Fixed :) Much better, thanks! Joshua D. Drake Cheers, David. -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- 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] [FINALLY] the TODO list has migrated to Wiki
Bruce Momjian [EMAIL PROTECTED] writes: I believe TODO and TODO.html files should now be removed from CVS. +1. Leaving them in CVS would just result in confusion. It might make sense to leave TODO still in the file set, but reduce its content to a pointer to the wiki page. 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] [FINALLY] the TODO list has migrated to Wiki
Magnus Hagander [EMAIL PROTECTED] writes: And let's keep the version in CVS around for a couple of days to let things settle before we do a cvs remove on it.. Why? cvs remove is reversible, if it comes to that. 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] [FINALLY] the TODO list has migrated to Wiki
Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: And let's keep the version in CVS around for a couple of days to let things settle before we do a cvs remove on it.. Why? cvs remove is reversible, if it comes to that. Good pt. I was mixing it up with the sucky way cvs deals with directory removals. My bad, suggestion withdrawn. //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] Bogus TODO item
Idly thumbing through the new TODO list, I noticed that the second item from the bottom (about how we don't want optional AS) has been superseded by events ... http://archives.postgresql.org/pgsql-committers/2008-02/msg00172.php 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] Bogus TODO item
Tom Lane wrote: Idly thumbing through the new TODO list, I noticed that the second item from the bottom (about how we don't want optional AS) has been superseded by events ... http://archives.postgresql.org/pgsql-committers/2008-02/msg00172.php Good point, removed. I didn't mark it as done becuase it is unclear what done means for a not wanted item. ;-) -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://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] [FINALLY] the TODO list has migrated to Wiki
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I believe TODO and TODO.html files should now be removed from CVS. +1. Leaving them in CVS would just result in confusion. It might make sense to leave TODO still in the file set, but reduce its content to a pointer to the wiki page. Done. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://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] SHOW ALL doesn't actually SHOW ALL
On Wed, Aug 20, 2008 at 01:56:50PM -0400, Tom Lane wrote: David Fetter [EMAIL PROTECTED] writes: I've noticed that neither SHOW ALL nor SELECT ... FROM pg_settings shows the value of custom GUCs, even though SHOW will do so for any given one. Yeah, that's intentional, because what the code is designed to do is allow GUC values for a user-written module to be specified before the user-written module has been loaded. It's expecting the user-written code to come along and issue a DefineCustomXXXVariable call so that it will know how (or whether) to display the variable. I'm not sure I understand why that's the right thing. I stumbled across it while trying to improve some SQL-only checks in DBI-Link, and since SHOW doesn't quite act like SELECT, it's causing some trouble. This ties back into previous discussions about how using this facility for random user-set values is an abuse, and we ought to instead provide some way of explicitly declaring user variables. How about seeing what all of them are via SELECT? I guess I'm missing why pg_show_all_settings(), the function underlying the pg_settings view, is actually doing pg_show_settings_except_the_ones_you_actually_set() :P Cheers, David. regards, tom lane -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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 item: Allow data to be pulled directly from indexes
I have added this email's URL to TODO under tuple visibility. --- Karl Schnaitter wrote: Sometime last year, a discussion started about including visibility metadata to avoid heap fetches during an index scan: http://archives.postgresql.org/pgsql-patches/2007-10/msg00166.php http://archives.postgresql.org/pgsql-patches/2008-01/msg00049.php I think the last discussion on this was in April: http://archives.postgresql.org/pgsql-hackers/2008-04/msg00618.php (last item) I have worked with the current patch, and I have some thoughts about that approach and the approaches listed in the TODO item. The TODO lists three approaches, in short (1) Add a bit for an index tuple that indicates visible or maybe visible. (2) Use a per-table bitmap that indicates which pages have at least one tuple that is not visible to all transactions. (3) Same as (2) but at the granularity of one bit per table. The approach in the patch is different: (4) Add transaction ids, etc to the index tuple (totaling 16 bytes) I would group (1) (4) together and (2) (3) together. I think the time and space trade-offs are pretty obvious, so I won't waste time on those. (1) (4) require an UPDATE or DELETE to twiddle the old index tuple. Tom has noted (in the linked message) that this is not reliable if the index has any expression-valued columns, because it is not always possible to find the old index entry. For this reason, the proposed patch does not keep visibility metadata for indexes on expressions. This seems like a reasonable limitation --- indexed expressions are just less efficient. The main difference between (1) (4) is that (1) will sometimes require heap lookups and (4) never will. Moreover, the heap lookups in (1) will be difficult for the optimizer to estimate, unless some special statistics can be maintained for this purpose. I should mention there is a major flaw in the patch, because it puts pointers to HOT tuples in the index, in order to capture the different transaction ids in the chain. I think this can be fixed by only pointing to the root of the HOT chain, and setting xmin/xmax to the entire range of transaction ids spanned by the chain. I'm not sure about all the details (the ctid and some other bits also need to be set). (2) (3) can work for any index, and they are quite elegant in the way that the overhead does not change with the number of indexes. The TODO also notes the benefit of (2) for efficient vacuuming. Thus, I think that (2) is a great idea in general, but it does not serve the intended purpose of this TODO item. Once a page gets marked as requiring visibility checks, it cannot be unmarked until the next VACUUM. The whole point of this feature is that we are willing to be more proactive during updates in order to make index access more efficient. So in summary, I think that (2) would be nice as a separate feature, with (1) and (4) being more favorable for index-only scans. The obvious trouble with (4) is the extra space overhead. There are also issues with correctness that I mentioned (any thoughts here would be appreciated). Other than that, I would favor (4) because it offers the most stable performance. Please let me know if you agree/disagree with anything here. I need to get this feature implemented for my research, but I would also love to contribute it to the community so your opinions matter a lot. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://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] make dist does not work in VPATH
Peter Eisentraut wrote: Fix option 1 would be to copy the build tree as well, if it is different from the source tree. Since the build tree contains a bunch of symlinks back to the source tree, this would probably need some careful file handling to not overwrite the real files with symlinks or something like that. Thanks, this seems to do the trick. The only thing I'm missing here is how to implement the is the build dir different from the source dir. Any ideas? It is a shame that one needs to copy all the .o, .Po, etc files and then remove them with make distclean. It would be possible to skip linking them by having an exception in the find line, but I'm not sure if that works portably enough (i.e. is the extension common across all archs?) Note that I skip directories not already existing in distdir. This is so that the .deps directories are not included in the tarball, which would be dumb. Also, as you note, we need to handle certain symlinks; in my installation this is only needed for Makefile.port. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. Index: GNUmakefile.in === RCS file: /home/alvherre/Code/cvs/pgsql/GNUmakefile.in,v retrieving revision 1.46 diff -c -p -r1.46 GNUmakefile.in *** GNUmakefile.in 9 Feb 2007 15:55:57 - 1.46 --- GNUmakefile.in 20 Aug 2008 20:59:18 - *** distdir: *** 107,112 --- 107,126 || cp $(top_srcdir)/$$file $(distdir)/$$file; \ fi || exit; \ done + for x in `find . \\( -type d -o -path *$(distdir)* -prune \\) -o -print`; do \ + file=`expr X$$x : 'X\./\(.*\)'`; \ + dir=`dirname $$file`; \ + if test ! -d $(distdir)/$$dir ; then \ + continue; \ + fi; \ + if test -L $$file test -e $(distdir)/$$file ; then \ + continue; \ + fi; \ + ln $$file $(distdir)/$$file /dev/null 21 \ + || cp $$file $(distdir)/$$file \ + || exit; \ + done + $(MAKE) -C $(distdir) distprep $(MAKE) -C $(distdir)/doc/src/sgml/ HISTORY INSTALL regress_README cp $(distdir)/doc/src/sgml/HISTORY $(distdir)/ -- 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] Improving non-joinable EXISTS subqueries
Tom Lane [EMAIL PROTECTED] wrote: when we have an EXISTS that could be done both ways, why not just generate plans for both ways, and leave the decision which to use until later? That seems good to me. The costs for the slower plan generally come out much higher. When the run times are close, the one that edges out the other doesn't always win, but that's to be expected. EXISTS is hardly unique in that respect. Competing on costs seems better than some more mechanical approach. Like maybe even execution time? We have speculated in the past about having alternative plans that could be conditionally executed based on information not available at planning time. This could be seen as a first experiment in that direction. I am not thinking of a general-purpose AlternativePlan kind of execution node, because SubPlans aren't actually part of the main plan-node tree, but an AlternativeSubPlans expression node type might work. The two issues that would obviously have to be faced to make this work are: 1. While the planner is estimating evaluation costs of the qual conditions for the upper query, which EXISTS implementation do we assume will be used? It might be that we could still use my original idea of providing cost_qual_eval() with some context about the likely number of calls, but what I'm thinking at the moment is that it's not worth the trouble, because it isn't going to matter that much. Either possibility is expensive enough compared to ordinary qual conditions that the planner will be driven in the direction of plans that minimize the number of EXISTS evaluations, and that's all that we really care about. So I'd be inclined to just use the numbers for the base (non hashed) implementation and be done with it. Seems reasonable from this point of view: it seems like you'd never choose a plan worse than the current releases, although you might sometimes miss a plan that would be even faster than the suggested improvement finds. I think it makes sense to defer this until such time (if ever) that it is shown to be worth the effort. 2. How will the executor make the decision which to use? Well, it's got access to the overall rowcount estimates that the planner made. What I'm thinking of doing is having the AlternativeSubPlans node look at the rowcount estimate of its immediate parent Plan node. This is actually exactly the right number for a subplan in the targetlist of the Plan node. For a subplan in the qual list, it's an underestimate, but probably not an enormous underestimate. (Assuming that the subplan is at the end of the qual list, which is where it'd normally be, the expected number of calls of the subplan would be the output rowcount estimate divided by the estimated selectivity of the subplan qual --- but at present the latter is always 0.5 ...) If you meant multiplied by 0.5 I think I followed that. Made sense. Another technique that we could play with is to have the AlternativeSubPlans node track the actual number of calls it gets, and switch from the retail implementation to the hashed implementation if that exceeds a threshold. This'd provide some robustness in the face of bad estimates, although of course it's not optimal compared to having made the right choice to start with. That sounds interesting, but unless it has value as a prototype for other runtime adaptivity, it sounds like a lot of work for the benefit. I'm not that unhappy with the estimates I'm getting in a properly tuned database. And the execution-time work to process some number of rows this way seems likely to far exceed the work to refine the estimates and costing used to choose a plan. -Kevin -- 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] Improving non-joinable EXISTS subqueries
Another technique that we could play with is to have the AlternativeSubPlans node track the actual number of calls it gets, and switch from the retail implementation to the hashed implementation if that exceeds a threshold. This'd provide some robustness in the face of bad estimates, although of course it's not optimal compared to having made the right choice to start with. Ideally you'd want to set that threshold dynamically. If you expect x calls and midway through execution notice that you're already up to 2x calls, the right thing to do depends a lot on whether you're 1% done or 99% done. Logic of this type also opens a bit of a can of worms, in that there are probably many other situations in which it's possible to notice that your estimates are off and shift gears in mid-query, but how much are you willing to slow down the queries where there isn't a problem to speed up the ones where there is? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] postgres-R
Anyone knows a link that has some docs about how to get that setup ? Also is it stable enough for production ? I though getting postgreSQL from CVS and compiling was not such a good idea since the CVSROOT is probably not stable, is that wrong ? since I could not find info out there this is what I have done to check it out and I downloaded the postgres-r snapshoot patch snapshot 2008-08-13 88 kb postgres-r-20080813.diff.bz2 cvs -d :pserver:[EMAIL PROTECTED]:/projects/cvsroot checkout -D 2008-08-13 -P pgsql Any comment much appreciated - thank you M -- 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] postgres-R
Marcelo Martins wrote: Anyone knows a link that has some docs about how to get that setup ? Also is it stable enough for production ? I though getting postgreSQL from CVS and compiling was not such a good idea since the CVSROOT is probably not stable, is that wrong ? since I could not find info out there this is what I have done to check it out and I downloaded the postgres-r snapshoot patch snapshot 2008-08-13 88 kbpostgres-r-20080813.diff.bz2 cvs -d :pserver:[EMAIL PROTECTED]:/projects/cvsroot checkout -D 2008-08-13 -P pgsql http://www.postgresql.org/docs/current/static/cvs.htm Any comment much appreciated - thank you M -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
