agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedWhy does the PL/pgSQL compiler do this?
11+ messages / 5 participants
[nested] [flat]
* Why does the PL/pgSQL compiler do this?
@ 2016-10-31 22:13 Michael Moore <michaeljmoore@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Michael Moore @ 2016-10-31 22:13 UTC (permalink / raw)
To: pgsql-sql
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_stat(
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-YY HH24:MI:SS')
AND updateddate <= TO_DATE(p_end_date,'DD-MON-YY HH24:MI:SS');
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,
COUNTRYCODE2TPOSTALCOORDINATE,POSTALCODE2TPOSTALCOORDINATE,
SERVICE2TX_SERVICE_CATALOG,MATCH_RATE,REVENUE_AMT,LAST_
CALCULATED_DATE,KEY2TX_CRITERIA_TREE,CREATEDDATE,
CREATEDBYT2USER,UPDATEDDATE,UPDATEDBY2TUSER)
select PULL_STAT_KEY,COUNTRYCODE2TPOSTALCOORDINATE,
POSTALCODE2TPOSTALCOORDINATE,SERVICE2TX_SERVICE_CATALOG,
MATCH_RATE,REVENUE_AMT,LAST_CALCULATED_DATE,KEY2TX_
CRITERIA_TREE,CREATEDDATE,CREATEDBYT2USER,UPDATEDDATE,UPDATEDBY2TUSER
FROM fs_QSN_APP.tx_pull_client_stat;
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. ;-)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why does the PL/pgSQL compiler do this?
@ 2016-10-31 22:24 David G. Johnston <david.g.johnston@gmail.com>
parent: Michael Moore <michaeljmoore@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: David G. Johnston @ 2016-10-31 22:24 UTC (permalink / raw)
To: Michael Moore <michaeljmoore@gmail.com>; +Cc: pgsql-sql
On Mon, Oct 31, 2016 at 3:13 PM, Michael Moore <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
> 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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why does the PL/pgSQL compiler do this?
@ 2016-10-31 22:55 Michael Moore <michaeljmoore@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Michael Moore @ 2016-10-31 22:55 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-sql
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:
> On Mon, Oct 31, 2016 at 3:13 PM, Michael Moore <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
>
>
>> 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.
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why does the PL/pgSQL compiler do this?
@ 2016-10-31 23:32 Michael Moore <michaeljmoore@gmail.com>
parent: Michael Moore <michaeljmoore@gmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: Michael Moore @ 2016-10-31 23:32 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-sql
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:
>
>> On Mon, Oct 31, 2016 at 3:13 PM, Michael Moore <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
>>
>>
>>> 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.
>>
>>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why does the PL/pgSQL compiler do this?
@ 2016-10-31 23:38 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Michael Moore <michaeljmoore@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Adrian Klaver @ 2016-10-31 23:38 UTC (permalink / raw)
To: Michael Moore <michaeljmoore@gmail.com>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why does the PL/pgSQL compiler do this?
@ 2016-11-01 01:09 Michael Moore <michaeljmoore@gmail.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 11+ messages in thread
From: Michael Moore @ 2016-11-01 01:09 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-sql
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-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-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
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why does the PL/pgSQL compiler do this?
@ 2016-11-01 01:20 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Michael Moore <michaeljmoore@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Adrian Klaver @ 2016-11-01 01:20 UTC (permalink / raw)
To: Michael Moore <michaeljmoore@gmail.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why does the PL/pgSQL compiler do this?
@ 2016-11-01 02:38 Tom Lane <tgl@sss.pgh.pa.us>
parent: Michael Moore <michaeljmoore@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Tom Lane @ 2016-11-01 02:38 UTC (permalink / raw)
To: Michael Moore <michaeljmoore@gmail.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why does the PL/pgSQL compiler do this?
@ 2016-11-01 17:36 Michael Moore <michaeljmoore@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Michael Moore @ 2016-11-01 17:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why does the PL/pgSQL compiler do this?
@ 2016-11-01 17:43 Pavel Stehule <pavel.stehule@gmail.com>
parent: Michael Moore <michaeljmoore@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Pavel Stehule @ 2016-11-01 17:43 UTC (permalink / raw)
To: Michael Moore <michaeljmoore@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; David G. Johnston <david.g.johnston@gmail.com>; pgsql-sql
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,
> 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
>>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Why does the PL/pgSQL compiler do this?
@ 2016-11-02 22:15 Pavel Stehule <pavel.stehule@gmail.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Pavel Stehule @ 2016-11-02 22:15 UTC (permalink / raw)
To: Michael Moore <michaeljmoore@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; David G. Johnston <david.g.johnston@gmail.com>; pgsql-sql
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
>
> Regards
>
> Pavel
>
>
>
>
>>
>> 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
>>>
>>
>>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2016-11-02 22:15 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-10-31 22:13 Why does the PL/pgSQL compiler do this? Michael Moore <michaeljmoore@gmail.com>
2016-10-31 22:24 ` David G. Johnston <david.g.johnston@gmail.com>
2016-10-31 22:55 ` Michael Moore <michaeljmoore@gmail.com>
2016-10-31 23:32 ` Michael Moore <michaeljmoore@gmail.com>
2016-10-31 23:38 ` Adrian Klaver <adrian.klaver@aklaver.com>
2016-11-01 01:09 ` Michael Moore <michaeljmoore@gmail.com>
2016-11-01 01:20 ` Adrian Klaver <adrian.klaver@aklaver.com>
2016-11-01 02:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-11-01 17:36 ` Michael Moore <michaeljmoore@gmail.com>
2016-11-01 17:43 ` Pavel Stehule <pavel.stehule@gmail.com>
2016-11-02 22:15 ` Pavel Stehule <pavel.stehule@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox