`ALTER DEFAULT PRIVILEGES`: intermittent error about primary key constraint violation

[davepacheco]

Describe the problem

I'm not sure how we got our database into the following state. It's not reproducible for us from a clean slate (i.e., as our software deploys the database), but it's somewhat reproducible through some of our automation.

Here's the problem: we try to run ALTER DEFAULT PRIVILEGES FOR ROLE root REVOKE ALL ON TABLES FROM omicron;. Sometimes it works, and sometimes it produces a confusing error message. Here's a sequence running the command one right after another (just a few tens of milliseconds between queries):

root@[fd00:1122:3344:101::2]:32221/defaultdb> ALTER DEFAULT PRIVILEGES FOR ROLE root REVOKE ALL ON TABLES FROM omicron;
ERROR: job-row-insert: duplicate key value violates unique constraint "primary"
SQLSTATE: 23505
DETAIL: Key (id)=(1441793) already exists.
CONSTRAINT: primary
root@[fd00:1122:3344:101::2]:32221/defaultdb> ALTER DEFAULT PRIVILEGES FOR ROLE root REVOKE ALL ON TABLES FROM omicron;
ALTER DEFAULT PRIVILEGES


Time: 47ms total (execution 47ms / network 0ms)

root@[fd00:1122:3344:101::2]:32221/defaultdb> ALTER DEFAULT PRIVILEGES FOR ROLE root REVOKE ALL ON TABLES FROM omicron;
ERROR: job-row-insert: duplicate key value violates unique constraint "primary"
SQLSTATE: 23505
DETAIL: Key (id)=(1507329) already exists.
CONSTRAINT: primary
root@[fd00:1122:3344:101::2]:32221/defaultdb> ALTER DEFAULT PRIVILEGES FOR ROLE root REVOKE ALL ON TABLES FROM omicron;
ALTER DEFAULT PRIVILEGES


Time: 54ms total (execution 53ms / network 0ms)

root@[fd00:1122:3344:101::2]:32221/defaultdb> ALTER DEFAULT PRIVILEGES FOR ROLE root REVOKE ALL ON TABLES FROM omicron;
ERROR: job-row-insert: duplicate key value violates unique constraint "primary"
SQLSTATE: 23505
DETAIL: Key (id)=(1572865) already exists.
CONSTRAINT: primary
root@[fd00:1122:3344:101::2]:32221/defaultdb> ALTER DEFAULT PRIVILEGES FOR ROLE root REVOKE ALL ON TABLES FROM omicron;
ALTER DEFAULT PRIVILEGES


Time: 39ms total (execution 39ms / network 0ms)

root@[fd00:1122:3344:101::2]:32221/defaultdb> ALTER DEFAULT PRIVILEGES FOR ROLE root REVOKE ALL ON TABLES FROM omicron;
ALTER DEFAULT PRIVILEGES


Time: 49ms total (execution 49ms / network 0ms)

root@[fd00:1122:3344:101::2]:32221/defaultdb> ALTER DEFAULT PRIVILEGES FOR ROLE root REVOKE ALL ON TABLES FROM omicron;
ERROR: job-row-insert: duplicate key value violates unique constraint "primary"
SQLSTATE: 23505
DETAIL: Key (id)=(1671169) already exists.
CONSTRAINT: primary
root@[fd00:1122:3344:101::2]:32221/defaultdb> 

First, it's not clear what this error message means in the context of this query. It seems like something internal bubbling out? But it's also not clear why it only happens sometimes, when we haven't changed the state of the system. I believe that while I'm doing this, there are no other connected sessions.

On a whim: I restarted CockroachDB from this state and now every attempt to run that SQL results in the constraint violation error. After a few more minutes (writing up this report), I tried again, and we're back to an intermittent result.

To Reproduce

It's hard to get you the sequence of steps from a fresh cluster because this is the result of a bunch of automation. But once we're here, just running that SQL query reproduces the problem.

Expected behavior

We expected that query to complete successfully, or maybe fail with an error message that has something to do with what's wrong with it.

Additional data / screenshots

Let me know what other information would be useful.

Environment:

This is a single-node CockroachDB cluster.

Build Tag:        v21.2.9
Build Time:       2022/04/28 04:02:42
Distribution:     OSS
Platform:         illumos amd64 (x86_64-pc-solaris2.11)
Go Version:       go1.16.10
C Compiler:       gcc 10.3.0
Build Commit ID:  11787edfcfc157a0df951abc34684e4e18b3ef20
Build Type:       release

Note we're running an OSS build on Helios (our illumos distro).

Additional context

The context here is that we have some automation that sets up a single-node cluster and populates it with this schema that defines one database and one user:
https://github.com/oxidecomputer/omicron/blob/main/common/src/sql/dbinit.sql

We also have a mechanism that's intended to wipe the database to a pristine state with:
https://github.com/oxidecomputer/omicron/blob/main/common/src/sql/dbwipe.sql

It's that second step that's failing in the way reported here. We stopped the automation at that point, logged in interactively, and found the behavior described above.

The impact is that our automation fails to reliably wipe the database in this case. This is not recoverable in an automatable way, at least not without knowing what we should do if we find this situation.

Jira issue: CRDB-20949

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I was unable to automatically find someone to ping.

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[davepacheco]

A few other things here:

We couldn't help but notice that the conflicting key values are a little funny:

root@[fd00:1122:3344:101::2]:32221/defaultdb> ALTER DEFAULT PRIVILEGES FOR ROLE root REVOKE ALL ON TABLES FROM omicron;
ERROR: job-row-insert: duplicate key value violates unique constraint "primary"
SQLSTATE: 23505
DETAIL: Key (id)=(32769) already exists.
CONSTRAINT: primary
root@[fd00:1122:3344:101::2]:32221/defaultdb> ALTER DEFAULT PRIVILEGES FOR ROLE root REVOKE ALL ON TABLES FROM omicron;
ERROR: job-row-insert: duplicate key value violates unique constraint "primary"
SQLSTATE: 23505
DETAIL: Key (id)=(65537) already exists.
CONSTRAINT: primary
root@[fd00:1122:3344:101::2]:32221/defaultdb> ALTER DEFAULT PRIVILEGES FOR ROLE root REVOKE ALL ON TABLES FROM omicron;
ERROR: job-row-insert: duplicate key value violates unique constraint "primary"
SQLSTATE: 23505
DETAIL: Key (id)=(98305) already exists.
CONSTRAINT: primary
root@[fd00:1122:3344:101::2]:32221/defaultdb> ALTER DEFAULT PRIVILEGES FOR ROLE root REVOKE ALL ON TABLES FROM omicron;
ERROR: job-row-insert: duplicate key value violates unique constraint "primary"
SQLSTATE: 23505
DETAIL: Key (id)=(131073) already exists.
CONSTRAINT: primary
root@[fd00:1122:3344:101::2]:32221/defaultdb> ALTER DEFAULT PRIVILEGES FOR ROLE root REVOKE ALL ON TABLES FROM omicron;
ERROR: job-row-insert: duplicate key value violates unique constraint "primary"
SQLSTATE: 23505
DETAIL: Key (id)=(163841) already exists.
CONSTRAINT: primary
root@[fd00:1122:3344:101::2]:32221/defaultdb> 

In hex, these are 8001, 10001, 18001, 20001, 28001. That's weirdly regular.

The time on this system is pretty old (January 9, 2000) for uninteresting reasons (but I thought I'd mention it in case CockroachDB has some dependency on recent timestamps). It's a single-node cluster so I don't think there's any time synchronization issue.

We've seen this on two different physical machines. We're using ZFS so we're pretty confident there's no underlying on-disk data corruption.

We've managed to reproduce this from a clean slate a few times but for various reasons I'm not sure how reproducible it really is -- our automation may have changed, and we're also running it for the first time on new hardware that might have different behavior than what we've done before.

We do have a ZFS snapshot of the underlying database.

[rafiss]

Adding the schema team, who might be better equipped to find out how this causes a duplicate job PK.

[davepacheco]

We've discovered this illumos OS issue that under some conditions causes memory that is supposed to be zero'd by the Go runtime to not be properly zero'd. Obviously this could lead to all sorts of strange behavior. That bug would have been present on the system where this occurred. I don't have any particular reason to believe it caused the behavior described above, aside from how bizarre this behavior looks and the fact that nobody else seems to have reported it. I leave it up to y'all to decide if this should be closed or investigated further or just closed (and reopened if we see it again on a fixed system). Thanks!