Thread: got some errors after upgrade poestgresql from 9.5 to 9.6
Hi I am got a problem after upgrade poestgresql from 9.5 to 9.6 .it's occurred when calling an exits function . 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[],charactervarying[],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 infofrom system catalog table , but it can't work and got other problems. 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 .
2016-10-12 7:27 GMT+02:00 =E5=BC=A0=E5=98=89=E5=BF=97 <zhangjiazhi@p1.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= . > > putong-contacts=3D# 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 =3D phone_number_arr_[i], > name =3D name_ > WHERE > user_id =3D user_id_ > AND > md5_hash11 =3D md5_hash11_arr_[i] > AND > coalesce(phone_number,'') =3D '' > 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=3D# select * from pg_attribute where attrelid =3D > 'user_mobile_contact_hashes'::regclass and attisdropped; > attrelid | attname | atttypid | attstattarget | > attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | > attstorage | attalign | attnotnull | atthasdef | attisdropped | attisloca= l > | 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 tab= le > , but it can't work and got other problems. > 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 > > putong-contacts=3D# 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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs >
2016-10-12 7:51 GMT+02:00 =E5=BC=A0=E5=98=89=E5=BF=97 <zhangjiazhi@p1.com>: > thanks for your reply , i try to vacuum the table , can't work > vacuum doesn't fix broken catalogue. > , 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 fu= ll > is like recreate a new table , but i will test > can someone can explain this errors. > there can be real PLpgSQL regression - please, send test case (reproducer). Regards Pavel > > thanks a lot . > > ------------------------------ > *=E5=8F=91=E4=BB=B6=E4=BA=BA: *"Pavel Stehule" <pavel.stehule@gmail.com> > *=E6=94=B6=E4=BB=B6=E4=BA=BA: *"=E5=BC=A0=E5=98=89=E5=BF=97" <zhangjiazhi= @p1.com> > *=E6=8A=84=E9=80=81: *pgsql-bugs@postgresql.org, "backend" <backend@p1.co= m>, "dba" < > dba@p1.com> > *=E5=8F=91=E9=80=81=E6=97=B6=E9=97=B4: *=E6=98=9F=E6=9C=9F=E4=B8=89, 2016= =E5=B9=B4 10 =E6=9C=88 12=E6=97=A5 =E4=B8=8B=E5=8D=88 1:40:15 > *=E4=B8=BB=E9=A2=98: *Re: [BUGS] got some errors after upgrade poestgresq= l from 9.5 to 9.6 > > > > > 2016-10-12 7:27 GMT+02:00 =E5=BC=A0=E5=98=89=E5=BF=97 <zhangjiazhi@p1.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. > > >> >> putong-contacts=3D# 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 =3D phone_number_arr_[i], >> name =3D name_ >> WHERE >> user_id =3D user_id_ >> AND >> md5_hash11 =3D md5_hash11_arr_[i] >> AND >> coalesce(phone_number,'') =3D '' >> 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=3D# select * from pg_attribute where attrelid =3D >> 'user_mobile_contact_hashes'::regclass and attisdropped; >> attrelid | attname | atttypid | attstattarget | >> attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | >> attstorage | attalign | attnotnull | atthasdef | attisdropped | attisloc= al >> | 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 ta= ble >> , but it can't work and got other problems. >> > > 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 > > >> >> putong-contacts=3D# 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@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-bugs >> > > >
收件人: "张嘉志" <zhangjiazhi@p1.com>
抄送: pgsql-bugs@postgresql.org, "backend" <backend@p1.com>, "dba" <dba@p1.com>
发送时间: 星期三, 2016年 10 月 12日 下午 1:40:15
主题: Re: [BUGS] got some errors after upgrade poestgresql from 9.5 to 9.6
Hi
I am got a problem after upgrade poestgresql from 9.5 to 9.6 .it's occurred when calling an exits function .
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.
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Hi 2016-11-23 12:49 GMT+01:00 =E5=BC=A0=E5=98=89=E5=BF=97 <zhangjiazhi@p1.com>= : > 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 manuall= y > , it works .could you explain this ? > > pg_dump: [archiver (db)] query failed: server closed the connection > unexpectedly > > This probably means the server terminated abnormally > > before or while processing the request. > > pg_dump: [archiver (db)] query was: SELECT t.tableoid, t.oid, t.relname A= S > 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_constraintd= ef(c.oid, > false) AS condef, (SELECT spcname FROM pg_catalog.pg_tablespace s WHERE > s.oid =3D t.reltablespace) AS tablespace, t.reloptions AS indreloptions F= ROM > pg_catalog.pg_index i JOIN pg_catalog.pg_class t ON (t.oid =3D i.indexrel= id) > LEFT JOIN pg_catalog.pg_constraint c ON (i.indrelid =3D c.conrelid AND > i.indexrelid =3D c.conindid AND c.contype IN ('p','u','x')) WHERE i.indre= lid > =3D '21438180'::pg_catalog.oid AND i.indisvalid AND i.indisready ORDER BY > indexname > > > > 219733,1 Bot > > =3D=3D=3DLOGS > > 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 terminated 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_constraintd= ef(c.oid, > false) AS condef, (SELECT spcname FROM pg_catalog.pg_tablespace s WHERE > s.oid =3D t.reltablespace) AS tablespace, t.reloptions AS indreloptions F= ROM > pg_catalog.pg_index i JOIN pg_catalog.pg_class t ON (t.oid =3D i.indexrel= id) > LEFT JOIN pg_catalog.pg_constraint c ON (i.indrelid =3D c.conrelid AND > i.indexrelid =3D c.conindid AND c.contype IN ('p','u','x')) WHERE i.indre= lid > =3D '21438180'::pg_catalog.oid AND i.indisvalid AND i.indisready ORDER BY > It looks like somebody killed Postgres - Postgres doesn't use signal 9 what I know - probably someone did kill -9 on some PostgreSQL process. Regards Pavel > 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 terminated; > 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 g= o > to log destination ""csvlog"".",,,,,,,"" > > > ------------------------------ > *=E5=8F=91=E4=BB=B6=E4=BA=BA: *"=E5=BC=A0=E5=98=89=E5=BF=97" <zhangjiazhi= @p1.com> > *=E6=94=B6=E4=BB=B6=E4=BA=BA: *"Pavel Stehule" <pavel.stehule@gmail.com> > *=E6=8A=84=E9=80=81: *pgsql-bugs@postgresql.org, "backend" <backend@p1.co= m>, "dba" < > dba@p1.com> > *=E5=8F=91=E9=80=81=E6=97=B6=E9=97=B4: *=E6=98=9F=E6=9C=9F=E4=B8=89, 2016= =E5=B9=B4 10 =E6=9C=88 12=E6=97=A5 =E4=B8=8B=E5=8D=88 1:51:47 > > *=E4=B8=BB=E9=A2=98: *Re: [BUGS] got some errors after upgrade poestgresq= l 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 w= e > 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 fu= ll > is like recreate a new table , but i will test > can someone can explain this errors. > > thanks a lot . > > ------------------------------ > *=E5=8F=91=E4=BB=B6=E4=BA=BA: *"Pavel Stehule" <pavel.stehule@gmail.com> > *=E6=94=B6=E4=BB=B6=E4=BA=BA: *"=E5=BC=A0=E5=98=89=E5=BF=97" <zhangjiazhi= @p1.com> > *=E6=8A=84=E9=80=81: *pgsql-bugs@postgresql.org, "backend" <backend@p1.co= m>, "dba" < > dba@p1.com> > *=E5=8F=91=E9=80=81=E6=97=B6=E9=97=B4: *=E6=98=9F=E6=9C=9F=E4=B8=89, 2016= =E5=B9=B4 10 =E6=9C=88 12=E6=97=A5 =E4=B8=8B=E5=8D=88 1:40:15 > *=E4=B8=BB=E9=A2=98: *Re: [BUGS] got some errors after upgrade poestgresq= l from 9.5 to 9.6 > > > > 2016-10-12 7:27 GMT+02:00 =E5=BC=A0=E5=98=89=E5=BF=97 <zhangjiazhi@p1.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. > > >> >> putong-contacts=3D# 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 =3D phone_number_arr_[i], >> name =3D name_ >> WHERE >> user_id =3D user_id_ >> AND >> md5_hash11 =3D md5_hash11_arr_[i] >> AND >> coalesce(phone_number,'') =3D '' >> 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=3D# select * from pg_attribute where attrelid =3D >> 'user_mobile_contact_hashes'::regclass and attisdropped; >> attrelid | attname | atttypid | attstattarget | >> attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | >> attstorage | attalign | attnotnull | atthasdef | attisdropped | attisloc= al >> | 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 ta= ble >> , but it can't work and got other problems. >> > > 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 > > >> >> putong-contacts=3D# 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@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-bugs >> > > > >
On Wed, Nov 23, 2016 at 8:58 PM, Pavel Stehule <pavel.stehule@gmail.com> wr= ote: > 2016-11-23 12:49 GMT+01:00 =E5=BC=A0=E5=98=89=E5=BF=97 <zhangjiazhi@p1.co= m>: >> =3D=3D=3DLOGS >> >> 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 terminated by signal 9: >> Killed" > > It looks like somebody killed Postgres - Postgres doesn't use signal 9 wh= at > I know - probably someone did kill -9 on some PostgreSQL process. This can be the OOM killer if the OS is Linux. The failure pattern matches with what I would expect the OOM killer to do. --=20 Michael
pg_dump: [archiver (db)] query failed: server closed the connection unexpectedly
This probably means the server terminated 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
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 terminated 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 terminated; 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"".",,,,,,,""
收件人: "Pavel Stehule" <pavel.stehule@gmail.com>
抄送: pgsql-bugs@postgresql.org, "backend" <backend@p1.com>, "dba" <dba@p1.com>
发送时间: 星期三, 2016年 10 月 12日 下午 1:51:47
主题: Re: [BUGS] got some errors after upgrade poestgresql from 9.5 to 9.6
收件人: "张嘉志" <zhangjiazhi@p1.com>
抄送: pgsql-bugs@postgresql.org, "backend" <backend@p1.com>, "dba" <dba@p1.com>
发送时间: 星期三, 2016年 10 月 12日 下午 1:40:15
主题: Re: [BUGS] got some errors after upgrade poestgresql from 9.5 to 9.6
Hi
I am got a problem after upgrade poestgresql from 9.5 to 9.6 .it's occurred when calling an exits function .
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.
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
收件人: "张嘉志" <zhangjiazhi@p1.com>
抄送: pgsql-bugs@postgresql.org, "backend" <backend@p1.com>, "dba" <dba@p1.com>
发送时间: 星期三, 2016年 11 月 23日 下午 7:58:17
主题: Re: [BUGS] got some errors after upgrade poestgresql from 9.5 to 9.6
Hii 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 terminated 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
===LOGS2016-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 terminated 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 terminated; 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@p1.com>
收件人: "Pavel Stehule" <pavel.stehule@gmail.com>
抄送: pgsql-bugs@postgresql.org, "backend" <backend@p1.com>, "dba" <dba@p1.com>
发送时间: 星期三, 2016年 10 月 12日 下午 1:51:47
主题: Re: [BUGS] got some errors after upgrade poestgresql from 9.5 to 9.6thanks 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 testcan someone can explain this errors.thanks a lot .发件人: "Pavel Stehule" <pavel.stehule@gmail.com>
收件人: "张嘉志" <zhangjiazhi@p1.com>
抄送: pgsql-bugs@postgresql.org, "backend" <backend@p1.com>, "dba" <dba@p1.com>
发送时间: 星期三, 2016年 10 月 12日 下午 1:40:15
主题: Re: [BUGS] got some errors after upgrade poestgresql from 9.5 to 9.62016-10-12 7:27 GMT+02:00 张嘉志 <zhangjiazhi@p1.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.
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.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,RegardsPavel
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
thanks a lot , i also think so , and will check the ram and kernel configure ,and try again . ----- 原始邮件 ----- 发件人: "Michael Paquier" <michael.paquier@gmail.com> 收件人: "Pavel Stehule" <pavel.stehule@gmail.com> 抄送: "张嘉志" <zhangjiazhi@p1.com>, pgsql-bugs@postgresql.org, "backend" <backend@p1.com>, "dba" <dba@p1.com> 发送时间: 星期三, 2016年 11 月 23日 下午 8:51:04 主题: Re: [BUGS] got some errors after upgrade poestgresql from 9.5 to 9.6 On Wed, Nov 23, 2016 at 8:58 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > 2016-11-23 12:49 GMT+01:00 张嘉志 <zhangjiazhi@p1.com>: >> ===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 terminated by signal 9: >> Killed" > > It looks like somebody killed Postgres - Postgres doesn't use signal 9 what > I know - probably someone did kill -9 on some PostgreSQL process. This can be the OOM killer if the OS is Linux. The failure pattern matches with what I would expect the OOM killer to do. -- Michael