Waiting for 9.2 – split of current_query in pg_stat_activity

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 states. Accordingly,the field has been
renamed from current_query to query.
 
Since backwards compatibility was broken anyway to make that, the procpid
field has also been renamed to pid - along with the same field in
pg_stat_replication for consistency.
 
Scott Mead and Magnus Hagander, review work from Greg Smith

Continue reading Waiting for 9.2 – split of current_query in pg_stat_activity

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

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

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

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

[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 sites? I mean literally, let's assume you are talking with an alien from other galaxy, and you want to explain why people are using these sites. I am that alien. Tested twitter and google+, and can't find any use in my life for them. But millions of other people do. Which means that (most likely) I'm missing out on something. “Being social" is terribly vague. Can anyone of you tell me what is possible (or significantly easier) that wasn't possible before (or was much harder) thanks to these services?

I am very serious about it – I doubt that it will turn me into user, but at the very least I want to understand what drives people to use it.

[wersja polska / polish version]

Od długiego czasu istnieje szereg serwisów społecznościowych. myspace, facebook, google +, Twitter i pewnie setki innych (a to tylko te światowe, w Polsce są/były grono, nasza-klasa, blip i pewnie inne).

Nie mam nic przeciwko nim, ale jedna rzecz mnie męczy, i może któryś czytelnik może mi pomóc: jaki jest sens tych site'ów? Chodzi mi o całkowicie dosłowny sens. Załóżmy, że rozmawiasz z obcymi z innej galaktyki, i chcesz wyjaśnić, czemu ludzie korzystają z tego. Ja jestem ten kosmita. Testowałem twittera i google +, i nie mogę znaleźć żadnego przypadku w moim życiu gdzie chciałbym ich użyć. Ale miliony znajdują sens i zastosowanie. Oznacza to (najprawdopodobniej), że brakuje mi czegoś. “Społeczność" jest mało konkretna. Czy ktoś z was mi powiedzieć, co jest możliwe (lub znacznie prostsze), a nie było możliwe (lub było o wiele trudniejsze) przed postaniem tych serwisów?

Piszę całkiem serio – nie sądzę bym stał się użytkownikiem, ale przynajmniej (może) zrozumiem co powoduje, że ludzie korzystają z tego.

OmniPITR 0.3.0

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 pg_stop_backup()), thanks to the tool it can be wrapped as single call, with standardized logging, and tested logic. It also makes it trivial, and cheap, to setup more than one new slave at a time, without need to read data off master more than once.

Tips N’ Tricks – getting sizes of relations without locks

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 problem – it can lock, or it can fail.

Continue reading Tips N' Tricks – getting sizes of relations without locks

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

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? )

Waiting for 9.2 – excluding data of table from dump

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

Louis CK – live at the Beacon Theater

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 – it's a must have. And at this price (again: 5 USD!) it's not like your budget will collapse.

The site is https://buy.louisck.net/, and there is also interesting statement explaining some things, and telling how much did he made so far.

Apparently there are some DNS issues with their site – it the name doesn't resolve, either switch (temporarily) to 8.8.8.8 dns server, or use following information to fill your /etc/hosts:

$ host buy.louisck.net
buy.louisck.net is an alias for LiveSiteELB-1109051832.us-east-1.elb.amazonaws.com.
LiveSiteELB-1109051832.us-east-1.elb.amazonaws.com has address 107.21.105.222
LiveSiteELB-1109051832.us-east-1.elb.amazonaws.com has address 107.21.113.172
LiveSiteELB-1109051832.us-east-1.elb.amazonaws.com has address 107.22.233.77
LiveSiteELB-1109051832.us-east-1.elb.amazonaws.com has address 107.21.103.188
LiveSiteELB-1109051832.us-east-1.elb.amazonaws.com has address 107.21.105.96
LiveSiteELB-1109051832.us-east-1.elb.amazonaws.com has address 107.21.105.220