MIME-Version: 1.0
In-Reply-To: 
 <CAEZATCVy77hhgTZ1Yrvbp6GbsUuWY0Bw8CLbv_Jr2mAF_bOkBA@mail.gmail.com>
References: 
 <CAFj8pRDY+m9OOxfO10R7J0PAkCCauM-TweaTrdsrsLGMb1VbEQ@mail.gmail.com>
 <623395617.20171113141500@gf.microolap.com>
 <CAFj8pRDdS7ViLBJ6eA93u=C_x15EBv2deiNQDGkBS=LNrjzLLw@mail.gmail.com>
 <CAFj8pRBfb-GTZSHSRVTpMzGr26-7e-_RmOmRpmuk+xuDTgC=mA@mail.gmail.com>
 <28924bcc-9242-9798-e4e8-9d83cea3fef6@dalibo.com>
 <CAFj8pRBRxJ09ibuZT+KK3E+vc3-sXAz7HrbW3oVie7FwQRU-uQ@mail.gmail.com>
 <ae98027e-25a7-b229-ffec-b05d68162718@dalibo.com>
 <CAFj8pRATM44F1ugXxTn6aofxOa=3DZbqOJ17=EVyG+CEzsRQvw@mail.gmail.com>
 <CAFj8pRDnoA3J2RM=WZJdYBXEiJUOfDv-gyJmp81Pq93jmrBb5g@mail.gmail.com>
 <CAFj8pRCTz_CRez3vFo_Ta_m=KtOxBGHE9+T1QG3UgRbuURfzjA@mail.gmail.com>
 <CAFj8pRA_jZYuTRHEMsv8CnZLBqmnS5xRjcZh-uf0nBWA7WrzMA@mail.gmail.com>
 <alpine.DEB.2.21.1808211938510.11873@lancre>
 <CAFj8pRCRUpNjX9Fb49SaADbRnCE69ndkOvtoeKxwvxetfJ8=kA@mail.gmail.com>
 <alpine.DEB.2.21.1808220831550.10677@lancre>
 <CAFj8pRCyvx37Fnw6yHdscGbbGo_Ak3WdeKiZ6arFW8JTA099YA@mail.gmail.com>
 <alpine.DEB.2.21.1808230927090.31897@lancre>
 <CAFj8pRAcFv09qOcbr09c2AbwZ9DGw5Hs6rPcYoo7r9OLdWWB2A@mail.gmail.com>
 <CAEZATCVy77hhgTZ1Yrvbp6GbsUuWY0Bw8CLbv_Jr2mAF_bOkBA@mail.gmail.com>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Tue, 4 Sep 2018 15:00:51 +0200
Message-ID: 
 <CAFj8pRBSQDs3SpJ6EV-SP2Gkoc6OwxzCkB8ka7815o3Tw2TkxQ@mail.gmail.com>
Subject: Re: [HACKERS] proposal: schema variables
To: Dean Rasheed <dean.a.rasheed@gmail.com>
Cc: Fabien COELHO <coelho@cri.ensmp.fr>,
 Gilles Darold <gilles.darold@dalibo.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000c248eb05750b41e8"
Precedence: bulk

--000000000000c248eb05750b41e8
Content-Type: text/plain; charset="UTF-8"

Hi

2018-09-04 9:21 GMT+02:00 Dean Rasheed <dean.a.rasheed@gmail.com>:

> AFAICS this patch does nothing to consider parallel safety -- that is,
> as things stand, a variable is allowed in a query that may be
> parallelised, but its value is not copied to workers, leading to
> incorrect results. For example:
>
> create table foo(a int);
> insert into foo select * from generate_series(1,1000000);
> create variable zero int;
> let zero = 0;
>
> explain (costs off) select count(*) from foo where a%10 = zero;
>
>                   QUERY PLAN
> -----------------------------------------------
>  Finalize Aggregate
>    ->  Gather
>          Workers Planned: 2
>          ->  Partial Aggregate
>                ->  Parallel Seq Scan on foo
>                      Filter: ((a % 10) = zero)
> (6 rows)
>
> select count(*) from foo where a%10 = zero;
>
>  count
> -------
>  38037    -- Different random result each time, should be 100,000
> (1 row)
>
> Thoughts?
>

The query use copy of values of variables now - but unfortunately, these
values are not passed to workers.  Should be fixed.

Thank you for test case.

Pavel


> Regards,
> Dean
>

--000000000000c248eb05750b41e8
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi<br><div><div class=3D"gmail_extra"><br><div class=3D"gm=
ail_quote">2018-09-04 9:21 GMT+02:00 Dean Rasheed <span dir=3D"ltr">&lt;<a =
href=3D"mailto:dean.a.rasheed@gmail.com" target=3D"_blank">dean.a.rasheed@g=
mail.com</a>&gt;</span>:<br><blockquote class=3D"gmail_quote" style=3D"marg=
in:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">AFAICS this patc=
h does nothing to consider parallel safety -- that is,<br>
as things stand, a variable is allowed in a query that may be<br>
parallelised, but its value is not copied to workers, leading to<br>
incorrect results. For example:<br>
<br>
create table foo(a int);<br>
insert into foo select * from generate_series(1,1000000);<br>
create variable zero int;<br>
let zero =3D 0;<br>
<br>
explain (costs off) select count(*) from foo where a%10 =3D zero;<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 QUERY PLAN<b=
r>
------------------------------<wbr>-----------------<br>
=C2=A0Finalize Aggregate<br>
=C2=A0 =C2=A0-&gt;=C2=A0 Gather<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Workers Planned: 2<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&gt;=C2=A0 Partial Aggregate<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-&gt;=C2=A0 Parallel=
 Seq Scan on foo<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0Filter: ((a % 10) =3D zero)<br>
(6 rows)<br>
<br>
select count(*) from foo where a%10 =3D zero;<br>
<br>
=C2=A0count<br>
-------<br>
=C2=A038037=C2=A0 =C2=A0 -- Different random result each time, should be 10=
0,000<br>
(1 row)<br>
<br>
Thoughts?<br></blockquote><div><br></div><div>The query use copy of values =
of variables now - but unfortunately, these values are not passed to worker=
s.=C2=A0 Should be fixed.</div><div><br></div><div>Thank you for test case.=
</div><div><br></div><div>Pavel</div><div><br></div><blockquote class=3D"gm=
ail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-le=
ft:1ex">
<br>
Regards,<br>
Dean<br>
</blockquote></div><br></div></div></div>

--000000000000c248eb05750b41e8--