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

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

Matt Miller <mattm@epx.com> 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

Michael Fuhr <mike@fuhr.org> 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

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

On Wed, 2005-08-31 at 15:29 -0400, Tom Lane wrote:
> Matt Miller <mattm@epx.com> 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

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

On Wed, Aug 31, 2005 at 07:43:45PM +0000, Matt Miller wrote:
> On Wed, 2005-08-31 at 15:29 -0400, Tom Lane wrote:
> > Matt Miller <mattm@epx.com> 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 jnasby@pervasive.com
Pervasive Software http://pervasive.com 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

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 jnasby@pervasive.com
Pervasive Software http://pervasive.com 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings