Invalid column reference: 7 ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

Dear

As it is a biannual journal, after some time without publishing, we gather the articles for a new volume, I click publish and the errors listed below appear.

A new volume is published, but without the authors and articles.

Server Settings:
postgresql13-server-13.4
Apache-2.4.51
php-73
Ojs-3.3.07

How could I fix this error?

Best regards,
Augusto Torres

Webserver Log:
there is no unique or exclusion constraint matching the ON CONFLICT specification in /usr/local/www/data/eprints/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:115\nStack
trace:\n#0 /usr/local/www/data/eprints/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php(115): PDOStatement->execute(NULL)\n#1
/usr/local/www/data/eprints/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(489): Doctrine\DBAL\Driver\PDOStatement->execute()\n#2
/usr/local/www/data/eprints/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(664): Illuminate\Database\Connection->Illuminate\Database\{closure}(‘insert into "cu…’,
Array)\n#3 /usr/local/www/data/eprints/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(631): Illuminate\Database\Connection->runQueryCallback(‘insert into "cu…’,
Array, Object(Closure))\n#4 /usr/local/www/data/eprints/li in /usr/local/www/data/eprints/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 671,
referer: https://webserver.br/eprints/index.php/kant-e-prints/manageIssues

Database Server Log:
postgres[37226]: [7-1] 2021-10-14 10:18:45.656 -03 [37226] ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
postgres[37226]: [7-2] 2021-10-14 10:18:45.656 -03 [37226] STATEMENT: insert into “custom_issue_orders” (“issue_id”, “journal_id”, “seq”) values ($1, $2, $3) on
conflict (“issue_id”, “journal_id”) do update set “issue_id” = “excluded”.“issue_id”, “journal_id” = “excluded”.“journal_id”, “seq” = “excluded”.“seq”

This has gone quite a while with no response. I’m seeing it too. Any solution?

Dear @drboone,

I found this error to be present using the full process, where part of the form entries entered by the editor are lost during the publishing process.

Unfortunately, I have not received any feedback about this error. I partially solved it using the Quick Submit plugin, where these errors do not occur.

Next February, I will have a new large volume to publish and I will have an opportunity to look further at where these errors occur in the system. I believe it happened during some version migration, where the indexes did not happen as expected.

If you have news, post it here.

Happy 2022 to you and the entire PKP.Org team!!!
Augusto Torres

Augusto,

Do you think the lost form entries are something the user does, or does OJS misplace them while handling the form submit?

Hi all,

Can anyone provide step-by-step instructions on how to reproduce this problem?

Thanks,
Alec Smecher
Public Knowledge Project Team

Alec,

Our journals person says she does this:

  • Dashboard > Issues > Future Issues > Issue 3
  • Click the triangle to expand > Publish Issue
  • Check box for Send email to all registered users, click Yes

Then it spins in the popup:
Screen Shot 2022-01-04 at 6.23.46 PM

The log entries from this attempt:

Stack trace:
#0 /usr/local/hnetojs/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php(115): PDOStatement->execute(NULL)
#1 /usr/local/hnetojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(489): Doctrine\DBAL\Driver\PDOStatement->execute()
#2 /usr/local/hnetojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(664): Illuminate\Database\Connection->Illuminate\Database\{closure}('insert into "cu...', Array)
#3 /usr/local/hnetojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(631): Illuminate\Database\Connection->runQueryCallback('insert into "cu...', Array, Object(Closure))
#4 /usr/local/hnetojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Data in /usr/local/hnetojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 671
PHP Fatal error:  Uncaught PDOException: SQLSTATE[42P10]: Invalid column reference: 7 ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification in /usr/local/hnetojs/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:115

PHP 7.4
Postgres 13.3
OJS 3.3.0-3

Hi all,

Could someone encountering this issue try applying a patch for bug 6828?

I think this might already address the problem.

Regards,
Alec Smecher
Public Knowledge Project Team

I’ve applied the attached diff. It seems to make no difference in the logged errors.

Why does the forum not allow diffs to be attached!?

TocGridHandler.inc.php
index f707dbe7c2b..ae72dc3d424 100644
--- a/controllers/grid/toc/TocGridHandler.inc.php
+++ b/controllers/grid/toc/TocGridHandler.inc.php
@@ -187,7 +187,11 @@ function setDataElementSequence($request, $sectionId, $gridDataElement, $newSequ
                if (!$sectionDao->customSectionOrderingExists($issue->getId())) {
                        $sectionDao->setDefaultCustomSectionOrders($issue->getId());
                }
-               $sectionDao->updateCustomSectionOrder($issue->getId(), $sectionId, $newSequence);
+               if ($sectionDao->getCustomSectionOrder($issue->getId(), $sectionId)) {
+                       $sectionDao->updateCustomSectionOrder($issue->getId(), $sectionId, $newSequence);
+               } else {
+                       $sectionDao->insertCustomSectionOrder($issue->getId(), $sectionId, $newSequence);
+               }
        }
 
        /**

Hi @drboone,

Thanks, that’s helpful; I’ve tracked down the issue to a PostgreSQL-specific expectation around indexes.

I’ve filed it and posted a patch here:

If you can confirm whether this resolves it, I’d be grateful!

Regards,
Alec Smecher
Public Knowledge Project Team

I applied this patch manually because it is so trivial, but I think it would apply just fine via patch.

With it applied, the issue published promptly and apparently without issue. We’ll do a bit more review to be sure eveything’s ok.

1 Like

Dear all,
Sorry I didn’t reply previously, I’m on vacation and I’m restricting access to computers.

Details about the reported bug are described at the beginning of this track (first post), similar to the one reported by Dennis @drboone

I applied the fix (only this one of the issue to PostgreSQL, as suggested Resolve custom issue ordering error with PostgreSQL on publish · Issue #7590 · pkp/pkp-lib · GitHub
of [‘issue_id’, ‘journal_id’]
to [‘issue_id’]
on line 699 of the classes / issue / IssueDAO.inc.php file

I did a test run, creating a new edit and inserting an article using Quick Submit and no failures were found. I’m asking the editor to run the tests using the full submission process if this error persists and will post it here shortly.

Sincerely,
Augusto Torres

1 Like

This topic was automatically closed after 27 days. New replies are no longer allowed.