Insert on local table raises ERROR: consistency check on SPI tuple count failed
- 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
Assessment
This issue has not been assessed yet.