[BUGS] multiple SRFs in SELECT clause.
I'm getting some odd results when running two generate_series calls in a SELECT. When the two calls return the same number of rows you get that many rows out: # SELECT generate_series(1,3), generate_series(1,3); generate_series | generate_series -+- 1 | 1 2 | 2 3 | 3 (3 rows) When the row counts differ you get the least common multiple number of rows. # SELECT generate_series(1,4), generate_series(1,2); generate_series | generate_series -+- 1 | 1 2 | 2 3 | 1 4 | 2 (4 rows) I was personally expecting a cross join between them that would be equivalent to # SELECT * FROM generate_series(1,4) a, generate_series(1,2) b; a | b ---+--- 1 | 1 1 | 2 2 | 1 2 | 2 3 | 1 3 | 2 4 | 1 4 | 2 (8 rows) Tested on 8.1.3 and CVS HEAD. Kris Jurka ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] fsync and semctl errors with 8.1.5/win32
I've been attempting to run PostgreSQL 8.1.5/win32 on a production deployment, but have started having many problems. McAfee Antivirus is installed and running, although I've excluded the entire drive where PostgreSQL is installed and where the data is installed. I've received several errors in the past few days/weeks. They fall into three general categories 1) permission denied errors 2) semctl errors 3) fsync errors. I am not sure how to reproduce these errors locally - they seem to occur at unpredictable intervals. The following posts seem related, although I don't see a resolution for any of the problems listed: http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg16097.html http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg14792.html http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg14916.html I have run PostgreSQL on Linux in the past and not had any problems. Is the win32 build generally considered stable or unstable for production use? Any help would be greatly appreciated! 1) PERMISSION DENIED ERROR This error occurred on the same day as the semctl started, but stopped occurring for a few hours before the semctl errors started. The following is an example: 2006-11-25 00:46:04 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:05 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:06 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:07 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:08 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:09 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:10 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:11 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:12 ERROR: could not open relation 1663/16404/84855: Permission denied 2) SEMCTL ERROR This error occurred over and over one day with the same pattern - several semctl errors, then the unexpected EOF. This resulted in clients being unable to create database connections. The error occurred overnight and into the next day, and did not disappear until postgres was restarted. The following is an example: 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 LOG: could not receive data from client: No connection could be made because the target machine actively refused it. 2006-11-25 22:10:03 LOG: unexpected EOF on client connection 3) FSYNC ERROR I've seen this error several times in the past - including today. The following is an example: 2006-11-27 00:00:20 LOG: autovacuum: processing database incommDashboard 2006-11-27 00:00:20 LOG: could not fsync segment 0 of relation 1663/16404/89952: Permission denied 2006-11-27 00:00:20 ERROR: storage sync failed on magnetic disk: Permission denied 2006-11-27 00:00:24 LOG: could not fsync segment 0 of relation 1663/16404/89952: Permission denied 2006-11-27 00:00:24 ERROR: storage sync failed on magnetic disk: Permission denied 2006-11-27 00:00:26 LOG: could not fsync segment 0 of relation 1663/16404/89952: Permission denied 2006-11-27 00:00:26 ERROR: storage sync failed on magnetic disk: Permission denied 2006-11-27 00:00:29 LOG: could not fsync segment 0 of relation 1663/16404/89952: Permission denied 2006-11-27 00:00:29 ERROR: storage sync failed on magnetic disk: Permission denied 2006-11-27 00:00:32 LOG: could not fsync segment 0 of relation 1663/16404/89952: Permission denied 2006-11-27 00:00:32 ERROR: storage sync failed on magnetic
Re: [BUGS] fsync and semctl errors with 8.1.5/win32
Per the FAQ, we suggest that you *uninstall* your antivirus. Especially if it has firewall-like functionality (like I beleive McAfee does). Just disabling the scan does *not* remove the filter drivers and does not make the antivirus not affect the database processes. So try this. If the problem doesn't go away, look for something else installed that might be interfernig with the normal operation of your windows install. //Magnus -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jeremy Haile Sent: den 27 november 2006 15:21 To: pgsql-bugs@postgresql.org Subject: [BUGS] fsync and semctl errors with 8.1.5/win32 I've been attempting to run PostgreSQL 8.1.5/win32 on a production deployment, but have started having many problems. McAfee Antivirus is installed and running, although I've excluded the entire drive where PostgreSQL is installed and where the data is installed. I've received several errors in the past few days/weeks. They fall into three general categories 1) permission denied errors 2) semctl errors 3) fsync errors. I am not sure how to reproduce these errors locally - they seem to occur at unpredictable intervals. The following posts seem related, although I don't see a resolution for any of the problems listed: http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg16097.html http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg14792.html http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg14916.html I have run PostgreSQL on Linux in the past and not had any problems. Is the win32 build generally considered stable or unstable for production use? Any help would be greatly appreciated! 1) PERMISSION DENIED ERROR This error occurred on the same day as the semctl started, but stopped occurring for a few hours before the semctl errors started. The following is an example: 2006-11-25 00:46:04 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:05 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:06 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:07 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:08 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:09 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:10 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:11 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:12 ERROR: could not open relation 1663/16404/84855: Permission denied 2) SEMCTL ERROR This error occurred over and over one day with the same pattern - several semctl errors, then the unexpected EOF. This resulted in clients being unable to create database connections. The error occurred overnight and into the next day, and did not disappear until postgres was restarted. The following is an example: 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 LOG: could not receive data from client: No connection could be made because the target machine actively refused it. 2006-11-25 22:10:03 LOG: unexpected EOF on client connection 3) FSYNC ERROR I've seen this error several times in the past - including today. The following is an example: 2006-11-27 00:00:20 LOG: autovacuum: processing database incommDashboard 2006-11-27 00:00:20 LOG: could not fsync segment 0 of relation 1663/16404/89952:
Re: [BUGS] fsync and semctl errors with 8.1.5/win32
Thanks Magnus. I will uninstall the AntiVirus and see if my problems persist. I have disabled all other non-essential services, indexing, etc. so I don't know of anything else that could be causing the problems. However, in some of the posts I referred to, the poster indicated that they were not running antivirus software and still experienced the problems I'm having. I'll repost if I do or don't continue to experience problems after uninstalling the antivirus. On Mon, 27 Nov 2006 15:58:33 +0100, Magnus Hagander [EMAIL PROTECTED] said: Per the FAQ, we suggest that you *uninstall* your antivirus. Especially if it has firewall-like functionality (like I beleive McAfee does). Just disabling the scan does *not* remove the filter drivers and does not make the antivirus not affect the database processes. So try this. If the problem doesn't go away, look for something else installed that might be interfernig with the normal operation of your windows install. //Magnus -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jeremy Haile Sent: den 27 november 2006 15:21 To: pgsql-bugs@postgresql.org Subject: [BUGS] fsync and semctl errors with 8.1.5/win32 I've been attempting to run PostgreSQL 8.1.5/win32 on a production deployment, but have started having many problems. McAfee Antivirus is installed and running, although I've excluded the entire drive where PostgreSQL is installed and where the data is installed. I've received several errors in the past few days/weeks. They fall into three general categories 1) permission denied errors 2) semctl errors 3) fsync errors. I am not sure how to reproduce these errors locally - they seem to occur at unpredictable intervals. The following posts seem related, although I don't see a resolution for any of the problems listed: http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg16097.html http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg14792.html http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg14916.html I have run PostgreSQL on Linux in the past and not had any problems. Is the win32 build generally considered stable or unstable for production use? Any help would be greatly appreciated! 1) PERMISSION DENIED ERROR This error occurred on the same day as the semctl started, but stopped occurring for a few hours before the semctl errors started. The following is an example: 2006-11-25 00:46:04 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:05 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:06 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:07 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:08 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:09 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:10 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:11 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:12 ERROR: could not open relation 1663/16404/84855: Permission denied 2) SEMCTL ERROR This error occurred over and over one day with the same pattern - several semctl errors, then the unexpected EOF. This resulted in clients being unable to create database connections. The error occurred overnight and into the next day, and did not disappear until postgres was restarted. The following is an example: 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be
Re: [BUGS] fsync and semctl errors with 8.1.5/win32
I've gotten pushback from my organization on removing antivirus from the servers completely. Are there any antiviruses that are known to be compatible with PostgreSQL/win32? On Mon, 27 Nov 2006 10:28:23 -0500, Jeremy Haile [EMAIL PROTECTED] said: Thanks Magnus. I will uninstall the AntiVirus and see if my problems persist. I have disabled all other non-essential services, indexing, etc. so I don't know of anything else that could be causing the problems. However, in some of the posts I referred to, the poster indicated that they were not running antivirus software and still experienced the problems I'm having. I'll repost if I do or don't continue to experience problems after uninstalling the antivirus. On Mon, 27 Nov 2006 15:58:33 +0100, Magnus Hagander [EMAIL PROTECTED] said: Per the FAQ, we suggest that you *uninstall* your antivirus. Especially if it has firewall-like functionality (like I beleive McAfee does). Just disabling the scan does *not* remove the filter drivers and does not make the antivirus not affect the database processes. So try this. If the problem doesn't go away, look for something else installed that might be interfernig with the normal operation of your windows install. //Magnus -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jeremy Haile Sent: den 27 november 2006 15:21 To: pgsql-bugs@postgresql.org Subject: [BUGS] fsync and semctl errors with 8.1.5/win32 I've been attempting to run PostgreSQL 8.1.5/win32 on a production deployment, but have started having many problems. McAfee Antivirus is installed and running, although I've excluded the entire drive where PostgreSQL is installed and where the data is installed. I've received several errors in the past few days/weeks. They fall into three general categories 1) permission denied errors 2) semctl errors 3) fsync errors. I am not sure how to reproduce these errors locally - they seem to occur at unpredictable intervals. The following posts seem related, although I don't see a resolution for any of the problems listed: http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg16097.html http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg14792.html http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg14916.html I have run PostgreSQL on Linux in the past and not had any problems. Is the win32 build generally considered stable or unstable for production use? Any help would be greatly appreciated! 1) PERMISSION DENIED ERROR This error occurred on the same day as the semctl started, but stopped occurring for a few hours before the semctl errors started. The following is an example: 2006-11-25 00:46:04 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:05 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:06 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:07 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:08 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:09 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:10 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:11 ERROR: could not open relation 1663/16404/84855: Permission denied 2006-11-25 00:46:12 ERROR: could not open relation 1663/16404/84855: Permission denied 2) SEMCTL ERROR This error occurred over and over one day with the same pattern - several semctl errors, then the unexpected EOF. This resulted in clients being unable to create database connections. The error occurred overnight and into the next day, and did not disappear until postgres was restarted. The following is an example: 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL, 0) failed: A non-blocking socket operation could not be completed immediately. 2006-11-25 22:10:03 FATAL: semctl(167238064, 15, SETVAL,
Re: [BUGS] fsync and semctl errors with 8.1.5/win32
Jeremy Haile wrote: I've gotten pushback from my organization on removing antivirus from the servers completely. Are there any antiviruses that are known to be compatible with PostgreSQL/win32? All my boxes (2 build farm members, 1 production server, and the laptop on which the official releases are built and tested) run Sophos Anti Virus (http://www.sophos.com/products/es/endpoint/sav.html), with no problems. Regards, Dave ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] fsync and semctl errors with 8.1.5/win32
Thanks for the feedback. If you don't mind, what version of PostgreSQL are you running? I'm trying to bring PostgreSQL into this company - they are primarily a Windows/SQL Server shop (although Java software development) I've already gotten comments similar to Why don't you just switch to SQL Server? - so I'm hoping to find a workaround before I get forced to switch DB platforms. As it is, my application seems unreliable because I haven't been able to resolve the PostgreSQL hanging problems in Windows. If I had my way, I'd switch the server to Linux - but alas, that hasn't been an option so far. I know this may be the wrong list to ask this question on - but as I'm an outspoken PostgreSQL advocate, I'd like your opinions. If I am unable to resolve these PostgreSQL issues given my constraints, will I likely have less problems running MySQL/InnoDB on Windows? (since it has had a native Windows build for much longer) On Mon, 27 Nov 2006 16:40:57 +, Dave Page [EMAIL PROTECTED] said: Jeremy Haile wrote: I've gotten pushback from my organization on removing antivirus from the servers completely. Are there any antiviruses that are known to be compatible with PostgreSQL/win32? All my boxes (2 build farm members, 1 production server, and the laptop on which the official releases are built and tested) run Sophos Anti Virus (http://www.sophos.com/products/es/endpoint/sav.html), with no problems. Regards, Dave ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] 8.2rc1: vacuum full fills up disk space
Thomas H. wrote: this somehow sounds buggy: vacuum full absolutely *will* bloat your index, if run on a heavily-modified table. I do not think it will bloat pg_xlog by itself however; are you sure you don't have some other open transactions? well yes, as the system is live, users are browsing the website. but all queries that try to access the table in question are stalled at the moment. when querying server status i'm seeing lots of queries that are waiting for access to the table. would vacuum freeze be faster? Vacuum freeze won't move tuples so it won't reclaim any more space than a normal vacuum. Cluster, however, rewrites the whole table and compacts the space, and runs faster than vacuum full on a badly bloated table. It will also recreate all indexes. In the future, instead of updating a whole table with UPDATE, you should consider doing a SELECT INTO to create a new table, dropping the old table and renaming the new one in place of the old one. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] 8.2rc1: vacuum full fills up disk space
well yes, as the system is live, users are browsing the website. but all queries that try to access the table in question are stalled at the moment. when querying server status i'm seeing lots of queries that are waiting for access to the table. would vacuum freeze be faster? Vacuum freeze won't move tuples so it won't reclaim any more space than a normal vacuum. Cluster, however, rewrites the whole table and compacts the space, and runs faster than vacuum full on a badly bloated table. It will also recreate all indexes. will give it a try later on, thanks! In the future, instead of updating a whole table with UPDATE, you should consider doing a SELECT INTO to create a new table, dropping the old table and renaming the new one in place of the old one. the problem is: the table was far from being bloated, IMO. it was 2 days old, every record at most 2-3 times updated. the space needed for the table dropped from 400mb to roughly 200mb after the 1.5hr vacuum full... i've never had such a long vacuuming time before, even on tables that are much larger and contains more dead rows. the table uses tsearch2 and a gin-index, could that be the problem? the gin faq says a drop/create index would be much faster than a reindex. maybe this is also true when vacuuming a table with a gin-index? - thomas ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [BUGS] multiple SRFs in SELECT clause.
Kris Jurka [EMAIL PROTECTED] writes: I'm getting some odd results when running two generate_series calls in a SELECT ... When the row counts differ you get the least common multiple number of rows. Yup, this is the expected or at least historical behavior. It's not entirely clear what you *should* get, which is one reason we ought to deprecate SRFs in SELECT lists. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [BUGS] Unexpected sort order (suspected bug)
Jeff Davis wrote: On Mon, 2006-11-27 at 12:44 -0800, Ron Mayer wrote: Shouldn't the results of this query shown here been sorted by b rather than by a? I would have thought since order by b is in the outer sql statement it would have been the one the final result gets ordered by. li=# select * from (select (random()*10)::int as a, (random()*10)::int as b from generate_series(1,10) order by a) as x order by b; a | b ---+ 0 | 8 1 | 10 3 | 4 4 | 8 5 | 1 5 | 9 6 | 4 6 | 5 8 | 4 9 | 0 (10 rows) ... It looks like a planner bug. Below are two plans; the first fails and the second succeeds. That leads me to believe it's a planner bug, but what seems strangest to me is that it does order by a, and not by some new evaluation of (random()*10). Yeah, looks that way to me too. So how would I report it. Ccing the bugs list? Guess it can't hurt. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[BUGS] BUG #2783: insufficient base table information for updating or refreshing
The following bug has been logged online: Bug reference: 2783 Logged by: mike Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.5 Operating system: windows 2000 Description:insufficient base table information for updating or refreshing Details: if using 8.01.0200 driver, can select table, but cannot insert picture if using 8.02.0200 driver, can insert picture, but cannot select table is it safe to use 8.2 beta 3 for production use? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] BUG #2784: Performance serious degrades over a period of a month
The following bug has been logged online: Bug reference: 2784 Logged by: Michael Simms Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.4 Operating system: Linux kernel 2.6.12 Description:Performance serious degrades over a period of a month Details: OK, we have a database that runs perfectly well after a dump and restore, but over a period of a month or two, it just degrades to the point of uselessness. vacuumdb -a is run every 24 hours. We have also run for months at a time using -a -z but the effect doesnt change. The database is for a counter, not the most critical part of the system, but a part of the system nonetheless. Other tables we have also degrade over time, but the counter is the most pronounced. There seems to be no common feature of the tables that degrade. All I know is that a series of queries that are run on the database every 24 hours, after a dump/restore takes 2 hours. Now, 2 months after, it is taking over 12. We are seriously considering switching to mysql to avoid this issue. But I wanted to let you guys have a chance to resolve the issue, we dont have the manpower or expertise to fix it ourselves. I am willing to let someone from the postgres development team have access to our server for a period of time to have a look at the issue. This would need to be someone extremely trustworthy as the database contains confidential client information. I am willing to wait 2 days for a response and for someone to take a look at the problem. The performance degridation isnt something we can leave as it is for long, and in 2 days time I will have to dump and restore the database, which will reset it to a good state, and will mean I will have to resort to the mysql switch instead. Sorry this sounds a bit rushed, but it cant be helped, this is causing *problems* and we need a solution, either a fix or a switch to another database. Id rather a fix cos I like postgres, but Im willing to bite the mysql bullet if I have to... ---(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
[BUGS] BUG #2782: Too Many open files in system
The following bug has been logged online: Bug reference: 2782 Logged by: Alaa El Gohary Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4.12 Operating system: FreeBSD 6.0 Description:Too Many open files in system Details: this message appears when trying to open an application Fatal:could not open file/usr/local/pgsql/data/global/1262:too many open files in system ---(end of broadcast)--- TIP 6: explain analyze is your friend
[BUGS] BUG #2781: database dump/restore problems
The following bug has been logged online: Bug reference: 2781 Logged by: Greg Peters Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1/8.2beta3 Operating system: WInXP Description:database dump/restore problems Details: Hello, I recently performed a database dump with 8.2b3 for a complete database. An example of the SQL output for a single table is below: CREATE TABLE admin_field_list ( key bigint NOT NULL, field character varying(25) NOT NULL, added_by character varying(25) NOT NULL, add_date timestamp without time zone DEFAULT now() NOT NULL, mod_date timestamp without time zone, modified_by character varying(25) ); ALTER TABLE public.admin_field_list OWNER TO root; -- -- TOC entry 1351 (class 1259 OID 16425) -- Dependencies: 1352 4 -- Name: admin_field_list_key_seq; Type: SEQUENCE; Schema: public; Owner: root -- CREATE SEQUENCE admin_field_list_key_seq START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; As you can see, the primary key is exported as a bigint, with a separate section for the sequence. This differs to the way 8.1 dumps the same table below: CREATE TABLE admin_field_list ( key bigserial NOT NULL, field character varying(25) NOT NULL, added_by character varying(25) NOT NULL, add_date timestamp without time zone DEFAULT now() NOT NULL, mod_date timestamp without time zone, modified_by character varying(25) ); I then tried to restore the DB dumped by 8.2b3 into 8.1, with no success. It didn't like the sequence part. This probably isn't so much a bug, but more a backwards compatibility issue. Also, it seems strange to make the newer database dumps more verbose and complicated. Surely the simple bigserial datatype is a better method, and easier to implement then the sequence statements? Regards, Greg. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[BUGS] BUG #2785: Exception Issue
The following bug has been logged online: Bug reference: 2785 Logged by: Patrick Hayes Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1 Operating system: Windows Professional Description:Exception Issue Details: I am defining Exception blocks. When I put something that others in the when clause, it will not compile. The others clause works fine. The following is working fine. BEGIN PPSDATA.member_date_of_birth=date(substr(ROWDATA.inline,55,4)||'-'|| substr(ROWDATA.inline,59,2)||'-'||substr(ROWDATA.inline,61,2)); EXCEPTION WHEN OTHERS THEN ERRORCODE:='Y'; PPSERROR.current_month_error:='*'; END; The following is not working fine. BEGIN select count(*) into cnt_county from county_code where county_code = PPSERROR.member_county_code; EXCEPTION WHEN no_data then PPSERROR.member_county_code_error:='*'; RAISE NOTICE ' ---county_code_error out %', PPSERROR.member_county_code; ERRORCODE:='Y'; END; I get the following error. postgres-# language plpgsql; ERROR: unrecognized exception condition no_data CONTEXT: compile of PL/pgSQL function chip_pps_data_check near line 101 Thanks ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] BUG #2787: postgresql-jdbc-8.1.407 won't install on RHEL4
The following bug has been logged online: Bug reference: 2787 Logged by: Pete Deffendol Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.5 Operating system: Red Hat Enterprise 4 Description:postgresql-jdbc-8.1.407 won't install on RHEL4 Details: When attempting to install ftp://ftp8.us.postgresql.org/postgresql/binary/v8.1.5/linux/rpms/redhat/rhel -es-4/postgresql-jdbc-8.1.407-4PGDG.i686.rpm the installer reports the following: error: Failed dependencies: /usr/bin/rebuild-gcj-db is needed by postgresql-jdbc-8.1.407-4PGDG.i686 It appears that some other distributions (Fedora?) package this file up with java-1.4.2-gcj-compat. However, the Red Hat version java-1.4.2-gcj-compat-1.4.2.0-27jpp doesn't do it. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[BUGS] BUG #2786: PGADMIN 1.6
The following bug has been logged online: Bug reference: 2786 Logged by: Gilberto Xavier Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1 Operating system: Linux and Windows Description:PGADMIN 1.6 Details: Where was set the backup and restore capability into the new version of PGAdmin - 1.6.0. It seens that is not available. Am I correct? Those capabilities are very important into the software and had been able on version 1.4 Thanks ---(end of broadcast)--- TIP 6: explain analyze is your friend
[BUGS] BUG #2788: Create Function operator Broken?
The following bug has been logged online: Bug reference: 2788 Logged by: Shawn Tayler Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.5 Operating system: Linux 2.6.17 Description:Create Function operator Broken? Details: I have been through the online DOC's the FAQ and can find nothing wrong with it. So I am thinking its a bug. CREATE FUNCTION default_status() returns null AS 'BEGIN update status set wc to false, np to false, elk to false, sp1 to false, sp2 to false, wc_rep to false, np_rep to false, elk_rep to false, sp1_rep to false, sp2_rep to false; END;' language 'sql'; It throughs a syntax error at the 'AS' and I can't seem to figure out why. Sorry if this is not a real bug. Shawn ---(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: [BUGS] BUG #2769: invalid memory alloc request size n on
Tom Lane wrote: Chris Jones [EMAIL PROTECTED] writes: I used to be decent with gdb, so let me know if there's anything I can do as far as backtraces, etc. Try setting a breakpoint at errfinish() and backtracing from there. Breakpoint 1, 0x081cd8ba in errfinish () (gdb) bt #0 0x081cd8ba in errfinish () #1 0x081ce756 in elog_finish () #2 0x081dd6e5 in MemoryContextAlloc () #3 0x081ca14c in load_relcache_init_file () #4 0x081c9164 in RelationCacheInitialize () #5 0x081d4dfe in InitPostgres () #6 0x0816facb in PostgresMain () #7 0x0811e04d in main () #8 0x08072e22 in ___start () Because it was built without debugging symbols, I think this is all I can get for you. I've dropped this database and restored it from backups to get it running again. But I've also kept a copy of the erroneous data for bug-hunting purposes. If you'd like to track this down and/or find a way to automatically clean up data corrupted in this fashion, I'll be happy to work with you. If you have no interest, please let me know so I can delete the files. Chris ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[BUGS] BUG #2789: problem with delete statement
The following bug has been logged online: Bug reference: 2789 Logged by: Lucian Capdefier Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.5 Operating system: Windows XP Description:problem with delete statement Details: I have noticed a problem with the DELETE statement. 1. I cannot user table alias in the FROM clause of the DELETE statement. 2. I cannot user more than one, table in the FROM clause. Example: 1. delete from fxkanban.product_offer where offer_id=1 and exists (select 1 from fxkanban.product_offer x, fxkanban.product p where x.product_id=p.product_id and x.offer_id=offer_id and p.owner='test') works ok delete from fxkanban.product_offer o where o.offer_id=1 and exists (select 1 from fxkanban.product_offer x, fxkanban.product p where x.product_id=p.product_id and x.offer_id=o.offer_id and p.owner='test') does not work 2. delete from only fxkanban.product_offer, fxkanban.product p where o.offer_id=1 and o.product_id=p.product_id and p.owner='test' does not work Both problems issue SQL Error 42601 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #2781: database dump/restore problems
Greg Peters [EMAIL PROTECTED] writes: As you can see, the primary key is exported as a bigint, with a separate section for the sequence. This differs to the way 8.1 dumps the same table below: This is an intentional change that fixes a lot of corner cases such as renamed sequences. The former behavior looked simple, perhaps, but it failed outright in too many scenarios. See http://archives.postgresql.org/pgsql-committers/2006-08/msg00376.php and preceding discussions in pgsql-hackers. I then tried to restore the DB dumped by 8.2b3 into 8.1, with no success. It didn't like the sequence part. This probably isn't so much a bug, but more a backwards compatibility issue. We have never promised backward compatibility of pg_dump output to older server versions. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #2784: Performance serious degrades over a period of a month
Michael Simms [EMAIL PROTECTED] writes: OK, we have a database that runs perfectly well after a dump and restore, but over a period of a month or two, it just degrades to the point of uselessness. vacuumdb -a is run every 24 hours. We have also run for months at a time using -a -z but the effect doesnt change. You probably need significantly-more-frequent vacuuming. Have you considered autovacuum? This is not a bug --- you'd get better help on the pgsql-performance mailing list. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #2782: Too Many open files in system
Alaa El Gohary [EMAIL PROTECTED] writes: this message appears when trying to open an application Fatal:could not open file/usr/local/pgsql/data/global/1262:too many open files in system Try reducing max_files_per_process. Your kernel is evidently promising more than it can deliver about how many files each process can open. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2781: database dump/restore problems
We have never promised backward compatibility of pg_dump output to older server versions. regarding pg_dump: where there some changes from b3 to rc1 that would explain the resulting rc1 pg_dump output (-c) being half as big as with b3? i've rerun pg_dump several times with the same result, and no error messages. - thomas ---(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: [BUGS] BUG #2784: Performance serious degrades over a period of a month
This really should have been asked on pgsql-performance and would probably get a better response there.. On Sun, Nov 26, 2006 at 16:35:52 +, Michael Simms [EMAIL PROTECTED] wrote: PostgreSQL version: 8.1.4 Operating system: Linux kernel 2.6.12 Description:Performance serious degrades over a period of a month Details: OK, we have a database that runs perfectly well after a dump and restore, but over a period of a month or two, it just degrades to the point of uselessness. vacuumdb -a is run every 24 hours. We have also run for months at a time using -a -z but the effect doesnt change. This sounds like you either need to increase your FSM setting or vacuum more often. I think vacuumdb -v will give you enough information to tell if FSM is too low at the frequency you are vacuuming. The database is for a counter, not the most critical part of the system, but a part of the system nonetheless. Other tables we have also degrade over time, but the counter is the most pronounced. There seems to be no common feature of the tables that degrade. All I know is that a series of queries that are run on the database every 24 hours, after a dump/restore takes 2 hours. Now, 2 months after, it is taking over 12. We are seriously considering switching to mysql to avoid this issue. You probably will want to vacuum the counter table more often than the other tables in the database. Depending on how often the counter(s) are being updated and how many separate counters are in the table you might want to vacuum that table as often as once a minute. Depending on your requirements you might also want to consider using a sequence instead of a table row for the counter. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #2788: Create Function operator Broken?
On Mon, 27 Nov 2006, Shawn Tayler wrote: The following bug has been logged online: Bug reference: 2788 Logged by: Shawn Tayler Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.5 Operating system: Linux 2.6.17 Description:Create Function operator Broken? Details: I have been through the online DOC's the FAQ and can find nothing wrong with it. So I am thinking its a bug. CREATE FUNCTION default_status() returns null AS Do you mean returns void? I believe the issue is that returns null starts the returns null on null input function attribute. 'BEGIN update status set wc to false, np to false, elk to false, sp1 to false, sp2 to false, wc_rep to false, np_rep to false, elk_rep to false, sp1_rep to false, sp2_rep to false; END;' language 'sql'; The above body isn't valid for an sql body as well. Sql functions won't have a begin or end, and I don't think those tos will be valid either. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
