Thread: pg_dump and languages
I used the pg_dump from Postgres 8.0beta5 to dump the data from a version 7.4.0 database. Both databases are located on SuSE Linux machines. The pg_restore to the 8.0 version went very well with the exception of the languages. I have plpgsql and plsh installed in the 7.4.0 database. pg_dump handled the dependencies for plpgsql alright but not for plsh. Searching the archives showed a previous problem with languages installed to pg_catalog which is were plsh is installed. However, I thought this had been solved. Is this correct? The other thought that occurred to me is that plsh is an untrusted language. I am dumping and restoring as user postgres so my understanding is that this should overcome any permissions issues. Again am I correct in thinking this? -- Adrian Klaver aklaver@comcast.net
Adrian Klaver <aklaver@comcast.net> writes:
> I used the pg_dump from Postgres 8.0beta5 to dump the data from a version
> 7.4.0 database. Both databases are located on SuSE Linux machines. The
> pg_restore to the 8.0 version went very well with the exception of the
> languages. I have plpgsql and plsh installed in the 7.4.0 database. pg_dump
> handled the dependencies for plpgsql alright but not for plsh. Searching the
> archives showed a previous problem with languages installed to pg_catalog
> which is were plsh is installed. However, I thought this had been solved. Is
> this correct? The other thought that occurred to me is that plsh is an
> untrusted language. I am dumping and restoring as user postgres so my
> understanding is that this should overcome any permissions issues. Again am I
> correct in thinking this?
This would be a more useful report had you said exactly what the problem
was ...
regards, tom lane
On Friday 26 November 2004 10:26 am, Tom Lane wrote: > Adrian Klaver <aklaver@comcast.net> writes: > > I used the pg_dump from Postgres 8.0beta5 to dump the data from a version > > 7.4.0 database. Both databases are located on SuSE Linux machines. The > > pg_restore to the 8.0 version went very well with the exception of the > > languages. I have plpgsql and plsh installed in the 7.4.0 database. > > pg_dump handled the dependencies for plpgsql alright but not for plsh. > > Searching the archives showed a previous problem with languages installed > > to pg_catalog which is were plsh is installed. However, I thought this > > had been solved. Is this correct? The other thought that occurred to me > > is that plsh is an untrusted language. I am dumping and restoring as user > > postgres so my understanding is that this should overcome any permissions > > issues. Again am I correct in thinking this? > > This would be a more useful report had you said exactly what the problem > was ... > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org Sorry should have been more explicit. The dump loaded functions depending on plsh before the function that created the language. In fact it did not instalI the language at all. I have installed both plpgsql and plsh into the 7.4 database as well as the template1 database for the 7.4 cluster. Running createlang -l shows both installed but only plpgsql is installed by the dump/restore procedure into the 8.0beta cluster. I compiled plsh on the machine with the 8.0beta so the files were in place on the new installation. At this point only two functions depend on plsh and Peter Eisentraut includes a script to load the language so this more of a wonder why question than a 'problem'. -- Adrian Klaver aklaver@comcast.net
Adrian Klaver <aklaver@comcast.net> writes:
> Sorry should have been more explicit. The dump loaded functions depending on
> plsh before the function that created the language. In fact it did not
> instalI the language at all.
Ah. Looking back at your first message, I see you'd installed plsh into
the pg_catalog schema rather than a user schema. pg_dump specifically
doesn't dump anything that appears in pg_catalog; it assumes all of that
stuff is supplied by the system. So the above is expected behavior.
I haven't looked at plsh, but if it installs stuff directly into
pg_catalog, I'd call that a mistake. At least it shouldn't be the
default behavior.
(Note for pedantry's sake: a language doesn't really belong to any
particular schema; but its support functions do, and pg_dump treats
the language as belonging to the same schema as the support functions.)
regards, tom lane
On Friday 26 November 2004 03:11 pm, Tom Lane wrote: > Adrian Klaver <aklaver@comcast.net> writes: > > Sorry should have been more explicit. The dump loaded functions depending > > on plsh before the function that created the language. In fact it did not > > instalI the language at all. > > Ah. Looking back at your first message, I see you'd installed plsh into > the pg_catalog schema rather than a user schema. pg_dump specifically > doesn't dump anything that appears in pg_catalog; it assumes all of that > stuff is supplied by the system. So the above is expected behavior. > > I haven't looked at plsh, but if it installs stuff directly into > pg_catalog, I'd call that a mistake. At least it shouldn't be the > default behavior. > > (Note for pedantry's sake: a language doesn't really belong to any > particular schema; but its support functions do, and pg_dump treats > the language as belonging to the same schema as the support functions.) > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org Problem solved. The language handler function was in both the pg_catalog and public schema. The pg_catalog version was masking the public version. Removing the pg_catalog version allowed the pg_dump program to sort things out properly and the restore completed with out a problem. Thanks for the insight. -- Adrian Klaver aklaver@comcast.net