On 05/02/2024 20:11, Rick Hillegas wrote:
I would need to see your full schema before speculating about why these
statements grab these locks.
Here are the relevant tables. One thing that strikes me as odd is the
separation of active_list and current_list -- probably there was a
reason for this in the dim distant past (or maybe not!) -- and I wonder
if merging the two would make any difference? (If not, it's something
I'd prefer not to try and do -- it would probably require a number of
code changes, and "if it ain't broke, don't fix it".)
CREATE TABLE active_list (
username VARCHAR(15) NOT NULL,
surname VARCHAR(255) NOT NULL,
initials VARCHAR(255) NOT NULL DEFAULT '',
email VARCHAR(255) NOT NULL DEFAULT '',
settings CLOB,
CONSTRAINT active_pk PRIMARY KEY (username),
CONSTRAINT active_1 UNIQUE (studentid)
);
CREATE TABLE current_list (
username VARCHAR(15) NOT NULL,
properties CLOB NOT NULL,
CONSTRAINT current_pk PRIMARY KEY (username),
CONSTRAINT current_1 FOREIGN KEY (username)
REFERENCES active_list(username)
ON DELETE CASCADE
);
CREATE TABLE activities (
activity VARCHAR(15) NOT NULL,
title VARCHAR(255) NOT NULL,
selfreg CHAR(1) DEFAULT NULL,
CONSTRAINT activities_pk PRIMARY KEY (activity)
);
CREATE TABLE registrations (
username VARCHAR(15) NOT NULL,
activity VARCHAR(15) NOT NULL,
role SMALLINT DEFAULT 0,
CONSTRAINT registrns_pk PRIMARY KEY (username,activity),
CONSTRAINT registrns_1 FOREIGN KEY (username)
REFERENCES users(username)
ON DELETE CASCADE,
CONSTRAINT registrns_2 FOREIGN KEY (activity)
REFERENCES activities(activity)
ON DELETE CASCADE
);
CREATE TABLE actions (
id INTEGER GENERATED ALWAYS AS IDENTITY,
activity VARCHAR(15) NOT NULL,
title VARCHAR(255) NOT NULL,
duration INTEGER NOT NULL DEFAULT 0,
actionxml CLOB NOT NULL,
author VARCHAR(15) NOT NULL,
time TIMESTAMP NOT NULL,
CONSTRAINT actions_pk PRIMARY KEY (id),
CONSTRAINT actions_1 UNIQUE (activity, title),
CONSTRAINT actions_2 FOREIGN KEY (activity)
REFERENCES activities(activity)
ON DELETE CASCADE
);
CREATE TABLE action_log (
id INTEGER GENERATED ALWAYS AS IDENTITY,
action INTEGER NOT NULL,
username VARCHAR(15) NOT NULL,
time TIMESTAMP DEFAULT NULL,
started TIMESTAMP DEFAULT NULL,
endtime TIMESTAMP DEFAULT NULL,
status CHAR(1),
content CLOB DEFAULT NULL,
CONSTRAINT actionlog_pk PRIMARY KEY (id),
CONSTRAINT actionlog_1 UNIQUE (action,username),
CONSTRAINT actionlog_2 FOREIGN KEY (username)
REFERENCES active_list(username)
ON DELETE CASCADE,
CONSTRAINT actionlog_3 FOREIGN KEY (action)
REFERENCES actions(id)
ON DELETE CASCADE,
CONSTRAINT actionlog_4 CHECK (status IN
('A','B','E','F','N','P','Q','S'))
);
--
John English
