I have a table with the following column:
The value is used as a fk in other tables. I've gone through all the tables and set both On Delete and On Update to Cascade, but when I edit the value I get this weird error that says Missing FROM-clause entry for table "new_table".... there is no table with that name.
How I Got Here
I set JS to run on page load for an app, it checks if current_user.id is in our database and navigates the user to another app..... I didn't consider the consequences of this 
and now when I try to edit the app it automatically navigates somewhere else. My solution was to change the user_id in the database so the lookup fails, but I'm not sure that's going to work now.
Solution
the problem was actually from a function I added as a trigger. I had to use DbSchema to figure this out:
Broken:
CREATE OR REPLACE FUNCTION public.process_tg_user()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
IF (TG_OP = 'INSERT') THEN
INSERT INTO ainsure_telegram_features (telegram_id, ai_chat_qa, link_ainsure_user, edit_telegram_email, edit_telegram_phone)
SELECT n.telegram_id, true, true, false, false FROM new_table n
ON CONFLICT DO NOTHING;
ELSEIF (TG_OP = 'UPDATE') THEN
UPDATE ainsure_telegram_features
SET (telegram_id) = new_table.telegram_id
WHERE new_table.telegram_id != old_table.telegram_id;
END IF;
RETURN NULL;
END;
$function$
;
Fixed:
CREATE OR REPLACE FUNCTION public.process_tg_user()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
IF (TG_OP = 'INSERT') THEN
INSERT INTO ainsure_telegram_features (telegram_id, ai_chat_qa, link_ainsure_user, edit_telegram_email, edit_telegram_phone)
VALUES (NEW.telegram_id, true, true, false, false)
ON CONFLICT DO NOTHING;
ELSEIF (TG_OP = 'UPDATE') THEN
UPDATE ainsure_telegram_features
SET telegram_id = NEW.telegram_id
WHERE telegram_id = OLD.telegram_id;
END IF;
RETURN NULL;
END;
$function$
;
Feature Requests(maybe?)
- More robust error messages for PSQL
- Display at least a placeholder for functions not originating from Retool as a tab along with tables and views.... or add a default table to hold function names we can view (while this wouldn't help find the error above, it could be step #1 in referencing functions in errors and linking to broken code or something)
- Add a setting/option to force navigation to open in a new tab even when explicitly set to same-tab while in edit mode
I have gotten myself into a similar situation before where I used the history url param to step back and revert the redirect event, but that can be tricky if you made a bunch of other edits
