DBI now correctly quotes PostgreSQL

I ran into an interesting problem while upgrading the production FreshPorts server. Specifically, this error:

select PortsUpdatingAdd(E'20080708'::date,
E'users of emulators/qemu-devel', E'nox@FreeBSD.org', EE'
  The port has been updated to a 20080620 SVN snapshot, which uses the
  new (incompatible) kqemu-kmod-devel port that now also works for the
  32 bit "qemu" executable on amd64.  If you upgrade qemu-devel with
the
  KQEMU knob on, you have to

	# pkg_delete kqemu-kmod-1\\*

  the old kqemu version first if it is installed.

');
WARNING:  nonstandard use of \\ in a string literal
LINE 2: E'users of emulators/qemu-devel', E'nox@FreeBSD.org', EE'
                                                                ^
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
ERROR:  type "ee" does not exist
LINE 2: E'users of emulators/qemu-devel', E'nox@FreeBSD.org', EE'

The error is clear. I have EE where I should have E, as shown in the PostgreSQL Escape string documentation. Interestingly enough, the commit ran fine in development, but not on this new server.

I started printing out debugging strings. Eventually I tracked it down to this line:

my $description     = $self->{dbh}->quote($Updates{log});

My first guess was: Ahh, DBI is doing better quoting of PostgreSQL.

Asking some questions on IRC supported my guess:

<dvl> I’m beginning to think that the perl DBI now correctly quotes for PostgreSQL (i.e. E”). Anyone concur?
<davidfetter> dvl, 2.9.2 is pretty good on that :)
<linuxpoet> yes I believe so

Then I went to compare the package version on the machines in question:

production

$ pkg_info | grep DBI
p5-DBD-Pg-2.9.0     Provides access to PostgreSQL databases through the DBI
p5-DBI-1.60.4       The perl5 Database Interface.  Required for DBD::* modules

development

p5-DBI-1.59     Provides access to PostgreSQL databases through the DBI
p5-DBD-Pg-1.49       The perl5 Database Interface.  Required for DBD::* modules

This was enough for me to upgrade the development environment (yes, they should always match, shouldn’t they?).

When did this change take place? According to the change log, May 2008:

2.7.0 Released May 10, 2008 (subversion r11239)

	- Have $dbh->quote() return E'' when server is >= 8.1 and string contains 
		backslashes. Fixes any problems with standard_conforming_strings.
		(CPAN bug #27538) [GSM]

OK, this confirms the issue. I know where the ‘problem’ is. Now it’s time to fix the FreshPorts so it takes advantage of $dbh->quote() instead of invoking its own function, db_escape().

I’ve made the changes to the code on my development server, but we’ll have to wait and see how that works out over the next few days before moving it to the BETA server (which is now the main server, aka production).