[SQL] spi and error messages
Hi,
I'm writing some function to fetch data from an Oracle database and store it in
a PostgreSQL database.
CREATE OR REPLACE FUNCTION public.replicate_billing(text, date, date) RETURNS
void AS
$body$
use DBI;
my $dbh_ora =
DBI->connect('dbi:Oracle:database=bmssa;host=firev120-1.indicator.be;sid=mars',
'bmssa', '***', {PrintError => 0});
elog(ERROR, DBI->errstr) unless ($dbh_ora);
my $query = 'SELECT ... FROM ... WHERE ...';
my $sel = $dbh_ora->prepare($query);
elog(ERROR, $dbh_ora->errstr);
sel->execute;
elog(ERROR, $dbh_ora->errstr);
my $target = 'INSERT INTO ... VALUES ($1,$2,$3)';
my $plan = spi_prepare($target, 'varchar', 'varchar', 'date');
elog(ERROR, ???);
...
spi_freeplan($plan);
$body$
LANGUAGE 'plperlu' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
As you can see I raise an error if connecting to Oracle fails and if preparing
or executing the plan for fetching data fails.
Likewise I would like to raise an error if preparing the insert statement
fails. As error message I would like to use the message generated by postgresql
itself just like I do in the Oracle part of my function. I can't seem to find
however how to do that.
In general how should I catch the error message generated if one of the spi
functions (spi_exec_query, spi_query, spi_fetchrow, spi_prepare,...) fails?
Thanks for your help.
Re: [SQL] spi and error messages
On Wed, May 30, 2007 at 09:33:40AM +0200, Bart Degryse wrote:
> In general how should I catch the error message generated if one
> of the spi functions (spi_exec_query, spi_query, spi_fetchrow,
> spi_prepare,...) fails?
In PL/Perl functions you can use eval to catch errors just as you
would in an ordinary Perl script:
eval { do something };
if ($@) { handle the error }
--
Michael Fuhr
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
[SQL] logging amount rows retrieved?
Hi all. Im wondering if there is a way to include in the postgresql logs the total rows retrieved after a query (im logging the query itself right now). Something like apache does when it logs the amount of bytes retrieved after a GET. Thanks! Gerardo ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] slow query execution
Hi all, This query executes very slow: select (select count(*) from customer where id <= a.id) as row, id, from customer as a order by id; Where customer has id column and others and has also index on id column. The table has about 10.000+ records. When used with "explain", it gives me this output: QUERY PLAN - Sort (cost=6513774.23..6513801.37 rows=10855 width=14) Sort Key: id -> Seq Scan on customer a (cost=0.00..6513046.62 rows=10855 width=14) SubPlan -> Aggregate (cost=599.94..599.95 rows=1 width=0) -> Bitmap Heap Scan on customer (cost=28.66..590.89 rows=361 width=0) Recheck Cond: (id <= $0) -> Bitmap Index Scan on iid (cost=0.00..28.66 rows=361 width=0) Index Cond: (id <= $0) (9 riadkov) So is there any solution to speed up this query? Or can I get the same result but with some other query? thanks Trigve Moody friends. Drama queens. Your life? Nope! - their life, your story. Play Sims Stories at Yahoo! Games. http://sims.yahoo.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] slow query execution
On Wed, May 30, 2007 at 08:56:45AM -0700, Trigve Siver wrote: > Hi all, > > This query executes very slow: > > select (select count(*) from customer where id <= a.id) as row, id, > from customer as a order by id; So you are trying to get the ordinal position of every ID in the table? I'm not surprised it takes a long time -- you have to join the whole table to itself and then do a lot of counting. Are you just trying to get the "row number" for your query answer? You can do this with a temporary sequence, among other approaches, more cheaply. A -- Andrew Sullivan | [EMAIL PROTECTED] Users never remark, "Wow, this software may be buggy and hard to use, but at least there is a lot of code underneath." --Damien Katz ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] slow query execution
Hi, Thanks for reply, As you have mentioned I need to get row numbers for my query, so when I make some other query with same data I will know which row number has a particular ID. As you mentioned "You can do this with a temporary sequence, among other approaches...". Can you point me to some sources or give me some examples, please? Thanks Trigve - Original Message From: Andrew Sullivan <[EMAIL PROTECTED]> To: pgsql-sql@postgresql.org Sent: Wednesday, May 30, 2007 6:45:53 PM Subject: Re: [SQL] slow query execution On Wed, May 30, 2007 at 08:56:45AM -0700, Trigve Siver wrote: > Hi all, > > This query executes very slow: > > select (select count(*) from customer where id <= a.id) as row, id, > from customer as a order by id; So you are trying to get the ordinal position of every ID in the table? I'm not surprised it takes a long time -- you have to join the whole table to itself and then do a lot of counting. Are you just trying to get the "row number" for your query answer? You can do this with a temporary sequence, among other approaches, more cheaply. A -- Andrew Sullivan | [EMAIL PROTECTED] Users never remark, "Wow, this software may be buggy and hard to use, but at least there is a lot of code underneath." --Damien Katz ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq Yahoo! oneSearch: Finally, mobile search that gives answers, not web links. http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] slow query execution
On 5/30/07, Trigve Siver <[EMAIL PROTECTED]> wrote:
Can you point me to some sources
or give me some examples, please?
CREATE OR REPLACE FUNCTION ROWNUM() RETURNS BIGINT AS
$$
BEGIN
RETURN NEXTVAL('ROWNUM_SEQ');
EXCEPTION WHEN OTHERS THEN
CREATE TEMP SEQUENCE ROWNUM_SEQ;
RETURN NEXTVAL('ROWNUM_SEQ');
END;
$$
LANGUAGE 'PLPGSQL';
SELECT ROWNUM(), S.X
FROM GENERATE_SERIES(5,1,-1) S(X);
Remember to reset the sequence value if you use this more than once in
the same session.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] slow query execution
Thanks a lot
Trigve
- Original Message
From: Rodrigo De León <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org; Trigve Siver <[EMAIL PROTECTED]>
Sent: Wednesday, May 30, 2007 7:28:47 PM
Subject: Re: [SQL] slow query execution
On 5/30/07, Trigve Siver <[EMAIL PROTECTED]> wrote:
> Can you point me to some sources
> or give me some examples, please?
CREATE OR REPLACE FUNCTION ROWNUM() RETURNS BIGINT AS
$$
BEGIN
RETURN NEXTVAL('ROWNUM_SEQ');
EXCEPTION WHEN OTHERS THEN
CREATE TEMP SEQUENCE ROWNUM_SEQ;
RETURN NEXTVAL('ROWNUM_SEQ');
END;
$$
LANGUAGE 'PLPGSQL';
SELECT ROWNUM(), S.X
FROM GENERATE_SERIES(5,1,-1) S(X);
Remember to reset the sequence value if you use this more than once in
the same session.
Take
the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news,
photos & more.
http://mobile.yahoo.com/go?refer=1GNXIC
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] slow query execution
On Wed, May 30, 2007 at 10:03:16AM -0700, Trigve Siver wrote:
> Hi, Thanks for reply, As you have mentioned I need to get row
> numbers for my query, so when I make some other query with same
> data I will know which row number has a particular ID.
Oh, wait. If _that's_ your plan, then this will never work. The
data could change, and your row numbers would come out wrong.
What do you need "row numbers" for anyway? The very idea is inimical
to SQL, because the data is fundamentally unordered.
> As you
> mentioned "You can do this with a temporary sequence, among other
> approaches...". Can you point me to some sources or give me some
> examples, please?
BEGIN;
CREATE SEQUENCE temp_seq;
SELECT nextval('temp_seq'), other stuff from table;
DROP SEQUENCE temp_seq;
COMMIT/ROLLBACK;
If you only select, you don't have to do the DROP, you just ROLLBACK.
I think there's some nifty way to get generate_series to do this too,
but I don't know it offhand (generating row numbers sounds to me like
a bad idea, so I don't do it).
A
--
Andrew Sullivan | [EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] slow query execution
>- Original Message >From: Andrew Sullivan <[EMAIL PROTECTED]> >To: pgsql-sql@postgresql.org >Sent: Wednesday, May 30, 2007 7:39:18 PM >Subject: Re: [SQL] slow query execution > >On Wed, May 30, 2007 at 10:03:16AM -0700, Trigve Siver wrote: >> Hi, Thanks for reply, As you have mentioned I need to get row >> numbers for my query, so when I make some other query with same >> data I will know which row number has a particular ID. > >Oh, wait. If _that's_ your plan, then this will never work. The >data could change, and your row numbers would come out wrong. >What do you need "row numbers" for anyway? The very idea is inimical >to SQL, because the data is fundamentally unordered. I want to do it only for some queries. So when I need query like that "SELECT * from t1" I need to add row_numbers there. So I will have "query with row_numbers" (where row_numbers are row numbers of my virtual list view). Then when I perform search I also get row_numbers for IDs of search result and I can highlight items in my list view. I haven't found other solution that met my requirements. I want also do it with cursors. [...] >I think there's some nifty way to get generate_series to do this too, >but I don't know it offhand (generating row numbers sounds to me like >a bad idea, so I don't do it). I have tried with generate_series but without success. thanks Trigve Luggage? GPS? Comic books? Check out fitting gifts for grads at Yahoo! Search http://search.yahoo.com/search?fr=oni_on_mail&p=graduation+gifts&cs=bz ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] slow query execution
On Wed, May 30, 2007 at 11:08:02AM -0700, Trigve Siver wrote: > I want to do it only for some queries. So when I need query like > that "SELECT * from t1" I need to add row_numbers there. So I will > have "query with row_numbers" (where row_numbers are row numbers of > my virtual list view). Then when I perform search I also get > row_numbers for IDs of search result and I can highlight items in > my list view. I haven't found other solution that met my > requirements. You really need to ensure you keep that ORDER BY in there, then. The order that SQL returns in is not determined until the data has come back. Are you doing this all in one serialisable transaction, though? If not, what guarantee will you have that new data won't mess up your row numbering from query to query? A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] slow query execution
>- Original Message >From: Andrew Sullivan <[EMAIL PROTECTED]> >To: pgsql-sql@postgresql.org >Sent: Wednesday, May 30, 2007 8:30:32 PM >Subject: Re: [SQL] slow query execution > >On Wed, May 30, 2007 at 11:08:02AM -0700, Trigve Siver wrote: > >> I want to do it only for some queries. So when I need query like >> that "SELECT * from t1" I need to add row_numbers there. So I will >> have "query with row_numbers" (where row_numbers are row numbers of >> my virtual list view). Then when I perform search I also get >> row_numbers for IDs of search result and I can highlight items in >> my list view. I haven't found other solution that met my >> requirements. > >You really need to ensure you keep that ORDER BY in there, then. The >order that SQL returns in is not determined until the data has come >back. Are you doing this all in one serialisable transaction, >though? If not, what guarantee will you have that new data won't >mess up your row numbering from query to query? I seee...when new data will be added to table, then row numbers wouldn''t be valid anymore. Hmmm...looked like I need to find other solution. thanks Trigve Get the free Yahoo! toolbar and rest assured with the added security of spyware protection. http://new.toolbar.yahoo.com/toolbar/features/norton/index.php ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] slow query execution
Trigve Siver wrote: I seee...when new data will be added to table, then row numbers wouldn''t be valid anymore. Hmmm...looked like I need to find other solution. Try explaining what it is you're trying to achieve, and maybe someone will be able to suggest a solution. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] slow query execution
>- Original Message >From: Richard Huxton <[EMAIL PROTECTED]> >To: Trigve Siver <[EMAIL PROTECTED]> >Cc: pgsql-sql@postgresql.org >Sent: Wednesday, May 30, 2007 9:05:09 PM >Subject: Re: [SQL] slow query execution > >Trigve Siver wrote: >> >> I seee...when new data will be added to table, then row numbers wouldn''t be >> valid anymore. >> >> Hmmm...looked like I need to find other solution. > >Try explaining what it is you're trying to achieve, and maybe someone >will be able to suggest a solution. I need to perform search on some query (which is already shown in Listview control) and highlight items that meet search criteria. thanks Trigve Don't pick lemons. See all the new 2007 cars at Yahoo! Autos. http://autos.yahoo.com/new_cars.html ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
