Postgresql “alter column type” creates an event which contains “temp_table_xxx”
I'm using "logical wal decoding" feature of postgresql. I've executed a
sample for data insert, update, delete operations successfully. Now I'm
trying to change my table schema like:
=> CREATE TABLE test( x varchar );CREATE TABLE=> insert into test(x)
values ('14'), ('42');INSERT 0 2=>
=> 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?
Thanks in advance.
--
Zehra Gül ÇABUK
=?UTF-8?B?WmVocmEgR8O8bCDDh2FidWs=?= <zgul.cabuk@gmail.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.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 25 July 2017 at 22:18, Tom Lane <tgl@sss.pgh.pa.us> wrote:
=?UTF-8?B?WmVocmEgR8O8bCDDh2FidWs=?= <zgul.cabuk@gmail.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 eventlike
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