Waiting for PostgreSQL 10 – hash indexing vs. WAL

For a long time hash indexed were not crash safe, and couldn't be used on replication slave, because they skipped WAL. Now, thanks to these two commits, it has changed:

On 14th of March 2017, Robert Haas committed patch:

hash: Add write-ahead logging support.
 
The warning about hash indexes not being write-ahead logged and their
use being discouraged has been removed.  "snapshot too old" is now
supported for tables with hash indexes.  Most importantly, barring
bugs, hash indexes will now be crash-safe and usable on standbys.
 
This commit doesn't yet add WAL consistency checking for hash
indexes, as we now have for other index types; a separate patch has
been submitted to cure that lack.
 
Amit Kapila, reviewed and slightly modified by me.  The larger patch
series of which this is a part has been reviewed and tested by Álvaro
Herrera, Ashutosh Sharma, Mark Kirkwood, Jeff Janes, and Jesper
Pedersen.
 
Discussion: http://postgr.es/m/CAA4eK1JOBX=YU33631Qh-XivYXtPSALh514+jR8XeD7v+K3r_Q@mail.gmail.com

and then, ~ 13 hours later, Robert committed also:

hash: Support WAL consistency checking.
 
Kuntal Ghosh, reviewed by Amit Kapila and Ashutosh Sharma, with
a few tweaks by me.
 
Discussion: http://postgr.es/m/CAGz5QCJLERUn_zoO0eDv6_Y_d0o4tNTMPeR7ivTLBg4rUrJdwg@mail.gmail.com

Continue reading Waiting for PostgreSQL 10 – hash indexing vs. WAL

Waiting for PostgreSQL 10 – Support XMLTABLE query expression

On 8th of March, Alvaro Herrera committed patch:

XMLTABLE is defined by the SQL/XML standard as a feature that allows
turning XML-formatted data into relational form, so that it can be used
as a <table primary> in the FROM clause of a query.
 
This new construct provides significant simplicity and performance
benefit for XML data processing; what in a client-side custom
implementation was reported to take 20 minutes can be executed in 400ms
using XMLTABLE.  (The same functionality was said to take 10 seconds
using nested PostgreSQL XPath function calls, and 5 seconds using
XMLReader under PL/Python).
 
The implemented syntax deviates slightly from what the standard
requires.  First, the standard indicates that the PASSING clause is
optional and that multiple XML input documents may be given to it; we
make it mandatory and accept a single document only.  Second, we don't
currently support a default namespace to be specified.
 
This implementation relies on a new executor node based on a hardcoded
method table.  (Because the grammar is fixed, there is no extensibility
in the current approach; further constructs can be implemented on top of
this such as JSON_TABLE, but they require changes to core code.)
 
Author: Pavel Stehule, Álvaro Herrera
Extensively reviewed by: Craig Ringer
Discussion: https://postgr.es/m/CAFj8pRAgfzMD-LoSmnMGybD0WsEznLHWap8DO79+-GTRAPR4qA@mail.gmail.com

Continue reading Waiting for PostgreSQL 10 – Support XMLTABLE query expression

Waiting for PostgreSQL 10 – psql: Add \gx command

On 7th of March, Stephen Frost committed patch:

psql: Add \gx command
 
It can often be useful to use expanded mode output (\x) for just a
single query.  Introduce a \gx which acts exactly like \g except that it
will force expanded output mode for that one \gx call.  This is simpler
than having to use \x as a toggle and also means that the user doesn't
have to worry about the current state of the expanded variable, or
resetting it later, to ensure a given query is always returned in
expanded mode.
 
Primairly Christoph's patch, though I did tweak the documentation and help
text a bit, and re-indented the tab completion section.
 
Author: Christoph Berg
Reviewed By: Daniel Verite
Discussion: <a href="https://postgr.es/m/20170127132737.6skslelaf4txs6iw%40msg.credativ.de">https://www.postgresql.org/message-id/20170127132737.6skslelaf4txs6iw%40msg.credativ.de

Continue reading Waiting for PostgreSQL 10 – psql: Add \gx command

Waiting for PostgreSQL 10 – Logical replication

On 20th of January, Peter Eisentraut committed patch:

Logical replication
 
- Add PUBLICATION catalogs and DDL
- Add SUBSCRIPTION catalog and DDL
- Define logical replication protocol and output plugin
- Add logical replication workers
 
From: Petr Jelinek
Reviewed-by: Steve Singer
Reviewed-by: Andres Freund
Reviewed-by: Erik Rijkers
Reviewed-by: Peter Eisentraut

Continue reading Waiting for PostgreSQL 10 – Logical replication

Waiting for PostgreSQL 10 – Add pg_sequence system catalog

On 20th of December, Peter Eisentraut committed patch:

Add pg_sequence system catalog
 
Move sequence metadata (start, increment, etc.) into a proper system
catalog instead of storing it in the sequence heap object.  This
separates the metadata from the sequence data.  Sequence metadata is now
operated on transactionally by DDL commands, whereas previously
rollbacks of sequence-related DDL commands would be ignored.
 
Reviewed-by: Andreas Karlsson

Continue reading Waiting for PostgreSQL 10 – Add pg_sequence system catalog

Waiting for PostgreSQL 10 – Implement table partitioning.

I had two month delay related to some work, but now I can finally write about:

On 7th of December, Robert Haas committed patch:

Implement table partitioning.
 
Table partitioning is like table inheritance and reuses much of the
existing infrastructure, but there are some important differences.
The parent is called a partitioned table and is always empty; it may
not have indexes or non-inherited constraints, since those make no
sense for a relation with no data of its own.  The children are called
partitions and contain all of the actual data.  Each partition has an
implicit partitioning constraint.  Multiple inheritance is not
allowed, and partitioning and inheritance can't be mixed.  Partitions
can't have extra columns and may not allow nulls unless the parent
does.  Tuples inserted into the parent are automatically routed to the
correct partition, so tuple-routing ON INSERT triggers are not needed.
Tuple routing isn't yet supported for partitions which are foreign
tables, and it doesn't handle updates that cross partition boundaries.
 
Currently, tables can be range-partitioned or list-partitioned.  List
partitioning is limited to a single column, but range partitioning can
involve multiple columns.  A partitioning "column" can be an
expression.
 
Because table partitioning is less general than table inheritance, it
is hoped that it will be easier to reason about properties of
partitions, and therefore that this will serve as a better foundation
for a variety of possible optimizations, including query planner
optimizations.  The tuple routing based which this patch does based on
the implicit partitioning constraints is an example of this, but it
seems likely that many other useful optimizations are also possible.
 
Amit Langote, reviewed and tested by Robert Haas, Ashutosh Bapat,
Amit Kapila, Rajkumar Raghuwanshi, Corey Huinker, Jaime Casanova,
Rushabh Lathia, Erik Rijkers, among others.  Minor revisions by me.

Continue reading Waiting for PostgreSQL 10 – Implement table partitioning.

Waiting for PostgreSQL 10 – postgres_fdw: Push down aggregates to remote servers.

On 21st of October, Robert Haas committed patch:

postgres_fdw: Push down aggregates to remote servers.
 
Now that the upper planner uses paths, and now that we have proper hooks
to inject paths into the upper planning process, it's possible for
foreign data wrappers to arrange to push aggregates to the remote side
instead of fetching all of the rows and aggregating them locally.  This
figures to be a massive win for performance, so teach postgres_fdw to
do it.
 
Jeevan Chalke and Ashutosh Bapat.  Reviewed by Ashutosh Bapat with
additional testing by Prabhat Sahu.  Various mostly cosmetic changes
by me.

Continue reading Waiting for PostgreSQL 10 – postgres_fdw: Push down aggregates to remote servers.

Waiting for PostgreSQL 10 – Rename “pg_xlog” directory to “pg_wal”.

On 20th of October, Robert Haas committed patch:

Rename "pg_xlog" directory to "pg_wal".
 
"xlog" is not a particularly clear abbreviation for "write-ahead log",
and it sometimes confuses users into believe that the contents of the
"pg_xlog" directory are not critical data, leading to unpleasant
consequences.  So, rename the directory to "pg_wal".
 
This patch modifies pg_upgrade and pg_basebackup to understand both
the old and new directory layouts; the former is necessary given the
purpose of the tool, while the latter merely avoids an unnecessary
backward-compatibility break.
 
We may wish to consider renaming other programs, switches, and
functions which still use the old "xlog" naming to also refer to
"wal".  However, that's still under discussion, so let's do just this
much for now.
 
Discussion: CAB7nPqTeC-8+zux8_-4ZD46V7YPwooeFxgndfsq5Rg8ibLVm1A@mail.gmail.com
 
Michael Paquier

discussion link

Continue reading Waiting for PostgreSQL 10 – Rename “pg_xlog" directory to “pg_wal".

Waiting for 9.6 – Support \crosstabview in psql

On 8th of April, Alvaro Herrera committed patch:

Support \crosstabview in psql
 
\crosstabview is a completely different way to display results from a
query: instead of a vertical display of rows, the data values are placed
in a grid where the column and row headers come from the data itself,
similar to a spreadsheet.
 
The sort order of the horizontal header can be specified by using
another column in the query, and the vertical header determines its
ordering from the order in which they appear in the query.
 
This only allows displaying a single value in each cell.  If more than
one value correspond to the same cell, an error is thrown.  Merging of
values can be done in the query itself, if necessary.  This may be
revisited in the future.
 
Author: Daniel Verité
<span class="signoff">Reviewed-by: Pavel Stehule, Dean Rasheed</span>

Continue reading Waiting for 9.6 – Support \crosstabview in psql

Waiting for 9.6 – Phrase full text search.

On 7th of April, Teodor Sigaev committed patch:

Phrase full text search.
 
Patch introduces new text search operator (<-> or <DISTANCE>) into tsquery.
On-disk and binary in/out format of tsquery are backward compatible.
It has two side effect:
- change order for tsquery, so, users, who has a btree index over tsquery,
  should reindex it
- less number of parenthesis in tsquery output, and tsquery becomes more
  readable
 
Authors: Teodor Sigaev, Oleg Bartunov, Dmitry Ivanov
Reviewers: Alexander Korotkov, Artur Zakirov

Continue reading Waiting for 9.6 – Phrase full text search.