Bug #88827 for DBD-Pg: DBD-Pg-2.19.3 syntax error deallocating prepared statement

Thu Sep 19 18:25:39 2013 ssun [...] arubanetworks.com - Ticket created

Subject:	DBD-Pg-2.19.3 syntax error deallocating prepared statement
Date:	Thu, 19 Sep 2013 22:25:23 +0000
To:	"bug-DBD-Pg [...] rt.cpan.org" <bug-DBD-Pg [...] rt.cpan.org>
From:	Spencer Sun <ssun [...] arubanetworks.com>

We have seen in very long-running processes where imp_dbh->prepare_number rolls over into negative number territory. This then tickles the same sort of bug that was addressed in bug #34738. Dec 12 14:49:31 localhost postgres[2607]: [10241-1] ERROR: 42601: syntax error at or near "-" at character 24 Dec 12 14:49:31 localhost postgres[2607]: [10241-2] LOCATION: scanner_yyerror, scan.l:1001 Dec 12 14:49:31 localhost postgres[2607]: [10241-3] STATEMENT: DEALLOCATE dbdpg_p2606_-2147474143 A proposed diff is attached which uses %x instead of %d.

Thu Sep 19 18:27:01 2013 ssun [...] arubanetworks.com - Correspondence added

Subject:	RE: [rt.cpan.org #88827] AutoReply: DBD-Pg-2.19.3 syntax error deallocating prepared statement
Date:	Thu, 19 Sep 2013 22:26:52 +0000
To:	"bug-DBD-Pg [...] rt.cpan.org" <bug-DBD-Pg [...] rt.cpan.org>
From:	Spencer Sun <ssun [...] arubanetworks.com>

So of course I forgot the attachment. Anyway here is the diff Index: dbdimp.c --- dbdimp.c.orig 2012-12-18 16:01:59.000000000 -0800 +++ dbdimp.c 2012-12-18 16:02:10.000000000 -0800 @@ -2090,7 +2090,7 @@ Renew(imp_sth->prepare_name, 25, char); /* freed in dbd_st_destroy */ /* Name is "dbdpg_xPID_#", where x is 'p'ositive or 'n'egative */ - sprintf(imp_sth->prepare_name,"dbdpg_%c%d_%d", + sprintf(imp_sth->prepare_name,"dbdpg_%c%d_%x", (imp_dbh->pid_number < 0 ? 'n' : 'p'), abs(imp_dbh->pid_number), imp_dbh->prepare_number);

Fri Sep 20 09:51:42 2013 greg [...] turnstep.com - Taken

Sat Sep 21 22:17:49 2013 greg [...] turnstep.com - Correspondence added

Using %x is a good idea, but it won't really solve this issue. The wraparound will still occur, in that it will wrap to -1, which gets mapped to fff..., but then we increment it again, so it goes to 0, then once more and bam! we get an error from Postgres about prepared statement name already exists. Thus, we need to bump it up from an int to a long or something.

Sat Sep 21 22:17:50 2013 The RT System itself - Status changed from 'new' to 'open'

Sat Sep 21 22:30:02 2013 greg [...] turnstep.com - Correspondence added

Ah, nevermind, we still have a 2 billion additional buffer cause we jump to -2B, not -1! I need more sleep. :)

Sat Sep 21 22:36:45 2013 greg [...] turnstep.com - Correspondence added

Thanks very much for the patch. Applied in ce90c65fdc143eb6a564128368008138686a4060 Should be a part of the next release.

Sat Sep 21 22:36:46 2013 greg [...] turnstep.com - Status changed from 'open' to 'patched'

Sun Sep 22 14:48:22 2013 ssun [...] arubanetworks.com - Correspondence added

Subject:	RE: [rt.cpan.org #88827] DBD-Pg-2.19.3 syntax error deallocating prepared statement
Date:	Sun, 22 Sep 2013 18:48:05 +0000
To:	"bug-DBD-Pg [...] rt.cpan.org" <bug-DBD-Pg [...] rt.cpan.org>
From:	Spencer Sun <ssun [...] arubanetworks.com>

Cool, thanks! BTW just to complete the thought, I don't think there will be a problem with prepared statement name already exists -- the nature of the bug is that due to quoting or whatever, the unpatched DBD::Pg is successfully *creating* statement names with negative numbers in them, but runs into a syntax error when it tries to deallocate them. The patch enables them to be successfully deallocated because the minus sign is no longer an issue.

Sun Sep 22 15:00:40 2013 greg [...] turnstep.com - Correspondence added

Ha! We probably could have fixed this by simply quoting the name. Ohwell. :)

Wed Feb 05 21:51:50 2014 greg [...] turnstep.com - Status changed from 'patched' to 'resolved'

Wed Feb 05 21:51:51 2014 greg [...] turnstep.com - Severity Normal added

Wed Feb 05 21:51:51 2014 greg [...] turnstep.com - Fixed in 3.0.0 added