Thread: BUG #15063: Updates to temporary tables fail when there is apublication with FOR ALL TABLES

The following bug has been logged on the website:

Bug reference:      15063
Logged by:          Dan Goodliffe
Email address:      dan@randomdan.homeip.net
PostgreSQL version: 10.2
Operating system:   Linux
Description:

Given a publication which uses FOR ALL TABLES, so that future tables are
included...

Create a temporary table with no keys or identity etc:
LOG:  statement: CREATE TEMPORARY TABLE tmp_masks(atomSpec text[],dateAdded
date,email text,message text,n int,person text)

Execute an update against the table, the update fails:
ERROR:  cannot update table "tmp_masks" because it does not have a replica
identity and publishes updates
HINT:  To enable updating the table, set REPLICA IDENTITY using ALTER
TABLE.
STATEMENT:  UPDATE tmp_masks SET dateadded = dateadded + INTERVAL '1000
years'  WHERE dateadded < '1400-01-01'

(yes, the SQL is a hack to fix typos)

I'm not sure if the temporary table has been added to the publication (which
isn't supported) or if the presence of the FOR ALL TABLES publication alone
(without the table being added to it) is enough to trigger the problem.

I can provide more debug/details etc if required given an idea of what's
needed and how to get it.

Thanks.

On Tuesday, February 13, 2018, PG Bug reporting form <noreply@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference:      15063
Logged by:          Dan Goodliffe
Email address:      dan@randomdan.homeip.net
PostgreSQL version: 10.2
Operating system:   Linux
Description:

Given a publication which uses FOR ALL TABLES, so that future tables are
included...

[...]

I'm not sure if the temporary table has been added to the publication (which
isn't supported) or if the presence of the FOR ALL TABLES publication alone
(without the table being added to it) is enough to trigger the problem.

I can provide more debug/details etc if required given an idea of what's
needed and how to get it.

Thanks David, I'm a Gentoo user... so actually I do... I can test (for my use case at least) any candidate fixes etc.... just need to know what/where they are.

I'm a C/C++ developer by profession... so I don't mind get my hands a little dirty there if I can be of some help.

Regards

Dan

On Tuesday, February 13, 2018, PG Bug reporting form <noreply@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference:      15063
Logged by:          Dan Goodliffe
Email address:      dan@randomdan.homeip.net
PostgreSQL version: 10.2
Operating system:   Linux
Description:

Given a publication which uses FOR ALL TABLES, so that future tables are
included...

[...]

I'm not sure if the temporary table has been added to the publication (which
isn't supported) or if the presence of the FOR ALL TABLES publication alone
(without the table being added to it) is enough to trigger the problem.

I can provide more debug/details etc if required given an idea of what's
needed and how to get it.

A similar report regarding materialized views came through the other week and though I haven't seen a patch as of yet I believe the cause is at least understood.  Unfortunately a minor release just came out so it will be a couple of months proabaly before a fix is released - unless you can compile from source (for production if not just for testing purposes).

For now, "for all tables" mode is broken unless all you really have are normal tables.

David J.

-- 
Dan Goodliffe
dan@randomdan.homeip.net
07970 523693

Anyway, a patch was proffered on the thread I was referring to but hasn't been acted upon that I recall.

Awesome, I'll give it a try over the next few days. Thanks.

​The convention here is to inline or bottom-post responses (and trimming unnecessary quoted material).

Noted.

-- 
Dan Goodliffe
dan@randomdan.homeip.net
07970 523693

Anyway, a patch was proffered on the thread I was referring to but hasn't been acted upon that I recall.

Tried the patch, but I'm afraid it made no difference in the temporary tables case. Tried adding a check that relation persistence was logged that was in the same vein to the one in the patch (figured the problem could also occur for non-temporary tables that were unlogged, but haven't checked) but that made no difference either. I stopped tinkering at that point cos I don't really know what I'm doing.