Re: [SQL] Re: Cursors in plpgsql

[Richard Huxton]

From: "Hans-Jürgen Schönig" <[EMAIL PROTECTED]>

> Currently no real cursors are supported - you can build a workaround using
> a loop - this works in most cases.
> I have found a doc (a very good one) that describes porting from Oracle to
> Postgres - accidentally I have lost the link but I remeber that I have

You can get to it from http://techdocs.postgresql.org/ - one of Roberto
Mello's contributions IIRC

- Richard Huxton


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

[SQL] pg_dump bug? (7.1)

[Cedar Cox]

Well, I finally decided to play around with 7.1.  Here's the problem:  
When I try to run pg_dump I get a segmentation fault.  This only seems to
happen if the PGDATABASE environment variable is set and I don't supply
the database name on the command line.  If I unset PGDATABASE, I get a
normal error. Or if I supply the database name on the command line, it
works fine.

Known problem?  (It works fine in 7.0.2)

-Cedar


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] Problems handling errors in PL/pgSQL

[Josh Berkus]

Folks,

I've run up against a problematic limitation of PL/pgSQL's
error-handling ability which could force me to re-write about 25 custom
functions.  I'm hoping that you folks can show me a way around the
situation.

THE PROBLEM:

PL/pgSQL handles errors though "Implied Transactions", where the entire
function is a transaction and rolls back in the event that an error is
encountered.  There is no way to declare a transaction within a PL/pgSQL
function, nor can one issue a ROLLBACK or COMMIT statement within a
function. As such, I have designed all of my data-modifiaction funcitons
to take advantage of this functionality, packaging all updates within a
single function.

However, not all types of errors are so trapped.  The most problematic
un-trapped error is referential integrity:  if an INSERT or UPDATE fails
because of a referential integrity violation, the PL/pgSQL function will
still see the statement as a success and not error out.  Example:

Postgres ver. 7.1 RC2

CREATE TABLE "order_details" (
"order_detail_id" integer DEFAULT 
nextval('order_details_order_detail__seq'::text)
NOT NULL,
"order_usq" integer NOT NULL REFERENCES orders(usq),
"detail_id" integer NOT NULL,
"detail_req" boolean,
Constraint "order_details_pkey" Primary Key ("order_detail_id")
);

CREATE FUNCTION "fn_save_order_details" (integer,integer[],boolean[])
RETURNS integer AS '
DECLARE
v_order ALIAS for $1;
arr_details ALIAS for $2;
arr_req ALIAS for $3;
arr_loop INT2;
detail_no INT4;
detail_r BOOLEAN;
BEGIN
DELETE FROM order_details
WHERE order_usq = v_order;

arr_loop := 1;

WHILE arr_details[arr_loop] LOOP
detail_no := arr_details[arr_loop];
detail_r := COALESCE(arr_req[arr_loop], FALSE);
INSERT INTO order_details ( order_usq, detail_id, detail_req )
VALUES ( v_order, detail_no, detail_r );
arr_loop := arr_loop + 1;
END LOOP;

RETURN arr_loop - 1;
END;
' LANGUAGE 'plpgsql';

SELECT fn_save_order_details (7703, '{34,29,40}','{TRUE, TRUE, FALSE}');

-
3

... thus supposedly reporting success: 3 order_details were saved.

However, it turns out that order 7703 has been deleted.  Thus, the three
INSERTS we ran on order_details failed due to lack of referential
integrity; no records were saved.  Yet the function did not error.


THE QUESTIONS:  

1. Based on the above, it seems I have to go back and add data
validation and RAISE ERROR statements to all of my functions that do
INSERTS or UPDATES to tables with referential integrity triggers.  Is
there a way around this?

2. Is there a plan to fix this kind of deficiency in Postgres
function/procedure error handling?

Thanks so much for your suggestions,

-Josh



__AGLIO DATABASE SOLUTIONS___
   Josh Berkus
  Complete information technology  [EMAIL PROTECTED]
   and data management solutions   (415) 565-7293
  for law firms, small businessesfax 621-2533
and non-profit organizations.  San Francisco

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Problems handling errors in PL/pgSQL

[Joe Conway]

> However, not all types of errors are so trapped.  The most problematic
> un-trapped error is referential integrity:  if an INSERT or UPDATE fails
> because of a referential integrity violation, the PL/pgSQL function will
> still see the statement as a success and not error out.  Example:
>

I'm not sure if this is what you're looking for, but in 7.1 you can do
something like:

INSERT INTO bar(barpk,foopk) VALUES(barpkval,foopkval);
GET DIAGNOSTICS rows = ROW_COUNT;
-- do something based on rows --

See "24.2.5.4. Obtaining other results status" at
http://postgresql.readysetnet.com/users-lounge/docs/7.1/postgres/plpgsql-des
cription.html.

Hope this helps,

Joe




---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

Re: [SQL] Problems handling errors in PL/pgSQL

[Josh Berkus]

Joe,

> I'm not sure if this is what you're looking for, but in 7.1 you can
> do
> something like:
> 
> INSERT INTO bar(barpk,foopk) VALUES(barpkval,foopkval);
> GET DIAGNOSTICS rows = ROW_COUNT;
> -- do something based on rows --

There's several other ways I can check, as well.  However, I was hoping
for some way to avoid adding anything to a couple of dozen functions
which are already debugged.

-Josh 

P.S. Does anyone yet have full documentation on GET DIAGNOSICS?
Roberto?

__AGLIO DATABASE SOLUTIONS___
   Josh Berkus
  Complete information technology  [EMAIL PROTECTED]
   and data management solutions   (415) 565-7293
  for law firms, small businessesfax 621-2533
and non-profit organizations.  San Francisco

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] pg_dump bug? (7.1)

[Tom Lane]

Cedar Cox <[EMAIL PROTECTED]> writes:
> When I try to run pg_dump I get a segmentation fault.  This only seems to
> happen if the PGDATABASE environment variable is set and I don't supply
> the database name on the command line.

Fixed.

regards, tom lane

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

Re: [SQL] Problems handling errors in PL/pgSQL

[Tom Lane]

"Josh Berkus" <[EMAIL PROTECTED]> writes:
> Always good to have you folks test something.  This does appear to be a
> bug ... in KPSQL (the KDE GUI for PSQL), not in Postgres.  When I run it
> through command-line PSQL, an error is returned; for some reason, KPSQL
> returns the return value for the function and no error.

But how would KPSQL know what value the function might have returned?
Something fishy here ... did you check the postmaster log to see whether
an error is really being reported or not?

regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] Problems handling errors in PL/pgSQL

[Josh Berkus]

Tom,

> This would clearly be a bug, but I cannot replicate the problem:
> 
> regression=# SELECT fn_save_order_details (7703, '{34,29,40}','{TRUE,
> TRUE, FALSE}');
> ERROR:   referential integrity violation - key referenced
> from order_details not found in orders
> regression=#

Always good to have you folks test something.  This does appear to be a
bug ... in KPSQL (the KDE GUI for PSQL), not in Postgres.  When I run it
through command-line PSQL, an error is returned; for some reason, KPSQL
returns the return value for the function and no error.

Grazie!

-Josh Berkus

__AGLIO DATABASE SOLUTIONS___
   Josh Berkus
  Complete information technology  [EMAIL PROTECTED]
   and data management solutions   (415) 565-7293
  for law firms, small businessesfax 621-2533
and non-profit organizations.  San Francisco

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

Re: [SQL] Problems handling errors in PL/pgSQL

[Josh Berkus]

Tom,

> But how would KPSQL know what value the function might have returned?
> Something fishy here ... did you check the postmaster log to see
> whether
> an error is really being reported or not?

 Actually, I haven't been able to get the postgresql log
to work since I compiled RC2.  I keep meaning to take it up with PGSQL
Inc. but not getting around to it ...

-Josh Berkus


__AGLIO DATABASE SOLUTIONS___
   Josh Berkus
  Complete information technology  [EMAIL PROTECTED]
   and data management solutions   (415) 565-7293
  for law firms, small businessesfax 621-2533
and non-profit organizations.  San Francisco

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] using top-level aggregate values in subqueries

[Thomas F. O'Connell]

from the docs, i know that if you have two tables, foo and bar, you can 
write a query such as

select f.bling
from foo f
where f.id = (
select max( b.id )
from bar b
where b.bling = "i kiss you!"
);

what i'm wondering is if you need that subquery in two places in a query 
if there's some way to cache it at the top level.

for instance, if i were shooting for

select f.id
from foo f, ola o
where f.id = (
select max( b.id )
from bar b
where b.bling = "i kiss you!"
)
and o.id != (
select max( b.id )
from bar b
where b.bling = "i kiss you!"
)

is there some way to grab the value returned by the subquery in the 
superquery and use the value instead of running the subquery twice?

i'm not looking for an optimized version of my example (unless it 
answers the question of the bigger picture); i'd rather know if there's 
some way to access top-level aggregates from within a subquery.

or find out that postgres is smart enough to recognize bits of SQL in a 
query that are identical and do its own internal caching.

generically stated, my question is:

is there some way, without writing a function, to calculate an aggregate 
value in a query that is used in multiple subqueries without needing to 
run an aggregating query multiple times?

i know it only amounts to syntactic sugar, but, as such, it would be 
pretty sweet.

thanks.

-tfo


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] using top-level aggregate values in subqueries

[Tom Lane]

"Thomas F. O'Connell" <[EMAIL PROTECTED]> writes:
> select f.id
> from foo f, ola o
> where f.id = (
>   select max( b.id )
>   from bar b
>   where b.bling = "i kiss you!"
> )
> and o.id != (
>   select max( b.id )
>   from bar b
>   where b.bling = "i kiss you!"
> )

> is there some way to grab the value returned by the subquery in the 
> superquery and use the value instead of running the subquery twice?

In 7.1, perhaps something like this would do:

select f.id
from foo f, ola o,
(select max( b.id ) as max
 from bar b
 where b.bling = "i kiss you!") ss
where f.id = ss.max
and o.id != ss.max

In prior versions you'd have to fake it by selecting the subquery
result into a temp table beforehand.

> i'm not looking for an optimized version of my example

While it's not a general solution, there's always transitivity:

select f.id
from foo f, ola o
where f.id = (
select max( b.id )
from bar b
where b.bling = "i kiss you!"
)
and o.id != f.id

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]