Monday, March 28, 2011

PostgreSQL and (Index) Names

Creating an index in PostgreSQL can be achieved by executing the following SQL command:
ON "<tableName>" 
USING btree (<columnNames>);

There is one caveat, though. If you specify an index name that is longer than 63 characters PostgreSQL will truncate this and not tell you. By issuing the following command you can list all indexes present in the database:
SELECT * FROM pg_catalog.pg_indexes;

In general a limit of 63 characters should not be a problem. However, in my case I was working on a tool which generates index names and it hit this limit. Of course, now I’m using a different algorithm to generate the index names and the problem is solved.
I also suspect that this 63 character limit applies to other names as well. PostgreSQL has defined a type ‘name’ which has a size of 64 (including the terminating character). This type is used in several places and its definition info is available via the statement
FROM pg_catalog.pg_type
WHERE typname='name';

Personally I would prefer if PostgreSQL would reject the CREATE INDEX statement and instead return an error message.
Note: The above SQL statements may use PostgreSQL specific syntax, tables, views and functions. Other database systems may require a different syntax and may have different limitations on names. For my experiments I used PostgreSQL 9.0.2 (64bit) running on Windows 7.

All About Agile

I just added another entry to the “Suggested Links” section. There are quite a few sites about agile approaches in particular for software development. Kelly Waters has put a lot of effort in her site – “All About Agile” - over the last view years and I find the material and links to further information very valuable. Have a look and I’m sure you will find nuggets, too.

Friday, March 04, 2011

Cannot toggle breakpoint with F9 key

Embarrassing but I still would like to share this in case it drives you nuts ... well I even re-installed Visual Studio to resolve this. What happened?

All the sudden setting breakpoints using F9 stopped working. I had just finished installing service pack 1 for my OS so I had a prime suspect. Or so I thought. After removing one plug-in at a time, repairing Visual Studio 2010 and eventually reinstalling it, it still wouldn't work.

I don't know what made me check this but for some reason I found that some function keys would still do something. After some experimentation I found that my keyboard has a special key "F Lock" and after I pressed that all was back to normal. Normally keyboards don't have that key. However, I have a Microsoft natural ergonomics keyboard and it has that key. Apparently I must have had pressed the "F Lock" key accidentally. Oh, well! I guess it's Friday night and time to get some sleep ...