Re: [HACKERS] A costing analysis tool
Yes I have looked at the TODO list. There is arguably a relationship to: * Have EXPLAIN ANALYZE highlight poor optimizer estimates * Log queries where the optimizer row estimates were dramatically different from the number of rows actually found? Neither of these, however, provides a systematic way to identify problem areas in costing. Nor do they provide systematic regression testing when costing is modified. I was largely motivated to think in the direction of starting with the tool I describe by this post: http://archives.postgresql.org/pgsql-hackers/2005-10/msg00434.php Also Tom Lane mentioned the need for test cases and doubt about whether a particular fix would help or hurt overall. For example: http://archives.postgresql.org/pgsql-hackers/2005-10/msg00417.php The tool I propose would be non-invasive -- it would be a client to the back end to help guide and check the actual back end enhancements. This all started because in some of our real-life queries the optimizer is looking at a reasonable set of available plans, and picking one which runs several times slower than one of the alternatives. The problem is clearly that the cost numbers don't approximate reality closely enough. I'm not convinced that the proposed adjustment is a good idea -- it might cause other queries which run fine now to shift to a suboptimal plan, and it might not go far enough toward solving the problem case. The best solution might be somewhat more sophisticated. I suspect that consideration of effective cache size and the expected iteration count might be necessary to get consistenly good cost estimates without breaking anything else. Nobody wants me to try something like that without a good way to do regression testing. At least, that's the impression I've gotten. And really, it's hard to pin down where the problem really lies without a tool like this. Personally, I suspect that part of the problem is an underestimation of the cost of the sort or the mergejoin. I had read through the TODO list several times, and in response to your post searched it again for key words like: tune, tuning, diagnostic, cost, estimate, and plan I haven't been able to spot anything that seems to address the area covered by the proposed tool. Is there something I'm overlooking? My client is willing to pay for my time to address the issue which is causing them a problem, and share that work with the PostgreSQL community. I don't think I'd get the same response regarding something which is not a demonstrated problem for them. I'm certainly not looking to get adversarial with anyone, or to bypass any part of the process. I am continually impressed by the quality of PostgreSQL, and even more impressed by the people posting to these lists, and the assistance they provide to the community. My client and I both hope to give something back as it meshes with our needs and falls within the capabilities of our staff. If this idea survives the conceptual discussions, I'll suggest a TODO item (if nobody beats me to it), so that it's on the record -- that seems only reasonable, to prevent duplicate efforts. Thanks for your response, and any further pointers you can provide. -Kevin Bruce Momjian pgman@candle.pha.pa.us 10/12/05 8:27 PM Have you looked at the TODO list to see our previous ideas on tuning diagnotics? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] A costing analysis tool
Good points, Tom. (I wish my client's email software supported quoting so that I could post replies closer to your points. Sorry 'bout that.) I tried searching the archives, though, and the words I could think to search with generated so many hits that it seemed more or less like a sequential search of the archives, which is daunting. If you have any particular references, suggestions for search strings I might have missed, or even a time range when you think it was discussed, I'll gladly go looking again. I'm not out to reinvent the wheel, lever, or any other basic idea. To cover the database fits in RAM situation, we could load some data, run test cases twice, using only the info from the second run, and never flush. Then we could load more data and get on to the cases where not everything is cached. I don't think we can get huge -- these tests have to run in a reasonable amount of time, but I hope we can load enough to get the major scaling effects covered. So far my wildest dreams have not gone beyond a few simple math operations to get to a cost estimate. Only testing will tell, but I don't think it will be significant compared to the other things going on in the planner. (Especially if I can compensate by talking you into letting me drop that ceil function on the basis that without it we're getting the statistical average of the possible actual costs.) It's even possible that more accurate costing of the current alternatives will reduce the need for other, more expensive, optimizer enhancements. (That glass is half FULL, I SWEAR it!) How do you establish that a cost estimate is completely out of line with reality except by comparing its runtime/estimate ratio with others? Unless you're saying not to look at just the summary level, in which case I totally agree -- any one subplan which has an unusual ratio in either direction needs to be examined. If you're getting at something else, please elaborate -- I don't want to miss anything. Thanks for your response. -Kevin Tom Lane [EMAIL PROTECTED] 10/13/05 12:01 AM Kevin Grittner [EMAIL PROTECTED] writes: Note that I'm talking about a tool strictly to check the accuracy of the estimated costs of plans chosen by the planner, nothing else. We could definitely do with some infrastructure for testing this. I concur with Bruce's suggestion that you should comb the archives for previous discussions --- but if you can work on it, great! (2) A large database must be created for these tests, since many issues don't show up in small tables. The same data must be generated in every database, so results are comparable and reproducable. Reproducibility is way harder than it might seem at first glance. What's worse, the obvious techniques for creating reproducible numbers amount to eliminating variables that are important in the real world. (One of which is size of database --- some people care about performance of DBs that fit comfortably in RAM...) Realistically, the planner is never going to have complete information. We need to design planning models that generally get the right answer, but are not so complicated that they are (a) impossible to maintain or (b) take huge amounts of time to compute. (We're already getting some flak on the time the planner takes.) So there is plenty of need for engineering compromise here. Still, you can't engineer without raw data, so I'm all for creating a tool that lets us gather real-world cost data. The only concrete suggestion I have at the moment is to not design the tool directly around measure the ratio of real time to cost. That's only meaningful if the planner's cost model is already basically correct and you are just in need of correcting the cost multipliers. What we need for the near term is ways of quantifying cases where the cost models are just completely out of line with reality. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] A costing analysis tool
Ouch! I just remembered locale and character sets and encoding. I can't even begin to get my head around what to do with those, unless it is just to make the tool agnostic regarding those issues and test against a variety of setups. Does that seem adequate? I flash back to my first attempts to use LIKE 'XXX%' under PostgreSQL... -Kevin Tom Lane [EMAIL PROTECTED] 10/13/05 12:01 AM Reproducibility is way harder than it might seem at first glance. What's worse, the obvious techniques for creating reproducible numbers amount to eliminating variables that are important in the real world. (One of which is size of database --- some people care about performance of DBs that fit comfortably in RAM...) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Comments on columns in the pg_catalog tables/views
David Fetter wrote: Dept of second thoughts: actually, perhaps see if you can generate the pg_description entries from the C comments in the include/catalog header files. There's already a strong motivation to hold those to shorter-than-a-line length, whereas the column descriptions in catalogs.sgml tend to run on a little longer, and wouldn't format nicely in \dt+. My thought is that by the time somebody is doing \dt+ (or equivalent in other tools than psql) on a pg_catalog table or view, they need to see details and are at most slightly concerned about the formatting. And not all tools have this formatting issue... I like the long comments I can take from pg_settings, enabling pgAdmin to deliver precise information on each config option. Regards, Andreas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Minor point about contrib/xml2 functions IMMUTABLE marking
Neil Conway wrote: On Wed, 2005-12-10 at 23:46 -0400, Bruce Momjian wrote: Agreed. I have changed them both to stable. I think xslt_process() should be stable because it is unlikely you would want a URL's contents to change inside a transaction Why is it unlikely? If a function's return value for a particular set of arguments could change within a single table scan, the function is volatile -- ISTM xslt_process() clearly falls within that definition. My thought was that a web page lookup is going to be a very expensive operation, so you would not want it to requery inside a transaction. It is not like random() where you want it to be re-called and it is inexpensive. Our documentation says about VOLATILE: VOLATILE indicates that the function value can change even within a single table scan, so no opti- mizations can be made. Relatively few database functions are volatile in this sense; some examples are random(), currval(), timeofday(). Note that any function that has side-effects must be classified volatile, even if its result is quite predictable, to prevent calls from being optimized away; an example is setval(). and I didn't think a web page lookup fit in that category. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Minor point about contrib/xml2 functions IMMUTABLE marking
Bruce Momjian pgman@candle.pha.pa.us writes: Neil Conway wrote: If a function's return value for a particular set of arguments could change within a single table scan, the function is volatile -- ISTM xslt_process() clearly falls within that definition. My thought was that a web page lookup is going to be a very expensive operation, so you would not want it to requery inside a transaction. It is not like random() where you want it to be re-called and it is inexpensive. It's too expensive is not a valid rationale for claiming that something is stable when it is not. In any case, you have fallen into the all too common trap of supposing that these labels have something to do with caching function results. Calling it stable is not going to improve performance, only create a risk of wrong answers. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Darwin compile fixes
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Attached is a cleaned up version of the patch (without wrapping). The change is to use $(CXX) $(CFLAGS) instead of $(COMPILER). Does this change break OS/X? Since our configure doesn't define CXX, I'm having a hard time imagining how it could fail to break it. If we *did* define CXX, it would presumably point to g++, which would also break things since our source code is not C++ clean. Even more to the point, Darwin isn't broken now --- at least it works fine on the powerbook I'm typing this on, and on the several OSX machines in the buildfarm. I would like to know what this patch is alleged to fix before even considering applying it. Yea, I see in Makefile.shlib: COMPILER = $(CC) $(CFLAGS) so the code is really changing $CC to $CXX. The big question is why that change is required. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] Minor point about contrib/xml2 functions IMMUTABLE marking
Bruce Momjian pgman@candle.pha.pa.us writes: Well, should be marked as VOLATILE? A web lookup? Yes. Its value is determined by factors outside the database, so it has to be categorized as volatile. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Minor point about contrib/xml2 functions IMMUTABLE marking
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Well, should be marked as VOLATILE? A web lookup? Yes. Its value is determined by factors outside the database, so it has to be categorized as volatile. OK, done. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Minor point about contrib/xml2 functions IMMUTABLE marking
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Neil Conway wrote: If a function's return value for a particular set of arguments could change within a single table scan, the function is volatile -- ISTM xslt_process() clearly falls within that definition. My thought was that a web page lookup is going to be a very expensive operation, so you would not want it to requery inside a transaction. It is not like random() where you want it to be re-called and it is inexpensive. It's too expensive is not a valid rationale for claiming that something is stable when it is not. In any case, you have fallen into the all too common trap of supposing that these labels have something to do with caching function results. Calling it stable is not going to improve performance, only create a risk of wrong answers. Well, should be marked as VOLATILE? A web lookup? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] pg_config --pgxs on Win32
Something changed very recently in the output from pg_config --pgxs command on Win32. It now outputs double backslash everywhere instead of forward slashes. The mingw GNU Make is not too happy about the double backslashes. I do: export PGXS := $(dir $(shell pg_config --pgxs)) and now it yields ./ I tried to do the following: $(warning PGXS = $(dir c:\\msys\\local\\pgsql\\lib\\pgxs\\src\\MAKEFI~1\\pgxs.mk)) $(warning PGXS = $(dir c:/msys/local/pgsql/lib/pgxs/src/MAKEFI~1\\pgxs.mk)) The first one yields ./, the second is OK. I guess it happened when changing to using short-name represenation? Regards, Thomas Hallgren ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Allowed timezone values
I am playing with our allowed timezone settings and saw a few strange things. It understands EST5EDT, but how does it understand XYT5ABT? test= set timezone = 'XYT5ABT'; SET test= SELECT current_timestamp; now -- 2005-10-13 12:48:39.59164-04 (1 row) test= SELECT current_timestamp + '3 months'; ?column? --- 2006-01-13 12:48:32.765068-05 (1 row) (Shouldn't current_timestamp's label be current_timestamp, and not now?) Also, JST doesn't work anymore, but JST9 does. JST has no daylight savings time values, so it is a valid timezone setting, while EST is not: test= set timezone = 'JST'; ERROR: unrecognized time zone name: JST test= set timezone = 'JST9'; SET -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] Last call for back-branch fixes
This has been saved for the 8.2 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Greg Sabino Mullane wrote: [ There is text before PGP section. ] -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 ! (void) sprintf((char *) rp, %03o, *vp); ! (void) snprintf((char *) rp, 6, %03o, *vp); Hardly looks like a critical fix, or even a needed one. Why bother? No, not critical. Just something that tripped me up a few months ago when writing some code based on this section, and I finally remembered it when I saw your email. It's more of a nice to have, as the surrounding code should prevent the %03o from making more than 3 characters. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200510032127 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFDQdq9vJuQZxSWSsgRAgeRAKCujcqJFtPf+LSlQ17BBCbGmfelmACgr30s 5OFfHfL3evrwV/FQI/XRMJI= =ZTcO -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] pg_config --pgxs on Win32
Thomas Hallgren [EMAIL PROTECTED] writes: Something changed very recently in the output from pg_config --pgxs command on Win32. It now outputs double backslash everywhere instead of forward slashes. The mingw GNU Make is not too happy about the double backslashes. I said that was a bad idea when it was done. I'll undo it. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] Work-in-progress referential action trigger
On Friday 09 September 2005 08:46, Stephan Szabo wrote: On Fri, 9 Sep 2005, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: Is there a case other than a before trigger updating a row we will want to act upon later in the statement where we'll get a row with xmax of our transaction and cmax greater than the current command? The greater-cmax case could occur via any kind of function, not only a trigger, ie update tab set x = foo(x) where ... where foo() is a volatile function that internally updates the tab table. I *thought* I was missing a case, I just couldn't figure out what. I suppose you could say that this is horrible programming practice and anyone who tries it deserves whatever weird behavior ensues ... but it's not the case that every such situation involves a trigger. Well, the change I was thinking of would have made it an error if foo(x) updated a row that was then later selected by the update rather than the current behavior which I think would have ignored the already updated row, so that's probably not going to work. I see that this still is not addressed fulling in beta 3. Can anybody give a quick overview of where this is sitting, and if it's likely to make it's way into 8.1 gold ? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Darcy Buskermolen Wavefire Technologies Corp. http://www.wavefire.com ph: 250.717.0200 fx: 250.763.1759 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] A costing analysis tool
Kevin, I'm looking at trying to fix some clear flaws in costing which cause of our real-world queries to choose sub-optimal plans under PostgreSQL. It's clear that there needs to be a tool to analyze the accuracy of costing for a variety of queries, both to direct any efforts to fix problems and to test for possible costing regressions. As far as I can tell, no such tool currently exists. If I've missed something, please let me know, even if it's ad hoc or incomplete. Actually, this is pretty completely what I've been thinking about for the last year. I'm very happy that someone else is interested in working on it. (2) A large database must be created for these tests, since many issues don't show up in small tables. The same data must be generated in every database, so results are comparable and reproducable. (3) Developers should be able to easily add test cases, either for their own use or contributed to the community. Sure. However, I think it's important to seperate the test cases from the cost collection tool. Our *best* test cases will be real production applications. For synthetic test cases, we can look to improving DBT-OSDL, Jan-TPCW, OSDBB and eDB's test (if they ever publish it). The only thing that mess of tests is lacking is easy setup and portability. (7) I envision a process to create a test database, populate it, run a series of test cases with EXPLAIN ANALYZE, capture the results, parse the results and store them in a database, analyze the results to find means and standard deviations both overall and for each type of plan, and report summaries and outliers -- with references to the test cases. The primary statistic of interest is actual time divided by cost. This seems like it would be of interest overall, and within the permutations mentioned above for a single query. I would actually like to do this differently. I think an asynchronous logging mechanism is more useful, because there are cost estimation problems which don't show up except under conditions of concurrency and heavy server load. For this reason, it's very important that this kind of cost collection could be performed on a production application. What that would mean is some process whereby the system could sample, say, 5% of the queries being run (at random) and run EXPLAIN ANALYZEs against them, logging the results in a way that could be tabularized. Speaking of which, I think you're missing an important first step: tabular output for EXPLAIN ANALYZE. A whole host of query testing tools could be developed if it were easy to shove EA results into a format where statistics could be run on them. Without it, it's pretty hard to do the rest of the testing. So, what do you think? How much time do you have to spend on this? I'd like to offer you the TestPerf project on pgfoundry (www.pgfoundry.org/projects/testperf) as a container for your work on this idea. I also have access to a variety of test machines for performance tests. --Josh ---(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] Allowed timezone values
Bruce Momjian pgman@candle.pha.pa.us writes: I am playing with our allowed timezone settings and saw a few strange things. It understands EST5EDT, but how does it understand XYT5ABT? Because the code in src/timezone does what the POSIX standard says it must do. The relevant man page on my HPUX box says TZ TZ sets time zone information. TZ can be set using the format: [:]STDoffset[DST[offset][,rule]] where: STD and DST Three or more bytes that designate the standard time zone (STD) and summer (or daylight-savings) time zone (DST) STD is required. If DST is not specified, summer time does not apply in this locale. Any characters other than digits, comma (,), minus (-), plus (+), or ASCII NUL are allowed. offset offset is the value that must be added to local time to arrive at Coordinated Universal Time (UTC). Offset is of the form : hh[:mm[:ss]] ... etc etc ... Also, JST doesn't work anymore, but JST9 does. JST9 is valid per the POSIX rules. JST isn't listed as a zone name in the zic database, so it's not valid. (Try Japan instead.) regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Spinlocks, yet again: analysis and proposed patches
On Wed, 12 Oct 2005 18:44:50 +0100
Simon Riggs [EMAIL PROTECTED] wrote:
On Wed, 2005-09-14 at 13:32 -0400, Tom Lane wrote:
I wrote:
Another thought came to mind: maybe the current data layout for LWLocks
is bad. Right now, the spinlock that protects each LWLock data struct
is itself part of the struct, and since the structs aren't large (circa
20 bytes), the whole thing is usually all in the same cache line. ...
Maybe it'd be better to allocate the spinlocks off by themselves.
Well, this is odd. I made up a patch to do this (attached) and found
that it pretty much sucks. Still the 4-way Opteron, previous best
(slock-no-cmpb and spin-delay-2):
1 31s 2 42s 4 51s 8 100s
with lwlock-separate added:
1 31s 2 52s 4 106s 8 213s
What I had expected to see was a penalty in the single-thread case (due
to instructions added to the LWLock functions), but there isn't any.
I did not expect to see a factor-of-2 penalty for four threads.
I guess what this means is that there's no real problem with losing the
cache line while manipulating the LWLock, which is what the patch was
intended to prevent. Instead, we're paying for swapping two cache lines
(the spinlock and the LWLock) across processors instead of just one line.
But that should at worst be a 2x inflation of the time previously spent
in LWLockAcquire/Release, which is surely not yet all of the application
;-). Why the heck is this so bad?
(Just going back through the whole thread for completeness.)
Should we expect that apparently
minor changes in shared data structures might be costing equivalently
huge penalties in SMP performance elsewhere?
Yes. That's the advice from Intel and AMD; but we should add that there
is potential for improving performance also.
The possible problem we were trying to avoid here was false sharing of
the cache line by lock requestors of locks whose spin locks were
adjacent in memory.
Splitting the data structure was just one of the possible ways of
avoiding that. The above shows that the possible solution described
above didn't work, but there could be others.
One thing we tried in February was padding out the statically defined
locks with dummy lock definitions in the enum. This has the effect of
ensuring that the most contested locks are very definitely in their own
cache line and not shared with others.
That showed a noticeable improvement in performance, probably because it
costs very little to implement, even if the code would require some
explanatory documentation.
The lwlock structure in include/storage/lwlock.h looks like
typedef enum LWLockId
{
BufMappingLock,
BufFreelistLock,
LockMgrLock,
OidGenLock,
XidGenLock,
ProcArrayLock,
SInvalLock,
FreeSpaceLock,
WALInsertLock,
WALWriteLock,
...
Notice that the heavily contested locks (i.e. first 3 and the WAL locks)
are adjacent to at least one other heavily contested lock. So they are
certain to be in the same cache line and therefore to cause false
sharing (on all CPU types, not just Intel and AMD (ref: Manufacturer's
optimization handbooks).
This could be replaced by...
typedef enum LWLockId
{
BufMappingLock,
BufMappingLock_Padding1,
BufMappingLock_Padding2,
BufMappingLock_Padding3,
BufMappingLock_Padding4,
BufMappingLock_Padding5,
BufMappingLock_Padding6,
BufMappingLock_Padding7,
BufFreelistLock,
BufFreelistLock_Padding1,
BufFreelistLock_Padding2,
BufFreelistLock_Padding3,
BufFreelistLock_Padding4,
BufFreelistLock_Padding5,
BufFreelistLock_Padding6,
BufFreelistLock_Padding7,
LockMgrLock,
LockMgrLock_Padding1,
LockMgrLock_Padding2,
LockMgrLock_Padding3,
LockMgrLock_Padding4,
LockMgrLock_Padding5,
LockMgrLock_Padding6,
LockMgrLock_Padding7,
OidGenLock,
XidGenLock,
ProcArrayLock,
SInvalLock,
FreeSpaceLock,
WALInsertLock,
WALInsertLock_Padding1,
WALInsertLock_Padding2,
WALInsertLock_Padding3,
WALInsertLock_Padding4,
WALInsertLock_Padding5,
WALInsertLock_Padding6,
WALInsertLock_Padding7,
WALWriteLock,
WALWriteLock_Padding1,
WALWriteLock_Padding2,
WALWriteLock_Padding3,
WALWriteLock_Padding4,
WALWriteLock_Padding5,
WALWriteLock_Padding6,
WALWriteLock_Padding7,
...
where the number of padding locks is determined by how many lock
structures fit within a 128 byte cache line.
This isn't exactly elegant coding, but it provides a useful improvement
on an 8-way SMP box when run on 8.0 base. OK, lets be brutal: this looks
pretty darn stupid. But it does follow the CPU optimization handbook
advice and I did see a noticeable improvement in performance and a
Re: [HACKERS] [COMMITTERS] pgsql: Back out this because of fear of changing error strings: This
Bruce Momjian wrote: Back out this because of fear of changing error strings: This makes the error messages for PREPARE TRANSACTION, COMMIT PREPARED etc. match the docs, which talk about transaction identifier not gid or global transaction identifier. I say make the change anyway. No language except Turkish is up to 100% yet, and there's plenty of time to fix the translations. It's only a couple of strings, right? -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34 No deja de ser humillante para una persona de ingenio saber que no hay tonto que no le pueda enseñar algo. (Jean B. Say) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [COMMITTERS] pgsql: Do all accesses to shared buffer headers through
On Wed, Oct 12, 2005 at 11:49:47PM -0400, Tom Lane wrote:
That would certainly be better if possible, but AFAIK it's not.
(Perhaps there is a gcc-specific hack, but certainly not one that's
portable to all compilers. volatile is the only tool the C standard
gives us.)
Indeed. The linux kernel defines the following:
/* Optimization barrier */
/* The volatile is due to gcc bugs */
#define barrier() __asm__ __volatile__(: : :memory)
The memory keyword (as the gcc docs state):
If your assembler instruction modifies memory in an unpredictable
fashion, add 'memory' to the list of clobbered registers. This will
cause GNU CC to not keep memory values cached in registers across the
assembler instruction.
They use this bit in all the spinlock and other locking code
specifically for this purpose. You can do things like:
do { barrier(); } while( condition );
where condition uses any memory variable and it will reread it
everytime, just as if the variable was volatile.
have seen the bug. Still, I think we have to do the volatile pointers
in order to guarantee correct results on non-gcc compilers, so it's not
clear that there's any point in pursuing the question of whether gcc by
itself could offer a nicer solution.
Yes, we need to look for solutions for other compilers. We just need to
be careful and have people check the spinlock code carefully when they
use other compilers. Maybe in the porting guide?
--
Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/
Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.
pgpYH5bKXxSVJ.pgp
Description: PGP signature
Re: [HACKERS] pg_config --pgxs on Win32
-Original Message- From: Tom Lane[EMAIL PROTECTED] Sent: 13/10/05 18:23:13 To: Thomas Hallgren[EMAIL PROTECTED] Cc: pgsql-hackers@postgresql.orgpgsql-hackers@postgresql.org Subject: Re: [HACKERS] pg_config --pgxs on Win32 The mingw GNU Make is not too happy about the double backslashes. I said that was a bad idea when it was done. I'll undo it. We should probably document that pg_config may not work reliably with non-mingw tools in that case. Microsoft code may or may not do what is expected with front slashes. BTW Thomas - I thought you said \\ did work when you were testing options for me, or was that just msys rather than make? Regards, Dave -Unmodified Original Message- Thomas Hallgren [EMAIL PROTECTED] writes: Something changed very recently in the output from pg_config --pgxs command on Win32. It now outputs double backslash everywhere instead of forward slashes. The mingw GNU Make is not too happy about the double backslashes. I said that was a bad idea when it was done. I'll undo it. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] auto vacuum lock on 8.1beta1
I have a vacuum process kicked of by autovacuum that appears hung and causing general grief. When I put too many queries at the db in this state, the Context Switches cruises up to ~90k and stay there. Queries that normally take 1 second are up to over a minute. The autovacuum thread has been sitting on the db for 24 hours now. Might this be the same problem as the recent thread database vacuum from cron hanging where Tom is: I'm busy volatile-izing all the code in bufmgr.c ... should be able to commit a fix soon.? I can provide more information as requested. postgres 30463 1 0 Oct11 pts/000:00:02 /usr/local/pgsql810/bin/postmaster -D /var/lib/pgsql/data810 postgres 30466 30463 0 Oct11 pts/000:02:04 postgres: writer process postgres 30467 30463 0 Oct11 pts/000:02:07 postgres: stats buffer process postgres 30469 30467 0 Oct11 pts/000:03:48 postgres: stats collector process postgres 21119 30463 0 Oct12 pts/000:00:00 postgres: integration_test int_test_new 129.80.80.16(49465) idle in transaction postgres 21120 30463 0 Oct12 pts/000:00:00 postgres: integration_test int_test_new 129.80.80.16(49466) idle in transaction postgres 21121 30463 0 Oct12 pts/000:00:01 postgres: integration_test int_test_new 129.80.80.16(49467) idle in transaction postgres 21157 30463 0 Oct12 pts/000:00:00 postgres: integration_test int_test_new 129.80.80.16(49885) idle in transaction postgres 23592 30463 0 Oct12 pts/000:00:00 postgres: integration_test int_test_new 129.80.17.25(53872) idle in transaction postgres 23593 30463 0 Oct12 pts/000:00:00 postgres: integration_test int_test_new 129.80.17.25(53873) idle in transaction postgres 23594 30463 1 Oct12 pts/000:12:46 postgres: integration_test int_test_new 129.80.17.25(53874) idle in transaction postgres 7041 30463 0 09:34 pts/000:00:00 postgres: creagrs int_test_new 129.80.80.164(49930) idle postgres 21064 30463 0 Oct12 pts/000:00:00 postgres: autovacuum process int_test_new postgres 8138 30463 2 11:05 pts/000:01:39 postgres: integration_test int_test_new 129.80.17.25(54366) idle in transaction postgres 8139 30463 4 11:05 pts/000:03:16 postgres: integration_test int_test_new 129.80.17.25(54367) idle in transaction postgres 8140 30463 4 11:05 pts/000:03:18 postgres: integration_test int_test_new 129.80.17.25(54368) idle in transaction postgres 8141 30463 4 11:05 pts/000:03:55 postgres: integration_test int_test_new 129.80.17.25(54369) idle in transaction postgres 8142 30463 4 11:05 pts/000:03:28 postgres: integration_test int_test_new 129.80.17.25(54370) idle in transaction postgres 8143 30463 4 11:05 pts/000:03:24 postgres: integration_test int_test_new 129.80.17.25(54371) idle in transaction postgres 8144 30463 4 11:05 pts/000:03:56 postgres: integration_test int_test_new 129.80.17.25(54372) idle in transaction postgres 8145 30463 2 11:05 pts/000:01:48 postgres: integration_test int_test_new 129.80.17.25(54373) idle in transaction postgres 8146 30463 4 11:05 pts/000:03:13 postgres: integration_test int_test_new 129.80.17.25(54374) idle in transaction postgres 8147 30463 4 11:05 pts/000:03:21 postgres: integration_test int_test_new 129.80.17.25(54375) idle in transaction postgres 8148 30463 4 11:05 pts/000:03:29 postgres: integration_test int_test_new 129.80.17.25(54376) idle in transaction postgres 8149 30463 2 11:05 pts/000:01:37 postgres: integration_test int_test_new 129.80.17.25(54377) idle in transaction postgres 8150 30463 4 11:05 pts/000:03:30 postgres: integration_test int_test_new 129.80.17.25(54378) idle in transaction postgres 8151 30463 5 11:05 pts/000:04:16 postgres: integration_test int_test_new 129.80.17.25(54379) idle in transaction postgres 8152 30463 1 11:05 pts/000:01:35 postgres: integration_test int_test_new 129.80.17.25(54380) idle in transaction postgres 8153 30463 4 11:05 pts/000:03:26 postgres: integration_test int_test_new 129.80.17.25(54381) idle in transaction postgres 9114 30463 0 12:24 pts/000:00:00 postgres: creagrs creagrs 129.80.80.164(50863) idle Process 21064 is not moving, and other manual vacuum attempt block at some point. (gdb) bt #0 0x401f1e78 in semop () from /lib/i686/libc.so.6 #1 0x0834ba38 in ?? () #2 0x0816e7d4 in PGSemaphoreLock () #3 0x08192936 in ProcWaitForSignal () #4 0x08187bce in LockBufferForCleanup () #5 0x0811c37c in lazy_vacuum_heap () #6 0x0811bcb0 in lazy_scan_heap () #7 0x0811baad in lazy_vacuum_rel () #8 0x081180b9 in vacuum_rel () #9 0x0811763b in vacuum () #10 0x081703fd in autovacuum_do_vac_analyze () #11 0x08170017 in do_autovacuum () #12 0x0816fb4a in AutoVacMain () #13 0x0816f831 in autovac_start () #14 0x08175202 in ServerLoop () #15 0x0817441a in PostmasterMain () #16 0x0813be94 in main () #17 0x4012bc57 in __libc_start_main () from /lib/i686/libc.so.6 The last log entries (for autovacuum) are (now is Thu Oct 13 12:32:18 MDT
Re: [HACKERS] pg_config --pgxs on Win32
Dave Page wrote: We should probably document that pg_config may not work reliably with non-mingw tools in that case. Microsoft code may or may not do what is expected with front slashes. BTW Thomas - I thought you said \\ did work when you were testing options for me, or was that just msys rather than make? That was when testing different paths in the Makefile.global. Apparenlty a make never does a $(dir xxx) on those paths. I do have a workaround in place that makes it work for me now. I do $(dir $(subst \\,/,xxx)) and that works fine but given that the targeted platform for pgxs on Win32 is MinGW, perhaps it should output forward slashes anyway. Hmm, then again, perhaps only PL/Java does a $(dir xxx) on the output. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pg_config --pgxs on Win32
Thomas Hallgren [EMAIL PROTECTED] writes: I do have a workaround in place that makes it work for me now. I do $(dir $(subst \\,/,xxx)) and that works fine but given that the targeted platform for pgxs on Win32 is MinGW, perhaps it should output forward slashes anyway. I've already applied that change. Your gripe was pretty much exactly what I was afraid we'd hear, namely that the double-backslash output would break cases that used to work. I think we should stick with the forward slash output, since that's what we know has been working in the 8.0 branch. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] A costing analysis tool
Thanks, Josh, for the feedback. It sounds as though you are more focused on picking up costing problems which happen during production -- which is clearly valuable, but addresses a somewhat different set of needs than I was looking at. That said, it seems like there is potential to share signifcant code between the two techniques. We'll have to see if we can work that out. I didn't want to broach the subject of the programming language for this at the early conceptual stages, but if we're talking about code sharing, it can't wait too long, so I'll jump in with it now. I was considering using python to program the tool I was discussing. If python is used, I don't care whether there is any change to EXPLAIN ANALYZE -- it only takes a few lines of code to pull out what I need in the current form. My concern is whether python is supported on all of the target platforms. Python does well running queries directly against PostgreSQL, and is fine for shelling out to run commands (such as those needed to stop the back end, flush cache, and start the back end again). I think I will be significantly more productive at this in python than if I used C or perl, but if it's not accessible to the PostgreSQL community as a whole, I'll cope. Comments, anyone? Perhaps the place we can share code is starting at the point where EXPLAIN ANALYZE results have been inserted into a database. The analysis and reporting from that point might be something which could be common code. I'm not yet familiar with DBT-OSDL, Jan-TPCW, OSDBB and eDB, but I'll look them up -- that exactly the sort of suggestion I was hoping to get, so that I don't need to start from scratch in generating the test data. Anyone want to point out something else I should consider? I need to have somewhere for the work to live, and I quite frankly would just as soon dodge the overhead of setting up and maintaining something, so if noone has objections or other suggestions, I'm inclined to take you up on your offer to use your testperf project. Does anyone think some other location would be more appropriate? How much time is a question I'll have to discuss with my client after the concept has been firmed up and I work out a design from which I can estimate. My off-the-cuff guess is that it will require, and I can get approval for, about three FTE weeks. Mixed in with other things which require my attention, that's probably spread over two to three calendar months. If we run into critical optimization problems, this could get a boost in priority, which would shorten the timeline. It's also possible I might have to set it aside to work on some issue which comes out of the blue -- I never know for sure, so I don't want anyone to count on this for anything with a hard deliverable date until we actually have the working tool. If we get into much more detail, I assume we should take this off-list. -Kevin Josh Berkus josh@agliodbs.com 10/13/05 12:25 PM Kevin, I'm looking at trying to fix some clear flaws in costing which cause of our real-world queries to choose sub-optimal plans under PostgreSQL. It's clear that there needs to be a tool to analyze the accuracy of costing for a variety of queries, both to direct any efforts to fix problems and to test for possible costing regressions. As far as I can tell, no such tool currently exists. If I've missed something, please let me know, even if it's ad hoc or incomplete. Actually, this is pretty completely what I've been thinking about for the last year. I'm very happy that someone else is interested in working on it. (2) A large database must be created for these tests, since many issues don't show up in small tables. The same data must be generated in every database, so results are comparable and reproducable. (3) Developers should be able to easily add test cases, either for their own use or contributed to the community. Sure. However, I think it's important to seperate the test cases from the cost collection tool. Our *best* test cases will be real production applications. For synthetic test cases, we can look to improving DBT-OSDL, Jan-TPCW, OSDBB and eDB's test (if they ever publish it). The only thing that mess of tests is lacking is easy setup and portability. (7) I envision a process to create a test database, populate it, run a series of test cases with EXPLAIN ANALYZE, capture the results, parse the results and store them in a database, analyze the results to find means and standard deviations both overall and for each type of plan, and report summaries and outliers -- with references to the test cases. The primary statistic of interest is actual time divided by cost. This seems like it would be of interest overall, and within the permutations mentioned above for a single query. I would actually like to do this differently. I think an asynchronous logging mechanism is more useful, because there are cost estimation problems which don't show
Re: [HACKERS] pg_config --pgxs on Win32
On Thu, Oct 13, 2005 at 02:53:09PM -0400, Tom Lane wrote: Thomas Hallgren [EMAIL PROTECTED] writes: I do have a workaround in place that makes it work for me now. I do $(dir $(subst \\,/,xxx)) and that works fine but given that the targeted platform for pgxs on Win32 is MinGW, perhaps it should output forward slashes anyway. I've already applied that change. Your gripe was pretty much exactly what I was afraid we'd hear, namely that the double-backslash output would break cases that used to work. I think we should stick with the forward slash output, since that's what we know has been working in the 8.0 branch. Besides, Windows has accepted the forward slash as directory seperator all the way since DOS 2.0 when directories were introduced. The only reason they sometimes don't work as expected is because it's also the option indicator :(. Presumably things break when commands read /path as an option rather than a path. Hopefully they're smart enough to realize c:/path does not actually contain an option but is just a path. Presumably that's fixable some other way? -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpzU6MAc3xKm.pgp Description: PGP signature
Re: [HACKERS] auto vacuum lock on 8.1beta1
Robert Creager [EMAIL PROTECTED] writes: Might this be the same problem as the recent thread database vacuum from cron hanging where Tom is: I'm busy volatile-izing all the code in bufmgr.c ... should be able to commit a fix soon.? Seems reasonably likely, seeing that the original report involved gcc 3.3.something IIRC, and you're using 3.3.1. Is this an SMP box? The bug could theoretically manifest on a uniprocessor but it seems more likely to happen on a multiprocessor. Too bad you didn't have it built with --enable-debug; I can't think of any very easy way to verify a negative refcount for that buffer without gdb support. You could try inspecting the assembly code generated for PinBuffer, as we did with Kevin's compiler. If it's generating the same code sequence then that would make it pretty likely that you're seeing the same thing. The volatile patch should be available in last night's nightly snapshot, if you just want to update. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pg_config --pgxs on Win32
Dave Page wrote: We should probably document that pg_config may not work reliably with non-mingw tools in that case. Microsoft code may or may not do what is expected with front slashes. BTW Thomas - I thought you said \\ did work when you were testing options for me, or was that just msys rather than make? My recollection from building libpq and pg_config using MSVC command line tools for DBD::Pg is that forward slashes worked just fine. If it doesn't then we should document fairly specifically where. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] enhancement to pg_dump: supress columns
I have a situation where I need to hack pg_dump not to dump columns with a particular name. If this is of interest to the community I can spend a little extra effort and work up a patch. I'd be curious to see if anyone else thinks this is worthwhile. Why would I want to do this? I use a global sequence for a database wide unique identifier for purposes of locking (to hook into the user lock module). This works great but our clients frequently like to make copies of data for testing purposes and a dump/reload into a separate schema makes a copy of the generated identifier in the database. Basically, I need a field to revert to default in a dump/reload cycle. A command line switch to pg_dump seems the easiest way to handle this. A specialized domain qualifier which prevents the column from being dumped is perhaps more elegant but more work. Merlin ---(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] [DOCS] Added documentation about caching, reliability
Applied. Thanks. --- Simon Riggs wrote: On Wed, 2005-09-28 at 14:26 -0400, Bruce Momjian wrote: I have added a section to the top of the WAL docs explaining caching and reliability issues: http://candle.pha.pa.us/main/writings/pgsql/sgml/reliability.html I also renamed the chapter Reilability rather than WAL. I submitted a patch last week that builds further on Bruce's changes. [Docs for PITR and full_page_writes interaction, 2 Oct] The patch includes: 1. Minor rewording of the case that Bruce refers to recovery and I have differentiated the cases of crash recovery and PITR. The corresponding section of the backup chapter has also been changed to include a short discussion on full_page_writes and WAL compression, with a link back to the WAL section. 2. Merged the 1st and 2nd sects of Reliability into a single section. The 1st section was only a single paragraph and so that improves readability. I've freshened that section, since it has not been updated since 7.1 and currently still reads as if WAL has just been released. 3. I've updated the section on WAL configuration with further WAL related comments and clarifications. These relate to full_page_writes and appropriate settings of wal_buffers to match. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] auto vacuum lock on 8.1beta1
I can confirm that the patch was in the snapshot I picked up this morning at about 10:30 CDT. We've been using it since then and have not seen the problem in spite of attempting to provoke it with database vacuums. -Kevin Tom Lane [EMAIL PROTECTED] 10/13/05 2:09 PM Robert Creager [EMAIL PROTECTED] writes: Might this be the same problem as the recent thread database vacuum from cron hanging where Tom is: I'm busy volatile-izing all the code in bufmgr.c ... should be able to commit a fix soon.? Seems reasonably likely, seeing that the original report involved gcc 3.3.something IIRC, and you're using 3.3.1. Is this an SMP box? The bug could theoretically manifest on a uniprocessor but it seems more likely to happen on a multiprocessor. Too bad you didn't have it built with --enable-debug; I can't think of any very easy way to verify a negative refcount for that buffer without gdb support. You could try inspecting the assembly code generated for PinBuffer, as we did with Kevin's compiler. If it's generating the same code sequence then that would make it pretty likely that you're seeing the same thing. The volatile patch should be available in last night's nightly snapshot, if you just want to update. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_config --pgxs on Win32
-Original Message- From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED] Sent: Thu 10/13/2005 8:08 PM To: Tom Lane Cc: Thomas Hallgren; Dave Page; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] pg_config --pgxs on Win32 Besides, Windows has accepted the forward slash as directory seperator all the way since DOS 2.0 when directories were introduced. The only reason they sometimes don't work as expected is because it's also the option indicator :(. Presumably things break when commands read /path as an option rather than a path. Hopefully they're smart enough to realize c:/path does not actually contain an option but is just a path. Presumably that's fixable some other way? When we first discussed this I posted a very simple example 'cd /' which does absolutely nothing unlike 'cd \' or 'cd \\' which work as expected, quite possibly for the reason you suggest. Although the / is accepted, I don't believe it can be called reliable as it obviously doesn't work in all situations. Regardless, the msys/gmake case is definately the most important to support, but I do think we should note in the docs that pg_config may not work well in non-msys/cygwin environments. Regards, dave. ---(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] auto vacuum lock on 8.1beta1
On Thu, 13 Oct 2005 15:09:58 -0400 Tom Lane [EMAIL PROTECTED] wrote: Robert Creager [EMAIL PROTECTED] writes: Might this be the same problem as the recent thread database vacuum from cron hanging where Tom is: I'm busy volatile-izing all the code in bufmgr.c ... should be able to commit a fix soon.? Seems reasonably likely, seeing that the original report involved gcc 3.3.something IIRC, and you're using 3.3.1. Is this an SMP box? The bug could theoretically manifest on a uniprocessor but it seems more likely to happen on a multiprocessor. Yes, it's a dual Xenon box. Too bad you didn't have it built with --enable-debug; I can't think of any very easy way to verify a negative refcount for that buffer without gdb support. I just downloaded last nights snapshot. I'll build with debug and try it out. Thanks, Rob ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_config --pgxs on Win32
Dave Page wrote: -Original Message- From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED] Sent: Thu 10/13/2005 8:08 PM To: Tom Lane Cc: Thomas Hallgren; Dave Page; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] pg_config --pgxs on Win32 Besides, Windows has accepted the forward slash as directory seperator all the way since DOS 2.0 when directories were introduced. The only reason they sometimes don't work as expected is because it's also the option indicator :(. Presumably things break when commands read /path as an option rather than a path. Hopefully they're smart enough to realize c:/path does not actually contain an option but is just a path. Presumably that's fixable some other way? When we first discussed this I posted a very simple example 'cd /' which does absolutely nothing unlike 'cd \' or 'cd \\' which work as expected, quite possibly for the reason you suggest. Although the / is accepted, I don't believe it can be called reliable as it obviously doesn't work in all situations. Regardless, the msys/gmake case is definately the most important to support, but I do think we should note in the docs that pg_config may not work well in non-msys/cygwin environments. Regards, dave. Perhaps pg_config should have a --pgxscanonical option? If someone then really wants the backslashes, he could use that. - thomas ---(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] A costing analysis tool
On Thu, Oct 13, 2005 at 01:52:10PM -0500, Kevin Grittner wrote: Thanks, Josh, for the feedback. It sounds as though you are more focused on picking up costing problems which happen during production -- which is clearly valuable, but addresses a somewhat different set of needs than I was looking at. That said, it seems like there is potential to share signifcant code between the two techniques. We'll have to see if we can work that out. Firstly, I really hope you get further with this than I did a while ago when I attempted. It's certainly a worthly goal. Secondly, while checking for problems in productions systems is good, it's not going to help with fixing the cost model. For that you need raw data. My basic plan was to setup tables of different sizes and attempt to run queries such as: - Index Scan on each table with different types of keys and coverage. - Seq Scan - Nested loop, etc... I did reach the point where I was wishing I could just give PostgreSQL the plan and tell it to execute it. :) The point of the exercise is to be able to derive correlations so you could from the plan calcuate the actual costs. For example, run a nested loop with an inner index scan once, twice, three times etc so we can actually *see* what the cache effects are. I got stuck on working out how to force the optimiser to produce the plan I want. I didn't try too hard though. The enable_xxx options should be enough, hopefully. Ofcourse you want to run it with different numbers of shared buffers to see how they affect the results. And then you ideally want the results for several different machines, different disk subsystems, memory types, etc and placed on a nice web page so other people can run correlations on the data themselves. This is essentially what you already came up with. Note that for these purposes the actual estimates by PostgreSQL are irrelevent. However, I strongly suggest finding a way of collating the results publically from lots of people because digging for correlations is something lots of people can hammer on and is really hard to program. Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgp8UuwsT42Lr.pgp Description: PGP signature
Re: [HACKERS] pg_config --pgxs on Win32
Dave Page dpage@vale-housing.co.uk writes: When we first discussed this I posted a very simple example 'cd /' which does absolutely nothing unlike 'cd \' or 'cd \\' which work as expected, quite possibly for the reason you suggest. Although the / is accepted, I don't believe it can be called reliable as it obviously doesn't work in all situations. However, that's not the issue here. AFAIK, pg_config will only put out fully qualified paths, that is C:/foo, so the risk that something starting with slash will be misinterpreted seems irrelevant to me. If it doesn't always prepend a drive letter, let's see about fixing that instead. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] auto vacuum lock on 8.1beta1
On Thu, 13 Oct 2005 14:20:46 -0500 Kevin Grittner [EMAIL PROTECTED] wrote: I can confirm that the patch was in the snapshot I picked up this morning at about 10:30 CDT. We've been using it since then and have not seen the problem in spite of attempting to provoke it with database vacuums. Thanks Kevin. I'm giving it a try now. ---(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] pg_config --pgxs on Win32
On Thu, Oct 13, 2005 at 08:36:39PM +0100, Dave Page wrote: When we first discussed this I posted a very simple example 'cd /' which does absolutely nothing unlike 'cd \' or 'cd \\' which work as expected, quite possibly for the reason you suggest. Although the / is accepted, I don't believe it can be called reliable as it obviously doesn't work in all situations. Indeed, what you are seeing there is cmd.exe (or whatever it's called) command-line parsing. The same issue applies to any builtin commands such as echo. Once you start executing external commands, they are responsible for their own command-line parsing. So, we need examples of other programs that behave differently when given complete paths with forward slashes (such as c:/windows/system). For example, the Visual C++ compiler explicitly states it takes filenames with either forward or backslashes. Commands built into cmd.exe tend to be strange in this way, although programs ported from UNIX will probably work fine. I don't see an easy way out. Half the system thinks backslashes are special and need expansion, the other half thinks forward slashes are option markers. This is really just a variation on the space in filenames issue in UNIX. Unfortunatly, Windows never had a getopt and so there is no standard way of dealing with options. Every program does it differently. For example, there is no end of options marker, which is how you would deal with this issue in UNIX. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpOf540wnpEY.pgp Description: PGP signature
Re: [HACKERS] Request for a force interactive mode flag (-I) for psql
I haven't seen any replies to this, so I guess you are left with either hacking psql yourself or getting Cygwin folks to fix it. Sorry. --- Bill Bartlett wrote: Back in 2003 Bruce Momjian proposed adding a flag (-I) to psql to force it into interactive mode. (See http://archives.postgresql.org/pgsql-hackers/2003-11/msg00013.php for the thread.) The proposal was rejected because there was no proven need for it at that time. I'd like to raise this proposal again, since I think in our situation, this is the only fix for our problem. Our environment is as follows: * A large number of PostgreSQL 7.4 servers running on a variety of SuSE Linux (9.0 - 9.3) servers * A growing number of PostgreSQL 8.0.3 servers running on Windows Server 2003 servers. (We are running the native Win32 version of PostgreSQL, not the Cygwin version.) The servers are all located in remote offices. Maintenance is done remotely via SSH to a local bash command prompt where we use the command line tools: psql, pg_dump, etc. On Linux we use the native sshd; on Windows we use Cygwin to get bash, sshd, cron, etc. On Linux this works fine; on Windows, however, psql thinks it is not in an interactive console so we get no prompts, no line editing, no history, very little cursor control, etc. (I see that the missing autocomplete feature is a readline issue, but that's a topic for another posting.) This makes remote maintenance on the Windows servers much more difficult than it otherwise could be. The issue appears to be due to isatty() returning false in the Windows Cygwin environment. From other research around the web, it appears that if the app is Cygwin-aware is knows to override this check (or allow manual override) or try to do further testing, but in this case, since we are using the native Win32 version of PostgreSQL, psql doesn't do any additional testing. Also, even in the local console on Windows, running rxvt -- our preferred terminal in Windows (since it lets us make our Windows command line act just like our Linux command line grin) -- causes psql to think that there is no terminal. (Our first encounter of this no terminal problem was in trying to run psql via a local bash shell via rxvt, and originally we thought that psql was hanging. Given our reliance on psql for remote maintenance, this would have prevented our rolling out a Windows version of PostgreSQL. It was only after too much time looking at it with some low-level tools that we stumbled across the fact that psql was simply silently waiting at a command prompt rather than being hung. However, from other posts in these lists [e.g.: http://archives.postgresql.org/pgsql-patches/2004-07/msg00369.php ] it appears that other people also thought psql was hanging when it was run from a terminal program, so I suppose I shouldn't feel too bad...) Bruce's proposal and suggested code simply added a new -I flag to force psql into interactive mode (by simply setting pset.notty = 0). From everything I can find (including reading through the Cygwin code, ssh and sshd man pages and code, psql code, testing various modes of running the sshd service in Windows, changing Windows profile account permissions, etc.), adding this flag seems to be the only viable option (other than writing a replacement for psql for our Windows servers, something I'd prefer to not do). While I can obviously add this patch myself and build and maintain a custom version of psql for our own use, since there now is a true native version of PostgreSQL for Windows, I think we will see a growing list of people supporting mixed environments just like ours, and bumping into this exact same problem. Thoughts? - Bill Bill Bartlett meridianEMR, Inc. http://www.meridianemr.com ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Bug 1473, pthread python on FreeBSD
Jim C. Nasby wrote: http://archives.postgresql.org/pgsql-bugs/2005-02/msg00135.php I think it may have been a bit early to disable pthread python support (http://archives.postgresql.org/pgsql-hackers/2005-09/msg01136.php), as Python was working fine on buildfarm member platypus. Maybe it's only an issue with 4.x machines? I have reenabled threaded python builds on FreeBSD5 (now disabled only for FreeBSD4 and OpenBSD). -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] roundoff problem in time datatype
Where are we on this? I see current CVS behaving the same as below, except the last query now returns 24:00:00. --- Tom Lane wrote: Inserting into a time field with limited precision rounds off, which is good except for this case: regression=# select '23:59:59.9'::time(0); time -- 24:00:00 (1 row) This is bad because: regression=# select '24:00:00'::time(0); ERROR: date/time field value out of range: 24:00:00 which means that data originally accepted will fail to dump and reload. I see this behavior in all versions back to 7.3. 7.2 was even more broken: regression=# select '23:59:59.9'::time(0); time -- 00:00:00 (1 row) I think the correct behavior has to be to check for overflow again after rounding off. Alternatively: why are we forbidding the value 24:00:00 anyway? Is there a reason not to allow the hours field to exceed 23? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] roundoff problem in time datatype
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Where are we on this? We haven't decided what to do. I think my preference is to allow '24:00:00' (but not anything larger) as a valid input value of the time datatypes. This for two reasons: * existing dump files may contain such values * it's consistent with allowing, eg, '12:13:60', which we allow even though it's certainly not a valid leap second. The alternative is to try to catch all places where 23:59:59.something could get rounded up to 24:00:00, but that looks messy, and it would introduce a gotcha into calculations on time values. Is this a must-fix for 8.1? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] roundoff problem in time datatype
Bruce Momjian pgman@candle.pha.pa.us writes: Where are we on this? We haven't decided what to do. I think my preference is to allow '24:00:00' (but not anything larger) as a valid input value of the time datatypes. This for two reasons: * existing dump files may contain such values * it's consistent with allowing, eg, '12:13:60', which we allow even though it's certainly not a valid leap second. The alternative is to try to catch all places where 23:59:59.something could get rounded up to 24:00:00, but that looks messy, and it would introduce a gotcha into calculations on time values. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Questions about proper newline handling in psql output
This has been saved for the 8.2 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Martijn van Oosterhout wrote: -- Start of PGP signed section. Hi, I basically have a functional version for aligned output, examples at the bottom of this email. It handles multiline data values and multiline headers. However, there are some areas where I could use some input. 1. To be able to control the spacing, psql now has to be very careful about its output. eg \r is printed as \r, ascii control characters are output as \x00 style and other control chars as \u. This is a change from previous behaviour, yet you're pretty much forced to if you want to control the output. Is this change acceptable? 2. Currently I've changed the aligned outputs but not the unaligned ones. Given you're not worrying about alignment there anyway, why do the work? Also, we recommend unaligned output for script users so I don't feel right changing it. Is this distinction acceptable? 3. How to show that a value is continued? As you can see below I use ':' before columns that have data. This obviously doesn't work for first column if there's no outer border. If your border style is 0 you're totally out of luck. I remember a discussion on this before but couldn't find it in the archives. Either a reference or some other hints would be appreciated. 4. Some system output like pg_views has really really long strings, would it be acceptable to change the output there to add newlines at various places to make it output nicer with this change? 5. Auto string folding. If a string is really long, fold it so it fits in a screen width, perhaps with '\' continuation. I havn't done this but I can imagine some people (including me) would love it. 6. Currently I've implemented support for UTF-8 and all ASCII compatable single-byte encodings. Given that psql didn't support the others anyway maybe no-one cares, but I have to ask: does anyone care? If so, I need info on *how* to support an encoding. Thanks for your attention. See you tomorrow. Query is: select oid, prosrc as HdrLine1 HdrLine2, proacl from pg_proc limit 1; Border style is 1. oid | HdrLine1 | proacl | HdrLine2 | ---+-+ 17009 | select 1 union all select 2 union all select 3 union all | : select 4 union all select 5 union all select 6 union all : select 7 union all select 8 union all select 9 union all : select 10 union all select 11 union all select 12 union all : select 13 union all select 14 union all select 15 union all : select 16 union all select 17 union all select 18 union all : select 19 union all select 20 union all select 21 union all : select 22 union all select 23 union all select 24 union all : select 25 union all select 26 union all select 27 union all : select 28 union all select 29 union all select 30 union all : select 31 union all select 32 (1 row) Expanded display is on. -[ RECORD 1 ]- oid | 17009 HdrLine1 | select 1 union all select 2 union all select 3 union all HdrLine2 : select 4 union all select 5 union all select 6 union all : select 7 union all select 8 union all select 9 union all : select 10 union all select 11 union all select 12 union all : select 13 union all select 14 union all select 15 union all : select 16 union all select 17 union all select 18 union all : select 19 union all select 20 union all select 21 union all : select 22 union all select 23 union all select 24 union all : select 25 union all select 26 union all select 27 union all : select 28 union all select 29 union all select 30 union all : select 31 union all select 32 proacl | # select chr(8); chr -- \x08 (1 row) -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. -- End of PGP section, PGP failed! -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
Re: [HACKERS] 64-bit API for large objects
This has been saved for the 8.2 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Tom Lane wrote: Jeremy Drake [EMAIL PROTECTED] writes: On Fri, 23 Sep 2005, Tom Lane wrote: postgresql-fe.h defines a ton of stuff that has no business being visible to libpq's client applications. It's designed to be used by our *own* client-side code (psql and the like), but we have not made any attempt to keep it from defining stuff that would likely break other peoples' code. So does this mean that there is a different, more advanced and more likely to break random other code, client library where this call would fit better? I've been thinking more about this and come to these conclusions: 1. libpq_fe.h definitely cannot include postgres_fe.h; in fact, it has no business even defining a type named int64. That is way too likely to collide with symbols coming from elsewhere in a client compilation. I think what we need is to declare a type named pg_int64 and use that in the externally visible declarations. The most reasonable place to put the typedef is postgres_ext.h. This will mean making configure generate postgres_ext.h from a template postgres_ext.h.in, but that's no big deal. 2. We need a strategy for what to do when configure doesn't find a working int64 type. My inclination is to just not export the functions in that case. So normally, postgres_ext.h would contain something like #define HAVE_PG_INT64 1 typedef long long int pg_int64; but neither of these would appear if configure couldn't find a working type. In libpq-fe.h, we'd have #ifdef HAVE_PG_INT64 extern pg_int64 lo_lseek64(PGconn *conn, int fd, pg_int64 offset, int whence); extern pg_int64 lo_tell64(PGconn *conn, int fd); #endif and similarly for all the code inside libpq. The reason this seems like a good idea is that client code could key off #ifdef HAVE_PG_INT64 to detect whether the lo64 functions are available; which is useful even if you don't care about machines without int64, because you still need to think about machines with pre-8.2 PG installations. 3. This is still not 100% bulletproof, as it doesn't address situations like building PG with gcc and then trying to compile client apps with a vendor cc that doesn't understand long long int. The compile would choke on the typedef even if you weren't trying to use large objects at all. I don't see any very nice way around that. It might be worth doing this in postgres_ext.h: #ifndef NO_PG_INT64 #define HAVE_PG_INT64 1 typedef long long int pg_int64; #endif which would at least provide an escape hatch for such situations: define NO_PG_INT64 before trying to build. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] roundoff problem in time datatype
Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: I think my preference is to allow '24:00:00' (but not anything larger) as a valid input value of the time datatypes. Is this a must-fix for 8.1? No, since it's a pre-existing issue, but it's the kind of thing that should be changed during a major release not a point-release. If we don't change it then I think we'd have to wait till 8.2 before doing anything about it. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] roundoff problem in time datatype
Tom, I think my preference is to allow '24:00:00' (but not anything larger) as a valid input value of the time datatypes. This for two reasons: * existing dump files may contain such values * it's consistent with allowing, eg, '12:13:60', which we allow even though it's certainly not a valid leap second. It's also consistent with how several other RDBMSes do things (SQL Server, MySQL), and several programming languages. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] A costing analysis tool
Thanks for the well wishes. It sounds like you were addressing a slightly different problem -- more ambitious than what I propose tackle do as a first step. If I understand you, you were trying to develop your own predictive costing formulas based on plans. I'm merely talking about a tool to evaluate the relative accuracy of the predictions generated by PostgreSQL. So for the purposes of the proposed tool, the PostgreSQL estimates are central. The ultimate goal is to be able to spot where the current calculations are least accurate, so that any adjustments can be made where they are most needed. You will notice that my working assumptions start with the observation that most of the time, PostgreSQL does very well. I have certainly found that to be the case, compared to a commercial product running the same queries against the same data. I'm operating on the assumption that relatively minor adjustments to current techniques can take off what rough edges there are. That said, there's certainly overlap between your effort and what I'm going to be developing. Do you have anything from your work which might save me some time? You point regarding a convenient way for people to submit results from diverse environments, with a nice web presentation of collated results is well taken. I'm not sure my off-the-cuff estimate from an earlier post allows enough time for that -- I'll have to be sure to include something at least adequate and expandable in the design. I probably won't try for anything too fancy. I'm at my best on frameworky internal sorts of things. There's a chance that I may be able to talk my client into putting a web app guy on this for a few days to make it pretty. You never know. Would it make more sense for my client to host something like that on their servers, or would it be more appropriate to have something which would install on a postgresql.org server? If the latter, what sorts of technologies are supported? (My client is partial to cocoon.) -Kevin Martijn van Oosterhout kleptog@svana.org 10/13/05 2:41 PM On Thu, Oct 13, 2005 at 01:52:10PM -0500, Kevin Grittner wrote: Thanks, Josh, for the feedback. It sounds as though you are more focused on picking up costing problems which happen during production -- which is clearly valuable, but addresses a somewhat different set of needs than I was looking at. That said, it seems like there is potential to share signifcant code between the two techniques. We'll have to see if we can work that out. Firstly, I really hope you get further with this than I did a while ago when I attempted. It's certainly a worthly goal. Secondly, while checking for problems in productions systems is good, it's not going to help with fixing the cost model. For that you need raw data. My basic plan was to setup tables of different sizes and attempt to run queries such as: - Index Scan on each table with different types of keys and coverage. - Seq Scan - Nested loop, etc... I did reach the point where I was wishing I could just give PostgreSQL the plan and tell it to execute it. :) The point of the exercise is to be able to derive correlations so you could from the plan calcuate the actual costs. For example, run a nested loop with an inner index scan once, twice, three times etc so we can actually *see* what the cache effects are. I got stuck on working out how to force the optimiser to produce the plan I want. I didn't try too hard though. The enable_xxx options should be enough, hopefully. Ofcourse you want to run it with different numbers of shared buffers to see how they affect the results. And then you ideally want the results for several different machines, different disk subsystems, memory types, etc and placed on a nice web page so other people can run correlations on the data themselves. This is essentially what you already came up with. Note that for these purposes the actual estimates by PostgreSQL are irrelevent. However, I strongly suggest finding a way of collating the results publically from lots of people because digging for correlations is something lots of people can hammer on and is really hard to program. Hope this helps, ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [COMMITTERS] pgsql: Back out this because of fear of changing
Alvaro Herrera wrote: Bruce Momjian wrote: Back out this because of fear of changing error strings: This makes the error messages for PREPARE TRANSACTION, COMMIT PREPARED etc. match the docs, which talk about transaction identifier not gid or global transaction identifier. I say make the change anyway. No language except Turkish is up to 100% yet, and there's plenty of time to fix the translations. It's only a couple of strings, right? Thanks, applied. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] beta2 no longer builds with MSVC?
Magnus Hagander wrote: Has anybody tried building beta2 or later with MSVC? It doesn'?t work for me - it builds fine, but whenever I run with it I get a coredump from it whenever I try to connect. If I revert it to the 8.0 version of port/getaddrinfo.c, things work again. The problem shows itself in that conn-addr_cur-ai_addr is NULL when it reaches the CONNECTION_NEEDED branch in PQconnectPoll(). Compiling with MingW appears to work fine. Strange. Would you try this patch and see if it helps? It disables the lookups to call the Win32-native getaddrinfo routines. (I know you are busy so maybe someone else can test MSVC.) Another posssible fix would be to do a 'continue' in the loop if conn-addr_cur-ai_addr is NULL, and see if that works. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 Index: src/port/getaddrinfo.c === RCS file: /cvsroot/pgsql/src/port/getaddrinfo.c,v retrieving revision 1.20 diff -c -c -r1.20 getaddrinfo.c *** src/port/getaddrinfo.c 13 Oct 2005 23:22:11 - 1.20 --- src/port/getaddrinfo.c 13 Oct 2005 23:24:56 - *** *** 70,75 --- 70,77 void *hLibrary = NULL; static bool alreadyLookedForIpv6routines = false; + return false; + if (alreadyLookedForIpv6routines) return (getaddrinfo_ptr != NULL); ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Seeing context switch storm with 10/13 snapshot of 8.1beta3
I've been having this problem since trying to upgrade from 7.4.1 to 8.03, and now 8.1. It's a dual Xenon machine: Linux annette.stortek.com 2.4.22-26mdkenterprise #1 SMP Wed Jan 7 07:10:39 MST 2004 i686 unknown unknown GNU/Linux PostgreSQL version is: --- PostgreSQL 8.1beta3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.1 (Mandrake Linux 9.2 3.3.1-2mdk) [EMAIL PROTECTED] creagrs]$ /usr/local/pgsql810/bin/pg_config BINDIR = /usr/local/pgsql810/bin DOCDIR = /usr/local/pgsql810/doc INCLUDEDIR = /usr/local/pgsql810/include PKGINCLUDEDIR = /usr/local/pgsql810/include INCLUDEDIR-SERVER = /usr/local/pgsql810/include/server LIBDIR = /usr/local/pgsql810/lib PKGLIBDIR = /usr/local/pgsql810/lib LOCALEDIR = MANDIR = /usr/local/pgsql810/man SHAREDIR = /usr/local/pgsql810/share SYSCONFDIR = /usr/local/pgsql810/etc PGXS = /usr/local/pgsql810/lib/pgxs/src/makefiles/pgxs.mk CONFIGURE = '--enable-syslog' '--prefix=/usr/local/pgsql810' '--enable-debug' CC = gcc CPPFLAGS = -D_GNU_SOURCE CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wendif-labels -fno-strict-aliasing -g CFLAGS_SL = -fpic LDFLAGS = -Wl,-rpath,/usr/local/pgsql810/lib LDFLAGS_SL = LIBS = -lpgport -lz -lreadline -lncurses -lcrypt -lresolv -lnsl -ldl -lm -lbsd VERSION = PostgreSQL 8.1beta3 I can reproduce this problem quite quickly with 20 concurrent queries ( 20 minutes with 5 sets of my app at 4 queries a piece, probably quicker). The queries nominally take .1 second, but when all 5 sets run, the queries are up around 5-6 seconds. CS hit over 100k. Below is a vmstat over a few seconds which show the problem, then it subsides, then it hits again. Note that when my application gets up to 5 sets of 8, it never comes down again, and the queries are over 40 seconds to complete. When run against 7.4.1, the CS problem does not occur (stays 10k). [EMAIL PROTECTED] creagrs]$ vmstat 1 procs ---memory-- ---swap-- -io --system-- cpu r b swpd free buff cache si sobibo incs us sy id wa 10 0 1032 63120 48 160847200 5183 6 92 8 0 0 8 0 1032 62584 48 160848000 024 173 97793 39 61 0 0 9 0 1032 62332 48 160850000 032 197 83089 43 57 0 0 4 0 1032 61216 48 160850800 016 158 79967 43 57 0 0 6 0 1032 65208 48 160855600 088 262 91266 52 48 0 0 5 0 1032 67368 48 160855600 0 292 235 14234 87 13 0 0 4 0 1032 65780 48 160858000 040 173 325 95 5 0 0 4 0 1032 64764 48 160858000 0 0 14486 98 2 0 0 6 0 1032 65800 48 160858000 024 197 397 96 4 0 0 6 0 1032 66284 48 160860400 064 234 9284 83 17 0 0 6 0 1032 66104 48 160860400 0 116 253 87658 56 44 0 0 5 0 1032 66276 48 160861200 056 245 20648 81 19 0 0 6 0 1032 65736 48 160864400 088 250 19699 82 18 0 0 5 0 1032 65316 48 160865200 040 215 73709 70 30 0 0 5 0 1032 67664 48 160870000 096 272 2 81 19 0 0 4 0 1032 66700 48 160870000 084 188 351 93 7 0 0 4 0 1032 67696 48 160873200 064 210 505 91 9 0 0 4 0 1032 67696 48 160873200 0 0 13986 99 1 0 0 4 0 1032 64612 48 160873200 0 0 13474 99 1 0 0 5 0 1032 65344 48 160875600 048 206 421 94 6 0 0 4 0 1032 66636 48 160876400 016 171 232 96 4 0 0 4 0 1032 67404 48 160876400 0 0 13671 100 0 0 0 7 0 1032 63544 48 160878000 056 239 67250 65 35 0 0 6 0 1032 65168 48 160879600 056 230 87782 59 41 0 0 7 0 1032 66588 48 160882000 072 236 106591 42 58 0 0 8 0 1032 62544 48 160883600 0 184 244 129696 48 52 0 0 4 0 1032 63504 48 160886000 072 245 110887 48 52 0 0 8 0 1032 63400 48 160886800 016 156 101638 35 65 0 0 8 0 1032 62744 48 160886800 024 193 91159 40 60 0 0 8 0 1032 63700 48 160886800 016 172 85165 42 58 0 0 7 0 1032 62888 48 160887600 048 228 94902 42 58 0 0 4 0 1032 64184 48 160890800 072 234 117585 46 54 0 0 7 0 1032 61832 48 160894800 072 209 104287 46 54 0 0 8 0 1032 64468 48 160895600 056 237 112181 44 56 0 0 7 0 1032 63032 48 160897200 056 236 114977 47 53 0 0 8 0 1032 63204 48 1608980
Re: [HACKERS] Allowed timezone values
Also, JST doesn't work anymore, but JST9 does. JST9 is valid per the POSIX rules. JST isn't listed as a zone name in the zic database, so it's not valid. (Try Japan instead.) Shall I add JST to our zic database? It's quite confusing that 2005-10-14 12:00 JST is allowed while SET TIME ZONE 'JST' is not, and 2005-10-14 12:00 Japan is NOT allowed while SET TIME ZONE 'Japan' is OK. Note that there's no daylight-saving time in Japan (at this point). -- SRA OSS, Inc. Japan Tatsuo Ishii ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Allowed timezone values
Tatsuo Ishii wrote: Also, JST doesn't work anymore, but JST9 does. JST9 is valid per the POSIX rules. JST isn't listed as a zone name in the zic database, so it's not valid. (Try Japan instead.) Shall I add JST to our zic database? It's quite confusing that 2005-10-14 12:00 JST is allowed while SET TIME ZONE 'JST' is not, and 2005-10-14 12:00 Japan is NOT allowed while SET TIME ZONE 'Japan' is OK. Note that there's no daylight-saving time in Japan (at this point). I have to say, I am a little confused too. First, why does JST work but XST does not? Where does it get the JST from? The database? test= select '2005-10-14 12:00 JST'::timestamp with time zone; timestamptz 2005-10-13 23:00:00-04 (1 row) test= select '2005-10-14 12:00 XST'::timestamp with time zone; ERROR: invalid input syntax for type timestamp with time zone: 2005-10-14 12:00 XST And this is Tatsuo's complaint: test= select '2005-10-14 12:00 Asia/Tokyo'::timestamp with time zone; ERROR: invalid input syntax for type timestamp with time zone: 2005-10-14 12:00 Asia/Tokyo test= set timezone = 'Asia/Tokyo'; SET And this: test= set timezone = 'JST'; ERROR: unrecognized time zone name: JST test= set timezone = 'JST9'; SET test= select '2005-10-14 12:00 JST9'::timestamp with time zone; ERROR: invalid input syntax for type timestamp with time zone: 2005-10-14 12:00 JST9 I assume it is related to these two TODO entries: o Merge hardwired timezone names with the TZ database; allow either kind everywhere a TZ name is currently taken o Allow customization of the known set of TZ names (generalize the present australian_timezones hack) -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] enhancement to pg_dump: supress columns
A general ability to be able to dump views as if they were tables would be more broadly applicable methinks? Merlin Moncure wrote: I have a situation where I need to hack pg_dump not to dump columns with a particular name. If this is of interest to the community I can spend a little extra effort and work up a patch. I'd be curious to see if anyone else thinks this is worthwhile. Why would I want to do this? I use a global sequence for a database wide unique identifier for purposes of locking (to hook into the user lock module). This works great but our clients frequently like to make copies of data for testing purposes and a dump/reload into a separate schema makes a copy of the generated identifier in the database. Basically, I need a field to revert to default in a dump/reload cycle. A command line switch to pg_dump seems the easiest way to handle this. A specialized domain qualifier which prevents the column from being dumped is perhaps more elegant but more work. Merlin ---(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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Allowed timezone values
Tatsuo Ishii [EMAIL PROTECTED] writes: Shall I add JST to our zic database? No. We have to update that from the upstream database every release; maintaining our own private mods is not acceptable. If you want JST to be recognized as a zic timezone, go lobby the upstream maintainers for it. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Allowed timezone values
Bruce Momjian pgman@candle.pha.pa.us writes: I assume it is related to these two TODO entries: o Merge hardwired timezone names with the TZ database; allow either kind everywhere a TZ name is currently taken Yes, the point here is that the datetime token table has a bunch of hardwired zone names --- actually not so much timezone names as names for specific GMT offsets (eg, it has EST and EDT but not EST5EDT). We need to think about how to generalize that facility and merge it with the zic stuff. This is not something that's going to get fixed for 8.1, however. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Seeing context switch storm with 10/13 snapshot of 8.1beta3
Robert Creager [EMAIL PROTECTED] writes: I've been having this problem since trying to upgrade from 7.4.1 to 8.03, and now 8.1. Can you put together a test case that other people could use to reproduce it? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Seeing context switch storm with 10/13 snapshot of
When grilled further on (Thu, 13 Oct 2005 22:44:54 -0400), Tom Lane [EMAIL PROTECTED] confessed: Robert Creager [EMAIL PROTECTED] writes: I've been having this problem since trying to upgrade from 7.4.1 to 8.03, and now 8.1. Can you put together a test case that other people could use to reproduce it? I can try. The data size isn't big, but the scripts that run against it are, and are dependent on our development enviornment. What I'll have to do is pull out the db portion of the app and see if I can simplify it - might work. The app is a test system that runs against our big storage libraries. Is there anything I might be able to do (without the test case) that would help figure out what's happening? Cheers, Rob -- 21:09:29 up 11 days, 12:44, 7 users, load average: 3.87, 4.27, 5.04 Linux 2.6.5-02 #8 SMP Mon Jul 12 21:34:44 MDT 2004 pgpXsXH0CCrdV.pgp Description: PGP signature
Re: [HACKERS] Seeing context switch storm with 10/13 snapshot of 8.1beta3
Robert Creager [EMAIL PROTECTED] writes: Is there anything I might be able to do (without the test case) that would help figure out what's happening? oprofile stats would be enlightening, perhaps. I'm particularly interested in why 7.4 is behaving better than newer versions --- that does not compute ;-). Have you checked that the same query plan is being used by 7.4 and the newer versions? Could you get oprofile results for both versions? 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] [BUGS] BUG #1962: ECPG and VARCHAR
[moved to hackers] Is this a regression in the stable branches? If so, shouldn't we do a new release rather immediately? What do others think about this situation? Can you remember regressions in stable branches in the past? How were those it handled? I think waiting for months (i.e. for the next major bug fixes) is not the correct answer here. IMHO, the latest released version should be known best in all components. Best Regards, Michael Paesold Bruce Momjian wrote: Michael Fuhr wrote: On Thu, Oct 13, 2005 at 09:49:20AM -0600, Michael Fuhr wrote: ecpg in 8.0.4 seems not to like the macros. I get the same error, but not if I do this: VARCHAR t[256]; VARCHAR o[256]; ecpg in 8.1beta3 works either way. This appears to be the guilty commit, which was made to 7.4, 8.0, and HEAD (8.1): http://archives.postgresql.org/pgsql-committers/2005-08/msg00266.php It was recently fixed in HEAD only: http://archives.postgresql.org/pgsql-committers/2005-10/msg00043.php Good catch! I have backpatched these fixes to the 8.0 and 7.4 branches as you suggested, (identical) patches attached. The big problem is that we might not make releases on these branches for months, so anyone needing the fix should download CVS for those branches. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
