[SQL] exception handling and CONTINUE
Hi all. Can anyone tell me if there's a way to use CONTINUE clause outside the loop ? An example : FOR a IN SELECT * FROM xxx LOOP INSERT INTO yyy VALUES (a.***, ..) END LOOP; EXCEPTION WHEN unique_violation THEN CONTINUE; I get an error saying I can't use CONTINUE outside of a loop. Is there a way around this ? regards mk
Re: [SQL] exception handling and CONTINUE
Marcin Krawczyk escribió: > Hi all. Can anyone tell me if there's a way to use CONTINUE clause outside > the loop ? > An example : > > FOR a IN SELECT * FROM xxx > LOOP > > INSERT INTO yyy VALUES (a.***, ..) > > END LOOP; > > EXCEPTION WHEN unique_violation THEN CONTINUE; Try something like this: for a in select * from xxx loop begin insert into yyy values (...) exception when unique_violation then null; -- noop, just for clarity end; end loop; -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] exception handling and CONTINUE
no, you can use CONTINUE only in loop. When you wont ignore exception, just do nothing For example, the following two fragments of code are equivalent: BEGIN y := x / 0; EXCEPTION WHEN division_by_zero THEN NULL; -- ignore the error END; BEGIN y := x / 0; EXCEPTION WHEN division_by_zero THEN -- ignore the error END; http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html Regards Pavel Stehule 2008/7/8 Marcin Krawczyk <[EMAIL PROTECTED]>: > Hi all. Can anyone tell me if there's a way to use CONTINUE clause outside > the loop ? > An example : > > FOR a IN SELECT * FROM xxx > LOOP > > INSERT INTO yyy VALUES (a.***, ..) > > END LOOP; > > EXCEPTION WHEN unique_violation THEN CONTINUE; > > I get an error saying I can't use CONTINUE outside of a loop. Is there a way > around this ? > > regards > mk -- 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] how to control the execution plan ?
Hi Scott,
I add the answers below.
>
> Just wondering what the query plans look like here, both regular
> explain, and if you can wait for it to execute, explain analyze.
>
Just with explain, because the function craches when it is running:
"Merge Join (cost=141.41..188.32 rows=1 width=24)"
" Merge Cond: ("TABLE_C"."PK_ID" = bp."COL_C")"
" -> Merge Left Join (cost=62.33..96.69 rows=1000 width=44)"
"Merge Cond: (s."PK_ID" = "MY_FUNCTION_B".COL_D)"
"-> Index Scan using "TABLE_D_pkey" on "TABLE_D" s
(cost=0.00..18.49 rows=349 width=4)"
"-> Sort (cost=62.33..64.83 rows=1000 width=44)"
" Sort Key: "MY_FUNCTION_B".COL_D"
" -> Function Scan on "MY_FUNCTION_B" (cost=0.00..12.50
rows=1000 width=44)"
" -> Sort (cost=79.08..79.09 rows=1 width=28)"
"Sort Key: bp."COL_C""
"-> Hash Join (cost=10.59..79.07 rows=1 width=28)"
" Hash Cond: (bp."COL_B" = pn."PK_ID")"
" -> Seq Scan on "TABLE_A" bp (cost=0.00..68.46 rows=4
width=32)"
"Filter: (("COL_A" IS NOT NULL) AND (("COL_A")::text <>
''::text) AND ((("MY_FUNCTION_A"("COL_A", NULL::boolean))::text || '
'::text) IS NULL))"
" -> Hash (cost=10.50..10.50 rows=7 width=4)"
"-> Seq Scan on "TABLE_B" pn (cost=0.00..10.50 rows=7
width=4)"
" Filter: (("COL_E")::text ~~ 'Some%'::text)"
> I'm guessing that the function is not indexed / indexable. Is it
> marked immutable (and is it actually immutable) or stable (and is
> stable)?
>
The function is marked stable.
> If it's still to smart, you can run two queries, one to pull the set
> you want to work with from the custom function into a temp table, then
> analyze it, then run the query against that.
> Not an optimal solution, but it might be the fastest if you can't
> index your function.
>
In fact I would use that statement to define a permanent view, not in a
procedure.
Finally I found a trick specifying not just WHERE (x.ALIAS_A::text ) IS
NULL;, but combining with a constant and a join with a constant.
By the way, it doesn't works just with the constant or with the join :(
See the query and the plan below:
EXPLAIN SELECT *
FROM (
SELECT "MY_FUNCTION_A"(bp."COL_A", NULL::boolean) AS ALIAS_A, MY_AUX
FROM "TABLE_A" bp
CROSS JOIN (
SELECT '*'::character varying AS MY_AUX
) afp
JOIN "TABLE_B" pn ON bp."COL_B" = pn."PK_ID"
JOIN "TABLE_C" vbo ON bp."COL_C" = vbo."PK_ID"
WHERE pn."COL_E"::text ~~ 'Some%'::text AND bp."COL_A" IS NOT NULL
AND bp."COL_A"::text <> ''::text
) x
WHERE (x.ALIAS_A::text || ' ' || MY_AUX) IS NULL;
"Merge Join (cost=131.68..178.60 rows=1 width=56)"
" Merge Cond: ("TABLE_C"."PK_ID" = bp."COL_C")"
" -> Merge Left Join (cost=62.33..96.69 rows=1000 width=44)"
"Merge Cond: (s."PK_ID" = "MY_FUNCTION_B".COL_D)"
"-> Index Scan using "TABLE_D_pkey" on "TABLE_D" s
(cost=0.00..18.49 rows=349 width=4)"
"-> Sort (cost=62.33..64.83 rows=1000 width=44)"
" Sort Key: "MY_FUNCTION_B".COL_D"
" -> Function Scan on "MY_FUNCTION_B" (cost=0.00..12.50
rows=1000 width=44)"
" -> Sort (cost=69.36..69.36 rows=1 width=60)"
"Sort Key: bp."COL_C""
"-> Nested Loop (cost=10.59..69.34 rows=1 width=60)"
" Join Filter: "MY_FUNCTION_A"(bp."COL_A",
NULL::boolean))::text || ' '::text) || (afp.MY_AUX)::text) IS NULL)"
" -> Result (cost=0.00..0.01 rows=1 width=0)"
" -> Hash Join (cost=10.59..68.94 rows=22 width=28)"
"Hash Cond: (bp."COL_B" = pn."PK_ID")"
"-> Seq Scan on "TABLE_A" bp (cost=0.00..54.90
rows=862 width=32)"
" Filter: (("COL_A" IS NOT NULL) AND
(("COL_A")::text <> ''::text))"
"-> Hash (cost=10.50..10.50 rows=7 width=4)"
" -> Seq Scan on "TABLE_B" pn (cost=0.00..10.50
rows=7 width=4)"
"Filter: (("COL_E")::text ~~ 'Some%'::text)"
However I'm not sure there are no circumstances when the execution plan will
detect my trick and will optimize the query again :((
Sabin
--
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] exception handling and CONTINUE
Thank you guys. Alvaro your idea works tha way I wanted. Why didn't I think about it ? :) regards mk
[SQL] Best practice for long-lived journal tables: bigint or recycling IDs?
Hello, I have some tables that continually collect statistics, and then over time are pruned as the stats are aggregated into more useful formats. For some of these tables, it it is fore-seeable that the associated sequences would be incremented past the max value of the "int" type in the normal course of things. I see two options to prepare for that: 1. Convert the primary keys to "bigint", which should be good enough "forever". I suppose there would some minor storage and performance penalty. 2. Reset the sequence at some point. There would no "collisions", because the older rows would have long been pruned-out. I suppose there is an improbable edge case in which we restore some old data from tape and then are confused because some new data has the same IDs, but as I said, these tables are used as temporary holding locations, not permanent storage. Both options have some appeal for me. What have others done? Mark -- . . . . . . . . . . . . . . . . . . . . . . . . . . . Mark StosbergPrincipal Developer [EMAIL PROTECTED] Summersault, LLC 765-939-9301 ext 202 database driven websites . . . . . http://www.summersault.com/ . . . . . . . . -- 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] Best practice for long-lived journal tables: bigint or recycling IDs?
Mark Stosberg wrote: > > Hello, > > I have some tables that continually collect statistics, and then over time are > pruned as the stats are aggregated into more useful formats. > > For some of these tables, it it is fore-seeable that the associated sequences > would be incremented past the max value of the "int" type in the normal course > of things. > > I see two options to prepare for that: 3. Deal with wraparound by ensuring that the applications behave sanely -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Re: Best practice for long-lived journal tables: bigint or recycling IDs?
On Tue, 8 Jul 2008 17:20:13 -0400 Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Mark Stosberg wrote: > > > > Hello, > > > > I have some tables that continually collect statistics, and then over time > > are > > pruned as the stats are aggregated into more useful formats. > > > > For some of these tables, it it is fore-seeable that the associated > > sequences > > would be incremented past the max value of the "int" type in the normal > > course > > of things. > > > > I see two options to prepare for that: > > 3. Deal with wraparound by ensuring that the applications behave sanely Wrap-around? Exceeding the max size of "int" looks more like a brick wall than wrap-around to me: insert into t values (2147483648); ERROR: integer out of range Mark -- . . . . . . . . . . . . . . . . . . . . . . . . . . . Mark StosbergPrincipal Developer [EMAIL PROTECTED] Summersault, LLC 765-939-9301 ext 202 database driven websites . . . . . http://www.summersault.com/ . . . . . . . . -- . . . . . . . . . . . . . . . . . . . . . . . . . . . Mark StosbergPrincipal Developer [EMAIL PROTECTED] Summersault, LLC 765-939-9301 ext 202 database driven websites . . . . . http://www.summersault.com/ . . . . . . . . -- 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] Re: Best practice for long-lived journal tables: bigint or recycling IDs?
Mark Stosberg wrote:
> On Tue, 8 Jul 2008 17:20:13 -0400
> Alvaro Herrera <[EMAIL PROTECTED]> wrote:
>
> > 3. Deal with wraparound by ensuring that the applications behave sanely
>
> Wrap-around?
>
> Exceeding the max size of "int" looks more like a brick wall than wrap-around
> to me:
>
> insert into t values (2147483648);
> ERROR: integer out of range
Hmm, you can alter the sequence so that it wraps around at the point it
reaches INT_MAX. So inserting this number would never actually happen.
alvherre=# create table t (a serial);
NOTICE: CREATE TABLE créera des séquences implicites « t_a_seq » pour la
colonne serial « t.a »
CREATE TABLE
alvherre=# alter sequence t_a_seq maxvalue 2147483647;
ALTER SEQUENCE
alvherre=# alter sequence t_a_seq cycle;
ALTER SEQUENCE
alvherre=# select setval('t_a_seq', 2147483645);
setval
2147483645
(1 ligne)
alvherre=# insert into t default values;
INSERT 0 1
alvherre=# insert into t default values;
INSERT 0 1
alvherre=# insert into t default values;
INSERT 0 1
alvherre=# insert into t default values;
INSERT 0 1
alvherre=# insert into t default values;
INSERT 0 1
alvherre=# select * from t;
a
2147483646
2147483647
1
2
3
(5 lignes)
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
