Schema Diff regression test swallows its own assertions, so invalid generated SQL passes silently

[dpage]

Bug Description

SchemaDiffTestCase (web/pgadmin/tools/schema_diff/tests/test_schema_diff_comp.py) is meant to be our end-to-end guarantee that the script Schema Diff generates actually applies and settles the difference: it compares two databases restored from source.sql and target.sql, writes every diff_ddl into a file, restores that file onto the target, and re-compares expecting everything to come out identical.

The final step is wrapped as:

        try:
            restore_schema(self.server, self.tar_database, self.schema_name,
                           diff_file)

            os.remove(diff_file)

            response_data = self.compare()
            for diff in response_data:
                self.assertEqual(diff['status'], 'Identical')
        except Exception as e:
            if os.path.exists(diff_file):
                os.remove(diff_file)

so a failed restore, a psycopg error, and the assertEqual() itself are all caught and discarded. The test reports a pass no matter what the generated script does.

It is not hypothetical: every run of python regression/runtests.py --pkg tools.schema_diff prints

syntax error at or near ")"
LINE 995: );

whilst still reporting 2 tests passed. The generated script does not apply cleanly today, and the harness is built so that nobody finds out.

Expected Behaviour

The restore of the generated script and the follow-up comparison should both be asserted, so that invalid or incomplete generated SQL fails the suite. The temporary diff file should still be cleaned up either way, and a failure should say which statement did not apply.

Context

Found whilst fixing #10236 (commit 01e5bf0). Fixing the harness means fixing whatever invalid SQL it then surfaces, hence filing this separately.