[COMMENTING-23] getForUpdate on PostgreSQL throws exception Created: 29/May/20  Updated: 02/Jun/20  Resolved: 02/Jun/20

Status: Closed
Project: Commenting
Component/s: None
Affects Version/s: None
Fix Version/s: 1.0

Type: Bug Priority: Major
Reporter: Edwin Guilbert Assignee: Riste Drangovski
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

PostgreSQL 12.3-1.pgdg100+1


Attachments: Text File postgresException.log    
Template:
Acceptance criteria:
Empty
Task DoD:
[ ]* Doc/release notes changes? Comment present?
[ ]* Downstream builds green?
[ ]* Solution information and context easily available?
[ ]* Tests
[ ]* FixVersion filled and not yet released
[ ]  Architecture Decision Record (ADR)
Bug DoR:
[ ]* Steps to reproduce, expected, and actual results filled
[ ]* Affected version filled
Sprint: Scrum Sprint 1

 Description   

When trying to like or dislie or report o run any update operation on comment entity, the following exception is thrown by info.magnolia.commenting.service.CommentingServiceImpl.getForUpdate(long):

javax.persistence.PersistenceException: Query threw SQLException:ERROR: FOR UPDATE cannot be applied to the nullable side of an outer join Bind values:[4, ] Query was:select t0.id, t0.mgnl_workspace, t0.mgnl_id, t0.text, t0.user_name, t0.user_email, t0.author_choice, t0.abuse_report, t0.num_likes, t0.num_unlikes, t0.rating, t0.version, t0.created, t0.created_by, t0.modified, t0.modified_by, t2.id, t2.mgnl_workspace, t2.mgnl_id, t2.text, t2.user_name, t2.user_email, t2.author_choice, t2.abuse_report, t2.num_likes, t2.num_unlikes, t2.rating, t2.version, t2.created, t2.created_by, t2.modified, t2.modified_by, t2.parent_id, t1.id, t1.mgnl_workspace, t1.mgnl_id, t1.text, t1.user_name, t1.user_email, t1.author_choice, t1.abuse_report, t1.num_likes, t1.num_unlikes, t1.rating, t1.version, t1.created, t1.created_by, t1.modified, t1.modified_by, t1.parent_id from comments t0 left join comments t2 on t2.id = t0.parent_id  left join comments t1 on t1.parent_id = t0.id  where t0.id = ?   order by t0.id for update

Full exception log attached.



 Comments   
Comment by Edwin Guilbert [ 29/May/20 ]

Changing the fetch method (info.magnolia.commenting.service.CommentingServiceImpl.getForUpdate(long)):

Comment fetchedComment = getDb().find(Comment.class).setDisableLazyLoading(true)
                //will hold a lock on the rows fetched.
                .forUpdate()
                .fetch("parent")
                .fetch("comments")
                .where().eq("id", commentId)
                .findOne();

for fetchLazy method, seems to fix it (avoiding the outer joins not supported by postgres):

Comment fetchedComment = getDb().find(Comment.class).setDisableLazyLoading(true)
                //will hold a lock on the rows fetched.
                .forUpdate()
                .fetchLazy("parent")
                .fetchLazy("comments")
                .where().eq("id", commentId)
                .findOne();

 its inspired on the solution provided in the link:

You can bypass this error with joining the tables with FetchType.LAZY. This fetch type is the default one and it is not required to specify for @OneToMany joins.

 

Generated at Mon Feb 12 00:01:24 CET 2024 using Jira 9.4.2#940002-sha1:46d1a51de284217efdcb32434eab47a99af2938b.