SEO

I saw in the release notes that 8.1 is supposed to do function syntax
checking at create rather than run time, but with the first beta this
does not seem to work. check function bodies is on by default in the
postgresql.conf file. Is there a setting that didn't make it into the
conf file?

Thanks,

Tony

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Wed, Aug 31, 2005 at 11:39:48AM -0500, Tony Caduto wrote:
> I saw in the release notes that 8.1 is supposed to do function syntax
> checking at create rather than run time, but with the first beta this
> does not seem to work. check function bodies is on by default in the
> postgresql.conf file. Is there a setting that didn't make it into the
> conf file?

It works for me; care to submit an test case?

--
Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com
"We are who we choose to be", sang the goldfinch
when the sun is high (Sandman)

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

http://archives.postgresql.org

here is a case that does not work:

CREATE or REPLACE FUNCTION public.test_func9(out firstname varchar,out
lastname varchar)
RETURNS SETOF pg_catalog.record AS
$BODY$
Declare
row record56;
BEGIN
for $0 in select '',description from common.common_groups
loop
-- firstname = row.description;
-- lastname = '';
RETURN NEXT;
end loop;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

notice the for in select, it's for sure wrong, but it raises no errors
until I execute the function
also note the declaration for row, there is no record56 type, but it
raises no errors at create.

here is my version string:

PostgreSQL 8.1beta1 on i686-pc-linux-gnu, compiled by GCC
i686-pc-linux-gnu-gcc (GCC) 3.3.5-20050130 (Gentoo Linux
3.3.5.20050130-r1, ssp-3.3.5.20050130-1, pie-8.7.7.1)

Alvaro Herrera wrote:

>On Wed, Aug 31, 2005 at 11:39:48AM -0500, Tony Caduto wrote:
>
>
>>I saw in the release notes that 8.1 is supposed to do function syntax
>>checking at create rather than run time, but with the first beta this
>>does not seem to work. check function bodies is on by default in the
>>postgresql.conf file. Is there a setting that didn't make it into the
>>conf file?
>>
>>
>
>It works for me; care to submit an test case?
>
>
>


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Tony Caduto <tony_caduto@amsoftwaredesign.com> writes:
> notice the for in select, it's for sure wrong, but it raises no errors
> until I execute the function
> also note the declaration for row, there is no record56 type, but it
> raises no errors at create.

It's *syntax* checking, not an exhaustive check that the function is OK.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Tom,
What exactly does it check then? What I pointed out is simple "syntax"
checking in other languages.

From what I have seen it does not check anything in the body of the
function, I can put gibberish in the body as long as it has a begin and end.

It does not seem to be doing anything differently than 8.0.x does with
function syntax checking at create time, so why even mention it in the
release notes?

the function below also raises no errors at create, but at run time it does.

If I run the below function I get this error:

PostgreSQL Error Code: (1)
ERROR: type "record44" does not exist

From what I read in the release notes I was expecting to see this
raised at create time.
Users coming from systems like Oracle and M$ SQL server are expecting
this stuff to be caught at create not run time.

How difficult would it be to have the server just run the function at
create time with null for any input params? Of course a user could just
do this but it is a annoying second step.

CREATE or REPLACE FUNCTION public.test_func9(out firstname varchar,out
lastname varchar)
RETURNS SETOF pg_catalog.record AS
$BODY$
Declare
row record44;
BEGIN
asfdfdfdfafdsfsdfsdf
sdf bla bla
sdf yada yada
s
df
sd
fsd
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

Thanks,

Tony

Tom Lane wrote:

>Tony Caduto <tony_caduto@amsoftwaredesign.com> writes:
>
>
>>notice the for in select, it's for sure wrong, but it raises no errors
>>until I execute the function
>>also note the declaration for row, there is no record56 type, but it
>>raises no errors at create.
>>
>>
>
>It's *syntax* checking, not an exhaustive check that the function is OK.
>
> regards, tom lane
>
>
>


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Wed, 2005-08-31 at 13:13 -0500, Tony Caduto wrote:
> the function below also raises no errors at create, but at run time it does.
> ...
> CREATE or REPLACE FUNCTION public.test_func9(out firstname varchar,out
> lastname varchar)
> RETURNS SETOF pg_catalog.record AS
> $BODY$
> Declare
> row record44;
> BEGIN
> asfdfdfdfafdsfsdfsdf
> sdf bla bla
> sdf yada yada
> s
> df
> sd
> fsd
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;

When I execute this CREATE statement I get:

ERROR: type "record44" does not exist
CONTEXT: compile of PL/pgSQL function "test_func9" near line 2

So, it does seem to be working as advertised.

I'm running HEAD as of a few hours ago.

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

http://www.postgresql.org/docs/faq

Tony Caduto <tony_caduto@amsoftwaredesign.com> writes:
> It does not seem to be doing anything differently than 8.0.x does with
> function syntax checking at create time, so why even mention it in the
> release notes?

The checking is more extensive than it was in 8.0. For example 8.0
didn't reject this at creation:

regression=# create function bla() returns int as 'begin zit; end' language plpgsql;
ERROR: syntax error at or near "zit" at character 1
QUERY: zit
CONTEXT: SQL statement in PL/PgSQL function "bla" near line 1
LINE 1: zit
^
regression=#

because 8.0 didn't feed any apparent SQL statements down to the main
SQL grammar to see if they were sane according to the main grammar.

But it remains purely *syntactic*. If the code gets through the
grammar then it's accepted. What this boils down to is that we
don't apply any checking that depends on anything outside the
function itself (for example, whether something that is used as
a type name actually exists in pg_type).

> How difficult would it be to have the server just run the function at
> create time with null for any input params?

What happens if the function (intentionally) errors out on null inputs?
Or goes into an infinite loop? (If it's declared STRICT then the
programmer would be quite within his rights not to handle such a case.)
What happens if the function changes the database on the basis of the
bogus call? How much would this actually prove, considering that null
inputs would be likely not to exercise many of the code paths within the
function?

regards, tom lane

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

http://www.postgresql.org/docs/faq

Tony Caduto <tony_caduto@amsoftwaredesign.com> writes:
> CREATE or REPLACE FUNCTION public.test_func9(out firstname varchar,out
> lastname varchar)
> RETURNS SETOF pg_catalog.record AS
> $BODY$
> Declare
> row record44;
> BEGIN
> asfdfdfdfafdsfsdfsdf
> sdf bla bla
> sdf yada yada
> s
> df
> sd
> fsd
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;

[ looks at that again... ] Wait, are you sure that you are talking to
an 8.1 server? 8.1 will certainly catch the garbage syntax in the
function body, whether or not it notices that the type name is bogus.

regards, tom lane

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

http://archives.postgresql.org

On Wed, Aug 31, 2005 at 01:13:00PM -0500, Tony Caduto wrote:
> From what I have seen it does not check anything in the body of the
> function, I can put gibberish in the body as long as it has a begin and end.
>
> It does not seem to be doing anything differently than 8.0.x does with
> function syntax checking at create time, so why even mention it in the
> release notes?

I see different behavior in 8.1 than in 8.0. Are you *sure* you're
connected to an 8.1 system when you're running your tests?

Are you using a database that was restored from an earlier version
of PostgreSQL? I wonder if you're not getting the lanvalidator
function. What's the result of the following query?

SELECT lanname,
lanplcallfoid, lanplcallfoid::regprocedure,
lanvalidator, lanvalidator::regprocedure
FROM pg_language;

What happens if you create a fresh database and run "createlang
plpgsql" in it, and then run your tests?

> the function below also raises no errors at create, but at run time it does.

With the example you posted I get the following at create time:

ERROR: type "record44" does not exist
CONTEXT: compile of PL/pgSQL function "test_func9" near line 2

If I change "record44" to "record" then I get the following (again,
at create time):

ERROR: syntax error at or near "asfdfdfdfafdsfsdfsdf" at character 1
QUERY: asfdfdfdfafdsfsdfsdf sdf bla bla sdf yada yada s df sd fsd END
CONTEXT: SQL statement in PL/PgSQL function "test_func9" near line 10
LINE 1: asfdfdfdfafdsfsdfsdf sdf bla bla sdf yada yada s df sd fsd E...
^

> From what I read in the release notes I was expecting to see this
> raised at create time.

Create-time checking works here.

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Tony,

> From what I have seen it does not check anything in the body of the
> function, I can put gibberish in the body as long as it has a begin and
> end.

Nope:

stp=# create function bad_stuff ( x boolean ) returns boolean as $x$
stp$# begin
stp$# afasdfasdfasdf;
stp$# afasdfasdfa;
stp$# asdfasfasdf;
stp$# end;
stp$# $x$ language plpgsql;
ERROR: syntax error at or near "afasdfasdfasdf" at character 1
QUERY: afasdfasdfasdf
CONTEXT: SQL statement in PL/PgSQL function "bad_stuff" near line 2
ERROR: syntax error at or near "afasdfasdfasdf" at character 1
QUERY: afasdfasdfasdf
CONTEXT: SQL statement in PL/PgSQL function "bad_stuff" near line 2
LINE 1: afasdfasdfasdf

Are you sure you don't have check_function_bodies = Off?

There is a difference between *syntax* errors and *sql* errors. If a
table does not exist, we don't want to check for that and bounce the
function; possibly the function will only be called in a context where the
table does exist.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

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

http://www.postgresql.org/docs/faq