Re: got some errors after upgrade poestgresql from 9.5 to 9.6

Hi
i got an error when upgrade postgresql9.5 to 9.6.1 ,server terminate the requests , but don't give the reasons , i execute this query manually , it works .could you explain this ?

pg_dump: [archiver (db)] query failed: server closed the connection unexpectedly

This probably means the server terminate d abnormally

before or while processing the request.

pg_dump: [archiver (db)] query was: SELECT t.tableoid, t.oid, t.relname AS indexname, pg_catalog.pg_get_indexdef(i.indexrelid) AS indexdef, t.relnatts AS indnkeys, i.indkey, i.indisclustered, i.indisreplident, t.relpages, c.contype, c.conname, c.condeferrable, c.condeferred, c.tableoid AS contableoid, c.oid AS conoid, pg_catalog.pg_get_constraintdef(c.oid, false) AS condef, (SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, t.reloptions AS indreloptions FROM pg_catalog.pg_index i JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) LEFT JOIN pg_catalog.pg_constraint c ON (i.indrelid = c.conrelid AND i.indexrelid = c.conindid AND c.contype IN ('p','u','x')) WHERE i.indrelid = '21438180'::pg_catalog.oid AND i.indisvalid AND i.indisready ORDER BY indexname

219733,1 Bot
===LOGS

2016-11-23 19:26:05.027 CST,,,7888,,58357a04.1ed0,3,,2016-11-23 19:14:12 CST,,0,LOG,00000,"server process (PID 7993) was terminate d by signal 9: Killed","Failed process was running: SELECT t.tableoid, t.oid, t.relname AS indexname, pg_catalog.pg_get_indexdef(i.indexrelid) AS indexdef, t.relnatts AS indnkeys, i.indkey, i.indisclustered, i.indisreplident, t.relpages, c.contype, c.conname, c.condeferrable, c.condeferred, c.tableoid AS contableoid, c.oid AS conoid, pg_catalog.pg_get_constraintdef(c.oid, false) AS condef, (SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, t.reloptions AS indreloptions FROM pg_catalog.pg_index i JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) LEFT JOIN pg_catalog.pg_constraint c ON (i.indrelid = c.conrelid AND i.indexrelid = c.conindid AND c.contype IN ('p','u','x')) WHERE i.indrelid = '21438180'::pg_catalog.oid AND i.indisvalid AND i.indisready ORDER BY indexname",,,,,,,,""

2016-11-23 19:26:05.031 CST,,,7888,,58357a04.1ed0,4,,2016-11-23 19:14:12 CST,,0,LOG,00000,"terminating any other active server processes",,,,,,,,,""

2016-11-23 19:26:05.059 CST,"postgres","putong-shard-2",7992,"[local]",58357a30.1f38,6,"SELECT",2016-11-23 19:14:56 CST,5/11,0,WARNING,57P02,"terminating connection because of crash of another server process","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.","In a moment you should be able to reconnect to the database and repeat your command.",,,,,,,"pg_dump"

2016-11-23 19:26:05.165 CST,,,7888,,58357a04.1ed0,5,,2016-11-23 19:14:12 CST,,0,LOG,00000,"all server processes terminate d; reinitializing",,,,,,,,,""

2016-11-23 19:26:05.473 CST,,,7888,,58357a04.1ed0,6,,2016-11-23 19:14:12 CST,,0,LOG,00000,"received fast shutdown request",,,,,,,,,""

2016-11-23 19:26:05.481 CST,,,8016,,58357ccd.1f50,1,,2016-11-23 19:26:05 CST,,0,LOG,00000,"database system was interrupted; last known up at 2016-11-23 19:24:19 CST",,,,,,,,,""

2016-11-23 19:26:22.417 CST,,,8016,,58357ccd.1f50,2,,2016-11-23 19:26:05 CST,,0,LOG,00000,"database system was not properly shut down; automatic recovery in progress",,,,,,,,,""

2016-11-23 19:26:22.752 CST,,,8016,,58357ccd.1f50,3,,2016-11-23 19:26:05 CST,,0,LOG,00000,"redo starts at 44A/2C0CEE30",,,,,,,,,""

2016-11-23 19:26:22.760 CST,,,7888,,58357a04.1ed0,7,,2016-11-23 19:14:12 CST,,0,LOG,00000,"abnormal database system shutdown",,,,,,,,,""

2016-11-23 19:43:12.371 CST,,,8051,,583580cf.1f73,1,,2016-11-23 19:43:11 CST,,0,LOG,00000,"ending log output to stderr",,"Future log output will go to log destination ""csvlog"".",,,,,,,""

----- 原始邮件 -----

发件人: "张嘉志" <zhangjiazhi(at)p1(dot)com>
收件人: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
抄送: pgsql-bugs(at)postgresql(dot)org, "backend" <backend(at)p1(dot)com>, "dba" <dba(at)p1(dot)com>
发送时间: 星期三, 2016年 10 月 12日 下午 1:51:47
主题: Re: [BUGS] got some errors after upgrade poestgresql from 9.5 to 9.6

thanks for your reply , i try to vacuum the table , can't work , i also recreate the table , worked , but can't do this in production , because we have lots of big table had change the columns ,
and thanks for your reminder , before delete the data from system catalog table , i do a backup , and restore it after test. .accturlly , vacuum full is like recreate a new table , but i will test
can someone can explain this errors.

thanks a lot .

----- 原始邮件 -----

发件人: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
收件人: "张嘉志" <zhangjiazhi(at)p1(dot)com>
抄送: pgsql-bugs(at)postgresql(dot)org, "backend" <backend(at)p1(dot)com>, "dba" <dba(at)p1(dot)com>
发送时间: 星期三, 2016年 10 月 12日 下午 1:40:15
主题: Re: [BUGS] got some errors after upgrade poestgresql from 9.5 to 9.6

2016-10-12 7:27 GMT+02:00 张嘉志 < zhangjiazhi(at)p1(dot)com > :

Hi

I am got a problem after upgrade poestgresql from 9.5 to 9.6 .it's occurred when calling an exits function .

It is little bit strange - I don't remember any related change in this area.

<blockquote>

putong-contacts=# select insert_user_mobile_contact_hashes(4009, '', ARRAY['7bd94067bb0597bf995c4eb46364a4ce'], ARRAY['c55288a186d0e6f0e3fb3b66deb65366'], ARRAY[''], false);
ERROR: table row type and query-specified row type do not match
DETAIL: Query provides a value for a dropped column at ordinal position 6.
CONTEXT: SQL statement "UPDATE c
SET
phone_number = phone_number_arr_[i],
name = name_
WHERE
user_id = user_id_
AND
md5_hash11 = md5_hash11_arr_[i]
AND
coalesce(phone_number,'') = ''
AND
char_length(phone_number_arr_[i]) > 0"
PL/pgSQL function insert_user_mobile_contact_hashes(integer,character varying,character varying[],character varying[],character varying[],boolean) line 36 at SQL statement

and here is the column in this table be dropped

putong-contacts=# select * from pg_attribute where attrelid = 'user_mobile_contact_hashes'::regclass and attisdropped;
attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attalign | attnotnull | atthasdef | attisdropped | attislocal | attinhcount | attcollation | attacl | attoptions | attfdwoptions
----------+-------------------------------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------+-----------+--------------+------------+-------------+--------------+--------+------------+---------------
6097850 | ........pg.dropped.6........ | 0 | 0 | 8 | 6 | 0 | -1 | -1 | f | p | d | f | f | t | t | 0 | 0 | | |
6097850 | ........pg.dropped.10........ | 0 | 0 | 1 | 10 | 0 | -1 | -1 | f | p | c | f | f | t | t | 0 | 0 | | |

when i create this table user_mobile_contact_hashes , the function works well. i try to delete those 2 dropped column info from system catalog table , but it can't work and got other problems.

</blockquote>

It is most bad idea! Newer delete anything from system tables. Now, the system catalogue is broken.

The correct fix for first issue is a VACUUM FULL. Second issue - you can try drop table and recreate it,

Regards

Pavel

<blockquote>

putong-contacts=# select insert_user_mobile_contact_hashes(4009, '', ARRAY['7bd94067bb0597bf995c4eb46364a4ce'], ARRAY['c55288a186d0e6f0e3fb3b66deb65366'], ARRAY[''], false);
ERROR: catalog is missing 2 attribute(s) for relid 6097850

Can you explain this issue and give me some advise how to handle this . thanks a lot .

--
Sent via pgsql-bugs mailing list ( pgsql-bugs(at)postgresql(dot)org )
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

</blockquote>