Обсуждение: BUG #1527: select retrieves 0 rows after vacuum analyze
The following bug has been logged online: Bug reference: 1527 Logged by: Theo Petersen Email address: tpetersen@ocv.com PostgreSQL version: 7.4 Operating system: Linux (Gentoo) Description: select retrieves 0 rows after vacuum analyze Details: I have a database (network monitoring data created by OpenNMS) that exhibits this behavior: 1) I restore the database from a production backup. 2) I perform a select that joins four tables, and get 9 rows of output. 3) I use the command VACUUM ANALYZE to maintain the database files. 4) I perform the same select and get 0 rows of output. I've reduced it to those steps to verify the problem. Regards, ..Theo
"Theo Petersen" <tpetersen@ocv.com> writes:
> I have a database (network monitoring data created by OpenNMS) that exhibits
> this behavior:
> 1) I restore the database from a production backup.
> 2) I perform a select that joins four tables, and get 9 rows of output.
> 3) I use the command VACUUM ANALYZE to maintain the database files.
> 4) I perform the same select and get 0 rows of output.
> I've reduced it to those steps to verify the problem.
Perhaps you should offer some details that would allow someone else to
replicate the problem.
regards, tom lane
"Theo Petersen" <tpetersen@ocv.com> writes: > I have a database (network monitoring data created by OpenNMS) that exhibits > this behavior: > > 1) I restore the database from a production backup. > 2) I perform a select that joins four tables, and get 9 rows of output. > 3) I use the command VACUUM ANALYZE to maintain the database files. > 4) I perform the same select and get 0 rows of output. > > I've reduced it to those steps to verify the problem. Are you perchance doing joins involving INET or CIDR types where the old database is 7.3.x and the new database is 7.4.x? I ask because I discovered a problem with the 7.4.x = operator when I was (hastily) writing the prototype for the application I think you're working on. http://archives.postgresql.org/pgsql-general/2004-04/msg00453.php If this is the problem you're having, then it should be mentioned on the project Wiki that Chris F. set up, if it still exists. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Mon, Mar 07, 2005 at 09:11:49AM -0700, Theo Petersen wrote: > > I had been running 7.4.7, and I'm back to 7.3.9 now so as to keep my db > working when I restore a backup. Is there any plan to address this in 7.4? I don't think so: http://archives.postgresql.org/pgsql-bugs/2004-06/msg00057.php You should be able to work around it by setting oprcanhash to false for the operator in question, although I never tested that extensively. http://archives.postgresql.org/pgsql-general/2004-04/msg00461.php http://archives.postgresql.org/pgsql-general/2004-04/msg00463.php The problem is fixed in 8.0, so you might want to consider trying 8.0.1. Among other things, it has an improved PL/Perl that might have been handy on that project a year ago. See the Release Notes if you haven't been keeping up with it: http://www.postgresql.org/docs/8.0/interactive/release-8-0.html http://www.postgresql.org/docs/8.0/interactive/release.html#RELEASE-8-0-1 -- Michael Fuhr http://www.fuhr.org/~mfuhr/