Horton

Thread

Commits

  1. Fix system column accesses in ON CONFLICT ... RETURNING.

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

  1. ERROR: virtual tuple table slot does not have system attributes

    Roby <pacman@finefun.com.au> — 2019-06-28T03:31:14Z

    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.




> DROP TABLE
> DROP FUNCTION
> CREATE TABLE
> CREATE FUNCTION
>  test_upsert 
> -------------
>            1
> (1 row)
> 
>  test_upsert 
> -------------
>            0
> (1 row)
> 
> psql:test.sql:29: ERROR:  virtual tuple table slot does not have system attributes
> CONTEXT:  SQL function "test_upsert" statement 1



Regards,
Rob.

  2. Re: ERROR: virtual tuple table slot does not have system attributes

    Jeff Janes <jeff.janes@gmail.com> — 2019-06-30T00:47:18Z

    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

  3. Re: ERROR: virtual tuple table slot does not have system attributes

    Andres Freund <andres@anarazel.de> — 2019-06-30T00:50:03Z

    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.

  4. Re: ERROR: virtual tuple table slot does not have system attributes

    Andres Freund <andres@anarazel.de> — 2019-07-25T01:54:48Z

    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
https://www.postgresql.org/message-id/20190724232439.lpxzjw2jg3ukgcqn%40alap3.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