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

Поиск
Список
Период
Сортировка
От Craig Ringer
Тема [HACKERS] Re: [SQL] Postgresql “alter column type” creates an event which contains “temp_table_xxx”
Дата
Msg-id CAMsr+YFnLa4PYByxUTY+9Vog9RfCCCkUUjcpJPJ=MopDL0eG-w@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [HACKERS] [SQL] Postgresql “alter column type” creates an event which contains “temp_table_xxx”  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-hackers
Дерево обсуждения
On 25 July 2017 at 22:18, Tom Lane <tgl@sss.pgh.pa.us> wrote:
\xC1\xBA\xFFZehra Gül Çabuk <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.

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

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: [HACKERS] pl/perl extension fails on Windows
Следующее
От: Tom Lane
Дата:
Сообщение: Re: [HACKERS] VACUUM and ANALYZE disagreeing on what reltuples means