Re: [HACKERS] Adding new output parameter of pg_stat_statements to identify operation of the query.

[Jim Nasby]

On 2/19/17 10:02 PM, Tom Lane wrote:

Jim Nasby  writes:

Something that needs to be considered with doing this in
pg_stat_statement is that a query that's reported there can contain
multiple SQL statements. I don't remember offhand if all statements get
parsed as a whole before anything else happens; if that's the case then
you could potentially have an array in pg_stat_statements indicating
what the command tags are.

I think that's been addressed as of 83f2061dd.

My own concern here is that pg_stat_statements shared hashtable entries
(pgssEntry) are currently 200 bytes, if I counted correctly.  It's hard
to see how to implement this feature without adding COMPLETION_TAG_BUFSIZE
(64 bytes) to that, which is kind of a large percentage bump for a feature
request that AFAIR nobody else has ever made.


AFAIK the only variable part of any tag is the rowcount from SELECT (if 
that's even part of the tag?)... so couldn't tags be switched over to an 
enum, at least internally?

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Adding new output parameter of pg_stat_statements to identify operation of the query.

[Tom Lane]

Jim Nasby  writes:
> Something that needs to be considered with doing this in  
> pg_stat_statement is that a query that's reported there can contain  
> multiple SQL statements. I don't remember offhand if all statements get  
> parsed as a whole before anything else happens; if that's the case then  
> you could potentially have an array in pg_stat_statements indicating  
> what the command tags are.

I think that's been addressed as of 83f2061dd.

My own concern here is that pg_stat_statements shared hashtable entries
(pgssEntry) are currently 200 bytes, if I counted correctly.  It's hard
to see how to implement this feature without adding COMPLETION_TAG_BUFSIZE
(64 bytes) to that, which is kind of a large percentage bump for a feature
request that AFAIR nobody else has ever made.

I suppose one way to avoid a large fixed-size field would be to store
the tag string out-of-line, similarly to what we do with the query text.
But then you've traded off a shared-memory-bloat worry for a performance
worry, ie what's the added overhead for dealing with another external
string.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Adding new output parameter of pg_stat_statements to identify operation of the query.

[Jim Nasby]

On 2/19/17 6:34 PM, Tsunakawa, Takayuki wrote:

We have done the job and are willing to post a patch.
I sent one through my work mail, but it seems that my mail didn't reach
the maillist, so I try again by using my personal mail account.

A view for counting the number of executions per operation type is being 
developed for PostgreSQL 10, which is expected to be released this year.

https://commitfest.postgresql.org/13/790/

Would this fit your need?  If not, what's the benefit of getting the operation 
type via pg_stat_statements?


Something that needs to be considered with doing this in  
pg_stat_statement is that a query that's reported there can contain  
multiple SQL statements. I don't remember offhand if all statements get  
parsed as a whole before anything else happens; if that's the case then  
you could potentially have an array in pg_stat_statements indicating  
what the command tags are.


Short of that, I'm not sure it would be a good idea to only support a  
single tag being visible at a time; it would be certain to induce users  
to create code that's going to be buggy as soon as someone starts using  
multiple statements.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Adding new output parameter of pg_stat_statements to identify operation of the query.

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of
> husttrip...@vip.sina.com
>  When using pg_stat_statements to collect running SQL of PG, we
> find it is hard for our program to get exact operation type of the SQL,
> such as SELECT, DELETE, UPDATE, INSERT, and so on.
>So we modify the the source code of pg_stat_statements and add another
> output parameter to tell us the operation type.
> Of course some application know their operation type, but for us and many
> public databases, doing this is hard.
> The only way is to reparse the SQL, obviously it is too expensive for a
> monitoring or diagnosis system.
> We have done the job and are willing to post a patch.
> I sent one through my work mail, but it seems that my mail didn't reach
> the maillist, so I try again by using my personal mail account.

A view for counting the number of executions per operation type is being 
developed for PostgreSQL 10, which is expected to be released this year.

https://commitfest.postgresql.org/13/790/

Would this fit your need?  If not, what's the benefit of getting the operation 
type via pg_stat_statements?

Regards
Takayuki Tsunakawa





-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Adding new output parameter of pg_stat_statements to identify operation of the query.

[husttripper]

 Hi PG hackers: When using pg_stat_statements to collect running SQL of 
PG, we find it is hard for our program to get exact operation type of the SQL,  
such as SELECT, DELETE, UPDATE, INSERT, and so on.   So we modify the the 
source code of pg_stat_statements and add another output parameter to tell us 
the operation type.Of course some application know their operation type, but 
for us and many public databases, doing this is hard. The only way is to 
reparse the SQL, obviously it is too expensive for a monitoring or diagnosis 
system.We have done the job and are willing to post a patch. I sent one through 
my work mail, but it seems that my mail didn't reach the maillist, so I try 
again by using my personal mail account.
jasonysli

Re: [HACKERS] Adding new output parameter of pg_stat_statements to identify operation of the query.

[Robert Haas]

On Sun, Feb 19, 2017 at 2:58 PM, jasonysli(李跃森)  wrote:
> Hi PG hackers:
>
>  When using pg_stat_statements to collect running SQL of PG, we find
> it is hard for our program to get exact operation type of the SQL,  such as
> SELECT, DELETE, UPDATE, INSERT, and so on.
>
>So we modify the the source code of pg_stat_statements and add another
> output parameter to tell us the operation type.
>
> Of course some application know their operation type, but for us and many
> public databases, doing this is hard.
>
> The only way is to reparse the SQL, obviously it is too expensive for a
> monitoring or diagnosis system.
>
> We have done the job and are willing to post a patch.

Hi,

You've posted this message to this mailing list 4 times so far.  Once is enough.

This effort sounds similar to https://commitfest.postgresql.org/13/790/

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Adding new output parameter of pg_stat_statements to identify operation of the query.

[李跃森]

Hi PG hackers:
 When using pg_stat_statements to collect running SQL of PG, we find it 
is hard for our program to get exact operation type of the SQL,  such as 
SELECT, DELETE, UPDATE, INSERT, and so on.
   So we modify the the source code of pg_stat_statements and add another 
output parameter to tell us the operation type.
Of course some application know their operation type, but for us and many 
public databases, doing this is hard.
The only way is to reparse the SQL, obviously it is too expensive for a 
monitoring or diagnosis system.
We have done the job and are willing to post a patch.

jasonysli

[HACKERS] Adding new output parameter of pg_stat_statements to identify operation of the query.

[李跃森]

Hi hackers:
 When using pg_stat_statements to collect running SQL of PG, we find it 
is hard for our program to get exact operation type of the SQL,  such as 
SELECT, DELETE, UPDATE, INSERT, and so on.
   So we modify the the source code of pg_stat_statements and add another 
output parameter to tell us the operation type.
Of course some application know their operation type, but for us and many 
public databases, doing this is hard.
The only way is to reparse the SQL, obviously it is too expensive for a 
monitoring or diagnosis system.
We have done the job and are willing to post a patch.

jasonysli