Re: [GENERAL] Commit hung bug
Can i see list of commit related bugs in postgresql. In one of customer, commit returned successfully but operation is actually committed after an hour or so successful ( Postgres version -8.1.18). I am proposing customer to shift to latest version as there is many fixes and major performance improvement in latest 9.x versions. See http://archives.postgresql.org/pgsql-hackers/2006-10/msg00561.php I am sorry i could not understand it. What will be impact to postgresql user in this bug. Date: Wed, 21 Aug 2013 00:37:08 -0400 From: alvhe...@2ndquadrant.com To: msq...@live.com CC: pgsql-general@postgresql.org Subject: Re: [GENERAL] Commit hung bug S H wrote: There is one bug mentioned commit hung for days.. http://www.postgresql.org/message-id/1af3044fcab26f4db1ae551f8a33634b3d2...@mail.digital-rapids.com The interesting thing would be to see the server logs, not the application logs. Specifically, an issue that could look just likethis was fixed in 8.1.7, in which case you would see weird error messages about permission denied or such in the *server* logs. None of thatwould show up in the client logs. Any idea what exactly is this bug. I could not make out relation between release notes mentioned in http://www.postgresql.org/docs/8.1/static/release-8-1-7.html and above comment. Maybe it's this commit, which was part of 8.1.6: commit 9f1b531420ee13d04c7701b34bb4b874df7ff2fa Author: Teodor Sigaev teo...@sigaev.ru Date: Fri Oct 13 14:00:17 2006 + Fix infinite sleep and failes of send in Win32. 1) pgwin32_waitforsinglesocket(): WaitForMultipleObjectsEx now called with finite timeout (100ms) in case of FP_WRITE and UDP socket. If timeout occurs then pgwin32_waitforsinglesocket() tries to write empty packet goes to WaitForMultipleObjectsEx again. 2) pgwin32_send(): add loop around WSASend and pgwin32_waitforsinglesocket(). The reason is: for overlapped socket, 'ok' result from pgwin32_waitforsinglesocket() isn't guarantee that socket is still free, it can become busy again and following WSASend call will fail with WSAEWOULDBLOCK error. See http://archives.postgresql.org/pgsql-hackers/2006-10/msg00561.php It's troubling to be talking about a bug that was patched in 2006 for the 8.1.6 release, however. Anything prior to that is not something anyone should be using anymore. At the very least, you should have migrated to 8.1.23; but 8.1 has been unsupported altogether for more than two years now. Even 8.2 is out of support. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services
[GENERAL] Commit hung bug
There is one bug mentioned commit hung for days.. http://www.postgresql.org/message-id/1af3044fcab26f4db1ae551f8a33634b3d2...@mail.digital-rapids.com The interesting thing would be to see the server logs, not the application logs. Specifically, an issue that could look just likethis was fixed in 8.1.7, in which case you would see weird error messages about permission denied or such in the *server* logs. None of thatwould show up in the client logs. Any idea what exactly is this bug. I could not make out relation between release notes mentioned in http://www.postgresql.org/docs/8.1/static/release-8-1-7.html and above comment. Regards,S H
Re: [GENERAL] Commit problem in read-commited isolation level
1) What is your exact Postgres version i.e 8.1.5.? V - 8.1.18 3) You also say it works fine in two environments, but not one. What are the environments? OS and version, memory, Postgres versions, etc. It is working on most of the production system ( more than 100) but failed once in one of the system ( not always). OS and version, memory, Postgres versions, etc.RHEL 5.2 , 32 GB , 8.1.18 Current value in colname = 5; Update tablename set colname = 0 where key = 18; commit , in parallel to above queries ( either vacuum or reindex of table was running) After 10 sec following query is executed. select colname from tablename where key = 18 ;it is returning old value i.e colname = 5. After another few seconds select colname from tablename where key = 18 ;it is returning new value i.e colname = 0. You say at the top the new value is 0, but show 5 as the new value at the bottom. Sorry it is returning new value ie 0 after some time. Corrected the above description.
Re: [GENERAL] Commit problem in read-commited isolation level
Any triggers on the table? There are no trigger associated with this table. FYI 8.1 is no longer supported. I understand that. If there are some known related issues, it will be easy to convince, Product mgmt team to upgrade the version of postgresql. Are there known issues related to commit problem in 8.1 version. Date: Wed, 7 Aug 2013 17:05:59 -0700 From: adrian.kla...@gmail.com To: msq...@live.com CC: pgsql-general@postgresql.org Subject: Re: [GENERAL] Commit problem in read-commited isolation level On 08/07/2013 04:54 PM, S H wrote: Hi, I have faced very strange problem in one of psotgresql query in one of the production environment. It is working fine in development and other environment. Current value in colname = 5; Update tablename set colname = 0 where key = 18; commit , in parallel to above queries ( either vacuum or reindex of table was running) After 10 sec following query is executed. select colname from tablename where key = 18 ; it is returning old value i.e colname = 5. After another few seconds select colname from tablename where key = 18 ; it is returning new value i.e colname = 5. I thought the new value is 0? Isolevel level is readcommited. Is there any possibility of bug in commit in V8.1 leading to delay of commit ? Any triggers on the table? FYI 8.1 is no longer supported. I need to provide explanation of above behavior to my customer. Regards, -- Adrian Klaver adrian.kla...@gmail.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] Commit problem in read-commited isolation level
Hi, I have faced very strange problem in one of psotgresql query in one of the production environment. It is working fine in development and other environment. Current value in colname = 5;Update tablename set colname = 0 where key = 18;commit , in parallel to above queries ( either vacuum or reindex of table was running) After 10 sec following query is executed. select colname from tablename where key = 18 ;it is returning old value i.e colname = 5. After another few seconds select colname from tablename where key = 18 ;it is returning new value i.e colname = 5. Isolevel level is readcommited.Is there any possibility of bug in commit in V8.1 leading to delay of commit ? I need to provide explanation of above behavior to my customer. Regards,
Re: [GENERAL] Vacuum problem
I confirmed that there is no bloating unfortunately. Did some experiment and it seems that connection open are always slow in case of vacuuming.. - Experiment done are as follows:-- Do frequent vacuuming.- Execute xx connections every min and close after one sec.- when vacuum is running connection open takes time. I can almost 100% reduce it when my database size is 30 MB only with 1.5 bloating. Date: Tue, 14 May 2013 10:54:04 -0600 Subject: Re: [GENERAL] Vacuum problem From: scott.marl...@gmail.com To: msq...@live.com CC: pgsql-general@postgresql.org Not saying you HAVE bloating there, saying you MIGHT. -- 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] Vacuum problem
I wonder if you've got a bloated pg catalog then. Certainly sounds like it's a possibility. So other than vacuuming when you recreate this, is the server working hard? What is vacuum vacuuming when this happens (pg_stat_activity should show that) Does vacuum full is required to avoid bloating, i am running vacuum analyze regularly but not vacuum full. Could it be cause of bloating ?
[GENERAL] bloating vacuum
I am doing some experiment to understand the behaviour of manual vacuum. I created small table and started doing insertion/deletion/updation on 2 rows in infinite loop. It started bloating around 844 times, but after it stopped bloating.. what could be the reason? In between i am running manual vacuum analyze ( without full option)
Re: [GENERAL] bloating vacuum
Got some information from following http://www.depesz.com/2011/07/06/bloat-happens/ What is the general solution to avoid bloating. On Tue, 2013-05-14 at 14:51 +, S H wrote: I am doing some experiment to understand the behaviour of manual vacuum. I created small table and started doing insertion/deletion/updation on 2 rows in infinite loop. It started bloating around 844 times, but after it stopped bloating.. what could be the reason? In between i am running manual vacuum analyze ( without full option) Explanation is described here http://www.postgresql.org/docs/9.1/static/sql-vacuum.html
Re: [GENERAL] Vacuum problem
I am doing regular insertion/deletion on the same tables .. and running vacuum in an hour... I suspect there is bloating in my tables.. but how does bloating in pgcatalog is happening is not clear... Normally vacuum full is NOT required on a regular basis. However, if you did something like creation 100M tables and then dropped them, or did it one at a time real fast, you could outrun your autovacuum daemons and get bloat in the pg catalog tables. Just offering a possibility for why a connection might be taking a long time. There's plenty of other possibilities I'm sure.
[GENERAL] Vacuum problem
Hi, I have my database in which i am executing vacuuming running manually in one hour. In my production database sometime when vacuuming is running it is taking long time in opening connection. My current version is version 8.1. Is there any known issue about open connection problem with vacuuming. I found something but i am not sure if it is applicable to V8.1 too? Vacuum of pg_catalog tables causes huge delay in opening new connections. http://www.postgresql.org/message-id/14249.1347556...@sss.pgh.pa.us I think you're hitting the problem that was fixed here: Author: Tom Lane tgl(at)sss(dot)pgh(dot)pa(dot)us Branch: master Release: REL9_2_BR [532fe28da] 2012-05-26 19:09:52 -0400 Branch: REL9_1_STABLE Release: REL9_1_4 [6c1bf45ea] 2012-05-26 19:09:59 -0400 Branch: REL9_0_STABLE Release: REL9_0_8 [2ce097e6e] 2012-05-26 19:10:05 -0400 Branch: REL8_4_STABLE Release: REL8_4_12 [35cc2be6f] 2012-05-26 19:10:13 -0400 Branch: REL8_3_STABLE Release: REL8_3_19 [422022b12] 2012-05-26 19:10:19 -0400 Prevent synchronized scanning when systable_beginscan chooses a heapscan. The only interesting-for-performance case wherein we force heapscan here is when we're rebuilding the relcache init file, and the only such case that is likely to be examining a catalog big enough to be syncscanned is RelationBuildTupleDesc. But the early-exit optimization in that code gets broken if we start the scan at a random place within the catalog, so that allowing syncscan is actually a big deoptimization if pg_attribute is large (at least for the normal case where the rows for core system catalogs have never been changed since initdb). Hence, prevent syncscan here. Per my testing pursuant to complaints from Jeff Frost and Greg Sabino Mullane, though neither of them seem to have actually hit this specific problem. Back-patch to 8.3, where syncscan was introduced. Regards,SH
Re: [GENERAL] Vacuum problem
vacuum or vacuum full?
vacuum analyze.
What is the db server doing when this happens? What does top, vmstat,
iostat etc have to say about it?
It is high end server with 96 GB of RAM , 16 core server, but there are many
other application running, This db is used for monitoring the performance of
this server and inserting/updating data every one in 10-20 tables.
I am able to reproduce this issue, in case i run vacuuming of my database and
in parallel try to open 30 connections, sometime db client takes time to open
connection Is there any workaround or there is some known issue already
existing. If there is any known issue it will be easy to persuade my customers
to upgrade..
Stack trace of client is as follows:-
sendto(3, p\0\0\0(md5de8bdf202e563b11a4384ba5..., 41, 0, NULL, 0) = 41
0.12rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0 0.05poll([{fd=3,
events=POLLIN|POLLERR}], 1, -1) = 1 ([{fd=3, revents=POLLIN}]) 35.027745
Running 8.1 means you're asking about a system no one else on this
list is likely to still be using much. The hackers aren't gonna be
interested in fixing it either, since it's out of support.
I am migrating to new version, for new customers , but for old existing
customer, it would require significant time. Is there any workaround for the
same. Like improving some DB parameters.
Date: Mon, 13 May 2013 08:25:30 -0600
Subject: Re: [GENERAL] Vacuum problem
From: scott.marl...@gmail.com
To: msq...@live.com
CC: pgsql-general@postgresql.org
On Mon, May 13, 2013 at 8:05 AM, S H msq...@live.com wrote:
Hi,
I have my database in which i am executing vacuuming running manually in one
hour.
vacuum or vacuum full?
In my production database sometime when vacuuming is running it is taking
long time in opening connection.
What is the db server doing when this happens? What does top, vmstat,
iostat etc have to say about it?
My current version is version 8.1. Is there any known issue about open
connection problem with vacuuming.
8.1 is out of support and has been for some time.
I found something but i am not sure if it is applicable to V8.1 too?
Tom wrote:
broken if we start the scan at a random place within the catalog, so
that
allowing syncscan is actually a big deoptimization if pg_attribute is
large
SNIP
Back-patch to 8.3, where syncscan was introduced.
It was added in 8.3 so no, it is likely not your issue.
Running 8.1 means you're asking about a system no one else on this
list is likely to still be using much. The hackers aren't gonna be
interested in fixing it either, since it's out of support.
--
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] Vacuum problem
My disk is utilized by many other components, thus do we have minimum recommendation my postgres to have sufficient speed. Current perfomance of my disk is around 1-5MB/sec. Is it sufficient? Is it slow and can be cause of slow vacuuming..
[GENERAL] Postgresql stuck at open connection
Hi,
In my product in some rare cases , DB connections could not be open, it is
stuck at following location:-
It is stuck at following location :-
sendto(3, p\0\0\0(md5de8bdf202e563b11a4384ba5..., 41, 0, NULL, 0) = 41
0.12rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0 0.05poll([{fd=3,
events=POLLIN|POLLERR}], 1, -1) = 1 ([{fd=3, revents=POLLIN}]) 35.027745
regards,S H
Re: [GENERAL] Regarding template1 database
$ sudo -u postgres psql template1 psql (9.2.4) Type help for help. template1=# \d No relations found. I am getting as mentioned above. I am not sure what is taking vacuuming long time. - What is the recommendation of vacuuming for wraparound issue for template1 - Once in month/week/year? - Is it required to run analyze on template1 and postgres database (system databases). Since it is already in production i can maximum change the interval and vacuum options and nothing more...
Re: [GENERAL] Regarding template1 database
What should be the interval for calling vacuum on template1 and postgres database in case 1. No table is created on template1. Should i pass analyze parameter for template1 and postgres vacuum.
Re: [GENERAL] Regarding template1 database
I am working on some legacy product. In which template1 vacuuming with Analyze is executed through cron job every one hour and some times it is taking lot of time.I want to know if there is any major update/insert in template1 at some time, so i wanted to monitor the number of insertions and deletions done in template1. As mentioned earlier the template databases are not really supposed to be used as active databases, but as templates for creating new databases. Also, as John mentioned system tables/views are often global and do not apply to a particular database. It would be helpful to explain what it is you want to accomplish. For example, this is for security purposes and I want to know if someone is using template1. -- Adrian Klaver adrian.kla...@gmail.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] Regarding template1 database
I would like some assistance in understanding of template1 database.- I want to know the tables of tempate1- I want to know when there is any insertion/updation in template1 theoretically.- If i want to monitor the transactions done in the template1 database ( insertion/updation ) , is there any way to track the same. ~SH
Re: [GENERAL] Regarding template1 database
- I want to know the tables of tempate1 There are none, unless you choose to add some.I got it there are no tables. What about the system tables of template1 database ? Are they updated or not? - If i want to monitor the transactions done in the template1 database ( insertion/updation ) , is there any way to track the same. I want to monitor externally, without changing the database. If there are some system queries which can tell me what are the number of database insertion/updation in the template1 ( including system table it will be helpful for me).
Re: [GENERAL] Problems with plpgsql and FOR loops
Dear Bill Moran , I know the correct solution is to use the same version to develop on that I'm using to test. So I'm going to downgrade my version to 7.3.4 for now ... but this doesn't solve my biggest problem: getting the FOR loop to work. It appears from the error that the parser is getting confused between a FOR IN SELECT loop and a FOR integer loop. If its not top secret we would like to see the code -- Best Regards, Vishal Kashyap Director / Lead Developer, Sai Hertz And Control Systems Pvt Ltd, http://saihertz.rediffblogs.com Jabber IM: [EMAIL PROTECTED] ICQ : 264360076 --- You yourself, as much as anybody in the entire universe, deserve your love and affection. - Buddha --- I am usually called as Vishal Kashyap and my Girlfriend calls me Vishal CASH UP. Because everyone loves me as Vishal Kashyap and my Girlfriend loves me as CASH. ___ //\\\ ( 0_0 ) o0o-o0o- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] boolean to int
Dear Mage , I'm wondering why pgsql doesn't support boolean typecasts like select true::int; Many client applications including php assign 1 to true and 0 to false This was a issue PHP 4.2 + PostgreSQL 7.3.x and supports it till now for backward compatibility -- Best Regards, Vishal Kashyap Director / Lead Developer, Sai Hertz And Control Systems Pvt Ltd, http://saihertz.rediffblogs.com Jabber IM: [EMAIL PROTECTED] ICQ : 264360076 Yahoo IM: [EMAIL PROTECTED] --- You yourself, as much as anybody in the entire universe, deserve your love and affection. - Buddha --- pgsql=# select marital_status from vishals_life; marital_status -- Single not looking 1 Row(s) affected ___ //\\\ ( 0_0 ) o0o-o0o- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] select statement against pg_stats returns inconsistent
Dear Shelby Cain , Is this expected behavior or perhaps a bug? For a novice like me can anyone please tell me 1. Will this effect my application developed on PostgreSQL 2. Will my Application break at some point I heavly use the type of queries defined in the post. Would be greatfull for any kinda answers. -- Best Regards, Vishal Kashyap Director / Lead Developer, Sai Hertz And Control Systems Pvt Ltd, http://saihertz.rediffblogs.com Jabber IM: [EMAIL PROTECTED] ICQ : 264360076 --- You yourself, as much as anybody in the entire universe, deserve your love and affection. - Buddha --- I am usually called as Vishal Kashyap and my Girlfriend calls me Vishal CASH UP. Because everyone loves me as Vishal Kashyap and my Girlfriend loves me as CASH. ___ //\\\ ( 0_0 ) o0o-o0o- ---(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] embeding postgre
Dear twosk I heard from somone that i could use PostgreSQL, but I cannot find any information that confirms or denies it. AFAIK There was a discussion on the same topic in Dec2003 please search the list and you will geta solution -- Best Regards, Vishal Kashyap Director / Lead Developer, Sai Hertz And Control Systems Pvt Ltd, http://saihertz.rediffblogs.com Jabber IM: [EMAIL PROTECTED] ICQ : 264360076 --- You yourself, as much as anybody in the entire universe, deserve your love and affection. - Buddha --- I am usually called as Vishal Kashyap and my Girlfriend calls me Vishal CASH UP. Because everyone loves me as Vishal Kashyap and my Girlfriend loves me as CASH. ___ //\\\ ( 0_0 ) o0o-o0o- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] backup and restore questions
Dear Sally Sally , I had a few questions concerning the backup/restore process for pg. 1) Is it possible to dump data onto an existing database that contains data (assumning the schema of both are the same). Has anyone done this? I am thinking of this in order to expediate the data load process This can work provided the database are on same system and have have same schema not tried though. 2) I read that when dumping and restoring data the insert option is safer but slower than copy? Does anyone know from experience how much slower (especially for a database containing millions of records). If you are real serious about your data best way AFAIK is insert because with insert statments you can move around in case you upgrade your database or add a new colum in new table but trying to restore a old data of the same table. On an Celeron 900 PostgreSQL 7.3.4 RH 9.0 a 151Kb tared backup takes about 5 Minutes. Though data restore depends 99 % on disk throughput 1% on CPU in case of plain insert file and 90 % on disk throughput and 10 % CPU in case of tared file. 3) can pg_restore accept a file that is not archived like a zipped file or plain text file (file.gz or file) Can use both zipped and Plain. New versions of pg_restore i.e 7.3 identify the file format automatically 4) Is the general practise to have one whole dump of a database or several separate dumps (by table etc...)? One dump for data and other dump for schema will always help. -- Regards, Vishal Kashyap Director / Lead Developer, Sai Hertz And Control Systems Pvt Ltd, http://saihertz.rediffblogs.com ~*~*~*~*~*~*~* You Guys start coding I will take care of what this customers needs. ~*~*~*~*~*~*~* I Know you believe my words so logon to Jabber.org and add [EMAIL PROTECTED] to your roster. OR Seek Me at 264360076 ~*~*~*~*~*~*~* I am usually called as Vishal Kashyap but my Girlfriend calls me as Vishal CASH UP. This is because others identify me because of my generosity but my Girlfriend identify me because of my CASH. ~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~* ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
