Re: got some errors after upgrade poestgresql 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 | 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
>