Приветствую коллеги! Возможно проблема очевидна, но я никак могу её найти. Прошу помочь свежим взглядом!
Основной сервер psql (PostgreSQL) 9.4.12
Тестовый сервер psql (PostgreSQL) 9.2.18
Суть проблемы, при удалении записи в таблице carts на рабочем сервере, не удаляются сопутствующие ей записи в таблице carts_items. При этом на тестовом всё работает
Ниже вывод команд:
1.
2.
\d+ carts_items
\d+ carts
Рабочий сервер:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
transit_db=# \d+ carts
Table "public.carts"
Column | Type | Modifiers | Storage | Stats target | Description
--------------------+-----------------------------+-------------------------------------------------------------------------------------------+----------+--------------+-------------
id | integer | not null default nextval('carts_id_seq'::regclass) | plain | |
pact_id | integer | not null | plain | |
customer_reference | character varying(64) | | extended | |
description | character varying(64) | | extended | |
date_created | timestamp without time zone | not null default timezone('Europe/Moscow'::text, (now())::timestamp(0) without time zone) | plain | |
api_source | character varying(12) | | extended | |
status | character varying | | extended | |
domain_id | integer | | plain | |
total_sum | double precision | not null default 0 | plain | |
final_sum | double precision | not null default 0 | plain | |
allowed_item_count | smallint | not null default 0 | plain | |
denied_item_count | smallint | not null default 0 | plain | |
date_fulfilled | timestamp without time zone | | plain | |
Indexes:
"carts_pkey" PRIMARY KEY, btree (id)
"cart_identifier" UNIQUE CONSTRAINT, btree (id, pact_id, api_source)
Referenced by:
TABLE "carts_items" CONSTRAINT "carts_items_carts_id_fkey" FOREIGN KEY (cart_id) REFERENCES carts(id) MATCH FULL ON DELETE CASCADE
transit_db=# \d+ carts_items
Table "public.carts_items"
Column | Type | Modifiers | Storage | Stats target | Description
----------------------+--------------------------+----------------------------------------------------------+----------+--------------+-------------------------------------
id | integer | not null default nextval('carts_items_id_seq'::regclass) | plain | |
cart_id | integer | not null | plain | |
product_type | character varying | not null | extended | |
product_id | integer | not null | plain | |
product_description | character varying | | extended | |
product_order_status | carts_items_order_status | default 'added'::carts_items_order_status | plain | | ENUM ('added', 'allowed', 'denied')
Indexes:
"carts_items_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"carts_items_carts_id_fkey" FOREIGN KEY (cart_id) REFERENCES carts(id) MATCH FULL ON DELETE CASCADE
Тестовый сервер:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
transit_db=# \d+ carts
Table "public.carts"
Column | Type | Modifiers | Storage | Stats target | Description
--------------------+-----------------------------+-------------------------------------------------------------------------------------------+----------+--------------+-------------
id | integer | not null default nextval('carts_id_seq'::regclass) | plain | |
pact_id | integer | not null default nextval('carts_pact_id_seq'::regclass) | plain | |
customer_reference | character varying(64) | | extended | |
description | character varying(64) | | extended | |
date_created | timestamp without time zone | not null default timezone('Europe/Moscow'::text, (now())::timestamp(0) without time zone) | plain | |
api_source | character varying(12) | | extended | |
status | character varying | | extended | |
domain_id | integer | | plain | |
total_sum | double precision | not null default 0 | plain | |
final_sum | double precision | not null default 0 | plain | |
allowed_item_count | smallint | not null default 0 | plain | |
denied_item_count | smallint | not null default 0 | plain | |
date_fulfilled | timestamp without time zone | | plain | |
Indexes:
"carts_pkey" PRIMARY KEY, btree (id)
"cart_identifier" UNIQUE CONSTRAINT, btree (id, pact_id, api_source)
Referenced by:
TABLE "carts_items" CONSTRAINT "carts_items_carts_id_fkey" FOREIGN KEY (cart_id) REFERENCES carts(id) MATCH FULL ON DELETE CASCADE
Has OIDs: no
transit_db=# \d+ carts_items
Table "public.carts_items"
Column | Type | Modifiers | Storage | Stats target | Description
----------------------+--------------------------+---------------------------------------------------------------+----------+--------------+-------------------------------------
id | integer | not null default nextval('carts_items_id_seq'::regclass) | plain | |
cart_id | integer | not null default nextval('carts_items_cart_id_seq'::regclass) | plain | |
product_type | character varying | not null | extended | |
product_id | integer | not null | plain | |
product_description | character varying | | extended | |
product_order_status | carts_items_order_status | default 'added'::carts_items_order_status | plain | | ENUM ('added', 'allowed', 'denied')
Indexes:
"carts_items_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"carts_items_carts_id_fkey" FOREIGN KEY (cart_id) REFERENCES carts(id) MATCH FULL ON DELETE CASCADE
Has OIDs: no
Проблема вылезла после переноса бекапа с версии psql (PostgreSQL) 9.4.8 на новый сервер версии Основной сервер psql (PostgreSQL) 9.4.12
Удаление и пересоздание таблиц не помогает.
Во вложении скриншот как сие выглядит через pg_admin
Удаляю я данные не через pg_admin а SQL Запросом DELETE
