Waiting for PostgreSQL 13 – Allow pg_stat_statements to track planning statistics.

On 2nd of April 2020, Fujii Masao committed patch:

Allow pg_stat_statements to track planning statistics.
 
This commit makes pg_stat_statements support new GUC
pg_stat_statements.track_planning. If this option is enabled,
pg_stat_statements tracks the planning statistics of the statements,
e.g., the number of times the statement was planned, the total time
spent planning the statement, etc. This feature is useful to check
the statements that it takes a long time to plan. Previously since
pg_stat_statements tracked only the execution statistics, we could
not use that for the purpose.
 
The planning and execution statistics are stored at the end of
each phase separately. So there are not always one-to-one relationship
between them. For example, if the statement is successfully planned
but fails in the execution phase, only its planning statistics are stored.
This may cause the users to be able to see different pg_stat_statements
results from the previous version. To avoid this,
pg_stat_statements.track_planning needs to be disabled.
 
This commit bumps the version of pg_stat_statements to 1.8
since it changes the definition of pg_stat_statements function.
 
Author: Julien Rouhaud, Pascal Legrand, Thomas Munro, Fujii Masao
Reviewed-by: Sergei Kornilov, Tomas Vondra, Yoshikazu Imai, Haribabu Kommi, Tom Lane
Discussion: https://postgr.es/m/CAHGQGwFx_=DO-Gu-MfPW3VQ4qC7TfVdH2zHmvZfrGv6fQ3D-Tw@mail.gmail.com
Discussion: https://postgr.es/m/CAEepm=0e59Y_6Q_YXYCTHZkqOc6H2pJ54C_Xe=VFu50Aqqp_sA@mail.gmail.com
Discussion: https://postgr.es/m/DB6PR0301MB21352F6210E3B11934B0DCC790B00@DB6PR0301MB2135.eurprd03.prod.outlook.com

Continue reading Waiting for PostgreSQL 13 – Allow pg_stat_statements to track planning statistics.

Waiting for PostgreSQL 13 – Enable BEFORE row-level triggers for partitioned tables

On 18th of March 2020, Alvaro Herrera committed patch:

Enable BEFORE row-level triggers for partitioned tables
 
... with the limitation that the tuple must remain in the same
partition.
 
Reviewed-by: Ashutosh Bapat
Discussion: https://postgr.es/m/20200227165158.GA2071@alvherre.pgsql

Continue reading Waiting for PostgreSQL 13 – Enable BEFORE row-level triggers for partitioned tables

Waiting for PostgreSQL 13 – Add %x to default PROMPT1 and PROMPT2 in psql

On 12nd of February 2020, Michael Paquier committed patch:

Add %x to default PROMPT1 and PROMPT2 in psql
 
%d can be used to track if the current connection is in a transaction
block or not, and adding it by default to the prompt has the advantage
to not need a modification of .psqlrc, something not possible depending
on the environment.
 
This discussion has happened across various sources, and there was a
strong consensus in favor of this change.
 
Author: Vik Fearing
Reviewed-by: Fabien Coelho
Discussion: https://postgr.es/m/09502c40-cfe1-bb29-10f9-4b3fa7b2bbb2@2ndquadrant.com

Continue reading Waiting for PostgreSQL 13 – Add %x to default PROMPT1 and PROMPT2 in psql

Waiting for PostgreSQL 13 – Add leader_pid to pg_stat_activity

On 6th of February 2020, Michael Paquier committed patch:

Add leader_pid to pg_stat_activity
 
This new field tracks the PID of the group leader used with parallel
query.  For parallel workers and the leader, the value is set to the
PID of the group leader.  So, for the group leader, the value is the
same as its own PID.  Note that this reflects what PGPROC stores in
shared memory, so as leader_pid is NULL if a backend has never been
involved in parallel query.  If the backend is using parallel query or
has used it at least once, the value is set until the backend exits.
 
Author: Julien Rouhaud
Reviewed-by: Sergei Kornilov, Guillaume Lelarge, Michael Paquier, Tomas
Vondra
Discussion: https://postgr.es/m/CAOBaU_Yy5bt0vTPZ2_LUM6cUcGeqmYNoJ8-Rgto+c2+w3defYA@mail.gmail.com

Continue reading Waiting for PostgreSQL 13 – Add leader_pid to pg_stat_activity

Waiting for PostgreSQL 13 – Add functions gcd() and lcm() for integer and numeric types.

On 25th of January 2020, Dean Rasheed committed patch:

Add functions gcd() and lcm() for integer and numeric types.
 
These compute the greatest common divisor and least common multiple of
a pair of numbers using the Euclidean algorithm.
 
Vik Fearing, reviewed by Fabien Coelho.
 
Discussion: https://postgr.es/m/adbd3e0b-e3f1-5bbc-21db-03caf1cef0f7@2ndquadrant.com

Continue reading Waiting for PostgreSQL 13 – Add functions gcd() and lcm() for integer and numeric types.

Waiting for PostgreSQL 13 – Allow vacuum command to process indexes in parallel.

On 20th of January 2020, Amit Kapila committed patch:

Allow vacuum command to process indexes in parallel.
 
This feature allows the vacuum to leverage multiple CPUs in order to
process indexes.  This enables us to perform index vacuuming and index
cleanup with background workers.  This adds a PARALLEL option to VACUUM
command where the user can specify the number of workers that can be used
to perform the command which is limited by the number of indexes on a
table.  Specifying zero as a number of workers will disable parallelism.
This option can't be used with the FULL option.
 
Each index is processed by at most one vacuum process.  Therefore parallel
vacuum can be used when the table has at least two indexes.
 
The parallel degree is either specified by the user or determined based on
the number of indexes that the table has, and further limited by
max_parallel_maintenance_workers.  The index can participate in parallel
vacuum iff it's size is greater than min_parallel_index_scan_size.
 
Author: Masahiko Sawada and Amit Kapila
 
Mahendra Singh and Sergei Kornilov
 
Discussion:
https://postgr.es/m/CAD21AoDTPMgzSkV4E3SFo1CH_x50bf5PqZFQf4jmqjk-C03BWg@mail.gmail.com
https://postgr.es/m/CAA4eK1J-VoR9gzS5E75pcD-OH0mEyCdp8RihcwKrcuw7J-Q0+w@mail.gmail.com

Continue reading Waiting for PostgreSQL 13 – Allow vacuum command to process indexes in parallel.

Waiting for PostgreSQL 13 – ALTER TABLE … ALTER COLUMN … DROP EXPRESSION

Title: Waiting for PostgreSQL 13 – ALTER TABLE … ALTER COLUMN … DROP EXPRESSION

On 14th of January 2020, Peter Eisentraut committed patch:

ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION 
 
Add an ALTER TABLE subcommand for dropping the generated property from
a column, per SQL standard.
 
Discussion: https://www.postgresql.org/message-id/flat/-946e-0453-d841-%402ndquadrant.com

Continue reading Waiting for PostgreSQL 13 – ALTER TABLE … ALTER COLUMN … DROP EXPRESSION

Waiting for PostgreSQL 13 – Allow invisible PROMPT2 in psql.

On 19th of November 2019, Thomas Munro committed patch:

Allow invisible PROMPT2 in psql. 
 
Keep track of the visible width of PROMPT1, and provide %w as a way
for PROMPT2 to generate the same number of spaces.
 
Author: Thomas Munro, with ideas from others
 
Discussion: https://postgr.es/m/CA%2BhUKG%2BzGd7RigjWbxwhzGW59gUpf76ydQECeGdEdodH6nd__A%40mail.gmail.com

Continue reading Waiting for PostgreSQL 13 – Allow invisible PROMPT2 in psql.

Waiting for PostgreSQL 13 – Introduce the ‘force’ option for the Drop Database command.

On 13rd of November 2019, Amit Kapila committed patch:

Introduce the 'force' option for the Drop Database command.
 
 
This new option terminates the other sessions connected to the target
database and then drop it.  To terminate other sessions, the current user
must have desired permissions (same as pg_terminate_backend()).  We don't
allow to terminate the sessions if prepared transactions, active logical
replication slots or subscriptions are present in the target database.
 
Author: Pavel Stehule with changes by me
 
Ryan Lambert and Amit Kapila
Discussion: https://postgr.es/m/CAP_rwwmLJJbn70vLOZFpxGw3XD7nLB_7+NKz46H5EOO2k5H7OQ@mail.gmail.com

Continue reading Waiting for PostgreSQL 13 – Introduce the ‘force' option for the Drop Database command.

Waiting for PostgreSQL 13 – Allow sampling of statements depending on duration

On 6th of November 2019, Tomas Vondra committed patch:

Allow sampling of statements depending on duration
 
This allows logging a sample of statements, without incurring excessive
log traffic (which may impact performance).  This can be useful when
analyzing workloads with lots of short queries.
 
The sampling is configured using two new GUC parameters:
 
 * log_min_duration_sample - minimum required statement duration
 
 * log_statement_sample_rate - sample rate (0.0 - 1.0)
 
Only statements with duration exceeding log_min_duration_sample are
considered for sampling. To enable sampling, both those GUCs have to
be set correctly.
 
The existing log_min_duration_statement GUC has a higher priority, i.e.
statements with duration exceeding log_min_duration_statement will be
always logged, irrespectedly of how the sampling is configured. This
means only configurations
 
  log_min_duration_sample < log_min_duration_statement
 
do actually sample the statements, instead of logging everything.
 
Author: Adrien Nayrat
 
Discussion: https://postgr.es/m/-a8f7-3be2-155a-@anayrat.info

Continue reading Waiting for PostgreSQL 13 – Allow sampling of statements depending on duration