Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pgsql-hackers-owner+archive@lists.postgresql.org>)
	id 1l2xcR-0003XI-Al
	for pgsql-hackers@arkaria.postgresql.org; Fri, 22 Jan 2021 14:40:59 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-hackers-owner+archive@lists.postgresql.org>)
	id 1l2xcQ-0005lP-6J
	for pgsql-hackers@arkaria.postgresql.org; Fri, 22 Jan 2021 14:40:58 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <easteregg@verfriemelt.org>)
	id 1l2wgd-0006aK-0V
	for pgsql-hackers@lists.postgresql.org; Fri, 22 Jan 2021 13:41:15 +0000
Received: from mail.verfriemelt.org ([116.202.226.213])
	by magus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.92)
	(envelope-from <easteregg@verfriemelt.org>)
	id 1l2wgW-00082Z-Uj
	for pgsql-hackers@lists.postgresql.org; Fri, 22 Jan 2021 13:41:14 +0000
X-Spam-Status: No, hits=1.6 required=4.0
	tests=AWL: 0.132,BAYES_00: -1.665,FH_DATE_PAST_20XX: 3.188,
	TOTAL_SCORE: 1.655,autolearn=no
X-Spam-Level: *
Received: from localhost ([127.0.0.1])
	by mail.verfriemelt.org;
	Fri, 22 Jan 2021 14:41:06 +0100
To: "PostgreSQL Hackers" <pgsql-hackers@lists.postgresql.org>
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>, "Pavel Stehule" <pavel.stehule@gmail.com>
From: easteregg@verfriemelt.org
Subject: Re: plpgsql variable assignment not supporting distinct anymore
In-Reply-To: 
	CAFj8pRCicOt5+2A+qnYiZR+ryPDUV3Rof9nBZhy=K1jPkXNyYQ@mail.gmail.com
Message-ID: <20210122134106.e94c5cd7@mail.verfriemelt.org>
Date: Fri, 22 Jan 2021 14:41:06 +0100
X-User-Agent: Mozilla/5.0 (X11;
 Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko)
	Chrome/89.0.4389.9 Safari/537.36
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Precedence: bulk

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

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


the code in production looked like this:


    =5Fresource=5Fid :=3D=20
        DISTINCT ti=5Fresource=5Fid
           FROM tabk.resource=5Ftimeline=20
          WHERE ti=5Fa2=5Fid =3D =5Fab2=5Fid
            AND ti=5Ftype =3D 'task'
    ;

this is backed up by a trigger function, that will ensure to every insta=
nce with the same ti=5Fa2=5Fid exists only one ti=5Fresource=5Fid, 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 1=
3, the assignment statement was just an implizit SELECT <expression> Que=
ry.
Since Tom Lane didn't mentioned this change in the other thread, i figur=
ed the devteam might not be aware of this chance.

i can refactor this line into

    =5Fresource=5Fid :=3D=20
        ti=5Fresource=5Fid
       FROM tabk.resource=5Ftimeline=20
      WHERE ti=5Fa2=5Fid =3D =5Fab2=5Fid
        AND ti=5Ftype =3D 'task'
      GROUP BY ti=5Fresource=5Fid
    ;

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