Adding a default now() column to replicated tables creates inconsistencies
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
It's possible to add a column with a `default` that's a stable or volatile function to replicated tables (e.g. reference tables). Given that the function will be evaluated on all replicas separately, the replicas may diverge.
```sql
CREATE TABLE ref (x int);
SELECT create_reference_table('ref');
INSERT INTO ref VALUES (1);
ALTER TABLE ref ADD COLUMN event_time timestamptz default now();
SELECT run_command_on_placements('ref', 'SELECT event_time FROM %s');
run_command_on_placements
───────────────────────────────────────────────────────────
(localhost,9700,102815,t,"2018-05-04 06:37:18.219029+02")
(localhost,9701,102815,t,"2018-05-04 06:37:18.219243+02")
(2 rows)
```
Contributor guide
Research direction
The reproduction uses create_reference_table, ALTER TABLE with a now() default, and run_command_on_placements; start by running this SQL scenario against replicated tables. The issue does not specify whether the operation should be rejected or coordinated, so clarify the intended behavior before locating the relevant implementation and tests. Done means the chosen behavior prevents unexplained replica divergence.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql, sql
- Domain
- databases, distributed-systems
- Issue type
- Bug
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 25/100