CODEWITHSUNDEEP
postgresql
Jonathan Ong
Jonathan Ong

Reputation: 20325

postgresql insert on conflict triggers attempted to delete invisible tuple error

i am sometimes seeing the following error in my logs when inserting the same data twice:

error: attempted to delete invisible tuple
    at Connection.parseE (/home/ubuntu/vacation-server/node_modules/pg/lib/connection.js:554:11)
    at Connection.parseMessage (/home/ubuntu/vacation-server/node_modules/pg/lib/connection.js:381:17)
    at Socket.<anonymous> (/home/ubuntu/vacation-server/node_modules/pg/lib/connection.js:117:22)
    at emitOne (events.js:96:13)
    at Socket.emit (events.js:188:7)
    at readableAddChunk (_stream_readable.js:176:18)
    at Socket.Readable.push (_stream_readable.js:134:10)
    at TCP.onread (net.js:548:20)

my query is as follows:

    INSERT INTO google_places (
      id,
      data,
      geographic_coordinates
    ) VALUES (
      $1,
      $2,
      ST_GeomFromText('POINT(-118.440908 34.063246)', 4326)
    )
    ON CONFLICT (id) DO UPDATE
      SET data = EXCLUDED.data,
        geographic_coordinates = EXCLUDED.geographic_coordinates,
        date_updated = CURRENT_TIMESTAMP
    RETURNING *

any ideas?

Upvotes: 1

Views: 402

Answers (1)

dmfay
dmfay

Reputation: 2477

In versions before 9.5.5, upserting TOASTed data can raise this exception in certain specific circumstances. I ran into it with a conflict key based on a function index (specifically ON CONFLICT (md5(myblob)) DO UPDATE), but it's the same error.

This thread from the mailing list has the full details. The bug has been fixed in 9.5.5 and 9.6, but I don't know of any easy workaround for earlier versions of Postgres. I ended up having to use an old-style CTE upsert.

Upvotes: 4

Related Questions