Обсуждение: Views and triggers more then one row returned by subquery.
My presumption of views and instead of trigger behavior is that the VIEW first gets populated with the WHERE filter and then the "DELETE or UPDATE" operation will fire against each of the rendered view rows. ( ? )
If this is true then I can't explain the more then one row returned error.
[11-1] user=redcom, db=ace_db, app=psql, client=[local] ERROR: 21000: more than one row returned by a subquery used as an expression
[11-2] user=redcom, db=ace_db, app=psql, client=[local] LOCATION: ExecScanSubPlan, nodeSubplan.c:347
[11-3] user=redcom, db=ace_db, app=psql, client=[local] STATEMENT: delete from public.rule_example where rule_head=30;
I would think that if the subquery matter was in my code and I would get a stack trace with a better indicator then this. SO I am presuming it is in a failure to understand the VEIW-TRIGGER process.
my view is modeling a table of translation "steps" in a phone switch application. "steps" are the components of "rules" and rules are components of "folders".
The VIEW columns of step, rule_seq, and a rule number are relative and derived from column content.
My triggers operate on rows.step_id which is a unique value across rules and folders.
ace_db=# select * from public.rule_example where rule_head=30;
folder_id | rule_head | rule_seq | rule_step | step_id | opta | optb
-----------+-----------+----------+-----------+---------+------+------
1 | 30 | 1 | 1 | 30 | 0 | 0
1 | 30 | 1 | 2 | 40 | 0 | 0
(2 rows)
However when the scope of my delete is not step scoped it fails:
ace_db=# delete from public.rule_example where rule_head=30;
ERROR: more than one row returned by a subquery used as an expression
ace_db=#
I was expecting each row of the rendered DELETE VIEW to be executed in succession. That is one step at a time. Apparently that is not happening ???
Base Table.
ace_db=# select * from public.my_translator;
---
folder_id | folder_seq | entry_type | opta | optb | step_id
-----------+------------+------------+------+------+---------
1 | 1 | 0 | 0 | 0 | 10
1 | 2 | 6 | 0 | 0 | 20
1 | 3 | 0 | 0 | 0 | 30
1 | 4 | 6 | 0 | 0 | 40
1 | 5 | 0 | 0 | 0 | 50
1 | 6 | 6 | 0 | 0 | 60
(6 rows)
View of Base Table.
ace_db=# select * from public.rule_example;
folder_id | rule_head | rule_seq | rule_step | step_id | opta | optb
-----------+-----------+----------+-----------+---------+------+------
1 | 10 | 1 | 1 | 10 | 0 | 0
1 | 10 | 1 | 2 | 20 | 0 | 0
1 | 30 | 2 | 1 | 30 | 0 | 0
1 | 30 | 2 | 2 | 40 | 0 | 0
1 | 50 | 3 | 1 | 50 | 0 | 0
1 | 50 | 3 | 2 | 60 | 0 | 0
(6 rows)
My trigger works appropriately if the scope is a step.
ace_db=# delete from public.rule_example where step_id=20;
DELETE 1
ace_db=# delete from public.rule_example where step_id=10;
DELETE 1
Thanks for any insights that might make this work when the scope of the WHERE is rule or folder targeted.
Dave Day
Here is code that represent above.. .
DROP TABLE IF EXISTS public.my_translator CASCADE;
CREATE TABLE public.my_translator (
folder_id int not null,
folder_seq int not null, -- 1-n, each rule in folder has unique value.
entry_type int default 0, -- 0 rule start, 6 rule append
optA int default 0,
optB int default 0,
step_id int primary key
);
--Add sample date to my_translator table.
INSERT INTO public.my_translator
(folder_id, folder_seq, entry_type, step_id )
VALUES -- insert three 2 step rules into folder 1
( 1,1,0,10), -- folder one, folder seq 1, rule 1 step 1
( 1,2,6,20), -- folder one, folder seq 2, rule 1 step 2
( 1,3,0,30), -- folder one, folder seq 3, rule 2 step 1
( 1,4,6,40), -- folder one, folder seq 4, rule 2 step 2
( 1,5,0,50), -- folder one, folder seq 5, rule 3 step 1
( 1,6,6,60); -- folder one, folder seq 6, rule 3 step 2
-- A function that determines a rules order within a folder.
CREATE OR REPLACE FUNCTION public.get_rule_seq( _fid int, _fseq int )
RETURNS integer
LANGUAGE plpgsql
AS $function$
DECLARE _rule_seq int := 1;
BEGIN
-- given a folder and step within that folder determine what
-- the conceptual rule sequence is for the step.
WITH rules AS (
SELECT folder_seq, row_number() over ( ORDER by folder_seq ASC)
FROM public.my_translator tx
WHERE tx.folder_id = _fid AND
entry_type = 0
)
SELECT MAX (row_number) FROM rules
WHERE folder_seq <= _fseq INTO _rule_seq;
RETURN _rule_seq;
END
$function$;
-- The view that breaks down the details of a rule.
-- Shows the folder, the rule and individual steps of a rule.
CREATE OR REPLACE VIEW public.rule_example AS
SELECT
tum.folder_id,
( WITH rule_heads AS (
-- Find rule heads for each rule in folder.
SELECT public.get_rule_seq( tx.folder_id, tx.folder_seq) AS rule_seq, tx.step_id
FROM public.my_translator tx
WHERE tx.folder_id = tum.folder_id
AND entry_type = 0
)
-- Find the rule head for the current row. (tum.folder_seq),
SELECT step_id FROM rule_heads rh
WHERE rh.rule_seq = public.get_rule_seq(tum.folder_id, folder_seq)
) AS rule_head, -- every step of same rule has the same head.
-- The rule sequence, 1-n, is determined by the rules in same folder.
public.get_rule_seq( tum.folder_id, folder_seq) AS rule_seq,
-- Make sure steps always begin with 1 for each of its rules.
( SELECT ((tum.folder_seq - MAX(folder_seq)) + 1)
FROM public.my_translator
WHERE folder_id = tum.folder_id
AND folder_seq <= tum.folder_seq
AND entry_type = 0 ) AS rule_step,
tum.step_id,
tum.optA,
tum.optB
FROM public.my_translator tum
ORDER BY tum.folder_id, tum.folder_seq asc;
CREATE OR REPLACE FUNCTION public.rule_delete_and_decrement(
_step_id INTEGER
)
RETURNS VOID
LANGUAGE plpgsql VOLATILE
AS $BODY$
DECLARE
_del_count INT;
_dec_count INT;
_obj_seq INT;
_next_obj_seq INT;
_max_obj_seq INT;
_del_ids INTEGER[];
_dec_ids INTEGER[];
_mbr INT;
_tu_id INT;
BEGIN
-- Get the obj_seq for the start of the rule being deleted,
-- the start of the next rule, and the end of the list.
SELECT folder_seq, folder_id INTO _obj_seq, _tu_id
FROM public.my_translator
WHERE step_id = _step_id;
SELECT MIN(folder_seq) INTO _next_obj_seq
FROM public.my_translator
WHERE folder_id = _tu_id
AND folder_seq > _obj_seq
AND entry_type = 0;
SELECT MAX(folder_seq)+1 INTO _max_obj_seq
FROM public.my_translator
WHERE folder_id = _tu_id;
-- _next_obj_seq is the end of the list if it is null (meaning
-- that this is deleteing the last rule in the sequence)
IF _next_obj_seq IS NULL THEN
_next_obj_seq = _max_obj_seq;
END IF;
-- Set del_count = amount of rows being deleted,
-- Set dec_count = amount of rows that must be decremented.
_del_count = _next_obj_seq - _obj_seq;
_dec_count = _max_obj_seq - _next_obj_seq;
-- Get the ids of the rows that need to be decremented
-- in the order that they need to be decremented
SELECT ARRAY_AGG(step_id ORDER BY folder_seq ASC ) INTO _dec_ids
FROM public.my_translator
WHERE folder_id = _tu_id
AND folder_seq >= _next_obj_seq;
-- Get the ids of the rows that need to be deleted
SELECT ARRAY_AGG(step_id ORDER BY folder_seq ASC ) INTO _del_ids
FROM public.my_translator
WHERE folder_id = _tu_id
AND folder_seq >= _obj_seq AND folder_seq < _next_obj_seq;
-- Delete the necessary rows
DELETE FROM public.my_translator
WHERE step_id = ANY(_del_ids);
-- Decrement the necessary rows in the correct order
IF ARRAY_LENGTH(_dec_ids,1) > 0 THEN
FOREACH _mbr IN ARRAY _dec_ids
LOOP
UPDATE public.my_translator
SET folder_seq = folder_seq - _del_count
WHERE step_id = _mbr;
END LOOP;
END IF;
RETURN;
END
$BODY$;
GRANT EXECUTE ON FUNCTION public.rule_delete_and_decrement(INT)
TO rest_user_max;
COMMENT ON FUNCTION public.rule_delete_and_decrement(INT)
IS $TEXT$
This function will delete a rule and all of its appended steps from the database
and decrement the rows above it by the amount of rows deleted.
$TEXT$;
CREATE OR REPLACE FUNCTION public.rule_delete()
RETURNS TRIGGER AS
$BODY$
BEGIN
IF OLD.rule_step = 1 THEN
-- Remove entire rule and Reassign obj_seq of succeeding rules.
PERFORM public.rule_delete_and_decrement( OLD.step_id);
ELSE
-- Only remove the single step.
DELETE FROM public.my_translator
WHERE step_id = OLD.step_id;
END IF;
RETURN OLD;
END;
$BODY$
LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION public.rule_delete() TO rest_user_max;
CREATE TRIGGER rule_advanced_delete_trigger
INSTEAD OF DELETE ON public.rule_example
FOR EACH ROW
EXECUTE PROCEDURE public.rule_delete();
"Day, David" <david.day@redcom.com> writes:
> My presumption of views and instead of trigger behavior is that the VIEW first gets populated with the WHERE filter
andthen the "DELETE or UPDATE" operation will fire against each of the rendered view rows. ( ? )
> If this is true then I can't explain the more then one row returned error.
This code makes my head hurt :-(
However, it's fairly easy to tell that the trigger successfully completes
on the first view row (you can check that by sticking some RAISE NOTICE
commands in it) and then the error is thrown while evaluating the next
view row. The error has to be complaining about the "WITH rule_heads ..."
subquery in the view's targetlist; the only other subquery is the MAX()
subquery, which most certainly isn't going to return more than one row.
The trigger is evidently running rule_delete_and_decrement(), which
I am not interested in deconstructing in full, but I can see that
it modifies the contents of the my_translator table. So what must
be happening is that the "WITH rule_heads ..." subquery is returning
more than one row after that modification occurs.
I have a rough theory as to why, though I'm not planning on tracing it
down in detail. The result of the WITH clause itself *does not see the
deletion*, as specified somewhere in our fine manual. (That part is
consistent with your expectation that the view output doesn't change
while this is all going on: my_translator is being scanned using the
original query snapshot, so the subquery doesn't see the already-applied
changes.) So when we re-execute the subquery at the second view row,
the "WITH rule_heads" output is the same as before. On the other hand,
the get_rule_seq() function is going to see the updated contents of
my_translator, since it's declared VOLATILE. I think that this
inconsistency results in more than one row getting let through the
WHERE filter, and voila we get the error.
You might be able to fix this by marking get_rule_seq() as STABLE
so that it sees the same snapshot as the calling query. At least,
when I change it to stable I don't see the error anymore. Whether
things are then consistent with your intent, I can't say. But
I will say that this code is an unmaintainable pile of spaghetti,
because when the side-effects occur and where they're visible
is going to be almost impossible to keep track of.
regards, tom lane
Tom,
You are exactly right about STABLE needed on the get_rule_seq function.
That resolved my issues.
I've been burned before by using a function within a view/WHERE situation and the function was not marked STABLE.
I need to start taking PREMAGEN.
I Kind of came to the same conclusion in parrallel after I ran the explain analyze on the select and wondered why the hell it was going about it like that.
Yes keeping the steps of a rule properly associated and ordered with a folder is mind bending. Sorry you had to see it.
Thanks so much for analysis and comments..
Regards
Dave
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Tuesday, January 12, 2021 6:24 PM
To: Day, David <david.day@redcom.com>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Views and triggers more then one row returned by subquery.
Sent: Tuesday, January 12, 2021 6:24 PM
To: Day, David <david.day@redcom.com>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Views and triggers more then one row returned by subquery.
"Day, David" <david.day@redcom.com> writes:
> My presumption of views and instead of trigger behavior is that the VIEW first gets populated with the WHERE filter and then the "DELETE or UPDATE" operation will fire against each of the rendered view rows. ( ? )
> If this is true then I can't explain the more then one row returned error.
This code makes my head hurt :-(
However, it's fairly easy to tell that the trigger successfully completes
on the first view row (you can check that by sticking some RAISE NOTICE
commands in it) and then the error is thrown while evaluating the next
view row. The error has to be complaining about the "WITH rule_heads ..."
subquery in the view's targetlist; the only other subquery is the MAX()
subquery, which most certainly isn't going to return more than one row.
The trigger is evidently running rule_delete_and_decrement(), which
I am not interested in deconstructing in full, but I can see that
it modifies the contents of the my_translator table. So what must
be happening is that the "WITH rule_heads ..." subquery is returning
more than one row after that modification occurs.
I have a rough theory as to why, though I'm not planning on tracing it
down in detail. The result of the WITH clause itself *does not see the
deletion*, as specified somewhere in our fine manual. (That part is
consistent with your expectation that the view output doesn't change
while this is all going on: my_translator is being scanned using the
original query snapshot, so the subquery doesn't see the already-applied
changes.) So when we re-execute the subquery at the second view row,
the "WITH rule_heads" output is the same as before. On the other hand,
the get_rule_seq() function is going to see the updated contents of
my_translator, since it's declared VOLATILE. I think that this
inconsistency results in more than one row getting let through the
WHERE filter, and voila we get the error.
You might be able to fix this by marking get_rule_seq() as STABLE
so that it sees the same snapshot as the calling query. At least,
when I change it to stable I don't see the error anymore. Whether
things are then consistent with your intent, I can't say. But
I will say that this code is an unmaintainable pile of spaghetti,
because when the side-effects occur and where they're visible
is going to be almost impossible to keep track of.
regards, tom lane
> My presumption of views and instead of trigger behavior is that the VIEW first gets populated with the WHERE filter and then the "DELETE or UPDATE" operation will fire against each of the rendered view rows. ( ? )
> If this is true then I can't explain the more then one row returned error.
This code makes my head hurt :-(
However, it's fairly easy to tell that the trigger successfully completes
on the first view row (you can check that by sticking some RAISE NOTICE
commands in it) and then the error is thrown while evaluating the next
view row. The error has to be complaining about the "WITH rule_heads ..."
subquery in the view's targetlist; the only other subquery is the MAX()
subquery, which most certainly isn't going to return more than one row.
The trigger is evidently running rule_delete_and_decrement(), which
I am not interested in deconstructing in full, but I can see that
it modifies the contents of the my_translator table. So what must
be happening is that the "WITH rule_heads ..." subquery is returning
more than one row after that modification occurs.
I have a rough theory as to why, though I'm not planning on tracing it
down in detail. The result of the WITH clause itself *does not see the
deletion*, as specified somewhere in our fine manual. (That part is
consistent with your expectation that the view output doesn't change
while this is all going on: my_translator is being scanned using the
original query snapshot, so the subquery doesn't see the already-applied
changes.) So when we re-execute the subquery at the second view row,
the "WITH rule_heads" output is the same as before. On the other hand,
the get_rule_seq() function is going to see the updated contents of
my_translator, since it's declared VOLATILE. I think that this
inconsistency results in more than one row getting let through the
WHERE filter, and voila we get the error.
You might be able to fix this by marking get_rule_seq() as STABLE
so that it sees the same snapshot as the calling query. At least,
when I change it to stable I don't see the error anymore. Whether
things are then consistent with your intent, I can't say. But
I will say that this code is an unmaintainable pile of spaghetti,
because when the side-effects occur and where they're visible
is going to be almost impossible to keep track of.
regards, tom lane