eugene 0.8.3

Careful with That Lock, Eugene
Documentation 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

# `E5` Type change requiring table rewrite

## Description

**Triggered when**: A column was changed to a data type that isn't binary compatible.

**Effect**: This causes a full table rewrite while holding a lock that prevents all other use of the table.

**Workaround**: Add a new column, update it in batches, and drop the old column.

**Detected by**: `eugene lint` and `eugene trace`

## Problematic migration

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

-- 2.sql
set local lock_timeout = '2s';
alter table prices
    alter price set data type bigint;
```

## Safer migration

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

-- 2.sql
set local lock_timeout = '2s';
alter table prices
    add column new_price bigint;

-- 3.sql
update prices set new_price = price :: bigint;
set local lock_timeout = '2s';
alter table prices
    add constraint check_new_price_not_null
        check (new_price is not null) not valid;

-- 4.sql
set local lock_timeout = '2s';
alter table prices
    validate constraint check_new_price_not_null,
    drop column price;
-- eugene: ignore E4
-- this has to run in the same transaction as dropping the old price column
alter table prices
    rename column new_price to price;
```

## Eugene report examples

- [Problem linted by Eugene](unsafe_lint.md)
- [Problem traced by Eugene](unsafe_trace.md)
- [Fix linted by Eugene](safer_trace.md)
- [Fix traced by Eugene](safer_trace.md)