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(dot)matte(at)cmatte(dot)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/