Re: [HACKERS] Core team statement on replication in PostgreSQL
Hello Andrew, Andrew Sullivan wrote: Yes. And silent as ever. :-) Are the slides of your PgCon talk available for download somewhere? BTW: up until recently, there was yet another mailing list: [EMAIL PROTECTED] It was less focused on hooks and got at least some traffic. :-) Are those mails still archived somewhere? Regards Markus -- 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] Core team statement on replication in PostgreSQL
Stephen Denne wrote: Hannu Krosing wrote: The simplest form of synchronous wal shipping would not even need postgresql running on slave, just a small daemon which reports when wal blocks are a) received and b) synced to disk. While that does sound simple, I'd presume that most people would want the guarantee of the same version of postgresql installed wherever the logs are ending up, with the log receiver speaking the same protocol version as the log sender. I imagine that would be most easily achieved through using something like the continuously restoring startup mode of current postgresql. Hmm, WAL version compatibility is an interesting question. Most minor releases hasn't changed the WAL format, and it would be nice to allow running different minor versions in the master and slave in those cases. But it's certainly not unheard of to change the WAL format. Perhaps we should introduce a WAL version number, similar to catalog version? -- 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] Core team statement on replication in PostgreSQL
Hmm, WAL version compatibility is an interesting question. Most minor releases hasn't changed the WAL format, and it would be nice to allow As I remember, high minor version should read all WALs from lowers, but it isn't true for opposite case and between different major versions. running different minor versions in the master and slave in those cases. But it's certainly not unheard of to change the WAL format. Perhaps we should introduce a WAL version number, similar to catalog version? Agree. Right now it only touches warm-stand-by servers, but introducing simple log-shipping and based on it replication will cause a lot of unobvious errors/bugs. Is it possible to use catalog version number as WAL version? -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ -- 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] Core team statement on replication in PostgreSQL
On Wed, 2008-06-04 at 11:13 +0300, Heikki Linnakangas wrote: Hmm, WAL version compatibility is an interesting question. Most minor releases hasn't changed the WAL format, and it would be nice to allow running different minor versions in the master and slave in those cases. But it's certainly not unheard of to change the WAL format. Perhaps we should introduce a WAL version number, similar to catalog version? Would that also cover possible differences in page size, 32bit OS vs. 64bit OS, different timestamp flavour, etc. issues ? AFAIR, all these things can have an influence on how the data is written and possibly make the WAL incompatible with other postgres instances, even if the exact same version... Cheers, Csaba. -- 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] Core team statement on replication in PostgreSQL
Csaba Nagy wrote: On Wed, 2008-06-04 at 11:13 +0300, Heikki Linnakangas wrote: Hmm, WAL version compatibility is an interesting question. Most minor releases hasn't changed the WAL format, and it would be nice to allow running different minor versions in the master and slave in those cases. But it's certainly not unheard of to change the WAL format. Perhaps we should introduce a WAL version number, similar to catalog version? Would that also cover possible differences in page size, 32bit OS vs. 64bit OS, different timestamp flavour, etc. issues ? AFAIR, all these things can have an influence on how the data is written and possibly make the WAL incompatible with other postgres instances, even if the exact same version... These are already covered by the information in pg_control. -- 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] Core team statement on replication in PostgreSQL
Teodor Sigaev wrote: Is it possible to use catalog version number as WAL version? No, because we don't change the catalog version number in minor releases, even though we might change WAL format. -- 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] cannot use result of (insert..returning)
dvs wrote: Hello, I need to use query like: select (insert into test (a) values (x) returning b),c from anytable where condition but it say ERROR: syntax error at or near into Is this a bug? No, it's a known limitation. -- 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] intercepting WAL writes
On May 29, 2008, at 1:57 AM, Hannu Krosing wrote: On Wed, 2008-05-28 at 19:11 -0400, Mike wrote: Can somebody point to the most logical place in the code to intercept the WAL writes? (just a rough direction would be enough)- or if this doesn’t make sense at all, another suggestion on where to get the data? I don't think that intercepting (and then decoding ) WAL is very productive. It is too low level to be of much help. The way I'd do it would be using pgQ from SkyTools package where change events can be queued when happening and then moved in bulk to memcached with not too much effort. Actually, you might look one step further and see about adding memcached as a subscriber type to londiste; it might be easier than just using PgQ... not that using PgQ would be all that hard. Also, keep in mind that no matter what you do you'll always have a race condition between data in the database and in memcached. -- 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] DISTINCT - GROUP BY
On Jun 3, 2008, at 9:03 AM, David Fetter wrote: On Tue, Jun 03, 2008 at 03:36:44PM +0200, Pavel Stehule wrote: Hello David http://www.postgresql.org/docs/faqs.TODO.html Consider using hash buckets to do DISTINCT, rather than sorting This would be beneficial when there are few distinct values. This is already used by GROUP BY. It's nice to see that this is kinda on the TODO, but it doesn't address the question I asked, which is, how would I get the planner to rewrite DISTINCTs as the equivalent GROUP BYs? :) IIRC, the patch to allow GROUP BY to do hashing is fairly recent... you could probably find it in archives. -- 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] Proposal: new function array_init
On Jun 2, 2008, at 11:46 AM, Tom Lane wrote: * Should the fill value be the first parameter instead of the last? +1. The other way just seems weird, at least to me. -- 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] Proposal: new function array_init
2008/6/4 Decibel! [EMAIL PROTECTED]: On Jun 2, 2008, at 11:46 AM, Tom Lane wrote: * Should the fill value be the first parameter instead of the last? +1. The other way just seems weird, at least to me. can you write reason? I put fill value on end, because it is like default value: array_set(array[2,2]); array_set(array[2,2], 0); array_set(array[2,2],array[1,1]); array_set(array[2,2],array[1,1], 0); - and it's analogy to memset fce etc set(where, value); Pavel -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 -- 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] Overhauling GUCS
On Sat, May 31, 2008 at 08:36:54PM +0100, Gregory Stark wrote: 4) By shifting from a model where postgresql.conf is document-formatted and hand-edited to one where it's machine generated, it becomes vastly easier to write simple utilities to manage these settings. Right now, the big obstacle to things like SET PERSISTENT is how to we preseve the hand-edited comments in the file -- and the answer is we *don't.* What this sounds like is a sly way to try to get rid of postgresql.conf entirely and replace it with parameters stored in the database so admins would adjust the parameters using an SQL syntax rather than a text file. There are pros and cons of such a system but I think for newbie admins that would be a thousand times *more* baffling. You would have to learn new commands and have no holistic view of what parameters had been set, what related parameters might exist. You also have no way to keep the file in a version control system or sync across servers etc. It doesn't have to be in the database. I kinda like Oracle's method here; they default to storing config options in a binary file that (iirc) you can change via commands, but they also provide a way to turn that binary file into text (and back). That would allow for a lot of options when it comes to configuring stuff. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 pgpevhyANSHLm.pgp Description: PGP signature
Re: [HACKERS] keyword list/ecpg
On Tue, May 27, 2008 at 09:44:02AM -0400, Tom Lane wrote:
If we were going to do that, I'd want it to go all the way and somehow
generate the common parts of the two .y files from a single source.
That'd be enough of a step forward that it would be worth whatever
ugliness is needed to make it happen ...
Any idea how to make this happen? We could of course do it with a lot of
ifdef like sequences:
rule {
IF ECPGCOMPILE
ecpg stuff
ELSE
backend stuff
ENDIF
}
Would you go into this direction?
Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED]
Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use 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] Overhauling GUCS
On Mon, Jun 02, 2008 at 10:12:06AM -0400, Tom Lane wrote: Greg Smith [EMAIL PROTECTED] writes: Joshua has been banging a drum for a while now that all this data needs to get pushing into the database itself. This is, very simply, not going to happen. Shall we go over the reasons why not, one more time? snip I have no objection to providing alternative ways to edit the configuration data, but the primary source of the settings is going to continue to be an editable text file. Any proposals for alternatives-to-a-text-editor have to work within that reality. There's no reason that the server has to deal with a text file. I completely agree that there must be a method to change settings even if the database isn't running, but that method does not necessarily need to be a text file. If we can come up with a standard API for reading and writing config changes, we (or anyone else) can write any number of tools to deal with the settings. And once we have an API, we can provide a SQL interface on top of it. Instead of focusing on exactly what the 'new postgresql.conf' is going to look like we should focus on creating a clean configuration API. Once we have that we can figure out what (if anything) we're doing with the existing .conf, and what a new one (if it exists) might look like. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 -- 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] rfc: add pg_dump options to dump output
On Mon, Jun 02, 2008 at 02:06:57PM -0700, Josh Berkus wrote: Robert, Anyone see any issues with this? Should there be other information taken into account? Does this need to be an option itself, or can we just do it in all cases? +1 to do it in all cases. +1. I've definitely been in situations where I had to try and track down what options had been used originally. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 pgppuIxLof0xp.pgp Description: PGP signature
Re: [HACKERS] Change lock requirements for adding a trigger
On Jun 3, 2008, at 5:04 PM, Simon Riggs wrote: On Tue, 2008-06-03 at 16:48 -0500, Decibel! wrote: On May 30, 2008, at 9:51 AM, Simon Riggs wrote: On Thu, 2008-05-29 at 19:18 -0500, Decibel! wrote: Is there a reason that we can't add a trigger to a table while a select is running? This is a serious pain when trying to setup londiste or slony. This is constrained by locking. There are a subset of DDL commands that might be able to be performed with just an ExclusiveLock or ShareLock rather than an AccessExclusiveLock. Nobody has studied which sub-statements this might apply to, but its do-able since CREATE INDEX already does this. Is there a good way to determine this other than depending on knowledge of the source code? The source doesn't know yet. So just analysis and thinking. The mechanism to hold less than an AccessExclusiveLock it doesn't exist yet, but it never will unless we have a list of the things that might be performed correctly with a lower level of lock. Ok, I'll take a stab at such a list. Can anyone think of any reasons why CREATE TRIGGER couldn't get by with ShareLock? -- 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] Overhauling GUCS
Decibel! wrote: There's no reason that the server has to deal with a text file. I completely agree that there must be a method to change settings even if the database isn't running, but that method does not necessarily need to be a text file. If we can come up with a standard API for reading and writing config changes, we (or anyone else) can write any number of tools to deal with the settings. And once we have an API, we can provide a SQL interface on top of it. Once in a lifetime, a man should plant a tree, father a child, and write an editor... :-) Hiding the storage of config parameters opaquely behind an API is something I've been hating for a long time on win32. When reading this thread, I'm wondering if anybody ever saw a config file for a complex software product that was easily editable and understandable. I don't know one. If there was one, it'd be nice to know it so we can learn from it. IMHO the best compromise in machine and human readability is an XML format. It's easily decorateable with comments, easily interpreted and a pg_settings view could enhance it with even more comments, so an editor using pgsql functions (to read and write postresql.conf.xml) could be enabled to supply comprehensive help. Regards, Andreas -- 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] keyword list/ecpg
Michael Meskes [EMAIL PROTECTED] writes:
On Tue, May 27, 2008 at 09:44:02AM -0400, Tom Lane wrote:
If we were going to do that, I'd want it to go all the way and somehow
generate the common parts of the two .y files from a single source.
Any idea how to make this happen? We could of course do it with a lot of
ifdef like sequences:
rule {
IF ECPGCOMPILE
ecpg stuff
ELSE
backend stuff
ENDIF
}
Would you go into this direction?
Ugh :-(.
I have not spent much time looking at the ecpg grammar, so feel free to
laugh this off, but I had the impression that all the rules derived from
the backend grammar have boilerplate action sections (ie, just join the
strings together). So I was hoping that we could leave the backend's
.y file more or less as-is, and write a perl script that would go
through it and replace each { ... } action with a suitable cat_str call,
which it could build on-the-fly by counting the number of rule tokens.
Then combine that output with the ecpg-specific rules taken from a
separate source file. Obviously there would have to be a few small
kluges, but an #if-like mechanism might suffice for those. An #if
in every rule is way past my tolerance for ugliness though, especially
when one arm of it is essentially mechanically-generatable code.
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] Overhauling GUCS
* Andreas Pflug [EMAIL PROTECTED] [080604 10:20]: Hiding the storage of config parameters opaquely behind an API is something I've been hating for a long time on win32. ;-) When reading this thread, I'm wondering if anybody ever saw a config file for a complex software product that was easily editable and understandable. I don't know one. If there was one, it'd be nice to know it so we can learn from it. PostreSQL, Apache, X.org They are all easily editable, and understandable, in the sense that I understand that I'm supposed to edit the line, changing the value (following the comments list of accepted values) They are less understandable if you mean that I know the implications of any change I make. But guess what, having those values inputed through some other mechanism (like a GUI config file editor, a SQL statement, or a nice pgadmin-SQL-hiding-interface isn't going to change that part of understandable. That part of understandable only comes through good documentation and reference material, which is universally applicable to any config method. IMHO the best compromise in machine and human readability is an XML format. It's easily decorateable with comments, easily interpreted and a pg_settings view could enhance it with even more comments, so an editor using pgsql functions (to read and write postresql.conf.xml) could be enabled to supply comprehensive help. Well, In my past, I've generally not got around to installing and using software that reqired me to edit some jumble of XML. Ya, maybe I'm lucky. And since I've got a lot invested in PG, I'ld be forced to of PG moved to an XML config, but I'ld be forced to kicking and screaming... I just *know* that I'ld reload/restart postmaster some time, and the config file wouldn't be quite correct, and I'ld search for 10 minutes trying to find the extra (or lack) , or missing closing /... But maybe most people are better at parsing XML than me. And that also may be because I've actively avoided it for so long ;-) I don't know *how* I'ld decorate my XML config file with comments and history the way I do my text-based #-commented config files. a. -- Aidan Van Dyk Create like a god, [EMAIL PROTECTED] command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] Core team statement on replication in PostgreSQL
On Wed, Jun 04, 2008 at 09:24:20AM +0200, Markus Schiltknecht wrote: Are the slides of your PgCon talk available for download somewhere? There weren't any slides, really (there were 4 that I put up in case the cases I was discussing needed back-references, but they didn't). Joshua tells me that I'm supposed to make the paper readable and put it up on Command Prompt's website, so I will soon. BTW: up until recently, there was yet another mailing list: [EMAIL PROTECTED] It was less focused on hooks and got at least some traffic. :-) Are those mails still archived somewhere? Unless whoever was operating that list moved it to pgfoundry, I doubt it (except on backups somewhere). 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] Core team statement on replication in PostgreSQL
Heikki Linnakangas [EMAIL PROTECTED] writes: Hmm, WAL version compatibility is an interesting question. Most minor releases hasn't changed the WAL format, and it would be nice to allow running different minor versions in the master and slave in those cases. But it's certainly not unheard of to change the WAL format. Perhaps we should introduce a WAL version number, similar to catalog version? Yeah, perhaps. In the past we've changed the WAL page ID field for this; I'm not sure if that's enough or not. It does seem like a good idea to have a way to check that the slaves aren't trying to read a WAL version they don't understand. Also, it's possible that the WAL format doesn't change across a major update, but you still couldn't work with say an 8.4 master and an 8.3 slave, so maybe we need the catalog version ID in there too. 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: new function array_init
Pavel Stehule [EMAIL PROTECTED] writes: I put fill value on end, because it is like default value: array_set(array[2,2]); array_set(array[2,2], 0); Huh? That's completely nonsensical ... where will you determine the type of the array, if you don't have a sample element? The fact that the fill value is determining the array type is why it seems important enough to put first. 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] Change lock requirements for adding a trigger
Decibel! [EMAIL PROTECTED] writes: Ok, I'll take a stab at such a list. Can anyone think of any reasons why CREATE TRIGGER couldn't get by with ShareLock? pg_class.reltriggers. 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] Overhauling GUCS
Aidan Van Dyk wrote: * Andreas Pflug [EMAIL PROTECTED] [080604 10:20]: Hiding the storage of config parameters opaquely behind an API is something I've been hating for a long time on win32. ;-) When reading this thread, I'm wondering if anybody ever saw a config file for a complex software product that was easily editable and understandable. I don't know one. If there was one, it'd be nice to know it so we can learn from it. PostreSQL, Apache, X.org They are all easily editable, and understandable, in the sense that I understand that I'm supposed to edit the line, changing the value (following the comments list of accepted values) They are less understandable if you mean that I know the implications of any change I make. But guess what, having those values inputed through some other mechanism (like a GUI config file editor, a SQL statement, or a nice pgadmin-SQL-hiding-interface isn't going to change that part of understandable. That part of understandable only comes through good documentation and reference material, which is universally applicable to any config method. Right. On the editing side, a column link in pg_settings that can be used to construct an URL to postgresql.org/docs/xxx#yyy could help creating editors that support the user. Whatever a text config file will look like, you need to know exactly which parameter to use and where to locate it; even structuring parameters won't help too much for the typical starter task I installed pgsql, what to do next. IMHO the best compromise in machine and human readability is an XML format. It's easily decorateable with comments, easily interpreted and a pg_settings view could enhance it with even more comments, so an editor using pgsql functions (to read and write postresql.conf.xml) could be enabled to supply comprehensive help. Well, In my past, I've generally not got around to installing and using software that reqired me to edit some jumble of XML. Ya, maybe I'm lucky. And since I've got a lot invested in PG, I'ld be forced to of PG moved to an XML config, but I'ld be forced to kicking and screaming... I just *know* that I'ld reload/restart postmaster some time, and the config file wouldn't be quite correct, and I'ld search for 10 minutes trying to find the extra (or lack) , or missing closing /... But maybe most people are better at parsing XML than me. And that also may be because I've actively avoided it for so long ;-) Well I'm an XML evangelist either. But the usual commenting out a parameter will reset it to default on reload, no? caveat isn't funny either, or duplicate parameter settings scattered throughout your file. This may be avoided by *preferably* editing the parameters through pgsql itself; the current postgresql.conf file format isn't too machine write friendly (as I know since I wrote the pgadmin config file editor). But having a config file that can't be used with simple editors at all is a nightmare. Regards, Andreas -- 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] keyword list/ecpg
On Wed, Jun 04, 2008 at 10:21:19AM -0400, Tom Lane wrote:
Ugh :-(.
This is why I didn't want to go that route. :-)
I have not spent much time looking at the ecpg grammar, so feel free to
laugh this off, but I had the impression that all the rules derived from
the backend grammar have boilerplate action sections (ie, just join the
This is true.
strings together). So I was hoping that we could leave the backend's
.y file more or less as-is, and write a perl script that would go
through it and replace each { ... } action with a suitable cat_str call,
which it could build on-the-fly by counting the number of rule tokens.
There is some small magic to know when to have blanks in between and
when not, but that should be doable.
Then combine that output with the ecpg-specific rules taken from a
separate source file. Obviously there would have to be a few small
This might work. Anyone with good perl knowledge interested?
Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED]
Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use 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] Core team statement on replication in PostgreSQL
On Wed, 2008-06-04 at 10:40 -0400, Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: Hmm, WAL version compatibility is an interesting question. Most minor releases hasn't changed the WAL format, and it would be nice to allow running different minor versions in the master and slave in those cases. But it's certainly not unheard of to change the WAL format. Perhaps we should introduce a WAL version number, similar to catalog version? Yeah, perhaps. In the past we've changed the WAL page ID field for this; I'm not sure if that's enough or not. It does seem like a good idea to have a way to check that the slaves aren't trying to read a WAL version they don't understand. Also, it's possible that the WAL format doesn't change across a major update, but you still couldn't work with say an 8.4 master and an 8.3 slave, so maybe we need the catalog version ID in there too. And something dependent on datetime being integer. We probably won't need to encode presence of user defined types, like PostGis , being present ? - 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] Core team statement on replication in PostgreSQL
Hannu Krosing [EMAIL PROTECTED] writes: On Wed, 2008-06-04 at 10:40 -0400, Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: Hmm, WAL version compatibility is an interesting question. Most minor releases hasn't changed the WAL format, and it would be nice to allow running different minor versions in the master and slave in those cases. But it's certainly not unheard of to change the WAL format. Perhaps we should introduce a WAL version number, similar to catalog version? Yeah, perhaps. In the past we've changed the WAL page ID field for this; I'm not sure if that's enough or not. It does seem like a good idea to have a way to check that the slaves aren't trying to read a WAL version they don't understand. Also, it's possible that the WAL format doesn't change across a major update, but you still couldn't work with say an 8.4 master and an 8.3 slave, so maybe we need the catalog version ID in there too. And something dependent on datetime being integer. This thread is getting out of hand, actually. Heikki's earlier comment about pg_control reminded me that we already have a unique system identifier stored in pg_control and check that against WAL headers. So I think we already have enough certainty that the master and slaves have the same pg_control and hence are the same for everything checked by pg_control. However, since by definition pg_control doesn't change in a minor upgrade, there isn't any easy way to enforce a rule like slaves must be same or newer minor version as the master. I'm not sure that we actually *want* to enforce such a rule, though. Most of the time, the other way around would work fine. 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] Overhauling GUCS
Andreas Pflug [EMAIL PROTECTED] writes: Well I'm an XML evangelist either. But the usual commenting out a parameter will reset it to default on reload, no? caveat isn't funny either, or duplicate parameter settings scattered throughout your file. Surely everyone who is opining on this thread knows that we fixed that in 8.3. 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] keyword list/ecpg
I wouldn't mind having a stab at this if you can expand on the 'magic'
required.
(I'm interested because I might be able to use the same logic to roll a third
version of the .y for Aubit4GL outside of the Postgresql tree)
On Wednesday 04 June 2008 16:11:49 Michael Meskes wrote:
On Wed, Jun 04, 2008 at 10:21:19AM -0400, Tom Lane wrote:
Ugh :-(.
This is why I didn't want to go that route. :-)
I have not spent much time looking at the ecpg grammar, so feel free to
laugh this off, but I had the impression that all the rules derived from
the backend grammar have boilerplate action sections (ie, just join the
This is true.
strings together). So I was hoping that we could leave the backend's
.y file more or less as-is, and write a perl script that would go
through it and replace each { ... } action with a suitable cat_str call,
which it could build on-the-fly by counting the number of rule tokens.
There is some small magic to know when to have blanks in between and
when not, but that should be doable.
Then combine that output with the ecpg-specific rules taken from a
separate source file. Obviously there would have to be a few small
This might work. Anyone with good perl knowledge interested?
Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED]
Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL!
--
Mike Aubury
http://www.aubit.com/
Aubit Computing Ltd is registered in England and Wales, Number: 3112827
Registered Address : Clayton House,59 Piccadilly,Manchester,M1 2AQ
--
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] keyword list/ecpg
Mike Aubury [EMAIL PROTECTED] writes: On Wednesday 04 June 2008 16:11:49 Michael Meskes wrote: There is some small magic to know when to have blanks in between and when not, but that should be doable. I wouldn't mind having a stab at this if you can expand on the 'magic' required. Wouldn't it work to just always insert a space between tokens, no matter whether there was one originally? 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] keyword list/ecpg
It might depend on the tokens.. Are =, ++ etc single tokens ? On Wednesday 04 June 2008 17:06:44 Tom Lane wrote: Mike Aubury [EMAIL PROTECTED] writes: On Wednesday 04 June 2008 16:11:49 Michael Meskes wrote: There is some small magic to know when to have blanks in between and when not, but that should be doable. I wouldn't mind having a stab at this if you can expand on the 'magic' required. Wouldn't it work to just always insert a space between tokens, no matter whether there was one originally? regards, tom lane -- Mike Aubury http://www.aubit.com/ Aubit Computing Ltd is registered in England and Wales, Number: 3112827 Registered Address : Clayton House,59 Piccadilly,Manchester,M1 2AQ -- 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] Core team statement on replication in PostgreSQL
On Wed, 2008-06-04 at 11:37 -0400, Tom Lane wrote: This thread is getting out of hand, actually. Agreed. We should start new threads for specific things. Please. However, since by definition pg_control doesn't change in a minor upgrade, there isn't any easy way to enforce a rule like slaves must be same or newer minor version as the master. I'm not sure that we actually *want* to enforce such a rule, though. Definitely don't want to prevent minor version mismatches. We want to be able to upgrade a standby, have it catch up with the master then switchover to the new version. Otherwise we'd have to take whole replicated system down to do minor upgrades/backouts. Ugh! -- 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] Overhauling GUCS
On Jun 3, 2008, at 20:48, Greg Smith wrote: Correct, but completely off-topic regardless. One problem to be solved here is to take PostgreSQL tuning from zero to, say, 50% automatic. Wander the user lists for a few months; the number of completely misconfigured systems out there is considerable, partly because the default values for many parameters are completely unreasonable for modern hardware and there's no easy way to improve on that without someone educating themselves. Getting distracted by the requirements of the high-end systems will give you a problem you have no hope of executing in a reasonable time period. Exactly. The issue is that application developers, who are not DBAs, have no idea how to tune PostgreSQL, and postgresql.conf is daunting and confusing. So they use a different database that's faster. I think that right now postgresql.conf is designed for full-time DBAs, rather than folks who might want to target PostgreSQL for an application they're developing. We want to attract the latter (without, of course, any expense with the former). Changing how configuration works so that it's easier to understand and, if possible, at least partly automatically tunable would go a long way towards making PostgreSQL friendlier for developers, IMHO. Best, David -- 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] Overhauling GUCS
On Jun 4, 2008, at 07:19, Andreas Pflug wrote: IMHO the best compromise in machine and human readability is an XML format. It's easily decorateable with comments, easily interpreted and a pg_settings view could enhance it with even more comments, so an editor using pgsql functions (to read and write postresql.conf.xml) could be enabled to supply comprehensive help. I hope that was a joke… Best, David -- 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] Overhauling GUCS
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Exactly. The issue is that application developers, who are not DBAs, have no idea how to tune PostgreSQL, and postgresql.conf is daunting and confusing. So they use a different database that's faster. Changing some of our defaults would go a long way as well. -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkhGzCYACgkQvJuQZxSWSsi2AQCfZFa8PBZ16ljeFNmhY/YlkxF9 8KcAnjciYml1icuuxSPiKIyeE8YrVxOA =q4Cf -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] Core team statement on replication in PostgreSQL
On Wed, 2008-06-04 at 14:17 +0300, Heikki Linnakangas wrote: Would that also cover possible differences in page size, 32bit OS vs. 64bit OS, different timestamp flavour, etc. issues ? AFAIR, all these things can have an influence on how the data is written and possibly make the WAL incompatible with other postgres instances, even if the exact same version... These are already covered by the information in pg_control. Another thing that can change between systems is the collation behavior, which can corrupt indexes (and other bad things). Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] meaning of backend exit code 2?
I have a need to find out the meaning of a backend exiting unexpectedly with exit code 2. Leafing through the source of 8.1 I can't really find it. Is there anything in postgres which would exit with code 2, or should I be looking at libraries and junk dragged in by languages?
Re: [HACKERS] Overhauling GUCS
David E. Wheeler [EMAIL PROTECTED] writes: Exactly. The issue is that application developers, who are not DBAs, have no idea how to tune PostgreSQL, and postgresql.conf is daunting and confusing. So they use a different database that's faster. I take it you haven't looked at mysql's configuration file lately. They aren't actually in any better shape than we are, except that they supply several preconfigured sample files for people to choose from. 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] Core team statement on replication in PostgreSQL
Jeff Davis [EMAIL PROTECTED] writes: On Wed, 2008-06-04 at 14:17 +0300, Heikki Linnakangas wrote: These are already covered by the information in pg_control. Another thing that can change between systems is the collation behavior, which can corrupt indexes (and other bad things). That is covered by pg_control, at least to the extent of forcing the same value of LC_COLLATE. 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] Core team statement on replication in PostgreSQL
On Wed, 2008-06-04 at 14:23 -0400, Tom Lane wrote: That is covered by pg_control, at least to the extent of forcing the same value of LC_COLLATE. But the same LC_COLLATE means different things on different systems. Even en_US means something different on Mac versus Linux. Regards, Jeff Davis -- 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] meaning of backend exit code 2?
Jeffrey Baker [EMAIL PROTECTED] writes: I have a need to find out the meaning of a backend exiting unexpectedly with exit code 2. Leafing through the source of 8.1 I can't really find it. But are you running 8.1? In 8.2 and up this is the expected result from SIGQUIT. 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] meaning of backend exit code 2?
On Wed, Jun 4, 2008 at 11:31 AM, Tom Lane [EMAIL PROTECTED] wrote: Jeffrey Baker [EMAIL PROTECTED] writes: I have a need to find out the meaning of a backend exiting unexpectedly with exit code 2. Leafing through the source of 8.1 I can't really find it. But are you running 8.1? In 8.2 and up this is the expected result from SIGQUIT. Yes, I'm running 8.1.9. I have a strong suspicion that the exit is coming either from R via pl/R or from perl via pl/perl. From my reading of the code, expected backend exit codes are 1, 0, and -1 mainly? -jwb
Re: [HACKERS] Overhauling GUCS
On Jun 4, 2008, at 11:22, Tom Lane wrote: David E. Wheeler [EMAIL PROTECTED] writes: Exactly. The issue is that application developers, who are not DBAs, have no idea how to tune PostgreSQL, and postgresql.conf is daunting and confusing. So they use a different database that's faster. I take it you haven't looked at mysql's configuration file lately. I'm not much into MySQL, but in the work I've done with it, I've had to create /etc/my.cnf myself. There *is* no configuration file configuring MySQL until that file is created, is there? So there is no configuration to learn at first. I'm not saying that this is necessarily admirable -- it's kind of the opposite end of the spectrum (PostgreSQL: Here is every configuration tweak you could ever possibly want, have fun! vs MySQL: There is no configuration until you need one, then you have to find the docs for it. They aren't actually in any better shape than we are, except that they supply several preconfigured sample files for people to choose from. Which would be a good start, if nothing else… Best, David -- 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] Overhauling GUCS
David E. Wheeler wrote: On Jun 4, 2008, at 11:22, Tom Lane wrote: David E. Wheeler [EMAIL PROTECTED] writes: Exactly. The issue is that application developers, who are not DBAs, have no idea how to tune PostgreSQL, and postgresql.conf is daunting and confusing. So they use a different database that's faster. I take it you haven't looked at mysql's configuration file lately. I'm not much into MySQL, but in the work I've done with it, I've had to create /etc/my.cnf myself. There *is* no configuration file configuring MySQL until that file is created, is there? So there is no configuration to learn at first. I'm not saying that this is necessarily admirable -- it's kind of the opposite end of the spectrum (PostgreSQL: Here is every configuration tweak you could ever possibly want, have fun! vs MySQL: There is no configuration until you need one, then you have to find the docs for it. Tell me how that's better. If that's what you want, simply remove all the comment lines from your config file. Problem solved. They aren't actually in any better shape than we are, except that they supply several preconfigured sample files for people to choose from. Which would be a good start, if nothing else… It's been suggested in the past. It is highly debatable that it would actually be an advance. 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] Overhauling GUCS
On Jun 4, 2008, at 12:48, Andrew Dunstan wrote: I'm not much into MySQL, but in the work I've done with it, I've had to create /etc/my.cnf myself. There *is* no configuration file configuring MySQL until that file is created, is there? So there is no configuration to learn at first. I'm not saying that this is necessarily admirable -- it's kind of the opposite end of the spectrum (PostgreSQL: Here is every configuration tweak you could ever possibly want, have fun! vs MySQL: There is no configuration until you need one, then you have to find the docs for it. Tell me how that's better. If that's what you want, simply remove all the comment lines from your config file. Problem solved. I didn't say it was better. The point is that it seems to be less confusing to non-DBAs. Which would be a good start, if nothing else… It's been suggested in the past. It is highly debatable that it would actually be an advance. Agreed; we've bandied around some better ideas here. Well, I haven't, I've just kibbitzed. But we can surely do better. Best, David -- 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] Overhauling GUCS
David E. Wheeler [EMAIL PROTECTED] writes: I'm not much into MySQL, but in the work I've done with it, I've had to create /etc/my.cnf myself. There *is* no configuration file configuring MySQL until that file is created, is there? So there is no configuration to learn at first. Postgres will start happily with an empty configuration file, too. What's your point? 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: new function array_init
2008/6/4 Tom Lane [EMAIL PROTECTED]: Pavel Stehule [EMAIL PROTECTED] writes: I put fill value on end, because it is like default value: array_set(array[2,2]); array_set(array[2,2], 0); Huh? That's completely nonsensical ... where will you determine the type of the array, if you don't have a sample element? it is nonsens in current postgres. But null array is castable to anyarray no? The fact that the fill value is determining the array type is why it seems important enough to put first. I agree - first is most important parameter. But I am not sure, that is it. size or value. An I am strong fixed on C, Intel notation where destination is first and value is second. 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] Change lock requirements for adding a trigger
On Wed, 2008-06-04 at 10:59 -0400, Tom Lane wrote: Decibel! [EMAIL PROTECTED] writes: Ok, I'll take a stab at such a list. Can anyone think of any reasons why CREATE TRIGGER couldn't get by with ShareLock? pg_class.reltriggers. ISTM that we do this in many ways on pg_class, if we believe the docs. We have * relhasindex (bool) set by CREATE INDEX but not unset by DROP INDEX * relhasrules (bool) * reltriggers (int2) set by CREATE and DROP, since its an integer Seems we should have one consistent way of adding associated objects. If CREATE INDEX can take a Share lock and can update pg_class, why would it not be theoretically possible for CREATE TRIGGER? -- 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] Proposal: new function array_init
Pavel Stehule [EMAIL PROTECTED] writes: 2008/6/4 Tom Lane [EMAIL PROTECTED]: Huh? That's completely nonsensical ... where will you determine the type of the array, if you don't have a sample element? it is nonsens in current postgres. But null array is castable to anyarray no? If you mean an array of nulls, it still has to have an element type. 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] Overhauling GUCS
2008/6/4 David E. Wheeler [EMAIL PROTECTED]: On Jun 3, 2008, at 20:48, Greg Smith wrote: Correct, but completely off-topic regardless. One problem to be solved here is to take PostgreSQL tuning from zero to, say, 50% automatic. Wander the user lists for a few months; the number of completely misconfigured systems out there is considerable, partly because the default values for many parameters are completely unreasonable for modern hardware and there's no easy way to improve on that without someone educating themselves. Getting distracted by the requirements of the high-end systems will give you a problem you have no hope of executing in a reasonable time period. Exactly. The issue is that application developers, who are not DBAs, have no idea how to tune PostgreSQL, and postgresql.conf is daunting and confusing. So they use a different database that's faster. do you thing, so any better config can help? It's not possible. And you can't tune database without well knowledge of applications that use database. Any automatic tools are joy for child. But some default PostgreSQL parameters are not optimal. I think that right now postgresql.conf is designed for full-time DBAs, rather than folks who might want to target PostgreSQL for an application they're developing. We want to attract the latter (without, of course, any expense with the former). Changing how configuration works so that it's easier to understand and, if possible, at least partly automatically tunable would go a long way towards making PostgreSQL friendlier for developers, IMHO. Best, David -- 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] Change lock requirements for adding a trigger
Simon Riggs [EMAIL PROTECTED] writes: We have * relhasindex (bool) set by CREATE INDEX but not unset by DROP INDEX * relhasrules (bool) * reltriggers (int2) set by CREATE and DROP, since its an integer Right. If CREATE INDEX can take a Share lock and can update pg_class, why would it not be theoretically possible for CREATE TRIGGER? It's (probably) theoretically possible, if we replace reltriggers with a bool that acts more like relhasindex, ie it's a hint to go look in pg_triggers. My point was just that you can't arbitrarily decide that some operation needs only a given strength of lock if you are not up to speed on these sorts of details. 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: new function array_init
2008/6/4 Tom Lane [EMAIL PROTECTED]: Pavel Stehule [EMAIL PROTECTED] writes: 2008/6/4 Tom Lane [EMAIL PROTECTED]: Huh? That's completely nonsensical ... where will you determine the type of the array, if you don't have a sample element? it is nonsens in current postgres. But null array is castable to anyarray no? If you mean an array of nulls, it still has to have an element type. I know it - but there was discus about untyped array for empty arrays like ARRAY[] it is little bit similar 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: new function array_init
Tom Lane wrote: Pavel Stehule [EMAIL PROTECTED] writes: 2008/6/4 Tom Lane [EMAIL PROTECTED]: Huh? That's completely nonsensical ... where will you determine the type of the array, if you don't have a sample element? it is nonsens in current postgres. But null array is castable to anyarray no? If you mean an array of nulls, it still has to have an element type. Right, and since the fill element is thus not optional it should be in a fixed position in the argument list, ISTM, which can only mean it would come first. 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] Proposal: new function array_init
Pavel Stehule [EMAIL PROTECTED] writes: 2008/6/4 Tom Lane [EMAIL PROTECTED]: If you mean an array of nulls, it still has to have an element type. I know it - but there was discus about untyped array for empty arrays like ARRAY[] What's that have to do with array_init? It will not (usually) be trying to create an empty array. 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] Overhauling GUCS
On Wed, 4 Jun 2008, Andreas Pflug wrote: When reading this thread, I'm wondering if anybody ever saw a config file for a complex software product that was easily editable and understandable. I would recommend Apache's httpd.conf as an example of something that's easy to edit and follow. Like any complex product, the comments in the configuration file itself can't possibly be sufficient by themselves. But in general I've found Apache's config file to have enough comments to jog my memory when I'm editing it while not being overwhelming. They provide enough detail that when I run into a setting I don't understand there's enough context provided that it's easy to search for more information. Poking around with Google for a bit, here's a reasonable sample: http://webdav.org/goliath/dav_on_x/apache.conf IMHO the best compromise in machine and human readability is an XML format. If the primary PostgreSQL configuration file becomes XML I will quit working with the project. I'm not kidding. If you think XML is easy to generate, edit by hand, and use revision control on, we are at such an fundamental disagreement that I wouldn't even try and directly argue with you. Instead I'll quote Eric Raymond: The most serious problem with XML is that it doesn't play well with traditional Unix tools. Software that wants to read an XML format needs an XML parser; this means bulky, complicated programs. http://www.catb.org/esr/writings/taoup/html/ch05s02.html#id2907018 Let me suggest the following requirement instead which naturally rules it out: it should be possible for a DBA-level coder to write a simple shell script that does something useful with the configuration file in order for having a text-based configuration to be useful in this context. To give a simple example, I can write a single line [sed|awk|perl] command that will let me update the value for one parameter in the current postgresql.conf file. When you can give me a one-liner that does that on an XML file in any shell language in that class, then we might have something to talk about. -- * 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] Overhauling GUCS
On Jun 4, 2008, at 13:31, Pavel Stehule wrote: do you thing, so any better config can help? It's not possible. And you can't tune database without well knowledge of applications that use database. Any automatic tools are joy for child. But some default PostgreSQL parameters are not optimal. Agreed. But, speaking as an app developer, I'm child-like in my DBA abilities. I could use a toy to help me with it. :-) Best, David -- 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] Overhauling GUCS
On Jun 4, 2008, at 13:12, Tom Lane wrote: David E. Wheeler [EMAIL PROTECTED] writes: I'm not much into MySQL, but in the work I've done with it, I've had to create /etc/my.cnf myself. There *is* no configuration file configuring MySQL until that file is created, is there? So there is no configuration to learn at first. Postgres will start happily with an empty configuration file, too. What's your point? That it's less daunting for inexperienced users to start with that. I'm not talking about how things work, I'm talking about what configurations are present to start with. That's all. Best, David -- 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] Overhauling GUCS
Greg Smith [EMAIL PROTECTED] writes: On Wed, 4 Jun 2008, Andreas Pflug wrote: IMHO the best compromise in machine and human readability is an XML format. If the primary PostgreSQL configuration file becomes XML I will quit working with the project. I'm not kidding. I have no particular use for XML in this scenario either, but really this thread seems to be arguing about mostly-irrelevant details. There is not anything fundamentally broken about keeping configuration in a text file, as is proven by the fact that all those other packages do it. The real problem we need to solve is how to allow newbies to have the system auto-configured to something that more or less solves their problems. Putting the config settings in XML does not accomplish that, and neither does putting them inside the database. It might knock a day or two off the time needed to develop a tool that actually does solve the newbie's problem ... but it's unlikely that the effort of changing Postgres to use some other configuration representation would get repaid through easier tool development. So I think we should stop worrying about the file format and think about these two problems: * Can we present the config options in a more helpful way (this is 99% a documentation problem, not a code problem)? * Can we build a configuration wizard to tell newbies what settings they need to tweak? 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] Change lock requirements for adding a trigger
On Wed, 2008-06-04 at 16:33 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: We have * relhasindex (bool) set by CREATE INDEX but not unset by DROP INDEX * relhasrules (bool) * reltriggers (int2) set by CREATE and DROP, since its an integer Right. If CREATE INDEX can take a Share lock and can update pg_class, why would it not be theoretically possible for CREATE TRIGGER? It's (probably) theoretically possible, if we replace reltriggers with a bool that acts more like relhasindex, ie it's a hint to go look in pg_triggers. My point was just that you can't arbitrarily decide that some operation needs only a given strength of lock if you are not up to speed on these sorts of details. Understood. Wouldn't have looked there without your hint. -- 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] Overhauling GUCS
On Jun 4, 2008, at 1:57 PM, Tom Lane wrote: * Can we build a configuration wizard to tell newbies what settings they need to tweak? Probably. Given the demographics of a lot of the newbies is Windows this likely needs to be a pointy-clicky sort of thing if it's going to be widely useful. Doing it in a smart way is likely hard, but even a very simple, crude one would likely be helpful. Doing it as a standalone GUI editor / Wizard wouldn't be too hard, but it sounds more like something that should be integrated in pgadmin, perhaps? Cheers, Steve -- 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: new function array_init
2008/6/4 Tom Lane [EMAIL PROTECTED]: Pavel Stehule [EMAIL PROTECTED] writes: 2008/6/4 Tom Lane [EMAIL PROTECTED]: If you mean an array of nulls, it still has to have an element type. I know it - but there was discus about untyped array for empty arrays like ARRAY[] What's that have to do with array_init? It will not (usually) be trying to create an empty array. sparse arrays?? Or it emulation in pg idealized code: a = array_set(array[10,10]); // untyped null array a[10,10] = 'text'; -- now array is typed without a = array_set(NULL::text, array[10,10]); a[10,10] = 'text'; this is only some ideas, I don't plan do it Pavel 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: new function array_init
Pavel Stehule [EMAIL PROTECTED] writes: idealized code: a = array_set(array[10,10]); // untyped null array a[10,10] = 'text'; -- now array is typed And how did you declare 'a'? This seems like a solution in search of a problem. 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: new function array_init
2008/6/4 Tom Lane [EMAIL PROTECTED]:
Pavel Stehule [EMAIL PROTECTED] writes:
idealized code:
a = array_set(array[10,10]); // untyped null array
a[10,10] = 'text'; -- now array is typed
And how did you declare 'a'? This seems like a solution in search of a
problem.
you have true - problem is in assignment
next question:
when I declare function only for anyelement, I can't simply create text array
CREATE OR REPLACE FUNCTION array_fill(dv anyelement, dims int[])
RETURNS anyarray
AS 'MODULE_PATHNAME', 'array_fill'
LANGUAGE C IMMUTABLE;
postgres=# select array_fill(1,array[4,4]);
array_fill
---
{{1,1,1,1},{1,1,1,1},{1,1,1,1},{1,1,1,1}}
(1 row)
Time: 1,369 ms
postgres=# select array_fill('p',array[4,4]);
ERROR: could not determine polymorphic type because input has type unknown
I can use hack:
CREATE OR REPLACE FUNCTION array_fill(dv text, dims int[])
RETURNS text[]
AS 'MODULE_PATHNAME', 'array_fill'
LANGUAGE C IMMUTABLE;
postgres=# select array_fill('p',array[4,4]);
array_fill
---
{{p,p,p,p},{p,p,p,p},{p,p,p,p},{p,p,p,p}}
(1 row)
what do you thing about it?
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] Overhauling GUCS
If I can add my 2 cents as the end user, IMHO having the configuration file in the xml format is unnecessary and only makes it less user-friendly. Thanks, Michael. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Wednesday, June 04, 2008 4:58 PM To: Greg Smith Cc: Andreas Pflug; Decibel!; Peter Eisentraut; pgsql-hackers@postgresql.org; Josh Berkus Subject: Re: [HACKERS] Overhauling GUCS Greg Smith [EMAIL PROTECTED] writes: On Wed, 4 Jun 2008, Andreas Pflug wrote: IMHO the best compromise in machine and human readability is an XML format. If the primary PostgreSQL configuration file becomes XML I will quit working with the project. I'm not kidding. I have no particular use for XML in this scenario either, but really this thread seems to be arguing about mostly-irrelevant details. There is not anything fundamentally broken about keeping configuration in a text file, as is proven by the fact that all those other packages do it. The real problem we need to solve is how to allow newbies to have the system auto-configured to something that more or less solves their problems. Putting the config settings in XML does not accomplish that, and neither does putting them inside the database. It might knock a day or two off the time needed to develop a tool that actually does solve the newbie's problem ... but it's unlikely that the effort of changing Postgres to use some other configuration representation would get repaid through easier tool development. So I think we should stop worrying about the file format and think about these two problems: * Can we present the config options in a more helpful way (this is 99% a documentation problem, not a code problem)? * Can we build a configuration wizard to tell newbies what settings they need to tweak? 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 -- 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] Overhauling GUCS
On Jun 4, 2008, at 13:57, Tom Lane wrote: So I think we should stop worrying about the file format and think about these two problems: * Can we present the config options in a more helpful way (this is 99% a documentation problem, not a code problem)? * Can we build a configuration wizard to tell newbies what settings they need to tweak? Amen, Tom. Nicely put. Thanks, David -- 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: new function array_init
Pavel Stehule [EMAIL PROTECTED] writes:
postgres=# select array_fill('p',array[4,4]);
ERROR: could not determine polymorphic type because input has type unknown
[ shrug... ] I don't really see a problem with that.
I can use hack:
CREATE OR REPLACE FUNCTION array_fill(dv text, dims int[])
RETURNS text[]
This would be a bad idea, I think; what will happen with
array_fill(null, array[1,2])
I think we *want* that to throw error rather than defaulting to text.
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] Overhauling GUCS
Pavel Stehule wrote: 2008/6/4 David E. Wheeler [EMAIL PROTECTED]: Exactly. The issue is that application developers, who are not DBAs, have no idea how to tune PostgreSQL, and postgresql.conf is daunting and confusing. So they use a different database that's faster. do you thing, so any better config can help? It's not possible. And you can't tune database without well knowledge of applications that use database. Any automatic tools are joy for child. But some default PostgreSQL parameters are not optimal. I think it would be an enourmous help for beginners if they had a simple tuning tool which would tell them which values where altered (and possibly why) from Postgres' default settings based on some basic information. Like: - machine hardware (disk layout, OS, installed memory, etc.) - application usage (no. of clients, read/write activity, etc) I don't think that such a tool could tune the database perfectly (or even very good), but at least people new to Postgres would know where to start looking for tuning it to their needs. And I am not speaking about end users running an application that uses Postgres. I talk about application developers like me that port their application to use Postgres. Rainer -- 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] Overhauling GUCS
Tom Lane wrote: * Can we present the config options in a more helpful way (this is 99% a documentation problem, not a code problem)? * Can we build a configuration wizard to tell newbies what settings they need to tweak? It's certainly one thing to create an initial postgresql.conf from scratch after some inquiry, but a different level of problems to deal with when offering to change the settings. IMHO initial creation isn't enough, users will feel even more left alone if there are no tools helping them further. I guess most users will start tweaking after the server is already running for a while, with some config already in place. That's when file format and/or APIs come into play. Preserving comments and/or using them in a wizard isn't too easy with the current format. Regards, Andreas -- 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] Overhauling GUCS
Tom Lane wrote: * Can we build a configuration wizard to tell newbies what settings they need to tweak? That would trump all the other suggestions conclusively. Anyone good at expert systems? 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] Core team statement on replication in PostgreSQL
Well, WAL format doesn't only depend on WAL itself, but also depend on each resource manager. If we introduce WAL format version identification, ISTM that we have to take care of the matching of resource manager in the master and the slave as well. 2008/6/4 Heikki Linnakangas [EMAIL PROTECTED]: Stephen Denne wrote: Hannu Krosing wrote: The simplest form of synchronous wal shipping would not even need postgresql running on slave, just a small daemon which reports when wal blocks are a) received and b) synced to disk. While that does sound simple, I'd presume that most people would want the guarantee of the same version of postgresql installed wherever the logs are ending up, with the log receiver speaking the same protocol version as the log sender. I imagine that would be most easily achieved through using something like the continuously restoring startup mode of current postgresql. Hmm, WAL version compatibility is an interesting question. Most minor releases hasn't changed the WAL format, and it would be nice to allow running different minor versions in the master and slave in those cases. But it's certainly not unheard of to change the WAL format. Perhaps we should introduce a WAL version number, similar to catalog version? -- 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 -- -- Koichi Suzuki -- 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] Overhauling GUCS
Andreas Pflug [EMAIL PROTECTED] writes: Tom Lane wrote: * Can we build a configuration wizard to tell newbies what settings they need to tweak? It's certainly one thing to create an initial postgresql.conf from scratch after some inquiry, but a different level of problems to deal with when offering to change the settings. IMHO initial creation isn't enough, users will feel even more left alone if there are no tools helping them further. I guess most users will start tweaking after the server is already running for a while, with some config already in place. Indeed, the wizard should be designed to assist with tweaking an existing installation. I see no value at all to trying to run it before/during initdb. That's when file format and/or APIs come into play. Preserving comments and/or using them in a wizard isn't too easy with the current format. [ shrug... ] We have already wasted more effort arguing about this issue than it would take to do something good enough for a wizard tool. The great thing about simple text formats is that they're pretty robust. I think all we need to do is comment out any existing setting(s) of the target variable and add the new setting before the first such, perhaps with a comment indicating what added it and when. If the user finds this too ugly, he can tweak it with a text editor. This is even assuming that the tool needs to edit the file itself, rather than just give advice. The advice is the hard part, folks; could we stop obsessing about trivia? 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] Overhauling GUCS
On Wed, 4 Jun 2008, Tom Lane wrote: The real problem we need to solve is how to allow newbies to have the system auto-configured to something that more or less solves their problems. Putting the config settings in XML does not accomplish that, and neither does putting them inside the database. The subtle issue here is that what makes sense for the database configuration changes over time; there's not just one initial generation and you're done. postgresql.conf files can end up moving from one machine to another for example. I think something that doesn't recognize that reality and move toward a tune-up capability as well as initial generation wouldn't be as useful, and that's where putting the settings inside the database helps so much. Also, there's a certain elegance to having a optimization tool that works again either a new installation or an existing one. I personally have zero interest in a one-shot config generator. It just doesn't solve the problems I see in the field. Performance starts out just fine even with the default settings when people first start, and then goes to hell after the system has been running for a while (and possibly moved to another machine). By that point nobody wants to mess with their configuration file unless it's one simple change at a time. -- * 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] rfc: add pg_dump options to dump output
On Tue, 3 Jun 2008, Tom Lane wrote: Well, the stuff included into the dump by pg_dump -v is informative, too. But we stopped doing that by default because of complaints. I remain unconvinced that this proposal won't suffer the same fate. I think it would be reasonable to only include the list of options used in the dump if you use one that changes what appears in the dump. That way, you wouldn't see anything by default. But if you make a modification that will likely break a diff with an existing dump done with the default parameters, the option change that introduced that should show at the very beginning. -- * 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] Overhauling GUCS
Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: * Can we build a configuration wizard to tell newbies what settings they need to tweak? That would trump all the other suggestions conclusively. Anyone good at expert systems? How far could we get with the answers to just three questions: * How many concurrent queries do you expect to have? * How much RAM space are you willing to let Postgres use? * How much overhead disk space are you willing to let Postgres use? concurrent queries drives max_connections, obviously, and RAM space would drive shared_buffers and effective_cache_size, and both of them would be needed to size work_mem. The third one is a bit weird but I don't see any other good way to set the checkpoint parameters. If those aren't enough questions, what else must we ask? Or maybe they aren't the right questions at all --- maybe we should ask is this a dedicated machine or not and try to extrapolate everything else from what we (hopefully) can find out about the hardware. 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] Overhauling GUCS
Greg Smith [EMAIL PROTECTED] writes: On Wed, 4 Jun 2008, Tom Lane wrote: The real problem we need to solve is how to allow newbies to have the system auto-configured to something that more or less solves their problems. Putting the config settings in XML does not accomplish that, and neither does putting them inside the database. The subtle issue here is that what makes sense for the database configuration changes over time; there's not just one initial generation and you're done. postgresql.conf files can end up moving from one machine to another for example. I think something that doesn't recognize that reality and move toward a tune-up capability as well as initial generation wouldn't be as useful, As I just mentioned to someone else, I don't see any point in framing it as an initial generation problem at all. initdb will already give you settings that work, for some value of work. The config wizard really only needs to deal with the case of tuning an existing installation. and that's where putting the settings inside the database helps so much. How does it help, pray tell? If you mean being able to see what the existing settings are, pg_settings already does 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] Core team statement on replication in PostgreSQL
Koichi Suzuki [EMAIL PROTECTED] writes: Well, WAL format doesn't only depend on WAL itself, but also depend on each resource manager. If we introduce WAL format version identification, ISTM that we have to take care of the matching of resource manager in the master and the slave as well. That seems a bit overdesigned. What are the prospects that two builds of the same Postgres version are going to have different sets of resource managers in them? 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] Overhauling GUCS
* Tom Lane [EMAIL PROTECTED] [080604 20:46]: If those aren't enough questions, what else must we ask? Or maybe they aren't the right questions at all --- maybe we should ask is this a dedicated machine or not and try to extrapolate everything else from what we (hopefully) can find out about the hardware. For these three things: 1) max connections/workmem 2) shared buffers/effective cache 3) bgwriter/checkpoint parameters What are the stats or measures that PostgreSQL produces (or that you want it too currently doesn't, but you would like it to produce) that the masters (i.e. people who tune PostgreSQL effectively, like you, Greg, Simon, Robert, Peter, Josh, Jim, etc - sorry if I missed others) actually use to decide whether to increase or decrease a value? I tune my postgresql.conf mainly on folklore, and assimilated understanding from reading the lists and blogs... But I haven't come across (or rather, haven't come across and remembered/bookmarked) anything that helps someone sample/read any stats or counts to find bottleneck points which to start tuning. Stuff like (remembering that I've never had to really work at tuning because PG has always been fast enough for my needs, so take this with a grain of salt) * Are backends always writing out dirty buffers because there are no free ones? This might mean tweaking settings affecting bgwriter. * Are the evicted buffers ones with really high usage counts? This might mean an increase shared buffers would help? * Are we always spilling small amounts of data to disk for sorting? A a small work_mem increase might help... * Are all our reads from disk really quick? This probably means OS pagecache has our whole DB, and means random_page_cost could be tweaked? If we could get a definitive list of things like this, or maybe just comprehensive, or even at least agreed-to-not-be-wrong things to look at, that would go a long way to documentation *how* to tune PG effectively, and could lead to any projects that want to tackle examining a running cluster and suggesting some config changes... a. -- Aidan Van Dyk Create like a god, [EMAIL PROTECTED] command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] Overhauling GUCS
On Jun 4, 2008, at 5:23 PM, Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: * Can we build a configuration wizard to tell newbies what settings they need to tweak? That would trump all the other suggestions conclusively. Anyone good at expert systems? How far could we get with the answers to just three questions: * How many concurrent queries do you expect to have? * How much RAM space are you willing to let Postgres use? * How much overhead disk space are you willing to let Postgres use? concurrent queries drives max_connections, obviously, and RAM space would drive shared_buffers and effective_cache_size, and both of them would be needed to size work_mem. The third one is a bit weird but I don't see any other good way to set the checkpoint parameters. If those aren't enough questions, what else must we ask? Or maybe they aren't the right questions at all --- maybe we should ask is this a dedicated machine or not and try to extrapolate everything else from what we (hopefully) can find out about the hardware. I'd be interested in putting together a framework+GUI client to do this cleanly in a cross-platform (Windows, Linux, Solaris, OS X as a bare minimum) sort of way, if no-one else already has such a thing. A framework doesn't get you all the way there, but it makes it a whole lot easier to work on what base data and information you need, and how easy it is to map pgsql-performance and #postgresql gut feel onto something more algorithmic. Cheers, Steve -- 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] Overhauling GUCS
On Wed, 4 Jun 2008, Andrew Dunstan wrote: Tom Lane wrote: * Can we build a configuration wizard to tell newbies what settings they need to tweak? That would trump all the other suggestions conclusively. Anyone good at expert systems? Sigh. I guess we need to start over again. Last year around this time, there was one of the recurring retreads of this topic named PostgreSQL Configuration Tool for Dummies: http://archives.postgresql.org/pgsql-performance/2007-06/msg00386.php Josh Berkus pointed out that he already had the expert system part of this problem solved pretty well with a spreadsheet: http://pgfoundry.org/docman/view.php/1000106/84/calcfactors.sxc (that's in the OpenOffice Calc format if you don't know the extension) That particular spreadsheet has more useful tuning suggestions in this area than 99.9% of PostgreSQL users have or will ever know. You can nitpick the exact recommendations, but the actual logic and thinking involved is pretty well solved. It could use a touch of tweaking and modernization but it's not too far off from being as good as you're likely to get at making guesses without asking the user too many questions. There is one ugly technical issue, that you can't increase shared_buffers usefully in many situations because of SHMMAX restrictions, and that issue will haunt any attempt to be completely automatic. Where Josh got hung up, where I got hung up, where Lance Campbell stopped at with his Dummies tool, and what some unknown number of other people have been twarted by, is that taking that knowledge and turning it into a tool useful to users is surprisingly difficult. The reason for that is the current postgresql.conf file and how it maps internally to GUC information isn't particularly well suited to automated generation, analysis, or updates. I think Josh got lost somewhere in the parsing the file stage. The parts I personally got stuck on were distinguishing user-added comments from ones the system put in, plus being completely dissatisfied with how lossy the internal GUC process was (I would like a lot more information out of pg_settings than are currently there). Lance's helper tool was hobbled by the limitations of being a simple web application. That's the background to Josh's proposal. It has about an 80% overlap with what I was working on suggesting, which is why I jumped on his bandwagon so fast. The outline at http://wiki.postgresql.org/wiki/GUCS_Overhaul includes the superset of our respective thinking on the first step here toward straightening out this mess, further expanded with observations made in this thread. I would respectively point out that comments about the actual tuning itself have no bearing whatsoever on this proposal. This is trying to nail down all the features needed to support both doing an initial generation and subsequent incremental improvements to the postgresql.conf file, while also reducing some redundancy in the code itself. Reducing the scope to only handling initial generation would make this a smaller task. But it happens to fall out that the work required to cut down on the redundancy and that required to better support incremental updates as well happen to be almost the same. Josh's stated agenda is to get this right in one swoop, with only one version worth of disruption to the format, and that goal is served better IMHO as well by addressing all these changes as one batch. I will attempt to resist further outbursts about non-productive comments here, and each time I am tempted instead work on prototyping the necessary code I think this really needs instead. -- * 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] Overhauling GUCS
On Jun 4, 2008, at 6:28 PM, Greg Smith wrote: Josh Berkus pointed out that he already had the expert system part of this problem solved pretty well with a spreadsheet: http://pgfoundry.org/docman/view.php/1000106/84/calcfactors.sxc (that's in the OpenOffice Calc format if you don't know the extension) On Jun 4, 2008, at 6:20 PM, Steve Atkins wrote: I'd be interested in putting together a framework+GUI client to do this cleanly in a cross-platform (Windows, Linux, Solaris, OS X as a bare minimum) sort of way, if no-one else already has such a thing. /me makes go together motions, if nobody has any objection Cheers, Steve -- 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] Overhauling GUCS
On Wed, 4 Jun 2008, Aidan Van Dyk wrote: * Are backends always writing out dirty buffers because there are no free ones? This might mean tweaking settings affecting bgwriter. What you mean on the first one is are backends always writing out dirty buffers becuase there are no *clean* ones; the server operates with no *free* buffers as standard operations. Figuring that out is now easy in 8.3 with the pg_stat_bgwriter view. * Are the evicted buffers ones with really high usage counts? This might mean an increase shared buffers would help? Evicted buffers must have a 0 usage count. The correct question to ask is are buffers never getting high usage counts because they keep getting evicted too fast?. You can look at that in 8.3 using pg_buffercache, I've got suggested queries as part of my buffer cache presentation at http://www.westnet.com/~gsmith/content/postgresql/ * Are we always spilling small amounts of data to disk for sorting? A a small work_mem increase might help... I was just talking to someone today about building a monitoring tool for this. Not having a clear way to recommend people monitor use of work_mem and its brother spilled to disk sorts is an issue right now, I'll whack that one myself if someone doesn't beat me to it before I get time. * Are all our reads from disk really quick? This probably means OS pagecache has our whole DB, and means random_page_cost could be tweaked? This is hard to do with low overhead in an OS-independant way. The best solution available now would use dtrace to try and nail it down. There's movement in this area (systemtap for Linux, recent discussion at the PGCon Developer Meeting of possibly needing more platform-specific code) but it's not quite there yet. So everything you mentioned is either recently added/documented or being actively worked on somewhere, and the first two were things I worked on myself after noticing they were missing. Believe me, I feel the items that still aren't there, but they're moving along at their own pace. There's already more tuning knowledge available than tools to help apply that knowledge to other people's systems, which is why I think a diversion to focus just on that part is so necessary. -- * 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] [PERFORM] Outer joins and equivalence
[ redirecting thread from -performance to -hackers ] Simon Riggs [EMAIL PROTECTED] writes: I've got a test case which shows something related and weird, though not the exact case. The queries shown here have significantly different costs, depending upon whether we use tables a or b in the query. Since a and b are equivalent this result isn't expected at all. Hmm. I had been guessing that there was something about your original query that prevented the system from applying best_appendrel_indexscan, but after fooling with this a bit, I don't believe that's the issue at all. The problem is that these two cases should be equivalent: select ... from a join b on (a.id = b.id) left join c on (a.id = c.id); select ... from a join b on (a.id = b.id) left join c on (b.id = c.id); but they are not seen that way by the current planner. It correctly forms an EquivalenceClass consisting of a.id and b.id, but it cannot put c.id into that same class, and so the clause a.id = c.id is just left alone; there is noplace that can generate b.id = c.id as an alternative join condition. This means that (for the first query) we can consider the join orders (a join b) leftjoin c and (a leftjoin c) join b, but there is no way to consider the join order (b leftjoin c) join a; to implement that we'd need to have the alternative join clause available. So if that join order is significantly better than the other two, we lose. This is going to take a bit of work to fix :-(. I am toying with the idea that we could go ahead and put c.id into the EquivalenceClass as a sort of second-class citizen that's labeled as associated with this particular outer join --- the implication being that we can execute the outer join using a generated clause that equates c.id to any one of the first-class members of the EquivalenceClass, but above the outer join we can't assume that c.id hasn't gone to null, so it's not really equal to anything else in the class. I think it might also be possible to get rid of the reconsider_outer_join_clauses() kluge in favor of driving transitive-equality-to-a-constant off of this representation. However there's a larger issue here, which is the very identity of an outer join :-(. Currently, for the first query above, the left join is defined as being between a and c, with a being the minimum left-hand-side needed to form the join. To be able to handle a case like this, it seems that the notion of a minimum left hand side falls apart altogether. We can execute the OJ using either a or b as left hand side. So the current representation of OuterJoinInfo, and the code that uses it to enforce valid join orders, needs a serious rethink. Looks like 8.4 development material to me, rather than something we can hope to back-patch a fix for... 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] Overhauling GUCS
* Greg Smith [EMAIL PROTECTED] [080604 22:14]: So everything you mentioned is either recently added/documented or being actively worked on somewhere, and the first two were things I worked on myself after noticing they were missing. Believe me, I feel the items that still aren't there, but they're moving along at their own pace. There's already more tuning knowledge available than tools to help apply that knowledge to other people's systems, which is why I think a diversion to focus just on that part is so necessary. But as an administrator/developer, I don't understand the focus on a new API for writing my config for me... I'ld love a tool that helped me analyze my current running PG database (and yes, that includes getting *current* settings), and suggest config changes, ideally in order that the tool thinks will make a difference... I can make that change, and distribute it. That's the easy part. If I really trust the tool then I'll just blindly run it (depending on it's output format): $TOOL $PGDATA/postgresql.conf in which case, I don't care if it groked any of my previous comments and cruft. Otherwise, I'll look at it, and integrate some (or all) of the changes into postgresql.conf using my preferred method of commenting/SCM/quirks. a. -- Aidan Van Dyk Create like a god, [EMAIL PROTECTED] command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] rfc: add pg_dump options to dump output
Robert Treat wrote: If you are calling pg_dump with different flags, it seems likely your breaking diff equality anyway so I'm not sure how valid that is. What about different users? Different connection options will result in the same file but it breaks diff-dump tools. I don't see the point of adding such an option. -- 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] Overhauling GUCS
On Wed, 4 Jun 2008, Aidan Van Dyk wrote: I'd love a tool that helped me analyze my current running PG database (and yes, that includes getting *current* settings), and suggest config changes Fine. To demonstrate why the overhaul is needed, let's start designing a simple tool whose sole purpose in life is to suggest new settings for shared_buffers and work_mem. Say we want to show people their current setting and what we'd recommend instead. I just created a new cluster on my laptop. It has the following in the postgresql.conf: shared_buffers = 32MB # min 128kB or max_connections*16kB # (change requires restart) #work_mem = 1MB # min 64kB Say I first start designing such a tool by trying to read the postgresql.conf file to figure out what these values are set to. In order to accomplish that, I need to parse the whole file correctly, doing things like turning 32MB into the actual numeric value so my program can make decisions based on its value[1]. This basically requires someone writing a tuning tool replicate the GUC parsing code, which is crazy; at this point you've already lost most potential tool authors. But you're smarter than that; instead you use pg_settings: psql=# select name,setting,unit from pg_settings where name='shared_buffers' or name='work_mem'; name | setting | unit +-+-- shared_buffers | 4096| 8kB work_mem | 1024| kB Now: what do you tell the user their current value is? The way the postgresql.conf is parsed into memory is lossy; at this point you don't know anymore what units where specified in the original file. If someone sees their current setting shown as 4096 but they know they set it to 32MB, they'll end up confused unless they understand the whole page size concept--and if they understood that, they'd automatically be disqualified from being the type of user our theoretical tool is targeted toward. If you try and make things more user-friendly by always showing the most human readable version, what about the person who ended up setting this parameter because they copied an old config file that recommended setting it to 4096. You show it to them as 32MB; they'll also be confused and blame the tool for being bad. And even if you work through all that, to give good advice here you have to know things like that shared_buffers is a server parameter requiring restart, while work_mem is a per-session parameter. Right now, the only way to know all that is for tool authors to assemble their own database and keep it up to date with each release. And you just lost another set of potential authors with that realization. Next up, we manage to work through all those issues, and someone happily follows our advice and gets their file updated with a much larger value for work_mem. Yeah, we are heroes! Or not. You see, in the config file we just helpful updated for them was this comment just above that setting: # OMG don't set this too high or the Linux OOM killer will # take down the server! (This is not a theoretical example; I have found variations on that text in two postgresql.conf files and I vaguely recall Josh mentioned running into it as well). And now you just *crashed their server* by giving bad advice that was clearly against the invaluable comment history in the file already. Oh, but where are those comments located at? Before the setting? After the setting? In a group at the top? Who can say? Since there's no strong standard, people put them all over the place. I don't know about you, but I'm too busy to spend a bunch of time writing a tool to work around all these issues knowing it is always going to be fragile, incomplete, and need updating with every GUC change no matter what. Instead, reconsider http://wiki.postgresql.org/wiki/GUCS_Overhaul , having gotten a taste of the motivation behind those changes, and consider how this would play out after those improvements. The approach where you connect to the database and query is the easy path. There is never a reason to consider parsing postgresql.conf. Anybody who knows how to write a simple script that connects to a database and reads a table (basically anyone who's written the database client equivilent of hello, world) can feel like a potential tool author. All the information about the postgresql.conf side of every setting is retained in case you want to show where they came from, or to generate a new file that's as similar as possible to the original. Any recommendations you suggest can be trivially annotated with whether you need to consider max_connections because it's per-session, and whether people need to restart the server or can just send it a signal, and that will continue to be the case in the future with minimal work on the tool author's part. The defaults are now available,
Re: [HACKERS] Proposal: new function array_init
2008/6/5 Tom Lane [EMAIL PROTECTED]:
Pavel Stehule [EMAIL PROTECTED] writes:
postgres=# select array_fill('p',array[4,4]);
ERROR: could not determine polymorphic type because input has type unknown
[ shrug... ] I don't really see a problem with that.
you have t cast in most common case
I can use hack:
CREATE OR REPLACE FUNCTION array_fill(dv text, dims int[])
RETURNS text[]
This would be a bad idea, I think; what will happen with
array_fill(null, array[1,2])
I think we *want* that to throw error rather than defaulting to text.
regards, tom lane
ok
Pavel
--
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] Core team statement on replication in PostgreSQL
If the version of the master and the slave is different and we'd still like to allow log shipping replication, we need a negotiation if WAL format for the two is compatible. I hope it is not in our scope and I'm worrying too much. 2008/6/5 Tom Lane [EMAIL PROTECTED]: Koichi Suzuki [EMAIL PROTECTED] writes: Well, WAL format doesn't only depend on WAL itself, but also depend on each resource manager. If we introduce WAL format version identification, ISTM that we have to take care of the matching of resource manager in the master and the slave as well. That seems a bit overdesigned. What are the prospects that two builds of the same Postgres version are going to have different sets of resource managers in them? regards, tom lane -- -- Koichi Suzuki -- 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] [PERFORM] Outer joins and equivalence
On Wed, 2008-06-04 at 22:18 -0400, Tom Lane wrote: [ redirecting thread from -performance to -hackers ] Simon Riggs [EMAIL PROTECTED] writes: I've got a test case which shows something related and weird, though not the exact case. The queries shown here have significantly different costs, depending upon whether we use tables a or b in the query. Since a and b are equivalent this result isn't expected at all. Hmm. I had been guessing that there was something about your original query that prevented the system from applying best_appendrel_indexscan, but after fooling with this a bit, I don't believe that's the issue at all. The problem is that these two cases should be equivalent: select ... from a join b on (a.id = b.id) left join c on (a.id = c.id); select ... from a join b on (a.id = b.id) left join c on (b.id = c.id); but they are not seen that way by the current planner. It correctly forms an EquivalenceClass consisting of a.id and b.id, but it cannot put c.id into that same class, and so the clause a.id = c.id is just left alone; there is noplace that can generate b.id = c.id as an alternative join condition. This means that (for the first query) we can consider the join orders (a join b) leftjoin c and (a leftjoin c) join b, but there is no way to consider the join order (b leftjoin c) join a; to implement that we'd need to have the alternative join clause available. So if that join order is significantly better than the other two, we lose. This is going to take a bit of work to fix :-(. I am toying with the idea that we could go ahead and put c.id into the EquivalenceClass as a sort of second-class citizen that's labeled as associated with this particular outer join --- the implication being that we can execute the outer join using a generated clause that equates c.id to any one of the first-class members of the EquivalenceClass, but above the outer join we can't assume that c.id hasn't gone to null, so it's not really equal to anything else in the class. I think it might also be possible to get rid of the reconsider_outer_join_clauses() kluge in favor of driving transitive-equality-to-a-constant off of this representation. Yes, EquivalenceClass allows an implication to be made either way around, which is wrong for this class of problem. I was imagining a higher level ImplicationClass that was only of the form A = B but not B = A. So we end up with an ImplicationTree, rather than a just a flat Class. Which is where I punted... However there's a larger issue here, which is the very identity of an outer join :-(. Currently, for the first query above, the left join is defined as being between a and c, with a being the minimum left-hand-side needed to form the join. To be able to handle a case like this, it seems that the notion of a minimum left hand side falls apart altogether. We can execute the OJ using either a or b as left hand side. So the current representation of OuterJoinInfo, and the code that uses it to enforce valid join orders, needs a serious rethink. Hadn't seen that at all. Looks like 8.4 development material to me, rather than something we can hope to back-patch a fix for... Definitely. -- 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
[HACKERS] orafce does NOT build with Sun Studio compiler
Hello hackers,
During the Oracle migration tutorial by peter at PGCon, I took an action
item for myself to try orafce on Solaris/OpenSolaris.
As pg binaries are bundled with Solaris now (using Sun Studio compiler),
I decided to try out building orafce against the same bundled binaries
(with USE_PGXS=1).
I see following build error,
/opt/SUNWspro/SS11/bin/cc -xc99=none -xCC -KPIC -I.
-I/usr/include/pgsql/server -I/usr/include/pgsql/internal
-I/usr/sfw/include -I/usr/include/kerberosv5 -c -o pipe.o pipe.c
pipe.c, line 149: null dimension: data
cc: acomp failed for pipe.c
gmake[1]: *** [pipe.o] Error 2
gmake[1]: Leaving directory `/builds2/postgres/orafce/orafce'
*** Error code 2
make: Fatal error: Command failed for target `orafce/config.status'
Current working directory /builds2/postgres/orafce
Sun Studio does not like array declarations with null as dimenstion.
So, In pipe.c we have,
typedef struct
{
LWLockId shmem_lock;
pipe *pipes;
alert_event *events;
alert_lock *locks;
size_t size;
unsigned int sid;
char data[]; /* line 149 */
} sh_memory;
A quick look tells me that this should not be hard to fix, but have not
prepared any patch as I dont understand the code very well.
Is it possible to fix this soon ? This will increase the portability and
would help people use orafce with existing pg binaries on Solaris.
Thanks
Mayuresh
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
