From: Tom Lane <tgl@sss.pgh.pa.us>
To: Sheryl Prabhu David <nestor.ssn@gmail.com>
cc: pgsql-sql@lists.postgresql.org
Subject: Re: Nested loop behaviour with pg_stat_activity
In-reply-to: 
 <CAKxBfWFZZO7MnvFVrJeX7bCJoXbVeeDY0Q2dJkKS8hs4ucYvXA@mail.gmail.com>
References: 
 <CAKxBfWFZZO7MnvFVrJeX7bCJoXbVeeDY0Q2dJkKS8hs4ucYvXA@mail.gmail.com>
Comments: In-reply-to Sheryl Prabhu David <nestor.ssn@gmail.com>
	message dated "Tue, 12 Mar 2024 13:00:57 +0530"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2046096.1710340244.1@sss.pgh.pa.us>
Date: Wed, 13 Mar 2024 10:30:44 -0400
Message-ID: <2046097.1710340244@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2046097.1710340244%40sss.pgh.pa.us>
Precedence: bulk

Sheryl Prabhu David <nestor.ssn@gmail.com> writes:
> In an Oracle database using a SQL like the one below, we have the ability
> to use, a series along the lines of Postgresqls generate_series() as seen
> 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, to
> 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