Tag: postgresql

OmniPITR 0.6.0

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 it – all the wal files have to be decompressed before making backup – and this decompression can be parallelized too.

All parallelization is controled using -PJ option (–parallel-jobs), so you can add “-PJ 10" to get up to 10 decompressions at the same time or up to 10 deliveries at the same time.

Let’s talk dirty

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 it. But what if backup is too old, and you didn't protect yourself?

Continue reading Let's talk dirty

Waiting for 9.2 – pg_stat_statements improvements

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 Greg Smith, with some further changes by me.
  • On 27th of March, Robert Haas committed patch: 
    Expose track_iotiming information via pg_stat_statements.
 
Ants Aasma, reviewed by Greg Smith, with very minor tweaks by me.
  • On 29th of March, Tom Lane committed patch:

    Improve contrib/pg_stat_statements to lump "similar" queries together.
 
pg_stat_statements now hashes selected fields of the analyzed parse tree
to assign a "fingerprint" to each query, and groups all queries with the
same fingerprint into a single entry in the pg_stat_statements view.
In practice it is expected that queries with the same fingerprint will be
equivalent except for values of literal constants.  To make the display
more useful, such constants are replaced by "?" in the displayed query
strings.
 
This mechanism currently supports only optimizable queries (SELECT,
INSERT, UPDATE, DELETE).  Utility commands are still matched on the
basis of their literal query strings.
 
There remain some open questions about how to deal with utility statements
that contain optimizable queries (such as EXPLAIN and SELECT INTO) and how
to deal with expiring speculative hashtable entries that are made to save
the normalized form of a query string.  However, fixing these issues should
require only localized changes, and since there are other open patches
involving contrib/pg_stat_statements, it seems best to go ahead and commit
what we've got.
 
Peter Geoghegan, reviewed by Daniel Farina

Continue reading Waiting for 9.2 – pg_stat_statements improvements

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

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.
 
Noah Misch, with a few stylistic changes and a fix for an OID
collision by me.
  • also on 7th, patch: 
    Add a transform function for varbit typmod coercisions.
 
This enables ALTER TABLE to skip table and index rebuilds when the
new type is unconstraint varbit, or when the allowable number of bits
is not decreasing.
 
Noah Misch, with review and a fix for an OID collision by me.
  • and a day later final patch: 
    Add transform functions for various temporal typmod coercisions.
 
This enables ALTER TABLE to skip table and index rebuilds in some cases.
 
Noah Misch, with trivial changes by me.

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

Waiting for 9.2 – EXPLAIN TIMING

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 by me.

Continue reading Waiting for 9.2 – EXPLAIN TIMING

Waiting for 9.2 – JSON

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 should attempt to
detect UTF-8 surrogate pairs represented as \uXXXX\uYYYY, but this gets
the basic framework in place.

and then, 3 days later, Andrew Dunstan committed another one, related:

Also move the escape_json function from explain.c to json.c where it
seems to belong.
 
Andrew Dunstan, Reviewd by Abhijit Menon-Sen.

Continue reading Waiting for 9.2 – JSON