We've come across a weirdness with ON CONFLICT, where UPSERTing a smallint value produces an error:
db=# INSERT INTO brokentab(id, k1,k2,k3,k4,k5,k6,k7, smallval) VALUES (5,0,0,0,1,0,1,0, 0) ON CONFLICT (id, k1,k2,k3,k4,k5,k6,k7) DO UPDATE SET smallval=EXCLUDED.smallval;
ERROR: attribute 29 has wrong type
DETAIL: Table has type integer, but query expects smallint.
If you change the SET to smallval=0 the problem goes away, although using SET smallval=CAST(EXCLUDED.smallval AS smallint) - or indeed AS int - doesn't help at all.
If I create a copy of the table using
CREATE mytab (LIKE brokentab INCLUDING ALL);
INSERT INTO mytab SELECT * FROM brokentab;
the new table does not exhibit the same problem (so I'm assuming it's not easily reproducible and giving you a creation script isn't going to help).
VACUUM FULL on the table makes no difference.
Is there anything you guys can suggest that I can do to help narrow down the problem?
Linux Centos 6.5, kernel 2.6.32-431.el6.i686, pgsql alpha1, built from source using gcc 4.4.7.
Thanks
Geoff