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

2016-10-12 7:51 GMT+02:00 张嘉志 <zhangjiazhi(at)p1(dot)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 full
> 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 .
>
> ------------------------------
> *发件人: *"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.
>
>
>>
>> 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,
>
> Regards
>
> Pavel
>
>
>>
>> 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
>>
>
>
>