Logical database is `offline: restoring` even after waiting for restore job to complete

**Describe the problem**

For a few seconds after a (detached) restore job completes, the database being restored won't accept any queries and returns errors like:

```
ERROR: database "mydbname" is offline: restoring (SQLSTATE XXUUU)
```

My expectation was that if the restore job reports success, the database would be available for querying.

**To Reproduce**

Running a job in kubernetes that performs the following steps to take a snapshot of one database and restore it to another:

1. `BACKUP DATABASE sourcedb INTO 's3://...' AS OF SYSTEM TIME '-10s' WITH DETACHED;`
2. `SHOW JOB WHEN COMPLETE (WITH x as (SHOW JOBS) SELECT job_id FROM x WHERE job_type = 'BACKUP' ORDER BY created DESC LIMIT 1);`
3. `RESTORE DATABASE sourcedb FROM LATEST IN 's3://...' WITH new_db_name = "targetdb-new", DETACHED;`
4. `SHOW JOB WHEN COMPLETE (WITH x as (SHOW JOBS) SELECT job_id FROM x WHERE job_type = 'RESTORE' ORDER BY created DESC LIMIT 1);`
- This blocks before returning and shows the job in the `succeeded` state: `978361979530477569,RESTORE,"RESTORE DATABASE targetdb FROM 's3://... WITH OPTIONS (detached, new_db_name = 'targetdb-new')",,...,succeeded,NULL,2024-06-17 16:46:21.075433+00,2024-06-17 16:46:45.504433+00,2024-06-17 16:46:46.789028+00,2024-06-17 16:46:46.789071+00,1,,1,3775933280525936069,2024-06-17 16:46:45.504433+00,2024-06-17 16:47:15.504433+00,1,NULL`
5. `ALTER DATABASE targetdb RENAME TO "targetdb-old";`
6. `ALTER DATABASE "targetdb-new" RENAME TO targetdb;`
7. `DROP DATABASE "targetdb-old" CASCADE;`

Separately, I have an application making queries against `targetdb` running continuously with a low level of traffic. I can reproducibly see a few seconds of errors that look like this:

```
ERROR: database "targetdb" is offline: restoring (SQLSTATE XXUUU)
```

**Expected behavior**
I expected this operation to make the database unavailable for a bit, but I didn't expect to see errors that the restore was still in progress - there is no state where queries should have been hitting the database while being restored, since I'm blocking on restore job completion with `SHOW JOB WHEN COMPLETE`. This makes me think there is a bug in reporting the `succeeded` status of a restore job?

**Environment:**
- CockroachDB version: Serverless, AWS v23.2.6 (I haven't tested in Dedicated yet, but will be doing that soon anyway)
- Client app: `cockroach sql` (backup/restore) and `pgx` v5 w/ go 1.22 (queries)

**Additional Info**
As an aside, I would also love to find a way to make a one-liner to both create a detached backup/restore job **and** wait for its completion (I want to wait in the normal case, but I want it to continue running in the background if the frontend dies for some reason).

Jira issue: CRDB-39610