Обсуждение: BUG #14162: No statistics for functions used as aggregates
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDE2MgpMb2dnZWQgYnk6ICAg ICAgICAgIERhbmllbCBNaWdvd3NraQpFbWFpbCBhZGRyZXNzOiAgICAgIGRt aWdvd3NraUBpa29mZmljZS5kZQpQb3N0Z3JlU1FMIHZlcnNpb246IDkuNS4w Ck9wZXJhdGluZyBzeXN0ZW06ICAgV2luZG93cyA3IDY0aXQKRGVzY3JpcHRp b246ICAgICAgICAKCkkgY3JlYXRlZCBzb21lIHVzZXIgZGVmaW5lZCBhZ2dy ZWdhdGUgd2l0aCBhIHVzZXIgZGVmaW5lZCBwbHBnc3FsIFNGVU5DLg0KDQpU aGUgY2FsbHMgdG8gcGxwZ3NxbCBmdW5jdGlvbnMgdXNlZCBpbiB0aGVzZSBh Z2dyZWdhdGVzIGFyZSBub3QgY291bnRlZCBieQp0aGUgc3RhdGlzdGljcyBj b2xsZWN0b3Igd2hlbiB0aGUgYWdncmVnYXRlIGlzIHVzZWQsIGJ1dCBjYWxs aW5nIHRoZW0KZGlyZWN0bHkgd2lsbCB3b3JrLiANCgoK
On Sat, May 28, 2016 at 10:52 PM, <dmigowski@ikoffice.de> wrote: > I created some user defined aggregate with a user defined plpgsql SFUNC. > > The calls to plpgsql functions used in these aggregates are not counted by > the statistics collector when the aggregate is used, but calling them > directly will work. Are you sure that track_functions is enabled? If it disabled by default. In order to track the number of calls of user-defined functions it needs to be set to "pl" or "all". -- Michael
WWVzLCBJIGFtIHN1cmUuIFRvIG1lYXN1cmUgbXkgZnVuY3Rpb24gSSBoYWQgdG8gcmVjcmVhdGUg aXQgd2l0aCBhIGRpZmZlcmVudCBuYW1lLCBhbmQgY3JlYXRlIGEgcGxwZ3NxbCB3cmFwcGVyIGZ1 bmN0aW9uIHdpdGggdGhlIG9yaWdpbmFsIHNpZ25hdHVyZSB0aGF0IGNhbGxzIGl0LiBOb3cgSSB3 YXMgYWJsZSB0byBnZXQgbXkgcmVzdWx0cyBhbmQgdGhlIGZ1bmN0aW9uIHNob3dlZCB1cCBpbiB0 aGUgc3RhdHMuIFRoZSB3cmFwcGVyIGRpZG4ndCENCg0KDQoNCi0tLS0tVXJzcHLDvG5nbGljaGUg TmFjaHJpY2h0LS0tLS0NClZvbjogTWljaGFlbCBQYXF1aWVyIFttYWlsdG86bWljaGFlbC5wYXF1 aWVyQGdtYWlsLmNvbV0gDQpHZXNlbmRldDogTW9udGFnLCAzMC4gTWFpIDIwMTYgMDU6NTMNCkFu OiBEYW5pZWwgTWlnb3dza2kgPGRtaWdvd3NraUBpa29mZmljZS5kZT4NCkNjOiBQb3N0Z3JlU1FM IG1haWxpbmcgbGlzdHMgPHBnc3FsLWJ1Z3NAcG9zdGdyZXNxbC5vcmc+DQpCZXRyZWZmOiBSZTog W0JVR1NdIEJVRyAjMTQxNjI6IE5vIHN0YXRpc3RpY3MgZm9yIGZ1bmN0aW9ucyB1c2VkIGFzIGFn Z3JlZ2F0ZXMNCg0KT24gU2F0LCBNYXkgMjgsIDIwMTYgYXQgMTA6NTIgUE0sICA8ZG1pZ293c2tp QGlrb2ZmaWNlLmRlPiB3cm90ZToNCj4gSSBjcmVhdGVkIHNvbWUgdXNlciBkZWZpbmVkIGFnZ3Jl Z2F0ZSB3aXRoIGEgdXNlciBkZWZpbmVkIHBscGdzcWwgU0ZVTkMuDQo+DQo+IFRoZSBjYWxscyB0 byBwbHBnc3FsIGZ1bmN0aW9ucyB1c2VkIGluIHRoZXNlIGFnZ3JlZ2F0ZXMgYXJlIG5vdCANCj4g Y291bnRlZCBieSB0aGUgc3RhdGlzdGljcyBjb2xsZWN0b3Igd2hlbiB0aGUgYWdncmVnYXRlIGlz IHVzZWQsIGJ1dCANCj4gY2FsbGluZyB0aGVtIGRpcmVjdGx5IHdpbGwgd29yay4NCg0KQXJlIHlv dSBzdXJlIHRoYXQgdHJhY2tfZnVuY3Rpb25zIGlzIGVuYWJsZWQ/IElmIGl0IGRpc2FibGVkIGJ5 IGRlZmF1bHQuIEluIG9yZGVyIHRvIHRyYWNrIHRoZSBudW1iZXIgb2YgY2FsbHMgb2YgdXNlci1k ZWZpbmVkIGZ1bmN0aW9ucyBpdCBuZWVkcyB0byBiZSBzZXQgdG8gInBsIiBvciAiYWxsIi4NCi0t DQpNaWNoYWVsDQo=
On Mon, May 30, 2016 at 4:56 PM, Daniel Migowski <dmigowski@ikoffice.de> wr= ote: > Yes, I am sure. To measure my function I had to recreate it with a differ= ent name, and create a plpgsql wrapper function with the original signature= that calls it. Now I was able to get my results and the function showed up= in the stats. The wrapper didn't! (please do not top-post it breaks the logic of the thread). Ah I see I got confused, nodeAgg.c does not track statistics of the transition or final functions when an aggregate is invoked, and has never done it. There is no call to pgstat_init_function_usage() when FunctionCallInvoke() is called so you cannot track statistics regarding that. Maybe that would be a interesting feature, but controlled by a different GUC than track_functions. -- Michael
T2ssIHRoZW4gbm8gc3RhdGlzdGljcyBmb3IgbWUgOikuIElmIHRoaXMgaXMgaW50ZW50aW9uYWws IGEgc21hbGwgbGluZSBvciB3YXJuaW5nIGluIHRoZSBkb2NzIHdvdWxkIGJlIGhlbHBmdWwuIA0K DQpJIGFsc28gd29uZGVyIGlmIGluIGFuZCBvdXQgZnVuY3Rpb25zIG9mIGRhdGF0eXBlcyBhcmUg dHJhY2tlZCAoQXMgdGhleSBhIEMgZnVuY3Rpb25zKSBvciBpZiB0aGVyZSBhcmUgb3RoZXIgcGxh Y2VzIHdoZXJlIGZ1bmN0aW9uIHRyYWNraW5nIGlzbid0IGltcGxlbWVudGVkIGFsdGhvdWdoIHRo ZSBmdW5jdGlvbnMgZ2V0IGNhbGxlZCAobWF5YmUgb24gaW5kZXggY3JlYXRpb24sIGV0Yy4pLiBJ IG5lZWQgdGhpcyBpbmZvcm1hdGlvbiBiZWNhdXNlIEkgY3VycmVudGx5IHRyeSB0byBkZWNpZGUg aWYgSSBoYXZlIHRvIGNvbnZlcnQgc29tZSBvZiB0aGVzZSBmdW5jdGlvbnMgdG8gbmF0aXZlIGMg ZnVuY3Rpb25zLiANCg0KLS0tLS1VcnNwcsO8bmdsaWNoZSBOYWNocmljaHQtLS0tLQ0KVm9uOiBN aWNoYWVsIFBhcXVpZXIgW21haWx0bzptaWNoYWVsLnBhcXVpZXJAZ21haWwuY29tXSANCkdlc2Vu ZGV0OiBEaWVuc3RhZywgMzEuIE1haSAyMDE2IDA2OjU3DQpBbjogRGFuaWVsIE1pZ293c2tpIDxk bWlnb3dza2lAaWtvZmZpY2UuZGU+DQpDYzogUG9zdGdyZVNRTCBtYWlsaW5nIGxpc3RzIDxwZ3Nx bC1idWdzQHBvc3RncmVzcWwub3JnPg0KQmV0cmVmZjogUmU6IFtCVUdTXSBCVUcgIzE0MTYyOiBO byBzdGF0aXN0aWNzIGZvciBmdW5jdGlvbnMgdXNlZCBhcyBhZ2dyZWdhdGVzDQoNCk9uIE1vbiwg TWF5IDMwLCAyMDE2IGF0IDQ6NTYgUE0sIERhbmllbCBNaWdvd3NraSA8ZG1pZ293c2tpQGlrb2Zm aWNlLmRlPiB3cm90ZToNCj4gWWVzLCBJIGFtIHN1cmUuIFRvIG1lYXN1cmUgbXkgZnVuY3Rpb24g SSBoYWQgdG8gcmVjcmVhdGUgaXQgd2l0aCBhIGRpZmZlcmVudCBuYW1lLCBhbmQgY3JlYXRlIGEg cGxwZ3NxbCB3cmFwcGVyIGZ1bmN0aW9uIHdpdGggdGhlIG9yaWdpbmFsIHNpZ25hdHVyZSB0aGF0 IGNhbGxzIGl0LiBOb3cgSSB3YXMgYWJsZSB0byBnZXQgbXkgcmVzdWx0cyBhbmQgdGhlIGZ1bmN0 aW9uIHNob3dlZCB1cCBpbiB0aGUgc3RhdHMuIFRoZSB3cmFwcGVyIGRpZG4ndCENCg0KKHBsZWFz ZSBkbyBub3QgdG9wLXBvc3QgaXQgYnJlYWtzIHRoZSBsb2dpYyBvZiB0aGUgdGhyZWFkKS4NCkFo IEkgc2VlIEkgZ290IGNvbmZ1c2VkLCBub2RlQWdnLmMgZG9lcyBub3QgdHJhY2sgc3RhdGlzdGlj cyBvZiB0aGUgdHJhbnNpdGlvbiBvciBmaW5hbCBmdW5jdGlvbnMgd2hlbiBhbiBhZ2dyZWdhdGUg aXMgaW52b2tlZCwgYW5kIGhhcyBuZXZlciBkb25lIGl0LiBUaGVyZSBpcyBubyBjYWxsIHRvIHBn c3RhdF9pbml0X2Z1bmN0aW9uX3VzYWdlKCkgd2hlbg0KRnVuY3Rpb25DYWxsSW52b2tlKCkgaXMg Y2FsbGVkIHNvIHlvdSBjYW5ub3QgdHJhY2sgc3RhdGlzdGljcyByZWdhcmRpbmcgdGhhdC4gTWF5 YmUgdGhhdCB3b3VsZCBiZSBhIGludGVyZXN0aW5nIGZlYXR1cmUsIGJ1dCBjb250cm9sbGVkIGJ5 IGEgZGlmZmVyZW50IEdVQyB0aGFuIHRyYWNrX2Z1bmN0aW9ucy4NCi0tDQpNaWNoYWVsDQo=
>>>>> "Daniel" == Daniel Migowski <dmigowski@ikoffice.de> writes: Daniel> Ok, then no statistics for me :). If this is intentional, a Daniel> small line or warning in the docs would be helpful. There's a workaround, though it's a somewhat bizarre one: If you apply any SET clause to the definition of a function, then calls to it will be tracked (if either track_functions is globally enabled or if the SET clause enables it) regardless of the context the function is called in. This is also true for security definer functions. This doesn't seem to be entirely intentional, it is fallout from the ability to do SET track_functions = ... in the definition of a specific function and have it take effect for that specific call. (There is some overhead with this, since SET clauses or secdef are implemented using a shim between the function and its caller, but the overhead should be much less than a plpgsql wrapper. Also, this will disable inlining of the function if it is LANGUAGE SQL.) Daniel> I also wonder if in and out functions of datatypes are tracked Daniel> (As they a C functions) or if there are other places where Daniel> function tracking isn't implemented although the functions get Daniel> called (maybe on index creation, etc.). I need this information Daniel> because I currently try to decide if I have to convert some of Daniel> these functions to native c functions. As far as I can tell, only the following cases are tracked: - functions called as triggers or event triggers - functions called from the expression evaluator - functions called as FROM-clause entries in queries - any function which has a SET clause or is SECURITY DEFINER Not tracked are any other cases, which include: - the function-call fastpath from clients - window functions and aggregate functions - functions used for sort/group/distinct/hash comparisons - index support functions - input and output functions - estimation functions - any case where a function calls another function without going via SQL -- Andrew (irc:RhodiumToad)
Hello Andrew, thanks for the nice info about the trick with SET, and your elaborations re= garding which functions are tracked. This is definitely useful for me. Regards, Daniel Migowski -----Urspr=FCngliche Nachricht----- Von: Andrew Gierth [mailto:andrew@tao11.riddles.org.uk]=20 Gesendet: Mittwoch, 1. Juni 2016 04:16 An: Daniel Migowski <dmigowski@ikoffice.de> Cc: Michael Paquier <michael.paquier@gmail.com>; PostgreSQL mailing lists <= pgsql-bugs@postgresql.org> Betreff: Re: [BUGS] BUG #14162: No statistics for functions used as aggrega= tes >>>>> "Daniel" =3D=3D Daniel Migowski <dmigowski@ikoffice.de> writes: Daniel> Ok, then no statistics for me :). If this is intentional, a Danie= l> small line or warning in the docs would be helpful.=20 There's a workaround, though it's a somewhat bizarre one: If you apply any SET clause to the definition of a function, then calls to = it will be tracked (if either track_functions is globally enabled or if the= SET clause enables it) regardless of the context the function is called in= . This is also true for security definer functions. This doesn't seem to be entirely intentional, it is fallout from the abilit= y to do SET track_functions =3D ... in the definition of a specific functio= n and have it take effect for that specific call. (There is some overhead with this, since SET clauses or secdef are implemen= ted using a shim between the function and its caller, but the overhead shou= ld be much less than a plpgsql wrapper. Also, this will disable inlining of= the function if it is LANGUAGE SQL.) Daniel> I also wonder if in and out functions of datatypes are tracked Da= niel> (As they a C functions) or if there are other places where Daniel> f= unction tracking isn't implemented although the functions get Daniel> call= ed (maybe on index creation, etc.). I need this information Daniel> becaus= e I currently try to decide if I have to convert some of Daniel> these fun= ctions to native c functions. As far as I can tell, only the following cases are tracked: - functions called as triggers or event triggers - functions called from the expression evaluator - functions called as FROM-clause entries in queries - any function which has a SET clause or is SECURITY DEFINER Not tracked are any other cases, which include: - the function-call fastpath from clients - window functions and aggregate functions - functions used for sort/group/distinct/hash comparisons - index support functions - input and output functions - estimation functions - any case where a function calls another function without going via SQL -- Andrew (irc:RhodiumToad)