[jira] [Commented] (ROL-2063) NextLink appears in latest entry of permalink in PostgreSQL

["Kohei Nozaki (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/ROL-2063?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14333173#comment-14333173 ] 

Kohei Nozaki commented on ROL-2063:
-----------------------------------

Also I'm curious whether this problem occurs on other databases such as MySQL. Roller uses DATETIME type which didn't store milliseconds.

> NextLink appears in latest entry of permalink in PostgreSQL
> -----------------------------------------------------------
>
>                 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
>         Attachments: ROL-2063.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)