Re: [HACKERS] storing TZ along timestamps
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Fri, May 27, 2011 at 04:43:28PM -0400, Alvaro Herrera wrote: > Hi, > > One of our customers is interested in being able to store original > timezone along with a certain timestamp. I've felt that pain here and there too... > So the first thing is cataloguing timezone names, and assigning an ID to > each (maybe an OID). If we do that, then we can store the OID of the > timezone name along the int64/float8 of the actual timestamp value. > > Right now we rely on the tzdata files on disk for things like > pg_timezone_names and other accesses of TZ data; so the files are the > authoritative source of TZ info. So we need to ensure that whenever the > files are updated, the catalogs are updated as well. Problem with this approach (mapping external time zone names to OIDs) is: dump/restore would only be meaningful if you "carry over" the time zone data, right? That is: two independent systems are likely to have different mappings (even if at some point they have the "same" TZ data?) What would be a solution to that? (a) A central, "official" catalog, with only additions, never deletions (perhaps with some space carved out for "local" additions, to minimize conflicts)? (b) A hash of the time zone name? Both not very good ideas, I know. Although (a) might be less bad than it seems. Most Unixoids (including OSX) seem to have basically Olson's. Don't know about Windows, but it might seem feasible to make some mapping (or union). Only important rule: no backtrack :-) Regards - -- tomás -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFN4JWtBcgs9XrR2kYRAt+mAJ0atx3u6pll50+s4vVwCKZUjqmnSQCffWNe gzSFgRCFUvsd8pbH1Qm/ho4= =FVhO -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How can I check the treatment of bug fixes?
From: "Peter Eisentraut" On fre, 2011-05-27 at 13:55 -0400, Robert Haas wrote: Also, I think it's about time we got ourselves some kind of bug tracker. I have no idea how to make that work without breaking workflow that works now, but a quick survey of my pgsql-bugs email suggests that this is far from the only thing slipping through the cracks. The problem is finding a usable bug tracking software. I think JIRA is very good. Almost all projects in Apache Software Foundation (ASF) including Tomcat, Hadoop, Apache HTTP server, use JIRA. With JIRA, we can know various counts such as the number of bugs per major/minor release, not-fixed bugs, new features in each major release, etc. Regards MauMau -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] What is the best and easiest implementation to reliably wait for the completion of startup?
From: "Tom Lane" "MauMau" writes: The bad thing is that pg_ctl continues to wait until the specified duration passes, even if postgres fails to start. For example, it is naturally desirable for pg_ctl to terminate when postgresql.conf contains a syntax error. Hmm, I thought we'd fixed this in the last go-round of pg_ctl wait revisions, but testing proves it does not work desirably in HEAD: not only does pg_ctl wait till its timeout elapses, but it then reports "server started" even though the server didn't start. That's clearly a bug :-( I think your proposal of a pipe-based solution might be overkill though. Seems like it would be sufficient for pg_ctl to give up if it doesn't see the postmaster.pid file present within a couple of seconds of postmaster startup. I don't really want to add logic to the postmaster to have the sort of reporting protocol you propose, because not everybody uses pg_ctl to start the postmaster. In any case, we need a fix in 9.1 ... Yes, I was a bit afraid the pipe-based fix might be overkill, too, so I was wondering if there might be a more easy solution. "server started"... I missed it. That's certainly a bug, as you say. I was also considering the postmaster.pid-based solution exactly as you suggest, but that has a problem -- how many seconds do we assume for "a couple of seconds"? If the system load is temporarily so high that postmaster takes many seconds to create postmaster.pid, pg_ctl mistakenly thinks that postmaster failed to start. I know this is a hypothetical rare case. I don't like touching the postmaster logic and complicating it, but logical correctness needs to come first (Japanese users are very severe). Another problem with postmaster.pid-based solution happens after postmaster crashes. When postmaster crashes, postmaster.pid is left. If the pid in postmaster.pid is allocated to some non-postgres process and that process remains, pg_ctl misjudges that postmaster is starting up, and waits for long time. Regards MauMau -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Allow ALTER TABLE name {OF type | NOT OF}.
Excerpts from Cédric Villemain's message of vie may 27 18:37:05 -0400 2011: > 2011/4/21 Robert Haas : > > Modified Files > > -- > > doc/src/sgml/ref/alter_table.sgml | 26 +++ > > src/backend/commands/tablecmds.c | 277 > > +++-- > > I noticed 2 warnings on unused variables from gcc in tablecmds.c > The attached patch fix that by removing those 2 variables. My compiler wasn't complaining, but since the variable is clearly unused I went ahead and pushed this. Thanks -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] dblink crash on PPC
Greg Stark writes: > On Fri, May 27, 2011 at 10:06 AM, Steve Singer > wrote: >> As another data point, the dblink regression tests work fine for me on a >> PPC32 debian (squeeze,gcc 4.4.5) based system. > Given that it's dblink my guess is that it's picking up the wrong > version of libpq somehow. Maybe, but then why does the test only crash during backend exit, and not while it's exercising dblink? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] storing TZ along timestamps
On May 27, 2011, at 6:29 PM, Greg Stark wrote: > Both of these two cases can be handled differently. The former by > storing the raw text inputs and then storing the interpreted value as > a derived column separetly, and the latter by storing the local time > zone to use for display as an additional attribute along with the > local address and other attributes of the calendar event. Which means you're back to a very cumbersome method that involves another field. That's a tremendous amount of extra code. We run multiple businesses around the globe. Each business operates in it's own timezone, and 90% of the time we want things handled in that timezone. The wheels fall off the wagon if we try and combine data from multiple locations into a single database; there's no reasonable way to say: give me the data in this field *at the timezone that was originally entered*, except for not storing timezone data at all. If we don't store timezone data at all, then it's impossible to determine an actual point in time that something happened at. -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] storing TZ along timestamps
On 05/27/2011 04:29 PM, Greg Stark wrote: On Fri, May 27, 2011 at 4:13 PM, Steve Crawford wrote: I am very interested in the use-case for this (in part as I'm working on a PG related time talk). My experience thus far is that people who want this do not fully understand the nature of date-time calculations and variables in PG. The use cases I recall having been mentioned in the past were accurate data retention and calendaring applications. Accurate data retention for things like drug trials need to guarantee they retain precisely what the user entered, not an equivalent value. If you run a report on a drug trial you need to see that the event was recorded as occuring at 1:00pm EST not 6:00pm GMT even if you happen to run the report in London. And calendaring apps want to know what timezone is attached to an event, not only the point in time at which it occurs. If your plane flight departs at 12:00pm GMT and lands at 2:00pm EST you need to know that to book your taxi at 2:30pm EST -- not 7:30pm GMT. Both of these two cases can be handled differently. The former by storing the raw text inputs and then storing the interpreted value as a derived column separetly, and the latter by storing the local time zone to use for display as an additional attribute along with the local address and other attributes of the calendar event. So the proposed change does not handle the first case as you need to capture the raw input. And the second case is already well handled. In fact calendaring is a great example. I enter the time for the teleconference and PG nicely uses my default timezone to store the point-in-time. When you retrieve it, it is shown in your timezone and we both pick up the phone at the correct time. And if I know I'll be somewhere else at that time, I just ask for the data in that zone. Altering the data type gains nothing. Cheers, Steve -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] storing TZ along timestamps
On Fri, May 27, 2011 at 4:13 PM, Steve Crawford wrote: > I am very interested in the use-case for this (in part as I'm working on a > PG related time talk). My experience thus far is that people who want this > do not fully understand the nature of date-time calculations and variables > in PG. The use cases I recall having been mentioned in the past were accurate data retention and calendaring applications. Accurate data retention for things like drug trials need to guarantee they retain precisely what the user entered, not an equivalent value. If you run a report on a drug trial you need to see that the event was recorded as occuring at 1:00pm EST not 6:00pm GMT even if you happen to run the report in London. And calendaring apps want to know what timezone is attached to an event, not only the point in time at which it occurs. If your plane flight departs at 12:00pm GMT and lands at 2:00pm EST you need to know that to book your taxi at 2:30pm EST -- not 7:30pm GMT. Both of these two cases can be handled differently. The former by storing the raw text inputs and then storing the interpreted value as a derived column separetly, and the latter by storing the local time zone to use for display as an additional attribute along with the local address and other attributes of the calendar event. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] storing TZ along timestamps
On 05/27/2011 01:43 PM, Alvaro Herrera wrote: Hi, One of our customers is interested in being able to store original timezone along with a certain timestamp. I am very interested in the use-case for this (in part as I'm working on a PG related time talk). My experience thus far is that people who want this do not fully understand the nature of date-time calculations and variables in PG. It is currently possible to store a TZ in a separate column, but this is a bit wasteful and not very convenient anyway. Are there objections to the general idea? If not, I'll flesh a more complete proposal. I'm not crazy about it. Although time-stamp-with-time-zone is, perhaps, a bad name for what is actually a "point in time", a point-in-time is what timestamptz represents. I can enter it and allow my defaults to take over, specify abbreviations, explicit offsets or long names none of which change the actual point in time. Likewise, I can display said point-in-time in any of dozens of ways according to my needs. steve=# select '2011-05-27 12:34'::timestamptz; timestamptz 2011-05-27 12:34:00-07 steve=# select '2011-05-27 12:34-07'::timestamptz; timestamptz 2011-05-27 12:34:00-07 steve=# select '2011-05-27 12:34 PDT'::timestamptz; timestamptz 2011-05-27 12:34:00-07 steve=# select '2011-05-27 11:34 PST'::timestamptz; timestamptz 2011-05-27 12:34:00-07 steve=# select '2011-05-27 15:34 US/Eastern'::timestamptz; timestamptz 2011-05-27 12:34:00-07 select now() - '02:58:54.605041'::interval; ?column? --- 2011-05-27 12:34:00.394959-07 Granted, I'm a random sample of 1, but I've never found anyone with a real need for this feature - especially since the capability already exists to achieve the requested result, and much more flexibly, by either a separate column or a user-defined type. Questions: What would be the storage impact (tables, indexes and backups) for those of use with tens-of-millions of pieces of timestamp data? What type of timestamp would be stored? Abbreviated/offset (PST, -07), full (US/Eastern) or a mix? Is there an expectation that the stored time zone information would be used for any calculation purposes? If so, how would rules be applied? Would there be any form of error-checking? Currently PG accepts non-existent time zones but maps them to UTC: steve=# select '2011-05-27 15:34'::timestamptz at time zone 'US/f00'; timezone - 2011-05-27 15:34:00 Would there be any impact to existing queries? How would dump/restore issues be handled - especially if the time-zone info changes in between? More as I think of them. Cheers, Steve -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] storing TZ along timestamps
On Fri, May 27, 2011 at 2:32 PM, Kevin Grittner wrote: > I think the key thing is that the timestamp portion of it would be > identical to our current TIMESTAMP WITH TIME ZONE -- always store it > in the value UTC zone. Fwiw our timestamp with time zone stores seconds since the epoch. This is a quantity which is independent of timezones entirely. Excluding relativistic effects there have been the same number of time zones since that point in time regardless of where you stand relative to the sun. My question for Alvarro is whether he really wants the text label for the time zone at all, or just the offset which was used to enter it. That is, if I enter "12:00pm" with my current time zone set to GMT and later update the tzdata on the machine to start summer time on a earlier date should the data type now show "1:00pm BST" or should it still display "12:00pm +000" and leave it up to the reader to decide whether why I entered it in a weird time zone for that time of year? -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] dblink crash on PPC
On Fri, May 27, 2011 at 10:06 AM, Steve Singer wrote: > As another data point, the dblink regression tests work fine for me on a > PPC32 debian (squeeze,gcc 4.4.5) based system. Given that it's dblink my guess is that it's picking up the wrong version of libpq somehow. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How can I check the treatment of bug fixes?
On Fri, May 27, 2011 at 9:24 PM, Peter Eisentraut wrote: > On fre, 2011-05-27 at 13:55 -0400, Robert Haas wrote: >> Also, I think it's about time we got ourselves some kind of bug >> tracker. I have no idea how to make that work without breaking >> workflow that works now, but a quick survey of my pgsql-bugs email >> suggests that this is far from the only thing slipping through the >> cracks. > > The problem is finding a usable bug tracking software. On the "upside," we have gotten to the point where people that count are finding the CommitFest application, which Is Not Simply Email, to be an acceptable and useful thing to use. But I don't find that I notably *like* any of the bug trackers that I have encountered thus far. There are a few "PG-basable" options (e.g. - RT, Bugzilla), but it's not *quite* good enough to pick something just because it's running on our own DB. I suspect that, from a technical perspective, the emergence of distributed bug trackers (Fossil, SD, Bugs Everywhere), which parallels distributed SCM (e.g. - Git) may be part of the "way to go," but that's still pointing at technical mechanism, as opposed to workflow. There is a page on the wiki documenting requirements that have been discussed: http://wiki.postgresql.org/wiki/TrackerDiscussion It hasn't been touched since 2008, but I expect that wiki page would make a better starting point to restart discussion than anything else. And it is quite likely worthwhile to consider what linkages to the CommitFest schema/code/interfaces are relevant. I'll also poke at SD (https://github.com/bestpractical/sd) as having some ideas worth looking at, as it combines: - Being inherently distributed, where bugs are assigned UUIDs as identifiers, and where data is pulled via Git repos - Essentially text-based, by default, so that it doesn't assume/mandate communicating with a web server - Somewhat agnostic of data sources; it can push/pull data to/from RT, Hiveminder, Trac, GitHub, Google Code, and Redmine. And there's a useful principle here: if the PostgreSQL project's issue tracker can sync data against something like SD, then developers have extra options. I rather wish that Slony was using one of those 6 trackers, rather than Bugzilla, as I could use SD+adaptor, and be able to work on issues offline. At any rate, a useful step would be to dust off the contents of that wiki page, and see if there are more details that are widely agreeable. The (sometimes modest) successes of the CommitFest application should provide some useful guidance. -- When confronted by a difficult problem, solve it by reducing it to the question, "How would the Lone Ranger handle this?" -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] "errno" not set in case of "libm" functions (HPUX)
On tor, 2011-05-26 at 17:31 -0400, Tom Lane wrote: > > We could also do that globally, but that would probably be something > for > > the next release. > > Hmm. I'm a bit scared of how much might break. I don't think the > autoconf tests are generally designed to guarantee no warnings. Yeah, I think you're right. Although one wonders why they have built-in support for that. Might be worth trying sometime. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How can I check the treatment of bug fixes?
On Friday, May 27, 2011 20:39:26 Robert Haas wrote: > On Fri, May 27, 2011 at 2:19 PM, Tom Lane wrote: > > Robert Haas writes: > >> On Fri, May 27, 2011 at 12:21 PM, Tom Lane wrote: > >>> That patch is waiting for a committer who knows something about Windows > >>> to pick it up. > >> > >> It might be useful, in this situation, for the OP to add this patch to > >> the CommitFest application. > >> > >> https://commitfest.postgresql.org/action/commitfest_view/open > >> > >> Also, I think it's about time we got ourselves some kind of bug > >> tracker. > > > > [ shrug... ] I think the main problem is a lack of committer cycles. > > If so, the extra bureaucracy involved in managing a bug tracker will > > make things worse, not better. > > > > However, if someone *else* wants to do the work of entering bugs into a > > tracker and updating their status, far be it from me to stand in their > > way. > And who is to say only committers can fix bugs? Actually commit the > fixes themselves, yes. Write the patches? No. If I see a bug in a region I know something about and its on a platform I care about (i.e. likely only linux) I try to do this. But its hard, in most situations one of you already did it. Tom and you are just to goddamn fast in many, many cases. Which is totally great, don't get me wrong, but makes it hard to beat you as a mere mortal ;) Do you like separate patches for the back branches or is that basically useless work? Related to doing stuff like that is that I really find it hard to write a patch that happens to be liked by Tom or you so it does not have to be mostly rewritten. For that to change for one I would like to have the Coding Style to be expanded because I think there are loads of rules that exist only in bits and bits on the mailing lists. For another I would like to get a patch back instead of rewritten because without knowing the individual reasons for the changes its sometimes rather hard to know what the reason for a specific change was. I do realize thats quite a bit of work for you which is why I hesitated writing that... Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] minor patch submission: CREATE CAST ... AS EXPLICIT
From a language definition perspective, it is helpful to have a name for every case instead of an implicit fallback, without any word to describe it. See for instance "CREATE USER CREATEDB/NOCREATEDB" or "CREATE RULE ... DO ALSO/INSTEAD" for similar occurences of naming default cases. Oddly enough, we did add the DO ALSO syntax much later, and no one complained about that, as far as I recall. I complained:-) and I submitted the patch then, AFAICR. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Reducing overhead of frequent table locks
On Fri, May 27, 2011 at 04:55:07PM -0400, Robert Haas wrote: > When a strong lock is taken or released, we have to increment or > decrement strong_lock_counts[fasthashpartition]. Here's the question: > is that atomic? In other words, suppose that strong_lock_counts[42] > starts out at 0, and two backends both do ++strong_lock_counts[42]. > Are we guaranteed to end up with "2" in that memory location or might > we unluckily end up with "1"? I think the latter is possible... and > some guard is needed to make sure that doesn't happen. Yeah: what Tom said. Guard it with a spinlock? Given that the backend is about to (or did earlier) go off and acquire dozens or hundreds of LWLocks, it doesn't seem like an area begging for early optimization. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] storing TZ along timestamps
On May 27, 2011, at 2:35 PM, Tom Lane wrote: > "David E. Wheeler" writes: >> I like it, but what do you do when a TZ has been renamed or has ceased >> to exist. > > As far as that goes, I think "nothing" is a sufficient answer. There's > no requirement that an OID in the mapping table correspond to a live TZ. > It's just a more compact way of storing a string name. Well then you'd just want to be sure to never delete TZs. I think the issue of trying this week to use a TZ that was removed last week might be more problematic. I mean, we could just let the user use it, but that hardly seems wise… Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] storing TZ along timestamps
"David E. Wheeler" writes: > I like it, but what do you do when a TZ has been renamed or has ceased > to exist. As far as that goes, I think "nothing" is a sufficient answer. There's no requirement that an OID in the mapping table correspond to a live TZ. It's just a more compact way of storing a string name. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] storing TZ along timestamps
"David E. Wheeler" wrote: > I like it, but what do you do when a TZ has been renamed or has > ceased to exist. Or, worse, existed last week, so last week's > dates might still use it, but next week's must not? I think the key thing is that the timestamp portion of it would be identical to our current TIMESTAMP WITH TIME ZONE -- always store it in the value UTC zone. That way comparisons and math between timestamps could remain sane. The stored time zone portion would be what it would be the display format, if usable. In an extreme situation like you describe above, I guess you could fall back on what we do now for display of a timestamptz value. Personally, I think it would be good to move a bit closer to the standard by including a time zone in a TIMESTAMP WITH TIME ZONE value. The biggest problem I can see is how to try to do this in a standard conforming fashion without breaking existing code. It would seem more than a little odd to support the standard semantics with nonstandard syntax and vice versa. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How can I check the treatment of bug fixes?
On fre, 2011-05-27 at 13:55 -0400, Robert Haas wrote: > Also, I think it's about time we got ourselves some kind of bug > tracker. I have no idea how to make that work without breaking > workflow that works now, but a quick survey of my pgsql-bugs email > suggests that this is far from the only thing slipping through the > cracks. The problem is finding a usable bug tracking software. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] storing TZ along timestamps
On May 27, 2011, at 1:43 PM, Alvaro Herrera wrote: > Right now we rely on the tzdata files on disk for things like > pg_timezone_names and other accesses of TZ data; so the files are the > authoritative source of TZ info. So we need to ensure that whenever the > files are updated, the catalogs are updated as well. > I think we could make this work if we "refreshed" the catalog from the > files on SIGHUP if the directory changes (say, a new timezone is > created). Note that I am currently proposing to store only the zone > names in the catalog, not the full TZ data. > > Are there objections to the general idea? If not, I'll flesh a more > complete proposal. I like it, but what do you do when a TZ has been renamed or has ceased to exist. Or, worse, existed last week, so last week's dates might still use it, but next week's must not? Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] storing TZ along timestamps
Alvaro Herrera writes: > Right now we rely on the tzdata files on disk for things like > pg_timezone_names and other accesses of TZ data; so the files are the > authoritative source of TZ info. So we need to ensure that whenever the > files are updated, the catalogs are updated as well. > I think we could make this work if we "refreshed" the catalog from the > files on SIGHUP if the directory changes (say, a new timezone is > created). (1) SIGHUP processing normally occurs outside any transaction. (2) The only obvious way to ensure the "refresh" is done once, and not once per backend, is to have the postmaster do it ... which is a nonstarter for many reasons. I'd suggest instead considering something like the pg_collations approach: load up the catalog once at initdb. If the user really needs to add to the set of accessible TZ names later, give him a tool to do that. But it's 100% not worth either the implementation pain or the cycles to try to auto-update the catalog, especially not as often as once per SIGHUP. BTW, what will you do about pg_upgrade? Ensuring the OID mapping doesn't change seems like loads of fun. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: starting to review the Extend NOT NULL representation to pg_constraint patch
I intend to have a look at this patch and hopefully fix the outstanding issues. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Reducing overhead of frequent table locks
Robert Haas writes: > When a strong lock is taken or released, we have to increment or > decrement strong_lock_counts[fasthashpartition]. Here's the question: > is that atomic? In other words, suppose that strong_lock_counts[42] > starts out at 0, and two backends both do ++strong_lock_counts[42]. > Are we guaranteed to end up with "2" in that memory location or might > we unluckily end up with "1"? I think the latter is possible... and > some guard is needed to make sure that doesn't happen. There are "atomic increment" primitives on most/all multiprocessors, although availing ourselves of them everywhere will take an amount of work not unlike developing the spinlock primitives :-(. You are dead right that this is unsafe without that. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Reducing overhead of frequent table locks
On Tue, May 24, 2011 at 10:03 AM, Noah Misch wrote: > On Tue, May 24, 2011 at 08:53:11AM -0400, Robert Haas wrote: >> On Tue, May 24, 2011 at 5:07 AM, Noah Misch wrote: >> > This drops the part about only transferring fast-path entries once when a >> > strong_lock_counts cell transitions from zero to one. >> >> Right: that's because I don't think that's what we want to do. I >> don't think we want to transfer all per-backend locks to the shared >> hash table as soon as anyone attempts to acquire a strong lock; >> instead, I think we want to transfer only those fast-path locks which >> have the same locktag as the strong lock someone is attempting to >> acquire. If we do that, then it doesn't matter whether the >> strong_lock_counts[] cell is transitioning from 0 to 1 or from 6 to 7: >> we still have to check for strong locks with that particular locktag. > > Oh, I see. I was envisioning that you'd transfer all locks associated with > the strong_lock_counts cell; that is, all the locks that would now go directly > to the global lock table when requested going forward. Transferring only > exact matches seems fine too, and then I agree with your other conclusions. I took a crack at implementing this and ran into difficulties. Actually, I haven't gotten to the point of actually testing whether it works, but I'm worried about a possible problem with the algorithm. When a strong lock is taken or released, we have to increment or decrement strong_lock_counts[fasthashpartition]. Here's the question: is that atomic? In other words, suppose that strong_lock_counts[42] starts out at 0, and two backends both do ++strong_lock_counts[42]. Are we guaranteed to end up with "2" in that memory location or might we unluckily end up with "1"? I think the latter is possible... and some guard is needed to make sure that doesn't happen. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] storing TZ along timestamps
Hi, One of our customers is interested in being able to store original timezone along with a certain timestamp. It is currently possible to store a TZ in a separate column, but this is a bit wasteful and not very convenient anyway. There are all sorts of UI issues that need to be resolved in order for this to be a complete feature proposal, but the first thing that we discussed was what is the storage going to look like. Of course, one thing we don't want is to store the complete TZ name as text. So the first thing is cataloguing timezone names, and assigning an ID to each (maybe an OID). If we do that, then we can store the OID of the timezone name along the int64/float8 of the actual timestamp value. Right now we rely on the tzdata files on disk for things like pg_timezone_names and other accesses of TZ data; so the files are the authoritative source of TZ info. So we need to ensure that whenever the files are updated, the catalogs are updated as well. I think we could make this work if we "refreshed" the catalog from the files on SIGHUP if the directory changes (say, a new timezone is created). Note that I am currently proposing to store only the zone names in the catalog, not the full TZ data. Are there objections to the general idea? If not, I'll flesh a more complete proposal. -- Álvaro Herrera -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tackling full page writes
On Wed, May 25, 2011 at 01:29:05PM -0400, Robert Haas wrote: > On Wed, May 25, 2011 at 1:06 PM, Greg Smith wrote: > > On 05/24/2011 04:34 PM, Robert Haas wrote: > > > I've been looking into a similar refactoring of the names here, where we > > bundle all of these speed over safety things (fsync, full_page_writes, etc.) > > into one control so they're easier to turn off at once. Not sure if it > > should be named "web_scale" or "do_you_feel_lucky_punk". > > Actually, I suggested that same idea to you, or someone, a while back, > only I was serious. crash_safety=off. I never got around to fleshing > out the details, though. clearly: crash_safety=running_with_scissors -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer & Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tackling full page writes
On Thu, May 26, 2011 at 12:38 AM, Fujii Masao wrote: > On Thu, May 26, 2011 at 1:18 PM, Robert Haas wrote: >>> The replay of the WAL record for A doesn't rely on the content of chunk 1 >>> which B modified. So I don't think that "partial page writes" has such >>> a problem. >>> No? >> >> Sorry. WAL records today DO rely on the prior state of the page. If >> they didn't, we wouldn't need full page writes. They don't rely on >> them terribly heavily - things like where pd_upper is pointing, and >> what the page LSN is. But they do rely on them. > > Yeah, I'm sure that normal WAL record (neither full page writes nor > "partial page writes") relies on the prior state of the page. But WAL > record for A is "partial page writes", which also relies on the prior > state? Yeah, that's how it shakes out. The idea is you have to write the parts of the page that you rely on, but not the rest - which in turn guarantees that those parts (but not the rest) will be correct when you read them. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How can I check the treatment of bug fixes?
On Fri, May 27, 2011 at 2:19 PM, Tom Lane wrote: > Robert Haas writes: >> On Fri, May 27, 2011 at 12:21 PM, Tom Lane wrote: >>> That patch is waiting for a committer who knows something about Windows >>> to pick it up. > >> It might be useful, in this situation, for the OP to add this patch to >> the CommitFest application. > >> https://commitfest.postgresql.org/action/commitfest_view/open > >> Also, I think it's about time we got ourselves some kind of bug >> tracker. > > [ shrug... ] I think the main problem is a lack of committer cycles. > If so, the extra bureaucracy involved in managing a bug tracker will > make things worse, not better. > > However, if someone *else* wants to do the work of entering bugs into a > tracker and updating their status, far be it from me to stand in their > way. Definitely something to think about. But I think lack of committer bandwidth is only part of the problem. If someone had a free day tomorrow and wanted to flip through all the bugs that haven't had a response and address the ones they knew something about, how would they get a list? And who is to say only committers can fix bugs? Actually commit the fixes themselves, yes. Write the patches? No. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [ADMIN] pg_class reltuples/relpages not updated by autovacuum/vacuum
Robert Haas writes: > Still, maybe we don't have a better option. If it were me, I'd add an > additional safety valve: use your formula if the percentage of the > relation scanned is above some threshold where there's unlikely to be > too much skew. But if the percentage scanned is too small, then don't > use that formula. Instead, only update relpages/reltuples if the > relation is now larger; set relpages to the new actual value, and > scale up reltuples proportionately. Ah, progress: now we're down to arguing about the size of the fudge factor ;-). I'll do something involving derating the reliability when the number is coming from VACUUM. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How can I check the treatment of bug fixes?
Robert Haas writes: > On Fri, May 27, 2011 at 12:21 PM, Tom Lane wrote: >> That patch is waiting for a committer who knows something about Windows >> to pick it up. > It might be useful, in this situation, for the OP to add this patch to > the CommitFest application. > https://commitfest.postgresql.org/action/commitfest_view/open > Also, I think it's about time we got ourselves some kind of bug > tracker. [ shrug... ] I think the main problem is a lack of committer cycles. If so, the extra bureaucracy involved in managing a bug tracker will make things worse, not better. However, if someone *else* wants to do the work of entering bugs into a tracker and updating their status, far be it from me to stand in their way. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Vacuum, visibility maps and SKIP_PAGES_THRESHOLD
On Fri, May 27, 2011 at 11:10 AM, Greg Stark wrote: > It would be nice if the VM had a bit for "all-frozen" but that > wouldn't help much except in the case of truly cold data. We could > perhaps keep the frozen data per segment or per VM page (which covers > a large section of the table) which would at least mean that would > have a fixed amount of data become vacuum-dirty when a tuple is > updated rather than a whole table which could be arbitrarily large. Instead of just having one bit, it might be useful to have a relfrozenxid counter for each, say, 64MB chunk, rather than just one for the whole table. At least in theory, that would give us the possibility of freezing only portions of the table that were most urgently in need of it. I'm not sure how exactly what algorithm we'd want to apply, though. In general, ISTM that the problem with VACUUM is that we don't know whether we're "keeping up" or "getting behind". For checkpoint_completion_target, we measure how fast we're writing pages relative to when the checkpoint needs to be done. We write faster if we get behind, where behind can mean either that checkpoint_segments is going to expire too soon, or that checkpoint_timeout is going to expire too soon. VACUUM has a very similar problem: operations that use XIDs or create dead tuples create the need for maintenance which VACUUM then performs. We want to vacuum fast enough to keep up with the work, but not so fast that we tax the I/O subsystem more than necessary. But unlike the checkpoint process, vacuum's decision-making is all local: it has no idea whether it's keeping up. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How can I check the treatment of bug fixes?
On Fri, May 27, 2011 at 12:21 PM, Tom Lane wrote: > "Joshua D. Drake" writes: >> You have done what you need to do to check the status. Someone who knows >> something about the bug should speak up at some point. > > That patch is waiting for a committer who knows something about Windows > to pick it up. It might be useful, in this situation, for the OP to add this patch to the CommitFest application. https://commitfest.postgresql.org/action/commitfest_view/open Also, I think it's about time we got ourselves some kind of bug tracker. I have no idea how to make that work without breaking workflow that works now, but a quick survey of my pgsql-bugs email suggests that this is far from the only thing slipping through the cracks. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] dblink crash on PPC
On 11-05-27 12:35 PM, Tom Lane wrote: grebe, which is also a PPC64 machine, isn't showing the bug. And I just failed to reproduce the problem on a RHEL6 PPC64 box. About to go try it on RHEL5, which has a gcc version much closer to what wombat says it's using, but I'm not very hopeful about that. I think the more likely thing to be keeping in mind is that Gentoo is a platform with poor quality control. regards, tom lane As another data point, the dblink regression tests work fine for me on a PPC32 debian (squeeze,gcc 4.4.5) based system. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] dblink crash on PPC
I wrote: > grebe, which is also a PPC64 machine, isn't showing the bug. And I just > failed to reproduce the problem on a RHEL6 PPC64 box. About to go try > it on RHEL5, which has a gcc version much closer to what wombat says > it's using, but I'm not very hopeful about that. Nope, no luck there either. It's going to be hard to make any progress on this without investigation on wombat itself. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] dblink crash on PPC
"Kevin Grittner" writes: > Robert Haas wrote: >> Around when did it start failing? > According to the buildfarm logs the first failure was roughly 1 day > 10 hours 40 minutes before this post. See http://buildfarm.postgresql.org/cgi-bin/show_history.pl?nm=wombat&br=HEAD The problem here is that wombat has been offline for about a month before that, so it could have broken anytime in the past month. It's also not unlikely that the hiatus signals a change in the underlying hardware or software, which might have been the real cause. (Mark?) > Keep in mind that PPC is a platform with weak memory ordering grebe, which is also a PPC64 machine, isn't showing the bug. And I just failed to reproduce the problem on a RHEL6 PPC64 box. About to go try it on RHEL5, which has a gcc version much closer to what wombat says it's using, but I'm not very hopeful about that. I think the more likely thing to be keeping in mind is that Gentoo is a platform with poor quality control. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How can I check the treatment of bug fixes?
"Joshua D. Drake" writes: > You have done what you need to do to check the status. Someone who knows > something about the bug should speak up at some point. That patch is waiting for a committer who knows something about Windows to pick it up. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How can I check the treatment of bug fixes?
On 05/27/2011 05:36 AM, MauMau wrote: > Hello, > > I posted a patch for bug #6011 to pgsql-hackers several days ago. How > can I check the status of bug fixes? I'm worried that the patch might be > forgotten, because bug #5842 was missed for two months until Bruce > noticed it. The joke that my lovely colleagues are not letting you in on is, "PostgreSQL does not believe in using a bug tracker". I personally think that some of us are still holding on to a strange and irrational premise that a bug tracker will somehow force the community to subjigate itself to "the man" and therefore we just can't allow it. Yes, it is a long standing argument. Yes, it is ridiculous. Yes, it is something that MySQL gets to make fun of us about (inside joke). You have done what you need to do to check the status. Someone who knows something about the bug should speak up at some point. Sincerely, Joshua D. Drake > > Regards > MauMau > > -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development The PostgreSQL Conference - http://www.postgresqlconference.org/ @cmdpromptinc - @postgresconf - 509-416-6579 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How can I check the treatment of bug fixes?
Excerpts from Andrew Dunstan's message of vie may 27 08:53:50 -0400 2011: > In the immortal words of Robert Haas: "Hey, look! An elephant!" This is Robert's $1000 tshirt, I think. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] dblink crash on PPC
Robert Haas wrote: > Andrew Dunstan wrote: >> >> Something odd is happening on buildfarm member wombat, a PPC970MP >> box running Gentoo. We're getting dblink test failures. On the >> one I << looked at more closely I saw this: >> >> [4ddf2c59.7aec:153] LOG: disconnection: session time: >> 0:00:00.444 >> user=markwkm database=contrib_regression host=[local] >> >> and then: >> >> [4ddf2c4e.79d4:2] LOG: server process (PID 31468) was terminated >> by signal 11: Segmentation fault >> [4ddf2c4e.79d4:3] LOG: terminating any other active server >> processes >> >> which makes it look like something is failing badly in the >> backend cleanup code. (7aec = hex(31468)) >> >> We don't seem to have a backtrace, which is sad. >> >> This seems to be happening on the 9.0 branch too. >> >> I wonder what it could be? > > Around when did it start failing? According to the buildfarm logs the first failure was roughly 1 day 10 hours 40 minutes before this post. Keep in mind that PPC is a platform with weak memory ordering -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Vacuum, visibility maps and SKIP_PAGES_THRESHOLD
On Fri, May 27, 2011 at 7:41 PM, Heikki Linnakangas wrote: > On 27.05.2011 16:52, Pavan Deolasee wrote: >> >> On closer inspection, I realized that we have >> deliberately put in this hook to ensure that we use visibility maps >> only when we see at least SKIP_PAGES_THRESHOLD worth of all-visible >> sequential pages to take advantage of possible OS seq scan >> optimizations. > > That, and the fact that if you skip any page, you can't advance > relfrozenxid. Hmm. For a significantly large table, wouldn't it be the case that we would most likely skip one page somewhere ? Would it be better that we instead do a full scan every once in a while instead of relying on a not-so-well-understood heuristic ? > >> My statistical skills are limited, but wouldn't that mean that for a >> fairly well distributed write activity across a large table, if there >> are even 3-4% update/deletes, we would most likely hit a >> not-all-visible page for every 32 pages scanned ? That would mean that >> almost entire relation will be scanned even if the visibility map >> tells us that only 3-4% pages require scanning ? And the probability >> will increase with the increase in the percentage of updated/deleted >> tuples. Given that the likelihood of anyone calling VACUUM (manually >> or through autovac settings) on a table which has less than 3-4% >> updates/deletes is very low, I am worried that might be loosing all >> advantages of visibility maps for a fairly common use case. > > Well, as with normal queries, it's usually faster to just seqscan the whole > table if you need to access more than a few percent of the pages, because > sequential I/O is so much faster than random I/O. The visibility map really > only helps if all the updates are limited to some part of the table. The vacuum scan is not a complete random scan. So I am not sure how effective a complete seq scan be. May be we need to run some tests to measure that too before we choose one over the other. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Vacuum, visibility maps and SKIP_PAGES_THRESHOLD
On Fri, May 27, 2011 at 7:11 AM, Heikki Linnakangas wrote: > Well, as with normal queries, it's usually faster to just seqscan the whole > table if you need to access more than a few percent of the pages, because > sequential I/O is so much faster than random I/O. Well it's not strictly random access, you're still reading sequentially, you're just skipping some pages. It'll never be slower than a sequential scan it just might not be any faster. In my testing reading every 8th page took exactly as long as reading every page, which makes sense as the drive still has to seek to every track exactly as if you were reading sequentially. IIRC reading less than every 8th page started seeing a speedup. >> Do we have any numbers to prove what we have today is good ? Sorry, I >> may not have followed the discussions very closely in the past and not >> sure if this has been debated/tested already. > > I think that number was chosen quite arbitrary. When you consider updating > relfrozenxid, it's a bit difficult to decide what the optimal value would > be; if you decide to skip pages you might have to perform an extra > anti-wraparound somewhere down the line. It would be nice if the VM had a bit for "all-frozen" but that wouldn't help much except in the case of truly cold data. We could perhaps keep the frozen data per segment or per VM page (which covers a large section of the table) which would at least mean that would have a fixed amount of data become vacuum-dirty when a tuple is updated rather than a whole table which could be arbitrarily large. Separately it's a bit strange that we actually have to visit the pages. We have all the information we need in the VM to determine whether there's a run of 32 vacuum-clean pages. Why can't we look at the next 32 pages and if they're all vacuum-clean then skip looking at the heap at all for them. What we do now is do the regular vacuum algorithm and only after we've processed 32 pages in a row realize that it was a waste of effort. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] dblink crash on PPC
On Fri, May 27, 2011 at 8:44 AM, Andrew Dunstan wrote: > > Something odd is happening on buildfarm member wombat, a PPC970MP box > running Gentoo. We're getting dblink test failures. On the one I looked at > more closely I saw this: > > [4ddf2c59.7aec:153] LOG: disconnection: session time: 0:00:00.444 > user=markwkm database=contrib_regression host=[local] > > and then: > > [4ddf2c4e.79d4:2] LOG: server process (PID 31468) was terminated by signal > 11: Segmentation fault > [4ddf2c4e.79d4:3] LOG: terminating any other active server processes > > which makes it look like something is failing badly in the backend cleanup > code. (7aec = hex(31468)) > > We don't seem to have a backtrace, which is sad. > > This seems to be happening on the 9.0 branch too. > > I wonder what it could be? Around when did it start failing? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Vacuum, visibility maps and SKIP_PAGES_THRESHOLD
On Fri, May 27, 2011 at 7:36 PM, Tom Lane wrote: > Pavan Deolasee writes: >> My statistical skills are limited, but wouldn't that mean that for a >> fairly well distributed write activity across a large table, if there >> are even 3-4% update/deletes, we would most likely hit a >> not-all-visible page for every 32 pages scanned ? > > Huh? With a typical table density of several dozen tuples per page, an > update ratio in that range would mean that just about every page would > have something for VACUUM to do, if the modified tuples are evenly > distributed. The case where the skip optimization has some use is where > there are large "cold" sections that have no changes at all. > I was pretty sure that I would have done my maths wrong :-) So that means, even for far lesser update ratio, we would pretty much scan every block and vacuum many of them for a typical well distributed updates. Hmm. That means the idea of a single pass vacuum is interesting even after visibility maps. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [ADMIN] pg_class reltuples/relpages not updated by autovacuum/vacuum
On Thu, May 26, 2011 at 5:50 PM, Tom Lane wrote: > "Kevin Grittner" writes: >> When we prune or vacuum a page, I don't suppose we have enough >> information about that page's previous state to calculate a tuple >> count delta, do we? That would allow a far more accurate number to >> be maintained than anything suggested so far, as long as we tweak >> autovacuum to count inserts toward the need to vacuum. > > Well, that was the other direction that was suggested upthread: stop > relying on reltuples at all, but use the stats collector's counts. > That might be a good solution in the long run, but there are some > issues: > > 1. It's not clear how using a current count, as opposed to > time-of-last-vacuum count, would affect the behavior of the autovacuum > control logic. At first glance I think it would break it, since the > basic logic there is "how much of the table changed since it was last > vacuumed?". Even if the equations could be modified to still work, > I remember enough feedback control theory from undergrad EE to think that > this is something to be seriously scared of tweaking without extensive > testing. IMO it is far more risky than what Robert is worried about. Yeah, I think that would be broken. > 2. You still have the problem that we're exposing inaccurate (or at > least less accurate than they could be) counts to the planner and to > onlooker clients. We could change the planner to also depend on the > stats collector instead of reltuples, but at that point you just removed > the option for people to turn off the stats collector. The implications > for plan stability might be unpleasant, too. > > So that's not a direction I want to go without a significant amount > of work and testing. FWIW, I agree. Your proposed solution is certainly better than trying to do this; but it still seems a bit shaky to me. Still, maybe we don't have a better option. If it were me, I'd add an additional safety valve: use your formula if the percentage of the relation scanned is above some threshold where there's unlikely to be too much skew. But if the percentage scanned is too small, then don't use that formula. Instead, only update relpages/reltuples if the relation is now larger; set relpages to the new actual value, and scale up reltuples proportionately. However, I just work here. It's possible that I'm worrying about a problem that won't materialize in practice. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Vacuum, visibility maps and SKIP_PAGES_THRESHOLD
2011/5/27 Cédric Villemain : > 2011/5/27 Pavan Deolasee : >> I wonder if we have tested the reasoning behind having >> SKIP_PAGES_THRESHOLD and the magic number of 32 assigned to it >> currently. While looking at the code after a long time and doing some >> tests, I realized that a manual VACUUM would always scan first 31 >> pages of a relation which has not received any write activity since >> the last VACUUM. On closer inspection, I realized that we have >> deliberately put in this hook to ensure that we use visibility maps >> only when we see at least SKIP_PAGES_THRESHOLD worth of all-visible >> sequential pages to take advantage of possible OS seq scan >> optimizations. >> >> My statistical skills are limited, but wouldn't that mean that for a >> fairly well distributed write activity across a large table, if there >> are even 3-4% update/deletes, we would most likely hit a >> not-all-visible page for every 32 pages scanned ? That would mean that > > The page skip is still based on VM. > So you wonder what are the chances of a VM not up-to-date when we access it ? re-reading the mails and answers, I misunderstood the case you exposed. > >> almost entire relation will be scanned even if the visibility map >> tells us that only 3-4% pages require scanning ? And the probability >> will increase with the increase in the percentage of updated/deleted >> tuples. Given that the likelihood of anyone calling VACUUM (manually >> or through autovac settings) on a table which has less than 3-4% >> updates/deletes is very low, I am worried that might be loosing all >> advantages of visibility maps for a fairly common use case. >> >> Do we have any numbers to prove what we have today is good ? Sorry, I >> may not have followed the discussions very closely in the past and not >> sure if this has been debated/tested already. >> >> Thanks, >> Pavan >> >> >> >> >> >> -- >> Pavan Deolasee >> EnterpriseDB http://www.enterprisedb.com >> >> -- >> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-hackers >> > > > > -- > Cédric Villemain 2ndQuadrant > http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support > -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] What is the best and easiest implementation to reliably wait for the completion of startup?
"MauMau" writes: > The bad thing is that pg_ctl continues to wait until the specified duration > passes, even if postgres fails to start. For example, it is naturally > desirable for pg_ctl to terminate when postgresql.conf contains a syntax > error. Hmm, I thought we'd fixed this in the last go-round of pg_ctl wait revisions, but testing proves it does not work desirably in HEAD: not only does pg_ctl wait till its timeout elapses, but it then reports "server started" even though the server didn't start. That's clearly a bug :-( I think your proposal of a pipe-based solution might be overkill though. Seems like it would be sufficient for pg_ctl to give up if it doesn't see the postmaster.pid file present within a couple of seconds of postmaster startup. I don't really want to add logic to the postmaster to have the sort of reporting protocol you propose, because not everybody uses pg_ctl to start the postmaster. In any case, we need a fix in 9.1 ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Vacuum, visibility maps and SKIP_PAGES_THRESHOLD
On 27.05.2011 16:52, Pavan Deolasee wrote: On closer inspection, I realized that we have deliberately put in this hook to ensure that we use visibility maps only when we see at least SKIP_PAGES_THRESHOLD worth of all-visible sequential pages to take advantage of possible OS seq scan optimizations. That, and the fact that if you skip any page, you can't advance relfrozenxid. My statistical skills are limited, but wouldn't that mean that for a fairly well distributed write activity across a large table, if there are even 3-4% update/deletes, we would most likely hit a not-all-visible page for every 32 pages scanned ? That would mean that almost entire relation will be scanned even if the visibility map tells us that only 3-4% pages require scanning ? And the probability will increase with the increase in the percentage of updated/deleted tuples. Given that the likelihood of anyone calling VACUUM (manually or through autovac settings) on a table which has less than 3-4% updates/deletes is very low, I am worried that might be loosing all advantages of visibility maps for a fairly common use case. Well, as with normal queries, it's usually faster to just seqscan the whole table if you need to access more than a few percent of the pages, because sequential I/O is so much faster than random I/O. The visibility map really only helps if all the updates are limited to some part of the table. For example, if you only recent records are updated frequently, and old ones are almost never touched. Do we have any numbers to prove what we have today is good ? Sorry, I may not have followed the discussions very closely in the past and not sure if this has been debated/tested already. I think that number was chosen quite arbitrary. When you consider updating relfrozenxid, it's a bit difficult to decide what the optimal value would be; if you decide to skip pages you might have to perform an extra anti-wraparound somewhere down the line. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Vacuum, visibility maps and SKIP_PAGES_THRESHOLD
2011/5/27 Pavan Deolasee : > I wonder if we have tested the reasoning behind having > SKIP_PAGES_THRESHOLD and the magic number of 32 assigned to it > currently. While looking at the code after a long time and doing some > tests, I realized that a manual VACUUM would always scan first 31 > pages of a relation which has not received any write activity since > the last VACUUM. On closer inspection, I realized that we have > deliberately put in this hook to ensure that we use visibility maps > only when we see at least SKIP_PAGES_THRESHOLD worth of all-visible > sequential pages to take advantage of possible OS seq scan > optimizations. > > My statistical skills are limited, but wouldn't that mean that for a > fairly well distributed write activity across a large table, if there > are even 3-4% update/deletes, we would most likely hit a > not-all-visible page for every 32 pages scanned ? That would mean that The page skip is still based on VM. So you wonder what are the chances of a VM not up-to-date when we access it ? > almost entire relation will be scanned even if the visibility map > tells us that only 3-4% pages require scanning ? And the probability > will increase with the increase in the percentage of updated/deleted > tuples. Given that the likelihood of anyone calling VACUUM (manually > or through autovac settings) on a table which has less than 3-4% > updates/deletes is very low, I am worried that might be loosing all > advantages of visibility maps for a fairly common use case. > > Do we have any numbers to prove what we have today is good ? Sorry, I > may not have followed the discussions very closely in the past and not > sure if this has been debated/tested already. > > Thanks, > Pavan > > > > > > -- > Pavan Deolasee > EnterpriseDB http://www.enterprisedb.com > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Vacuum, visibility maps and SKIP_PAGES_THRESHOLD
Pavan Deolasee writes: > My statistical skills are limited, but wouldn't that mean that for a > fairly well distributed write activity across a large table, if there > are even 3-4% update/deletes, we would most likely hit a > not-all-visible page for every 32 pages scanned ? Huh? With a typical table density of several dozen tuples per page, an update ratio in that range would mean that just about every page would have something for VACUUM to do, if the modified tuples are evenly distributed. The case where the skip optimization has some use is where there are large "cold" sections that have no changes at all. Having said that, I don't know how carefully we tested different values for SKIP_PAGES_THRESHOLD. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Vacuum, visibility maps and SKIP_PAGES_THRESHOLD
I wonder if we have tested the reasoning behind having SKIP_PAGES_THRESHOLD and the magic number of 32 assigned to it currently. While looking at the code after a long time and doing some tests, I realized that a manual VACUUM would always scan first 31 pages of a relation which has not received any write activity since the last VACUUM. On closer inspection, I realized that we have deliberately put in this hook to ensure that we use visibility maps only when we see at least SKIP_PAGES_THRESHOLD worth of all-visible sequential pages to take advantage of possible OS seq scan optimizations. My statistical skills are limited, but wouldn't that mean that for a fairly well distributed write activity across a large table, if there are even 3-4% update/deletes, we would most likely hit a not-all-visible page for every 32 pages scanned ? That would mean that almost entire relation will be scanned even if the visibility map tells us that only 3-4% pages require scanning ? And the probability will increase with the increase in the percentage of updated/deleted tuples. Given that the likelihood of anyone calling VACUUM (manually or through autovac settings) on a table which has less than 3-4% updates/deletes is very low, I am worried that might be loosing all advantages of visibility maps for a fairly common use case. Do we have any numbers to prove what we have today is good ? Sorry, I may not have followed the discussions very closely in the past and not sure if this has been debated/tested already. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] dblink crash on PPC
Something odd is happening on buildfarm member wombat, a PPC970MP box running Gentoo. We're getting dblink test failures. On the one I looked at more closely I saw this: [4ddf2c59.7aec:153] LOG: disconnection: session time: 0:00:00.444 user=markwkm database=contrib_regression host=[local] and then: [4ddf2c4e.79d4:2] LOG: server process (PID 31468) was terminated by signal 11: Segmentation fault [4ddf2c4e.79d4:3] LOG: terminating any other active server processes which makes it look like something is failing badly in the backend cleanup code. (7aec = hex(31468)) We don't seem to have a backtrace, which is sad. This seems to be happening on the 9.0 branch too. I wonder what it could be? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How can I check the treatment of bug fixes?
On 05/27/2011 08:36 AM, MauMau wrote: > Hello, > > I posted a patch for bug #6011 to pgsql-hackers several days ago. How > can I check the status of bug fixes? I'm worried that the patch might > be forgotten, because bug #5842 was missed for two months until Bruce > noticed it. > > In the immortal words of Robert Haas: "Hey, look! An elephant!" cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] How can I check the treatment of bug fixes?
Hello, I posted a patch for bug #6011 to pgsql-hackers several days ago. How can I check the status of bug fixes? I'm worried that the patch might be forgotten, because bug #5842 was missed for two months until Bruce noticed it. Regards MauMau -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] What is the best and easiest implementation to reliably wait for the completion of startup?
Hello, I've encountered a problem of PostgreSQL startup, and I can think of a simple solution for that. However, I don't yet have much knowledge about PostgreSQL implementation, I'd like to ask you about what is the best and easiest solution. If it is easy for me to work on during my spare time at home, I'm willing to implement the patch. [problem] I can't reliably wait for the completion of PostgreSQL startup. I want pg_ctl to wait until the server completes startup and accepts connections. Yes, we have "-w" and "-t wait_second" options of pg_ctl. However, what value should I specify to -t? I have to specify much time, say 3600 seconds, in case the startup processing takes long for crash recovery or archive recovery. The bad thing is that pg_ctl continues to wait until the specified duration passes, even if postgres fails to start. For example, it is naturally desirable for pg_ctl to terminate when postgresql.conf contains a syntax error. [solution idea] Use unnamed pipes for postmaster to notify pg_ctl of the completion of startup. That is: pg_ctl's steps: 1. create a pair of unnamed pipes. 2. starts postgres. 3. read the pipe, waiting for a startup completion message from postmaster. postmaster's steps: 1. inherit a pair of unnamed pipes from pg_ctl. 2. do startup processing. 3. write a startup completion message to the pipe, then closes the pipe. I'm wondering if this is correct and easy. One concern is whether postmaster can inherit pipes through system() call. Please give me your ideas. Of course, I would be very happy if some experienced community member could address this problem. And finally, do you think this should be handled as a bug, or an improvement in 9.2? Regards MauMau -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] kill -KILL: What happens?
On 27 May 2011 10:01, Florian Pflug wrote: > Anyway, I'm glad to see that Peter Geoghegan has picked this up > any turned this into an actual patch. > > Extremely cool! Thanks Florian. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Online base backup from the hot-standby
On 27.05.2011 09:09, Jun Ishiduka wrote:
> STEP1: Make startup process to acquire backup-end-position from
> not only backup-end record but also backup-history-file .
>* startup process allows to acquire backup-end-position
> from backup-history-file .
>* When pg_basebackup is executed , backup-history-file is
> sent to the designated backup server .
I don't much like that approach. The standby would need to be able to
write the backup history file to the archive at the end of backup, and
we'd have to reintroduce the code to fetch it from archive and, when
streaming, from the master. At the moment, the archiver doesn't even run
in the standby.
I think we'll need to write the end-of-backup location somewhere in the
base backup instead. pg_stop_backup() already returns it, the client
just needs to store it somewhere with the base backup. So I'm thinking
that the procedure for taking a base backup from slave would look
something like this:
1. psql postgres -c "SELECT pg_start_backup('label')";
2. tar cvzf basebackup.tar.gz $PGDATA
3. psql postgres -c "SELECT pg_stop_backup()"; > backup_end_location
4. (keep backup_end_location alongside basebackup.tar.gz)
Or, we can just document that the control file must be backed up *last*,
so that the minimum recovery point in the control file serves the same
purposes as the end-of-backup location.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] switch UNLOGGED to LOGGED
On Fri, May 27, 2011 at 10:49:13AM +0100, Leonardo Francalanci wrote: > > From: Noah Misch > > > - the patch is missing the "send all table pages to the > > > standby" part; is there some code I can use as base? > > > > Nothing comes to mind as especially similar. > > > > > I guess I have to generate some special log type that > > > is only "played" by standby servers. > > > > What you described in your followup mail seemed reasonable. > > > So, it's ok to have a log item that is replayed only if > > WalRcvInProgress() > > is true? No, that checks for WAL streaming in particular. A log-shipping standby needs the same treatment. > Is it a correct approach? I couldn't find any other way to > find out if we are in a standby or a master... InArchiveRecovery looks like the right thing, but it's currently static to xlog.c. Perhaps exporting that is the way to go. > > > - on the standby, the commit part should be played as it > > > is on the master (that is, removing the INIT fork). > > > The abort case is different though: it would mean > > > doing nothing on the master, while removing every forks > > > but the INIT fork on the standby. > > > Would it be ok to add to xl_xact_abort a new array of > > > RelFileNode(s), where for each one at abort all the forks, > > > except the init fork, have to be deleted by the standby > > > (while the master shouldn't do anything with them)? > > > I bet there's a cleaner solution... > > > > Your "use less space in xl_xact_commit patch" seems to be going in a good > > direction here. It would probably also be okay to do a > >ResetUnloggedRelations() > > on the standby at every abort of a transaction that had started an > > UNLOGGED > -> > > LOGGED conversion. That is, just a flag might be enough. > > ok, but that would mean that a transaction that aborts a conversion > would try to reset all unlogged relations (traversing all the FS)... > I don't know if that's acceptable performance-wise. I'm not sure, either, but I don't figure such operations will be at all common. nm -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] compatibility issue with DirectFunctionCall1
2011/5/27 Heikki Linnakangas : > On 27.05.2011 12:06, Pavel Stehule wrote: >> >> Hello, >> >> I am working on testing Orafce for PostgreSQL 9.1. I found a issue. >> >> I cannot directly call a function "lower". > > See DirectFunctionCall1Coll() ook Thank you Pavel > > -- > Heikki Linnakangas > EnterpriseDB http://www.enterprisedb.com > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] compatibility issue with DirectFunctionCall1
On 27.05.2011 12:06, Pavel Stehule wrote: Hello, I am working on testing Orafce for PostgreSQL 9.1. I found a issue. I cannot directly call a function "lower". See DirectFunctionCall1Coll() -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] switch UNLOGGED to LOGGED
> From: Noah Misch > > - the patch is missing the "send all table pages to the > > standby" part; is there some code I can use as base? > > Nothing comes to mind as especially similar. > > > I guess I have to generate some special log type that > > is only "played" by standby servers. > > What you described in your followup mail seemed reasonable. So, it's ok to have a log item that is replayed only if WalRcvInProgress() is true? Is it a correct approach? I couldn't find any other way to find out if we are in a standby or a master... > > - on the standby, the commit part should be played as it > > is on the master (that is, removing the INIT fork). > > The abort case is different though: it would mean > > doing nothing on the master, while removing every forks > > but the INIT fork on the standby. > > Would it be ok to add to xl_xact_abort a new array of > > RelFileNode(s), where for each one at abort all the forks, > > except the init fork, have to be deleted by the standby > > (while the master shouldn't do anything with them)? > > I bet there's a cleaner solution... > > Your "use less space in xl_xact_commit patch" seems to be going in a good > direction here. It would probably also be okay to do a >ResetUnloggedRelations() > on the standby at every abort of a transaction that had started an UNLOGGED -> > LOGGED conversion. That is, just a flag might be enough. ok, but that would mean that a transaction that aborts a conversion would try to reset all unlogged relations (traversing all the FS)... I don't know if that's acceptable performance-wise. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] switch UNLOGGED to LOGGED
On Fri, May 20, 2011 at 09:37:20AM +0100, Leonardo Francalanci wrote: > I'll try to sum up what I understood: > > 1) the standby keeps the lock, so no problem with > stray files coming from the unlogged->logged log > reply, as the table can't be read during the operation > > 2) calling ResetUnloggedRelations before > ProcArrayApplyRecoveryInfo would remove the problem > of the stray files on the standby in case of master crash > before commit/abort > > 3) promoting the standby shouldn't be an issue, > since ResetUnloggedRelations is already called in > ShutdownRecoveryTransactionEnvironment All correct, as far as I can tell. > Now, to move forward, some questions: > > - the patch is missing the "send all table pages to the > standby" part; is there some code I can use as base? Nothing comes to mind as especially similar. > I guess I have to generate some special log type that > is only "played" by standby servers. What you described in your followup mail seemed reasonable. > - on the standby, the commit part should be played as it > is on the master (that is, removing the INIT fork). > The abort case is different though: it would mean > doing nothing on the master, while removing every forks > but the INIT fork on the standby. > Would it be ok to add to xl_xact_abort a new array of > RelFileNode(s), where for each one at abort all the forks, > except the init fork, have to be deleted by the standby > (while the master shouldn't do anything with them)? > I bet there's a cleaner solution... Your "use less space in xl_xact_commit patch" seems to be going in a good direction here. It would probably also be okay to do a ResetUnloggedRelations() on the standby at every abort of a transaction that had started an UNLOGGED -> LOGGED conversion. That is, just a flag might be enough. nm -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] kill -KILL: What happens?
On May7, 2011, at 03:50 , Robert Haas wrote: > On Sat, Jan 15, 2011 at 10:44 AM, Florian Pflug wrote: >> I've realized that POSIX actually *does* provide a way to receive a signal - >> the SIGIO machinery. I've modified my test case do to that. To simplify >> things, >> I've removed support for multiple life sign objects. >> >> > Are you planning to develop this into a patch for 9.2? Sorry for the extremely late answer - I received this mail while I was on vacation, and then forgot to answer it once I came back :-( Anyway, I'm glad to see that Peter Geoghegan has picked this up any turned this into an actual patch. Extremely cool! best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] compatibility issue with DirectFunctionCall1
Hello,
I am working on testing Orafce for PostgreSQL 9.1. I found a issue.
I cannot directly call a function "lower".
Is it correct?
select dbms_assert.enquote_name('''"AAA');
! enquote_name.
! --
! "'""aaa"
! (1 row)
!.
select dbms_assert.enquote_name('''"AAA', false);
enquote_name.
--
--- 1180,1188
(1 row)
..
select dbms_assert.enquote_name('''"AAA');
! ERROR: could not determine which collation to use for lower() function
! HINT: Use the COLLATE clause to set the collation explicitly.
! CONTEXT: SQL function "enquote_name" statement 1
select dbms_assert.enquote_name('''"AAA', false);
enquote_name.
Datum
dbms_assert_enquote_name(PG_FUNCTION_ARGS)
{
<-->Datum name = PG_GETARG_DATUM(0);
<-->bool loweralize = PG_GETARG_BOOL(1);
<-->name = DirectFunctionCall1(quote_ident, name);
<-->if (loweralize)
<--><-->name = DirectFunctionCall1(lower, name);
<-->PG_RETURN_DATUM(name);
}
Regards
Pavel Stehule
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] "errno" not set in case of "libm" functions (HPUX)
On Fri, May 27, 2011 at 2:31 AM, Tom Lane wrote: > Peter Eisentraut writes: > > On tor, 2011-05-26 at 12:14 -0400, Tom Lane wrote: > >> I tried this on my HP-UX 10.20 box, and it didn't work very nicely: > >> configure decided that the compiler accepted +Olibmerrno, so I got a > >> compile full of > >> cc: warning 450: Unrecognized option +Olibmerrno. > >> warnings. The reason is that PGAC_PROG_CC_CFLAGS_OPT does not pay any > >> attention to whether the proposed flag generates a warning. That seems > >> like a bug --- is there any situation where we'd want to accept a flag > >> that does generate a warning? I'm thinking that macro should set > >> ac_c_werror_flag=yes, the same way PGAC_C_INLINE does. > > > I think so. > > OK, committed with that addition. > > Thanks, Is it worth to backport this? > > We could also do that globally, but that would probably be something for > > the next release. > > Hmm. I'm a bit scared of how much might break. I don't think the > autoconf tests are generally designed to guarantee no warnings. > >regards, tom lane > -- Ibrar Ahmed
