Re: pg_stat_statements oddity with track = all

Hi, a crazy idea: - add a parent_statement_id column that would be NULL for top level queries - build statement_id for nested queries based on the merge of: a/ current_statement_id and parent one or b/ current_statement_id and nested level. this would offer the ability to track counters at

Re: pg_stat_statements oddity with track = all

Hi Julien, > The extra field I've proposed would increase the number of records, as it > needs to be a part of the key. To get an increase in the number of records that means that the same statement would appear at top level AND nested level. This seems a corner case with very low (neglectible

Re: Implementing Incremental View Maintenance

Hello Konstantin, I remember testing it with pg_stat_statements (and planning counters enabled). Maybe identifying internal queries associated with this (simple) test case, could help dev team ? Regards PAscal -- Sent from: https://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

Re: WIP: System Versioned Temporal Table

Hello, it seems that Oracle (11R2) doesn't add the Start and End timestamp columns and permit statement like select * from tt union select * from tt AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '6' SECOND) minus select * from tt VERSIONS BETWEEN TIMESTAMP (SYSTIMESTAMP - INTERVAL '6' second) and SY

Re: Query about pg asynchronous processing support

Hi, You should search informations about postgres hooks like thoses used in extension pg_stat_statements https://github.com/postgres/postgres/blob/master/contrib/pg_stat_statements/pg_stat_statements.c And about background capabilities as thoses used in extension pg_background https://github.com/

RE: Is it useful to record whether plans are generic or custom?

>> Main purpose is to decide (1) the user interface and (2) the >> way to get the plan type from pg_stat_statements. >> >> (1) the user interface >> I added a new boolean column 'generic_plan' to both >> pg_stat_statements view and the member of the hash key of >> pg_stat_statements. >> >> This is

nested queries vs. pg_stat_activity

Hello, An other solution is to expose nested queryid, and to join it with pg_stat_statements. Actual development trying to add queryid to pg_stat_activity isn't helpfull, because it is only exposing top level one. Extension pg_stat_sql_plans (github) propose a function called pg_backend_queryid

RE: Is it useful to record whether plans are generic or custom?

I thought it might be preferable to make a GUC to enable or disable this feature, but changing the hash key makes it harder. >> >>> What happens if the server was running with this option enabled and then >>> restarted with the option disabled? Firstly two entries for the same query

Re: [FEATURE PATCH] pg_stat_statements with plans (v02)

Hello, I'm very interested in pg_stat_statements usage, and I'm very happy to see you adding plans to it. Reading other pg_stat_statements threads on this forum, there are also activ developments to add: - planing duration, - first date, - last_update date, - parameters for normalized queries, - .

Re: Sample values for pg_stat_statements

+1 If pgss had a PlanId column (just after QueryId), that would be wonderfull ;o) Question: Is there a simple way to "un-normalize" the query (I mean rebuild the original query as it was before normalization) ? Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers

Poc: pg_stat_statements with planid

Hello Hackers As described in http://www.postgresql-archive.org/pg-stat-statements-HLD-for-futur-developments-td6012381.html here is a proposal to add a planid identifier to pg_stat_statements. The new key for the view is userid, dbid, queryid, planid. planid is calculated using code from pg_s

Re: Planning counters in pg_stat_statements

+1 Shouldn't this be added in next CF ? nb: As plan_time is not included into total_time, could it be added to usage (for statement eviction calculation) ? I will try to include plan_time into my proposed version of pgss with planid. http://www.postgresql-archive.org/Poc-pg-stat-statements-with-p

Re: Planning counters in pg_stat_statements

Hello, When testing this patch on my WIN1252 database with my java front end, using 11devel snapshot I get org.posgresql.util.PSQLException: ERROR: character with byte sequence 0x90 in encoding "WIN1252" has no equivalent in encoding "UTF8" When using psql with client_encoding = WIN1252, q

Re: Diagonal storage model

Great Idea ! thank you Konstantin -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

Re: Planning counters in pg_stat_statements

I forgot to recompile core ... now only utility statements (with 0 plans) seems truncated. Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

Re: [PROPOSAL] timestamp informations to pg_stat_statements

> No, the entries are not removed randomly. We track "usage" for each > entry (essentially +1 for each time the query got executed, with a decay > factor applied on each eviction (and we evict 5% at a time). OK I apologize, I hadn't looked in source code in details, and effectively the "Usage" alg

Re: [FEATURE PATCH] pg_stat_statements with plans (v02)

> I've created a draft patch that provides access to plans in a view > called pg_stat_statements_plans. ++ I like it ! > There is no column that indicates whether the plan is "good" or "bad", > because that is evident from the execution time of both plans and because > that would require s

Re: [FEATURE PATCH] pg_stat_statements with plans (v02)

[...] > I've taken a look at your patch. I agree that having a plan identifier > would be great, but I'm not a big fan of the jumbling. That's a lot of > hashing that needs to be done to decide wether two plans are > essentially equivalent or not. As there is no planid available yet in core, I reu

Re: Proposal: Better query optimization for "NOT IN" clause

Hello, Just for information there are some works regarding how to include this in core, that may interest you ;o) see "NOT IN subquery optimization" https://www.postgresql.org/message-id/flat/1550706289606-0.post%40n3.nabble.com commitfest entry: https://commitfest.postgresql.org/24/2023/ and "

Re: Hooks for session start and end, take two

Hello, Thank you for the work done on this subject. After starting to play with it, I have a question and a remark: > - previous hook calls were only called for normal backends, which was > incorrect as we define the backend so as we apply no backend-related > filtering for the hook. Does that

Re: Hooks for session start and end, take two

Michael Paquier-2 wrote > On Thu, Sep 26, 2019 at 09:57:57AM -0700, legrand legrand wrote: >> Does that mean that all processes seen in pg_stat_activity like >> - autovacuum launcher >> - logical replication launcher >> - background writer >> - checkpointer >

Re: How to install login_hook in Postgres 10.5

pavan95 wrote > Hello Community, > > While I was searching for logon trigger in postgres similar to that of > Oracle, I came across "login_hook", which can be installed as a Postgres > database extension to mimic a logon trigger. > > But I tried to install but failed. Error is that it could not

Re: Columns correlation and adaptive query optimization

Hello Konstantin, What you have proposed regarding join_selectivity and multicolumn statistics is a very good new ! Regarding your auto_explain modification, maybe an "advisor" mode would also be helpfull (with auto_explain_add_statistics_threshold=-1 for exemple). This would allow to track which

Re: any plan to support shared servers like Oracle in PG?

There already are solutions regarding this feature in Postgres using "connection pooler" wording see pgpool: http://www.pgpool.net/mediawiki/index.php/Main_Page pgbouncer: https://pgbouncer.github.io/ there are also discussions to include this as a core feature https://www.postgresql.org/mes

Re: Feature improvement: can we add queryId for pg_catalog.pg_stat_activity view?

Hello, This is available in https://github.com/legrandlegrand/pg_stat_sql_plans extension with a specific function pgssp_backend_queryid(pid) that permits to join pg_stat_activity with pg_stat_sql_plans (that is similar to pg_stat_statements) and also permits to collect samples of wait events per

Re: Feature improvement: can we add queryId for pg_catalog.pg_stat_activity view?

Great, thank you Julien ! Would it make sense to add it in auto explain ? I don't know for explain itself, but maybe ... Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

[survey] New "Stable" QueryId based on normalized query text

Hello, There are many projects that use alternate QueryId distinct from the famous pg_stat_statements jumbling algorithm. https://github.com/postgrespro/aqo query_hash https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.ViewPlans.html sql_hash https://github.

Re: Feature improvement: can we add queryId for pg_catalog.pg_stat_activity view?

Hi Jim, Robert, As this is a distinct subject from adding QueryId to pg_stat_activity, would it be possible to continue the discussion "new QueryId definition" (for postgres open source software) here: https://www.postgresql.org/message-id/1553029215728-0.p...@n3.nabble.com Thanks in advance. R

RE: [survey] New "Stable" QueryId based on normalized query text

> From: "Tsunakawa, Takayuki" >> From: legrand legrand [mailto:legrand_legrand@] >> There are many projects that use alternate QueryId >> distinct from the famous pg_stat_statements jumbling algorithm. >I'd like to welcome the standard QueryID that DBAs

Re: [survey] New "Stable" QueryId based on normalized query text

> From "Kyotaro HORIGUCHI-2" >>At Wed, 20 Mar 2019 00:23:30 +, "Tsunakawa, Takayuki" >>> From: legrand legrand [mailto:legrand_legrand@] >>> norm.9: comments aware >> Is this to distinguish queries that have different comments for optimizer

Re: [survey] New "Stable" QueryId based on normalized query text

Julien Rouhaud wrote > On Wed, Mar 20, 2019 at 8:39 PM legrand legrand > < > legrand_legrand@ > > wrote: >> >> Yes, I would like first to understand what are the main needs, > > I don't really see one implementation that suits every need, as > probab

Re: [survey] New "Stable" QueryId based on normalized query text

maybe this patch (with a GUC) https://www.postgresql.org/message-id/55e51c48.1060...@uptime.jp would be enough for thoses actually using a text normalization function. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

Re: [survey] New "Stable" QueryId based on normalized query text

Julien Rouhaud wrote > On Wed, Mar 20, 2019 at 10:18 PM legrand legrand > < > legrand_legrand@ > > wrote: >> >> On my personal point of view, I need to get the same Queryid between >> (OLAP) >> environments >> to be able to compare Production, Pre

Re: [survey] New "Stable" QueryId based on normalized query text

Julien Rouhaud wrote > On Wed, Mar 20, 2019 at 10:30 PM legrand legrand > < > legrand_legrand@ > > wrote: >> >> maybe this patch (with a GUC) >> https://www.postgresql.org/message-id/ > 55E51C48.1060102@ >> would be enough for thoses actually using

RE: Planning counters in pg_stat_statements (using pgss_store)

Hi, Here is a rebased and corrected version . Columns naming has not been modified, I would propose to change it to: - plans: ok - planning_time --> plan_time - calls: ok - total_time --> exec_time - {min,max,mean,stddev}_time: ok - new total_time (being the sum of plan_time and exec_time)

RE: Planning counters in pg_stat_statements (using pgss_store)

> This patch has multiple trailing whitespace, indent and coding style > issues. You should consider running pg_indent before submitting a > patch. I attach the diff after running pgindent if you want more > details about the various issues. fixed > - * Track statement execution times acro

RE: Feature improvement: can we add queryId for pg_catalog.pg_stat_activity view?

>> Would it make sense to add it in auto explain ? >> I don't know for explain itself, but maybe ... > I'd think that people interested in getting the queryid in the logs > would configure the log_line_prefix to display it consistently rather > than having it in only a subset of cases, so that's

RE: Feature improvement: can we add queryId for pg_catalog.pg_stat_activity view?

>> Shoudn't you add this to commitfest ? > I added it last week, see https://commitfest.postgresql.org/23/2069/ Oups, sorry for the noise

Re: Planning counters in pg_stat_statements (using pgss_store)

As there are now 3 locking times on pgss hash struct, one day or an other, somebody will ask for a GUC to disable this feature (to be able to run pgss unchanged with only one lock as today). With this GUC, pgss_store should be able to store the query text and accumulated execution duration in th

RE: Planning counters in pg_stat_statements (using pgss_store)

here is a new version: - "track_planning" GUC added to permit to keep previous behavior unchanged - columns names have been changed / added: total_plan_time, total_exec_time, total_time - trailing whitespaces and comments wider than 80 characters not fixed

Re: minimizing pg_stat_statements performance overhead

Hi, the part that hurts in terms or performances is: if (jstate.clocations_count > 0) pgss_store(pstate->p_sourcetext, query->queryId, query->stmt_location, query->stmt_len,

RE: minimizing pg_stat_statements performance overhead

Your fix is probably the best one. Maybe this could be considered as a bug and back ported to previous versions ... Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

Re: Planning counters in pg_stat_statements (using pgss_store)

>> - trailing whitespaces and comments wider than 80 characters >> not fixed > why? In case it's not clear, I'm talking about the .c file, not the > regression tests. I work on a poor msys install on windows 7, where perl is broken ;o( So no pgindent available. Will fix that later,

RE: minimizing pg_stat_statements performance overhead

my test case: drop table a; create table a (); DO $$ DECLARE i int; BEGIN for i in 1..20 loop execute 'alter table a add column a'||i::text||' int'; end loop; END $$; select pg_stat_statements_reset(); set pg_stat_statements.track='none'; DO $$ DECLARE i int; j int; BEGIN for j in 1..20 loop f

Re: Planning counters in pg_stat_statements (using pgss_store)

Julien Rouhaud wrote > On Wed, Mar 27, 2019 at 9:36 PM legrand legrand > < > legrand_legrand@ > > wrote: >> >> >> - trailing whitespaces and comments wider than 80 characters >> >> not fixed >> >> > why? In case it'

Re: How to install login_hook in Postgres 10.5

Hello, shouldn't we update associated commitfest entry https://commitfest.postgresql.org/15/1318/ to give it a chance to be included in pg13 ? Regards PAscal -- Sent from: https://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

Re: Feature improvement: can we add queryId for pg_catalog.pg_stat_activity view?

> However having the nested queryid in > pg_stat_activity would be convenient to track > what is a long stored functions currently doing. +1 And this could permit to get wait event sampling per queryid when pg_stat_statements.track = all Regards PAscal -- Sent from: https://www.postgresql

Re: Feature improvement: can we add queryId for pg_catalog.pg_stat_activity view?

Kyotaro Horiguchi-4 wrote > At Sun, 4 Aug 2019 00:04:01 -0700 (MST), legrand legrand < > legrand_legrand@ > > wrote in < > 1564902241482-0.post@.nabble >> >> > However having the nested queryid in >> > pg_stat_activity would be convenient

Re: [survey] New "Stable" QueryId based on normalized query text

Hi Jim, Its never too later, as nothing has been concluded about that survey ;o) For information, I thought It would be possible to get a more stable QueryId, by hashing relation name or fully qualified names. With the support of Julien Rouhaud, I tested with this kind of code: case RTE

Re: [survey] New "Stable" QueryId based on normalized query text

my understanding is * pg_stat_statements.track = 'none' or 'top' (default) or 'all' to make queryId optionally computed * a new GUC: pg_stat_statements.queryid_based = 'oids' (default) or 'names' or 'fullnames' to choose the queryid computation algorithm am I rigth ? -- Sent from: h

Re: AS OF queries

Maybe that a simple check of the asof_timestamp value like: asof_timestamp >= now() - time_travel_period AND asof_timestamp >= latest_table_ddl would permit to raise a warning or an error message saying that query result can not be garanteed with this asof_timestamp value. latest_table_ddl bei

Re: AS OF queries

> But performing this query on each access to the table seems to be bad > idea: in case of nested loop join it can cause significant degrade of > performance. this could be a pre-plan / pre-exec check, no more. > But I am not sure that this check is actually needed. > If table is changed in some

Re: AS OF queries

> Sorry, truncate is not compatible with AS OF. It is performed at file > level and deletes old old version. > So if you want to use time travel, you should not use truncate. As time travel doesn't support truncate, I would prefer it to be checked. If no check is performed, ASOF queries (with tim

Re: PoC: custom signal handler for extensions

This comes from https://github.com/postgrespro/pg_query_state Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

Re: Add PGDLLIMPORT to enable_hashagg

Hello, I know an other extension that would need the same for: src/include/optimizer/paths.h geqo_threshold enable_geqo min_parallel_index_scan_size min_parallel_table_scan_size src/include/optimizer/cost.h max_parallel_workers_per_gather enable_hashjoin enable_mergejoin enable_nestloop enable_in

Re: Add PGDLLIMPORT to enable_hashagg

Thank you Metin ! Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

Re: Implementing Incremental View Maintenance

Hello, First of all many thanks for this Great feature replacing so many triggers by a so simple syntax ;o) I was wondering about performances and add a look at pg_stat_statements (with track=all) with IVM_v9.patch. For each insert into a base table there are 3 statements: - ANALYZE pg_temp_3.

Re: Implementing Incremental View Maintenance

Hello, regarding my initial post: > For each insert into a base table there are 3 statements: > - ANALYZE pg_temp_3.pg_temp_81976 > - WITH updt AS ( UPDATE public.mv1 AS mv SET __ivm_count__ = ... > - DROP TABLE pg_temp_3.pg_temp_81976 For me there where 3 points to discuss: - create/drop tables

Re: Implementing Incremental View Maintenance

Yugo Nagata wrote > On Mon, 23 Dec 2019 03:41:18 -0700 (MST) > legrand legrand < > legrand_legrand@ > > wrote: > > [ ...] > >> I would even >> prefer a common "table" shared between all sessions like GLOBAL TEMPORARY >> T

Re: Implementing Incremental View Maintenance

Hello, Thank you for this patch. I have tried to use an other patch with yours: "Planning counters in pg_stat_statements (using pgss_store)" https://www.postgresql.org/message-id/CAOBaU_Y12bn0tOdN9RMBZn29bfYYH11b2CwKO1RO7dX9fQ3aZA%40mail.gmail.com setting shared_preload_libraries='pg_stat_stateme

Incremental View Maintenance: ERROR: out of shared memory

Hello here is an unexpected error found while testing IVM v11 patches create table b1 (id integer, x numeric(10,3)); create incremental materialized view mv1 as select id, count(*),sum(x) from b1 group by id; do $$ declare i integer; begin for i in 1..1 loop

Re: WIP: System Versioned Temporal Table

Vik Fearing-4 wrote > On 05/01/2020 11:16, Surafel Temesgen wrote: >> >> >> On Fri, Jan 3, 2020 at 4:22 PM Vik Fearing >> < > vik.fearing@ > vik.fearing@ > >> wrote: >> > > [...] > > You only test FROM-TO and with a really wide interval.  There are no > tests for AS OF and no test

Re: Planning counters in pg_stat_statements (using pgss_store)

Hi Julien, I would like to create a link with https://www.postgresql.org/message-id/1577490124579-0.p...@n3.nabble.com where we met an ASSET FAILURE because query text was not initialized ... The question raised is: - should query text be always provided or - if not how to deal that case (in

Re: Planning counters in pg_stat_statements (using pgss_store)

Julien Rouhaud wrote > On Sun, Jan 5, 2020 at 4:11 PM legrand legrand > < > legrand_legrand@ > > wrote: >> >> Hi Julien, >> >> I would like to create a link with >> https://www.postgresql.org/message-id/ > 1577490124579-0.post@.nabble >>

Re: WIP: System Versioned Temporal Table

Vik Fearing-4 wrote > On 05/01/2020 16:01, legrand legrand wrote: > > > No, that is incorrect.  The standard syntax is: > > >     FROM tablename FOR SYSTEM_TIME AS OF '...' > >     FROM tablename FOR SYSTEM_TIME BETWEEN '...' AND '...&#x

Re: Implementing Incremental View Maintenance

Hello, It seems that patch v11 doesn't apply any more. Problem with "scanRTEForColumn" maybe because of change: https://git.postgresql.org/pg/commitdiff/b541e9accb28c90656388a3f827ca3a68dd2a308 Regards PAscal -- Sent from: https://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

pg13 PGDLLIMPORT list

Hello, would it be possible to add PGDLLIMPORT to permit to build following extensions on windows pg_stat_sql_plans: src/include/pgstat.h extern PGDLLIMPORT bool pgstat_track_activities; pg_background: src/include/storage/proc.h extern PGDLLIMPORT int StatementTimeout; Thanks in advance Regards

Re: pg13 PGDLLIMPORT list

Michael Paquier-2 wrote > On Fri, Jan 17, 2020 at 03:07:48PM -0700, legrand legrand wrote: > [...] > > No objections from me to add both to what's imported. Do you have a > specific use-case in mind for an extension on Windows? Just > wondering.. > -- > Michael

postgresql-13devel initDB Running in debug mode.

Hello, after building devel snapshot from 2020-01-17 with msys, initDB generates a lot of additional informations when launched: VERSION=13devel PGDATA=../data share_path=C:/msys64/usr/local/pgsql/share PGPATH=C:/msys64/usr/local/pgsql/bin POSTGRES_SUPERUSERNAME=lemoyp POSTGRES_BKI=C:/msys64/usr/l

Re: postgresql-13devel initDB Running in debug mode.

Tom Lane-2 wrote > legrand legrand < > legrand_legrand@ > > writes: >> after building devel snapshot from 2020-01-17 with msys, >> initDB generates a lot of additional informations when launched: >> [ debug output snipped ] >> Is that the expected behavio

Re: Planning counters in pg_stat_statements (using pgss_store)

Hi Julien, bot is still unhappy https://travis-ci.org/postgresql-cfbot/postgresql/builds/638701399 portalcmds.c: In function ‘PerformCursorOpen’: portalcmds.c:93:7: error: ‘queryString’ may be used uninitialized in this function [-Werror=maybe-uninitialized] plan = pg_plan_query(query, queryStr

RE: minimizing pg_stat_statements performance overhead

Hi, it seems that your patch is not readable. If you want it to be included in a commitfest, you should add it by yourself in https://commitfest.postgresql.org/ Not sure that there is any room left in pg12 commitfest. Regard PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-ha

Re: Planning counters in pg_stat_statements (using pgss_store)

Hi, I have played with this patch, it works fine. rem the last position of the "new" total_time column is confusing +CREATE VIEW pg_stat_statements AS + SELECT *, total_plan_time + total_exec_time AS total_time +FROM pg_stat_statements(true); I wanted to perform some benchmark between those

Re: DWIM mode for psql

Andreas Karlsson wrote > On 3/31/19 10:52 PM, Thomas Munro wrote:> Building on the excellent work > begun by commit e529cd4ffa60, I would >> like to propose a do-what-I-mean mode for psql. Please find a POC >> patch attached. It works like this: >> >> postgres=# select datnaam from pg_database

RE: Planning counters in pg_stat_statements (using pgss_store)

Hi, >> >> case avg_tps pct_diff >> 089 278 -- >> 188 745 0,6% >> 288 282 1,1% >> 386 660 2,9% >> >> This means that even in this extrem test case, the worst degradation is less >> than 3% >> (this overhead can be removed using pg_stat_statements.track_pla

Re: minimizing pg_stat_statements performance overhead

Robert Haas wrote > On Tue, Apr 2, 2019 at 5:37 AM Christoph Berg < > myon@ > > wrote: >> Re: Raymond Martin 2019-04-01 < > BN8PR21MB121708579A3782866DF1F745B1550@.outlook > > >> > Thanks again Fabien. I am attaching the patch to this email in the hope >> of getting it approved during the next

RE: minimizing pg_stat_statements performance overhead

CF entry created https://commitfest.postgresql.org/23/2092/ Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

Re: Logging the feature of SQL-level read/write commits

Hello, may be you can find more informations regarding WAL concepts in Write Ahead Logging — WAL http://www.interdb.jp/pg/pgsql09.html It seems very complicated to change WAL format ... Maybe there are other solutions to answer your need, I found many interesting solutions in postgres archives

RE: Re: Logging the feature of SQL-level read/write commits

Hi, good point ! wal2Json seems to correspond to your needs, this is first designed for Change Data Capture, taht could generate a (very) big size of logs. You didn't tell us much about your use case ... and maybe, if the number of data modifications is not too big, and the number of tables to

Re: How to install login_hook in Postgres 10.5

Hello, This extension https://github.com/splendiddata/login_hook seems very interesting ! But I didn't test it myself and maybe the best place to ask support is there https://github.com/splendiddata/login_hook/issues For information there is something equivalent in core "[PATCH] A hook for sessi

Re: [Proposal] Add accumulated statistics for wait event

Bertrand DROUVOT wrote > Hello Guys, > > As you mentioned Oracle like active session history sampling in this > thread, I just want to let you know that I am working on a brand new > extension to provide this feature. > > You can find the extension here: https://github.com/pgsentinel/pgsentinel >

Multiple Wait Events for extensions

Hello, I'm playing with adding into my pg_stat_statements extension a wait event for pgss time duration (pgss_store) Adding pgstat_report_wait_start(PG_WAIT_EXTENSION) gives wait type = "Extension" / event name "Extension" and that's perfect. Now I would like to add a second wait event (for exe

Re: Multiple Wait Events for extensions

Would a hard coded solution as described here after possible for mid-term ? note: actual result from pgstat_report_wait_star(PG_WAIT_EXTENSION); is preserved. Regards PAscal pgstat.h /* -- * Wait Events - Extension * * Use this category when an extension is waiting. * -

Re: Multiple Wait Events for extensions

Michael Paquier-2 wrote > On Wed, Oct 24, 2018 at 11:18:13AM -0700, legrand legrand wrote: >> Would a hard coded solution as described here after possible for >> mid-term? > > I don't think I would commit that as we would want a better solution > with custom names, b

Re: Multiple Wait Events for extensions

Michael Paquier-2 wrote > On Wed, Oct 24, 2018 at 11:18:13AM -0700, legrand legrand wrote: >> Would a hard coded solution as described here after possible for >> mid-term? > > I don't think I would commit that as we would want a better solution > with custom names, b

RE: [Proposal] Add accumulated statistics for wait event

Hello, You are right, sampling has to be "tuned" regarding the event(s) you want to catch. Sampling of 1 second interval is good with treatments that take hours, and not enough for a minute or a second analysis. May I invite you to try it, using PASH-viewer (github) with pgsentinel (github). Cha

Re: Delta Materialized View Refreshes?

denty wrote > (Seems I can't attach via the web interface, so copy/paste patch below.) > > -- > Sent from: > http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html Sending attachments from this web site (that is not an official postgres website) has been disabled as requested by postg

Re: proposal: simple query profile and tracing API

Hello Pavel, What about using wait events and a trigger on pg_stat_activity ? just : * create a functions to get current query signature (queryid) for a pid (not the top_level_query given for pl/pgsql blocks or triggers but the active one) * add some kind of active events to track planning (i

Re: proposal: simple query profile and tracing API

Pavel Stehule wrote > út 13. 11. 2018 v 20:38 odesílatel Tomas Vondra < > tomas.vondra@ >> napsal: > > My idea is very simple. > > 1. continual collect of data - planning start, execution start, waiting > start, waiting end, query end > > 2. run a some callback function after query is finished

Re: [PROPOSAL] extend the object names to the qualified names in pg_stat_statements

Alvaro Herrera-9 wrote > On 2018-Nov-28, Tom Lane wrote: > >> This would also entail rather significant overhead to find out schema >> names and interpolate them into the text. > > True. I was thinking that the qualified-names version of the query > would be obtained via ruleutils or some simila

Re: [PROPOSAL] timestamp informations to pg_stat_statements

+1 Having the time of first occurence of a statement is very usefull for trouble shouting, it permits for exemple to retrieve the order of operations in some complex cases (and thoses informations aren't taken by any third party collecting tool, that will only be able to provide a time range of occ

Re: All Taxi Services need Index Clustered Heap Append

Hello, Would the following custom solution: - a pre-loaded table rows being sorted by id and ts containing null values for other columns, enough free space per block to permit updates in place, - having a (btree or brin) index on (id,ts), - loaded using UPDATEs in spite of INSERTs

pg_stat_statements HLD for futur developments

Hello Kackers, As a new user of PostgreSQL, I have started using pg_stat_statements, and was pleased but a little surprised: First of all, the normalized form of the query string makes it impossible to be used in EXPLAIN commands. Second, normalized constants and parameters values where missing

RE: pg_stat_statements HLD for futur developments

09:32:13 À : legrand legrand Cc : pgsql-hack...@postgresql.org Objet : Re: pg_stat_statements HLD for futur developments Hello, > As a new user of PostgreSQL, I have started using pg_stat_statements, and > was pleased but a little surprised: > > First of all, the normalized form of

RE: Planning counters in pg_stat_statements (using pgss_store)

imai.yoshik...@fujitsu.com wrote > On Thu, Mar 12, 2020 at 6:37 PM, Julien Rouhaud wrote: >> On Thu, Mar 12, 2020 at 1:11 PM Marco Slot < > marco.slot@ > > wrote: >> > On Thu, Mar 12, 2020 at 11:31 AM Julien Rouhaud < > rjuju123@ > > >> wrote: >> > > There's at least the current version of IVM

RE: Planning counters in pg_stat_statements (using pgss_store)

> I don't know it is useful but there are also codes that avoid an error when > sourceText is NULL. > executor_errposition(EState *estate, int location) > { > ... >/* Can't do anything if source text is not available */ >if (estate == NULL || estate->es_sourceText == NULL) > } or may

Re: Planning counters in pg_stat_statements (using pgss_store)

> I'm instead attaching a v7 which removes the assert in pg_plan_query, and > modify pgss_planner_hook to also ignore queries without a query text, as > this > seems the best option. Ok, it was the second solution, go on ! -- Sent from: https://www.postgresql-archive.org/PostgreSQL-hackers-f19

Re: Allow auto_explain to log plans before queries are executed

Kyotaro Horiguchi-4 wrote > At Thu, 27 Feb 2020 06:27:24 +0100, Pavel Stehule < > pavel.stehule@ > > wrote in >> odesílatel Kyotaro Horiguchi < > horikyota.ntt@ > > >> napsal: > > If we need a live plan dump of a running query, We could do that using > some kind of inter-backend triggering. (

Re: Patch: to pass query string to pg_plan_query()

Tom Lane-2 wrote > Fujii Masao < > masao.fujii@.nttdata > > writes: >> Does anyone object to this patch? I'm thinking to commit it separetely >> at first before committing the planning_counter_in_pg_stat_statements >> patch. > > I took a quick look through v9-0001-Pass-query-string-to-the-planne

Re: Planning counters in pg_stat_statements (using pgss_store)

Fujii Masao-4 wrote > On 2020/04/01 18:19, Fujii Masao wrote: > > Finally I pushed the patch! > Many thanks for all involved in this patch! > > As a remaining TODO item, I'm thinking that the document would need to > be improved. For example, previously the query was not stored in pgss > when it

  1   2   >