Creation of temporary tables on a publisher

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Creation of temporary tables on a publisher

Cory Nemelka
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
Reply | Threaded
Open this post in threaded view
|

Re: Creation of temporary tables on a publisher

Shreeyansh dba
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 <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: Creation of temporary tables on a publisher

Cory Nemelka
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 <[hidden email]> 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 <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: Creation of temporary tables on a publisher

Shreeyansh dba
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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: Creation of temporary tables on a publisher

Cory Nemelka
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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: Creation of temporary tables on a publisher

Cory Nemelka
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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: Creation of temporary tables on a publisher

Cory Nemelka
wrong mailing list. My apologies.
--cnemelka


On Mon, Jan 28, 2019 at 11:27 AM Cory Nemelka <[hidden email]> 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@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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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