citusdata / citusdata/citus

Insert on local table raises ERROR: consistency check on SPI tuple count failed

Open
#2,743 0 comments 0 reactions 0 assignees View on GitHub
bug
Dominant language
C
Stars
12.8k
Forks
794
Avg merge
2d 14h
Merged PRs (30d)
31

Description

When there is a foreign key between a local table and a distributed/reference table (created before distribution), inserting into the local table fails with

```
ERROR: consistency check on SPI tuple count failed
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."auth_user" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"
```

Here is to reproduce (I simplified the schemas generated by django):

```
CREATE TABLE public.auth_user (
id integer NOT NULL,
username character varying(150) NOT NULL
);

CREATE TABLE public.django_admin_log (
id integer NOT NULL,
content_type_id integer,
user_id integer NOT NULL
);

ALTER TABLE ONLY public.auth_user
ADD CONSTRAINT auth_user_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.django_admin_log
ADD CONSTRAINT django_admin_log_pkey PRIMARY KEY (id);

ALTER TABLE ONLY public.django_admin_log
ADD CONSTRAINT django_admin_log_user_id_c564eba6_fk_auth_user_id FOREIGN KEY (user_id) REFERENCES public.auth_user(id) DEFERRABLE INITIALLY DEFERRED;

SELECT create_reference_table('auth_user');

INSERT INTO auth_user VALUES (1, 'louise');

INSERT INTO django_admin_log VALUES (1, 1, 1);
```

If we drop the foreign key, the insert doesn't fail anymore

```
ALTER TABLE django_admin_log DROP CONSTRAINT django_admin_log_user_id_c564eba6_fk_auth_user_id;

INSERT INTO django_admin_log VALUES (1, 1, 1);
```

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.