citusdata / citusdata/citus

INSERT ... RETURNING mixes order of rows

Open
#5,770 3 comments 1 reaction 0 assignees View on GitHub
Dominant language
C
Stars
12.8k
Forks
794
Avg merge
2d 14h
Merged PRs (30d)
31

Description

When inserting multiple rows with e. g. autogenerating ids from a sequence, the returned ids don't match the order of inserted values.

DB setup:
```sql
CREATE TABLE t1(id SERIAL PRIMARY KEY, val INT);
SELECT create_distributed_table('t1', 'id');
```

Observing this:
```sql
test_db=# INSERT INTO t1 (val) VALUES (1), (2), (3) RETURNING id, val;
id | val
----+-----
1 | 1
3 | 3
2 | 2
(3 rows)
```

Expected this (PostgreSQL behaviour):

```sql
test_db=# INSERT INTO t1 (val) VALUES (1), (2), (3) RETURNING id, val;
id | val
----+-----
1 | 1
2 | 2
3 | 3
(3 rows)
```

### Why we need it

The Django ORM has a method called `bulk_insert` that takes objects without `id`, inserts them into a database and assigns them back the ids returned by the database. We then insert related objects into a second table. This leads to incorrect foreign keys.

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.