Re: [HACKERS] Postgres-R
Unfortunately, I'am getting the error as below when I start the gossip. I had followed the same steps as you mentioned. REFLECT:I'm not in the list of gossip hosts, exiting (the hosts are [cluster_1|cluster_2]) cluster_1 cluster_2 are node names are the in /etc/hosts. Did you face this? regards, Niranjan -Original Message- From: ext leiyonghua [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 19, 2008 6:42 AM To: K, Niranjan (NSN - IN/Bangalore) Cc: Markus Wanner; pgsql-hackers@postgresql.org Subject: Re: Postgres-R hi, Assume that we have two node node 0 , 192.168.0.2 node 1 , 192.168.0.3 1. add a host entry in /etc/hosts for hostname resolving. 2. add the host list in configuration 'ensemble.conf' for gossip service: ENS_GOSSIP_HOSTS=node0:node1 3. set the envrionment variable ENS_CONFIG_FILE export ENS_CONFIG_FILE=/xxx/xxx/ensemble.conf 4. start ensemble gossip 5. try 'c_mtalk' and happy. this is a simplest case for me, hehe! leiyonghua K, Niranjan (NSN - IN/Bangalore) 写道: Thanks for the information. For Step5 (starting ensemble daemon).- I set the multicast address to both nodes (Node 1 Node 2 eth0: 224.0.0.9/4) before starting the ensemble. And started the server application mtalk in node 1 node 2 and then client application in node 1 node 2. But the count of members ('nmembers') show as 1. This is the output of the client program 'c_mtalk'. Seeing this, I'am assuming that the applications are not merged. Could you please let me know how did you proceed with the setup of ensemble? regards, Niranjan -Original Message- From: ext leiyonghua [mailto:[EMAIL PROTECTED] Sent: Monday, August 18, 2008 2:58 PM To: K, Niranjan (NSN - IN/Bangalore); Markus Wanner; pgsql-hackers@postgresql.org Subject: Re: Postgres-R [EMAIL PROTECTED] 写道: I wish to set up the Postgres-R environment, could you please let me know the steps for setting it up. Thanks. yeah, actually, i have not been successful to set up this, but let me give some information for you. 1. download the postgresql snapshot source code from here: http://www.postgresql.org/ftp/snapshot/dev/ (this is a daily tarball) 2. Get the corresponding patch for postgres-r from: http://www.postgres-r.org/downloads/ 3. apply the patch for snapshot source, and configure like this: ./configure --enable-replication make make install 4. install the GCS ensemble, according the document : http://www.cs.technion.ac.il/dsl/projects/Ensemble/doc.html 5. start ensemble daemon and gossip if neccessary ( yes, make sure the two nodes can 'GCS' each other) 3. Assume that you have two nodes, start up postgresql and create a database 'db', and create a table 'tb' for testing which should be have a primary key for all nodes. 4. At the origin node, execute the command at psql console: alter database db start replication in group gcs; (which means the database 'db' is the origin and the group 'gcs' is the GCS group name) 5. At the subscriber node, execute the command: alter database db accept replication from group gcs; Hope information above would be helpful, and keep in touch. leiyonghua -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extending varlena
On Mon, 18 Aug 2008, Tom Lane wrote: What would make more sense is to redesign the large-object stuff to be somewhat modern and featureful, and provide stream-access APIs (think lo_read, lo_seek, etc) that allow offsets wider than 32 bits. A few years ago, I was working on such a project for a company I used to work for. The company changed directions shortly thereafter, and the project was dropped, but perhaps the patch might still be useful as a starting point for someone else. The original patch is http://archives.postgresql.org/pgsql-hackers/2005-09/msg01026.php, and the advice I was working on implementing was in http://archives.postgresql.org/pgsql-hackers/2005-09/msg01063.php I am attaching the latest version of the patch I found around. As it was almost 3 years ago, I am a little fuzzy on where I left off, but I do remember that I was trying to work through the suggestions Tom Lane gave in that second linked email. I would recommend discarding the libpq changes, since that seemed to not pass muster. Note that this patch was against 8.0.3. There only seem to be a few issues applying it to the current head, but I haven't really dug into them to see how difficult it would be to update. Luckily, the large object code is fairly slow-moving, so there aren't too many conflicts. One thing I did notice is that it looks like someone extracted one of the functions I wrote in this patch and applied it as a 32-bit version. Good for them. I'm glad someone got some use out of this project, and perhaps more use will come of it. -- At the source of every error which is blamed on the computer you will find at least two human errors, including the error of blaming it on the computer.diff -Nur postgresql-8.0.3-orig/src/backend/libpq/be-fsstubs.c postgresql-8.0.3/src/backend/libpq/be-fsstubs.c --- postgresql-8.0.3-orig/src/backend/libpq/be-fsstubs.c2004-12-31 13:59:50.0 -0800 +++ postgresql-8.0.3/src/backend/libpq/be-fsstubs.c 2005-10-03 11:43:36.0 -0700 @@ -233,6 +233,34 @@ PG_RETURN_INT32(status); } + +Datum +lo_lseek64(PG_FUNCTION_ARGS) +{ + int32 fd = PG_GETARG_INT32(0); + int64 offset = PG_GETARG_INT64(1); + int32 whence = PG_GETARG_INT32(2); + MemoryContext currentContext; + int64 status; + + if (fd 0 || fd = cookies_size || cookies[fd] == NULL) + { + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), +errmsg(invalid large-object descriptor: %d, fd))); + PG_RETURN_INT64(-1); + } + + Assert(fscxt != NULL); + currentContext = MemoryContextSwitchTo(fscxt); + + status = inv_seek(cookies[fd], offset, whence); + + MemoryContextSwitchTo(currentContext); + + PG_RETURN_INT64(status); +} + Datum lo_creat(PG_FUNCTION_ARGS) { @@ -283,6 +311,165 @@ PG_RETURN_INT32(inv_tell(cookies[fd])); } + +Datum +lo_tell64(PG_FUNCTION_ARGS) +{ + int32 fd = PG_GETARG_INT32(0); + + if (fd 0 || fd = cookies_size || cookies[fd] == NULL) + { + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), +errmsg(invalid large-object descriptor: %d, fd))); + PG_RETURN_INT64(-1); + } + + /* +* We assume we do not need to switch contexts for inv_tell. That is +* true for now, but is probably more than this module ought to +* assume... +*/ + PG_RETURN_INT64(inv_tell(cookies[fd])); +} + +Datum +lo_length(PG_FUNCTION_ARGS) +{ + int32 fd = PG_GETARG_INT32(0); + int32 sz = 0; + MemoryContext currentContext; + + if (fd 0 || fd = cookies_size || cookies[fd] == NULL) + { + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), +errmsg(invalid large-object descriptor: %d, fd))); + PG_RETURN_INT32(-1); + } + Assert(fscxt != NULL); + currentContext = MemoryContextSwitchTo(fscxt); + + sz = inv_length(cookies[fd]); + + MemoryContextSwitchTo(currentContext); + + PG_RETURN_INT32(sz); +} + +Datum +lo_length64(PG_FUNCTION_ARGS) +{ + int32 fd = PG_GETARG_INT32(0); + int64 sz = 0; + MemoryContext currentContext; + + if (fd 0 || fd = cookies_size || cookies[fd] == NULL) + { + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), +errmsg(invalid large-object descriptor: %d, fd))); + PG_RETURN_INT64(-1); + } + Assert(fscxt != NULL); + currentContext = MemoryContextSwitchTo(fscxt); + + sz = inv_length(cookies[fd]); + + MemoryContextSwitchTo(currentContext); + +
[HACKERS] possible minor EXPLAIN bug?
Hello I thing so Agg node doesn't set width well: postgres=# explain select a,b from twocol; QUERY PLAN -- Seq Scan on twocol (cost=0.00..31.40 rows=2140 width=8) (1 row) postgres=# explain select sum(a) from twocol group by b; QUERY PLAN HashAggregate (cost=42.10..44.60 rows=200 width=8) -- wrong should be 4 - Seq Scan on twocol (cost=0.00..31.40 rows=2140 width=8) (2 rows) Agg get width directly from outer plan, what could be wrong. Regards Pavel Stehule -- 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] WITH RECURSIVE patches 0818
I think I may have found another bug: WITH RECURSIVE t(i,j) AS ( VALUES (1,2) UNION ALL SELECT t2.i, t.j FROM ( SELECT 2 AS i UNION ALL /* Wrongly getting detected, I think */ SELECT 3 AS i ) AS t2 JOIN t ON (t2.i = t.i) ) SELECT * FROM t; ERROR: attribute number 2 exceeds number of columns 1 Is there some way to ensure that in the case of WITH RECURSIVE, the query to the right of UNION ALL follows only the SQL:2008 rules about not having outer JOINs, etc. in it, but otherwise make it opaque to the error-checking code? I know I didn't explain that well, but the above SQL should work and the error appears to stem from the parser's looking at the innermost UNION ALL instead of the outermost. Thanks for the report. I will look into this. -- Tatsuo Ishii SRA OSS, Inc. Japan -- 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
Alvaro Herrera wrote: Tom Lane escribió: Gregory Stark [EMAIL PROTECTED] writes: The entire target market for such a thing is DBAs stuck on hosted databases which don't have shell access to their machines. Perhaps the overlap between that and the people who can write a server-side module which dumps out a config file according to some rules is just too small? There's a veritable boatload of stuff we do that assumes shell access (how many times have you seen cron jobs recommended, for instance?). So I'm unconvinced that modify the config without shell access is really a goal that is worth lots of effort. Actually, lots of people are discouraged by suggestions of using cron to do anything. The only reason cron is suggested is because we don't have any other answer, and for many people it's a half-solution. An integrated task scheduler in Pg would be more than welcome. Yes, I hear a lot of people complaining aobut that too. Now that we have a working autovacuum, some of it goes away though - no need to cron your VACUUMs in most cases anymore. But there are still backups - but they are often managed by the scheduler of an enterprise backup software. Also, remember that pgAdmin already comes with a pgAgent thing. Yeah, it's a real life-saver on Windows where the builtin task-scheduler isn't as readily accessible or easy to use.. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extending varlena
Josh Berkus wrote: Andrew, I always find these requests puzzling. Is it really useful to store the data for a jpeg, video file or a 10GB tar ball in a database column? Some people find it useful. Because LOs are actually easier to manage in PG than in most other DBMSes, right now that's a significant source of PostgreSQL adoption. I'd like to encourage those users by giving them more useful LO features. Given that they don't replicate with most (any?) of the currently available replication solutions, the fact that they are easy to use becomes irrelevant fairly quickly to larger installations in my experience. But the interface *is* nice, so if we could fix that, it would be very good. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extending varlena
Simon Riggs wrote: On Mon, 2008-08-18 at 16:22 -0400, Tom Lane wrote: David Fetter [EMAIL PROTECTED] writes: What would need to happen for the next jump up from where varlena is now, to 8 bytes? Dealing with upwards-of-4GB blobs as single Datums isn't remotely sane, and won't become so in the near (or even medium) future. So I don't see the point of doing all the work that would be involved in making this go. What would make more sense is to redesign the large-object stuff to be somewhat modern and featureful, and provide stream-access APIs (think lo_read, lo_seek, etc) that allow offsets wider than 32 bits. The main things I think we'd need to consider besides just the access API are - permissions features (more than none anyway) - better management of orphaned objects (obsoleting vacuumlo) - support 16TB of large objects (maybe partition pg_largeobject?) - dump and restore probably need improvement to be practical for such large data volumes Sounds like a good list. Probably also using a separate Sequence to allocate numbers rather than using up all the Oids on LOs would be a good plan. The ability to partition the large object store would not suck either... For backup/recovery purposes mainly. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
Hi The reason that this case wasn't covered in 8.3 is that there didn't seem to be a use-case that justified doing the extra work. I still haven't seen one. You just stopped reading the thread where it was discussed after your troll remark? Other than inline-able SQL functions there is no reason to invalidate a stored plan based on the fact that some function it called changed contents. Isn't it reason enough for this patch? ERROR: cache lookup failed for function is normal and good behaviour and should not be recoverd from because it never happen if you PostgreSQL right :) Usecase 1: Inlined functions postgres=# create or replace function salary_without_income_tax(i_salary in numeric, salary out numeric ) returns numeric as $$ select $1 * 0.76 as salary $$ language sql; postgres=# prepare c2 as select salary, salary_without_income_tax(salary) from salaries; postgres=# execute c2; salary | salary_without_income_tax +--- 1 | 7600.00 postgres=# create or replace function salary_without_income_tax(i_salary in numeric, salary out numeric ) returns numeric as $$ select $1 * 0.74 as salary $$ language sql; postgres=# execute c2; salary | salary_without_income_tax +--- 1 | 7600.00 Use case 2: While rewriting existing modules due to changes in business requirements then in addition to new code we have to refactor lots of old functions one natural thing to do would be to get rid of return types as they are even more inconvenient to use than out parameters. Another reason is keep coding style consistent over modules so future maintenace will be less painful in the assholes. postgres=# create type public.ret_status as ( status integer, status_text text); CREATE TYPE postgres=# create or replace function x ( i_param text ) returns public.ret_status as $$ select 200::int, 'ok'::text; $$ language sql; CREATE FUNCTION postgres=# create or replace function x ( i_param text, status OUT int, status_text OUT text ) returns record as $$ select 200::int, 'ok'::text; $$ language sql; ERROR: cannot change return type of existing function HINT: Use DROP FUNCTION first Usecase 3.: Extra out parameters are needed in existing functions. I assure you if you have 5 years of legacy code that is constantly changing it does happen (often). postgres=# create or replace function xl ( i_param text, status OUT int, status_text OUT text, more_text OUT text ) returns record as $$ select 200::int, 'ok'::text, 'cat'::text; $$ language sql; ERROR: cannot change return type of existing function DETAIL: Row type defined by OUT parameters is different. HINT: Use DROP FUNCTION first. Usecase 4: Things are even worse when you need to change the type that is used in functions. You have to drop and recreate the type and all the functions that are using it. Sometimes type is used in several functions and only some of them need changes. postgres=# create type public.ret_status_ext as ( status integer, status_text text, more_money numeric); CREATE TYPE postgres=# create or replace function x ( i_param text ) returns public.ret_status_ext as $$ select 200::int, 'ok'::text; $$ language sql; ERROR: cannot change return type of existing function HINT: Use DROP FUNCTION first. And whenever we do drop and create as hinted then we receive error flood that won't stop until something is manually done to get rid of it postgres=# drop function x(text); DROP FUNCTION postgres=# create or replace function x ( i_param text ) returns public.ret_status_ext as $$ select 200::int, $1, 2.3 $$ language sql; CREATE FUNCTION postgres=# execute c; ERROR: cache lookup failed for function 24598 I hope i have answered your question Why do you not use CREATE OR REPLACE FUNCTION? That leaves us to deal with functions in our usual bad, wrong and stupid ways. * We create a function with new name and redirect all the calls to it. (stupid as it creates extra development, testing, code reviewing and releasing work and leaves around old code). * We pause pgBouncer and after release let it reconnect all connections (bad as it creates downtime). * We invalidate all procedures using update to pg_proc (simply wrong way to do it but still our best workaround short of restarting postgres). postgres=# update pg_proc set proname = proname; UPDATE 2152 postgres=# execute c2; salary | salary_without_income_tax +--- 1 | 7400.00 Perhaps Skype needs to rethink how they are modifying functions. We have had to change the way we use functions to suit PostgreSQL for 5 years now. That creates us quite a lot of extra work both on development side and DBA side plus the constantly hanging danger of downtime. Our DBA teams job is to reduce all possible causes for downtime and this patch is solution to one of them. Sadly we just get trolled into the ground :) All in all it's not the job of PostgreSQL to tell the
Re: [HACKERS] Compatibility types, type aliases, and distinct types
Am Monday, 18. August 2008 schrieb Tom Lane: If the type has no functions of its own, then the only way to make it easily usable is to throw in implicit conversions *in both directions* between it and the type it's an alias for. You're going to find that that's a problem. I'm not finding that that's a problem. We have several cases of that in the standard catalogs already. What kind of problem are you foreseeing? One direction of the cast could be AS ASSIGNMENT, btw., but that is another decision that would have to be worked out. -- 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] possible minor EXPLAIN bug?
On Tue, 2008-08-19 at 09:45 +0200, Pavel Stehule wrote: postgres=# explain select sum(a) from twocol group by b; QUERY PLAN HashAggregate (cost=42.10..44.60 rows=200 width=8) -- wrong should be 4 - Seq Scan on twocol (cost=0.00..31.40 rows=2140 width=8) (2 rows) Although column b is not displayed it is kept in the HashAgg node to allow your request to GROUP BY B. I'm happy that it tells me the width of 8 so I can work out space used by hash, but perhaps it should say 12 (or even 16) to include hash value also, so we include the full cost per row in the hash table. If you do explain select sum(a) from twocol you will see the width is only 4 -- 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] Extending varlena
Am Monday, 18. August 2008 schrieb Tom Lane: - permissions features (more than none anyway) - better management of orphaned objects (obsoleting vacuumlo) - support 16TB of large objects (maybe partition pg_largeobject?) - dump and restore probably need improvement to be practical for such large data volumes If you replace the third point by maybe partition TOAST tables, replace large object handle by TOAST pointer, and create an API to work on TOAST pointers, how are the two so much different? And why should they be? I can see that there are going to be needs to access large data with interfaces that are not traditional SQL, but at least the storage handling could be the same. That way you would solve the first two points and others for free. -- 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
Am Monday, 18. August 2008 schrieb Josh Berkus: Right now, if you want to survey your databases, tables, approx disk space, query activity, etc., you can do that all through port 5432. You can't manage most of your server settings that way, and definitely can't manage the *persistent* settings. When you're trying to manage 1000 PostgreSQL servers, this is not a minor issue. Some of that effort could go into making less settings persistent. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Auto-tuning GUCS
I do think you and others make it less likely every time you throw up big insoluble problems like above though. As a consequence every proposal has started with big overly-complex solutions trying to solve all these incidental issues which never go anywhere instead of simple solutions which directly tackle the main problem. insoluble? overly-complex solution? parsing a text file? I do not think we understand each other, or rather we start with totally different assumptions and design goals. it was probably a mistake to post keeping the subject line as it is, considering I have no interest overhauling GUCS, but this is where the subject of autotuning was brought up and this is where I posted. now, to me, shell access, cron jobs, text config files - or rather, a single text config file, these are all good. if you plan to deploy/maintain entire farms or cloud solutions, tough! you should be looking into configuration management, such as cfengine and puppet already! you seem to consider ease of use a prerequisite for tuning efficiency, our design goals couldn't be more different. what you want is an installer - what I'd like is DBA support Coping with user and system-generated comments is one difficult part that people normally don't consider, dealing with bad settings the server won't start with is another. now, as things stand, I will tinker in this area, simply because I'm stubborn and this is part of my job. I have parsed many text files in my professional career, please do not think a simple config file should be a problem (even with comments, I think) The impression I get every time this comes up is that various people have different problems they want to solve that (they think) require redesign of the way GUC works. Those complicated solutions arise from attempting to satisfy N different demands simultaneously. The fact that many of these goals aren't subscribed to by the whole community to begin with doesn't help to ease resolution of the issues. in this single thread I have identified at least three different development targets: * newbie-friendly default-guessing installer * configuration manager for farms/clouds etc. * auto-tuning support this is why I'm posting this with a different subject line. If anyone wants to discuss the GUCS auto-tuning part, I'm all ears. regards, Michael
Re: [HACKERS] possible minor EXPLAIN bug?
2008/8/19 Simon Riggs [EMAIL PROTECTED]: On Tue, 2008-08-19 at 09:45 +0200, Pavel Stehule wrote: postgres=# explain select sum(a) from twocol group by b; QUERY PLAN HashAggregate (cost=42.10..44.60 rows=200 width=8) -- wrong should be 4 - Seq Scan on twocol (cost=0.00..31.40 rows=2140 width=8) (2 rows) Although column b is not displayed it is kept in the HashAgg node to allow your request to GROUP BY B. I'm happy that it tells me the width of 8 so I can work out space used by hash, but perhaps it should say 12 (or even 16) to include hash value also, so we include the full cost per row in the hash table. If you do explain select sum(a) from twocol you will see the width is only 4 yes, Agg get this value directly, but it wrong postgres=# explain select * from (select sum(a) from twocol group by b offset 0) c; QUERY PLAN Subquery Scan c (cost=42.10..46.60 rows=200 width=8) - Limit (cost=42.10..44.60 rows=200 width=8) - HashAggregate (cost=42.10..44.60 rows=200 width=8) - Seq Scan on twocol (cost=0.00..31.40 rows=2140 width=8) (4 rows) limit, subquery scan has wrong width now. regards Pavel -- 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
Am Monday, 18. August 2008 schrieb Tom Lane: The impression I get every time this comes up is that various people have different problems they want to solve that (they think) require redesign of the way GUC works. Those complicated solutions arise from attempting to satisfy N different demands simultaneously. Which may be the reason that I have been getting the impression that the Problems and the proposed resolutions on http://wiki.postgresql.org/wiki/GUCS_Overhaul are not really closely related. I can agree with the Problems, but then I am lost. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] A smaller default postgresql.conf
I seem to recall that there was general support for installing a smaller default postgresql.conf file with only, say, a dozen parameters mentioned for initial tuning. The complete file can stay as a sample. Any objections to that? (Let's not discuss quite yet exactly which parameters are the chosen ones.) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A smaller default postgresql.conf
Peter Eisentraut wrote: I seem to recall that there was general support for installing a smaller default postgresql.conf file with only, say, a dozen parameters mentioned for initial tuning. The complete file can stay as a sample. Any objections to that? (Let's not discuss quite yet exactly which parameters are the chosen ones.) i think this would make sense as long as this small file tells users where to find the full story. generally i would say that this would be a step into the right direction. alternatively we could use some sort of #include mechanism to split most important and not so important. hans -- Cybertec Schönig Schönig GmbH PostgreSQL Solutions and Support Gröhrmühlgasse 26, A-2700 Wiener Neustadt Tel: +43/1/205 10 35 / 340 www.postgresql-support.de, www.postgresql-support.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
On Mon, 2008-08-18 at 22:41 +0200, Pavel Stehule wrote: 2008/8/18 Hannu Krosing [EMAIL PROTECTED]: On Mon, 2008-08-18 at 11:05 +0200, Pavel Stehule wrote: 2008/8/18 Dimitri Fontaine [EMAIL PROTECTED]: Hi, Le lundi 18 août 2008, Andrew Dunstan a écrit : On Sat, Aug 16, 2008 at 09:40:19PM -0400, Tom Lane wrote: This is not the kind of patch we put into stable branches. So what? That is not the only criterion for backpatching. I fail to understand why this problem is not qualified as a bug. Does it change of result some queries? Not in the long run, but not invalidating the functions (current behaviour) postpones seeing the results of function change until DBA manually restarts the error-producing client. It is protection to server's hang? Can't understand this question :( If you mean, does the change protect against hanging the server, then no, currently the server does not actually hang, it just becomes unusable until reconnect :( Hi I am sorry, but it's really new feature and not bug fix Could you please explain why you think so ? As I see it, the patch does not change visible behaviour, except removing some sonditions where client becomes unusable after some other backend does some legitimate changes. Is the current behavior planned or even defined by spec ? I agree, that the bug (if it is a bug) could also be circumvented by the calling function by detecting a failed cache lookup and doing replan/requery itself, but this would require all PL implementations and other functions with stored plans to do it independently. - 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] Patch: plan invalidation vs stored procedures
On Mon, 2008-08-18 at 20:29 -0400, Tom Lane wrote: Asko Oja [EMAIL PROTECTED] writes: For users of stored procedures it is protection from downtime. For Skype it has been around 20% of databse related downtime this year. Perhaps Skype needs to rethink how they are modifying functions. Why not suggest they just should stop using functions and move all business logic into client or 3rd tier ? (Actually I would not recommend that as functions are very good way to abstract database access AND provide better security AND speed up queries) The reason that this case wasn't covered in 8.3 is that there didn't seem to be a use-case that justified doing the extra work. I still haven't seen one. Other than inline-able SQL functions there is no reason to invalidate a stored plan based on the fact that some function it called changed contents. Maybe there should be something in postgreSQL docs that warns users against using functions in any non-trivial circumstances, as functions are not expected to behave like the rest of postgreSQL features and there is not plan to fix that ? 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] Patch: plan invalidation vs stored procedures
2008/8/19 Hannu Krosing [EMAIL PROTECTED]: On Mon, 2008-08-18 at 22:41 +0200, Pavel Stehule wrote: 2008/8/18 Hannu Krosing [EMAIL PROTECTED]: On Mon, 2008-08-18 at 11:05 +0200, Pavel Stehule wrote: 2008/8/18 Dimitri Fontaine [EMAIL PROTECTED]: Hi, Le lundi 18 août 2008, Andrew Dunstan a écrit : On Sat, Aug 16, 2008 at 09:40:19PM -0400, Tom Lane wrote: This is not the kind of patch we put into stable branches. So what? That is not the only criterion for backpatching. I fail to understand why this problem is not qualified as a bug. Does it change of result some queries? Not in the long run, but not invalidating the functions (current behaviour) postpones seeing the results of function change until DBA manually restarts the error-producing client. It is protection to server's hang? Can't understand this question :( If you mean, does the change protect against hanging the server, then no, currently the server does not actually hang, it just becomes unusable until reconnect :( Hi I am sorry, but it's really new feature and not bug fix Could you please explain why you think so ? As I see it, the patch does not change visible behaviour, except removing some sonditions where client becomes unusable after some other backend does some legitimate changes. Are you sure, so this behave hasn't any secondary effect? So this change doesn't breaks any application? Pavel Is the current behavior planned or even defined by spec ? I agree, that the bug (if it is a bug) could also be circumvented by the calling function by detecting a failed cache lookup and doing replan/requery itself, but this would require all PL implementations and other functions with stored plans to do it independently. - 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] Patch: plan invalidation vs stored procedures
2008/8/19 Hannu Krosing [EMAIL PROTECTED]: On Mon, 2008-08-18 at 22:41 +0200, Pavel Stehule wrote: 2008/8/18 Hannu Krosing [EMAIL PROTECTED]: On Mon, 2008-08-18 at 11:05 +0200, Pavel Stehule wrote: 2008/8/18 Dimitri Fontaine [EMAIL PROTECTED]: Hi, Le lundi 18 août 2008, Andrew Dunstan a écrit : On Sat, Aug 16, 2008 at 09:40:19PM -0400, Tom Lane wrote: This is not the kind of patch we put into stable branches. So what? That is not the only criterion for backpatching. I fail to understand why this problem is not qualified as a bug. Does it change of result some queries? Not in the long run, but not invalidating the functions (current behaviour) postpones seeing the results of function change until DBA manually restarts the error-producing client. It is protection to server's hang? Can't understand this question :( If you mean, does the change protect against hanging the server, then no, currently the server does not actually hang, it just becomes unusable until reconnect :( Hi I am sorry, but it's really new feature and not bug fix Could you please explain why you think so ? As I see it, the patch does not change visible behaviour, except removing some sonditions where client becomes unusable after some other backend does some legitimate changes. Is the current behavior planned or even defined by spec ? I agree, that the bug (if it is a bug) could also be circumvented by the calling function by detecting a failed cache lookup and doing replan/requery itself, but this would require all PL implementations and other functions with stored plans to do it independently. I am not against to this patch or this feature. But I am sure, so isn't well to do not necessary changes in stable version. Pavel - 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] Patch: plan invalidation vs stored procedures
On Tue, 2008-08-19 at 12:42 +0200, Pavel Stehule wrote: 2008/8/19 Hannu Krosing [EMAIL PROTECTED]: On Mon, 2008-08-18 at 22:41 +0200, Pavel Stehule wrote: 2008/8/18 Hannu Krosing [EMAIL PROTECTED]: On Mon, 2008-08-18 at 11:05 +0200, Pavel Stehule wrote: 2008/8/18 Dimitri Fontaine [EMAIL PROTECTED]: Hi, Le lundi 18 août 2008, Andrew Dunstan a écrit : On Sat, Aug 16, 2008 at 09:40:19PM -0400, Tom Lane wrote: This is not the kind of patch we put into stable branches. So what? That is not the only criterion for backpatching. I fail to understand why this problem is not qualified as a bug. Does it change of result some queries? Not in the long run, but not invalidating the functions (current behaviour) postpones seeing the results of function change until DBA manually restarts the error-producing client. It is protection to server's hang? Can't understand this question :( If you mean, does the change protect against hanging the server, then no, currently the server does not actually hang, it just becomes unusable until reconnect :( Hi I am sorry, but it's really new feature and not bug fix Could you please explain why you think so ? As I see it, the patch does not change visible behaviour, except removing some sonditions where client becomes unusable after some other backend does some legitimate changes. Are you sure, so this behave hasn't any secondary effect? So this change doesn't breaks any application? I can't think of any. What it does, is it makes the changed function usable right after redefining the new function. Current behaviour is to make the calling function unusable until the backend is restarted, after which it still will use the new version of the function. Pavel Is the current behavior planned or even defined by spec ? I agree, that the bug (if it is a bug) could also be circumvented by the calling function by detecting a failed cache lookup and doing replan/requery itself, but this would require all PL implementations and other functions with stored plans to do it independently. - 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] Patch: plan invalidation vs stored procedures
Hannu Krosing [EMAIL PROTECTED] writes: Maybe there should be something in postgreSQL docs that warns users against using functions in any non-trivial circumstances, as functions are not expected to behave like the rest of postgreSQL features and there is not plan to fix that ? Now who's trolling :) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] about postgres-r setup.
Hi leiyonghua, leiyonghua wrote: and still same status. Uh.. do you have debugging enabled? Any logging output of the two postmaster processes? 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] Postgres-R
Hi, leiyonghua wrote: ./configure --enable-replication make make install You certainly also want --enable-debug and --enable-cassert, maybe also additional flags for the C compiler, like -DRMGR_DEBUG, please check the source code for these. 4. install the GCS ensemble, according the document : http://www.cs.technion.ac.il/dsl/projects/Ensemble/doc.html 5. start ensemble daemon and gossip if neccessary ( yes, make sure the two nodes can 'GCS' each other) Yeah, either use the gossip process, or make sure IP multicast works for your network configuration. I admit that ensemble is quite a beast WRT compilation and configuration. 3. Assume that you have two nodes, start up postgresql and create a database 'db', and create a table 'tb' for testing which should be have a primary key for all nodes. 4. At the origin node, execute the command at psql console: alter database db start replication in group gcs; (which means the database 'db' is the origin and the group 'gcs' is the GCS group name) 5. At the subscriber node, execute the command: alter database db accept replication from group gcs; As recovery doesn't work automatically, you still need to sync the complete database from the node which initiated the replication group. Then accept replication. I'm working on automatic recovery. Regards Markus Wanner -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Postgres-R
Hi, K, Niranjan (NSN - IN/Bangalore) wrote: Thanks for the information. For Step5 (starting ensemble daemon).- I set the multicast address to both nodes (Node 1 Node 2 eth0: 224.0.0.9/4) before starting the ensemble. And started the server application mtalk in node 1 node 2 and then client application in node 1 node 2. But the count of members ('nmembers') show as 1. This is the output of the client program 'c_mtalk'. Seeing this, I'am assuming that the applications are not merged. This sounds like IP multicast does not work properly for your network (is IP multicast available and enabled for your OS? Maybe you are running on virtual hosts with a virtual network, which doesn't support multicasting?). You can either try to fix that or switch to using a gossip process. Regards Markus Wanner -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Improving non-joinable EXISTS subqueries
* Tom Lane ([EMAIL PROTECTED]) wrote: So ... I'm wondering if this actually touches anyone's hot-button, or if we should just file it in the overflowing pile of Things That Might Be Nice To Do Someday. What bugs me the most about having IN() be faster than EXISTS() in certain situations is that it ends up being counter-intuitive and not really what you'd expect to happen. That being said, we can always tell people that they can use IN() as a work-around for these situations. In the long run, I think it's definitely worth it to spend a bit of extra time in planning the query for this case. Not knowing what else is on your plate for 8.4, I don't know where I'd rank this, but it wouldn't be at the top. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] possible minor EXPLAIN bug?
Pavel Stehule [EMAIL PROTECTED] writes: I thing so Agg node doesn't set width well: The planner doesn't really bother to set the width correctly for any expression-computing node. This is partly laziness, but OTOH it is very hard to estimate a sane width for any function returning a variable-width data type; eg what are the odds of a useful answer for select repeat(textcol, intcol) from tab1; For plan nodes that return just Vars it's easier, since we generally have got stats about average column widths. I think Agg just copies the width of its input ... 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] Compatibility types, type aliases, and distinct types
Peter Eisentraut [EMAIL PROTECTED] writes: One direction of the cast could be AS ASSIGNMENT, btw., but that is another decision that would have to be worked out. Making the back-cast be AS ASSIGNMENT would reduce the risks of ambiguities, for sure. 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] Improving non-joinable EXISTS subqueries
Tom Lane [EMAIL PROTECTED] wrote: The examples that Kevin Grittner put up awhile back included several uses of EXISTS() in places where it couldn't be turned into a semijoin, eg in the query's targetlist. I was musing a bit about whether we could improve those scenarios. I would like to get 8.4 to the point where we could say as a blanket performance recommendation prefer EXISTS over IN. The semantic gotchas associated with NOT IN make it hard to optimize well, not to mention being a perennial bane of novices; so if we could just point people in the other direction without qualification I think we'd be better off. Agreed. So ... I'm wondering if this actually touches anyone's hot-button, or if we should just file it in the overflowing pile of Things That Might Be Nice To Do Someday. Comments? I'm in the position of trying to influence programmers here to write queries using set logic. Way too many of the queries here are coded with a cursor for a primary select, with a bunch of lower level cursors to navigate around and get the related rows one at a time. Results are often stuck into a work table as this progresses, with the work table massaged a bit here and there in this procedural process, and the final results selected out. It should surprise nobody here that this is not fast to write, easy to maintain, efficient to run, or generally free from subtle errors. I point out that they should write queries which state what they want, regardless of how complex those rules are, instead of writing how to get it. The optimizer, I argue, has tricks available which they don't. Usually, a rewrite into set logic has a fraction of the number of lines, runs much faster, and loses a bug or two that was hidden within the procedural spaghetti. On the other hand, sometimes they write a perfectly good set logic query (from the point of view of stating what they want), and the optimizer falls down, and I have to come in and say Oh, it has trouble with EXISTS; you can use IN here. When I tell them to use IN instead of EXISTS, then I need to have all these caveats about the sizes of tables and the possibilities of NULL on the NOT EXISTS. At this point I tend to lose a big part of my audience. So I'd be very happy to see this work done, not because I can't find a workaround, but because trying to teach all the programmers tricky hand-optimizations is a losing battle, and if I lose that battle the queries degenerate into spaghetti-land. As with others, I can't say where this fits on a priority list, but I would hate to see it drift off onto a someday list. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
Hannu Krosing [EMAIL PROTECTED] writes: On Mon, 2008-08-18 at 22:41 +0200, Pavel Stehule wrote: I am sorry, but it's really new feature and not bug fix Could you please explain why you think so ? For the same reasons that plan invalidation itself was a new feature and not a bug fix; notably, risk vs reward tradeoff and not wanting to change long-established behavior in stable branches. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A smaller default postgresql.conf
Hans-Juergen Schoenig wrote: alternatively we could use some sort of #include mechanism to split most important and not so important. We already have an include mechanism. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A smaller default postgresql.conf
Alvaro Herrera wrote: Hans-Juergen Schoenig wrote: alternatively we could use some sort of #include mechanism to split most important and not so important. We already have an include mechanism. Using that to include a file that's full of comments anyway (which is all that's left in postgresql.conf at this time, I'm sure) just seems. Well. Sub-optimal. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extending varlena
Tom Lane [EMAIL PROTECTED] wrote: I was kinda wondering about something closer to the TOAST model, where a blob is only referenceable from a value that's in a table field; and that value encapsulates the name of the blob in some way that needn't even be user-visible. This'd greatly simplify the cleanup-dead-objects problem, and we could avoid addressing the permissions problem at all, since regular SQL permissions on the table would serve fine. But it's not clear what regular SQL fetch and update behaviors should be like for such a thing. (Fetching or storing the whole blob value is right out, IMHO.) ISTR hearing of concepts roughly like this in other DBs --- does it ring a bell for anyone? It'd probably be good to have methods parallel to the JDBC API within the implementation. http://java.sun.com/javase/6/docs/api/java/sql/Blob.html http://java.sun.com/javase/6/docs/api/java/sql/Clob.html http://java.sun.com/javase/6/docs/api/java/sql/NClob.html http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#getBlob(int) http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#getClob(int) http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#getNClob(int) http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#updateBlob(int,%20java.sql.Blob) http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#updateBlob(int,%20java.io.InputStream) http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#updateBlob(int,%20java.io.InputStream,%20long) (similar for CLOB NCLOB) Reading a blob value gives you an object which lets you perform the stream-based operations against the blob. To set a blob value on an insert, you prepare a statement and then link the stream to the blob -- the insertRow method sucks the data from the stream. To set a blob on an update, you use an updateable cursor (or maybe a prepared statement) to do the same. You can set a lob from another lob directly in SQL I assume we'd want to support streams directly inline in the protocol, as well as support functions to convert between datums and streams (for, say, tiny little 2MB or 10MB values), and files and streams (kinda like COPY). -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A smaller default postgresql.conf
On Tue, 19 Aug 2008 17:11:49 +0200 Magnus Hagander [EMAIL PROTECTED] wrote: Alvaro Herrera wrote: Hans-Juergen Schoenig wrote: alternatively we could use some sort of #include mechanism to split most important and not so important. We already have an include mechanism. Using that to include a file that's full of comments anyway (which is all that's left in postgresql.conf at this time, I'm sure) just seems. Well. Sub-optimal. Yes but part of this idea is valid. The fact is the majority of the postgresql.conf parameters don't need to be in there by default. It just makes the file an intimidating mess for newbies and I am not talking about just n00bs but also people coming from other environments such as MSSQL. I believe we could probably break the conf down to a reasonable 2 dozen or less parameters. The rest should just be documented in our documentation and call it good. We even have static URLs for this (I seem to have dejavu with this as I am pretty sure I have had this discussion already). Joshua D Drake //Magnus -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
Le mardi 19 août 2008, Tom Lane a écrit : For the same reasons that plan invalidation itself was a new feature and not a bug fix; I'm sorry but that doesn't help me a dime to understand current situation. It could well be just me, but... here's how I see it: - plan invalidation is a new feature in 8.3 - previous releases are out of business: new feature against stable code - now, we have found a bug in plan invalidation - HEAD (to be 8.4) will get some new code to fix it But 8.3 won't benefit from this bugfix? On the grounds that the feature which is now deployed on the field should *maybe* not get used the way it *is*? Sorry again, I really don't get it. -- dim signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] Patch: plan invalidation vs stored procedures
On Tue, 19 Aug 2008 12:48:06 +0100 Gregory Stark [EMAIL PROTECTED] wrote: Hannu Krosing [EMAIL PROTECTED] writes: Maybe there should be something in postgreSQL docs that warns users against using functions in any non-trivial circumstances, as functions are not expected to behave like the rest of postgreSQL features and there is not plan to fix that ? Now who's trolling :) Although I read his remark as sarcastic after reading the entire thread I have to say it may be a good idea to have the something in the docs about the limitation. I never think about it anymore because I am used to the behavior. I can see where and entity like skype who has I am sure thousands of procedures would have this as a constant irritant. Do I think it should be pushed back to 8.3.x; no. It is a feature. I don't consider the existing behavior a bug. I consider it a limitation and we don't back patch fixes for limitations. Sincerely, Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
Dimitri Fontaine [EMAIL PROTECTED] writes: - now, we have found a bug in plan invalidation [ shrug... ] You have not found a bug in plan invalidation. You have found omitted functionality --- functionality that was *intentionally* omitted from the 8.3 version. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A smaller default postgresql.conf
Joshua Drake [EMAIL PROTECTED] writes: Magnus Hagander [EMAIL PROTECTED] wrote: Using that to include a file that's full of comments anyway (which is all that's left in postgresql.conf at this time, I'm sure) just seems. Well. Sub-optimal. Yes but part of this idea is valid. The fact is the majority of the postgresql.conf parameters don't need to be in there by default. It just makes the file an intimidating mess for newbies and I am not talking about just n00bs but also people coming from other environments such as MSSQL. Well, why not just make a one-eighty and say that the default postgresql.conf is *empty* (except for whatever initdb puts into it)? I've never thought that the current contents were especially useful as documentation; the kindest thing you can say about 'em is that they are duplicative of the SGML documentation. For novices they aren't even adequately duplicative. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A smaller default postgresql.conf
On Tue, 19 Aug 2008 12:12:16 -0400 Tom Lane [EMAIL PROTECTED] wrote: Joshua Drake [EMAIL PROTECTED] writes: Magnus Hagander [EMAIL PROTECTED] wrote: Yes but part of this idea is valid. The fact is the majority of the postgresql.conf parameters don't need to be in there by default. It just makes the file an intimidating mess for newbies and I am not talking about just n00bs but also people coming from other environments such as MSSQL. Well, why not just make a one-eighty and say that the default postgresql.conf is *empty* (except for whatever initdb puts into it)? I guess it would depend on what initdb puts into it. I don't really have a problem ripping out all extra stuff as it would help force people to read the docs but would we still have 150 parameters?. From a friendly perspective it would make sense to tone it down to the key parameters such as shared_buffers, listen_address, work_mem etc... We don't need (for example) to have autovacuum in there by default as it is always on and configured reasonably at this point. If they need to change autovacuum they should be reading the docs about it first; the same with bgwriter, fsync, async_commit etc... I've never thought that the current contents were especially useful as documentation; the kindest thing you can say about 'em is that they are duplicative of the SGML documentation. For novices they aren't even adequately duplicative. I can't argue with this. :) Sincerely, Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Time to get rid of -Winline ?
Having recently updated my work machine to Fedora 9, I'm now getting blessed with all the -Winline warnings that gcc 4.3 likes to emit. I recall some other folk complaining of that previously. While I could suppress the switch in a Makefile.custom, I'm wondering whether it's really doing anything for us. The original motivation for putting it in by default was to find out how many of our inline decorations were really working, but AFAIR we have never done anything with that issue anyway... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
Le mardi 19 août 2008, Tom Lane a écrit : [ shrug... ] You have not found a bug in plan invalidation. You have found omitted functionality --- functionality that was *intentionally* omitted from the 8.3 version. Thanks a lot for this clarification, now I understand you viewpoint. So, the 8.3 fix would be about documenting this intentionnal omit in the great manual, maybe in a Limits section of the sql-createfunction page? Another thing I do not understand well is how people are expected to work in 8.3 with a function based API, without hitting Skype problems. I'm having a project here where the project manager wants a database function API to keep data logic at serverside, should I tell him to reconsider this while 8.4 is not ready? We would then have to go live with an 8.3 based solution containing middleware code, then port it again to SQL functions when 8.4 is out stable. Not appealing, but I sure understand the no new feature in stable code base argument here. Regards, -- dim signature.asc Description: This is a digitally signed message part.
[HACKERS] Adjusting debug_print_plan to be more useful by default
Back in April we changed EXPLAIN VERBOSE to not dump the internal plan tree anymore, on the grounds that non-hackers didn't want that info and hackers could get it with debug_print_plan and related variables. Well, now that I've tried to do some planner development work relying on debug_print_plan instead of EXPLAIN VERBOSE, I find it a mite annoying. It's not sufficient to set debug_print_plan = true, because the output comes out at priority DEBUG1, which is to say it doesn't come out at all in a default configuration. If you boost up client_min_messages or log_min_messages so you can see it, you get lots of extraneous debugging messages too. I'd like to propose that the messages emitted by debug_print_plan and friends be given priority LOG rather than DEBUG1. If you've gone to the trouble of turning on the variable, then you presumably want the results, so it seems dumb to print them at a priority that isn't logged by default. (Note that this is biased to the assumption that you want the messages in the postmaster log, not on the console. Which is usually what I want, but maybe someone wants to argue for NOTICE?) I'd also like to propose making debug_pretty_print default to ON. At least for me, the other formatting is 100% unreadable. Comments? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A smaller default postgresql.conf
Joshua Drake [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] wrote: Well, why not just make a one-eighty and say that the default postgresql.conf is *empty* (except for whatever initdb puts into it)? I guess it would depend on what initdb puts into it. Per the code: max_connections shared_buffers max_fsm_pages (slated to die anyway in 8.4) lc_messages lc_monetary lc_numeric lc_time datestyle default_text_search_config The first three of those are derived from probing the SHMMAX setting, and the rest are from the initdb-time locale settings. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A smaller default postgresql.conf
Tom, Well, why not just make a one-eighty and say that the default postgresql.conf is *empty* (except for whatever initdb puts into it)? I've never thought that the current contents were especially useful as documentation; the kindest thing you can say about 'em is that they are duplicative of the SGML documentation. For novices they aren't even adequately duplicative. Well, that doesn't help unless we either provide a .conf generation tool (something I favor) or docs somewhere which explain which are the variables to be the most concerned with instead of making users read through all 218 of them. Attached is the postgresql.conf.simple I used in my presentaiton. It has an egregious math error in it (see if you can find it) but should give you the general idea. --Josh # # Simple PostgreSQL Configuration File # # This file provides a simple configuration with the most common options # which most users need to modify for running PostgreSQL in production, # including extensive notes on how to set each of these. If your configuration # needs are more specific, then use the standard postgresql.conf, or add # additional configuration options to the bottom of this file. # # This file is re-read when you send a SIGHUP to the server, or on a full # restart. Note that on a SIGHUP simply recommenting the settings is not # enough to reset to default value; the last explicit value you set will # still be in effect. # # AvRAM: Several of the formulas below ask for AvRAM, which is short for # Available RAM. This refers to the amount of memory which is available for # running PostgreSQL. On a dedicated PostgreSQL server, you can use the total # system RAM, but on shared servers you need to estimate what portion of RAM # is usually available for PostgreSQL. # # Each setting below lists one recommended starting setting, followed by # several alternate settings which are commented out. If multiple settings # are uncommented, the *last* one will take effect. # listen_addresses # # listen_addresses takes a list of network interfaces the Postmaster will # listen on. The setting below, '*', listens on all interfaces, and is only # appropriate for development servers and initial setup. Otherwise, it # should be restrictively set to only specific addresses. Note that most # PostgreSQL access control settings are in the pg_hba.conf file. listen_addresses = '*' # all interfaces # listen_addresses = 'localhost' # unix sockets and loopback only # listen_addresses = 'localhost,192.168.1.1' # local and one external interface # max_connections # # An integer setting a limit on the number of new connection processes which # PostgreSQL will create. Should be set to the maximum number of connections # which you expect to need at peak load. Note that each connection uses # shared_buffer memory, as well as additional non-shared memory, so be careful # not to run the system out of memory. In general, if you need more than 1000 # connections, you should probably be making more use of connection pooling. # # Note that by default 3 connections are reserved for autovacuum and # administration. max_connections = 200 # small server # max_connections = 700 # web application database # max_connections = 40 # data warehousing database # shared_buffers # # A memory quantity defining PostgreSQL's dedicated RAM, which is used # for connection control, active operations, and more. However, since # PostgreSQL also needs free RAM for file system buffers, sorts and # maintenance operations, it is not advisable to set shared_buffers to a # majority of RAM. # # Note that increasing shared_buffers often requires you to increase some # system kernel parameters, most notably SHMMAX and SHMALL. See # Operating System Environment: Managing Kernel Resources in the PostgreSQL # documentation for more details. Also note that shared_buffers over 2GB is # only supported on 64-bit systems. # # The setting below is a formula. Calculate the resulting value, then # uncomment it. Values should be expressed in kB, MB or GB. # shared_buffers = ( AvRAM / 4 ) # shared_buffers = 512MB # basic 2GB web server # shared_buffers = 8GB # 64-bit server with 32GB RAM # work_mem # # This memory quantity sets the limit for the amount of non-shared RAM # available for each query operation, including sorts and hashes. This limit # acts as a primitive resource control, preventing the server from going # into swap due to overallocation. Note that this is non-shared RAM per # *operation*, which means large complex queries can use multple times # this amount. Also, work_mem is allocated by powers of two, so round # to the nearest binary step. # The setting below is a formula. Calculate the resulting value, then
Re: [HACKERS] A smaller default postgresql.conf
On Tue, 19 Aug 2008 12:48:20 -0400 Tom Lane [EMAIL PROTECTED] wrote: Joshua Drake [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] wrote: Well, why not just make a one-eighty and say that the default postgresql.conf is *empty* (except for whatever initdb puts into it)? I guess it would depend on what initdb puts into it. Per the code: max_connections shared_buffers max_fsm_pages (slated to die anyway in 8.4) lc_messages lc_monetary lc_numeric lc_time datestyle default_text_search_config The first three of those are derived from probing the SHMMAX setting, and the rest are from the initdb-time locale settings. When I first started to reply I had a list of another dozen or so we should add but in reality as I think about it; we need only one more parameter. If we add listen_addresses and a link to the documention for the rest, I would +1 this. I was thinking about the apache conf and it is riddled with documentation, lots and lots of text. I find that either I am irritated with how much documentation there is (because I already understand the directive I am working with) or I am frustrated because it doesn't adequately explain the dependencies. If we move to the above route, we end up in an environment with a single source for official documentation and we can always point to that. Sincerely, Joshua D. Drake regards, tom lane -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
Dimitri Fontaine [EMAIL PROTECTED] writes: Another thing I do not understand well is how people are expected to work in 8.3 with a function based API, without hitting Skype problems. I could understand this level of complaining if this were a new problem that'd appeared in 8.3. But *every PG version that we've ever released* behaves the same way with respect to function drop/recreate. If the Skype folk have developed a way of working that is guaranteed not to work with any released version, one has to wonder what they were thinking. If you need to DROP rather than CREATE OR REPLACE functions, then 8.3 doesn't make things better for you than prior releases did, but it does't make them worse either. Making things better for that case is unequivocally a new feature. And it's rather a corner case at that, else there would have been enough prior complaints to put it on the radar screen for 8.3. What we've got at this point is a submitted patch for a new feature that hasn't even been accepted into HEAD yet. Lobbying to get it back-patched is entirely inappropriate IMHO. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A smaller default postgresql.conf
Josh Berkus [EMAIL PROTECTED] wrote: Attached is the postgresql.conf.simple I used in my presentaiton. It has an egregious math error in it (see if you can find it) but should give you the general idea. Well, this sure looks scary: # maintenance_work_mem = 256MB #webserver with 2GB RAM But I'm amazed by this, too: # max_connections = 700 # web application database How many CPUs and spindles are you assuming there? My testing and experience suggest applications should use no more than 4 per CPU plus 2 per spindle, absolute maximum. Don't you find that a connection pool with queuing capability is required for best performance with a large number of users? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A smaller default postgresql.conf
Joshua Drake [EMAIL PROTECTED] writes: If we move to the above route, we end up in an environment with a single source for official documentation and we can always point to that. Yeah, the fundamental point here is whether or not postgresql.conf should be trying to serve as part of our system documentation. I'm inclined to think that any comments in it should be more about why these particular values have been set, and not here are some values you might like to twiddle. So initdb might emit # Set by initdb from probing kernel limits 2008-08-11 max_connections = 100 shared_buffers = 32MB # Set by initdb from its locale environment: LANG = en_US lc_messages = en_US lc_monetary = en_US [etc] I'm really not in favor of having comments in the conf file that try to tell you about stuff you might want to set, much less why. That task properly belongs to some kind of introductory chapter in the SGML docs. Novice DBAs are unlikely even to *find* the config file, let alone look inside it, if there's not an introductory chapter telling them about Things They Ought To Do. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A smaller default postgresql.conf
On Tue, Aug 19, 2008 at 1:17 PM, Kevin Grittner [EMAIL PROTECTED] wrote: Josh Berkus [EMAIL PROTECTED] wrote: But I'm amazed by this, too: # max_connections = 700 # web application database How many CPUs and spindles are you assuming there? My testing and experience suggest applications should use no more than 4 per CPU plus 2 per spindle, absolute maximum. Don't you find that a connection pool with queuing capability is required for best performance with a large number of users? Agreed, with this many concurrent users, I would expect severe lock contention on the ProcArrayLock. Similarly, if this were heavily updated, WAL-related locks would likely become another significant bottleneck. -- Jonah H. Harris, Senior DBA myYearbook.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] Adjusting debug_print_plan to be more useful by default
On Tue, 2008-08-19 at 12:40 -0400, Tom Lane wrote: Back in April we changed EXPLAIN VERBOSE to not dump the internal plan tree anymore, on the grounds that non-hackers didn't want that info and hackers could get it with debug_print_plan and related variables. Well, now that I've tried to do some planner development work relying on debug_print_plan instead of EXPLAIN VERBOSE, I find it a mite annoying. It's not sufficient to set debug_print_plan = true, because the output comes out at priority DEBUG1, which is to say it doesn't come out at all in a default configuration. If you boost up client_min_messages or log_min_messages so you can see it, you get lots of extraneous debugging messages too. I'd like to propose that the messages emitted by debug_print_plan and friends be given priority LOG rather than DEBUG1. If you've gone to the trouble of turning on the variable, then you presumably want the results, so it seems dumb to print them at a priority that isn't logged by default. (Note that this is biased to the assumption that you want the messages in the postmaster log, not on the console. Which is usually what I want, but maybe someone wants to argue for NOTICE?) I'd also like to propose making debug_pretty_print default to ON. At least for me, the other formatting is 100% unreadable. +1 -- 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] A smaller default postgresql.conf
On Tue, 19 Aug 2008 13:22:34 -0400 Tom Lane [EMAIL PROTECTED] wrote: I'm really not in favor of having comments in the conf file that try to tell you about stuff you might want to set, much less why. That task properly belongs to some kind of introductory chapter in the SGML docs. Novice DBAs are unlikely even to *find* the config file, let alone look inside it, if there's not an introductory chapter telling them about Things They Ought To Do. I would be willing to work up a patch that does as you suggest. Sincerely, Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A smaller default postgresql.conf
On Tue, 19 Aug 2008 12:17:46 -0500 Kevin Grittner [EMAIL PROTECTED] wrote: Well, this sure looks scary: # maintenance_work_mem = 256MB #webserver with 2GB RAM I would agree. 2GB isn't that much memory as it is and that is a fairly heft amount of maintenance_work_mem. This isn't the days when vacuum ran via cron at 2am anymore. Autovacuum will fire at any time. But I'm amazed by this, too: # max_connections = 700 # web application database How many CPUs and spindles are you assuming there? My testing and experience suggest applications should use no more than 4 per CPU plus 2 per spindle, absolute maximum. Don't you find that a connection pool with queuing capability is required for best performance with a large number of users? I just did the math on this and I would say you are correct. I had never really evaluated in the way you just had but based on some of our larger installs (32cores, 100 spindles) your math works. Noting that he actually states it is a webserver connecting there should absolutely be a pool in front of PostgreSQL. Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
Polite answers lead to polite discussions. Caling other people names lead to flame wars. It's perfectly ok for Skype to keep our own build of 8.3 with given patch and make it available for whoever might want it. At least now there is almost good enough description why the patch was needed althou it would have been more pleasant if the discussion had been constructive. We didn't keep close enough watch on the list when 8.3 plan invalidation was discussed and it came as bad surprise to us that some parts important to us were left out. By the way it's real nice what you are doing with in and exists improvements. Thanks. regards Asko On Tue, Aug 19, 2008 at 8:06 PM, Tom Lane [EMAIL PROTECTED] wrote: Dimitri Fontaine [EMAIL PROTECTED] writes: Another thing I do not understand well is how people are expected to work in 8.3 with a function based API, without hitting Skype problems. I could understand this level of complaining if this were a new problem that'd appeared in 8.3. But *every PG version that we've ever released* behaves the same way with respect to function drop/recreate. If the Skype folk have developed a way of working that is guaranteed not to work with any released version, one has to wonder what they were thinking. If you need to DROP rather than CREATE OR REPLACE functions, then 8.3 doesn't make things better for you than prior releases did, but it does't make them worse either. Making things better for that case is unequivocally a new feature. And it's rather a corner case at that, else there would have been enough prior complaints to put it on the radar screen for 8.3. What we've got at this point is a submitted patch for a new feature that hasn't even been accepted into HEAD yet. Lobbying to get it back-patched is entirely inappropriate IMHO. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
2008/8/19 Dimitri Fontaine [EMAIL PROTECTED]: Le mardi 19 août 2008, Tom Lane a écrit : [ shrug... ] You have not found a bug in plan invalidation. You have found omitted functionality --- functionality that was *intentionally* omitted from the 8.3 version. Thanks a lot for this clarification, now I understand you viewpoint. So, the 8.3 fix would be about documenting this intentionnal omit in the great manual, maybe in a Limits section of the sql-createfunction page? Another thing I do not understand well is how people are expected to work in 8.3 with a function based API, without hitting Skype problems. I'm having a project here where the project manager wants a database function API to keep data logic at serverside, should I tell him to reconsider this while 8.4 is not ready? You could to use patched 8.3. We would then have to go live with an 8.3 based solution containing middleware code, then port it again to SQL functions when 8.4 is out stable. Not appealing, but I sure understand the no new feature in stable code base argument here. This problem isn't too hard without pooling. Not all systems are global - so usually is possible to find some window and recreate functions and close all user connections. Regards Pavel Stehule Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
Another thing I do not understand well is how people are expected to work in 8.3 with a function based API, without hitting Skype problems. People are expected to use same workarounds as Skype is using. For us another unneccessary downtime week ago was what set us moving/thinking :). When you use software with limitations then you learn to live with them. Good thing about postgres you can do something yourself to get some of the limitations removed. As Pavel said you are probably using your own build anyway so one more patch should not be a problem. regards Asko On Tue, Aug 19, 2008 at 8:48 PM, Pavel Stehule [EMAIL PROTECTED]wrote: 2008/8/19 Dimitri Fontaine [EMAIL PROTECTED]: Le mardi 19 août 2008, Tom Lane a écrit : [ shrug... ] You have not found a bug in plan invalidation. You have found omitted functionality --- functionality that was *intentionally* omitted from the 8.3 version. Thanks a lot for this clarification, now I understand you viewpoint. So, the 8.3 fix would be about documenting this intentionnal omit in the great manual, maybe in a Limits section of the sql-createfunction page? Another thing I do not understand well is how people are expected to work in 8.3 with a function based API, without hitting Skype problems. I'm having a project here where the project manager wants a database function API to keep data logic at serverside, should I tell him to reconsider this while 8.4 is not ready? You could to use patched 8.3. We would then have to go live with an 8.3 based solution containing middleware code, then port it again to SQL functions when 8.4 is out stable. Not appealing, but I sure understand the no new feature in stable code base argument here. This problem isn't too hard without pooling. Not all systems are global - so usually is possible to find some window and recreate functions and close all user connections. Regards Pavel Stehule Regards, -- dim -- 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] temporary statistics option at initdb time
Magnus Hagander wrote: Decibel! wrote: On Aug 13, 2008, at 4:12 AM, Magnus Hagander wrote: Tom Lane wrote: Decibel! [EMAIL PROTECTED] writes: I disagree. While we don't guarantee stats are absolutely up-to-date, or atomic I don't think that gives license for them to just magically not exist sometimes. Would it really be that hard to have the system copy the file out before telling all the other backends of the change? Well, there is no (zero, zilch, nada) use-case for changing this setting on the fly. Why not make it a frozen at postmaster start GUC? Seems like that gets all the functionality needed and most of the ease of use. Oh, there is a use-case. If you run your system and then only afterwards realize the I/O from the stats file is high enough to be an issue, and want to change it. That said, I'm not sure the use-case is anywhere near common enough to put a lot of code into it. Something to keep in mind as PG is used to build larger systems 'further up the enterprise'... for us to bounce a database at work costs us a LOT in lost revenue. I don't want to go into specifics, but it's more than enough to buy a very nice car. :) That's why I asked how hard it'd be to do this on the fly. Well, it's doable, but fairly hard. But you can do it the symlink way without shutting it down, I think. :-) Actually, I think maybe not so hard. Attached is a patch that fixes this. It's done by keeping the old filename around. When you change the path, the stats collector will start writing the new file the next time it writes something (which should be max 0.5 seconds later if something is happening). The backends will immediately try to read from the new filename, but if that one is not found, they will switch to reading the old filename. This obviously fails if you change the temp directory twice in less than half a second, but I really don't see a use-case for that... Or did I miss something here? :-) //Magnus Index: backend/postmaster/pgstat.c === RCS file: /cvsroot/pgsql/src/backend/postmaster/pgstat.c,v retrieving revision 1.179 diff -c -r1.179 pgstat.c *** backend/postmaster/pgstat.c 15 Aug 2008 08:37:39 - 1.179 --- backend/postmaster/pgstat.c 19 Aug 2008 15:24:59 - *** *** 110,115 --- 110,116 */ char *pgstat_stat_filename = NULL; char *pgstat_stat_tmpname = NULL; + char *pgstat_stat_lastname = NULL; /* * BgWriter global statistics counters (unused in other processes). *** *** 203,208 --- 204,210 static volatile bool need_exit = false; static volatile bool need_statwrite = false; + static volatile bool got_SIGHUP = false; /* * Total time charged to functions so far in the current backend. *** *** 224,229 --- 226,232 static void pgstat_exit(SIGNAL_ARGS); static void force_statwrite(SIGNAL_ARGS); static void pgstat_beshutdown_hook(int code, Datum arg); + static void pgstat_sighup_handler(SIGNAL_ARGS); static PgStat_StatDBEntry *pgstat_get_db_entry(Oid databaseid, bool create); static void pgstat_write_statsfile(bool permanent); *** *** 2571,2577 * Ignore all signals usually bound to some action in the postmaster, * except SIGQUIT and SIGALRM. */ ! pqsignal(SIGHUP, SIG_IGN); pqsignal(SIGINT, SIG_IGN); pqsignal(SIGTERM, SIG_IGN); pqsignal(SIGQUIT, pgstat_exit); --- 2574,2580 * Ignore all signals usually bound to some action in the postmaster, * except SIGQUIT and SIGALRM. */ ! pqsignal(SIGHUP, pgstat_sighup_handler); pqsignal(SIGINT, SIG_IGN); pqsignal(SIGTERM, SIG_IGN); pqsignal(SIGQUIT, pgstat_exit); *** *** 2635,2640 --- 2638,2652 break; /* + * Reload configuration if we got SIGHUP from the postmaster. + */ + if (got_SIGHUP) + { + ProcessConfigFile(PGC_SIGHUP); + got_SIGHUP = false; + } + + /* * If time to write the stats file, do so. Note that the alarm * interrupt isn't re-enabled immediately, but only after we next * receive a stats message; so no cycles are wasted when there is *** *** 2834,2839 --- 2846,2858 need_statwrite = true; } + /* SIGHUP handler for collector process */ + static void + pgstat_sighup_handler(SIGNAL_ARGS) + { + got_SIGHUP = true; + } + /* * Lookup the hash table entry for the specified database. If no hash *** *** 3018,3023 --- 3037,3059 if (permanent) unlink(pgstat_stat_filename); + else + { + /* + * If the old filename exists, we need to unlink it now that we have written + * the new file. This indicates that we are done, and it also makes it possible + * to switch the directory back without getting the old data. + * + * Also do away with the old name here, so we don't try to delete it again. + */ + if (pgstat_stat_lastname != NULL) +
Re: [HACKERS] Adjusting debug_print_plan to be more useful by default
On Tue, Aug 19, 2008 at 06:33:33PM +0100, Simon Riggs wrote: On Tue, 2008-08-19 at 12:40 -0400, Tom Lane wrote: Back in April we changed EXPLAIN VERBOSE to not dump the internal plan tree anymore, on the grounds that non-hackers didn't want that info and hackers could get it with debug_print_plan and related variables. Well, now that I've tried to do some planner development work relying on debug_print_plan instead of EXPLAIN VERBOSE, I find it a mite annoying. It's not sufficient to set debug_print_plan = true, because the output comes out at priority DEBUG1, which is to say it doesn't come out at all in a default configuration. If you boost up client_min_messages or log_min_messages so you can see it, you get lots of extraneous debugging messages too. I'd like to propose that the messages emitted by debug_print_plan and friends be given priority LOG rather than DEBUG1. If you've gone to the trouble of turning on the variable, then you presumably want the results, so it seems dumb to print them at a priority that isn't logged by default. (Note that this is biased to the assumption that you want the messages in the postmaster log, not on the console. Which is usually what I want, but maybe someone wants to argue for NOTICE?) I'd also like to propose making debug_pretty_print default to ON. At least for me, the other formatting is 100% unreadable. +1 +1 -dg -- David Gould [EMAIL PROTECTED] 510 536 1443510 282 0869 If simplicity worked, the world would be overrun with insects. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
Joshua Drake wrote: On Tue, 19 Aug 2008 12:48:06 +0100 Gregory Stark [EMAIL PROTECTED] wrote: Hannu Krosing [EMAIL PROTECTED] writes: Maybe there should be something in postgreSQL docs that warns users against using functions in any non-trivial circumstances, as functions are not expected to behave like the rest of postgreSQL features and there is not plan to fix that ? Now who's trolling :) Although I read his remark as sarcastic after reading the entire thread I have to say it may be a good idea to have the something in the docs about the limitation. I never think about it anymore because I am used to the behavior. I can see where and entity like skype who has I am sure thousands of procedures would have this as a constant irritant. Do I think it should be pushed back to 8.3.x; no. It is a feature. I don't consider the existing behavior a bug. I consider it a limitation and we don't back patch fixes for limitations. The bottom line here is that we don't have the time to explain or justify our backpatch policy every time someone shows up with a bug that needs to be fixed. If you want to create your own version of Postgres, go ahead; no one is stopping you. But if we backpatched everything and we introduced bugs or change behavior, more people would complain. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
On Tue, 19 Aug 2008 14:29:52 -0400 (EDT) Bruce Momjian [EMAIL PROTECTED] wrote: Do I think it should be pushed back to 8.3.x; no. It is a feature. I don't consider the existing behavior a bug. I consider it a limitation and we don't back patch fixes for limitations. The bottom line here is that we don't have the time to explain or justify our backpatch policy every time someone shows up with a bug that needs to be fixed. Is our backpatch policy documented? It does not appear to be in developer FAQ. Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] temporary statistics option at initdb time
Magnus Hagander [EMAIL PROTECTED] writes: Actually, I think maybe not so hard. Attached is a patch that fixes this. It's done by keeping the old filename around. When you change the path, the stats collector will start writing the new file the next time it writes something (which should be max 0.5 seconds later if something is happening). The backends will immediately try to read from the new filename, but if that one is not found, they will switch to reading the old filename. This obviously fails if you change the temp directory twice in less than half a second, but I really don't see a use-case for that... I think this is introducing complication and race conditions to solve a problem that no one will really care about. Just let people change the filename at SIGHUP and document that doing that on-the-fly may cause stats queries to fail for a short interval. 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] Adjusting debug_print_plan to be more useful by default
On Tue, 2008-08-19 at 12:40 -0400, Tom Lane wrote: Back in April we changed EXPLAIN VERBOSE to not dump the internal plan tree anymore, on the grounds that non-hackers didn't want that info and hackers could get it with debug_print_plan and related variables. Well, now that I've tried to do some planner development work relying on debug_print_plan instead of EXPLAIN VERBOSE, I find it a mite annoying. It's not sufficient to set debug_print_plan = true, because the output comes out at priority DEBUG1, which is to say it doesn't come out at all in a default configuration. If you boost up client_min_messages or log_min_messages so you can see it, you get lots of extraneous debugging messages too. I'd like to propose that the messages emitted by debug_print_plan and friends be given priority LOG rather than DEBUG1. If you've gone to the trouble of turning on the variable, then you presumably want the results, so it seems dumb to print them at a priority that isn't logged by default. (Note that this is biased to the assumption that you want the messages in the postmaster log, not on the console. Which is usually what I want, but maybe someone wants to argue for NOTICE?) what about changing (or adding) values log and notice ? debug_print_plan = log; debug_print_plan = notice; so you could set that on demand ? I'd also like to propose making debug_pretty_print default to ON. At least for me, the other formatting is 100% unreadable. Comments? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
Joshua Drake wrote: On Tue, 19 Aug 2008 14:29:52 -0400 (EDT) Bruce Momjian [EMAIL PROTECTED] wrote: Do I think it should be pushed back to 8.3.x; no. It is a feature. I don't consider the existing behavior a bug. I consider it a limitation and we don't back patch fixes for limitations. The bottom line here is that we don't have the time to explain or justify our backpatch policy every time someone shows up with a bug that needs to be fixed. Is our backpatch policy documented? It does not appear to be in developer FAQ. Seems we need to add it. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Adjusting debug_print_plan to be more useful by default
Hannu Krosing [EMAIL PROTECTED] writes: what about changing (or adding) values log and notice ? debug_print_plan = log; debug_print_plan = notice; so you could set that on demand ? Well, we could, but it would break existing habits for not much gain. Really this proposal is to make debug_print_plan and friends work like all our other logging options, and AFAIR all the rest emit at level LOG. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A smaller default postgresql.conf
On Tuesday 19 August 2008 19:12:16 Tom Lane wrote: Well, why not just make a one-eighty and say that the default postgresql.conf is *empty* (except for whatever initdb puts into it)? Well, my original implementation of GUC had an empty default configuration file, which was later craptaculated to its current form based on seemingly popular demand. I am very happy to work back toward the empty state, and there appears to be growing support for that. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
Joshua Drake [EMAIL PROTECTED] wrote: Is our backpatch policy documented? It does not appear to be in developer FAQ. It's mentioned here: http://www.postgresql.org/support/versioning PostgreSQL minor releases fix only frequently-encountered, security, and data corruption bugs to reduce the risk of upgrading. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
Bruce Momjian [EMAIL PROTECTED] writes: Joshua Drake wrote: Is our backpatch policy documented? It does not appear to be in developer FAQ. Seems we need to add it. I'm not sure that I *want* a formal written-down backpatch policy. Whether (and how far) to backpatch has always been a best-judgment call in the past, and we've gotten along fine with that. I think having a formal policy is just likely to lead to even more complaints: either patching or not patching could result in second-guessing by someone who feels he can construe the policy to match the result he prefers. 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] compilig libpq with borland 5.5
I made tests compiling both sources (from CVS repository and from HTTP), and i got the next results: 1-) MSVS 2005 *Source from CVS repository: *fatal error U1073: Don't know how to make 'libpq-dist.rc' *Source from http (ver. 8.3.3): **Successfully generated* 2.) Borland 5.5.1 * Source from CVS repository:* Error E2451 ..\..\port\dirent.c 35: Undefined symbol 'INVALID_FILE_ATTRIBUTES' in function opendir Error E2451 ..\..\port\dirent.c 52: Undefined symbol 'dirname' in function opendir Error E2451 ..\..\port\dirent.c 53: Undefined symbol 'dirname' in function opendir Error E2451 ..\..\port\dirent.c 59: Undefined symbol 'dirname' in function opendir Error E2451 ..\..\port\dirent.c 60: Undefined symbol 'dirname' in function opendir Error E2451 ..\..\port\dirent.c 60: Undefined symbol 'dirname' in function opendir Error E2451 ..\..\port\dirent.c 61: Undefined symbol 'dirname' in function opendir Error E2451 ..\..\port\dirent.c 61: Undefined symbol 'dirname' in function opendir Error E2451 ..\..\port\dirent.c 62: Undefined symbol 'dirname' in function opendir Error E2451 ..\..\port\dirent.c 64: Undefined symbol 'dirname' in function opendir Error E2451 ..\..\port\dirent.c 65: Undefined symbol 'handle' in function opendir Error E2451 ..\..\port\dirent.c 66: Undefined symbol 'ret' in function opendir Error E2451 ..\..\port\dirent.c 67: Undefined symbol 'ret' in function opendir Error E2451 ..\..\port\dirent.c 77: Undefined symbol 'handle' in function readdir Error E2451 ..\..\port\dirent.c 79: Undefined symbol 'handle' in function readdir Error E2451 ..\..\port\dirent.c 79: Undefined symbol 'dirname' in function readdir Error E2451 ..\..\port\dirent.c 80: Undefined symbol 'handle' in function readdir Error E2451 ..\..\port\dirent.c 88: Undefined symbol 'handle' in function readdir Error E2451 ..\..\port\dirent.c 100: Undefined symbol 'ret' in function readdir Error E2451 ..\..\port\dirent.c 102: Undefined symbol 'ret' in function readdir Error E2451 ..\..\port\dirent.c 102: Undefined symbol 'ret' in function readdir Error E2451 ..\..\port\dirent.c 103: Undefined symbol 'ret' in function readdir Error E2451 ..\..\port\dirent.c 109: Undefined symbol 'handle' in function closedir Error E2451 ..\..\port\dirent.c 110: Undefined symbol 'handle' in function closedir Error E2451 ..\..\port\dirent.c 111: Undefined symbol 'dirname' in function closedir Error E2228 ..\..\port\dirent.c 111: Too many error or warning messages in function closedir *** 26 errors in Compile *** ** error 1 ** deleting .\Release\dirent.obj *Source from http (ver. 8.3.3):* Warning: 'win32' not found in library Warning: 'getaddrinfo' not found in library Warning: 'pgstrcasecmp' not found in library Warning: 'thread' not found in library Warning: 'inet_aton' not found in library Warning: 'crypt' not found in library Warning: 'noblock' not found in library Warning: 'md5' not found in library Warning: 'ip' not found in library Warning: 'fe-auth' not found in library Warning: 'fe-protocol2' not found in library Warning: 'fe-protocol3' not found in library Warning: 'fe-connect' not found in library Warning: 'fe-exec' not found in library Warning: 'fe-lobj' not found in library Warning: 'fe-misc' not found in library Warning: public '_pqFlush' in module 'fe-misc' clashes with prior module 'fe-exec' Warning: 'fe-print' not found in library Warning: 'fe-secure' not found in library Warning: 'pqexpbuffer' not found in library Warning: 'pqsignal' not found in library Warning: 'wchar' not found in library Warning: 'encnames' not found in library Warning: 'snprintf' not found in library Warning: 'strlcpy' not found in library Warning: 'pthread-win32' not found in library Warning: '' not found in library Warning: '.OBJ' file not found brcc32.exe -l 0x409 -iC:\Borland\BCC55\include -fo.\Release\libpq.res libpq.rc Borland Resource Compiler Version 5.40 Copyright (c) 1990, 1999 Inprise Corporation. All rights reserved. ilink32.exe @MAKE.@@@ Turbo Incremental Link 5.00 Copyright (c) 1997, 2000 Borland Error: Unresolved external '_pgwin32_safestat' referenced from C:\SOURCE POSTGRES 8.3\SRC\INTERFACES\LIBPQ\REL EASE\BLIBPQ.LIB|fe-connect ** error 2 ** deleting .\Release\blibpq.dll He would welcome any suggestions or help Claudio Lezcano
Re: [HACKERS] Patch: plan invalidation vs stored procedures
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Joshua Drake wrote: Is our backpatch policy documented? It does not appear to be in developer FAQ. Seems we need to add it. I'm not sure that I *want* a formal written-down backpatch policy. Whether (and how far) to backpatch has always been a best-judgment call in the past, and we've gotten along fine with that. I think having a formal policy is just likely to lead to even more complaints: either patching or not patching could result in second-guessing by someone who feels he can construe the policy to match the result he prefers. OK, agreed. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
Asko Oja escribió: Another thing I do not understand well is how people are expected to work in 8.3 with a function based API, without hitting Skype problems. People are expected to use same workarounds as Skype is using. For us another unneccessary downtime week ago was what set us moving/thinking :). When you use software with limitations then you learn to live with them. Good thing about postgres you can do something yourself to get some of the limitations removed. Make sure you do not live with patches forever, i.e. that it gets into 8.4. Otherwise it's going to be a pain for everyone. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A smaller default postgresql.conf
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 I'm really not in favor of having comments in the conf file that try to tell you about stuff you might want to set, much less why. That task properly belongs to some kind of introductory chapter in the SGML docs. Novice DBAs are unlikely even to *find* the config file, let alone look inside it, if there's not an introductory chapter telling them about Things They Ought To Do. Ugh, you are heading in the wrong direction. The configuration file should be well documented: moving the documentation further away from it is the wrong idea, especially if it means firing up a web browser to do so. As link is fine, and recommended, but a bare configuration file would be far, far worse than the mess we have today. I like Josh B's version a lot. It's not perfect (I'd add a URL for each config for example), but it's a great start. Text space is cheap, and having a consistent, well-documented, easy-to-read conf file is something worth shooting for. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200808191511 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkirGyEACgkQvJuQZxSWSsgsvwCdH6Hb4KOj47j/Zceb26FgEQUM J2gAoKE19rLhMpgP17EdJIuUVoKQ7H3u =//eH -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A smaller default postgresql.conf
Greg Sabino Mullane [EMAIL PROTECTED] writes: I like Josh B's version a lot. It's not perfect (I'd add a URL for each config for example), but it's a great start. Josh B's approach is great until people start making changes that are unrelated to (or perhaps even contradictory to) his comments. And then it's just a recipe for confusion. I would far rather see his text as part of the SGML docs. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A smaller default postgresql.conf
On Tue, 19 Aug 2008 19:12:47 - Greg Sabino Mullane [EMAIL PROTECTED] wrote: Ugh, you are heading in the wrong direction. The configuration file should be well documented: moving the documentation further away from it is the wrong idea, especially if it means firing up a web browser to do so. It is impossible to document the postgresql.conf file in a manner that is truly useful without firing up the reading material in the first place. Even with Josh's improvements there are too many variables and we are just going to have a bunch of people breaking stuff and then complaining that, well it was suggested in the postgresql.conf. a URL for each config for example), but it's a great start. Text space is cheap, and having a consistent, well-documented, easy-to-read conf file is something worth shooting for. I have yet to find one; anywhere. Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, Le 19 août 08 à 19:06, Tom Lane a écrit : Dimitri Fontaine [EMAIL PROTECTED] writes: Another thing I do not understand well is how people are expected to work in 8.3 with a function based API, without hitting Skype problems. What we've got at this point is a submitted patch for a new feature that hasn't even been accepted into HEAD yet. Lobbying to get it back-patched is entirely inappropriate IMHO. Well, there's a misunderstanding here. I certainly were lobbying for considering a backpatch as I saw it as a bugfix. You told me it's a new feature, I say ok for not backpatching, obviously. This mail was a real attempt at learning some tips to be able to push the functions usage as far as Skype is doing, in 8.3 release, and avoiding the trap which has always existed in released PostgreSQL version. This certainly was a bad attempt at it. Now, my understanding is that rolling out new versions of functions requires forcing dropping all current opened sessions as soon as PostgreSQL considers you need to drop any function. I'll think about it in next project design meetings. Regards, - -- dim -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (Darwin) iEYEARECAAYFAkirHlEACgkQlBXRlnbh1bk4YQCgswDS1bu+P+N7yKJvwnRAWnL3 FYkAnRZQzqbEoahShh/Qz9mnrIm1e99y =hIBt -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
Le 19 août 08 à 20:47, Tom Lane a écrit : I'm not sure that I *want* a formal written-down backpatch policy. Whether (and how far) to backpatch has always been a best-judgment call in the past, and we've gotten along fine with that. I think having a formal policy is just likely to lead to even more complaints: either patching or not patching could result in second-guessing by someone who feels he can construe the policy to match the result he prefers. Agreed. The problem here (at least for me) was to understand why this (yet to be reviewed) patch is about implementing a new feature and not about bugfixing an existing one. So we're exactly in the fog around the informal backpatch policy, and as long as we're able to continue talking nicely about it, this seems the finest solution :) Keep up the amazing work, regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A smaller default postgresql.conf
On Tuesday 19 August 2008 22:12:47 Greg Sabino Mullane wrote: moving the documentation further away from it is the wrong idea, especially if it means firing up a web browser to do so. I can see that argument, but I think we can quite simply solve it if we provide a plain-text version of the configuration chapter of the documentation. You can easily grep that in a second window and don't have to be in-your-face to users who just want to edit the settings. Text space is cheap, I'd offer the alternative theory that anything that is longer than one screen is overwhelming and unwieldy. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A smaller default postgresql.conf
I'm really not in favor of having comments in the conf file that try to tell you about stuff you might want to set, much less why. That task properly belongs to some kind of introductory chapter in the SGML docs. Novice DBAs are unlikely even to *find* the config file, let alone look inside it, if there's not an introductory chapter telling them about Things They Ought To Do. +1. When I have a question about something PostgreSQL-related, the first think I do is Read The Fine Manual. The PostgreSQL documentation is excellent, and one of the highlights of the project IMO. I've read through the postgresql.conf file occasionally, but that's a really difficult way to try to understand the subject. I'd much rather read through that file in a web browser than a shell window - but the real advantage of putting it in the documentation is that you can not only document each specific setting, but also give a broad overview of relevant topics. A section on Performance Tuning in Section III: Server Administration would be really great. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
On Tue, 19 Aug 2008 14:47:13 -0400 Tom Lane [EMAIL PROTECTED] wrote: Bruce Momjian [EMAIL PROTECTED] writes: Joshua Drake wrote: Is our backpatch policy documented? It does not appear to be in developer FAQ. Seems we need to add it. I'm not sure that I *want* a formal written-down backpatch policy. Then we write a formal guideline. It really isn't fair to new developers to not have any idea how they are going to be able to get a patch applied to older branches. Something like: Generally speaking we adhere to the following guideline for patches. * Security fixes are applied to all applicable branches. * Bugfixes are applied to all applicable branches * Note: A patch that addresses a known limitation is generally not backpatched * New features are always applied to -HEAD only. This is not a policy as much as a legend for developers to consider before they submit their patch. If we do this, we have the opportunity to just point to the FAQ when there is no ambiguity. It also increases transparency of the process; which is always a good thing. Sincerely, Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A smaller default postgresql.conf
Peter Eisentraut wrote: On Tuesday 19 August 2008 22:12:47 Greg Sabino Mullane wrote: moving the documentation further away from it is the wrong idea, especially if it means firing up a web browser to do so. I can see that argument, but I think we can quite simply solve it if we provide a plain-text version of the configuration chapter of the documentation. You can easily grep that in a second window and don't have to be in-your-face to users who just want to edit the settings. Hmm, let me suggest providing it as a manpage for postgresql.conf, i.e., you run man postgresql.conf and it gives you this manpage documenting every option. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
On Tue, Aug 19, 2008 at 02:47:13PM -0400, Tom Lane wrote: Whether (and how far) to backpatch has always been a best-judgment call in the past, and we've gotten along fine with that. I think having a formal policy is just likely to lead to even more complaints: I completely agree with this. If you formalise the back-patch policy, then it will be necessary to invent classifications for bug severity to determine whether to back patch. This will inevitably lead to some sort of false objectivity measure, where bugs get a severity number that actually just means we have already decided to back-patch. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A smaller default postgresql.conf
On Tue, 19 Aug 2008 15:43:11 -0400 Alvaro Herrera [EMAIL PROTECTED] wrote: Peter Eisentraut wrote: On Tuesday 19 August 2008 22:12:47 Greg Sabino Mullane wrote: moving the documentation further away from it is the wrong idea, especially if it means firing up a web browser to do so. I can see that argument, but I think we can quite simply solve it if we provide a plain-text version of the configuration chapter of the documentation. You can easily grep that in a second window and don't have to be in-your-face to users who just want to edit the settings. Hmm, let me suggest providing it as a manpage for postgresql.conf, i.e., you run man postgresql.conf and it gives you this manpage documenting every option. and native windows help (egad) but yes that would be good. Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
Another thing I do not understand well is how people are expected to work in 8.3 with a function based API, without hitting Skype problems. I'm having a All database-driven applications have this problem. Any time you have a database on the backend and interface code on the front-end, you need to keep in mind that it won't necessarily be possible to update the two of them simultaneously, especially if you have multiple back-ends and multiple front-ends, as you almost certainly do. Even if PostgreSQL invalidated plans in the particular situation you're discussing, there would still be other problems. You could add a new, non-NULLable column to a table before updating the code that insert into that table, or drop an old column that the code still counts on being able to access. I handle these problems all the time by ordering the changes carefully. If I need to change a function API in an incompatible way, I change the NAME of the function as well as the type signature (eg. do_important_thing - do_important_thing_v2). Then I change the code. Then I remove the old function once everything that relies on it is dead. Maybe in your particular environment plan invalidation for functions will solve most of the cases you care about, but I respectfully submit that there's no substitute for good release engineering. If you don't know exactly what functions are going to be created, modified, or dropped on your production servers during each release before you actually roll that release out... you probably need to improve your internal documentation. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A smaller default postgresql.conf
Peter Eisentraut wrote: On Tuesday 19 August 2008 22:12:47 Greg Sabino Mullane wrote: Text space is cheap, I'd offer the alternative theory that anything that is longer than one screen is overwhelming and unwieldy. One more benefit of a small file is that it makes it easier to ask someone please attach a copy of your postgresql.conf file; rather than please send the output of grep -v '^[]*#' postgresql.conf | grep = or worse Can you recall what you changed? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, Le 19 août 08 à 22:03, Robert Haas a écrit : All database-driven applications have this problem. Any time you have a database on the backend and interface code on the front-end, you need to keep in mind that it won't necessarily be possible to update the two of them simultaneously, especially if you have multiple back-ends and multiple front-ends, as you almost certainly do. Even if PostgreSQL invalidated plans in the particular situation you're discussing, there would still be other problems. You could add a new, non-NULLable column to a table before updating the code that insert into that table, or drop an old column that the code still counts on being able to access. Using functions the way Skype uses them means not issuing a single insert, update or delete directly from your code, but calling a function which takes care about it. So you use PostgreSQL transactionnal DDL to roll-out new function versions at the same time you push the schema modifications, and commit it all in one go. Maybe in your particular environment plan invalidation for functions will solve most of the cases you care about When the code only is a client to an SQL functions API, which effectively replaces SQL as the way to interact between code and database, then I believe plan invalidation at function change is the missing piece. , but I respectfully submit that there's no substitute for good release engineering. If you don't know exactly what functions are going to be created, modified, or dropped on your production servers during each release before you actually roll that release out... you probably need to improve your internal documentation. Agreed :) - -- dim -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (Darwin) iEYEARECAAYFAkirK2kACgkQlBXRlnbh1bmxvQCgmowpfnZ5nFRml0mNfj2HRE+3 HJEAnR3G6Lhnb7R4+iSze8xGACwyk4D7 =of1o -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
On Tue, Aug 19, 2008 at 12:42:29PM -0700, Joshua Drake wrote: Generally speaking we adhere to the following guideline for patches. * Security fixes are applied to all applicable branches. * Bugfixes are applied to all applicable branches * Note: A patch that addresses a known limitation is generally not backpatched * New features are always applied to -HEAD only. This is not a policy as much as a legend for developers to consider before they submit their patch. But it's meaningless. Bugfixes are applied to all applicable branches, is either false or trivially true. It's trivially true if you interpret applicable branches to mean the ones that get the patch. It's false if you mean bugfix to mean every patch that fixes a bug. I can think of bugs that we have lived with in older releases because fixing them was too risky or because the bug was so tiny or unusual as to make the risk greater than the reward. A formal policy that's any more detailed than what's in the FAQ today is a solution in search of a problem. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
On Tue, 2008-08-19 at 21:26 +0200, Dimitri Fontaine wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, Le 19 août 08 à 19:06, Tom Lane a écrit : Dimitri Fontaine [EMAIL PROTECTED] writes: Another thing I do not understand well is how people are expected to work in 8.3 with a function based API, without hitting Skype problems. What we've got at this point is a submitted patch for a new feature that hasn't even been accepted into HEAD yet. Lobbying to get it back-patched is entirely inappropriate IMHO. Well, there's a misunderstanding here. I certainly were lobbying for considering a backpatch as I saw it as a bugfix. You told me it's a new feature, I say ok for not backpatching, obviously. This mail was a real attempt at learning some tips to be able to push the functions usage as far as Skype is doing, in 8.3 release, and avoiding the trap which has always existed in released PostgreSQL version. This certainly was a bad attempt at it. Now, my understanding is that rolling out new versions of functions requires forcing dropping all current opened sessions as soon as PostgreSQL considers you need to drop any function. I'll think about it in next project design meetings. I think that another option is to manipulate pg_proc - just do a no-op update to advance xmin for all functions that may have cached plans. UPDATE pg_proc SET proname = proname; then make sure that pg_proc is vacuumed often enough. It's a bit wasteful, as it forces re-planning of all functions, but should have similar effect than the patch. It's also possible that updating pg_proc in bulk introduces some race conditions which lock up the database. -- 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] Patch: plan invalidation vs stored procedures
On Tue, 19 Aug 2008 16:22:43 -0400 Andrew Sullivan [EMAIL PROTECTED] wrote: A formal policy that's any more detailed than what's in the FAQ today is a solution in search of a problem. Odd that the problem continues to rear its head though isn't it? This certainly isn't the first time it has come up. I have however made my argument. I also tried to help solve the problem. If we aren't interested in a solution, oh well. It doesn't make my life any harder. Sincerely, Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
Dimitri Fontaine escribió: The problem here (at least for me) was to understand why this (yet to be reviewed) patch is about implementing a new feature and not about bugfixing an existing one. So we're exactly in the fog around the informal backpatch policy, and as long as we're able to continue talking nicely about it, this seems the finest solution :) The actual criterion is not really new user-visible feature versus bug fix. It's more an attempt at measuring how large a potential impact the change has. The patch I saw was introducing a whole new message type to go through the shared invalidation queue, which is not something to be taken lightly (consider that there are three message types of messages currently.) It's possible that for the Skype usage this patch introduces the behavior they want. But for other people, perhaps this kind of invalidation causes secondary effects that are completely unforeseen -- what if it breaks their apps and they must carry out a week's work to fix it? What if a serious security problem is discovered tomorrow and they can't update because we've broken backwards compatibility for them? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
Alvaro Herrera [EMAIL PROTECTED] writes: The actual criterion is not really new user-visible feature versus bug fix. It's more an attempt at measuring how large a potential impact the change has. The patch I saw was introducing a whole new message type to go through the shared invalidation queue, which is not something to be taken lightly (consider that there are three message types of messages currently.) I hadn't read it yet, but that makes it wrong already. There's no need for any new inval traffic --- the existing syscache inval messages on pg_proc entries should serve fine. More generally, if we are to try to invalidate on the strength of pg_proc changes, what of other DDL changes? Operators, operator classes, maybe? How about renaming a schema? I would like to see a line drawn between things we find worth trying to track and things we don't. If there is no such line, we're going to need a patch a lot larger than this one. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
On Tue, 2008-08-19 at 16:03 -0400, Robert Haas wrote: Another thing I do not understand well is how people are expected to work in 8.3 with a function based API, without hitting Skype problems. I'm having a All database-driven applications have this problem. Any time you have a database on the backend and interface code on the front-end, you need to keep in mind that it won't necessarily be possible to update the two of them simultaneously, especially if you have multiple back-ends and multiple front-ends, as you almost certainly do. Even if PostgreSQL invalidated plans in the particular situation you're discussing, there would still be other problems. You could add a new, non-NULLable column to a table before updating the code that insert into that table, or drop an old column that the code still counts on being able to access. I handle these problems all the time by ordering the changes carefully. If I need to change a function API in an incompatible way, I change the NAME of the function as well as the type signature (eg. do_important_thing - do_important_thing_v2). Then I change the code. Then I remove the old function once everything that relies on it is dead. Not having plan invalidation forces you to have do_important_thing_v2 for do_important_thing even with no changes in source code, just for the fact that do_part_of_important_thing() which it calls has changed. An example - you have functions A) caller1() to callerN() which includes call to called1() B) one of these functions, say callerM() needs one more field returned from called1(), so you either write a completely new function called1_v2() with one more field and then update callerM() to call called1_v2() C) now, to get rid of called1() you have to replace called1 with called1_v2 also in all other functions caller1() to callerN() D) then you can drop called1() if you missed one of callerx() functions (you can drop called1() even if it is used, as postgreSQL does not check dependencies in functions) then you have a non-functioning database, where even client reconnect won't help, only putting called1() back. If there is plan invalidation then you just change called1() to return one more field and that's it - no juggling with C) and D) and generally less things that can go wrong. Maybe in your particular environment plan invalidation for functions will solve most of the cases you care about, but I respectfully submit that there's no substitute for good release engineering. Nope, but the amount of release engineering (and deployment-time work) you need to do depends a lot on fragility of the system. The more arcane and fragile the system is, the more you need to rely on external systems and procedures to keep it working. Imagine how much harder it would be, if there were no transactions and you had to ensure the right ordering of all changes by release process only. You probably would end up doing several times more work and temporary hacks and you would still be out of luck doing _any_ nontrivial updates while the systems are running 24/7. If you don't know exactly what functions are going to be created, modified, or dropped on your production servers during each release before you actually roll that release out... this is not about knowing this at all - this is about needing to change less, about optimizing on work that does not need to be done if system is smarter. you probably need to improve your internal documentation. or improve the database system you use. if you need to change less functions, you also need less documentation about changes. if you can prove that select a,b from f() always returns the same data as select a,b from f_b2() then you don't need to write f_b2() at all, you just redefine f() and can also skip migrating all callers of f() to f_v2() just to satisfy your databases quirks. --- 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] A smaller default postgresql.conf
Alvaro Herrera [EMAIL PROTECTED] writes: Peter Eisentraut wrote: I can see that argument, but I think we can quite simply solve it if we provide a plain-text version of the configuration chapter of the documentation. Hmm, let me suggest providing it as a manpage for postgresql.conf, i.e., you run man postgresql.conf and it gives you this manpage documenting every option. Seems a bit Unix-centric, but +1 for it on Unix machines anyway. Is there any near equivalent on Windows? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A smaller default postgresql.conf
On Tue, 19 Aug 2008 17:03:48 -0400 Tom Lane [EMAIL PROTECTED] wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Peter Eisentraut wrote: I can see that argument, but I think we can quite simply solve it if we provide a plain-text version of the configuration chapter of the documentation. Hmm, let me suggest providing it as a manpage for postgresql.conf, i.e., you run man postgresql.conf and it gives you this manpage documenting every option. Seems a bit Unix-centric, but +1 for it on Unix machines anyway. Is there any near equivalent on Windows? Yes there are Windows Help files. I would imagine the installer would deal with that. Magnus would obviously know better than I. Sincerely, Joshua D. Drake regards, tom lane -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
On Tue, 2008-08-19 at 16:56 -0400, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: The actual criterion is not really new user-visible feature versus bug fix. It's more an attempt at measuring how large a potential impact the change has. The patch I saw was introducing a whole new message type to go through the shared invalidation queue, which is not something to be taken lightly (consider that there are three message types of messages currently.) I hadn't read it yet, but that makes it wrong already. There's no need for any new inval traffic --- the existing syscache inval messages on pg_proc entries should serve fine. More generally, if we are to try to invalidate on the strength of pg_proc changes, what of other DDL changes? Operators, operator classes, maybe? How about renaming a schema? I would like to see a line drawn between things we find worth trying to track and things we don't. If there is no such line, we're going to need a patch a lot larger than this one. Or maybe a simpler and smaller patch - just invalidate everything on every schema change :) It will have a momentary impact on performance at DDL time, but otherways might be more robust and easier to check for errors. - 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] Patch: plan invalidation vs stored procedures
On Tue, 2008-08-19 at 16:56 -0400, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: The actual criterion is not really new user-visible feature versus bug fix. It's more an attempt at measuring how large a potential impact the change has. The patch I saw was introducing a whole new message type to go through the shared invalidation queue, which is not something to be taken lightly (consider that there are three message types of messages currently.) I hadn't read it yet, but that makes it wrong already. There's no need for any new inval traffic --- the existing syscache inval messages on pg_proc entries should serve fine. I have'nt looke at the patch either, but I suspect that what goes through shared mem is the registration for invalidation, as dependent function OIDs are only learned while compiling functions so when f_caller() learns that it caches plan f_called() then it registers through shared mem message its wish to invalidate this plan if f_called() is dropped or redefined. -- 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] A smaller default postgresql.conf
On Tue, Aug 19, 2008 at 10:03 PM, Tom Lane [EMAIL PROTECTED] wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Peter Eisentraut wrote: I can see that argument, but I think we can quite simply solve it if we provide a plain-text version of the configuration chapter of the documentation. Hmm, let me suggest providing it as a manpage for postgresql.conf, i.e., you run man postgresql.conf and it gives you this manpage documenting every option. Seems a bit Unix-centric, but +1 for it on Unix machines anyway. Is there any near equivalent on Windows? No. There are helpfiles (which consist of a navigation tree and a bunch of pages), but they're what we use for the main docs. There's nothing akin to a man page. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A smaller default postgresql.conf
On Tue, 19 Aug 2008, Josh Berkus wrote: Well, that doesn't help unless we either provide a .conf generation tool (something I favor) or docs somewhere which explain which are the variables to be the most concerned with instead of making users read through all 218 of them. The design for a pg_generate_conf tool you suggested that's now dumped into http://wiki.postgresql.org/wiki/GUCS_Overhaul#pg_generate_conf seemed the only reasonable solution I've ever heard here. The difference of opinion between those those want a tiny file and those who want a full one cannot be reconciled. It's not a logical debate, it's a religious one. The best you can do is provide something that's switchable to work for the most popular positions: * The file should be minimal * Every parameter should be there with lots of documentation * Just the important parameters should be listed Because no one who is firmly in one of those camps will ever move to another just by arguing here. I'm going to rewrite that Wiki page to make it more obvious how the proposed changes actually map to resolving problems in this area. Much of what's come up in this thread is already addressed there but that's clearly not obvious to most people. -- * 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] A smaller default postgresql.conf
Dave Page wrote: On Tue, Aug 19, 2008 at 10:03 PM, Tom Lane [EMAIL PROTECTED] wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Hmm, let me suggest providing it as a manpage for postgresql.conf, i.e., you run man postgresql.conf and it gives you this manpage documenting every option. Seems a bit Unix-centric, but +1 for it on Unix machines anyway. Is there any near equivalent on Windows? No. There are helpfiles (which consist of a navigation tree and a bunch of pages), but they're what we use for the main docs. There's nothing akin to a man page. Well, so we provide a reference to the help file and that's it. If there's a way to provide a link in the config file that would automatically open the appropriate help file on click, that would be perfect. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
you have functions A) caller1() to callerN() which includes call to called1() B) one of these functions, say callerM() needs one more field returned from called1(), so you either write a completely new function called1_v2() with one more field and then update callerM() to call called1_v2() C) now, to get rid of called1() you have to replace called1 with called1_v2 also in all other functions caller1() to callerN() D) then you can drop called1() True. I complained about this same problem in the context of views - you can add a column to a table in place but not to a view, or even a type created via CREATE TYPE. I even went so far as to develop a patch[1] to improve the situation, which to my sadness was not met with wild enthusiasm. [1] http://archives.postgresql.org/pgsql-hackers/2008-08/msg00272.php Does it help to do CREATE OR REPLACE FUNCTION on callerX() after dropping and recreating called1()? If so, you might want to just recreate all of your functions every time you do a release. I wrote a perl script that does this and it's worked pretty well for me. Besides possibly avoiding this problem, it means that I don't really need to worry about which functions I've modified in this release quite as much, since I'm just going to push out the most-current definition for all of them. Nope, but the amount of release engineering (and deployment-time work) you need to do depends a lot on fragility of the system. Also true, but I think comparing plan invalidation to transactional semantics is quite unfair. There's basically no amount of user code which will compensate for the lack of an ACID-compliant database. On the other hand, working around the lack of plan invalidation (or the inability to add columns to views without recreating them) just requires being careful to catch all of the stray references in your DDL and testing thoroughly before you roll out to production, which are good things to do anyway. That's not to say that we shouldn't have plan invalidation, just that I don't think it's anywhere close to the same level of broken. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A smaller default postgresql.conf
On Tue, Aug 19, 2008 at 07:12:47PM -, Greg Sabino Mullane wrote: I'm really not in favor of having comments in the conf file that try to tell you about stuff you might want to set, much less why. That task properly belongs to some kind of introductory chapter in the SGML docs. Novice DBAs are unlikely even to *find* the config file, let alone look inside it, if there's not an introductory chapter telling them about Things They Ought To Do. Ugh, you are heading in the wrong direction. The configuration file should be well documented: moving the documentation further away from it is the wrong idea, especially if it means firing up a web browser to do so. As link is fine, and recommended, but a bare configuration file would be far, far worse than the mess we have today. I like Josh B's version a lot. It's not perfect (I'd add a URL for each config for example), but it's a great start. Text space is cheap, and having a consistent, well-documented, easy-to-read conf file is something worth shooting for. How about a man page for postgresql.conf? We already ship very nice man pages for SQL commands. While we're at it, we could ship one for pg_hba.conf, too :) What do we do about man pages on Windows? Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] compilig libpq with borland 5.5
Hi. I made tests compiling both sources (from CVS repository and from HTTP), and i got the next results: 1-) MSVS 2005 *Source from CVS repository: *fatal error U1073: Don't know how to make 'libpq-dist.rc' *Source from http (ver. 8.3.3): **Successfully generated* It can be made from 'make distprep'. and def file is also made. However, I make it except windows environment. Regards, Hiroshi Saito -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers