Author: depesz

Waiting for PostgreSQL 10 – Identity columns

On 6th of April 2017, Peter Eisentraut committed patch:

Identity columns
 
This is the SQL standard-conforming variant of PostgreSQL's serial
columns.  It fixes a few usability issues that serial columns have:
 
- CREATE TABLE / LIKE copies default but refers to same sequence
- cannot add/drop serialness with ALTER TABLE
- dropping default does not drop sequence
- need to grant separate privileges to sequence
- other slight weirdnesses because serial is some kind of special macro

Continue reading Waiting for PostgreSQL 10 – Identity columns

Waiting for PostgreSQL 10 – Full Text Search support for json and jsonb

On 31st of March 2017, Andrew Dunstan committed patch:

Full Text Search support for json and jsonb
 
The new functions are ts_headline() and to_tsvector.
 
Dmitry Dolgov, edited and documented by me.

Continue reading Waiting for PostgreSQL 10 – Full Text Search support for json and jsonb

Waiting for PostgreSQL 10 – Support \if … \elif … \else … \endif in psql scripting.

On 30th of March 2017, Tom Lane committed patch:

Support \if ... \elif ... \else ... \endif in psql scripting.
 
This patch adds nestable conditional blocks to psql.  The control
structure feature per se is complete, but the boolean expressions
understood by \if and \elif are pretty primitive; basically, after
variable substitution and backtick expansion, the result has to be
"true" or "false" or one of the other standard spellings of a boolean
value.  But that's enough for many purposes, since you can always
do the heavy lifting on the server side; and we can extend it later.
 
Along the way, pay down some of the technical debt that had built up
around psql/command.c:
* Refactor exec_command() into a function per command, instead of
being a 1500-line monstrosity.  This makes the file noticeably longer
because of repetitive function header/trailer overhead, but it seems
much more readable.
* Teach psql_get_variable() and psqlscanslash.l to suppress variable
substitution and backtick expansion on the basis of the conditional
stack state, thereby allowing removal of the OT_NO_EVAL kluge.
* Fix the no-doubt-once-expedient hack of sometimes silently substituting
mainloop.c's previous_buf for query_buf when calling HandleSlashCmds.
(It's a bit remarkable that commands like \r worked at all with that.)
Recall of a previous query is now done explicitly in the slash commands
where that should happen.
 
Corey Huinker, reviewed by Fabien Coelho, further hacking by me
 
Discussion: https://postgr.es/m/CADkLM=c94OSRTnat=LX0ivNq4pxDNeoomFfYvBKM5N_xfmLtAA@mail.gmail.com

Continue reading Waiting for PostgreSQL 10 – Support \if … \elif … \else … \endif in psql scripting.

Waiting for PostgreSQL 10 – Logical replication support for initial data copy

On 23rd of March 2017, Peter Eisentraut committed patch:

Logical replication support for initial data copy
 
Add functionality for a new subscription to copy the initial data in the
tables and then sync with the ongoing apply process.
 
For the copying, add a new internal COPY option to have the COPY source
data provided by a callback function.  The initial data copy works on
the subscriber by receiving COPY data from the publisher and then
providing it locally into a COPY that writes to the destination table.
 
A WAL receiver can now execute full SQL commands.  This is used here to
obtain information about tables and publications.
 
Several new options were added to CREATE and ALTER SUBSCRIPTION to
control whether and when initial table syncing happens.
 
Change pg_dump option --no-create-subscription-slots to
--no-subscription-connect and use the new CREATE SUBSCRIPTION
... NOCONNECT option for that.
 
Author: Petr Jelinek

Continue reading Waiting for PostgreSQL 10 – Logical replication support for initial data copy

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

A way to share SQL queries – paste.depesz.com

Long time ago I wrote first version of explain.depesz.com. Since then I gradually improve it. But, what was lacking was a way to paste queries too – explain.depesz.com handles explains, but not plain queries.

Now this has changed. I created new site: paste.depesz.com which allows for sharing queries.

Thanks to pgFormatter it also does query pretty-printing (which is not something readily available on other paste sites).

Obviously, code to the site is publicly available in GitHub repo.

Now, goes my request – if you have designer skills, I would greatly appreciate someone that could make the site nicer (prettier, more responsive). My CSS/JS knowledge is pretty limited, and I'm happy anyway about what I did with the look right now, but if you could make it nicer/prettier, that would be amazing.

Have fun, and if you have any feature requests, please post them in here…

Getting first and last values per group

Every so often someone needs solution to getting first (or couple of first) values for given column. Or last.

For some cases (when there is not many groups) you can use recursive queries. But it's not always the best choice.

Let's try to implement first() and last() aggregates, so these could be easily used by anybody.

Continue reading Getting first and last values per group

Why does “sudo ls -l /proc/1/fd/*” fail?

I usually write about PostgreSQL, but lately someone asked for help, and one of the problems was similar to sudo command from title.

This was not the first time I saw it, so figured, I'll write a blogpost about it, just so I can refer people to it in the future.

Continue reading Why does “sudo ls -l /proc/1/fd/*" fail?