Month: March 2017

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