Some guy came to #postgresql today. Described his problem, got additional question, and then … well .. I wouldn't believe if it didn't happen to me.
Continue reading How to make sure you will not get any help on IRC
Tag: postgresql
Waiting for 9.1 – CREATE TABLE IF NOT EXISTS
On 25th Robert Haas committed patch which adds first of ‘CREATE IF NOT EXISTS' commands:
Log Message: ----------- CREATE TABLE IF NOT EXISTS. Reviewed BY Bernd Helmle.
Continue reading Waiting for 9.1 – CREATE TABLE IF NOT EXISTS
How to order by some random – query defined – values?
Let's imagine simple situation – you have table of objects (each with id), and you want objects 3, 71, 5 and 16. And in that order!
How to do it?
Continue reading How to order by some random – query defined – values?
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.
Waiting for 9.1 – \conninfo in psql
On 20th of July, Robert Haas committed (and later committed change to it) patch which adds another \* command to psql:
Log Message: ----------- Add \conninfo command to psql, to show current connection info. David Christensen. Reviewed by Steve Singer. Some further changes by me.
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.
Tips N’ Tricks – looking for value in all columns of a table
Every so often you might need to find a value regardless of which column it's in.
Of course not in application code, as this would be very slow. But you might be in situation where you just don't know where did application get some value from, and you want to find it in table. Table that has 1-2 fields is trivial to search, but if you have 15 columns, naming them all in WHERE clause is tedious.
Continue reading Tips N' Tricks – looking for value in all columns of a table