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 in SQL.
Continue reading r/trees ( recursive trees, what did you think about? )
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.
Continue reading Waiting for 9.2 – excluding data of table from dump
( 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:
Continue reading The secret ingredient in the webscale sauce
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 want some checksums (md5, sha1, sha2).
On 3rd of November, Heikki Linnakangas committed patch:
Support range data types. Selectivity estimation functions are missing for some range type operators, which is a TODO. Jeff Davis
On 8th of October, Tom Lane committed patch:
Support index-only scans using the visibility map to avoid heap fetches. When a btree index contains all columns required by the query, and the visibility map shows that all tuples on a target heap page are visible-to-all, we don't need to fetch that heap page. This patch depends on the previous patches that made the visibility map reliable. There's a fair amount left to do here, notably trying to figure out a less chintzy way of estimating the cost of an index-only scan, but the core functionality seems ready to commit. Robert Haas and Ibrar Ahmed, with some previous work by Heikki Linnakangas.
Some time ago I wrote a blogpost about why index might not be used.
While this post seemed to be well received (top link from depesz.com on reddit), it doesn't answer another question – what index to create for given situation.
I'll try to cover this question now.
On 22nd of September, Tom Lane committed patch:
Make EXPLAIN ANALYZE report the numbers of rows rejected by filter steps. This provides information about the numbers of tuples that were visited but not returned by table scans, as well as the numbers of join tuples that were considered and discarded within a join plan node. There is still some discussion going on about the best way to report counts for outer-join situations, but I think most of what's in the patch would not change if we revise that, so I'm going to go ahead and commit it as-is. Documentation changes to follow (they weren't in the submitted patch either). Marko Tiikkaja, reviewed by Marc Cousin, somewhat revised by Tom
Continue reading Waiting for 9.2 – filtered info in explain analyze
I'd like to announce start of new project for PostgreSQL admins – Curo.
On 19th of July, Simon Riggs committed patch:
Cascading replication feature for streaming log-based replication. Standby servers can now have WALSender processes, which can work with either WALReceiver or archive_commands to pass data. Fully updated docs, including new conceptual terms of sending server, upstream and downstream servers. WALSenders terminated when promote to master. Fujii Masao, review, rework and doc rewrite by Simon Riggs
Continue reading Waiting for 9.2 – cascading streaming replication