[
https://issues.apache.org/jira/browse/ROL-2063?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14504582#comment-14504582
]
Glen Mazza commented on ROL-2063:
---------------------------------
It would seem like fixing the data model, rather than adding confusing/hacky
code, is the better solution. If storing time to the hundredths of a second
requires the data model to support thousandths of a second, go ahead and fix
the data model. Roller easily supports database upgrades, it has for years.
Maintaining a buggy database model just to avoid making database changes
doesn't help Roller's growth long-term, indeed it just speeds its obsolescence.
You might as well argue for keeping buggy code just to avoid making code
changes.
With a data model upgrade it is not necessary to test every single database
Roller supports (I normally just check between one and three: MySQL, Derby, and
PostgreSQL). We don't generally test MS SQL Server, DB2, and Oracle for
example. Instead you update the scripts with what seems most logical (where
there is a "2", make it a "3" in the upgrade scripts.) and if a user discovers
a syntax problem with our changes they'll contact us with a patch or an alert
for us to get it fixed.
> NextLink appears in latest entry of permalink
> ---------------------------------------------
>
> Key: ROL-2063
> URL: https://issues.apache.org/jira/browse/ROL-2063
> Project: Apache Roller
> Issue Type: Bug
> Components: Data Model & JPA Backend
> Affects Versions: 5.1.1
> Environment: PostgreSQL 9.3.4
> Reporter: Kohei Nozaki
> Assignee: Roller Unassigned
> Priority: Minor
> Fix For: 5.1.2
>
> Attachments: ROL-2063.patch, ROL-2063_alternative.patch,
> ROL-2063_refresh.patch, ROL-2063_truncate.patch,
> ROL-2063_truncate_update1.patch, ROL-2063_update1.patch
>
>
> -With PostgreSQL-, NextLink shouldn't be appeared in latest entry of
> permalink, but sometimes it appears as the link to latest entry itself.
> The cause is precision of the column WEBLOGENTRY.PUBTIME in PostgreSQL. it is
> created with the type "timestamp(2) with time zone" as specified in
> postgresql.properties. the problem occurs as follows.
> 1. Someone posts an entry. following SQL was executed:
> 2015-02-23 17:07:26 JST LOG: execute <unnamed>: INSERT INTO weblogentry (id,
> allowcomments, anchor, commentdays, content_src, content_type, creator, link,
> locale, pinnedtomain, plugins, pubtime, righttoleft, search_description,
> status, summary, text, title, updatetime, categoryid, websiteid) VALUES ($1,
> $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18,
> $19, $20, $21)
> 2015-02-23 17:07:26 JST DETAIL: parameters: $1 =
> '71837e6f-735b-4c7e-b498-6b432accdcb2', $2 = 't', $3 = 'test', $4 = '0', $5 =
> NULL, $6 = NULL, $7 = 'kyle', $8 = NULL, $9 = 'en_US', $10 = 'f', $11 = '',
> $12 = '2015-02-23 17:07:26.548+09', $13 = 'f', $14 = '', $15 = 'PUBLISHED',
> $16 = '', $17 = 'test', $18 = 'test', $19 = '2015-02-23 17:07:26.551+09', $20
> = 'fb216817-f8ea-46ec-aad9-35d9f222cac4', $21 =
> '78d076b8-19c2-4ee0-a766-2cdaf0b75d0d'
> 2. Someone visits the permalink of the entry just posted.
> JPAWeblogEntryManager#getNextEntry() is invoked and following SQL was
> executed and the query returns a row of current latest entry itself.
> 2015-02-23 17:07:34 JST LOG: execute <unnamed>: SELECT id AS a1,
> allowcomments AS a2, anchor AS a3, commentdays AS a4, content_src AS a5,
> content_type AS a6, creator AS a7, link AS a8, lo
> cale AS a9, pinnedtomain AS a10, plugins AS a11, pubtime AS a12, righttoleft
> AS a13, search_description AS a14, status AS a15, summary AS a16, text AS
> a17, title AS a18, updatetime AS a19,
> categoryid AS a20, websiteid AS a21 FROM weblogentry WHERE (((websiteid =
> $1) AND (status = $2)) AND (pubtime > $3)) ORDER BY pubtime ASC LIMIT $4
> OFFSET $5
> 2015-02-23 17:07:34 JST DETAIL: parameters: $1 =
> '78d076b8-19c2-4ee0-a766-2cdaf0b75d0d', $2 = 'PUBLISHED', $3 = '2015-02-23
> 17:07:26.548+09', $4 = '1', $5 = '0'
> It caused by the entry of PUBTIME is stored after rounded in the table as
> follows:
> roller2=# select title, pubtime FROM weblogentry WHERE (((websiteid =
> '78d076b8-19c2-4ee0-a766-2cdaf0b75d0d') AND (status = 'PUBLISHED')) AND
> (pubtime > '2015-02-23 17:07:26.548+09')) ORDER BY pubtime ASC LIMIT '1'
> OFFSET '0';
> title | pubtime
> -------+---------------------------
> test | 2015-02-23 17:07:26.55+09
> (1 row)
> Roller compared '2015-02-23 17:07:26.548+09' against '2015-02-23
> 17:07:26.55+09' and recognized current latest entry itself as next entry
> while PUBTIME was rounded '.548' to '.55'. I don't know where Roller keeps
> '2015-02-23 17:07:26.548+09' but I guess that it saved in caches in Roller or
> EclipseLink because restarting of application server solves the problem
> temporarily.
> To solve the problem, I think changing precision of TIMESTAMP_SQL_TYPE_NULL
> and TIMESTAMP_SQL_TYPE in /resources/sql/postgresql.properties from 2 to 3 is
> reasonable because Roller uses milliseconds precision with java.util.Date,
> larger precision is unnecessary though.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
