re: DBSequence Table and PostGre

[Rainer Döbele <do...@esteam.de>]

Hi Eike,

What a pity, I hoped this would do the job.
However I am not quite sure, where the problem is.

The current implementation of the Postre driver creates the timestamp in java using GregorianCalendar.getTimeInMillis(). This value is used for inserting new records and the value should also be formatted using the SQL_DATETIME_PATTERN.

Hence the sql generated for an insert is something like:
INSERT INTO FOO(..., UPDATE_TIMESTAMP) VALUES (...,'2010-07-02 11:38:44.752');

Provided the records have been inserted this way an update with the corresponding timestamp constraint should work:
UPDATE FOO
SET ...
WHERE UPDATE_TIMESTAMP='2010-07-02 11:38:44.752'

I assume that the timestamps in your db have not been created this way. Rather the timestamp has been set by the db - which is usually a better anyway.
First you should consider overriding DBDatabaseDriver.getUpdateTimestamp() in order to get the timestamp from the db.
As an example you can have a look at the Oracle driver implementation.

Next it will be necessary to add the nanos to the sql command text (if possible). Another option would be to use a statement parameter.
In order to add the nanos to the sql command text I would first try to override getDateTimeString(), check if the supplied value is of type java.sql.Timestamp and then add the nanos.
This should then hopefully do the job.

As mentioned before I unfortunately do not have a Postgre database running here and I cannot really test it.
But if you find a good solution for this, then we would be grateful if you let us know what you have done.

Regards
Rainer


Eike Kettner wrote:
> Re: DBSequence Table and PostGre
> 
> Hi Rainer,
> 
> I tried your hint but it did not work out. The problem is that postgre
> really stores microseconds unless one does not restrict it. If I create
> the seqtime field as "timestamp(3) without time zone" and apply your
> date-time pattern ( ...mm:ss.SSS) it works, but by default postgre
> stores 6 precision digits. I think the java.sql.Timestamp can handle
> times up to nano-seconds, but I don't think the java.util.Date does (?).
> 
> At least I have not encountered a case where I really liked to have a
> more accurate timestamp than milliseconds, so one could let the
> postgresqldriver always create the restricted version of the timestamp
> ...
> 
> kind regards,
> Eike
> 
> 
> 
> On 29.06.2010 11:24, Eike Kettner wrote:
> > Hi Rainer,
> >
> > I did not know about  the SQL_DATETIME_PATTERN - this sounds very
> > promising. I will try this and let you know.
> >
> > I've still opened a jira issue for that, before I read this mail.
> >
> > Thank you and kind regards
> > Eike
> >
> > On 29.06.2010 10:00, Rainer Döbele wrote:
> >> Hello Eike,
> >>
> >> I have not started a deep investigation but I could imagine that
> >> supplying a modified SQL_DATETIME_PATTERN would solve this.
> >>
> >> Currently the function getSQLPhrase(int phrase) on
> >> DBDatabaseDriverPostgreSQL returns the following pattern which does
> >> not include milliseconds:
> >>
> >>              case SQL_DATETIME_PATTERN:        return "yyyy-MM-dd
> >> HH:mm:ss";
> >>
> >> you could now override getSQLPhrase() and add the milliseconds
> >> similar to this:
> >>
> >>              case SQL_DATETIME_PATTERN:        return "yyyy-MM-dd
> >> HH:mm:ss.SSS";
> >>
> >> This should hopefully add the milliseconds to the constraint.
> >>
> >> Let me know if it helped so that we can correct this in our
> >> implementation.
> >>
> >> Regards
> >> Rainer
> >>
> >>
> >> Eike Kettner wrote:
> >>> re: DBSequence Table and PostGre
> >>>
> >>> Hello there
> >>>
> >>> I think I found a bug in DBSeqTable#getNextValue():
> >>>
> >>> I use postgre sql and getting the next sequence value, the
> >>> getNextValue() goes into an endless loop. It fails when updating the
> >>> sequence value and therefore tries again and again and again...
> >>>
> >>> It cannot update the sequence because postgre sql stores milli and
> >>> nanoseconds within the timestamp. The WHERE clause from the update
> omit
> >>> the milli and nanoseconds. So it tries to update ... WHERE
> >>> timestamp='2010-06-10 14:22:24'  but in DB it is '2010-06-10
> >>> 14:22:24.21231'.  The update fails and the loop does never stop. It
> >>> would be great to be informed somehow if the loop goes beyound some
> big
> >>> value.  I did not dig into this deeper, I'm using a quick workaround
> >>> that saves the time as a long (override getNextValue()).
> >>>
> >>> kind regards,
> >>> Eike
> >>>
> >>>
> >>>
> >