[SQL] FW: Help- post gress sql error
Hi! I am receiving the following error when trying to execute sql Error: ERROR: ExecSubPlan: failed to find placeholder for subplan result (State:HY000, Native Code: 2C) - My query works fine in Oracle database without any issues. I have sub queries with in the sql. If I replace sub queries with hard coded values query works fine. - select ft.a_id ,sum(nvl(ft.t_amt,0)) fee from a_tran ft ,c_sum c1 ,( select c.a_id,max(c.c_end_date)as end_date from c_sum c where c.d_status_id not in (7,10) and c.c_end_date between '01-jun-2008' and '31-jul-2008' group by c.a_id) prev where ft.a_id=prev.a_id and c1.a_id=prev.a_id and c1.c_end_date=prev.end_date and ft.p_date between '01-jul-2008' and '31-jul-2008' and (( ft.t_code in ( select fld1_source_value from tran_code) ) OR ( ft.t_code in ( select fld1_source_value from tran_code_2) and ft.t_description not in ( select tran_dscr from tran_code_dscr) ) ) and ft.p_date between c1.c_start_date and c1.c_end_date group by ft.a_id; -- Please advice if there is any setting that can be made to avoid this problem. Thanks, Prasoona - This message contains PRIVILEGED and CONFIDENTIAL information that is intended only for use by the named recipient. If you are not the named recipient, any disclosure, dissemination, or action based on the contents of this message is prohibited. In such case please notify us and destroy and delete all copies of this transmission. Thank you. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] FW: Help- post gress sql error
"Kota, Prasoona" <[EMAIL PROTECTED]> writes: > I am receiving the following error when trying to execute sql > Error: ERROR: ExecSubPlan: failed to find placeholder for subplan What Postgres version is that? If it's not a current minor release, please try updating, because I seem to remember having fixed some bugs with symptoms like that. If you can reproduce it on any current release version, please send a complete test case (the query alone is pretty useless without tables to try it on) to pgsql-bugs. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Function Syntax involving pipes and ' marks?
Ruben Gouveia wrote: v_where varchar(256) := 'where m.jb_date < '||p_date + integer '1'|| ' and m.jb_date >='||p_date||''; Try wrapping your p_date in a quote_literal like ... 'where m.jb_date < '||quote_literal(p_date+INTEGER '1')||' and ... eg. CREATE OR REPLACE FUNCTION fcn_job(p_date date, p_type varchar,p_jobid numeric) RETURNS numeric AS $$ DECLARE v_job numeric := 0; v_stmt varchar(1024); v_where varchar(256) := 'where m.jb_date < '||quote_literal(p_date + integer '1')||' and m.jb_date >='||quote_literal(p_date); BEGIN v_stmt := fcn_gen_statement(p_type, v_where, p_newonly); execute v_stmt into v_job; RAISE NOTICE 'sql looks like this: % . ',v_stmt; return v_job; END; $$ LANGUAGE plpgsql; -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Function Syntax involving pipes and ' marks?
Ruben Gouveia wrote: Is that more expensive to run than just useing a bunch of ticks? Try wrapping your p_date in a quote_literal like ... 'where m.jb_date < '||quote_literal(p_date+INTEGER '1')||' and ... I personally have never noticed any increased overhead from quote_literal. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Function Syntax involving pipes and ' marks?
Bricklen Anderson <[EMAIL PROTECTED]> writes: > Ruben Gouveia wrote: >> Is that more expensive to run than just useing a bunch of ticks? > I personally have never noticed any increased overhead from quote_literal. Much more important is that you'll reliably get the right answer. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Function Syntax involving pipes and ' marks?
i will try that. thank you On Wed, Sep 10, 2008 at 11:45 AM, Tom Lane <[EMAIL PROTECTED]> wrote: > Bricklen Anderson <[EMAIL PROTECTED]> writes: > > Ruben Gouveia wrote: > >> Is that more expensive to run than just useing a bunch of ticks? > > > I personally have never noticed any increased overhead from > quote_literal. > > Much more important is that you'll reliably get the right answer. > >regards, tom lane >
[SQL] pg_restore in java connection (auto_commit = false)
Good afternoon,
I have a question about pg_restore in JAVA and need your help.
I planed to do:
==
1. pg_dump each tables (done successfully)
2. start a java connection
auto_commit = false
3.for(Table1... ... TableN)
{
drop primary key
pg_restore table (dead lock here)
add primary key
Connection.commit
}
It seems that the above steps caused a lock for the tableX. Could
someone give me some ideas please?
Thanks a lot!
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Function Syntax involving pipes and ' marks?
Is that more expensive to run than just useing a bunch of ticks? Sent from Apple iPhone 3G On Sep 10, 2008, at 11:24 AM, Bricklen Anderson <[EMAIL PROTECTED]> wrote: Ruben Gouveia wrote: v_where varchar(256) := 'where m.jb_date < '||p_date + integer '1'|| ' and m.jb_date >='||p_date||''; Try wrapping your p_date in a quote_literal like ... 'where m.jb_date < '||quote_literal(p_date+INTEGER '1')||' and ... eg. CREATE OR REPLACE FUNCTION fcn_job(p_date date, p_type varchar,p_jobid numeric) RETURNS numeric AS $$ DECLARE v_job numeric := 0; v_stmt varchar(1024); v_where varchar(256) := 'where m.jb_date < '|| quote_literal(p_date + integer '1')||' and m.jb_date >='|| quote_literal(p_date); BEGIN v_stmt := fcn_gen_statement(p_type, v_where, p_newonly); execute v_stmt into v_job; RAISE NOTICE 'sql looks like this: % . ',v_stmt; return v_job; END; $$ LANGUAGE plpgsql; -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Aggregates in WHERE clause?
I tried to do the following and got the following error message: select employee,count(distinct tasks) from job where greatest(max(last_job_date),max(last_position_date)) < 2008-08-28 + integer '1' group by employee; ERROR: aggregates not allowed in WHERE clause ** Error ** ERROR: aggregates not allowed in WHERE clause SQL state: 42803 Is there away around this? Should i create a function to populate a variable that can be used in it's place...will that even work?
Re: [SQL] Aggregates in WHERE clause?
On Wed, 10 Sep 2008, Ruben Gouveia wrote: > I tried to do the following and got the following error message: > > select employee,count(distinct tasks) > from job > where greatest(max(last_job_date),max(last_position_date)) < > 2008-08-28 + integer '1' > group by employee; > > ERROR: aggregates not allowed in WHERE clause You probably want to look at some variant with HAVING, assuming you intend those max()s to be based on the employee groups. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Aggregates in WHERE clause?
On Wed, Sep 10, 2008 at 4:11 PM, Ruben Gouveia <[EMAIL PROTECTED]> wrote: > select employee,count(distinct tasks) > from job > where greatest(max(last_job_date),max(last_position_date)) < > 2008-08-28 + integer '1' > group by employee; > > ERROR: aggregates not allowed in WHERE clause > select employee,count(distinct tasks) from job group by employee having greatest(max(last_job_date),max(last_position_date)) < 2008-08-28 + integer '1'; Having clause works on agregates. If you want to force it in the where, you need to put the groupby in a sub-query. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pg_restore in java connection (auto_commit = false)
Emi Lu wrote:
Good afternoon,
I have a question about pg_restore in JAVA and need your help.
I planed to do:
==
1. pg_dump each tables (done successfully)
2. start a java connection
auto_commit = false
3.for(Table1... ... TableN)
{
drop primary key
pg_restore table (dead lock here)
add primary key
Connection.commit
}
I suspect I see what's going on (see below) but let's jump back a step
or two first and look at what you're actually doing.
What are you trying to achieve with this? What is the end goal, the
problem to be solved?
Why are you dumping tables individually then restoring them individually
in your code? Do you want to selectively restore only a subset of your
tables?
Are you actually invoking the "pg_dump" and "pg_restore" commands via
Runtime.exec() or similar? Or are you emulating their function in your
Java code? (I'm assuming the former, but have to ask anyway).
It seems that the above steps caused a lock for the tableX. Could
someone give me some ideas please?
If I'm guessing correctly from your fairly vague post, I'd say you're
actually executing an instance of the pg_restore process while holding
an open transaction on the table. Your connection via (presumably) JDBC
obtains an ACCESS EXCLUSIVE lock on the table when it issues the ALTER
TABLE ... DROP CONSTRAINT to drop the primary key. Assuming that's what
you're doing, since you didn't actually show your commands.
See:
http://www.postgresql.org/docs/8.3/interactive/explicit-locking.html
where it says that ALTER TABLE obtains the ACCESS EXCLUSIVE lock. You
can confirm this by issuing the command of interest then running:
SELECT * from pg_catalog.pg_locks;
With that transaction still open, and that lock still held, you then
execute a new process (pg_restore) that establishes its own unrelated
connection to the database and tries to get a ROW EXCLUSIVE lock (if
using INSERT, and presumably COPY though the docs don't say so) on the
table. It can't do so, because your Java program holds an ACCESS
EXCLUSIVE lock on the table that conflicts with the requested lock mode.
Your java code won't release the lock until pg_restore finishes, and
pg_restore won't finish until your java code releases the lock.
Deadlock.
There is no way you can "pass" your connection to pg_restore when you
invoke it from Java. Thus, you must either not hold any locks that would
prevent pg_restore from acting on the table, or you must do all the work
within Java using your existing JDBC connection.
Personally, I strongly suspect that the latter is by far the best
option. Shelling out to database admin tools isn't a great idea, as
their paths will vary, their names might even vary in some installs,
etc. If you need to dump and restore within Java, do it from within Java.
Personally I'd want to dump the tables into a Java-friendly serialized
format that I could read back in later and transform into multi-valued
INSERT statements. Another option that might be easier would be to use a
tool like Hibernate that can take care of the data serialization for you.
This all depends, of course, on whether there's a better way to achieve
what you're trying to do in the first place that doesn't involve all
this per-table dump and restore hassle at all.
--
Craig Ringer
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
