## Eugene 🔒 trace report of `examples/E10/bad/1.sql`

This is a human-readable lock tracing and migration report generated by
[eugene](https://github.com/kaaveland/eugene) to assist you in writing safer database migration scripts.

Here are some tips for reading it:

- A lock is called **dangerous** ❌ if it will cause concurrent queries to **wait** for the migration to complete
- You read that right, once a lock is acquired, it is only released at the end of the script
- Eugene will tell you what kinds of queries **dangerous** locks would block in a summary
- **Hints** can sometimes help you avoid dangerous locks, or hold them for a shorter time
- It is hard to avoid dangerous locks, but we should minimize time spent while holding them
- Sometimes seemingly fast migration scripts cause long outages because of lock queues,
  [here is an example scenario](https://kaveland.no/careful-with-that-lock-eugene.html)

There is a summary section for the entire script at the start of the report
and then a section for each statement in the script, that goes over the state of the database
at the time the script was executed, as well as effects or hints specific to that particular
statement.

### Overall Summary

| Started at | Total duration (ms) | Number of dangerous locks |
|------------|----------------------|--------------------------|
| 2021-01-01T00:00:00+00:00 | 10 | 0 ✅|

No locks acquired on database objects that already exist.



### Statement number 1 for 10ms

#### SQL

```sql
-- 1.sql
create table prices (
    id integer generated always as identity
        primary key,
    price int not null
)
```

#### Locks at start

No locks held at the start of this statement.

#### New locks taken

No new locks taken by this statement.



### Statement number 2 for 10ms

#### SQL

```sql
create table authors (
    id integer generated always as identity
        primary key,
    name text not null
)
```

#### Locks at start

No locks held at the start of this statement.

#### New locks taken

No new locks taken by this statement.



## Eugene 🔒 trace report of `examples/E10/bad/2.sql`

This is a human-readable lock tracing and migration report generated by
[eugene](https://github.com/kaaveland/eugene) to assist you in writing safer database migration scripts.

Here are some tips for reading it:

- A lock is called **dangerous** ❌ if it will cause concurrent queries to **wait** for the migration to complete
- You read that right, once a lock is acquired, it is only released at the end of the script
- Eugene will tell you what kinds of queries **dangerous** locks would block in a summary
- **Hints** can sometimes help you avoid dangerous locks, or hold them for a shorter time
- It is hard to avoid dangerous locks, but we should minimize time spent while holding them
- Sometimes seemingly fast migration scripts cause long outages because of lock queues,
  [here is an example scenario](https://kaveland.no/careful-with-that-lock-eugene.html)

There is a summary section for the entire script at the start of the report
and then a section for each statement in the script, that goes over the state of the database
at the time the script was executed, as well as effects or hints specific to that particular
statement.

### Overall Summary

| Started at | Total duration (ms) | Number of dangerous locks |
|------------|----------------------|--------------------------|
| 2021-01-01T00:00:00+00:00 | 10 | 4 ❌|

#### All locks found
| Schema | Object | Mode | Relkind | OID | Safe | Duration held (ms) |
|--------|--------|------|---------|-----|------|--------------------|
| `public` | `authors` | `AccessExclusiveLock` | Table | 1 | ❌ | 10 |
| `public` | `prices` | `AccessExclusiveLock` | Table | 1 | ❌ | 10 |
| `public` | `prices` | `ShareLock` | Table | 1 | ❌ | 10 |
| `public` | `prices_pkey` | `AccessExclusiveLock` | Index | 1 | ❌ | 10 |

#### Dangerous locks found
- `AccessExclusiveLock` would block the following operations on `public.authors`:
  + `SELECT`
  + `FOR UPDATE`
  + `FOR NO KEY UPDATE`
  + `FOR SHARE`
  + `FOR KEY SHARE`
  + `UPDATE`
  + `DELETE`
  + `INSERT`
  + `MERGE`
- `AccessExclusiveLock` would block the following operations on `public.prices`:
  + `SELECT`
  + `FOR UPDATE`
  + `FOR NO KEY UPDATE`
  + `FOR SHARE`
  + `FOR KEY SHARE`
  + `UPDATE`
  + `DELETE`
  + `INSERT`
  + `MERGE`
- `ShareLock` would block the following operations on `public.prices`:
  + `UPDATE`
  + `DELETE`
  + `INSERT`
  + `MERGE`
- `AccessExclusiveLock` would block the following operations on `public.prices_pkey`:
  + `SELECT`
  + `FOR UPDATE`
  + `FOR NO KEY UPDATE`
  + `FOR SHARE`
  + `FOR KEY SHARE`
  + `UPDATE`
  + `DELETE`
  + `INSERT`
  + `MERGE`


### Statement number 1 for 10ms

#### SQL

```sql
-- 2.sql
set local lock_timeout = '2s'
```

#### Locks at start

No locks held at the start of this statement.

#### New locks taken

No new locks taken by this statement.



### Statement number 2 for 10ms

#### SQL

```sql
alter table authors add column meta jsonb
```

#### Locks at start

No locks held at the start of this statement.

#### New locks taken

| Schema | Object | Mode | Relkind | OID | Safe | Duration held (ms) |
|--------|--------|------|---------|-----|------|--------------------|
| `public` | `authors` | `AccessExclusiveLock` | Table | 1 | ❌ | 10 |



### Statement number 3 for 10ms

#### SQL

```sql
-- eugene: ignore E5, E4
-- causes table rewrite, but this example isnt't about that
alter table prices
    alter price set data type bigint
```

#### Locks at start

| Schema | Object | Mode | Relkind | OID | Safe | Duration held (ms) |
|--------|--------|------|---------|-----|------|--------------------|
| `public` | `authors` | `AccessExclusiveLock` | Table | 1 | ❌ | 10 |

#### New locks taken

| Schema | Object | Mode | Relkind | OID | Safe | Duration held (ms) |
|--------|--------|------|---------|-----|------|--------------------|
| `public` | `prices` | `AccessExclusiveLock` | Table | 1 | ❌ | 10 |
| `public` | `prices` | `ShareLock` | Table | 1 | ❌ | 10 |
| `public` | `prices_pkey` | `AccessExclusiveLock` | Index | 1 | ❌ | 10 |

#### Hints

##### [Creating a new index on an existing table](https://kaveland.no/eugene/hints/E6/)
ID: `E6`

A new index was created on an existing table without the `CONCURRENTLY` keyword. This blocks all writes to the table while the index is being created. A safer way is: Run `CREATE INDEX CONCURRENTLY` instead of `CREATE INDEX`.

A new index was created on the table `public.prices`. The index was created non-concurrently, which blocks all writes to the table. Use `CREATE INDEX CONCURRENTLY` to avoid blocking writes.
##### [Rewrote table or index while holding dangerous lock](https://kaveland.no/eugene/hints/E10/)
ID: `E10`

A table or index was rewritten while holding a lock that blocks many operations. This blocks many operations on the table or index while the rewrite is in progress. A safer way is: Build a new table or index, write to both, then swap them.

The Table `public.prices` was rewritten while holding `AccessExclusiveLock` on the Table `public.authors`. This blocks `SELECT`, `FOR UPDATE`, `FOR NO KEY UPDATE`, `FOR SHARE`, `FOR KEY SHARE`, `UPDATE`, `DELETE`, `INSERT`, `MERGE` while the rewrite is in progress.