Bug #731: Same SQL produces different results before and after VACUUM FULL
Henry (henry@ssc-ltd.com) reports a bug with a severity of 1
The lower the number the more severe it is.
Short Description
Same SQL produces different results before and after VACUUM FULL
Long Description
We have run the SQL statement below before and after VACUUM FULL.
Before VACUUM FULL, it returns 3615 records.
After VACUUM FULL, it return 34123 records which is the result I expect.
Before VACUUM FULL, if I change the LEFT OUTER JOIN to join the table a, it returns 34123 records.
I have a daily backup job which executes VACUUM ANALYSE daily.
I don't know what causes the problem. Please help!!!!
Sample Code
SELECT *
from (SELECT pbdedup_party_1.party_id
from pbdedup_party_1
where pbdedup_party_1.batch_no = ?
UNION
SELECT pbdedup_party_2.party_id
from pbdedup_party_2
where pbdedup_party_2.batch_no = ?
) as a
, pb_person
left outer join pbdedup_person_parts ON pb_person.party_id = pbdedup_person_parts.party_id
where a.party_id = pb_person.party_id
No file was uploaded with this report
pgsql-bugs@postgresql.org writes:
Same SQL produces different results before and after VACUUM FULL
What Postgres version is this?
What does EXPLAIN show for the query plan in each state?
regards, tom lane
Dear Tom,
Our Postgresql version is 7.2. We have not upgraded to 7.2.1 yet.
I cannot simulate what happened that time. But however, during that
time, we have also tried the 2 SQL in the attached file but also
produced different results before VACUUM FULL.
(I expect them to produce same result)
However, they produced same result after VACUUM FULL.
I hope this could help your investigation.
Thanks.
Regards,
Henry
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, August 07, 2002 9:46 PM
To: henry@ssc-ltd.com; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] Bug #731: Same SQL produces different results before
and after VACUUM FULL
pgsql-bugs@postgresql.org writes:
Same SQL produces different results before and after VACUUM FULL
What Postgres version is this?
What does EXPLAIN show for the query plan in each state?
regards, tom lane