Re: Postgres 10.3 delete on partition table cannot execute, out ofmemory.

Hi.

On 2018/03/15 13:15, Mark Magnus wrote:
> Bug
> 
> Partitioned table has 7202 partitions. No partition contains more than 50
> records. Partitioning is done on a foreign key. Any delete operation i.e.
> "delete from contacts where id = ?" or
> "delete from contacts where id = ? and account_id = ?" or
> "delete from contacts where account_id = ?"
> results in out of memory condition.
> 
> Default Postgres Configuration with exception
> max_locks_per_transaction = 1024

As Fabio says, it seems most likely that OOM was triggered while planning
the delete operation.  I can reproduce OOM being triggered on my modest
development machine, so perhaps that's what's happening in your case too.

This is unfortunately expected, given that the underlying planning
mechanism cannot cope beyond a few hundred partitions. :-(  See a relevant
note in the documentation; last line of the page at this link:
https://www.postgresql.org/docs/devel/static/ddl-partitioning.html.

Until things improve in that area, one workaround might be to perform the
delete operation directly on the partition, as it's possible to do that.
Or redesign your schema to use less number of partitions.

Thanks,
Amit