Re: [HACKERS] strange bison, cannot remove reduce
Pavel Stehule wrote: > I am playing with methods. It's +/- function with first hidden arguments. > > example: sin(10) ~ (10).sin() is equivalent. > legal is substring('',1,3).upper() too etc > > I spent some time with bison (without success). I don't think you can actually resolve this in the parser. For example a.b(x) could be, call function b(x) in schema a, or call function b(a, x). You need to resolve this later, with catalog access, it appears. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 8.3devel slower than 8.2 under read-only load
On Nov 22, 2007 6:44 AM, Tom Lane <[EMAIL PROTECTED]> wrote: > Are you examining only "trivial" queries? I've been able to identify a > couple of new planner hotspots that could explain some slowdown if the > planning time is material compared to the execution time. If you're > seeing a slowdown on queries that run for awhile, that would be > something else ... Yes, I kept only queries with no join and a couple of where conditions. As I explained previously, I can reproduce the behavior with a single index scan on only one table (plan posted previously). If anyone is interested I can post the content of this table (there's nothing confidential in it so I should have the customer permission) and a couple of instructions to reproduce the test case. The case in which I used a few differents queries executes the following ones directly extracted from the application (all are index scans - and they use the exact same index on 8.2 and 8.3): select libvil from vilsitelang where codelang='FRA' and codevil='LYO' select TL.motsclesmetatags, TL.descriptifmeta, TL.motcleoverture_l, TL.motcleoverture_c, TL.baselinetheme from themelang TL where TL.codeth = 'ASS' and TL.codelang = 'FRA' SELECT libvilpubwoo, codelang, codepays, petiteville FROM vilsite WHERE codevil = 'LYO' select libvil from vilsitelang where codelang='FRA' and codevil='LYO' So as you can see, queries can't be simpler. -- Guillaume ---(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] backup_label and server start
Simon Riggs wrote: > On Wed, 2007-11-21 at 09:47 +, Peter Childs wrote: >> How about this, emit a warning on shutdown and fail to shutdown until >> the backup has finished. > > That would be reasonable for -m smart shutdown. > > We would then be treating the backup as a connection. > > ...but not for a fast shutdown. > > Any comments against? No, that would be ok with me. Anything that gets us out of the trap that you can shutdown a server without any warning and then cannot restart it without manual intervention. What about: refuse shutdown for "smart" if a backup is in progress, but shutdown with a loud warning for "fast". ... I still don't know what's wrong with removing backup_label upon a clean server shutdown ... Yours, Laurenz Albe ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] 8.3devel slower than 8.2 under read-only load
On Nov 22, 2007 2:17 AM, Tom Lane <[EMAIL PROTECTED]> wrote: > Another issue is that on read-only transactions there's an extra > gettimeofday() call caused by pgstat_report_tabstats, which could be a > problem on machines with slow gettimeofday(). However that shouldn't > happen if you've got track_counts turned off, so if you don't see any > difference with or without stats then it's not the problem for you. The box is a Core2 duo box so I don't think it's the case. track_counts on/off doesn't change anything to the results. -- Guillaume ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] run_build.pl ~ By Andrew Dunstan
Hi All, I was exploring through the BuildFarm specific perl script run_build.pl. I executed the perl script and it went on file by downloading the latest PostgreSQL source code that is 8.3Beta2, after successful completion of the script it creates the required set of logfiles in the "lastrun-logs" folder. Now Inside this script there are certain places where the "rmtree"command is used for removing the installation, if I comment these places it should not remove the current installation, but infact it is doing so, it is installing the latest version of PostgreSQL in the location "/HEAD/inst/bin" and after the execution of the script it is cleaning the folder "inst". Please tell me : 1) Is "rmtree" the command used for removing the installation or is there any other command for the same, please specify? 2) Is there any site or portal where I can get detailed description about the perl script run_build.pl? 3) If I rename the perl script "run_build.pl" to some other file like "test.pl" will it execute or will it give errors? For your information : I am running this script through the postgres user. Please let me know if there is any other details required. Thanks in advance Regards Cinu Kuriakose Chat on a cool, new interface. No download required. Go to http://in.messenger.yahoo.com/webmessengerpromo.php ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 8.3devel slower than 8.2 under read-only load
"Guillaume Smet" <[EMAIL PROTECTED]> writes: > I'm not saying my benchmark is perfectly relevant: I made it > excessively simple on purpose. I just see a general slow down which is > quite consistent accross all the tests I did (with pgbench or the > application) and I'd really like to know if it's just my case on this > particular box or something more general. Are you examining only "trivial" queries? I've been able to identify a couple of new planner hotspots that could explain some slowdown if the planning time is material compared to the execution time. If you're seeing a slowdown on queries that run for awhile, that would be something else ... 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] 8.3devel slower than 8.2 under read-only load
On Nov 21, 2007 7:22 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > FWIW, the test cases I was just comparing are entirely CPU-bound --- > vmstat says there are no disk reads happening at all. Now I only got a > 3% drop, so that may not be the same effect Guillaume is seeing. But > the whole thing is a bit upsetting seeing that we thought we'd reduced > the overhead for short read-only transactions ... A month or so ago I mentioned to Bruce that we were seeing this behavior (accidentally) but hadn't had time to focus on it or determine whether it was a tuning issue. Basically we're performing the same select-only pgbench test, but with a varying scale from 1 to 1000. In almost all cases, 8.2.5 is faster than 8.3 by about 2-5 percent. The script given to us by a customer was as follows: for scale in 1 2 5 10 20 30 40 50 75 100 200 400 800 1000; do echo "" echo "SCALE $scale" dropdb pgbench createdb pgbench pgbench -p 5432 -i -s $scale pgbench psql pgbench -c 'CHECKPOINT' pgbench -p 5432 -c 8 -t 2500 -S pgbench pgbench -p 5432 -c 8 -t 2500 -S pgbench pgbench -p 5432 -c 8 -t 2500 -S pgbench done -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] 8.3devel slower than 8.2 under read-only load
I wrote: > The weird thing is that after a couple of hours of poking at it with > oprofile and other sharp objects, I have no idea *why* it's slower. > oprofile shows just about the same relative percentages for all the > hot-spot functions in the backend. However, some comparisons with gprof show that the planner is calling the hot-spot functions more than it used to, which might be enough to account for a couple percent on trivial queries like the ones being issued by pg_bench ("SELECT abalance FROM accounts WHERE aid = :aid;"). After the holiday I'll look into refactoring to try to avoid the extra calls. Another issue is that on read-only transactions there's an extra gettimeofday() call caused by pgstat_report_tabstats, which could be a problem on machines with slow gettimeofday(). However that shouldn't happen if you've got track_counts turned off, so if you don't see any difference with or without stats then it's not the problem for you. 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] 8.3devel slower than 8.2 under read-only load
On Nov 22, 2007 12:49 AM, Greg Smith <[EMAIL PROTECTED]> wrote: > Hard drives deliver a higher transfer rate at their inner > portions, typically the start of the disk from the operating system's > perspective. It could have been a good point if it was seq scans of large tables. It's only index scans on small tables which return only a couple of rows. They all fit in cache without any problem. For the last test I exposed in a previous email, the table is just 5480 rows and here are the sizes of the table and the index used: cityvox=> select pg_size_pretty(pg_relation_size('vilsitelang')); pg_size_pretty 232 kB (1 row) cityvox=> select pg_size_pretty(pg_relation_size('pk_vilsitelang')); pg_size_pretty 120 kB (1 row) > Not saying this is responsible for your results, just that benchmarking is > hard and there may be somthing other than what you think responsible for a > difference of this size. Sure. That's why I wanted other people advice :). I'm not saying my benchmark is perfectly relevant: I made it excessively simple on purpose. I just see a general slow down which is quite consistent accross all the tests I did (with pgbench or the application) and I'd really like to know if it's just my case on this particular box or something more general. Let's call it a call to share benchmark results for 8.3 :). We're not that far from the release and I didn't see a lot of benchmarks results around. I just wanted to add that I know that there is a lot of other things which may be faster with 8.3. What bothers me is that I don't think the other improvements will help that much this database in particular and this is by far the most critical database we're hosting here. -- Guillaume ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] 8.3devel slower than 8.2 under read-only load
On Nov 22, 2007 12:59 AM, Tom Lane <[EMAIL PROTECTED]> wrote: > It's not that autovacuum is now > on by default --- turning it off made no particular difference. Tested that also a few hours ago. No difference. > It's not that stats collection is now on by default --- ditto. Same here. My 8.2 has stats collection enabled in the same way that 8.3 does. -- Guillaume ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 8.3devel slower than 8.2 under read-only load
Greg Smith <[EMAIL PROTECTED]> writes: > On Wed, 21 Nov 2007, Guillaume Smet wrote: >> *** 8.2 *** >> tps = 853.360277 (including connections establishing) >> >> *** 8.3 *** >> tps = 784.819087 (including connections establishing) > This is an 8% drop. I've seen a larger difference than that between two > identical installations of the same version when the database is many GB > large. Hard drives deliver a higher transfer rate at their inner > portions, typically the start of the disk from the operating system's > perspective. It's not unusual for the slow parts of the disk to be 30-40% > slower than the fast ones. FWIW, the test cases I was just comparing are entirely CPU-bound --- vmstat says there are no disk reads happening at all. Now I only got a 3% drop, so that may not be the same effect Guillaume is seeing. But the whole thing is a bit upsetting seeing that we thought we'd reduced the overhead for short read-only transactions ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 8.3devel slower than 8.2 under read-only load
"Guillaume Smet" <[EMAIL PROTECTED]> writes: > I took a couple of very simple read only queries executed in the pages > to create a pgbench script and I have the following results: Hmm ... I can reproduce a consistent difference of about three percent between 8.2 and HEAD. Using pgbench's built-in SELECT-only transaction after "pgbench -i -s 10 bench", I get HEAD: $ time pgbench -n -S -c 10 -t 10 bench transaction type: SELECT only scaling factor: 10 number of clients: 10 number of transactions per client: 10 number of transactions actually processed: 100/100 tps = 9399.185809 (including connections establishing) tps = 9402.305058 (excluding connections establishing) real1m46.402s user0m19.889s sys 0m23.497s 8.2: $ time pgbench -n -S -c 10 -t 10 bench82 transaction type: SELECT only scaling factor: 10 number of clients: 10 number of transactions per client: 10 number of transactions actually processed: 100/100 tps = 9729.892729 (including connections establishing) tps = 9732.769774 (excluding connections establishing) real1m42.785s user0m19.250s sys 0m23.646s Vanilla build (--enable-debug but not much else), C locale, SQL_ASCII encoding, dual Xeon/EMT on Fedora Core 6. Configuration parameters are all defaults in both cases, except I had fsync off, which shouldn't matter anyway in a read-only test. The weird thing is that after a couple of hours of poking at it with oprofile and other sharp objects, I have no idea *why* it's slower. oprofile shows just about the same relative percentages for all the hot-spot functions in the backend. strace shows that there's no particular increase in kernel calls (indeed, HEAD seems to use significantly fewer semops/selects, indicating that we had some success in reducing contention). It's not that autovacuum is now on by default --- turning it off made no particular difference. It's not that stats collection is now on by default --- ditto. Slowing down the walwriter and bgwriter doesn't help either. It's not pgbench itself --- I get about the same results if I use 8.2 pgbench with HEAD or vice versa. The best theory I can come up with is that all the new stuff added to the backend (the executable is about 12% larger than in 8.2) has resulted in some generalized slowdown just because the code is larger. But most of the added code isn't getting exercised by this test, so in theory the code bloat shouldn't be hurting us either. Weird. Given that it's only a couple percent I'm not gonna panic about it, but I would like to know where the time is going ... regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 8.3devel slower than 8.2 under read-only load
On Wed, 21 Nov 2007, Guillaume Smet wrote: *** 8.2 *** tps = 853.360277 (including connections establishing) *** 8.3 *** tps = 784.819087 (including connections establishing) This is an 8% drop. I've seen a larger difference than that between two identical installations of the same version when the database is many GB large. Hard drives deliver a higher transfer rate at their inner portions, typically the start of the disk from the operating system's perspective. It's not unusual for the slow parts of the disk to be 30-40% slower than the fast ones. I've been known to mkfs all the database paritions before each test run just to remove this bias, so that the data was on exactly the same portion of the drive each time. Not saying this is responsible for your results, just that benchmarking is hard and there may be somthing other than what you think responsible for a difference of this size. I'd suggest running "select count(*) from x" on a couple of the big tables as one way to get a feel for whether the underlying disk is delivering at the same speed in both installations. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 8.3devel slower than 8.2 under read-only load
On Nov 22, 2007 12:07 AM, Gregory Williamson <[EMAIL PROTECTED]> wrote: > I've been running some fairly heavy read-only tests (5 minutes duration) > against 8.3beta2 and 8.2.5 and 8.1.10 and are getting slightly faster > numbers for 8.2.5 over 8.1 and 8.3beta2 looks consistently faster by a few > percent. This is heavily oriented to postGIS queries so your mileage may > vary. But so far I haven't seen any red flags or show stoppers from my > (limited) perspective. There are some changes to the config files but I > don't have details at hand. Thanks for your input. That's what I expected and that's why I'm a bit surprised... -- Guillaume ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 8.3devel slower than 8.2 under read-only load
On Nov 21, 2007 10:09 PM, Simon Riggs <[EMAIL PROTECTED]> wrote: > Please do tests of at least 2 minutes duration. A 1.25 second test isn't > enough. I already run far longer tests. It's not a matter of running a test for long, it's just that each unique query is a little bit slower. Moreover it's not a pgbench artifact, I have the same results inside the real application (it's a PHP app). > Please confirm you have VACUUM ANALYZED each db beforehand. Yes of course. The dump was just loaded in both databases. Both databases are identical (the 8.3 db is smaller in size on the disk as expected). They are both analyzed. FYI, I also have the very same database running on a 8.1.x branch (just loaded and analyzed) and the results are more like the 8.2 ones than like the 8.3 ones. > Have you checked that the EXPLAIN ANALYZEs are essentially identical > also? I did the test before. Every plan of every query involved in the test is identical. I removed from the test the one where a different index was chosen (8.2 chooses a larger index and 8.3 chooses the good one - Tom fixed something about that not so long ago and it works fine for us too). > Is the data identical on both systems? Freshly loaded from a dump. > How do the postgresql.conf files differ? They don't differ at all, except for the new parameters introduced in 8.3 (I let them the default). > Also, do a run with SELECT 1 FROM table where col = constant; so we can > assess differences in path without cache or data being relevant. I don't think the cache is relevant as they are all index scans and queries don't return a lot of rows. The indexes fit in RAM and I run each pgbench test several times. And to answer a question Joshua asked on IRC, pgbench is the same in both tests. I use the system wide one (8.1.9 from the RH package). To be sure, here are more information: ** 8.2 ** cityvox=# show shared_buffers; shared_buffers 128MB (1 row) cityvox=# show work_mem; work_mem -- 32MB (1 row) cityvox=# show lc_collate; lc_collate - fr_FR.UTF-8 (1 row) cityvox=# select version(); version PostgreSQL 8.2.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.1 20070105 (Red Hat 4.1.1-52) (1 row) ** 8.3 ** cityvox=# ANALYZE; ANALYZE cityvox=# show shared_buffers; shared_buffers 128MB (1 row) cityvox=# show work_mem; work_mem -- 32MB (1 row) cityvox=# show lc_collate; lc_collate - fr_FR.UTF-8 (1 row) cityvox=# select version(); version --- PostgreSQL 8.3beta2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.1 20070105 (Red Hat 4.1.1-52) (1 row) Then I run the test longer (I run it with 1000 transactions before to have the data in cache): ** 8.2 ** [ip-dyn151 postgresql]# pgbench -h localhost -p 5482 -U cityvox -f bench.cityvox.simple.sql -t 10 -c 1 cityvox starting vacuum...end. transaction type: Custom query scaling factor: 1 number of clients: 1 number of transactions per client: 10 number of transactions actually processed: 10/10 tps = 853.100511 (including connections establishing) tps = 853.124776 (excluding connections establishing) ** 8.3 ** [ip-dyn151 postgresql]# pgbench -h localhost -p 5483 -U cityvox -f bench.cityvox.simple.sql -t 10 -c 1 cityvox starting vacuum...end. transaction type: Custom query scaling factor: 1 number of clients: 1 number of transactions per client: 10 number of transactions actually processed: 10/10 tps = 791.244011 (including connections establishing) tps = 791.268316 (excluding connections establishing) Then let's simplify the test a bit with only one query: [EMAIL PROTECTED] postgresql]# cat bench.cityvox.really.simple.sql select libvil from vilsitelang where codelang='FRA' and codevil='LYO' ** 8.2 ** cityvox=> EXPLAIN ANALYZE select libvil from vilsitelang where codelang='FRA' and codevil='LYO'; QUERY PLAN - Index Scan using pk_vilsitelang on vilsitelang (cost=0.00..4.27 rows=1 width=12) (actual time=0.028..0.031 rows=1 loops=1) Index Cond: (((codevil)::text = 'LYO'::text) AND ((codelang)::text = 'FRA'::text)) Total runtime: 0.071 ms (3 rows) [ip-dyn151 postgresql]# pgbench -h localhost -p 5482 -U cityvox -f bench.cityvox.really.simple.sql -t 10 -c 1 cityvox starting vacuum...end. transaction type: Custom query scaling factor: 1 number of clients: 1 number of transactions per client: 10 number of transactions actually processed: 10/10 tps = 3468.220041 (including connections establishing) tps = 3
Re: [HACKERS] Postgres 8.3 archive_command
On Wed, 21 Nov 2007, Tom Lane wrote: Whether 16MB is still a reasonable default segment size is worth questioning, though I don't think that increasing it is an open-and-shut proposition. I don't think it's a reasonable change to make right now. I think 16MB is already too big for some people, reasonable for most, and only too small for a tiny portion of users that have fairly powerful systems. Incorporating what you said, there are two downsides to a bigger segment that immediately come to mind: -More garbage in unfilled segments means higher archiving overhead in some configurations. There are already people annoyed enough about this problem to be coding around it (I'm thinking of Kevin Grittner's pg_clearxlogtail). -The way new WAL segments get created can block clients while they wait for that write. As the segments gets larger this problem becomes worse, and there are many situations where reducing worse-case latency is far more important than throughput. There's already been talk of improving the latter by having a background process create the segments, but that doesn't make the problem go away altogether; it just makes it less likely to happen. I'd at least like to see that change and an official log tail cleaning mechanism both available before considering a change to the default WAL size. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] 8.3devel slower than 8.2 under read-only load
FWIW, > > Please do tests of at least 2 minutes duration. A 1.25 second test isn't > enough. Please confirm you have VACUUM ANALYZED each db beforehand. > > Have you checked that the EXPLAIN ANALYZEs are essentially identical > also? Is the data identical on both systems? > I've been running some fairly heavy read-only tests (5 minutes duration) against 8.3beta2 and 8.2.5 and 8.1.10 and are getting slightly faster numbers for 8.2.5 over 8.1 and 8.3beta2 looks consistently faster by a few percent. This is heavily oriented to postGIS queries so your mileage may vary. But so far I haven't seen any red flags or show stoppers from my (limited) perspective. There are some changes to the config files but I don't have details at hand. Initial tests are always faster; we usually throw them out and run for real numbers starting with 3rd tests to make sure we don't jump at cache issues. For the most part we only care about performance with as much of the database in cache as we can so those initial tests aren;t of much use. (Sorry for the poor posting -- challenged mail client) HTH, Greg Williamson Senior DBA GlobeXplorer LLC, a DigitalGlobe company Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. (My corporate masters made me say this.)
Re: [HACKERS] plperl failure on OS X 10.5(.1)
On 21 Nov, 2007, at 11:38 , Tom Lane wrote: Brandon Maust <[EMAIL PROTECTED]> writes: yes, it is sucking it in (via handy.h), at least in 10.5: #if 1 /* always on Mac OS X */ # include # ifndef HAS_BOOL #define HAS_BOOL 1 # endif #endif Nasty. I'm still surprised that there's no redefinition warning for "false" and "true", but that's not too important, since the cast to (bool) isn't really critical. I guess what we need is as attached; would you try it and see? yep, fixes it for me with 8.2.5 and 8.3b3. everything works even allowing that -arch warning, but it's not the clean compiling experience I'm used to from postgresql... Thanks, Tom. -- Brandon ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] 8.3devel slower than 8.2 under read-only load
On Wed, 2007-11-21 at 20:04 +0100, Guillaume Smet wrote: > number of clients: 1 > number of transactions per client: 1000 > number of transactions actually processed: 1000/1000 > tps = 784.819087 (including connections establishing) > tps = 786.884214 (excluding connections establishing) > > All the queries are index scans (SELECT a couple of fields FROM a > table WHERE one or two conditions). I checked the plans on both 8.2 > and 8.3 and they are identical. I made several runs and numbers are > consistent. Please do tests of at least 2 minutes duration. A 1.25 second test isn't enough. Please confirm you have VACUUM ANALYZED each db beforehand. Have you checked that the EXPLAIN ANALYZEs are essentially identical also? Is the data identical on both systems? How do the postgresql.conf files differ? Please find out any differences you can, so we can rule things out. Also, do a run with SELECT 1 FROM table where col = constant; so we can assess differences in path without cache or data being relevant. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] strange bison, cannot remove reduce
hello I am playing with methods. It's +/- function with first hidden arguments. example: sin(10) ~ (10).sin() is equivalent. legal is substring('',1,3).upper() too etc I spent some time with bison (without success). indirection_el: '.' attr_name { $$ = (Node *) makeString($2); } | '.' attr_name '(' ')' { $$ = (Node *) makeString($2); } | '.' attr_name '(' expr_list ')' { $$ = (Node *) makeString($2); } | '.' '*' { $$ = (Node *) makeString("*"); } this is correct but doesn't work postgres=# select (10).aaa...procedure(10); ERROR: syntax error at or near "(" LINE 1: select (10).aaa...procedure(10); ^ postgres=# select (10).aaa...procedure(); ERROR: syntax error at or near "(" LINE 1: select (10).aaa...procedure(); correct is indirection_el: '.' attr_name { $$ = (Node *) makeString($2); } | '.' type_function_name '(' ')' { $$ = (Node *) makeString($2); } | '.' type_function_name '(' expr_list ')' { $$ = (Node *) makeString($2); } | '.' '*' { $$ = (Node *) makeString("*"); } It works postgres=# select (10).aaa(10).ajjaja(10).qqq(); ERROR: column notation .aaa applied to type integer, which is not a composite type but there are bison gram.y gram.y: conflicts: 3 reduce/reduce state 1160 1436 type_function_name: IDENT . 1439 ColLabel: IDENT . '(' reduce using rule 1436 (type_function_name) '(' [reduce using rule 1439 (ColLabel)] $default reduce using rule 1439 (ColLabel) state 1165 1437 type_function_name: unreserved_keyword . 1440 ColLabel: unreserved_keyword . '(' reduce using rule 1437 (type_function_name) '(' [reduce using rule 1440 (ColLabel)] $default reduce using rule 1440 (ColLabel) state 1167 1438 type_function_name: type_func_name_keyword . 1442 ColLabel: type_func_name_keyword . '(' reduce using rule 1438 (type_function_name) '(' [reduce using rule 1442 (ColLabel)] $default reduce using rule 1442 (ColLabel) Any ideas? Regards Pavel ---(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] Re: pgsql: New versions of mingw have gettimeofday(), so add an autoconf
Magnus Hagander <[EMAIL PROTECTED]> writes: > On Mon, 2007-11-19 at 02:50 -0700, Kris Jurka wrote: >> Can we backport this fix? I'm trying to setup a new windows build >> environment and this is currently halting my progress for back branches. > Technically, it's fairly easy. And given that it's been working for a > couple of betas of 8.3, it seems it should be safe. OTOH, the official > build for 8.3 doesn't use it, so binary testers haven't seen it. But any > issues should've been build issues rather than runtime ones, I think. > So yeah, it would be reasonably easy to do, and probably a good idea. > Anybody think we shouldn't? Given that we're abandoning support for 8.0 and 8.1 on Windows, I'd say +1 for fixing 8.2, but not for any older branches. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] plperl failure on OS X 10.5(.1)
Brandon Maust <[EMAIL PROTECTED]> writes: > yes, it is sucking it in (via handy.h), at least in 10.5: > #if 1 /* always on Mac OS X */ > # include > # ifndef HAS_BOOL > #define HAS_BOOL 1 > # endif > #endif Nasty. I'm still surprised that there's no redefinition warning for "false" and "true", but that's not too important, since the cast to (bool) isn't really critical. I guess what we need is as attached; would you try it and see? How far back should we patch this? Is anyone likely to care about pre-8.2 plperl on Leopard? Another thought here is that c.h tries to cater to the possibility of bool being #define'd by system headers, but if that did actually happen anywhere then this patch would fail. We'd have pretty serious problems anyway if bool were not char-size, since pg_type.h hardwires its size as 1 byte. So I'm a bit tempted to remove the "#ifndef bool" from c.h, or else make it do "#undef bool". Comments? regards, tom lane *** src/pl/plperl/plperl.h.orig Fri Jan 5 17:20:01 2007 --- src/pl/plperl/plperl.h Wed Nov 21 14:31:54 2007 *** *** 38,43 --- 38,48 #define pTHX void #endif + /* perl may have a different width of "bool", don't buy it */ + #ifdef bool + #undef bool + #endif + /* routines from spi_internal.c */ int spi_DEBUG(void); int spi_LOG(void); ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] plperl failure on OS X 10.5(.1)
I wrote: > Apparently 5.8.8 > is sucking stdbool.h into the compile where 5.8.6 did not. Can you > track down just what the inclusion path is? I pulled down the perl 5.8.8 sources and cannot find a reference to stdbool.h anywhere. What I do find is that "handy.h" defines what Perl thinks bool is: #ifndef HAS_BOOL # if defined(UTS) || defined(VMS) # define bool int # else # define bool char # endif # define HAS_BOOL 1 #endif On OSX 10.4 this file is installed in /System/Library/Perl/5.8.6/darwin-thread-multi-2level/CORE/handy.h Would you look at what 10.5 has? I suspect that Apple has modified their version to force bool to be int as of 10.5. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] plperl failure on OS X 10.5(.1)
On 21 Nov, 2007, at 10:50 , Tom Lane wrote: Brandon Maust <[EMAIL PROTECTED]> writes: On 21 Nov, 2007, at 08:39 , Tom Lane wrote: Seems the question is not so much about OS X as it is about what perl you're using ... it's 5.8.8, as provided by apple (same for gcc, etc): perl on OS X does look to be constitutively defining a 'bool' as _Bool via gcc's stdbool.h, so perhaps this is more of a compiler issue? No, because I see the identical content in stdbool.h on OS X 10.4 (perl 5.8.6) and it is not causing a problem here. Apparently 5.8.8 is sucking stdbool.h into the compile where 5.8.6 did not. Can you track down just what the inclusion path is? yes, it is sucking it in (via handy.h), at least in 10.5: /* XXX Configure ought to have a test for a boolean type, if I can just figure out all the headers such a test needs. Andy Dougherty August 1996 */ /* bool is built-in for g++-2.6.3 and later, which might be used for extensions. <_G_config.h> defines _G_HAVE_BOOL, but we can't be sure _G_config.h will be included before this file. _G_config.h also defines _G_HAVE_BOOL for both gcc and g++, but only g++ actually has bool. Hence, _G_HAVE_BOOL is pretty useless for us. g++ can be identified by __GNUG__. Andy Dougherty February 2000 */ #if 1 /* always on Mac OS X */ # include # ifndef HAS_BOOL #define HAS_BOOL 1 # endif #endif I'm tempted to fix this with #ifdef bool #undef bool #endif in plperl.h after pulling in the Perl headers. However, it's not clear to me why you aren't seeing warnings about "false" and "true" getting redefined, if stdbool.h is really getting included. For reference, the interesting part of stdbool.h on 10.4 looks like #define false 0 #define true1 #define bool_Bool #if __STDC_VERSION__ < 199901L && __GNUC__ < 3 typedef int _Bool; #endif Since this is gcc 3, I suppose that the typedef isn't being used here but must get supplied internally by the compiler... stdbool.h looks the same in 10.5. -- Brandon ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] 8.3devel slower than 8.2 under read-only load
Hi -hackers, I'm currently testing 8.3devel on the database of one of our customers (4 GB database used by a website - mostly read only activity). My main concern was to discover if there is any query choosing a bad plan in 8.3 for one reason or another. While I didn't find anything far slower than before yet, the time needed to generate pages with 8.3 is consistently a little higher than with 8.1 or 8.2. I have a debug interface giving the execution time of each query and they are all a bit slower with 8.3. When you have a lot of queries on a page, it becomes noticeable. I took a couple of very simple read only queries executed in the pages to create a pgbench script and I have the following results: *** 8.2 *** [ip-dyn151 postgresql]# pgbench -h localhost -p 5482 -U cityvox -f bench.cityvox.simple.sql -t 1000 cityvox starting vacuum...end. transaction type: Custom query scaling factor: 1 number of clients: 1 number of transactions per client: 1000 number of transactions actually processed: 1000/1000 tps = 853.360277 (including connections establishing) tps = 855.792905 (excluding connections establishing) *** 8.3 *** [ip-dyn151 postgresql]# pgbench -h localhost -p 5483 -U cityvox -f bench.cityvox.simple.sql -t 1000 cityvox starting vacuum...end. transaction type: Custom query scaling factor: 1 number of clients: 1 number of transactions per client: 1000 number of transactions actually processed: 1000/1000 tps = 784.819087 (including connections establishing) tps = 786.884214 (excluding connections establishing) All the queries are index scans (SELECT a couple of fields FROM a table WHERE one or two conditions). I checked the plans on both 8.2 and 8.3 and they are identical. I made several runs and numbers are consistent. I used the default ./configure options, the configuration is identical for both versions, locale is fr_FR.UTF8 and it's a Unicode database. Both are compiled with the same compiler (it's a CentOS 5 box). Is this something expected? While I'm not so worried by these figures for our other databases, this database in particular is highly loaded with a *lot* of read only queries and I'm not sure we can afford this sort of performance drop. I can provide any additional information needed or run further tests without any problem so feel free to ask. Thanks. -- Guillaume ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] VACUUM/ANALYZE counting of in-doubt tuples
>>> On Wed, Nov 21, 2007 at 12:32 PM, in message <[EMAIL PROTECTED]>, Tom Lane <[EMAIL PROTECTED]> wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: >> Has this issue been a real problem? If so, probably we should consider >> adjusting ANALYZE for 8.3 per your proposal. > > I'm not sure. Upthread, two or three people said they thought they'd > seen autovac launching vacuums against tables during bulk inserts. > However, that could only happen if there were already a reason to launch > an auto-analyze (which could misreport dead tuples and thus trigger a > subsequent auto-vacuum), and in typical bulk load situations I don't see > why that would be very likely to happen. We had been in the habit of throwing a commit into our bulk loads periodically (say, every 10,000 or 100,000 rows. This was because our prior database product needed to keep the entire transaction image in a fixed-size transaction log until commit; if we didn't commit now and then, the whole thing locked up and died. I'm not sure I've seen the behavior since we realized it was just an old habit and went to a single transaction per table. > I'm fine with leaving the whole issue for 8.4. Perhaps a comment somewhere in the documentation regarding the above should go into releases where this technique can be costly? Suggesting a single transaction or suspension of autovacuum? -Kevin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] plperl failure on OS X 10.5(.1)
Brandon Maust <[EMAIL PROTECTED]> writes: > On 21 Nov, 2007, at 08:39 , Tom Lane wrote: >> Seems the question is not so much about OS X as it is about what >> perl you're using ... > it's 5.8.8, as provided by apple (same for gcc, etc): > perl on OS X does look to be constitutively defining a 'bool' as _Bool > via gcc's stdbool.h, so perhaps this is more of a compiler issue? No, because I see the identical content in stdbool.h on OS X 10.4 (perl 5.8.6) and it is not causing a problem here. Apparently 5.8.8 is sucking stdbool.h into the compile where 5.8.6 did not. Can you track down just what the inclusion path is? I'm tempted to fix this with #ifdef bool #undef bool #endif in plperl.h after pulling in the Perl headers. However, it's not clear to me why you aren't seeing warnings about "false" and "true" getting redefined, if stdbool.h is really getting included. For reference, the interesting part of stdbool.h on 10.4 looks like #define false 0 #define true1 #define bool_Bool #if __STDC_VERSION__ < 199901L && __GNUC__ < 3 typedef int _Bool; #endif Since this is gcc 3, I suppose that the typedef isn't being used here but must get supplied internally by the compiler... 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] plperl failure on OS X 10.5(.1)
On 21 Nov, 2007, at 08:39 , Tom Lane wrote: Alvaro Herrera <[EMAIL PROTECTED]> writes: Nothing fatal? Huh, you have a curious idea about warnings. This makes me think you have the wrong headers or something -- the argument mentioned in all these cases is bool, so maybe there is an ABI incompatibility somewhere. Yeah, and it's hardly difficult to see how that might lead to the reported "null prosrc" error, either. boolisnull; ... prosrcdatum = SysCacheGetAttr(PROCOID, procTup, Anum_pg_proc_prosrc, &isnull); if (isnull) elog(ERROR, "null prosrc"); Perhaps a Perl header is redefining "bool" on your platform? Seems the question is not so much about OS X as it is about what perl you're using ... it's 5.8.8, as provided by apple (same for gcc, etc): $ perl -V Summary of my perl5 (revision 5 version 8 subversion 8) configuration: Platform: osname=darwin, osvers=9.0, archname=darwin-thread-multi-2level uname='darwin omen.apple.com 9.0 darwin kernel version 9.0.0b5: mon sep 10 17:17:11 pdt 2007; root:xnu-1166.6~1release_ppc power macintosh ' config_args='-ds -e -Dprefix=/usr -Dccflags=-g -pipe -Dldflags=- Dman3ext=3pm -Duseithreads -Duseshrplib' hint=recommended, useposix=true, d_sigaction=define usethreads=define use5005threads=undef useithreads=define usemultiplicity=define useperlio=define d_sfio=undef uselargefiles=define usesocks=undef use64bitint=define use64bitall=define uselongdouble=undef usemymalloc=n, bincompat5005=undef Compiler: cc='cc', ccflags ='-arch i386 -arch ppc -g -pipe -fno-common - DPERL_DARWIN -no-cpp-precomp -fno-strict-aliasing -Wdeclaration-after- statement -I/usr/local/include', optimize='-O3', cppflags='-no-cpp-precomp -g -pipe -fno-common -DPERL_DARWIN -no- cpp-precomp -fno-strict-aliasing -Wdeclaration-after-statement -I/usr/ local/include' ccversion='', gccversion='4.0.1 (Apple Inc. build 5465)', gccosandvers='' intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=4321 d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=16 ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='off_t', lseeksize=8 alignbytes=8, prototype=define Linker and Libraries: ld='cc -mmacosx-version-min=10.5', ldflags ='-arch i386 -arch ppc -L/usr/local/lib' libpth=/usr/local/lib /usr/lib libs=-ldbm -ldl -lm -lutil -lc perllibs=-ldl -lm -lutil -lc libc=/usr/lib/libc.dylib, so=dylib, useshrplib=true, libperl=libperl.dylib gnulibc_version='' Dynamic Linking: dlsrc=dl_dlopen.xs, dlext=bundle, d_dlsymun=undef, ccdlflags=' ' cccdlflags=' ', lddlflags='-arch i386 -arch ppc -bundle - undefined dynamic_lookup -L/usr/local/lib' Characteristics of this binary (from libperl): Compile-time options: MULTIPLICITY PERL_IMPLICIT_CONTEXT PERL_MALLOC_WRAP USE_ITHREADS USE_LARGE_FILES USE_PERLIO USE_REENTRANT_API Built under darwin Compiled at Sep 23 2007 19:07:53 the worrisome "not required architecture" warning drops out from the link if I exclude the '-arch i386', so at least that one's probably ignorable. Doing so makes a binary different libplperl, but doesn't seem to change the end result. perl on OS X does look to be constitutively defining a 'bool' as _Bool via gcc's stdbool.h, so perhaps this is more of a compiler issue? OS X 10.5.1 uses gcc 4.0.1. -- Brandon ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Re: pgsql: New versions of mingw have gettimeofday(), so add an autoconf
On Mon, 2007-11-19 at 02:50 -0700, Kris Jurka wrote: > Magnus Hagander wrote: > > Log Message: > > --- > > New versions of mingw have gettimeofday(), so add an autoconf test > > for this. > > > > Can we backport this fix? I'm trying to setup a new windows build > environment and this is currently halting my progress for back branches. Technically, it's fairly easy. And given that it's been working for a couple of betas of 8.3, it seems it should be safe. OTOH, the official build for 8.3 doesn't use it, so binary testers haven't seen it. But any issues should've been build issues rather than runtime ones, I think. So yeah, it would be reasonably easy to do, and probably a good idea. Anybody think we shouldn't? //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
Re: [HACKERS] VACUUM/ANALYZE counting of in-doubt tuples
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Should we attempt to adjust VACUUM's accounting as well, or leave it >> for 8.4? For that matter, should adjusting ANALYZE be left for 8.4? >> Thoughts? > Has this issue been a real problem? If so, probably we should consider > adjusting ANALYZE for 8.3 per your proposal. I'm not sure. Upthread, two or three people said they thought they'd seen autovac launching vacuums against tables during bulk inserts. However, that could only happen if there were already a reason to launch an auto-analyze (which could misreport dead tuples and thus trigger a subsequent auto-vacuum), and in typical bulk load situations I don't see why that would be very likely to happen. I'm fine with leaving the whole issue for 8.4. 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] Postgres 8.3 archive_command
>>> On Wed, Nov 21, 2007 at 11:24 AM, in message <[EMAIL PROTECTED]>, Tom Lane <[EMAIL PROTECTED]> wrote: > "Kevin Grittner" <[EMAIL PROTECTED]> writes: >> In poking around the logs just now, I noticed one message I'd like >> to squelch. Run against Milwaukee County's recent log files: > >> grep -c 'PRIMARY KEY will create implicit index' > > Set log_min_messages higher than NOTICE. Given the current usage of > NOTICE --- basically they're *all* newbie-annoyance items --- Done statewide. Thanks for the suggestion. > I kinda wonder why WARNING isn't the default setting anyway. Based on what you said above, I wonder, too. Maybe that's something to consider changing? -Kevin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Postgres 8.3 archive_command
On Wed, 2007-11-21 at 12:24 -0500, Tom Lane wrote: > "Kevin Grittner" <[EMAIL PROTECTED]> writes: > > Tom Lane <[EMAIL PROTECTED]> wrote: > >> Just to clarify: I don't object to lowering "successfully archived" > >> messages to DEBUG1, if the field consensus is that it's too chatty. > >> What I didn't like was the idea of logging some events but not other > >> identical events. > > > Agreed on the intermittent logging. I don't feel it's too chatty, > > but on the other hand, I could always change the logging level on > > the fly if I was investigating a problem, so it wouldn't be much of > > an inconvenience to switch it if it bugs others. > > Also, you can always tweak your archive_command script to do some > logging of its own, so it's always possible to make the thing more > noisy if you need to. Less noisy, though, is hard without changing > the server code. OK, both of those thoughts are good, so happy now with DEBUG1 for success messages: "archived transaction log file..." Enclosed patch to set archived message to DEBUG1. I've got a few other comments for PITR docs, so I'll mention this also in forthcoming patch. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com Index: src/backend/postmaster/pgarch.c === RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/backend/postmaster/pgarch.c,v retrieving revision 1.32 diff -c -r1.32 pgarch.c *** src/backend/postmaster/pgarch.c 15 Nov 2007 21:14:37 - 1.32 --- src/backend/postmaster/pgarch.c 21 Nov 2007 17:37:52 - *** *** 492,498 return false; } ! ereport(LOG, (errmsg("archived transaction log file \"%s\"", xlog))); return true; --- 492,498 return false; } ! ereport(DEBUG1, (errmsg("archived transaction log file \"%s\"", xlog))); return true; ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Postgres 8.3 archive_command
Simon Riggs <[EMAIL PROTECTED]> writes: > On Wed, 2007-11-21 at 18:16 +0100, Zeugswetter Andreas ADI SD wrote: >> Maybe it is time for making WAL segment size >> changeable in the conf with a clean shutdown. > I think its too late in the release cycle to fully consider all the > implications of that. 16MB is hardcoded in lots of places. Starting with the WAL file names. You couldn't even find the last checkpoint record if this were changed between shutdown and restart; you'd very likely go looking under the wrong filename, and even if you hit on an existent filename you'd be looking at the wrong offset within it. I don't think that should even be a TODO item --- it seems far more likely to provide a foot-gun than useful capability. Whether 16MB is still a reasonable default segment size is worth questioning, though I don't think that increasing it is an open-and-shut proposition. Larger segments mean more overhead in configurations that force frequent segment switches, for instance. 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] random dataset generator for SKYLINE operator
Hannes Eder worte: We wrote a little contrib module, which we'd like to share. It can be used to generate random datasets as they have been used in [Borzsonyi2001] and related work. [snip] We release a command line version of this module. See: http://randdataset.projects.postgresql.org/ the source is available as tarball at: http://pgfoundry.org/frs/?group_id=1000305 or in the SCM. -Hannes ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Postgres 8.3 archive_command
On Wed, 2007-11-21 at 18:16 +0100, Zeugswetter Andreas ADI SD wrote: > > Perhaps we should move the successful archived message to DEBUG1 now, > > except for the first message after the archiver starts or when the > > archive_command changes, plus one message every 255 segments? > > That would reduce the log volume in the normal case without > endangering > > our ability to see what is happening. > > Wouldn't it be more useful to increase the WAL segment size on such > installations > that switch WAL files so frequently that it is a problem for the log ? > > This currently needs a recompile. I wondered for some time now whether > 16 Mb isn't > too low for current hw. Maybe it is time for making WAL segment size > changeable > in the conf with a clean shutdown. I think its too late in the release cycle to fully consider all the implications of that. 16MB is hardcoded in lots of places. The performance advantages of that have been mostly removed in 8.3, you should note. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(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] Postgres 8.3 archive_command
"Kevin Grittner" <[EMAIL PROTECTED]> writes: > Tom Lane <[EMAIL PROTECTED]> wrote: >> Just to clarify: I don't object to lowering "successfully archived" >> messages to DEBUG1, if the field consensus is that it's too chatty. >> What I didn't like was the idea of logging some events but not other >> identical events. > Agreed on the intermittent logging. I don't feel it's too chatty, > but on the other hand, I could always change the logging level on > the fly if I was investigating a problem, so it wouldn't be much of > an inconvenience to switch it if it bugs others. Also, you can always tweak your archive_command script to do some logging of its own, so it's always possible to make the thing more noisy if you need to. Less noisy, though, is hard without changing the server code. > In poking around the logs just now, I noticed one message I'd like > to squelch. Run against Milwaukee County's recent log files: > grep -c 'PRIMARY KEY will create implicit index' Set log_min_messages higher than NOTICE. Given the current usage of NOTICE --- basically they're *all* newbie-annoyance items --- I kinda wonder why WARNING isn't the default setting anyway. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Postgres 8.3 archive_command
> Perhaps we should move the successful archived message to DEBUG1 now, > except for the first message after the archiver starts or when the > archive_command changes, plus one message every 255 segments? > That would reduce the log volume in the normal case without endangering > our ability to see what is happening. Wouldn't it be more useful to increase the WAL segment size on such installations that switch WAL files so frequently that it is a problem for the log ? This currently needs a recompile. I wondered for some time now whether 16 Mb isn't too low for current hw. Maybe it is time for making WAL segment size changeable in the conf with a clean shutdown. Andreas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Postgres 8.3 archive_command
>>> On Wed, Nov 21, 2007 at 10:57 AM, in message <[EMAIL PROTECTED]>, Tom Lane <[EMAIL PROTECTED]> wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: >> On Wed, 2007-11-21 at 11:27 -0500, Tom Lane wrote: >>> That would confuse people terribly, and it *would* endanger our ability >>> to see what was happening, 254 times out of 255. > >> That's my feeling too, just wanted to check it still made sense for >> y'all. > > Just to clarify: I don't object to lowering "successfully archived" > messages to DEBUG1, if the field consensus is that it's too chatty. > What I didn't like was the idea of logging some events but not other > identical events. Agreed on the intermittent logging. I don't feel it's too chatty, but on the other hand, I could always change the logging level on the fly if I was investigating a problem, so it wouldn't be much of an inconvenience to switch it if it bugs others. In poking around the logs just now, I noticed one message I'd like to squelch. Run against Milwaukee County's recent log files: grep -c 'PRIMARY KEY will create implicit index' I get this for the last ten full days: /var/pgsql/data/cc/pg_log/postgresql-2007-11-11_00.log:210 /var/pgsql/data/cc/pg_log/postgresql-2007-11-12_00.log:14138 /var/pgsql/data/cc/pg_log/postgresql-2007-11-13_00.log:13250 /var/pgsql/data/cc/pg_log/postgresql-2007-11-14_00.log:14912 /var/pgsql/data/cc/pg_log/postgresql-2007-11-15_00.log:11635 /var/pgsql/data/cc/pg_log/postgresql-2007-11-16_00.log:10774 /var/pgsql/data/cc/pg_log/postgresql-2007-11-17_00.log:183 /var/pgsql/data/cc/pg_log/postgresql-2007-11-18_00.log:120 /var/pgsql/data/cc/pg_log/postgresql-2007-11-19_00.log:12667 /var/pgsql/data/cc/pg_log/postgresql-2007-11-20_00.log:13992 It's actually pretty hard to find the archive logging amidst all that. The log is next to useless without a grep -v to filter them out. If we're going to pursue the idea further, I guess I should spawn a new thread, but would people consider moving *that* one to DEBUG1? There's an idea I can get behind! -Kevin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Postgres 8.3 archive_command
Simon Riggs <[EMAIL PROTECTED]> writes: > On Wed, 2007-11-21 at 11:27 -0500, Tom Lane wrote: >> That would confuse people terribly, and it *would* endanger our ability >> to see what was happening, 254 times out of 255. > That's my feeling too, just wanted to check it still made sense for > y'all. Just to clarify: I don't object to lowering "successfully archived" messages to DEBUG1, if the field consensus is that it's too chatty. What I didn't like was the idea of logging some events but not other identical events. 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] Postgres 8.3 archive_command
On Wed, 2007-11-21 at 11:27 -0500, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > Perhaps we should move the successful archived message to DEBUG1 now, > > except for the first message after the archiver starts or when the > > archive_command changes, plus one message every 255 segments? That would > > reduce the log volume in the normal case without endangering our ability > > to see what is happening. > > That would confuse people terribly, and it *would* endanger our ability > to see what was happening, 254 times out of 255. That's my feeling too, just wanted to check it still made sense for y'all. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] plperl failure on OS X 10.5(.1)
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Nothing fatal? Huh, you have a curious idea about warnings. This makes > me think you have the wrong headers or something -- the argument > mentioned in all these cases is bool, so maybe there is an ABI > incompatibility somewhere. Yeah, and it's hardly difficult to see how that might lead to the reported "null prosrc" error, either. boolisnull; ... prosrcdatum = SysCacheGetAttr(PROCOID, procTup, Anum_pg_proc_prosrc, &isnull); if (isnull) elog(ERROR, "null prosrc"); > Perhaps a Perl header is redefining "bool" on your platform? Seems the question is not so much about OS X as it is about what perl you're using ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Postgres 8.3 archive_command
Simon Riggs <[EMAIL PROTECTED]> writes: > Perhaps we should move the successful archived message to DEBUG1 now, > except for the first message after the archiver starts or when the > archive_command changes, plus one message every 255 segments? That would > reduce the log volume in the normal case without endangering our ability > to see what is happening. That would confuse people terribly, and it *would* endanger our ability to see what was happening, 254 times out of 255. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Postgres 8.3 archive_command
>>> On Wed, Nov 21, 2007 at 8:49 AM, in message <[EMAIL PROTECTED]>, Simon Riggs <[EMAIL PROTECTED]> wrote: > Perhaps we should move the successful archived message to DEBUG1 now, > except for the first message after the archiver starts or when the > archive_command changes, plus one message every 255 segments? That would > reduce the log volume in the normal case without endangering our ability > to see what is happening. I would prefer to always log the archives; it has proven useful in investigating what's going on when our backup stream "stalls". How much other stuff would I be getting if I had to turn on DEBUG1 to see this? Would it be overkill to add a GUC to configure the log level of archiving? For less active databases, where we rarely get more than one segment per hour, it would be kinda odd to see an archive logged once every couple weeks. -Kevin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] wrong behavior using to_char() again
Euler Taveira de Oliveira wrote: > Hi, > > Looking again at bug report [1], I agree that's a glibc bug. Numbers in > pt_BR has its format 1.234.567,89; sometimes the format 1234567,89 is > acceptable too, ie, the thousand separator is optional. I guess that > some locales use the 'optional' thousand separator too (yep, they are > all broken too). Yeah, formatting.c revs 1.106 and 1.105 contains this (it was already pointed out in the previous thread): revision 1.106 date: 2006-02-12 20:48:23 -0300; author: momjian; state: Exp; lines: +3 -4; Revert because C locale uses "" for thousands_sep, meaning "n/a", while French uses "" for "don't want". Seems we have to keep the existing behavior. revision 1.105 date: 2006-02-12 16:52:06 -0300; author: momjian; state: Exp; lines: +5 -4; Support "" for thousands separator and plus sign in to_char(), per report from French Debian user. psql already handles "" fine. I'm not sure that your proposed patch is OK for the C locale. It was proposed that the C locale should be handled as an exception, but it seems nothing got done in that direction. Are we going to do something for 8.3? -- Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4 "La experiencia nos dice que el hombre peló millones de veces las patatas, pero era forzoso admitir la posibilidad de que en un caso entre millones, las patatas pelarían al hombre" (Ijon Tichy) ---(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] backup_label and server start
Simon Riggs wrote: > That will make PITRs fail: > > 1. pg_start_backup() > 2. backup > 3. shutdown, removes backup_label > 4. pg_stop_backup() > > step 4 will now fail because of a missing backup_label file. Wait a minute: pg_stop_backup() will also fail in the current setup, because after recovery backup_label gets renamed to backup_label.old. So what do we lose if we remove (or rename) backup_label on a clean server shutdown? Yours, Laurenz Albe ---(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] [GENERAL] possible to create multivalued index from xpath() results in 8.3?
Peter Eisentraut <[EMAIL PROTECTED]> writes: > The spec doesn't allow casts between xml and text (varchar) at all. The way > I appear to have derived the current behavior from the spec is that this is > interpreted as an implicit XMLSERIALIZE call in the context of a prepared > statement, which is defined to observe the XML option, as per clause 17.3 > (part 14). This was the clostest piece of spec that described conversion > from xml to character types. Now with the xpath functionality, there is > certainly a strong use case for ignoring this altogether and just serializing > with the XML option set to "content". Given the actual behavior of xmltotext_with_xmloption, it certainly seems like a pretty useless error check. Also, xml_out doesn't behave that way, so why should xmltotext? The volatility markings of xml_in and texttoxml seem wrong too. It looks to me like we need: xml_in should be STABLE because it depends on xmloption xml_recvditto (OK already) xml_out correctly(?) marked IMMUTABLE xml_sendis STABLE, OK because it depends on client_encoding texttoxml should be STABLE because it depends on xmloption xmltotext remove xmloption dependency, mark as IMMUTABLE Should we force initdb to correct these pg_proc entries, or just quietly change pg_proc.h? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Postgres 8.3 archive_command
On Wed, 2007-11-21 at 14:45 +0100, Rudolf van der Leeden wrote: > In v8.2.4 we are currently switching archiving on/off on the fly by > just inserting an archiving command. > Now we have got a separate option for handling the on/off state. > That's ok. > But the note "change requires restart" is a serious change over v8.2. Thanks for the feedback. There was a window of data loss caused by the capability to change the archive_command on and off while running a large COPY, CTAS, CLUSTER or CREATE INDEX, which we had to avoid. The new way of doing this changes that so you must have archive_mode set on always, but you can still change archive_command on the fly. >- The workaround is a script that simply does a NOOP if archiving > is effectively disabled while archive_mode=on all the time. > How can one switch off the archiving log messages? You can't. I think if you choose to set archive command to something that doesn't actually archive the file, thats up to you. The server log shows that Postgres server did as you asked it to do. That helps to avoid complaints like "How come Postgres didn't tell me when it wasn't archiving". Perhaps we should move the successful archived message to DEBUG1 now, except for the first message after the archiver starts or when the archive_command changes, plus one message every 255 segments? That would reduce the log volume in the normal case without endangering our ability to see what is happening. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] backup_label and server start
On Wed, 2007-11-21 at 15:04 +0100, Albe Laurenz wrote: > Simon Riggs wrote: > >> If somebody stops the postmaster while an online backup is > >> in progress, there is no warning or nothing. Only the server > >> will fail to restart. > > > > Well, it seems best not to do this. There is always a need > > for a careful > > procedure to manually shutdown a live server, interlocking with other > > applications. ISTM like a manual procedure will resolve this for you. > > You're arguing that there *should* be a manual intervention > if a server was shutdown while a backup was active. Shutting down the server was a manual action, so what is wrong in a manual action to recover from that mistake? If the shutdown was automatic, then it needs to be properly scheduled so automatic actions do not conflict with one another. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] backup_label and server start
Simon Riggs wrote: >> If somebody stops the postmaster while an online backup is >> in progress, there is no warning or nothing. Only the server >> will fail to restart. > > Well, it seems best not to do this. There is always a need > for a careful > procedure to manually shutdown a live server, interlocking with other > applications. ISTM like a manual procedure will resolve this for you. You're arguing that there *should* be a manual intervention if a server was shutdown while a backup was active. > If we remove the file in the place you suggest then an Archive Recovery > will succeed when it should fail, with no possibility of a hint, which > seems a worse error. > >> How about my second suggestion: >> >> Remove backup_label when the server shuts down cleanly. >> In that case an online backup in progress will not be useful >> anyway, and there is no need to recover on server restart. > > That will make PITRs fail: > > 1. pg_start_backup() > 2. backup > 3. shutdown, removes backup_label > 4. pg_stop_backup() > > step 4 will now fail because of a missing backup_label file. Using the same kind of argument as you did above I would say that pg_stop_backup() *should* fail if the server restarted (and recovered!) inbetween - there was certainly something fishy going on during the online backup. In your list, you left out step 3.5: restart the server. This step may fail if you do *not* remove the backup_label. What is worse: - Have pg_stop_backup() fail if the server was shut down during the backup or - Prevent the server from restarting at all without manual intervention. I would say the latter. Yours, Laurenz Albe ---(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] Postgres 8.3 archive_command
Hi, I just installed v8.3beta3 and discovered that the way WAL archiving is handled has changed. From postgresql.conf: #archive_mode = off # allows archiving to be done (change requires restart) #archive_command = '' # command to use to archive a logfile segment In v8.2.4 we are currently switching archiving on/off on the fly by just inserting an archiving command. Now we have got a separate option for handling the on/off state. That's ok. But the note "change requires restart" is a serious change over v8.2. Background: We are using the archive_command for PITR backup every night. This can easily be done while the server is running. After the migration to v8.3 we'd have to (1) stop the server, switch on archiving, start the server, (2) run PITR backup, and (3) stop the server, switch off archiving, start the server. Questions: - Is the requirement "change of archive_mode requires restart" just temporary or is it going to stay in the final release? If the server restart is planned for final release (for whatever reason) then my next question is: - The workaround is a script that simply does a NOOP if archiving is effectively disabled while archive_mode=on all the time. How can one switch off the archiving log messages? Thanks and best regards, Rudolf VanderLeeden IT Consultant Logicunited GmbH Germany [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] backup_label and server start
On Wed, 2007-11-21 at 09:47 +, Peter Childs wrote: > How about this, emit a warning on shutdown and fail to shutdown until > the backup has finished. That would be reasonable for -m smart shutdown. We would then be treating the backup as a connection. ...but not for a fast shutdown. Any comments against? -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] VACUUM/ANALYZE counting of in-doubt tuples
Tom Lane wrote: > I feel fairly comfortable with this analysis for ANALYZE, and the > patch I posted yesterday can easily be adjusted to accommodate it. > However, what of VACUUM? As that code stands, every non-removable > tuple (including RECENTLY_DEAD ones) is counted as live, and the > dead-tuples count gets reset to zero. That seems clearly bogus. > But the other-transaction-commits-second hypothesis seems a good bit > more dubious for VACUUM than it is for ANALYZE. > > Should we attempt to adjust VACUUM's accounting as well, or leave it > for 8.4? For that matter, should adjusting ANALYZE be left for 8.4? > Thoughts? Has this issue been a real problem? If so, probably we should consider adjusting ANALYZE for 8.3 per your proposal. For VACUUM, I think one thing we should do to reduce the severity of the problem is to send the pgstat message before attempting the truncation; that way, less transactions are lost. (There is still going to be a lot of lost pgstat traffic when vacuum_delay is high). I am not sure about further changes. For 8.4 we could discuss more invasive changes. Maybe send a pgstat message just before each vacuum_delay sleep point? This would have to use the incremental update approach, which is probably better when vacuum_delay is enabled. -- Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC "Right now the sectors on the hard disk run clockwise, but I heard a rumor that you can squeeze 0.2% more throughput by running them counterclockwise. It's worth the effort. Recommended." (Gerry Pourwelle) ---(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] [GENERAL] possible to create multivalued index from xpath() results in 8.3?
Am Mittwoch, 21. November 2007 schrieb Tom Lane: > "Matt Magoffin" <[EMAIL PROTECTED]> writes: > > Ugh, you're right of course! Somehow I had this wrong. So I tried to > > create an index on the xml[] result by casting to text[] but I got the > > "function must be immutable" error. Is there any reason the xml[] to > > text[] cast is not immutable? > > Hmm ... I see that xmltotext() is marked 'stable' in pg_proc.h, > but texttoxml() is marked 'immutable', which is at best inconsistent. > It looks to me like they both depend on the GUC setting "xmloption", > which would mean they should both be stable. Peter, is there a bug > there? Yeah, that doesn't look right. > Also, is there a way to get rid of the GUC dependency so that > there's a reasonably safe way to index XML values? We could drop the dependency in xmltotext() with little loss of functionality. The spec doesn't allow casts between xml and text (varchar) at all. The way I appear to have derived the current behavior from the spec is that this is interpreted as an implicit XMLSERIALIZE call in the context of a prepared statement, which is defined to observe the XML option, as per clause 17.3 (part 14). This was the clostest piece of spec that described conversion from xml to character types. Now with the xpath functionality, there is certainly a strong use case for ignoring this altogether and just serializing with the XML option set to "content". -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] plperl failure on OS X 10.5(.1)
B. Maust wrote: > there were a few warnings building plperl: > > gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline > -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -I. > -I../../../src/include > -I/System/Library/Perl/5.8.8/darwin-thread-multi-2level/CORE -c -o > plperl.c: In function '_PG_initg: > plperl.c:191: warning: passing argument 4 of 'DefineCustomBoolVariableg > from incompatible pointer type > plperl.c: In function 'compile_plperl_functiong: > plperl.c:1624: warning: passing argument 4 of 'SysCacheGetAttrg from > incompatible pointer type > plperl.c:1652: warning: passing argument 4 of 'hash_searchg from > incompatible pointer type > plperl.c: In function 'plperl_hash_from_tupleg: plperl.c:1685: warning: > passing argument 4 of 'nocachegetattrg from incompatible pointer type > plperl.c:1685: warning: passing argument 4 of 'nocachegetattrg from > incompatible pointer type > plperl.c:1685: warning: passing argument 4 of 'heap_getsysattrg from > incompatible pointer type > plperl.c:1696: warning: passing argument 3 of 'getTypeOutputInfog from > incompatible pointer type > plperl.c: In function 'plperl_return_nextg: > plperl.c:1926: warning: passing argument 3 of 'heap_form_tupleg from > incompatible pointer type > plperl.c: In function 'plperl_spi_prepareg: > plperl.c:2229: warning: passing argument 4 of 'hash_searchg from > incompatible pointer type > > nothing fatal... and a more worrisome warning when linking libperl: Nothing fatal? Huh, you have a curious idea about warnings. This makes me think you have the wrong headers or something -- the argument mentioned in all these cases is bool, so maybe there is an ABI incompatibility somewhere. Perhaps a Perl header is redefining "bool" on your platform? -- Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4 "I love the Postgres community. It's all about doing things _properly_. :-)" (David Garamond) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] backup_label and server start
On 21/11/2007, Simon Riggs <[EMAIL PROTECTED]> wrote: > > On Wed, 2007-11-21 at 09:04 +0100, Albe Laurenz wrote: > > > If somebody stops the postmaster while an online backup is > > in progress, there is no warning or nothing. Only the server > > will fail to restart. > > Well, it seems best not to do this. There is always a need for a careful > procedure to manually shutdown a live server, interlocking with other > applications. ISTM like a manual procedure will resolve this for you. > > If we remove the file in the place you suggest then an Archive Recovery > will succeed when it should fail, with no possibility of a hint, which > seems a worse error. > > > All I want to do is restart a server after a clean shutdown. > > > > How about my second suggestion: > > > > Remove backup_label when the server shuts down cleanly. > > In that case an online backup in progress will not be useful > > anyway, and there is no need to recover on server restart. > > That will make PITRs fail: > > 1. pg_start_backup() > 2. backup > 3. shutdown, removes backup_label > 4. pg_stop_backup() > > step 4 will now fail because of a missing backup_label file. > > How about this, emit a warning on shutdown and fail to shutdown until the backup has finished. Seams to me that either way your sunk if you shut down a server while a backup is in progress. Your only way out is to work out weather to use the previous pitr backups plus logs or remove the label. Doing it automatically would be very very dangerous. Peter.
Re: [HACKERS] backup_label and server start
On Wed, 2007-11-21 at 09:04 +0100, Albe Laurenz wrote: > If somebody stops the postmaster while an online backup is > in progress, there is no warning or nothing. Only the server > will fail to restart. Well, it seems best not to do this. There is always a need for a careful procedure to manually shutdown a live server, interlocking with other applications. ISTM like a manual procedure will resolve this for you. If we remove the file in the place you suggest then an Archive Recovery will succeed when it should fail, with no possibility of a hint, which seems a worse error. > All I want to do is restart a server after a clean shutdown. > > How about my second suggestion: > > Remove backup_label when the server shuts down cleanly. > In that case an online backup in progress will not be useful > anyway, and there is no need to recover on server restart. That will make PITRs fail: 1. pg_start_backup() 2. backup 3. shutdown, removes backup_label 4. pg_stop_backup() step 4 will now fail because of a missing backup_label file. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] plperl failure on OS X 10.5(.1)
I mentioned in #postgres a bit ago to no avail that I couldn't get my plperl stored procedures to work after upgrading to OS X 10.5, with only a obtuse "ERROR: null prosrc". I was using 8.2.4 and didn't want to make a big deal about a non-current release, but today I had a chance to build 8.3 and 8.2.5 and am seeing the same behavior. I don't see 10.5 in the build farm list, so I thought I would ask if this is a known issue. A synopsis: configure, make and install a fresh postgres and database (configured --with-perl --with-python --with-openssl). This is on my G4 powerbook. createlang pgperl attempt to create a perl function, e.g. the perl_max function from the docs (http://www.postgresql.org/docs/8.2/static/plperl-funcs.html): $ cat perlmax.pl.sql CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$ if ($_[0] > $_[1]) { return $_[0]; } return $_[1]; $$ LANGUAGE plperl; $ ~/local/pg-8.3b3/bin/psql -f perlmax.pl.sql test psql:perlmax.pl.sql:4: ERROR: null prosrc there were a few warnings building plperl: gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -I. -I../../../src/include -I/System/Library/Perl/5.8.8/darwin-thread-multi-2level/CORE -c -o plperl.c: In function '_PG_initg: plperl.c:191: warning: passing argument 4 of 'DefineCustomBoolVariableg from incompatible pointer type plperl.c: In function 'compile_plperl_functiong: plperl.c:1624: warning: passing argument 4 of 'SysCacheGetAttrg from incompatible pointer type plperl.c:1652: warning: passing argument 4 of 'hash_searchg from incompatible pointer type plperl.c: In function 'plperl_hash_from_tupleg: plperl.c:1685: warning: passing argument 4 of 'nocachegetattrg from incompatible pointer type plperl.c:1685: warning: passing argument 4 of 'nocachegetattrg from incompatible pointer type plperl.c:1685: warning: passing argument 4 of 'heap_getsysattrg from incompatible pointer type plperl.c:1696: warning: passing argument 3 of 'getTypeOutputInfog from incompatible pointer type plperl.c: In function 'plperl_return_nextg: plperl.c:1926: warning: passing argument 3 of 'heap_form_tupleg from incompatible pointer type plperl.c: In function 'plperl_spi_prepareg: plperl.c:2229: warning: passing argument 4 of 'hash_searchg from incompatible pointer type nothing fatal... and a more worrisome warning when linking libperl: gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -bundle -multiply_defined suppress plperl.o spi_internal.o SPI.o -L/usr/local/lib -L/System/Library/Perl/5.8.8/darwin-thread-multi-2level/CORE -L../../../src/port -arch i386 -arch ppc /System/Library/Perl/5.8.8/darwin-thread-multi-2level/auto/DynaLoader/DynaLoader.a -lperl -ldl -lm -lutil -lc -bundle_loader ../../../src/backend/postgres -o libplperl.0.0.so ld: warning in ../../../src/backend/postgres, file is not of required architecture ld: warning in plperl.o, file is not of required architecture ld: warning in spi_internal.o, file is not of required architecture ld: warning in SPI.o, file is not of required architecture I don't have terribly much time to figure out the guts of plperl, but would be glad to provide any more information or a login to an OS 10.5 host if that can help... Thanks for everything, -- Brandon Maust, Research Consultant Mullins Lab, University of Washington School of Medicine ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] backup_label and server start
>> If the postmaster is stopped with 'pg_ctl stop' while an >> online backup is in progress, the 'backup_label' file will remain >> in the data directory. [...] >> the startup process will fail with a message like this: [...] >> PANIC: could not locate required checkpoint record >> HINT: If you are not restoring from a backup, try removing the file >> "/POSTGRES/data/PG820/backup_label". >> >> wouldn't it be a good thing >> for the startup process to ignore (and rename) the backup_label >> file if no recovery.conf is present? Tom Lane replied: > No, it certainly wouldn't. Point taken. When backup_label is present and recovery.conf isn't, there is the risk that the data directory has been restored from an online backup, in which case using the latest available checkpoint would be detrimental. > I don't see why we should simplify the bizarre case you're > talking about Well, it's not a bizarre case, it has happened twice here. If somebody stops the postmaster while an online backup is in progress, there is no warning or nothing. Only the server will fail to restart. One of our databases is running in a RedHat cluster, which in this case cannot failover to another node. And this can also happen during an online backup. Simon Riggs replied: > The hint is telling you how to restart the original server, not a crafty > way of cheating the process to allow you to use it for backup. > > What are you trying to do? You misunderstood me, I'm not trying to cheat anything, nor do I want to restore a backup that way. All I want to do is restart a server after a clean shutdown. How about my second suggestion: Remove backup_label when the server shuts down cleanly. In that case an online backup in progress will not be useful anyway, and there is no need to recover on server restart. What do you think? Yours, Laurenz Albe ---(end of broadcast)--- TIP 6: explain analyze is your friend