After 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.
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;
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?
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.
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?
Here are the commands and the log showing how you can recreate this issue (bold added):
CREATE PUBLICATION TESTPUB FOR ALL TABLES;
CREATE TEMP TABLE testing123 ON COMMIT DROP AS ( SELECT 1 AS updated_by );
UPDATE testing123 SET updated_by = 2;
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
Time: 2.004 ms
Time: 0.115 ms
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
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?
On Sun, Jan 27, 2019 at 2:12 AM Shreeyansh Dba <[hidden email]> wrote:
wrong mailing list. My apologies.
On Mon, Jan 28, 2019 at 11:27 AM Cory Nemelka <[hidden email]> wrote:
|Free forum by Nabble||Edit this page|