Re: DDL deparse

Dear Masahiko Sawada, All

> Agreed. I think that generating DDL while keeping the user intent
> would cover both cases.

> I think CTAS needs special handling for DDL replication purposes; it
> can be replicated as CREATE TABLE + INSERT. However, if DDL deparse
> expands CTAS into CREATE TABLE + INSERT, it might be against the
> policy that DDL deparse would preserve user intent. So an idea is that
> DDL deparse has two output modes for CTAS: one generates CREATE TABLE
> ... AS to preserve the user intent (for audit cases etc.) and another
> one generates CREATE TABLE. DDL replication would use the latter mode
> to capture the CREATE TABLE.

Some more thoughts...

Scenarios, that require to keep the original query, may relate to other
types of solutions, than DDL replication. For example, logging and auditing
solutions do not require DDL to be applied. Solutions may just log the
original query unchanged. There is no need to support such solutions
in the core. Utility hook seems to be the right choice.

Solutions, like WAL miners, may require the original query if a DBA
wants to look closely on some WAL changes, but it is not a strict
requirement if such solutions are used for CDC purposes.

For replication purpose we must guarantee that the replica (or an
external client) gets the right set of commands, that reproduce the
schema on the master. A number of simple commands instead a single
command looks ok. I guess, for external clients it would be better
to receive small simple commands instead of a single complex command.

I guess, we may optionally keep both the original query and the final
set of simple DDL commands suitable for replication purposes.

Furthermore, an original user query like SELECT f() may be finally
represented in the form of CREATE TABLE, if f() creates a new table.
Which query should we get for logging or auditing purposes?

>>
>> Query 3: CTAS with a function parameter
>>
>> CREATE TABLE t AS SELECT @mypar
> This is not supported in PostgreSQL.

Sorry, I mean a query like below, where a function variable is used:

CREATE OR REPLACE FUNCTION temp_x () RETURNS void
AS $$
DECLARE
_x integer;
BEGIN
CREATE TABLE t_b AS SELECT _x;
END;
$$ LANGUAGE plpgsql;

> Or you might mean generating DDL from the WAL records of system
> catalog changes. IIUC it reconstructs the system catalog state as DDL
> and so loses the user intent. For instance, in v19 the default TOAST
> compression became lz4, while it was pglz before. If we had DDL
> replication in v18 and a user set up a logical replication v18 -> v19,
> I think they would not want new tables on v19 to be forced to pglz
> just because that was the publisher's default. but if they
> deliberately chose pglz, they'd want it preserved. We cannot know
> whether a user explicitly specified pglz when creating the table by
> just looking at the system catalogs.

I mean to decode DDL changes from the WAL on the master and convert it
into a suitable form (sql or json) before sending to a replica or a
client.

Thank you for mentioning such example. I agree, it may be hard to define,
whether a specific setting was explicitly set by user or not. There are
some possible solutions may be like: (1) ignore the setting if it has
a default value, (2) do not replicate (or optionally replicate) some
specific settings like toast compression method.

With best regards,
Vitaly