Search Postgresql Archives

Re: VACUUM anomoly: FIXED in 8.0.4

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux