Re: [HACKERS] [PATCH] pgbench: new feature allowing to launch shell commands
I see this in pgbench.c: /* return false iff client should be disconnected */ static bool doCustom(CState *st, instr_time *conn_time) I think you need to increase the verbosity of the error messages when you're working on this code, because when I compile I get a slew of these errors pointing to the problem too: pgbench.c:1009: warning: return with no value, in function returning non-void The fix is that when there's an error, you need to do this: return clientDone(st, false); Thanks for the tip. In fact I based my patch on postgres 8.4.1 and not on the head of the git repository. This explains why I did not go through the error messages returned by doCustom. The new version of the patch attached to this email has been made directly from the git repository. It looks definitely cleaner this time. I tried to clean the patch so as to pass the rest of the command line also, so as not to have to do it later. A short update of the setshell feature is also available on PostgreSQL's wiki at the page pgbench shell command. About the potential examples, I can also write a short script and put that on the wiki. As you said previously, the Pareto example is possible, but also why not thinking about other statistical distributions? a Gaussian or a Poisson distribution? There are many possibilities. Regards, -- Michael Paquier NTT OSSC pgbenchsetshell.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: String key space for advisory locks
Christophe Pettus x...@thebuild.com wrote:
Summary:Add a string key space to the advisory lock functionality.
Why aren't you satisfied with hashtext('foo') ?
The restriction comes from LOCKTAG struct, in which
we can use only 3 * uint32 and 1 * uint16 for lock descriptor.
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Tightening binary receive functions
Andrew Gierth wrote:
James == James Pye li...@jwp.name writes:
James Is the new date_recv() constraint actually correct?
No, it's not:
Oops, you're right. The check is indeed confusing julian day numbers,
with epoch at 23th of Nov 4714 BC, with postgres-reckoning day numbers,
with epoch at 1th of Jan 2000. Thanks, will fix.
BTW, I just noticed that to_date() doesn't respect those limits either:
postgres=# create table x (a date);
CREATE TABLE
postgres=# insert into x values (to_date('-4713 11 23', ' MM DD'));
INSERT 0 1
postgres=# select * from x;
a
---
4714-11-23 BC
(1 row)
postgres=# copy x to '/tmp/tst.dmp'; -- text mode
COPY 1
postgres=# copy x from '/tmp/tst.dmp';
ERROR: date out of range: 4714-11-23 BC
CONTEXT: COPY x, line 1, column a: 4714-11-23 BC
The date arithmetic operators + and - also allow you to create such
dates. I also note that they don't check for overflow.
I'm thinking that we should fix all that by adding range checks to all
those functions (or maybe just in date2j() and the operators).
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] License clarification: BSD vs MIT
2009/10/26 David Fetter da...@fetter.org: Not being any kind of attorney, and assuming the Red Hat lawyers are pretty much on our side, They're not really. They're just interested in doing things the right way for Redhat users (which is fine - that's what they're paid for). I'll just say we're more MIT-like, or 2-clause BSD if the former causes confusion. Thanks! :) I've also spoken to a lawyer about this, and he concurred that our licence is more MIT-like in the way that its worded. It has roughly the same requirements as the simplified BSD though - but then so do a bunch of other OSI approved licences. As Tom says though, the effect this has on users is zero. The licence is still the same as its always been, regardless of what we say it is based on or looks like. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PGDay.EU 2009 Conference: http://2009.pgday.eu/start -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 8.4.1 strange GiST (btree_gist?) messages + index row size error (possible BUG, test case + test data)
Hi, All I faced this odd situation when I was migrating my data from 8.3.7 to 8.4.1. After setting up instance, applying schema dump w/o and indexes and loading data I was trying to create this indexes and got a lot of multiply messages picksplit method for column 2 of index ... doesn't support secondary split after each gist index containing 2+ columns. Finally I got this message index row requires 10440 bytes, maximum size is 8191 after creation of one complex index. Test-case: 1. Install PG 8.4.1 + btree_gist, set log_statement = 'all', create test_db 2. Create test table CREATE TABLE test_table ( obj_id bigint NOT NULL, obj_status_did smallint NOT NULL DEFAULT 5, obj_created timestamp with time zone NOT NULL DEFAULT now(), obj_main_pic_obj_id bigint, obj_tsvector tsvector NOT NULL DEFAULT ''::tsvector, person_photo_is_best boolean NOT NULL DEFAULT false, person_vislvl smallint NOT NULL DEFAULT 9, CONSTRAINT pk_test_table PRIMARY KEY (obj_id) ); 3. Load a data into the table Data dump is here http://drop.io/rdccygi (it was created with COPY (SELECT...) TO '...' using psql from 8.3.7) COPY test_table FROM '/tmp/data.dump'; 4. Try to create this indexes CREATE INDEX i_test_table__created_tsvector ON test_table USING gist (obj_created, obj_tsvector) WHERE obj_status_did = 1; CREATE INDEX i_test_table__tsvector_vislvl_by_photo_created ON test_table USING btree (obj_tsvector, person_vislvl, (COALESCE(person_photo_is_best::integer, 0)) DESC, sign(COALESCE(obj_main_pic_obj_id, 0::bigint)::double precision) DESC, obj_created DESC) WHERE obj_status_did = 1; And you will see something like this http://drop.io/5tla8sg p.s. One thing I have forgotten to write - I tried it on Ubuntu 9.04, PG was built from sources. -- Regards, Sergey Konoplev -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Endgame for all those SELECT FOR UPDATE changes: fix plan node order
Tom Lane escribió: Robert Haas robertmh...@gmail.com writes: This seems like it could potentially introduce a performance regression, but the current behavior is so bizarre that it seems like we should still change it. Yeah, it could definitely run slower than the existing code --- in particular the combination of all three (FOR UPDATE ORDER BY LIMIT) would tend to become a seqscan-and-sort rather than possibly just reading one end of an index. However, I quote the old aphorism that it can be made indefinitely fast if it doesn't have to give the right answer. The reason the current behavior is fast is it's giving the wrong answer :-( So this probably merits a warning in the release notes for people to check that their queries continue to run with the performance they expect. -- 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] License clarification: BSD vs MIT
On Sun, 2009-10-25 at 22:48 -0400, Tom Lane wrote: Otherwise I'm not sure it matters. If that were true, why did Red Hat lawyers do this? ISTM we should apply to OSI for approval of our licence, so we can then refer to it as the PostgreSQL licence. That then avoids any situation that might allow someone to claim some injunctive relief of part of the licence because of it being widely misdescribed. -- Simon Riggs www.2ndQuadrant.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] Parsing config files in a directory
On Mon, Oct 26, 2009 at 12:46 AM, Josh Berkus j...@agliodbs.com wrote: On 10/25/09 5:33 PM, Robert Haas wrote: Greg believes that it isn't politically feasible to change the default postgresql.conf, now or perhaps ever. I notice that he didn't say that he thinks it's a bad idea. So he has come up with an alternate plan which he believes is the best one possible considering that limitation. I agree with Greg. I would love to dump the current stupid long postgresql.conf, but I've lost that argument every time I've had it. We have to work around it. Do you have a pointer to the archives? ...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] Proposal: String key space for advisory locks
Christophe Pettus wrote: API Changes: Overloading the various advisory lock functions to take a suitable string type (varchar(64)?) in addition to the bigint / 2 x int variations. As with the bigint / 2 x int forms, this string namespace would be disjoint from the other key spaces. I don't think this can be made to work. The locktag hash element has a fixed size. Perhaps you could make it work if you hashed the string and used that as a locktag, but it would lock too much as soon as two strings had matching hashes. -- 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] Parsing config files in a directory
On Mon, Oct 26, 2009 at 12:18 AM, Greg Smith gsm...@gregsmith.com wrote: On Sun, 25 Oct 2009, Robert Haas wrote: I especially don't believe that it will ever support SET PERSISTENT, which I believe to be a feature a lot of people want. It actually makes it completely trivial to implement. SET PERSISTENT can now write all the changes out to a new file in the include directory. Just ship the database with a persistent.conf in there that looks like this: This only sorta works. If the changes are written out to a file that is processed after postgresql.conf (or some other file that contains values for those variables), then someone who edits postgresql.conf (or some other file) by hand will think they have changed a setting when they really haven't. On the flip side, there could also be still other files that are processed afterwards, in which case SET PERSISTENT would appear to work but not actually do anything. ...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] License clarification: BSD vs MIT
On Mon, Oct 26, 2009 at 1:08 PM, Simon Riggs si...@2ndquadrant.com wrote: On Sun, 2009-10-25 at 22:48 -0400, Tom Lane wrote: Otherwise I'm not sure it matters. If that were true, why did Red Hat lawyers do this? Because they categorise licences to help their users. It's just a label. ISTM we should apply to OSI for approval of our licence, so we can then refer to it as the PostgreSQL licence. That then avoids any situation that might allow someone to claim some injunctive relief of part of the licence because of it being widely misdescribed. Already in hand. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PGDay.EU 2009 Conference: http://2009.pgday.eu/start -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] table corrupted
On Thu, Oct 22, 2009 at 7:16 PM, Joshua D. Drake j...@commandprompt.comwrote: On Thu, 2009-10-22 at 14:28 -0200, João Eugenio Marynowski wrote: Hi Repair? Not likely. Get past? Maybe. I don't know how valuable your data is, but I've performed data recovery on tens of PG databases suffering from both hardware and software corruption on versions 7.0 through 8.3. My rate is $300-600 USD/hour depending on the database/table size and the extent of the corruption. If you're just trying to save what's not corrupted, there's quite a few examples online. -- Jonah H. Harris
Re: [HACKERS] Scaling up deferred unique checks and the after trigger queue
2009/10/25 Simon Riggs si...@2ndquadrant.com: On Mon, 2009-10-19 at 17:48 +0100, Dean Rasheed wrote: This is a WIP patch to replace the after-trigger queues with TID bitmaps to prevent them from using excessive amounts of memory. Each round of trigger executions is a modified bitmap heap scan. This is an interesting patch. The justification is fine, the idea is good, though I'd like to see more analysis of the technique, what other options exist and some thought about when we should use the technique. We have a bitmap for each UPDATE statement, I think, but there's no docs or readme. Why just UPDATE? Is the cost of starting up the bitmap higher than the existing mechanism? Do we need to look at starting with an existing mechanism and then switching over to new mechanism? Is the TID bitmap always a win for large numbers of rows? Thanks for looking at this. It works for all kinds of trigger events, and is intended as a complete drop-in replacement for the after triggers queue. I admit that I haven't yet done very much performance testing. As it stands, there does appear to be a small performance penalty associated with the bitmaps, but I need to do more testing to be more specific about that. I had thought that, for relatively small numbers of rows, I could use something like a small list of CTID arrays of increasing size, and then switch over to the new mechanism when this becomes too large. But first, I wanted to get feedback on whether this TID bitmap approach is actually valid for general trigger operation. The technique relies on these assumptions * Trigger functions are idempotent I don't understand what you're saying here. It should execute the triggers in exactly the same way as the current code (but possibly in a different order). Idempotentence isn't required. * Trigger execution order is not important (in terms of rows) It is true that the order in which the rows are processed will change. As far as I can tell from the spec, there is nothing to say that the rows for a given statement should be processed in any particular order. I guess that I'm looking for feedback from people on this list as to whether that will be a problem for existing apps. * Multiple trigger execution order is not important This patch does not change the order of execution in the case where there are multiple triggers (at least not for regular non-constraint triggers). They should still be fired in name order, for each row. All such triggers share a single TID bitmap, and are processed together. This is in line with the spec. Deferrable constraint triggers are a different matter, and these will be fired in a different order (each set of triggers for a given constraint will be fired together, rather than being interleaved). This is not covered by the spec, but if they are genuinely being used to enforce constraints, the order shouldn't matter. All of those seem false in the general case. What will you do? At this point I'm looking for more feedback as to whether any of this is a show-stopper, before I expend more effort on this patch. - Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Scaling up deferred unique checks and the after trigger queue
2009/10/25 Jeff Davis pg...@j-davis.com: On Mon, 2009-10-19 at 17:48 +0100, Dean Rasheed wrote: This is a WIP patch to replace the after-trigger queues with TID bitmaps to prevent them from using excessive amounts of memory. Each round of trigger executions is a modified bitmap heap scan. Can you please take a look at my patch here: http://archives.postgresql.org/message-id/1256499249.12775.20.ca...@jdavis to make sure that we're not interfering with eachother? I implemented deferred constraint checking in my operator exclusion constraints patch (formerly generalized index constraints). Yes, I've been following this, and I'm looking forward to this new functionality. After looking very briefly at your approach, I think that it's entirely orthogonal, so I don't expect a problem. I agree. I think that the 2 are orthogonal. Possibly they could both share some common bulk checking code, but I've not thought much about how to do that yet. I have a git repo here: http://git.postgresql.org/gitweb?p=users/jdavis/postgres.git;a=shortlog;h=refs/heads/operator-exclusion-constraints which may be helpful if you just want to look at the commit for deferred constraint checking. Any comments welcome. I did a quick bit of testing, and I think that there is a locking/concurrency problem :-( Attached is a (rather crappy) python script (using PyGreSQL) that I used to test consistency while I was working on the deferrable uniqueness constraints patch. Basically it just spawns a bunch of threads, each of which does random CRUD, with heavy contention and lots of constraint violations and deadlocks, which are rolled back. I modified the script to enforce uniqueness with an exclusion constraint, and the script is able to break the constraint, forcing invalid data into the table. I haven't looked at your code in depth, but I hope that this is not a difficult problem to fix. It seems like it ought to be similar to the btree code. - Dean #!/usr/bin/python import sys, pg, threading, random, time, datetime num_threads = 10 num_loops = 1000 lock = threading.RLock() total_nontrans = 0 total_commits = 0 total_rollbacks = 0 total_inserts = 0 total_updates = 0 total_deletes = 0 total_violations = 0 total_deadlocks = 0 total_unknown_errors = 0 total_errors = 0 total_duplicates = 0 def open(): return pg.DB(pgdevel, localhost, -1, -c client_min_messages=WARNING, None, pgdevel, ) def find_duplicates(db): result = db.query(SELECT max(c)-1 AS dups FROM +\ (SELECT count(*) AS c FROM foo GROUP BY a) AS foo) result = result.dictresult()[0][dups] if result == None: return 0 return result def setup(): db = open() db.query(DROP TABLE IF EXISTS foo) #db.query(CREATE TABLE foo(a int UNIQUE)) #db.query(CREATE TABLE foo(a int UNIQUE DEFERRABLE INITIALLY DEFERRED)) db.query(CREATE TABLE foo(a int)) db.query(ALTER TABLE foo ADD CONSTRAINT foo_u EXCLUSION+\ USING btree (a CHECK WITH =) DEFERRABLE INITIALLY DEFERRED) db.close() def do_crud(db): global total_nontrans, total_commits, total_rollbacks global total_inserts, total_updates, total_deletes global total_violations, total_deadlocks, total_unknown_errors global total_errors, total_duplicates inserts = 0 updates = 0 deletes = 0 do_trans = random.random() 0.2 do_commit = random.random() 0.2 do_loop = True duplicates = find_duplicates(db) lock.acquire() total_duplicates += duplicates if duplicates 0: print 1 FOUND DUPLICATES lock.release() if total_duplicates 0: sys.exit(1) try: if do_trans: db.query(BEGIN) while do_loop: if random.random() 0.5: val = int(random.random()*100) db.query(INSERT INTO foo VALUES(+str(val)+)) inserts += 1 if random.random() 0.5: val1 = int(random.random()*100) val2 = int(random.random()*100) db.query(UPDATE foo SET a=+str(val2)+ WHERE a=+str(val1)) updates += 1 if random.random() 0.5: val = int(random.random()*100) db.query(DELETE FROM foo WHERE a=+str(val)) deletes += 1 if random.random() 0.5: do_loop = False if do_trans: if do_commit: db.query(COMMIT) else: db.query(ROLLBACK) inserts = 0 updates = 0 deletes = 0 duplicates = find_duplicates(db) lock.acquire() if do_trans: if do_commit: total_commits += 1 else: total_rollbacks += 1 else: total_nontrans += 1 total_inserts += inserts total_updates += updates total_deletes += deletes total_duplicates += duplicates if duplicates 0: print 2
Re: [HACKERS] Scaling up deferred unique checks and the after trigger queue
On Mon, 2009-10-26 at 13:28 +, Dean Rasheed wrote: It works for all kinds of trigger events, and is intended as a complete drop-in replacement for the after triggers queue. All of those seem false in the general case. What will you do? At this point I'm looking for more feedback as to whether any of this is a show-stopper, before I expend more effort on this patch. I see no show stoppers, only for you to look at ways of specifying that this optimization is possible for particular cases. I think we might be able to make the general statement that it will work for all after triggers that execute STABLE or IMMUTABLE functions. I don't think we can assume that firing order is irrelevant for some cases, e.g. message queues. -- Simon Riggs www.2ndQuadrant.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] License clarification: BSD vs MIT
On Mon, 2009-10-26 at 13:13 +, Dave Page wrote: ISTM we should apply to OSI for approval of our licence, so we can then refer to it as the PostgreSQL licence. That then avoids any situation that might allow someone to claim some injunctive relief of part of the licence because of it being widely misdescribed. Already in hand. OK, nose retracted. -- Simon Riggs www.2ndQuadrant.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] Proposal: String key space for advisory locks
On Mon, Oct 26, 2009 at 1:54 AM, Christophe Pettus x...@thebuild.com wrote:
Greetings,
I'd like to propose a potential patch, and wanted to get preliminary
feedback on it before I started looking into the design.
Summary: Add a string key space to the advisory lock functionality.
Rationale:
Right now, the key spaces (the range of unique values that can be used as
identity) for advisory locks are either a bigint or two ints. This is, of
course, technically more than one could imaginably need in any application.
The difficulty arises when the number of potential advisory locks is
related to rows in one or more tables.
For example, suppose one wanted to use advisory locks to signal that a queue
entry is being processed, and entries in that queue have a primary key
that's also a bigint. There's no problem; the advisory lock id is the
primary key for the row.
And, then, one wants to use an advisory lock to signal that a particular
record in another table is being processed in a long-term process. One has
a series of unappealing alternatives at that point, mostly involving
encoding a table ID and the primary key of a record into the 64 bit number,
or just hoping that the primary key doesn't overflow an int, and using the 2
x int form.
If you want to lock records from multiple tables, probably the best
approach is to use a single sequence and pull IDs from it for each
table you want to use advisory locks with. It doesn't even have to be
the primary key (although it can be)...you can even use a domain:
create sequence lock_seq;
create domain lock_val not null default nextval('lock_seq');
create table a_table(lock_val lock_val, ...);
create table b_table(lock_val lock_val, ...);
Regarding your proposal...the lock system is highly optimized and any
suggestion that incurs performance issues is probably not going to
make it...
merlin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parsing config files in a directory
On Mon, 2009-10-26 at 09:04 -0400, Robert Haas wrote: On Mon, Oct 26, 2009 at 12:46 AM, Josh Berkus j...@agliodbs.com wrote: On 10/25/09 5:33 PM, Robert Haas wrote: Greg believes that it isn't politically feasible to change the default postgresql.conf, now or perhaps ever. I notice that he didn't say that he thinks it's a bad idea. So he has come up with an alternate plan which he believes is the best one possible considering that limitation. I agree with Greg. I would love to dump the current stupid long postgresql.conf, but I've lost that argument every time I've had it. We have to work around it. Do you have a pointer to the archives? http://archives.postgresql.org/pgsql-hackers/2008-08/msg00812.php -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] License clarification: BSD vs MIT
On Mon, Oct 26, 2009 at 1:47 PM, Simon Riggs si...@2ndquadrant.com wrote: On Mon, 2009-10-26 at 13:13 +, Dave Page wrote: ISTM we should apply to OSI for approval of our licence, so we can then refer to it as the PostgreSQL licence. That then avoids any situation that might allow someone to claim some injunctive relief of part of the licence because of it being widely misdescribed. Already in hand. OK, nose retracted. :-) -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PGDay.EU 2009 Conference: http://2009.pgday.eu/start -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parsing config files in a directory
Robert Haas escribió: On Mon, Oct 26, 2009 at 12:18 AM, Greg Smith gsm...@gregsmith.com wrote: It actually makes it completely trivial to implement. SET PERSISTENT can now write all the changes out to a new file in the include directory. Just ship the database with a persistent.conf in there that looks like this: This only sorta works. If the changes are written out to a file that is processed after postgresql.conf (or some other file that contains values for those variables), then someone who edits postgresql.conf (or some other file) by hand will think they have changed a setting when they really haven't. Maybe SET PERSISTENT needs to go back to postgresql.conf, add an automatic comment # overridden in persistent.conf and put a comment marker in front of the original line. That way the user is led to the actual authoritative source. On the flip side, there could also be still other files that are processed afterwards, in which case SET PERSISTENT would appear to work but not actually do anything. Fortunately we now have an easy way to find out which file is each setting's value coming from. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: String key space for advisory locks
Christophe Pettus x...@thebuild.com writes: I'd like to propose a potential patch, and wanted to get preliminary feedback on it before I started looking into the design. Summary:Add a string key space to the advisory lock functionality. Your chances of making the LOCKTAG struct bigger for this are nonexistent. I concur with Itagaki-san's suggestion that a hash of your strings ought to be a fine solution ... and you could use it today. 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] Parsing config files in a directory
Alvaro Herrera alvhe...@commandprompt.com writes: Maybe SET PERSISTENT needs to go back to postgresql.conf, add an automatic comment # overridden in persistent.conf and put a comment marker in front of the original line. That way the user is led to the actual authoritative source. Doesn't that require the same AI-complete parsing ability we have said we don't want to implement? Personally I think this is just a matter of usage. If you want to use SET PERSISTENT, don't set values manually in postgresql.conf. How is that different from the existing rule that if you want to set values in postgresql.conf, you'd better not set them on the postmaster command line? Fortunately we now have an easy way to find out which file is each setting's value coming from. Yeah --- that feature should make it easy enough to debug any conflicts. I think we shouldn't overthink this. The separate file with a clear warning to not edit it manually seems like a fine approach from here. 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] Endgame for all those SELECT FOR UPDATE changes: fix plan node order
Alvaro Herrera alvhe...@commandprompt.com writes: Tom Lane escribió: Yeah, it could definitely run slower than the existing code --- in particular the combination of all three (FOR UPDATE ORDER BY LIMIT) would tend to become a seqscan-and-sort rather than possibly just reading one end of an index. However, I quote the old aphorism that it can be made indefinitely fast if it doesn't have to give the right answer. The reason the current behavior is fast is it's giving the wrong answer :-( So this probably merits a warning in the release notes for people to check that their queries continue to run with the performance they expect. One problem with this is that there isn't any good way for someone to get back the old behavior if they want to. Which might be a perfectly reasonable thing, eg if they know that no concurrent update is supposed to change the sort-key column. The obvious thing would be to allow select * from (select * from foo order by col limit 10) ss for update; to apply the FOR UPDATE last. Unfortunately, that's not how it works now because the FOR UPDATE will get pushed down into the subquery. I was shot down when I proposed a related change, a couple weeks ago http://archives.postgresql.org/message-id/7741.1255278...@sss.pgh.pa.us but it seems like we might want to reconsider. 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] Parsing config files in a directory
Tom Lane escribió: Alvaro Herrera alvhe...@commandprompt.com writes: Maybe SET PERSISTENT needs to go back to postgresql.conf, add an automatic comment # overridden in persistent.conf and put a comment marker in front of the original line. That way the user is led to the actual authoritative source. Doesn't that require the same AI-complete parsing ability we have said we don't want to implement? Huh, no, it's not necessary to parse the comment previous to the value. Just comment it off. Personally I think this is just a matter of usage. If you want to use SET PERSISTENT, don't set values manually in postgresql.conf. How is that different from the existing rule that if you want to set values in postgresql.conf, you'd better not set them on the postmaster command line? I agree, except that some things are defined in postgresql.conf by initdb and you probably want to be able to change them by SET PERSISTENT anyway (e.g. lc_messages, listen_addresses, shared_buffers) -- 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] Proposal: String key space for advisory locks
Alvaro Herrera wrote: Christophe Pettus wrote: API Changes: Overloading the various advisory lock functions to take a suitable string type (varchar(64)?) in addition to the bigint / 2 x int variations. As with the bigint / 2 x int forms, this string namespace would be disjoint from the other key spaces. I don't think this can be made to work. The locktag hash element has a fixed size. Perhaps you could make it work if you hashed the string and used that as a locktag, but it would lock too much as soon as two strings had matching hashes. You could add another level of indirection, e.g by adding a new table that maps the string to a bigint. I doubt it's worth the effort and performance impact, though. Cleaning up old unused rows from the table etc. would require a fair amount of work. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parsing config files in a directory
On Mon, Oct 26, 2009 at 9:51 AM, Peter Eisentraut pete...@gmx.net wrote: On Mon, 2009-10-26 at 09:04 -0400, Robert Haas wrote: On Mon, Oct 26, 2009 at 12:46 AM, Josh Berkus j...@agliodbs.com wrote: On 10/25/09 5:33 PM, Robert Haas wrote: Greg believes that it isn't politically feasible to change the default postgresql.conf, now or perhaps ever. I notice that he didn't say that he thinks it's a bad idea. So he has come up with an alternate plan which he believes is the best one possible considering that limitation. I agree with Greg. I would love to dump the current stupid long postgresql.conf, but I've lost that argument every time I've had it. We have to work around it. Do you have a pointer to the archives? http://archives.postgresql.org/pgsql-hackers/2008-08/msg00812.php Thanks. This thread seems to contain overwhelming SUPPORT for shortening the file. Greg Sabino Mullane didn't like it, and there were a smattering of ideas like we should have a postgresql.conf man page, we should make sure to document which parameters people are most likely to need to adjust, and we should have a config generator (all of which are good ideas), but nearly everyone seemed to agree with the general idea that the current file contained way too much unnecessary cruft. What am I missing here? ...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] Endgame for all those SELECT FOR UPDATE changes: fix plan node order
On Mon, Oct 26, 2009 at 10:30 AM, Tom Lane t...@sss.pgh.pa.us wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Tom Lane escribió: Yeah, it could definitely run slower than the existing code --- in particular the combination of all three (FOR UPDATE ORDER BY LIMIT) would tend to become a seqscan-and-sort rather than possibly just reading one end of an index. However, I quote the old aphorism that it can be made indefinitely fast if it doesn't have to give the right answer. The reason the current behavior is fast is it's giving the wrong answer :-( So this probably merits a warning in the release notes for people to check that their queries continue to run with the performance they expect. One problem with this is that there isn't any good way for someone to get back the old behavior if they want to. Which might be a perfectly reasonable thing, eg if they know that no concurrent update is supposed to change the sort-key column. The obvious thing would be to allow select * from (select * from foo order by col limit 10) ss for update; to apply the FOR UPDATE last. Unfortunately, that's not how it works now because the FOR UPDATE will get pushed down into the subquery. I was shot down when I proposed a related change, a couple weeks ago http://archives.postgresql.org/message-id/7741.1255278...@sss.pgh.pa.us but it seems like we might want to reconsider. Shot down might be an overstatement of the somewhat cautious reaction that proposal. :-) Could the desired behavior be obtained using a CTE? ...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] Parsing config files in a directory
Alvaro Herrera alvhe...@commandprompt.com writes: Tom Lane escribió: Personally I think this is just a matter of usage. If you want to use SET PERSISTENT, don't set values manually in postgresql.conf. I agree, except that some things are defined in postgresql.conf by initdb and you probably want to be able to change them by SET PERSISTENT anyway (e.g. lc_messages, listen_addresses, shared_buffers) Well, initdb would also find it a lot easier to dump its settings into a machine-generated file. I don't think we have to continue doing things exactly the same way there. 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] Parsing config files in a directory
Robert Haas robertmh...@gmail.com writes: What am I missing here? You're still attacking the wrong straw man. Whether the file contains a lot of commentary by default is NOT the problem, and removing the commentary is NOT the solution. 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] Parsing config files in a directory
Tom Lane escribió: Alvaro Herrera alvhe...@commandprompt.com writes: Tom Lane escribi�: Personally I think this is just a matter of usage. If you want to use SET PERSISTENT, don't set values manually in postgresql.conf. I agree, except that some things are defined in postgresql.conf by initdb and you probably want to be able to change them by SET PERSISTENT anyway (e.g. lc_messages, listen_addresses, shared_buffers) Well, initdb would also find it a lot easier to dump its settings into a machine-generated file. I don't think we have to continue doing things exactly the same way there. Hmm, so it would create a 00initdb.conf file instead of the current mess with search replace on the template? That sounds good. (But to me this also says that SET PERSISTENT has to go over 00initdb.conf and add a comment mark to the setting.) -- 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] Parsing config files in a directory
Alvaro Herrera alvhe...@commandprompt.com writes: (But to me this also says that SET PERSISTENT has to go over 00initdb.conf and add a comment mark to the setting.) Why? As you yourself pointed out, pg_settings will show exactly where the active value came from. Moreover, should we then conclude that any edit to any file in the config directory has to run around and edit every other file? I'll bet a lot of money that no Apache config editor does that. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Tightening binary receive functions
Heikki == Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Heikki Oops, you're right. The check is indeed confusing julian day Heikki numbers, with epoch at 23th of Nov 4714 BC, with Heikki postgres-reckoning day numbers, with epoch at 1th of Jan Heikki 2000. Thanks, will fix. Which reminds me: why isn't there an extract(jday from ...) function or similar? -- Andrew. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parsing config files in a directory
Robert Haas robertmh...@gmail.com wrote: I realize that the current file format is an old and familiar friend; it is for me, too. But I think it's standing in the way of progress. Being able to type a SQL command to update postgresql.conf would be more substantially convenient than logging in as root, using su to become postgres, changing to the correct directory, starting up vi, finding the right setting, editing it, quitting out, and requesting a reload. And I deal with 1 PostgreSQL instance at a time, not tens or hundreds or thousands. Speaking as someone who has to help keep 200 geographically dispersed PostgreSQL clusters running, I can say that convenient ways to change configuration settings on individual servers has little appeal, particularly if it makes it harder to enforce configuration policies or to audit current settings. Generally, before applying any update or configuration change to production servers we must first apply it to a development environment and prove that it improves things without breaking anything, then it can be rolled to a test environment where those results must be confirmed, and then to a staging environment to confirm both our install procedures and the behavior of the change with a large number of testers going through standard scripts for exercising the application software. Copying scripts into place and reloading or restarting PostgreSQL is not an imposition; anything which reduces my confidence in knowing what configuration is in use is an imposition. Piping a list of server names through xargs to a deploy script just isn't a big deal, once we have an acceptable configuration. We do find the include capabilities useful. For example, for our 72 production servers for county Circuit Court systems, we copy an identical postgresql.conf file to each county, with the last line being an include to an overrides conf file in /etc/. For most counties that file is empty. For counties where we've installed extra RAM or where data is not fully cached, we override settings like effective_cache_size or the page costs. I can't see where any of the options under discussion would do much to help an environment like ours -- they seem more likely to help shops with fewer servers or more relaxed deployment procedures. -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] Parsing config files in a directory
Greg Smith gsm...@gregsmith.com writes: People who want to continue managing just the giant postgresql.conf are free to collapse the initdb.conf back into the larger file instead. If we wanted to make that transition easier, an option to initdb saying do things the old way might make sense. I think the best we can do here is make a path where new users who don't ask for anything special get a setup that's easy for tools to work on, while not completely deprecating the old approach for those who want it--but you have to ask for it. I don't think we need an explicit option for that. What we need is an 'includedir' directive at the bottom of postgresql.conf. Someone who prefers to let manual settings override anything else might choose to move it to the top, or even comment it out (at the cost of breaking SET PERSISTENT). Everybody is happy, trogdolyte or otherwise. I would personally suggest having initdb dump its settings right into persistent.conf, rather than having a separate file for them, but it's not a big deal either way. (BTW, why do we actually need an includedir mechanism for this? A simple include of a persistent.conf file seems like it would be enough.) 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] Parsing config files in a directory
On Mon, 26 Oct 2009, Alvaro Herrera wrote: some things are defined in postgresql.conf by initdb and you probably want to be able to change them by SET PERSISTENT anyway (e.g. lc_messages, listen_addresses, shared_buffers) An obvious next step once the directory parsing is committed is to change initdb to put all of its changes into a separate file. Ideally, 8.5 would ship with a postgresql.conf having zero active settings, and the conf/ directory would have two entries: initdb.conf : shared_buffers, lc_messages, listen_addresses, etc. persistent.conf : Blank except for comment text People who want to continue managing just the giant postgresql.conf are free to collapse the initdb.conf back into the larger file instead. If we wanted to make that transition easier, an option to initdb saying do things the old way might make sense. I think the best we can do here is make a path where new users who don't ask for anything special get a setup that's easy for tools to work on, while not completely deprecating the old approach for those who want it--but you have to ask for it. -- * Greg Smith gsm...@gregsmith.com 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] Parsing config files in a directory
On Mon, 26 Oct 2009, Alvaro Herrera wrote: But to me this also says that SET PERSISTENT has to go over 00initdb.conf and add a comment mark to the setting. Now you're back to being screwed if the server won't start because of your change, because you've lost the original working setting. I think the whole idea of making tools find duplicates and comment them out as part of making their changes is fundamentally broken, and it's just going to get worse when switching to use more config files. The fact that user edits can introduce the same problem, where something is set in more than one file but only one of them works, means that you can run into this even if tool editing hygiene is perfect. A whole new approach is needed if you're going to get rid of this problem both for tools and for manual edits. What I've been thinking of is making it possible to run a configuration file check that scans the config structure exactly the same way as the server, but when it finds a duplicate setting it produces a warning showing where the one being ignored is. The patch added near to the end of 8.4 development that remembers the source file and line number of lines already parsed made that more straightforward I think. Not having that data is what made this hard to write when I last considered it a while ago. If you had that utility, it's a simple jump to then make it run in a --fix mode that just comments out every such ignored duplicate. Now you've got a solution to this problem that handles any sort of way users can mess with the configuration. One might even make a case that this tool should get run just after every time the server starts successfully. -- * Greg Smith gsm...@gregsmith.com 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] Parsing config files in a directory
Greg Smith gsm...@gregsmith.com writes: I think the whole idea of making tools find duplicates and comment them out as part of making their changes is fundamentally broken, and it's just going to get worse when switching to use more config files. Quite. There seems to me to be a whole lot of solving of hypothetical problems going on in this thread. I think we should just do the simplest thing and see how it works. When and if there is some evidence of people actually getting confused, we could consider trying to auto-comment-out duplicate settings. But I've never heard of any other tool doing that, and fail to see why we should think Postgres needs to. 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] License clarification: BSD vs MIT
On Mon, Oct 26, 2009 at 8:08 AM, Simon Riggs si...@2ndquadrant.com wrote: ISTM we should apply to OSI for approval of our licence, so we can then refer to it as the PostgreSQL licence. IMHO and not being a lawyer, this is the only reason for anyone to think in change our license i think... even in the case both licenses are roughly equivalent, because users are afraid of any changes. if we simply change our license for no good reason we will have a ton of questions about if PostgreSQL is being sold just as MySQL was... -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] License clarification: BSD vs MIT
On Mon, Oct 26, 2009 at 3:36 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Mon, Oct 26, 2009 at 8:08 AM, Simon Riggs si...@2ndquadrant.com wrote: ISTM we should apply to OSI for approval of our licence, so we can then refer to it as the PostgreSQL licence. IMHO and not being a lawyer, this is the only reason for anyone to think in change our license i think... even in the case both licenses are roughly equivalent, because users are afraid of any changes. if we simply change our license for no good reason we will have a ton of questions about if PostgreSQL is being sold just as MySQL was... Changing the licence is *not* going to happen. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PGDay.EU 2009 Conference: http://2009.pgday.eu/start -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parsing config files in a directory
On Mon, 26 Oct 2009, Tom Lane wrote: BTW, why do we actually need an includedir mechanism for this? A simple include of a persistent.conf file seems like it would be enough. Sure, you could do it that way. This patch is more about elegance rather than being strictly required. The general consensus here seemed to be that if you're going to start shipping the database with more than one config file, rather than just hacking those in one at a time it would be preferrable to grab a directory of them. That seems to be how similar programs handle things once the number of shipped config files goes from 1 to 1. One thing this discussion has made me reconsider is whether one of those files needs to be enforced as always the last one to be parsed, similar to how postgresql.conf is always the first one. I am slightly concerned that a future SET PERSISTENT mechanism might update a setting that's later overriden by a file that just happens to be found later than the mythical persistent.conf. I'd rather worry about that in the future rather than burden current design with that detail though. Alvaro already introduced the init-script way of handling this by suggesting the configuration file name 00initdb ; using that and 99persistent would seem to be a reasonable solution that's quite familiar to much of the target audience here. Note that I don't think that standard requires anything beyond what the proposed patch already does, processing files in alphabetical order. -- * Greg Smith gsm...@gregsmith.com 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] Parsing config files in a directory
On Mon, 26 Oct 2009, Tom Lane wrote: When and if there is some evidence of people actually getting confused, we could consider trying to auto-comment-out duplicate settings. But I've never heard of any other tool doing that, and fail to see why we should think Postgres needs to. It's what people tend to do when editing the postgresql.conf file(s) by hand, which is why I think there's some expectation that tools will continue that behavior. What everyone should understand is that we don't have more tools exactly because their design always gets burdened with details like that. This is easy to handle by hand, but hard to get a program to do in a way that satisfies what everyone is looking for. Raising the bar for tool-assisted changes (and I'm including SET PERSISTENT in that category) like that is one reason so few such tools have been written. -- * Greg Smith gsm...@gregsmith.com 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] Unicode UTF-8 table formatting for psql text output
On sön, 2009-10-25 at 23:48 +, Roger Leigh wrote: Just for reference, this is what the output looks like (abridged) using the attached patch. Should display fine if your mail client handles UTF-8 messages correctly: rleigh=# \l List of databases Name │ Owner │ Encoding │ Collation │Ctype│ Access privileges ─┼──┼──┼─┼─┼─── merkelpb│ rleigh │ UTF8 │ en_GB.UTF-8 │ en_GB.UTF-8 │ postgres│ postgres │ UTF8 │ en_GB.UTF-8 │ en_GB.UTF-8 │ projectb│ rleigh │ UTF8 │ en_GB.UTF-8 │ en_GB.UTF-8 │ rleigh │ rleigh │ UTF8 │ en_GB.UTF-8 │ en_GB.UTF-8 │ […] template0 │ postgres │ UTF8 │ en_GB.UTF-8 │ en_GB.UTF-8 │ =c/postgres ↵ │ │ │ │ │ postgres=CTc/postgres template1 │ postgres │ UTF8 │ en_GB.UTF-8 │ en_GB.UTF-8 │ =c/postgres ↵ │ │ │ │ │ postgres=CTc/postgres […] (17 rows) That's pretty much what I had in mind. Cool. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Endgame for all those SELECT FOR UPDATE changes: fix plan node order
Robert Haas robertmh...@gmail.com writes: On Mon, Oct 26, 2009 at 10:30 AM, Tom Lane t...@sss.pgh.pa.us wrote: One problem with this is that there isn't any good way for someone to get back the old behavior if they want to. Which might be a perfectly reasonable thing, eg if they know that no concurrent update is supposed to change the sort-key column. The obvious thing would be to allow select * from (select * from foo order by col limit 10) ss for update; to apply the FOR UPDATE last. Unfortunately, that's not how it works now because the FOR UPDATE will get pushed down into the subquery. Could the desired behavior be obtained using a CTE? Nope, we push FOR UPDATE into WITHs too. I don't really see any way to deal with this without some sort of semantic changes. 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] Parsing config files in a directory
On Mon, Oct 26, 2009 at 11:07 AM, Tom Lane t...@sss.pgh.pa.us wrote: Greg Smith gsm...@gregsmith.com writes: People who want to continue managing just the giant postgresql.conf are free to collapse the initdb.conf back into the larger file instead. If we wanted to make that transition easier, an option to initdb saying do things the old way might make sense. I think the best we can do here is make a path where new users who don't ask for anything special get a setup that's easy for tools to work on, while not completely deprecating the old approach for those who want it--but you have to ask for it. I don't think we need an explicit option for that. What we need is an 'includedir' directive at the bottom of postgresql.conf. Someone who prefers to let manual settings override anything else might choose to move it to the top, or even comment it out (at the cost of breaking SET PERSISTENT). Everybody is happy, trogdolyte or otherwise. I would personally suggest having initdb dump its settings right into persistent.conf, rather than having a separate file for them, but it's not a big deal either way. That make sense to me. (BTW, why do we actually need an includedir mechanism for this? A simple include of a persistent.conf file seems like it would be enough.) I was starting to wonder that, too. ...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] Endgame for all those SELECT FOR UPDATE changes: fix plan node order
I wrote: Robert Haas robertmh...@gmail.com writes: Could the desired behavior be obtained using a CTE? Nope, we push FOR UPDATE into WITHs too. I don't really see any way to deal with this without some sort of semantic changes. ... although on reflection, I'm not sure *why* we push FOR UPDATE into WITHs. That seems a bit antithetical to the position we've evolved that WITH queries are executed independently of the outer query. If we removed that bit of behavior, which hopefully is too new for much code to depend on, then the old FOR-UPDATE-last behavior could be attained via a WITH. And we'd not have to risk touching the interaction between plain subqueries and FOR UPDATE, which is something that seems much more likely to break existing apps. That seems like a reasonable compromise to me ... any objections? 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] [ANNOUNCE] PostgreSQL 8.5alpha2 Now Available
On Oct 24, 2009, at 10:44 AM, Peter Eisentraut wrote: More detail is available in the Release Notes included with each alpha: http://developer.postgresql.org/pgdocs/postgres/release-8.5.html That seems to just have alpha1 at the moment. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] per-tablespace random_page_cost/seq_page_cost
On Sun, Oct 25, 2009 at 9:05 AM, Robert Haas robertmh...@gmail.com wrote: Arguably, you would expect parameters set using this syntax to be stored similar to reloptions - that is, as text[]. But as we're going to need these values multiple times per table to plan any non-trivial query, I don't want to inject unnecessary parsing overhead and code complexity. Two comments, perhaps complementary, though I'm not sure of either answer. 1 Would we rather the storage scheme allow for future GUCs to be easily moved to per-tablespace as well without changing the catalog schema for every option? (Someone might accuse me of trolling the anti-EAV people here though...) 2 Would it make sense to slurp these options from the tablespace options into the relcache when building the relcache entry for a table? That would make the storage format in the tablespace options much less relevant. It might even make the catcache less important too. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] License clarification: BSD vs MIT
On Mon, Oct 26, 2009 at 10:40 AM, Dave Page dp...@pgadmin.org wrote: On Mon, Oct 26, 2009 at 3:36 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Mon, Oct 26, 2009 at 8:08 AM, Simon Riggs si...@2ndquadrant.com wrote: ISTM we should apply to OSI for approval of our licence, so we can then refer to it as the PostgreSQL licence. IMHO and not being a lawyer, this is the only reason for anyone to think in change our license i think... even in the case both licenses are roughly equivalent, because users are afraid of any changes. if we simply change our license for no good reason we will have a ton of questions about if PostgreSQL is being sold just as MySQL was... Changing the licence is *not* going to happen. to tell someone we no longer label our license as simplified BSD but as MIT is, in the eyes and mind of users, changing the license... even if the wording doesn't change... that's because we have years telling people our license is BSD like and is very liberal, if we change the way we label our license we have to change that and say our license is MIT like and when you do that the question will arise: what was that change for? and you will explain that the license hadn't changed but the mind of the users is not listening anymore it's very busy trying to find for themselves hidden reasons and they will find them even if that reasons doesn't exist. -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] License clarification: BSD vs MIT
On Mon, Oct 26, 2009 at 4:15 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Mon, Oct 26, 2009 at 10:40 AM, Dave Page dp...@pgadmin.org wrote: On Mon, Oct 26, 2009 at 3:36 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Mon, Oct 26, 2009 at 8:08 AM, Simon Riggs si...@2ndquadrant.com wrote: ISTM we should apply to OSI for approval of our licence, so we can then refer to it as the PostgreSQL licence. IMHO and not being a lawyer, this is the only reason for anyone to think in change our license i think... even in the case both licenses are roughly equivalent, because users are afraid of any changes. if we simply change our license for no good reason we will have a ton of questions about if PostgreSQL is being sold just as MySQL was... Changing the licence is *not* going to happen. to tell someone we no longer label our license as simplified BSD but as MIT is, in the eyes and mind of users, changing the license... even if the wording doesn't change... So what do you suggest? Burying our heads in the sand is not an option. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PGDay.EU 2009 Conference: http://2009.pgday.eu/start -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] License clarification: BSD vs MIT
Dave Page dp...@pgadmin.org writes: On Mon, Oct 26, 2009 at 4:15 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: to tell someone we no longer label our license as simplified BSD but as MIT is, in the eyes and mind of users, changing the license... even if the wording doesn't change... So what do you suggest? Burying our heads in the sand is not an option. I'm of the opinion that we should continue to say that it's simplified BSD. It's not our problem that Red Hat has chosen not to use that terminology (which OSI uses, so it's not like there's no precedent). Red Hat has an interest in minimizing the number of pigeonholes they classify things into, but that doesn't mean anyone else has to care. I quite agree with Jaime that starting to call ourselves MIT rather than BSD would be a public-relations disaster. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Anonymous Code Blocks as Lambdas?
Howdy, Very excited about the new `DO` command in 8.5a2. I read through the patch review thread and found that, like me, Dim had expected it to behave more like a lambda than a simple command. And from Tom's comments, it looks like it was committed in such a way to make such extensions possible (passing arguments, returning values (maybe even sets?). So I was wondering if anyone has thought about adding such functionality, and if so, what it might look like? If the answer is no, because we want to see what cow paths develop in 8.5, that's fine with me. I'll just be chasing cows. :-) Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Anonymous Code Blocks as Lambdas?
Howdy, Very excited about the new `DO` command in 8.5a2. I read through the patch review thread and found that, like me, Dim had expected it to behave more like a lambda than a simple command. And from Tom's comments, it looks like it was committed in such a way to make such extensions possible (passing arguments, returning values (maybe even sets?). So I was wondering if anyone has thought about adding such functionality, and if so, what it might look like? If the answer is no, because we want to see what cow paths develop in 8.5, that's fine with me. I'll just be chasing cows. :-) Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] License clarification: BSD vs MIT
On Mon, Oct 26, 2009 at 4:25 PM, Tom Lane t...@sss.pgh.pa.us wrote: Dave Page dp...@pgadmin.org writes: On Mon, Oct 26, 2009 at 4:15 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: to tell someone we no longer label our license as simplified BSD but as MIT is, in the eyes and mind of users, changing the license... even if the wording doesn't change... So what do you suggest? Burying our heads in the sand is not an option. I'm of the opinion that we should continue to say that it's simplified BSD. It's not our problem that Red Hat has chosen not to use that terminology (which OSI uses, so it's not like there's no precedent). Red Hat has an interest in minimizing the number of pigeonholes they classify things into, but that doesn't mean anyone else has to care. Except it is not the simplified BSD - it's notably different. That's Redhat's argument, and was also the comment that the lawyer I spoke to made. I quite agree with Jaime that starting to call ourselves MIT rather than BSD would be a public-relations disaster. You already know I agree with that :-) I'm working on getting the licence through the OSI approval process. When/if that is done, I expect we'll have 'The PostgreSQL License' which we can then describe as being *similar* to the simplified BSD. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PGDay.EU 2009 Conference: http://2009.pgday.eu/start -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] table corrupted
On Mon, 2009-10-26 at 09:14 -0400, Jonah H. Harris wrote: On Thu, Oct 22, 2009 at 7:16 PM, Joshua D. Drake j...@commandprompt.com wrote: On Thu, 2009-10-22 at 14:28 -0200, João Eugenio Marynowski wrote: Hi Repair? Not likely. Get past? Maybe. I don't know how valuable your data is, but I've performed data recovery on tens of PG databases suffering from both hardware and software corruption on versions 7.0 through 8.3. My rate is $300-600 USD/hour depending on the database/table size and the extent of the corruption. If you're just trying to save what's not corrupted, there's quite a few examples online. Jonah, This reply is wholly inappropriate for a Pg list. We are here to help people. If you have a consultancy, please feel free to list that but any discussion of rates is just plain rude. Please use better discretion in the future. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering If the world pushes look it in the eye and GRR. Then push back harder. - Salamander -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Scaling up deferred unique checks and the after trigger queue
On Mon, 2009-10-26 at 13:41 +, Dean Rasheed wrote: I did a quick bit of testing, and I think that there is a locking/concurrency problem :-( Unfortunately I can't reproduce the problem on my machine; it always passes. If you have a minute, can you try to determine if the problem can happen with a non-deferrable constraint? I'll keep looking into it. Thanks, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Scaling up deferred unique checks and the after trigger queue
2009/10/26 Simon Riggs si...@2ndquadrant.com: On Mon, 2009-10-26 at 13:28 +, Dean Rasheed wrote: It works for all kinds of trigger events, and is intended as a complete drop-in replacement for the after triggers queue. All of those seem false in the general case. What will you do? At this point I'm looking for more feedback as to whether any of this is a show-stopper, before I expend more effort on this patch. I see no show stoppers, only for you to look at ways of specifying that this optimization is possible for particular cases. I think we might be able to make the general statement that it will work for all after triggers that execute STABLE or IMMUTABLE functions. I don't think we can assume that firing order is irrelevant for some cases, e.g. message queues. Hmm, thinking about this some more... one thing this patch does is to separate out the queues for regular triggers from those for RI triggers and deferrable constraint checks. ITSM that row-order only really matters for the former. It's also the case that for these triggers there will never be any other choice but to execute them one at a time, so they may as well just spool to a file rather than using a TID bitmap. The bitmaps are probably only useful for constraint triggers, where a bulk check can be used instead of executing individual triggers for each row, if enough rows are modified. - Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Unicode UTF-8 table formatting for psql text output
2009/10/25 Roger Leigh rle...@codelibre.net: rleigh=# \l List of databases Name │ Owner │ Encoding │ Collation │ Ctype │ Access privileges ─┼──┼──┼─┼─┼─── merkelpb │ rleigh │ UTF8 │ en_GB.UTF-8 │ en_GB.UTF-8 │ postgres │ postgres │ UTF8 │ en_GB.UTF-8 │ en_GB.UTF-8 │ projectb │ rleigh │ UTF8 │ en_GB.UTF-8 │ en_GB.UTF-8 │ rleigh │ rleigh │ UTF8 │ en_GB.UTF-8 │ en_GB.UTF-8 │ […] template0 │ postgres │ UTF8 │ en_GB.UTF-8 │ en_GB.UTF-8 │ =c/postgres ↵ │ │ │ │ │ postgres=CTc/postgres template1 │ postgres │ UTF8 │ en_GB.UTF-8 │ en_GB.UTF-8 │ =c/postgres ↵ │ │ │ │ │ postgres=CTc/postgres […] (17 rows) While i agree this looks nicer I wonder what it does to things like excel/gnumeric/ooffice auto-recognizing table layouts and importing files. I'm not sure our old format was so great for this so maybe this is actually an improvement I'm asking for. But as long as we're changing the format... It would at at least be good to test the behaviour -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] per table random-page-cost?
Le samedi 24 octobre 2009 01:04:19, Josh Berkus a écrit : Cedric, ase is a table containing 29 GB of bytea in a database of 52 GB. Every row on the 29GB table is grab only few times. And it will just renew OS cache memory every time (the server have only 8GB of ram). So when I remove this table (not the index) from the OS cache memory, I keep more interesting blocks in the OS cache memory. effective_cache_size doesn't control what gets cached, it just tells the planner about it. Now, if we had an OS which could be convinced to handle caching differently for different physical devices, then I could see wanting this setting to be per-tablespace. For example, it would make a lot of sense not to FS-cache any data which is on a ramdisk or superfast SSD array. The same with archive data which you expected to be slow and infrequently accessed on a NAS device. If your OS can do that, while caching data from other sources, then it would make sense. However, I don't know any current OS which allows for this. Does anyone else? Isn't it what fadvise -dontneed let you do ? Josh, I talk about effective_cache_size per tablespace *exactly* for the reason you explain. -- Cédric Villemain Administrateur de Base de Données Cel: +33 (0)6 74 15 56 53 http://dalibo.com - http://dalibo.org signature.asc Description: This is a digitally signed message part.
[HACKERS] Re: a question about relkind of RelationData handed over to heap_update function
On Sun, Oct 25, 2009 at 9:37 AM, 노홍찬 falls...@cs.yonsei.ac.kr wrote: What I am trying to do now is to examine the real dirty portion of buffer pages to be flushed like the following. page 1 - | | dportion1 (real dirty portion 1) ranges between 20 ~ 80 | dportion1 | | | dportion2 (real dirty portion 2) ranges between 8190 ~ 8192 | | | dportion2 | - Since there are many different kinds of page-updates such as updates to local buffer, temp relation, indexes, toasted attributes, and so forth. It would be a big burden to me if I inspect all that codes. Therefore, I decided to make a start point as inspecting only updates to the ordinary tables. I added a log array field to BufferDesc struct, and added logs to the designated bufferDesc of the updated buffer when it comes to ordinary table updates (The logs specifies the real dirty portion ranges of the buffer). I would think you would want to modify MarkBufferDirty to take a start and end point and store that in your log. Then modify every existing MarkBufferDirty operation that you can to specify the range that the subsequent operation is going to modify. You're going to run into problems where you have code which looks like: - mark buffer dirty - do some work which modifies a predictable portion - if (some rare condition) - do some more work which modifies other parts of the buffer The some more work may be some function call which doesn't usually do much either. So you may end up having to restructure a lot of code so that every function is responsible for marking the buffer range dirty itself instead of assuming it's already been marked. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Scaling up deferred unique checks and the after trigger queue
2009/10/26 Jeff Davis pg...@j-davis.com: On Mon, 2009-10-26 at 13:41 +, Dean Rasheed wrote: I did a quick bit of testing, and I think that there is a locking/concurrency problem :-( Unfortunately I can't reproduce the problem on my machine; it always passes. That's odd. It happens every time on my machine (10 threads, 1000 loops). If you have a minute, can you try to determine if the problem can happen with a non-deferrable constraint? If anything, that seems to make it fail more quickly. If it's of any relevance, I'm currently using an optimised build, with assert checking off. [Linux x86_64, 2 core Intel Core2] - Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parsing config files in a directory
On Mon, Oct 26, 2009 at 10:47 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: What am I missing here? You're still attacking the wrong straw man. Whether the file contains a lot of commentary by default is NOT the problem, and removing the commentary is NOT the solution. Wow, not only am I attacking a straw man, but I'm attacking the wrong one. :-) I'm not sure whether you're saying that I'm bringing this issue up in the wrong thread, or whether you disagree with the basic suggestion. If it's the former, I'm prepared to concede the point and will start a new thread. If the latter, you took the opposite position here. http://archives.postgresql.org/pgsql-hackers/2008-08/msg00835.php I think the questions of what goes into the default postgresql.conf files, the include-dir mechanism, automatic tuning tools, and SET PERSISTENT are all closely related, and if you think otherwise, I don't understand why, but would appreciate an explanation. Elsewhere on this thread, you suggested dumping the initdb functions into a mostly-empty persistent.conf file that would be read after postgresql.conf. If we did that, then we would presumably advise people not to set settings in postgresql.conf because of the possibility that they would be overriden in persistent.conf, which begs the question of why we need two files at all. ...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] Unicode UTF-8 table formatting for psql text output
Greg Stark gsst...@mit.edu writes: While i agree this looks nicer I wonder what it does to things like excel/gnumeric/ooffice auto-recognizing table layouts and importing files. I'm not sure our old format was so great for this so maybe this is actually an improvement I'm asking for. Yeah. We can do what we like with the UTF8 format but I'm considerably more worried about the aspect of making random changes to the plain-ASCII output. On the other hand, we changed that just a release or so ago (to put in the multiline output in the first place) and I didn't hear complaints about it that time. 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] Scaling up deferred unique checks and the after trigger queue
On Mon, Oct 26, 2009 at 9:46 AM, Simon Riggs si...@2ndquadrant.com wrote: On Mon, 2009-10-26 at 13:28 +, Dean Rasheed wrote: It works for all kinds of trigger events, and is intended as a complete drop-in replacement for the after triggers queue. All of those seem false in the general case. What will you do? At this point I'm looking for more feedback as to whether any of this is a show-stopper, before I expend more effort on this patch. I see no show stoppers, only for you to look at ways of specifying that this optimization is possible for particular cases. I think we might be able to make the general statement that it will work for all after triggers that execute STABLE or IMMUTABLE functions. I don't think we can assume that firing order is irrelevant for some cases, e.g. message queues. Hmm. After-trigger functions are very unlikely to really be STABLE or IMMUTABLE, though. Almost by definition, they'd better be modifying some data somewhere, or there's no point. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: Endgame for all those SELECT FOR UPDATE changes: fix plan node order
On Sun, Oct 25, 2009 at 7:34 PM, Tom Lane t...@sss.pgh.pa.us wrote: All that we have to do to fix the first one is to put the LockRows node below the Limit node instead of above it. The solution for the second one is to also put LockRows underneath the Sort node, and to regard its output as unsorted so that a Sort node will certainly be generated. (This in turn implies that we should prefer the cheapest-total plan for the rest of the query.) I'm not following how this would work. Would it mean that every record would end up being locked? -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Endgame for all those SELECT FOR UPDATE changes: fix plan node order
Greg Stark gsst...@mit.edu writes: On Sun, Oct 25, 2009 at 7:34 PM, Tom Lane t...@sss.pgh.pa.us wrote: All that we have to do to fix the first one is to put the LockRows node below the Limit node instead of above it. The solution for the second one is to also put LockRows underneath the Sort node, and to regard its output as unsorted so that a Sort node will certainly be generated. (This in turn implies that we should prefer the cheapest-total plan for the rest of the query.) I'm not following how this would work. Would it mean that every record would end up being locked? In the case of LIMIT, no, because LIMIT doesn't fetch any more rows than it needs from its input node. In the case of ORDER BY, yes, potentially. So we might conceivably decide we should fix the first issue and not the second. However, I'd prefer to have a solution whereby the query does what it appears to mean and you have to write something more complicated if you want performance over correctness. 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] Parsing config files in a directory
On Sat, Oct 24, 2009 at 6:55 PM, Tom Lane t...@sss.pgh.pa.us wrote: I think we should have an explicit include-directory directive, and the reason I think so is that it makes it fairly easy for the user to control the relative precedence of the manual settings (presumed to still be kept in postgresql.conf) and the automatic settings (presumed to be in files in the directory). Manual settings before the include are overridable, those after are not. I think we can actually aim higher now. We don't need nearly as many degrees of freedom as people seem to be suggesting. And in this space degrees of freedom just mean the ability to have confusing configurations that surprise users. I would suggest the following: The system always scans postgresql.conf and postgresql.conf.d in the same location. We can support include and includedir directives though I think they would be mostly unnecessary. But they would be purely for the purpose of organizing your files and adding additional locations, not replacing the standard locations. They might be useful for, for example, having a site-wide set of defaults which are loaded before the cluster-specific files. postgresql.conf settings override postgresql.conf.d settings. postgresql.conf should no longer be a place for tools to automatically edit, and ideally it should be shipped empty so anything there is an explicit manual instruction from a sysadmin and should override anything installed by a package or tool. When scanning postgresql.conf.d we should follow the Apache/Debian standard of scanning only files which match a single simple hard-coded template. I think the convention is basically the regexp ^[0-9a-zA-Z-]*.conf$. It's important that it exclude typical backup file conventions like foo~ or foo.bak and lock file conventions like .#foo. There's no need for this to be configurable and I think that would be actively harmful. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parsing config files in a directory
On Mon, Oct 26, 2009 at 7:06 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Maybe SET PERSISTENT needs to go back to postgresql.conf, add an automatic comment # overridden in persistent.conf and put a comment marker in front of the original line. That way the user is led to the actual authoritative source. We cannot have automatic processes editing user configuration files. What we could do is give a warning if you do set persistent and the source of the current value is a postgresql.conf. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parsing config files in a directory
On Mon, Oct 26, 2009 at 7:25 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: I agree, except that some things are defined in postgresql.conf by initdb and you probably want to be able to change them by SET PERSISTENT anyway (e.g. lc_messages, listen_addresses, shared_buffers) These things should go into a postgresql.d/00initdb-defaults.conf file instead. Otherwise tools wouldn't be able to tune them at all without user intervention. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parsing config files in a directory
On Mon, 2009-10-26 at 10:19 -0400, Tom Lane wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Maybe SET PERSISTENT needs to go back to postgresql.conf, add an automatic comment # overridden in persistent.conf and put a comment marker in front of the original line. That way the user is led to the actual authoritative source. Doesn't that require the same AI-complete parsing ability we have said we don't want to implement? Personally I think this is just a matter of usage. If you want to use SET PERSISTENT, don't set values manually in postgresql.conf. How is that different from the existing rule that if you want to set values in postgresql.conf, you'd better not set them on the postmaster command line? Fortunately we now have an easy way to find out which file is each setting's value coming from. Yeah --- that feature should make it easy enough to debug any conflicts. I think we shouldn't overthink this. The separate file with a clear warning to not edit it manually seems like a fine approach from here. +1 This is a very usual thing to do. You just have a warning that says, THIS FILE IS AUTOGENERATED FROM SEE THE PERSISTANCE DOCS Joshua D. Drake regards, tom lane -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering If the world pushes look it in the eye and GRR. Then push back harder. - Salamander -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] table corrupted
On Mon, Oct 26, 2009 at 12:55 PM, Joshua D. Drake j...@commandprompt.comwrote: This reply is wholly inappropriate for a Pg list. We are here to help people. If you have a consultancy, please feel free to list that but any discussion of rates is just plain rude. Please use better discretion in the future. Perhaps. Though, I only posted because you made it sound somewhat impossible and because I only know of a few ppl in the PG community that offer it and/or have done is successfully. Maybe letting people know there are options, other than being screwed, is wrong... my bad :-) -- Jonah H. Harris, Senior DBA myYearbook.com
Re: [HACKERS] Anonymous Code Blocks as Lambdas?
David E. Wheeler wrote: Howdy, Very excited about the new `DO` command in 8.5a2. I read through the patch review thread and found that, like me, Dim had expected it to behave more like a lambda than a simple command. And from Tom's comments, it looks like it was committed in such a way to make such extensions possible (passing arguments, returning values (maybe even sets?). So I was wondering if anyone has thought about adding such functionality, and if so, what it might look like? If the answer is no, because we want to see what cow paths develop in 8.5, that's fine with me. I'll just be chasing cows. :-) It was discussed and rejected, at least for now. See earlier discussion. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous Code Blocks as Lambdas?
Hello I have a idea about migration of outer (psql) variables, and custom shell variables. some like: psql --allow_custom_variables --table_name=mytable inside psql we should to use :table_name variable with mytable as content. then we can use syntax do (table_name varchar) $$ begin raise notice 'TABLENAME IS %', table_name; return; end; $$ so with this mechanism we can to simply parametrise plpgsql do scripts from outer environment. comments? Regards Pavel 2009/10/26 Andrew Dunstan and...@dunslane.net: David E. Wheeler wrote: Howdy, Very excited about the new `DO` command in 8.5a2. I read through the patch review thread and found that, like me, Dim had expected it to behave more like a lambda than a simple command. And from Tom's comments, it looks like it was committed in such a way to make such extensions possible (passing arguments, returning values (maybe even sets?). So I was wondering if anyone has thought about adding such functionality, and if so, what it might look like? If the answer is no, because we want to see what cow paths develop in 8.5, that's fine with me. I'll just be chasing cows. :-) It was discussed and rejected, at least for now. See earlier discussion. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous Code Blocks as Lambdas?
Andrew Dunstan and...@dunslane.net writes: David E. Wheeler wrote: Very excited about the new `DO` command in 8.5a2. I read through the patch review thread and found that, like me, Dim had expected it to behave more like a lambda than a simple command. It was discussed and rejected, at least for now. See earlier discussion. A lambda facility would require being able to pass arguments and return results, which we intentionally left out of DO to keep it simple. By the time you add all that notation, it's far from clear that you shouldn't just define a function. Also, DO is (intended to be) optimized for execute-once behavior. A lambda block inside a query shouldn't assume that. So it would not be the same facility from either a syntax or an implementation standpoint. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: String key space for advisory locks
Why aren't you satisfied with hashtext('foo') ?
Collisions, mostly.
The restriction comes from LOCKTAG struct, in which
we can use only 3 * uint32 and 1 * uint16 for lock descriptor.
Yeah, that's a pretty hard limit. NM, we'll have to figure out some way
around it.
--Josh Berkus
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parsing config files in a directory
Robert Haas robertmh...@gmail.com writes: I'm not sure whether you're saying that I'm bringing this issue up in the wrong thread, or whether you disagree with the basic suggestion. The former --- whether we want to trim down the commentary in postgresql.conf seems to me to have nothing to do with what's being discussed in this thread. As Greg Smith already stated in a couple of ways, the issue here is about being able to support **simple** tools that make modifications to system-wide parameter settings. Removing default commentary from postgresql.conf does not help that at all. Removing the ability to use comments there at all might help, but if you haven't figured out yet that no such proposal will fly, I'm not sure how much clearer I can say it. The desire to not have a ridiculously high bar for config adjustment tools also seems to me to be plenty of reason to reject the various odd ideas we have seen like making tools go around and edit files other than the one they are chartered to put settings in. on this thread, you suggested dumping the initdb functions into a mostly-empty persistent.conf file that would be read after postgresql.conf. If we did that, then we would presumably advise people not to set settings in postgresql.conf because of the possibility that they would be overriden in persistent.conf, which begs the question of why we need two files at all. You are confusing who is in charge here. It's not the tool, it's the DBA, and anybody who thinks differently is going to keep losing arguments. I would actually suggest that it'd be better to put the include of persistent.conf first, with a comment (!) pointing out that any subsequent manual settings will override that. Some people will choose to use persistent.conf, some won't care to; and the main problem I'm seeing in this debate is the apparent desire to force the latter group to do what somebody else thinks is good for them. If you design a setup that can be used in multiple styles, including the old one, you'll have a lot better chance of getting it through. 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] Parsing config files in a directory
On 10/26/09 9:01 AM, Robert Haas wrote: (BTW, why do we actually need an includedir mechanism for this? A simple include of a persistent.conf file seems like it would be enough.) I was starting to wonder that, too. Different issue, really, which is that some people (including me) would like to break up PostgreSQL configuration into 7 or 8 files based on functional area (e.g. memory.conf, logging.conf, custom_options.conf ...). I do this with my Apache configs, and find it vastly more manageable than one big file, especially under SCM.If I write a config management tool, my tool will also do this. That's the reason for the dir, not persistent.conf, which I agree could be a single file. --Josh Berkus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [ANNOUNCE] PostgreSQL 8.5alpha2 Now Available
On mån, 2009-10-26 at 09:08 -0700, David E. Wheeler wrote: On Oct 24, 2009, at 10:44 AM, Peter Eisentraut wrote: More detail is available in the Release Notes included with each alpha: http://developer.postgresql.org/pgdocs/postgres/release-8.5.html That seems to just have alpha1 at the moment. Fixed. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] table corrupted
Jonah H. Harris jonah.har...@gmail.com wrote: Joshua D. Drake j...@commandprompt.comwrote: This reply is wholly inappropriate for a Pg list. We are here to help people. If you have a consultancy, please feel free to list that but any discussion of rates is just plain rude. Please use better discretion in the future. Perhaps. Though, I only posted because you made it sound somewhat impossible and because I only know of a few ppl in the PG community that offer it and/or have done is successfully. Maybe letting people know there are options, other than being screwed, is wrong... my bad :-) That really sounded disingenuous. It would probably be appropriate to point out that there are numerous sources of professional support for PostgreSQL. http://www.postgresql.org/support/professional_support As someone who ran a consulting business for 25 years and has been on both sides of the calls for assistance on recovery from database problems (and currently not in that business, so I have no ax to grind here), that post was inappropriate for the list. I don't think anyone would fault you (or anyone else) for making a polite offer of assistance off-list, as long as you quietly bow out if they're not interested; but anybody who is a professional in this business should know better than to post that to the list. There are several reasons, and they should be obvious. -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] Anonymous Code Blocks as Lambdas?
On Oct 26, 2009, at 1:16 PM, Pavel Stehule wrote: I have a idea about migration of outer (psql) variables, and custom shell variables. some like: psql --allow_custom_variables --table_name=mytable inside psql we should to use :table_name variable with mytable as content. then we can use syntax do (table_name varchar) $$ begin raise notice 'TABLENAME IS %', table_name; return; end; $$ so with this mechanism we can to simply parametrise plpgsql do scripts from outer environment. How is this different from psql :variables? And why would a `DO` feature be tied directly to psql? Confused, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous Code Blocks as Lambdas?
On Oct 26, 2009, at 1:21 PM, Tom Lane wrote: A lambda facility would require being able to pass arguments and return results, which we intentionally left out of DO to keep it simple. By the time you add all that notation, it's far from clear that you shouldn't just define a function. Well sometimes I want to do something like that as an expression, rather than having to write a separate statement that declares a function. Also, DO is (intended to be) optimized for execute-once behavior. A lambda block inside a query shouldn't assume that. So it would not be the same facility from either a syntax or an implementation standpoint. Perhaps lambda isn't the proper term. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parsing config files in a directory
On Mon, Oct 26, 2009 at 8:07 AM, Tom Lane t...@sss.pgh.pa.us wrote: (BTW, why do we actually need an includedir mechanism for this? A simple include of a persistent.conf file seems like it would be enough.) Actually I think the include directory came from another use case which we've also discussed. Namely modules which need some configuration themselves. So for example when you install PostGIS it could drop a postgis.conf in the directory which you could then either edit yourself or override with SET PERSISTENT. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parsing config files in a directory
On Mon, Oct 26, 2009 at 4:33 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I'm not sure whether you're saying that I'm bringing this issue up in the wrong thread, or whether you disagree with the basic suggestion. The former --- whether we want to trim down the commentary in postgresql.conf seems to me to have nothing to do with what's being discussed in this thread. As Greg Smith already stated in a couple of ways, the issue here is about being able to support **simple** tools that make modifications to system-wide parameter settings. Removing default commentary from postgresql.conf does not help that at all. [ shrug ] I don't particularly agree; but perhaps we can agree to disagree on this point. For what it's worth, the thing that originally got me involved in this thread was the following sentence in Magnus' OP. The idea being that something like a tuning tool, or pgadmin, for example can drop and modify files in this directory instead of modifying the main config file (which can be very hard to machine-parse). I presume this sentence must refer to the aforementioned commentary, because what else is there in that file that could be hard to parse? Or that would be any different in an include-dir file? Removing the ability to use comments there at all might help, but if you haven't figured out yet that no such proposal will fly, I'm not sure how much clearer I can say it. I agree that that would be a bad design, which is why I did not suggest it. The desire to not have a ridiculously high bar for config adjustment tools also seems to me to be plenty of reason to reject the various odd ideas we have seen like making tools go around and edit files other than the one they are chartered to put settings in. I agree completely. on this thread, you suggested dumping the initdb functions into a mostly-empty persistent.conf file that would be read after postgresql.conf. If we did that, then we would presumably advise people not to set settings in postgresql.conf because of the possibility that they would be overriden in persistent.conf, which begs the question of why we need two files at all. You are confusing who is in charge here. It's not the tool, it's the DBA, and anybody who thinks differently is going to keep losing arguments. I would actually suggest that it'd be better to put the include of persistent.conf first, with a comment (!) pointing out that any subsequent manual settings will override that. Some people will choose to use persistent.conf, some won't care to; and the main problem I'm seeing in this debate is the apparent desire to force the latter group to do what somebody else thinks is good for them. If you design a setup that can be used in multiple styles, including the old one, you'll have a lot better chance of getting it through. What you're proposing here will work with both styles, but it might sometimes exhibit the rather surprising behavior that SET PERSISTENT appears to work but doesn't actually do anything, with no clear warning to the user of what has gone wrong. It seems like it would be better to have some kind of a hard switch - e.g. if postgresql.conf.auto exists, then we read settings that file, and SET PERSISTENT updates it. If not, then we read the regular postgresql.conf file, and any attempt to SET PERSISTENT fails with a suitably informative error message. ...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] Anonymous Code Blocks as Lambdas?
2009/10/26 David E. Wheeler da...@kineticode.com: On Oct 26, 2009, at 1:16 PM, Pavel Stehule wrote: I have a idea about migration of outer (psql) variables, and custom shell variables. some like: psql --allow_custom_variables --table_name=mytable inside psql we should to use :table_name variable with mytable as content. then we can use syntax do (table_name varchar) $$ begin raise notice 'TABLENAME IS %', table_name; return; end; $$ so with this mechanism we can to simply parametrise plpgsql do scripts from outer environment. How is this different from psql :variables? is is psql variables. And why would a `DO` feature be tied directly to psql? it should be light relation. 'DO' should be parametrised, and psql can use own variables as 'DO' parameters. Confused, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parsing config files in a directory
On Mon, Oct 26, 2009 at 1:40 PM, Josh Berkus j...@agliodbs.com wrote: Different issue, really, which is that some people (including me) would like to break up PostgreSQL configuration into 7 or 8 files based on functional area (e.g. memory.conf, logging.conf, custom_options.conf ...). I do this with my Apache configs, and find it vastly more manageable than one big file, especially under SCM. If I write a config management tool, my tool will also do this. This actually seems like a bad idea to me. It's fine for something like apache virtual hosts where there's no ambiguity and in any case it's you organizing it and you reading it back out. But for a tool to do this is only going to lead to confusion when my thinking of where to find the variables differs from yours. That's the reason for the dir, not persistent.conf, which I agree could be a single file. Well you're assuming there's only one tool generating this config? We have at least two and possibly more. initdb generates an initial set of defaults, the user may well run some kind of autotuning program, and then they also have variables set by SET PERSISTENT. That's three pieces of configuration being edited by different pieces of software. The only way that will stay sane will be if each piece of software has its own file to dump its own configuration into. If they start editing each others configuration it'll all be one big pile of non-deterministic spaghetti. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parsing config files in a directory
+1 Would you make it +2? -- dim Le 26 oct. 2009 à 19:15, Greg Stark gsst...@mit.edu a écrit : On Sat, Oct 24, 2009 at 6:55 PM, Tom Lane t...@sss.pgh.pa.us wrote: I think we should have an explicit include-directory directive, and the reason I think so is that it makes it fairly easy for the user to control the relative precedence of the manual settings (presumed to still be kept in postgresql.conf) and the automatic settings (presumed to be in files in the directory). Manual settings before the include are overridable, those after are not. I think we can actually aim higher now. We don't need nearly as many degrees of freedom as people seem to be suggesting. And in this space degrees of freedom just mean the ability to have confusing configurations that surprise users. I would suggest the following: The system always scans postgresql.conf and postgresql.conf.d in the same location. We can support include and includedir directives though I think they would be mostly unnecessary. But they would be purely for the purpose of organizing your files and adding additional locations, not replacing the standard locations. They might be useful for, for example, having a site-wide set of defaults which are loaded before the cluster-specific files. postgresql.conf settings override postgresql.conf.d settings. postgresql.conf should no longer be a place for tools to automatically edit, and ideally it should be shipped empty so anything there is an explicit manual instruction from a sysadmin and should override anything installed by a package or tool. When scanning postgresql.conf.d we should follow the Apache/Debian standard of scanning only files which match a single simple hard-coded template. I think the convention is basically the regexp ^[0-9a-zA-Z-]*.conf$. It's important that it exclude typical backup file conventions like foo~ or foo.bak and lock file conventions like .#foo. There's no need for this to be configurable and I think that would be actively harmful. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] GROUP BY bug or feature?
Hi,
we have come across a problem where we need an inverted index,
an array of IDs ordered by another condition. We came up
with this scheme:
-- final inverted index
CREATE TABLE product.t_product_inv (
wordtextprimary key not null,
ids bigint[]
);
-- transition table, word contains a single lexeme
-- from an original table's description field
CREATE TABLE product.t_product_inv0 (
wordtextnot null,
id bigint not null,
price numeric -- not null
);
CREATE INDEX t_product_inv0_idx ON product.t_product_inv0 (word, price
NULLS FIRST, id);
CREATE OR REPLACE FUNCTION array_append_1(bigint[], numeric, bigint)
RETURNS bigint[] AS $$select array_append($1, $3)$$ LANGUAGE SQL;
CREATE AGGREGATE array_accum_1 (numeric, bigint)
(
sfunc = array_append_1,
stype = bigint[],
initcond = '{}'
);
I would like the get the list of IDs ordered by the price field:
INSERT INTO product.t_product_inv
SELECT word, array_accum_1(price, id) FROM product.t_product_inv0
GROUP BY word ORDER BY word, price NULLS FIRST, id;
However, I get an error:
ERROR: column t_product_inv0.price must appear in the GROUP BY clause
or be used in an aggregate function
LINE 3: GROUP BY word ORDER BY word, price NULLS FIRST, id;
^
The condition in the error message is true, the field price
is indeed used in an aggregate function but ignored on purpose
in the sfunc. So I obviously didn't expect the error to happen.
Is the bug in our approach or in the check for the
GROUP BY/ORDER BY/aggregated variables?
This was tested on 8.4.1, 8.5CVS from 20090930 and from today.
Best regards,
Zoltán Böszörményi
--
Bible has answers for everything. Proof:
But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil. (Matthew 5:37) - basics of digital technology.
May your kingdom come - superficial description of plate tectonics
--
Zoltán Böszörményi
Cybertec Schönig Schönig GmbH
http://www.postgresql.at/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parsing config files in a directory
On Mon, 26 Oct 2009, Greg Stark wrote: Actually I think the include directory came from another use case which we've also discussed. Namely modules which need some configuration themselves. So for example when you install PostGIS it could drop a postgis.conf in the directory which you could then either edit yourself or override with SET PERSISTENT. For modules that want to touch custom_variable_classes, they would still need to touch the global config. While there was some discussion about the ability to append/prepend/filter search_path, is there something simpler (because order doesn't matter) we can do for custom_variable_classes? Kris Jurka -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] toast.fillfactor is documented but not recognized?
Hi, I tried to utilize the advertised feature of 8.4, the separate fillfactor setting for the toast table. o=# create table t2 (id serial primary key, t text) with (fillfactor=75, toast.fillfactor=60); NOTICE: CREATE TABLE will create implicit sequence t2_id_seq for serial column t2.id ERROR: unrecognized parameter fillfactor The same error happens on 8.4.1 and 8.5CVS. The error disappears only if I omit the toast.fillfactor setting completely. Best regards, Zoltán Böszörményi -- Bible has answers for everything. Proof: But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil. (Matthew 5:37) - basics of digital technology. May your kingdom come - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous Code Blocks as Lambdas?
On Oct 26, 2009, at 2:12 PM, Pavel Stehule wrote: it should be light relation. 'DO' should be parametrised, and psql can use own variables as 'DO' parameters. I see, because `DO` is a statement, not an expression. Thus arguments don't really make much sense (I wish it was an expression!). I don't think it's a good idea to tie SQL syntax to a feature of a client, though. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous Code Blocks as Lambdas?
David E. Wheeler wrote: On Oct 26, 2009, at 2:12 PM, Pavel Stehule wrote: it should be light relation. 'DO' should be parametrised, and psql can use own variables as 'DO' parameters. I see, because `DO` is a statement, not an expression. Thus arguments don't really make much sense (I wish it was an expression!). I don't think it's a good idea to tie SQL syntax to a feature of a client, though. Me either. I think we need to take this more slowly. Frankly, I think we have most of what we really wanted already, and I suspect anything else is probably not worth the code complexity involved. The original motivation as I understood it was to enable people to embed a piece of pl/foo in a script with minimal syntactic overhead, and I think that's been achieved. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] per-tablespace random_page_cost/seq_page_cost
Robert, As to (1), my thought is to add two new float8 columns to pg_tablespace. The naming is a little awkward, because random_page_cost and seq_page_cost would not fit with our (rather odd) convention for naming system catalog columns. I'm tempted to call them spcrandompagecost and spcseqpagecost, but I wonder if anyone has any strong preferences. I'm thinking an array, in case we want to make other tablespace cost parameters in the future.* Or, better, whatever structure we're currently using for ROLEs. (* for example, if someone does manage a filesystem with a separate cache space per mount, then we'd want effective_cache_size to be tablespace-based as well) --Josh -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] toast.fillfactor is documented but not recognized?
Boszormenyi Zoltan wrote: Hi, I tried to utilize the advertised feature of 8.4, the separate fillfactor setting for the toast table. o=# create table t2 (id serial primary key, t text) with (fillfactor=75, toast.fillfactor=60); NOTICE: CREATE TABLE will create implicit sequence t2_id_seq for serial column t2.id ERROR: unrecognized parameter fillfactor We explicitely disallow setting fillfactor on toast tables. -- 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] toast.fillfactor is documented but not recognized?
On Mon, 2009-10-26 at 19:11 -0300, Alvaro Herrera wrote: We explicitely disallow setting fillfactor on toast tables. So should that be made more clear in the documentation? http://www.postgresql.org/docs/8.4/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS Looking at that page briefly I would assume that it could be set. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parsing config files in a directory
Greg, This actually seems like a bad idea to me. You write your tool your way, I'll write my tool mine. We'll see which one works the best in the field. Well you're assuming there's only one tool generating this config? We have at least two and possibly more. initdb generates an initial set of defaults, the user may well run some kind of autotuning program, and then they also have variables set by SET PERSISTENT. That's three pieces of configuration being edited by different pieces of software. Well, that's what I'd call a bad idea. Mixing external autotuner which writes to files with SET PERSISTENT? --Josh Berkus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GROUP BY bug or feature?
Boszormenyi Zoltan z...@cybertec.at writes: INSERT INTO product.t_product_inv SELECT word, array_accum_1(price, id) FROM product.t_product_inv0 GROUP BY word ORDER BY word, price NULLS FIRST, id; However, I get an error: ERROR: column t_product_inv0.price must appear in the GROUP BY clause or be used in an aggregate function LINE 3: GROUP BY word ORDER BY word, price NULLS FIRST, id; ^ The condition in the error message is true, the field price is indeed used in an aggregate function but ignored on purpose in the sfunc. So I obviously didn't expect the error to happen. It is not complaining about the use in the aggregate. It is complaining about the un-aggregated use in ORDER BY. Notice the error pointer. 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] Anonymous Code Blocks as Lambdas?
2009/10/26 David E. Wheeler da...@kineticode.com: On Oct 26, 2009, at 2:12 PM, Pavel Stehule wrote: it should be light relation. 'DO' should be parametrised, and psql can use own variables as 'DO' parameters. I see, because `DO` is a statement, not an expression. Thus arguments don't really make much sense (I wish it was an expression!). uff. How you would to write scripts? How you would to join client side and server side? What I know, statements in PostgreSQL are parametrised - INSERT, SELECT and others. I don't think it's a good idea to tie SQL syntax to a feature of a client, though. DO should have any syntax. Other than I wrote. It isn't important in this moment. Do without parametrsation has not full power. It is like EXECUTE without USING clause. Sure. You can live without it, but the live with it is much more confortable. So now we have isolated PL fragment without any outer inputs. Regards Pavel Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Unicode UTF-8 table formatting for psql text output
On Mon, Oct 26, 2009 at 01:33:19PM -0400, Tom Lane wrote: Greg Stark gsst...@mit.edu writes: While i agree this looks nicer I wonder what it does to things like excel/gnumeric/ooffice auto-recognizing table layouts and importing files. I'm not sure our old format was so great for this so maybe this is actually an improvement I'm asking for. Yeah. We can do what we like with the UTF8 format but I'm considerably more worried about the aspect of making random changes to the plain-ASCII output. On the other hand, we changed that just a release or so ago (to put in the multiline output in the first place) and I didn't hear complaints about it that time. I checked (using strace) gnumeric (via libgda and gnome-database-properties) openoffice (oobase) Both spreadsheets require a connection to be set up first for them to use as a handle, so I did that and traced from there. Neither made any use of psql; they both appear to use libpq via their respective database abstraction libs--no forking of any children observed. Excel is a bit tougher, I bought my first copy last week for other reasons, but I lack both windows expertise and debugging tools to trace things, and I also dual boot my computer with the postgres install on the Linux partition, making connecting to the database rather hard! I think someone else is better suited to check this one! On a related note, there's something odd with the pager code. The output of \l with the pager off: rleigh=# \l List of databases Name │ Owner │ Encoding │ Collation │Ctype│ Access privileges ─┼──┼──┼─┼─┼─── [...] (header line is 91 characters, 273 bytes) And with the pager on: rleigh=# \l List of databases Name │ Owner │ Encoding │ Collation │Ctype│ Access privileges ─┼──┼──┼─┼─┼─ ��─ [...] (longest header line 85 characters, 255 bytes, 256 bytes inc. LF, remainder on second line) Note that the pager wasn't required and so wasn't actually invoked, but the output was corrupted. A newline was inserted almost at the end of the line and the continuation lacks a leading \342 which (since these UTF-8 codes are all three-byte) leads to two bytes which are invalid UTF-8. Since this spurious newline got inserted exactly on a 256 byte boundary, I was wondering if there was some buffer either internal to psql or in the termios/pty layer that was getting flushed. It also lost the first byte of the second line (possibly swapped for the \n). Another wierdness: it only happens if the terminal width is 85 columns wide, otherwise it just wraps around as one would expect! AFAICT there are no 255/256 length buffers in the code, and the code doing the printing is just doing stdio to fout which is either stdout or a pipe! Because of this, I can't see how the spurious \n appears in the middle of a simple loop. If border=2, you'll see this for all top mid and bottom ruled lines. I do see strace showing some termios fiddling, could that be at fault or is that just readline ncurses initialisation? Regards, Roger -- .''`. Roger Leigh : :' : Debian GNU/Linux http://people.debian.org/~rleigh/ `. `' Printing on GNU/Linux? http://gutenprint.sourceforge.net/ `-GPG Public Key: 0x25BFB848 Please GPG sign your mail. signature.asc Description: Digital signature
Re: [HACKERS] Unicode UTF-8 table formatting for psql text output
Roger Leigh rle...@codelibre.net writes: On Mon, Oct 26, 2009 at 01:33:19PM -0400, Tom Lane wrote: Yeah. We can do what we like with the UTF8 format but I'm considerably more worried about the aspect of making random changes to the plain-ASCII output. I checked (using strace) gnumeric (via libgda and gnome-database-properties) openoffice (oobase) Even if that were the entire universe of programs we cared about, whether their internal ODBC logic goes through psql isn't really the point here. What I'm worried about is somebody piping the text output of psql into another program. On a related note, there's something odd with the pager code. Hm, what platform are you testing that on? 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] Anonymous Code Blocks as Lambdas?
Pavel Stehule wrote: 2009/10/26 David E. Wheeler da...@kineticode.com: On Oct 26, 2009, at 2:12 PM, Pavel Stehule wrote: it should be light relation. 'DO' should be parametrised, and psql can use own variables as 'DO' parameters. I see, because `DO` is a statement, not an expression. Thus arguments don't really make much sense (I wish it was an expression!). uff. How you would to write scripts? How you would to join client side and server side? What I know, statements in PostgreSQL are parametrised - INSERT, SELECT and others. They are not arbitrarily parameterized at all. Just try using a parameter for a table name. And not all statements accept parameters. You are making a quite false comparison here. I don't think it's a good idea to tie SQL syntax to a feature of a client, though. DO should have any syntax. Other than I wrote. It isn't important in this moment. Do without parametrsation has not full power. It is like EXECUTE without USING clause. Sure. You can live without it, but the live with it is much more confortable. There is no proof at all of this. We have not even released this feature into the field and already you are declaring it inadequate. That declaration is at best premature. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Unicode UTF-8 table formatting for psql text output
On Mon, Oct 26, 2009 at 07:19:24PM -0400, Tom Lane wrote:
Roger Leigh rle...@codelibre.net writes:
On Mon, Oct 26, 2009 at 01:33:19PM -0400, Tom Lane wrote:
Yeah. We can do what we like with the UTF8 format but I'm considerably
more worried about the aspect of making random changes to the
plain-ASCII output.
I checked (using strace)
gnumeric (via libgda and gnome-database-properties)
openoffice (oobase)
Even if that were the entire universe of programs we cared about,
whether their internal ODBC logic goes through psql isn't really
the point here. What I'm worried about is somebody piping the
text output of psql into another program.
On a related note, there's something odd with the pager code.
Hm, what platform are you testing that on?
Debian GNU/Linux (unstable)
linux2.6.30
eglibc 2.10.1
libreadline6 6.0.5
libncurses5 5.7
gcc 4.3.4
This is the trace of the broken write:
16206 write(1, Name \342\224\202 Owner \342\224..., 102) = 102
16206 write(1,
\342\224\200\342\224\200\342\224\200\342\224\200\342\224\200\342\224\200\342\224\200\342\224\200\342\224\200\342\224\200\342
\224..., 256) = 256
16206 write(1,
\224\200\342\224\200\342\224\200\342\224\200\342\224\200\342\224\200\n, 18) =
18
I'll attach the whole thing for reference. What's clear is that the
first write was *exactly* 256 bytes, which is what was requested,
presumably by libc stdio buffering (which shouldn't by itself be a
problem). Since we use 3-byte UTF-8 and 256/3 is 85 + 1 remainder,
this is where the wierd 85 char forced newline comes from. Since it
only happens when the terminal window is 85 chars, that's where I'm
assuming some odd termios influence comes from (though it might just
be the source of the window size and be completely innocent). The
fact that libc did the two separate writes kind of rules out termios
mangling the output post-write().
Regards,
Roger
--
.''`. Roger Leigh
: :' : Debian GNU/Linux http://people.debian.org/~rleigh/
`. `' Printing on GNU/Linux? http://gutenprint.sourceforge.net/
`-GPG Public Key: 0x25BFB848 Please GPG sign your mail.
16203 execve(./psql, [./psql], [/* 43 vars */]) = 0
16203 brk(0)= 0x1dbb000
16203 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0)
= 0x7ff0b8697000
16203 access(/etc/ld.so.nohwcap, F_OK) = -1 ENOENT (No such file or directory)
16203 mmap(NULL, 8192, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0)
= 0x7ff0b8695000
16203 access(/etc/ld.so.preload, R_OK) = -1 ENOENT (No such file or directory)
16203 open(/usr/local/pgsql/lib/tls/x86_64/libpq.so.5, O_RDONLY) = -1 ENOENT
(No such file or directory)
16203 stat(/usr/local/pgsql/lib/tls/x86_64, 0x7fffeefa1f10) = -1 ENOENT (No
such file or directory)
16203 open(/usr/local/pgsql/lib/tls/libpq.so.5, O_RDONLY) = -1 ENOENT (No
such file or directory)
16203 stat(/usr/local/pgsql/lib/tls, 0x7fffeefa1f10) = -1 ENOENT (No such
file or directory)
16203 open(/usr/local/pgsql/lib/x86_64/libpq.so.5, O_RDONLY) = -1 ENOENT (No
such file or directory)
16203 stat(/usr/local/pgsql/lib/x86_64, 0x7fffeefa1f10) = -1 ENOENT (No such
file or directory)
16203 open(/usr/local/pgsql/lib/libpq.so.5, O_RDONLY) = -1 ENOENT (No such
file or directory)
16203 stat(/usr/local/pgsql/lib, 0x7fffeefa1f10) = -1 ENOENT (No such file or
directory)
16203 open(/etc/ld.so.cache, O_RDONLY) = 3
16203 fstat(3, {st_mode=S_IFREG|0644, st_size=115844, ...}) = 0
16203 mmap(NULL, 115844, PROT_READ, MAP_PRIVATE, 3, 0) = 0x7ff0b8678000
16203 close(3) = 0
16203 access(/etc/ld.so.nohwcap, F_OK) = -1 ENOENT (No such file or directory)
16203 open(/usr/lib/libpq.so.5, O_RDONLY) = 3
16203 read(3,
\177ELF\2\1\1\0\0\0\0\0\0\0\0\0\3\0\0\1\0\0\0\0\214\0\0\0\0\0\0..., 832) =
832
16203 fstat(3, {st_mode=S_IFREG|0644, st_size=161848, ...}) = 0
16203 mmap(NULL, 2257336, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0)
= 0x7ff0b845
16203 mprotect(0x7ff0b8475000, 2097152, PROT_NONE) = 0
16203 mmap(0x7ff0b8675000, 12288, PROT_READ|PROT_WRITE,
MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0x25000) = 0x7ff0b8675000
16203 close(3) = 0
16203 access(/etc/ld.so.nohwcap, F_OK) = -1 ENOENT (No such file or directory)
16203 open(/lib/libreadline.so.5, O_RDONLY) = 3
16203 read(3,
\177ELF\2\1\1\0\0\0\0\0\0\0\0\0\3\0\0\1\0\0\0pMa\3157\0\0\0..., 832) = 832
16203 fstat(3, {st_mode=S_IFREG|0644, st_size=260632, ...}) = 0
16203 mmap(0x37cd60, 2358528, PROT_READ|PROT_EXEC,
MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 0x37cd60
16203 mprotect(0x37cd638000, 2093056, PROT_NONE) = 0
16203 mmap(0x37cd837000, 32768, PROT_READ|PROT_WRITE,
MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0x37000) = 0x37cd837000
16203 mmap(0x37cd83f000, 3328, PROT_READ|PROT_WRITE,
MAP_PRIVATE|MAP_FIXED|MAP_ANONYMOUS, -1, 0) = 0x37cd83f000
16203 close(3) = 0
16203 access(/etc/ld.so.nohwcap,
