BUG #8703: Server terminated while this query running

The following bug has been logged on the website:

Bug reference: 8703
Logged by: Truong Hua
Email address: truong(dot)hua(at)chuyentin(dot)info
PostgreSQL version: 9.3.2
Operating system: CentOS 6.4 (Final)
Description:

Every time I run this query below, postgres will be crashed because of
non-shared memory accessing. My contents table is partitioned into 8 table
by hash (bytea) column and it has about 30 millions records.

CREATE TABLE contents_dup2_backup
AS
SELECT UNNEST(array_agg(id))
FROM contents
GROUP BY hash
HAVING COUNT(id) > 1

This is postgres log
< 2013-12-29 14:58:14.539 ICT >LOG: server process (PID 16597) was
terminated by signal 9: Killed
< 2013-12-29 14:58:14.539 ICT >DETAIL: Failed process was running: CREATE
TABLE contents_dup2_backup
AS
SELECT UNNEST(array_agg(id))
FROM contents
GROUP BY hash
HAVING COUNT(id) > 1
< 2013-12-29 14:58:14.539 ICT >LOG: terminating any other active server
processes
< 2013-12-29 14:58:14.540 ICT >WARNING: terminating connection because of
crash of another server process
< 2013-12-29 14:58:14.540 ICT >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.
< 2013-12-29 14:58:14.540 ICT >HINT: In a moment you should be able to
reconnect to the database and repeat your command.
< 2013-12-29 14:58:14.605 ICT >LOG: all server processes terminated;
reinitializing
< 2013-12-29 14:58:16.422 ICT >LOG: database system was interrupted; last
known up at 2013-12-29 14:55:40 ICT
< 2013-12-29 14:58:16.426 ICT >LOG: database system was not properly shut
down; automatic recovery in progress
< 2013-12-29 14:58:16.474 ICT >LOG: redo starts at 57C/AF3C1618
< 2013-12-29 14:58:16.480 ICT >LOG: record with zero length at
57C/AF3F1DB0
< 2013-12-29 14:58:16.480 ICT >LOG: redo done at 57C/AF3F1D80
< 2013-12-29 14:58:16.480 ICT >LOG: last completed transaction was at log
time 2013-12-29 14:57:13.065277+07
< 2013-12-29 14:58:16.553 ICT >LOG: autovacuum launcher started
< 2013-12-29 14:58:16.553 ICT >LOG: database system is ready to accept
connections

And this is OS log

Dec 29 14:58:11 zie kernel: 7099484 pages non-shared
Dec 29 14:58:11 zie kernel: [ pid ] uid tgid total_vm rss cpu
oom_adj oom_score_adj name
Dec 29 14:58:11 zie kernel: [ 971] 0 971 2887 0 1
-17 -1000 udevd
Dec 29 14:58:11 zie kernel: [ 1878] 0 1878 64048 224 0
0 0 rsyslogd
Dec 29 14:58:11 zie kernel: [ 1924] 497 1924 87314 13 5
0 0 memcached
Dec 29 14:58:11 zie kernel: [ 1941] 0 1941 16029 13 0
-17 -1000 sshd
Dec 29 14:58:11 zie kernel: [ 2054] 0 2054 19680 30 18
0 0 master
Dec 29 14:58:11 zie kernel: [ 2061] 89 2061 19742 36 20
0 0 qmgr
Dec 29 14:58:11 zie kernel: [ 2064] 0 2064 29310 17 0
0 0 crond
Dec 29 14:58:11 zie kernel: [ 2077] 0 2077 1015 1 1
0 0 mingetty
Dec 29 14:58:11 zie kernel: [ 2079] 0 2079 1015 1 22
0 0 mingetty
Dec 29 14:58:11 zie kernel: [ 2081] 0 2081 1015 1 3
0 0 mingetty
Dec 29 14:58:11 zie kernel: [ 2083] 0 2083 1015 1 20
0 0 mingetty
Dec 29 14:58:11 zie kernel: [ 2085] 0 2085 1015 1 20
0 0 mingetty
Dec 29 14:58:11 zie kernel: [ 2087] 0 2087 1015 1 4
0 0 mingetty
Dec 29 14:58:11 zie kernel: [ 2094] 0 2094 3093 0 13
-17 -1000 udevd
Dec 29 14:58:11 zie kernel: [ 2095] 0 2095 3093 0 2
-17 -1000 udevd
Dec 29 14:58:11 zie kernel: [29084] 0 29084 29712 1 14
0 0 screen
Dec 29 14:58:11 zie kernel: [29085] 0 29085 27108 1 0
0 0 bash
Dec 29 14:58:11 zie kernel: [29106] 0 29106 36336 1 0
0 0 su
Dec 29 14:58:11 zie kernel: [29107] 26 29107 27074 1 14
0 0 bash
Dec 29 14:58:11 zie kernel: [29108] 26 29108 42167 1 0
0 0 psql
Dec 29 14:58:11 zie kernel: [23867] 501 23867 29712 1 21
0 0 screen
Dec 29 14:58:11 zie kernel: [23868] 501 23868 27109 1 1
0 0 bash
Dec 29 14:58:11 zie kernel: [ 2920] 0 2920 23298 6 18
-17 -1000 auditd
Dec 29 14:58:11 zie kernel: [ 6427] 502 6427 27098 1 8
0 0 bash
Dec 29 14:58:11 zie kernel: [32578] 501 32578 24684 1 22
0 0 rptr
Dec 29 14:58:11 zie kernel: [32580] 501 32580 24665 1 10
0 0 rptr
Dec 29 14:58:11 zie kernel: [32583] 501 32583 24161 1 21
0 0 rptr
Dec 29 14:58:11 zie kernel: [32584] 501 32584 24193 1 14
0 0 rptr
Dec 29 14:58:11 zie kernel: [ 343] 501 343 22037 1 3
0 0 ctrlr
Dec 29 14:58:11 zie kernel: [ 1886] 501 1886 22952 1 19
0 0 rptr
Dec 29 14:58:11 zie kernel: [ 1887] 501 1887 23410 1 2
0 0 rptr
Dec 29 14:58:11 zie kernel: [ 1888] 501 1888 23410 1 0
0 0 rptr
Dec 29 14:58:11 zie kernel: [ 1889] 501 1889 23410 1 2
0 0 rptr
Dec 29 14:58:11 zie kernel: [14805] 501 14805 49109 0 0
0 0 data
Dec 29 14:58:11 zie kernel: [14807] 501 14807 49109 0 0
0 0 data
Dec 29 14:58:11 zie kernel: [14809] 501 14809 49231 0 0
0 0 data
Dec 29 14:58:11 zie kernel: [14811] 501 14811 49109 0 14
0 0 data
Dec 29 14:58:11 zie kernel: [14815] 501 14815 49231 0 4
0 0 data
Dec 29 14:58:11 zie kernel: [14816] 501 14816 49147 0 6
0 0 data
Dec 29 14:58:11 zie kernel: [14817] 501 14817 49109 0 17
0 0 data
Dec 29 14:58:11 zie kernel: [14820] 501 14820 49109 0 0
0 0 data
Dec 29 14:58:11 zie kernel: [14821] 501 14821 49109 0 0
0 0 data
Dec 29 14:58:11 zie kernel: [14822] 501 14822 49109 0 7
0 0 data
Dec 29 14:58:11 zie kernel: [14825] 501 14825 49109 0 1
0 0 data
Dec 29 14:58:11 zie kernel: [14837] 501 14837 49147 0 19
0 0 data
Dec 29 14:58:11 zie kernel: [14867] 501 14867 49109 0 10
0 0 data
Dec 29 14:58:11 zie kernel: [14868] 501 14868 49109 0 7
0 0 data
Dec 29 14:58:11 zie kernel: [14905] 501 14905 49231 0 13
0 0 data
Dec 29 14:58:11 zie kernel: [15048] 501 15048 49109 0 1
0 0 data
Dec 29 14:58:11 zie kernel: [15049] 501 15049 49109 0 10
0 0 data
Dec 29 14:58:11 zie kernel: [25309] 501 25309 49109 0 14
0 0 data
Dec 29 14:58:11 zie kernel: [26323] 501 26323 49147 0 6
0 0 data
Dec 29 14:58:11 zie kernel: [28914] 501 28914 49231 0 6
0 0 data
Dec 29 14:58:11 zie kernel: [25298] 501 25298 49231 0 18
0 0 data
Dec 29 14:58:11 zie kernel: [ 1862] 501 1862 49109 0 14
0 0 data
Dec 29 14:58:11 zie kernel: [ 1863] 501 1863 49109 0 21
0 0 data
Dec 29 14:58:11 zie kernel: [15703] 501 15703 49109 0 20
0 0 data
Dec 29 14:58:11 zie kernel: [15704] 501 15704 49109 0 5
0 0 data
Dec 29 14:58:11 zie kernel: [15705] 501 15705 49109 0 3
0 0 data
Dec 29 14:58:11 zie kernel: [25058] 501 25058 49109 0 10
0 0 data
Dec 29 14:58:11 zie kernel: [25059] 501 25059 49109 0 5
0 0 data
Dec 29 14:58:11 zie kernel: [25060] 501 25060 49109 0 1
0 0 data
Dec 29 14:58:11 zie kernel: [ 2984] 501 2984 49109 0 0
0 0 data
Dec 29 14:58:11 zie kernel: [ 6506] 501 6506 49109 0 8
0 0 data
Dec 29 14:58:11 zie kernel: [13109] 501 13109 49109 0 6
0 0 data
Dec 29 14:58:11 zie kernel: [ 8154] 501 8154 49109 0 12
0 0 data
Dec 29 14:58:11 zie kernel: [ 4172] 501 4172 49109 0 15
0 0 data
Dec 29 14:58:11 zie kernel: [26429] 501 26429 49109 0 9
0 0 data
Dec 29 14:58:11 zie kernel: [23823] 501 23823 49109 0 3
0 0 data
Dec 29 14:58:11 zie kernel: [23824] 501 23824 49109 0 8
0 0 data
Dec 29 14:58:11 zie kernel: [10483] 501 10483 49109 0 3
0 0 data
Dec 29 14:58:11 zie kernel: [24080] 501 24080 49109 0 10
0 0 data
Dec 29 14:58:11 zie kernel: [10570] 501 10570 49109 0 4
0 0 data
Dec 29 14:58:11 zie kernel: [11180] 501 11180 49109 0 7
0 0 data
Dec 29 14:58:11 zie kernel: [13700] 0 13700 24466 14 0
0 0 sshd
Dec 29 14:58:11 zie kernel: [13819] 0 13819 27108 43 0
0 0 bash
Dec 29 14:58:11 zie kernel: [13972] 0 13972 24467 1 13
0 0 sshd
Dec 29 14:58:11 zie kernel: [13976] 501 13976 24467 0 1
0 0 sshd
Dec 29 14:58:11 zie kernel: [13977] 501 13977 27108 1 6
0 0 bash
Dec 29 14:58:11 zie kernel: [14260] 0 14260 22927 8 0
0 0 searchd
Dec 29 14:58:11 zie kernel: [14261] 0 14261 1077221 5681 13
0 0 searchd
Dec 29 14:58:11 zie kernel: [14582] 501 14582 21373 5 13
0 0 rptr
Dec 29 14:58:11 zie kernel: [14583] 501 14583 22484 3 0
0 0 rptr
Dec 29 14:58:11 zie kernel: [14584] 501 14584 21899 3 2
0 0 rptr
Dec 29 14:58:11 zie kernel: [14585] 501 14585 21899 3 7
0 0 rptr
Dec 29 14:58:11 zie kernel: [14586] 501 14586 21899 3 16
0 0 rptr
Dec 29 14:58:11 zie kernel: [14587] 501 14587 21899 3 3
0 0 rptr
Dec 29 14:58:11 zie kernel: [14589] 501 14589 21899 3 1
0 0 rptr
Dec 29 14:58:11 zie kernel: [14591] 501 14591 21899 3 19
0 0 rptr
Dec 29 14:58:11 zie kernel: [14593] 501 14593 21899 3 19
0 0 rptr
Dec 29 14:58:11 zie kernel: [14596] 501 14596 21899 3 21
0 0 rptr
Dec 29 14:58:11 zie kernel: [14598] 501 14598 21899 3 22
0 0 rptr
Dec 29 14:58:11 zie kernel: [15724] 89 15724 19751 49 8
0 0 smtp
Dec 29 14:58:11 zie kernel: [16488] 89 16488 19736 34 7
0 0 cleanup
Dec 29 14:58:11 zie kernel: [16554] 26 16554 2206430 41809 6
0 0 postmaster
Dec 29 14:58:11 zie kernel: [16557] 26 16557 43802 12 6
0 0 postmaster
Dec 29 14:58:11 zie kernel: [16592] 26 16592 2210028 42095 1
0 0 postmaster
Dec 29 14:58:11 zie kernel: [16593] 26 16593 2207455 219324 12
0 0 postmaster
Dec 29 14:58:11 zie kernel: [16594] 26 16594 2207455 4135 6
0 0 postmaster
Dec 29 14:58:11 zie kernel: [16595] 26 16595 2207748 121 18
0 0 postmaster
Dec 29 14:58:11 zie kernel: [16596] 26 16596 44360 39 8
0 0 postmaster
Dec 29 14:58:11 zie kernel: [16597] 26 16597 13760572 7979891 6
0 0 postmaster
Dec 29 14:58:11 zie kernel: [16670] 89 16670 19700 210 19
0 0 pickup
Dec 29 14:58:11 zie kernel: [17165] 89 17165 19709 210 7
0 0 bounce
Dec 29 14:58:11 zie kernel: Out of memory: Kill process 16597 (postmaster)
score 790 or sacrifice child
Dec 29 14:58:11 zie kernel: Killed process 16597, UID 26, (postmaster)
total-vm:55042288kB, anon-rss:27742156kB, file-rss:4177408kB