From: | Cory Nemelka <cnemelka(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Fwd: Creation of temporary tables on a publisher |
Date: | 2019-01-28 19:02:58 |
Message-ID: | CAMe5Gn1ovYVy7hp7wYqxjSbV4obwkkK1S_zU-GLK10163cweDw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-sql |
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
>>
>>
From | Date | Subject | |
---|---|---|---|
Next Message | Ibrahim Shaame | 2019-01-28 21:11:49 | Re: Building pgadmin4 on Slackware |
Previous Message | Cory Nemelka | 2019-01-28 19:02:21 | Re: Creation of temporary tables on a publisher |
From | Date | Subject | |
---|---|---|---|
Next Message | Sven Berkvens-Matthijsse | 2019-02-01 16:06:56 | Idea: INSERT INTO ... NATURAL SELECT ... |
Previous Message | Cory Nemelka | 2019-01-28 19:02:21 | Re: Creation of temporary tables on a publisher |