[jira] [Created] (OPENJPA-2905) Sequences are altered unnecessarily

["Pawel Veselov (Jira)" <ji...@apache.org>]

Pawel Veselov created OPENJPA-2905:
--------------------------------------

             Summary: Sequences are altered unnecessarily
                 Key: OPENJPA-2905
                 URL: https://issues.apache.org/jira/browse/OPENJPA-2905
             Project: OpenJPA
          Issue Type: Bug
          Components: jdbc
    Affects Versions: 3.2.2, 3.2.1, 3.2.0, 3.1.2, 3.1.1, 3.1.0
            Reporter: Pawel Veselov


{{NativeJDBCSeq.allocateInternal()}} attempts to alter a sequence for every sequence encountered at least once during a runtime.

In Postgres, for example, executing this DDL requires that no other locks are held, we are seeing this blockage:

{noformat}
blocked_pid                           | 12519
blocked_user                          | snapstore
blocking_pid                          | 29611
blocking_user                         | snapstore
blocked_statement                     | ALTER SEQUENCE campaign_seq INCREMENT BY 50
current_statement_in_blocking_process | select id from other_lock where id = $1 for share 
{noformat}

It doesn't make sense to alter the sequence if the sequence already has the right increment, otherwise there are the following issues:

1. In a cluster, the alterations are going to be attempted by each node
2. If there is a sudden request, in the middle of operations, to get a sequence value for some rarely written to table, that sequence can be held up for a long time, unwarranted so, at least in Progress

I really recommend that the sequence increment is first checked, and is only changed if it doesn't match the expectation, at least on the databases where this is possible.




--
This message was sent by Atlassian Jira
(v8.20.10#820010)