DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=On79YfNv95+bYi4yXkoZD656mfhEVn9CEpjU2SdCnN7pvlIAs7doDg295ftNBlND1w
	jktppEWJGJK2steFejlpfmnU4XtCnbldKmt96AFPjYouHek6VKl+5Dj7lQbQV5cLkbkb
	C0R2XBx62h8WY8HAQlLI/UJYkX4PHsZVYW8SU=
MIME-Version: 1.0
In-Reply-To: <4A733418.6000304@netzmeister-st-pauli.de>
References: <a0eee4d40907310824h3fd0df14p75b633e38fd2d203@mail.gmail.com>
	<4A733418.6000304@netzmeister-st-pauli.de>
Date: Fri, 31 Jul 2009 20:52:16 +0200
Message-ID: <a0eee4d40907311152v31ea781boc756edff926b04c2@mail.gmail.com>
Subject: Re: on error resume next
From: Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
To: Andreas Wenk <a.wenk@netzmeister-st-pauli.de>
Cc: pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary=000e0cd250f043cbdd047004eb31

--000e0cd250f043cbdd047004eb31
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Hi,

that's not really that what i need. I think i will solve it on client side.

thank you.
jasmin

2009/7/31 Andreas Wenk <a.wenk@netzmeister-st-pauli.de>

>  Jasmin Dizdarevic wrote:
>
>> hi,
>>  can i use savepoints to realize something like "on error resume next"?
>>  i've got the following situation:
>>  begin;
>> 1. create view user001.accounts as select * from base.accounts;
>> 2. grant select on user001.accounts to loginuser001;
>> commit;
>>  begin;
>> 3. create view user002.accounts as select * from base.accounts;
>> 4. grant select on user002.accounts to loginuser002;
>> commit;
>>   my goal is to avoid execution stop, if one of the transactions fail.
>> let's say line 1 throws an error it should go further to line 3.
>>  any ideas?
>>  thank you.
>>  jasmin
>>
>
> AFAIK it's not possible. A transaction is kind of a container with a
> positive or negative result. If one of the queries fails in between a
> transaction, it will be rolled back after a commit. What you can do with
> savepoints is the following:
>
> usage=# CREATE TABLE test (id serial, content text);
> usage=# BEGIN;
> usage=# INSERT INTO test (content) VALUES ('first stuff');
> usage=# SAVEPOINT s1;
> usage=# INSERT INTO test (content) VALUES ();
> ERROR:  syntax error at or near ")"
> usage=# ROLLBACK TO SAVEPOINT s1;
> ROLLBACK
> usage=# SELECT * FROM test;
>  id |   content
> ----+--------------
>  1 | first stuff
> (1 row)
>
> usage=# COMMIT;
> COMMIT
> usage=# SELECT * FROM test;
>  id |   content
> ----+--------------
>  1 | first stuff
> (1 row)
>
> The second INSERT statement fails. If you would go further with insert
> statements and then fire a COMMIT at the end, nothing would be inserted into
> the table. But if you fire a ROLLBACK TO SAVEPOINT s1, at least the data of
> the first INSERT statement are written.
>
> So maybe this is a start help for creating some logic to get something like
> 'on error resume next'.
>
> Cheers
>
> Andy
>
>
>
>

--000e0cd250f043cbdd047004eb31
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<div>Hi, </div>
<div>=A0</div>
<div>that&#39;s not really that what i need. I think i will solve it on cli=
ent side. </div>
<div>=A0</div>
<div>thank you.</div>
<div>jasmin<br><br></div>
<div class=3D"gmail_quote">2009/7/31 Andreas Wenk <span dir=3D"ltr">&lt;<a =
href=3D"mailto:a.wenk@netzmeister-st-pauli.de">a.wenk@netzmeister-st-pauli.=
de</a>&gt;</span><br>
<blockquote style=3D"BORDER-LEFT: #ccc 1px solid; MARGIN: 0px 0px 0px 0.8ex=
; PADDING-LEFT: 1ex" class=3D"gmail_quote">
<div>
<div></div>
<div class=3D"h5">Jasmin Dizdarevic wrote:<br>
<blockquote style=3D"BORDER-LEFT: #ccc 1px solid; MARGIN: 0px 0px 0px 0.8ex=
; PADDING-LEFT: 1ex" class=3D"gmail_quote">hi,<br>=A0can i use savepoints t=
o realize something like &quot;on error resume next&quot;?<br>=A0i&#39;ve g=
ot the following situation:<br>
=A0begin;<br>1. create view user001.accounts as select * from base.accounts=
;<br>2. grant select on user001.accounts to loginuser001;<br>commit;<br>=A0=
begin;<br>3. create view user002.accounts as select * from base.accounts;<b=
r>
4. grant select on user002.accounts to loginuser002;<br>commit;<br>=A0=A0my=
 goal is to avoid execution stop, if one of the transactions fail.<br>let&#=
39;s say line 1 throws an error it should go further to line 3.<br>=A0any i=
deas?<br>
=A0thank you.<br>=A0jasmin<br></blockquote><br></div></div>AFAIK it&#39;s n=
ot possible. A transaction is kind of a container with a positive or negati=
ve result. If one of the queries fails in between a transaction, it will be=
 rolled back after a commit. What you can do with savepoints is the followi=
ng:<br>
<br>usage=3D# CREATE TABLE test (id serial, content text);<br>usage=3D# BEG=
IN;<br>usage=3D# INSERT INTO test (content) VALUES (&#39;first stuff&#39;);=
<br>usage=3D# SAVEPOINT s1;<br>usage=3D# INSERT INTO test (content) VALUES =
();<br>
ERROR: =A0syntax error at or near &quot;)&quot;<br>usage=3D# ROLLBACK TO SA=
VEPOINT s1;<br>ROLLBACK<br>usage=3D# SELECT * FROM test;<br>=A0id | =A0 con=
tent<br>----+--------------<br>=A01 | first stuff<br>(1 row)<br><br>usage=
=3D# COMMIT;<br>
COMMIT<br>usage=3D# SELECT * FROM test;<br>=A0id | =A0 content<br>----+----=
----------<br>=A01 | first stuff<br>(1 row)<br><br>The second INSERT statem=
ent fails. If you would go further with insert statements and then fire a C=
OMMIT at the end, nothing would be inserted into the table. But if you fire=
 a ROLLBACK TO SAVEPOINT s1, at least the data of the first INSERT statemen=
t are written.<br>
<br>So maybe this is a start help for creating some logic to get something =
like &#39;on error resume next&#39;.<br><br>Cheers<br><br>Andy<br><br><br><=
br></blockquote></div><br>

--000e0cd250f043cbdd047004eb31--