Re: OOM-killer issue when updating a inheritance table which has large number of child tables - Mailing list pgsql-hackers

>At the moment, partitioning into thousands of tables is not supported.

Thank you for your reply. And thanks Tom Lane and Stephen Frost！

The following(with createsql.sql and update.sql as attachment) is my complete test case. And i reproduced this problem in PostgreSQL 9.4.1 . 

1)create table and data

createdb db1000

psql -q -v total=10000000 -v pnum=1000 -f createsql.sql |psql db1000

psql -c "insert into maintb values(1,'abcde12345')" db1000

2)update the parent table with one connection, 955MB memory has been used.

[chenhj@node2 part]$ pgbench -c 1 -n -T 10 -r -f update.sql db1000;

transaction type: Custom query

scaling factor: 1

query mode: simple

number of clients: 1

number of threads: 1

duration: 10 s

number of transactions actually processed: 20

tps = 1.933407 (including connections establishing)

tps = 1.934807 (excluding connections establishing)

statement latencies in milliseconds:

516.836800 update maintb set name = 'aaaaa12345' where id=1;

part of output from "top" when runing pgbench:

...

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                   

22537 chenhj    20   0  955m 667m  11m R 99.4 33.3   0:06.12 postgres  

3)update the parent table with ten connections simultaneously, OOM ocurrs.

Now,to run pgbench 955MB * 10 memory are needed,but my machine only has 2GB physical memory and 4GB Swap.

[chenhj@node2 part]$ pgbench -c 10 -n -T 2 -r -f update.sql db1000;

Client 0 aborted in state 0. Probably the backend died while processing.

WARNING:  terminating connection because of crash of another server process

DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

HINT:  In a moment you should be able to reconnect to the database and repeat your command.

WARNING:  terminating connection because of crash of another server process

DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

HINT:  In a moment you should be able to reconnect to the database and repeat your command.

WARNING:  terminating connection because of crash of another server process

DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

HINT:  In a moment you should be able to reconnect to the database and repeat your command.

WARNING:  terminating connection because of crash of another server process

DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

HINT:  In a moment you should be able to reconnect to the database and repeat your command.

WARNING:  terminating connection because of crash of another server process

DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

HINT:  In a moment you should be able to reconnect to the database and repeat your command.

WARNING:  terminating connection because of crash of another server process

DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

HINT:  In a moment you should be able to reconnect to the database and repeat your command.

Client 3 aborted in state 0. Probably the backend died while processing.

Client 6 aborted in state 0. Probably the backend died while processing.

Client 1 aborted in state 0. Probably the backend died while processing.

Client 5 aborted in state 0. Probably the backend died while processing.

Client 8 aborted in state 0. Probably the backend died while processing.

Client 9 aborted in state 0. Probably the backend died while processing.

WARNING:  terminating connection because of crash of another server process

DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

HINT:  In a moment you should be able to reconnect to the database and repeat your command.

Client 7 aborted in state 0. Probably the backend died while processing.

WARNING:  terminating connection because of crash of another server process

DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

HINT:  In a moment you should be able to reconnect to the database and repeat your command.

Client 4 aborted in state 0. Probably the backend died while processing.

WARNING:  terminating connection because of crash of another server process

DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

HINT:  In a moment you should be able to reconnect to the database and repeat your command.

Client 2 aborted in state 0. Probably the backend died while processing.

transaction type: Custom query

scaling factor: 1

query mode: simple

number of clients: 10

number of threads: 1

duration: 2 s

number of transactions actually processed: 0

tps = 0.000000 (including connections establishing)

tps = 0.000000 (excluding connections establishing)

statement latencies in milliseconds:

0.000000 update maintb set name = 'aaaaa12345' where id=1;

Best Regards,

Chen Huajun