It has however been installed just with the errors above. Which I assume means it cant be updated ?

The same error is true for other extensions with postgres

This comment was created with the J!Tracker Application at http://issues.joomla.org/.

I reported this bug in the past, http://joomlacode.org/gf/project/joomla/tracker/&action=TrackerItemEdit&tracker_item_id=24428/?action=TrackerItemEdit&tracker_item_id=33684&start=8775.

It is caused by duplicated ID. PostgeSQL tries to insert the new row with primary key's value is 1 which already exists (after installation, we have 4 update sites in #__update_sites table). Found a solution for it by using terminal to update the next value to 5:

joomla336=# SELECT setval('j336_update_sites_update_site_id_seq', (SELECT MAX(update_site_id) FROM j336_update_sites)+1);
 setval 
--------
    5
(1 row)

Then install the plugins again and everything works as expected.

I'm still looking for the reason why PostgreSQL doesn't get the next available value for the primary key column and how to fix. I've just used PostgreSQL recently, still learning.

I found the reason. The queries you use when install Joomla cause this problem.

Create the first test table:

joomla3=# CREATE TABLE "update_sites" (
joomla3(#   "update_site_id" serial NOT NULL,
joomla3(#   "name" varchar(100) DEFAULT '',
joomla3(#   "type" varchar(20) DEFAULT '',
joomla3(#   "location" text NOT NULL,
joomla3(#   "enabled" bigint DEFAULT 0,
joomla3(#   "last_check_timestamp" bigint DEFAULT 0,
joomla3(#   "extra_query" varchar(1000) DEFAULT '',
joomla3(#   PRIMARY KEY ("update_site_id")
joomla3(# );
CREATE TABLE

Insert the default update sites:

joomla3=# INSERT INTO "update_sites" ("update_site_id", "name", "type", "location", "enabled", "last_check_timestamp") VALUES
joomla3-# (1, 'Joomla! Core', 'collection', 'http://update.joomla.org/core/list.xml', 1, 0),
joomla3-# (2, 'Joomla! Extension Directory', 'collection', 'http://update.joomla.org/jed/list.xml', 1, 0),
joomla3-# (3, 'Accredited Joomla! Translations', 'collection', 'http://update.joomla.org/language/translationlist_3.xml', 1, 0),
joomla3-# (4, 'Joomla! Update Component Update Site', 'extension', 'http://update.joomla.org/core/extensions/com_joomlaupdate.xml', 1, 0);
INSERT 0 4

The next value of the table's primary keys is 1, not 5:

joomla3=# select nextval('update_sites_update_site_id_seq');
 nextval 
---------
       1
(1 row)

Create the second table:

joomla3=# CREATE TABLE "update_sites_again" (
joomla3(#   "update_site_id" serial NOT NULL,
joomla3(#   "name" varchar(100) DEFAULT '',
joomla3(#   "type" varchar(20) DEFAULT '',
joomla3(#   "location" text NOT NULL,
joomla3(#   "enabled" bigint DEFAULT 0,
joomla3(#   "last_check_timestamp" bigint DEFAULT 0,
joomla3(#   "extra_query" varchar(1000) DEFAULT '',
joomla3(#   PRIMARY KEY ("update_site_id")
joomla3(# );
CREATE TABLE

Insert the default sites, but this time we don't manually insert the ID:

joomla3=# INSERT INTO "update_sites_again" ("name", "type", "location", "enabled", "last_check_timestamp") VALUES
joomla3-# ('Joomla! Core', 'collection', 'http://update.joomla.org/core/list.xml', 1, 0),
joomla3-# ('Joomla! Extension Directory', 'collection', 'http://update.joomla.org/jed/list.xml', 1, 0),
joomla3-# ('Accredited Joomla! Translations', 'collection', 'http://update.joomla.org/language/translationlist_3.xml', 1, 0),
joomla3-# ('Joomla! Update Component Update Site', 'extension', 'http://update.joomla.org/core/extensions/com_joomlaupdate.xml', 1, 0);
INSERT 0 4

Now we have the correct value:

joomla3=# select nextval('update_sites_again_update_site_id_seq');
 nextval 
---------
       5
(1 row)

We have 2 options to fix:

• Remove the IDs in INSERT queries.
• Keep the IDs in INSERT queries and manually run "SELECT setval..." after INSERT queries.

Please comment, I would like to send pull request if necessary.

Advice to remove ID from INSERT. They shouldn't be used anywhere. exceptions are the tables where they made the mistake to put meaning in ID.

i think lacks on installation\sql\postgresql\joomla.sql

-- Dumping data for table #__update_sites_extensions

INSERT INTO "#__update_sites_extensions" ("update_site_id", "extension_id")
VALUES
(1, 700),
(2, 700),
(3, 600),
(4, 28);
+select nextval('#__update_sites_update_site_id_seq');
+SELECT setval('#__update_sites_update_site_id_seq', 5, false);

for a quick fix run this queries:
select nextval('#__update_sites_update_site_id_seq');
SELECT setval('#__update_sites_update_site_id_seq', 5, false);

This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/4408.

PR for this issue: #4684
Patch for this issue: https://github.com/joomla/joomla-cms/pull/4684.diff


This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/4408.

@dbhurley / @mbabker do we need to do anything to alter the insertation of weblinks into the table with this in mind?

on the sample_*.sql
lacks on this one installation\sql\postgresql\sample_data.sql

SELECT setval('#__weblinks_id_seq', max(id)) FROM #__weblinks;

wich is present on the other 3 sample_.sql files

*This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/4408.

See #4684

This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/4408.