Re: pgarchives: Bug report + Patches: loader can't handle message in multiple lists

On Thu, Jul 13, 2023 at 3:36 PM Célestin Matte <celestin.matte@cmatte.me> wrote:
>
> >> Traceback (most recent call last):
> >>     File "/path/pgarchives/local//loader/load_message.py", line 158, in <module>
> >>       ap.store(conn, listid, opt.overwrite, opt.overwrite)
> >>     File "/path/pgarchives/local/loader/lib/storage.py", line 216, in store
> >>       'listid': listid,
> >> psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "list_threads_pkey"
> >> DETAIL:  Key (threadid)=(21) already exists.
> >
> > That looks like your primary key on list_threads is wrong. The primary
> > key should be on (threadid, listid) but based on that error message it
> > looks like it's on (threadid).
>
> Hmm. This is due to my deployment relying on my patch to merge schema.sql into a Django model (review still pending!
[1]).
> As Django does not support composite primary keys [2], I had to use unique_together instead:
>
> migrations.CreateModel(
>              name='ListThreads',
>              fields=[
>                  ('threadid', models.IntegerField(primary_key=True, serialize=False)),
>                  ('listid', models.ForeignKey(db_column='listid', on_delete=django.db.models.deletion.CASCADE,
to='mailarchives.List')),
>              ],
>              options={
>                  'db_table': 'list_threads',
>                  'unique_together': {('threadid', 'listid')},
>              },
>          ),
>
> Which leads to the following indexes being created:
> Indexes:
>      "list_threads_pkey" PRIMARY KEY, btree (threadid)
>      "list_threads_listid_44de2a94" btree (listid)
>      "list_threads_listid_idx" btree (listid)
>      "list_threads_threadid_listid_226e84bf_uniq" UNIQUE CONSTRAINT, btree (threadid, listid)
>
>
> Not sure how to fix this. Inputs on previously mentioned patch would be helpful!

Yeah, that patch is then clearly broken :/ The unique_together part is
correct. And I'm not sure why that should lead to *two* indexes on
listid - that looks like a django bug, if that creation is purely from
that model.

You can't set threadid to the primary key. You have to either:

1. Add another column that's autonumbering and use that as primary key.
2. Use migrations.RunSQL() to create the table instead of
migrations.CreateModel().

I would recommend #2, as #1 is an ugly workaround to the limitations
of the django ORM - and limitations that we don't hit anywhere else
since we don't use it that way.

--
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/