[HACKERS] PQsendQuery/ PQgetResult Problem
Hi Hackers, I have a strange problem, or maybe it's not a strange problem but just something wrong with my understanding i have SIP router which works with postgresql using libpq, somewhere in the code it inserts a row in the database and then when the insert is finished it invokes another module which tries to read it , however sometimes the read operation doesn't find the row which was just inserted, this happens if there are large number of insert / read cycles like this are going on , the code which inserts the row uses PQsendQuery to insert and waits for PQgetResult to return null before the read module is invoked, the module which reads the row , reads it over a new connection. My question is if PQgetResult returns null is this a good enough guarantee that a subsequent connection (even in next millisecond) should be able to read that row?? Thanks for making me wiser /Usama
Re: [HACKERS] VACUUM FULL out of memory
On Jan 7, 2008 2:40 PM, Michael Akinde [EMAIL PROTECTED] wrote: As suggested, I tested a VACUUM FULL ANALYZE with 128MB shared_buffers and 512 MB reserved for maintenance_work_mem (on a 32 bit machine with 4 GB RAM). My Apologies if my question seems redundant and something you have already discussed with list members, but why do you need to do a VACUUM FULL? have you not vacuumed for a while? or some special requirement which requires very aggressive space re-claim? Vacuum Full is also known to cause some index bloat at times as well. most systems i know run regular vacuums and had never required to run a vacuum full. -- Usama Munir Dar http://www.linkedin.com/in/usamadar Consultant Architect Cell:+92 321 5020666 Skype: usamadar
Re: [HACKERS] Tuning Postgresql on Windows XP Pro 32 bit
Doug Knight wrote: We are running the binary distribution, version 8.2.5-1, installed on Windows XP Pro 32 bit with SP2. We typically run postgres on linux, but have a need to run it under windows as well. Our typical admin tuning for postgresql.conf doesn't seem to be as applicable for windows. So what have you tuned so far? what are your current postgresql settings that you have modified? What are your system specs for Hardware, RAM , CPU etc? -- Usama Munir Dar http://www.linkedin.com/in/usamadar http://www.linkedin.com/in/usamadar Consultant Architect Cell:+92 321 5020666 Skype: usamadar ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Tuning Postgresql on Windows XP Pro 32 bit
On Jan 3, 2008 8:57 PM, Doug Knight [EMAIL PROTECTED] wrote: All, Is there a place where I can find information about tuning postgresql running on a Windows XP Pro 32 bit system? I installed using the binary installer. I am seeing a high page fault delta and total page faults for one of the postgresql processes. Any help would be great. Which version of postgres? the process you are seeing this for is a user process? -- Usama Munir Dar http://www.linkedin.com/in/usamadar Consultant Architect Cell:+92 321 5020666 Skype: usamadar
Re: [HACKERS] Autovacuum Table List Ordering
On Jan 3, 2008 7:39 PM, Alvaro Herrera [EMAIL PROTECTED] wrote: Sure, feel free to propose a specific ordering. I think you would need to take table size into account too. Thanks, i thought we were already taking the database size into account somewhat when we calculate the vacuum threshold by factoring in reltuples. My initial thought is that we already decide to vacuum a table if (vactuples vacthresh). if we order the list by vacpriority where vacpriority=vactuples - vacthresh it would be reasonable start, without being too complicated , thoughts? Something that's also important to fix while you're doing that is fixing the BUG that is mentioned in the code that Simon griped about not long ago. OK, i will look what that is -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Usama Munir Dar http://www.linkedin.com/in/usamadar Consultant Architect Cell:+92 321 5020666 Skype: usamadar
[HACKERS] Autovacuum Table List Ordering
OK, so i was going through the autovacuum code and i noticed that when we construct the table list to vacuum in a database per run, we don't process them in a particular order. I mean since we pick the list up from pg_class so it may be ordered on oid? but when we select a database we have a priority algo, which select the db's in XID wraparound danger first, and that too the in an order that the db nearest to the XID wraparound is picked first So wouldn't it make sense to pick up the tables in a similar order as well? like sorting the list on (deadtuples - calculated threshold) this way we will be vacuuming the tables in more need first. Without an order i can imagine a scenario where a table which is near a wrap-around or has more dead-rows waiting or possible having the XID warparound while another big table takes a lot of time. With the launcher - worker architecture in 8.3 the situation will improve as you can have multiple workers vacuuming multiple tables in parallel, but having some order there would also help , i would think. Thanks, -- Usama Munir Dar http://www.linkedin.com/in/usamadar Consultant Architect Cell:+92 321 5020666 Skype: usamadar
Re: [HACKERS] timetz range check issue
On Dec 22, 2007 7:40 PM, Andrew Chernow [EMAIL PROTECTED] wrote: Tom Lane wrote: range-checks are present only where needed for the backend to defend itself Survival is very important, but so is maintaining data integrity. IMHO, data validation should be as consistent as possible. If the backend refuses data on one hand but allows it on the other, confusion sets in. I realize that binary input can't always be 100% validated, but a best effort is good form. +1 -- Usama Munir Dar http://www.linkedin.com/in/usamadar Consultant Architect Cell:+92 321 5020666 Skype: usamadar
Re: [HACKERS] Error while sending request to database
On Dec 6, 2007 5:26 PM, srinath narra [EMAIL PROTECTED] wrote: hi, when i connecting my postgresql which giving org.postgresql.util.PSQLException: ERROR: conversion between UNICODE and MULE_INTERNAL is not supported.please send solution about this problem. Srinath postgresql jdbc just works with utf8 client encoding, and since postgres server doesn't support encoding conversion to and from mule_internal server encoding and unicode client encoding , you won't be able to connect. Try setting your server encoding to something else, i.e. utf-8 more detail at http://www.postgresql.org/docs/8.2/static/multibyte.html#AEN24142 -- Usama Munir Dar http://linkedin.com/in/usamadar Consultant Architect Cell:+92 321 5020666 Skype: usamadar
Re: [HACKERS] Stored procedure issue
On Dec 2, 2007 7:40 AM, Dragan Zubac [EMAIL PROTECTED] wrote: Hello I have a stored procedure which does the billing stuff in our system,it works ok,but if I put in production,where there is some 5-10 billing events per second,the whole database slows down. It won't even drop some test table,reindex,vacuum,things which were done before in the blink of an eye. If I stop the application which calls the procedure,all is back to normal. We didn't implement any special locking mechanism in the procedure,all is default. The procedure is updating user's balance in table 'users'. On the other hand a couple of 'heavy load' table has foreign keys pointing to table 'users'. Is it the matter of concurency and some locking issue or maybe the existing of all those foreign keys pointing to table 'users',or maybe something else which we're not aware at the moment ? Can you please post your procedure and explain plan of the SQL which the procedure uses to do the billing stuff . There can be a zillion reasons for the performance problems you are seeing, but the email does not provide enough information. Sincerely Pera Be a better sports nut! Let your teams follow you with Yahoo Mobile. Try it now. http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate -- Usama Munir Dar http://linkedin.com/in/usamadar Consultant Architect Cell:+92 321 5020666 Skype: usamadar
Re: [HACKERS] Release Note Changes
On Nov 30, 2007 11:07 PM, Tom Lane [EMAIL PROTECTED] wrote: Josh Berkus [EMAIL PROTECTED] writes: I disagree. For people who want a quick summary of the major user-facing things changed we'll have multiple sources: (a) the announcement, (b) the press features list, (c) the Feature-Version matrix. The Release notes should have a *complete* list of changes. Define complete. Why? Because we don't use a bug/feature tracker. So a user trying to figure out hey, was my issue XXX fixed so that I should upgrade? has *no other source* than the Release notes to look at, except CVS history. And if we start asking sysadmins and application vendors to read the CVS history, we're gonna simply push them towards other DBMSes which have this information more clearly. So in other words, you don't *really* want complete. i think he means a list meant for end users which mentions all features and bug fixes done for that release. Your argument of go read the CVS logs is valid, but there are just too many for someone to go through to get the complete picture. i mean people may end up reading 1000 + logs in a worst case scenario to find out if a bug they are interested in is fixed , and the someone who compiled the release notes didn't think it was important enough to make it to the notes. Going through a 5K release notes document would be half that time, granted that over time thier ability to read through logs quicker will improve, but thats a learning curve they have to be willing to go trough, and not everyone will be interested to do that if i would have to find a word to describe what we need, i would say we need something *compendious* i.e. what is at once full in scope and brief and concise in treatment it is however work that someone will have to do, but it can be managed as such that it is a by-product of the process, instead of a 'one time in the end' job. This discussion is all about finding a suitable balance between length and detail. Simplistic pronouncements don't help us strike that balance. FWIW, I tend to agree with the folks who think Bruce trimmed too much this time. But the release notes are, and always have been, intended to boil the CVS history down to something useful by eliminating irrelevant detail. For the vast majority of people, the details that are being mentioned here are indeed irrelevant. There will be some for whom they are not. But depending on the question, almost any detail might not be irrelevant, and at that point you have to be prepared to go check the archives. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Usama Munir Dar http://linkedin.com/in/usamadar Consultant Architect Cell:+92 321 5020666 Skype: usamadar
Re: [HACKERS] Release Note Changes
On 11/30/07, Gregory Stark [EMAIL PROTECTED] wrote: Simon Riggs [EMAIL PROTECTED] writes: If people understand there aren't 13 performance improvements there are at *least* 19+ that is a positive message to help people decide to upgrade. Frankly I think the release notes are already too long. People who judge a release by counting the number of items in the release notes are not worth appeasing. Including every individual lock removed or code path optimized will only obscure the important points on which people should be judging the relevance of the release to them. Things like smoothing checkpoint i/o which could be removing a show-stopper problem for them. If they're mentioned at all a single release note bullet point saying Many optimizations and concurrency improvements in areas such as transaction start and finish, checkpoint start, record visibility checking, merge join plans, ... would suffice. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings i agree that release notes should not be too long, but may be there should be (if there isn't one already) something like a change log where people can find out all the changes done from the previous release, if they are intrested ? -- Usama Munir Dar http://linkedin.com/in/usamadar Consultant Architect Cell:+92 321 5020666 Skype: usamadar
Re: [HACKERS] PG 7.3 is five years old today
+1 On Nov 29, 2007 4:09 AM, Andreas 'ads' Scherbaum [EMAIL PROTECTED] wrote: On Tue, 27 Nov 2007 15:37:04 -0500 Tom Lane wrote: Andreas 'ads' Scherbaum [EMAIL PROTECTED] writes: On Tue, 27 Nov 2007 11:08:58 -0800 Joshua D. Drake wrote: Release 7.3.21 with and EOL addendum :). E.g; this is the last release of 7.3 and 7.3 is now considered unsupported. I know at least one customer who is using RHEL-3 and PG 7.3 on dozens machines worldwide. Are they running 7.3.20? Will they update to 7.3.21 promptly when we ship it? Or are they using whatever Red Hat includes in RHEL-3? (which is still 7.3.19 I believe) I'm not sure, which micro version they are using right now. I only know, they have 7.3.x, cause i already had to take care of this on some projects. One of the reasons for losing interest in frequent updates is that it seems most of the people we hear from who are running 7.3.x are running a pretty obsolete x. If we produce an update and no one actually installs it, we're just wasting time with make-work. I said: we should not disband support of 7.3 today, release a final version next week and that's it. Something like 3, 4 month of pre-announce seems to be ok for me and i don't think, this makes much difference. Kind regards -- Andreas 'ads' Scherbaum Failure is not an option. It comes bundled with your Microsoft product. (Ferenc Mantfeld) ---(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] convert int to bytea
Does it matter if you have written an explicit cast for int to bytea? On Nov 29, 2007 9:00 PM, Douglas McNaught [EMAIL PROTECTED] wrote: On 11/29/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: On Thu, 29 Nov 2007, Gregory Stark wrote: What do you want the resulting bytea to look like? example : id = 9 , bytea = '\000\000\011' IIRC What do you expect to happen when server and client are differently-endian? -Doug ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Usama Munir Dar http://linkedin.com/in/usamadar Consultant Architect Cell:+92 321 5020666 Skype: usamadar
Re: [HACKERS] convert int to bytea
On Nov 29, 2007 9:35 PM, Zoltan Boszormenyi [EMAIL PROTECTED] wrote: Hi, please don't top post to someone who didn't used this convention in answering you. It's impolite. I edited the mail a bit to return sanity. On Nov 29, 2007 9:00 PM, Douglas McNaught [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: On 11/29/07, [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: On Thu, 29 Nov 2007, Gregory Stark wrote: What do you want the resulting bytea to look like? example : id = 9 , bytea = '\000\000\011' IIRC What do you expect to happen when server and client are differently-endian? -Doug Usama Dar írta: Does it matter if you have written an explicit cast for int to bytea? You don't know what't endianness is, do you? Say, you have a number: 0x12345678. This is stored differently depending on the endianness. Big-endian (like Sparc, Motorola, etc): 0x12 0x34 0x56 0x78 Little-endian (Intel-compatibles, etc): 0x78 0x56 0x34 0x12 So, how do you want your number to come out as a byte array? Since a bytea is a sequence of bytes as stored in memory, you may have different meaning for an int-bytea conversion. It's your homework to look up what's network order is. :-) But it would give you consistent answer no matter what CPU your server uses. 1) i wasn't aware people are sensitive to top email reply vs inline, apologies if it offended you 2) i know what a byte order is , i just thought your interface i.e. libpq would convert it to the local byte order. -- -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- Usama Munir Dar http://linkedin.com/in/usamadar Consultant Architect Cell:+92 321 5020666 Skype: usamadar