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".

Missing waiting for …. ?

It looks that for some reason my subscription to pgsql-committers disappeared, and I stopped receiving mails from this list.

I also didn't notice it, for some time – not sure when it got lost.

I did resubscribed now, but if you can think of any specific commits that I should have written about, but haven't, please let me know, and I'll write about it. Sorry for the problem.

Getting list of unique elements in table, per group

Today, on irc, someone asked interesting question.

Basically she ran a query like:

select a, b, c, d, e, f from table order by a

then, she processed the query to get, for each a array of unique values of b, c, d, e, and f, and then he inserted it back to database, to some other table.

It was a problem, because the table had many rows (millions I would assume), and the whole process was slow.

So, how to make it faster?

Continue reading Getting list of unique elements in table, per group

The hard math of killing zombies in Zombicide

I happen to play Zombicide. It's cooperative board game set in some kind of post-apocalyptic, zombie-infested, world.

Despite the general theme, lots of people like it, so we play it quite a lot.

One thing that bugged us, was : in given scenario, which weapon it's best to use?

Continue reading The hard math of killing zombies in Zombicide

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.