Re: Two more RI fast-path issues

From: Amit Langote <amitlangote09(at)gmail(dot)com>
To: Alexander Lakhin <exclusion(at)gmail(dot)com>
Cc: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Two more RI fast-path issues
Date: 2026-11-18 07:12:57
Message-ID: CA+HiwqHEmphFuiku7g-_TicHnF9qY7ojnKa2OCm0ePtYQzffVQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

HI Alexander,

On Wed, Nov 18, 2026 at 3:00 PM Alexander Lakhin <exclusion(at)gmail(dot)com> wrote:
>
> Hello Amit,
>
> 08.10.2026 06:58, Amit Langote wrote:
> > On Wed, Oct 7, 2026 at 10:02 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> >> On Tue, Oct 6, 2026 at 3:20 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> >> ...
> >> Barring comments, would like to push the attached slightly updated
> >> version (comment, commit message fixes) tomorrow after committing the
> >> patches in [1] because I rebased these over those other patches.
> > I've pushed these.
>
> I've accidentially discovered that a test case added with 061065e28 can
> produce a new assertion failure with just:
> - UPDATE fp_pk_cid SET v = v + 1 WHERE id OPERATOR(pg_catalog.=) $1;
> + UPDATE fp_pk_cid SET id = id + 1 WHERE id OPERATOR(pg_catalog.=) $1;
>
> TRAP: failed Assert("entry->data.lockmode == BUFFER_LOCK_UNLOCK"), File: "bufmgr.c", Line: 5941, PID: 2303915
> ExceptionalCondition at assert.c:51:13
> BufferLockAcquire at bufmgr.c:5948:2
> LockBufferInternal at bufmgr.c:6631:3
> LockBuffer at bufmgr.h:340:1
> _bt_lockbuf at nbtpage.c:1072:1
> _bt_getbuf at nbtpage.c:839:2
> _bt_getroot at nbtpage.c:380:13
> _bt_search at nbtsearch.c:113:8
> _bt_search_insert at nbtinsert.c:385:1
> _bt_doinsert at nbtinsert.c:170:10
> btinsert at nbtree.c:225:11
> index_insert at indexam.c:235:9
> ExecInsertIndexTuples at execIndexing.c:450:4
> ExecUpdateEpilogue at nodeModifyTable.c:2372:20
> ExecUpdate at nodeModifyTable.c:2754:19
> ExecModifyTable at nodeModifyTable.c:4801:12
> ExecProcNodeFirst at execProcnode.c:470:1
> ...
> 2026-11-18 07:06:47.374 EET postmaster[2302673] LOG: client backend (PID 2303915) was terminated by signal 6: Aborted
> 2026-11-18 07:06:47.374 EET postmaster[2302673] DETAIL: Failed process was running: INSERT INTO fp_fk_cid VALUES (1);
>
> Could you please have a look?

Thanks for testing.

I looked into this and it doesn't seem to be a bug in the fast-path or
elsewhere. With your change, fp_cid_eq() (the function behind the
index's equality operator) updates the indexed column, so the UPDATE
in it needs to insert a new entry into the index fp_pk_cid_id. But
fp_cid_eq() is called from _bt_readpage(), which holds a share lock on
the leaf page while checking scan keys. So the nested insert seems to
end up trying to lock the same page (the root is the only leaf here)
that the backend has already locked. On master, that fails the
assertion added by fcb9c977aa5. I also checked on 18 just to be sure
and I found that the backend hangs in _bt_search() instead, waiting
for an exclusive lock on the page that it already holds a share lock
on. In fact, a plain index scan using the operator hits the same
assertion on master, with no foreign key involved:

SET enable_seqscan = off;
SELECT * FROM fp_pk_cid WHERE id =~= 1;
server closed the connection unexpectedly
...

That's also why the test updates v and not id, as the comment above it mentions:

-- ... The
-- equality function runs while the scan holds the index page locked; its
-- UPDATE is a HOT update, since v isn't indexed, so it doesn't touch the
-- index.

The test only needs the equality function to update the referenced row
so that the command counter advances before the row lock is taken, and
a HOT update is enough for that.

--
Thanks, Amit Langote

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Kapila 2026-11-18 07:13:01 Re: Hash index: stop registering pin-only bucket pages in WAL (remove REGBUF_NO_CHANGE)
Previous Message Chao Li 2026-11-18 07:06:57 Re: [PATCH v1] Avoid deforming unused trailing columns in COPY TO