The problem described below in 7.4.x, does not occur in 8.0.4, even with
near-simultaneous VACUUMs and updating. Previously, if one VACUUM was
run within a minute or two of the other, the problem below occurred.
-- Dean
On 2005-09-19 09:26, Dean Gibson (DB Administrator) wrote:
Simultaneous VACUUMs in tables in different schemas appear to
interact. Observed in v7.4.5 & 7.4.8 on Fedora Core 1.
Details:
I have a database consisting of several schemas. Two of these schemas
are contain eight tables each (about 700K rows each), which are
populated and updated daily via eMail from US Gov't data. The other
schemas contain smaller tables that are regenerated via PSQL scripts
after each update to the first two schemas, as follows:
1. A schema with a unique, temporary name (based on the process id) is
created, and the tables are created, indexes added, and then a
"VACUUM VERBOSE ANALYZE" is performed
2. In a transaction block, the previous schema is renamed to another
temporary name, and the new schema is renamed to its permanent name.
3. Since any outside views that were defined on the schema now point
to the previous schema, the views are recreated with CREATE OR REPLACE
VIEW statements.
4. The previous schema is DROPped.
In order to detect problems, the PSQL output is logged and compared to
a reference copy. In order to make the comparison useful, all
sequences of digits are replaced by a single "#" before the
comparison. The result of the comparison is eMailed to me.
If the two daily updates eMailed from the gov't arrive at the same
time (as is often the case), then two instances of the above script
(but with different process ids) are started at the same time. This
should not be a problem, as two separate temporary schemas are created
in parallel, and step #2 above should insure that the resulting
desired schema is replaced in an atomic fashion.
All this works fine, EXCEPT for one thing: when I observe the eMailed
comparison, there are no differences UNLESS there have been two
scripts run in parallel as described above. If that happens,
everything is still fine (there is no data corruption), except for the
differences file from one of the scripts, which shows the following
from the "VACUUM VERBOSE ANALYZE" in step #1 above:
05:16:20 ===== Begin dbDiffs for GenAppNew =====
185,186c185
< DETAIL: # index row versions were removed.
< # index pages have been deleted, # are currently reusable.
---
> DETAIL: # index pages have been deleted, # are currently reusable.
189,190c188
< DETAIL: # index row versions were removed.
< # index pages have been deleted, # are currently reusable.
---
> DETAIL: # index pages have been deleted, # are currently reusable.
193,194c191
< DETAIL: # index row versions were removed.
< # index pages have been deleted, # are currently reusable.
---
> DETAIL: # index pages have been deleted, # are currently reusable.
197,198c194
< DETAIL: # index row versions were removed.
< # index pages have been deleted, # are currently reusable.
---
> DETAIL: # index pages have been deleted, # are currently reusable.
200,201d195
< INFO: "_Pending": removed # row versions in # pages
< DETAIL: CPU #.#s/#.#u sec elapsed #.# sec.
05:16:20 ------- End dbDiffs for GenAppNew -----
I can recreate this problem at will, by just manually starting the
script twice in quick succession without changing any of the data. As
a result, I don't believe the differences above are the result of any
differences in the data.
If I rerun the script, the output shows no differences from the
reference log file.
Is this a bug in VACUUM, or something to be expected?
Sincerely, Dean
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
