I was wondering if anyone could help me with the following questions.
They are all related to user defined types and functions.

    1. Environment variables in function pathname.  We would like to
have multiple environments (i.e. production, backup, reporting, test
etc) with each environment totally independent of the other
environments.  One thing that stands in the way of this is the absolute
pathname requirement in the CREATE FUNCTION syntax.  Obtimally we would
like the following syntax to work:

            CREATE FUNCTION myfunc(mytype) RETURNS text AS
'$HOME/lib/libmyso.so' LANGUAGE 'c':

        and have the environment variable $HOME "lazy" evaluated.  I
have looked at the fmgr code and this doesn't look too difficult to add
as long as I could get the $HOME past the parser.  Has anyone thought
about this before?  Is there another, better way to do this?

    2. tid assignment - We make extensive use of user defined types.
One, fairly painful, thing we have noticed is the following sequence
really doesn't work very well:

            CREATE FUNCTION mytype_in(opaque) RETURNS mytype AS
'/lib/libmyso.so' LANGUAGE 'c';
            CREATE FUNCTION mytype_out(opaque) RETURNS opaque AS
'/lib/libmyso.so' LANGUAGE 'c';
            CREATE TYPE mytype (internallength = VARIABLE,
input=mytype_in, output=mytype_out);
            CREATE TABLE mytable (t mytype);
            DROP TYPE mytype;
            then create the type again

        The reason for possibly wanting to do this is to fix a problem
with the implementation of the type mytype.  The reason this doesn't
seem to work is that the definition of mytable "knows" the TypeID of
mytype and, because it got dropped and recreated, the TypeID is now
different.  So the question is, is there a way to modify the definition
of a type without dropping and recreating all of the tables that use the
type.

    3. fid assignment - Basically the same question as above but with
functions instead of types.  If there is an index that uses a function,
it appears that you can't drop and re-create the function without
blowing away the index definition.

Thank you,
Jeff Collins



Wed, 26 Jul 2000 09:03:28 -0400
Jeffery Collins <collins@onyx-technologies.com>
Some questions on user defined types and functions.
Jeffery Collins wrote:

> I was wondering if anyone could help me with the following questions.
> They are all related to user defined types and functions.
>
>     1. Environment variables in function pathname.  We would like to
> have multiple environments (i.e. production, backup, reporting, test
> etc) with each environment totally independent of the other
> environments.  One thing that stands in the way of this is the absolute
> pathname requirement in the CREATE FUNCTION syntax.  Obtimally we would
> like the following syntax to work:
>
>             CREATE FUNCTION myfunc(mytype) RETURNS text AS
> '$HOME/lib/libmyso.so' LANGUAGE 'c':
>
>         and have the environment variable $HOME "lazy" evaluated.  I
> have looked at the fmgr code and this doesn't look too difficult to add
> as long as I could get the $HOME past the parser.  Has anyone thought
> about this before?  Is there another, better way to do this?
>
>     2. tid assignment - We make extensive use of user defined types.
> One, fairly painful, thing we have noticed is the following sequence
> really doesn't work very well:
>
>             CREATE FUNCTION mytype_in(opaque) RETURNS mytype AS
> '/lib/libmyso.so' LANGUAGE 'c';
>             CREATE FUNCTION mytype_out(opaque) RETURNS opaque AS
> '/lib/libmyso.so' LANGUAGE 'c';
>             CREATE TYPE mytype (internallength = VARIABLE,
> input=mytype_in, output=mytype_out);
>             CREATE TABLE mytable (t mytype);
>             DROP TYPE mytype;
>             then create the type again
>
>         The reason for possibly wanting to do this is to fix a problem
> with the implementation of the type mytype.  The reason this doesn't
> seem to work is that the definition of mytable "knows" the TypeID of
> mytype and, because it got dropped and recreated, the TypeID is now
> different.  So the question is, is there a way to modify the definition
> of a type without dropping and recreating all of the tables that use the
> type.
>
>     3. fid assignment - Basically the same question as above but with
> functions instead of types.  If there is an index that uses a function,
> it appears that you can't drop and re-create the function without
> blowing away the index definition.
>

Never mind on questions 2 and 3.  I figured out the answer.  It seems the
way to do this is to update the system tables (pg_attributes, pg_index,
etc.) to refer to the new oids instead of old oids.

I would still appreciate any thoughts on using environment variables in
function paths.

>
> Thank you,
> Jeff Collins


Wed, 26 Jul 2000 09:58:27 -0400
Jeffery Collins <collins@onyx-technologies.com>
Re: Some questions on user defined types and functions.
Tom Lane wrote:

> Jeffery Collins <collins@onyx-technologies.com> writes:
> > like the following syntax to work:
>
> >             CREATE FUNCTION myfunc(mytype) RETURNS text AS
> > '$HOME/lib/libmyso.so' LANGUAGE 'c':
>
> >         and have the environment variable $HOME "lazy" evaluated.  I
> > have looked at the fmgr code and this doesn't look too difficult to add
> > as long as I could get the $HOME past the parser.
>
> The parser doesn't know a thing about that, it's just seeing a string
> literal.  I think hacking in dfmgr.c would be sufficient.  Whether it's
> a good idea is another question --- you realize you'd be dealing with
> postmaster environment variables, right, not those of the connected
> user?  The way we handle this in the distribution is by substituting
> appropriate strings into a script before it's handed to psql; see the
> regression tests directory for examples.
>

Yes, I want the backend's environment to be used, so this is what I want.
It looks like a pretty simple change.  I'm going to give it a shot.  Whether
or not it is something y'all want for the main distribution is, of course,
up to you.

>
> >     3. fid assignment - Basically the same question as above but with
> > functions instead of types.  If there is an index that uses a function,
> > it appears that you can't drop and re-create the function without
> > blowing away the index definition.
>
> I think it would be a really bad idea to allow recycling of type and
> function OIDs for what might be completely incompatible objects.  But
> something that's been on the TODO list for a while is to create an ALTER
> FUNCTION command that would replace the body of an existing function
> without changing the declared signature (parameters and return type).
> That seems relatively safe, and it'd be awfully handy.  Want to have a
> go at it?
>
>                         regards, tom lane
>
> PS: you do realize that revising the function on which an index is
> based probably renders the index useless anyway?  Unless you can
> guarantee that none of the stored values change...

I agree it is a really bad idea, but sometimes bad things happen to good
databases.  I am really just attempting to figure out how to recover if
someone accidently types a DROP TABLE or DROP FUNCTION command or (as
actually did happen) needs to change the function's path.  Assuming the
reCREATEd TABLE and/or FUNCTION matches the old ones, it looks like updating
the oid references in the relevant system tables to point to the new TABLE
and/or FUNCTION oids works just fine.

As far as looking at the ALTER FUNCTION command, I can't promise anything (I
have a lot of work commitments and a vacation coming up), but I will take a
look at it.

Thank you,
Jeff



Wed, 26 Jul 2000 10:37:26 -0400
Jeffery Collins <collins@onyx-technologies.com>
Re: Some questions on user defined types and functions.
Jeffery Collins <collins@onyx-technologies.com> writes:
> like the following syntax to work:

>             CREATE FUNCTION myfunc(mytype) RETURNS text AS
> '$HOME/lib/libmyso.so' LANGUAGE 'c':

>         and have the environment variable $HOME "lazy" evaluated.  I
> have looked at the fmgr code and this doesn't look too difficult to add
> as long as I could get the $HOME past the parser.

The parser doesn't know a thing about that, it's just seeing a string
literal.  I think hacking in dfmgr.c would be sufficient.  Whether it's
a good idea is another question --- you realize you'd be dealing with
postmaster environment variables, right, not those of the connected
user?  The way we handle this in the distribution is by substituting
appropriate strings into a script before it's handed to psql; see the
regression tests directory for examples.

>     3. fid assignment - Basically the same question as above but with
> functions instead of types.  If there is an index that uses a function,
> it appears that you can't drop and re-create the function without
> blowing away the index definition.

I think it would be a really bad idea to allow recycling of type and
function OIDs for what might be completely incompatible objects.  But
something that's been on the TODO list for a while is to create an ALTER
FUNCTION command that would replace the body of an existing function
without changing the declared signature (parameters and return type).
That seems relatively safe, and it'd be awfully handy.  Want to have a
go at it?

			regards, tom lane

PS: you do realize that revising the function on which an index is
based probably renders the index useless anyway?  Unless you can
guarantee that none of the stored values change...

Wed, 26 Jul 2000 11:12:08 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Some questions on user defined types and functions. 
Jeffery Collins wrote:
> I was wondering if anyone could help me with the following questions.
> They are all related to user defined types and functions.
>
>     1. Environment variables in function pathname.  We would like to
> [...]

    Create  your  SQL scripts that define the functions in a make
    step, invoking sed(1) to substitute a  constant  string  with
    the  content  of an environment variable. This worked for the
    past 20 years and I'm sure it's extremely portable.

>     2. tid assignment - We make extensive use of user defined types.
> One, fairly painful, thing we have noticed is the following sequence
> really doesn't work very well:
>
> [...]
>
>         The reason for possibly wanting to do this is to fix a problem
> with the implementation of the type mytype.

    You don't need to DROP and reCREATE the functions and type if
    you  just  fixed  some  bug  in  the  C coding. Recompile it,
    replace the shared object and reconnect to the database.  The
    new  backend  (you  get  at  reconnect)  will load in the new
    shared module and use the new code.

>     3. fid assignment - Basically the same question as above but with
> functions instead of types.  If there is an index that uses a function,
> it appears that you can't drop and re-create the function without
> blowing away the index definition.

    Same as 2.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



Wed, 26 Jul 2000 21:17:15 +0200 (MEST)
JanWieck@t-online.de (Jan Wieck)
Re: Some questions on user defined types and functions.
Jeffery Collins <collins@onyx-technologies.com> writes:
>>>> like the following syntax to work:
>> 
>>>> CREATE FUNCTION myfunc(mytype) RETURNS text AS
>>>> '$HOME/lib/libmyso.so' LANGUAGE 'c':
>> 
>>>> and have the environment variable $HOME "lazy" evaluated.  I
>>>> have looked at the fmgr code and this doesn't look too difficult to add
>>>> as long as I could get the $HOME past the parser.

> I have made the changes necessary to allow environment variables to be
> entered and expanded in file names.  Two files had to be changed
> backend/commands/define.c and backend/utils/fmgr/dfmgr.c.  Assuming you are
> interested in the change,

Well, that's a good question.  Does anyone else have an opinion on
whether this would be a good/bad/indifferent feature?  We've seen
problems in the past caused by depending on postmaster environment
variables (restart the postmaster with different environment than
usual, things mysteriously break).  So I'm inclined to feel that adding
more dependence on them isn't such a hot idea.  But I'm not going to
veto it if there's interest in the feature from other people.

> what is the proper way to build a patch file that
> contains the changes?  I have never done this before.

"diff -c" against current sources, done so that the correct file
pathnames are visible in the diff output; that is, cd to top level
of distribution tree and do something like
diff -c src/backend/utils/fmgr/dfmgr.c.orig src/backend/utils/fmgr/dfmgr.c
Don't forget to include diffs for documentation updates, as well.

			regards, tom lane

Wed, 26 Jul 2000 17:50:43 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Some questions on user defined types and functions. 
It would seem that it wouldn't break anyone's existing setup, since 
you couldn't have an env variable in there anyway. (No one really 
has a directory called $HOME, I hope!)

So, perhaps it could just be something in the documentation that 
has a stern warning about watching your consistency. Caveat 
hacker and all that.

On 26 Jul 2000, at 17:50, Tom Lane wrote:

> Jeffery Collins <collins@onyx-technologies.com> writes:
> >>>> like the following syntax to work:
> >> 
> >>>> CREATE FUNCTION myfunc(mytype) RETURNS text AS
> >>>> '$HOME/lib/libmyso.so' LANGUAGE 'c':
> >> 
> >>>> and have the environment variable $HOME "lazy" evaluated.  I have
> >>>> looked at the fmgr code and this doesn't look too difficult to
> >>>> add as long as I could get the $HOME past the parser.
> 
> > I have made the changes necessary to allow environment variables to
> > be entered and expanded in file names.  Two files had to be changed
> > backend/commands/define.c and backend/utils/fmgr/dfmgr.c.  Assuming
> > you are interested in the change,
> 
> Well, that's a good question.  Does anyone else have an opinion on
> whether this would be a good/bad/indifferent feature?  We've seen
> problems in the past caused by depending on postmaster environment
> variables (restart the postmaster with different environment than
> usual, things mysteriously break).  So I'm inclined to feel that
> adding more dependence on them isn't such a hot idea.  But I'm not
> going to veto it if there's interest in the feature from other people.
> 
> > what is the proper way to build a patch file that
> > contains the changes?  I have never done this before.
> 
> "diff -c" against current sources, done so that the correct file
> pathnames are visible in the diff output; that is, cd to top level of
> distribution tree and do something like diff -c
> src/backend/utils/fmgr/dfmgr.c.orig src/backend/utils/fmgr/dfmgr.c
> Don't forget to include diffs for documentation updates, as well.
> 
>    regards, tom lane


--
Joel Burton, Director of Information Systems -*- jburton@scw.org
Support Center of Washington (www.scw.org)

Wed, 26 Jul 2000 18:46:08 -0400
"Joel Burton" <jburton@scw.org>
Re: Some questions on user defined types and functions. 
Tom Lane wrote:
> Jeffery Collins <collins@onyx-technologies.com> writes:
> > what is the proper way to build a patch file that
> > contains the changes?  I have never done this before.
>
> "diff -c" against current sources, done so that the correct file
> pathnames are visible in the diff output; that is, cd to top level
> of distribution tree and do something like

    For the past years I do a

        cp -R src src.orig

    after the configure step. Whatever I do in the sources, a

        diff -cr src src.orig

    in  the toplevel directory gives me a patch I can apply to my
    CVS checkout. With this I can never forget  a  single  source
    file touched.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



Thu, 27 Jul 2000 00:52:54 +0200 (MEST)
JanWieck@t-online.de (Jan Wieck)
Re: Some questions on user defined types and functions.
> Well, that's a good question.  Does anyone else have an opinion on
> whether this would be a good/bad/indifferent feature?  We've seen
> problems in the past caused by depending on postmaster environment
> variables (restart the postmaster with different environment than
> usual, things mysteriously break).  So I'm inclined to feel that adding
> more dependence on them isn't such a hot idea.  But I'm not going to
> veto it if there's interest in the feature from other people.

As usual, I would like to see *more* support for environment variables
etc. This would fall into that category. You can choose to use it, or
choose to not, but the system has *more* flexibility when all is said
and done.

There is code in the postmaster which does the same thing, nearly. You
might want to check out the implementation there...

                       - Thomas

Thu, 27 Jul 2000 05:52:27 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: Some questions on user defined types and functions.
I moved the discussion from General to Hackers, as I am getting into actual
code changes now.  I hope this is appropriate.

Jeff


Thomas Lockhart wrote:

>
> There is code in the postmaster which does the same thing, nearly. You
> might want to check out the implementation there...
>
>                        - Thomas

I'm not exactly sure which code you are referring to.  I did see the
following places where environment variables in paths are expanded:

backend/utils/adt/filename.c:
    After I looked at this code for a while, I decided I didn't like it
enough to use.  My biggest concern with this code is that it only allows one
environment variable at the beginning of the path.  For example,
$PGHOME/rest/of/path is allowed, but $PGHOME/version_$VER/path is not
allowed.

backend/utils/misc/database.c:
    This code is only applicable to finding the path to the database
directory.  It has a hardwired 'base' in the expanded pathname.  Also only
environment variables in the beginning of the path are supported.

If y'all want the code, here are the diffs.  The are from 7.0.2.  I made,
what I think, are the appropriate changes to the documentation.  I do not
have a way to build the documentation so I can't see how my changes actually
look - I hope they are appropriately formatted.

Jeff
Thu, 27 Jul 2000 09:55:02 -0400
Jeffery Collins <collins@onyx-technologies.com>
Re: [GENERAL] Some questions on user defined types and functions.
> I moved the discussion from General to Hackers, as I am getting into actual
> code changes now.  I hope this is appropriate.

Yup.

> I'm not exactly sure which code you are referring to.  I did see the
> following places where environment variables in paths are expanded:
...
> backend/utils/misc/database.c:
>     This code is only applicable to finding the path to the database
> directory.  It has a hardwired 'base' in the expanded pathname.  Also only
> environment variables in the beginning of the path are supported.

This is the one I was thinking of. The "leading envar" is pretty
unambiguous; allowing them farther into the string will restrict paths
from having a dollar sign (not terribly important, but it is an obscure
restriction). Also, and probably more important, by requiring that the
envar be in the first position it is a simple one-byte comparison to see
if any expansion *may* need to be done. So the performance is not
affected at all if no environment variable is used.

> If y'all want the code, here are the diffs.  The are from 7.0.2.  I made,
> what I think, are the appropriate changes to the documentation.  I do not
> have a way to build the documentation so I can't see how my changes actually
> look - I hope they are appropriately formatted.

Thanks. Shall we tweak it to support the same conventions as for the
other cases (leading envar only)? It will remove any possible objection
regarding efficiency, and it will conform to the other usages (btw, the
"hardwired 'base/' in the database.c example could/should be considered
a security feature since it requires a well-formed directory structure).

                         - Thomas

Fri, 28 Jul 2000 02:33:01 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: Re: [GENERAL] Some questions on user defined types and functions.
Thomas Lockhart wrote:

> > I'm not exactly sure which code you are referring to.  I did see the
> > following places where environment variables in paths are expanded:
> ...
> > backend/utils/misc/database.c:
> >     This code is only applicable to finding the path to the database
> > directory.  It has a hardwired 'base' in the expanded pathname.  Also only
> > environment variables in the beginning of the path are supported.
>
> This is the one I was thinking of. The "leading envar" is pretty
> unambiguous; allowing them farther into the string will restrict paths
> from having a dollar sign (not terribly important, but it is an obscure
> restriction). Also, and probably more important, by requiring that the
> envar be in the first position it is a simple one-byte comparison to see
> if any expansion *may* need to be done. So the performance is not
> affected at all if no environment variable is used.
>
> > If y'all want the code, here are the diffs.  The are from 7.0.2.  I made,
> > what I think, are the appropriate changes to the documentation.  I do not
> > have a way to build the documentation so I can't see how my changes actually
> > look - I hope they are appropriately formatted.
>
> Thanks. Shall we tweak it to support the same conventions as for the
> other cases (leading envar only)? It will remove any possible objection
> regarding efficiency, and it will conform to the other usages (btw, the
> "hardwired 'base/' in the database.c example could/should be considered
> a security feature since it requires a well-formed directory structure).
>
>                          - Thomas

Feel free to change so that the envar must be leading.  It is definitely easier
code with this restriction and definately faster.

If you want, I will make the change.  If the restriction is desired, I can make
the code in database.c and my code common.

Jeff



Fri, 28 Jul 2000 00:30:50 -0400
Jeffery Collins <collins@onyx-technologies.com>
Re: Re: [GENERAL] Some questions on user defined types and functions.
> If you want, I will make the change.  If the restriction is desired, I can 
> make the code in database.c and my code common.

Ooh. That sounds like a great offer. You are welcome to do the work :)

So, we will end up with a routine which will take a string, check for a
leading environment variable, and expand the envar if necessary? And we
will use that in two places?

istm that the above will require some memory allocation at times. I
haven't checked to see if the memory context or states for both cases
are compatible. If they aren't, then feel free to bring along two sets
of code.

Regards.

                     -Thomas

Fri, 28 Jul 2000 14:41:11 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: Re: [GENERAL] Some questions on user defined types and functions.