Testing DDL Deparser

Hello:

I’m working on developing a testing harness for the DDL Deparser being
worked on in [1], please apply the patches in [1] before apply this
patch. I think the testing harness needs to achieve the following
goals:

1. The deparsed JSON output is as expected.
2. The SQL commands re-formed from deparsed JSON should make the same
schema change as the original SQL commands.
3. Any DDL change without modifying the deparser should fail the
testing harness.

Based on these 3 goals, we think the deparser testing harness should
have 2 parts: the first part is unit testing to cover the first two
goals and the second part is integrating deparser test with pg_regress
to cover the third goal.

I think the unit test part can be based on
https://git.postgresql.org/gitweb/?p=postgresql.git;a=tree;f=src/test/modules/test_ddl_deparse;hb=HEAD.
We can improve upon this test by using it to validate the output JSON
and the re-formed SQL from the deparsed JSON.

[2] and [3] proposed using regression tests to test deparser and
provided an implementation using TAP framework. I made some changes
which enables testing any SQL file under the folder provided in
$inputdir variable. This implementation enables us to run test cases
under regression tests folder, or just any test cases using a SQL
file.

I came up with some ideas during the investigation and want to collect
some feedback:
1, Currently we want to utilize the test cases from regression tests.
However you will find that many test cases end with DROP commands. In
current deparser testing approach proposed in [2] and [3], we compare
the pg_dump schema results between the original SQL scripts and
deparser generated commands. Because most test cases end with DROP
command, the schema will not be shown in pg_dump, so the test coverage
is vastly reduced. Any suggestion to this problem?

2, We found that DROP command are not returned by
pg_event_trigger_ddl_commands() fucntion in ddl_command_end trigger,
but it’s caught by ddl_command_end trigger. Currently, we catch DROP
command in sql_drop trigger. It’s unclear why
pg_event_trigger_ddl_commands() function is designed to not return
DROP command.

3, For unsupported DDL commands by the deparser, the current
implementation just skips them silently. So we cannot detect
unsupported DDL commands easily. Customers may also want the deparser
related features like logical replication to be executed in a strict
mode, so that the system can warn them when deparser can not deparse
some DDL command. So I propose to introduce a new GUC such as
“StopOnDeparserUnsupportedCommand = true/false” to allow the deparser
to execute in strict mode, in which an unsupported DDL command will
raise an error.

4, We found that the event trigger function
pg_event_trigger_ddl_commands() only returns subcommands, and deparser
is deparsing subcommands returned by this function. The deparser works
on subcommand level by using this function, but the deparser is
designed to deparse the complete command to JSON output. So there is a
mismatch here, what do you think about this problem? Should the
deparser work at subcommand level? Or should we provide some event
trigger function which can return the complete command instead of
subcommands?

Your feedback is appreciated.

Regards,
Runqi Tian
Amazon RDS/Aurora for PostgreSQL

[1] https://www.postgresql.org/message-id/CALDaNm0VnaCg__huSDW%3Dn%3D_rSGGES90cpOtqwZeWnA6muoz3oA%40mail.gmail.com
[2] https://www.postgresql.org/message-id/CAD21AoBVCoPPRKvU_5-%3DwEXsa92GsNJFJOcYyXzvoSEJCx5dKw%40mail.gmail.com
[3] https://www.postgresql.org/message-id/20150215044814.GL3391@alvh.no-ip.org