Issue Details (XML | Word | Printable)

Key: CORE-6258
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Unassigned
Reporter: Attila Molnár
Votes: 0
Watchers: 4
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Cannot alter or drop procedure - "there are x dependencies." on FB30

Created: 27/Feb/20 04:14 PM   Updated: 28/Feb/20 07:37 AM
Component/s: Engine
Affects Version/s: 3.0.5
Fix Version/s: None

QA Status: No test


 Description  « Hide
exception on commit trying ro ALTER or DROP a procedure.

create or alter procedure GL_USER (
    CSAK_EHHEZ_A_PELDANYHOZ varchar(1))
returns (
    ID integer,
    UNEV varchar(30),
    TNEV varchar(70),
    GL_ROLE_ID integer,
    EMAIL varchar(100))
AS
declare variable conn xszoveg;
declare variable pass xvar30;
declare variable gl_pldid integer;
begin
  SELECT c.conn
  FROM gl_getdbconn('Libra3s') c
  INTO :conn;
  IF (conn IS NULL)
  THEN EXECUTE PROCEDURE gl_exception('gl_sql_run.conn.null');

  SELECT rdb$get_context('USER_SESSION', 'PASSWORD_USER')
  FROM dual
  INTO :pass;
  IF (pass IS NULL)
  THEN EXECUTE PROCEDURE gl_exception('gl_sql_run.pass.null');

  select ertek from xpref_olvas('GL_PLDID')
  into gl_pldid;

  for execute statement 'select xu.id, xu.unev, xu.tnev, xu.gl_role_id, xu.email from xuser xu ' ||
  iif(:csak_ehhez_a_peldanyhoz is not distinct from 'I', 'where exists(select 1 from xuserpld up where up.xuser_id = xu.id and up.xpld_id = ' || :gl_pldid || ')', '')
  on external :conn
  as user current_user
  role current_role
  password :pass
  into id, unev, tnev, :gl_role_id, :email do
    begin
      suspend;
    end
end

drop procedure gl_user

select *
from rdb$dependencies d
where d.rdb$dependent_name in ('GL_USER', 'RDB$112355', 'RDB$112354', 'RDB$112356') or
    d.rdb$depended_on_name in ('GL_USER', 'RDB$112355', 'RDB$112354', 'RDB$112356')



"Cannot commit transaction:
This operation is not defined for system tables.
unsuccessful metadata update.
cannot delete.
DOMAIN RDB$112355.
there are 2 dependencies."

The reported dependency is wrong, the procedure do not depend on the named domain.

I can send you the database.

This is a stopper for us, until this is not fixed, we cannot upgrade to FB30.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Attila Molnár added a comment - 27/Feb/20 04:17 PM
Might be related to CORE-4844?

Attila Molnár added a comment - 27/Feb/20 04:25 PM
Also might be related to CORE-6257?

Sean Leyne added a comment - 27/Feb/20 06:40 PM
Are you committing between creating the SP and dropping it?

Attila Molnár added a comment - 27/Feb/20 06:57 PM
The procedure already exists with empty body, and the goal is to alter and fill the body with create or alter procedure GL_USER.
The ALTER fails with the "cannot delete"

I just tested with DROP and it also fails, but it is not part of our process.
Our process (database schema update) :
- create or alter evrey proc and view with empty body
- then alter them with body,
In this way we don't have to run create or alter commands in dependecy order.
This method works fine on FB25.

Previously a view was depended on this proc, but the view body is also "empty" currenty, that is why it may link to CORE-6257.

Sean Leyne added a comment - 27/Feb/20 07:31 PM
You have not answered my question... are you committing between *any/separate* changes to the SPs?

Attila Molnár added a comment - 28/Feb/20 07:32 AM
Yes, there is a commit after every DDL command.

Attila Molnár added a comment - 28/Feb/20 07:34 AM
Found a workaround, the proceure out parameters are defined with domains

returns (
    ID XIDN,
    UNEV XVAR30N,
    TNEV XVAR70N,
    GL_ROLE_ID XID,
    EMAIL XVAR100)