Horton

Test to dump and restore objects left behind by regression

Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>

View thread View on postgr.es
From: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: Peter Eisentraut <peter@eisentraut.org>
Date: 2024-02-21T06:48:45Z
Lists: pgsql-hackers

Attachments

Hi All,
In [1] we found that having a test to dump and restore objects left
behind by regression test is missing. Such a test would cover many
dump restore scenarios without much effort. It will also help identity
problems described in the same thread [2] during development itself.

I am starting a new thread to discuss such a test. Attached is a WIP
version of the test. The test does fail at the restore step when
commit 74563f6b90216180fc13649725179fc119dddeb5 is reverted
reintroducing the problem.

Attached WIP test is inspired from
src/bin/pg_upgrade/t/002_pg_upgrade.pl which tests binary-upgrade
dumps. Attached test tests the non-binary-upgrade dumps.

Similar to 0002_pg_upgrade.pl the test uses SQL dumps before and after
dump and restore to make sure that the objects are restored correctly.
The test has some shortcomings
1. Objects which are not dumped at all are never tested.
2. Since the rows are dumped in varying order by the two clusters, the
test only tests schema dump and restore.
3. The order of columns of the inheritance child table differs
depending upon the DDLs used to reach a given state. This introduces
diffs in the SQL dumps before and after restore. The test ignores
these diffs by hardcoding the diff in the test.

Even with 1 and 2 the test is useful to detect dump/restore anomalies.
I think we should improve 3, but I don't have a good and simpler
solution. I didn't find any way to compare two given clusters in our
TAP test framework. Building it will be a lot of work. Not sure if
it's worth it.

Suggestions welcome.

[1] https://www.postgresql.org/message-id/CAExHW5vyqv%3DXLTcNMzCNccOrHiun_XhYPjcRqeV6dLvZSamriQ%40mail.gmail.com
[2] https://www.postgresql.org/message-id/3462358.1708107856%40sss.pgh.pa.us

--
Best Wishes,
Ashutosh Bapat

Commits

  1. Hide expensive pg_upgrade test behind PG_TEST_EXTRA

  2. Set log_statement=none in t/002_pg_upgrade.pl

  3. 002_pg_upgrade.pl: Move pg_dump test code for better stability

  4. 002_pg_upgrade.pl: rename some variables for clarity

  5. Verify roundtrip dump/restore of regression database

  6. Refactor TAP test code for file comparisons into new routine in Utils.pm

  7. Virtual generated columns

  8. Put generated_stored test objects in a schema

  9. Introduce "builtin" collation provider.

  10. Revert "Improve compression and storage support with inheritance"