Re: Nested loop behaviour with pg_stat_activity

Sheryl Prabhu David <nestor(dot)ssn(at)gmail(dot)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