Here are the commands and the log showing how you can recreate this issue (bold added):
SQL:
CREATE PUBLICATION TESTPUB FOR ALL TABLES;
BEGIN;
CREATE TEMP TABLE testing123 ON COMMIT DROP AS ( SELECT 1 AS updated_by );
UPDATE testing123 SET updated_by = 2;
COMMIT;
LOG:
psql (11.1 (Ubuntu 11.1-1.pgdg18.04+1), server 10.6 (Ubuntu 10.6-1.pgdg18.04+1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "testpublications" as user "cnemelka".
localhost cnemelka@testpublications=# \e
CREATE PUBLICATION
Time: 2.004 ms
BEGIN
Time: 0.115 ms
SELECT 1
Time: 2.376 ms
ERROR: 55000: cannot update table "testing123" because it does not have a replica identity and publishes updates
HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
LOCATION: CheckCmdReplicaIdentity, execReplication.c:564
Time: 0.483 ms
COMMIT
Time: 1.876 ms
As you can see, despite being a temporary table, testing123 appears to think that it will be replicated. Can we get around this? Yes. The question is: Is this expected behaviour?
--cnemelka
On Sun, Jan 27, 2019 at 2:12 AM Shreeyansh Dba <shreeyansh2014@xxxxxxxxx> wrote:
Hi Cory,
We have tested this scenario, but we haven't noticed such an issue. However, if you have created or updated a temporary table which is automatically dropped at the end of a session.
These tables are not replicated on the subscription side and neither fails the replication as well.On Sat, Jan 26, 2019 at 1:34 AM Cory Nemelka <cnemelka@xxxxxxxxx> wrote:That is my understanding also. However, we are getting the previously mentioned error above when trying to update a temporary table in a database that has other tables published.I’m not sure you understand the issue. We don’t want to temporary tables replicated but they appear to be. As I mentioned, is this expected behavior? Anyone, Bueller?--On Fri, Jan 25, 2019 at 03:30 Shreeyansh Dba <shreeyansh2014@xxxxxxxxx> wrote:Temporary tables can't be part of a replication. In logical replication perform only DML. As well as same structure must be exists on publication and subscribe side.On Thu, Jan 24, 2019 at 10:04 PM Cory Nemelka <cnemelka@xxxxxxxxx> wrote:The issue is the apparent replication of temporary tables. The workarounds you proposed do work but why do postgresql publications replicate temporary tables? they will not be on the subscriber so the replication will fail.As I mentioned, is this expected behavior?--cnemelkaOn Wed, Jan 23, 2019 at 6:58 PM Shreeyansh Dba <shreeyansh2014@xxxxxxxxx> wrote:In Logical replication all tables have a valid default REPLICA IDENTITY (i.e. PK). If suppose there isn't one then You could try to set identity to full, with will use whole row as identifier ALTER TABLE name REPLICA IDENTITY FULL;or if it has unique index ALTER TABLE name USING INDEX index_name;On Thu, Jan 24, 2019 at 4:16 AM Cory Nemelka <cnemelka@xxxxxxxxx> wrote:Postgresql v10.6After defining a publication with "FOR ALL TABLES" clause, temporary tables that are created after the definition will produce errors if they are updated,etc. The error: "cannot update table <tablename> because it does not have a replica identity and publishes updates."Is this expected behaviour? I had understood that temporary tables were not included in publications.--cnemelka--cnemelka
