Re: Creation of temporary tables on a publisher

wrong mailing list. My apologies.
--cnemelka

On Mon, Jan 28, 2019 at 11:27 AM Cory Nemelka <cnemelka(at)gmail(dot)com> wrote:

> 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(at)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(at)gmail(dot)com>
> 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 <cnemelka(at)gmail(dot)com> 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(at)gmail(dot)com>
>>> 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 <cnemelka(at)gmail(dot)com>
>>>> 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 <
>>>>> shreeyansh2014(at)gmail(dot)com> 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 <cnemelka(at)gmail(dot)com>
>>>>>> 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
>>>
>>