Re: Requiring temp tables to have replication identity defined

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:

https://www.postgresql.org/message-id/f3f151f7-c4dd-1646-b998-f60bd6217dd3%402ndquadrant.com

Thanks,
Amit