- Forum
- Main PGNP Forum
- SQL Server/DTS/SSIS/Linked servers/Replication
- Execute SQL Task didn't work correctly with functions
Execute SQL Task didn't work correctly with functions
- yello
- Topic Author
- Visitor
-
8 years 4 months ago #11895
by yello
Execute SQL Task didn't work correctly with functions was created by yello
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.
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="


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.
Please Log in or Create an account to join the conversation.
8 years 4 months ago #12024
by Moderator
Replied by Moderator on 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.3. inspect Greenplum logs (and/or PGNP Profiler's trace), and see if call to the stored procedure succeeded.
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
Please Log in or Create an account to join the conversation.
- yello
- Topic Author
- Visitor
-
8 years 4 months ago #12027
by yello
Replied by yello on 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.
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.
Please Log in or Create an account to join the conversation.
8 years 4 months ago #12030
by Moderator
Replied by Moderator on 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 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.
- yello
- Topic Author
- Visitor
-
8 years 4 months ago #12032
by yello
Replied by yello on 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.
I will search for the GP logs today. Maybe there is the answer.
Please Log in or Create an account to join the conversation.
- yello
- Topic Author
- Visitor
-
8 years 4 months ago #12033
by yello
Replied by yello on 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
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
Please Log in or Create an account to join the conversation.
- Forum
- Main PGNP Forum
- SQL Server/DTS/SSIS/Linked servers/Replication
- Execute SQL Task didn't work correctly with functions
Time to create page: 0.062 seconds
- You are here:
-
Home
-
Forum
-
Main PGNP Forum
-
SQL Server/DTS/SSIS/Linked servers/Replication
- Execute SQL Task didn't work correctly with functions