• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 992
  • Last Modified:

Query within a query using a function in Postgres on Greenplum

I am trying to run this query and getting error as below.

select test1.*, test_function(a.column1, a.column2, current_date-6, current_date)
from test1  a;

Error:
function cannot execute on segment because it accesses relation "table name"

The function returns a single number.

-Thanks
0
MRPT
Asked:
MRPT
  • 9
  • 4
  • 2
  • +2
1 Solution
 
tel2Commented:
Hi MRPT,

Please show us the test_function.  Knowing what it is meant to return is not sufficient for me to work this out.

Thanks.
0
 
MRPTAuthor Commented:
The function is huge and is confidential. I cannot post the function.
0
 
tel2Commented:
OK.  Does the function work when called by other SQL code?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
SurranoSystem EngineerCommented:
It's because you refer to the table as alias and as original name. Try either of these:

-- alias everywhere
select a.*, test_function(a.column1, a.column2, current_date-6, current_date)
from test1 a;

-- orig name everywhere
select test1.*, test_function(test1.column1, test1.column2, current_date-6, current_date)
from test1;

-- alias but only for fields; 
-- not needed for function params if there's no duplicate name
select a.*, test_function(column1, column2, current_date-6, current_date)
from test1 a;

-- orig name but only for fields; 
-- not needed for function params if there's no duplicate name
select test1.*, test_function(column1, column2, current_date-6, current_date)
from test1;

Open in new window

0
 
MRPTAuthor Commented:
Tried all queries mentioned above and still doesn't work.

The function works when individual values are passed, but when I pass in the values by referencing to a column then I get the error.

Thanks
0
 
SurranoSystem EngineerCommented:
Does it work if you limit it to a single record?

select a.*, test_function(a.column1, a.column2, current_date-6, current_date)
from test1 a limit 1;

-- if that doesn't work try this
select a.*, test_function(a.column1, a.column2, current_date-6, current_date)
from (select * from test1 limit 1) a;

Open in new window

0
 
SurranoSystem EngineerCommented:
Also, if it doesn't work with limits, check what happens if you substitute the exact same values:

select column1, column2 from test1 limit 5;

-- one-by-one, substitute the lines here
select test_function(literal1, literal2, current_date-6, current_date);

Open in new window


Can you possibly give some hints on the exact environment?
- You can't tell too much about the function but can you tell its signature? (parameter list, return value and language)
- Table structure of test1? Maybe some sample data from there?
- Version information? OS, Greenplum, PostgreSQL?
- Did you test it using psql command line prompt or some nifty GUI like pgAdmin? I'd say anything other than psql is an unnecessary extra layer of complexity.
0
 
SurranoSystem EngineerCommented:
Moreover, is this a trigger function?
Is it immutable, strict or volatile?
0
 
SurranoSystem EngineerCommented:
And finally: set log_error_verbosity = VERBOSE in your postgresql configuration (needs SIGHUP to the rdbms processes to take effect)
0
 
MRPTAuthor Commented:
Hi Surrano,
Here is some of the information that you requested.

1. I tried the limits and it doesn't work. I took 5 records from the table and substituted the exact values one after the other in the function and I get the desired output.

2. There are 4 parameters I am passing in and I have the return as NUMERIC. Here is what I have:

 RETURNS numeric AS
$BODY$
---
--
 $BODY$
 LANGUAGE sql VOLATILE SECURITY DEFINER;

3. I am executing the query in pgdmin.

4. It is not a trigger function


Thanks
0
 
SurranoSystem EngineerCommented:
Hello MRPT,

1. Strange.

2. What are the 4 parameter types?

3. Can you please try in psql?

4. OK.

5. Did you manage to get more details in server log by setting log_error_verbosity to VERBOSE?
0
 
MRPTAuthor Commented:
Hi,

2. Here are the parameter types (character varying, character varying, timestamp without time zone, timestamp without time zone)

3. I will try in plsql.

5. I am unable to set the server log to VERBOSE. I get a syntax error.
set log_error_verbosity to VERBOSE or set log_error_verbosity = VERBOSE throws me
ERROR: syntax error at or near "VERBOSE"

-Thanks
0
 
SurranoSystem EngineerCommented:
Hello,

3. Not plsql ; psql :)

5. It's not a set command at command line nor pgadmin; it is a config parameter that has to be set in postgresql.conf (at least in stock postgresql; not sure about greenplum). Once modified, you'll have to send a SIGHUP (hangup) signal to the postmaster process (it doesn't cause outage) or restart it (it does).

By the way, the default location for the log file is usually /var/log/postgresql/postgresql*.log in most unix/linux distributions.
0
 
gheistCommented:
You can test a statement in verbose mode.
It will tell where it deadlocks on relation table_name.

EXPLAIN ANALYZE VERBOSE (your query goes here) ;
0
 
SurranoSystem EngineerCommented:
@gheist: do you think it works with exceptions raised inside a stored procedure as well? I think it works only with deadlocks but this is no deadlock as far as I can tell.

@MRPT: please try it, I'm very curious :)
0
 
earth man2Commented:
could this behaviour be limited to greenplum, rather than postgresql ?

What is the error message - explicitly what is "table_name" is it "test1" ?
0
 
earth man2Commented:
Postgresql SQL function docs....

"Arguments of a SQL function can be referenced in the function body using either names or numbers.

You can avoid ambiguity by choosing a different alias for the table within the SQL command.

In the older numeric approach, arguments are referenced using the syntax $n: $1 refers to the first input argument, $2 to the second, and so on. This will work whether or not the particular argument was declared with a name.

you might need to qualify the argument's name with the function name to make the form with an argument name unambiguous.

SQL function arguments can only be used as data values, not as identifiers."

---------------------------------------------------------------------------------------------------------------

Where you use the values in the function have you tried using $1 syntax instead of argument name ?

trying a specific cast on the parameters may help to track down this behaviour
ie

select test1.*, test_function(cast(test1.column1 as character varying), cast(test1.column2 as character varying), cast(current_date-6 as timestamp without time zone), cast(current_date as timestamp without time zone) from test1;

I guess if you use a pgplsql function this problem will go away.
0
 
SurranoSystem EngineerCommented:
@earthman2:
could this behaviour be limited to greenplum,

Definitely; the term "segment" in the error message exists in greenplum and I don't think it exists in pure postgresql.

@MRPT: still waiting for some useful postgresql.log snippet.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 9
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now