Re: gen_random_uuid key collision

> > Note that the parent variant takes a disruptive lock that will block > write DML. You might prefer to just use the first query if this is > running in a production environment. > Fortunately this has only been observed on the dev instance. This morning I tried just dropping and recreating the

Re: gen_random_uuid key collision

> On Sep 2, 2021, at 4:25 PM, jesusthefrog wrote: > > Anyone have any thoughts on this? I agree with Peter's suggestion upthread to run amcheck on the index, but if that comes back with no corruption, can you verify that there are no rules or triggers that might cause multiple copies of

Re: gen_random_uuid key collision

On Thu, Sep 2, 2021 at 4:48 PM Tom Lane wrote: > That is pretty weird, all right. The only idea that comes to mind > immediately is that maybe that table's pkey index is corrupt and needs > to be reindexed. This isn't a great theory, because I don't see why > a corrupt index would lead to bogus

Re: gen_random_uuid key collision

On 9/2/21 5:38 PM, jesusthefrog wrote: On Thu, Sep 2, 2021 at 8:05 PM Adrian Klaver > wrote: But only one is generating errors. Schema refers to an object's definition as well as a namespace. So what does: \d return? I see what you mean.

Re: gen_random_uuid key collision

On Thu, Sep 2, 2021 at 8:05 PM Adrian Klaver wrote: > But only one is generating errors. Schema refers to an object's > definition as well as a namespace. So what does: > > \d > > return? > I see what you mean. I don't have access to the instance at the moment so I'd have to take a look

Re: gen_random_uuid key collision

On Thu, Sep 2, 2021 at 7:47 PM Tom Lane wrote: > > BTW, are you *entirely* certain that your application never inserts > non-default values into that column? > > regards, tom lane > Yes, I double checked that we never attempt to bind a value for that column. I'll have a

Re: gen_random_uuid key collision

On 9/2/21 4:38 PM, jesusthefrog wrote: On Thu, Sep 2, 2021 at 7:35 PM Adrian Klaver > wrote: What is the table schema as returned by \d in psql? The tables are in various schemas; that one is in one called But only one is generating errors. Schema

Re: gen_random_uuid key collision

jesusthefrog writes: > I work with a postgres instance which has dozens (probably hundreds) of > tables which each have a column defined as "uuid primary key default > gen_random_uuid()". > Most of the time this is fine, but one specific table has recently started > repeatedly having inserts

Re: gen_random_uuid key collision

On Thu, Sep 2, 2021 at 7:35 PM Adrian Klaver wrote: > What is the table schema as returned by \d in psql? > > The tables are in various schemas; that one is in one called "access_control", but we always set the search path explicitly to (in this case) "access_control, public". Anyway, if if

Re: gen_random_uuid key collision

On 9/2/21 4:25 PM, jesusthefrog wrote: Hello, I'm hoping someone might be able to shed a little light on a strange situation I encountered recently. Most of the time this is fine, but one specific table has recently started repeatedly having inserts fail because of a unique constraint

gen_random_uuid key collision

Hello, I'm hoping someone might be able to shed a little light on a strange situation I encountered recently. I work with a postgres instance which has dozens (probably hundreds) of tables which each have a column defined as "uuid primary key default gen_random_uuid()". Most of the time this is