Re: VACUUM anomoly: FIXED in 8.0.4 - Mailing list pgsql-general

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