Re: Pgoutput not capturing the generated columns

On Sun, 19 Jan 2025 at 06:39, Peter Smith <smithpb2250@gmail.com> wrote:
>
> Hi Vignesh,
>
> I was having some second thoughts about this patch and my previous suggestion.
>
> Currently the code is current written something like:
>
> printfPQExpBuffer(&buf,
>  "SELECT oid, pubname,\n"
>  "  pg_catalog.pg_get_userbyid(pubowner) AS owner,\n"
>  "  puballtables, pubinsert, pubupdate, pubdelete");
>
> if (has_pubtruncate)
>  appendPQExpBufferStr(&buf, ", pubtruncate");
>
> if (has_pubgencols)
>  appendPQExpBufferStr(&buf, ", pubgencols");
>
> if (has_pubviaroot)
>  appendPQExpBufferStr(&buf, ", pubviaroot");
>
> ~~
>
> IIUC the variable number of result columns (for different server
> versions) is what is causing all the subsequent hassles.
>
> So, wouldn't the easiest fix be to change the code by adding the
> appropriate 'else' alias for when the column is not available?
>
> Like this:
>
> printfPQExpBuffer(&buf,
>  "SELECT oid, pubname,\n"
>  "  pg_catalog.pg_get_userbyid(pubowner) AS owner,\n"
>  "  puballtables, pubinsert, pubupdate, pubdelete");
>
> if (has_pubtruncate)
>  appendPQExpBufferStr(&buf, ", pubtruncate");
> else
>  appendPQExpBufferStr(&buf, ", 'f' AS pubtruncate");
>
> if (has_pubgencols)
>  appendPQExpBufferStr(&buf, ", pubgencols");
> else
>  appendPQExpBufferStr(&buf, ", 'f' AS pubgencols");
>
> if (has_pubviaroot)
>  appendPQExpBufferStr(&buf, ", pubviaroot");
> else
>  appendPQExpBufferStr(&buf, ", 'f' AS pubviaroot");
>
> ~~
>
> Unless I am mistaken this will simplify the subsequent code a lot because:
> 1. Now you can put the cols in the same order you want to display them
> 2. Now the tuple result has a fixed number of cols for all server versions
> 3. Now hardcoding the indexes (1,2,3,4...) is fine because they are
> always the same
>
> Thoughts?

We typically use this approach when performing a dump, where we
retrieve the default values for older versions and store them in a
structure. The values are then included in the dump only if they
differ from the default. However, this approach cannot be used with
psql because older version servers may not have these columns. As a
result, we must avoid displaying these columns when interacting with
older version servers.

Regards,
Vignesh