tag:blogger.com,1999:blog-360314402023-12-16T09:27:10.120-08:00Merlin's stuffThe writings of Merlin Moncure, professional database developer, about work, life, family, and everything else.Merlin Moncurehttp://www.blogger.com/profile/07784270014334369640noreply@blogger.comBlogger14125tag:blogger.com,1999:blog-36031440.post-26843402415386752472014-02-10T08:29:00.000-08:002014-02-10T08:29:35.821-08:00Bitcoin is fine; Surrogate keys are the problem.If you've been following the bitcoin saga you may have heard about Mt Gox's halting of currency withdrawals. Well, it's come out that due to their (completely preventable) improper transaction tracking bad actors have been gaming them. Oops.
Mt Gox (bitcoin exchange) uses surrogate key improperly and pays the price.
I've determined a long time ago that overuse of surrogate keys is aMerlin Moncurehttp://www.blogger.com/profile/07784270014334369640noreply@blogger.com7tag:blogger.com,1999:blog-36031440.post-18052483941629132222012-09-12T10:59:00.001-07:002012-09-17T08:44:52.108-07:00psql -- now with a splash of color!I was tired of looking at psql's dull grey output and decided to see if I could work in some color. I was able to do this -- it's pretty hacky but seems to work well enough for me to publish here. first, if you haven't already, please read the instructions here for a good primer for how to drastically improve psql data browsing through the less utility (this will work Merlin Moncurehttp://www.blogger.com/profile/07784270014334369640noreply@blogger.com4tag:blogger.com,1999:blog-36031440.post-19337155323106252902007-12-17T18:55:00.000-08:002007-12-17T19:45:46.417-08:00Advisory Locks, an UpdateUpdate: more testing has shown this to not work as well as I thought...details will followUpdate 2: this doesn't work at all. I need to give it some more thought...MI've made no secret that 'advisory locks' is one one of the coolest features in PostgreSQL (see my posts here and here). As a general purpose database mutex, you can do all sorts of wonderful things with them...long term locks, Merlin Moncurehttp://www.blogger.com/profile/07784270014334369640noreply@blogger.com2tag:blogger.com,1999:blog-36031440.post-76124927858601189112007-12-16T08:39:00.000-08:002007-12-17T00:19:39.679-08:0012 Days of Christmas, PostgreSQL styleIn case you have trouble remembering the lyrics...select v || case when v=1 then ' partridge in a pear tree' when v=2 then ' turtle doves' when v=3 then ' french hens' when v=4 then ' calling birds' when v=5 then ' GO-O-OLDEN RINGS!' when v=6 then ' swans a swimming' when v=7 then ' geese a laying' when v=8 then ' maids a milking' when v=9 then ' ladies dancing' when v=10 then ' lords aMerlin Moncurehttp://www.blogger.com/profile/07784270014334369640noreply@blogger.com10tag:blogger.com,1999:blog-36031440.post-64034534058925157222007-12-16T07:31:00.000-08:002007-12-16T15:50:34.397-08:00Managing Trees with Arrays, Part 2Einhverfr writes:Out of curiosity, have you looked at contrib/tablefunc and its connectby() function? We use it and the parent_id solution in LSMB and it works pretty well. It also allows for a cleaner semantics because we don't have to store the full path in the record (this and "level" are both generated by connectby!).Thanks for your interest.I have looked at connectby, and I have a lot of Merlin Moncurehttp://www.blogger.com/profile/07784270014334369640noreply@blogger.com2tag:blogger.com,1999:blog-36031440.post-72537597810785481812007-10-19T09:55:00.000-07:002007-10-19T12:27:06.421-07:00A better psql with lesspsql is a great tool, but not very good at browsing data. Or is it? The following settings will make psql much more usable.in your user profile:export PAGER=lessexport LESS="-iMSx4 -FX"in your .psqlrc (make it if it's not already there)\timing\pset pager alwaystry it!! now wide query results will paginate much better and searching is much easier.update: fixed quotes for LESS, thanks depeszMerlin Moncurehttp://www.blogger.com/profile/07784270014334369640noreply@blogger.com16tag:blogger.com,1999:blog-36031440.post-19274796951625766382007-09-06T21:19:00.000-07:002007-09-06T21:20:12.011-07:00Managing Trees with ArraysOne of my favorite problems in databases is working with recursive structures. In particular, I've made it my quest to debunk the prevailing myth that recursive structures are not 'relational' and thus should be handled in application code or some alternative format. One strategy in this war is demonstrating how easy it is to deal with recursive structures with a little thought and some clever Merlin Moncurehttp://www.blogger.com/profile/07784270014334369640noreply@blogger.com5tag:blogger.com,1999:blog-36031440.post-79247930151140330042007-09-04T20:43:00.000-07:002007-09-06T18:59:41.537-07:00PostgreSQL 8.3 Features: Plan InvalidationAs previously stated, PostgreSQL 8.3 is shaping up to be a great release of the software. One of the biggest beneficiaries of the new feature set are pl/pgsql developers. 8.3 stands to be the best thing to happen to pl/pgsql since 8.0 brought dollar quoting to the table...before which some might argue serious development with the language bordered on the impractical. The major new features areMerlin Moncurehttp://www.blogger.com/profile/07784270014334369640noreply@blogger.com5tag:blogger.com,1999:blog-36031440.post-44128403112694012952007-09-03T08:55:00.000-07:002007-09-03T08:58:13.456-07:00PostgreSQL 8.3 Features: Arrays of Compound TypesThis will be the first in what hopefully be a series of entries describing the new and interesting features in PostgreSQL 8.3. The database is entering in the final stages of the development cycle before going into beta with all the major patches having been submitted weeks ago (with one possible stunning exception, lazy xid assignment described here. Thus, aside from the hard decisions that Merlin Moncurehttp://www.blogger.com/profile/07784270014334369640noreply@blogger.com4tag:blogger.com,1999:blog-36031440.post-37203899905247707512007-08-31T13:13:00.000-07:002007-09-08T14:37:51.557-07:00Dell md1000 bonnie++ benchmarksFollowing are the results of our testing of the Dell MD1000 with two raid controllers (Perc 5/e). Server was dell 2950, 8gb ram. Pay close attention to the seeks figure, as this important figure for databases (the sequential rates are not all that important). We tried various combinations of software raid and hardware raid in both active/active and active passive configurations.scrolldown Merlin Moncurehttp://www.blogger.com/profile/07784270014334369640noreply@blogger.com17tag:blogger.com,1999:blog-36031440.post-42047187823229477672006-12-13T10:14:00.000-08:002006-12-13T10:51:29.697-08:00Advisory Locks Part 2Previously, I wrote about PostgreSQL Advisory Locks and how important and useful they are. One thing I forgot to mention is how in very specifc cases they can improve concurrency in your application. This is because they are both long-term (lock duration can exceed a single transaction) and short-term (lock duration can be shorter than a single transaction). This entry will describe the Merlin Moncurehttp://www.blogger.com/profile/07784270014334369640noreply@blogger.com2tag:blogger.com,1999:blog-36031440.post-2551453626451517882006-12-10T08:46:00.000-08:002006-12-10T09:05:00.753-08:00About the authorWho am I?My name is Merlin Moncure and I am a computer programmer living in Florida. I have a beautiful wife and two beautiful children and am pretty successful by most reasonable measures. This blog is going to be moslty about things related to computer and technology but also about the various things I am concerned about. Particular areas of focus will be the economy, PostgreSQL, a Merlin Moncurehttp://www.blogger.com/profile/07784270014334369640noreply@blogger.com3tag:blogger.com,1999:blog-36031440.post-1165704122004078352006-12-09T14:42:00.000-08:002007-09-05T05:25:54.996-07:00PostgreSQL 8.2 Advisory LocksRecently released PostgreSQL 8.2 is a treasure trove of helpful features for the database developer. Dubbed a performance release by the advocacy team, the most powerful open source database engine has once again shown that it is greater than the sum of its parts, and the breadth and scope of the project is greater than its developers. Many internal structural improvements were made, and Merlin Moncurehttp://www.blogger.com/profile/07784270014334369640noreply@blogger.com5tag:blogger.com,1999:blog-36031440.post-1160854838884367282006-10-14T12:40:00.000-07:002006-10-14T12:40:41.046-07:00 OK, I'm testing posting to blogger from google docs. If it works, it will be the first 'killer feature' of google office. Spreadsheets has a long way to go, but I can see docs being a Word replacement with a few improvements. Well, here goes!merlinMerlin Moncurehttp://www.blogger.com/profile/07784270014334369640noreply@blogger.com0