Re: Procedural language definitions (was Re: [HACKERS] 8.1 and syntax checking at create time)
On Wed, Aug 31, 2005 at 05:56:52PM -0400, Tom Lane wrote: I wrote: We've had repeated problems with PL languages stemming from the fact that pg_dump dumps them at a pretty low semantic level. Aside from this problem with adding a validator, we used to have issues with hardwired paths to the shared libraries in the CREATE FUNCTION commands. And in 8.1, whether the functions are in public or pg_catalog is going to vary across installations depending on whether the language was restored from a dump or not. I wonder if we could change the dump representation to abstract out the knowledge encapsulated in createlang. I don't suppose this would work: \! createlang plpgsql dbname but it'd be nice if the dump didn't know any more about the language than its name, and didn't mention the implementation functions at all. I thought some more about this and came up with a sketch of a solution. This would solve the problem of loading subtly-bogus language definitions from existing dump files, and it also offers a possibility of relaxing the rule that only superusers can create PLs. The basic idea is to create a shared catalog that contains procedural language templates. This catalog would essentially replace the knowledge that's now hardwired in the createlang program. It's shared because we need it to be already available in a new database; and anyway, the information in it need not vary across databases of an installation. I'm envisioning a schema like pg_pltemplate: lanname namename of PL lantrusted boolean trusted? lanhandler textname of its call handler function lanvalidator textname of its validator function, or NULL lanlibrary textpath of shared library, eg $libdir/plpgsql lanacl acl[] see below This could be filled in at initdb time with information about all the languages available in the standard distribution (whether or not they've actually been built) --- heck, we could include entries for all the PLs we know of, whether shipped in the core or not. Then we would change CREATE LANGUAGE so that it first takes the given PL name and looks to see if there is an entry by that name in pg_pltemplate. If so, it *ignores the given parameters* (if any) and uses what's in pg_pltemplate. The logic would be identical to what createlang does now: look to see if the functions already exist in the current database, create them if not, then create the language entry. (If the specified shared library does not actually exist in the installation, we'd fail at the create functions step --- this is why it's OK to have entries for languages not built in the distribution.) The bit about ignoring the given parameters is needed to be able to have the right things happen when loading an existing dump script from an older PG version with different support functions for the language. However, we would also simplify pg_dump to never dump the implementation functions of a language in future, and to emit CREATE LANGUAGE as just CREATE LANGUAGE plpgsql; without decoration. (createlang would reduce to that too.) For languages that do not have a template in pg_pltemplate, CREATE LANGUAGE would operate the same as now. This case supports languages that we don't know of. It might also be worthwhile to create a command like CREATE LANGUAGE TEMPLATE ... to simplify making new entries in pg_pltemplate. (However, we could not ask pg_dump to dump templates, else we've merely moved the obsolete-dump problem over one space. Not sure if anyone would see that as a fatal objection to the scheme. I think it's a pretty minor point as long as we are liberal about including template entries in the standard distro, so that you'd seldom need to add one by hand.) Finally, you noticed I stuck an ACL column in there. I am imagining that the superuser could grant USAGE rights on a template to designated people (eg, admins of individual databases), who could then issue CREATE LANGUAGE using that template in their databases, without needing superuser rights. You'd still have to be superuser to muck with the templates of course, but given a known-good template there's no reason why a non-superuser shouldn't be allowed to instantiate the language within his database. (This might need a little more thought when it comes to untrusted PLs, but the idea seems sound.) It's a shame that we didn't think about this before feature freeze, as the recent changes to create PL support functions in pg_catalog have made both pg_dump and createlang noticeably uglier than before. We could have dispensed with those hacks. Oh well. Comments? This idea appears to me to be sound. It may be worth adding the feature during beta anyway to simplify the ugliness of pg_dump with createlang problems. The large number of weird configurations out there could
Re: Procedural language definitions (was Re: [HACKERS] 8.1 and syntax checking at create time)
[EMAIL PROTECTED] (elein) writes: On Wed, Aug 31, 2005 at 05:56:52PM -0400, Tom Lane wrote: The basic idea is to create a shared catalog that contains procedural language templates. This catalog would essentially replace the knowledge that's now hardwired in the createlang program. ... It's a shame that we didn't think about this before feature freeze, as the recent changes to create PL support functions in pg_catalog have made both pg_dump and createlang noticeably uglier than before. We could have dispensed with those hacks. Oh well. This idea appears to me to be sound. It may be worth adding the feature during beta anyway to simplify the ugliness of pg_dump with createlang problems. The large number of weird configurations out there could use the beta testing of this release. I ran into this issue a lot with non-standard installations. I was thinking the same thing, but it's a big change to put in during beta. We could trim back the size of the patch a good deal by not implementing the ACL part just yet (ie, you'd still have to be superuser to create a PL). However, we'd still need to force an initdb to add the new system catalog, and I hate to do that to our long-suffering beta testers. An even more trimmed-back version would not create a new system catalog now, but would use a constant table of known PLs that's hardwired into the CREATE LANGUAGE code. We could do that in a really localized fashion, so it seems small enough for a post-beta change. On the other hand: if we put that into beta2, and then get a related bug report, we wouldn't be really sure if the reporter had a correct PL definition or an incorrect one that he'd carried forward from beta1. Forcing an initdb would let us be sure from the version what we were dealing with. Comments anyone? 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
Re: Procedural language definitions (was Re: [HACKERS] 8.1 and syntax checking at create time)
Andrew Dunstan [EMAIL PROTECTED] writes: I agree with Tom that it should not be done at this stage of beta. But maybe we should look again at the much lower impact suggestion I made when we moved the handlers and validators to pg_catalog, which was to have pg_dump also do that move rather than leave existing handlers in public. How are you retroactively going to make existing pg_dumps do that? I think trying to handle this in pg_dump would introduce still more inconsistency across installations, because on top of the variables we have already, it'd matter which pg_dump version you used. I feel the best idea for a non-initdb-forcing solution is to hardwire the template knowledge into CREATE LANGUAGE for 8.1 (with of course the intention of doing my full original proposal for 8.2). With that in place, the only messiness from loading old dumps is that you would have handler function definitions in public --- but they wouldn't be used (the actual languages would rely on handlers in pg_catalog) and could be dropped easily. One reason for doing this now rather than later is that if we wait, in 8.2 we will be having to contend with 8.1 dumps that want to load handler function definitions into pg_catalog. That'll be OK as long as said definitions are correct --- but if we change any of the PL function properties between now and 8.2, we'll have a self-inflicted problem to deal with. (In the PL template approach as I proposed it, any existing function of the right name is presumed to be the right thing.) I think it would be a really good idea if we could get that out of pg_dump again before 8.1 goes final. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: Procedural language definitions (was Re: [HACKERS] 8.1 and syntax checking at create time)
Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: I feel the best idea for a non-initdb-forcing solution is to hardwire the template knowledge into CREATE LANGUAGE for 8.1 (with of course the intention of doing my full original proposal for 8.2). With that in place, the only messiness from loading old dumps is that you would have handler function definitions in public --- but they wouldn't be used (the actual languages would rely on handlers in pg_catalog) and could be dropped easily. Ok, that sounds good. Maybe have pg_dump issue a warning about the useless handler funcs left lying around? Again, you're imagining that we can retroactively fix existing pg_dumps. A pg_dump that's aware of this change will simply not dump handlers at all --- so it doesn't need to issue any warning. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] 8.1 and syntax checking at create time
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
Re: [HACKERS] 8.1 and syntax checking at create time
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
Re: [HACKERS] 8.1 and syntax checking at create time
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
Re: [HACKERS] 8.1 and syntax checking at create time
Tony Caduto [EMAIL PROTECTED] 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
Re: [HACKERS] 8.1 and syntax checking at create time
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 8.1 and syntax checking at create time
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
Re: [HACKERS] 8.1 and syntax checking at create time
Tony Caduto [EMAIL PROTECTED] 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
Re: [HACKERS] 8.1 and syntax checking at create time
Tony Caduto [EMAIL PROTECTED] 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
Re: [HACKERS] 8.1 and syntax checking at create time
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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 8.1 and syntax checking at create time
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
Re: [HACKERS] 8.1 and syntax checking at create time
Hi, I did restore from a 8.0 dump. here is the output from the query: lanname | lanplcallfoid | lanplcallfoid | lanvalidator | lanvalidator --+---++--+-- internal | 0 | - | 2246 | fmgr_internal_validator(oid) c| 0 | - | 2247 | fmgr_c_validator(oid) sql | 0 | - | 2248 | fmgr_sql_validator(oid) plperlu | 16392 | plperl_call_handler() |0 | - plpgsql | 16394 | plpgsql_call_handler() |0 | - (5 rows) 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) I am trying my tests on a new database with fresh language install now. How can I get my restored databases to behave the same as a fresh one? Thanks for your help on this. 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
Re: [HACKERS] 8.1 and syntax checking at create time
Matt, On Wed, 2005-08-31 at 11:59 -0700, Josh Berkus wrote: 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. The Pl/pgSQL compiler should be able to dive into SQL statements, hit the catalog, and bounce a function because of invalid database object references. Ideally this capability could be turned off on demand. Well, that would certainly be nice to have as an *additional* capability. Patches welcome! -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] 8.1 and syntax checking at create time
Matt Miller [EMAIL PROTECTED] writes: On Wed, 2005-08-31 at 11:59 -0700, Josh Berkus wrote: 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. I am thankful that Oracle's PL/SQL compiler checks these things for me. I don't remember the last time I intended to write code that referenced something that did not exist in the database. Almost every day, people try to write stuff like CREATE TEMP TABLE foo ... ; INSERT INTO foo ... ; etc etc DROP TABLE foo ; in plpgsql functions. Now I know that that doesn't work very well, but we should be going in the direction of fixing it to work well, not installing error checks that are guaranteed to make it fail. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 8.1 and syntax checking at create time
Michael Fuhr [EMAIL PROTECTED] writes: Are you using a database that was restored from an earlier version of PostgreSQL? I wonder if you're not getting the lanvalidator function. Ah-hah, that sounds like a good theory. He'd have had to have carried the DB forward from 7.4 or before, though, since plpgsql had a validator in 8.0. We've had repeated problems with PL languages stemming from the fact that pg_dump dumps them at a pretty low semantic level. Aside from this problem with adding a validator, we used to have issues with hardwired paths to the shared libraries in the CREATE FUNCTION commands. And in 8.1, whether the functions are in public or pg_catalog is going to vary across installations depending on whether the language was restored from a dump or not. I wonder if we could change the dump representation to abstract out the knowledge encapsulated in createlang. I don't suppose this would work: \! createlang plpgsql dbname but it'd be nice if the dump didn't know any more about the language than its name, and didn't mention the implementation functions at all. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.1 and syntax checking at create time
On Wed, 2005-08-31 at 11:59 -0700, Josh Berkus wrote: 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. The Pl/pgSQL compiler should be able to dive into SQL statements, hit the catalog, and bounce a function because of invalid database object references. Ideally this capability could be turned off on demand. I am thankful that Oracle's PL/SQL compiler checks these things for me. I don't remember the last time I intended to write code that referenced something that did not exist in the database. I agree,though, that some developers might rely on such a capability in some circumstances. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.1 and syntax checking at create time
On Wed, 2005-08-31 at 15:29 -0400, Tom Lane wrote: Matt Miller [EMAIL PROTECTED] writes: I don't remember the last time I intended to write code that referenced something that did not exist in the database. Almost every day, people try to write stuff like CREATE TEMP TABLE foo ... ; INSERT INTO foo ... ; etc etc DROP TABLE foo ; Point taken. PL/SQL requires all DDL to be dynamic SQL. For example: execute immediate 'drop table foo'; The stuff inside the string is pretty-much ignored at compile time. Maybe, then, my idealized PL/pgSQL compiler always allows DDL to reference any object, but DML is checked against the catalog. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.1 and syntax checking at create time
On Wed, Aug 31, 2005 at 03:37:21PM -0400, Andrew Dunstan wrote: Tony Caduto wrote: How can I get my restored databases to behave the same as a fresh one? Run createlang plpgsql mydb before running your restore, and possibly remove the bits that create them from the dump script, or they might just fail benignly. In an already-loaded database, I think the following should work: UPDATE pg_language SET lanvalidator = 'plpgsql_validator'::regproc WHERE lanname = 'plpgsql'; I'd recommend wrapping the update in a transaction and making sure only one record was updated before committing. Tom (or anybody else), are there any gotchas with updating pg_language like this? It works for me in simple tests. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 8.1 and syntax checking at create time
On Wed, Aug 31, 2005 at 07:43:45PM +, Matt Miller wrote: On Wed, 2005-08-31 at 15:29 -0400, Tom Lane wrote: Matt Miller [EMAIL PROTECTED] writes: I don't remember the last time I intended to write code that referenced something that did not exist in the database. Almost every day, people try to write stuff like CREATE TEMP TABLE foo ... ; INSERT INTO foo ... ; etc etc DROP TABLE foo ; Point taken. PL/SQL requires all DDL to be dynamic SQL. For example: execute immediate 'drop table foo'; BTW, the way you handled this case in DB2 was: CREATE TEMP TABLE foo ...; CREATE FUNCTION blah AS ...; DROP TEMP TABLE foo; This way the object you wanted did exist when you were creating the function. Of course it would be better if plpgsql could just read the DDL and deal with it... but I'd say that doing the CREATE TABLE outside the statement is better than nothing. Actually, I think you only had to do the CREATE TEMP TABLE outside the function creation if the function didn't create the temp table itself. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.1 and syntax checking at create time
I just found out the databases on 8.0 where originally restored from a 7.4 server, so it seems I have never had the lanvalidator function even while running on 8.0 for the last 10 months :-( So how can I update my restored databases, i tried dropping the language, but it wouldn't let me becasuse of dependent objects. Thanks, Tony 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? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.1 and syntax checking at create time
On Wed, Aug 31, 2005 at 11:59:47AM -0700, Josh Berkus wrote: 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. It would still be nice to have, with a way to over-ride it, either via an option to CREATE FUNCTION or with some directive to plpgsql itself inside the function body (probably the most useful case since it allows disabling error checking just where it's needed). -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.1 and syntax checking at create time
Tony Caduto wrote: Hi, I did restore from a 8.0 dump. [snip] I am trying my tests on a new database with fresh language install now. How can I get my restored databases to behave the same as a fresh one? Run createlang plpgsql mydb before running your restore, and possibly remove the bits that create them from the dump script, or they might just fail benignly. cheers andrew ---(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
Re: [HACKERS] 8.1 and syntax checking at create time
Tom, I successfully updated my database to use the validator function without dropping it using: CREATE FUNCTION plpgsql_validator (oid) RETURNS void AS '$libdir/plpgsql' LANGUAGE C; UPDATE pg_language SET lanvalidator = 'plpgsql_validator'::regproc WHERE lanname = 'plpgsql'; The create checking is *much* better now :-) Thanks to everyone for helping me track this down, turned out it had nothing to do with 8.1 but I didn't know that. Sorry about that. Tony That would not create a dependency from the language to the validator, but in practice you probably don't care about that. The bigger problem for Tony is likely to be that plpgsql_validator() doesn't exist as a function in his database; he'll have to create it (see createlang -e for a reference) first. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.1 and syntax checking at create time
[Please don't top-post; it destroys the conversational flow. I've moved your comment below what you commented on.] On Wed, Aug 31, 2005 at 03:13:02PM -0500, Tony Caduto wrote: In an already-loaded database, I think the following should work: UPDATE pg_language SET lanvalidator = 'plpgsql_validator'::regproc WHERE lanname = 'plpgsql'; When I run this I get this error in the database: PostgreSQL Error Code: (1) ERROR: function plpgsql_validator does not exist Oops...createlang would ordinarily create that function, but since you restored from another database the validator function was never created. Try adding this before the UPDATE (stolen from pg_dump): CREATE FUNCTION pg_catalog.plpgsql_validator(oid) RETURNS void AS '$libdir/plpgsql', 'plpgsql_validator' LANGUAGE c; -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 8.1 and syntax checking at create time
Michael Fuhr [EMAIL PROTECTED] writes: In an already-loaded database, I think the following should work: UPDATE pg_language SET lanvalidator = 'plpgsql_validator'::regproc WHERE lanname = 'plpgsql'; Tom (or anybody else), are there any gotchas with updating pg_language like this? It works for me in simple tests. That would not create a dependency from the language to the validator, but in practice you probably don't care about that. The bigger problem for Tony is likely to be that plpgsql_validator() doesn't exist as a function in his database; he'll have to create it (see createlang -e for a reference) first. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] 8.1 and syntax checking at create time
When I run this I get this error in the database: PostgreSQL Error Code: (1) ERROR: function plpgsql_validator does not exist In an already-loaded database, I think the following should work: UPDATE pg_language SET lanvalidator = 'plpgsql_validator'::regproc WHERE lanname = 'plpgsql'; I'd recommend wrapping the update in a transaction and making sure only one record was updated before committing. Tom (or anybody else), are there any gotchas with updating pg_language like this? It works for me in simple tests. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] 8.1 and syntax checking at create time
I am new to this hacker's job. What I was looking for was to record the actual disk IO performed for arbituary query plan. I searched in backend/executor but not sure if that was the right place to add a tracer. would the /backend/storage be the place that controls the actual I/O? btw, is there a way to find the definitions of all variables or functions defined? I tried cscope but it is not good for such a large framework. thanks a lot
Re: [HACKERS] 8.1 and syntax checking at create time
huaxin zhang wrote: I am new to this hacker's job. What I was looking for was to record the actual disk IO performed for arbituary query plan. I searched in backend/executor but not sure if that was the right place to add a tracer. would the /backend/storage be the place that controls the actual I/O? btw, is there a way to find the definitions of all variables or functions defined? I tried cscope but it is not good for such a large framework. What has this to do with syntax checking? Please don't post irrelevant replies. Start a new topic instead. thanks andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Procedural language definitions (was Re: [HACKERS] 8.1 and syntax checking at create time)
I wrote: We've had repeated problems with PL languages stemming from the fact that pg_dump dumps them at a pretty low semantic level. Aside from this problem with adding a validator, we used to have issues with hardwired paths to the shared libraries in the CREATE FUNCTION commands. And in 8.1, whether the functions are in public or pg_catalog is going to vary across installations depending on whether the language was restored from a dump or not. I wonder if we could change the dump representation to abstract out the knowledge encapsulated in createlang. I don't suppose this would work: \! createlang plpgsql dbname but it'd be nice if the dump didn't know any more about the language than its name, and didn't mention the implementation functions at all. I thought some more about this and came up with a sketch of a solution. This would solve the problem of loading subtly-bogus language definitions from existing dump files, and it also offers a possibility of relaxing the rule that only superusers can create PLs. The basic idea is to create a shared catalog that contains procedural language templates. This catalog would essentially replace the knowledge that's now hardwired in the createlang program. It's shared because we need it to be already available in a new database; and anyway, the information in it need not vary across databases of an installation. I'm envisioning a schema like pg_pltemplate: lannamenamename of PL lantrusted boolean trusted? lanhandler textname of its call handler function lanvalidator textname of its validator function, or NULL lanlibrary textpath of shared library, eg $libdir/plpgsql lanacl acl[] see below This could be filled in at initdb time with information about all the languages available in the standard distribution (whether or not they've actually been built) --- heck, we could include entries for all the PLs we know of, whether shipped in the core or not. Then we would change CREATE LANGUAGE so that it first takes the given PL name and looks to see if there is an entry by that name in pg_pltemplate. If so, it *ignores the given parameters* (if any) and uses what's in pg_pltemplate. The logic would be identical to what createlang does now: look to see if the functions already exist in the current database, create them if not, then create the language entry. (If the specified shared library does not actually exist in the installation, we'd fail at the create functions step --- this is why it's OK to have entries for languages not built in the distribution.) The bit about ignoring the given parameters is needed to be able to have the right things happen when loading an existing dump script from an older PG version with different support functions for the language. However, we would also simplify pg_dump to never dump the implementation functions of a language in future, and to emit CREATE LANGUAGE as just CREATE LANGUAGE plpgsql; without decoration. (createlang would reduce to that too.) For languages that do not have a template in pg_pltemplate, CREATE LANGUAGE would operate the same as now. This case supports languages that we don't know of. It might also be worthwhile to create a command like CREATE LANGUAGE TEMPLATE ... to simplify making new entries in pg_pltemplate. (However, we could not ask pg_dump to dump templates, else we've merely moved the obsolete-dump problem over one space. Not sure if anyone would see that as a fatal objection to the scheme. I think it's a pretty minor point as long as we are liberal about including template entries in the standard distro, so that you'd seldom need to add one by hand.) Finally, you noticed I stuck an ACL column in there. I am imagining that the superuser could grant USAGE rights on a template to designated people (eg, admins of individual databases), who could then issue CREATE LANGUAGE using that template in their databases, without needing superuser rights. You'd still have to be superuser to muck with the templates of course, but given a known-good template there's no reason why a non-superuser shouldn't be allowed to instantiate the language within his database. (This might need a little more thought when it comes to untrusted PLs, but the idea seems sound.) It's a shame that we didn't think about this before feature freeze, as the recent changes to create PL support functions in pg_catalog have made both pg_dump and createlang noticeably uglier than before. We could have dispensed with those hacks. Oh well. Comments? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
