Waiting for 9.4 – New json functions.

On 28th of January, Andrew Dunstan committed patch:

New json functions.
 
json_build_array() and json_build_object allow for the construction of
arbitrarily complex json trees. json_object() turns a one or two
dimensional array, or two separate arrays, into a json_object of
name/value pairs, similarly to the hstore() function.
json_object_agg() aggregates its two arguments into a single json object
as name value pairs.
 
Catalog version bumped.
 
Andrew Dunstan, reviewed by Marko Tiikkaja.

Continue reading Waiting for 9.4 – New json functions.

Getting count of distinct elements, per group, in PostgreSQL.

So, couple of days ago, some guy, from Periscope company wrote a blogpost about getting number of distinct elements, per group, faster using subqueries.

This was then submitted to Hacker News and r/Programming on Reddit.

Then, the original authors submitted second blogpost comparing speed between four different DB engines. Which, in turn, was also commented on Reddit.

I found the numbers presented by Periscope (as their improvement) as not that great.

Unfortunately – their blog doesn't allow for comments, so I decided to test it, and write on my own blog, what I can find about it.

Continue reading Getting count of distinct elements, per group, in PostgreSQL.

Waiting for 9.4 – Add pg_stat_archiver statistics view.

On 28th of January, Fujii Masao committed patch:

Add pg_stat_archiver statistics view.
 
This view shows the statistics about the WAL archiver process's activity.
 
Gabriele Bartolini, reviewed by Michael Paquier, refactored a bit by me.

Continue reading Waiting for 9.4 – Add pg_stat_archiver statistics view.

Waiting for 9.4 – Add recovery_target=’immediate’ option.

On 25th of January, Heikki Linnakangas committed patch:

Add recovery_target='immediate' option.
 
This allows ending recovery as a consistent state has been reached. Without
this, there was no easy way to e.g restore an online backup, without
replaying any extra WAL after the backup ended.
 
MauMau and me.

Continue reading Waiting for 9.4 – Add recovery_target='immediate' option.

Waiting for 9.4 – Tablespace changes

On 18th of January, Stephen Frost committed patch:

Add ALTER TABLESPACE ... MOVE command
 
This adds a 'MOVE' sub-command to ALTER TABLESPACE which allows moving sets of
objects from one tablespace to another.  This can be extremely handy and avoids
a lot of error-prone scripting.  ALTER TABLESPACE ... MOVE will only move
objects the user owns, will notify the user if no objects were found, and can
be used to move ALL objects or specific types of objects (TABLES, INDEXES, or
MATERIALIZED VIEWS).

He followed it, with another commit:

Add CREATE TABLESPACE ... WITH ... Options
 
Tablespaces have a few options which can be set on them to give PG hints
as to how the tablespace behaves (perhaps it's faster for sequential
scans, or better able to handle random access, etc).  These options were
only available through the ALTER TABLESPACE command.
 
This adds the ability to set these options at CREATE TABLESPACE time,
removing the need to do both a CREATE TABLESPACE and ALTER TABLESPACE to
get the correct options set on the tablespace.
 
Vik Fearing, reviewed by Michael Paquier.

Continue reading Waiting for 9.4 – Tablespace changes

I did my part :)

I am enthusiast of Bitcoins. This can be seen in my blog (above each post, in post view page, there is help plea), or on explain.depesz.com – my most known creation.

Aside from these, I try to talk with various people about it, explain what I can (and learn what I don't know), and generally provide positive (but hopefully not overly intrusive) channel of information.

The thing, though, was that so far I didn't actually use much of the currency. Bought test vpn once, and that's about it.

But recently, I got 0.07 btc donations for my depesz.com blogposts (thanks to whoever contributed), and since I learned that relatively close to me there is burger bar that accepts payments in bitcoins – decided to spend some of donation money 🙂

Long story short – been today to White Burger with my S.O., and we got ourselves burgers.

The place is relatively nice. In Warsaw/Ursynow. At the time we were there (around 1pm) it was empty. Rather comfortable, though not cozy. But – it's a bar, not a secluded restaurant, so it's ok.

Prices – rather good. Been in couple of other burger bars in Warsaw, and this was below the average (if memory serves right).

Food – taste was great. I think these are in fact best burgers I had in Warsaw. Like them much more than the famous “Burger Bar" on Olkuska. There were two small(ish) issues, though. One – two (out of three) burgers had a bit cold buns. Not a big problem, but still easily fixable. The other problem was that kitchen mixed up sauces in the burgers. My wife got her burger, with bacon, but with my sauce – tabasco with some additions. This could have been much more problematic, but luckily she managed to eat it. The worst part, but easily avoidable, was coffee – if the owner of the place reads it – please check/fix the coffee machine.

Now for the interesting part. Bitcoin payment. It worked. Cashier (very nice, young lady) didn't know what to do when transaction was showed as “unconfirmed" (it was waiting for 6 blockchain confirmations), but since we were eating in the bar, it was not a problem. She seemed genuinely interested on what's going on, and how it works, and, despite not being sure what to do about unconfirmed transaction, provided us with the food without any delay. Thanks a lot.

I think this is definitely a moment to start a company that would take some of the risk involved in BTC and early confirming for faster confirmations in exchange for small fee.

TL;DR: Bought burgers with bitcoins, in White Burger, Warsaw, Poland. Burgers were great (small issues aside). BTC payment took too long, but it can be improved.

Waiting for 9.4 – Support ordered-set (WITHIN GROUP) aggregates.

On 23rd of December, Tom Lane committed patch:

Support ordered-set (WITHIN GROUP) aggregates.
 
This patch introduces generic support for ordered-set and hypothetical-set
aggregate functions, as well as implementations of the instances defined in
SQL:2008 (percentile_cont(), percentile_disc(), rank(), dense_rank(),
percent_rank(), cume_dist()).  We also added mode() though it is not in the
spec, as well as versions of percentile_cont() and percentile_disc() that
can compute multiple percentile values in one pass over the data.
 
Unlike the original submission, this patch puts full control of the sorting
process in the hands of the aggregate's support functions.  To allow the
support functions to find out how they're supposed to sort, a new API
function AggGetAggref() is added to nodeAgg.c.  This allows retrieval of
the aggregate call's Aggref node, which may have other uses beyond the
immediate need.  There is also support for ordered-set aggregates to
install cleanup callback functions, so that they can be sure that
infrastructure such as tuplesort objects gets cleaned up.
 
In passing, make some fixes in the recently-added support for variadic
aggregates, and make some editorial adjustments in the recent FILTER
additions for aggregates.  Also, simplify use of IsBinaryCoercible() by
allowing it to succeed whenever the target type is ANY or ANYELEMENT.
It was inconsistent that it dealt with other polymorphic target types
but not these.
 
Atri Sharma and Andrew Gierth; reviewed by Pavel Stehule and Vik Fearing,
and rather heavily editorialized upon by Tom Lane

Continue reading Waiting for 9.4 – Support ordered-set (WITHIN GROUP) aggregates.

Waiting for 9.4 – pg_prewarm, a contrib module for prewarming relationd data.

On 20th of December, Robert Haas committed patch:

pg_prewarm, a contrib module for prewarming relationd data.
 
Patch by me.  Review by Álvaro Herrera, Amit Kapila, Jeff Janes,
Gurjeet Singh, and others.

Continue reading Waiting for 9.4 – pg_prewarm, a contrib module for prewarming relationd data.

Waiting for 9.4 – Add ALTER SYSTEM command to edit the server configuration file.

On 18th of December, Tatsuo Ishii committed patch:

Add ALTER SYSTEM command to edit the server configuration file.
 
Patch contributed by Amit Kapila. Reviewed by Hari Babu, Masao Fujii,
Boszormenyi Zoltan, Andres Freund, Greg Smith and others.

On the next day, Fujii Masao committed patch:

Add tab completion for ALTER SYSTEM SET in psql.

Continue reading Waiting for 9.4 – Add ALTER SYSTEM command to edit the server configuration file.