It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Is anyone using PGPOOL for JIRA, Confluence and Bitbucket data center editions Edited

We are getting db exceptions as below during issue creations. This is intermittent error and not occurring always. 

"2019-01-10 01:38:38,714 http-nio-8080-exec-9 ERROR XXXXX 98x109961x1 c5x1a 10.23.147.254 /secure/QuickCreateIssue.jspa [c.a.j.bc.issue.DefaultIssueService] Error creating issue:
com.atlassian.jira.exception.CreateException: Could not create workflow instance: root cause: while inserting: [GenericEntity:OSWorkflowEntry][name,Software Simplified Workflow for Project XXXXXX][id,51276][state,0] (SQL Exception while executing the following:INSERT INTO public.OS_WFENTRY (ID, NAME, INITIALIZED, STATE) VALUES (?, ?, ?, ?) (ERROR: duplicate key value violates unique constraint ""pk_os_wfentry""
  Detail: Key (id)=(51276) already exists.))

 

Also we are seeing below DB exception in the logs. 

2019-01-11 09:46:37,416 http-nio-8080-exec-12 ERROR XXXXXX 586x525577x1 do0rdz 10.23.147.254 /secure/QuickCreateIssue.jspa [c.a.j.quickedit.action.QuickCreateIssue] com.atlassian.plugins.rest.common.json.JsonMarshallingException: org.codehaus.jackson.map.JsonMappingException: Caught PSQLException for select ACTION.id, ACTION.issueid, ACTION.author, ACTION.actiontype, ACTION.actionlevel, ACTION.rolelevel, ACTION.actionbody, ACTION.created, ACTION.updateauthor, ACTION.updated, ACTION.actionnum
    from public.jiraaction ACTION
    where ACTION.issueid = ? and ACTION.actiontype = ?
    order by ACTION.created asc, ACTION.id asc
    limit ? (through reference chain: com.atlassian.jira.quickedit.rest.api.field.QuickEditFields["createdIssueDetails"]->com.atlassian.jira.rest.v2.issue.IssueBean["fields"]->java.util.HashMap["comment"])
com.atlassian.plugins.rest.common.json.JsonMarshallingException: org.codehaus.jackson.map.JsonMappingException: Caught PSQLException for select ACTION.id, ACTION.issueid, ACTION.author, ACTION.actiontype, ACTION.actionlevel, ACTION.rolelevel, ACTION.actionbody, ACTION.created, ACTION.updateauthor, ACTION.updated, ACTION.actionnum
from public.jiraaction ACTION
where ACTION.issueid = ? and ACTION.actiontype = ?
order by ACTION.created asc, ACTION.id asc
limit ? (through reference chain: com.atlassian.jira.quickedit.rest.api.field.QuickEditFields["createdIssueDetails"]->com.atlassian.jira.rest.v2.issue.IssueBean["fields"]->java.util.HashMap["comment"])
	at com.atlassian.plugins.rest.common.json.DefaultJaxbJsonMarshaller.marshal(DefaultJaxbJsonMarshaller.java:57)
...
Caused by: org.postgresql.util.PSQLException: ERROR: prepared statement "S_1" already exists

Have you seen these type of errors? If yes, what was the resolution to fix them? Also, as we are using PGPOOL (Officially not supported by Atlassian) we need to your help to identify the best practices to use PGPOOL with Atlassian data center editions. 

 

1 answer

Are you running an Active/Active Postgres cluster? If not, my recommendation would be to not use PGPOOL.  It really doesn't add any value unless you're Active/Active.  

We most commonly set up Postgres clusters as Active/Passive and use Repmgr to handle the replication.  It can be tuned to failover quickly.  We've seen slave promotion to master typically in under 20 seconds. If you have a VIP database IP and use keepalived to move the IP to the active node, you should expect a complete failover in under 30 seconds. It's a pretty simple, elegant solution that doesn't require a Postgres expert to manage, as well.

This also has the benefit of bringing you closer to Atlassian Support's ability to help you.

Hi Dave,

We are using Active/Active Postgres cluster. Can you suggest in this case?

Suggest an answer

Log in or Sign up to answer
This widget could not be displayed.
This widget could not be displayed.
Community showcase
Published Apr 09, 2019 in Portfolio for Jira

Portfolio for Jira 3.0 is here!

The wait is over... Portfolio for Jira Server and Data Center 3.0 is now officially here! Platform releases offer Atlassian an opportunity to shift our strategy, make bold predictions about t...

1,391 views 14 26
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you