Thread: BUG #15749: ERROR: dsa_area could not attach to segment | FATAL: cannot unpin a segment that is not pinned

The following bug has been logged on the website:

Bug reference:      15749
Logged by:          Torrey Johnson
Email address:      torrey@interactiveshift.com
PostgreSQL version: 11.2
Operating system:   Linux Ubuntu 16.04
Description:

Hi there, after upgrading our postgres version from 10.5 to 11.2, we noticed
that our parallel query was causing DynamicSharedMemoryControlLock on our
queries, and locking everything up, causing our connections to expand and
ending up killing our database. I found this thread, and it's pretty much
the exact same thing:

https://www.postgresql.org/message-id/flat/458c691a-1b2d-7a70-1c04-58361b894011%40gmail.com#bab0f00fb877880d0fbaadadd2624ce2

I've disabled 'max_parallel_workers_per_gather = 0', and it hasn't happened
since. These were the settings when this started to happening
max_parallel_workers_per_gather = 4
max_worker_processes = 8
max_parallel_workers = 8

Here's the errors from my postgres logs after seeing
DynamicSharedMemoryControlLock fill up in 'pg_stat_activity':
2019-03-28 22:24:32.728 EDT [24283] ignite@ignite_production ERROR:
dsa_area could not attach to segment
2019-03-28 22:24:32.728 EDT [24283] ignite@ignite_production STATEMENT:
SELECT  "team_activity_views".* FROM "team_activity_views" WHERE
"team_activity_views"."team_id" = $1 LIMIT $2
2019-03-28 22:24:32.728 EDT [63006] FATAL:  terminating connection due to
administrator command
2019-03-28 22:24:32.728 EDT [63006] STATEMENT:  SELECT
"team_activity_views".* FROM "team_activity_views" WHERE
"team_activity_views"."team_id" = $1 LIMIT $2
2019-03-28 22:24:32.728 EDT [63005] FATAL:  terminating connection due to
administrator command
2019-03-28 22:24:32.728 EDT [63005] STATEMENT:  SELECT
"team_activity_views".* FROM "team_activity_views" WHERE
"team_activity_views"."team_id" = $1 LIMIT $2
2019-03-28 22:24:32.729 EDT [63005] FATAL:  cannot unpin a segment that is
not pinned
2019-03-28 22:24:32.730 EDT [1110] LOG:  background worker "parallel worker"
(PID 63006) exited with exit code 1

Any help or update on this would be great. I would love to turn this option
back on. Thanks

Hello

This bug should be already fixed in stable branch and will be shipped in next minor release. Until the fix is released,
thesimplest workaround is to disable parallel query with SET max_parallel_workers_per_gather = 0 if you're affected by
thebug.
 
It's a rare timing bug, but some workloads seem to be more prone to it, so unfortunately it went undetected for a long
time.

If you're interested, related discussion and patch was here:
https://www.postgresql.org/message-id/flat/20190207014719.GJ29720%40telsasoft.com

regards, Sergei