For each SQL statement inside a stored procedure, function, trigger or event use
an SQL prepared statement,
so that it can be validated and re-prepared upon a change in metadata.
Do not prepare all statements when a program is loaded: prepare them on
demand, before first execution.

Rationale:

Same as the rationale of WL#4165, but applies to the case when all
statements are part of the same stored program.
Necessary to fix:
BUG#32868  Stored routines do not detect changes in meta-data.
BUG#33082  Stored Procedure: crash if table replaced with a view in a loop
BUG#33083  	Stored Function: error if a temporary table is dropped in a loop
BUG#33000  Triggers do not detect changes in meta-data.
BUG#27011 Stored Procedures: bad data if view used inside a SP is dropped or
modified
BUG#33289 Stored Procedure: bad data if view is replaced within a iteration

If this task is implemented, invalidation of stored routine caches will become
unnecessary, since individual stored routines will not be affected by DDL.
Therefore, this task supersedes WL#4178.

Note, the impact of the problem this task aims to solve increases significantly
the moment we stop invalidating the stored procedure cache whenever a view is
created or altered (WL#4299 is implemented).
One won't need to execute a stored procedure instruction twice within 
one invocation of a stored procedure to trigger a crash or a bad data -- two
invocations of the same procedure
intermixed with ALTER or CREATE VIEW will lead to the same pitiful result.

• WL#4166: Prepared statements: automatic re-prepare

Pre-locking issue: a re-prepare of an individual trigger or stored function

statement that uses a

view may yield a different set of tables to pre-lock. 

You must be logged in to tag this worklog

No Comments yet