org.quartz.impl.jdbcjobstore.LockException: Failure obtaining db row lock: ERROR: relation "tsp_qrtz_locks" does not exist

[Former user]

For postgres database, the tables created are all lowercase and the way postgres works is that the table needs to be wrapped in quotes if you want them to be in uppercase. The side effect is that the constants are trying to use upppercase when in postgres it translates to lowercase. If you change all the constant names to lowercase in QuartzSchedulerConstants then it works. The real solution I imagine would be to wrap these names in quotes when dealing with postgres.

[Christopher Pence]

Also, this was for version 3.2.4

[jost]

Hello,

Thank you for this note, we will try to include it in the next version (The Scheduler 3.2.6). Feel free to reopen this issue if you need further informations.

Cheers

[Bartosz Gilewski]

Hello,

We need more information about this issue. Could you include logs with the errors here or send them to the [e-mail] ?

Regards

[Christopher Pence]

This is the error in the log:

org.quartz.impl.jdbcjobstore.LockException: Failure obtaining db row lock: ERROR: relation "tsp_qrtz_locks" does not exist

The plugin is creating all lowercase tables in postgres.

[Christopher Pence]

For example, in "QuartzConnectionProvider.java" the following code fails:

/ 95 / sql.append(QuartzSchedulerConstants.TSP_QUARTZ_TABLE_PREFIX).append(tableName); / 96 / sql.append(" AS (SELECT * FROM "); / 97 / sql.append(QuartzSchedulerConstants.JIRA_QUARTZ_TABLE_PREFIX).append(tableName).append(")");

This is because when the Contant gets inserted here it is inserting the UPPERCASE value, however postgres will automatically convert it to lower case if the table name is not enclosed in doublequotes.

Here's a forum I found with an example:

If you specify an object's name without quoting it, e.g.:

ALTER TABLE MyTable ADD COLUMN MyNewCol INT4;

then it is read as all lower case. This means, that table "mytable" will be altered, and the new column will be called "mynewcol".

To force mixed or upper case, you quote the names, e.g.:

ALTER TABLE "MyTable" ADD COLUMN "MyNewCol" INT4;

This will alter only a table called "MyTable", and table "mytable" will not be altered. The new column will be called "MyNewCol".

[Paweł Kulicki]

Hello,

Unfortunately we cannot reproduce this bug. Maybe this is the problem with the version or configuration of Postgres. We tested it on Postgres 9.3.5, however we fixed it according to your guidelines. Could you please download version 3.2.6, test and check if it works.

Best Regards,

Pawel

[Christopher Pence]

3.2.6 did not fix this issue. It is still trying to use lowercase table names.

[Bartosz Gilewski]

Hello,

We are still struggling with your issue, and we cannot reproduce it on our environment. We agree that Postrgess converts value (table names) taken from Constants to lowercases but by default tables in postgress also are in lowercases. We performed different tests and it always worked no matter if we have these tables with small letters or if we change these names manually to capital letters.

Nevertheless we have one more assumption: could you please check if table "tsp_qrtz_locks" exists in your database (no matter if in lower or capital cases), because we had in the past one issue when tables weren't initialized properly.

Regards

[Bartosz Gilewski]

Hello,

Have you still got problems with this issue?

Regards