Обсуждение: Requiring temp tables to have replication identity defined
/* 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 */
ERROR: cannot update table "testing123" because it does not have a replica identity and publishes updates */
ROLLBACK;
Michael Lewis | Software Engineer
Entrata
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
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