Thread: Some questions on user defined types and functions.
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
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
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...
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
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 #
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
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)
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 #
> 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