Clojure clj-record library and Postgresql

I came across a problem when using the clj-record library with Postgresql. When creating a new record in a table, a java.lang.IllegalArgumentException is thrown, despite the fact that the record was successfully created. The "create" function inserts and retrieves the inserted record, so it looked like the problem happened when fetching the newly inserted record. By looking at the source code for the "create" function, I found out that the "insert" function called by "create" calls "id-query-for", which is a multimethod. For postgresql, the function is :

(defmethod id-query-for "postgresql" [_ table-name]
  (str "SELECT currval(pg_get_serial_sequence('" table-name "','id'))"))

Calling Postgresql's pg_get_serial_sequence means that the id column must be called "id", and that it must be "serial". My table layout looked like that :

CREATE TABLE users
(
  id integer NOT NULL DEFAULT nextval('seq_users'::regclass),
  "login" character varying(10),
  first_name character varying(20),
  last_name character varying(20),
  "password" character varying(10),
  email_address character varying(50),
  CONSTRAINT pk_users PRIMARY KEY (id)
)

The column id was properly named "id", but its type was not serial, which was the problem. pg_get_serial_sequence returned null, so the record could not be fetched. I changed my table layout to look like this :

CREATE TABLE users
(
  id serial NOT NULL,
  "login" character varying(10),
  first_name character varying(20),
  last_name character varying(20),
  "password" character varying(10),
  email_address character varying(50),
  CONSTRAINT pk_users PRIMARY KEY (id)
)

This worked as expected. I don't quite like the fact that the column id is fixed to "id", nor the logic to fetch the inserted record...

Using Clojure in Vim

There are already a few plugins for well known editors to use Clojure. Anybody familiar with Vim will want to use it with Clojure. My environment is not complete yet, but here is what I am using so far :

• The VimClojure plugin, for syntax highlighting.
• The TagList plugin. Very useful plugin which shows the content of tags files.
• Exuberant Ctags, used by the TagList plugin.

I'm currently using Vim 7.3, TagList 4.5 and Exuberant Ctags 5.8.

VimClojure plugin

The installation is straight forward, and is fully explained in a readme file in the archive. The following settings has to added in your .vimrc (_vimrc) :

syntax on
filetype plugin indent on

I've also added the following two settings:

let vimclojure#HighlightBuiltins=1 
let vimclojure#ParenRainbow=1

The rainbow parenthesis might be distracting, but I quite like it this way.


I'm leaving the plugin in offline mode, so I didn't configure the Nailgun server. This allows to use nice features like dynamic documentation lookup and Repl running in a Vim buffer, but I'll configure that when I get more familiar with Clojure. For the moment, I use Repl in another window and load the file I'm editing using (load "filename").

(UPDATE: the explanations for configuring the Nailgun client and server are here)

Exuberant Ctags

Nothing more than downloading the latest archive, extracting it, and setting the directory where the ctags executable is into the path.

TagList plugin

One problem: the taglist plugin relies on the filetype detected by Vim and passes the filetype to the exuberant ctags utility to parse the tags. With the VimClojure plugin on, the filetype is set to "clojure", which is not recognized by the taglist plugin. In order to tell the plugin to use the Lisp syntax, the following line should be added to the .vimrc (_vimrc) file :

let tlist_clojure_settings = 'lisp;f:function'

A useful setting is "let Tlist_Exit_OnlyWindow=1", which will close the TagList window if the last file was closed.

In Vim, the ":Tlist" command shows the tag list in a new window.

That's it. With a couple of plugins, using Clojure in Vim becomes much more fun.To generate the help documentation of both plugins, just type ":helptags ~/vimfiles/doc"(or another path where the docs are) in Vim. Take some time to go through the help and check useful settings can be used.

Using Exuberant Ctags on the command line with Clojure files

The following steps are not necessary in order to use the TagList plugin. It should be done only if you want to generate the tags file for Clojure files from the command line.

I downloaded the latest version (5.8). Unfortunately, Clojure is not supported yet. But lisp is. Exuberant Ctags autodetects a file type by looking at its file extension. We can for it to use a different language by using the --language-force option (--language-force=lisp), but it's annoying to do it all the time. What can we do then ? It is possible to override the file mappings via the --langmap option. Let's see how it's done:

Check the supported languages

ctags --list-languages

Ant
Asm
Asp
Awk
Basic
BETA
C
C++
C#
Cobol
DosBatch
...

Clojure is not there.

Check the file mappings

ctags --list-maps
...
Lisp     *.cl *.clisp *.el *.l *.lisp *.lsp
...

The extension above are associated to Lisp.

Adding Clojure file extensions to the Lisp mapping

New extensions can be added via the --langmap option:

ctags --langmap=lisp:+.clj --list-maps
...
Lisp     *.cl *.clisp *.el *.l *.lisp *.lsp *.clj
...

To set it permanently, we have to set this option in the CTAGS environment variable (e.g. set CTAGS=--langmap=lisp:+.clj), or in one of the setting files used by Exuberant Tags (e.g. $HOME/.ctags)

After everything was set, looking at a tags file generated by "ctags *.clj" will confirm that the Clojure extension is properly recognized.

Oracle ROWNUM : What it is and what it's not

What is ROWNUM ?

In Oracle, ROWNUM is a pseudocolumn added by Oracle after the data has been selected. The ROWNUM will be incremented only if the selected row satisfies the condition clause. A pseudocolumn does not physically exist, but it behaves like any table column. The first row returned by a query has a ROWNUM of 1, the second row has a ROWNUM of 2, etc... The ROWNUM can be used to retrieve the top n records of a query. For example, to return a maximum of ten cats from an animal table, the query would look like:

SELECT * from animals WHERE type='cat' and ROWNUM < 11

Pitfall 1: Ordered results

The preceding query returned ten cats. But it doesn't mean that the same ten cats will be returned each time we execute that query, because the fetched records are not ordered. However, adding a simple ORDER BY does not work:

SELECT * from animals WHERE type='cat' and ROWNUM < 11 ORDER BY name

because the results will be first fetched and then ordered. To get the first ten results of an ordered query, we have to fetch records and order them first :

SELECT * from ( SELECT * from animals WHERE type='cat' ORDER BY name) WHERE ROWNUM < 11

Pitfall 2: Getting the nth record

We may think that using the ROWNUM allows to fetch the nth record of a query. For example, getting the second cat of the list :

SELECT * from animals WHERE type='cat' and ROWNUM = 2

Unfortunatly, this doesn't work. In the first paragraph of this article, you might have noticed that some words are in bold : the ROWNUM is set after records were fetched and will be incremented only if the selected row satisfies the condition clause. What's the problem with that ? Imagine that there are ten cats in the animals table. After fetching ten cats, oracle will try to assign a ROWNUM to the first record. The first record ROWNUM is 1. But the query fetches only the record whose ROWNUM is 2. The first record will be skipped, but the ROWNUM will still be 1. The ROWNUM will not be incremented. Thus, the second record ROWNUM is 1. And again, the "ROWNUM = 2" condition is not satisfied. Finally, the ROWNUM will never be incremented, and no records will be fetched. The equality condition will only work with the first record (ROWNUM = 1).

Pitfall 3: Getting all records after the nth record

Well, the problem is the same as with getting the nth record. The condition will never be met. Having a condition looking like "WHERE ROWNUM > 10" will never return anything. The first record whose ROWNUM is 1 will not satisfy the condition, so 1 will be used again for the next record, and so on.

Execution plans

It's interesting to look at the difference of execution plan between "ROWNUM < 11" and "ROWNUM = 2". The first condition uses a "COUNT STOPKEY" operation, which means that Oracle performs a count operation where the number of rows returned is limited by the ROWNUM expression in the WHERE clause. Even if there are one billion records in the animals table, only the first ten records will be read.

SELECT * from animals WHERE ROWNUM < 11

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |    10 |   850 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |         |       |       |            |          |
|   2 |   TABLE ACCESS FULL| ANIMALS |    10 |   850 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM>10)

On the other hand, the erronous "ROWNUM = 2" condition produces the following plan:

SELECT * from animals WHERE ROWNUM = 2

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |    80 |  6800 |     3   (0)| 00:00:01 |
|   1 |  COUNT              |         |       |       |            |          |
|*  2 |   FILTER            |         |       |       |            |          |
|   3 |    TABLE ACCESS FULL| ANIMALS |    80 |  6800 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM=2)

We can see that Oracle goes through all records and tries to filter them using "ROWNUM = 2".

References

• Oracle Sql Language Reference : 3 Pseudocolumns, ROWNUM Pseudocolumn
• Ask Tom

Oracle : Primary Keys and Non-Unique Indexes

I was asked by a colleague recently about "Unique and Nonunique Indexes" in Oracle Concepts guide :

Oracle recommends that unique indexes be created explicitly, using CREATE UNIQUE INDEX. Creating unique indexes through a primary key or unique constraint is not guaranteed to create a new index, and the index they create is not guaranteed to be a unique index.

Well, I was as puzzled as she was, by the following two facts:

1. Creating unique indexes through a primary key or unique constraint is not guaranteed to create a new index.
2. The index they create is not guaranteed to be a unique index.

Looking at the "PRIMARY KEY Integrity Constraints" of the same guide, it says :

Oracle Database enforces all PRIMARY KEY constraints using indexes. The primary key constraint created for a column is enforced by the implicit creation of:

• A unique index on that column
• A NOT NULL constraint for that column

Composite primary key constraints are limited to 32 columns, which is the same limitation imposed on composite indexes. The name of the index is the same as the name of the constraint. Also, you can specify the storage options for the index by including the ENABLE clause in the CREATE TABLE or ALTER TABLE statement used to create the constraint. If a usable index exists when a primary key constraint is created, then the primary key constraint uses that index rather than implicitly creating a new one.

The important part is "If a usable index exists when a primary key constraint is created, then the primary key constraint uses that index rather than implicitly creating a new one". I was under the false impression that set a PRIMARY KEY constraint on a table would create a unique index. Well, no, not necessarily. Oracle will reuse (thus not even create) a non-unique index for the constraint, when the columns on which the index is are the same as those for the primary key. This is also true for indexes having more columns than the primary key. As long as the columns used for the primary are the same as the first columns used for the index, the index will be reused. Unique or not.

Another thing to know is "Deferred Constraint Checking". From the Concepts guide :

You can defer checking constraints for validity until the end of the transaction.

• A constraint is deferred if the system checks that it is satisfied only on commit. If a deferred constraint is violated, then commit causes the transaction to undo.
• If a constraint is immediate (not deferred), then it is checked at the end of each statement. If it is violated, the statement is rolled back immediately.

What happens when a PRIMARY KEY is created as being DEFERRABLE ? Until the transaction is committed, it is allowed to have duplicate values. So a unique index cannot be used. Oracle has to use a non-unique index. Again, a primary key constraint is not necessarily ensured by a unique index.

The fun part is that :

• If a unique index was used by a primary key constraint and that constraint is dropped, the index will also be dropped !
• If a non-unique index was used by a primary key constraint and that constraint is dropped, the index will not be dropped.

References

• Primary Keys and Non-Unique Indexes (What's Really Happening ?) by Richard Foote
• What is the difference between a unique index and a unique constraint?
• Oracle documentation