Обсуждение: pg_authid has duplicated rows
Hi,
Today I faced a situation where I saw a postgresql 9.2 database that had duplicated rows in pg_autid. I saw 6 roles while 3 of them were duplicated (3 uniques rolenames in total). I checked and the oid of the duplicates were the same oid as the originals. The solution was running vacuum on the table but I wanted to understand how this situation was created ? Why even with unique index on the rolname I saw duplicated values for rolname ?
Thanks , Mariel.
Hi, On 2018-07-23 21:03:32 +0300, Mariel Cherkassky wrote: > Today I faced a situation where I saw a postgresql 9.2 database that had > duplicated rows in pg_autid. I saw 6 roles while 3 of them were duplicated > (3 uniques rolenames in total). I checked and the oid of the duplicates > were the same oid as the originals. The solution was running vacuum on the > table but I wanted to understand how this situation was created ? Why even > with unique index on the rolname I saw duplicated values for rolname ? That suggests some form of corruption. Which version of 9.2 did this occur on? Did you have any OS level crashes? Ran with fsync=off? Please note that 9.2 is not supported anymore. Greetings, Andres Freund
On 2018-Jul-23, Andres Freund wrote: > Hi, > > On 2018-07-23 21:03:32 +0300, Mariel Cherkassky wrote: > > Today I faced a situation where I saw a postgresql 9.2 database that had > > duplicated rows in pg_autid. I saw 6 roles while 3 of them were duplicated > > (3 uniques rolenames in total). I checked and the oid of the duplicates > > were the same oid as the originals. The solution was running vacuum on the > > table but I wanted to understand how this situation was created ? Why even > > with unique index on the rolname I saw duplicated values for rolname ? > > That suggests some form of corruption. Which version of 9.2 did this > occur on? Did you have any OS level crashes? Ran with fsync=off? I have vague memories of seeing this recently. Can you please show select xmin, xmax, ctid, oid, rolname from pg_authid And the output of pg_controldata. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On July 23, 2018 1:33:11 PM PDT, Alvaro Herrera <alvherre@2ndquadrant.com> wrote: >On 2018-Jul-23, Andres Freund wrote: > >> Hi, >> >> On 2018-07-23 21:03:32 +0300, Mariel Cherkassky wrote: >> > Today I faced a situation where I saw a postgresql 9.2 database >that had >> > duplicated rows in pg_autid. I saw 6 roles while 3 of them were >duplicated >> > (3 uniques rolenames in total). I checked and the oid of the >duplicates >> > were the same oid as the originals. The solution was running vacuum >on the >> > table but I wanted to understand how this situation was created ? >Why even >> > with unique index on the rolname I saw duplicated values for >rolname ? >> >> That suggests some form of corruption. Which version of 9.2 did this >> occur on? Did you have any OS level crashes? Ran with fsync=off? > >I have vague memories of seeing this recently. Can you please show It's at least one of the potential effects of the out-of-date shared relcache bug I recently fixed (but wasn't backpatchedto 9.2, as it's unsupported). Andres -- Sent from my Android device with K-9 Mail. Please excuse my brevity.
Hi,
I checked it also in version 9.6, and in this version I can edit the pg_authid table. I'm suppose to get an error that the table is a system catalog or am I wrong ?
![](/media/maillist_attaches/pgsql-admin/2018/07/24/CA+t6e1=BngUk6xPzZX2-LcYRiFSGKYUfqkgX8PPdxtZNNJX7fg@mail.gmail.com/image.png)
![](/media/maillist_attaches/pgsql-admin/2018/07/24/CA+t6e1=BngUk6xPzZX2-LcYRiFSGKYUfqkgX8PPdxtZNNJX7fg@mail.gmail.com/image-1.png)
2018-07-23 23:43 GMT+03:00 Andres Freund <andres@anarazel.de>:
On July 23, 2018 1:33:11 PM PDT, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
>On 2018-Jul-23, Andres Freund wrote:
>
>> Hi,
>>
>> On 2018-07-23 21:03:32 +0300, Mariel Cherkassky wrote:
>> > Today I faced a situation where I saw a postgresql 9.2 database
>that had
>> > duplicated rows in pg_autid. I saw 6 roles while 3 of them were
>duplicated
>> > (3 uniques rolenames in total). I checked and the oid of the
>duplicates
>> > were the same oid as the originals. The solution was running vacuum
>on the
>> > table but I wanted to understand how this situation was created ?
>Why even
>> > with unique index on the rolname I saw duplicated values for
>rolname ?
>>
>> That suggests some form of corruption. Which version of 9.2 did this
>> occur on? Did you have any OS level crashes? Ran with fsync=off?
>
>I have vague memories of seeing this recently. Can you please show
It's at least one of the potential effects of the out-of-date shared relcache bug I recently fixed (but wasn't backpatched to 9.2, as it's unsupported).
Andres
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
Вложения
Mariel Cherkassky <mariel.cherkassky@gmail.com> writes: > I checked it also in version 9.6, and in this version I can edit the > pg_authid table. I'm suppose to get an error that the table is a system > catalog or am I wrong ? No, the "system catalog modification" restriction only applies to DDL on the system catalogs --- for instance, adding/removing a column, which our C code couldn't cope with. You're free to change their contents by hand if you're a superuser. (And if you break something that way, you get to keep both pieces. But sometimes it's necessary.) regards, tom lane
Ok, thanks for the clarification.
On Tue, Jul 24, 2018, 4:57 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Mariel Cherkassky <mariel.cherkassky@gmail.com> writes:
> I checked it also in version 9.6, and in this version I can edit the
> pg_authid table. I'm suppose to get an error that the table is a system
> catalog or am I wrong ?
No, the "system catalog modification" restriction only applies to DDL
on the system catalogs --- for instance, adding/removing a column, which
our C code couldn't cope with. You're free to change their contents by
hand if you're a superuser. (And if you break something that way, you
get to keep both pieces. But sometimes it's necessary.)
regards, tom lane