How to limit rows to at most N per category – fix

A bit ago I wrote a blog post that was supposed to show how to keep number of rows in table to N per some category.

Unfortunately, I overlooked a problem related to concurrency.

Continue reading How to limit rows to at most N per category – fix

How to limit rows to at most N per category

The question was asked relatively recently on irc. And it proved to be non-trivial.

Surely, if you want to have one row per category (one address per user), it's trivial – add user_id column to addresses, make it unique, and we're done. But what if we want to allow five addresses? Or five thousands?

Let's see.

Continue reading How to limit rows to at most N per category

Waiting for 9.4 – Allow BASE_BACKUP to be throttled

On 27th of February, Alvaro Herrera committed patch:

Allow BASE_BACKUP to be throttled
 
A new MAX_RATE option allows imposing a limit to the network transfer
rate from the server side.  This is useful to limit the stress that
taking a base backup has on the server.
 
pg_basebackup is now able to specify a value to the server, too.
 
Author: Antonin Houska
 
Patch reviewed by Stefan Radomski, Andres Freund, Zoltán Böszörményi,
Fujii Masao, and Álvaro Herrera.

Continue reading Waiting for 9.4 – Allow BASE_BACKUP to be throttled

Explaining the unexplainable – part 3

In previous post in the series I wrote about how to interpret single line in explain analyze output, it's structure, and later on described all basic data-getting operations (nodes in explain tree).

Today, we'll move towards more complicated operations.

Continue reading Explaining the unexplainable – part 3

Waiting for 9.1 – Rewrite-less changing types of column

On 12th of February, Robert Haas committed patch:

Teach ALTER TABLE .. SET DATA TYPE TO avoid SOME TABLE rewrites.
 
WHEN the OLD TYPE IS BINARY coercible TO the NEW TYPE AND the USING     
clause does NOT CHANGE the COLUMN contents, we can avoid a FULL TABLE
rewrite, though any indexes ON the affected COLUMNS will still need
TO be rebuilt.  This applies, FOR example, WHEN changing a VARCHAR
COLUMN TO be OF TYPE text.
 
The prior coding assumed that the SET OF operations that force a
rewrite IS identical TO the SET OF operations that must be propagated
TO TABLES making USE OF the affected TABLE's rowtype.  This is
no longer true: even though the tuples in those tables wouldn't
need TO be modified, the DATA TYPE CHANGE invalidate indexes built
USING those composite TYPE COLUMNS.  Indexes ON the TABLE we're
actually modifying can be invalidated too, of course, but the
existing machinery is sufficient to handle that case.
 
Along the way, add some debugging messages that make it possible
to understand what operations ALTER TABLE is actually performing
in these cases.                                                      
 
Noah Misch and Robert Haas

Later on, on 15th, he committed second patch with few more cases where rewrite can be avoided.

Continue reading Waiting for 9.1 – Rewrite-less changing types of column