Rewriting pg_upgrade (was Re: [GENERAL] State of Beta 2)
On Sat, 2003-09-27 at 16:50, Nigel J. Andrews wrote: On Sat, 27 Sep 2003, Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: With all the discussion and pg_upgrade, I saw no one offer to work on it. Does someone want to convert it to Perl? I think that would be a better language than shell script for this purpose, and C is too low-level. The reason that it needs to be rewritten in C is that it needs access to internal stuff that the backend doesn't expose. (For example, the transaction counter, end-of-WAL pointer, etc.) I don't think Perl would offer anything except creating an entirely new dependency for Postgres. Also, C code would be easier to keep in sync with the backend code that accesses the same stuff. Isn't Perl pretty ubiquitous on Unix now, though? Except maybe Unixware True, but doing all that text manipulation is C is going to be very hard to do and maintain. What about using embedded perl? I've never done it before but the mention of it in manpages has flashed past my eyes a couple of times so I know it's possible. Did the discuss decide on what was required for this. Last I noticed was that there was a distinction being made between system and user tables but I don't recall seeing a 'requirements' summary. What about Perl w/ C modules? Of course, there's my favorite: Python. It's got a good facility for writing C modules, and I think it's better for writing s/w that needs to be constantly updated. (I swear, it's just circumstance that this particular .signature came up at this time, but it is apropos.) -- - Ron Johnson, Jr. [EMAIL PROTECTED] Jefferson, LA USA YODA: Code! Yes. A programmer's strength flows from code maintainability. But beware of Perl. Terse syntax... more than one way to do it...default variables. The dark side of code maintainability are they. Easily they flow, quick to join you when code you write. If once you start down the dark path, forever will it dominate your destiny, consume you it will. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Can't Build 7.3.4 on OS X
Eric Ridge [EMAIL PROTECTED] writes: I don't think the OS X 10.3 betas are readily available (I've payed to be in Apple's developer program), so if you don't have access to 10.3 but have some idea as to what would cause this problem with tas, I'll do whatever I can to help test. I have verified that CVS tip builds okay on 10.3 beta. I would recommend dropping the CVS-tip versions of s_lock.h and s_lock.c into the 7.3 source tree if you need to get 7.3 working on 10.3. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Can't Build 7.3.4 on OS X
On Sep 27, 2003, at 3:43 PM, Tom Lane wrote: Eric Ridge [EMAIL PROTECTED] writes: I don't think the OS X 10.3 betas are readily available (I've payed to be in Apple's developer program), so if you don't have access to 10.3 but have some idea as to what would cause this problem with tas, I'll do whatever I can to help test. I have verified that CVS tip builds okay on 10.3 beta. I would recommend dropping the CVS-tip versions of s_lock.h and s_lock.c into the 7.3 source tree if you need to get 7.3 working on 10.3. Using s_lock.c and .h from at least 7.4 works too. Were you ever able to figure out why 7.3.4 wouldn't build? eric ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Can't Build 7.3.4 on OS X
Eric B. Ridge [EMAIL PROTECTED] writes: Were you ever able to figure out why 7.3.4 wouldn't build? Didn't really look, since the 7.4 inlined version of TAS is a better solution anyway. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Can't Build 7.3.4 on OS X
Eric B.Ridge wrote: On Sep 27, 2003, at 3:43 PM, Tom Lane wrote: Eric Ridge [EMAIL PROTECTED] writes: I don't think the OS X 10.3 betas are readily available (I've payed to be in Apple's developer program), so if you don't have access to 10.3 but have some idea as to what would cause this problem with tas, I'll do whatever I can to help test. I have verified that CVS tip builds okay on 10.3 beta. I would recommend dropping the CVS-tip versions of s_lock.h and s_lock.c into the 7.3 source tree if you need to get 7.3 working on 10.3. Using s_lock.c and .h from at least 7.4 works too. Were you ever able to figure out why 7.3.4 wouldn't build? eric ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings if you execute 'select_gcc 3.1' as root it should change your default GCC to a compiler that works. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Can't Build 7.3.4 on OS X
if you execute 'select_gcc 3.1' as root it should change your default GCC to a compiler that works. That fixes the problems with -traditional-cpp v/s -no-cpp-precomp, but it doesn't fix: ld: Undefined symbol _tas eric ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: Rewriting pg_upgrade (was Re: [GENERAL] State of Beta 2)
On Sat, 27 Sep 2003, Ron Johnson wrote: Isn't Perl pretty ubiquitous on Unix now, though? Except maybe Unixware I know that Solaris now has it included by default ... ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: Rewriting pg_upgrade (was Re: [GENERAL] State of Beta 2)
perl ships on UnixWare (5.005, but that will change in UP3). LER --On Saturday, September 27, 2003 22:42:02 -0300 Marc G. Fournier [EMAIL PROTECTED] wrote: On Sat, 27 Sep 2003, Ron Johnson wrote: Isn't Perl pretty ubiquitous on Unix now, though? Except maybe Unixware I know that Solaris now has it included by default ... ---(end of broadcast)--- TIP 8: explain analyze is your friend -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 pgp0.pgp Description: PGP signature
Re: [GENERAL] State of Beta 2
$$$ -- I wasn't looking to purchase a programmer. :-) Well sometimes it takes money to get things done. Personally I don't see a big need for pg_upgrade but there was enough people making noise about it that it made sense to make the proposal. Several people did come back and offer to cough up a little bit but not enough to get the project done. My prefernce is to see all that work going into pg_dump, pg_dumpall and pg_restore. Sincerely, Joshua Drake ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Can't Build 7.3.4 on OS X
On Sep 27, 2003, at 7:41 PM, Tom Lane wrote: I'm not sure whether we are planning another 7.3 release or not. I'd like to push forward to a 7.4 release, myself. Do you have any idea when OS X 10.3 will be released? If it's further out than next month, we could probably plan that 7.4 will win the footrace. By no means is this official, cuz well, I ain't in the loop, but I recently read something about mid-late November. I suspect they'll want it out by the time all the G5's are shipped... and the 2x2gig started shipping on friday... well, at least mine did. eric ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] State of Beta 2
On Saturday 27 September 2003 09:45 pm, Joshua D. Drake wrote: $$$ -- I wasn't looking to purchase a programmer. :-) Well sometimes it takes money to get things done. Personally I don't see a big need for pg_upgrade but there was enough people making noise about it that it made sense to make the proposal. Several people did come back and offer to cough up a little bit but not enough to get the project done. I could always forward you my fan mail (context for the following message is that I was extolling the group of people that help me build the various RPM sets as an example of how backports of Fedora Core packages could be done to 'Fedora Legacy' stuff (many thanks to those who help me, BTW.)): === Re: I volunteer From: Chuck Wolber [EMAIL PROTECTED] To: [EMAIL PROTECTED] I as PostgreSQL RPM maintainer for the PostgreSQL Global Development Group do something similar to this using a loose group of volunteers. TROLL Ahhh, so you're the one. Perhaps you could write a postgreSQL RPM with upgrade functionality that actually works? /TROLL -Chuck -- Quantum Linux Laboratories - ACCELERATING Business with Open Technology * Education | -=^ Ad Astra Per Aspera ^=- * Integration| http://www.quantumlinux.com * Support| [EMAIL PROTECTED] = You know, I don't mind owning up to my own bugs. But this bug ain't mine. -- Lamar Owen Director of Information Technology Pisgah Astronomical Research Institute 1 PARI Drive Rosman, NC 28772 (828)862-5554 www.pari.edu ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: Rewriting pg_upgrade (was Re: [GENERAL] State of Beta 2)
On Sat, 27 Sep 2003, Larry Rosenman wrote: perl ships on UnixWare (5.005, but that will change in UP3). In what way? :) It won't ship anymore ... or upgraded? LER --On Saturday, September 27, 2003 22:42:02 -0300 Marc G. Fournier [EMAIL PROTECTED] wrote: On Sat, 27 Sep 2003, Ron Johnson wrote: Isn't Perl pretty ubiquitous on Unix now, though? Except maybe Unixware I know that Solaris now has it included by default ... ---(end of broadcast)--- TIP 8: explain analyze is your friend -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: Rewriting pg_upgrade (was Re: [GENERAL] State of Beta 2)
--On Sunday, September 28, 2003 00:14:18 -0300 Marc G. Fournier [EMAIL PROTECTED] wrote: On Sat, 27 Sep 2003, Larry Rosenman wrote: perl ships on UnixWare (5.005, but that will change in UP3). In what way? :) It won't ship anymore ... or upgraded? upgraded to 5.8.0 (sorry, should have been more clear :-)) LER --On Saturday, September 27, 2003 22:42:02 -0300 Marc G. Fournier [EMAIL PROTECTED] wrote: On Sat, 27 Sep 2003, Ron Johnson wrote: Isn't Perl pretty ubiquitous on Unix now, though? Except maybe Unixware I know that Solaris now has it included by default ... ---(end of broadcast)--- TIP 8: explain analyze is your friend -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 pgp0.pgp Description: PGP signature
Re: [GENERAL] State of Beta 2
Ron Johnson wrote: There's always the general point that C has more pitfalls (mainly from pointers/free()/malloc(), and HLLs do more for you, thus you have to code less, and, consequently, there are fewer bugs. Someday, they're going to make a langauge called: CBC, C Bounds Checked No buffer overflows, all memory allocs and mallocs create a memory object that self expands or contracts as necessary, or issues an exception if it tries to go past a limit you put as an argumen to a malloc. With gigabytes of real memory and 100 gigibytes plus of virtual memory, the programmer should not handle memory management any more. The consumers and software users expect programmers to give up their pride and let go of total control of the memory model, (like they have it now ). The only excetion might be hardware drivers. Nobody say C#, OK? An Msoft imposed solution that integrates all their products, mistakes, football stadium sized APIs, and private backdoors is not the answer. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Tuning/performance question.
We are doing some performance testing among various databases (Oracle, MySQL and Postgres). One of the queries is showing Postgres lagging quite a bit: SELECT count(*) FROM commercial_entity, country, user_account, address_list LEFT JOIN state_province ON address_list.state_province_id = state_province.state_province_id LEFT JOIN contact_info ON address_list.contact_info_id = contact_info.contact_info_id WHERE address_list.address_type_id = 101 AND commercial_entity.commercial_entity_id = address_list.commercial_entity_id AND address_list.country_id = country.country_id AND commercial_entity.user_account_id = user_account.user_account_id AND user_account.user_role_id IN (101, 101); I ran a vacuum analyze after realizing that I had loaded all the data into the database without redoing the statistics; the query jumped from 19 seconds to 41 seconds _after_ the analyze. I'd also like to make sure my query is performing correctly - I want all the count of records where the commercial_entity matches user_account, address_list, country, and a left-outer-join on address_list-province and address_list-contact_info. Finally, I read some posts on the shared_buffers; they stated that the shared_buffers should be set to 1/4 to 1/5 of total memory available. Is that correct? I give the MySQL/InnoDB buffers about 70% of the 2 gig on the machine. Here's the explain (I'm not too familiar with reading a Postgres explain...): Aggregate (cost=52951.09..52951.09 rows=1 width=116) - Merge Join (cost=52941.61..52950.83 rows=105 width=116) Merge Cond: (outer.country_id = inner.country_id) - Index Scan using country_pkey on country (cost=0.00..7.54 rows=231 width=11) - Sort (cost=52941.61..52941.88 rows=105 width=105) Sort Key: address_list.country_id - Merge Join (cost=52729.54..52938.07 rows=105 width=105) Merge Cond: (outer.commercial_entity_id = inner.commercial_entity_id) - Sort (cost=8792.01..8792.52 rows=201 width=36) Sort Key: commercial_entity.commercial_entity_id - Nested Loop (cost=0.00..8784.31 rows=201 width=36) - Index Scan using usr_acc_usr_role_id_i on user_account (cost=0.00..2403.08 rows=1401 width=12) Index Cond: (user_role_id = 101::numeric) - Index Scan using comm_ent_usr_acc_id_i on commercial_entity (cost=0.00..4.54 rows=1 width=24) Index Cond: (commercial_entity.user_account_id = outer.user_account_id) - Sort (cost=43937.53..44173.84 rows=94526 width=69) Sort Key: address_list.commercial_entity_id - Merge Join (cost=29019.03..32585.73 rows=94526 width=69) Merge Cond: (outer.contact_info_id = inner.contact_info_id) - Index Scan using contact_info_pkey on contact_info (cost=0.00..3366.76 rows=56435 width=12) - Sort (cost=29019.03..29255.34 rows=94526 width=57) Sort Key: address_list.contact_info_id - Merge Join (cost=16930.18..18354.55 rows=94526 width=57) Merge Cond: (outer.state_province_id = inner.state_province_id) - Index Scan using state_province_pkey on state_province (cost=0.00..3.81 rows=67 width=11) - Sort (cost=16930.18..17166.50 rows=94526 width=46) Sort Key: address_list.state_province_id - Seq Scan on address_list (cost=0.00..6882.52 rows=94526 width=46) Filter: (address_type_id = 101::numeric) What's the Sort (cost...)? I noticed that joining the address_list to country was slow; there was no index on just country_id; there were composite indexes on multiple columns, so I added one and did a vacuum analyze on the table, and got: Aggregate (cost=54115.74..54115.74 rows=1 width=116) - Merge Join (cost=54105.91..54115.46 rows=109 width=116) Merge Cond: (outer.country_id = inner.country_id) - Index Scan using country_pkey on country (cost=0.00..7.54 rows=231 width=11) - Sort (cost=54105.91..54106.19 rows=110 width=105) Sort Key: address_list.country_id - Merge Join (cost=53884.34..54102.18 rows=110 width=105) Merge Cond: (outer.commercial_entity_id = inner.commercial_entity_id)
Re: [GENERAL] State of Beta 2
On Sat, 2003-09-27 at 22:19, Dennis Gearon wrote: Ron Johnson wrote: There's always the general point that C has more pitfalls (mainly from pointers/free()/malloc(), and HLLs do more for you, thus you have to code less, and, consequently, there are fewer bugs. Someday, they're going to make a langauge called: CBC, C Bounds Checked No buffer overflows, all memory allocs and mallocs create a memory object that self expands or contracts as necessary, or issues an exception if it tries to go past a limit you put as an argumen to a malloc. With gigabytes of real memory and 100 gigibytes plus of virtual memory, the programmer should not handle memory management any more. The consumers and software users expect programmers to give up their pride and let go of total control of the memory model, (like they have it now ). The only excetion might be hardware drivers. Some would say that that's what Java and C++ are for. I'd do more Java programming if it didn't have an API the size of Montana, no make that Alaska and a good chunk of Siberia. But still, multiple pointers being able to point to the same chunk of the heap will doom any solution to inefficiency. IMNSHO, only the kernel and *high-performance* products should be written in C. Everything else should be written in HLLs. Anything from COBOL (still a useful language), FORTRAN, modern BASICs, to pointer-less Pascal, Java, Smalltalk, Lisp, and scripting languages. Note that I did *not* mention C++. Nobody say C#, OK? An Msoft imposed solution that integrates all their products, mistakes, football stadium sized APIs, and private backdoors is not the answer. natch! -- - Ron Johnson, Jr. [EMAIL PROTECTED] Jefferson, LA USA they love our milk and honey, but preach about another way of living Merle Haggard, The Fighting Side Of Me ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: Rewriting pg_upgrade (was Re: [GENERAL] State of Beta 2)
Ron Johnson [EMAIL PROTECTED] writes:
Tom Lane wrote:
The reason that it needs to be rewritten in C is that it needs access to
internal stuff that the backend doesn't expose. (For example, the
transaction counter, end-of-WAL pointer, etc.) I don't think Perl would
offer anything except creating an entirely new dependency for Postgres.
Also, C code would be easier to keep in sync with the backend code that
accesses the same stuff.
What about Perl w/ C modules? Of course, there's my favorite: Python.
Fwiw, it's pretty easy to call out to C functions from perl code these days.
bash-2.05b$ perl -e 'use Inline C = int a(int i,int j) { return i+j;};
print(a(1,2),\n)'
3
That said I don't know if this is really such a good approach. I don't see why
you would need much string manipulation at all. The C code can just construct
directly whatever data structures it needs and call directly whatever
functions it needs. Doing string manipulation to construct dynamic sql code
and then hope it gets interpreted and executed the way it's expecting seems a
roundabout way to go about getting things done.
--
greg
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
