pgsql tablespaces

csn

I see tablespaces are on the menu for 7.5. What are they?

Sxooter

Basically, they're a way of having a database that stores different tables and indexes on different mount points. so, you could build a RAID1+0 for your indexes and leave your data on a RAID1, or you could put half the tables in one table space and the other half of the tables in another tablespace.

csn

Ah 🙂. Do you know what else will be in 7.5? The latest news was PITR and win32.

Sxooter

There's actually a big giant discussion over when to set the beta test feature freeze date, and it's looking like the only absolutely sure thing to be included this time is the win32 port.

PITR seems like it's close to making it, as do nested transactions. Maybe 2 phase commit, but no one's holding their breath on that one.

The only one I REALLY want is nested transactions. PostgreSQL's overly simple transaction semantics make it a hard sell for certain projects that it would be a shoe in for if it supported nested transactions.

csn

Just curious - what are some scenarios where you'd use nested transactions?

Sxooter

A common example is where you want to insert a few dozen records, OR update them if the PK already exists. without sub transactions, you have to carefully test to see if there is already a record there.

With sub transactions, you can just begin a new (sub) transaction, insert, and if it fails, detect that in the outlying transaction and update.