Only Try This At Home
By Josh Williams
May 19, 2011
Taken by Josh 6 years to the day before the release of 9.1 beta 1
For the record, 9.1 is gearing up to be an awesome release. I was tinkering and testing PostgreSQL 9.1 Beta 1 (… You are beta testing, too, right?) … and some of the new PL/Python features caught my eye. These are minor among all the really cool high profile features, to be sure. But it made me think back to a little bit of experimental code written some time ago, and how these couple language additions could make a big difference.
For one reason or another I’d just hit the top level postgresql.org website, and suddenly realized just how many Postgres databases it took to put together what I was seeing on the screen. Not only does it power the content database that generated the page, of course, but even the lookup of the .org went through Afilias and their Postgres-backed domain service. It’s a pity the DBMS couldn’t act as the middle layer between those.
Or could it?
That’s a shortened form of it just for demonstration purposes (the original one had things like a table browser) … but it works. For example, on this test 9.1 install, hit http://localhost:8000/public/webtest and the following table appears:
| generate_series | lh | rnd |
|---|---|---|
| 1 | 0 | 0.548577250913 |
| 2 | 1 | 1.70926172473 |
| 3 | 1 | 1.24841631576 |
| (etc) | ... | ... |
Note the use of two specific 9.1 features, though. The plpy object contains nice query building helper utilities like quote_ident that you may be familiar with in other languages. But this also makes use of subtransactions, which helps recover from db errors. That’s important here, as something like a typo in a table name will generate an error from Postgres and without that in place the database will end the transaction and ignore any subsequent commands the function tries to run.
But with that in place, the page shows the 404 error, and picks up where it left off with subsequent requests:
Error code 404.
Message: Table not found.
By the way, if it’s not clear by now don’t take this anywhere near a production database, if not any other reason that a transaction will be held open as long as that function runs. That will hold back all the nice maintenance stuff that keeps things running efficiently. Still, I think it helps show off what just a handful of lines of code can do in a powerful language like PL/Python. I’m sure with the right module PL/PerlU could do something very similar. But even more I think it shows how Postgres is growing and innovating by leaps and bounds, seemingly every day!
Comments