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

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

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

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

To eliminate the problem completely I think we have following solutions by now:

# Extending precision of TIMESTAMP (ROL-2063.patch way): Most straightforward and elegant. but we are likely to avoid changing of data model, and we need to create patches and need to test against all of supporting databases.
# Rounding milli-seconds precision of TIMESTAMP before saving (ROL-2063_truncate.patch): It won't need any changes to data model. but some users (who uses TIMESTAMP in their database, such as PostgreSQL) will lost that precision anyway (I'm not sure whether there are someone depends on milli-seconds precision of WEBLOGENTRY.PUBTIME though).

Additionaly I think current approach (invoking WeblogEntryManager#evict() after Weblogger#flush()) has a problem that some visitors may still possibly see wrong link, for who visited the permalink in a very short amount of time between invocations of Weblogger#flush() and WeblogEntryManager#evict(). I thought that adding invocation of WeblogEntryManager#evict() after every invocation of WeblogEntryManager#saveWeblogEntry() would be an another solution, but I excluded it from options due to this.

So now I prefer ROL-2063_truncate.patch. what do you think? I'll try to make a commit of ROL-2063_truncate.patch to the SVN in a several weeks, so I'd appreciate if someone give me any feedback before.

> 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_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)