Horton

Thread

Commits

  1. Re-allow DISTINCT in pl/pgsql expressions.

  1. Re: plpgsql variable assignment not supporting distinct anymore

    easteregg@verfriemelt.org — 2021-01-22T13:41:06Z

    the code provided is just a little poc to get the error ( which i have not included with my first mail sorry. )

   ERROR:  syntax error at or near "DISTINCT"
   LINE 8:     _test := DISTINCT a FROM ( VALUES ( (true), ( true ) ) )...


the code in production looked like this:


    _resource_id := 
        DISTINCT ti_resource_id
           FROM tabk.resource_timeline 
          WHERE ti_a2_id = _ab2_id
            AND ti_type = 'task'
    ;

this is backed up by a trigger function, that will ensure to every instance with the same ti_a2_id exists only one ti_resource_id, hence the query can never fail due to more than one row beeing returned. but this syntax is not supported anymore, which will break BC. up until PG 13, the assignment statement was just an implizit SELECT <expression> Query.
Since Tom Lane didn't mentioned this change in the other thread, i figured the devteam might not be aware of this chance.

i can refactor this line into

    _resource_id := 
        ti_resource_id
       FROM tabk.resource_timeline 
      WHERE ti_a2_id = _ab2_id
        AND ti_type = 'task'
      GROUP BY ti_resource_id
    ;

but concerns about BC was already raised, although with UNION there might be far less people affected.
with kind regards, richard

  2. Re: plpgsql variable assignment not supporting distinct anymore

    Pavel Stehule <pavel.stehule@gmail.com> — 2021-01-22T13:58:50Z

    pá 22. 1. 2021 v 14:41 odesílatel <easteregg@verfriemelt.org> napsal:

> the code provided is just a little poc to get the error ( which i have not
> included with my first mail sorry. )
>
>    ERROR:  syntax error at or near "DISTINCT"
>    LINE 8:     _test := DISTINCT a FROM ( VALUES ( (true), ( true ) ) )...
>
>
> the code in production looked like this:
>
>
>     _resource_id :=
>         DISTINCT ti_resource_id
>            FROM tabk.resource_timeline
>           WHERE ti_a2_id = _ab2_id
>             AND ti_type = 'task'
>     ;
>
> this is backed up by a trigger function, that will ensure to every
> instance with the same ti_a2_id exists only one ti_resource_id, hence the
> query can never fail due to more than one row beeing returned. but this
> syntax is not supported anymore, which will break BC. up until PG 13, the
> assignment statement was just an implizit SELECT <expression> Query.
> Since Tom Lane didn't mentioned this change in the other thread, i figured
> the devteam might not be aware of this chance.
>
> i can refactor this line into
>
>     _resource_id :=
>         ti_resource_id
>        FROM tabk.resource_timeline
>       WHERE ti_a2_id = _ab2_id
>         AND ti_type = 'task'
>       GROUP BY ti_resource_id
>     ;
>
> but concerns about BC was already raised, although with UNION there might
> be far less people affected.
> with kind regards, richard
>

Probably the fix is not hard, but it is almost the same situation as the
UNION case. The result of your code is not deterministic

If there are more different ti_resource_id then some values can be randomly
ignored - when hash agg is used.

The safe fix should be

_resource_id := (SELECT ti_resource_id
       FROM tabk.resource_timeline
      WHERE ti_a2_id = _ab2_id
        AND ti_type = 'task');

and you get an exception if some values are ignored. Or if you want to
ignore some values, then you can write

_resource_id := (SELECT MIN(ti_resource_id) -- or MAX
       FROM tabk.resource_timeline
      WHERE ti_a2_id = _ab2_id
        AND ti_type = 'task');

Using DISTINCT is not a good solution.

  3. Re: plpgsql variable assignment not supporting distinct anymore

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-01-22T18:59:50Z

    Pavel Stehule <pavel.stehule@gmail.com> writes:
> pá 22. 1. 2021 v 14:41 odesílatel <easteregg@verfriemelt.org> napsal:
>> ERROR:  syntax error at or near "DISTINCT"
>> LINE 8:     _test := DISTINCT a FROM ( VALUES ( (true), ( true ) ) )...

> Using DISTINCT is not a good solution.

Yeah.  It wouldn't be as painful to support this in the grammar
as it would be for UNION et al, so maybe we should just do it.
But I still find this to be mighty ugly plpgsql code.

			regards, tom lane