Wednesday, March 16. 2011
Earlier today my colleague Depesz posted a nice write up showing one of the use cases for the new 9.1 feature, "Writable CTE's". It certainly shows one handy thing that this feature will enable, but it's not the only one. Here's a quick little bit of SQL I have been playing with for some time that re-implements the infamous "UPSERT" command (a long time sticking point for people trying to make MySQL apps more cross-database compatible) into Postgres.
pagila=# select * from actor where first_name = 'AMBER' and last_name = 'LEE';
actor_id | first_name | last_name | last_update
----------+------------+-----------+-------------
(0 rows)
pagila=# with upsert as
pagila-# (update actor set last_update = now() where first_name = 'AMBER' and last_name = 'LEE' returning actor_id)
pagila-# insert into actor (first_name, last_name, last_update)
pagila-# select 'AMBER', 'LEE', now() WHERE NOT EXISTS (SELECT 1 FROM upsert) ;
INSERT 0 1
pagila=# select * from actor where first_name = 'AMBER' and last_name = 'LEE';
actor_id | first_name | last_name | last_update
----------+------------+-----------+----------------------------
201 | AMBER | LEE | 2011-03-16 11:29:15.611445
(1 row)
pagila=# with upsert as
pagila-#(update actor set last_update = now() where first_name = 'AMBER' and last_name = 'LEE' returning actor_id)
pagila-# insert into actor (first_name, last_name, last_update)
pagila-# select 'AMBER', 'LEE', now() WHERE NOT EXISTS (SELECT 1 FROM upsert) ;
INSERT 0 0
pagila=# select * from actor where first_name = 'AMBER' and last_name = 'LEE';
actor_id | first_name | last_name | last_update
----------+------------+-----------+----------------------------
201 | AMBER | LEE | 2011-03-16 11:30:21.244226
(1 row)
Now, to be fair, this bit of SQL does have a race condition (think two people trying to insert the same actor at the same time), so it doesn't really solve all of your problems, but if you are looking for a quick hack, it might just do the trick. Also don't be afraid to play with it; this was like 2 minutes of thought and making sure the syntax worked; you could certainly try turning it around or coming up with other variants. That's actually one of the coolest things about this feature; waiting to see what use cases people come up with for it.
Monday, February 7. 2011
Once again someone has brought up the idea of having a hints system in Postgres, which means once again we're all subjected to watching people trod out the same tired, faulty, and even self-contradictory reasons to try to justify the idea that Postgres doesn't have, need, or even want a hinting system. As frail as the arguments might be, people are so entrenched in their positions now that even having a discussion on the topic is difficult. And in fact, there are really two discussion going on here; one is whether Postgres should have any type of hinting system, and the other a more specific discussion on if we should have a query hinting system.
Continue reading "Why the F&#% Doesn't Postgres Have Hints?!?! "
Saturday, April 10. 2010
One of the things I preach about a lot is good monitoring of your database servers; having tools in place to tell you both what good looks like and when things go bad is critical for large scale success. But sometimes you just need to monitor a momentary process, where setting up a check in your normal monitoring software is overkill. In these cases one tool that can help out is the watch command.
Case in point, the other day I needed to back up a fairly large partitioned table (about 1.3TB on disk). The plan? A quick little script to pg_dump each of the partitions (about 325). Feed the script through xargs -P so I don't swamp the box, but I get some concurrency out of things. And of course, I planned to run the whole thing in screen session. But dumping this much data will take some time, so how to check on the progress?
When working on databases, one of the most natural things to me is to whip up some SQL to see what going on inside my database. Then you pipe that through watch, and you have some quick and simple monitoring. This example happens to be on postgres, but you could do it with any database's command line program.
Wednesday, July 22. 2009
Happy to see the first issue of the new Open Source Database Magazine has been released.
For those that aren't aware, Open Source Database Magazine is a re-incarnation of the old MySQL Magazine. The open source database ecosystem has grown a lot of the last year, with the rising popularity of newish systems like Drizzle and MariaDB, the continued growth of the PostgreSQL community, the revival of old concepts like CouchDB, and the really ground breaking stuff like HadoopDB.
So, check out the website, check out my article, and let the OSDBzine folks know what new stuff in Open Source Database you'd like to see more about.
Sunday, April 19. 2009
This week I'll be traveling to California to take part in the Percona Performance Conference, a two-day event run concurrently with the MySQL Users Conference, but centered around all aspects of internet based design, performance, and scalability. At OmniTI, we have helped many organizations scale their systems using Oracle, Postgres, and/or MySQL, both for high OLTP loads as well as large OLAP oriented systems. In my talk, "Scaling with Postgres" (Wednesday @ 11:55AM), I plan to explain the general path for scaling Postgres related systems, and compare how this lines up with other database systems.
Since I'll be in town for a few days, I've also signed up to do a Postgres BOF at the MySQL users conference (Wednesday @ 7:30PM). Currently the BOF has no topic, but there are a number of topics that I expect people might want to talk about, including ways to use Postgres and MySQL together, Porting applications and doing cross database development, and of course the straight-forward "why should I use Postgres?" If you are planning on coming to the BOF and have a specific topic or question you'd like answered, please post it in the comments, or send me an email.
Note I'll also be in town Thursday as well; I'm hoping to take in several of the talks during that time, but I've no official duties, so should be available for further questions and conversation during that time as well. Hope to see you there.
Friday, December 12. 2008
Well, it's been a long time (100 editions in fact!) since I have hosted a log buffer, but I thought what better way to break-in the new blog than by hosting the 127th edition of Log Buffer. Let's get started!
Friday, December 5. 2008
Lorenzo Alberton has a nice post about understanding how different databases handle quoting identifiers and case sensitivity. There was one thing I thought worth adding to the discussion, which was to remind folks that along with the inconsistent behavior you get running MySQL on different operating systems, you need to be aware that it also uses a non-standard quoting mechanism; using ` rather than " for quoting identifiers. You can change this behavior to something more standard by using the SQL_MODE parameter, worth looking at if you're doing cross database development (or just future proofing your app). Of course, thinking about SQL_MODE, you also have to be aware of things like lowercase_table_names, which can also change the behavior that you might see (i'd recommend defining that setting as part of your database naming conventions). And of course, you can override a lot of these things at the session level, so you can't necessarily count on setting these in your mysql conf file (consider an application that overrides this behavior). Blech... sometimes TMTOWTDI is more trouble than it is worth.
|
You were saying?
Tue, 20.12.2011 10:49
thanks for the slides and the post.
Sun, 27.11.2011 15:42
And the slides are up at http: //www.2ndquadrant.com/en/talks /
Thu, 24.11.2011 11:42
You probably want array_agg in stead of array_accum. That sa id, if you don't understand ho w to fix the query, it's [...]