Обсуждение: Why does the PL/pgSQL compiler do this?
Here is the complete function, but all you need to look at is the exception block. (I didn't write this code) :-) I will ask the question after the code.
CREATE OR REPLACE FUNCTION etl_app.detl_tx_pull_client_
p_start_date character varying,
p_end_date character varying)
RETURNS boolean AS
$BODY$
DECLARE
COUNT INTEGER;
SOURCE RECORD;
v_check_count INTEGER;
BEGIN
COUNT := 0;
SELECT count(*) into v_check_count
FROM fs_QSN_APP.tx_pull_client_stat
WHERE updateddate >= TO_DATE(p_start_date,'DD-MON-
IF v_check_count > 0 then
RAISE INFO 'Rows detected=%', v_check_count ;
DELETE FROM QSN_APP.tx_pull_client_stat;
RAISE INFO 'Done Deleting tx_pull_client_stat';
INSERT INTO QSN_APP.tx_pull_client_stat (PULL_STAT_KEY,
select PULL_STAT_KEY,
FROM fs_QSN_APP.tx_pull_client_
RAISE INFO 'Done Inserting tx_pull_client_stat';
END IF;
RETURN TRUE;
EXCEPTION WHEN OTHERS THEN
RAISE EXCEPTION '% %', SQLERRM, SQLSTATE;
ROLLBACK;
RETURN FALSE;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
So, here is the question. Why does the compiler not catch:
1) ROLLBACK; is not a valid PL/pgSQL command
2) ROLLBACK; and RETURN FALSE; can never be reached
Again, my question is about the compiler, not about wrongness of the error handling code.
I understand that as far as fixing the error handling is concerned, the correct thing to do would be to remove the EXCEPTION block all together and let any errors be propagated up the call stack.
This code is what happens when you let an Oracle PL/SQL programmer try his hand at PL/pgSQL. ;-)
Here is the complete function, but all you need to look at is the exception block. (I didn't write this code) :-) I will ask the question after the code.[...]RETURN TRUE;
EXCEPTION WHEN OTHERS THEN
RAISE EXCEPTION '% %', SQLERRM, SQLSTATE;
ROLLBACK;
RETURN FALSE;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
So, here is the question. Why does the compiler not catch:
1) ROLLBACK; is not a valid PL/pgSQL command
R
eading section 41.10.2 at the linked page should answer this part.
2) ROLLBACK; and RETURN FALSE; can never be reached
Similar to the above - though "static analysis" is yet a step beyond even what the syntax checking skipping covered above would reveal.
David J.
Cool, thanks David, I'll give it a read.
On Mon, Oct 31, 2016 at 3:24 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
Here is the complete function, but all you need to look at is the exception block. (I didn't write this code) :-) I will ask the question after the code.[...]RETURN TRUE;
EXCEPTION WHEN OTHERS THEN
RAISE EXCEPTION '% %', SQLERRM, SQLSTATE;
ROLLBACK;
RETURN FALSE;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
So, here is the question. Why does the compiler not catch:
1) ROLLBACK; is not a valid PL/pgSQL command
Reading section 41.10.2 at the linked page should answer this part.2) ROLLBACK; and RETURN FALSE; can never be reached
Similar to the above - though "static analysis" is yet a step beyond even what the syntax checking skipping covered above would reveal.David J.
I'm still a bit confused. If I replace the ROLLBACK; command with ELEPHANT; the result is a syntax error. Why doesn't ROLLBACK; produce the same error since it is not valid in the LANGUAGE plpgsql. I understand that "ROLLBACK TO SAVEPOINT" IS valid. But it's not the same thing.
On Mon, Oct 31, 2016 at 3:55 PM, Michael Moore <michaeljmoore@gmail.com> wrote:
Cool, thanks David, I'll give it a read.On Mon, Oct 31, 2016 at 3:24 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:Here is the complete function, but all you need to look at is the exception block. (I didn't write this code) :-) I will ask the question after the code.[...]RETURN TRUE;
EXCEPTION WHEN OTHERS THEN
RAISE EXCEPTION '% %', SQLERRM, SQLSTATE;
ROLLBACK;
RETURN FALSE;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
So, here is the question. Why does the compiler not catch:
1) ROLLBACK; is not a valid PL/pgSQL command
Reading section 41.10.2 at the linked page should answer this part.2) ROLLBACK; and RETURN FALSE; can never be reached
Similar to the above - though "static analysis" is yet a step beyond even what the syntax checking skipping covered above would reveal.David J.
On 10/31/2016 04:32 PM, Michael Moore wrote: > I'm still a bit confused. If I replace the ROLLBACK; command with > ELEPHANT; the result is a syntax error. Why doesn't ROLLBACK; produce > the same error since it is not valid in the LANGUAGE plpgsql. I > understand that "ROLLBACK TO SAVEPOINT" IS valid. But it's not the same > thing. I am guessing this: https://www.postgresql.org/docs/9.5/static/plpgsql-implementation.html " A disadvantage is that errors in a specific expression or command cannot be detected until that part of the function is reached in execution. (Trivial syntax errors will be detected during the initial parsing pass, but anything deeper will not be detected until execution.)" ROLLBACK might actually be valid at some point, ELEPHANT will not so it caught in the trivial error stage. > > On Mon, Oct 31, 2016 at 3:55 PM, Michael Moore <michaeljmoore@gmail.com > <mailto:michaeljmoore@gmail.com>> wrote: > > Cool, thanks David, I'll give it a read. > > > On Mon, Oct 31, 2016 at 3:24 PM, David G. Johnston > <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote: > > On Mon, Oct 31, 2016 at 3:13 PM, Michael Moore > <michaeljmoore@gmail.com <mailto:michaeljmoore@gmail.com>>wrote: > > Here is the complete function, but all you need to look at > is the exception block. (I didn't write this code) :-) I > will ask the question after the code. > [...] > > RETURN TRUE; > > EXCEPTION WHEN OTHERS THEN > > RAISE EXCEPTION '% %', SQLERRM, SQLSTATE; > > ROLLBACK; > > RETURN FALSE; > > END; > > $BODY$ > > LANGUAGE plpgsql VOLATILE > > COST 100; > > > So, here is the question. Why does the compiler not catch: > > 1) ROLLBACK; is not a valid PL/pgSQL command > > > R > eading section 41.10.2 at the linked page should answer this part. > > https://www.postgresql.org/docs/current/static/plpgsql-implementation.html > <https://www.postgresql.org/docs/current/static/plpgsql-implementation.html> > > > 2) ROLLBACK; and RETURN FALSE; can never be reached > > > > Similar to the above - though "static analysis" is yet a step > beyond even what the syntax checking skipping covered above > would reveal. > > David J. > > > -- Adrian Klaver adrian.klaver@aklaver.com
Thanks Adrian, but is ROLLBACK ever possible in PL/pgSQL? My understanding is, "No".
On Mon, Oct 31, 2016 at 4:38 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 10/31/2016 04:32 PM, Michael Moore wrote:I'm still a bit confused. If I replace the ROLLBACK; command with
ELEPHANT; the result is a syntax error. Why doesn't ROLLBACK; produce
the same error since it is not valid in the LANGUAGE plpgsql. I
understand that "ROLLBACK TO SAVEPOINT" IS valid. But it's not the same
thing.
I am guessing this:
https://www.postgresql.org/docs/9.5/static/plpgsql-implement ation.html
" A disadvantage is that errors in a specific expression or command cannot be detected until that part of the function is reached in execution. (Trivial syntax errors will be detected during the initial parsing pass, but anything deeper will not be detected until execution.)"
ROLLBACK might actually be valid at some point, ELEPHANT will not so it caught in the trivial error stage.
On Mon, Oct 31, 2016 at 3:55 PM, Michael Moore <michaeljmoore@gmail.com
<mailto:michaeljmoore@gmail.com>> wrote:
Cool, thanks David, I'll give it a read.
On Mon, Oct 31, 2016 at 3:24 PM, David G. Johnston
<david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
On Mon, Oct 31, 2016 at 3:13 PM, Michael Moore
<michaeljmoore@gmail.com <mailto:michaeljmoore@gmail.com>>wrote:
Here is the complete function, but all you need to look at
is the exception block. (I didn't write this code) :-) I
will ask the question after the code.
[...]
RETURN TRUE;
EXCEPTION WHEN OTHERS THEN
RAISE EXCEPTION '% %', SQLERRM, SQLSTATE;
ROLLBACK;
RETURN FALSE;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
So, here is the question. Why does the compiler not catch:
1) ROLLBACK; is not a valid PL/pgSQL command
R
eading section 41.10.2 at the linked page should answer this part.
https://www.postgresql.org/docs/current/static/plpgsql-imple mentation.html
<https://www.postgresql.org/docs/current/static/plpgsql-impl ementation.html>
2) ROLLBACK; and RETURN FALSE; can never be reached
Similar to the above - though "static analysis" is yet a step
beyond even what the syntax checking skipping covered above
would reveal.
David J.
--
Adrian Klaver
adrian.klaver@aklaver.com
On 10/31/2016 06:09 PM, Michael Moore wrote: > Thanks Adrian, but is ROLLBACK *_ever_* possible in PL/pgSQL? My > understanding is, "No". Well not directly. This is where the memory faded. As I understand it pl/pgsql uses savepoints under the hood for: https://www.postgresql.org/docs/9.5/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING When trying to figure this out in the past I found: RollbackAndReleaseCurrentSubTransaction(); in pl_exec.c So you are correct. > > On Mon, Oct 31, 2016 at 4:38 PM, Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote: > > On 10/31/2016 04:32 PM, Michael Moore wrote: > > I'm still a bit confused. If I replace the ROLLBACK; command with > ELEPHANT; the result is a syntax error. Why doesn't ROLLBACK; > produce > the same error since it is not valid in the LANGUAGE plpgsql. I > understand that "ROLLBACK TO SAVEPOINT" IS valid. But it's not > the same > thing. > > > I am guessing this: > > https://www.postgresql.org/docs/9.5/static/plpgsql-implementation.html > <https://www.postgresql.org/docs/9.5/static/plpgsql-implementation.html> > " A disadvantage is that errors in a specific expression or command > cannot be detected until that part of the function is reached in > execution. (Trivial syntax errors will be detected during the > initial parsing pass, but anything deeper will not be detected until > execution.)" > > ROLLBACK might actually be valid at some point, ELEPHANT will not so > it caught in the trivial error stage. > > > On Mon, Oct 31, 2016 at 3:55 PM, Michael Moore > <michaeljmoore@gmail.com <mailto:michaeljmoore@gmail.com> > <mailto:michaeljmoore@gmail.com > <mailto:michaeljmoore@gmail.com>>> wrote: > > Cool, thanks David, I'll give it a read. > > > On Mon, Oct 31, 2016 at 3:24 PM, David G. Johnston > <david.g.johnston@gmail.com > <mailto:david.g.johnston@gmail.com> > <mailto:david.g.johnston@gmail.com > <mailto:david.g.johnston@gmail.com>>> wrote: > > On Mon, Oct 31, 2016 at 3:13 PM, Michael Moore > <michaeljmoore@gmail.com > <mailto:michaeljmoore@gmail.com> <mailto:michaeljmoore@gmail.com > <mailto:michaeljmoore@gmail.com>>>wrote: > > Here is the complete function, but all you need to > look at > is the exception block. (I didn't write this code) > :-) I > will ask the question after the code. > [...] > > RETURN TRUE; > > EXCEPTION WHEN OTHERS THEN > > RAISE EXCEPTION '% %', SQLERRM, SQLSTATE; > > ROLLBACK; > > RETURN FALSE; > > END; > > $BODY$ > > LANGUAGE plpgsql VOLATILE > > COST 100; > > > So, here is the question. Why does the compiler not > catch: > > 1) ROLLBACK; is not a valid PL/pgSQL command > > > R > eading section 41.10.2 at the linked page should > answer this part. > > > https://www.postgresql.org/docs/current/static/plpgsql-implementation.html > <https://www.postgresql.org/docs/current/static/plpgsql-implementation.html> > > <https://www.postgresql.org/docs/current/static/plpgsql-implementation.html > <https://www.postgresql.org/docs/current/static/plpgsql-implementation.html>> > > > 2) ROLLBACK; and RETURN FALSE; can never be reached > > > > Similar to the above - though "static analysis" is yet a > step > beyond even what the syntax checking skipping covered above > would reveal. > > David J. > > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > > -- Adrian Klaver adrian.klaver@aklaver.com
Michael Moore <michaeljmoore@gmail.com> writes:
> I'm still a bit confused. If I replace the ROLLBACK; command with ELEPHANT;
> the result is a syntax error. Why doesn't ROLLBACK; produce the same error
> since it is not valid in the LANGUAGE plpgsql.
That's a runtime error so far as plpgsql is concerned, because it relies
on the SPI layer to throw the error. It might be practical to complain
about it at compile time, but it would be some extra code that nobody's
written.
regards, tom lane
Hi Tom,
understood. If anybody needs a tiny bit of motivation to write that extra bit of code which would complain at compile time: I sense that there are a lot of shops like mine who would love to get off of Oracle due to the cost. That means you'll have guys like myself who are well versed on Oracle's PL/SQL trying to write PL/pgSQL functions. If ROLLBACK; were to cause a syntax error, it would immediately tell guys like myself that we are missing a key concept of how PL/pgSQL works. Saving the error until run time makes look for other reasons that ROLLBACK; might not be working. I know it's a trivial point, but just putting it out there.
Thanks everybody for the enlightening conversation!
Regards,
Mike
On Mon, Oct 31, 2016 at 7:38 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Michael Moore <michaeljmoore@gmail.com> writes:
> I'm still a bit confused. If I replace the ROLLBACK; command with ELEPHANT;
> the result is a syntax error. Why doesn't ROLLBACK; produce the same error
> since it is not valid in the LANGUAGE plpgsql.
That's a runtime error so far as plpgsql is concerned, because it relies
on the SPI layer to throw the error. It might be practical to complain
about it at compile time, but it would be some extra code that nobody's
written.
regards, tom lane
2016-11-01 18:36 GMT+01:00 Michael Moore <michaeljmoore@gmail.com>:
Hi Tom,understood. If anybody needs a tiny bit of motivation to write that extra bit of code which would complain at compile time: I sense that there are a lot of shops like mine who would love to get off of Oracle due to the cost. That means you'll have guys like myself who are well versed on Oracle's PL/SQL trying to write PL/pgSQL functions. If ROLLBACK; were to cause a syntax error, it would immediately tell guys like myself that we are missing a key concept of how PL/pgSQL works. Saving the error until run time makes look for other reasons that ROLLBACK; might not be working. I know it's a trivial point, but just putting it out there.
This issue can be checked simply by plpgsql_check in next version.
Regards
Pavel
Thanks everybody for the enlightening conversation!Regards,MikeOn Mon, Oct 31, 2016 at 7:38 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:Michael Moore <michaeljmoore@gmail.com> writes:
> I'm still a bit confused. If I replace the ROLLBACK; command with ELEPHANT;
> the result is a syntax error. Why doesn't ROLLBACK; produce the same error
> since it is not valid in the LANGUAGE plpgsql.
That's a runtime error so far as plpgsql is concerned, because it relies
on the SPI layer to throw the error. It might be practical to complain
about it at compile time, but it would be some extra code that nobody's
written.
regards, tom lane
2016-11-01 18:43 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:
2016-11-01 18:36 GMT+01:00 Michael Moore <michaeljmoore@gmail.com>:Hi Tom,understood. If anybody needs a tiny bit of motivation to write that extra bit of code which would complain at compile time: I sense that there are a lot of shops like mine who would love to get off of Oracle due to the cost. That means you'll have guys like myself who are well versed on Oracle's PL/SQL trying to write PL/pgSQL functions. If ROLLBACK; were to cause a syntax error, it would immediately tell guys like myself that we are missing a key concept of how PL/pgSQL works. Saving the error until run time makes look for other reasons that ROLLBACK; might not be working. I know it's a trivial point, but just putting it out there.This issue can be checked simply by plpgsql_check in next version.
tested by https://github.com/okbob/plpgsql_check/commit/1d3d2f5c853077b9b11a2aad9ad8986c06bee0af commit.
Regards
Pavel
RegardsPavelThanks everybody for the enlightening conversation!Regards,MikeOn Mon, Oct 31, 2016 at 7:38 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:Michael Moore <michaeljmoore@gmail.com> writes:
> I'm still a bit confused. If I replace the ROLLBACK; command with ELEPHANT;
> the result is a syntax error. Why doesn't ROLLBACK; produce the same error
> since it is not valid in the LANGUAGE plpgsql.
That's a runtime error so far as plpgsql is concerned, because it relies
on the SPI layer to throw the error. It might be practical to complain
about it at compile time, but it would be some extra code that nobody's
written.
regards, tom lane