BUG #8703: Server terminated while this query running - Mailing list pgsql-bugs
From | truong.hua@chuyentin.info |
---|---|
Subject | BUG #8703: Server terminated while this query running |
Date | |
Msg-id | E1VxBOp-0000Bj-Un@wrigleys.postgresql.org Whole thread Raw |
List | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 8703 Logged by: Truong Hua Email address: truong.hua@chuyentin.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
pgsql-bugs by date: