Picking task from queue – revisit

Some time ago, I wrote blogpost about how to pick a task from queue, without locking.

It was written in 2013, and as such it couldn't reflect everything we have now in PostgreSQL – namely SKIP LOCKED – which was added to PostgreSQL over year later.

Two people mentioned SKIP LOCKED in comments, but it was before it was committed even to git repo. But now, we have, officially released, PostgreSQL version with this mechanism, so let's see what it can do.

Continue reading Picking task from queue – revisit

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.

Waiting for 9.6 – Add a \gexec command to psql for evaluation of computed queries.

On 4th of April, Tom Lane committed patch:

Add a \gexec command to psql for evaluation of computed queries.
 
\gexec executes the just-entered query, like \g, but instead of printing
the results it takes each field as a SQL command to send to the server.
Computing a series of queries to be executed is a fairly common thing,
but up to now you always had to resort to kluges like writing the queries
to a file and then inputting the file.  Now it can be done with no
intermediate step.
 
The implementation is fairly straightforward except for its interaction
with FETCH_COUNT.  ExecQueryUsingCursor isn't capable of being called
recursively, and even if it were, its need to create a transaction
block interferes unpleasantly with the desired behavior of \gexec after
a failure of a generated query (i.e., that it can continue).  Therefore,
disable use of ExecQueryUsingCursor when doing the master \gexec query.
We can still apply it to individual generated queries, however, and there
might be some value in doing so.
 
While testing this feature's interaction with single-step mode, I (tgl) was
led to conclude that SendQuery needs to recognize SIGINT (cancel_pressed)
as a negative response to the single-step prompt.  Perhaps that's a
back-patchable bug fix, but for now I just included it here.
 
Corey Huinker, reviewed by Jim Nasby, Daniel Vérité, and myself

Continue reading Waiting for 9.6 – Add a \gexec command to psql for evaluation of computed queries.

Waiting for 9.6 – Bloom index contrib module

On 1st of April, Teodor Sigaev committed patch:

Bloom index contrib module
 
Module provides new access method. It is actually a simple Bloom filter
implemented as pgsql's index. It could give some benefits on search
with large number of columns.
 
Module is a single way to test generic WAL interface committed earlier.
 
Author: Teodor Sigaev, Alexander Korotkov
Reviewers: Aleksander Alekseev, Michael Paquier, Jim Nasby

Continue reading Waiting for 9.6 – Bloom index contrib module

Change on explain.depesz.com

As of now, main table that stores explain.depesz.com plans is partitioned.

This shouldn't be, at all, visible for users of the site, but if it would, please let me know (on irc, or via email).

In case you're wondering why, after all there is only ~ 270,000 plans – the reason is very simple. Splitting the data into multiple tables makes maintenance tasks (vacuum, dump) much simpler and easier.

Waiting for 9.6 – Support parallel aggregation.

On 21st of March, Robert Haas committed patch:

Support parallel aggregation.
 
Parallel workers can now partially aggregate the data and pass the
transition values back to the leader, which can combine the partial
results to produce the final answer.
 
David Rowley, based on earlier work by Haribabu Kommi.  Reviewed by
Álvaro Herrera, Tomas Vondra, Amit Kapila, James Sewell, and me.

Continue reading Waiting for 9.6 – Support parallel aggregation.

Waiting for 9.6 – Directly modify foreign tables.

On 18th of March, Robert Haas committed patch:

Directly modify foreign tables.
 
postgres_fdw can now sent an UPDATE or DELETE statement directly to
the foreign server in simple cases, rather than sending a SELECT FOR
UPDATE statement and then updating or deleting rows one-by-one.
 
Etsuro Fujita, reviewed by Rushabh Lathia, Shigeru Hanada, Kyotaro
Horiguchi, Albe Laurenz, Thom Brown, and me.

Continue reading Waiting for 9.6 – Directly modify foreign tables.