select * from depesz;

OmniPITR 0.7.0

depesz — Mon, 07 May 2012 20:34:27 +0000

Just released new version of OmniPITR. This version has one important new feature: when you’re calling omnipitr-backup-slave, it will make backups only of required xlog files, and not, as previously, of all in walarchive directory. This is important, especially in case you have multiple slaves, or you keep shared long-term walarchive. Previously – backups would [...]

OmniPITR 0.6.0

depesz — Thu, 26 Apr 2012 20:21:09 +0000

Just released new version, 0.6.0 (it should be visible on pgxn soon) of OmniPITR set of tools. New version has one new feature – parallelism. This works in omnipitr-archive and omnipitr-backup-* programs, and allows for parallel delivery to remote destinations (multiple -dr switches). Also – if you’re using compresses wal archive and omnipitr-backup-slave reading from [...]

Waiting for 9.2 – DROP INDEX CONCURRENTLY

depesz — Tue, 10 Apr 2012 12:06:40 +0000

On 6th of April, Simon Riggs committed patch: Add DROP INDEX CONCURRENTLY [IF EXISTS], uses ShareUpdateExclusiveLock The log message is pretty laconic, so let’s see what it actually means. Before I can show you new functionality, we need to see and understand what was happening before. For my tests, I have this table: $ \d [...]

Let’s talk dirty

depesz — Wed, 04 Apr 2012 11:59:24 +0000

Important disclaimer: the module that I’m writing about was written by my colleague Phil Sorber. We all have been in, or heard about, situation like this: $ UPDATE users SET password = '...'; WHERE id = 123; (hint: first ; is before where). Of course you should have backups, and you can protect yourself from [...]

Szukam webmastera [polish only]

depesz — Mon, 02 Apr 2012 13:43:50 +0000

Ostatnio pisałem o moim pierwszym, malutkim, projekciku w Pythonie – kanasta.depesz.com. Całość działa, ale – mimo, że nie spodziewam się mieć tam użytkowników (tzn. innych niż nasza czwórka), chciałbym by to wyglądało, i może było odrobinę prostsze w użyciu. Szukam więc webmastera. Czego oczekuję? Rzeczony webmaster … pobierze sobie z githuba soft, postawi go u [...]

Request for help with Python/Flask – Prośba o pomoc z Pythonem/Flaskiem

depesz — Sat, 31 Mar 2012 17:17:41 +0000

( wersja polska poniżej ) I (not-so) recently started to learn Python. To have some playground that I can work on, I decided to write simple website that will let me track scores of a card game that I play with my family – Canasta. To write it, I chose to use Flask framework, so [...]

Waiting for 9.2 – pg_stat_statements improvements

depesz — Fri, 30 Mar 2012 15:19:40 +0000

Three interesting patches: On 27th of March, Robert Haas committed patch: New GUC, track_iotiming, to track I/O timings. Currently, the only way to see the numbers this gathers is via EXPLAIN (ANALYZE, BUFFERS), but the plan is to add visibility through the stats collector and pg_stat_statements in subsequent patches. Ants Aasma, reviewed by [...]

OmniPITR 0.5.0

depesz — Fri, 30 Mar 2012 10:10:31 +0000

Today, I released new version of OmniPITR – 0.5.0. This new version has one important new feature – which is so called “direct destination” for backups. What it means? What it does? How it helps? Let’s see… Let’s assume you have remote destination for backups, something like: $ omnipitr-backup-master ... -dr gzip=storage.host:/path/to/store/backups ... Up to [...]

Waiting for 9.2 – More rewrite-less ALTER TABLE ALTER TYPEs

depesz — Tue, 14 Feb 2012 14:38:51 +0000

Three patches for you today, all committed by Robert Hass: On 7th of February, patch: Add a transform function for numeric typmod coercisions. This enables ALTER TABLE to skip table and index rebuilds when a column is changed to an unconstrained numeric, or when the scale is unchanged and the precision does not decrease. [...]

Waiting for 9.2 – EXPLAIN TIMING

depesz — Mon, 13 Feb 2012 11:19:15 +0000

On 7th of February, Robert Haas committed patch: Sometimes it may be useful to get actual row counts out of EXPLAIN (ANALYZE) without paying the cost of timing every node entry/exit. With this patch, you can say EXPLAIN (ANALYZE, TIMING OFF) to get that. Tomas Vondra, reviewed by Eric Theise, with minor doc changes [...]

Waiting for 9.2 – Named parameters in SQL functions

depesz — Mon, 13 Feb 2012 01:17:56 +0000

On 5th of February, Tom Lane committed patch: Allow SQL-language functions to reference parameters by name. Matthew Draper, reviewed by Hitoshi Harada SQL language for writing functions has some benefits – including the fact that they are very simple, can be inlined, and are always available (pre 9.0 pl/PgSQL had to be specially installed [...]

Waiting for 9.2 – JSON

depesz — Sun, 12 Feb 2012 16:00:12 +0000

On 31st of January, Robert Haas committed patch: Like the XML data type, we simply store JSON data as text, after checking that it is valid. More complex operations such as canonicalization and comparison may come later, but this is enough for not. There are a few open issues here, such as whether we [...]

Waiting for 9.2 – deadlock counter

depesz — Thu, 09 Feb 2012 08:37:59 +0000

On 26th of January, Magnus Hagander committed patch: Adds a counter that tracks number of deadlocks that occurred in each database to pg_stat_database. Magnus Hagander, reviewed by Jaime Casanova It’s similar to previously described patch – another column in pg_stat_database: $ SELECT datname, deadlocks FROM pg_stat_database; datname │ deadlocks ───────────┼─────────── template0 │ 0 depesz [...]

Waiting for 9.2 – temporary file stats per database

depesz — Tue, 07 Feb 2012 13:35:12 +0000

On 26th of January, Magnus Hagander committed patch: Add counters for number and size of temporary files used for spill-to-disk queries for each database to the pg_stat_database view. Tomas Vondra, review by Magnus Hagander As you perhaps know certain operations in PostgreSQL can create temporary files. I am not talking about temporary tables – [...]

Waiting for 9.2 – pg_basebackup from slave

depesz — Fri, 03 Feb 2012 14:08:48 +0000

On 25th of January, Simon Riggs committed patch: Allow pg_basebackup from standby node with safety checking. Base backup follows recommended procedure, plus goes to great lengths to ensure that partial page writes are avoided. Jun Ishizuka and Fujii Masao, with minor modifications In PostgreSQL 9.1 we got pg_basebackup – it is a tool to [...]

Waiting for 9.2 – Trigger Depth

depesz — Wed, 01 Feb 2012 17:36:50 +0000

On 25th of January, Alvaro Herrera committed patch: Add pg_trigger_depth() function This reports the depth level of triggers currently in execution, or zero if not called from inside a trigger. No catversion bump in this patch, but you have to initdb if you want access to the new function. Author: Kevin Grittner [...]

Waiting for 9.2 – ALTER IF EXISTS

depesz — Wed, 01 Feb 2012 13:14:11 +0000

On 23th of January, Simon Riggs committed patch: ALTER <thing> [IF EXISTS] ... allows silent DDL if required, e.g. ALTER FOREIGN TABLE IF EXISTS foo RENAME TO bar Pavel Stehule This adds important capability – change object of it exists, and not raise exception if it doesn’t. Conditional DDL was always big point on [...]

Change in anonymization of plans on explain.depesz.com

depesz — Tue, 31 Jan 2012 12:10:56 +0000

As you perhaps know, explain.depesz.com has anonymization feature. Couple of days ago Filip contacted me and sent a patch that stopped anonymization of typecasts. I thought about the patch, and what it achieves, changed it’s internals, but kept the effect. And today, it got released. Normal plan looks like this. But you might want to [...]

Some new tools for PostgreSQL or around PostgreSQL

depesz — Mon, 23 Jan 2012 10:31:41 +0000

During last months I wrote some tools to help me with my daily duties. I’d like to let you know you about them, as you might find them useful. So, here we go: pg.logs.tail Available from OmniTI SVN. It’s a smarter “tail -f” for PostgreSQL logs. Smarter in a way, that it knows that PostgreSQL [...]

Waiting for 9.2 – split of current_query in pg_stat_activity

depesz — Sun, 22 Jan 2012 23:14:57 +0000

On 19t of January, Magnus Hagander committed patch: Separate state from query string in pg_stat_activity This separates the state (running/idle/idleintransaction etc) into it's own field ("state"), and leaves the query field containing just query text. The query text will now mean "current query" when a query is running and "last query" in other [...]

Waiting for 9.2 – NULLS from pg_*_size() functions

depesz — Sun, 22 Jan 2012 15:51:23 +0000

On 19t of January, Heikki Linnakangas committed patch: Make pg_relation_size() and friends return NULL if the object doesn't exist. That avoids errors when the functions are used in queries like "SELECT pg_relation_size(oid) FROM pg_class", and a table is dropped concurrently. Phil Sorber This patch on its own is not very visible, but it [...]

What is the point of social sites? / Do czego służą serwisy społecznościowe?

depesz — Fri, 13 Jan 2012 19:11:16 +0000

[wersja polska poniżej / polish version below] For a quite long time there is a number of social services/sites out there. myspace, facebook, google+, twitter and probably hundreds of others. I don’t have anything against them, but one thing bugs me, and perhaps someone reading this could help me: what is the point of these [...]

OmniPITR 0.3.0

depesz — Wed, 04 Jan 2012 20:44:42 +0000

Just released version 0.3.0 of our tool for handling WAL based replication in PostgreSQL – OmniPITR. Version jump is related to addition of another tool – omnipitr-synch. This tool is used to copy PostgreSQL data dir (including all tablespaces of course) to remote location(s). While this process is usually simple (call pg_start_backup(), transfer data, call [...]

Tips N’ Tricks – getting sizes of relations without locks

depesz — Tue, 20 Dec 2011 16:23:02 +0000

If you have production DB servers, chances are you’re running variant of these queries: SELECT SUM(pg_relation_size(oid)) FROM pg_class WHERE relkind = 'i' SELECT SUM(pg_relation_size(oid)) FROM pg_class WHERE relkind = 'r' To get summarized size of tables and/or indexes in your database (for example for graphing purposes). This (getting pg_relation_size for rows in pg_class) has one [...]

r/trees ( recursive trees, what did you think about? )

depesz — Fri, 16 Dec 2011 13:39:14 +0000

I got asked on irc to show some examples how to use recursive CTE. Apparently my previous post wasn’t good enough I think that most of the users will use recursive cte to deal with trees I decided to show how to use it, even though it’s not my favorite approach to dealing with trees [...]

Waiting for 9.2 – excluding data of table from dump

depesz — Thu, 15 Dec 2011 13:24:50 +0000

On 14h of December, Andrew Dunstan committed patch: Add --exclude-table-data option to pg_dump. Andrew Dunstan, reviewed by Josh Berkus, Robert Haas and Peter Geoghegan. This allows dumping of a table definition but not its data, on a per table basis. Table name patterns are supported just as for --exclude-table. This patch gives me [...]

Louis CK – live at the Beacon Theater

depesz — Wed, 14 Dec 2011 12:45:22 +0000

I’m huge fan of stand-up comedy, and one of the guys that I like is Louis CK. Learned today that he made new movie – “Live at the Beacon Theater”, and it’s available on his site. For $5. Without DRM, geographical limitations or other bullshit. So, if you can handle his kind of jokes – [...]

The secret ingredient in the webscale sauce

depesz — Fri, 02 Dec 2011 14:20:08 +0000

( title, in case you don’t know, comes from excellent “movie” about databases ) The secret ingredient is of course sharding. Can we do sharding in PostgreSQL? First, let’s define what sharding is: Wikipedia shows nice, correct, and absolutely unhelpful answer: A database shard is a horizontal partition in a database or search engine. But [...]

I never got this far :(

depesz — Thu, 24 Nov 2011 15:06:21 +0000

No Points Name Hp [max] 1 179310 depesz-Wiz-Hum-Fem-Cha died in The Dungeons of Doom on level 22. Killed by a minotaur. - [94]

OmniPITR 0.2.0

depesz — Thu, 10 Nov 2011 19:01:53 +0000

Just released version 0.2.0 of OmniPITR. There is one important change – that is OmniPITR backup programs can now create checksum files for backups. Details in docs, but the basic idea is that to all the other options to omnipitr-backup-{master,slave} we added (Phil Sorber to be exact) –digest option, which lets you specify that you [...]