Re: [HACKERS] Getting a move on for 8.2 beta
Tom Lane wrote: AFAICS the bottom line here is that we need some intelligent filtering. In the short run I doubt that we can have that except through human gruntwork to filter the mail traffic and update a tracker database. Maybe after we see such a system in operation for awhile, we can start to automate some obvious bits. But if we start with the assumption that it's going to be mostly automated on day zero, I predict a resounding failure. I agree 100%. Lets start off with grunt workers doing their magic in parallel with whatever systems we currently have. They will one by one figure out what to automate, what cannot be automated, and maybe provide value that is promising enough for people to slightly modify their modus operanti for those aspects that cannot be automated. However there will probably always be a great deal of grunt work. Again Email's are great for discussions and I think its great to link up discussions with a bug or issue tracker id. However Email discussions also often go in circles, are side tracked by IRC discussions etc. So its really hard to figure out what decisions have been made if you look things up later on. So the task of the grunt workers is to make sure that there is a summary of the relevant information available, even if all they do is flag the important decision emails. regards, Lukas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Getting a move on for 8.2 beta
Andrew Dunstan wrote: I spent months on a working party on these and similar issues a few years back, in a commercial setting. One of the big issues is when you start tracking something. Bugs are a pretty simple case. Features are much harder to handle. Someone comes up with an idea. There is a lot of discussion. a consensus is arrived at to go forward. I think that's the point at which we start tracking, but it's a judgement call. What is we decide not to go ahead? Do we capture that in the tracker (with a resolution of rejected)? Exactly its a judgement call. The idea would be to try and pick up each of the proposals in the discussion, summarize them in the issue tracker or via a link to the wiki. This way people do not argue in circles all that much (hopefully) and there is something to vote on. More importantly there is something to point to if the topic ever comes up again and the previous discussion did not lead to a decision. The classic read the archives is just very suboptimal. regards, Lukas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] @ versus ~, redux
On Sep 4, 2006, at 12:44 , Tom Lane wrote: OK, so if everyone is leaning to #3, the name game remains to be played. Do we all agree on this: x @ y means x contains y x @ y means x is contained in y Are we all prepared to sign a solemn oath to commit hara-kiri if we invent a new datatype that gets this wrong? No? Maybe these still aren't obvious enough. When I've been working on range/interval stuff, I tried to come up with a self-consistent set of operator symbols for the Allen operators, which includes the contains and is contained in pair. Here's what I came up with. Where r1 and r2 are ranges r1 r2r1 is strictly during r2, i.e., r1 is a strict subset of r2 r1 r2r2 is strictly during r1, i.e., r2 is a strict subset of r1 and are meant to evoke the (strict) subset (⊂ or sub;) and superset (⊃ or sup;) operators. r1 = r2 r1 is a superset of r2 r1 = r2 r1 is a subset of r2 = and = are mean to evoke the subset (⊆ or sube;) and superset (⊇ or supe;) operators. Assuming the meaning of contains and is contained in is inclusive (rather than strict), then we'd have a = b : a contains b a = b : a is contained by b I've included the other Allen operators at the bottom for completeness. Michael Glaesemann grzm seespotcode net r1 = r2 r1 equals r2 r1 r2r1 does not equal r2 For the following, the or indicates the relative position of the two ranges if they were depicted on an line that increases from left to right. r1 | r2r1 strictly meets r2, i.e., begin(r2) is next(end(r1)) r1 | r2r2 strictly meets r2, i.e., begin(r1) is next(end(r2)) The | is meant to evoke the meeting point of r1 and r2. They don't overlap, they are just abutting. The or points to the direction the of the range it points to relative to the other range, i.e., r1 is to the left of r2 on an line that increases from left to right. r1 / r2r1 is before r2 r1 / r2r1 is after r2 The / is meant to evoke the fact that they are not abutting. r1 r2r1 strictly overlaps r2 r1 r2r2 strictly overlaps r1 The is meant to evoke and, in that there is something the two ranges share. r1 @ r2r1 starts r2 r1 @ r2r2 starts r1 r1 @ r2r1 finishes r2 r1 @ r2r2 finishes r1 The @ is meant to indicate the point where the two ranges share a begin or end point. E.g., for r1 @ r2, r1 and r2 start together, and end(r1) end(r2). For r1 @ r2, begin(r1) begin(r2), but they share the same end point. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Developer's Wiki
On Sun, Sep 03, 2006 at 08:30:13PM -0700, Neil Conway wrote: Martijn van Oosterhout said: Ok, it looks like pages can be arranged hierarchically. Well, a prefix like Todo: is not the incantation one needs to use to arrange pages in hierarchies. You probably want / to indicate a subpage: i.e. Parent/Child. See http://meta.wikimedia.org/wiki/Help:Link#Subpage_feature It also says it's not enabled by default. Is it enabled? I was actually hoping for more feedback on the content itself. I'm still not clear if it's supposed to be developers only - to the exclusion of users or developers only - but accessable to anyone. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] gBorg status?
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Christopher Browne Sent: 04 September 2006 03:55 To: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] gBorg status? The fact that it has been out for a week, without any public comment being made, certainly gives me pause. I *HOPE* that we can still recover CVS and email. My understanding is that Gborg is being recovered from backup as I type. I also understand that the delay was not caused by lack of backups or anything similarly scary, but simply by other priorities. Regards, Dave. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] @ versus ~, redux
On 2006-09-04, Tom Lane [EMAIL PROTECTED] wrote: OK, so if everyone is leaning to #3, the name game remains to be played. Do we all agree on this: x @ y means x contains y x @ y means x is contained in y While I suggested something like those, I would also suggest that the existing operators for inet/cidr be taken into consideration: x = y x contains y x y x strictly contains y x = y x is contained in y x y x is strictly contained in y (obviously these don't all necessarily make sense for all types) These have the advantage of resembling set notation more closely and being in use in one existing core type. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] @ versus ~, redux
Tom Lane ha scritto: OK, so if everyone is leaning to #3, the name game remains to be played. Do we all agree on this: x @ y means x contains y x @ y means x is contained in y Are we all prepared to sign a solemn oath to commit hara-kiri if we invent a new datatype that gets this wrong? No? Maybe these still aren't obvious enough. Does this mean that also contrib/ltree operators will likely change for consistency? ltree @ ltree - returns TRUE if left argument is an ancestor of right argument (or equal). ltree @ ltree - returns TRUE if left argument is a descendant of right argument (or equal). Best regards -- Matteo Beccati http://phpadsnew.com http://phppgads.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Optimizing prepared statements
On Mon, Sep 04, 2006 at 11:12:13AM +0700, Jeroen T. Vermeulen wrote: As I've said before, all this falls down if there is a significant cost to keeping one or two extra plans per prepared statement. You mentioned something about tracking plans. I don't know what that means, but it sounded like it might impose a runtime cost on keeping plans around. I think what he meant is tracking plans during the planning process. Currently at the end of each step you weed out all the plans that arn't the best for each path-key. To track multiple results at that stage would be expensive. However, just running the planner over the same query multiple times with different estimates shouldn't be too expensive to store. However, you're discussing the process of replanning based on changes in variables. At the moment we really need to work on replanning generally, it isn't done at all currently... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] @ versus ~, redux
Matteo Beccati [EMAIL PROTECTED] writes: Tom Lane ha scritto: OK, so if everyone is leaning to #3, the name game remains to be played. Do we all agree on this: x @ y means x contains y x @ y means x is contained in y Are we all prepared to sign a solemn oath to commit hara-kiri if we invent a new datatype that gets this wrong? No? Maybe these still aren't obvious enough. Does this mean that also contrib/ltree operators will likely change for consistency? ltree @ ltree - returns TRUE if left argument is an ancestor of right argument (or equal). ltree @ ltree - returns TRUE if left argument is a descendant of right argument (or equal). If you consider ltree entries to be sets containing all their children then those sound consistent. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] GRANT role docs inconsistency
GRANT role [, ...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH
ADMIN
OPTION ]
It doesn't say that anymore:
http://archives.postgresql.org/pgsql-committers/2006-
08/msg00034.php
Pfft. I need to remember to check development docs as well :-) sorry
about the noise.
Perhaps we ought to start thinking about an 8.1 update release ---
we haven't had any forcing functions like security issues for
awhile, but we've accumulated a fair number of plain ol' bug fixes.
If nothing else, it should IMHO be scheduled to happen well before the
8.2 release, simply to clear the queue before that one. We can still
end up with the need to have an urgent release because of a security
issue or something, but it's probably a good idea not to have a
scheduled release in the stable branches at the same time as the new
major version...
//Magnus
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] WIP archive_timeout patch
On Fri, 2006-08-18 at 08:52 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: On Thu, 2006-08-17 at 19:11 -0400, Tom Lane wrote: I noticed a minor annoyance while testing: when the system is completely idle, you get a forced segment switch every checkpoint_timeout seconds, even though there is nothing useful to log. The checkpoint code is smart enough not to do a checkpoint if nothing has happened since the last one, and the xlog switch code is smart enough not to do a switch if nothing has happened since the last one ... but they aren't talking to each other and so each one's change looks like something happened to the other one. I noticed that minor annoyance and understood that I had fixed it before submitting. That was the reason for putting the code in bgwriter to check whether the pointer had moved before attempting the switch... perhaps that functionality has been removed? No, the original form of the patch was equally vulnerable. AFAICS the only way to prevent this would be for XLogRequestSwitch (or really XLogInsert, which does the heavy lifting for this) to suppress a switch if the current segment is empty *or* contains only a checkpoint WAL record. Basically it'd have to pretend the checkpoint record is not there. This is doable but seems a bit weird --- in particular, that would mean that pg_switch_xlog sometimes returns a pointer less than pg_current_xlog_location, which might confuse backup scripts. On the whole I'm leaning towards not changing it. As Florian mentioned, guaranteed segment-every-checkpoint isn't completely without its uses. And people who are looking for low WAL volume ought to be stretching out their checkpoint intervals anyway. Agreed. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Postgres tracking - the pgtrack project
Right now, the release notes show a list of all the significant items in each release, but it isn't available until the release, and it isn't complete (because it would be unreadable by ordinary users). And there is no tracking of individual items in progress except by individual developers. Magnus, for example, tracks Win32 items, and Tom tracks backend stuff. I track whatever no one else tracks. Well, I *try*. I find myself not managing quite as well as I'd like ;-) So I for one would definitely appreciate a tool that would help with that - provided that the tool fits the development model, not the other way around. (most other things seems to have been said already this time around, so I won't repeat arguments others have made) //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] @ versus ~, redux
Gregory Stark [EMAIL PROTECTED] writes: Matteo Beccati [EMAIL PROTECTED] writes: Tom Lane ha scritto: x @ y means x is contained in y ltree @ ltree If you consider ltree entries to be sets containing all their children then those sound consistent. Oops, sorry for the noise. -- greg ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] autoconf version for back branches?
Am Montag, 4. September 2006 03:57 schrieb Andrew Dunstan: Ah! Thanks! What had failed for me was just running with /path/to/old/autoconf - this one works however. Strange that a config package can't work out where its own installed files are. I had that fixed in Autoconf a while back for this very reason. It certainly works with 2.59 but apparently not in that older version. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] python / 7.4 / FC5 / x86_64
Am Montag, 4. September 2006 04:06 schrieb Andrew Dunstan: Patch attached - seems to work on my FC5/x86_64 box. Also contains the OSX fix backported. Not sure that it qualifies as small though :-) It looks pretty scary to me. Didn't we say once that we don't want to backport fixes for platforms that didn't exist at the time of first release? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Getting a move on for 8.2 beta
Am Montag, 4. September 2006 04:10 schrieb Bruce Momjian: Are you saying you don't like the patch, That's it. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] TODO Item : GRANT/REVOKE to all schema objects
Folks, Because of a broken wrist, I won't be able to type much. 1. Is there any discussion being going to regrading the: %Allow GRANT/REVOKE permissions to be applied to all schema objects with one command 2. I took a brief look at gramm.y, would it be okay to create a new section like GrantRoleStmt: for the todo item? (Tom? Alvaro?) Regrads, Gevik. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Interval month, week - day
On Sun, Sep 03, 2006 at 10:21:11PM -0400, Bruce Momjian wrote:
When I tried the ecpg regression tests it complained there was no
results/ directory. I created one and it worked.
Hmm, anyone else experiencing this? The pg_regress.sh has this code that
should create it:
outputdir=results/
if [ ! -d $outputdir ]; then
mkdir -p $outputdir || { (exit 2); exit; }
fi
Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED]
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] @ versus ~, redux
x @ y means x is contained in y ltree @ ltree If you consider ltree entries to be sets containing all their children then those sound consistent. Now we get to decide whether @ was better than the now proposed @ :-) I like @. (or we stay clear by using the inet ops) Andreas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] Documentation fix for --with-ldap
Am Montag, 4. September 2006 10:23 schrieb Albe Laurenz: This is just a 'one line' change in the documentation of the --with-ldap flag of ./configure Well, if you want to link from the configure option to the place where the feature is explained, then that should be done consistently for all options. That might bloat the installation instructions, though. Not sure. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] insert/update/delete returning and rules
With this approach, you still have to update your rules if you want to support RETURNING on your views --- but if you don't update them, you don't have a security hole. Basically the standard setup for an updatable view would use ON INSERT DO INSTEAD INSERT INTO ... RETURNING ... where today you don't write any RETURNING. I like that approach. And if the sections allow CASE WHEN it should be possible to cover all use cases efficiently. Andreas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] Interval month, week - day
Michael Meskes [EMAIL PROTECTED] writes:
On Sun, Sep 03, 2006 at 10:21:11PM -0400, Bruce Momjian wrote:
When I tried the ecpg regression tests it complained there was no
results/ directory. I created one and it worked.
Hmm, anyone else experiencing this? The pg_regress.sh has this code that
should create it:
outputdir=results/
if [ ! -d $outputdir ]; then
mkdir -p $outputdir || { (exit 2); exit; }
fi
I'll bet you should lose the slash in $outputdir. test(1) might or
might not be friendly about stripping that off.
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [HACKERS] @ versus ~, redux
Matteo Beccati [EMAIL PROTECTED] writes: Tom Lane ha scritto: OK, so if everyone is leaning to #3, the name game remains to be played. Do we all agree on this: x @ y means x contains y x @ y means x is contained in y Does this mean that also contrib/ltree operators will likely change for consistency? Oh, I hadn't noticed that ltree spells it @ rather than @. I'd be inclined to stick with the ltree precedent. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Documentation fix for --with-ldap
Peter Eisentraut wrote: Am Montag, 4. September 2006 10:23 schrieb Albe Laurenz: This is just a 'one line' change in the documentation of the --with-ldap flag of ./configure Well, if you want to link from the configure option to the place where the feature is explained, then that should be done consistently for all options. That might bloat the installation instructions, though. Not sure. I didn't think of that. Originally, all I wanted to do is change the wording from Build with LDAP authentication support. to Build with LDAP support for authentication and connection parameter lookup. Then I thought it might be nice to add links. But I don't think it is important. Yours, Laurenz Albe ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] @ versus ~, redux
Michael Glaesemann [EMAIL PROTECTED] writes: Assuming the meaning of contains and is contained in is inclusive (rather than strict), then we'd have a = b : a contains b a = b : a is contained by b I don't think we can consider that, because we already have and operators meaning is left of, is right of for (some of) the affected datatypes. We'd have to start renaming those too, and that very rapidly turns into a mess. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Planner estimates and cast operations ,...
i am looking at some corner case which might also cause troubles for other people. consider the following: SELECT some_timestamp::date FROM very_large_table GROUP BY some_timestamp::date my very_large_table is around 1billion entries. the problem is: the planner has a problem here as it is taking the (correct) estimates for timestamp. this avoids a HashAggregate because the dataset seems to large for work_mem. what the planner cannot know is that the number of days is quite limited (in my case around 1000 different values). i wonder how to teach the planner to take the cast into consideration. at the moment the planner uses the per column statistics - it cannot know that the cast might change the number of different values. how about the following? Command: CREATE CAST Description: define a new cast Syntax: CREATE CAST (sourcetype AS targettype) [USING SELECTIVITY number | funcname(argtypes)] WITH FUNCTION funcname (argtypes) [ AS ASSIGNMENT | AS IMPLICIT ] if it was possible to assign a constant or some function to the cast i think we could make the example used above work. by default no costs are changed. if somebody is doing some fancy query it would be possible to tweak GOUOP BY planning by assigning some cleverly written function or a constant to the scenery. a constant would be useful in terms of casts to boolean or so. does anybody have an idea which could help solving this issue? best regards, hans ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] @ versus ~, redux
Andrew - Supernews [EMAIL PROTECTED] writes: On 2006-09-04, Tom Lane [EMAIL PROTECTED] wrote: Do we all agree on this: x @ y means x contains y x @ y means x is contained in y While I suggested something like those, I would also suggest that the existing operators for inet/cidr be taken into consideration: x = y x contains y x y x strictly contains y x = y x is contained in y x y x is strictly contained in y As I commented to Michael, adopting these names for geometric inclusion seems unworkable because and already mean is left of and is right of for those datatypes. We'd have to rename those operators too. Also, if we wanted to implement both strict and nonstrict containment operators, we're suddenly talking about adding code not only catalog entries. So that sounds like an awful lot of work and a whole lot more user code affected, in return for not that much gain in consistency. The existing geometric containment tests seem to be nonstrict, so if we wanted to leave room to add strict ones later, it might be best to settle on x @= y x contains or equals y x =@ y x is contained in or equals y reserving @ and @ for future strict comparison operators. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] possible ecpg vpath build error
On Mon, Sep 04, 2006 at 12:06:02AM -0400, Tom Lane wrote: Michael Glaesemann [EMAIL PROTECTED] writes: There's a lot of duplicate code in ecpg. No kidding :-(. The parser is bad enough but the datatype library is an order of magnitude worse. I don't have a great solution at hand though. The backend utils/adt/ code gets to rely on the backend's Neither have I. error handling and memory management protocols, which I surely do not propose to remove, but how could we keep common sources when ecpglib has to work in a far less friendly environment? We could modify the backend code to use pgtypeslib, but that would cost at least a little bit of performance I would guess. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Planner estimates and cast operations ,...
Hans-Juergen Schoenig [EMAIL PROTECTED] writes: consider the following: SELECT some_timestamp::date FROM very_large_table GROUP BY some_timestamp::date my very_large_table is around 1billion entries. the problem is: the planner has a problem here as it is taking the (correct) estimates for timestamp. this avoids a HashAggregate because the dataset seems to large for work_mem. what the planner cannot know is that the number of days is quite limited (in my case around 1000 different values). i wonder how to teach the planner to take the cast into consideration. Create an index on that expression. regression=# create table foo(x) as select x * '864 sec'::interval + now()::timestamp from generate_series(1,1) x; SELECT regression=# analyze foo; ANALYZE regression=# explain select x::date from foo group by x::date; QUERY PLAN --- HashAggregate (cost=205.00..330.00 rows=1 width=8) - Seq Scan on foo (cost=0.00..180.00 rows=1 width=8) (2 rows) regression=# create index fooi on foo((x::date)); CREATE INDEX regression=# analyze foo; ANALYZE regression=# explain select x::date from foo group by x::date; QUERY PLAN --- HashAggregate (cost=205.00..206.26 rows=101 width=8) - Seq Scan on foo (cost=0.00..180.00 rows=1 width=8) (2 rows) regression=# I had to cheat a little bit here: I tried to do this example with a timestamptz column, and the index creation failed because timestamptz to date isn't immutable (it depends on TimeZone). If yours is too, you could perhaps do something involving AT TIME ZONE to generate an immutable conversion to date. It would perhaps make sense to provide a way to cue ANALYZE to compute stats on expressions that aren't actually being indexed, but I see no good reason to limit our attention to cast expressions. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] possible ecpg vpath build error
Michael Meskes [EMAIL PROTECTED] writes: On Mon, Sep 04, 2006 at 12:06:02AM -0400, Tom Lane wrote: The backend utils/adt/ code gets to rely on the backend's error handling and memory management protocols, which I surely do not propose to remove, but how could we keep common sources when ecpglib has to work in a far less friendly environment? We could modify the backend code to use pgtypeslib, but that would cost at least a little bit of performance I would guess. I'd prefer to go in the other direction: provide enough infrastructure in ecpglib that it can use the unmodified backend sources. It would probably not take too much code to provide minimal elog and palloc support ... the question is what else would we need? (BTW, if anyone is working on making that pie-in-the-sky TODO list, here's a pet peeve for it: ecpg's bison parser should be auto-generated from the backend's, instead of derived manually.) regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [COMMITTERS] pgsql: sslinfo contrib module - information about current SSL
[EMAIL PROTECTED] (Peter Eisentraut) writes: sslinfo contrib module - information about current SSL certificate Author: Victor Wagner [EMAIL PROTECTED] It was premature to add this: Bruce is still trying to get a copyright assignment out of the author. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] @ versus ~, redux
Michael Glaesemann [EMAIL PROTECTED] writes: [ [EMAIL PROTECTED] wrote: ] x = y x contains y x y x strictly contains y x = y x is contained in y x y x is strictly contained in y (I'd be fine with Andrew's versions. I probably picked them up from his ip4r code, now that I think about it.) Actually, I have another objection to those names, which is that they look too much like C bit-shift operators to me ... Well, I do have suggestions for those, too :) r1 / r2r1 is to the left of r2 (r1 is before r2) r1 / r2r1 is to the right of r2 (r1 is after r2) And do you have extensions of those for is below/is above? This way madness lies. Let's sync the containment operators, not start relabeling every operator in sight. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Getting a move on for 8.2 beta
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Bruce Momjian wrote: Oh, lots of grunt work. I can see that working, but at a high cost. I doubt it. Let's just start with bugs, since that's the easy case anyway. Our real volume is pretty low, so the cost of maintaining it should not be high. I am assuming we would not be including HEAD, but only stable branches. With HEAD the volume would be quite a bit higher, but not impossibly so. Maybe we're working from different assumptions, but I thought the entire basis for this discussion is that the project has grown to the point where we have more resources than we used to --- and in particular, we can find people who don't feel able to fix deep backend bugs, but are ready and willing to track bug details and status with a goodly amount of cluefulness. If that resource doesn't actually exist, then I fear this whole discussion will come to naught. If it does exist, we should call upon it. This is not that far different from the premise upon which you built the buildfarm: that there were people out there able to provide machine resources and a certain amount of admin time. The resources this project requires are not those exactly, but why shouldn't we expect that some people will answer the call? I am not saying there is no work involved. In fact, throughout this thread I have agreed with you that a tracker that is not given regular maintenance effort is doomed to fail. But I don't think the level of effort required is undoable, nor that the cost is too high. Unlike running buildfarm, this is not largely automatable. Incidentally, the buildfarm has taken far more of my time and energy than I originally expected. It has probably been worth it - I doubt I could have delivered anything else as valuable in its place, but in that sense the cost to me has been quite high. I hope that by spreading the load a bit maintenance of a tracker will not be as burdensome to anybody. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Planner estimates and cast operations ,...
hi tom ... i thought about creating an index on the expression but the problem is that this is hardly feasable. in 8.0 (what i have here) this would block the table and i would run out of disk space as well. this is a 600 gb biest :( what about the planner approach? this would solve the problem for some other issues as well. an index might not be flexible enough :(. many thanks, hans On Sep 4, 2006, at 4:57 PM, Tom Lane wrote: Hans-Juergen Schoenig [EMAIL PROTECTED] writes: consider the following: SELECT some_timestamp::date FROM very_large_table GROUP BY some_timestamp::date my very_large_table is around 1billion entries. the problem is: the planner has a problem here as it is taking the (correct) estimates for timestamp. this avoids a HashAggregate because the dataset seems to large for work_mem. what the planner cannot know is that the number of days is quite limited (in my case around 1000 different values). i wonder how to teach the planner to take the cast into consideration. Create an index on that expression. regression=# create table foo(x) as select x * '864 sec'::interval + now()::timestamp from generate_series(1,1) x; SELECT regression=# analyze foo; ANALYZE regression=# explain select x::date from foo group by x::date; QUERY PLAN --- HashAggregate (cost=205.00..330.00 rows=1 width=8) - Seq Scan on foo (cost=0.00..180.00 rows=1 width=8) (2 rows) regression=# create index fooi on foo((x::date)); CREATE INDEX regression=# analyze foo; ANALYZE regression=# explain select x::date from foo group by x::date; QUERY PLAN --- HashAggregate (cost=205.00..206.26 rows=101 width=8) - Seq Scan on foo (cost=0.00..180.00 rows=1 width=8) (2 rows) regression=# I had to cheat a little bit here: I tried to do this example with a timestamptz column, and the index creation failed because timestamptz to date isn't immutable (it depends on TimeZone). If yours is too, you could perhaps do something involving AT TIME ZONE to generate an immutable conversion to date. It would perhaps make sense to provide a way to cue ANALYZE to compute stats on expressions that aren't actually being indexed, but I see no good reason to limit our attention to cast expressions. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] Contrib module to examine client
Bruce Momjian [EMAIL PROTECTED] writes: Uh, I have a problem with the README copyright: +sslinfo - information about current SSL certificate for PostgreSQL +== +Copyright (c) 2006 Cryptocom LTD Speaking of which, has anyone checked the copyrights on the other proposed-for-inclusion contrib modules? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Getting a move on for 8.2 beta
Peter Eisentraut [EMAIL PROTECTED] writes: Bruce Momjian wrote: Without a reply from Peter, I have to assume the patch is valid. To make it more explicit: I think the patch is stupid, but if someone wants to review it, go ahead. But I am not comfortable with the if no one objects, I'll just commit it mode that is sometimes going on. Has anyone actually tested the patch? Perhaps more to the point: a refactorization patch is all about beauty in the eye of the beholder. If Peter, the original author of the guc code, thinks that it's a disimprovement, I think it's a hard argument to make that the patch should go in anyway. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Optimizing prepared statements
Jeroen T. Vermeulen [EMAIL PROTECTED] writes: On Sun, September 3, 2006 23:52, Tom Lane wrote: What exactly do you mean by optimize away a parameter? The way you described the mechanism, there are no parameters that are optimized away, you've merely adjusted selectivity predictions using some assumed values. I'm using optimized away as shorthand for replaced with a literal constant in the statement definition used to generate the plan. Ah. I think you're confusing the spectators by using predict when you should say match. You're looking for previously generated plans that have assumed parameter values matching the current query --- saying that the plan predicts a parameter value is just a really poor choice of wording. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Hierarchical Queries--Status
On Sat, 2006-08-26 at 22:46 -0400, Jonah H. Harris wrote: On 8/26/06, Alvaro Herrera [EMAIL PROTECTED] wrote: Actually I was thinking in the design rather than the code ... Doh! We hadn't posted the design just yet. Let me write him and see where he's at and we'll throw something together for the list. [Note to Jonah: I've tried sending a similar version of this email to you a couple of times, but I'm not sure that it's getting through, hence the post to -hackers in the hope you may be able to pick it up there.] Hi everyone, I've had a chance to sit down for a day or so to see how to approach adding hierarchical queries to PostgreSQL, so I thought I'd post my initial thoughts on how to proceed. My aim is to refine the list below based upon feedback from hackers until it gets to the point where I can start work on it. Here's what I've got so far: 1) Add detection of the WITH clause to the parser. 2) Create a new type of RangeTblEntry to represent each common table expression specified within the WITH clause where the subquery field points to the nodes representing the common table expression. 3) Add planner support so that WITH clauses are mapped to a new type of node that utilises two tuplestores - an output tuplestore and a working tuplestore. The output tuple store will in effect be the contents of the table expression while the working tuplestore holds the results of the last iteration if recursive. Also implement some kind of WithState node which keeps track of the recursion state. Having spent some more time today looking at 1) and also at the SQL 2003 spec, it would seem that other databases offer the WITH clause within subqueries and also as part of a view. I'd be interested to hear feedback from other developers as to whether it would be possible to achieve this level of support within PostgreSQL. Many thanks, Mark. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Planner estimates and cast operations ,...
On Mon, Sep 04, 2006 at 17:19:37 +0200, Hans-Juergen Schoenig [EMAIL PROTECTED] wrote: i thought about creating an index on the expression but the problem is that this is hardly feasable. in 8.0 (what i have here) this would block the table and i would run That may be hard to deal with. out of disk space as well. this is a 600 gb biest :( I wouldn't expect this to be a problem. If you have 10^9 rows, I would expect the index to be less than 10% of you current size. If you are so close to your disk space limit that that is a problem, you have a problem in any case. what about the planner approach? this would solve the problem for some other issues as well. an index might not be flexible enough :(. If you disable sorting you might be able to get it to switch plans. Lying about the amount of work memory so that the planner thinks the hash will fit in memory despite its misguessing the number of buckets might also help. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Planner estimates and cast operations ,...
On Sep 4, 2006, at 7:04 PM, Bruno Wolff III wrote: On Mon, Sep 04, 2006 at 17:19:37 +0200, Hans-Juergen Schoenig [EMAIL PROTECTED] wrote: i thought about creating an index on the expression but the problem is that this is hardly feasable. in 8.0 (what i have here) this would block the table and i would run That may be hard to deal with. it is ... but the problem is not primarily that i have some problem with a certain query. somehow this can be solved somehow. i am thinking about GROUP BY and estimates in general here ... just wondering if there is a chance to improve ... out of disk space as well. this is a 600 gb biest :( I wouldn't expect this to be a problem. If you have 10^9 rows, I would expect the index to be less than 10% of you current size. If you are so close to your disk space limit that that is a problem, you have a problem in any case. the index itself is not too large but when building it up it is written several times. it is not funny when dealing with so much data ... what about the planner approach? this would solve the problem for some other issues as well. an index might not be flexible enough :(. If you disable sorting you might be able to get it to switch plans. Lying about the amount of work memory so that the planner thinks the hash will fit in memory despite its misguessing the number of buckets might also help. setting work_mem to 2gb does not help here ;) set it to the max value on 8.0. this was my first try too. the problem is - there is no magic switch to mislead the planner a little without hacking the system stats (which is not what people should do i would say ;) ). my question is: is adding hooks for selectivity a feasable way of dealing with things like that? hans ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Backend SSL configuration enhancement
Bruce Momjian [EMAIL PROTECTED] writes: This has been saved for the 8.3 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold This version was withdrawn by the author for rework, no? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Stopgap solution for ILIKE in multibyte encodings
I've gotten a little tired of reading reports that ILIKE doesn't work as expected in UTF8. The problem is that iwchareq() in like.c is several bricks shy of a load, as noticed e.g. here http://archives.postgresql.org/pgsql-bugs/2005-10/msg1.php I looked a little bit at making iwchareq less broken, but it seems like a mess because of the disconnect between pg_wchar and whatever the system towlower() function might be expecting. And in any case it can be expected that all this code will be thrown away someday, whenever we bite the bullet and do our own locale handling --- so I'm disinclined to spend a great deal of effort on it. I propose that for ILIKE in multibyte encodings, we just pass the strings through lower() and then use the normal LIKE code. This will be a bit slower than what we do now, but as a wise man once said, code can be arbitrarily fast if it needn't give the right answer. And we can't just ignore the bug for still another release cycle. Any objections? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Getting a move on for 8.2 beta
Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: Bruce Momjian wrote: Without a reply from Peter, I have to assume the patch is valid. To make it more explicit: I think the patch is stupid, but if someone wants to review it, go ahead. But I am not comfortable with the if no one objects, I'll just commit it mode that is sometimes going on. Has anyone actually tested the patch? Perhaps more to the point: a refactorization patch is all about beauty in the eye of the beholder. If Peter, the original author of the guc code, thinks that it's a disimprovement, I think it's a hard argument to make that the patch should go in anyway. How many times do I have to say this: IT IS NOT A REFACTOR PATCH AS REPORTED BY THE AUTHOR, AND PETER HAS NOT REFUTED THAT. It fixes a bug reported by the author, and Peter's inability to reply to the comments the author made is exactly the behavior I am talking about. If Peter does not want to engage in a technical discussion about the patch, I don't think we can consider his opinion valid. Seems I will have to call for a vote on this patch. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Contrib module to examine client
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Uh, I have a problem with the README copyright: +sslinfo - information about current SSL certificate for PostgreSQL +== +Copyright (c) 2006 Cryptocom LTD Speaking of which, has anyone checked the copyrights on the other proposed-for-inclusion contrib modules? Uh, what other ones? I see none in the patch queue. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Stopgap solution for ILIKE in multibyte encodings
Tom, On 9/4/06, Tom Lane [EMAIL PROTECTED] wrote: I propose that for ILIKE in multibyte encodings, we just pass the strings through lower() and then use the normal LIKE code. This will be a bit slower than what we do now, but as a wise man once said, code can be arbitrarily fast if it needn't give the right answer. And we can't just ignore the bug for still another release cycle. Perhaps it's a stupid question but what about the indexes? An index on lower(field) will be used by the new code or we wiil keep the current behaviour of ILIKE? Regards, -- Guillaume ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [COMMITTERS] pgsql: sslinfo contrib module - information about current SSL
Tom Lane wrote: [EMAIL PROTECTED] (Peter Eisentraut) writes: sslinfo contrib module - information about current SSL certificate Author: Victor Wagner [EMAIL PROTECTED] It was premature to add this: Bruce is still trying to get a copyright assignment out of the author. Another one of those things that was not evident from the patch queue. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Contrib module to examine client
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Speaking of which, has anyone checked the copyrights on the other proposed-for-inclusion contrib modules? Uh, what other ones? I see none in the patch queue. http://archives.postgresql.org/pgsql-hackers/2006-09/msg00050.php regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Planner estimates and cast operations ,...
On Mon, Sep 04, 2006 at 19:09:16 +0200, Hans-Juergen Schoenig [EMAIL PROTECTED] wrote: setting work_mem to 2gb does not help here ;) set it to the max value on 8.0. this was my first try too. the problem is - there is no magic switch to mislead the planner a little without hacking the system stats (which is not what people should do i would say ;) ). Did you combine that with telling it not to use sorts? I am not sure that will really work for GROUP BY, but it is probably an easy test. You can do an explain to see what it will try without actually running the query in case it picks the poor plan again. my question is: is adding hooks for selectivity a feasable way of dealing with things like that? I think the expectation is that you create a functional index and that's how you would tell the system to keep stats for particular functions. I don't think data on the most common values are kept now for functional indexes, but the index itself will still have clues about the data. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Getting a move on for 8.2 beta
Peter Eisentraut wrote: Bruce Momjian wrote: How many times do I have to say this: IT IS NOT A REFACTOR PATCH AS REPORTED BY THE AUTHOR, AND PETER HAS NOT REFUTED THAT. The initial patch was the feature plus some code refactoring included. That was what the author said. I asked him to submit the refactoring and the feature as two separate patches. What I got was a refactoring subpatch that actually made the code longer in terms of lines, which must be the very first code refactoring ever to achieve that. I did not get a satisfying answer on why that has to be, so I sort of lost interest in working with that patch. Sure, thanks. Here is his reply from the patch author as to why the patch isn't just refactoring: http://archives.postgresql.org/pgsql-patches/2006-08/msg00103.php The next email in the thread is two weeks later from the patch author asking about the status of the patch. If we don't need the refactoring part, great, but I want to be sure. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] Contrib module to examine client
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Speaking of which, has anyone checked the copyrights on the other proposed-for-inclusion contrib modules? Uh, what other ones? I see none in the patch queue. http://archives.postgresql.org/pgsql-hackers/2006-09/msg00050.php OK, I see: * new ISBN/etc module * hstore (finally proposed for inclusion) * new sslinfo module * pgstattuple changes * removing the deadwood The new ISBN is the only open one. hstore hasn't had enough requests for inclusion. sslinfo I got approval from the author today to remove the notice. pgstattuple had no new copyright mention. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Contrib module to examine client
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Speaking of which, has anyone checked the copyrights on the other proposed-for-inclusion contrib modules? The new ISBN is the only open one. hstore hasn't had enough requests for inclusion. Really? A quick search of the archives shows three different threads requesting its inclusion within the past two months. What's your definition of enough requests? I note that sslinfo has exactly zero prior requests for inclusion, so I'm not sure how it got by your filter. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Stopgap solution for ILIKE in multibyte encodings
Guillaume Smet [EMAIL PROTECTED] writes: On 9/4/06, Tom Lane [EMAIL PROTECTED] wrote: I propose that for ILIKE in multibyte encodings, we just pass the strings through lower() and then use the normal LIKE code. Perhaps it's a stupid question but what about the indexes? An index on lower(field) will be used by the new code or we wiil keep the current behaviour of ILIKE? No, this is just an internal change in the function's implementation, it won't have any effect like that. If you want indexing you'd still need to write out lower(col) like whatever. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] setseed() doc
Tom Lane skrev: entryliteralfunctionsetseed/function(typedp/type)/literal/entry entrytypeint/type/entry - entryset seed for subsequent literalrandom()/literal calls/entry + entryset seed for subsequent literalrandom()/literal calls (value between -1.0 and 1.0)/entry Looking at the code, it would appear that the intended range is 0 to 1. Ok. What about the return value? The doc didn't say anything about it. /Dennis ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Contrib module to examine client
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Speaking of which, has anyone checked the copyrights on the other proposed-for-inclusion contrib modules? The new ISBN is the only open one. hstore hasn't had enough requests for inclusion. Really? A quick search of the archives shows three different threads requesting its inclusion within the past two months. What's your definition of enough requests? I note that sslinfo has exactly zero prior requests for inclusion, so I'm not sure how it got by your filter. As I remember, hstore had questions because its documentation incorrectly stated it was in the PostgreSQL core distribution. If people want hstore, that's fine with me. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Contrib module to examine client
Stefan Kaltenbrunner wrote: Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Speaking of which, has anyone checked the copyrights on the other proposed-for-inclusion contrib modules? Uh, what other ones? I see none in the patch queue. http://archives.postgresql.org/pgsql-hackers/2006-09/msg00050.php OK, I see: * new ISBN/etc module * hstore (finally proposed for inclusion) * new sslinfo module * pgstattuple changes * removing the deadwood The new ISBN is the only open one. hstore hasn't had enough requests for inclusion. sslinfo I got approval from the author today to remove the notice. pgstattuple had no new copyright mention. we do actually get the occasional question about why hstore is not at least in contrib on IRC. I guess some of those questions might be the result of the confusing(wrong) wording on the website which says Stable version, included into PostgreSQL distribution, released under BSD license on http://www.sai.msu.su/~megera/oddmuse/index.cgi?Hstore . So there is definitely some real-world demand for hstore (at least on IRC much more than ISBN for example) OK, I will track that as an 8.2 open item then. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] setseed() doc
Dennis Bjorklund [EMAIL PROTECTED] writes: entryliteralfunctionsetseed/function(typedp/type)/literal/entry entrytypeint/type/entry - entryset seed for subsequent literalrandom()/literal calls/entry + entryset seed for subsequent literalrandom()/literal calls (value between -1.0 and 1.0)/entry Looking at the code, it would appear that the intended range is 0 to 1. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] setseed() doc
Dennis Bjorklund [EMAIL PROTECTED] writes: What about the return value? The doc didn't say anything about it. AFAICT it's just junk. It happens to be the input times MAX_RANDOM_VALUE, but what use is that? I wonder if we shouldn't change the function to return VOID ... that option wasn't available when it was coded originally, else it'd probably have been done that way. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Have psql show current sequnce values - (Resubmission)
Dhanaraj M [EMAIL PROTECTED] writes: Sorry for resubmitting this patch. Just now I found a problem. Instead of assigning initial sequence value to 1, I assign LLONG_MAX to avoid the buffer overflow problem. Please find the current version here. This patch is a mess. In the first place, it's completely unkosher for an application to scribble on a PGresult's contents, even if you do take steps like the above to try to make sure there's enough space. But said step does not work anyway -- LLONG_MAX might not exist on the client, or might exist but be smaller than the server's value. Another problem with it is it's not schema-aware and not proof against quoting requirements for the sequence name (try it with a mixed-case sequence name for instance). It also ought to pay some attention to the possibility that the SELECT for last_value fails --- quite aside from communication failure or such, there might be a permissions problem preventing the last_value from being read. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] TODO Item : GRANT/REVOKE to all schema objects
Gevik Babakhani wrote: Folks, Because of a broken wrist, I won't be able to type much. 1. Is there any discussion being going to regrading the: %Allow GRANT/REVOKE permissions to be applied to all schema objects with one command No. 2. I took a brief look at gramm.y, would it be okay to create a new section like GrantRoleStmt: for the todo item? (Tom? Alvaro?) I think so. I would read the developer's FAQ and discuss how this is going to work first. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [COMMITTERS] pgsql: sslinfo contrib module -
Tom Lane wrote: [EMAIL PROTECTED] (Peter Eisentraut) writes: sslinfo contrib module - information about current SSL certificate Author: Victor Wagner [EMAIL PROTECTED] It was premature to add this: Bruce is still trying to get a copyright assignment out of the author. I got it this morning. The text they sent was: The copyright has to be removed so it can be copyrighted by the PostgreSQL Global Development Group. Is that OK? We can still keep your name and company at the top. Yes, it can be removed. I just wasn't aware that copyright transfer is neccessary. Most open-source projects don't have such a requirement, and individual portions of code are copyrighted by their respecitve authors. Thanks. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Contrib module to examine client
Patch has applied this patch. Thanks. --- Peter Eisentraut wrote: Am Dienstag, 22. August 2006 02:52 schrieb Bruce Momjian: This seems like a nice /contrib module. Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. I have cleaned up this patch a little. I have changed all the function signatures from varchar to text, fixed up the formatting and packaging a little, and renamed it to just sslinfo. Note to the author: Whitespace is free. Use it. :) -- Peter Eisentraut http://developer.postgresql.org/~petere/ [ Attachment, skipping... ] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Sorry about the GIN docs :(
Patch applied. Thanks. --- Christopher Kings-Lynne wrote: Hi guys, I've attached as much as I've done so far on the GIN docs. It's not a lot, but I'm afraid with the feature freeze in effect, I'm just not going to have the ability to get them done by the RC date. The main problem was I just strugged to fully understand it all :( Anyway, hopefully someone else can pick them up and finish them off for the release. Sorry about that, Chris [ Attachment, skipping... ] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Contrib module to examine client
Bruce Momjian wrote: Patch has applied this patch. Thanks. Sorry typo: Peter has applied this patch. Thanks. --- --- Peter Eisentraut wrote: Am Dienstag, 22. August 2006 02:52 schrieb Bruce Momjian: This seems like a nice /contrib module. Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. I have cleaned up this patch a little. I have changed all the function signatures from varchar to text, fixed up the formatting and packaging a little, and renamed it to just sslinfo. Note to the author: Whitespace is free. Use it. :) -- Peter Eisentraut http://developer.postgresql.org/~petere/ [ Attachment, skipping... ] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Open items
I should have a list of open items for 8.2 within 24 hours. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Hierarchical Queries--Status
On Mon, Sep 04, 2006 at 05:15:57PM +0100, Mark Cave-Ayland wrote: 3) Add planner support so that WITH clauses are mapped to a new type of node that utilises two tuplestores - an output tuplestore and a working tuplestore. The output tuple store will in effect be the contents of the table expression while the working tuplestore holds the results of the last iteration if recursive. Also implement some kind of WithState node which keeps track of the recursion state. That's basically what I came up with. Basically you have a sort of loop in the execution plan where tuples that come out are copied into a tuplestore and run through a particular part of the executor again. The top-down approach of the executor makes it a bit trickier... Having spent some more time today looking at 1) and also at the SQL 2003 spec, it would seem that other databases offer the WITH clause within subqueries and also as part of a view. I'd be interested to hear feedback from other developers as to whether it would be possible to achieve this level of support within PostgreSQL. Absolutly possible. The question is how much work :) Incidently, if you find a way to support common subplans (where a part of the executor is shared between two executions) that might provide a way to solve some of the trickier multiple evaluation problems with rules. Again, it would just be a tuplestore the stored the results for multiple executions. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] [COMMITTERS] pgsql: sslinfo contrib module -
Alvaro Herrera [EMAIL PROTECTED] writes: Also, the code is released under BSD license, so why is it important if it says Copyright Foo, Inc or something else? Because every so often we get pestered by lawyers who get worried when there's a collection of random different copyright notices in the code. Actually, I don't think there's anything wrong with Copyright Foo Inc as long as there's also a statement Released under the PostgreSQL license or equivalent. The problem here was that with neither that nor a copyright assignment, there is no clear intent to make the code available under our license terms. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [COMMITTERS] pgsql: sslinfo contrib module -
Alvaro Herrera wrote: Bruce Momjian wrote: Tom Lane wrote: [EMAIL PROTECTED] (Peter Eisentraut) writes: sslinfo contrib module - information about current SSL certificate Author: Victor Wagner [EMAIL PROTECTED] It was premature to add this: Bruce is still trying to get a copyright assignment out of the author. I got it this morning. The text they sent was: The copyright has to be removed so it can be copyrighted by the PostgreSQL Global Development Group. Is that OK? We can still keep your name and company at the top. Yes, it can be removed. I just wasn't aware that copyright transfer is neccessary. Most open-source projects don't have such a requirement, and individual portions of code are copyrighted by their respecitve authors. I still don't understand why is the copyright assignment needed at all. Is it even valid, given that the PGDG does not have a written/ signed document? At least the FSF requires you to given them a written and signed statement to that effect. And if it's not valid, why bother doing it at all? Also, the code is released under BSD license, so why is it important if it says Copyright Foo, Inc or something else? We will be able to use it regardless of the copyright assignment, as will anyone else. One point may be that if PGDG doesn't have a Copyright line in the header, it can't then automatically increment the year in there when the time comes. Does this have anything to do with it? It may also have to do with when someone from PGDG fixes a bug in that code. [ Patch author added as CC.] I think we just felt that an explicit copyright to someone else in our code could be confusing. I don't believe there is any fundamental reason to remove it, but it just seems best to do that. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Interval month, week - day
Tom Lane wrote:
Michael Meskes [EMAIL PROTECTED] writes:
On Sun, Sep 03, 2006 at 10:21:11PM -0400, Bruce Momjian wrote:
When I tried the ecpg regression tests it complained there was no
results/ directory. I created one and it worked.
Hmm, anyone else experiencing this? The pg_regress.sh has this code that
should create it:
outputdir=results/
if [ ! -d $outputdir ]; then
mkdir -p $outputdir || { (exit 2); exit; }
fi
I'll bet you should lose the slash in $outputdir. test(1) might or
might not be friendly about stripping that off.
Yep, I saw this error:
mkdir: results/: No such file or directory
gmake: *** [installcheck] Error 2
I have removed the trailing slash from CVS; tests run fine now.
Thanks.
--
Bruce Momjian [EMAIL PROTECTED]
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [HACKERS] Fix linking of OpenLDAP libraries
Albe Laurenz [EMAIL PROTECTED] writes: # The backend doesn't need everything that's in LIBS, however ! LIBS := $(filter-out -lz -lreadline -ledit -ltermcap -lncurses -lcurses -lldap_r $(PTHREAD_LIBS), $(LIBS)) This seems pretty risky. What if PTHREAD_LIBS contains -L switches? They'd get removed even if needed for other libraries. It would probably be safer not to put LDAP into LIBS at all, but invent two new macros for configure to set, say LDAP_LIBS and LDAP_LIBS_R, and add these to the link lines in the backend and libpq respectively. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Backend SSL configuration enhancement
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: This has been saved for the 8.3 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold This version was withdrawn by the author for rework, no? Right, and the thread in patches_hold shows that. The reason it is in there is so we can ping the author at the start of 8.3 to get an updated version. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [COMMITTERS] pgsql: sslinfo contrib module -
Bruce Momjian wrote: Alvaro Herrera wrote: Bruce Momjian wrote: Tom Lane wrote: [EMAIL PROTECTED] (Peter Eisentraut) writes: sslinfo contrib module - information about current SSL certificate Author: Victor Wagner [EMAIL PROTECTED] It was premature to add this: Bruce is still trying to get a copyright assignment out of the author. I got it this morning. The text they sent was: The copyright has to be removed so it can be copyrighted by the PostgreSQL Global Development Group. Is that OK? We can still keep your name and company at the top. Yes, it can be removed. I just wasn't aware that copyright transfer is neccessary. Most open-source projects don't have such a requirement, and individual portions of code are copyrighted by their respecitve authors. I still don't understand why is the copyright assignment needed at all. Is it even valid, given that the PGDG does not have a written/ signed document? At least the FSF requires you to given them a written and signed statement to that effect. And if it's not valid, why bother doing it at all? Also, the code is released under BSD license, so why is it important if it says Copyright Foo, Inc or something else? We will be able to use it regardless of the copyright assignment, as will anyone else. One point may be that if PGDG doesn't have a Copyright line in the header, it can't then automatically increment the year in there when the time comes. Does this have anything to do with it? It may also have to do with when someone from PGDG fixes a bug in that code. [ Patch author added as CC.] hmm ? actually the author seems to be Victor Wagner ([EMAIL PROTECTED] - now in CC) - I don't have anything to do with that module ... Stefan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] FE/BE protocol vs. parameterized queries
Picking out a specific point from the thread on prepared queries: Currently, the handling of Parse/Bind on the unnamed statement seems to go like this: - Parse on the unnamed statement does analysis and rewriting but does not plan, storing the query in a special memory context dedicated to the unnamed statement - Bind on the unnamed statement plans the query (using the supplied parameters) and stores the plan back in the unnamed statement's context I believe this could usefully (and transparently to clients) be changed so that Bind on the unnamed statement does _not_ store the plan back in the unnamed statement's context, but instead produces a plan which is only used _for that specific portal_. Thus, it would promote the parameters to constants before planning, knowing that the plan could only be run once; this would, I believe, allow the planner to produce a plan that was equivalent to that of a non-parameterized query. This would hopefully remove all cases where it is currently necessary to use PQexec rather than PQexecParams, such as where parameterized limits, immutable functions of parameters, partial indexes etc. are involved. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] FE/BE protocol vs. parameterized queries
Andrew - Supernews [EMAIL PROTECTED] writes: I believe this could usefully (and transparently to clients) be changed so that Bind on the unnamed statement does _not_ store the plan back in the unnamed statement's context, but instead produces a plan which is only used _for that specific portal_. That seems OK to me, since we document the unnamed statement/portal as being optimized for one-shot execution. Unfortunately it's probably less than a trivial change, because the planner never assumes that Params are constants; that would have to be changed somehow. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [COMMITTERS] pgsql: Sequences were not being shown due to the use of lowercase 's'
[EMAIL PROTECTED] (Bruce Momjian) writes: Sequences were not being shown due to the use of lowercase 's' instead of 'S', and the views were not checking for table visibility with regards to temporary tables and sequences. What became of my objection that the test should be on USAGE privilege for the containing schema instead? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [COMMITTERS] pgsql: Sequences were not being shown due to
Tom Lane wrote: [EMAIL PROTECTED] (Bruce Momjian) writes: Sequences were not being shown due to the use of lowercase 's' instead of 'S', and the views were not checking for table visibility with regards to temporary tables and sequences. What became of my objection that the test should be on USAGE privilege for the containing schema instead? I remember puzzling over Greg's reply: http://archives.postgresql.org/pgsql-patches/2006-08/msg00247.php Anyway, Greg is going to fix that, plus the syntax error in his other patch. I will see it gets corrected. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Documentation fix for --with-ldap
Albe Laurenz wrote: Peter Eisentraut wrote: Am Montag, 4. September 2006 10:23 schrieb Albe Laurenz: This is just a 'one line' change in the documentation of the --with-ldap flag of ./configure Well, if you want to link from the configure option to the place where the feature is explained, then that should be done consistently for all options. That might bloat the installation instructions, though. Not sure. I didn't think of that. Originally, all I wanted to do is change the wording from Build with LDAP authentication support. to Build with LDAP support for authentication and connection parameter lookup. Then I thought it might be nice to add links. But I don't think it is important. More limited modification you mentioned added to documentation. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/installation.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/installation.sgml,v retrieving revision 1.260 diff -c -c -r1.260 installation.sgml *** doc/src/sgml/installation.sgml 17 Aug 2006 17:25:43 - 1.260 --- doc/src/sgml/installation.sgml 4 Sep 2006 21:42:13 - *** *** 853,860 termoption--with-ldap/option/term listitem para ! Build with acronymLDAP/indextermprimaryLDAP// ! authentication support. On Unix, this requires the productnameOpenLDAP/ package to be installed. filenameconfigure/ will check for the required header files and libraries to make sure that your productnameOpenLDAP/ --- 853,861 termoption--with-ldap/option/term listitem para ! Build with acronymLDAP/indextermprimaryLDAP// support ! for authentication and connection parameter lookup. ! On Unix, this requires the productnameOpenLDAP/ package to be installed. filenameconfigure/ will check for the required header files and libraries to make sure that your productnameOpenLDAP/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Trivial patch to double vacuum speed on tables with no indexes
Gregory Stark wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Patch applied. Thanks. Wait a minute. This patch changes the behavior so that LockBufferForCleanup is applied to *every* heap page, not only the ones where there are removable tuples. It's not hard to imagine scenarios where that results in severe system-wide performance degradation. Has there been any real-world testing of this idea? I see the no-index case now: + if (nindexes) + LockBuffer(buf, BUFFER_LOCK_SHARE); + else + LockBufferForCleanup(buf); Let's see what Greg says, or revert. Hm, that's a good point. I could return it to the original method where it released the share lock and did he LockBufferForCleanup only if necessary. I thought it was awkward to acquire a lock then release it to acquire a different lock on the same buffer but it's true that it doesn't always have to acquire the second lock. This rush to apply patches just because no one seems to be capable of keeping up with them not being reviewed, is starting to get a bit worrisome. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [COMMITTERS] pgsql: sslinfo contrib module - information about current SSL
Peter Eisentraut wrote: Log Message: --- sslinfo contrib module - information about current SSL certificate Author: Victor Wagner [EMAIL PROTECTED] This seems to have broken the bustard buildfarm member, which uses VPATH IIRC: make[1]: Leaving directory `/home/andrew/bf/root/HEAD/pgsql.19349/contrib/vacuumlo' make[1]: Entering directory `/home/andrew/bf/root/HEAD/pgsql.19349/contrib/sslinfo' Makefile:9: ../contrib-global.mk: No such file or directory make[1]: *** No rule to make target `../contrib-global.mk'. Stop. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Unable to post to -patches (was: Visual C++
Patch applied. Placed in src/tools/msvc. Thanks. --- Magnus Hagander wrote: a.hub.org[200.46.208.251], delay=1, status=sent (250 2.7.1 Ok, discarded, id=258 35-09 - BANNED: P=p003,L=1,M=multipart/mixed | P=p002,L=1/2,M=application/x-gzip ,T=gz,N=vcbuild.tar.gz | P=p...) Seems -patches is rejecting any mail with attached .tar.gz files, if I read that correctly? Hm, I just managed to send a patch labeled application/octet-stream without any problem. Not sure what's the point in banning application/x-gzip, unless that's a common virus signature? I doubt it would be, and if it is then really, it's still not a very smart thing to do IMHO :) Anyway try the other MIME type. Hmm. I can't really control the MIME type out of my system (remember, running Exchange here..). But I guess I can rename the file ;-) Attempting here to get it into the archives at least.. //Magnus [note, file is a .tar.gz even though it doesn't look that way] Content-Description: vcbuild.tar.gz.bin [ Attachment, skipping... ] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] Trivial patch to double vacuum speed
Patch applied. Thanks.
---
Gregory Stark wrote:
Tom Lane [EMAIL PROTECTED] writes:
The reason the patch is so short is that it's a kluge. If we really
cared about supporting this case, more wide-ranging changes would be
needed (eg, there's no need to eat maintenance_work_mem worth of RAM
for the dead-TIDs array); and a decent respect to the opinions of
mankind would require some attention to updating the header comments
and function descriptions, too.
The only part that seems klugy to me is how it releases the lock and
reacquires it rather than wait in the first place until it can acquire the
lock. Fixed that and changed lazy_space_alloc to allocate only as much space
as is really necessary.
Gosh, I've never been accused of offending all mankind before.
--- vacuumlazy.c 31 Jul 2006 21:09:00 +0100 1.76
+++ vacuumlazy.c 28 Aug 2006 09:58:41 +0100
@@ -16,6 +16,10 @@
* perform a pass of index cleanup and page compaction, then resume the heap
* scan with an empty TID array.
*
+ * As a special exception if we're processing a table with no indexes we can
+ * vacuum each page as we go so we don't need to allocate more space than
+ * enough to hold as many heap tuples fit on one page.
+ *
* We can limit the storage for page free space to MaxFSMPages entries,
* since that's the most the free space map will be willing to remember
* anyway. If the relation has fewer than that many pages with free space,
@@ -106,7 +110,7 @@
TransactionId
OldestXmin);
static BlockNumber count_nondeletable_pages(Relation onerel,
LVRelStats *vacrelstats,
TransactionId OldestXmin);
-static void lazy_space_alloc(LVRelStats *vacrelstats, BlockNumber relblocks);
+static void lazy_space_alloc(LVRelStats *vacrelstats, BlockNumber relblocks,
unsigned nindexes);
static void lazy_record_dead_tuple(LVRelStats *vacrelstats,
ItemPointer itemptr);
static void lazy_record_free_space(LVRelStats *vacrelstats,
@@ -206,7 +210,8 @@
* This routine sets commit status bits, builds lists of dead
tuples
* and pages with free space, and calculates statistics on the
number
* of live tuples in the heap. When done, or when we run low on
space
- * for dead-tuple TIDs, invoke vacuuming of indexes and heap.
+ * for dead-tuple TIDs, or after every page if the table has no
indexes
+ * invoke vacuuming of indexes and heap.
*
* It also updates the minimum Xid found anywhere on the table in
* vacrelstats-minxid, for later storing it in pg_class.relminxid.
@@ -247,7 +252,7 @@
vacrelstats-rel_pages = nblocks;
vacrelstats-nonempty_pages = 0;
- lazy_space_alloc(vacrelstats, nblocks);
+ lazy_space_alloc(vacrelstats, nblocks, nindexes);
for (blkno = 0; blkno nblocks; blkno++)
{
@@ -282,8 +287,14 @@
buf = ReadBuffer(onerel, blkno);
- /* In this phase we only need shared access to the buffer */
- LockBuffer(buf, BUFFER_LOCK_SHARE);
+ /* In this phase we only need shared access to the buffer
unless we're
+ * going to do the vacuuming now which we do if there are no
indexes
+ */
+
+ if (nindexes)
+ LockBuffer(buf, BUFFER_LOCK_SHARE);
+ else
+ LockBufferForCleanup(buf);
page = BufferGetPage(buf);
@@ -450,6 +461,12 @@
{
lazy_record_free_space(vacrelstats, blkno,
PageGetFreeSpace(page));
+ } else if (!nindexes) {
+ /* If there are no indexes we can vacuum the page right
now instead
+ * of doing a second scan */
+ lazy_vacuum_page(onerel, blkno, buf, 0, vacrelstats);
+ lazy_record_free_space(vacrelstats, blkno,
PageGetFreeSpace(BufferGetPage(buf)));
+ vacrelstats-num_dead_tuples = 0;
}
/* Remember the location of the last page with nonremovable
tuples */
@@ -891,16 +908,20 @@
* See the comments at the head of this file for rationale.
*/
static void
-lazy_space_alloc(LVRelStats *vacrelstats, BlockNumber relblocks)
+lazy_space_alloc(LVRelStats *vacrelstats, BlockNumber relblocks, unsigned
nindexes)
{
longmaxtuples;
int maxpages;
- maxtuples = (maintenance_work_mem * 1024L) / sizeof(ItemPointerData);
- maxtuples = Min(maxtuples, INT_MAX);
- maxtuples = Min(maxtuples, MaxAllocSize /
Re: [HACKERS] [PATCHES] Trivial patch to double vacuum speed on tables with no indexes
Bruce Momjian [EMAIL PROTECTED] writes: Patch applied. Thanks. Wait a minute. This patch changes the behavior so that LockBufferForCleanup is applied to *every* heap page, not only the ones where there are removable tuples. It's not hard to imagine scenarios where that results in severe system-wide performance degradation. Has there been any real-world testing of this idea? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] Trivial patch to double vacuum speed
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Patch applied. Thanks. Wait a minute. This patch changes the behavior so that LockBufferForCleanup is applied to *every* heap page, not only the ones where there are removable tuples. It's not hard to imagine scenarios where that results in severe system-wide performance degradation. Has there been any real-world testing of this idea? I see the no-index case now: + if (nindexes) + LockBuffer(buf, BUFFER_LOCK_SHARE); + else + LockBufferForCleanup(buf); Let's see what Greg says, or revert. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] setseed() doc
Tom Lane wrote: Dennis Bjorklund [EMAIL PROTECTED] writes: entryliteralfunctionsetseed/function(typedp/type)/literal/entry entrytypeint/type/entry - entryset seed for subsequent literalrandom()/literal calls/entry + entryset seed for subsequent literalrandom()/literal calls (value between -1.0 and 1.0)/entry Looking at the code, it would appear that the intended range is 0 to 1. Docs updated. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/func.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.332 diff -c -c -r1.332 func.sgml *** doc/src/sgml/func.sgml 22 Aug 2006 00:49:19 - 1.332 --- doc/src/sgml/func.sgml 4 Sep 2006 21:45:15 - *** *** 795,801 row entryliteralfunctionsetseed/function(typedp/type)/literal/entry entrytypeint/type/entry !entryset seed for subsequent literalrandom()/literal calls/entry entryliteralsetseed(0.54823)/literal/entry entryliteral1177314959/literal/entry /row --- 795,801 row entryliteralfunctionsetseed/function(typedp/type)/literal/entry entrytypeint/type/entry !entryset seed for subsequent literalrandom()/literal calls (value between 0 and 1.0)/entry entryliteralsetseed(0.54823)/literal/entry entryliteral1177314959/literal/entry /row ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Trivial patch to double vacuum speed on tables with no indexes
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Patch applied. Thanks. Wait a minute. This patch changes the behavior so that LockBufferForCleanup is applied to *every* heap page, not only the ones where there are removable tuples. It's not hard to imagine scenarios where that results in severe system-wide performance degradation. Has there been any real-world testing of this idea? I see the no-index case now: + if (nindexes) + LockBuffer(buf, BUFFER_LOCK_SHARE); + else + LockBufferForCleanup(buf); Let's see what Greg says, or revert. Hm, that's a good point. I could return it to the original method where it released the share lock and did he LockBufferForCleanup only if necessary. I thought it was awkward to acquire a lock then release it to acquire a different lock on the same buffer but it's true that it doesn't always have to acquire the second lock. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] plpgsql, return can contains any expression
Pavel Stehule [EMAIL PROTECTED] writes: This patch allows using any row expression in return statement and does transformation from untyped row to composite types if it's necessary. This patch doesn't seem to cope with cases where the supplied tuple has the wrong number of columns, and it doesn't look like it's being careful about dropped columns either. Also, that's a mighty bizarre-looking choice of cache memory context in coerce_to_tuple ... but then again, why are you bothering with a cache at all for temporary arrays? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [COMMITTERS] pgsql: Sequences were not being shown due to the use of lowercase `s`
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I wrote: Sequences were not being shown due to the use of lowercase 's' instead of 'S', and the views were not checking for table visibility with regards to temporary tables and sequences. Tom Lane replied: What became of my objection that the test should be on USAGE privilege for the containing schema instead? I took a stab at implementing this, but what exactly would we check? Looks like all the temp tables have automatic usage for the same user, according to SELECT *,has_schema_privilege(oid,'USAGE') FROM pg_namespace; So I'd need another way to test that the schema was created by another process. I agree that is_visible may not be ideal for most cases, but it should be okay if we are simply using it to filter temporary schemas, right? - -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200609041803 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFE/Km6vJuQZxSWSsgRAgkaAKC/Nzc8xIcxRC1TW2UJCB76LurWmgCg+Dkk 4HbMsy4H1uwRAUz9lqCSdXg= =eBg2 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [COMMITTERS] pgsql: sslinfo contrib module - information about current SSL
Alvaro Herrera [EMAIL PROTECTED] writes: This seems to have broken the bustard buildfarm member, which uses VPATH IIRC: Fixed --- I noticed it about the same time you did. I'm surprised Peter didn't get a Makefile right the first time though ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [COMMITTERS] pgsql: Sequences were not being shown due to the use of lowercase `s`
Greg Sabino Mullane [EMAIL PROTECTED] writes: Tom Lane replied: What became of my objection that the test should be on USAGE privilege for the containing schema instead? I took a stab at implementing this, but what exactly would we check? Looks like all the temp tables have automatic usage for the same user, according to SELECT *,has_schema_privilege(oid,'USAGE') FROM pg_namespace; Well, if you test it as a superuser, it's going to return TRUE every time. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Erratic failures on buildfarm member leveret
Hey Stefan, could you run some hardware diagnostics on leveret? It's been returning increasingly erratic results over the past few days. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Trivial patch to double vacuum speed
Alvaro Herrera wrote: I see the no-index case now: + if (nindexes) + LockBuffer(buf, BUFFER_LOCK_SHARE); + else + LockBufferForCleanup(buf); Let's see what Greg says, or revert. Hm, that's a good point. I could return it to the original method where it released the share lock and did he LockBufferForCleanup only if necessary. I thought it was awkward to acquire a lock then release it to acquire a different lock on the same buffer but it's true that it doesn't always have to acquire the second lock. This rush to apply patches just because no one seems to be capable of keeping up with them not being reviewed, is starting to get a bit worrisome. When things are placed in the patches queue, I need to get feedback if there is a problem with them. I am not sure what other process we can follow, unless we just keep patches there indefinitely, or just ignore them and never place them in the queue. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Information schema - finalize key_column_usage
Greg Sabino Mullane [EMAIL PROTECTED] writes: Attached version should now work properly. Applied, thanks. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] DOC: catalog.sgml
On Sun, Sep 03, 2006 at 12:01:06AM -0400, Tom Lane wrote: But ever since 7.3 the convention for identifying system objects has been pretty well-defined: anything that lives in one of the predefined schemas. What problem were you having using that approach in newsysviews? It was just an issue of trawling through pg_dump to confirm that. -- Jim C. Nasby, Database Architect [EMAIL PROTECTED] 512.569.9461 (cell) http://jim.nasby.net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] TODO Request
Ühel kenal päeval, T, 2006-08-29 kell 22:12, kirjutas Joshua D. Drake: Auto creations of partitions This would be something like: create table foo () partition by ... from the referenced MySQL manual entry CREATE TABLE members ( ... joined DATE NOT NULL ) PARTITION BY KEY(joined) PARTITIONS 6; Do you have any idea how this should work ? What date range should go into which partition ? For reference I am directly apply my fair use rights to the above per the MySQL development docs. Reference below: http://dev.mysql.com/doc/refman/5.1/en/partitioning.html Yes I am fully aware that we don't need to do something just because MySQL does it. However, Oracle has similar functionality and I would like to see us keep up :) Of course I would like it to be done correctly :) Do you know if ther is anything about partitioning in any ISO/ANSI SQL standards ? -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Trivial patch to double vacuum speed
Bruce Momjian [EMAIL PROTECTED] writes: Alvaro Herrera wrote: This rush to apply patches just because no one seems to be capable of keeping up with them not being reviewed, is starting to get a bit worrisome. When things are placed in the patches queue, I need to get feedback if there is a problem with them. I am not sure what other process we can follow, unless we just keep patches there indefinitely, or just ignore them and never place them in the queue. The problem with the process you're using is that it defaults to applying patches --- and in fact, lately it seems like it takes a threat of mayhem to prevent you from applying a patch. Now, apply-unless-objected-to was the right default back in 1997, when the code was in bad enough shape that it was hard to make it worse ;-). I do not believe it's the right default anymore though. The system is a lot larger and more complicated than it was when it left Berkeley, and our quality standards are an order of magnitude higher too. We need to default to *not* applying patches until they've passed some amount of review. I don't want to be too hard-nosed about this, since the last thing we need is another level of bureaucracy added to our processes, especially for simple trivial stuff. But when there's been some discussion or objection to a patch, ISTM that just because the patch submitter has put up a second version should not mean that it's okay to apply. At that point some actual review is needed. I'm also having a bit of a problem with the silence-means-assent rule. Most of the time I'm OK with it, but right now I simply don't have the time to look at everything that comes in as soon as it comes in; especially not second versions of patches. I don't have a concrete proposal to make, but I do think that the current patch-queue process is not suited to the project as it stands today. Maybe if this issue-tracking stuff gets off the ground, we could let developers place ACK or NAK flags on patches they've looked at, and have some rule about ACK-vs-NAK requirements for something to go in. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [COMMITTERS] pgsql: Sequences were not being shown due to the use of lowercase `s`
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 SELECT *,has_schema_privilege(oid,'USAGE') FROM pg_namespace; Well, if you test it as a superuser, it's going to return TRUE every time. Exactly. So I'm not seeing how we can use USAGE as a reliable test for the case where a temporary table was created by the same user, but in another session. - -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200609041941 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFE/LsJvJuQZxSWSsgRAt5mAKDWAWmnljELeRJn+LvdAnpfkwhDIwCfSls8 hR0xST8C88uA4xXrEP6pAh0= =bHRd -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] Trivial patch to double vacuum speed
I don't have a concrete proposal to make, but I do think that the current patch-queue process is not suited to the project as it stands today. Maybe if this issue-tracking stuff gets off the ground, we could let developers place ACK or NAK flags on patches they've looked at, and have some rule about ACK-vs-NAK requirements for something to go in. How about *requiring* test cases that prove the patch? Sincerely, Joshua D. Drake regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Trivial patch to double vacuum speed
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Alvaro Herrera wrote: This rush to apply patches just because no one seems to be capable of keeping up with them not being reviewed, is starting to get a bit worrisome. When things are placed in the patches queue, I need to get feedback if there is a problem with them. I am not sure what other process we can follow, unless we just keep patches there indefinitely, or just ignore them and never place them in the queue. The problem with the process you're using is that it defaults to applying patches --- and in fact, lately it seems like it takes a threat of mayhem to prevent you from applying a patch. I am just trying to keep everyone happy, the developers, user community, and patch submitters. Now, apply-unless-objected-to was the right default back in 1997, when the code was in bad enough shape that it was hard to make it worse ;-). I do not believe it's the right default anymore though. The system is a lot larger and more complicated than it was when it left Berkeley, and our quality standards are an order of magnitude higher too. We need to default to *not* applying patches until they've passed some amount of review. I don't want to be too hard-nosed about this, since the last thing we need is another level of bureaucracy added to our processes, especially for simple trivial stuff. But when there's been some discussion or objection to a patch, ISTM that just because the patch submitter has put up a second version should not mean that it's okay to apply. At that point some actual review is needed. I'm also having a bit of a problem with the silence-means-assent rule. Most of the time I'm OK with it, but right now I simply don't have the time to look at everything that comes in as soon as it comes in; especially not second versions of patches. I don't have a concrete proposal to make, but I do think that the current patch-queue process is not suited to the project as it stands today. Maybe if this issue-tracking stuff gets off the ground, we could let developers place ACK or NAK flags on patches they've looked at, and have some rule about ACK-vs-NAK requirements for something to go in. Yes, I realize this is a very hard time. I know you were pushing for end-of-week beta, and though I don't think we can hit that date, I am trying to move things along. I agree it is that second version of the patch that often doesn't get the thorough review. Should I increase the amount of time something is in the queue, or ask for someone to state it is OK to apply, and just keep asking until I get a yes from someone? I can do that pretty easily. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Trivial patch to double vacuum speed
Joshua D. Drake wrote: I don't have a concrete proposal to make, but I do think that the current patch-queue process is not suited to the project as it stands today. Maybe if this issue-tracking stuff gets off the ground, we could let developers place ACK or NAK flags on patches they've looked at, and have some rule about ACK-vs-NAK requirements for something to go in. How about *requiring* test cases that prove the patch? That doesn't hit most of the failures, which can be portability, performance, or missing features, or bad style. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Trivial patch to double vacuum speed
Joshua D. Drake [EMAIL PROTECTED] writes: How about *requiring* test cases that prove the patch? There are lots and lots of things that can't really be tested with pg_regress-based tests, and in any case a test does not prove the absence of bugs; particularly not a test devised by the code author, since almost by definition it won't test cases he didn't think of. Certainly test cases have their place, but I have a lot more faith in code-reading by knowledgeable developers as a way to spot problems that got past the original author. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] GIN FailedAssertions on Itanium2 with Intel
I suggest you test for the Intel compiler, and if it is there, make the static variable volatile, and add a comment about why that is being done. --- Sergey E. Koposov wrote: On Sat, 2 Sep 2006, Bruce Momjian wrote: Teodor Sigaev wrote: What does that option do? Is it practical to enable it for the entire backend? From docs: Disables inline expansion of standard library or intrinsic functions. And isn't this a straightforward compiler bug they should be notified about? What's a choice? Now I see 3: 1) -O1 2) volatile 3) -nolib_inline IMHO, only -O1 is guarantee for other possible places... But I'm not familiar enough with such kinds of bugs. My guess is that the compiler writers saw you calling a libc function, and assumed that library could not modify the file static variable, forgetting that the libc function can call back into the original file. Can you detect the Itanium compiler and optimization levels via preprocessor symbols, and test for that, and throw an #error? No, it's impossible. Unfortunately the __OPTIMIZE__ preproc. symbol of icc doesn't allow to distinguish between different optimization levels. (only between -O0 and anything else). Regards, Sergey *** Sergey E. Koposov Max Planck Institute for Astronomy/Sternberg Astronomical Institute Tel: +49-6221-528-349 Web: http://lnfm1.sai.msu.ru/~math E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
