Re: [HACKERS] HOT is applied
On 9/21/07, Tom Lane <[EMAIL PROTECTED]> wrote: > > > > but control never gets that far because neither xmin nor xmax is > committed yet. We could fix that, probably, by considering the > xmin=xmax case in the xmin-in-progress case further up; but the > HEAP_UPDATED exclusion is still a problem. Still, it seems like this > is leaving some money on the table when you think about pruning a HOT > chain. Can we improve on it easily? > > May be we can, but it would get a bit tricky. There might be a transaction looking at the first tuple in the chain and waiting for this (inserting-deleting) transaction to finish. If the waiting transaction is running in READ COMMITTED mode, it would then follow the update chain. Removing any intermediate tuples without fixing the previous tuple's xmax/ctid (or redirected line pointer) would be tricky. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] HOT is applied
"Pavan Deolasee" <[EMAIL PROTECTED]> writes: > On 9/21/07, Tom Lane <[EMAIL PROTECTED]> wrote: >> Shouldn't we be able to prune rows that have been inserted and deleted >> by the same transaction? I'd have hoped to see this example use only >> one heap page ... >> > Not before the transaction commits ? In the test, we update a single tuple > 1 times in the same transaction. So there is no opportunity to prune. [ looks a bit more ... ] Hm, the test I was thinking of was this one at the end of HeapTupleSatisfiesVacuum: if (TransactionIdEquals(HeapTupleHeaderGetXmin(tuple), HeapTupleHeaderGetXmax(tuple))) { /* * Inserter also deleted it, so it was never visible to anyone else. * However, we can only remove it early if it's not an updated tuple; * else its parent tuple is linking to it via t_ctid, and this tuple * mustn't go away before the parent does. */ if (!(tuple->t_infomask & HEAP_UPDATED)) return HEAPTUPLE_DEAD; } but control never gets that far because neither xmin nor xmax is committed yet. We could fix that, probably, by considering the xmin=xmax case in the xmin-in-progress case further up; but the HEAP_UPDATED exclusion is still a problem. Still, it seems like this is leaving some money on the table when you think about pruning a HOT chain. Can we improve on it easily? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] HOT is applied
On 9/21/07, Tom Lane <[EMAIL PROTECTED]> wrote: > > > Shouldn't we be able to prune rows that have been inserted and deleted > by the same transaction? I'd have hoped to see this example use only > one heap page ... > > Not before the transaction commits ? In the test, we update a single tuple 1 times in the same transaction. So there is no opportunity to prune. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] HOT is applied
I wrote: > ... so basically it's all about the locking. Maybe the problem is that with > HOT we lock the buffer too often? heap_page_prune_opt is designed to > not take the buffer lock unless there's a good probability of needing > to prune, but maybe that's not working as intended. Indeed it seems it's not; gprof shows 0.000.00 111/1389276 index_getnext [89] 0.05 49.52 1389165/1389276 heapgetpage [6] [8] 50.90.05 49.52 1389276 heap_page_prune_opt [8] 7.17 42.31 1366696/1366696 heap_page_prune [9] 0.010.03 1366696/1366696 ConditionalLockBufferForCleanup [106] 0.010.00 278/2780795 PageGetHeapFreeSpace [177] so this example is getting past the heuristic tests in heap_page_prune_opt almost every time. Why is that? Too tired to poke at it more tonight. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] ecpg PREPARE is not thread safe
Hi, I encountered segfaults in a multi-threaded ecpg application. It uses PREPARE statements concurrently in several threads. The cause seems to be the global variable 'prep_stmts' in ecpg/ecpglib/prepare.c . It is accessed without any locks. I'm trying to fix it, but there are some approaches to fix it. 1. Add a giant lock to protect prep_stmts. 2. Put prep_stmts into TSD (Thread Specific Data). 3. Put prep_stmts into connection specific data. I think the proper approach is 3, because server-side prepared statements are independent in each connection. For that matter, are there any problems in current codes? Prepared statements are managed with a single list in it. Even if we have some kinds of exclusive controls, current ecpg might not good at prepared statements when we use multiple connections in a signle thread or do multiple PREPARE in multiple threads. If so, 1 and 2 are not correct fixes. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] HOT is applied
"Heikki Linnakangas" <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> I'd still like to think about whether we >> can be smarter about when to invoke pruning, but that's a small enough >> issue that the patch can go in without it. > Yeah. I'm doing some micro-benchmarking, and the attached test case is > much slower with HOT. It's spending a lot of time trying to prune, only > to find out that it can't. Not sure if that's an appropriate description or not. oprofile (on a dual Xeon running Fedora 6) shows me this: CPU: P4 / Xeon with 2 hyper-threads, speed 2792.99 MHz (estimated) Counted GLOBAL_POWER_EVENTS events (time during which processor is not stopped) with a unit mask of 0x01 (mandatory) count 10 samples %symbol name 1070003 29.8708 LWLockAcquire 1015097 28.3380 LWLockRelease 2835147.9147 heap_page_prune 2522707.0425 AllocSetAlloc 1489814.1590 HeapTupleSatisfiesMVCC 1464424.0882 TransactionIdIsInProgress 92673 2.5871 AllocSetFree 84966 2.3720 HeapTupleSatisfiesVacuum 83097 2.3198 TransactionIdGetStatus 80737 2.2539 SimpleLruReadPage_ReadOnly 52803 1.4741 TransactionLogFetch 43406 1.2117 heapgetpage 42536 1.1875 HeapTupleHeaderGetCmin 34842 0.9727 TransactionIdIsCurrentTransactionId 27761 0.7750 TransactionIdDidAbort 24833 0.6933 MemoryContextAlloc 16446 0.4591 TransactionIdPrecedes 16089 0.4491 HeapTupleHeaderGetCmax 12919 0.3607 hash_search_with_hash_value 11857 0.3310 pfree 4964 0.1386 heap_page_prune_opt 3683 0.1028 hash_any 3215 0.0898 LWLockConditionalAcquire 3086 0.0862 PinBuffer 2573 0.0718 UnpinBuffer 2009 0.0561 ConditionalLockBufferForCleanup 1854 0.0518 ReadBuffer_common 934 0.0261 XLogInsert 784 0.0219 heapgettup_pagemode so basically it's all about the locking. Maybe the problem is that with HOT we lock the buffer too often? heap_page_prune_opt is designed to not take the buffer lock unless there's a good probability of needing to prune, but maybe that's not working as intended. [ pokes at it a bit more... ] Actually the disturbing part is that pruning doesn't seem to be working at all: after the test finishes, I see regression=# vacuum verbose testtable; INFO: vacuuming "public.testtable" INFO: "testtable": removed 1 row versions in 44 pages INFO: "testtable": found 1 removable, 1 nonremovable row versions in 45 pages DETAIL: 0 dead row versions cannot be removed yet. There were 9955 unused item pointers. 45 pages contain useful free space. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM Shouldn't we be able to prune rows that have been inserted and deleted by the same transaction? I'd have hoped to see this example use only one heap page ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] curious regression failures
Alvaro Herrera <[EMAIL PROTECTED]> writes: > In any case, at that point we are mostly done with the expensive steps > of vacuuming, so the transaction finishes not long after this. I don't > think this issue is worth inventing a new invalidation mechanism. Yeah, I agree --- there are only a few catalog updates left to do after we truncate. If we held the main-table exclusive lock while vacuuming the TOAST table, we'd have a problem, but it looks to me like we don't. Idle thought here: did anything get done with the idea of decoupling main-table vacuum decisions from toast-table vacuum decisions? vacuum.c comments * Get a session-level lock too. This will protect our access to the * relation across multiple transactions, so that we can vacuum the * relation's TOAST table (if any) secure in the knowledge that no one is * deleting the parent relation. and it suddenly occurs to me that we'd need some other way to deal with that scenario if autovac tries to vacuum toast tables independently. Also, did you see the thread complaining that autovacuums block CREATE INDEX? This seems true given the current locking definitions, and it's a bit annoying. Is it worth inventing a new table lock type just for vacuum? 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
[HACKERS] Beginning Tamil Community for Postgre
Hi, I am Sri Ramadoss from, Chennai, India interested in forming & taking up the responsibility of Localising Postgre into my native language Tamil. I am also interested in forming a Community for Postgre in TamilNadu, a State of India. Tamil is spoken by around 70 million people across the globe. Please help me as to how to take this up further and guide me. My previous experiences in Localisation include KDE Localisation and GNU Website Localisation. -- Regards, Sri Ramadoss M. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] curious regression failures
Tom Lane wrote: > There might be another way to manage this, but we're not inventing > a new invalidation mechanism for 8.3. This patch will have to be > reverted for the time being :-( Thanks. Seems it was a good judgement call to apply it only to HEAD, after all. In any case, at that point we are mostly done with the expensive steps of vacuuming, so the transaction finishes not long after this. I don't think this issue is worth inventing a new invalidation mechanism. -- Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC "La victoria es para quien se atreve a estar solo" ---(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] stored procedure stats in collector
On Thu, 2007-09-20 at 16:08 +0300, Martin Pihlak wrote: > The GUC variable stats_function_level now takes 3 values: on, off and all. That seems a confusing set of values. Perhaps "off", "pl", and "all" would be clearer? I'm curious if you've measured the performance overhead of enabling this functionality. > PS. Would something like this be a canditate for 8.4 inclusion (if polished > up)? It sounds like a useful feature to me. -Neil ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Hash index todo list item
We are pleased to announce an upcoming patch to the hash index code which improves build time and index size, based on this item in the TODO list: During index creation, pre-sort the tuples to improve build speed http://archives.postgresql.org/pgsql-hackers/2007-03/msg01199.php Using our implementation, build times and index sizes are comparable with btree index build times and index sizes. For example, for a particular 12 million row relation, the 8.2.4 release requires over 2.8 hours to build the hash index. With our patch, the index is built in 80 seconds. Here is the link for a graph, showing a comparative analysis of btree and hash index builds and describing the benchmark data. http://web.cecs.pdx.edu/~raneyt/pg/ We are currently cleaning up the patch and will submit it asap. Regards, Shreya Bhargava <[EMAIL PROTECTED]> Tom Raney <[EMAIL PROTECTED]> Kenneth Marshall wrote: Dear PostgreSQL Hackers: After following the hackers mailing list for quite a while, I am going to start investigating what will need to be done to improve hash index performance. Below are the pieces of this project that I am currently considering: 1. Characterize the current hash index implementation against the BTree index, with a focus on space utilization and lookup performance against a collection of test data. This will give a baseline performance test to evaluate the impact of changes. I initially do not plan to bench the hash creation process since my initial focus will be on lookup performance. 2. Evaluate the performance of different hash index implementations and/or changes to the current implementation. My current plan is to keep the implementation as simple as possible and still provide the desired performance. Several hash index suggestions deal with changing the layout of the keys on a page to improve lookup performance, including reducing the bucket size to a fraction of a page or only storing the hash value on the page, instead of the index value itself. My goal in this phase is to produce one or more versions with better performance than the current BTree. 3. Look at build time and concurrency issues with the addition of some additional tests to the test bed. (1) 4. Repeat as needed. This is the rough plan. Does anyone see anything critical that is missing at this point? Please send me any suggestions for test data and various performance test ideas, since I will be working on that first. Regards, Ken Marshall ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [COMMITTERS] pgsql: Silence Solaris compiler warnings, per buildfarm.
Gregory Stark <[EMAIL PROTECTED]> writes: >> Silence Solaris compiler warnings, per buildfarm. > This one was also lost in the tsearch merge. Done. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] HOT is applied
On 9/20/07, Heikki Linnakangas <[EMAIL PROTECTED]> wrote: > Tom Lane wrote: > > I've committed the HOT patch. > > Thanks, much easier to work with it now that it's in. > > > I'd still like to think about whether we > > can be smarter about when to invoke pruning, but that's a small enough > > issue that the patch can go in without it. > > Yeah. I'm doing some micro-benchmarking, and the attached test case is > much slower with HOT. It's spending a lot of time trying to prune, only > to find out that it can't. > > Instead of/in addition to avoiding pruning when it doesn't help, maybe > we could make HeapTupleSatisfiesVacuum cheaper. > > I'm going to continue testing, this is just a heads-up that HOT as > committed seriously hurts performance in some cases. (though one can > argue that this test case isn't a very realistic one.) well, I ran your test on my box and here are the results: pre hot: run 1: 3617.641 ms run 2: 5195.215 ms run 3: 6760.449 ms after vacuum: run 1: 4171.362 ms run 2: 5513.317 ms run 3: 6884.125 ms post hot: run 1: Time: 7286.292 ms run 2: Time: 7477.089 ms run 3: Time: 7701.229 ms those results aren't exactly terrible, and this case is highly artificial. merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] HOT is applied
Tom Lane wrote: > I've committed the HOT patch. Thanks, much easier to work with it now that it's in. > I'd still like to think about whether we > can be smarter about when to invoke pruning, but that's a small enough > issue that the patch can go in without it. Yeah. I'm doing some micro-benchmarking, and the attached test case is much slower with HOT. It's spending a lot of time trying to prune, only to find out that it can't. Instead of/in addition to avoiding pruning when it doesn't help, maybe we could make HeapTupleSatisfiesVacuum cheaper. I'm going to continue testing, this is just a heads-up that HOT as committed seriously hurts performance in some cases. (though one can argue that this test case isn't a very realistic one.) -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com DROP TABLE IF EXISTS testtable; CREATE TABLE testtable (key integer); -- Note that there's no indexes, so updates have to do a seq scan. CREATE OR REPLACE FUNCTION testfunc(data int, key1 int) RETURNS int AS $$ DECLARE BEGIN FOR cnt IN 1..1 LOOP UPDATE testtable SET key = data WHERE key = key1; END LOOP; RETURN 1; END; $$ LANGUAGE plpgsql; INSERT INTO testtable VALUES (1); BEGIN; SELECT testfunc(1,1); COMMIT; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] minor compiler warning in backend/utils/adt/tsrank.c
Thank you, committed Hannes Eder wrote: while rebuilding postgres with msvc 2005 I noticed some minor compiler warnings: .\src\backend\utils\adt\tsrank.c(24): warning C4305: 'initializing' : truncation from 'double' to 'float' .\src\backend\utils\adt\tsrank.c(24): warning C4305: 'initializing' : truncation from 'double' to 'float' .\src\backend\utils\adt\tsrank.c(24): warning C4305: 'initializing' : truncation from 'double' to 'float' .\src\backend\utils\adt\tsrank.c(46): warning C4305: 'return' : truncation from 'double' to 'float4' .\src\backend\utils\adt\tsrank.c(339): warning C4305: '=' : truncation from 'double' to 'float' see attached diff -Hannes ---(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 -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(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
[HACKERS] HOT is applied
I've committed the HOT patch. I'd still like to think about whether we can be smarter about when to invoke pruning, but that's a small enough issue that the patch can go in without it. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] like/ilike improvements
I wrote: I can't see an obvious way around the problem for multi-byte case - lower() then requires converting to and from wchar, and I don't see a way of avoiding calling lower(). There is one way we could reduce the use of lower() by up to (almost) 50% in the common case where the pattern is a constant expression (or a literal, as it usually is) - cache the result of lower() on the pattern rather than call it for every text the pattern is being compared to. I'm not quite sure how to achieve that though. Anyone have good ideas? cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Improving the Performance of Full Table Updates
"Gokulakannan Somsundaram" <[EMAIL PROTECTED]> writes: > I propose to change this row-by-row approach, when it is a full table > update. I plan to send a extra flag(which will be set for Full table > Deletes/Updates). this would make the access method directly acquire the > exclusive lock and update the existing record. This sounds like a recipe for utter destruction of what little modularity and layering we've got left. And I rather doubt it will buy anything interesting performance-wise. To cite just one concrete objection: surely the tuple-fetch code has got no business firing triggers. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] like/ilike improvements
Guillaume Smet wrote: app_hls On 9/20/07, Andrew Dunstan <[EMAIL PROTECTED]> wrote: Can you retry both sets of tests but this time in C locale? The lower() code works differently in C locale, and it might be that we need to look at tweaking just one case. Please try the attached patch, which goes back to using a special case for single-byte ILIKE. I want to make sure that at the very least we don't cause a performance regression with the code done this release. I can't see an obvious way around the problem for multi-byte case - lower() then requires converting to and from wchar, and I don't see a way of avoiding calling lower(). If this is a major blocker I would suggest you look at an alternative to using ILIKE for your UTF8 data. cheers andrew Index: src/backend/utils/adt/like.c === RCS file: /cvsroot/pgsql/src/backend/utils/adt/like.c,v retrieving revision 1.69 diff -c -r1.69 like.c *** src/backend/utils/adt/like.c 2 Jun 2007 02:03:42 - 1.69 --- src/backend/utils/adt/like.c 20 Sep 2007 13:12:39 - *** *** 36,41 --- 36,43 static int UTF8_MatchText(char *t, int tlen, char *p, int plen); + static int SB_IMatchText(char *t, int tlen, char *p, int plen); + static int GenericMatchText(char *s, int slen, char* p, int plen); static int Generic_Text_IC_like(text *str, text *pat); *** *** 104,109 --- 106,117 #include "like_match.c" + /* setup to compile like_match.c for single byte case insensitive matches */ + #define MATCH_LOWER + #define NextChar(p, plen) NextByte((p), (plen)) + #define MatchText SB_IMatchText + + #include "like_match.c" /* setup to compile like_match.c for UTF8 encoding, using fast NextChar */ *** *** 132,146 int slen, plen; ! /* Force inputs to lower case to achieve case insensitivity */ ! str = DatumGetTextP(DirectFunctionCall1(lower, PointerGetDatum(str))); ! pat = DatumGetTextP(DirectFunctionCall1(lower, PointerGetDatum(pat))); ! s = VARDATA(str); ! slen = (VARSIZE(str) - VARHDRSZ); ! p = VARDATA(pat); ! plen = (VARSIZE(pat) - VARHDRSZ); ! return GenericMatchText(s, slen, p, plen); } /* --- 140,171 int slen, plen; ! /* For efficiency reasons, in the single byte case we don't call ! * lower() on the pattern and text, but instead call to_lower on each ! * character. In the multi-byte case we don't have much choice :-( ! */ ! if (pg_database_encoding_max_length() > 1) ! { ! pat = DatumGetTextP(DirectFunctionCall1(lower, PointerGetDatum(pat))); ! p = VARDATA(pat); ! plen = (VARSIZE(pat) - VARHDRSZ); ! str = DatumGetTextP(DirectFunctionCall1(lower, PointerGetDatum(str))); ! s = VARDATA(str); ! slen = (VARSIZE(str) - VARHDRSZ); ! if (GetDatabaseEncoding() == PG_UTF8) ! return UTF8_MatchText(s, slen, p, plen); ! else ! return MB_MatchText(s, slen, p, plen); ! } ! else ! { ! p = VARDATA(pat); ! plen = (VARSIZE(pat) - VARHDRSZ); ! s = VARDATA(str); ! slen = (VARSIZE(str) - VARHDRSZ); ! return SB_IMatchText(s, slen, p, plen); ! } } /* Index: src/backend/utils/adt/like_match.c === RCS file: /cvsroot/pgsql/src/backend/utils/adt/like_match.c,v retrieving revision 1.16 diff -c -r1.16 like_match.c *** src/backend/utils/adt/like_match.c 2 Jun 2007 02:03:42 - 1.16 --- src/backend/utils/adt/like_match.c 20 Sep 2007 13:12:39 - *** *** 13,18 --- 13,19 * NextChar * MatchText - to name of function wanted * do_like_escape - name of function if wanted - needs CHAREQ and CopyAdvChar + * MATCH_LOWER - define iff using to_lower on text chars * * Copyright (c) 1996-2007, PostgreSQL Global Development Group * *** *** 68,73 --- 69,80 * */ + #ifdef MATCH_LOWER + #define TCHAR(t) tolower((t)) + #else + #define TCHAR(t) (t) + #endif + static int MatchText(char *t, int tlen, char *p, int plen) { *** *** 143,155 else { ! char firstpat = *p ; if (*p == '\\') { if (plen < 2) return LIKE_FALSE; ! firstpat = p[1]; } while (tlen > 0) --- 150,162 else { ! char firstpat = TCHAR(*p) ; if (*p == '\\') { if (plen < 2) return LIKE_FALSE; ! firstpat = TCHAR(p[1]); } while (tlen > 0) *** *** 158,164 * Optimization to prevent most recursion: don't recurse * unless first pattern byte matches first text byte. */ ! if (*t == firstpat) { int matched = MatchText(t, tlen, p, plen); --- 165,171 * Optimization to prevent most recursion: don't recurse * unless first pattern byte matches first text byte. */ ! if (TCHAR(*t)
Re: [HACKERS] stored procedure stats in collector
Howdy, Here's an updated version of the function stats patch. The biggest change is that by default only procedural language functions are counted. The GUC variable stats_function_level now takes 3 values: on, off and all. The latter also counts SQL and C language functions, "on" means only to count procedural language functions. The decision is now made in fmgr_info_cxt_security() Trigger functions are now also counted. Sample output: select procname, calls, total_time, total_cpu, self_time, self_cpu from pg_stat_user_functions order by self_cpu desc limit 5; procname | calls | total_time | total_cpu | self_time | self_cpu +---++---+---+-- next_batch | 32765 | 27139 | 8574 | 27139 | 8574 fetch_batch_events | 3636 | 9252 | 5622 | 3771 | 2717 batch_event_sql| 3636 | 5454 | 2888 | 3910 | 1962 finish_batch | 3420 | 3215 | 1475 | 3215 | 1475 batch_event_tables | 3636 | 1448 | 865 | 1434 | 858 (5 rows) This is still 8.2 only (tested on 8.2.4 and 8.2.5), has seen some production usage here at Skype (about a month on reasonably busy boxes). So far so good. Couple of issues: - sometimes self_cpu > self_time - currently blaming it on Linux version of gettimeofday(). - dropped functions are not purged from stats, might bloat the stats file for some usage patterns. PS. Would something like this be a canditate for 8.4 inclusion (if polished up)? Regards, Martin 8.2-procstat.patch.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] minor compiler warning in backend/utils/adt/tsrank.c
while rebuilding postgres with msvc 2005 I noticed some minor compiler warnings: .\src\backend\utils\adt\tsrank.c(24): warning C4305: 'initializing' : truncation from 'double' to 'float' .\src\backend\utils\adt\tsrank.c(24): warning C4305: 'initializing' : truncation from 'double' to 'float' .\src\backend\utils\adt\tsrank.c(24): warning C4305: 'initializing' : truncation from 'double' to 'float' .\src\backend\utils\adt\tsrank.c(46): warning C4305: 'return' : truncation from 'double' to 'float4' .\src\backend\utils\adt\tsrank.c(339): warning C4305: '=' : truncation from 'double' to 'float' see attached diff -Hannes *** ../pgsql-cvshead/src/backend/utils/adt/tsrank.c Thu Sep 20 10:30:19 2007 --- src/backend/utils/adt/tsrank.c Thu Sep 20 12:20:10 2007 *** *** 21,27 #include "miscadmin.h" ! static float weights[] = {0.1, 0.2, 0.4, 1.0}; #define wpos(wep) ( w[ WEP_GETWEIGHT(wep) ] ) --- 21,27 #include "miscadmin.h" ! static float weights[] = {0.1f, 0.2f, 0.4f, 1.0f}; #define wpos(wep) ( w[ WEP_GETWEIGHT(wep) ] ) *** *** 43,49 word_distance(int4 w) { if (w > 100) ! return 1e-30; return 1.0 / (1.005 + 0.05 * exp(((float4) w) / 1.5 - 2)); } --- 43,49 word_distance(int4 w) { if (w > 100) ! return 1e-30f; return 1.0 / (1.005 + 0.05 * exp(((float4) w) / 1.5 - 2)); } *** *** 336,342 calc_rank_and(w, t, q) : calc_rank_or(w, t, q); if (res < 0) ! res = 1e-20; if ((method & RANK_NORM_LOGLENGTH) && t->size > 0) res /= log((double) (cnt_length(t) + 1)) / log(2.0); --- 336,342 calc_rank_and(w, t, q) : calc_rank_or(w, t, q); if (res < 0) ! res = 1e-20f; if ((method & RANK_NORM_LOGLENGTH) && t->size > 0) res /= log((double) (cnt_length(t) + 1)) / log(2.0); ---(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
[HACKERS] compiler warnings in ecpglib/execute.c (uninitialized local variable 'prepname' used)
while rebuilding pgsql with msvc 2005 I noticed this compiler warning: .\src\interfaces\ecpg\ecpglib\execute.c(1495): warning C4700: uninitialized local variable 'prepname' used ECPGfree(prepname) is called in line 1495, prepname was not unitialized befor. Below the line 1495 ECPGfree(prepname) is not called in the function ECPGdo. I didn't investigate the code in detail, but I assume that at least in some error conditions (when the function returns false?) ECPGfree(prepname) should be called. -Hannes ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Timezones change - never ending story
Gregory Stark napsal(a): "Zdenek Kotala" <[EMAIL PROTECTED]> writes: Just for information. Venezuela is going to have new timezone change (30minutes shift) on this weekend. This change is not yet integrated in the last version in Olson database. (Original announcement said it happens on 1.1.2008) Is there still time to slip this into the upcoming 8.2.5? I haven't been able to find anything which specifies precisely when it'll happen though. Just knowing the week or even day isn't enough. There is latest info http://www.worldtimezone.com/dst_news/dst_news_venezuela02.html but I think until Olson database will not be updated, we can do nothing. Maybe put some info on webpage. Zdenek ---(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] Raw device I/O for large objects
Thank you everyone for your valuable input! I will have a look at some other part of PostgreSQL, and maybe find something else to do instead. Best, Georgi ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Debugger
Peter Eisentraut schrieb: Pedro Belmino wrote: I am with a problem. When I am using debugger, breakpoints that they are inside of main function (stop the execution in the marked point) but when breakpoint is marked in another archive (index.c for example) breakpoint does not function (the execution in the point does not stop), because this happens? There is a fork() happen between the main function and what you are probably looking for break at. You need to attach to the session process after it forks, not to the main postmaster process. If you just want a single backend, and entering sql commands to the "backend>" prompt is enough for you, you should consider using the command line argument --single DATABASE for debugging. see also: http://developer.postgresql.org/pgdocs/postgres/app-postgres.html -Hannes ---(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] like/ilike improvements
On 9/20/07, Andrew Dunstan <[EMAIL PROTECTED]> wrote: > Can you retry both sets of tests but this time in C locale? The lower() > code works differently in C locale, and it might be that we need to look > at tweaking just one case. Here we go with SQL_ASCII: ** 8.1 ** cityvox_c=# SELECT e.numeve FROM evenement e WHERE e.libgeseve LIKE '%hocus pocus%'; numeve (0 rows) Time: 117.485 ms cityvox_c=# SELECT e.numeve FROM evenement e WHERE e.libgeseve ILIKE '%hocus pocus%'; numeve --- 900024298 87578 (2 rows) Time: 132.823 ms ** 8.2 ** cityvox_c=# SELECT e.numeve FROM evenement e WHERE e.libgeseve LIKE '%hocus pocus%'; numeve (0 rows) Time: 100.008 ms cityvox_c=# SELECT e.numeve FROM evenement e WHERE e.libgeseve ILIKE '%hocus pocus%'; numeve --- 900024298 87578 (2 rows) Time: 113.579 ms ** 8.3 ** cityvox_c=# SELECT e.numeve FROM evenement e WHERE e.libgeseve LIKE '%hocus pocus%'; numeve (0 rows) Time: 112.462 ms cityvox_c=# SELECT e.numeve FROM evenement e WHERE e.libgeseve ILIKE '%hocus pocus%'; numeve --- 900024298 87578 (2 rows) Time: 160.961 ms -- Guillaume ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Improving the Performance of Full Table Updates
The obvious advantages are a) Avoidance of one read lock per page b) One Big write lock instead of multiple write locks. But as you said, i will do some initial profiling and get back. Thanks, Gokul. On 9/20/07, Heikki Linnakangas <[EMAIL PROTECTED]> wrote: > > Gokulakannan Somsundaram wrote: > > Hi, > >The Current architecture of Updates in PostgreSQL is > > 1) Make a select query out of update. It involves a READ > lock/BUFFER_SHARE > > 2) Get the tupleid > > 3) Goto the buffer containing the tupleid, make a BUFFER_EXCLUSIVE lock > on > > it > > 4) update it > > 5) Repeat the above process for subsequent rows > > > > I propose to change this row-by-row approach, when it is a full table > > update. I plan to send a extra flag(which will be set for Full table > > Deletes/Updates). this would make the access method directly acquire the > > exclusive lock and update the existing record. > > > > For Deletes this is simple. But for updates, the projection tuple has to > be > > made before re-inserting it. So there will be a list of Heap tuples > stored > > in memory for each page getting updated. these tuples will be inserted > after > > the deletion part of update is done. This is just a rough design. I may > get > > involved in a detail design once i get a nod from the mailing list > > community. > > I doubt the locking overhead is that significant. Have you done any > profiling to show that it's worth it? > > -- > Heikki Linnakangas > EnterpriseDB http://www.enterprisedb.com >
Re: [HACKERS] Improving the Performance of Full Table Updates
Gokulakannan Somsundaram wrote: > Hi, >The Current architecture of Updates in PostgreSQL is > 1) Make a select query out of update. It involves a READ lock/BUFFER_SHARE > 2) Get the tupleid > 3) Goto the buffer containing the tupleid, make a BUFFER_EXCLUSIVE lock on > it > 4) update it > 5) Repeat the above process for subsequent rows > > I propose to change this row-by-row approach, when it is a full table > update. I plan to send a extra flag(which will be set for Full table > Deletes/Updates). this would make the access method directly acquire the > exclusive lock and update the existing record. > > For Deletes this is simple. But for updates, the projection tuple has to be > made before re-inserting it. So there will be a list of Heap tuples stored > in memory for each page getting updated. these tuples will be inserted after > the deletion part of update is done. This is just a rough design. I may get > involved in a detail design once i get a nod from the mailing list > community. I doubt the locking overhead is that significant. Have you done any profiling to show that it's worth it? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Improving the Performance of Full Table Updates
Hi, The Current architecture of Updates in PostgreSQL is 1) Make a select query out of update. It involves a READ lock/BUFFER_SHARE 2) Get the tupleid 3) Goto the buffer containing the tupleid, make a BUFFER_EXCLUSIVE lock on it 4) update it 5) Repeat the above process for subsequent rows I propose to change this row-by-row approach, when it is a full table update. I plan to send a extra flag(which will be set for Full table Deletes/Updates). this would make the access method directly acquire the exclusive lock and update the existing record. For Deletes this is simple. But for updates, the projection tuple has to be made before re-inserting it. So there will be a list of Heap tuples stored in memory for each page getting updated. these tuples will be inserted after the deletion part of update is done. This is just a rough design. I may get involved in a detail design once i get a nod from the mailing list community. Thanks, Gokul.