From: | Tobias Meyer <t9m(at)qad(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Remove duplicated row in pg_largeobject_metadata |
Date: | 2021-09-18 15:35:45 |
Message-ID: | CAAEpUZn=G6+tJ46QGYegdNyKuOsSYVAbsXGGR4_Fj7EJDxvicQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Well, it seems we have hit a bug in postgresql 10.
We tried running vacuumlo on a database and it complained at some point
with a message
Failed to remove lo 64985186: ERROR: large object 64985186 does not exist
Removal from database "XXXXX" failed at object 26 of 100.
Yet, object 64985186 is neither in pg_largeobject.loid nor in
pg_largeobject_metadata.oid
Researching similar problems did not yield anything enlightening,
except for a comment by Tom Lane that vacuumlo will use an index.
That led be to the assumption an index had gone wrong and I did
REINDEX ( VERBOSE ) TABLE pg_largeobject_metadata;
Which failed with the message
ERROR: could not create unique index "pg_largeobject_metadata_oid_index"
DETAIL: Key (oid)=(665238) is duplicated.
Well,
select oid,* from pg_largeobject_metadata where oid=665238;
oid | lomowner | lomacl
--------+----------+--------
665238 | 16123 |
(1 row)
That seemed odd, so I decided to do a FULL VACUUM
VACUUM (full, verbose) pg_largeobject_metadata
Now I see
select oid,* from pg_largeobject_metadata where oid=665238;
oid | lomowner | lomacl
--------+----------+--------
665238 | 16123 |
665238 | 16123 |
(2 rows)
Deleting both would be a no-go, as I cannot INSERT a specific oid to add
one back as far as I know.
So how do I delete only one of the two?
Luckily this is only a test instance, so I'm not totally freaked out now.
Note: I randomized the IDs a bit in this post for security reasons, but
kept the scale. Please ignore should they be in an unusual range.
Thanks,
Tobias
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2021-09-18 17:01:48 | Re: Remove duplicated row in pg_largeobject_metadata |
Previous Message | Miles Elam | 2021-09-18 14:03:52 | Re: The tragedy of SQL |