The dynamic_shared_memory_type was set to POSIX . Because of this it was using tmpfs /dev/shm. When the query was running I saw the file system was filling. So I extended the file system and luckily the query worked for that time
>> Do you see any other errors around this one, in the PostgreSQL logs? > No , only this is the error from db and jdbc end .. and queries are failing
If you need a workaround right now you could always set max_parallel_workers_per_gather=0 so that it doesn't try to use parallel query. That could make some queries run more slowly.
When you say you extended the /dev/shm filesystem, what are the mount options, or how did you configure it? It's interesting that the problem went away for a while when you did that.
