Google Cloud SQL PG11：无法调整共享内存段的大小
我最近在Google Cloud SQL上将Postgres 9.6实例升级到11.1。从那时起，我开始在多个查询中注意到大量以下错误：
I recently upgraded a Postgres 9.6 instance to 11.1 on Google Cloud SQL. Since then I've begun to notice a large number of the following error across multiple queries:
org.postgresql.util.PSQLException：错误：无法将共享
内存段 /PostgreSQL.78044234的大小调整为2097152字节：设备上
上没有剩余空间
org.postgresql.util.PSQLException: ERROR: could not resize shared memory segment "/PostgreSQL.78044234" to 2097152 bytes: No space left on device
根据我的阅读，这可能是由于PG10中的更改所致，典型的解决方案是增加实例的共享内存。据我所知，这在Google Cloud SQL上是不可能的。我也尝试过调整 work_mem 没有积极的作用。
From what I've read, this is probably due to changes that came in PG10, and the typical solution involves increasing the instance's shared memory. To my knowledge this isn't possible on Google Cloud SQL though. I've also tried adjusting work_mem with no positive effect.
这可能并不重要，但出于完整性考虑，实例配置有30 GB的RAM，120 GB的SSD HD空间和8个CPU。我以为Google会为这些规格提供适当的共享内存设置，但也许不会吗？有任何想法吗？
This may not matter, but for completeness, the instance is configured with 30 gigs of RAM, 120 gigs of SSD hd space and 8 CPU's. I'd assume that Google would provide an appropriate shared memory setting for those specs, but perhaps not? Any ideas?
更新
将数据库标志 random_page_cost 设置为 1 似乎减少了问题的影响。尽管这不是一个完整的解决方案，所以如果有解决方案，仍然希望获得适当的解决方案。
点数转到此博客文章。
UPDATE
Setting the database flag random_page_cost to 1 appears to have reduced the impact the issue. This isn't a full solution though so would still love to get a proper fix if one is out there.
Credit goes to this blog post for the idea.
UPDATE 2
原始问题报告已关闭，创建了一个新的内部问题，公众无法看到。但是，根据GCP客户经理的电子邮件回复，谷歌于2019年8月11日推出了修复程序。
UPDATE 2 The original issue report was closed and a new internal issue that isnt viewable by the public was created. According to a GCP Account Manager's email reply however, a fix was rolled out by Google on 8/11/2019.
这对我有用，我认为Google需要更改一个标志，说明它们如何开始使用postgres容器
This worked for me, I think google needs to change a flag on how they're starting the postgres container on their end that we can't influence inside postgres.
宾果游戏。您的容器技术以某种方式限制了共享内存。
错误正在按设计工作。您可以弄清楚如何修复
挂载选项，或者可以使用
max_parallel_workers_per_gather = 0禁用并行性。
Bingo. Somehow your container tech is limiting shared memory. That error is working as designed. You could figure out how to fix the mount options, or you could disable parallelism with max_parallel_workers_per_gather = 0.
show max_parallel_workers_per_gather;
-- 2
-- Run your query
-- Query fails
alter user ${MY_PROD_USER} set max_parallel_workers_per_gather=0;
-- Run query again -- query should work
alter user ${MY_PROD_USER} set max_parallel_workers_per_gather=2;
-- -- Run query again -- fails