Обсуждение: Problem with a "complex" upsert

Поиск
Список
Период
Сортировка

Problem with a "complex" upsert

От
Mario De Frutos Dieguez
Дата:
I'm trying to do an upsert to an updatable view with the following SQL query:

INSERT INTO "acs2014_5yr"."b01003" (geoid, b01003001)                                                       
SELECT (left(acs.geoid, 7) || bi.blockid) geoid, (b01003001 * (percentage*100.0)) b01003001
FROM "tiger2015".blocks_interpolation bi
INNER JOIN "acs2014_5yr"."b01003" acs ON bi.blockgroupid = substr(acs.geoid,8)
WHERE acs.geoid = '15000US020200001013' AND char_length(substr(acs.geoid, 8)) = 12
ON CONFLICT (geoid) DO UPDATE SET (b01003001) = ROW(EXCLUDED.b01003001);

The View is:

                                  View "acs2014_5yr.b01003"
  Column   |         Type          | Collation | Nullable | Default | Storage  | Description
-----------+-----------------------+-----------+----------+---------+----------+-------------
 geoid     | character varying(40) |           |          |         | extended |
 b01003001 | double precision      |           |          |         | plain    |
View definition:
 SELECT seq0003.geoid,
    seq0003.b01003001
   FROM acs2014_5yr.seq0003;

If I don't get any conflict everything works as intended but if we hit a conflict then I get the following error message:

ERROR:  attribute 2 of type record has the wrong type
DETAIL:  Table has type character varying, but query expects double precision.

Looks like it's trying to use the geoid value in the b01003001 field.

I've tried using the source insert table data but the server crashes:

INSERT INTO "acs2014_5yr"."b01003" (geoid, b01003001)                                                       
SELECT (left(acs.geoid, 7) || bi.blockid) geoid, (b01003001 * (percentage*100.0))::float b01003001
FROM "tiger2015".blocks_interpolation bi
INNER JOIN "acs2014_5yr"."b01003" acs ON bi.blockgroupid = substr(acs.geoid,8)
WHERE acs.geoid = '15000US020200001013' AND char_length(substr(acs.geoid, 8)) = 12
ON CONFLICT (geoid) DO UPDATE SET (b01003001) = ROW("acs2014_5yr"."b01003".b01003001);

server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

Any clues? Could be a bug? I see something similar here https://www.postgresql.org/message-id/CAEzk6fdzJ3xYQZGbcuYM2rBd2BuDkUksmK=mY9UYYDugg_GgZg@mail.gmail.com and it was a bug

Re: Problem with a "complex" upsert

От
Geoff Winkless
Дата:
On Thu, 21 Jun 2018 at 15:07, Mario De Frutos Dieguez <mariodefrutos@gmail.com> wrote:
ON CONFLICT (geoid) DO UPDATE SET (b01003001) = ROW(EXCLUDED.b01003001);
​
At first glance, shouldn't the query simply be

 SET b01003001 = EXCLUDED.b01003001;

?

The second part of your email does suggest a bug though, syntax shouldn't cause a crash.

Geoff

Re: Problem with a "complex" upsert

От
Mario de Frutos Dieguez
Дата:
In this case is just one column but in other queries I'm updating
multiple columns thats why I set the ROW thing

2018-06-21 16:40 GMT+02:00 Geoff Winkless <pgsqladmin@geoff.dj>:
> On Thu, 21 Jun 2018 at 15:07, Mario De Frutos Dieguez
> <mariodefrutos@gmail.com> wrote:
>>
>> ON CONFLICT (geoid) DO UPDATE SET (b01003001) = ROW(EXCLUDED.b01003001);
>
> At first glance, shouldn't the query simply be
>
>  SET b01003001 = EXCLUDED.b01003001;
>
> ?
>
> The second part of your email does suggest a bug though, syntax shouldn't
> cause a crash.
>
> Geoff

Re: Problem with a "complex" upsert

От
Geoff Winkless
Дата:
On Thu, 21 Jun 2018 at 15:46, Mario de Frutos Dieguez
<mariodefrutos@gmail.com> wrote:
> In this case is just one column but in other queries I'm updating
> multiple columns thats why I set the ROW thing

Hmm. The documentation isn't explicit that that's valid syntax. Choices are
({expression|DEFAULT} [,...] |
( sub-SELECT )

and although the docs do say row constructors are valid as sub-SELECT
for comparisons I'm not sure that it fits here.

It does seem like it's trying to work anyway but my guess is that
"Table has type character varying". doesn't mean it's trying to use
the geoid value, but rather that it's implying that the ROW() (which
returns an anonymous type) is the same type as the target table of the
main INSERT query (rather than the type of the column in brackets).
Whether it counts as a bug or not (given that it's not explicitly
defined as allowed syntax) is probably an esoteric argument.

Are you using the ROW() syntax just because it's easier to build the
query programmatically? Otherwise I can't see why you wouldn't just
use SET col1=EXCLUDED.col1, col2=EXCLUDED.col2 [, ....]

Does
SET (b01003001) = (SELECT b01003001 FROM EXCLUDED)
work?

Geoff