Tag: postgresql

Waiting for 9.1 – format()

On 21st of November Robert Haas committed new patch, which adds new function:

Add new SQL function, format(text).

Currently, three conversion format specifiers are supported: %s for a
string, %L for an SQL literal, and %I for an SQL identifier.  The latter
two are deliberately designed not to overlap with what sprintf() already
supports, in case we want to add more of sprintf()'s functionality here
later.
 
Patch by Pavel Stehule, heavily revised by me.  Reviewed by Jeff Janes
and, in earlier versions, by Itagaki Takahiro and Tom Lane.

Continue reading Waiting for 9.1 – format()

Waiting for 9.1 – Removed autocast footgun

On 8th of November, Tom Lane committed patch, which doesn't provide any new features, but removes one of the more annoying footguns in PostgreSQL:

Prevent invoking I/O conversion casts via functional/attribute notation.
 
PG 8.4 added a built-in feature for casting pretty much any data type to
string types (text, varchar, etc).  We allowed this to work in any of the
historically-allowed syntaxes: CAST(x AS text), x::text, text(x), or
x.text.  However, multiple complaints have shown that it's too easy to
invoke such casts unintentionally in the latter two styles, particularly
field selection.  To cure the problem with the narrowest possible change
of behavior, disallow use of I/O conversion casts from composite types to
string types via functional/attribute syntax.  The new functionality is
still available via cast syntax.
 
In passing, document the equivalence of functional and attribute syntax
in a more visible place.

Continue reading Waiting for 9.1 – Removed autocast footgun

Understanding postgresql.conf : checkpoint_completion_target

Starting new blog series – explanation of various configuration parameters.

I will of course follow no schedule or order – if I'd had to – it would be my job, and in this way – it's fun.

First configuration parameter to write about is checkpoint_completion_target.

Continue reading Understanding postgresql.conf : checkpoint_completion_target

Waiting for 9.1 – adding values to enums

Finally, on 25th of October, Tom Lane committed patch which does:

Allow new values to be added to an existing enum type.
 
After much expenditure of effort, we've got this to the point where the
performance penalty is pretty minimal in typical cases.
 
Andrew Dunstan, reviewed by Brendan Jurd, Dean Rasheed, and Tom Lane

Continue reading Waiting for 9.1 – adding values to enums

Grouping data into time ranges

Today some guy on IRC asked question, which I didn't fully understand, but which could (probably) be summarized: how to group data into 5 minute intervals, based on some timestamp column.

Well, it seems trivial (as long as you know how to do it), but since he clearly didn't know how to do it (or I misunderstood his problem), here's the explanation:

Continue reading Grouping data into time ranges

Reduce bloat of table without long/exclusive locks

Some time ago Joshua Tolley described how to reduce bloat from tables without locking (well, some locks are there, but very short, and not really intrusive).

Side note: Joshua: big thanks, great idea.

Based on his idea and some our research, i wrote a tool which does just this – reduces bloat in table.

Continue reading Reduce bloat of table without long/exclusive locks