dolthub / dolthub/doltgresql

Generated column with COALESCE: every INSERT fails after ALTER TABLE ADD PRIMARY KEY

Open
#3,323 0 comments 0 reactions 1 assignee Claimed by @Hydrocharged View on GitHub
customer issue
Dominant language
Go
Stars
2.1k
Forks
73
Avg merge
1d 10h
Merged PRs (30d)
129

Description

On DoltgreSQL 1.3.1, a table with a `STORED` generated column whose expression is `COALESCE` over an expression, such as `COALESCE(a + 1, 0)`, accepts inserts until the table is altered. After `ALTER TABLE t ADD PRIMARY KEY (a)`, every `INSERT` fails:

```
ERROR: Invalid default value for '(coalesce("a" + 1 as a + 1,0))': at or near "as": syntax error
```

PostgreSQL 18.6 runs the same statements without an error.

## Reproduction

```sql
CREATE TABLE t (
a int NOT NULL,
b int GENERATED ALWAYS AS (COALESCE(a + 1, 0)) STORED
);
INSERT INTO t (a) VALUES (1); -- INSERT 0 1
ALTER TABLE t ADD PRIMARY KEY (a); -- ALTER TABLE
INSERT INTO t (a) VALUES (2); -- ERROR: Invalid default value for '(coalesce("a" + 1 as a + 1,0))': at or near "as": syntax error
SELECT * FROM t ORDER BY a;
```

**Expected** (PostgreSQL 18.6): both inserts succeed, and the `SELECT` returns two rows, `(1, 2)` and `(2, 3)`.

**Actual** (DoltgreSQL 1.3.1): the `ALTER TABLE` succeeds, the second insert fails with the error above, and the `SELECT` returns only `(1, 2)`.

A runnable reproduction is at https://github.com/Reliable-Collaboration/repro-doltgresql-bug-1. Its script runs the SQL in a throwaway `dolthub/doltgresql:1.3.1` container, or in `postgres:18.6` for comparison, and prints the full output:

```sh
git clone https://github.com/Reliable-Collaboration/repro-doltgresql-bug-1.git
cd repro-doltgresql-bug-1
./repro.sh # DoltgreSQL 1.3.1: fails, exits 1
./repro.sh postgres # PostgreSQL 18.6: succeeds, exits 0
```

## Other observations

All on DoltgreSQL 1.3.1:

- After the alteration, `UPDATE` and `CREATE INDEX` on the table fail with the same error. `SELECT` still works.
- `ADD UNIQUE`, `DROP COLUMN` and `ALTER COLUMN ... TYPE` have the same effect as `ADD PRIMARY KEY`. `ADD COLUMN` or `CREATE INDEX`, as the only alteration, does not.
- `COALESCE(abs(a), 0)` fails the same way, stored as `coalesce(abs("a") as abs,0)`. `COALESCE(a, 0)` does not, and neither does `a + 1` without `COALESCE`.
- Declaring the primary key inside `CREATE TABLE`, instead of adding it afterwards, avoids the error.

## Why it matters

`pg_dump` writes primary keys as `ALTER TABLE ONLY ... ADD CONSTRAINT ... PRIMARY KEY` after `CREATE TABLE`, so restoring a dump that has such a table leaves the table unable to accept inserts.

## Possible cause

Not verified with a build; offered in case it saves time. The stored text in the error, `coalesce("a" + 1 as a + 1,0)`, looks like `PgCoalesce.String()` output with `*expression.Alias` arguments. `CREATE TABLE` clears these aliases from the generated expression (https://github.com/dolthub/doltgresql/blob/84805dd1ca67d7dc43cbb93ad3ccde3e1187287b/server/ast/column_table_def.go#L121-L123), but the alterations above appear to write the expression back from its resolved form. `CompiledFunction.String()` unwraps alias arguments, noting that "Aliases will output the string "x as x", which is an artifact of how we build the AST" (https://github.com/dolthub/doltgresql/blob/84805dd1ca67d7dc43cbb93ad3ccde3e1187287b/server/functions/framework/compiled_function.go#L291-L306). `PgCoalesce.String()` prints its arguments as they are (https://github.com/dolthub/doltgresql/blob/84805dd1ca67d7dc43cbb93ad3ccde3e1187287b/server/expression/coalesce.go#L162-L168), and it is unchanged on `main`.

## Possibly related

- #810: a generated column's expression is stored in a form the parser rejects (backticks), so inserts into the table fail.
- #3094, fixed by #3142: an expression index on `coalesce()` was accepted, and then every write to the table failed. It had a different error, but the same shape of delayed failure.

## Environment

- `dolthub/doltgresql:1.3.1`, digest `sha256:6c85cb1f35beabf47f094336a420255130b841b1645f36d79ef046276af36851`, the latest release when this was written
- Docker 29.7.2 on Linux x86_64

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.