Have you had your application in production for awhile? Maybe you have a little traffic now and you’re starting to see some places where performance could be better? The easiest place to find gains that will benefit your entire application is usually in the database. Because the schema tends to be [...]

Postgres just released a 8.3.4 update a few days ago and when trying to update with a “yum update”, I got the following error:
–> Finished Dependency Resolution
Error: Missing Dependency: libossp-uuid.so.15 is needed by package postgresql-contrib
Interesting. Well libossp-uuid.so.15 is presumably used by the new-to-8.3 UUID datatype but I haven’t had this issue before. The [...]

In the same member merging process described in my last post, I was running a bit of code with a TRY/CATCH that looked for a duplicate primary key condition and, if found, deleted it from the source account:
<cfquery name=”select” datasource=”#variables.datasource.getName()#”>
SELECT *
FROM roles
WHERE id = ‘#id#’
</cfquery>
<cfloop query=”select”>
<cftry>
<cfquery name=”update” datasource=”#variables.datasource.getName()#”>
UPDATE roles
SET id = ‘#target#’
WHERE id = ‘#id#’
AND [...]

I use fairly vanilla SQL most of the time so I was shocked to read this post on the PostgreSQL list when I ran into some slowness with a NOT IN query. While my experience is with Postgres, apparently this works with most databases.
If you have a NOT IN with a subquery that returns [...]

Yesterday I started a post talking about PostgreSQL 8.3 RC2 and this morning I come in to find the official 8.3 release in my inbox! There are quite a few interesting new features, a few of which are specifically of interest to ColdFusion developers:

UUID datatype
ANSI-standard SQL/XML support
ENUM datatype

There is a page about UUID support [...]

If you skim the Transfer docs, like I do, you might miss the fact that specifying a relationship (onetomany, manytoone, manytomany) automatically creates support for the column you reference. This can cause maddening errors like the following:
Error Executing Database Query.
ERROR: column “dtype” specified more than once
transfer\com\sql\QueryExecution.cfc (108)
transfer\com\sql\TransferInserter.cfc (371)
transfer\com\sql\TransferInserter.cfc (132)
transfer\com\sql\TransferInserter.cfc (49)
transfer\com\sql\SQLManager.cfc (61)
transfer\com\Transfer.cfc (197)
transfer\com\Transfer.cfc (177)
If [...]

The SE-PostgreSQL team released their first full version today based on Postgres 8.2.4. Apparently it’s not a holiday in Japan where the primary contributors live.
SE-PostgreSQL is an interesting effort to combine the policies and controls of SE-Linux into the database engine to extend the access controls of the OS. It’s [...]

Based on my geocoded zip code database research, I wound up purchasing the ZipList Can-USA GeoCode database. Here’s what I did to get their 848,000-record CSV file into a PostgreSQL database table. First, create a table to hold the data:
CREATE TABLE tblLookupZipCode (
vchCity varchar(33) DEFAULT ” NOT NULL
,booDST boolean DEFAULT false NOT [...]

I’m preparing to migrate my application to a framework like Model-Glue. Because I have 70 tables and a large API already, I’m afraid of the cutting and pasting required to migrate my code to a Bean/DAO/Gateway structure. Brian Rinaldi’s Illudium PU-36 Code Generator would solve my problem except it didn’t support Postgres 8.x [...]

Quick tip - if you use PostgreSQL, you should look into the INTERVAL data type. It’s a great way of expressing periods of time without using actual dates and makes things like calendaring and recurring events very easy.
Unfortunately, ColdFusion’s CFQUERYPARAM doesn’t have support for the INTERVAL type so I had to hack around to [...]