[GENERAL] About PostgreSQL Installer
Hi all, I want to make an installer like postgresql-8.3.msi and postgresql-8.3-int.msi. I want after one msi runs, another msi will run. I want to ask where can I find sources of these msi installers. I have found postgresql-8.3-int.msi wix sources but I couldn't find postgresql-8.3.msi sources. I decompiled them but they are not useful much. Thanks for reading _ Windows Live Messenger'ın için Ücretsiz 30 İfadeyi yükle http://www.livemessenger-emoticons.com/funfamily/tr-tr/
Re: [GENERAL] Rollback of Query Cancellation
Well Jaime, I executed all the delete queries one by one. Now I am unable to understand that why it took sufficient time to run the last query before cancellation. i.e. delete from ci_cif_v where req_id='0824100207'; This morning it performed the delete operation and deleted certain number of rows within few seconds. Then what it was doing yesterday. Here it seems that it was performing something else (as guessed by Sim Zacks) before the execution of delete query. And when I canceled, the query was not executed till that time. Then why it didn't take sufficient amount of time to perform something else before the execution of same query today. From: Jaime Casanova jcasa...@systemguards.com.ec To: Abdul Rahman abr_...@yahoo.com Cc: pgsql-general@postgresql.org Sent: Wednesday, January 28, 2009 12:20:41 PM Subject: Re: Rollback of Query Cancellation On Wed, Jan 28, 2009 at 2:10 AM, Abdul Rahman abr_...@yahoo.com wrote: Thanks Jaime, Plz consider the actual log files to explore the issue in detail. Because I have pasted the log files of client machine, I am using (sorry). 2009-01-27 18:29:25 STATEMENT: delete from ci_cin_type_v where req_id='0824100207' delete from ci_cust_type_v where req_id='0824100207' delete from ci_dependents_v where req_id='0824100207' delete from ci_employer_v where req_id='0824100207' delete from ci_cor_sig_v where req_id='0824100207' delete from ci_corporate_v where req_id='0824100207' delete from ci_individual_v where req_id='0824100207' delete from ci_cif_v where req_id='0824100207' then you have a lot of deletes, are there executing inside a transaction? are you calling a trigger? 2009-01-27 18:29:41 ERROR: relation ci_cust_type_v does not exist this table does not exist 2009-01-27 18:29:41 STATEMENT: delete from ci_cust_type_v where req_id='0824100207' 2009-01-27 18:52:08 LOG: could not receive data from client: No connection could be made because the target machine actively refused it. 2009-01-27 18:52:08 LOG: unexpected EOF on client connection 2009-01-27 18:52:08 LOG: could not receive data from client: No connection could be made because the target machine actively refused it. 2009-01-27 18:52:08 LOG: unexpected EOF on client connection guess this messages are received after the CANCEL QUERY if the series of deletes are all executed inside a transaction then they all were rollback if not only the last one (the one that generates the error) was rolledback -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157
[GENERAL] Feature request dblink: Security issue - dblink user+password parameters must be optional
When creating a view via DBLINK, the user=... and password=... parameters shall be optional. If they are left out, then the current user accessing the view shall be impersonated implicitely to the dblinked database as well. Forcing anybody to hardcode a password readable within the view definition should be an absolute DON'T! Haven't found a better place to post this request. Hope the author of dblink is reading it here, too. :-) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rollback of Query Cancellation
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Abdul Rahman wrote: Very nice! Most probably it was waiting for something else. This is the reason the query executed today and clearly showed certain number of rows deleted. But what ELSE? It could be a ton of things, but as an example, if you have a cursor open on a row that your query wants to delete. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkmADtsACgkQjDX6szCBa+onTQCgoEKG1CsZXfmmYLBW29gvHoOb kv8AoNzW1MHOUuPM0bOISTn/ppnuuyXM =CFb7 -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature request dblink: Security issue - dblink user+password parameters must be optional
On 1/28/09, Hermann Muster hermann.mus...@gmx.de wrote: When creating a view via DBLINK, the user=... and password=... parameters shall be optional. If they are left out, then the current user accessing the view shall be impersonated implicitely to the dblinked database as well. Forcing anybody to hardcode a password readable within the view definition should be an absolute DON'T! Haven't found a better place to post this request. Hope the author of dblink is reading it here, too. :-) I think this will be properly fixed by SQL-MED connection handling in 8.4. In older version maybe you can use wrapper function around dblink that constructs per-user connect string. -- marko -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] About PostgreSQL Installer
2009/1/28 fatih arıkan fthari...@hotmail.com: Hi all, I want to make an installer like postgresql-8.3.msi and postgresql-8.3-int.msi. I want after one msi runs, another msi will run. I want to ask where can I find sources of these msi installers. I have found postgresql-8.3-int.msi wix sources but I couldn't find postgresql-8.3.msi sources. I decompiled them but they are not useful much. Source code for both can be found at http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pginstaller/pginst/. postgresql-8.3.msi is the wrapper and can be found in the wrapper/ directory. Most of the rest of the source tree makes up the main msi. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Killing an offending process without restarting Postgresql safe?
Hi, I added an index on a Postgresql 7.4 database this morning (about 50 concurrent users), and then deleted it. I noticed the delete took too much time to complete, so I logged on the server, and did a ps -afxu. I saw a process was stuck in a drop index waiting state, and several others in a select waiting state. I killed the drop index waiting process, and all the select waiting processes disappeared. Then I dropped the index again, and it succeeded this time. I never restarted the database server at any time. Is that safe, or is it a good thing to restart the server in such cases? Best regards, Philippe Lang -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL for a mission critical system
Greetings everyone, I'm new to this list (No, I'm not about to ask how to install pgsql on Windows 3.11!) We are involved with a financial project, similar to Paypal (Web Payments Standard/Payment Buttons) but the scope and functionality is much narrower. This project will be released in a market niche that will not exceed thirty thousand (3) users in the very best (and very optimistic) of scenarios and will surely have a much lower transaction/user/day ratio than Paypal. Our development department has to take all the technical decisions (what RDBMS to use, what additional personnel is required and what equipment to acquire/rent), and at the head of those decisions is, unfortunately, me. We have vast experience in MySQL, though not in large-scale or mission critical database setups. We have reviewed some MySQL specific use cases and whitepapers and have done intensive research (Paggo is a highlighted use case here, since it's similar though much bigger). However, I refuse to take the bait and just follow the propaganda. I've been working with PostgreSQL a few (very few) times before and I believe it's superior, TCO is much better (since you hire expertise and not licenses), but I don't know how easy it can be to scale up/out and if it can handle the most optimistic scenario (optimistic from the business point of view, for me it will be a pain in the ...). In addition, we have to design this application in such a way that it can be scaled up/out to the presumed limit (3 users) by just upgrading hardware, acquiring new hardware, or modifying the hardware setup. We don't want to redesign the application or modify it heavily once launched, though some tune up or bug fix will surely happen. We have our way to deal with this, we've planned for some front-end web applications (written in PHP, with caching and acceleration) and a back-end which is responsible for all the database management (probably written in Java and C/C++, we haven't decided yet). The front-end and back-end would communicate by means of a web services API (say SOAP, or the more lightweight XML-RPC), this will allow us to scale the front-end and back-end parts separately. This system could grow to more than 3 users (say, to a million) in the very far future, but for that to happen, this pilot test should be a complete success (which means our client pocket would be full of money) and the market niche should have to be extended. Meaning, if we need to jump higher, we can redesign our application and/or switch to another database/programming technology, such as Oracle (which I strongly hate :@). However, it's more likely that if we succeed, you find me again on this list looking for arguments to adopt PostgreSQL :D. What I would like to know (if you are so kind ;) ) is: 1) Do you really think that we can start low (say, a master/master or master/slave replication system on commodity servers) and jump high (say, upgrading to Opteron 8300 servers)? That is... how much can we scale up, without the need to scale out? 2) Do you know of any Hosting/Collocation service that can provide PostgreSQL installation/configuration support at an affordable price? (This is very important in the early stages). I've seen the hosting list at postgresql.org, but if possible, please share your experiences. 3) Any articles, benchmarking, white papers, ebooks, seminars, personal experience, use cases that you can recommend about this? (Yes, I've downloaded the postgresql.org use cases and I can use Google, but your voice is louder, specially if it speaks for your experience) 4) Any other comments are welcome. Thanks in advance (and sorry if I have bothered you in any way), Andrés Robinet | Lead Developer | BESTPLACE CORPORATION 5100 Bayview Drive 206, Royal Lauderdale Landings, Fort Lauderdale, FL 33308 | TEL 954-607-4296 | FAX 954-337-2695 | Email: i...@bestplace.net | MSN Chat: b...@bestplace.net | SKYPE: bestplace | Web: bestplace.biz | Web: seo-diy.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Slow first query despite LIMIT and OFFSET clause
On Wed, Jan 28, 2009 at 2:37 AM, Alban Hertroys dal...@solfertje.student.utwente.nl wrote: Does that query plan look any better without the select count(id) from testimonials? If so you may be better off keeping track of those counts in a separate table updated by triggers on the testimonials table. Whether that really helps depends on how variable your selectors are to determine those counts. If those counts are generally very low the benefit will probably be minimal. Thanks Alban. We have now made all the triggers and such. That part is working. I suppose not having the count(id) is helping just with a few seconds, but the query is still taking about 15 seconds in some cases. Here are the query and its exec plan again fyi. Any other ideas for tweaking? explain analyze SELECT testimonials.url ,testimonials.alias ,testimonials.aliasEntered ,testimonials.title ,testimonials.modify_date ,testimonials.id ,visitcount.visit_count ,visitcount.unique_count ,visitcount.modify_date ,coalesce( extract(epoch from now()) - extract(epoch from visitcount.modify_date), 0) ,(select count(id) from testimonials WHERE testimonials.user_id = 'superman' and testimonials.user_known = 1 and testimonials.status = 'Y' ) AS total FROM testimonials LEFT JOIN visitcount ON testimonials.id = visitcount.id WHERE testimonials.user_id = 'superman' and testimonials.user_known = 1 and testimonials.status = 'Y' ORDER BY testimonials.modify_date desc OFFSET 0 LIMIT 10 ; QUERY PLAN Limit (cost=224.68..224.71 rows=10 width=187) (actual time=453.429..453.539 rows=10 loops=1) InitPlan - Aggregate (cost=63.52..63.53 rows=1 width=8) (actual time=89.268..89.271 rows=1 loops=1) - Index Scan using new_idx_userknown on testimonials (cost=0.00..63.41 rows=42 width=8) (actual time=0.039..49.968 rows=10149 loops=1) Index Cond: ((user_id)::text = 'superman'::text) Filter: (status = 'Y'::bpchar) - Sort (cost=161.16..161.26 rows=42 width=187) (actual time=453.420..453.464 rows=10 loops=1) Sort Key: testimonials.modify_date - Nested Loop Left Join (cost=0.00..160.02 rows=42 width=187) (actual time=89.384..395.008 rows=10149 loops=1) - Index Scan using new_idx_userknown on testimonials (cost=0.00..63.41 rows=42 width=171) (actual time=0.061..50.990 rows=10149 loops=1) Index Cond: ((user_id)::text = 'superman'::text) Filter: (status = 'Y'::bpchar) - Index Scan using visitcount_pkey1 on visitcount (cost=0.00..2.28 rows=1 width=24) (actual time=0.007..0.010 rows=1 loops=10149) Index Cond: (testimonials.id = visitcount.id) Total runtime: 461. 682 ms (15 rows) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PG's suitability for high volume environment (many INSERTs and lots of aggregation reporting)
Hi. Further to my bafflement with the count(*) queries as described in this thread: http://archives.postgresql.org/pgsql-general/2009-01/msg00804.php It seems that whenever this question has come up, Postgresql comes up very short in terms of count(*) functions. The performance is always slow, because of the planner's need to guess and such. I don't fully understand how the statistics work (and the explanation on the PG website is way too geeky) but he columns I work with already have a stat level of 100. Not helping at all. We are now considering a web based logging functionality for users of our website. This means the table could be heavily INSERTed into. We get about 10 million hits a day, and I'm guessing that we will have to keep this data around for a while. My question: with that kind of volume and the underlying aggregation functions (by product id, dates, possibly IP addresses or at least countries of origin..) will PG ever be a good choice? Or should I be looking at some other kind of tools? I wonder if OLAP tools would be overkill for something that needs to look like a barebones version of google analytics limited to our site.. Appreciate any thoughts. If possible I would prefer to tone down any requests for MySQL and such! Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PG's suitability for high volume environment (many INSERTs and lots of aggregation reporting)
Phoenix Kiula phoenix.ki...@gmail.com writes: My question: with that kind of volume and the underlying aggregation functions (by product id, dates, possibly IP addresses or at least countries of origin..) will PG ever be a good choice? Well, only you're able to judge that for your own data and use cases. Your query is sorting 10,000 records in half a second which is not great but not terrible either. I think the only way you'll be able to speed that up is by changing your index design so that Postgres can access the data you need without sorting through all the irrelevant records. I suspect others already suggested this, but you might look at partial indexes. If your queries are very dynamic against relatively static data you might look at building denormalized caches of the precalculated data. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] New 8.4 hot standby feature
Fujii Masao wrote: Hi, On Wed, Jan 28, 2009 at 4:28 AM, Gabi Julien gabi.jul...@broadsign.com wrote: Yes, the logs are shipped every minute but the recevory is 3 or 4 times longer. Are you disabling full_page_writes? It may slow down recovery several times. Thanks I will take a look at it. Also, I came across the record log shipping feature too in my research: http://www.postgresql.org/docs/current/static/warm-standby.html#WARM-STANDBY-RECORD Could this help? If the logs are smaller then I could potentially afford shipping then at a higher frequency. No. Even if the logs are shipped frequently, they cannot be applied until the log file fills. Regards, Is pg_clearxlogtail http://www.2ndquadrant.com/code/pg_clearxlogtail.c going to be in contrib or integrated in some other way?
Re: [GENERAL] New 8.4 hot standby feature
Hi, On Thu, Jan 29, 2009 at 12:23 AM, Jason Long mailing.l...@supernovasoftware.com wrote: Is pg_clearxlogtail going to be in contrib or integrated in some other way? I also hope so. The related topic was discussed before. http://archives.postgresql.org/pgsql-hackers/2009-01/msg00639.php Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [lapug] Seeking volunteers for SCALE (one more time)
On Tue, Jan 27, 2009 at 5:49 PM, Noel Proffitt no...@calpacs.org wrote: I'm available to volunteer for a couple of hours or so. Let me know what time slots you're looking to cover. Thanks Noel. The schedule will be pretty flexible. Any times between Saturday and Sunday will be fine. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] possible pg_dump bug
Pg Dump does not include schema name on insert statement generated from pg_dump with a -d option when exporting data for a particular table using the -t schema.table in version 8.3. I believe this same bug exists in 8.4 but have not confirmed it. I believe pg_dump should have an option to retain schema information. David Miller River Systems, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] possible pg_dump bug
On 28/01/2009 17:09, David Miller wrote: Pg Dump does not include schema name on insert statement generated from pg_dump with a -d option when exporting data for a particular table using the -t schema.table in version 8.3. I believe this same bug exists in 8.4 but have not confirmed it. I believe pg_dump should have an option to retain schema information. From memory, I think it does include a set search_path statement near the beginning. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] possible pg_dump bug
On Wed, 2009-01-28 at 09:09 -0800, David Miller wrote: Pg Dump does not include schema name on insert statement generated from pg_dump with a -d option when exporting data for a particular table using the -t schema.table in version 8.3. I believe this same bug exists in 8.4 but have not confirmed it. I believe pg_dump should have an option to retain schema information. would the option --clean solve this problem ? pg_dump --clean David Miller River Systems, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] possible pg_dump bug
Raymond O'Donnell r...@iol.ie writes: On 28/01/2009 17:09, David Miller wrote: Pg Dump does not include schema name on insert statement generated from pg_dump with a -d option when exporting data for a particular table using the -t schema.table in version 8.3. I believe this same bug exists in 8.4 but have not confirmed it. I believe pg_dump should have an option to retain schema information. From memory, I think it does include a set search_path statement near the beginning. Quite. There is no bug here unless loading the dump file puts something in the wrong schema. (Suppressing the schema name where possible is in fact considered a feature not a bug, since it makes it simpler to hand-edit the dump file at need.) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Slow first query despite LIMIT and OFFSET clause
On Jan 28, 2009, at 1:38 PM, Phoenix Kiula wrote: Thanks Alban. We have now made all the triggers and such. That part is working. I suppose not having the count(id) is helping just with a few seconds, but the query is still taking about 15 seconds in some cases. Here are the query and its exec plan again fyi. Any other ideas for tweaking? Ah I see, that's the original query and its plan again, not the one after implementing those triggers! You had me scratching my head for a bit there, wondering why the count() subquery was still there. A few things in this query appear to take relatively much time: - The index scans on new_idx_userknown; What's worrying there is that the planner expects only a few rows (42) while in actuality they are quite many (10149). This scan is performed twice too! It seems that the statistics that index uses are off. That may mean changing the statistics on the columns involved or increasing the frequency that autovacuum visits them. - The nested loop left join is expensive; That's probably also due to the incorrect assumptions the planner makes about the index scans I mentioned above. It expects to have to loop 42 times, but ends up doing so 10149 times instead! I believe loops aren't particularly efficient, they'll only beat other methods if there are few rows to loop through. The loop is taking 395-89 = 306 ms for 10149 rows, while the planner expected it to take 306 * (42/10149) = 1.3 ms. Quite a difference! You probably need to do something about new_idx_userknown. A partial index (as suggested elsewhere) may help make it smaller (easier to fit in RAM, fewer branches required to find a node), but the bad statistics are likely to be the real problem here. Without knowing anything about that particular index and the tables it's indexing it's hard to tell how to improve it. explain analyze SELECT testimonials.url ,testimonials.alias ,testimonials.aliasEntered ,testimonials.title ,testimonials.modify_date ,testimonials.id ,visitcount.visit_count ,visitcount.unique_count ,visitcount.modify_date ,coalesce( extract(epoch from now()) - extract(epoch from visitcount.modify_date), 0) ,(select count(id) from testimonials WHERE testimonials.user_id = 'superman' and testimonials.user_known = 1 and testimonials.status = 'Y' ) AS total FROM testimonials LEFT JOIN visitcount ON testimonials.id = visitcount.id WHERE testimonials.user_id = 'superman' and testimonials.user_known = 1 and testimonials.status = 'Y' ORDER BY testimonials.modify_date desc OFFSET 0 LIMIT 10 ; QUERY PLAN Limit (cost=224.68..224.71 rows=10 width=187) (actual time=453.429..453.539 rows=10 loops=1) InitPlan - Aggregate (cost=63.52..63.53 rows=1 width=8) (actual time=89.268..89.271 rows=1 loops=1) - Index Scan using new_idx_userknown on testimonials (cost=0.00..63.41 rows=42 width=8) (actual time=0.039..49.968 rows=10149 loops=1) Index Cond: ((user_id)::text = 'superman'::text) Filter: (status = 'Y'::bpchar) - Sort (cost=161.16..161.26 rows=42 width=187) (actual time=453.420..453.464 rows=10 loops=1) Sort Key: testimonials.modify_date - Nested Loop Left Join (cost=0.00..160.02 rows=42 width=187) (actual time=89.384..395.008 rows=10149 loops=1) - Index Scan using new_idx_userknown on testimonials (cost=0.00..63.41 rows=42 width=171) (actual time=0.061..50.990 rows=10149 loops=1) Index Cond: ((user_id)::text = 'superman'::text) Filter: (status = 'Y'::bpchar) - Index Scan using visitcount_pkey1 on visitcount (cost=0.00..2.28 rows=1 width=24) (actual time=0.007..0.010 rows=1 loops=10149) Index Cond: (testimonials.id = visitcount.id) Total runtime: 461. 682 ms (15 rows) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4980a309747032541118883! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Fwd: Re: [GENERAL] New 8.4 hot standby feature
On Tuesday 27 January 2009 16:25:44 you wrote: On Tue, 2009-01-27 at 14:28 -0500, Gabi Julien wrote: Could this help? If the logs are smaller then I could potentially afford shipping then at a higher frequency. See if there are times during which the recovery process isn't doing anything (i.e. just waiting for WAL data). If so, something like this might help. If it's constantly working as hard as it can, then probably not. An important question you should ask yourself is whether it can keep up in the steady state at all. If the primary is producing segments faster than the standby is recovering them, I don't think there's any way around that. The load on the slave is close to 0 so it does not explain the speed of recovery. Also the shipping of the 16MB WAL log takes only 1 second on the LAN. I guess the problem is probably what Fujii Masao explained. The WAL log shipped are not yet usable or something like that. I won't try to increase the frequency of log shipping because of that. Also, my setting of 60 seconds is the lowest frequency suggested by the documentation anyways. However, I have found the v4 patch about the PITR performance improvement. I will give it a try and report here. I might try pg_clearxlogtail too if I have time. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] very long update gin index troubles back?
On Tue, 27 Jan 2009 20:45:53 +0300 Teodor Sigaev teo...@sigaev.ru wrote: No matter if I drop the trigger that update agg content and the fact that I'm just updating d, postgresql will update the index? Yes, due to MVCC. Update of row could produce new version (tuple) and new version should be index as old one. Does that mean that it could be a good choice to place the tsvector in another table? thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] very long update gin index troubles back?
On Wed, 28 Jan 2009, Ivan Sergio Borgonovo wrote: On Tue, 27 Jan 2009 20:45:53 +0300 Teodor Sigaev teo...@sigaev.ru wrote: No matter if I drop the trigger that update agg content and the fact that I'm just updating d, postgresql will update the index? Yes, due to MVCC. Update of row could produce new version (tuple) and new version should be index as old one. Does that mean that it could be a good choice to place the tsvector in another table? this is a trade-off - additional join Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] libpq binary format for contrib/cube type?
I'm using libpq with a prepared query, and I'd like to access the contrib/cube type using the binary format. The documentation describes the text format for cubes but doesn't say what the binary format is. I glanced at the source code but... its complicated. :) Any help is appreciated. Thanks-- Andy. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] libpq binary format for contrib/cube type?
aws andy.schme...@gmail.com writes: I'm using libpq with a prepared query, and I'd like to access the contrib/cube type using the binary format. The documentation describes the text format for cubes but doesn't say what the binary format is. There isn't one --- contrib/cube doesn't provide send/receive functions at all. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] New 8.4 hot standby feature
On Tuesday 27 January 2009 21:47:36 you wrote: Hi, On Wed, Jan 28, 2009 at 4:28 AM, Gabi Julien gabi.jul...@broadsign.com wrote: Yes, the logs are shipped every minute but the recevory is 3 or 4 times longer. Are you disabling full_page_writes? It may slow down recovery several times. It looks like you found my problem. Everything I needed to know is described here: http://www.postgresql.org/docs/8.3/interactive/wal-configuration.html Setting checkpoint_timeout to 55 seconds speeds up the recovery to the level I want. Ironically, it makes the pg_last_recovered_xact_timestamp() function more reliable too on how up to date the replica is. I am not sure that I can take this for granted however. I will disable full_page_writes to make sure this agressive checkpoint_timeout setting won't slow down my master database too much. Can I be reassured on the fact that, if the master database crashes and some data is lost, at least the replica would keep its integrity (even though it is not in sync)? My settings: full_page_writes = off checkpoint_timeout = 55s checkpoint_completion_target = 0.7 archive_mode = on archive_command = './archive_command.sh %p %f' archive_timeout = 60 Also, would it be possible to recompile postgresql by using a different size (smaller) then 16M for WAL logs and would that be a smart thing to try? Thanks a lot to all of you. Thanks I will take a look at it. Also, I came across the record log shipping feature too in my research: http://www.postgresql.org/docs/current/static/warm-standby.html#WARM-STAN DBY-RECORD Could this help? If the logs are smaller then I could potentially afford shipping then at a higher frequency. No. Even if the logs are shipped frequently, they cannot be applied until the log file fills. Regards, -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] New 8.4 hot standby feature
On Wednesday 28 January 2009 18:35:18 Gabi Julien wrote: On Tuesday 27 January 2009 21:47:36 you wrote: Hi, On Wed, Jan 28, 2009 at 4:28 AM, Gabi Julien gabi.jul...@broadsign.com wrote: Yes, the logs are shipped every minute but the recevory is 3 or 4 times longer. Are you disabling full_page_writes? It may slow down recovery several times. It looks like you found my problem. Everything I needed to know is described here: http://www.postgresql.org/docs/8.3/interactive/wal-configuration.html Setting checkpoint_timeout to 55 seconds speeds up the recovery to the level I want. Ironically, it makes the pg_last_recovered_xact_timestamp() function more reliable too on how up to date the replica is. I am not sure that I can take this for granted however. This is a good question actually. If I set the checkpoint_timeout to something less then the archive_timeout, can I take this for granted the fact that pg_last_recovered_xact_timestamp() will always accurately tell me how up to date the replica is? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Slow first query despite LIMIT and OFFSET clause
On Thu, Jan 29, 2009 at 2:25 AM, Alban Hertroys dal...@solfertje.student.utwente.nl wrote: Ah I see, that's the original query and its plan again, not the one after implementing those triggers! You had me scratching my head for a bit there, wondering why the count() subquery was still there. Yes that was just for info. Here are the new query without the count() in there: explain analyze SELECT testimonials.url ,testimonials.alias ,testimonials.aliasEntered ,testimonials.title ,testimonials.modify_date ,testimonials.id ,visitcount.visit_count ,visitcount.unique_count ,visitcount.modify_date ,coalesce( extract(epoch from now()) - extract(epoch from visitcount.modify_date), 0) FROM testimonials LEFT OUTER JOIN visitcount USING (id) WHERE testimonials.user_id = 'superman' and testimonials.user_known = 1 and testimonials.status = 'Y' ORDER BY testimonials.modify_date desc OFFSET 0 LIMIT 10 ; QUERY PLAN --- Limit (cost=61.42..61.44 rows=10 width=162) (actual time=105.400..105.499 rows=10 loops=1) - Sort (cost=61.42..61.46 rows=16 width=162) (actual time=105.392..105.425 rows=10 loops=1) Sort Key: testimonials.modify_date - Nested Loop Left Join (cost=0.00..61.10 rows=16 width=162) (actual time=0.092..94.516 rows=2027 loops=1) - Index Scan using new_idx_userknown on testimonials (cost=0.00..24.29 rows=16 width=146) (actual time=0.058..10.983 rows=2027 loops=1) Index Cond: ((user_id)::text = 'superman'::text) Filter: (status = 'Y'::bpchar) - Index Scan using visitcount_pkey1 on visitcount (cost=0.00..2.28 rows=1 width=24) (actual time=0.024..0.026 rows=1 loops=2027) Index Cond: (testimonials.id = visitcount.id) Total runtime: 105.652 ms (10 rows) Note that I have an index on user_id, but because this is a website, there are several user_ids where we only have the IP. The above query is only ever needed for registered users, so for just the registered users we created another partial index called new_idx_userknown btree (user_id) WHERE user_known = 1 Of course for unregistered users we use user_known = 0, so they are excluded from this index. Is this not a useful partial index? I think in this SQL, the user_id is always superman and the user_known always 1 which is why the guesstimate from the planner may be off? Love to hear thoughts. THANKS! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] System table documentation
Where can I find documentation for the system tables? I have not found anything in the 8.3.1 documentation. Thanks. Bill -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] System table documentation
Bill Todd wrote: Where can I find documentation for the system tables? I have not found anything in the 8.3.1 documentation. Thanks. Bill http://www.postgresql.org/docs/8.3/static/catalogs.html Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Disabling FK for tables in Postgresql
Hi Is it possible to disable FK constraints in Postgresql? Thanks Josh
Re: [GENERAL] System table documentation
Bill Todd wrote: Where can I find documentation for the system tables? I have not found anything in the 8.3.1 documentation. Thanks. Bill Hi Bill, Good to see a Delphi guy here :-) http://www.postgresql.org/docs/8.3/interactive/catalogs.html Later, Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for PostgreSQL -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Disabling FK for tables in Postgresql
In response to Josh Harrison : Hi Is it possible to disable FK constraints in Postgresql? You can set the constraint deferred (if the constraint defined deferrable) or drop the constraint. http://www.postgresql.org/docs/current/static/sql-set-constraints.html Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] possible pg_dump bug
Hi For the record. Using search_path for schema name handling is most annoying feature of pg_dump for us. I have run into many cases where separating schema is inconvenient but don't seem to remember any upsides. regards Asko On Wed, Jan 28, 2009 at 7:39 PM, Tom Lane t...@sss.pgh.pa.us wrote: Raymond O'Donnell r...@iol.ie writes: On 28/01/2009 17:09, David Miller wrote: Pg Dump does not include schema name on insert statement generated from pg_dump with a -d option when exporting data for a particular table using the -t schema.table in version 8.3. I believe this same bug exists in 8.4 but have not confirmed it. I believe pg_dump should have an option to retain schema information. From memory, I think it does include a set search_path statement near the beginning. Quite. There is no bug here unless loading the dump file puts something in the wrong schema. (Suppressing the schema name where possible is in fact considered a feature not a bug, since it makes it simpler to hand-edit the dump file at need.) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] libpq binary format for contrib/cube type?
OK, I found another way to send the data using the array format and cube($1::float8[]). Thanks! Andy. On Jan 28, 2009, at 2:47 PM, Tom Lane wrote: There isn't one --- contrib/cube doesn't provide send/receive functions at all. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] New 8.4 hot standby feature
On Tue, 2009-01-27 at 12:53 -0500, Gabi Julien wrote:
I have merged the last hot standby patch (v9g) to 8.4 devel and I am pleased
with the experience. This is promising stuff.
Thanks,
Perhaps it is a bit too soon to
ask questions here but here it is:
Thanks very much for the bug report.
1. Speed of recovery
With a archive_timeout of 60 seconds, it can take about 4 minutes before I
see
the reflected changes in the replica. This is normal since, in addition to
the WAL log shipping, it takes more time to do the recovery itself. Still, is
there any way besides the archive_timeout config option to speed up the
recovery of WAL logs on the hot standby?
There was a reported bug whose apparent symptoms were delay of WAL
files. The bug was not in fact anything to do with that at all, it was
just delayed *visibility*. So I doubt very much that you have a
performance problem.
The bug fix patch is attached, verified to solve the problem.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
*** a/src/backend/access/transam/xact.c
--- b/src/backend/access/transam/xact.c
***
*** 1381,1387 RecordTransactionAbort(bool isSubXact)
* main xacts, the equivalent happens just after this function returns.
*/
if (isSubXact)
! XidCacheRemoveRunningXids(xid, nchildren, children, latestXid);
/* Reset XactLastRecEnd until the next transaction writes something */
if (!isSubXact)
--- 1381,1387
* main xacts, the equivalent happens just after this function returns.
*/
if (isSubXact)
! XidCacheRemoveRunningXids(MyProc, xid, nchildren, children, latestXid);
/* Reset XactLastRecEnd until the next transaction writes something */
if (!isSubXact)
***
*** 4536,4541 RecordKnownAssignedTransactionIds(XLogRecPtr lsn, TransactionId top_xid, Transac
--- 4536,4548
{
int nxids = myproc-subxids.nxids;
+ /*
+ * It's possible for us to overflow the subxid cache and then
+ * for a subtransaction abort to reduce the number of subxids
+ * in the cache below the cache threshold again. If that happens
+ * then it's still OK for us to use the subxid cache again, since
+ * once its in the cache it lives there till abort or commit.
+ */
if (nxids PGPROC_MAX_CACHED_SUBXIDS)
{
/*
***
*** 4621,4629 RecordKnownAssignedTransactionIds(XLogRecPtr lsn, TransactionId top_xid, Transac
LWLockRelease(ProcArrayLock);
elog(trace_recovery(DEBUG4),
! record known xact top_xid %u child_xid %u %slatestObservedXid %u,
top_xid, child_xid,
(unobserved ? unobserved : ),
latestObservedXid);
/*
--- 4628,4637
LWLockRelease(ProcArrayLock);
elog(trace_recovery(DEBUG4),
! record known xact top_xid %u child_xid %u %s%slatestObservedXid %u,
top_xid, child_xid,
(unobserved ? unobserved : ),
+ (mark_subtrans ? mark subtrans : ),
latestObservedXid);
/*
***
*** 4690,4707 xact_redo_commit(xl_xact_commit *xlrec, TransactionId xid, bool preparedXact)
PGPROC *proc;
int i;
- /* Make sure nextXid is beyond any XID mentioned in the record */
- max_xid = xid;
sub_xids = (TransactionId *) (xlrec-xnodes[xlrec-nrels]);
! /*
! * Find the highest xid and remove unobserved xids if required.
! */
! for (i = 0; i xlrec-nsubxacts; i++)
! {
! if (TransactionIdPrecedes(max_xid, sub_xids[i]))
! max_xid = sub_xids[i];
! }
/* Mark the transaction committed in pg_clog */
TransactionIdCommitTree(xid, xlrec-nsubxacts, sub_xids);
--- 4698,4706
PGPROC *proc;
int i;
sub_xids = (TransactionId *) (xlrec-xnodes[xlrec-nrels]);
! max_xid = TransactionIdLatest(xid, xlrec-nsubxacts, sub_xids);
/* Mark the transaction committed in pg_clog */
TransactionIdCommitTree(xid, xlrec-nsubxacts, sub_xids);
***
*** 4720,4726 xact_redo_commit(xl_xact_commit *xlrec, TransactionId xid, bool preparedXact)
*/
if (IsRunningXactDataValid() !preparedXact)
{
! ProcArrayRemove(proc, InvalidTransactionId, xlrec-nsubxacts, sub_xids);
FreeRecoveryProcess(proc);
}
--- 4719,4725
*/
if (IsRunningXactDataValid() !preparedXact)
{
! ProcArrayRemove(proc, max_xid, xlrec-nsubxacts, sub_xids);
FreeRecoveryProcess(proc);
}
***
*** 4790,4821 xact_redo_commit(xl_xact_commit *xlrec, TransactionId xid, bool preparedXact)
/*
* Be careful with the order of execution, as with xact_redo_commit().
* The two functions are similar but differ in key places.
*/
static void
! xact_redo_abort(xl_xact_abort *xlrec, TransactionId xid, bool preparedXact)
{
PGPROC *proc = NULL;
TransactionId *sub_xids;
TransactionId max_xid;
int i;
- /* Make sure nextXid is beyond any XID mentioned in the record */
- max_xid = xid;
sub_xids =
Re: [GENERAL] Slow first query despite LIMIT and OFFSET clause
On Jan 29, 2009, at 1:35 AM, Phoenix Kiula wrote: On Thu, Jan 29, 2009 at 2:25 AM, Alban Hertroys dal...@solfertje.student.utwente.nl wrote: Ah I see, that's the original query and its plan again, not the one after implementing those triggers! You had me scratching my head for a bit there, wondering why the count() subquery was still there. Yes that was just for info. Here are the new query without the count() in there: explain analyze SELECT testimonials.url ,testimonials.alias ,testimonials.aliasEntered ,testimonials.title ,testimonials.modify_date ,testimonials.id ,visitcount.visit_count ,visitcount.unique_count ,visitcount.modify_date ,coalesce( extract(epoch from now()) - extract(epoch from visitcount.modify_date), 0) FROM testimonials LEFT OUTER JOIN visitcount USING (id) WHERE testimonials.user_id = 'superman' and testimonials.user_known = 1 and testimonials.status = 'Y' ORDER BY testimonials.modify_date desc OFFSET 0 LIMIT 10 ; QUERY PLAN --- Limit (cost=61.42..61.44 rows=10 width=162) (actual time=105.400..105.499 rows=10 loops=1) - Sort (cost=61.42..61.46 rows=16 width=162) (actual time=105.392..105.425 rows=10 loops=1) Sort Key: testimonials.modify_date - Nested Loop Left Join (cost=0.00..61.10 rows=16 width=162) (actual time=0.092..94.516 rows=2027 loops=1) - Index Scan using new_idx_userknown on testimonials (cost=0.00..24.29 rows=16 width=146) (actual time=0.058..10.983 rows=2027 loops=1) Index Cond: ((user_id)::text = 'superman'::text) Filter: (status = 'Y'::bpchar) - Index Scan using visitcount_pkey1 on visitcount (cost=0.00..2.28 rows=1 width=24) (actual time=0.024..0.026 rows=1 loops=2027) Index Cond: (testimonials.id = visitcount.id) Total runtime: 105.652 ms (10 rows) Note that I have an index on user_id, but because this is a website, there are several user_ids where we only have the IP. The above query is only ever needed for registered users, so for just the registered users we created another partial index called new_idx_userknown btree (user_id) WHERE user_known = 1 Of course for unregistered users we use user_known = 0, so they are excluded from this index. Is this not a useful partial index? I think in this SQL, the user_id is always superman and the user_known always 1 which is why the guesstimate from the planner may be off? Love to hear thoughts. Well, that seems to have got you rid of the somewhat expensive index scans on new_idx_userknown as well (the duplicate entry for the scan being due to the subquery of course). What's remaining is the left join. If I understand correctly you have a PK on visitcount.id and that table only contains records for people who have a visitcount 0? That table gets updated a lot I'd think? The query plan still shows a bad estimate on that join; it has improved, but not enough. Does the plan look better right after you ANALYSE visitcount? I'm suspecting you either need to autovacuum visitcount more frequently or you need to increase the statistics size on visitcount.id. You're updating that table a lot I think, which creates one new dead row for every update. Letting vacuum mark the dead ones as reusable more frequently should also help keep that table and it's indexes cleaner, although the records the indexes are pointing to will be all over the place. I'm wondering... In highly updated tables it's probably more efficient to leave the dead rows alone (just marking them dead) and only append the updated ones at the end of the table? The dead rows will accumulate at the start of the table while the new ones go to the end. After a while a large section of the start of the table could just be removed as it'd only contain dead rows... This may already be in place of course, I don't have time now to look into the design specifics and it seems kind of an obvious thing to do! Regards, Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,49816122747034095710041! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
