Hi,
I tried to delete a survey today, but it fails:
db_exec_plsql delete_survey {} in survey-delete-postgresql.xql returns an error:
Database operation "0or1row" failed (exception NSDB, "Query was not a statement returning rows.")
while executing
"ns_db 0or1row $db "select $function_name ()""
invoked from within
"db_exec_plpgsql $db $full_statement_name $sql $statement_name"
invoked from within
"if {[regexp -nocase -- {^\s*select} $test_sql match]} {
# ns_log Debug "PLPGSQL: bypassed anon function"
set selection..."
("uplevel" body line 6)
invoked from within
"uplevel 1 $code_block "
invoked from within
"db_with_handle -dbn $dbn db {
# plsql calls that are simple selects bypass the plpgsql
# mechanism for creating anonymous fun..."
(procedure "db_exec_plsql" line 57)
invoked from within
"db_exec_plsql delete_survey {}"
This is really strange. So we changed the call a little:
From
db_exec_plsql delete_survey {}
to
db_0or1row delete_survey {}
and instead of
begin
perform survey__remove(:survey_id);
return NULL;
end;
to
select survey__remove(:survey_id);
And tried to delete the survey again. This time we get the following error:
Database operation "0or1row" failed (exception NSDB, "Query was not a statement returning rows.")
ERROR: update or delete on "acs_objects" violates foreign key constraint "acs_objects_context_id_fk" on "acs_objects"
DETAIL: Key (object_id)=(19065) is still referenced from table "acs_objects".
SQL:
select survey__remove('19065');
while executing
"ns_pg_bind 0or1row nsdb0 {
select survey__remove(:survey_id);
Which is better but still strange. Looking at the function survey__remove there are several other functions called with "perform" that when used with "select" instead work but not when called with "perform". It seems as if the function is not called at all with when called with perform. That why we end up with the foreign key violation when the survey is tried to be deleted since all depending objects still exist.
Has someone an explanation for this behaviour?
