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

On Mon, Jun 12, 2023 at 5:30 PM Célestin Matte <celestin(dot)matte(at)cmatte(dot)me> wrote:
>
> > OK, I think I need to take a step back to try to figure out what's
> > actually wrong here.
> >
> > What is it you are actually trying to accomplish? Are you trying to
> > remove the single-store functionality for emails? As mentioned, the
> > whole design of the system is done around that a single email should
> > only be stored once - but it sounds like you're removing that for some
> > reason? Or am I misunderstanding?
>
> Yes, because during my tests, the import script crashed when trying to import a message that belongs to two different lists at once.
> I'm having trouble reproducing the crash as properly cleaning the database would require some work. Not sure it's the crash I had back then, but I now have something like this:
> 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).

The SQL scrpit for generating the db has:
CREATE TABLE list_threads(
threadid int NOT NULL, /* comes from threadid_seq */
listid int NOT NULL REFERENCES lists(listid),
CONSTRAINT pg_list_threads PRIMARY KEY (threadid, listid)
);

But it sounds like you somehow ended up with the wrong index there?
(There should be no index named list_threads_pkey -- the name of that
primary key constraint is very strange and probably a typo in there,
but in particular it's different)

Do you still have the wrong index there, or has it been fixed at some
other time? If it has, that could explain the inability to
reproduce...

> After a bit of digging, the workaround I came around with was to store each email once *for each list* instead of just once (which did not work).

Yeah, that is clearly an incorrect fix :/

//Magnus