Copying Tables in Postgresql

If you want to copy a table, all of its data and all of its constraints, indexes, defaults, etc., you have a few options in postgresql

Create table has a couple forms that are helpful:

This form will create a table like yours with all your data, but without any sequences, indexes, etc on it.  As such it is very quick for copying data and nothing else.

This form will create a table without any of the data but with all appropriate schema objects. Be careful, this will cause serial columns on the table you are creating to point toward sequence values of the table you are copying.
CREATE sequence new_blah_seq;
-- do what you actually need here, 1 might be a good first id for your situation
SELECT setval('new_blah_seq', (SELECT max(id) FROM blah), true);
ALTER TABLE new_blah ALTER ID SET DEFAULT nextval('new_blah_seq');

Data can then be inserted with the following, but this could take a long time on a large table because all constraints and indexes are checked/built one at a time for each row of data.

INSERT INTO new_blah (SELECT * FROM blah);

The next option is to have all your constraints and indexes be declared externally so that they can be re-added later. This allows the CREATE TABLE AS syntax to do its quick inserts and then you can reinstate all schema objects around this table after the data has been copied. This seems to be the absolutely fastest way to copy a table, but requires you to manage your schema objects outside of postgresql or to pg_dump the schema, do some tricky find and replace on names, and then re-run each schema create/alter command.

The final option is to pg_dump the tables you wish to copy, rename the originals and then restore the copy that you made. This takes longer because it must write the data to disk twice (once into the stream/file you dump to then once again when you run the restore and it gives the results

Sending HTML Email with attachments in Common Lisp

Ryepup gave us a great couple of simple examples for CL-SMTP.  Unfortunately when I tried to combine them to send an HTML email that had an attachment, I did not get the expected results.  Instead, we insert an inappropriate ContentType header above the multipart then the message body has content type text/plain.  I saw Leslie Polzer’s comment about a BKNR branch of cl-smtp that handled mime-types better.  So I downloaded it, fired it up, bound the content-type variable and it almost produced the correct email.  Unfortunately the result email was missing a newline after the header so my client skipped displaying this first email part (assuming it to be part of the header).  I submitted a patch, that was accepted, that adds the newline after the multipart message header.

[Paste Example]

Munich, Eagles Nest, and Salzberg

<Sorry for no updates till now; it has been hard to get free internet at our hotels.  I will try to improve the formatting of this article in the future.  For now I just want to try to get as much down as possible.>

Plane Flight

Airport security was far more friendly and I left Orlando TSA a nice comment on their comment card.  I had been so nervous about air travel security that to be treated with such kindness and respect by all employees was a nice treat.  Our trip started with a 3 hour delay on our first flight which caused us to miss our DC to Munich connection.  Instead half our party was rerouted on a DC to to Frankfurt flight and the rest were rescheduled for the next day.  After much cajoling with United staff they were convinced that we should all be put on the 10 pm flight to Munich with a connection to Frankfurt.  The rest of the flights were relatively uneventful, save for my and Shannon’s luggage being left in Frankfurt. (It arrived at our hotel later in the day.)


Finally we were happily at our hotel with very little sleep.  After an all too brief nap we headed out for dinner and some of Munich’s famous Bier.  We wandered (via underground) to Marienplatz and saw the Rathaus/Glockenspiel at night (which was gorgeous).  I have been in constant awe of all the beautiful architecture we have seen.  It seems that here they actually care about the way things look.  We found the Hackerhaus Restaurant, we John and I had a sampler platter of traditional German foods.  I had sausage roast pork, boiled beef, and a beef & beef-liver meatball.  All the food (even the liver ball) was delicious.

After dinner we took in the Munich saturday nightlife.  The first pub we stopped at had cask beer which was something I hope to have frequently here.  We drank with some very nice Germans and toasted our arrival in their country (Prost!).  They convinced us that litres of beer are superior to half litres.

Sunday we took in the Glockenspiel in the morning and then had a traditional Munich Bratwurst for lunch.  It was white and much softer than the brats I am used to, but still delicious.  By the end of the meal I had been about sauerkrauted out.  The rest of the afternoon was spent at the Munich Technology Museum (biggest in the world) and their English Gardens.

The technology museum was about the coolest museum that I have ever been to.  It was essentially a museum that contained the history of every type of technology that humans have endeavored at.  We covered about a fifth of the museum in about 3-4 hours, focusing on airflight, ships, computers, the Altima Cave, and musical instruments. I also walked through areas of glass, ceramics, astronomy, measurement devices, and other areas I cannot remember right now.  Some highlights for me were: A Cray One super computer, an IBM System 360, a Curta hand calculator (including a clear version that aloud some examination of the inner workings), and boats of all variety (including U-1 the first/prototype german u-boat).

After everyone was museumed out, we decided to head through the English Gardens back to our hotel.  We stopped at the beer garden in the center for a brief rest and imbibement (Ein Dunkle Beir – One Dark Beer).  The garden was beautiful and offered a great view of the skyline.  After our beer and coffee, we decided to head back to the hotel for a rest before heading out to dinner at Hofbreauhaus.  While it was a tad more commercialized than what I remember when I was 10, it was still a good time (though the food was much less good than the night before, but what can you expect from a buffet).  It was here I heard my favorite anti-American joke thus far: “What do you call a person who knows two languages? Bilingual.  What do you call a person who knows only one? American.”  This was told by a German entertaining Italians claiming to know 5 languages (and was extremely nice during the entire meal).  

<tipsier now after returning from Nuremberg pubbing>

After dinner we took our entire party around the corner to introduced the rest of the group to some cask beer from the same bar as the previous night.  This led to drunken conversation with my father-in-law about how to improve businesses, particularly Acceleration.  His suggestion was to institute a system of employee incentives and rewards for new business.  After finishing my 2nd and a half liter the rents returned to the hotel.  We stayed out drunkenly chatting with a swede we met on the street.  We visited the hard rock Munich (where they played the crappiest American music they could find), in deference to my sister/law’s desire to drink some vodka instead of beer.  While everybody else went to bed I decided to stay up and drink some more with my brother/law Brian and his wife Sarah.  This devolved into a heated discussion over whether it is better to believe in the message of Jesus or the divinity of the man (obviously with no clear answer), until we were chased off by the hotel front desk for being too loud.

Eagles Nest

A very hungover morning and a nap in the car later, we arrived at Eagle’s Nest.  Gorgeous, nothing more can improve that sentiment.  The alps are so very pretty and I got to climb some mountains in my Birks.  (A nice german man asked if my shoes had been stolen).  The thing that seemed so strange to me is that in spending something like 150 million dollars they didn’t manage to make any bedrooms.  Its not like this place is close to anything (at least a half hour ride now to anything that resembles a town).  Why would you buld one of the most gorgeous retreats in the world placement wise and not bother to allow anyone to stay there over night? (Seriously there are like 3 rooms that are not the kitchen.)  Oh well, I guess that they were not thinking things through so clearly at this time.  

Salzburg / Mozart Dinner

After our visit to Eagles Nest we headed to Salzburg for a trip to the Mozart Dinner Concert.  If you get the chance this is definitely worth it.  Old Salzburg is more beautiful yet than even Munich, on the trip we saw engravings eulogizing Schubert and statues dedicated to Mozart.  We wandered through old Salzburg to get to our destination, St. Peters Cloister.  We ate and enjoyed immensely the concert in the Baroque Hall of St. Peter’s, which focused on Mozart’s better known instrumental pieces and excerpts of his operas.  The singing and playing was heavenly, especially in this ancient room.  The performance was by a string sextet including two violins, a viola, cello, double bass, harpsichord, and two vocalists.  It could not have been more wonderful.  After this wonderful dinner, we went back to our Mariott hotel in the newer part of Salzburg.

Salzburg Shopping

The next morning, we went back into the old city, to view it during the day and do a bit of shopping for all the nice people back at home.  We saw a huge amount of gorgeous architecture and visited a beautiful old baroque church that had been in continuous existence since the 1200 hundreds.  We visited a market that had fresh and cooked food where I was able to pick up a hotdog.  It was almost American, but with a tougher skin, and instead of sitting in a bun, it was jammed down a hole in the middle of a baguette.  We came back for our departure via a park where we purchased a couple paintings.  On the way we grabbed a quick lunch at a cafe where I was (finally) able to satiate my need for a cheese burger (with ham instead of bacon).  Culture Note: there is nothing that the Germans will not put pig on; I like that in a people.

~ Fin ~

I will write more when I get a chance, thanks to those at home, and sorry if this is less legible than it currently seems.  I will make another post linking to our pictures ASAP

Reader Macros: A cool feature, frustration waiting to happen

One of the neat features in Common Lisp is that any program can register a new reader macro that will read from the input stream when it sees some character. A good example of where this is useful and well implemented is Edi Weitz’s :cl-interpol.  This library allows you to, for any given file, declare that you want to read phrases that begin with #? as a interpolated.

For example:
#?"${name} is coming over for ${event}"
#?/\d{3}(-|\.|\s)\d{3}(-|\.|\s)\d{4}/ ;creates a regular expression

This can be a wonderful time saver and can be quite a bit easier on the eyes than a serious format.  However, these reader syntaxes can be good and bad.  Many library authors include them by default and they are enabled when the package is loaded.  The reader syntaxes are not bound by packages (because the readtable is global).  This has the downside of your code randomly ceasing to work based on some vagary of library load order and which reader syntax you expect to be enabled.

I had problems when loading LISA into an application for the first time because it stomped my :cl-interpol file-level reader declaration and kept stomping it.  No amount of reevalution seemed to provide reliable results as to which of the two syntaxes were enabled.  I finally found where in the LISA config to disable that syntax, which allowed me to work around the conflict.

I had a similar experience when trying to use :cl-mediawiki in a project for the first time.  CL-MediaWiki used to use of the :cl-unification #T reader macro which enabled a template engine to ease unification.   (Now it uses the make-template function).  When I added cl-mediawiki to my projects dependancy list and started to use it, it failed utterly.  The reason? Apparently Closure-HTML has a reader syntax defined and enabled for #T.  Both of these libraries add this syntax as part of their standard load.  This leaves the end user completely unable to use your library (or forcing them to patch it) if it stomps on existing reader-syntaxes that they have enabled.

My suggesstion would be to follow Edi’s example and allow the end user to enable a syntax on a perfile level (at least) and have the syntaxes disabled by default.  This allows the user to decide when and where they use your handy syntax without frustrating them along the way. Also as best I can tell, following Edi’s example is always safe and good advise.

CL-MediaWiki in Action

As mentioned in a previous post, we heavily utilize a MediaWiki instance in to manage a lot of small bits of data.  Last night I worked :cl-mediawiki into our internal billing system.  For many months it has been a manual process to create a new page and category for a client in our billing system.  We had a report that would give you a link and some default wiki text to type in.  With the desire to bring more notes into the wiki, and the advent of the edit-api available in MediaWiki 1.13, I set about writing cl-mediawiki to allow us to more directly integrate.

Now when a new client is entered into our billing system, we call out to the wiki to automatically create a wikipage and category for that client.  When a new order gets added to our system, we will add a new order page.  When that order is attached to an account or moved to a new account, we will replace the account number on their wiki page with the new account number.  When the order gets canceled, we can add the cancelation notes directly to the wiki.

CL-MediaWiki now also requires :cl-ppcre.  The upshot of this is a regex-replace-all function that will replace the content of a wikipage (with an optional value to set the page to if it doesnt exist).  This was used to keep the account number up to date on the order page.

link to the paste – Scroll down to see all the examples mentioned

Announcing CL-MediaWiki: A Common Lisp interface to the MediaWiki API

UPDATE: Changed hosting information at the bottom

We use a MediaWiki instance at work to manage a tremendous amount of random facts.  We have it setup with Sphinx FTI to get good search results and have found the wiki to be a huge help in managing our data.  In fact, via a bit of reverse proxy magic, some of the notes fields in our internal billing application are chunks of a wiki page for that client with links to all of that client’s other attached wiki pages

Media Wiki Notes Field Screen Shot

Media Wiki Notes Field Screen Shot

We wanted the ability to interact with the wiki in a more programatic manner (such as making cancelation notes), so I started checking into the media wiki api.  It gives you the ability to do almost anything you would want to do with the Media Wiki engine programatically (except I cant figure out how to get it to render wiki markup for me).  I wanted a wrapper around this in Common Lisp which could take care of formulating the messages to the wiki engine and determining whether or not we succeeded preferably leaving these as just function calls to the rest of application.  Because I could find no mention of a library like this elsewhere, I decided to go ahead and whip one up.

Using :cxml :drakma, & :cl-unification, I have written a very incomplete wrapper around media wiki remote api.  The basic call cycle so far is as follows:

  • Build a list of parameters / values based on the api documentation
  • Feed these into Drakma to make the request of the media wiki api
  • Use CXML to parse the returned XML string into an XML-S tree
  • Use CL-Unification to match the response XML and extract data for return (when successful) and error conditions which we signal (if it is not).

Adding mixins to an instance

I often want to attach little bits of meta data to certain instances of an object without all the hassle of declaring a million and one types.  If I have a set of 5 mixins that I want to add in various combinations to a concrete class there are like 5! new classes I have to create to get them all.  What I often want is to just add a mixin to a specific instance of a class and let that instance’s init args/fns handle the rest.  I was able to accomplish this (with help from vic on #lisp) with the meta-mixin mixin.  Applied to a class, it lets you pass a list of mixins as an initarg and will change the class of your running object to one that contains all the superclasses requested in the correct order (and with the same metaclass) as the original object.

I found this to be a nifty, useful way to interact with my objects.  Check out the paste for code and example.

Fun with CL-PPCRE: Counting Parentheses

So traditionally, regular expressions have been… err… regular.  While regular expressions are incredibly useful, being regular is a limitation.  The canonical example of what a regular language cannot do, is counting parentheses.  For example, if I am trying to match an expression with arbitrary nesting, I need a more robust matching solution than a regular language can provide.

I have seen that CommonLisp – Portable Perl-compatible regular expressions (cl-ppcre) supported a parse tree representation of regular expressions.  My hope was that this would allow arbitrary embedding of CL code into the matching structure of the regular expressions.  It does.

To teach myself how to use this tool, I decided to count parentheses.  The basic structure of the parse tree is just a list of symbols that get compiled into specific matching functions.

  • ‘.’ -> :EVERYTHING

To embed a lisp function you create a node in that parse-tree named :FILTER whose child is a function of one argument (the position in the string this function should try to match).  This function returns how many charcters it consumes (by position), or nil if it did not match.

Keep in mind that the following sample is not reentrant, but could be made to be so.  Now without further ado, the code:
link to the paste – A UCW / Common Lisp website about our environment


As a piece of speculative work, we created a tool for visualizing utility usage for our fair city Gainesville, FL. We chose to write it in Common Lisp using a somewhat modified version of the UCW web framework. This will be the third site we have written in Common Lisp and the first that is publicly available. It is also our first publicly deployed site with a PostgreSql backend. We chose to use pgsql because the bridge between sqlserver on windows and Steel Bank Common Lisp on Debian/GNU/Linux is a bit leaky through the multiple layers of C. Most of the data for this site is static, and shouldn’t need to tax the database too much (not that postgres couldn’t handle it, just that we might not have all of the config worked out yet).

We are hoping that this site will be useful for local developers, the city council, and for GRU (the local utility company) in better planning and executing “green” initiatives. We also hope that by putting something in place to allow consumers to see how well they are doing ecologically (compared to those around them), that those consumers can take charge of their utility usage and make better decisions that leave the earth a better place.

We chose to write the application in Common Lisp because:

  • It allows us to get our ideas to the web with less work.
  • I don’t have to interact with xml in terms of angle brackets. In our XHTML generation library (built on CXML and CHTML) I can write functions that take, manipulate and return DOM nodes. This allows tremendous abstraction possibilities on the HTML front.
  • Macros – sometimes they are the only abstraction that works, and then they are great to work with.
  • I compile once and then never think about it again as I continuously work in a running image. I continue to compile frequently, but each of these is only for a form or file and takes very little time to finish. This has led to problems with broken check-ins, but I think that is more of an issue with my usage patterns than the environment.
  • I get to retain small pieces of my sanity that C# would mercilessly torment to Death death = new Death (my_sanity).

We will hopefully be blogging about this a bit more in the future so stay tuned if you care and if you don’t… um… continue not caring.