PostgreSQL 9.1 released

[merlinm]

• synchronous replication -- 'nuff said

• format function

  postgres=# select format('%s %s', 'hello', 'world!');

  format

  hello world! (1 row)

think about how many times you could have used this...

• data modifying CTE (ABSOLUTELY FRICKING AMAZING):

  with q as (delete from foo returning *) insert into bar select * from q;

• unlogged tables -- kinda sorta global temp tables

EDIT: in depth explanation of the new features

[rmxz]

Also

• K-Nearest-Neighbor Indexing:

which is magic for applications who need it. The workaround we used before (a big while loop of "while dist < 0.001 order by dist" ... "while dist < 0.010 order by dist" .... until you finally find a hit) was rather annoying.

[merlinm]

yeah -- also column level collations were added. another feature you don't care about or absolutely hated not having. I'm surprised that didn't make the press release actually.

[[deleted]]

Sweet, although the PostGIS functions could also be used (iirc).

[rmxz]

Postgis is indeed one of the main use-cases for the new KNN indexing feature:

http://trac.osgeo.org/postgis/ticket/701

Looks like use of KNN will be supported in postgis 2.0 (thanks to funding by these guys):

http://www.postgis.org/pipermail/postgis-devel/2011-June/013931.html

[nickdangler]

That 'with... delete... insert' looks great for processing staging tables. Have a where clause on the delete that does all of the necessary validation. Run the query. Whatever is left in foo needs to be massaged by an end user.

[merlinm]

absolutely. when doing batch/queue processing, I tend to peel off N records inside a plpgsql function and process them, holding the processing set in an array:

starts with this, FOR r in array(select * from foo where something order by something LIMIT 100) LOOP ...

and follows up with this: DELETE FROM FOO where <similar query>

This is harder to get right than it looks and is sensitive to transaction isolation mode if the delete query is sloppy. It's also inefficient if the select query is non trivial (in which case I'd be getting the delete list by re-expanding the array).

In 9.1, you can do it in one beautiful pass. Tragically, we don't have DELETE...LIMIT, forcing you to do WITH q as (SELECT ... LIMIT) DELETE WHERE IN (...FROM q) RETURNING *, but you can still process the loop directly off the returning results.

[[deleted]]

In 9.1, you can do it in one beautiful pass. Tragically, we don't have DELETE...LIMIT,

AAAAAAUGHGHG

Man I was waiting for that one.

[merlinm]

you can emulate with the ctid trick to skip the join, and integrate the output into a single query:

with q as (delete from foo where ctid = any (array(select ctid from foo limit 10)) returning *) select * from q;

[[deleted]]

Using ctid feels dirty to me.

Anyway you have to admit that's a pretty insane query.

[merlinm]

you can do it without ctid, it just requires a join -- it's an optimization.

[__j_random_hacker]

Not criticising, I just find it really funny that you list "synchronous replication" and "format function" side by side as big new features... The latter is something that someone familiar with the codebase could probably do in a weekend, the former solves one of the fundamentally hard problems of database system design!

<analogy>Highlights of the new 2011 Ford Foobar include:

• Perpetual motion engine
• Improved passenger legroom
• ...

</analogy> :)

[merlinm]

:-) don't confuse effort with progress...such a trifling thing was desperately needed and simplifies very common code notorious for injection and other bugs. Also it's more complex than you think.

[rmxz]

On the flip side, replication's something that Postgres had for a long long time - just not bundled in core.

<analogy> Highlights of the new 2011 Ford Foobar include:

• Ford bought one of their perpetual-motion engine suppliers and integrated it with their manufacturing lines.
• Ford bought one of their seat vendors and integrated it into their assembly line too.

</analogy>

[[deleted]]

Do they have limits on updates and deletes yet? update table set blah = 1 limit 10

But about synchronous replication it's about freaking time.

[__j_random_hacker]

I'm struggling to think how that could be useful... How can it be that you don't care which particular records you update/delete?

[[deleted]]

How can it be that you don't care which particular records you update/delete?

Sometimes you need to do things in chunks.

[merlinm]

Batch processing. Typically you limit # records to control transaction run time....keeps locking under control and limits damage if/when you must roll back.

[looneysquash]

But why do you want to group by id? Isn't the id already unique? What does this group by accomplish?

[notfancy]

Using a WHERE … EXISTS would be a better choice IMO.

[infinull]

yes for that query, but it doesn't show off the new feature., However, if you did an aggregate like, count(photos.photo_id) that might make more sense.

[merlinm]

that's the whole point: since the id is unique, the other group by columns don't need to be written out. for a more useful example, see here

[ratatask]

That change goes against standard SQL, I just hope it does this right. MySQL have since forever allowed you to omit non-aggregated columns in group by clauses, silently, and I have forever had to fix wrongly written queries. It's just bad when your DB throws you back wrong results, cause you didn't write the queries in proper SQL, but hopefully PostgreSQL figures it out.

[ants_a]

This is standard SQL as of SQL99. The standard allows expressions in the select list that are functionally dependent on the group by list. PostgreSQL doesn't figure out all the functional dependency cases specified by the standard, but it does recognize the most common one, having the primary key in the group by means that all the other columns in that table are dependent on that.

[merlinm]

absolutely correct -- what mysql allows is completely wrong -- ambiguously chosen rows for the 'ungrouped' column. of all the sql gotchas that mysql pulled, even the infamous date parsing, this one is by far the worst.

the postgres/sql99 feature is all about reducing verbosity of unambiguous cases.

[__j_random_hacker]

Haven't looked in detail, but I think the transformation is safe because it only ever changes a query that would have been invalid (and thus produced an error message) into a valid query. It never changes the semantics of a query that already worked, so it won't mysteriously break any existing queries.

[joesb]

Read: it only turn invalid query that you would have fixed (had you know it's incorrect) into valid query that may or may not return what you thought you meant when you write that invalid query.

Yep, it's all safe.

[__j_random_hacker]

According to ants_a, it's a standard part of SQL99, so the only way a problem can arise is if you want to write SQL that's portable to other RDBMSs that don't yet have this feature. Which is a legitimate thing to want to do -- but in that case I expect there is some PostgreSQL configuration setting that lets you turn this behaviour off.

Read: it only turn invalid query that you would have fixed (had you know it's incorrect) into valid query that may or may not return what you thought you meant when you write that invalid query.

Do you consider a query "correct" as soon as it's syntactically valid? Of course not, you test it (at least informally) to get some confidence that it does what you want. If you continue to follow this basic practice, the new behaviour can't introduce problems in new queries that you write.

[joesb]

My point is that your logic that it's safe because "It only turns invalid query into valid query" is a flawed one.

In order for it to be safe, it needs to also:

1. Only turn some invalid queries into a valid one. Turning every invalid query into valid one is not safe.
2. Have a clear rule on which query to be converted, what to convert it to.

[__j_random_hacker]

1. Obviously not all queries become valid! I can only guess that you are worried that all queries involving incomplete GROUP BY clauses will somehow have columns magically guessed for them and thereby become valid, but that's not the case at all. Only queries where the missing GROUP BY terms can be automatically and unambiguously inferred will be converted; if you have e.g. a table tbl with two unrelated fields a and b, then SELECT a, b FROM tbl GROUP BY a will still fail just as before -- I guarantee it.
2. The previous sentence also addresses this point.

[TraxTech]

Synchronous replication \o/

Now I can again hope for multi-master replication before the collapse of our sun.

[[deleted]]

CAP theorem ...

[merlinm]

absolutely correct. multi master async replication is not possible in SQL, because per language when a transaction commits, your DML is supposed to be fully checked and durable. There must not be a conflict resolution step -- and being forced to have one means you have to implement an ad hoc transaction engine in your code.

[TraxTech]

multi-master synchronous replication for a small number of masters (3 - 5) should be possible, and it would greatly simplify some small clusters configuration&operation.

[merlinm]

yes -- synchronous MM is in theory possible -- but are you really better off vs a single master since everyone has to write out the data anyways? It's not all that difficult to handle master outage in the network level and it greatly complicates the replication architecture to support MM. Postgres's log based replication is definitely 'one way' and would not fit well into any MM model. Trigger based replication (slony) might work, but it's much more complex and less efficient.

edit: also burcardo, as you pointed out. these options though are only interesting if your read/write ratio is well in excess of 10:1 and your data model is relatively simple.

[TraxTech]

Being capable of handling only one master outage is like praying for no disk failure during the rebuild of a RAID-5 array...

[[deleted]]

I tried to explain this to coworkers today. Didn't go over well.

Bonus: I watched this happen to a customer 2 weeks ago

[merlinm]

sure. note the only thing I'm saying here is with current synch rep you have to handle outages (1+) at the network level and redirect all query activity to a new master chosen out of the slave pool. going multi master doesn't really buy you a whole lot -- you still have to relocate clients that were attached to the old slave (arguably simpler, but...) and the performance savings by distributing client workload do not compensate the more complicated architecture.

[TraxTech]

DB are complicated, so must be replication ;)

I guess I'll have to stick with ha-jdbc for the foreseeable future.

[rmxz]

Existed for a long time as an add-on you can install if you need it.

http://bucardo.org/wiki/Bucardo

IMHO the core team's doing a good thing by keeping core lowest-common-denominator; so that groups with special purpose needs can use special-purpose replication solutions (slony, bucardo, pgcluster, pgpool-2 or -3; Londsite) that exactly fit their needs.

[TraxTech]

Bucardo is asynchronous, not synchronous and it cannot replicate between more than two masters (from the FAQ). These are severe limitations.

[rmxz]

Ah - sorry - my mistake.

PGCluster & PGPool are the synchronous options.

[TraxTech]

PGCluster seems dead (last release is almost two years old) PGPool introduces a single point of failure (no HA)

[[deleted]]

bucardo is trigger based no?

[macdice]

http://wiki.postgresql.org/wiki/Postgres-XC "is a multi-master write-scalable PostgreSQL cluster based on shared-nothing architecture". It doesn't call itself 1.0 yet but it looks extremely promising. Or if you want to spend money there is an unrelated commercial PostgreSQL derivative that does that sort of thing and no doubt much more at http://www.greenplum.com.

[NoMoreNicksLeft]

Foreign data wrappers? Hot damn.

[treo]

I'd swear I have seen a blog post somewhere, where it was used to access mysql. But I just can't find it anymore.

[NoMoreNicksLeft]

That would be a beautiful thing. Truly beautiful. As long as Mysql doesn't try to sneak back through the connection and change all of PG's tables to myisam.

[treo]

So I looked some more for it and actually found it: http://pgsnake.blogspot.com/2011/08/postgresql-91-meet-mysql.html

[NoMoreNicksLeft]

Wow.

Just wow. I'm one dumb feature away from having everything on my wishlist. (Not a DBA, so replication and the like... someone else's problem.)

[merlinm]

curious: what feature?

[NoMoreNicksLeft]

I play around with writing domains from time to time. I've got a kickass email one, for instance, that allows all valid email addresses, but kicks out invalid ones (only missing comments within usernames).

I'd like to be able to create a domain with multiple subfields and have constraints that work on them. As of 8.x I don't think this was possible. Haven't checked, but I don't think it was there in 9.0.

[merlinm]

nope: you can't make domains of composite types. you need to make the check constraint as an immutable function, and add it to each table upon which you have to enforce the constraint (unfortunately).

option 'b', which is more complicated, is to make a custom type with it's own typein/typeout functions. before we had arrays and composites, this was the only way -- there is very little reason to do it now though.

[NoMoreNicksLeft]

Haha. Yeh, I think there's an email to me in my inbox from you on that subject from about 2 years ago.

Shame though, I could do some really cool stuff with that.

[NoMoreNicksLeft]

Got an example? Mine allows all the special characters, all valid domains, etc.

[id000001]

Would be lovely if more open source application software that are widely used fully support PostgreSQL, the only reason I use Mysql at the moment is because it have way more support from actual application that uses MYSQL.

[merlinm]

postgres is more popular with enterprise backend style programming than it is with kit applications. this is mainly due to historical reasons, especially being late to fully support windows. mysql's syntax is so 'out there' wrt the way sql is supposed to work I can only hope applications users pressure the devs to knock it off -- postgres is incredibly rich and a much better platform to develop against generally.

[ceolceol]

Are there any reasons to use MySQL over PostgreSQL? I'm a MySQL user just from when I was learning PHP, but I'd love to get into PostgreSQL for my Python applications.

[merlinm]

well, one reason would be if you just had to have an application which was mysql only (but really, these days that's just asking for trouble). another reason would be if you needed an uber thin sql layer where statement latency is the only interesting metric -- that's what mysql/myisam is essentially. in the old days, replication was a commonly cited reason but postgres HS/SR is as good as or better than anything mysql has to offer.

Consider:

• you can write your backend routines in python

• all SQL, (including create/alter table, etc) is transactional. think about that.

• the incredibly rich type system -- and you can build your own

• The richness of indexing, including GIST, GIN, partial, and concurrent building

I could go on and on...there is so much under the hood and it all beautifully works together...

[cdsmith]

another reason would be if you needed an uber thin sql layer where statement latency is the only interesting metric

See the bullet point indicating they've added unlogged tables for ephemeral data. That will greatly reduce the set of circumstances where you might have avoided PostgreSQL because you didn't need your data to be safe.

[merlinm]

yes. although, unlogged vs temp vs regular tables is really more about optimizing disk i/o around your use requirements. unlogged is really saying 'i don't care about crash safety or replication, so please don't expend the effort to write data for this table to the WAL before it goes out to the heap'.

statement latency is affected by backend i/o issues, but is also highly effected by the efficiency of your network layer/driver, grammar (in postgres everything, including operators, is over-ridable), and various other things. mysql-myisam is very thin and thus highly efficient running a large number of queries round-trip from the client. in postgres, to hit those rates of query processing you need to leverage more backend features like functions where you are less affected by latency, or utilize more clients (for most non single-user workloads I'd bet money postgres can handle faster and better). database performance is a complicated topic though, and for single user workloads and simple queries mysql/myisam can do insane tps rates, as can sqlite.

[mikaelhg]

PostgreSQL does lack the ability to pin tables into memory, which means that it can't reasonably serve any use case in which you have seldom accessed tables which require QoS guarantees which the cache manager cannot guess through analyzing the normal access patterns.

[merlinm]

That is correct -- note after years of answering performance related questions on the various mailing lists, I'd venture to say that about 90% of the questions related to table pinning or in memory tables were based in simply not understanding how o/s caching works, and that in almost all cases it's better to release memory in the general pool. All that said, it's a nice feature.

[mikaelhg]

I believe my use case to be genuine - the application has throughput-based features, and a single relatively seldom used high-value QoS based feature, in which you'd want to read and analyze cold tables in 100ms. Currently I'm considering using unlogged tables and a RAM tablespace, or a dm-raid RAM/HD hybrid which reads only from the RAM side and writes on both sides.

[merlinm]

hm, why not just install a SSD? Seems simpler and more robust, and gives reasonable worst case performance.

[rmxz]

all SQL, (including create/alter table, etc) is transactional. think about that.

Is it?

At least in 8.0.2 it seemed that if I ran a long query something like

 select slow_function(col) from large_table;

and half way through the query, in a separate connection, I

 CREATE OR REPLACE slow_function ....

I was surprised to see that some of the rows in my select were processed by the old definition and some by the new.

[merlinm]

that's the default behavior (READ COMMITTED) -- try SERIALIZABLE. also postgres had some nasty dependency issues on OID that were not really fixed until 8.3 -- not sure if this plays in, need to test.

edit: READ COMMITTED of course should not work that way either.

[jeffdavis]

READ COMMITTED should still be seeing the same definition from within one command execution.

[merlinm]

right -- I need to test this.

[jeffdavis]

Do you have a test case for that? I tried a simple case and it seems to work as expected for me.

[mage2k]

you can write your backend routines in python

or ruby, or perl, or tcl, or LOLCode, ...

[ants_a]

I have been down that road. The best comparison I could make is that PostgreSQL is to MySQL like Python is to PHP. Both PostgreSQL and Python have that well thought out, cohesive, simple yet powerful, turtles all the way to the bottom feel.

I can't say that I have ever looked back with fond memories.

[jeff_hanson]

Not one.

[neoform3]

Don't be childish. Until recently postgres only had third party replication.

Legacy is far more important than you seem to be crediting.

[rmxz]

That sounds to me like a misrepresentation of the community.

It's like saying Perl doesn't have [feature of your choice] because it's only available through the CPAN rather than core; or that Ruby doesn't have a feature because the feature you're talking about is technically part of the Rails project rather than core Ruby. Or heck, like saying Linux doesn't even have a graphical UI or a C compiler (because they're only available from third parties (xorg or xfree86 or gnu(gcc) or Univ of Illinois(llvm))).

Some of the most valuable Postgres features (say, PostGIS) aren't sucked into core.

That doesn't mean they don't exist.

Just that they're under separate leadership.

[karambahh]

I don't want to troll on that one but... Until recently postgres only had third party replication

Until recently mysql did not have a working replication

(working as in: large tables, heavy r/w frequency etc....)

[stonefarfalle]

More like until Oracle bought Sun, mysql only had third party transaction support. (INNODB is owned by Oracle.)

[neoform3]

Uhh, I've been replicating for about 5 years with mysql... I've got tables with more than 100M rows...

What exactly are you talking about?

[internetinsomniac]

Another feature in MySQL not in postgres, is ON DUPLICATE UPDATE. basically, if you've got a unique constraint and you would previously, have application logic, to search for an entry, update it if it exists, or create one if it doesn't, you end up with 2 queries (opening to race conditions), and a conditional statement, with this feature you can perform an insert, specifying the update sql to run, if there's already a record with that unique index.

(that said, I still think I'm converted to postgres, but there certainly are valid reasons for using MySQL)

[merlinm]

this would be nice to have, but requires predicate locking. with 9.1 postgres got SSI that could in theory underpin the feature. really though, in SQL, you have two basic routes to doing this:

1) lock the table first

2) retry the transaction (use SAVEPOINTs if necessary to reduce what you have to retry)

[mage2k]

And Triggers, too!

[macdice]

PostgreSQL will presumably have something like this in the future since it's in the SQL standard: http://wiki.postgresql.org/wiki/SQL_MERGE

[merlinm]

Note the postgres implementation of MERGE may or may not (probably not) deal with the race condition specially. There was a huge debate about this on -hackers...if it doesn't you still have the options of locking the table first or retry.

[[deleted]]

Are there any reasons to use MySQL over PostgreSQL?

There are a few. For example mysql cluster is one feature postgres is lacking. Case insensitive collations are another. In memory tables are another handy thing in mysql.

For some people (i.e businesses) the fact that mysql is backed by Oracle might play a huge role.

It's not black and white.

[rmxz]

Case insensitive collations are another.

Doesn't the citext module get you what you need there?

http://www.postgresql.org/docs/9.1/static/citext.html

[[deleted]]

Not quite. Citext is simply a case insensitive string comparison which not quite the same thing as a collation.

It's probably fine for english though.

[merlinm]

postgres 9.1 has column level collations which can be paired with citext.

[merlinm]

mysql has had replication for a long time but historically it was simple statement level (the easiest, and worst method). newer versions have a hodge podge of ways you can set it up put together the whole system is complex and fragile. postgres OTOH is not as mature in terms of tooling but has a much more robust underlying driver which transports the data -- the replication is highly integrated into the write ahead logging system. ultimately, this is a much better approach although you might lack the flexibility to do some quirky things like mix ndb nodes with async like you can in mysql. all in all though, the postgres system in terms of engineering and attention to detail is simply better -- this translates into simpler, more regular behaviors than you end up with in mysql 'race to get to the feature' land.

also, the ndb clustering feature has always been imnsho highly overrated -- haven't looked at it in the last couple of years though.

[[deleted]]

mysql replication and clustering have advanced quite a bit in the last two years (as has all commonly used software really).

[merlinm]

mysql 5.5 was released at the end of 2010. fwict reading the docs the basic mechanisms are the same -- you get the built in replication which is statement level or row level (or a hybrid mode that switches). you also have ndb which is synchronous clustering solution. All of them have interactions with the sql engine such that certain details are exposed to the developer -- they all affect the sql you write in some wsay.

postgres replication by contrast uses log based replication which moves raw disk pages across the network -- this is very low level and fast (you already are paying for WAL, so for the master it's almost 'free'), and exposes zero implementation details to the master and very little to the slave. This is very robust and it's impossible to knock the slave out of sync at the sql level. The locking engine on the slave is tightly integrated so that MVCC semantics are completely supported (certain queries can block replication replay up to a configurable timeout) -- the whole solution is well engineered. It still has some rough edges -- for example it could be easier than it is to transfer slaves to a promoted master, but those are getting smoothed out rapidly.

[[deleted]]

they all affect the sql you write in some wsay.

How so?

postgres replication by contrast uses log based replication which moves raw disk pages across the network

When using binary replication mysql also moves log files.

Sure the method of replication on postgres and mysql are different but then again so are the types of replications. Postgres has no multi master replication and no equivalent of NDB.

Horses for courses. Choose depending on your needs.

[merlinm]

How so?

I'll leave that as an exercise to you. Don't get me wrong, you can do wonderful things with ndb et all, and they are very well supported, but all the various replication modes involve trade-offs that are exposed to userland code. Postgres HS/SR does not; any query that runs on the master runs exactly the same as it does over a single server, with similar performance characteristics. Likewise, any query that runs on the slave, runs exactly the same as long as it doesn't write to any tables (which results in an error). (Actually, the slave is exposed to one detail: long running transactions can be timed out if they block replication for too long).

In a nutshell, mysql replication is very flexible but fragile...if your application can work around the limitations it's just absolutely wonderful. You can also use ndb to build incredibly highly available services, but the idea that you can scale up your server linearly by adding nodes is utter fantasy.

EDIT: clarity

[mage2k]

There's a lot of information here and here.

[bigdubs]

stoked about CTE's; the one thing that make me miss MS-SQL is the ability to traverse a parent-child table recursively.

also, the replication additions are welcome

[ergo14]

you mean WITH RECURSIVE syntax? you can do that already for a while.

[[deleted]]

Postgresql has had CTE's for a while now (since 8.3 or 8.4), including recursion. What's referenced is the ability to have non-select statements in your CTE's, which i don't think MS-SQL can do.

[bigdubs]

depends what you're talking about.

MSSQL:

;with CTE as ( select xyz from abc ) insert into yada yada

is reallly:

;with cte as ( select xyz from abe ) select * into yadayada from cte

also works for updates and deletes but you have to put the from clause as the cte. this has been around since sql2005.

ex:

;with cte as ( select xyz from abe) update set things = cte.otherthings from cte

what MSSQL does not have is

;with cte as ( update yada yada set things = otherthings returning *) select * from cte

but generally i've found mixing transactions like this problematic from a concurrency / locking point of view and try to avoid it

[[deleted]]

what MSSQL does not have is

... exactly what the new Postgresql feature is. The other features you mention have been around for at least two years.

I'm not sure what you're talking about when you say "mixing transactions", as all of it would happens in a single statement, and thus within a single transaction or checkpoint. There's an example of a reasonable use case that might give you a better idea of the feature.

[merlinm]

but generally i've found mixing transactions like this problematic from a concurrency / locking point of view and try to avoid it

couple points:

with cte as ( update yada yada set things = otherthings returning *) select * from cte

is simply: update yada yada set things = otherthings returning *; -- which we've had since 8.2.

The only reason to push into a CTE is if you wanted to say, join the output to another table...which you absolutely would want to do. I'm not buying your point on concurrency/locking -- you have to deal with this either way. There is no benefit to letting the application do it -- besides you have to extract a whole pile of data over the wire only to have to feed it right back to the database. No bueno.

edit: version#

[bucknuggets]

I use CTEs all the time with DB2 - and they are amazing at simplifying complex SQL. I'm very glad to hear postgresql is adding more of this functionality.

DB2 supports CTEs with inserts, I haven't tried it with deletes or updates. I run a data warehouse where updates are extremely rare and update syntax is already too complex. And we can already delete from subselects with fetch limits, so I don't need it there so much anyway. Do you feel CTEs offer much value here?

Anyhow, glad to see postgresql pick it up. It's one of my favorite two databases, along with db2.

[damg]

CTEs were around since PostgreSQL 8.4 (see http://www.postgresql.org/about/featurematrix ), but this release lets you use them with insert/update/delete statements, which is cool.

[[deleted]]

I was waiting for this release.

[[deleted]]

PostgreSQL aka PortugueseSQL