Thread: SQL99, CREATE CAST, and initdb
I've got patches for the CREATE CAST/DROP CAST feature (just a
rearrangement of our existing function declaration syntax). The SQL99
form assumes that an existing function will be used for the cast
definition, so I've extended the syntax to allow that and to have an
alternate form which has more of our CREATE FUNCTION functionality.
I'm also looking at the SQL99 INFORMATION_SCHEMA views. Is anyone
already defining these? Is someone interested in picking this up? I've
got some definitions in a contrib-style directory but have not yet
mapped them to PostgreSQL.
The initdb folks may want to start thinking about the best way to
support a larger number of views; currently they are embedded directly
into the initdb script but that would get unwieldy with more of them
(and some of them are *really* fat definitions!).
- Thomas
Thomas Lockhart writes: > I've got patches for the CREATE CAST/DROP CAST feature (just a > rearrangement of our existing function declaration syntax). The SQL99 > form assumes that an existing function will be used for the cast > definition, so I've extended the syntax to allow that and to have an > alternate form which has more of our CREATE FUNCTION functionality. Could you provide more precise details? I've thought of this before, when the new "may be a cast function" feature was added, and this feature didn't match very well. > I'm also looking at the SQL99 INFORMATION_SCHEMA views. Is anyone > already defining these? Yes. I'm done through section 20.18 (COLUMNS view). > The initdb folks may want to start thinking about the best way to > support a larger number of views; currently they are embedded directly > into the initdb script but that would get unwieldy with more of them > (and some of them are *really* fat definitions!). I think they can be loaded from an external file. -- Peter Eisentraut peter_e@gmx.net
> I'm also looking at the SQL99 INFORMATION_SCHEMA views. Is anyone > already defining these? Is someone interested in picking this up? I've > got some definitions in a contrib-style directory but have not yet > mapped them to PostgreSQL. I have a few of the basics done, but nothing really significant. Once I get domains fairly fixed (don't think I'll get check constraints done), I was going to attempt to finish off the small group (triggeres, views, schemata, domains, etc.) for early July. If your interested, send me a note and I'll forward the view definitions and the patch for functions -- against fairly old source (early 7.3).
> > I've got patches for the CREATE CAST/DROP CAST feature (just a
> > rearrangement of our existing function declaration syntax). The SQL99
> > form assumes that an existing function will be used for the cast
> > definition, so I've extended the syntax to allow that and to have an
> > alternate form which has more of our CREATE FUNCTION functionality.
> Could you provide more precise details? I've thought of this before, when
> the new "may be a cast function" feature was added, and this feature
> didn't match very well.
It doesn't match perfectly in that one field is ignored as being
(afaict) redundant for us. The basic definition from SQL99 is
CREATE CAST(from AS to) WITH FUNCTION func(args) [AS ASSIGNMENT]
I can map this to something equivalent to
CREATE FUNCTION to(from) RETURNS to AS 'select func($1)' LANGUAGE 'sql';
with another clause or two to get the implicit coersion enabled, and
ignoring the "args" field(s).
This supposes that a coersion function of some other name already
exists, and if I define one it seems to work nicely. I defined two
alternate forms, one resembling the SQL99 clauses and one resembling the
existing PostgreSQL CREATE FUNCTION clauses, as follows:
CREATE CAST(from AS to) WITH FUNCTION func(args) AS 'path' WITH ...
and
CREATE CAST(from AS to) AS 'path' WITH ...
and both of these latter forms allow one to eliminate a corresponding
CREATE FUNCTION.
> > I'm also looking at the SQL99 INFORMATION_SCHEMA views. Is anyone
> > already defining these?
> Yes. I'm done through section 20.18 (COLUMNS view).
Great. I'll stop looking at it then.
> > The initdb folks may want to start thinking about the best way to
> > support a larger number of views; currently they are embedded directly
> > into the initdb script but that would get unwieldy with more of them
> > (and some of them are *really* fat definitions!).
> I think they can be loaded from an external file.
Sounds good.
- Thomas
I've gone ahead and committed patches for CREATE CAST/DROP CAST, as well
as for a few other SQL99 clauses in other statements. Details below...
- Thomas
Implement SQL99 CREATE CAST and DROP CAST statements.Also implement alternative forms to expose the PostgreSQL CREATE
FUNCTIONfeatures.
Implement syntax for READ ONLY and READ WRITE clauses in SET
TRANSACTION.READ WRITE is already implemented (of course).
Implement syntax for "LIKE table" clause in CREATE TABLE. Should be
fairlyeasy to complete since it resembles SELECT INTO.
Implement MATCH SIMPLE clause for foreign key definitions. This is
explicitSQL99 syntax for the default behavior, so we now support it :)
Start implementation of shorthand for national character literals inscanner. For now, just swallow the leading "N", but
sometimesoon let'sfigure out how to pass leading type info from the scanner to the
parser.We should use the same technique for binary and hex bit string
literals,though it might be unusual to have two apparently independent literaltypes fold into the same storage type.
> > It doesn't match perfectly in that one field is ignored as being
> > (afaict) redundant for us. The basic definition from SQL99 is
> > CREATE CAST(from AS to) WITH FUNCTION func(args) [AS ASSIGNMENT]
> > I can map this to something equivalent to
> > CREATE FUNCTION to(from) RETURNS to AS 'select func($1)' LANGUAGE 'sql';
> > with another clause or two to get the implicit coersion enabled, and
> > ignoring the "args" field(s).
> I think this is wrong. When you call CREATE CAST ... WITH FUNCTION
> func(args) then func(args) must already exist.
Right. And that is what is required for SQL99 also afaict. There are not
enough clauses in the SQL99 syntax to allow anything else!
> So the closest you could
> map it to would be
> ALTER FUNCTION to(from) IMPLICIT CAST
That would require that the function to be used as the cast have the
same name as the underlying PostgreSQL conventions for casting
functions. The implementation I've done does not require this; it
basically defines a new SQL function with a body of
select func($1)
where "func" is the name specified in the "WITH FUNCTION func(args)"
clause. It does hang together in the way SQL99 intends and in a way
which is consistant with PostgreSQL's view of the world.
But, I've also implemented alternate forms which would allow one not
define a separate function beforehand. So the nice PostgreSQL feature of
allowing function names to be different than the entry points can be
used.
> iff the name of the function and the target data type agree. (Of course
> this command doesn't exit, but you get the idea.) The SQL99 feature is
> more general than ours, but in order to use if effectively we would need
> to maintain another index on pg_proc. Tom Lane once opined that that
> would be too costly.
I don't follow you here, but the implementation I have is consistant
with SQL99 (or at least with the way I'm interpreting it :)
- Thomas
Rod Taylor writes: > > I'm also looking at the SQL99 INFORMATION_SCHEMA views. Is anyone > > already defining these? Is someone interested in picking this up? > I've > > got some definitions in a contrib-style directory but have not yet > > mapped them to PostgreSQL. > > I have a few of the basics done, but nothing really significant. I guess I'll polish what I have and will commit it so that the group can fill in the rest at convenience. -- Peter Eisentraut peter_e@gmx.net
Thomas Lockhart writes: > It doesn't match perfectly in that one field is ignored as being > (afaict) redundant for us. The basic definition from SQL99 is > > CREATE CAST(from AS to) WITH FUNCTION func(args) [AS ASSIGNMENT] > > I can map this to something equivalent to > > CREATE FUNCTION to(from) RETURNS to AS 'select func($1)' LANGUAGE 'sql'; > > with another clause or two to get the implicit coersion enabled, and > ignoring the "args" field(s). I think this is wrong. When you call CREATE CAST ... WITH FUNCTION func(args) then func(args) must already exist. So the closest you could map it to would be ALTER FUNCTION to(from) IMPLICIT CAST iff the name of the function and the target data type agree. (Of course this command doesn't exit, but you get the idea.) The SQL99 feature is more general than ours, but in order to use if effectively we would need to maintain another index on pg_proc. Tom Lane once opined that that would be too costly. -- Peter Eisentraut peter_e@gmx.net
Thomas Lockhart <lockhart@fourpalms.org> writes:
>> So the closest you could
>> map it to would be
>> ALTER FUNCTION to(from) IMPLICIT CAST
> That would require that the function to be used as the cast have the
> same name as the underlying PostgreSQL conventions for casting
> functions. The implementation I've done does not require this; it
> basically defines a new SQL function with a body of
> select func($1)
> where "func" is the name specified in the "WITH FUNCTION func(args)"
> clause. It does hang together in the way SQL99 intends and in a way
> which is consistant with PostgreSQL's view of the world.
Urk. Do you realize how expensive SQL functions are for such uses?
(I have had a to-do item for awhile to teach the planner to inline
trivial SQL functions, but it seems unlikely to happen for another
release or three.)
I see no real reason why we should not require casting functions to
follow the Postgres naming convention --- after all, what else would
you name a casting function?
So I'm with Peter on this one: make the SQL99 syntax a mere wrapper
for setting the IMPLICIT CAST bit on an existing function. Otherwise,
people will avoid it as soon as they discover what it's costing them.
regards, tom lane
> I see no real reason why we should not require casting functions to
> follow the Postgres naming convention --- after all, what else would
> you name a casting function?
We do require casting functions to follow the Postgres naming
convention. istm to be a waste of time to have the CREATE CAST() feature
*only* set a bit on an existing function, especially given the SQL99
syntax which implies that it can define a cast operation for an
arbitrarily named function. It also supposes that the only allowed casts
are *implicit casts* (see below for a new issue) which is not quite
right. I've defined alternate forms which draw on the general PostgreSQL
feature set and capabilities, but if we can fit the SQL99 model then we
should go ahead and do that too.
I've got another issue with casting which I've run into while testing
this feature; afaict invoking an explicit CAST() in SQL does not
guarantee that the function of the expected name would be called, if
that function does not have the implicit flag set. Seems that it should
be willing to do the conversion even if the function is not marked as
allowing implicit casts; after all, this is an *explicit* cast!
I'm pretty sure that this is the behavior I've been seeing, but will
publish a test case to confirm it when I have a chance.
- Thomas
I said: > Thomas Lockhart <lockhart@fourpalms.org> writes: >> I've got another issue with casting which I've run into while testing >> this feature; afaict invoking an explicit CAST() in SQL does not >> guarantee that the function of the expected name would be called, if >> that function does not have the implicit flag set. > [ scratches head ] Whether the flag is set or not shouldn't matter; > if the cast function is needed it will be called. Were you perhaps > testing binary-compatible cases? Another possibility is that you got burnt by some schema-related issue; cf the updated conversion docs at http://developer.postgresql.org/docs/postgres/typeconv-func.html IIRC, a function is only considered to be a cast function if it matches by name *and schema* with the target type. So if you, for example, make a function public.int4(something), it'll never be considered a cast function for pg_catalog.int4. I had some doubts about that rule when I put it in, but so far have not thought of an alternative I like better. regards, tom lane
Thomas Lockhart <lockhart@fourpalms.org> writes: > I've got another issue with casting which I've run into while testing > this feature; afaict invoking an explicit CAST() in SQL does not > guarantee that the function of the expected name would be called, if > that function does not have the implicit flag set. [ scratches head ] Whether the flag is set or not shouldn't matter; if the cast function is needed it will be called. Were you perhaps testing binary-compatible cases? Note the order of cases specified in http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/typeconv-func.html I recall we changed what is now case 2 to be higher priority than it used to be; I do not recall the examples that motivated that change, but I'm pretty sure moving it down in the priority list would be bad. regards, tom lane
> Another possibility is that you got burnt by some schema-related issue; > cf the updated conversion docs at > http://developer.postgresql.org/docs/postgres/typeconv-func.html I'll bet that is it, though possible differences in CAST() behaviors are not explained. I'll see if I can reproduce them... > IIRC, a function is only considered to be a cast function if it matches > by name *and schema* with the target type. So if you, for example, > make a function public.int4(something), it'll never be considered a > cast function for pg_catalog.int4. I had some doubts about that rule > when I put it in, but so far have not thought of an alternative I like > better. Well, istm that we should choose something different. The example I was using might be a good use case for a situation we should handle: I implemented a function to convert Unix system time to PG timestamp, and wanted it to respond to an explicit cast but *not* an implicit cast. I got it to work at some point (not sure how, given your description of the schema, uh, scheme) but istm that we definitely do not want to *require* modifications to pg_catalog for any and every change in feature or behavior for built-in types. The schema settings are important, and should have some influence over behavior; that is, if someone extends PG in one schema then if that schema is in the chain it should be able to influence the session, and if it is not then it should only be able to influence the session if there are side-effects from previous definitions. btw, how *do* I control the default schema? Is it always the schema at the front of the search list, or are there other more direct knobs to help determine this other than explicitly qualifying names in queries? - Thomas
Thomas Lockhart <lockhart@fourpalms.org> writes:
>> IIRC, a function is only considered to be a cast function if it matches
>> by name *and schema* with the target type. So if you, for example,
>> make a function public.int4(something), it'll never be considered a
>> cast function for pg_catalog.int4. I had some doubts about that rule
>> when I put it in, but so far have not thought of an alternative I like
>> better.
> Well, istm that we should choose something different.
Well, let's see an alternate proposal.
> I got it to work at some point (not sure how, given your description of
> the schema, uh, scheme) but istm that we definitely do not want to
> *require* modifications to pg_catalog for any and every change in
> feature or behavior for built-in types.
If we just look for "anything named int4() in the current search path"
then I think we will have some unpleasantnesses of a different sort,
namely unexpected conflicts between similarly-named types in different
schemas.
> btw, how *do* I control the default schema? Is it always the schema at
> the front of the search list,
If you mean the default schema for creating things, yes, it's whatever
is at the front of the search list. Should it be different?
regards, tom lane
> Well, let's see an alternate proposal.
OK. Proposal:
Schemas should be able to contain extensions to any and all features
available in the database. Features are resolved by searching the path,
choosing the first exact-match in the path. Under-specified cases should
be resolved from within the entire set of schemas, using the match
closest to the front of the schema list as a tie-breaker.
> If we just look for "anything named int4() in the current search path"
> then I think we will have some unpleasantnesses of a different sort,
> namely unexpected conflicts between similarly-named types in different
> schemas.
Yup. Schemas are a larger gun and can be loaded with larger bullets, and
feet should beware. We should get the expected behavior if they are not
being explicitly used, and we should get the most powerful behavior if
they are imho.
> > btw, how *do* I control the default schema? Is it always the schema at
> > the front of the search list,
> If you mean the default schema for creating things, yes, it's whatever
> is at the front of the search list. Should it be different?
We should at least have some ability to incrementally add and subtract
from the search list. Something like SET SCHEMA = 'foo' would be
helpful. Since we don't currently have the search list as a queryable
(sp?) entity, it is difficult to manipulate on the fly afaict.
- Thomas
Tom Lane writes: > IIRC, a function is only considered to be a cast function if it matches > by name *and schema* with the target type. So if you, for example, > make a function public.int4(something), it'll never be considered a > cast function for pg_catalog.int4. I had some doubts about that rule > when I put it in, but so far have not thought of an alternative I like > better. Perhaps it wouldn't be such a terrible idea after all to store the casting paths separately, such as in a system table pg_cast (from, to, func, implicit). This would implement the SQL99 spec fairly exactly. -- Peter Eisentraut peter_e@gmx.net
Peter Eisentraut <peter_e@gmx.net> writes:
> Perhaps it wouldn't be such a terrible idea after all to store the casting
> paths separately, such as in a system table pg_cast (from, to, func,
> implicit). This would implement the SQL99 spec fairly exactly.
Well, maybe. One question is how that would fit in with schemas.
Thomas appears to want your schema search path to have some effect on
which casts you can see --- which I'm not at all sure I agree with,
but if that's the requirement then the above doesn't do it either.
If we just want to get out from under the coupling of function name to
cast status, the above would do it ... and also break existing
applications that aren't expecting to have to do something special to
make a function of the right name become a cast function. Perhaps there
could be a GUC variable to allow created functions matching the old
naming convention to be automatically made into casts? We could default
it to 'true' for a release or two and then default to 'false'.
BTW, the above would also provide a place to encode binary compatibility
associations in the DB, rather than hard-wired, which would be a Good
Thing. You could say that func == 0 means that no actual function call
is needed to transform type 'from' to 'to'.
regards, tom lane
On Thu, 2002-06-27 at 02:10, Tom Lane wrote: > Peter Eisentraut <peter_e@gmx.net> writes: > > Perhaps it wouldn't be such a terrible idea after all to store the casting > > paths separately, such as in a system table pg_cast (from, to, func, > > implicit). This would implement the SQL99 spec fairly exactly. > > Well, maybe. One question is how that would fit in with schemas. > Thomas appears to want your schema search path to have some effect on > which casts you can see --- which I'm not at all sure I agree with, I hope that schema search path has some effect on other user-defined stuff like simple functions and operators. > but if that's the requirement then the above doesn't do it either. What is and what is not affected by schemas ? Are the docs on our schema usage already available someplace ? ------------- Hannu
Hannu Krosing <hannu@tm.ee> writes: > Are the docs on our schema usage already available someplace ? Yes, although there's not a pulled-together introduction (someone needs to write a section for the tutorial, I think). Try http://developer.postgresql.org/docs/postgres/sql-naming.html and see the SEARCH_PATH variable at http://developer.postgresql.org/docs/postgres/runtime-config.html#RUNTIME-CONFIG-GENERAL as well as the schema-aware rules for resolution of overloaded functions and operators: http://developer.postgresql.org/docs/postgres/typeconv-func.html http://developer.postgresql.org/docs/postgres/typeconv-oper.html also various new functions at http://developer.postgresql.org/docs/postgres/functions-misc.html http://developer.postgresql.org/docs/postgres/datatype-oid.html regards, tom lane
Tom Lane writes: > Thomas appears to want your schema search path to have some effect on > which casts you can see --- which I'm not at all sure I agree with, > but if that's the requirement then the above doesn't do it either. If I understand this right, this would be nearly analogous to determining an operator's underlying function by schema path. That smells an awful lot like dynamic scoping, a.k.a. a bad idea, and completely inconsistent with the rest of the system. > If we just want to get out from under the coupling of function name to > cast status, the above would do it ... and also break existing > applications that aren't expecting to have to do something special to > make a function of the right name become a cast function. Perhaps there > could be a GUC variable to allow created functions matching the old > naming convention to be automatically made into casts? We could default > it to 'true' for a release or two and then default to 'false'. Sure. However, AFAIK, the current development progress has already broken the previous expectations slightly by requiring that implicit casting paths be explicitly declared. -- Peter Eisentraut peter_e@gmx.net
Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane writes:
>> Thomas appears to want your schema search path to have some effect on
>> which casts you can see --- which I'm not at all sure I agree with,
>> but if that's the requirement then the above doesn't do it either.
> If I understand this right, this would be nearly analogous to determining
> an operator's underlying function by schema path. That smells an awful
> lot like dynamic scoping, a.k.a. a bad idea, and completely inconsistent
> with the rest of the system.
I don't like it either. ISTM that the casting relationship between two
types is a property of those types and should *not* be affected by your
search path. Maybe you referenced one or both types by qualified
schema names, rather than by finding them in your path, but should that
keep you from seeing the cast? Especially since there's no obvious
place in the CAST syntax to attach a schema qualification, if we try
to insist that one might be needed to get at the desired cast function.
An extreme case is binary-equivalence, which as I mentioned maps nicely
into the sort of pg_cast table you suggested. It doesn't map nicely
into anything that involves schema visibility --- there is no cast
function to hide or make visible. Even more to the point, if types A
and B are binary-equivalent, should changing my search path make them
stop being so? Doesn't make sense to me.
> Sure. However, AFAIK, the current development progress has already broken
> the previous expectations slightly by requiring that implicit casting
> paths be explicitly declared.
True. If we wanted to maintain the old behavior exactly then we could
allow this hypothetical GUC variable to also cause old-convention cast
functions to be automatically marked IMPLICIT CAST. (I suppose the
IMPLICIT CAST bit would actually stop being a property of functions at
all, and would become a column of pg_cast.)
regards, tom lane