Horton

BUG #15324: Non-deterministic behaviour from parallelised sub-query

PG Bug reporting form <noreply@postgresql.org>

View thread
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: andy@prestigedigital.com
Date: 2018-08-13T16:14:03Z
Lists: pgsql-bugs

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Don't allow LIMIT/OFFSET clause within sub-selects to be pushed to workers.

  2. Back-patch "Fix parallel hash join path search."

  3. Prohibit pushing subqueries containing window function calculation to

  4. Fix parallel hash join path search.

The following bug has been logged on the website:

Bug reference:      15324
Logged by:          Andrew Fletcher
Email address:      andy@prestigedigital.com
PostgreSQL version: 10.5
Operating system:   macOS High Sierra 10.13.6
Description:        

Reproductions - 

Make sure postgresql.conf sets max_parallel_workers_per_gather to 2 or
more

max_parallel_workers_per_gather = 2

Repro 1:

Create table from the following sql file -

https://www.dropbox.com/s/3cm643vmugcgkxh/events.sql.zip?dl=0

Execute this query (multiple times!)

select * from events where account in (select account from events where
data->>'page' = 'success.html' limit 3);

Incorrect output -

 account |   type   |           data           
---------+----------+--------------------------
  304873 | pageview | {"page": "success.html"}
  304875 | pageview | {"page": "c.html"}
  304875 | pageview | {"page": "success.html"}
  304885 | pageview | {"page": "a.html"}
  304885 | pageview | {"page": "success.html"}
(5 rows)

Correct output -

 account |   type   |           data           
---------+----------+--------------------------
  304873 | pageview | {"page": "success.html"}
  304875 | pageview | {"page": "c.html"}
  304875 | pageview | {"page": "success.html"}
  304885 | pageview | {"page": "a.html"}
  304885 | pageview | {"page": "success.html"}
  304873 | pageview | {"page": "b.html"}
(6 rows)


Repro 2 -

Create table from the following sql file -

https://www.dropbox.com/s/mzglgm4a5x1mqno/repro1.sql.zip?dl=0

Execute this query (multiple times!)

select * from repro1 where account in (select account from repro1 where page
= 'success.html' limit 3);

Incorrect Output -

 account |     page     
---------+--------------
      14 | a.html
      14 | success.html
      65 | b.html
      65 | success.html
      80 | b.html
      80 | success.html
   24084 | a.html
   24084 | success.html
   24085 | c.html
   24085 | success.html
   24095 | a.html
   24095 | success.html
(12 rows)

Correct output -

 account |     page     
---------+--------------
      14 | a.html
      14 | success.html
      65 | b.html
      65 | success.html
      80 | b.html
      80 | success.html
(6 rows)


Full version string -

PostgreSQL 10.5 on x86_64-apple-darwin17.7.0, compiled by Apple LLVM version
9.1.0 (clang-902.0.39.2), 64-bit

Also reproduced (with slightly different non-determinism) on -

PostgreSQL 9.6.3, compiled by Visual C++ build 1800, 32-bit on Windows 10
Pro 1709, build 16299.547


Known workarounds -

1.  max_parallel_workers_per_gather = 0
2.  Add order by account asc to the subquery (works for both repros)