pg_stat_statements has duplicate entries for the same query & queryId
Hi Folks! Let me know if there's a better mailing list to ask this in. I have a statistics collector that collects data from various postgres statistics tables, pg_stat_statements being one of them. This is done on an entire fleet of Postgres databases. From the collected data we record the timestamp of each collection in the query itself as extract(epoch from now()) as ts. What I'm seeing is that for the same query *and* query id, there are two rows with different statistics data *at the same time*. For example one row can have 2 calls while another can have 4. Anyone else run into this or have any idea why this can occur? Regards, Jevon Cowell
Clarification on REVOKE ALL ON FUNCTION – Are there any privileges apart from EXECUTE?
Hi Postgres Community, I had a quick question regarding function-level privileges in PostgreSQL. We know that REVOKE EXECUTE ON FUNCTION ... removes the ability to call the function. But when we do: REVOKE ALL ON FUNCTION my_func(args) FROM some_role; does this revoke anything other than EXECUTE? Are there any other privileges that apply to functions which get revoked via REVOKE ALL? I looked through the documentation but couldn’t find a definitive answer on whether ALL includes more than just EXECUTE in the context of functions. Would appreciate any insights or pointers. --- Regards, Ayush
Re: Clarification on REVOKE ALL ON FUNCTION – Are there any privileges apart from EXECUTE?
On 5/18/25 12:17, Ayush Vatsa wrote: Hi Postgres Community, I had a quick question regarding function-level privileges in PostgreSQL. We know that |REVOKE EXECUTE ON FUNCTION ...| removes the ability to call the function. But when we do: REVOKE ALL ON FUNCTION my_func(args) FROM some_role; does this revoke anything other than |EXECUTE|? Are there any other privileges that apply to functions which get revoked via |REVOKE ALL|? > I looked through the documentation but couldn’t find a definitive answer on whether |ALL| includes more than just |EXECUTE| in the context of functions. See here: https://www.postgresql.org/docs/current/ddl-priv.html Table 5.1. ACL Privilege Abbreviations This is the best way to see what privileges apply to what objects. Would appreciate any insights or pointers. --- Regards, Ayush -- Adrian Klaver adrian.kla...@aklaver.com
Re: pg_stat_statements has duplicate entries for the same query & queryId
On 5/18/25 12:20, Jevon Cowell wrote: Hi Folks! Let me know if there's a better mailing list to ask this in. I have a statistics collector that collects data from various postgres statistics tables, pg_stat_statements being one of them. This is done on an entire fleet of Postgres databases. From the collected data we record the timestamp of each collection in the query itself as extract(epoch from now()) as ts. What I'm seeing is that for the same query /and/ query id, there are two rows with different statistics data /at the same time/. For example one row can have 2 calls while another can have 4. Anyone else run into this or have any idea why this can occur? From here: https://www.postgresql.org/docs/current/pgstatstatements.html queryid bigint Hash code to identify identical normalized queries. query text Text of a representative statement From here: https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT "now() is a traditional PostgreSQL equivalent to transaction_timestamp()" and "transaction_timestamp() is equivalent to CURRENT_TIMESTAMP, but is named to clearly reflect what it returns." Therefore now() is pinned to the time the transaction started. Consequently it is conceivable that the queries actually ran at different times but got stamped with an identical timestamp via extract(epoch from now()) as ts. Regards, Jevon Cowell -- Adrian Klaver adrian.kla...@aklaver.com
