| Lists: | pgsql-bugs |
|---|
| From: | Michael Lewis <mlewis(at)entrata(dot)com> |
|---|---|
| To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
| Subject: | Requiring temp tables to have replication identity defined |
| Date: | 2019-03-13 20:26:51 |
| Message-ID: | CAHOFxGr=mqPZXbAuoR7Nbq-bU4HxqVWHbTTUy5=PKQut_F0=XA@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-bugs |
/* select version();
PostgreSQL 10.6 (Ubuntu 10.6-1.pgdg18.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 7.3.0-27ubuntu1~18.04) 7.3.0, 64-bit */
To reproduce, try the below code. Uncomment the alter table line and the
error is gone as expected. I would expect the identity should not be need
to be defined on a temp table since it won't be replicated anyway.
BEGIN;
CREATE publication test1 FOR ALL TABLES;
CREATE TEMP TABLE testing123 ON COMMIT DROP AS ( SELECT 1 AS value );
/* ALTER TABLE pg_temp.testing123 REPLICA IDENTITY FULL; */
UPDATE testing123 SET value = 2;
/*OUTPUT:
ERROR: cannot update table "testing123" because it does not have a replica
identity and publishes updates */
ROLLBACK;
*Michael Lewis | Software Engineer*
*Entrata*
| From: | Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> |
|---|---|
| To: | Michael Lewis <mlewis(at)entrata(dot)com>, pgsql-bugs(at)lists(dot)postgresql(dot)org |
| Subject: | Re: Requiring temp tables to have replication identity defined |
| Date: | 2019-03-14 02:35:37 |
| Message-ID: | dc903f6a-d42c-c06d-827a-adb544ff59db@lab.ntt.co.jp |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | Postg롤 토토SQL : Postg롤 토토SQL 메일 링리스트 : 2019-03-14 이후 PGSQL-BUGS 02:35 |
On 2019/03/14 5:26, Michael Lewis wrote:
> /* select version();
> PostgreSQL 10.6 (Ubuntu 10.6-1.pgdg18.04+1) on x86_64-pc-linux-gnu,
> compiled by gcc (Ubuntu 7.3.0-27ubuntu1~18.04) 7.3.0, 64-bit */
>
> To reproduce, try the below code. Uncomment the alter table line and the
> error is gone as expected. I would expect the identity should not be need
> to be defined on a temp table since it won't be replicated anyway.
>
> BEGIN;
> CREATE publication test1 FOR ALL TABLES;
> CREATE TEMP TABLE testing123 ON COMMIT DROP AS ( SELECT 1 AS value );
> /* ALTER TABLE pg_temp.testing123 REPLICA IDENTITY FULL; */
> UPDATE testing123 SET value = 2;
>
> /*OUTPUT:
> ERROR: cannot update table "testing123" because it does not have a replica
> identity and publishes updates */
> ROLLBACK;
It's a bug. A patch to fix this has been posted and is being discussed on
the development mailing list:
/message-id/f3f151f7-c4dd-1646-b998-f60bd6217dd3%402ndquadrant.com
Thanks,
Amit
| From: | Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> |
|---|---|
| To: | Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>, Michael Lewis <mlewis(at)entrata(dot)com>, pgsql-bugs(at)lists(dot)postgresql(dot)org |
| Subject: | Re: Requiring temp tables to have replication identity defined |
| Date: | 2019-04-18 08:15:02 |
| Message-ID: | 6b586788-6f05-7c1e-d235-6865bcd22700@2ndquadrant.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-bugs |
On 2019-03-14 03:35, Amit Langote wrote:
>> ERROR: cannot update table "testing123" because it does not have a replica
>> identity and publishes updates */
>> ROLLBACK;
> It's a bug. A patch to fix this has been posted and is being discussed on
> the development mailing list:
A fix for this has been committed and backpatched. It will be in the
May minor releases.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services