Обсуждение: Updating from 8.2 to 8.4
I was hoping to finally get the servers updated from 8.2 to 8.4 over the festive season, but by now I think I've left things too tight. Is it necessary to update the (Windows) ODBC driver as well? I've got a couple of app servers still on NT4 with ODBC 8.02.0100 which I'm trying to be careful with lest I put something on them which requires a later OS and can't back out. The apps are written in a rather old version of Delphi with BDE which is fine with 8.2. Trying to run against 8.4 I get "Couldn't perform the edit because another user changed the record."- looking back through this and other MLs I see suggestion that this could be caused by an excessive number of decimal places in the data (current case only contains integers, timestamps and text) or possibly by a transaction isolation issue. I'll carry on hacking at this from the app side but is there anything obvious that I've missed? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues]
On Thursday 24 December 2009 11:20:35 am Mark Morgan Lloyd wrote: > I was hoping to finally get the servers updated from 8.2 to 8.4 over the > festive season, but by now I think I've left things too tight. > > Is it necessary to update the (Windows) ODBC driver as well? I've got a > couple of app servers still on NT4 with ODBC 8.02.0100 which I'm trying > to be careful with lest I put something on them which requires a later > OS and can't back out. > > The apps are written in a rather old version of Delphi with BDE which is > fine with 8.2. Trying to run against 8.4 I get "Couldn't perform the > edit because another user changed the record."- looking back through > this and other MLs I see suggestion that this could be caused by an > excessive number of decimal places in the data (current case only > contains integers, timestamps and text) or possibly by a transaction > isolation issue. My experience with this is it related to timestamps with fractional second precision. The other thing to note is that in 8.4 the default for datetimes is now 64-bit integer datetimes, not the previous float datetimes. > > I'll carry on hacking at this from the app side but is there anything > obvious that I've missed? > > -- > Mark Morgan Lloyd > markMLl .AT. telemetry.co .DOT. uk > > [Opinions above are the author's, not those of his employers or colleagues] -- Adrian Klaver aklaver@comcast.net
Adrian Klaver wrote: > On Thursday 24 December 2009 11:20:35 am Mark Morgan Lloyd wrote: >> I was hoping to finally get the servers updated from 8.2 to 8.4 over the >> festive season, but by now I think I've left things too tight. >> >> Is it necessary to update the (Windows) ODBC driver as well? I've got a >> couple of app servers still on NT4 with ODBC 8.02.0100 which I'm trying >> to be careful with lest I put something on them which requires a later >> OS and can't back out. >> >> The apps are written in a rather old version of Delphi with BDE which is >> fine with 8.2. Trying to run against 8.4 I get "Couldn't perform the >> edit because another user changed the record."- looking back through >> this and other MLs I see suggestion that this could be caused by an >> excessive number of decimal places in the data (current case only >> contains integers, timestamps and text) or possibly by a transaction >> isolation issue. > > My experience with this is it related to timestamps with fractional second > precision. The other thing to note is that in 8.4 the default for datetimes is > now 64-bit integer datetimes, not the previous float datetimes. Many thanks for that Adrian. I notice this in the ODBC release notes which could be relevant: -----8<----- psqlODBC 08.04.0100 Release .. 8.) Remove a spurious "." with no trailing digits in timestamp representation (bug report [#1010540] from Brian Feldman). ----->8----- although I don't know why it didn't bite on 8.2 unless it's specifically when 64-bit timestamps are processed. I think that I'll try an ODBC update on a non-critical system, if that doesn't work I'll dig into my code. The table giving problems is part of a scheduling program where I can probably truncate timestamps, elsewhere it might not be so convenient. I'll report back if I find anything interesting. The sooner I can get this code off Delphi+BDE+ODBC+Windows to Lazarus+Linux the better. Happy Christmas everybody, whatever country you're in and whatever your tradition of celebration. We run 24x365 here but the caterers are hard at work :-) -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues]
On Friday 25 December 2009 5:06:28 am Mark Morgan Lloyd wrote: > Adrian Klaver wrote: > > On Thursday 24 December 2009 11:20:35 am Mark Morgan Lloyd wrote: > >> I was hoping to finally get the servers updated from 8.2 to 8.4 over the > >> festive season, but by now I think I've left things too tight. > >> > >> Is it necessary to update the (Windows) ODBC driver as well? I've got a > >> couple of app servers still on NT4 with ODBC 8.02.0100 which I'm trying > >> to be careful with lest I put something on them which requires a later > >> OS and can't back out. > >> > >> The apps are written in a rather old version of Delphi with BDE which is > >> fine with 8.2. Trying to run against 8.4 I get "Couldn't perform the > >> edit because another user changed the record."- looking back through > >> this and other MLs I see suggestion that this could be caused by an > >> excessive number of decimal places in the data (current case only > >> contains integers, timestamps and text) or possibly by a transaction > >> isolation issue. > > > > My experience with this is it related to timestamps with fractional > > second precision. The other thing to note is that in 8.4 the default for > > datetimes is now 64-bit integer datetimes, not the previous float > > datetimes. > > Many thanks for that Adrian. I notice this in the ODBC release notes > which could be relevant: > > -----8<----- > psqlODBC 08.04.0100 Release > .. > 8.) Remove a spurious "." with no trailing digits in timestamp > representation (bug report [#1010540] from Brian Feldman). > ----->8----- > > although I don't know why it didn't bite on 8.2 unless it's specifically > when 64-bit timestamps are processed. You might want to confirm your 8.4 installation is using integer datetimes as there is some variability in its useage among packagers. Follow this thread for one such discussion: http://archives.postgresql.org/pgsql-general/2009-07/msg01119.php -- Adrian Klaver aklaver@comcast.net
Adrian Klaver wrote: >> although I don't know why it didn't bite on 8.2 unless it's specifically >> when 64-bit timestamps are processed. > > You might want to confirm your 8.4 installation is using integer datetimes as > there is some variability in its useage among packagers. Follow this thread for > one such discussion: > http://archives.postgresql.org/pgsql-general/2009-07/msg01119.php Thanks Adrian, noted. I'm hoping to get onto 8.4 before too long because of the analytic functions but that thread suggests that the best course of action would be to make sure that my code is robust against the new server and then decide on an upgrade schedule. I'm building from scratch here so all options are as default except for added Perl. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues]
On Friday 25 December 2009 10:27:09 am Mark Morgan Lloyd wrote: > Adrian Klaver wrote: > >> although I don't know why it didn't bite on 8.2 unless it's specifically > >> when 64-bit timestamps are processed. > > > > You might want to confirm your 8.4 installation is using integer > > datetimes as there is some variability in its useage among packagers. > > Follow this thread for one such discussion: > > http://archives.postgresql.org/pgsql-general/2009-07/msg01119.php > > Thanks Adrian, noted. I'm hoping to get onto 8.4 before too long because > of the analytic functions but that thread suggests that the best course > of action would be to make sure that my code is robust against the new > server and then decide on an upgrade schedule. > > I'm building from scratch here so all options are as default except for > added Perl. > > -- Alright then, nothing worse then trying to fix a problem that does not exist :) One other thing that came to mind in regards to the the 'changed record' problem is whether Row Versioning has been checked in the ODBC connection parameters? -- Adrian Klaver aklaver@comcast.net
Adrian Klaver wrote: >> Thanks Adrian, noted. I'm hoping to get onto 8.4 before too long because >> of the analytic functions but that thread suggests that the best course >> of action would be to make sure that my code is robust against the new >> server and then decide on an upgrade schedule. >> >> I'm building from scratch here so all options are as default except for >> added Perl. >> >> -- > > Alright then, nothing worse then trying to fix a problem that does not exist :) "When a programmer has a hard time fixing a bug it's because he's looking in the wrong place." :-) > One other thing that came to mind in regards to the the 'changed record' > problem is whether Row Versioning has been checked in the ODBC connection > parameters? No. However once I got to the point that a development machine was looking at the new server I could reproduce the problem and (appear to have) fixed it: the app was a scheduler running on a Windows system and contained a lot of pathnames with backslashes hence standard_conforming_strings = on. I'll play with the new system for a few days and if all goes well try to migrate on New Year's Day. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues]