Re: pg_stat_user_functions' notion of user

pg_stat_user_functions has an inconsistent notion of what "user" is.
Whereas the other pg_stat_user_* views filter out non-user objects by
schema, pg_stat_user_functions checks for language "internal", which
does not successfully exclude builtin functions of language SQL. Is
there a reason for this inconsistency?

On Thu, Aug 05, 2010 at 04:58:32PM +0300, Peter Eisentraut wrote:
> pg_stat_user_functions has an inconsistent notion of what "user" is.
> Whereas the other pg_stat_user_* views filter out non-user objects
> by schema, pg_stat_user_functions checks for language "internal",
> which does not successfully exclude builtin functions of language
> SQL. Is there a reason for this inconsistency?

If I had to hazard a guess, it would be that the functionality was
written over time by different people, not all of whom were using the
same criteria for coherence.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On 8/5/10 6:58 AM, Peter Eisentraut wrote:
> pg_stat_user_functions has an inconsistent notion of what "user" is.
> Whereas the other pg_stat_user_* views filter out non-user objects by
> schema, pg_stat_user_functions checks for language "internal", which
> does not successfully exclude builtin functions of language SQL. Is
> there a reason for this inconsistency?

Undoubtedly because function data collection already filters on function
language, per the GUC setting. Not that that is a *good* reason, but I
can see how we arrived a the current functionality.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

On tor, 2010-08-05 at 07:13 -0700, David Fetter wrote:
> On Thu, Aug 05, 2010 at 04:58:32PM +0300, Peter Eisentraut wrote:
> > pg_stat_user_functions has an inconsistent notion of what "user" is.
> > Whereas the other pg_stat_user_* views filter out non-user objects
> > by schema, pg_stat_user_functions checks for language "internal",
> > which does not successfully exclude builtin functions of language
> > SQL. Is there a reason for this inconsistency?
>
> If I had to hazard a guess, it would be that the functionality was
> written over time by different people, not all of whom were using the
> same criteria for coherence.

Would anyone object to changing it to make it more consistent with other
others? And since we're jollily making catalog changes in 9.0 still,
could this also be backpatched?

On Sun, Aug 08, 2010 at 03:25:06PM +0300, Peter Eisentraut wrote:
> On tor, 2010-08-05 at 07:13 -0700, David Fetter wrote:
> > On Thu, Aug 05, 2010 at 04:58:32PM +0300, Peter Eisentraut wrote:
> > > pg_stat_user_functions has an inconsistent notion of what "user"
> > > is. Whereas the other pg_stat_user_* views filter out non-user
> > > objects by schema, pg_stat_user_functions checks for language
> > > "internal", which does not successfully exclude builtin
> > > functions of language SQL. Is there a reason for this
> > > inconsistency?
> >
> > If I had to hazard a guess, it would be that the functionality was
> > written over time by different people, not all of whom were using
> > the same criteria for coherence.
>
> Would anyone object to changing it to make it more consistent with
> other others? And since we're jollily making catalog changes in 9.0
> still, could this also be backpatched?

+1 for both.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> On tor, 2010-08-05 at 07:13 -0700, David Fetter wrote:
>> On Thu, Aug 05, 2010 at 04:58:32PM +0300, Peter Eisentraut wrote:
>>> pg_stat_user_functions has an inconsistent notion of what "user" is.
>>> Whereas the other pg_stat_user_* views filter out non-user objects
>>> by schema, pg_stat_user_functions checks for language "internal",
>>> which does not successfully exclude builtin functions of language
>>> SQL. Is there a reason for this inconsistency?

> Would anyone object to changing it to make it more consistent with other
> others? And since we're jollily making catalog changes in 9.0 still,
> could this also be backpatched?

The reason for the inconsistency is that the underlying behavior is
different: fmgr automatically doesn't collect stats for internal
functions. And yes I will object to trying to change that right now.
It's not just a "catalog change".

regards, tom lane

On sön, 2010-08-08 at 11:59 -0400, Tom Lane wrote:
> The reason for the inconsistency is that the underlying behavior is
> different: fmgr automatically doesn't collect stats for internal
> functions.

Sure it does: When they are defined in language SQL.

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> On sn, 2010-08-08 at 11:59 -0400, Tom Lane wrote:
>> The reason for the inconsistency is that the underlying behavior is
>> different: fmgr automatically doesn't collect stats for internal
>> functions.

> Sure it does: When they are defined in language SQL.

Those aren't "internal" (in the sense of prolang = 12) functions.

The big picture here is that we intentionally suppressed collection of
stats for "internal" functions because of performance concerns. The
test in the pg_stat_user_functions view is not about restricting what
functions can be shown in the view; it's just a small performance
optimization to avoid calling the low-level pg_stat_get_ functions for
pg_proc entries that we know a-priori won't have any stats.

If we added a clause to hide functions based on their schema, we would
logically need to provide the full set of pg_stat_all_functions,
pg_stat_sys_functions, and pg_stat_user_functions views. Otherwise the
views would provide no way to get at stats for functions that the
collection mechanism is perfectly willing to collect stats for. I'm
not convinced that it's worth the trouble, but we'd have to do it
if we decide to filter on schema as well as prolang.

regards, tom lane

On 8/8/10 8:40 AM, David Fetter wrote:
>> Would anyone object to changing it to make it more consistent with
>> other others? And since we're jollily making catalog changes in 9.0
>> still, could this also be backpatched?

I'd object. It's not a bug (arguable spec, maybe, but working as
spec'd), and it's not trivial, and it's functionality we've already
released.

-1 from me for doing anything in 9.0

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com