|
Hi, With SA 16.0.0.1948, in sa_make_object, does anyone know if there is a particular reason why for event objects, the procedure drop the event if it exists, instead of only creating it like other object type ? This cause a problem because if an event has a schedule (or is a system event), calling sa_make_object for the event cause the schedule(s) to be dropped. |
|
There is really no good answer to say why sa_make_object() does what it does for event objects but there is a reason: I'll explain by giving some history - sa_make_object was created back in the days when the OR REPLACE clause did not exist and (obviously therefore) was not supported. The procedure was added as an easy way to allow SQL 'build' scripts to be run over and over again (by developers) during implementation phase of a project. When you use sa_make_object in this scenario - i.e. development (not production) - then it does not matter much because the very next statement in the SQL script is always an ALTER EVENT that defines the real values for the event. If you are wanting to alter an event in a production system - as you have described - then you should just use ALTER EVENT to do this with no preceding call to sa_make_object(). To respond to Volker's comment about the 'bug' in the sa_make_object implementation - no it is not a bug since events do not have owners. (Yes the CREATE and ALTER EVENT statements accept an owner in the definition but the owner value is ignored - if anything the 'bug' is that these statements should not accept an owner field... but we're stuck with this since we would break backward compatibility). Note that events are executed by the creator, the user that is creating the event, and this is explained in the CREATE EVENT notes. Note the docs do not make it clear that the owner is ignored (I will let the doc team to add this note). Why is there no OR REPLACE clause on the CREATE EVENT statement? An oversight perhaps? (and it is on the list for a future version). But note that the OR REPLACE clause of CREATE EVENT is likely going to just do a DROP EVENT and then a CREATE EVENT under the covers so does not really resolve the issue. FWIW: For reasons explained above, I would recommend anyone that is using events to always just make the HANDLER portion of the event just be a call to a procedure. Doing this resolves a number of things: (a) you end up not needing to change the event definition very often since most changes are to the logic (i.e. procedure) and hence don't run into the issues asked in this question, and (b) you have better control which owner the procedure is executed as (i.e. owner of the procedure), and (c) ... well I can't tell you about c (yet ;-) Mark, while I can follow your reasoning, I would still think it would be more expected when sa_make_object would leave an existing event unchanged (at least when the "owner" is the same) and would only create it if it does not already exist. Like lebpas I'm surprised that sa_make_object would drop an existing event schedule...
(15 Jan '15, 03:50)
Volker Barth
Replies hidden
Do they tell it at all? (Except that there is error message SQLCODE -772 with the cause "You have attempted to create an event with a name that already exists in the database.") - So a clarification here is surely welcome:) One might also add to the docs that ALTER EVENT and DROP EVENT seem to be agnostic of the owner name, too: It seems to be possible to alter and drop events even when specifying a different owner name.
(15 Jan '15, 03:53)
Volker Barth
Replies hidden
Yep, ALTER and DROP event also ignore the owner name. I will pass on the comment to the doc team.
(15 Jan '15, 08:45)
Mark Culp
Here is the reasoning that was presented to me when all of this was being developed: After sa_make_object is called we should leave the object in a consistent state regardless of the original state so that, well, the object is in a consistent known state (i.e. as if the object did not exist beforehand). I'll open up the discussion on this topic within engineering and see what, if anything, we can do about it.
(15 Jan '15, 08:51)
Mark Culp
Hm, maybe I have been too bold here, being too used to the old Watcom rule that SQL Anywhere fits one's expecations even in details:) IMHO, it would be enough if the docs would document the current behaviour. Instead of a behaviour change for sa_make_object() it might be less compatible-problematic to introduce "CREATE OR REPLACE EVENT" which then would be expected (IMHO, anyway) to drop any existing schedule and create only those schedules specified in the statement.
(15 Jan '15, 09:07)
Volker Barth
1
I will modify sa_make_object to better fit with our needs (not dropping events, only creating them). Most of our events don't have schedule when released to the production DB, schedules are defined by the users, this caused some trouble until we found the issue, but it's an easy fix.
(15 Jan '15, 16:01)
lebpas
|
Dummy answer: It does so because it is coded that way - here's an excerpt from that procedure's definition:
elseif "objtype" = 'event' then call "dbo"."sp_check_login_user_perms"('sa_make_object','OE'); if exists (select * from "SYS"."SYSEVENT" where "event_name" = "objname") then execute immediate with quotes on 'drop event "' || "objname" || '"' end if; execute immediate with quotes on 'create event ' || "qualified_objname" || ' handler begin end'Aside: I would have thought the proc would make use of the newer "CREATE OR REPLACE" syntax for views, functions and the like, something that is missing for CREATE EVENT (for unknown reasons) but apparently that is not the case.
I'd call it a bug.
FWIW: It seems that the check for existing events does ignore the owner, as well. AFAIK different owners can have events with the same name (though that may be unusual).
OK, so I stand partially corrected: As Mark has explained, that's not true: Event names must be unique, if there is one event named "MyEvent" for owner X and one tries to create an event "Y.MyEvent", it will fail with SQLCODE -772 "Event 'MyEvent' already exists".