Waiting for 9.1 – Reduced lock levels for ALTER TABLE

On 28th of July, Simon Riggs committed patch which:

Log Message:
-----------
Reduce lock levels of CREATE TRIGGER and some ALTER TABLE, CREATE RULE actions.
Avoid hard-coding lockmode used for many altering DDL commands, allowing easier
future changes of lock levels. Implementation of initial analysis on DDL
sub-commands, so that many lock levels are now at ShareUpdateExclusiveLock or
ShareRowExclusiveLock, allowing certain DDL not to block reads/writes.
First of number of planned changes in this area; additional docs required
when full project complete.

Continue reading Waiting for 9.1 – Reduced lock levels for ALTER TABLE

Logging queries – how?

One of the questions that pop up frequently on IRC is how to see queries are now executed on the server, and what queries were earlier.

Theoretically answer to this is simple – pg_stat_activity and log_min_duration_statement. Or log_statement. What is the difference? That's exactly why I'm writing this post.

Continue reading Logging queries – how?

Waiting for 9.1 – standard_conforming_strings = on

Generally I write about new features, but this change is relatively important.

Yesterday, on 20th of July, Robert Haas committed following change:

Log Message:
-----------
Change the default value of standard_conforming_strings to on.
 
This change should be publicized to driver maintainers at once and
release-noted as an incompatibility with previous releases.

Continue reading Waiting for 9.1 – standard_conforming_strings = on

Waiting for 9.0 – Final Post ?

As of now, I am happy user of 9.1devel version of PostgreSQL:

=$ psql -c 'select version()'
                                                       version                                                       
---------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1devel on x86_64-unknown-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit
(1 row)

So, I guess no new features will make it to 9.0 – after all, 9.0 is currently already beta3.

If you want to get list of what I wrote about for 8.5/9.0 – just check the tag page.

OmniPITR – update

OmniPITR project that I wrote about some time ago is going on.

Just today I finished tests for omnipitr-backup-slave – part of OmniPITR which lets you make hot-backups of WAL-slave machine – without any additional load on master.

As previously – please download (svn co) and test. In case you have problems – please mail me or contact me on irc.freenode.net – I'm usually on #postgresql.

Should you use HASH index?

Today, Mattias|farm on IRC asked how to create primary key using HASH index. After some talk, he said that in some books it said that for “=" (equality) hash indexes are better.

So, I digged a bit deeper.

IMPORTANT UPDATE: As of PostgreSQL 10 hash indexes are WAL logged. As such, main point against them is gone.

Continue reading Should you use HASH index?