MIME-Version: 1.0
References: 
 <CAKxBfWFZZO7MnvFVrJeX7bCJoXbVeeDY0Q2dJkKS8hs4ucYvXA@mail.gmail.com>
 <2046097.1710340244@sss.pgh.pa.us>
In-Reply-To: <2046097.1710340244@sss.pgh.pa.us>
From: Sheryl Prabhu David <nestor.ssn@gmail.com>
Date: Thu, 14 Mar 2024 12:59:03 +0530
Message-ID: 
 <CAKxBfWEq9GoMxmtyoFnjwnAFbTLHFZK5G+57Cu2Oek67F_1X0Q@mail.gmail.com>
Subject: Re: Nested loop behaviour with pg_stat_activity
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000294995061399d9ce"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKxBfWEq9GoMxmtyoFnjwnAFbTLHFZK5G%2B57Cu2Oek67F_1X0Q%40mail.gmail.com>
Precedence: bulk

--000000000000294995061399d9ce
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Thanks a lot Tom, Postgres community truly is impressive, I never thought I
will get a response this soon if at all.

In Oracle with v$session being the inner table of a nested loop for each
scan we get different copies of it even as the data changes under us, thats
the hack someone figured out, and it gives us tons of samples - in the
order of ~500 per second with a single SQL run.

In Postgres the reason I mention the behaviour is different is that the
count I get with the outer group by for each connected client is one record
with a count of 2000 if I use 2000 records from generate_series. Oracles
behaviour would be multiple records each with a different wait event adding
up to 2000.

As you describe this may not be possible with Postgres. I will try SELECT
pg_stat_clear_snapshot() but not sure if that can be done is a single SQL
statement. Will let you know if it works.

Thanks a lot again.

Regards
Prabhu David

On Wed, Mar 13, 2024 at 8:00=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Sheryl Prabhu David <nestor.ssn@gmail.com> writes:
> > In an Oracle database using a SQL like the one below, we have the abili=
ty
> > to use, a series along the lines of Postgresqls generate_series() as se=
en
> > in the 'connect by' line as outer table of a forced nested loop, and
> > v$session(Oracles equivalent of pg_stat_activity) as the inner table, t=
o
> > get many samples of v$session each second.
>
> That's ... impressively ill-defined.  How do you know you are getting
> consistent "samples" at all?
>
> > Trying something similar in Postgres does not produce an equivalent
> result.
> > Instead of 'Number of sample' times *DIFFERENT* copies of
> pg_stat_activity,
> > we are seeing 'Number of sample' times *SAME* copy of pg_stat_activity,
> > unlike Oracle. MVCC and Isolation guarantees for regular tables is
> expected
> > to produce this kind of a result, but I was hoping pg_stat_activity
> being a
> > portal into internal data structures will act similar to Oracles
> v$session
> > bypassing MVCC+Isolation. I am hoping to find out if there is anyway to
> > force Oracle type behaviour for pg_stat_activity, please help.
>
> A Postgres session captures a snapshot of pg_stat_activity on first
> reference, and holds it until end of transaction or you call
> pg_stat_clear_snapshot().  Without this behavior you would get total
> garbage from queries as the data changes under you, especially so
> from join queries which may require multiple scans of the input.
>
> I don't think there's any way to precisely duplicate what you describe
> doing in Postgres, but you can easily get a similar result by
> alternating "SELECT pg_stat_clear_snapshot()" with selects from
> pg_stat_activity.
>
>                         regards, tom lane
>

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

<div dir=3D"ltr">Thanks a lot Tom, Postgres community truly is impressive, =
I never thought I will get a response this soon if at all.<div><br></div><d=
iv>In Oracle with v$session being the inner table of a nested loop for each=
 scan we get different copies of it even as the data changes under us, that=
s the hack someone figured out,=C2=A0and it gives us tons of samples - in t=
he order of ~500 per second with a single SQL run.=C2=A0</div><div><br></di=
v><div>In Postgres the reason I mention the behaviour is different is that =
the count I get with the outer group by for each connected client is one re=
cord with a count of 2000 if I use 2000 records from generate_series. Oracl=
es behaviour would be multiple records each with a different wait event add=
ing up to 2000.</div><div><br></div><div>As you describe this may not be po=
ssible with Postgres. I will try SELECT pg_stat_clear_snapshot() but not su=
re if that can be done is a single SQL statement. Will let you know if it w=
orks.</div><div><br></div><div>Thanks a lot again.</div><div><br></div><div=
>Regards</div><div>Prabhu David</div></div><br><div class=3D"gmail_quote"><=
div dir=3D"ltr" class=3D"gmail_attr">On Wed, Mar 13, 2024 at 8:00=E2=80=AFP=
M Tom Lane &lt;<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&g=
t; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0p=
x 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Sheryl=
 Prabhu David &lt;<a href=3D"mailto:nestor.ssn@gmail.com" target=3D"_blank"=
>nestor.ssn@gmail.com</a>&gt; writes:<br>
&gt; In an Oracle database using a SQL like the one below, we have the abil=
ity<br>
&gt; to use, a series along the lines of Postgresqls generate_series() as s=
een<br>
&gt; in the &#39;connect by&#39; line as outer table of a forced nested loo=
p, and<br>
&gt; v$session(Oracles equivalent of pg_stat_activity) as the inner table, =
to<br>
&gt; get many samples of v$session each second.<br>
<br>
That&#39;s ... impressively ill-defined.=C2=A0 How do you know you are gett=
ing<br>
consistent &quot;samples&quot; at all?<br>
<br>
&gt; Trying something similar in Postgres does not produce an equivalent re=
sult.<br>
&gt; Instead of &#39;Number of sample&#39; times *DIFFERENT* copies of pg_s=
tat_activity,<br>
&gt; we are seeing &#39;Number of sample&#39; times *SAME* copy of pg_stat_=
activity,<br>
&gt; unlike Oracle. MVCC and Isolation guarantees for regular tables is exp=
ected<br>
&gt; to produce this kind of a result, but I was hoping pg_stat_activity be=
ing a<br>
&gt; portal into internal data structures will act similar to Oracles v$ses=
sion<br>
&gt; bypassing MVCC+Isolation. I am hoping to find out if there is anyway t=
o<br>
&gt; force Oracle type behaviour for pg_stat_activity, please help.<br>
<br>
A Postgres session captures a snapshot of pg_stat_activity on first<br>
reference, and holds it until end of transaction or you call<br>
pg_stat_clear_snapshot().=C2=A0 Without this behavior you would get total<b=
r>
garbage from queries as the data changes under you, especially so<br>
from join queries which may require multiple scans of the input.<br>
<br>
I don&#39;t think there&#39;s any way to precisely duplicate what you descr=
ibe<br>
doing in Postgres, but you can easily get a similar result by<br>
alternating &quot;SELECT pg_stat_clear_snapshot()&quot; with selects from<b=
r>
pg_stat_activity.<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
</blockquote></div>

--000000000000294995061399d9ce--