Re: [HACKERS] Background LRU Writer/free list
This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Greg Smith wrote: > I'm mostly done with my review of the "Automatic adjustment of > bgwriter_lru_maxpages" patch. In addition to issues already brought up > with that code, there are some small things that need to be done to merge > it with the recent pg_stat_bgwriter patch, and I have some concerns about > its unbounded scanning of the buffer pool; I'll write that up in more > detail or just submit an improved patch as I get time this week. > > But there's a fundamental question that has been bugging me, and I think > it impacts the direction that code should take. Unless I'm missing > something in my reading, buffers written out by the LRU writer aren't ever > put onto the free list. I assume this is to stop from prematurely > removing buffers that contain useful data. In cases where a substantial > percentage of the buffer cache is dirty, the LRU writer has to scan a > significant portion of the pool looking for one of the rare clean buffers, > then write it out. When a client goes to grab a free buffer afterward, it > has to scan the same section of the pool to find the now clean buffer, > which seems redundant. > > With the new patch, the LRU writer is fairly well bounded in that it > doesn't write out more than it thinks it will need; you shouldn't get into > a situation where many more pages are written than will be used in the > near future. Given that mindset, shouldn't pages the LRU scan writes just > get moved onto the free list? > > -- > * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Bgwriter LRU cleaning: we've been going at this all wrong
This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Gregory Stark wrote: > > "Tom Lane" <[EMAIL PROTECTED]> writes: > > > I don't really see why it's "overkill". > > Well I think it may be overkill in that we'll be writing out buffers that > still have a decent chance of being hit again. Effectively what we'll be doing > in the approximated LRU queue is writing out any buffer that reaches the 80% > point down the list. Even if it later gets hit and pulled up to the head > again. > > I suppose that's not wrong though, the whole idea of the clock sweep is that > that's precisely the level of precision to which it makes sense to approximate > the LRU. Ie, that any point in the top 20% is equivalent to any other and when > we use a buffer we want to promote it to somewhere "near" the head but any > point in the top 20% is good enough. Then any point in the last 20% should be > effectively "good enough" too be considered a target buffer to clean as well. > > If we find it's overkill then what we should consider doing is raising > BM_MAX_USAGE_COUNT. That's effectively tuning the percentage of the lru chain > that we decide we try to keep clean. > > -- > Gregory Stark > EnterpriseDB http://www.enterprisedb.com > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] SetBufferCommitInfoNeedsSave and race conditions
This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Simon Riggs wrote: > On Thu, 2007-06-28 at 20:23 -0400, Tom Lane wrote: > > "Simon Riggs" <[EMAIL PROTECTED]> writes: > > > On Thu, 2007-06-28 at 15:16 -0400, Tom Lane wrote: > > >> A quick grep suggests that VACUUM FULL might be at risk here. > > > > > No we're clear: I caught that issue specifically for VACUUM FULL fairly > > > early on. VF assumes all hint bits are set after the first scan, so we > > > flush prior to the scan to ensure its safe to set the hint bits. > > > > Flush what prior to the scan? > > > > The methodology I suggested earlier (involving tracking LSN only at the > > level of pg_clog pages) isn't going to make that work, unless you > > somehow force the XID counter forward to the next page boundary. > > It might be that that level of tracking is too coarse anyway, since > > it essentially says that you can't hint any transaction until the > > next 32K-transaction boundary is reached. > > Solutions I'm going for are these: > > - Force XLogFlush() prior to initial VF scan. Tqual will set hint bits > if WAL has been flushed, else it will be deferred, so no WAL flushes > will be forced by normal hint bit setting and VF will work without > needing any crufty special cases or rework of VF logic. > > - Use Tom's LSN tracking at clog page level. Make the LSN tracking store > an array of LSNs rather than just one. Array size is fixed at > NUMBER_TRACKED_LSNS_PER_PAGE, so that each LSN covers > 32,000/NUMBER_TRACKED_LSNS_PER_PAGE transactions. I'd guess that storing > 8 per page would be optimal, so each stored xid would track 4,000 > transactions - probably around 1 sec worth of transactions when the > feature is used. > > Comments? > > -- > Simon Riggs > EnterpriseDB http://www.enterprisedb.com > > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Postgresql.conf cleanup
This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Josh Berkus wrote: > All, > > I'm working on cleaning up postgresql.conf and pg_settings for the > release. Attached is a sample WIP. It's not in patch form because I'm > not done yet; I've just been editing postgresql.conf and need to fix the > docs and pg_settings to match. > > Issues encountered and changes made: > > PostgreSQL.conf > > > suggestions: added section with the 7 most important obvious settings at > the top and suggestions on how to calculate them. If people like this, > I'll add it to the Tutorial in the docs as well. > > seq_scan_cost: this is independant of all of the other _costs. I can't > think of any way in which that doesn't make the whole set of costs > unmanageable. For example, if you want to change seq_scan_cost in order > to make query cost more-or-less match up with ms execution time, you > have to modify all 6 settings. If we do implement per-tablespace > costs, then we'll need per-tablespace random_page_cost as well. Or am I > missing something? > > (change requires restart): this phrase appears over 20 times in the > notes. This is enough times to be really repetitive and take up a lot > of scrolling space, while not actually covering all startup-time > parameters. We should either (a) remove all such notes and rely on > docs, or (b) make an annotation symbol (e.g. *R) and mark 100% of them. > Votes? > > Vacuum: all vacuum & autovacuum parameters put under their own section. > > Client Cost Defaults: this section became a "catch-all" for all userset > parameters which people weren't sure what to do with. I've divided it > into logical subsections, and moved some parameters to other sections > where they logically belong (for example, explain_pretty_print belongs > in Query Tuning). > > pg_settings issues > > > transaction_isolation and transaction_read_only appear more than once in > the pg_settings pseudo_table. The setting column is supposed to be unique. > > > Given the amount of cleanup/improvement which I'm seeing as necessary > for the GUCs, I'm wondering if I put this off too long for 8.3. > > --Josh > > > > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Reviewing new index types (was Re: [PATCHES] Updated bitmap indexpatch)
This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Simon Riggs wrote: > On Sat, 2007-07-21 at 12:20 +0100, Simon Riggs wrote: > > > I'd like to help where I can if nobody else is currently doing this. I > > would focus initially on some analysis of the various use cases to give > > a better view on what we would need B-tree, clustered indexes and bitmap > > indexes to do for us. > > I've done some further analysis of bitmap indexes in preparation for a > comparison with clustered indexes (GIT), to help understand the use > cases for each. > > Overall, my conclusion is that BMI and GIT have separate use cases, > almost opposite use cases or at least orthogonal ones. I would > eventually like both. BMI optimises for high numbers of rows per value, > whilst GIT optimises for clustering of values. BMI is not useful at all > for PKs, whilst GIT is specifically designed to handle them. Both handle > INSERTs well, though GIT handles growing numbers of values easily, BMI > prefers to keep the distribution more constant. GIT needs HOT to > continue to operate effectively for long periods, whereas BMI doesn't > seem to handle UPDATEs well at all (but more testing required on that > one). > > --- > > Neither the latest bitmap index nor the latest GIT patch applied > cleanly. The bitmap patch was close, but GIT needs an update yet to > integrate Alexey's recent work. > > My test case was a table with 10 million rows, with columns with varying > numbers of unique values. So Ndistinct = 100 means 100,000 rows per > value. > > BITMAP INDEXES > > Ndistinct Best time Size in blocks > 1 10.6s 100 > 1010.4s 102 > 100 11.7s 2002 > 1000 15.1s 6006 > 1 19.8s 10046 > 1082.1s 100442 > 100 - >45 > > Size exactly equivalent for both Integer and Text (same values). Build > time was similar also. > > The test for 1 million distinct values didn't return after over 4 CPU > minutes expended with the disk going crazy. After a number of minutes I > decided to cancel the index build. Multiple cancels didn't stop the > build, so after some more time I decided to kill it, which then crashed > the server. Automatic restart crashed as well with a "could not find > transaction id 0" error. Clearly some WAL-weirdness to investigate... > > Overall, I'd have to say that's quite enough for me to say bitmap is not > quite ready yet without clear health warnings. I had hopes... > > B-TREE INDEXES (Integers) > > Rows/valueBest time Size in blocks > 1000 49s 21899 > 100 49s 21899 > 1049s 21899 > 1 47s 21899 > 1000 43s 21899 > 100 38s 21899 > 1038s 21899 > 1 33s 21899 > > Build time for Integers shown. Build time for Text ~x5-6 times as long. > > Testing against equivalent b-tree builds, the fastest b-tree build I > could get was 33s on a unique integer index. So BMI build time is > certainly optimised for low numbers of distinct values, but doesn't have > any optimisation for when the BMI is built on a poor candidate column. > GIT does degrade down to a normal b-tree when clustering isn't > sufficient to give reduction in index size. > > The cross-over point was between 10^4 and 10^5 distinct values for both > size and build time; on that test around 100-1000 rows per value. So > BMIs are probably still useful with varying number of rows per value, > but overall high Ndistinct proves inefficient in both build time and > space allocation. This isn't such a surprise since we know that b-tree > build uses a sort-based plan whereas BMI uses a hash based plan; neither > will win all of the time, we know that from the executor. > > GIT works well even with unique indexes, since each grouped tuple covers > a range of values. I'll re-run the tests when I can to get timings. GIT > can compress typically down to 1-5% with clustered data, not quite as > good as bitmap's 0.5% best. > > GIT's design was to have an index that was tuned for clustered data, yet > degrades cleanly to a standard b-tree when conditions are not right. > This makes me think that a hybrid b-tree should be possible, even > desirable. When the data is clustered, use the grouping technique to > reduce he number of tuples stored and when the data is highly non-unique > use the bitmap technique to reduce numbers of tuples. Using both > techniques in the same index would offer even wider flexibility, since > we'd be able to cater for real-world data more easily. Both GIT and BMI > use bitmaps, just in different ways. > > -- > Simon Riggs > EnterpriseDB http://w
Re: [HACKERS] pgcrypto & strong ciphers limitation
Just confirming, this should be applied to 8.3, right? --- Zdenek Kotala wrote: > Stefan reported me that prcrypto regression test fails on solaris 10 > with openssl support. I investigated this problem and the result is that > Solaris 10 delivers only support for short keys up to 128. Strong crypto > (SUNWcry and SUNWcryr packages) is available on web download pages. (It > is result of US crypto export policy.) > > However, on default installation (which is commonly used) it is a > problem. Regression test cannot be fixed because it tests strong > ciphers, but there two very strange issue: > > 1) First issue is blowfish cipher. Because pgcrypto uses old interface > instead new "evp" it calls bf_set_key function which does not return any > output and cut key if it is too long. See > http://src.opensolaris.org/source/xref/onnv/onnv-gate/usr/src/common/openssl/crypto/bf/bf_skey.c > line 84. > > If user installs strong crypto he will not be able decrypt data which > has been encrypted before. > > The fix of this issue is ugly, because there is not way how to verify > supported key length with old openssl API and only new API return err if > length is not supported. > > > 2) AES ciphere crashes when key is longer. It happens because return > value from AES_set_encrypt_key is ignored and AES_encrypt is called with > uninitialized structure. > > > I attach patch which fix both issues, but main problem is there that old > openssl API is used and supported key lengths are hardcoded. I think we > can add to TODO list rewrite pgcrypto to use evp openssl interface. > > > Any comments? > > Zdenek > > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pgcrypto & strong ciphers limitation
Bruce Momjian wrote: Just confirming, this should be applied to 8.3, right? I think marko is working on an updated patch for this: http://archives.postgresql.org/pgsql-hackers/2007-09/msg00386.php without that the backend will coredump if ones uses string ciphers with pgcrypto on a default solaris install so it seems like a thing we should fix for 8.3. Stefan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Other ecpg thread unsafety
I looked for another thread-unsafe usage of variables like prep_stmts, and I found the 4 possibly-unsafe ones: [1] all_descriptors in ecpg/ecpglib/descriptor.c: Management of descriptors used by SQL DESCRIPTOR is not safe. [2] auto_allocs in ecpg/ecpglib/memory.c: All memory allocation for statements are not safe. [3] nextStmtID in ecpg/ecpglib/prepare.c: Increment is not safe in ECPGauto_prepare(). [4] stmtCacheEntries in ecpg/ecpglib/prepare.c: Reading/writing are not safe in ECPGauto_prepare(). I'm not convinced that all of the above are completely dangerous, but [2] might affect all of users writing multi-threaded ecpg programs... Are there any bugs we should fix? Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(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] Include Lists for Text Search
This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Simon Riggs wrote: > On Mon, 2007-09-10 at 10:21 -0400, Tom Lane wrote: > > Oleg Bartunov <[EMAIL PROTECTED]> writes: > > > On Mon, 10 Sep 2007, Simon Riggs wrote: > > >> Can we include that functionality now? > > > > > This could be realized very easyly using dict_strict, which returns > > > only known words, and mapping contains only this dictionary. So, > > > feel free to write it and submit. > > > > ... for 8.4. > > I've coded a small patch to allow CaseSensitive synonyms. > > CREATE TEXT SEARCH DICTIONARY my_diction ( > TEMPLATE = biglist, > DictFile = words, > CaseSensitive = true > ); > > -- > Simon Riggs > 2ndQuadrant http://www.2ndQuadrant.com [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] SetBufferCommitInfoNeedsSave and race conditions
On Wed, 2007-09-26 at 04:33 -0400, Bruce Momjian wrote: > This has been saved for the 8.4 release: > > http://momjian.postgresql.org/cgi-bin/pgpatches_hold Already applied. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] 8.3 performance features presentation
Here is the talk I gave in Moscow highlighting 8.3 performance features: http://momjian.us/main/writings/pgsql/features.pdf -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Other ecpg thread unsafety
On Wed, Sep 26, 2007 at 05:56:59PM +0900, ITAGAKI Takahiro wrote: > [1] all_descriptors in ecpg/ecpglib/descriptor.c: > Management of descriptors used by SQL DESCRIPTOR is not safe. > > [2] auto_allocs in ecpg/ecpglib/memory.c: > All memory allocation for statements are not safe. These two should suffer from the same problem prep_stmt suffered from. > [3] nextStmtID in ecpg/ecpglib/prepare.c: > Increment is not safe in ECPGauto_prepare(). Not sure whether this is really a problem, but if we're fixing stuff anyway, why not play it safe. > [4] stmtCacheEntries in ecpg/ecpglib/prepare.c: > Reading/writing are not safe in ECPGauto_prepare(). This also doesn't look like a dangerous bug, but it's still not working as it should. I'd say let's fix them all. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] top for postgresql (ptop?)
Mark, Very interesting. I'm looking for such tool. Unfortunately, I can't compile it on my Solaris right now, but I hope it will be shipped with PostgreSQL distribution. Mark Wong wrote: Hi everyone, I was playing with converting unixtop (the version of top used in FreeBSD) to only show PostgreSQL processes pulled from the pg_stat_activity table. I have a version that kind of works here: http://pgfoundry.org/frs/download.php/1468/ptop-3.6.1-pre6.tar.gz I've tried it on FreeBSD and Linux, not sure about other platforms though. So it looks a lot like top and can currently do a few simple things like display the current_query from pg_stat_activity for a given process, show the locks held by a process and on which tables, and show the query plan for the current query. It is a ways from polished (not really documented, etc.) but I wanted to see what people thought of a text/curses sort of monitoring tool like this. Maybe something to distribute with PostgreSQL? :) Forgive me if I didn't try out pgtop (the CPAN module.) Regards, Mark ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- NAGAYASU Satoshi <[EMAIL PROTECTED]> Phone: +81-50-5546-2496 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] stored procedure stats in collector
This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Martin Pihlak wrote: > 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 > [ application/x-gzip is not supported, skipping... ] > > ---(end of broadcast)--- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] MSVC build scripts status
On Tue, Sep 25, 2007 at 03:25:08PM +0200, Magnus Hagander wrote:
> > > /* exec sql type customer is struct { #line 12 "array_of_struct.pgc"
> > > struct varchar_name_12 { int len; char arr[ 50 ]; } name; #line 13
> > > "array_of_struct.pgc" int phone; } */ #line 14
> > > "array_of_struct.pgc"
> > > ...
> > > Seems like the entire definition of the struct is commented out?
> ...
> Any idea as to *why* it's doing that?
This might be too simple but are you sure ecpg is called with option
"-c" when compiling array_of_struct.pgc? It is listed that way in the
Makefile, however lacking this option should generate exactly the file
you sent.
Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED]
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] MSVC build scripts status
On Wed, Sep 26, 2007 at 01:07:04PM +0200, Michael Meskes wrote:
> On Tue, Sep 25, 2007 at 03:25:08PM +0200, Magnus Hagander wrote:
> > > > /* exec sql type customer is struct { #line 12 "array_of_struct.pgc"
> > > > struct varchar_name_12 { int len; char arr[ 50 ]; } name; #line
> > > > 13 "array_of_struct.pgc" int phone; } */ #line 14
> > > > "array_of_struct.pgc"
> > > > ...
> > > > Seems like the entire definition of the struct is commented out?
> > ...
> > Any idea as to *why* it's doing that?
>
> This might be too simple but are you sure ecpg is called with option
> "-c" when compiling array_of_struct.pgc? It is listed that way in the
> Makefile, however lacking this option should generate exactly the file
> you sent.
Well, bingo. With that, ti passes that check. Oops. Will commit fix for
that.
But it's not done yet, I now get a different problem :-)
define.c
define.pgc(13): error C2059: syntax error : '['
diff of the .c file is:
17c17
< #line 1 "./../regression.h"
---
> #line 1 "regression.h"
34c34
< typedef int [ 6 ] intarray ;
---
> /* exec sql type intarray is int [ 6 ] */
//Magnus
---(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] Regression test message
Hi All, I am trying to run Regression test on postgreSQL-7.2.8, it got installed successfully, but the regression test is not going through, it is giving the following errors... == 78 of 79 tests passed, 1 failed test(s) ignored. == The differences that caused some tests to fail can be viewed in the file `./regression.diffs'. A copy of the test summary that you see above is saved in the file `./regression.out'. I am also pasting whatever is listed in the regression.diffs file, it is as follows *** ./expected/geometry.out Fri Nov 30 00:27:31 2001 --- ./results/geometry.out Wed Sep 26 16:13:00 2007 *** *** 150,160 six |box -+ | (2.12132034355964,2.12132034355964),(-2.12132034355964,-2.12132034355964) ! | (71.7106781186548,72.7106781186548),(-69.7106781186548,-68.7106781186548) ! | (4.53553390593274,6.53553390593274),(-2.53553390593274,-0.535533905932738) ! | (3.12132034355964,4.12132034355964),(-1.12132034355964,-0.121320343559643) | (107.071067811865,207.071067811865),(92.9289321881345,192.928932188135) ! | (170.710678118655,70.7106781186548),(29.2893218813452,-70.7106781186548) (6 rows) -- translation --- 150,160 six |box -+ | (2.12132034355964,2.12132034355964),(-2.12132034355964,-2.12132034355964) ! | (71.7106781186547,72.7106781186547),(-69.7106781186547,-68.7106781186547) ! | (4.53553390593274,6.53553390593274),(-2.53553390593274,-0.535533905932737) ! | (3.12132034355964,4.12132034355964),(-1.12132034355964,-0.121320343559642) | (107.071067811865,207.071067811865),(92.9289321881345,192.928932188135) ! | (170.710678118655,70.7106781186547),(29.2893218813453,-70.7106781186547) (6 rows) -- translation == Please tell me what shall I do to resolve this issue. Thanks and Regards Cinu Kuriakose ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Regression test message
"Kuriakose, Cinu Cheriyamoozhiyil" <[EMAIL PROTECTED]> writes: > Hi All, > > I am trying to run Regression test on postgreSQL-7.2.8, it got installed > successfully, but the regression test is not going through, it is giving the > following errors... What architecture is this? And why would you be building 7.2.8, an unsupported release over two years and 5 major releases old? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] MSVC build scripts status
On Wed, Sep 26, 2007 at 01:15:21PM +0200, Magnus Hagander wrote: > > This might be too simple but are you sure ecpg is called with option > > "-c" when compiling array_of_struct.pgc? It is listed that way in the > > Makefile, however lacking this option should generate exactly the file > > you sent. > > Well, bingo. With that, ti passes that check. Oops. Will commit fix for > that. > > But it's not done yet, I now get a different problem :-) > > define.c > define.pgc(13): error C2059: syntax error : '[' Not really, it looks like you're using "-c" on this file too. This one, however, is supposed to be compiled without "-c". > diff of the .c file is: > 17c17 > < #line 1 "./../regression.h" > --- > > #line 1 "regression.h" Wonder where this comes from. It should look the same for each source file. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] MSVC build scripts status
> > > This might be too simple but are you sure ecpg is called with option > > > "-c" when compiling array_of_struct.pgc? It is listed that way in the > > > Makefile, however lacking this option should generate exactly the file > > > you sent. > > > > Well, bingo. With that, ti passes that check. Oops. Will commit fix for > > that. > > > > But it's not done yet, I now get a different problem :-) > > > > define.c > > define.pgc(13): error C2059: syntax error : '[' > > Not really, it looks like you're using "-c" on this file too. This one, > however, is supposed to be compiled without "-c". Indeed I am, I changed all of the invocations. on my cell now so can't read the code - how do I determine which files need it? > > > diff of the .c file is: > > 17c17 > > < #line 1 "./../regression.h" > > --- > > > #line 1 "regression.h" > > Wonder where this comes from. It should look the same for each source > file. > Probably the same kind of issue, even though it worked before. will investigate when the other stuff works. /Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] stored procedure stats in collector
On Thu, Sep 20, 2007 at 05:34:32PM -0700, Neil Conway wrote: > 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. i'm quite worried about "all" setting. all operators are functions as well, so tracking literally all function calls might be too heavy. on the other hand i would like to be able to track some non-pl functions like RI%. depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] (bez předmětu)
---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] stored procedure stats in collector
hubert depesz lubaczewski wrote: On Thu, Sep 20, 2007 at 05:34:32PM -0700, Neil Conway wrote: 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. i'm quite worried about "all" setting. all operators are functions as well, so tracking literally all function calls might be too heavy. "all" would additionally count only C and SQL functions, builtins (language "internal") are skipped. Maybe there should be option to count these as well, perhaps with a really scare name (track_functions = all_plus_internal). on the other hand i would like to be able to track some non-pl functions like RI%. These are builtin. Regards, Martin ---(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] MSVC build scripts status
On Wed, Sep 26, 2007 at 02:12:55PM +0200, Magnus Hagander wrote: > > Not really, it looks like you're using "-c" on this file too. This one, > > however, is supposed to be compiled without "-c". > > Indeed I am, I changed all of the invocations. on my cell now so can't read > the code - how do I determine which files need it? It's only array_of_struct that needs this option. I think one test for this special option should be sufficient. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 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] Improving the Performance of Full Table Updates
Hi Tom/ Heikki, Thanks for the suggestion. After profiling i got similar results. So i am thinking of a design like this to get the performance improvement. a) We can get one page for insert(during update) and we will hold the write lock on it, till the page gets filled. In this way, RelationGetBufferForTuple will get called only once for one page of inserts. b) Do you think if we can optimize the XlogInsert in such a way, it will write a page instead of writing all the records in the page. I think we need to write a recovery routine for the same. Currently the page gets flushed to the WAL, if it gets modified after the checkpoint. So i still need to understand those code pieces. But do you think it is wise to continue working on this line? Thanks, Gokul. On 9/21/07, Heikki Linnakangas <[EMAIL PROTECTED]> wrote: > > Gokulakannan Somsundaram wrote: > > Again if this full table updates are thought with the OLTP applications > in > > mind, then this is not at all a suitable option. This will only benefit > the > > people with Data Warehouses. > > > > Expecting some more replies > > Start with profiling. > > I just ran a quick oprofile run of a full-table UPDATE on a simple table > with one index, and it looks like RelationGetBufferForTuple uses 4.53% > of the CPU time. Out of that, 2.86 percentage points are spent in > ReadBuffer_common. That means that write-locking the heap pages takes at > most 4.53 - 2.86 = 1.67 % of the total CPU time. > > That's the upper limit of the benefit from the scheme you're proposing. > Surely the effort would be better spent on something else. For example, > if you kept the insertion target page just pinned over the calls, which > wouldn't have the problems with triggers etc, you could save that 2.86%. > Which still isn't much. Or take a look at WAL logging. XLogInsert took > 16.06% of the CPU time. Earlier tests have suggested that a big chunk of > that time is spent in CRC calculation. Alternative CRC methods have been > suggested in the past, or perhaps that could time could be offloaded to > the WAL writer process, speeding up the UPDATE on a multi-CPU server. > > Also, if we're talking about data warehousing, we're talking about big > tables that don't fit in memory. That means that you're likely > bottlenecked by I/O speed, not CPU. If that's the case, saving some CPU > time makes no difference whatsoever. What would help with I/O bottleneck > is to try to make the disk footprint smaller, or make better use of the > I/O bandwidth available. > > Three steps to improve throughput: > > 1. Identify the hardware component that's the bottleneck. > 2. Profile the workload to see what's using the bottlenecked resource > the most. > 3. Figure out how to make that piece of code cheaper. > > -- > Heikki Linnakangas > EnterpriseDB http://www.enterprisedb.com >
Re: [HACKERS] Regression test message
"Kuriakose, Cinu Cheriyamoozhiyil" <[EMAIL PROTECTED]> writes: > 78 of 79 tests passed, 1 failed test(s) ignored. > Please tell me what shall I do to resolve this issue. Nothing --- the reason it's ignored is it's not significant. I concur though with Greg's question: why aren't you building something more modern? 7.2.x was desupported more than two years ago, and contains many known unfixed (some unfixable) bugs. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pgcrypto & strong ciphers limitation
Stefan Kaltenbrunner wrote: Bruce Momjian wrote: Just confirming, this should be applied to 8.3, right? I think marko is working on an updated patch for this: http://archives.postgresql.org/pgsql-hackers/2007-09/msg00386.php without that the backend will coredump if ones uses string ciphers with pgcrypto on a default solaris install so it seems like a thing we should fix for 8.3. Yes, I also would like to have backport for 8.2 and 8.1. Because this branch are also affected. (I think backport is easy there are no much change between 8.1 and 8.3) Zdenek ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Cannot properly initialize inet type fields in the table
I have a CREATE TABLE statement where I am trying to initialize several inet fields, e.g. CREATE TABLE myConfigTable ( telnetbool DEFAULT FALSE, ftp bool DEFAULT FALSE, ipForwarding bool DEFAULT FALSE, configIntControlNetw inet DEFAULT '1.1.0.0', configIntControlMask inet DEFAULT '255.255.0.0' ); When I try to execute an INSERT statement associated with this table I am getting: NOTICE: failed to convert IP address string, invalid inetstruct len NOTICE: failed to convert IP address string, invalid inetstruct len for each initialized inet field. Does anyone know how to fix this problem? This code used to work under psql version 7.2.1, but I am trying to make it work under 8.1.4. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Cannot properly initialize inet type fields in the table
fimarn <[EMAIL PROTECTED]> writes: > When I try to execute an INSERT statement associated with this table I > am getting: > NOTICE: failed to convert IP address string, invalid inetstruct len Your example works fine for me: regression=# insert into myConfigTable default values; INSERT 0 1 regression=# select * from myConfigTable; telnet | ftp | ipforwarding | configintcontrolnetw | configintcontrolmask +-+--+--+-- f | f | f| 1.1.0.0 | 255.255.0.0 (1 row) and I can find no trace of any such message in either CVS HEAD or 8.1. Are you running a custom-modified backend? regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] uh-oh, dugong failing again (was Re: Pgbuildfarm-status-green Digest, Vol 28, Issue 24)
> The PGBuildfarm member dugong had the following event on branch HEAD: > Status changed from OK to ContribCheck failure > The snapshot timestamp for the build that triggered this notification is: > 2007-09-25 20:05:01 This seems to be exactly what we saw two weeks ago, and I just noticed that in the JIT bgwriter patch, I put an Assert into ForwardFsyncRequest in exactly the place where one was removed to make icc happy two weeks ago. This one is less cosmetic and so I'm not as willing to just take it out. I think we need to look closer. Can we confirm that ForwardFsyncRequest somehow becomes a no-op when icc compiles it with an Assert right there? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Reducing NUMERIC size for 8.3
Andrew Dunstan wrote: Zdenek Kotala wrote: Tom Lane wrote: Gregory Stark <[EMAIL PROTECTED]> writes: We previously discussed compressing the numeric data type for small values: http://archives.postgresql.org/pgsql-hackers/2007-06/msg00715.php We didn't do this for 8.3 but in any case Tom did suggest we ought to reverse the weight and sign/dscale so we could do this sometime without introducing another incompatibility. I had forgotten about that, but it does seem like a good idea to do it now. Any objections? For in-place upgrade purpose It would be good change also OID for numeric type and preserve current OID for current implementation on updated system. If we want to get into that game we need a better way of allocating Oids. Right now anything not currently used is liable to be grabbed, so there's a high risk of reuse. Yes, it will be necessary. Or maybe second way is create only really base types (name, int, bool ...) on bootstrap and others types will be created in standard manner by CREATE TYPE, CREATE OPERATOR ... commands. Or third way is not remove old datatypes and only rename them to e.g. numeric_old1 ... Zdenek ---(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] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
>>> On Mon, Sep 24, 2007 at 4:17 PM, in message <[EMAIL PROTECTED]>, "Kevin Grittner" <[EMAIL PROTECTED]> wrote: > On Thu, Sep 6, 2007 at 7:03 PM, in message >> <[EMAIL PROTECTED]>, Jeff Davis <[EMAIL PROTECTED]> >> wrote: >>> >>> I think ... there's still room for a simple tool that can zero out >>> the meaningless data in a partially-used WAL segment before compression. > > so I'm looking for advice, direction, and suggestions before I get started. Lacking any suggestions, I plowed ahead with something which satisfies our needs. First, rough, version attached. It'll save us buying another drawer of drives, so it was worth a few hours of research to figure out how to do it. If anyone spots any obvious defects please let me know. We'll be running about 50,000 WAL files through it today or tomorrow; if any problems turn up in that process I'll repost with a fix. Given the lack of response to my previous post, I'll assume it's not worth the effort to do more in terms of polishing it up; but if others are interested in using it, I'll make some time for that. Adding this to the pipe in our archive script not only saves disk space, but reduces the CPU time overall, since gzip usually has less work to do. When WAL files switch because they are full, the CPU time goes from about 0.8s to about 1.0s. -Kevin pg_clearxlogtail.c Description: Binary data ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] top for postgresql (ptop?)
On 9/25/07, Luke Lonergan <[EMAIL PROTECTED]> wrote: > Hi Mark, > > I haven't yet looked at what you've done, but I'm an enthusiastic supporter > of this idea. We're looking to do something that will view running queries > and allow drill down into those executing at any given time, showing their > plans and some notion of what operators are being executed. > > The idea of a ptop that shows running queries using a curses interface seems > like a great start. > > Our needs for data warehousing workloads are going to be different from the > OLTP users - our queries hang around long enough to warrant a drill-down. > > How far can you take the drill-down piece? Have you thought about how to > acquire the status in the executor yet? One strategy we've considered is to > use the same approach as "pstack" on Solaris - it takes a pid and dumps the > stack of a backend, which clearly shows which executor node is being worked > on currently. I think pstack uses dtrace underneath the hood... Hi Luke, Thanks. Honestly I haven't thought much past what I've thrown together thus far, so I haven't considered getting status in the executor. First thought would be to use popen() to call pstack and dump the output to the screen. Of course other suggestions are welcome. :) Regards, Mark ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] top for postgresql (ptop?)
On 9/25/07, Euler Taveira de Oliveira <[EMAIL PROTECTED]> wrote: > Mark Wong wrote: > > Hi everyone, > > > > I was playing with converting unixtop (the version of top used in > > FreeBSD) to only show PostgreSQL processes pulled from the > > pg_stat_activity table. I have a version that kind of works here: > > > > Nice idea. But I got a segfault trying to execute it. My SO is a > Slackware 12.0, glibc 2.5, gcc 4.1.2. I didn't have time to look through > the code, sorry. [snip] Thanks for that stack trace. I'm not too familiar with that part of the unixtop code, but I'll let you know if I can figure it out... Regards, Mark ---(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] top for postgresql (ptop?)
On 9/25/07, Satoshi Nagayasu <[EMAIL PROTECTED]> wrote: > Mark, > > Very interesting. I'm looking for such tool. > > Unfortunately, I can't compile it on my Solaris right now, > but I hope it will be shipped with PostgreSQL distribution. I haven't tried it on Solaris but I'm not surprised. If I can get my hands on a Solaris system I can probably get it to work. :) The code for getting the process information is platform specific and I know I've broken it for all the platforms I haven't tried it on... Regards, Mark ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Reducing NUMERIC size for 8.3
On 9/24/07, Gregory Stark <[EMAIL PROTECTED]> wrote:
>
>
> We previously discussed compressing the numeric data type for small values:
>
> http://archives.postgresql.org/pgsql-hackers/2007-06/msg00715.php
>
> We didn't do this for 8.3 but in any case Tom did suggest we ought to reverse
> the weight and sign/dscale so we could do this sometime without introducing
> another incompatibility.
>
> I think we also should move the NumericData and declaration to numeric.c and
> make the Numeric type an opaque pointer for the rest of the source tree. That
> will catch any contrib modules or third-party modules which would be broken by
> any new data representation.
>
>
> --- numeric.h 27 Feb 2007 23:48:10 + 1.24
> +++ numeric.h 24 Sep 2007 16:07:24 +0100
> @@ -63,8 +63,8 @@
> typedef struct NumericData
> {
> int32 vl_len_;/* varlena header (do not
> touch directly!) */
> - int16 n_weight; /* Weight of 1st digit */
> uint16 n_sign_dscale; /* Sign + display scale */
> + int16 n_weight; /* Weight of 1st digit */
> charn_data[1]; /* Digits (really array of
> NumericDigit) */
> } NumericData;
would this break any application pulling a numeric field as binary
over the protocol?
merlin
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Improving the Performance of Full Table Updates
Gokulakannan Somasundaram wrote: > Hi Tom/ Heikki, >Thanks for the suggestion. After profiling i got similar results. > So i am thinking of a design like this to get the performance improvement. > > a) We can get one page for insert(during update) and we will hold the write > lock on it, till the page gets filled. In this way, > RelationGetBufferForTuple will get called only once for one page of inserts. The locking actually isn't that expensive once you have the page pinned. For starters, keep the page pinned over calls to heap_update, and just relock it instead of calling RelationGetBufferForTuple. Unsurprisingly, this is not a new idea: http://archives.postgresql.org/pgsql-patches/2007-05/msg00499.php. > b) Do you think if we can optimize the XlogInsert in such a way, it will > write a page instead of writing all the records in the page. I think we > need to write a recovery routine for the same. Currently the page gets > flushed to the WAL, if it gets modified after the checkpoint. So i still > need to understand those code pieces. But do you think it is wise to > continue working on this line? It's going to be very difficult at least. There's a lot of race conditions lurking if you try to coalesce multiple updates to a single WAL record. That said, making XLogInsert faster would help a lot of use cases, not only full-table udpates. Most of the time is spent calculating the CRC, but it has some other non-trivial overhead as well. Profiling XLogInsert in more detail, and figuring out how to make it faster would be very nice. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Reducing NUMERIC size for 8.3
"Merlin Moncure" <[EMAIL PROTECTED]> writes: > would this break any application pulling a numeric field as binary > over the protocol? No. 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] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
On Wed, 2007-09-26 at 11:05 -0500, Kevin Grittner wrote: > Lacking any suggestions, I plowed ahead with something which satisfies > our needs. First, rough, version attached. It'll save us buying another > drawer of drives, so it was worth a few hours of research to figure out > how to do it. > > If anyone spots any obvious defects please let me know. We'll be running > about 50,000 WAL files through it today or tomorrow; if any problems turn > up in that process I'll repost with a fix. > > Given the lack of response to my previous post, I'll assume it's not worth > the effort to do more in terms of polishing it up; but if others are > interested in using it, I'll make some time for that. > > Adding this to the pipe in our archive script not only saves disk space, > but reduces the CPU time overall, since gzip usually has less work to do. > When WAL files switch because they are full, the CPU time goes from about > 0.8s to about 1.0s. It's nicely written and looks like it would perform well. The logic for zeroing the blocks makes me nervous. It doesn't locate the block from which to start, it treats all blocks equally, so might zero some blocks and not others. What you have should work, but I'd be inclined to put a test in there to check that doesn't happen: once we begin to zero pages, all of them should be zeroed to end of file. If we find one that shouldn't be zeroed, throw an error. We should also document that this is designed to help compress files that aren't full because we switched early because of archive_timeout. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Re: [COMMITTERS] pgsql: Reduce the size of memoryallocations by lazy vacuum when
Alvaro Herrera wrote: > How about we change it to MaxHeapTuplesPerPage for now. That closes all > complaints in this thread. Done, thanks for the input! -- Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4 "La rebeldía es la virtud original del hombre" (Arthur Schopenhauer) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
>>> On Wed, Sep 26, 2007 at 3:14 PM, in message <[EMAIL PROTECTED]>, Simon Riggs <[EMAIL PROTECTED]> wrote: > > It's nicely written Thanks. I spent some time looking at Tom Lane's pg_resetxlog and the source code for cat to model my code. I'm rather rusty on C, so I wanted to minimize the chance of doing anything outrageously stupid. Should I be including anything in the comments to give credit for that? (I'm never sure where the line is on that.) > and looks like it would perform well. In my tests so far, it is faster to pipe through this and then gzip than to just gzip, except when the WAL file is full or nearly so. In tests with small counties (which rarely fill a file except at peak periods), I'm seeing archival WAL space reduced to 27% of the original. I expect that to climb to 35% to 40% when we do all counties, but that's just a guess. I've seen some clues that it will get a bit better in 8.3 because of HOT updates. (We force WAL files to be written hourly, by the way.) For us, this reduces overall CPU time used in archiving, reduces disk space needed for backups, reduces network traffic (including over a relatively slow WAN). The one downside I've found is that it adds 0.2 seconds of CPU time per WAL file archive during our heaviest update periods. It's in the archiver process, not a backend process that's running a query, and we're not generally CPU bound, so this is not a problem for us. > The logic for zeroing the blocks makes me nervous. It doesn't locate the > block from which to start, it treats all blocks equally, so might zero > some blocks and not others. What you have should work, but I'd be > inclined to put a test in there to check that doesn't happen: once we > begin to zero pages, all of them should be zeroed to end of file. If we > find one that shouldn't be zeroed, throw an error. Agreed. That is one of the reasons I referred to this as a first, rough version. I wanted to prove the technique in general before that refinement. Another reason is that it is rather light on error checking in general. While I was loath limit it to an exact match on the magic number, since it works unmodified on multiple versions, it seems dangerous not to enforce any limits there. I wasn't sure how best to approach that. Suggestions? I think I should also error if stdin has more data when I think I'm done. Agreed? I omitted the code I was originally considering to have it work against files "in place" rather than as a filter. It seemed much simpler this way, we didn't actually have a use case for the additional functionality, and it seemed safer as a filter. Thoughts? > We should also document that this is designed to help compress files > that aren't full because we switched early because of archive_timeout. Sure. Again, this is more at a "proof of concept" stage. It's enough to get us out of a tight spot on drive space, even as it stands, but I know that it needs polishing and documentation if it is to be accepted by the community. I just wasn't sure the interest was actually there. I'm still not sure whether this might be considered for inclusion in the base release or contrib, or whether I should open a pgfoundry project. Thanks for the feedback. -Kevin ---(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] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
>>> On Wed, Sep 26, 2007 at 3:14 PM, in message <[EMAIL PROTECTED]>, Simon Riggs <[EMAIL PROTECTED]> wrote: > > The logic for zeroing the blocks makes me nervous. It doesn't locate the > block from which to start, it treats all blocks equally, so might zero > some blocks and not others. What you have should work, but I'd be > inclined to put a test in there to check that doesn't happen: once we > begin to zero pages, all of them should be zeroed to end of file. If we > find one that shouldn't be zeroed, throw an error. > > We should also document that this is designed to help compress files > that aren't full because we switched early because of archive_timeout. Attached is a modified version to implement both of these. I also bailed out if there was surplus input. I tried an optimization of allocating a separate buffer for outputting the zeros, to avoid repeated memset calls. It didn't seem to make a very big difference; do you think it's worth cluttering the code with that? -Kevin pg_clearxlogtail.c Description: Binary data ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
Kevin Grittner wrote: I omitted the code I was originally considering to have it work against files "in place" rather than as a filter. It seemed much simpler this way, we didn't actually have a use case for the additional functionality, and it seemed safer as a filter. Thoughts? A special "non-filter" mode could save some IO and diskspace by not actually writing all those zeros, but instead just seek to SizeOfWal-1 after writing the last valid byte, and writing one more zero. Of course, if you're gonna compress the WAL anyway, there is no point... greetings, Florian Pflug ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] top for postgresql (ptop?)
Mark Wong wrote: On 9/25/07, Satoshi Nagayasu <[EMAIL PROTECTED]> wrote: Mark, Very interesting. I'm looking for such tool. Unfortunately, I can't compile it on my Solaris right now, but I hope it will be shipped with PostgreSQL distribution. I haven't tried it on Solaris but I'm not surprised. If I can get my hands on a Solaris system I can probably get it to work. :) The code for getting the process information is platform specific and I know I've broken it for all the platforms I haven't tried it on... Mark, I tested it on Solaris and I found two problems there. One is with configure. It does not correctly handled CPPFLAGS. Generated makefile ignore path to the include files. This line is wrong: # explicit dependency for the module appropriate to this machine m_sunos5.o: $(srcdir)/machine/m_sunos5.c $(COMPILE) -o $@ -c $(srcdir)/machine/m_sunos5.c Second issue is with new interface of get_process_info function. Currently it has 4 arguments but in m_sunos5.c is defined only with 3 arguments. Last issue is with -m64 switch. Solaris pg installation does not have 64bit libpg (will be soon) and linker is not able put everything together. Zdenek ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] top for postgresql (ptop?)
On 9/26/07, Zdenek Kotala <[EMAIL PROTECTED]> wrote: > Mark Wong wrote: > > On 9/25/07, Satoshi Nagayasu <[EMAIL PROTECTED]> wrote: > >> Mark, > >> > >> Very interesting. I'm looking for such tool. > >> > >> Unfortunately, I can't compile it on my Solaris right now, > >> but I hope it will be shipped with PostgreSQL distribution. > > > > I haven't tried it on Solaris but I'm not surprised. If I can get my > > hands on a Solaris system I can probably get it to work. :) The code > > for getting the process information is platform specific and I know > > I've broken it for all the platforms I haven't tried it on... > > Mark, > > I tested it on Solaris and I found two problems there. One is with > configure. It does not correctly handled CPPFLAGS. Generated makefile > ignore path to the include files. This line is wrong: > > # explicit dependency for the module appropriate to this machine > m_sunos5.o: $(srcdir)/machine/m_sunos5.c > $(COMPILE) -o $@ -c $(srcdir)/machine/m_sunos5.c > > Second issue is with new interface of get_process_info function. > Currently it has 4 arguments but in m_sunos5.c is defined only with 3 > arguments. > > Last issue is with -m64 switch. Solaris pg installation does not have > 64bit libpg (will be soon) and linker is not able put everything together. Hi Zdenek, I don't have a Solaris system to work on. Would you be able to send a patch? I have a mercurial repository, info here: http://ptop.projects.postgresql.org/ I think I've already caught the CPPFLAGS problem too. Regards, Mark ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Minor changes to Recovery related code
This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Simon Riggs wrote: > On Sat, 2007-03-31 at 00:51 +0200, Florian G. Pflug wrote: > > Simon Riggs wrote: > > > On Fri, 2007-03-30 at 16:34 -0400, Tom Lane wrote: > > >> "Simon Riggs" <[EMAIL PROTECTED]> writes: > > >>> 2. pg_stop_backup() should wait until all archive files are safely > > >>> archived before returning > > >> Not sure I agree with that one. If it fails, you can't tell whether the > > >> action is done and it failed while waiting for the archiver, or if you > > >> need to redo it. > > > > > > There's a slight delay between pg_stop_backup() completing and the > > > archiver doing its stuff. Currently if somebody does a -m fast straight > > > after the pg_stop_backup() the backup may be unusable. > > > > > > We need a way to plug that small hole. > > > > > > I suggest that pg_stop_backup() polls once per second until > > > pg_xlog/archive_status/LOG.ready disappears, in which case it ends > > > successfully. If it does this for more than 60 seconds it ends > > > successfully but produces a WARNING. > > > > I fear that ending sucessfully despite having not archived all wals > > will make this feature less worthwile. If a dba knows what he is > > doing, he can code a perfectly safe backup script using 8.2 too. > > He'll just have to check the current wal position after pg_stop_backup(), > > (There is a function for that, right?), and wait until the corresponding > > wal was archived. > > > > In realitly, however, I feare that most people will just create a script > > that does 'echo "select pg_stop_backup | psql"' or something similar. > > If they're a bit more carefull, they will enable ON_ERROR_STOP, and check > > the return value of pgsql. I believe that those are the people who would > > really benefit from a pg_stop_backup() that waits for archiving to complete. > > But they probably won't check for WARNINGs. > > > > Maybe doing it the other way round would be an option? > > pg_stop_backup() could wait for the archiver to complete forever, but > > spit out a warning every 60 seconds or so "WARNING: Still waiting > > for wal archiving of wal ??? to complete". If someone really wants > > a 60-second timeout, he can just use statement_timeout. > > I've just come up against this problem again, so I think it is a must > fix for this release. Other problems exist also, mentioned on separate > threads. > > We have a number of problems surrounding pg_stop_backup/shutdown: > > 1. pg_stop_backup() currently returns before the WAL file containing the > last change is correctly archived. That is a small hole, but one that is > exposed when people write test scripts that immediately shutdown the > database after issuing pg_stop_backup(). It doesn't make much sense to > shutdown immediately after a hot backup, but it should still work > sensibly. > > 2. We've also had problems caused by making the archiver wait until all > WAL files are archived. If there is a backlog for some reason and the > DBA issues a restart (i.e. stop and immediate restart) then making the > archiver loop while it tries (and possibly fails) to archive all files > would cause an outage. Avoiding this is why we do the current > get-out-fast approach. > There are some sub scenarios: > a) there is a backlog of WAL files, but no error has occurred on the > *last* file (we might have just fixed a problem). > b) there is a backlog of WAL files, but an error is causing a retry of > the last file. > > My proposal is for us to record somewhere other than the logs that a > failure to archive has occurred and is being retried. Failure to archive > will be recorded in the archive_status directory as an additional file > called archive_error, which will be deleted in the case of archive > success and created in the case of archive error. This maintains > archiver's lack of attachment to shared memory and general simplicity of > design. > > - pg_stop_backup() will wait until the WAL file that ends the backup is > safely archived, even if a failure to archive occurs. This is a change > to current behaviour, but since it implements the originally *expected* > behaviour IMHO it should be the default. > > - new function: pg_stop_backup_nowait() return immediately without > waiting for archive, the same as the current pg_stop_backup() > > - new function: pg_stop_backup_wait(int seconds) wait until either an > archival fails or the ending WAL file is archived, with a max wait as > specified. wait=0 means wait until archive errors are resolved. > > Alternatives? > > -- > Simon Riggs > EnterpriseDB http://www.enterprisedb.com > > > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq -- Bruce Momjian <[EMAIL PROT
[HACKERS] Re: [COMMITTERS] pgsql: Temporarily modify tsearch regression tests to suppress notice
I just talked to Teodor and we discussed this problem. My idea is to have a special marker in the synonym table, perhaps "*" to indicate the presence of _any_ stop word at that location. This will not produce any warnings because it is clearly intentional. The original warning for a literal stop word will remain but will happen only when the user users an actual unintentional use of a stop word. The synonym examples will need to be updated to use "*" as stop word markers. --- Tom Lane wrote: > Log Message: > --- > Temporarily modify tsearch regression tests to suppress notice that comes > out at erratic times, because it is creating a totally unacceptable level > of noise in our buildfarm results. This patch can be reverted when and if > the code is fixed to not issue notices during cache reload events. > > Modified Files: > -- > pgsql/src/backend/tsearch: > thesaurus_sample.ths (r1.1 -> r1.2) > > (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/tsearch/thesaurus_sample.ths?r1=1.1&r2=1.2) > pgsql/src/test/regress/expected: > tsdicts.out (r1.1 -> r1.2) > > (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/test/regress/expected/tsdicts.out?r1=1.1&r2=1.2) > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] plpgsql TABLE patch
This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Neil Conway wrote: > To review, Pavel Stehule submitted a proposal and patch to add support > for "table functions" a few months back: > > http://archives.postgresql.org/pgsql-hackers/2007-02/msg00318.php > http://archives.postgresql.org/pgsql-patches/2007-05/msg00054.php > > Pavel proposed two basically independent features: > > (1) RETURN TABLE syntax sugar for PL/PgSQL > > This allows you to return the result of evaluating a SELECT query as the > result of a SETOF pl/pgsql function. I don't like the RETURN TABLE > syntax, because TABLE (...) is defined as part of SQL (6.39 in SQL:2003, > as one of the variants of ). If we're going > to implement TABLE (...), the right place to do that is in the Postgres > backend proper (presumably as part of a larger effort to implement > multisets). Therefore I'd like to rename the PL/PgSQL syntax sugar to > RETURN QUERY (I'm open to other suggestions for the name). > > Another question is whether it is sensible to allow RETURN QUERY and > RETURN NEXT to be combined in a single function. That is, whether RETURN > QUERY should be more like RETURN (and return from the function > immediately), or more like RETURN NEXT (just append a result set to the > SRF's tuplestore and continue evaluating the function). I think making > it behave more like RETURN NEXT would be more flexible, but perhaps it > would be confusing for users to see a "RETURN QUERY" statement that does > not in fact return control to the caller of the function... (Is RETURN > NEXT QUERY too ugly a name?) > > (2) RETURNS TABLE (...) syntax sugar for CREATE FUNCTION > > This lets you write "CREATE FUNCTION ... RETURNS TABLE (x int, y int)" > as essentially syntax sugar for OUT parameters. The syntax is specified > by SQL:2003, so I think this feature is worth implementing. > > When Pavel proposed this, the sticking point is whether RETURNS TABLE > (...) is truly just syntax sugar for OUT parameters, or whether it > should behave differently with regard to variables with the same name in > the function body:[1] > > CREATE OR REPLACE FUNCTION foo(arg int) RETURNS TABLE (cust_id int) AS > $$ > BEGIN > RETURN QUERY (SELECT cust_id FROM tab WHERE some = arg); > END; $$ LANGUAGE plpgsql; > > would cause a name collision if RETURNS TABLE were treated as syntax > sugar for OUT parameters. Pavel's patch fixes this by introducing a new > proargmode for RETURNS TABLE parameters. Tom objected to this on the > grounds that it could break user code that examines pg_proc.proargmode, > but I'm inclined to think that it is worth the trouble to avoid what > could be a common source of confusion. > > Comments welcome; I'll submit revised patches for these features > shortly. > > -Neil > > [1] example stolen shamelessly from a prior mail from Pavel > > > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] little PITR annoyance
This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Simon Riggs wrote: > On Sun, 2007-06-10 at 20:48 +0200, [EMAIL PROTECTED] wrote: > > > > > > > My questions was: why don't we start the archiving *BEFORE* postmaster > > > > to > > > > make room. > > > > > > The archiver is executed from the postmaster, so thats not possible. > > > > > I'm aware of that, my point is maybe the archiver doesn't need postmaster > > to be fully operational (responding to db requests) to be started > > > We could investigate where best to put some code, but it wouldn't be > > > executed very frequently. > > I agree, OTOH, the more PITR is used on big busy db to more this may > > happend. > > > > > > Why not just execute the archive_command in a script, replacing > > > the .ready with .done files in archive_status directory when its > > > processed? > > > > > Sure, but if *I* can do it, why can't the system? > > > > What do you think, > > Just looked at the code. Does seem possible to start archiver earlier - > it has no hooks into anything else and doesn't need transactions. > > Starting archiver earlier would not be the only change required, since > recovery could be very short. That will take some thought on how to > resolve. > > I have other things pressing on me now, but I'll add this to my todo, > though I'll be relying on you to test it when I get round to it. > > -- > Simon Riggs > EnterpriseDB http://www.enterprisedb.com > > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [COMMITTERS] pgsql: Temporarily modify tsearch regression tests to suppress notice
Bruce Momjian <[EMAIL PROTECTED]> writes: > I just talked to Teodor and we discussed this problem. My idea is to > have a special marker in the synonym table, perhaps "*" to indicate the > presence of _any_ stop word at that location. This will not produce any > warnings because it is clearly intentional. That's not fixing the problem, unless your proposal includes never issuing any warnings at all, for anything. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [COMMITTERS] pgsql: Temporarily modify tsearch regression tests to suppress notice
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > I just talked to Teodor and we discussed this problem. My idea is to > > have a special marker in the synonym table, perhaps "*" to indicate the > > presence of _any_ stop word at that location. This will not produce any > > warnings because it is clearly intentional. > > That's not fixing the problem, unless your proposal includes never > issuing any warnings at all, for anything. No warning for "*" because it is intentional, but warning for actual stop words. -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
