Re: [HACKERS] ON CONFLICT DO UPDATE using EXCLUDED.column gives an error about mismatched types

Hi, > 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. I pushed a

Re: [HACKERS] ON CONFLICT DO UPDATE using EXCLUDED.column gives an error about mismatched types

Peter, On 2015-08-11 AM 07:37, Peter Geoghegan wrote: > What I'm going to do is roll this into my own pending patch to fix the > issue with wholerow vars, which is also down to a problem with the > excluded targetlist initially generated by calling expandRelAttrs(). > Andres might want to take

Re: [HACKERS] ON CONFLICT DO UPDATE using EXCLUDED.column gives an error about mismatched types

On Wed, Sep 2, 2015 at 1:18 AM, Amit Langote wrote: > Did you get around to making a patch for this? I've worked on it inconsistently. I'll pick this up again soon. I may take the opportunity to talk this over with Andres in person when we meet at Postgres Open

Re: [HACKERS] ON CONFLICT DO UPDATE using EXCLUDED.column gives an error about mismatched types

On Wed, Aug 5, 2015 at 12:58 AM, Amit Langote langote_amit...@lab.ntt.co.jp wrote: I forgot mentioning one thing later yesterday. The way exclRelTlist is initialized, all the way in the beginning (transformOnConflictClause), is most probably to blame. It uses expandRelAttrs() for other valid

Re: [HACKERS] ON CONFLICT DO UPDATE using EXCLUDED.column gives an error about mismatched types

On 2015-08-05 AM 06:44, Peter Geoghegan wrote: On Tue, Aug 4, 2015 at 2:29 AM, Amit Langote langote_amit...@lab.ntt.co.jp wrote: Perhaps, it may have to do with how EXCLUDED pseudo-rel's targetlist is manipulated through parse-plan stage? I think so, yes. I'll look into writing a fix for

Re: [HACKERS] ON CONFLICT DO UPDATE using EXCLUDED.column gives an error about mismatched types

On 2015-08-04 PM 05:58, Geoff Winkless wrote: ​Although it seems Amit has defined the problem better than I could, so this is a bit late to the party (!), yes, the table had been ALTERed after it was created (looking back through the history, that modification included at least one DROP

Re: [HACKERS] ON CONFLICT DO UPDATE using EXCLUDED.column gives an error about mismatched types

On 4 August 2015 at 09:30, Amit Langote langote_amit...@lab.ntt.co.jp wrote: On 2015-08-04 AM 02:57, Peter Geoghegan wrote: On Mon, Aug 3, 2015 at 8:53 AM, Geoff Winkless pgsqlad...@geoff.dj wrote: If I create a copy of the table using CREATE mytab (LIKE brokentab INCLUDING ALL);

Re: [HACKERS] ON CONFLICT DO UPDATE using EXCLUDED.column gives an error about mismatched types

On 2015-08-04 AM 02:57, Peter Geoghegan wrote: On Mon, Aug 3, 2015 at 8:53 AM, Geoff Winkless pgsqlad...@geoff.dj wrote: If I create a copy of the table using CREATE mytab (LIKE brokentab INCLUDING ALL); INSERT INTO mytab SELECT * FROM brokentab; Also, did you drop any columns from the

Re: [HACKERS] ON CONFLICT DO UPDATE using EXCLUDED.column gives an error about mismatched types

On Tue, Aug 4, 2015 at 2:29 AM, Amit Langote langote_amit...@lab.ntt.co.jp wrote: Perhaps, it may have to do with how EXCLUDED pseudo-rel's targetlist is manipulated through parse-plan stage? I think so, yes. I'll look into writing a fix for this later in the week. Thanks for the report,

[HACKERS] ON CONFLICT DO UPDATE using EXCLUDED.column gives an error about mismatched types

Hi 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

Re: [HACKERS] ON CONFLICT DO UPDATE using EXCLUDED.column gives an error about mismatched types

On Mon, Aug 3, 2015 at 8:53 AM, Geoff Winkless pgsqlad...@geoff.dj wrote: 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

Re: [HACKERS] ON CONFLICT DO UPDATE using EXCLUDED.column gives an error about mismatched types

On Mon, Aug 3, 2015 at 8:53 AM, Geoff Winkless pgsqlad...@geoff.dj wrote: If I create a copy of the table using CREATE mytab (LIKE brokentab INCLUDING ALL); INSERT INTO mytab SELECT * FROM brokentab; Also, did you drop any columns from the original brokentab table where the bug can be