Thread

1. Some questions on user defined types and functions.

   Jeffery Collins <collins@onyx-technologies.com> — 2000-07-26T13:03:28Z

   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
   
   
   
   

2. Re: Some questions on user defined types and functions.

   Jeffery Collins <collins@onyx-technologies.com> — 2000-07-26T13:58:27Z

   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
   
   
   

3. Re: Some questions on user defined types and functions.

   Jeffery Collins <collins@onyx-technologies.com> — 2000-07-26T14:37:26Z

   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
   
   
   
   

4. Re: Some questions on user defined types and functions.

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-07-26T15:12:08Z

   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...
   
   

5. Re: Some questions on user defined types and functions.

   Jan Wieck <janwieck@t-online.de> — 2000-07-26T19:17:15Z

   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 #
   
   
   
   

6. Re: Some questions on user defined types and functions.

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-07-26T21:50:43Z

   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
   
   

7. Re: Some questions on user defined types and functions.

   Joel Burton <jburton@scw.org> — 2000-07-26T22:46:08Z

   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)
   
   

8. Re: Some questions on user defined types and functions.

   Jan Wieck <janwieck@t-online.de> — 2000-07-26T22:52:54Z

   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 #
   
   
   
   

9. Re: Some questions on user defined types and functions.

   Thomas Lockhart <lockhart@alumni.caltech.edu> — 2000-07-27T05:52:27Z

   > 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
   
   

10. Re: [GENERAL] Some questions on user defined types and functions.

    Jeffery Collins <collins@onyx-technologies.com> — 2000-07-27T13:55:02Z

    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
    
    

11. Re: Re: [GENERAL] Some questions on user defined types and functions.

    Thomas Lockhart <lockhart@alumni.caltech.edu> — 2000-07-28T02:33:01Z

    > 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
    
    

12. Re: Re: [GENERAL] Some questions on user defined types and functions.

    Jeffery Collins <collins@onyx-technologies.com> — 2000-07-28T04:30:50Z

    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
    
    
    
    

13. Re: Re: [GENERAL] Some questions on user defined types and functions.

    Thomas Lockhart <lockhart@alumni.caltech.edu> — 2000-07-28T14:41:11Z

    > 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