plpgsql error

Edit: /usr/src/pgsql/postgresql-6.4.2/src/pl/plpgsql/src/mklang.sql

Change: as '${exec_prefix}/lib/plpgsql.so'
to: as '/usr/local/pgsql/lib/plpgsql.so'

Then: psql your_db < mklang.sql

This should really be part of the documentation as I wasted two days on
this same problem a few weeks back.

Have fun

Andy

On Mon, 10 May 1999, Adam H. Pendleton wrote:

Yes, this clearly looks broken. In mklang.sql.in, @libdir@ is replaced
with ${exec_prefix} in mklang.sql. Seems it should be something else.

Edit: /usr/src/pgsql/postgresql-6.4.2/src/pl/plpgsql/src/mklang.sql

Change: as '${exec_prefix}/lib/plpgsql.so'
to: as '/usr/local/pgsql/lib/plpgsql.so'

Then: psql your_db < mklang.sql

This should really be part of the documentation as I wasted two days on
this same problem a few weeks back.

Have fun

Andy

On Mon, 10 May 1999, Adam H. Pendleton wrote:

I get this error when I try to create a function using plpgsql:

ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'.
Recognized languages are sql, C, internal and the created procedural
languages.

Do I need to specify another flag when I compile Postgresql?

Adam
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Si hoc legere scis nimium eruditionis habes.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <maillist@candle.pha.pa.us> writes:

Yes, this clearly looks broken. In mklang.sql.in, @libdir@ is replaced
with ${exec_prefix} in mklang.sql. Seems it should be something else.

Oh ... OK, that looks like a garden-variety configure bug (one too many
levels of quoting, or some such). I can look at this if no one else
beats me to it.

regards, tom lane

Bruce Momjian <maillist@candle.pha.pa.us> writes:

Yes, this clearly looks broken. In mklang.sql.in, @libdir@ is replaced
with ${exec_prefix} in mklang.sql. Seems it should be something else.

Oh ... OK, that looks like a garden-variety configure bug (one too many
levels of quoting, or some such). I can look at this if no one else
beats me to it.

It replaces @libdir@ with ${exec_prefix}/lib. It appears the
configure code expects the replacement to occour in a Makefile, so
${exec_prefix} can be replaced in Makefile.global. However,
$exec_prefix is not in Makefile.global, so maybe it is just a problem
with configure that $exec_prefix is replace before @libdir@, and
libdir's that contain exec_prefix have a problem.

However, it appears the default value of libdir contains exec_prefix, so
you would think they would have found such a problem themselves in
testing.

I am confused.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Edit: /usr/src/pgsql/postgresql-6.4.2/src/pl/plpgsql/src/mklang.sql

Change: as '${exec_prefix}/lib/plpgsql.so'
to: as '/usr/local/pgsql/lib/plpgsql.so'

Then: psql your_db < mklang.sql

This should really be part of the documentation as I wasted two days on
this same problem a few weeks back.

And this became IMHO an FAQ. Should we avoid it by installing
PL/pgSQL and PL/Tcl (if built) by default in the template1
database during intidb? Installing it in template1 would
have the effect that it will be available after every
createdb.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #

Bruce Momjian <maillist@candle.pha.pa.us> writes:

Oh ... OK, that looks like a garden-variety configure bug (one too many
levels of quoting, or some such). I can look at this if no one else
beats me to it.

It replaces @libdir@ with ${exec_prefix}/lib. It appears the
configure code expects the replacement to occour in a Makefile, so
${exec_prefix} can be replaced in Makefile.global. However,
$exec_prefix is not in Makefile.global, so maybe it is just a problem
with configure that $exec_prefix is replace before @libdir@, and
libdir's that contain exec_prefix have a problem.

configure is designed to generate makefiles that look like this:

exec_prefix=/usr/local
bindir=${exec_prefix}/bin
libdir=${exec_prefix}/lib

with the notion that this will simplify after-the-fact hand tweaking
of install destinations in the makefile if you feed a need to do that.
So that's why libdir's default definition looks the way it does.

Now, that works OK in makefiles and in shell scripts, where the
reference to the exec_prefix variable can get expanded when the file
is read. But it falls down for mklang.sql, where the value of libdir
is substituted into an SQL command --- Postgres ain't gonna expand the
variable reference.

What we need is to substitute a "fully expanded" version of libdir into
this file, instead of a version that might depend on other variables.

Any shell-scripting gurus on the list? I thought this would be an easy
fix, but I'm having some difficulty getting the configure script to
produce a fully-expanded value for libdir. Given a shell variable that
may contain $-references to other variables, the requirement is to
assign to a new variable an expanded value containing no $-references.
I tried
expanded_libdir="$libdir"
but that just gets you an exact copy, no recursive expansion. A few
other ideas didn't work either; the Bourne shell doesn't seem to want
to re-expand text it's already expanded. Suggestions?

regards, tom lane

Edit: /usr/src/pgsql/postgresql-6.4.2/src/pl/plpgsql/src/mklang.sql

Change: as '${exec_prefix}/lib/plpgsql.so'
to: as '/usr/local/pgsql/lib/plpgsql.so'

Then: psql your_db < mklang.sql

This should really be part of the documentation as I wasted two days on
this same problem a few weeks back.

And this became IMHO an FAQ. Should we avoid it by installing
PL/pgSQL and PL/Tcl (if built) by default in the template1
database during intidb? Installing it in template1 would
have the effect that it will be available after every
createdb.

Sure, why not?

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

What we need is to substitute a "fully expanded" version of libdir into
this file, instead of a version that might depend on other variables.

Any shell-scripting gurus on the list? I thought this would be an easy
fix, but I'm having some difficulty getting the configure script to
produce a fully-expanded value for libdir. Given a shell variable that
may contain $-references to other variables, the requirement is to
assign to a new variable an expanded value containing no $-references.
I tried

expanded_libdir="$libdir"
but that just gets you an exact copy, no recursive expansion. A few
other ideas didn't work either; the Bourne shell doesn't seem to want
to re-expand text it's already expanded. Suggestions?

Please try:

expanded_libdir="`eval echo $libdir`"

Then I assume you have to do a:

sed "s/@libdir@/$expanded_libdir/g" <mklang.sql.template >mklang.sql

I can take it if you commit what you have. The one item I am not sure
about is having it generate mklang.sql when the configure values change.
When they run configure, I think we have to generate a new file, so the
Makefile can see the change in datestamp and generate a new mklang.sql.
Sounds like we need mklang.template.in, mklang.template, and mklang.sql
and a rule in the makefile that mklang.sql depends on mklang.template.

You can complete it, or I will take a crack at it.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tom Lane wrote:

Any shell-scripting gurus on the list? I thought this would be an easy
fix, but I'm having some difficulty getting the configure script to
produce a fully-expanded value for libdir. Given a shell variable that
may contain $-references to other variables, the requirement is to
assign to a new variable an expanded value containing no $-references.
I tried
expanded_libdir="$libdir"
but that just gets you an exact copy, no recursive expansion. A few
other ideas didn't work either; the Bourne shell doesn't seem to want
to re-expand text it's already expanded. Suggestions?

Use eval:

$ v1=DF_\$EIFFEL_GTK
$ echo $v1
DF_$EIFFEL_GTK
$ v2=$v1
$ echo $v2
DF_$EIFFEL_GTK
$ eval v2=$v1
$ echo $v2
DF_/usr/lib/eiffel-gtk
$

but if it gets too complicated, you might have to change to Perl

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP key from public servers; key ID 32B8FAA1
========================================
"Search me, O God, and know my heart; try me, and know
my thoughts. And see if there be any wicked way in me,
and lead me in the way everlasting."
Psalms 139:23,24

"Oliver Elphick" <olly@lfix.co.uk> writes:

Use eval:
$ eval v2=$v1
$ echo $v2
DF_/usr/lib/eiffel-gtk

Looks good. Thanks for the clue.

but if it gets too complicated, you might have to change to Perl

If configure depended on Perl, you couldn't build Postgres at all
without having a working Perl installation... somehow I doubt that
would go over well...

regards, tom lane

Any shell-scripting gurus on the list? I thought this would be an easy
fix, but I'm having some difficulty getting the configure script to
produce a fully-expanded value for libdir. Given a shell variable that
may contain $-references to other variables, the requirement is to
assign to a new variable an expanded value containing no $-references.
I tried
expanded_libdir="$libdir"
but that just gets you an exact copy, no recursive expansion. A few
other ideas didn't work either; the Bourne shell doesn't seem to want
to re-expand text it's already expanded. Suggestions?

Isn't the correct solution to have the Makefile contain a rule that
creates the file from a template (e.g., with sed -e
's/@xxx@/${xxx}/g')? That way make resolves the variable references
and you needn't worry about it. You can have the rule depend on
something like Makefile or Makefile.global or wherever the relevant
variables are set so that if local tweaks are made the files get
remade automatically.

Cheers,
Brook

Any shell-scripting gurus on the list? I thought this would be an easy
fix, but I'm having some difficulty getting the configure script to
produce a fully-expanded value for libdir. Given a shell variable that
may contain $-references to other variables, the requirement is to
assign to a new variable an expanded value containing no $-references.
I tried
expanded_libdir="$libdir"
but that just gets you an exact copy, no recursive expansion. A few
other ideas didn't work either; the Bourne shell doesn't seem to want
to re-expand text it's already expanded. Suggestions?

Isn't the correct solution to have the Makefile contain a rule that
creates the file from a template (e.g., with sed -e
's/@xxx@/${xxx}/g')? That way make resolves the variable references
and you needn't worry about it. You can have the rule depend on
something like Makefile or Makefile.global or wherever the relevant
variables are set so that if local tweaks are made the files get
remade automatically.

Yes, that is what we were saying.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Isn't the correct solution to have the Makefile contain a rule that

Yes, that is what we were saying.

The problem is simply a matter of failing to expand indirect references
in that substitution process. I just committed a fix.

regards, tom lane

Brook Milligan <brook@trillium.NMSU.Edu> writes:

Isn't the correct solution to have the Makefile contain a rule that
creates the file from a template (e.g., with sed -e
's/@xxx@/${xxx}/g')? That way make resolves the variable references
and you needn't worry about it.

(after further thought...) Oh, right, I see what you're saying: don't
generate mklang.sql in configure at all, but let pl/plpgsql/src/Makefile
be responsible for it. Yeah, that'd be a cleaner solution. However,
what I just committed works ;-). If you feel like improving it, be
my guest; I have other items on the to-do list...

regards, tom lane

Bruce Momjian <maillist@candle.pha.pa.us> writes:

Oh ... OK, that looks like a garden-variety configure bug (one too many
levels of quoting, or some such). I can look at this if no one else
beats me to it.

It replaces @libdir@ with ${exec_prefix}/lib. It appears the
configure code expects the replacement to occour in a Makefile, so
${exec_prefix} can be replaced in Makefile.global. However,
$exec_prefix is not in Makefile.global, so maybe it is just a problem
with configure that $exec_prefix is replace before @libdir@, and
libdir's that contain exec_prefix have a problem.

configure is designed to generate makefiles that look like this:

exec_prefix=/usr/local
bindir=${exec_prefix}/bin
libdir=${exec_prefix}/lib

with the notion that this will simplify after-the-fact hand tweaking
of install destinations in the makefile if you feed a need to do that.
So that's why libdir's default definition looks the way it does.

Tom, I like your fix in configure.in better than adding a silly
Makefile���rule. Yours is much cleaner. You just created an
expanded_libdir in configure.in and let that be expanded in the *.in
files. Great.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Brook Milligan <brook@trillium.NMSU.Edu> writes:

Isn't the correct solution to have the Makefile contain a rule that
creates the file from a template (e.g., with sed -e
's/@xxx@/${xxx}/g')? That way make resolves the variable references
and you needn't worry about it.

(after further thought...) Oh, right, I see what you're saying: don't
generate mklang.sql in configure at all, but let pl/plpgsql/src/Makefile
be responsible for it. Yeah, that'd be a cleaner solution. However,
what I just committed works ;-). If you feel like improving it, be
my guest; I have other items on the to-do list...

I've just committed a little change to initdb and it's
Makefile. The initdb Makefile now expands __DLSUFFIX__ into
it and initdb uses $PGLIB/plpgsql__DLSUFFIX__ to test if it
is there and then runs the appropriate queries against
template1. Same for PL/Tcl.

If anyone agrees we can get rid of these mklang.sql scripts
totally.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #

Brook Milligan <brook@trillium.NMSU.Edu> writes:

Isn't the correct solution to have the Makefile contain a rule that
creates the file from a template (e.g., with sed -e
's/@xxx@/${xxx}/g')? That way make resolves the variable references
and you needn't worry about it.

(after further thought...) Oh, right, I see what you're saying: don't
generate mklang.sql in configure at all, but let pl/plpgsql/src/Makefile
be responsible for it. Yeah, that'd be a cleaner solution. However,
what I just committed works ;-). If you feel like improving it, be
my guest; I have other items on the to-do list...

I've just committed a little change to initdb and it's
Makefile. The initdb Makefile now expands __DLSUFFIX__ into
it and initdb uses $PGLIB/plpgsql__DLSUFFIX__ to test if it
is there and then runs the appropriate queries against
template1. Same for PL/Tcl.

If anyone agrees we can get rid of these mklang.sql scripts
totally.

Sure.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tom, I like your fix in configure.in better than adding a silly
Makefile�rule. Yours is much cleaner. You just created an
expanded_libdir in configure.in and let that be expanded in the *.in
files. Great.

The problem with solutions like this is that we end up proliferating
expanded and unexpanded versions of the same variables; hence,
maintenance problems with coordinating the various uses of this
information.

We've already had this discussion with some of the other cases (the
perl or tcl interfaces come to mind but I'm not sure). It would be
unfortunate to make configure that much more complex when we don't
really need to at all. It seems we should be using Makefile rules to
do what they are best at: automatically generating files based on
known rules that depend on a specific database of local configuration
options; configure's role should simply be to create that database.

Cheers,
Brook

Bruce Momjian <maillist@candle.pha.pa.us> writes:

Tom, I like your fix in configure.in better than adding a silly
Makefile rule. Yours is much cleaner. You just created an
expanded_libdir in configure.in and let that be expanded in the *.in
files.

Not really --- did you see what I had to do to get the thing expanded
properly? Ick... Brook's approach would be cleaner. But I don't want
to spend more time on it now.

regards, tom lane

jwieck@debis.com (Jan Wieck) writes:

I've just committed a little change to initdb and it's
Makefile. The initdb Makefile now expands __DLSUFFIX__ into
it and initdb uses $PGLIB/plpgsql__DLSUFFIX__ to test if it
is there and then runs the appropriate queries against
template1. Same for PL/Tcl.
If anyone agrees we can get rid of these mklang.sql scripts
totally.

Well, I hate to be a party-pooper, but I don't agree: I like having
the flexibility of installing plpgsql into just selected databases.
If it's automatically included into template1 then I no longer have
any choice in the matter.

Perhaps this could be driven by a configuration switch?

regards, tom lane

Bruce Momjian <maillist@candle.pha.pa.us> writes:

Tom, I like your fix in configure.in better than adding a silly
Makefile rule. Yours is much cleaner. You just created an
expanded_libdir in configure.in and let that be expanded in the *.in
files.

Not really --- did you see what I had to do to get the thing expanded
properly? Ick... Brook's approach would be cleaner. But I don't want
to spend more time on it now.

And it's not required for PL/pgSQL or PL/Tcl any more. initdb
now installs them in template1 (if their shared objects are
installed in the libdir), so we can remove the mklang.sql
scripts. So concentrate on your other items.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #

jwieck@debis.com (Jan Wieck) writes:

I've just committed a little change to initdb and it's
Makefile. The initdb Makefile now expands __DLSUFFIX__ into
it and initdb uses $PGLIB/plpgsql__DLSUFFIX__ to test if it
is there and then runs the appropriate queries against
template1. Same for PL/Tcl.
If anyone agrees we can get rid of these mklang.sql scripts
totally.

Well, I hate to be a party-pooper, but I don't agree: I like having
the flexibility of installing plpgsql into just selected databases.
If it's automatically included into template1 then I no longer have
any choice in the matter.

Perhaps this could be driven by a configuration switch?

Or maybe some switch to initdb and createdb?

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #

jwieck@debis.com (Jan Wieck) writes:

Perhaps this could be driven by a configuration switch?

Or maybe some switch to initdb and createdb?

That's a good idea; it would save having to propagate the value out of
configure and into the places where it'd be needed.

regards, tom lane

jwieck@debis.com (Jan Wieck) writes:

Perhaps this could be driven by a configuration switch?

Or maybe some switch to initdb and createdb?

That's a good idea; it would save having to propagate the value out of
configure and into the places where it'd be needed.

I've thought a little more about that one and I don't like it
anymore. It's a bad idea that enabling a language in such a
looser-friendly way is only possible during db creation, not
for existing databases.

I'll remove that from initdb again and instead add two new
utilities "installpl" and "removepl". That way it's also
possible to automate the process in scripts but it is as
flexible as can.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #