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 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
