Solved

Query within a query using a function in Postgres on Greenplum

Posted on 2014-01-17
18
659 Views
Last Modified: 2014-12-14
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
Comment
Question by:MRPT
  • 9
  • 4
  • 2
  • +2
18 Comments
 
LVL 11

Expert Comment

by:tel2
ID: 39789827
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
 

Author Comment

by:MRPT
ID: 39789870
The function is huge and is confidential. I cannot post the function.
0
 
LVL 11

Expert Comment

by:tel2
ID: 39789875
OK.  Does the function work when called by other SQL code?
0
 
LVL 8

Expert Comment

by:Surrano
ID: 39793913
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
 

Author Comment

by:MRPT
ID: 39794213
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
 
LVL 8

Expert Comment

by:Surrano
ID: 39794234
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
 
LVL 8

Expert Comment

by:Surrano
ID: 39794265
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
 
LVL 8

Expert Comment

by:Surrano
ID: 39794274
Moreover, is this a trigger function?
Is it immutable, strict or volatile?
0
 
LVL 8

Expert Comment

by:Surrano
ID: 39794283
And finally: set log_error_verbosity = VERBOSE in your postgresql configuration (needs SIGHUP to the rdbms processes to take effect)
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:MRPT
ID: 39806882
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
 
LVL 8

Expert Comment

by:Surrano
ID: 39807117
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
 

Author Comment

by:MRPT
ID: 39807302
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
 
LVL 8

Expert Comment

by:Surrano
ID: 39807382
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
 
LVL 61

Expert Comment

by:gheist
ID: 39808484
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
 
LVL 8

Expert Comment

by:Surrano
ID: 39811274
@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
 
LVL 22

Accepted Solution

by:
earth man2 earned 500 total points
ID: 39825988
could this behaviour be limited to greenplum, rather than postgresql ?

What is the error message - explicitly what is "table_name" is it "test1" ?
0
 
LVL 22

Expert Comment

by:earth man2
ID: 39826000
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
 
LVL 8

Expert Comment

by:Surrano
ID: 39831758
@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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now