Execute SQL Task didn't work correctly with functions

More
5 years 11 months ago #11895 by yello
yello created the topic: Execute SQL Task didn't work correctly with functions
Hi,

i've found a new problem.
On Greenplum i've made a function. I will start this function with SSIS "Execute SQL Task".
The Task finished successfully in one second, normaly the function works for over one minute!

<!-- s:!: --><img src="{SMILIES_PATH}/icon_exclaim.gif" alt=":!:" title="Exclamation" /><!-- s:!: --> A lookup in the database shows, that the function didn't run in reality <!-- s:!: --><img src="{SMILIES_PATH}/icon_exclaim.gif" alt=":!:" title="Exclamation" /><!-- s:!: -->

Here is the Function:

CREATE OR REPLACE FUNCTION misc.ysp_full_tabledistribution()
RETURNS text AS
$BODY$
--XXX
DECLARE
integer_var int = 0;
tablename_var text;
variable_name text;
SQL text;

BEGIN
truncate table misc.tabledistribution;

WHILE integer_var < (select max(id) from misc.tables) LOOP

integer_var = integer_var + 1;

tablename_var = (select tablename from misc.tables where id = integer_var);

variable_name = '''' || tablename_var || '''';

SQL = 'insert into misc.tabledistribution select ''' || tablename_var ||''' , gp_segment_id, count(*) anzahlimsegment from ' || tablename_var || ' group by gp_segment_id';

EXECUTE SQL;

END LOOP;
return SQL;
--return tablename_var;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
ALTER FUNCTION misc.ysp_full_tabledistribution()
OWNER TO misc_role;


I do not know what has to be done, that the funciton will executed in a normal way.
Attachments:

Please Log in or Create an account to join the conversation.

More
5 years 10 months ago #12024 by Moderator
Moderator replied the topic: Re: Execute SQL Task didn't work correctly with functions
I tried to reproduce the issue using a similar procedure and a SSIS task, and it worked as expected. Could you try the following:
1. make sure that the latest build of the PGNP OLEDB Provider is used;
2. simplify the stored procedure, and see if the simple version works, e.g.
    BEGIN
       SQL = 'insert into misc.tbl(fld) values(''my test'')';
       EXECUTE SQL;
       return SQL;
    END
3. inspect Greenplum logs (and/or PGNP Profiler's trace), and see if call to the stored procedure succeeded.

Please Log in or Create an account to join the conversation.

More
5 years 10 months ago #12027 by yello
yello replied the topic: Re: Execute SQL Task didn't work correctly with functions
Thank for your reply.
I didn't worked. That's strange.


--i ve created the table.
drop table if exists misc.tbl;
create table misc.tbl(fld varchar(30));

select *
from misc.tbl
--result: completely clean.




--i add your code in this function.
CREATE OR REPLACE FUNCTION misc.fn_tbl_my_test()
RETURNS text AS
$BODY$
DECLARE
SQL text;

--Code from PGOleDB
BEGIN
SQL = 'insert into misc.tbl(fld) values(''my test'')';
EXECUTE SQL;
return SQL;
END;

$BODY$
LANGUAGE plpgsql VOLATILE;
ALTER FUNCTION misc.ysp_full_tabledistribution()
OWNER TO misc_role;


--call in SSIS (no result)
select misc.fn_tbl_my_test()

select *
from misc.tbl
--completely clean :( :(


--call in PGAdminIII (insert one line like expected).
select misc.fn_tbl_my_test()

select *
from misc.tbl
--there is one line like expected.

Do you know where i can find the greenplum logs?

If i start an analyze database function in execute SQL Task, it works!
"truncate table" works too.
Attachments:

Please Log in or Create an account to join the conversation.

More
5 years 10 months ago #12030 by Moderator
Moderator replied the topic: Re: Execute SQL Task didn't work correctly with functions
I guess this issue could be related to lack of Greenplum permissions. Reason why the stored proc works in PGAdmin is that a different user is logged in the PGAdmin. Could you double-check if permissions granted to the right users?

Please refer to Greenplum Admin Guide for location of the logs. I think they can be found by the command: $ find /gpmaster -name *.log

Please Log in or Create an account to join the conversation.

More
5 years 10 months ago #12032 by yello
yello replied the topic: Re: Execute SQL Task didn't work correctly with functions
I tried with the GPAdmin Account. It didn't work.
I will search for the GP logs today. Maybe there is the answer.

Please Log in or Create an account to join the conversation.

More
5 years 10 months ago #12033 by yello
yello replied the topic: Re: Execute SQL Task didn't work correctly with functions
Now it works!

The function has to be called without "select" and ";".
Resultset = "None"

The return of the function must be set to "1", like the example:

CREATE OR REPLACE FUNCTION misc.ysp_analyse_tabledistribution()
RETURNS integer AS
$BODY$
DECLARE
BEGIN

truncate table misc.analyse_tabledistribution;

--ta ta ta

return 1;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
ALTER FUNCTION misc.ysp_analyse_tabledistribution()
OWNER TO misc_role;


Thx, Daniel
Attachments:

Please Log in or Create an account to join the conversation.

Time to create page: 0.081 seconds
Powered by Kunena Forum