Re: [Roundup-users] Docker Container 2.5.0 / UniqueViolation: duplicate key value violates unique constraint "_issue_pkey"

[John R. <ro...@ie...>]

Hi Norbert:

Sorry, I'm kind of tied up with other things.

On Wed, Oct 29, 2025 at 5:00 AM SCHLEMMER Norbert
<Nor...@pd...> wrote:
> What is the recommended upgrade path from Docker "2.3.0 / PostgreSQL 17" to "2.5.0 / PostgreSQL 18"
> and for migrating to a different host with Debian 13 / Trixie?

I suggest:

   dump/restore the database from postgresql 17 to 18 using pg_dump etc.

   then copy over the tracker home directory and follow the steps in
upgrading.txt
   (https://www.roundup-tracker.org/docs/upgrading.html) from 2.3.0 to
2.4.0 and then 2.5.0.
   The required steps need to be done. If you omit the rest, it should
still allow Roundup to
   work, but there may be security or other issues.

> Can the Docker image simply be changed from roundup:2.3.1a0 to roundup:2.5.0-1?
> I did this and didn't find any problems during testing. Is this procedure permissible and recommended?
> If so, I can then upgrade to PostgreSQL 18 in the next step using SQL dump/restore.

That should work provided you do the changes in upgrading.txt.

Ralf said on Mittwoch, 29. Oktober 2025 09:29
> On Wed, Oct 29, 2025 at 07:31:32AM +0000, SCHLEMMER Norbert wrote:
> > An export was created from the productive Roundup instance 2.3.0.
> > After importing in version 2.5.0, new messages can be added to an
> > existing issue but creating a new issue result in this violation; see
> > logs attached (the import process takes approximately 6 hours...).

How many issues do you have?

> Note that using the roundup-export / roundup-import is not recommended for version upgrade, that
> should happen in-place. This might be the reason of the error-message you're seeing.

> > UniqueViolation: duplicate key value violates unique constraint "_issue_pkey" DETAIL: Key (id)=(3)
>  >       already exists.    Python 3.13.5
> > /usr/local/bin/python3.13

> Hmm, this might be because a retired node (e.g. an issue) is first created and then retired, it could
> produce a duplicate primary key if a non-retired node *with the same key* already exists.
> But under normal circumstances duplicate primary keys should not occur (even including retired nodes).

This is happening post import, correct Norbert?

We fixed an import issue with duplicate keys back in the 2.1.0
release. it was caused when a user was
created with the name "A". Then it was retired. Later a new user with
the name "A" was created. When
the export/import was done if the retired entry was imported after the
non-retired entry the name "A"
which is a pkey conflicted.

However for issues, the title is not a primary key so I am not sure
how you would get this conflict.

It almost looks like the sequence used to number (id) the issues isn't
set properly.
Can you connect to the database and run:

  select * from _issue_ids;

you should see something like:

 last_value | log_cnt | is_called
------------+---------+-----------
            1 |         0 |          f

where last_value is one more than the largest id number in the _issues table.
Something like `select max(id) from _issue;` might do the trick to
find the current max id number.

If the last_value is wrong, check the rest of the sequences (use \ds
to list sequences and select like above
to see the value).

I haven't seen an issue like that in my (limited) postgres testing of
import/export.

-- rouilj