Fwd: Creation of temporary tables on a publisher

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 <[email protected]>
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.
>
> Thanks & Regards,
> *Shreeyansh DBA Team*
> www.shreeyansh.com
>
> On Sat, Jan 26, 2019 at 1:34 AM Cory Nemelka <[email protected]> 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 <[email protected]>
>> 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.
>>>
>>> Thanks & Regards,
>>> *Shreeyansh DBA Team*
>>> www.shreeyansh.com
>>>
>>>
>>> On Thu, Jan 24, 2019 at 10:04 PM Cory Nemelka <[email protected]>
>>> 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?
>>>>
>>>> --cnemelka
>>>>
>>>>
>>>> On Wed, Jan 23, 2019 at 6:58 PM Shreeyansh Dba <
>>>> [email protected]> 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;
>>>>>
>>>>> Thanks & Regards,
>>>>> *Shreeyansh DBA Team*
>>>>> www.shreeyansh.com
>>>>>
>>>>>
>>>>> On Thu, Jan 24, 2019 at 4:16 AM Cory Nemelka <[email protected]>
>>>>> wrote:
>>>>>
>>>>>> Postgresql v10.6
>>>>>>
>>>>>> 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.
>>>>>>
>>>>>>
>>>>>> --cnemelka
>>>>>>
>>>>> --
>> --cnemelka
>>
>>