Tag: postgresql

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

New Pg::Explain and explain.depesz.com

Just now pushed new version of Pg::Explain Perl library that is handling parsing for explain.depesz.com.

There have been many changes, but the short summary is:

  • Fix display of heap fetches from json (https://gitlab.com/depesz/explain.depesz.com/issues/15)
  • Move global data (planning/execution/total time, trigger info) from top_node to explain object itself
  • Add method to easily get real total, wall-clock, runtime of query.
  • Add Pg::Explain::Analyzer, so far it can extract node types and their “paths"
  • Add scripts to run perltidy, start development tmux env, and explain schema dumper
  • Fix handling of parallel queries (hopefully)
  • Remove edge-case deep recursion error
  • Speed optimization for large explains

Hopefully it will not break anything for you 🙂

And as a final note: explain.depesz.com has been used to add almost 740kplans (out of which 120k were deleted). Currently database contains over 311k public (visible in history), not deleted, plans.

Waiting for PostgreSQL 13 – pgbench: add –partitions and –partition-method options.

On 3rd of October 2019, Amit Kapila committed patch:

pgbench: add --partitions and --partition-method options.
 
These new options allow users to partition the pgbench_accounts table by
specifying the number of partitions and partitioning method.  The values
allowed for partitioning method are range and hash.
 
This feature allows users to measure the overhead of partitioning if any.
 
Author: Fabien COELHO
 
Alvaro Herrera
Discussion: https://postgr.es/m/alpine.DEB.2.21..7008@lancre

Continue reading Waiting for PostgreSQL 13 – pgbench: add –partitions and –partition-method options.

How to run short ALTER TABLE without long locking concurrent queries

Recently I've seen case like:

  1. application had to add column to table.
  2. application ran ALTER TABLE ADD COLUMN (without default!)
  3. everything stopped for many MINUTES

Why? How to avoid the problem?

Continue reading How to run short ALTER TABLE without long locking concurrent queries

Waiting for PostgreSQL 13 – Support for FF1-FF6 and SSSS datetime format patterns

On 16th of September 2019, Alexander Korotkov committed two patches:

First patch:

Support for SSSSS datetime format pattern
 
SQL Standard 2016 defines SSSSS format pattern for seconds past midnight in
jsonpath .datetime() method and CAST (... FORMAT ...) SQL clause.  In our
datetime parsing engine we currently support it with SSSS name.
 
This commit adds SSSSS as an alias for SSSS.  Alias is added in favor of
upcoming jsonpath .datetime() method.  But it's also supported in to_date()/
to_timestamp() as positive side effect.
 
Discussion: https://postgr.es/m/CAPpHfdsZgYEra_PeCLGNoXOWYx6iU-S3wF8aX0ObQUcZU%2B4XTw%40mail.gmail.com
Author: Nikita Glukhov, Alexander Korotkov

and second one:

Support for FF1-FF6 datetime format patterns
 
SQL Standard 2016 defines FF1-FF9 format patters for fractions of seconds in
jsonpath .datetime() method and CAST (... FORMAT ...) SQL clause.  Parsing
engine of upcoming .datetime() method will be shared with to_date()/
to_timestamp().
 
This patch implements FF1-FF6 format patterns for upcoming jsonpath .datetime()
method.  to_date()/to_timestamp() functions will also get support of this
format patterns as positive side effect.  FF7-FF9 are not supported due to
lack of precision in our internal timestamp representation.
 
Extracted from original patch by Nikita Glukhov, Teodor Sigaev, Oleg Bartunov.
Heavily revised by me.
 
Discussion: https://postgr.es/m/-b497-f39a-923d-%402ndQuadrant.com
Discussion: https://postgr.es/m/CAPpHfdsZgYEra_PeCLGNoXOWYx6iU-S3wF8aX0ObQUcZU%2B4XTw%40mail.gmail.com
Author: Nikita Glukhov, Teodor Sigaev, Oleg Bartunov, Alexander Korotkov

Continue reading Waiting for PostgreSQL 13 – Support for FF1-FF6 and SSSS datetime format patterns

Waiting for PostgreSQL 13 – Add support for –jobs in reindexdb

On 27th of July 2019, Michael Paquier committed patch:

Add support for --jobs in reindexdb
 
When doing a schema-level or a database-level operation, a list of
relations to build is created which gets processed in parallel using
multiple connections, based on the recent refactoring for parallel slots
in src/bin/scripts/.  System catalogs are processed first in a
serialized fashion to prevent deadlocks, followed by the rest done in
parallel.
 
This new option is not compatible with --system as reindexing system
catalogs in parallel can lead to deadlocks, and with --index as there is
no conflict handling for indexes rebuilt in parallel depending in the
same relation.
 
Author: Julien Rouhaud
 
Discussion: https://postgr.es/m/CAOBaU_YrnH_Jqo46NhaJ7uRBiWWEcS40VNRQxgFbqYo9kApUsg@mail.gmail.com

Continue reading Waiting for PostgreSQL 13 – Add support for –jobs in reindexdb