Just downloaded "HP Vertica Analytic Database Server" (to be exact - vertica-7.1.2-0.x86_64.RHEL5). Played a little. M-m-m, strange. Trying to understand Vertica's logic of contraints handling, faced with problem:
Simple sequence causes an issue reported in subject:
create table Employees (EmployeeID int primary key, FirstName varchar(30), LastName varchar(30), SSN varchar(30) unique);
create table HealthPlans (HealthPlanID int primary key, EmployeeID int NOT NULL references Employees (EmployeeID), name varchar(30));
CREATE PROJECTION EMP_HEALTH (EmployeeID, FirstName, LastName, SSN)
AS (SELECT e.EmployeeID, e.FirstName, e.LastName, e.SSN
FROM Employees e, HealthPlans hp
WHERE e.EmployeeID = hp.EmployeeID);
insert into Employees (EmployeeID, FirstName, LastName, SSN) values (1, 'John', 'Doe', '1111');
insert into Employees (EmployeeID, FirstName, LastName, SSN) values (3, 'Richard', 'Roe', '3333');
commit;
insert into HealthPlans (HealthPlanID, EmployeeID) values (1, 1);
insert into HealthPlans (HealthPlanID, EmployeeID) values (3, 3);
commit;
select * from EMP_HEALTH;
/*As expectable, 2 rows*/
delete from HealthPlans where EmployeeID = 1;
/*delete from child table - OK*/
delete from Employees where EmployeeID = 1;
/*
ERROR: ERROR 3587: Insufficient resources to execute plan on pool
general [Request Too Large:Memory(KB) Exceeded: Requested =
594635, Free = 512827 (Limit = 512827, Used = 0)] Error Code:
3587
Query = delete from Employees where EmployeeID = 1
*/
All settings are from default installation (VM was downloaded). Default settings prevent me to add 4 and delete 1-2 records???
Thanks in advance,
Axel
