Re: [SQL] Postgresql “alter column type” creates an event which contains “temp_table_xxx”

On 25 July 2017 at 22:18, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> =?UTF-8?B?WmVocmEgR8O8bCDDh2FidWs=?= <zgul(dot)cabuk(at)gmail(dot)com> writes:
> > => ALTER TABLE test ALTER COLUMN x TYPE integer USING
> > (trim(x)::integer);ALTER TABLE
> > Last command I've executed to alter column data type creates an event
> like
> > this:
> > BEGIN 500913table public.pg_temp_1077668: INSERT: x[integer]:14table
> > public.pg_temp_1077668: INSERT: x[integer]:42COMMIT 500913
> > How could I find "real" table name using this record? Is there any way to
> > see real table name in fetched record?
>
> That is the real name --- table rewrites create a table with a temporary
> name and the desired new column layout, then fill it with data, then
> exchange the data area with the old table, then drop the temp table.
>
> Evidently logical decoding is exposing some of this infrastructure
> to you. I bet it isn't exposing the critical "swap data" step though,
> so I wonder how exactly a logical decoding plugin is supposed to make
> sense of what it can see here.

IMO, table rewrite support is less than ideal in logical decoding, and it's
something I'd love to tackle soon. Currently make_new_heap and
finish_heap_swap appear to be completely unaware of logical
decoding/replication. (I'm not sure that's the right level at which to
handle table rewrites yet, but it's a potential starting point).

Rather than emitting normal-looking insert change events for some fake
table name pg_temp_xxx, we should probably invoke a table-rewrite(start)
callback with the original table info, stream the new contents, and call a
table-rewrite(finished) callback. That'd likely just mean one new callback
for the output plugin, a rewrite(oid, bool start|finished)) callback.

Making this work sanely on the apply side might require some work too, but
it's one of the things that's needed to make logical replication more
transparent. The apply side should probably mirror what the originating txn
did, making a new temporary heap, populating it, and swapping it in. This
could result in FK violations if downstream-side tables have extra rows not
present in upstream tables, but that's no worse than regular logical
replication with session_replication_role='replica', and currently falls
into the "don't do that then" category.

We should probably support TRUNCATE the same way. The current mechanism
used in pglogical, capturing truncates with triggers, is a hack
necessitated by logical decoding's lack of support for telling output
plugins about relation truncation. AFAIK in-core logical rep doesn't
natively handle truncation yet, and this is one of the things it'd be good
to do for pg11, especially if more people get interested in contributing.

In the mean time, logical decoding clients can special case "pg_temp_nnnn"
relation names in their output plugins, extracting the oid and looking up
the table being rewritten and handling it that way. Not beautiful but it
offers a workaround.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services