ERROR: virtual tuple table slot does not have system attributes

On Fri, Jun 28, 2019 at 5:38 AM Roby <pacman@finefun.com.au> wrote:

Hi,

Thank you all very much for your work on Postgres.

I have encountered the following error in 12b2 in code that has been
working fine for a while in version 11.

A test script is attached, the output of which follows.

Thanks for the reproducible test case. This bisects down to this commit,

commit 277cb789836b5ddf81aabb80c2058268c70e2f36
Author: Andres Freund <andres@anarazel.de>
Date: Wed Mar 6 15:43:33 2019 -0800

Don't reuse slots between root and partition in ON CONFLICT ... UPDATE.

I was a little surprised it was this particular commit, because your test
doesn't use partitioning.

I don't know how to investigate the issue further, so I'm copying Andres
and will add it to the open issues page.

Cheers,

Jeff

Hi,

On June 29, 2019 5:47:18 PM PDT, Jeff Janes <jeff.janes@gmail.com> wrote:

On Fri, Jun 28, 2019 at 5:38 AM Roby <pacman@finefun.com.au> wrote:

Hi,

Thank you all very much for your work on Postgres.

I have encountered the following error in 12b2 in code that has been
working fine for a while in version 11.

A test script is attached, the output of which follows.

Thanks for the reproducible test case. This bisects down to this
commit,

commit 277cb789836b5ddf81aabb80c2058268c70e2f36
Author: Andres Freund <andres@anarazel.de>
Date: Wed Mar 6 15:43:33 2019 -0800

Don't reuse slots between root and partition in ON CONFLICT ... UPDATE.

I was a little surprised it was this particular commit, because your
test
doesn't use partitioning.

I don't know how to investigate the issue further, so I'm copying
Andres
and will add it to the open issues page.

Thanks, will have a look.

Andres
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.

Hi,

On 2019-06-29 17:50:03 -0700, Andres Freund wrote:

On June 29, 2019 5:47:18 PM PDT, Jeff Janes <jeff.janes@gmail.com> wrote:

On Fri, Jun 28, 2019 at 5:38 AM Roby <pacman@finefun.com.au> wrote:

Hi,

Thank you all very much for your work on Postgres.

I have encountered the following error in 12b2 in code that has been
working fine for a while in version 11.

FWIW, this approach to detect whether ON CONFLICT has performed an
update or a delete is quite unreliable - it only works because of an
issue that's somewhere between a missed optimization and a bug. See
/messages/by-id/20190724232439.lpxzjw2jg3ukgcqn@alap3.anarazel.de

Normally the result of an UPDATE will not have an xmax set (unless
there's concurrent FOR KEY SHARE lockers), it's really just an accident
that it does so here. And we'll hopefully fix that. It will also
certainly not work the same for future additional table access methods.

I suggest writing an email to -hackers or such presenting as to why
you'd a proper way to diagnose this. Shouldn't be too hard to add.

A test script is attached, the output of which follows.

Here's a simpler reproducer:

prep:

DROP TABLE IF EXISTS upserttest;
CREATE TABLE upserttest(key text primary key, data text);

postgres[29156][1]=# INSERT INTO upserttest VALUES('1', '1') ON CONFLICT (key) DO UPDATE SET data = 'orig: '||upserttest.data || ' excluded: '||EXCLUDED.data RETURNING xmax;
┌──────┐
│ xmax │
├──────┤
│ 0 │
└──────┘
(1 row)

INSERT 0 1

postgres[29156][1]=# INSERT INTO upserttest VALUES('1', '1') ON CONFLICT (key) DO UPDATE SET data = 'orig: '||upserttest.data || ' excluded: '||EXCLUDED.data RETURNING xmax;
ERROR: XX000: virtual tuple table slot does not have system attributes
LOCATION: tts_virtual_getsysattr, execTuples.c:138
Time: 0.715 ms

The fix for this is trivial, just need to use the table's slot
type. I/We just didn't recognize that was necessary.

diff --git i/src/backend/executor/nodeModifyTable.c w/src/backend/executor/nodeModifyTable.c
index d8b695d897f..b299a246850 100644
--- i/src/backend/executor/nodeModifyTable.c
+++ w/src/backend/executor/nodeModifyTable.c
@@ -2546,7 +2546,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
         tupDesc = ExecTypeFromTL((List *) node->onConflictSet);
         resultRelInfo->ri_onConflict->oc_ProjSlot =
             ExecInitExtraTupleSlot(mtstate->ps.state, tupDesc,
-                                   &TTSOpsVirtual);
+                                   table_slot_callbacks(resultRelInfo->ri_RelationDesc));

/* build UPDATE SET projection state */
resultRelInfo->ri_onConflict->oc_ProjInfo =

(plus comments and tests, of course)

I pushed a fix. Thanks Roby for the report, and Jeff for the bisecting!

Greetings,

Andres Freund