Re: [HACKERS] Re: [COMMITTERS] pgsql: Fix free space map to correctly track the total amount of FSM
On Tue, Oct 02, 2007 at 09:07:54PM -0400, Alvaro Herrera wrote: Decibel! wrote: Hrm... what about adding output to vacuum verbose that indicates how many pages in a relation have free space? That would allow something like pgfouine to see how many FSM pages were needed. It would also make it easier to identify relations that could stand a vacuum full/reindex/cluster (though you'd also want to know something like average free space per page). Rather than wasting time fixing minor FSM issues, I would favor rewriting the stuff so that the FSM is disk-spillable. Sure, but this would also likely be a 20 line change to vacuum... These proposals would not help, at least me at all. Since I was talking about the pre 8.2 versions. There's 0 chance these changes are backported to previous versions. I'm thinkg about writing a small function which will do something 8.2 or later's vacuum does(telling the right FSM pages needed). -- Tatsuo Ishii SRA OSS, Inc. Japan ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Getting to 8.3 beta1
Hi, On 10/4/07, Tom Lane [EMAIL PROTECTED] wrote: At this point the bulk of the work is done, except for SGML markup prettification. There is a typo in the contrib part: # Add GIN support for hstore (Guillaume Smet, Teodor) # Add GIN support for pg_trgm (Guillaume Smet, Teodor0 s/Teodor0/Teodor)/ And I didn't participate to the GIN support of hstore, I just added it to pg_trgm with the help of Teodor so it should be Teodor alone on hstore GIN support. Regards, -- Guillaume ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Something's been bugging me
* Tom Lane: I ran into an interesting failure here on HPPA: the code the compiler generated for copying unaligned toast pointers into aligned local variables failed, because it was assuming halfword (2-byte) alignment of the data to be copied! (Instead of a memcpy call it was generating an inline loop of ldh/sth instructions.) Apparently gcc's thought process is the pointer is declared as struct varlena *, therefore must be at least 4-aligned, therefore the data at offset 2 is at least 2-aligned. The intermediate cast to varattrib_1b_e * did not prevent this; I had to assign the datum pointer into a separate local variable of that type to suppress the optimization. This is quite deliberate, it leads to better code. In general, once you've cast a pointer to something which needs more alignment than what's actually, there is no way to get away from that (except using asm insertions as optimization barriers, of course). This is fine from the C semantics because undefined behavior occurs during the cast already. I believe that other compilers have similar rules (certainly when it comes to aliasing), so it's better not to try to outsmart the C standard here. -- Florian Weimer[EMAIL PROTECTED] BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Getting to 8.3 beta1
On Thu, 2007-10-04 at 09:04 +0200, Guillaume Smet wrote: There is a typo in the contrib part: # Add GIN support for hstore (Guillaume Smet, Teodor) # Add GIN support for pg_trgm (Guillaume Smet, Teodor0 s/Teodor0/Teodor)/ And I didn't participate to the GIN support of hstore, I just added it to pg_trgm with the help of Teodor so it should be Teodor alone on hstore GIN support. Fixed, thanks. -Neil ---(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] Not *quite* there on ecpg fixes
On Thu, Oct 04, 2007 at 12:47:13AM -0400, Tom Lane wrote: Buildfarm member brown_bat (cygwin/gcc) still isn't happy: ... Just committed a patch that hopefully solves this. Kind of surprises me that this only occurs on cygwin. 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 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
Alvaro Herrera wrote: I came up with the following patch. What this does is cancel any ANALYZE started by autovacuum, at the top of ALTER TABLE. There's a small race condition, autoanalyze could start between the calls autovac_cancel_analyze and relation_open. And it doesn't solve the problem for autovacuum. Or other commands than ALTER TABLE, like CLUSTER. There is a new function relation_openrv_cav(). This is the same as relation_openrv, except that it will also cancel analyzes. I'm still wondering if I should merge the two and have a third boolean argument to specify whether to do the cancel. I like it better the way you have it now. -- 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] First steps with 8.3 and autovacuum launcher
Alvaro, On 10/4/07, Alvaro Herrera [EMAIL PROTECTED] wrote: I came up with the following patch. What this does is cancel any ANALYZE started by autovacuum, at the top of ALTER TABLE. It doesn't seem to work for me. I still have my ALTER TABLEs waiting: \_ postgres: postgres cityvox [local] ALTER TABLE waiting \_ postgres: autovacuum worker process cityvox \_ postgres: autovacuum worker process cityvox \_ postgres: autovacuum worker process cityvox I have a lot of NOTICE: cancelling auto-analyze lines in my log but it doesn't seem to change the overall behaviour (even if it's a bit better than before). With cost delay of 20: real44m20.525s user0m35.757s sys 0m2.985s compared to 51 minutes before applying your patch. The box is still idle for a long periods during the restore. Regards, -- Guillaume ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Not *quite* there on ecpg fixes
Michael Meskes wrote: On Thu, Oct 04, 2007 at 12:47:13AM -0400, Tom Lane wrote: Buildfarm member brown_bat (cygwin/gcc) still isn't happy: ... Just committed a patch that hopefully solves this. Kind of surprises me that this only occurs on cygwin. It's still not working. Don't have time right now to diagnose why. For now, since Michael doesn't have a windows machine to play with, I have switched brown_bat's schedule so it runs HEAD 6 times a day (not exactly every 4 hours, the intervals vary between 3 and 4 1/2 hours). cheers andrew ---(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] First steps with 8.3 and autovacuum launcher
Guillaume Smet escribió: Alvaro, On 10/4/07, Alvaro Herrera [EMAIL PROTECTED] wrote: I came up with the following patch. What this does is cancel any ANALYZE started by autovacuum, at the top of ALTER TABLE. It doesn't seem to work for me. I still have my ALTER TABLEs waiting: \_ postgres: postgres cityvox [local] ALTER TABLE waiting \_ postgres: autovacuum worker process cityvox \_ postgres: autovacuum worker process cityvox \_ postgres: autovacuum worker process cityvox I have a lot of NOTICE: cancelling auto-analyze lines in my log but it doesn't seem to change the overall behaviour (even if it's a bit better than before). With cost delay of 20: real44m20.525s user0m35.757s sys 0m2.985s compared to 51 minutes before applying your patch. Hmm, it looks like the race condition Heikki mentioned is the culprit. We need a way to stop future analyzes from starting. Back to the drawing board ... -- Alvaro Herrera http://www.PlanetPostgreSQL.org/ Criptografía: Poderosa técnica algorítmica de codificación que es empleada en la creación de manuales de computadores. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Connection Pools and DISCARD ALL
There's been a lively discussion on JDBC list recently about how we handle connection pooling. This has connected a few thoughts in my head. That's made me think about the PHP interface, which issues a BEGIN; ROLLBACK; pair every time somebody connects to the pool. We should have a single/consistent way of starting a new connection to PostgreSQL when using a session pool. As committed, DISCARD ALL does everything but cannot be issued inside a transaction block. I'd like to propose that DISCARD ALL also issue a ROLLBACK command if it is issued from within a transaction block. That way whenever we reassign a session pool connection to another agent we can just issue a single command from all interfaces, without needing to test what the state of the connection is beforehand. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Not *quite* there on ecpg fixes
Andrew Dunstan wrote: Michael Meskes wrote: On Thu, Oct 04, 2007 at 12:47:13AM -0400, Tom Lane wrote: Buildfarm member brown_bat (cygwin/gcc) still isn't happy: ... Just committed a patch that hopefully solves this. Kind of surprises me that this only occurs on cygwin. It's still not working. It looks like dlltool does not like the .def file: dllwrap -Wl,--enable-auto-import -o cygpgtypes.dll --dllname cygpgtypes.dll --def libpgtypesdll.def numeric.o datetime.o common.o dt_common.o timestamp.o interval.o pgstrcasecmp.o -L../../../../src/port -L/usr/local/lib -lm dlltool: Syntax error in def file libpgtypesdll.def:3 dlltool: Syntax error in def file libpgtypesdll.def:3 dlltool --dllname cygpgtypes.dll --def libpgtypesdll.def --output-lib libpgtypes.a dlltool: Syntax error in def file libpgtypesdll.def:3 Line 3 of that file is unadorned EXPORTS: echo '; DEF file for MS VC++' libpgtypesdll.def echo 'LIBRARY LIBPGTYPES' libpgtypesdll.def echo 'EXPORTS' libpgtypesdll.def The error that actually finishes the build is below: gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -g -I../include -I../../../../src/interfaces/ecpg/include -I../../../../src/interfaces/libpq -I../../../../src/port -I../../../../src/include -DBUILDING_DLL -c -o thread.o thread.c dllwrap -Wl,--enable-auto-import -o cygecpg.dll --dllname cygecpg.dll --def libecpgdll.def execute.o typename.o descriptor.o data.o error.o prepare.o memory.o connect.o misc.o path.o thread.o -L../pgtypeslib -L../../../../src/interfaces/libpq -L../../../../src/port -L/usr/local/lib -lpgtypes -lpq -lm execute.o: In function `ecpg_store_input': /home/AndrewDunstan/bf/root/HEAD/pgsql.2768/src/interfaces/ecpg/ecpglib/execute.c:979: undefined reference to `_PGTYPEStimestamp_to_asc' [...] I wonder why the dlltool failure is not causing the build to fail immediately? -- Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC The Postgresql hackers have what I call a NASA space shot mentality. Quite refreshing in a world of weekend drag racer developers. (Scott Marlowe) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [SQL] Why does the sequence skip a number with generate_series?
Stephan Szabo wrote: On Tue, 2 Oct 2007, Jeff Frost wrote: I expected these numbers to be in sync, but was suprised to see that the sequence skips a values after every generate series. CREATE TABLE jefftest ( id serial, num int ); INSERT INTO jefftest (num) values (generate_series(1,10)); INSERT INTO jefftest (num) values (generate_series(11,20)); INSERT INTO jefftest (num) values (generate_series(21,30)); It seems to do what you'd expect if you do INSERT INTO jefftest(num) select a from generate_series(1,10) as foo(a); INSERT INTO jefftest(num) select a from generate_series(11,20) as foo(a); INSERT INTO jefftest(num) select a from generate_series(21,30) as foo(a); I tried a function that raises a notice and called it as select f1(1), generate_series(1,10); and got 11 notices so it looks like there's some kind of phantom involved. That's interesting - might need an answer from the core hackers. I am posting this to pgsql-hackers to get their comments and feedback. I wouldn't count it as a bug but it could be regarded as undesirable side effects. My guess is that what appears to happen is that the sequence is created by incrementing as part of the insert steps and the test to check the end of the sequence is - if last_inserted_number end_sequence_number rollback_last_insert This would explain the skip in sequence numbers. My thoughts are that - if last_inserted_number end_sequence_number insert_again would be a better way to approach this. Of course you would also need to check that the (last_insert + step_size) isn't greater than the end_sequence_number when the step_size is given. I haven't looked at the code so I don't know if that fits easily into the flow of things. The as foo(a) test would fit this as the sequence is generated into the equivalent of a temporary table the same as a subselect, then used as insert data. The rollback would be applied during the temporary table generation so won't show when the data is copied across to fulfill the insert. Maybe the planner or the generate series function could use a temporary table to give the same results as select from generate_series() -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Not *quite* there on ecpg fixes
Andrew Dunstan [EMAIL PROTECTED] writes: It's still not working. Don't have time right now to diagnose why. For now, since Michael doesn't have a windows machine to play with, I have switched brown_bat's schedule so it runs HEAD 6 times a day (not exactly every 4 hours, the intervals vary between 3 and 4 1/2 hours). I wonder if we could get trout back in the rotation, too? 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] Not *quite* there on ecpg fixes
On Thu, Oct 04, 2007 at 10:04:41AM -0400, Alvaro Herrera wrote: It looks like dlltool does not like the .def file: ... Line 3 of that file is unadorned EXPORTS: echo '; DEF file for MS VC++' libpgtypesdll.def echo 'LIBRARY LIBPGTYPES' libpgtypesdll.def echo 'EXPORTS' libpgtypesdll.def ... I wonder why the dlltool failure is not causing the build to fail immediately? These lines are simply copied from libpq/Makefile but ddltool does not complain while working on libpq. Any idea where they differ? 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 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [SQL] Why does the sequence skip a number with generate_series?
Shane Ambler wrote: Stephan Szabo wrote: On Tue, 2 Oct 2007, Jeff Frost wrote: I expected these numbers to be in sync, but was suprised to see that the sequence skips a values after every generate series. CREATE TABLE jefftest ( id serial, num int ); INSERT INTO jefftest (num) values (generate_series(1,10)); INSERT INTO jefftest (num) values (generate_series(11,20)); INSERT INTO jefftest (num) values (generate_series(21,30)); It seems to do what you'd expect if you do INSERT INTO jefftest(num) select a from generate_series(1,10) as foo(a); INSERT INTO jefftest(num) select a from generate_series(11,20) as foo(a); INSERT INTO jefftest(num) select a from generate_series(21,30) as foo(a); I tried a function that raises a notice and called it as select f1(1), generate_series(1,10); and got 11 notices so it looks like there's some kind of phantom involved. That's interesting - might need an answer from the core hackers. I am posting this to pgsql-hackers to get their comments and feedback. I wouldn't count it as a bug but it could be regarded as undesirable side effects. Don't use set-returning functions in scalar context. If you put them in the FROM clause, as Stephan says above, it works fine. Anywhere else they have strange behavior and they are supported only because of backwards compatibility. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Connection Pools and DISCARD ALL
On Thu, 2007-10-04 at 10:29 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: As committed, DISCARD ALL does everything but cannot be issued inside a transaction block. I'd like to propose that DISCARD ALL also issue a ROLLBACK command if it is issued from within a transaction block. That was *intentional* to prevent mistakes. I understand; I'm challenging that intention. Neil's original commit message said that was intended to catch programmer mistakes and that such use is probably unintended. If the developer has attempted to issue it in the wrong place, he's probably also forgot to handle errors correctly, i.e. ROLLBACK then reissue. If we care about helping the developer we should make the command end the transaction block if one exists then issue it. Less code for the developer, less mistakes. Somebody who wants the above behavior can send ROLLBACK; DISCARD ALL. ...which generates an ERROR if no transaction is in progress and fills the log needlessly. http://svr5.postgresql.org/pgsql-interfaces/2001-02/msg00116.php -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] uh-oh, dugong failing again
Tom Lane [EMAIL PROTECTED] writes: 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? It seems to work with icc on my 32 bit intel cpu. Earlier you speculated that the struct might be getting padded out which would cause hash failures. But surely using a different padding from other compilers would be a compiler bug since it would be an incompatible ABI change. I find it hard to believe intel's compiler would get the ia64 ABI wrong. And hard to believe nobody's noticed an incompatible ABI from gcc-generated binaries. -- Gregory Stark EnterpriseDB http://www.enterprisedb.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] First steps with 8.3 and autovacuum launcher
On Tue, 2007-10-02 at 11:17 -0400, Tom Lane wrote: In any case, this would still only fix things for pg_restore, and I remain concerned that people will gripe about autovacuum blocking locks. The idea of kicking autovac off tables remains probably more interesting in the long run. Yes, sounds good. I'd also like to see vacuum_delay_point() do a test against CountActiveBackends() to see if anything else is running. If there all non-autovac processes are idle or waiting, then we should skip the delay point, this time only. That way a VACUUM can go at full speed on an idle system and slow down when people get active again. It will also help when people issue a DDL statement against a table that is currently being vacuumed. I've got a patch worked out to do this. -- 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
Re: [HACKERS] Connection Pools and DISCARD ALL
Simon Riggs [EMAIL PROTECTED] writes: As committed, DISCARD ALL does everything but cannot be issued inside a transaction block. I'd like to propose that DISCARD ALL also issue a ROLLBACK command if it is issued from within a transaction block. That was *intentional* to prevent mistakes. Somebody who wants the above behavior can send ROLLBACK; DISCARD ALL. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
Simon Riggs [EMAIL PROTECTED] writes: I'd also like to see vacuum_delay_point() do a test against CountActiveBackends() to see if anything else is running. If there all non-autovac processes are idle or waiting, then we should skip the delay point, this time only. That way a VACUUM can go at full speed on an idle system and slow down when people get active again. It will also help when people issue a DDL statement against a table that is currently being vacuumed. I've got a patch worked out to do this. This is exceedingly Postgres-centric thinking. The lack of any other backends does not mean that the system owner wants Postgres to take over the machine. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] What does 'elapsed' exact means in PostgreSQL Log Statistics
Hi, I am engaged in a master degree project and i need the answer to the following question: How much time the EXECUTOR of PostgreSQL spent during a determined time interval? My Scenario is like this: PostgreSQL 7.4 with the following parameters: - log_duration = true - log_pid = true - log_statement = true - log_timestamp = true - log_parser_stats = true - log_planner_stats = true - log_executor_stats = true - stats_start_colector = true - stats_command_string = true - stats_block_level = true Using the BenchmarkSQL Software, I submit random transaction (simluating the TPC-C benchmark) during a time interval (say 30-45 seconds) PostgreSQL then generate statistics for each phase which an SQL Command is executed (PARSER, REWRITE, PLANNER, EXECUTOR), like the log section below: 2007-09-27 15:07:25 [9269] LOG: sentença: SELECT c_discount, c_last, c_credit, w_tax FROM customer, warehouse WHERE w_id = $1 AND w_id = c_w_id AND c_d_id = $2 AND c_id = $3 2007-09-27 15:07:25 [9269] LOG: PARSER STATISTICS DETALHE: ! system usage stats: ! 0.73 elapsed 0.00 user 0.00 system sec ! [0.000999 user 0.001999 sys total] ! 0/0 [0/0] filesystem blocks in/out ! 0/26 [0/490] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent ! 0/0 [2/1] voluntary/involuntary context switches ! buffer usage stats: ! Shared blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written 2007-09-27 15:07:25 [9269] LOG: PARSE ANALYSIS STATISTICS DETALHE: ! system usage stats: ! 0.001513 elapsed 0.001000 user 0.001000 system sec ! [0.001999 user 0.002999 sys total] ! 0/0 [0/0] filesystem blocks in/out ! 0/149 [0/640] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent ! 0/0 [2/1] voluntary/involuntary context switches ! buffer usage stats: ! Shared blocks: 40 read, 0 written, buffer hit rate = 75.00% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written 2007-09-27 15:07:25 [9269] LOG: REWRITER STATISTICS DETALHE: ! system usage stats: ! 0.16 elapsed 0.00 user 0.00 system sec ! [0.001999 user 0.002999 sys total] ! 0/0 [0/0] filesystem blocks in/out ! 0/2 [0/644] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent ! 0/0 [2/1] voluntary/involuntary context switches ! buffer usage stats: ! Shared blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written 2007-09-27 15:07:25 [9269] LOG: PLANNER STATISTICS DETALHE: ! system usage stats: ! 0.001496 elapsed 0.001000 user 0.00 system sec ! [0.002999 user 0.002999 sys total] ! 0/0 [0/0] filesystem blocks in/out ! 0/109 [0/754] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent ! 0/1 [2/2] voluntary/involuntary context switches ! buffer usage stats: ! Shared blocks: 26 read, 0 written, buffer hit rate = 81.56% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written 2007-09-27 15:07:25 [9269] LOG: EXECUTOR STATISTICS DETALHE: ! system usage stats: ! 0.022129 elapsed 0.00 user 0.002000 system sec ! [0.002999 user 0.005999 sys total] ! 0/0 [0/0] filesystem blocks in/out ! 0/186 [0/966] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent ! 2/0 [4/2] voluntary/involuntary context switches ! buffer usage stats: ! Shared blocks: 94 read, 0 written, buffer hit rate = 0.00% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written Trying to answer my initial question, I do something like this: 1. Through the Commands TIMESTAMP, I calculate the total time interval where all the commands had been executed. (END TIMESTAMP - BEGIN TIMESTAMP) 2. Then I sum all the 'elapsed' fields of all EXECUTOR Sections of all commands executed. This, in thesis, give me the amount of time that the
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
On Thu, 2007-10-04 at 10:43 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: I'd also like to see vacuum_delay_point() do a test against CountActiveBackends() to see if anything else is running. If there all non-autovac processes are idle or waiting, then we should skip the delay point, this time only. That way a VACUUM can go at full speed on an idle system and slow down when people get active again. It will also help when people issue a DDL statement against a table that is currently being vacuumed. I've got a patch worked out to do this. This is exceedingly Postgres-centric thinking. Thanks :-) The lack of any other backends does not mean that the system owner wants Postgres to take over the machine. Good thought. Sounds like we'd benefit from having another parameter: autovacuum_vacuum_delay_siblings = -1 (default) 0..INT_MAX Minimum number of active backends before autovacuum delay becomes effective. If there are fewer than param active backends automatic maintenance will proceed at full speed when the opportunity arises. The delay will vary dynamically, thus utilising quiet periods more effectively as and when they occur. Set to -1 if the database server is running on a shared system and you do not want quiet periods to be used for maintenance. -- 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
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
Alvaro Herrera [EMAIL PROTECTED] writes: Hmm, it looks like the race condition Heikki mentioned is the culprit. We need a way to stop future analyzes from starting. Back to the drawing board ... A crazy idea I just had -- what if you roll this into the deadlock check? So after waiting on the lock for 1s it wakes up, finds that the holder it's waiting on is an autovacuum process and cancels it instead of finding no deadlock. -- Gregory Stark 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] First steps with 8.3 and autovacuum launcher
On Thu, 2007-10-04 at 16:07 +0100, Gregory Stark wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Hmm, it looks like the race condition Heikki mentioned is the culprit. We need a way to stop future analyzes from starting. Back to the drawing board ... A crazy idea I just had -- what if you roll this into the deadlock check? So after waiting on the lock for 1s it wakes up, finds that the holder it's waiting on is an autovacuum process and cancels it instead of finding no deadlock. +1 -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [SQL] Why does the sequence skip a number with generate_series?
Alvaro Herrera [EMAIL PROTECTED] writes:
Shane Ambler wrote:
CREATE TABLE jefftest ( id serial, num int );
INSERT INTO jefftest (num) values (generate_series(1,10));
INSERT INTO jefftest (num) values (generate_series(11,20));
INSERT INTO jefftest (num) values (generate_series(21,30));
Don't use set-returning functions in scalar context.
I think what is actually happening is that the expanded targetlist is
nextval('seq'), generate_series(1,10)
On the eleventh iteration, generate_series() returns ExprEndResult to
show that it's done ... but the 11th nextval() call already happened.
If you switched the columns around, you wouldn't get the extra call.
If you think that's bad, the behavior with multiple set-returning
functions in the same targetlist is even stranger. The whole thing
is a mess and certainly not something we would've invented if we
hadn't inherited it from Berkeley.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] Not *quite* there on ecpg fixes
Michael Meskes wrote: On Thu, Oct 04, 2007 at 10:04:41AM -0400, Alvaro Herrera wrote: It looks like dlltool does not like the .def file: ... Line 3 of that file is unadorned EXPORTS: echo '; DEF file for MS VC++' libpgtypesdll.def echo 'LIBRARY LIBPGTYPES' libpgtypesdll.def echo 'EXPORTS' libpgtypesdll.def ... I wonder why the dlltool failure is not causing the build to fail immediately? These lines are simply copied from libpq/Makefile but ddltool does not complain while working on libpq. Any idea where they differ? Newline style perhaps? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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] First steps with 8.3 and autovacuum launcher
Gregory Stark wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Hmm, it looks like the race condition Heikki mentioned is the culprit. We need a way to stop future analyzes from starting. Back to the drawing board ... A crazy idea I just had -- what if you roll this into the deadlock check? So after waiting on the lock for 1s it wakes up, finds that the holder it's waiting on is an autovacuum process and cancels it instead of finding no deadlock. Another idea would be to make no changes to the relation_openrv call, but have autovacuum periodically check if anyone's blocked waiting on its locks, and commit suicide (or set cost delay to zero) if so. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(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] What does 'elapsed' exact means in PostgreSQL Log Statistics
Camilo, My Scenario is like this: PostgreSQL 7.4 with the following parameters: If you're testing performance, why are you using a 4-year-old version of PostgreSQL? -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Not *quite* there on ecpg fixes
Alvaro Herrera [EMAIL PROTECTED] writes: Michael Meskes wrote: These lines are simply copied from libpq/Makefile but ddltool does not complain while working on libpq. Any idea where they differ? Newline style perhaps? There seems to be a blank line at the end of interfaces/ecpg/pgtypeslib/exports.txt ... maybe that is producing a bogus line in the .def file? 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] tsearch2 wrapper
Hello I am working on subj. There are two variants: a) simple C wrapper which can be SQL compatible .. with this wrapper we don't need any changes in dump files .. but I thinking it's impossible, because there are conflicts between functio'n names and table's names. b) simple SQL wrapper - it need modification of dump file :(, but it can work (probably doesn't needs modify of application). I started work on variant b) http://www.pgsql.cz/index.php/Tsearch2_wrapper plpgsql procedures I'll rewrite to C later. Now I thing, so compatibility is broken, but we can simplify port to 8.3 with some small wrapper like my wrapper. Regards Pavel Stehule ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] What does 'elapsed' exact means in PostgreSQL Log Statistics
[Camilo Porto] From: [EMAIL PROTECTED] To: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] What does 'elapsed' exact means in PostgreSQL Log Statistics Date: Thu, 4 Oct 2007 08:43:24 -0700 CC: [EMAIL PROTECTED] Camilo, My Scenario is like this: PostgreSQL 7.4 with the following parameters: If you're testing performance, why are you using a 4-year-old version of PostgreSQL? The problem persist with PostgreSQL v 8.2 -- Josh Berkus PostgreSQL @ Sun San Francisco _ Veja mapas e encontre as melhores rotas para fugir do trânsito com o Live Search Maps! http://www.livemaps.com.br/index.aspx?tr=true
Re: [HACKERS] Not *quite* there on ecpg fixes
Michael Meskes wrote: On Thu, Oct 04, 2007 at 10:04:41AM -0400, Alvaro Herrera wrote: It looks like dlltool does not like the .def file: ... Line 3 of that file is unadorned EXPORTS: echo '; DEF file for MS VC++' libpgtypesdll.def echo 'LIBRARY LIBPGTYPES' libpgtypesdll.def echo 'EXPORTS' libpgtypesdll.def ... I wonder why the dlltool failure is not causing the build to fail immediately? These lines are simply copied from libpq/Makefile but ddltool does not complain while working on libpq. Any idea where they differ? The libpq exports.txt has spaces, not tabs, as separators. In fact, if you see the .def files you notice that the generated files for ecpg are all wrong. Simplest is to change tabs to spaces ... However I'm thinking that maybe it's better to change the sed line to consider both spaces and tabs in the regex. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Not *quite* there on ecpg fixes
On Thu, 4 Oct 2007, Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: It's still not working. Don't have time right now to diagnose why. For now, since Michael doesn't have a windows machine to play with, I have switched brown_bat's schedule so it runs HEAD 6 times a day (not exactly every 4 hours, the intervals vary between 3 and 4 1/2 hours). I wonder if we could get trout back in the rotation, too? Both trout and eel are permanently dead due to a drive failure. I can potentially setup new animals next week, but these are gone. Kris Jurka ---(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] First steps with 8.3 and autovacuum launcher
Gregory Stark escribió: Alvaro Herrera [EMAIL PROTECTED] writes: Hmm, it looks like the race condition Heikki mentioned is the culprit. We need a way to stop future analyzes from starting. Back to the drawing board ... A crazy idea I just had -- what if you roll this into the deadlock check? So after waiting on the lock for 1s it wakes up, finds that the holder it's waiting on is an autovacuum process and cancels it instead of finding no deadlock. Another crazy idea is to have some sort of blacklist of tables in shared memory. Any autovacuum process would skip those tables. My idea is that a would-be locker automatically puts the table in the blacklist, then kill autovacs, then press on. My idea is to accompany the relid with the Xid of the locker transaction, so the worker checks whether the transaction is still running, and removes the item from the blacklist if not. (The only problem then is figuring out how large a black list to have, and how to evict items when it is full and somebody else wants to blacklist another table. For pg_dump it is more than enough to have MaxBackends, since there is always at most one transaction, but I wouldn't be surprised if I'm overlooking something.) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
Alvaro Herrera [EMAIL PROTECTED] writes: Gregory Stark escribió: A crazy idea I just had -- what if you roll this into the deadlock check? So after waiting on the lock for 1s it wakes up, finds that the holder it's waiting on is an autovacuum process and cancels it instead of finding no deadlock. Another crazy idea is to have some sort of blacklist of tables in shared memory. Any autovacuum process would skip those tables. The deadlock check idea sounds promising to me, not least because it avoids adding any cycles in performance-critical paths. I'm not certain how easy it'd be to fold the idea into the checker though. That logic is pretty complicated :-( and I'm not sure that it makes a consistent effort to visit every possible blocker. 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] Not *quite* there on ecpg fixes
Michael Meskes wrote: On Thu, Oct 04, 2007 at 10:04:41AM -0400, Alvaro Herrera wrote: It looks like dlltool does not like the .def file: ... Line 3 of that file is unadorned EXPORTS: echo '; DEF file for MS VC++' libpgtypesdll.def echo 'LIBRARY LIBPGTYPES' libpgtypesdll.def echo 'EXPORTS' libpgtypesdll.def ... I wonder why the dlltool failure is not causing the build to fail immediately? These lines are simply copied from libpq/Makefile but ddltool does not complain while working on libpq. Any idea where they differ? One thing I noticed is this: ifneq ($(PORTNAME), win32) OBJS += thread.o DLL_DEFFILE=libecpgdll.def endif Why are we defining DLL_DEFFILE for the non-win32 case but not for the win32 case? That seems rather odd. cheers andrew ---(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] Not *quite* there on ecpg fixes
On Thu, Oct 04, 2007 at 12:35:29PM -0400, Alvaro Herrera wrote: The libpq exports.txt has spaces, not tabs, as separators. In fact, if you see the .def files you notice that the generated files for ecpg are all wrong. Right, that's it. Simplest is to change tabs to spaces ... However I'm thinking that maybe it's better to change the sed line to consider both spaces and tabs in the regex. I'm not sure how portable sed scripts containing tabs are, so I simply replaced the tabs in those export files by white spaces. Hopefully that'll do it. 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] uh-oh, dugong failing again
Gregory Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: 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? It seems to work with icc on my 32 bit intel cpu. Earlier you speculated that the struct might be getting padded out which would cause hash failures. But surely using a different padding from other compilers would be a compiler bug since it would be an incompatible ABI change. I find it hard to believe intel's compiler would get the ia64 ABI wrong. And hard to believe nobody's noticed an incompatible ABI from gcc-generated binaries. Well, I changed the Assert() to an explicit if-test-and-elog, and the failure seems to have gone away. So I'd say that makes it absolutely certainly an icc bug. Not clear what difference icc sees between an enabled Assert and an if/elog, but evidently there is one. 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] Not *quite* there on ecpg fixes
Michael Meskes [EMAIL PROTECTED] writes: On Thu, Oct 04, 2007 at 12:35:29PM -0400, Alvaro Herrera wrote: The libpq exports.txt has spaces, not tabs, as separators. In fact, if you see the .def files you notice that the generated files for ecpg are all wrong. Right, that's it. I see that libpq manufactures three different .def files, whereas the ecpg code is only making two. Is this OK or an oversight? I'm not clear on the reason for the two different MSVC .def files in libpq. Also, do we actually care about supporting Borland builds of ecpg --- maybe we don't need the 'b' versions for ecpg? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
Tom Lane escribió: Alvaro Herrera [EMAIL PROTECTED] writes: Gregory Stark escribi�: A crazy idea I just had -- what if you roll this into the deadlock check? So after waiting on the lock for 1s it wakes up, finds that the holder it's waiting on is an autovacuum process and cancels it instead of finding no deadlock. Another crazy idea is to have some sort of blacklist of tables in shared memory. Any autovacuum process would skip those tables. The deadlock check idea sounds promising to me, not least because it avoids adding any cycles in performance-critical paths. I'm not certain how easy it'd be to fold the idea into the checker though. That logic is pretty complicated :-( and I'm not sure that it makes a consistent effort to visit every possible blocker. The idea sounds interesting, but I am not at all sure how to fit it in the deadlock code. I am totally uninclined to mess with this stuff. I am barely aware of what exactly is it doing; I don't have the slightest idea how to modify it to cancel autovacs. Furthermore it sounds very much like a layering violation (what is deadlock.c doing with autovac processes anyway). -- Alvaro Herrerahttp://www.advogato.org/person/alvherre Pensar que el espectro que vemos es ilusorio no lo despoja de espanto, sólo le suma el nuevo terror de la locura (Perelandra, CSLewis) ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
On Thu, 2007-10-04 at 14:33 -0400, Alvaro Herrera wrote: Tom Lane escribió: Alvaro Herrera [EMAIL PROTECTED] writes: Gregory Stark escribi: A crazy idea I just had -- what if you roll this into the deadlock check? So after waiting on the lock for 1s it wakes up, finds that the holder it's waiting on is an autovacuum process and cancels it instead of finding no deadlock. Another crazy idea is to have some sort of blacklist of tables in shared memory. Any autovacuum process would skip those tables. The deadlock check idea sounds promising to me, not least because it avoids adding any cycles in performance-critical paths. I'm not certain how easy it'd be to fold the idea into the checker though. That logic is pretty complicated :-( and I'm not sure that it makes a consistent effort to visit every possible blocker. The idea sounds interesting, but I am not at all sure how to fit it in the deadlock code. I am totally uninclined to mess with this stuff. I'll look at it, if Greg isn't already doing so. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Not *quite* there on ecpg fixes
Andrew Dunstan [EMAIL PROTECTED] writes: One thing I noticed is this: ifneq ($(PORTNAME), win32) OBJS += thread.o DLL_DEFFILE=libecpgdll.def endif Why are we defining DLL_DEFFILE for the non-win32 case but not for the win32 case? Comparing to the libpq Makefile, this seems definitely backward --- presumably the result is that Makefile.shlib overwrites the deffile with an all-symbols deffile, eliminating the intended limitation on which symbols are exported. Will fix. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
Alvaro Herrera [EMAIL PROTECTED] writes: I am totally uninclined to mess with this stuff. I am barely aware of what exactly is it doing; I don't have the slightest idea how to modify it to cancel autovacs. Furthermore it sounds very much like a layering violation (what is deadlock.c doing with autovac processes anyway). I think any fix for this at all is going to qualify as a layering violation, so I'm not sure that that objection has merit. I hear you on the first point though. I used to know how the deadlock checker worked, let me see if I can swap that knowledge back in. This change isn't going to make beta1 anyway ... 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
[HACKERS] type money causes unrestorable dump
I noticed that if you create a dump on a database containing a money
column and a certain locale, this dump is not restorable on a database
with a different locale.
Most notably, I tried dumping the regression database (which is created
with --locale=C), and then importing it into a database of my own
creation, which uses --locale=fr_CA.UTF-8.
Due to the thousands separator being different, the regression database
is not restorable on the french database.
The regression test uses for input the unadorned form:
insert into rtest_emp values ('wiech', '5000.00');
But on output, pg_dump uses the localized form:
COPY money (a) FROM stdin;
$7 000,00
\.
Surely pg_dump should be using the unadorned form as well?
--
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
No hay ausente sin culpa ni presente sin disculpa (Prov. francés)
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] type money causes unrestorable dump
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: I noticed that if you create a dump on a database containing a money column and a certain locale, this dump is not restorable on a database with a different locale. We've been through this, no? Hmm, true. I even replied to the thread. I forgot. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] type money causes unrestorable dump
Alvaro Herrera [EMAIL PROTECTED] writes: I noticed that if you create a dump on a database containing a money column and a certain locale, this dump is not restorable on a database with a different locale. We've been through this, no? If money doesn't print that way, there's no obvious reason to have the type at all. Use numeric if you don't want something with locale-specific behavior. 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] First steps with 8.3 and autovacuum launcher
On Thu, 2007-10-04 at 19:40 +0100, Simon Riggs wrote: On Thu, 2007-10-04 at 14:33 -0400, Alvaro Herrera wrote: Tom Lane escribió: Alvaro Herrera [EMAIL PROTECTED] writes: Gregory Stark escribi: A crazy idea I just had -- what if you roll this into the deadlock check? So after waiting on the lock for 1s it wakes up, finds that the holder it's waiting on is an autovacuum process and cancels it instead of finding no deadlock. Another crazy idea is to have some sort of blacklist of tables in shared memory. Any autovacuum process would skip those tables. The deadlock check idea sounds promising to me, not least because it avoids adding any cycles in performance-critical paths. I'm not certain how easy it'd be to fold the idea into the checker though. That logic is pretty complicated :-( and I'm not sure that it makes a consistent effort to visit every possible blocker. The idea sounds interesting, but I am not at all sure how to fit it in the deadlock code. I am totally uninclined to mess with this stuff. I'll look at it, if Greg isn't already doing so. Seems like we don't need to mess with the deadlock checker itself. We can rely on the process at the head of the lock wait queue to sort this out for us. So all we need do is look at the isAutovacuum flag on the process that is holding the lock we're waiting on. If it isn't an autoANALYZE we can carry on with the main deadlock check. We just need a new kind of deadlock state to handle this, then let ProcSleep send SIGINT to the autoANALYZE and then go back to sleep, waiting to be reawoken when the auotANALYZE aborts. So it looks do-able without major horrors. -- 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] Connection Pools and DISCARD ALL
On Thu, 2007-10-04 at 13:03 -0700, Neil Conway wrote: On Thu, 2007-10-04 at 15:50 +0100, Simon Riggs wrote: On Thu, 2007-10-04 at 10:29 -0400, Tom Lane wrote: Somebody who wants the above behavior can send ROLLBACK; DISCARD ALL. ...which generates an ERROR if no transaction is in progress and fills the log needlessly. Well, it's a WARNING, but your point is taken. Can't a clueful interface just check what the transaction status of the connection is, rather than unconditionally issuing a ROLLBACK? I think it can, but can't a clueful server do this and avoid the problem of non-clueful interfaces? This is making me think that we should just embed the session pool inside the server as well and have done with it. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Connection Pools and DISCARD ALL
On Thu, 2007-10-04 at 15:50 +0100, Simon Riggs wrote: On Thu, 2007-10-04 at 10:29 -0400, Tom Lane wrote: Somebody who wants the above behavior can send ROLLBACK; DISCARD ALL. ...which generates an ERROR if no transaction is in progress and fills the log needlessly. Well, it's a WARNING, but your point is taken. Can't a clueful interface just check what the transaction status of the connection is, rather than unconditionally issuing a ROLLBACK? -Neil ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Connection Pools and DISCARD ALL
Simon Riggs wrote: On Thu, 2007-10-04 at 13:03 -0700, Neil Conway wrote: On Thu, 2007-10-04 at 15:50 +0100, Simon Riggs wrote: On Thu, 2007-10-04 at 10:29 -0400, Tom Lane wrote: Somebody who wants the above behavior can send ROLLBACK; DISCARD ALL. ...which generates an ERROR if no transaction is in progress and fills the log needlessly. Well, it's a WARNING, but your point is taken. Can't a clueful interface just check what the transaction status of the connection is, rather than unconditionally issuing a ROLLBACK? I think it can, but can't a clueful server do this and avoid the problem of non-clueful interfaces? This is making me think that we should just embed the session pool inside the server as well and have done with it. Could we maybe have some flavor of ROLLBACK that doesn't issue a warning if no transaction is in progress? There is precedent for this sort of facility - DROP ... IF EXISTS. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Connection Pools and DISCARD ALL
Simon Riggs wrote: This is making me think that we should just embed the session pool inside the server as well and have done with it. You mean prefork? That would be neat. I don't think it's all that impossible. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Connection Pools and DISCARD ALL
Andrew Dunstan [EMAIL PROTECTED] writes: Could we maybe have some flavor of ROLLBACK that doesn't issue a warning if no transaction is in progress? There is precedent for this sort of facility - DROP ... IF EXISTS. Something that would actually be doable for 8.3 would be to downgrade this particular WARNING to a NOTICE. A DBA who hasn't got log_min_messages set higher than NOTICE hasn't really got a lot of room to whine about bulky logs. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] ecpg build now breaks mingw
dllwrap -o libecpg.dll --dllname libecpg.dll --def libecpgdll.def execute.o typename.o descriptor.o data.o error.o prepare.o memory.o connect.o misc.o path.o strlcpy.o snprintf.o -L../pgtypeslib -L../../../../src/interfaces/libpq -L../../../../src/port -L/mingw/lib -lpgtypes -lpq -lm -lshfolder path.o(.text+0x741): In function `get_progname': C:/msys/1.0/local/pgbuildfarm/buildroot/HEAD/pgsql.836/src/interfaces/ecpg/ecpglib/path.c:413: undefined reference to `pg_strcasecmp' c:\MinGW\bin\dllwrap.exe: c:\MinGW\bin\gcc exited with status 1 I presume that this symbol was previously exported by the pgtypes lib but now we have an explicit exports list is no longer. Is it just a matter of adding a line to the exports.txt file? cheers andrew ---(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] ecpg build now breaks mingw
Andrew Dunstan [EMAIL PROTECTED] writes: dllwrap -o libecpg.dll --dllname libecpg.dll --def libecpgdll.def execute.o typename.o descriptor.o data.o error.o prepare.o memory.o connect.o misc.o path.o strlcpy.o snprintf.o -L../pgtypeslib -L../../../../src/interfaces/libpq -L../../../../src/port -L/mingw/lib -lpgtypes -lpq -lm -lshfolder path.o(.text+0x741): In function `get_progname': C:/msys/1.0/local/pgbuildfarm/buildroot/HEAD/pgsql.836/src/interfaces/ecpg/ecpglib/path.c:413: undefined reference to `pg_strcasecmp' I just fixed that. I presume that this symbol was previously exported by the pgtypes lib but now we have an explicit exports list is no longer. Is it just a matter of adding a line to the exports.txt file? Well, that would be the solution if we intended to make pg_strcasecmp part of libpgtype's official API, but that doesn't seem like a good plan at all. Instead, the right thing is for ecpglib to pull its own copy from src/port/. BTW, it looks like this dependency exists only on Cygwin/Win32, not other platforms, which is no doubt why we didn't see it before. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] 8.4 TODO item: make src/port support libpq and ecpg directly
This business with having libpq and ecpg pull in src/port modules manually is getting unmaintainable. I wonder whether we could persuade src/port to generate three versions of libpgport.a --- backend, frontend, and frontend-shlib-ready --- and then just -l the appropriate one in libpq and ecpg. This'd waste a few cycles building modules that would never be used, but on the other hand we'd buy some of that back by not building the same object files three or four times. 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] First steps with 8.3 and autovacuum launcher
Simon Riggs escribió:
Seems like we don't need to mess with the deadlock checker itself.
We can rely on the process at the head of the lock wait queue to sort
this out for us. So all we need do is look at the isAutovacuum flag on
the process that is holding the lock we're waiting on. If it isn't an
autoANALYZE we can carry on with the main deadlock check. We just need a
new kind of deadlock state to handle this, then let ProcSleep send
SIGINT to the autoANALYZE and then go back to sleep, waiting to be
reawoken when the auotANALYZE aborts.
Ok, I think this makes sense.
I can offer the following patch -- it makes it possible to determine
whether an autovacuum process is doing analyze or not, by comparing the
PGPROC of the running WorkerInfo list (the list has at most
max_autovacuum_workers entries, so this is better than trolling
ProcGlobal).
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Index: src/backend/postmaster/autovacuum.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/postmaster/autovacuum.c,v
retrieving revision 1.61
diff -c -p -r1.61 autovacuum.c
*** src/backend/postmaster/autovacuum.c 24 Sep 2007 04:12:01 - 1.61
--- src/backend/postmaster/autovacuum.c 4 Oct 2007 21:32:11 -
*** typedef struct autovac_table
*** 182,188
* wi_links entry into free list or running list
* wi_dboid OID of the database this worker is supposed to work on
* wi_tableoid OID of the table currently being vacuumed
! * wi_workerpid PID of the running worker, 0 if not yet started
* wi_launchtime Time at which this worker was launched
* wi_cost_* Vacuum cost-based delay parameters current in this worker
*
--- 182,189
* wi_links entry into free list or running list
* wi_dboid OID of the database this worker is supposed to work on
* wi_tableoid OID of the table currently being vacuumed
! * wi_proc pointer to PGPROC of the running worker, NULL if not started
! * wi_activity Type of task this worker is currently executing
* wi_launchtime Time at which this worker was launched
* wi_cost_* Vacuum cost-based delay parameters current in this worker
*
*** typedef struct autovac_table
*** 191,202
* that worker itself).
*-
*/
typedef struct WorkerInfoData
{
SHM_QUEUE wi_links;
Oid wi_dboid;
Oid wi_tableoid;
! int wi_workerpid;
TimestampTz wi_launchtime;
int wi_cost_delay;
int wi_cost_limit;
--- 192,211
* that worker itself).
*-
*/
+ typedef enum
+ {
+ AvActivityNone,
+ AvActivityVacuum,
+ AvActivityAnalyze
+ } AvActivity;
+
typedef struct WorkerInfoData
{
SHM_QUEUE wi_links;
Oid wi_dboid;
Oid wi_tableoid;
! PGPROC *wi_proc;
! AvActivity wi_activity;
TimestampTz wi_launchtime;
int wi_cost_delay;
int wi_cost_limit;
*** AutoVacLauncherMain(int argc, char *argv
*** 694,700
worker = (WorkerInfo) MAKE_PTR(AutoVacuumShmem-av_startingWorker);
worker-wi_dboid = InvalidOid;
worker-wi_tableoid = InvalidOid;
! worker-wi_workerpid = 0;
worker-wi_launchtime = 0;
worker-wi_links.next = AutoVacuumShmem-av_freeWorkers;
AutoVacuumShmem-av_freeWorkers = MAKE_OFFSET(worker);
--- 703,710
worker = (WorkerInfo) MAKE_PTR(AutoVacuumShmem-av_startingWorker);
worker-wi_dboid = InvalidOid;
worker-wi_tableoid = InvalidOid;
! worker-wi_proc = NULL;
! worker-wi_activity = AvActivityNone;
worker-wi_launchtime = 0;
worker-wi_links.next = AutoVacuumShmem-av_freeWorkers;
AutoVacuumShmem-av_freeWorkers = MAKE_OFFSET(worker);
*** do_start_worker(void)
*** 1198,1204
AutoVacuumShmem-av_freeWorkers = worker-wi_links.next;
worker-wi_dboid = avdb-adw_datid;
! worker-wi_workerpid = 0;
worker-wi_launchtime = GetCurrentTimestamp();
AutoVacuumShmem-av_startingWorker = sworker;
--- 1208,1215
AutoVacuumShmem-av_freeWorkers = worker-wi_links.next;
worker-wi_dboid = avdb-adw_datid;
! worker-wi_proc = NULL;
! worker-wi_activity = AvActivityNone;
worker-wi_launchtime = GetCurrentTimestamp();
AutoVacuumShmem-av_startingWorker = sworker;
*** AutoVacWorkerMain(int argc, char *argv[]
*** 1542,1548
{
MyWorkerInfo = (WorkerInfo) MAKE_PTR(AutoVacuumShmem-av_startingWorker);
dbid = MyWorkerInfo-wi_dboid;
! MyWorkerInfo-wi_workerpid = MyProcPid;
/* insert into the running list */
SHMQueueInsertBefore(AutoVacuumShmem-av_runningWorkers,
--- 1553,1559
{
MyWorkerInfo = (WorkerInfo) MAKE_PTR(AutoVacuumShmem-av_startingWorker);
dbid = MyWorkerInfo-wi_dboid;
! MyWorkerInfo-wi_proc = MyProc;
/* insert into the running list */
Re: [HACKERS] 8.4 TODO item: make src/port support libpq and ecpg directly
Tom Lane wrote: This business with having libpq and ecpg pull in src/port modules manually is getting unmaintainable. I wonder whether we could persuade src/port to generate three versions of libpgport.a --- backend, frontend, and frontend-shlib-ready --- and then just -l the appropriate one in libpq and ecpg. This'd waste a few cycles building modules that would never be used, but on the other hand we'd buy some of that back by not building the same object files three or four times. Works for me. cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] code documentation
Hi all I wonder if there is a PostgreSQL code documentation that may help in understanding the code. Regards Islam Hegazy ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] code documentation
Islam Hegazy escribió: Hi all I wonder if there is a PostgreSQL code documentation that may help in understanding the code. Yes. There is the developer's FAQ, then there is the internals chapter in the official docs, then there's the various README's sprinkled throughout the code, and finally there's the comments in the code itself. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] tsearch2 wrapper
On Thursday 04 October 2007 11:52, Pavel Stehule wrote:
Hello
I am working on subj. There are two variants:
a) simple C wrapper which can be SQL compatible .. with this wrapper
we don't need any changes in dump files .. but I thinking it's
impossible, because there are conflicts between functio'n names and
table's names.
the new built in stuff should all be in pg_catalog schema, and the old stuff
in public schema, so in theory it would work... example of conflict?
b) simple SQL wrapper - it need modification of dump file :(, but it
can work (probably doesn't needs modify of application).
I started work on variant b)
http://www.pgsql.cz/index.php/Tsearch2_wrapper
plpgsql procedures I'll rewrite to C later.
Now I thing, so compatibility is broken, but we can simplify port to
8.3 with some small wrapper like my wrapper.
thank goodness I've switched to putting tsearch in it's own schema, so I can
easily seperate it with pg_dump.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] tsearch2 wrapper
Robert Treat [EMAIL PROTECTED] writes: On Thursday 04 October 2007 11:52, Pavel Stehule wrote: a) simple C wrapper which can be SQL compatible .. with this wrapper we don't need any changes in dump files .. but I thinking it's impossible, because there are conflicts between functio'n names and table's names. the new built in stuff should all be in pg_catalog schema, and the old stuff in public schema, so in theory it would work... example of conflict? The problem is the pg_catalog entries would capture any unqualified names. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] default_text_search_config
When I run initdb -E EUC_JP --no-locale, I found following in my postgresql.conf: default_text_search_config = 'pg_catalog.english' The manual says: default_text_search_config (string) Selects the text search configuration that is used by those variants of the text search functions that do not have an explicit argument specifying the configuration. See Chapter 12 for further information. The built-in default is pg_catalog.simple, but initdb will initialize the configuration file with a setting that corresponds to the chosen lc_ctype locale, if a configuration matching that locale can be identified. So I thought the initial value for it should be pg_catalog.simple, rather than pg_catalog.english. If this is not a bug, what is the idea behind lc_ctype = C corresponds to 'pg_catalog.english'? When is pg_catalog.simple supposed to be used? -- Tatsuo Ishii SRA OSS, Inc. Japan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] code documentation
Also Doxygen docs are greatly help. You can generate them yourself for the latest or obtain them from doxygen.postgresql.org. I found doxygen and the FAQ internals to be most useful. Alvaro Herrera [EMAIL PROTECTED] wrote: Islam Hegazy escribi�: Hi all I wonder if there is a PostgreSQL code documentation that may help in understanding the code. Yes. There is the developer's FAQ, then there is the internals chapter in the official docs, then there's the various README's sprinkled throughout the code, and finally there's the comments in the code itself. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] default_text_search_config
Tatsuo Ishii [EMAIL PROTECTED] writes: When I run initdb -E EUC_JP --no-locale, I found following in my postgresql.conf: default_text_search_config = 'pg_catalog.english' The manual says: default_text_search_config (string) Selects the text search configuration that is used by those variants of the text search functions that do not have an explicit argument specifying the configuration. See Chapter 12 for further information. The built-in default is pg_catalog.simple, but initdb will initialize the configuration file with a setting that corresponds to the chosen lc_ctype locale, if a configuration matching that locale can be identified. So I thought the initial value for it should be pg_catalog.simple, rather than pg_catalog.english. If this is not a bug, what is the idea behind lc_ctype = C corresponds to 'pg_catalog.english'? When is pg_catalog.simple supposed to be used? Well, that documentation is correct as far as it goes; what it doesn't say is that initdb's mapping table explicitly maps C/POSIX locales to english. It seems like a reasonable default on this side of the water, but maybe I'm being too North-American-centric. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] default_text_search_config
Tatsuo Ishii [EMAIL PROTECTED] writes: When I run initdb -E EUC_JP --no-locale, I found following in my postgresql.conf: default_text_search_config = 'pg_catalog.english' The manual says: default_text_search_config (string) Selects the text search configuration that is used by those variants of the text search functions that do not have an explicit argument specifying the configuration. See Chapter 12 for further information. The built-in default is pg_catalog.simple, but initdb will initialize the configuration file with a setting that corresponds to the chosen lc_ctype locale, if a configuration matching that locale can be identified. So I thought the initial value for it should be pg_catalog.simple, rather than pg_catalog.english. If this is not a bug, what is the idea behind lc_ctype = C corresponds to 'pg_catalog.english'? When is pg_catalog.simple supposed to be used? Well, that documentation is correct as far as it goes; what it doesn't say is that initdb's mapping table explicitly maps C/POSIX locales to english. It seems like a reasonable default on this side of the water, but maybe I'm being too North-American-centric. Ok. Are you going to to add initdb's mapping table explicitly maps C/POSIX locales to english to the doc? If no, I can do that part. -- Tatsuo Ishii SRA OSS, Inc. Japan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] default_text_search_config
Tatsuo Ishii [EMAIL PROTECTED] writes: Well, that documentation is correct as far as it goes; what it doesn't say is that initdb's mapping table explicitly maps C/POSIX locales to english. It seems like a reasonable default on this side of the water, but maybe I'm being too North-American-centric. Ok. Are you going to to add initdb's mapping table explicitly maps C/POSIX locales to english to the doc? If no, I can do that part. Before we worry about documenting the behavior, are you happy about it? What could be done differently? I'm wondering if it makes any sense to consider the specified database encoding while making the text-search decision ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] default_text_search_config
Tatsuo Ishii [EMAIL PROTECTED] writes: Well, that documentation is correct as far as it goes; what it doesn't say is that initdb's mapping table explicitly maps C/POSIX locales to english. It seems like a reasonable default on this side of the water, but maybe I'm being too North-American-centric. Ok. Are you going to to add initdb's mapping table explicitly maps C/POSIX locales to english to the doc? If no, I can do that part. Before we worry about documenting the behavior, are you happy about it? What could be done differently? I'm wondering if it makes any sense to consider the specified database encoding while making the text-search decision ... For me the idea that a text-search configuration maps to a locale/language seems to be totally wrong. IMO an encoding/charset could include several languages and a text-search configuration should be mapped to an encoding/charset, rather than a language. Apparently this would not happen in the near future however. Good thing is, text-search english configuration can handle multibyte characters. So I can live with current text-search implementation. -- Tatsuo Ishii SRA OSS, Inc. Japan ---(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
