From: | 张嘉志 <zhangjiazhi(at)p1(dot)com> |
---|---|
To: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | pgsql-bugs(at)postgresql(dot)org, backend <backend(at)p1(dot)com>, dba <dba(at)p1(dot)com> |
Subject: | Re: got some errors after upgrade poestgresql from 9.5 to 9.6 |
Date: | 2016-10-12 05:51:47 |
Message-ID: | 713389805.16313402.1476251507846.JavaMail.zimbra@p1.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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>
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2016-10-12 06:01:42 | Re: got some errors after upgrade poestgresql from 9.5 to 9.6 |
Previous Message | Pavel Stehule | 2016-10-12 05:40:15 | Re: got some errors after upgrade poestgresql from 9.5 to 9.6 |