Deadlock when using pipeline mode #685

wemoloh · 2023-11-27T21:56:30Z

When inserting a large amount of data, psycopg hangs when using pipeline mode. It does not hang if pipeline mode is not used (but it is much slower when the amount of inserted data is small).

Platform: Windows Server 2019
psycopg version: 3.1.13
PostgreSQL version: 15.2
Python version: 3.11.5

For example, given the following definitions:

create table test (bigdata text[]);

from psycopg import connect

def insert_a_lot(cur, nrows, nchars, nvals):
    for _ in range(nrows):
        data = ['a' * nchars] * nvals
        cur.execute('insert into test (bigdata) values (%s)', (data,))

...the following code executes as expected:

with connect(TESTDB) as conn, conn.cursor() as cur:
    insert_a_lot(cur, 10, 1000, 1000)

...but the following code just hangs forever. The database reports the connection is left in 'ClientRead' state:

with connect(TESTDB) as conn, conn.cursor() as cur:
    with conn.pipeline():
        insert_a_lot(cur, 10, 1000, 1000)

dvarrazzo · 2024-01-25T23:45:47Z

Hello,

I have played with the issue. On Linux, with psycopg 3.1.17, using the C extension the script never blocks, using the Python implementation, the script blocks about the 50% of the times.

When it blocks, a ctrl-c reveals:

Traceback (most recent call last):
  File "/home/piro/dev/psycopg3/test-685.py", line 12, in <module>
    with conn.pipeline():
  File "/usr/lib/python3.10/contextlib.py", line 142, in __exit__
    next(self.gen)
  File "/home/piro/dev/psycopg3/psycopg/psycopg/connection.py", line 958, in pipeline
    with pipeline:
  File "/home/piro/dev/psycopg3/psycopg/psycopg/_pipeline.py", line 248, in __exit__
    self._conn.wait(self._exit_gen())
  File "/home/piro/dev/psycopg3/psycopg/psycopg/connection.py", line 974, in wait
    return waiting.wait(gen, self.pgconn.socket, timeout=timeout)
  File "/home/piro/dev/psycopg3/psycopg/psycopg/waiting.py", line 342, in wait_poll
    fileevs = poll.poll(timeout)
KeyboardInterrupt

In these days I'm making some changes to the generators/wait functions in #673 (needed to implement interruptibe notification listenres): I identified some shortcomings that I thought related to this report, but I don't think it's the case and the test fails in that branch too.

Taking a look at Pipeline._exit_gen()...

dvarrazzo · 2024-01-26T00:01:26Z

Using the PSYCOPG_WAIT_FUNC to choose a wait function, and PSYCOPG_IMPL to choose python/c implementation, it seems that all the python wait function (wait_poll, wait_selector, wait_select) block, wait_c doesn't. The Python wait functions block both with Python and C generators.

dvarrazzo · 2024-01-26T00:28:35Z

A few prints showed that the hang happened in generators.fetch, with yield WAIT_R not returning. This seems to suggest that we read too soon from the pipeline, before the data is totally flushed. And, effectively, this change fixes the problem in 3.1.17:

diff --git a/psycopg/psycopg/_pipeline.py b/psycopg/psycopg/_pipeline.py
index 7064f603..6b09c69f 100644
--- a/psycopg/psycopg/_pipeline.py
+++ b/psycopg/psycopg/_pipeline.py
@@ -132,8 +132,7 @@ class BasePipeline:
             self._enqueue_sync()
             yield from self._communicate_gen()
         finally:
-            # No need to force flush since we emitted a sync just before.
-            yield from self._fetch_gen(flush=False)
+            yield from self._fetch_gen(flush=True)
 
     def _communicate_gen(self) -> PQGen[None]:
         """Communicate with pipeline to send commands and possibly fetch

@dlax what do you think? Maybe we used to send a sync and it got lost in a refactoring?

Without it there may be a deadlock and we would be waiting to fetch a result that will never come. Close #685.

dlax · 2024-01-26T08:14:03Z

I cannot reproduce the issue on my side.

Maybe we used to send a sync and it got lost in a refactoring?

Could be 773944b#diff-b72fee89b5b546cb07a77fa3825789c8ad6f3623dca6e3d30a3f376ab60c5f3e, but not sure. The sync is enqueued in the try: block, but maybe it cannot be sent because the output buffer is full on server-side?

dvarrazzo · 2024-01-26T09:41:04Z

Oh, it has been there for a long time, May 2022. Thank you for taking a look, @dlax.

It's curious that the issue doesn't manifest using the C wait function. However that reduces the number of users affected.

@wemoloh are you using only psycopg and not the [binary] or [c] extension?

Without it there may be a deadlock and we would be waiting to fetch a result that will never come. Close #685.

wemoloh · 2024-01-26T23:31:05Z

Hi @dvarrazzo, thank you for investigating this. I am using the [binary] extension. I am on Windows, so #645 makes wait_select the default, but the block still happens even if I set PSYCOPG_WAIT_FUNC=wait_c. I've upgraded to version 3.1.17 but it makes no difference. The only thing that does seem to work is your _exit_gen patch at 3d230d4.

dvarrazzo · 2024-01-26T23:48:09Z

Thank you for confirming that #685 fixes the issue for you too, @wemoloh (and sorry for getting back on this ticket so late). I will try to release a 3.1.18 including this change in the next few days.

Without it there may be a deadlock and we would be waiting to fetch a result that will never come. Close #685.

Bumps [psycopg](https://github.com/psycopg/psycopg) from 3.1.15 to 3.1.18. <details> <summary>Changelog</summary> <p><em>Sourced from <a href="https://github.com/psycopg/psycopg/blob/master/docs/news.rst">psycopg's changelog</a>.</em></p> <blockquote> <p>.. currentmodule:: psycopg</p> <p>.. index:: single: Release notes single: News</p> <h1><code>psycopg</code> release notes</h1> <h2>Future releases</h2> <p>Psycopg 3.2 (unreleased) ^^^^^^^^^^^^^^^^^^^^^^^^</p> <ul> <li>Add support for integer, floating point, boolean <code>NumPy scalar types</code>__ (:ticket:<code>[#332](https://github.com/psycopg/psycopg/issues/332)</code>).</li> <li>Add <code>!timeout</code> and <code>!stop_after</code> parameters to <code>Connection.notifies()</code> (:ticket:<code>340</code>).</li> <li>Add :ref:<code>raw-query-cursors</code> to execute queries using placeholders in PostgreSQL format (<code>$1</code>, <code>$2</code>...) (:ticket:<code>[#560](https://github.com/psycopg/psycopg/issues/560)</code>).</li> <li>Add <code>~rows.scalar_row</code> to return scalar values from a query (:ticket:<code>[#723](https://github.com/psycopg/psycopg/issues/723)</code>).</li> <li>Add <code>~Connection.set_autocommit()</code> on sync connections, and similar transaction control methods available on the async connections.</li> <li>Add support for libpq functions to close prepared statements and portals introduced in libpq v17 (:ticket:<code>[#603](https://github.com/psycopg/psycopg/issues/603)</code>).</li> <li>The <code>!context</code> parameter of <code>sql</code> objects <code>~sql.Composable.as_string()</code> and <code>~sql.Composable.as_bytes()</code> methods is now optional (:ticket:<code>[#716](https://github.com/psycopg/psycopg/issues/716)</code>).</li> <li>Disable receiving more than one result on the same cursor in pipeline mode, to iterate through <code>~Cursor.nextset()</code>. The behaviour was different than in non-pipeline mode and not totally reliable (:ticket:<code>[#604](https://github.com/psycopg/psycopg/issues/604)</code>). The <code>Cursor</code> now only preserves the results set of the last <code>~Cursor.execute()</code>, consistently with non-pipeline mode.</li> </ul> <p>.. __: <a href="https://numpy.org/doc/stable/reference/arrays.scalars.html#built-in-scalar-types">https://numpy.org/doc/stable/reference/arrays.scalars.html#built-in-scalar-types</a></p> <h2>Current release</h2> <p>Psycopg 3.1.18 ^^^^^^^^^^^^^^</p> <ul> <li>Fix possible deadlock on pipeline exit (:ticket:<code>[#685](https://github.com/psycopg/psycopg/issues/685)</code>).</li> <li>Fix overflow loading large intervals in C module (:ticket:<code>[#719](https://github.com/psycopg/psycopg/issues/719)</code>).</li> <li>Fix compatibility with musl libc distributions affected by <code>CPython issue [#65821](https://github.com/psycopg/psycopg/issues/65821)</code>__ (:ticket:<code>[#725](https://github.com/psycopg/psycopg/issues/725)</code>).</li> </ul> <p>.. __: <a href="https://redirect.github.com/python/cpython/issues/65821">python/cpython#65821</a></p>  </blockquote> <p>... (truncated)</p> </details> <details> <summary>Commits</summary> <ul> <li><a href="https://github.com/psycopg/psycopg/commit/8585a23fcd7bcf75193adbc10d3005752ba8f15f"><code>8585a23</code></a> chore: bump psycopg package version to 3.1.18</li> <li><a href="https://github.com/psycopg/psycopg/commit/ab646b70c82aafe6004064a40a3ba358142999a3"><code>ab646b7</code></a> fix(c): drop spurious loop break in pipeline_communicate</li> <li><a href="https://github.com/psycopg/psycopg/commit/bebfe97f934c9136e4db52709ac0fb4dd9cae64d"><code>bebfe97</code></a> chore: bump cibuildwheel version</li> <li><a href="https://github.com/psycopg/psycopg/commit/89394a6f36d42d308a8e672e9b5deef8e76254ae"><code>89394a6</code></a> chore: bump checkout action to v4</li> <li><a href="https://github.com/psycopg/psycopg/commit/ed579e51ca9b44af148e55d345e312f58ce12a6f"><code>ed579e5</code></a> docs: fix tickets format</li> <li><a href="https://github.com/psycopg/psycopg/commit/d4a4e8e1447de3446f614a29a8274ef7c4d03d64"><code>d4a4e8e</code></a> Merge branch 'musl-ctypes' into maint-3.1</li> <li><a href="https://github.com/psycopg/psycopg/commit/8bc51e6812cfaedebdd7afff7c86be301d5fbf66"><code>8bc51e6</code></a> docs: mention musl-ctypes workaround in news file</li> <li><a href="https://github.com/psycopg/psycopg/commit/afb040a800b2667a07dc441e8cdb94e55a0dcf65"><code>afb040a</code></a> fix: add <code>libc.so</code> fallback for musl systems to the ctypes impl</li> <li><a href="https://github.com/psycopg/psycopg/commit/06ef0d92109a63fa1a7630804a3a26af0e0a39c9"><code>06ef0d9</code></a> test: drop ineffective marker on fixture</li> <li><a href="https://github.com/psycopg/psycopg/commit/b955118e523c84f5f702d93fd74288ce51ff61db"><code>b955118</code></a> Merge branch 'fix-interval-overflow' into maint-3.1</li> <li>Additional commits viewable in <a href="https://github.com/psycopg/psycopg/compare/3.1.15...3.1.18">compare view</a></li> </ul> </details> <br /> [![Dependabot compatibility score](https://dependabot-badges.githubapp.com/badges/compatibility_score?dependency-name=psycopg&package-manager=pip&previous-version=3.1.15&new-version=3.1.18)](https://docs.github.com/en/github/managing-security-vulnerabilities/about-dependabot-security-updates#about-compatibility-scores) Dependabot will resolve any conflicts with this PR as long as you don't alter it yourself. You can also trigger a rebase manually by commenting `@dependabot rebase`. [//]: # (dependabot-automerge-start) [//]: # (dependabot-automerge-end) --- <details> <summary>Dependabot commands and options</summary> <br /> You can trigger Dependabot actions by commenting on this PR: - `@dependabot rebase` will rebase this PR - `@dependabot recreate` will recreate this PR, overwriting any edits that have been made to it - `@dependabot merge` will merge this PR after your CI passes on it - `@dependabot squash and merge` will squash and merge this PR after your CI passes on it - `@dependabot cancel merge` will cancel a previously requested merge and block automerging - `@dependabot reopen` will reopen this PR if it is closed - `@dependabot close` will close this PR and stop Dependabot recreating it. You can achieve the same result by closing it manually - `@dependabot show <dependency name> ignore conditions` will show all of the ignore conditions of the specified dependency - `@dependabot ignore this major version` will close this PR and stop Dependabot creating any more for this major version (unless you reopen the PR or upgrade to it yourself) - `@dependabot ignore this minor version` will close this PR and stop Dependabot creating any more for this minor version (unless you reopen the PR or upgrade to it yourself) - `@dependabot ignore this dependency` will close this PR and stop Dependabot creating any more for this dependency (unless you reopen the PR or upgrade to it yourself) </details> Signed-off-by: dependabot[bot] <support@github.com> Co-authored-by: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com> Co-authored-by: Eduard van Valkenburg <eavanvalkenburg@users.noreply.github.com> Co-authored-by: Evan Mattson <35585003+moonbox3@users.noreply.github.com>

Bumps [psycopg](https://github.com/psycopg/psycopg) from 3.1.15 to 3.1.18. <details> <summary>Changelog</summary> <p><em>Sourced from <a href="https://github.com/psycopg/psycopg/blob/master/docs/news.rst">psycopg's changelog</a>.</em></p> <blockquote> <p>.. currentmodule:: psycopg</p> <p>.. index:: single: Release notes single: News</p> <h1><code>psycopg</code> release notes</h1> <h2>Future releases</h2> <p>Psycopg 3.2 (unreleased) ^^^^^^^^^^^^^^^^^^^^^^^^</p> <ul> <li>Add support for integer, floating point, boolean <code>NumPy scalar types</code>__ (:ticket:<code>[microsoft#332](https://github.com/psycopg/psycopg/issues/332)</code>).</li> <li>Add <code>!timeout</code> and <code>!stop_after</code> parameters to <code>Connection.notifies()</code> (:ticket:<code>340</code>).</li> <li>Add :ref:<code>raw-query-cursors</code> to execute queries using placeholders in PostgreSQL format (<code>$1</code>, <code>$2</code>...) (:ticket:<code>[microsoft#560](https://github.com/psycopg/psycopg/issues/560)</code>).</li> <li>Add <code>~rows.scalar_row</code> to return scalar values from a query (:ticket:<code>[microsoft#723](https://github.com/psycopg/psycopg/issues/723)</code>).</li> <li>Add <code>~Connection.set_autocommit()</code> on sync connections, and similar transaction control methods available on the async connections.</li> <li>Add support for libpq functions to close prepared statements and portals introduced in libpq v17 (:ticket:<code>[microsoft#603](https://github.com/psycopg/psycopg/issues/603)</code>).</li> <li>The <code>!context</code> parameter of <code>sql</code> objects <code>~sql.Composable.as_string()</code> and <code>~sql.Composable.as_bytes()</code> methods is now optional (:ticket:<code>[microsoft#716](https://github.com/psycopg/psycopg/issues/716)</code>).</li> <li>Disable receiving more than one result on the same cursor in pipeline mode, to iterate through <code>~Cursor.nextset()</code>. The behaviour was different than in non-pipeline mode and not totally reliable (:ticket:<code>[microsoft#604](https://github.com/psycopg/psycopg/issues/604)</code>). The <code>Cursor</code> now only preserves the results set of the last <code>~Cursor.execute()</code>, consistently with non-pipeline mode.</li> </ul> <p>.. __: <a href="https://numpy.org/doc/stable/reference/arrays.scalars.html#built-in-scalar-types">https://numpy.org/doc/stable/reference/arrays.scalars.html#built-in-scalar-types</a></p> <h2>Current release</h2> <p>Psycopg 3.1.18 ^^^^^^^^^^^^^^</p> <ul> <li>Fix possible deadlock on pipeline exit (:ticket:<code>[microsoft#685](https://github.com/psycopg/psycopg/issues/685)</code>).</li> <li>Fix overflow loading large intervals in C module (:ticket:<code>[microsoft#719](https://github.com/psycopg/psycopg/issues/719)</code>).</li> <li>Fix compatibility with musl libc distributions affected by <code>CPython issue [#65821](https://github.com/psycopg/psycopg/issues/65821)</code>__ (:ticket:<code>[microsoft#725](https://github.com/psycopg/psycopg/issues/725)</code>).</li> </ul> <p>.. __: <a href="https://redirect.github.com/python/cpython/issues/65821">python/cpython#65821</a></p>  </blockquote> <p>... (truncated)</p> </details> <details> <summary>Commits</summary> <ul> <li><a href="https://github.com/psycopg/psycopg/commit/8585a23fcd7bcf75193adbc10d3005752ba8f15f"><code>8585a23</code></a> chore: bump psycopg package version to 3.1.18</li> <li><a href="https://github.com/psycopg/psycopg/commit/ab646b70c82aafe6004064a40a3ba358142999a3"><code>ab646b7</code></a> fix(c): drop spurious loop break in pipeline_communicate</li> <li><a href="https://github.com/psycopg/psycopg/commit/bebfe97f934c9136e4db52709ac0fb4dd9cae64d"><code>bebfe97</code></a> chore: bump cibuildwheel version</li> <li><a href="https://github.com/psycopg/psycopg/commit/89394a6f36d42d308a8e672e9b5deef8e76254ae"><code>89394a6</code></a> chore: bump checkout action to v4</li> <li><a href="https://github.com/psycopg/psycopg/commit/ed579e51ca9b44af148e55d345e312f58ce12a6f"><code>ed579e5</code></a> docs: fix tickets format</li> <li><a href="https://github.com/psycopg/psycopg/commit/d4a4e8e1447de3446f614a29a8274ef7c4d03d64"><code>d4a4e8e</code></a> Merge branch 'musl-ctypes' into maint-3.1</li> <li><a href="https://github.com/psycopg/psycopg/commit/8bc51e6812cfaedebdd7afff7c86be301d5fbf66"><code>8bc51e6</code></a> docs: mention musl-ctypes workaround in news file</li> <li><a href="https://github.com/psycopg/psycopg/commit/afb040a800b2667a07dc441e8cdb94e55a0dcf65"><code>afb040a</code></a> fix: add <code>libc.so</code> fallback for musl systems to the ctypes impl</li> <li><a href="https://github.com/psycopg/psycopg/commit/06ef0d92109a63fa1a7630804a3a26af0e0a39c9"><code>06ef0d9</code></a> test: drop ineffective marker on fixture</li> <li><a href="https://github.com/psycopg/psycopg/commit/b955118e523c84f5f702d93fd74288ce51ff61db"><code>b955118</code></a> Merge branch 'fix-interval-overflow' into maint-3.1</li> <li>Additional commits viewable in <a href="https://github.com/psycopg/psycopg/compare/3.1.15...3.1.18">compare view</a></li> </ul> </details> <br /> [![Dependabot compatibility score](https://dependabot-badges.githubapp.com/badges/compatibility_score?dependency-name=psycopg&package-manager=pip&previous-version=3.1.15&new-version=3.1.18)](https://docs.github.com/en/github/managing-security-vulnerabilities/about-dependabot-security-updates#about-compatibility-scores) Dependabot will resolve any conflicts with this PR as long as you don't alter it yourself. You can also trigger a rebase manually by commenting `@dependabot rebase`. [//]: # (dependabot-automerge-start) [//]: # (dependabot-automerge-end) --- <details> <summary>Dependabot commands and options</summary> <br /> You can trigger Dependabot actions by commenting on this PR: - `@dependabot rebase` will rebase this PR - `@dependabot recreate` will recreate this PR, overwriting any edits that have been made to it - `@dependabot merge` will merge this PR after your CI passes on it - `@dependabot squash and merge` will squash and merge this PR after your CI passes on it - `@dependabot cancel merge` will cancel a previously requested merge and block automerging - `@dependabot reopen` will reopen this PR if it is closed - `@dependabot close` will close this PR and stop Dependabot recreating it. You can achieve the same result by closing it manually - `@dependabot show <dependency name> ignore conditions` will show all of the ignore conditions of the specified dependency - `@dependabot ignore this major version` will close this PR and stop Dependabot creating any more for this major version (unless you reopen the PR or upgrade to it yourself) - `@dependabot ignore this minor version` will close this PR and stop Dependabot creating any more for this minor version (unless you reopen the PR or upgrade to it yourself) - `@dependabot ignore this dependency` will close this PR and stop Dependabot creating any more for this dependency (unless you reopen the PR or upgrade to it yourself) </details> Signed-off-by: dependabot[bot] <support@github.com> Co-authored-by: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com> Co-authored-by: Eduard van Valkenburg <eavanvalkenburg@users.noreply.github.com> Co-authored-by: Evan Mattson <35585003+moonbox3@users.noreply.github.com>

dvarrazzo added a commit that referenced this issue Jan 26, 2024

fix: force a sync exiting the pipeline mode

e5ef80d

Without it there may be a deadlock and we would be waiting to fetch a result that will never come. Close #685.

dvarrazzo added a commit that referenced this issue Jan 26, 2024

fix: force a sync exiting the pipeline mode

0757ea1

Without it there may be a deadlock and we would be waiting to fetch a result that will never come. Close #685.

dvarrazzo added a commit that referenced this issue Jan 26, 2024

fix: force a sync exiting the pipeline mode

3d230d4

Without it there may be a deadlock and we would be waiting to fetch a result that will never come. Close #685.

dvarrazzo mentioned this issue Jan 26, 2024

Fix deadline on pipeline exit #722

Merged

dvarrazzo added a commit that referenced this issue Jan 26, 2024

fix: force a sync exiting the pipeline mode

3bbb52f

Without it there may be a deadlock and we would be waiting to fetch a result that will never come. Close #685.

dvarrazzo added a commit that referenced this issue Jan 26, 2024

fix: force a sync exiting the pipeline mode

7606b30

Without it there may be a deadlock and we would be waiting to fetch a result that will never come. Close #685.

dvarrazzo closed this as completed in fde1a0d Jan 26, 2024

dvarrazzo mentioned this issue May 10, 2025

Add a result iterator to all cursor classes #1081

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Deadlock when using pipeline mode #685

Deadlock when using pipeline mode #685

Deadlock when using pipeline mode #685

Deadlock when using pipeline mode #685

Comments