Horton
Menu

Re: Pgoutput not capturing the generated columns

Peter Smith <smithpb2250@gmail.com>

View thread
From: Peter Smith <smithpb2250@gmail.com>
To: vignesh C <vignesh21@gmail.com>
Cc: Amit Kapila <amit.kapila16@gmail.com>, Michael Paquier <michael@paquier.xyz>, "Shinoda, Noriyoshi (SXD Japan FSIP)" <noriyoshi.shinoda@hpe.com>, Shubham Khanna <khannashubham1197@gmail.com>, Masahiko Sawada <sawada.mshk@gmail.com>, Rajendra Kumar Dangwal <dangwalrajendra888@gmail.com>, "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>, "euler@eulerto.com" <euler@eulerto.com>
Date: 2025-01-19T01:09:11Z
Lists: pgsql-hackers 
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?

======
Kind Regards,
Peter Smith.
Fujitsu Australia

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Doc: Generated column replication.

  2. Rename pubgencols_type to pubgencols in pg_publication.

  3. Doc: Fix column name in pg_publication catalog.

  4. Fix buildfarm failure introduced by commit e65dbc9927.

  5. Change publication's publish_generated_columns option type to enum.

  6. Fix \dRp+ output when describing publications with a lower server version.

  7. Replicate generated columns when 'publish_generated_columns' is set.

  8. Doc: Update the behavior of generated columns in Logical Replication.

  9. Replicate generated columns when specified in the column list.

  10. Doc: Generated columns are skipped for logical replication.