Re: [HACKERS] SQL99, CREATE CAST, and initdb
On Thu, 2002-06-27 at 02:10, Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] SQL99, CREATE CAST, and initdb
Hannu Krosing [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] SQL99, CREATE CAST, and initdb
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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SQL99, CREATE CAST, and initdb
Peter Eisentraut [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] SQL99, CREATE CAST, and initdb
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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SQL99, CREATE CAST, and initdb
Peter Eisentraut [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] SQL99, CREATE CAST, and initdb
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] SQL99, CREATE CAST, and initdb
Thomas Lockhart [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] SQL99, CREATE CAST, and initdb
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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] SQL99, CREATE CAST, and initdb
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] SQL99, CREATE CAST, and initdb
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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] SQL99, CREATE CAST, and initdb
Thomas Lockhart [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SQL99, CREATE CAST, and initdb
Thomas Lockhart [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] SQL99, CREATE CAST, and initdb
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SQL99, CREATE CAST, and initdb
I said: Thomas Lockhart [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 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. 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] SQL99, CREATE CAST, and initdb
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 FUNCTION features. 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 fairly easy to complete since it resembles SELECT INTO. Implement MATCH SIMPLE clause for foreign key definitions. This is explicit SQL99 syntax for the default behavior, so we now support it :) Start implementation of shorthand for national character literals in scanner. For now, just swallow the leading N, but sometime soon let's figure 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 literal types fold into the same storage type. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] SQL99, CREATE CAST, and initdb
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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] SQL99, CREATE CAST, and initdb
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). ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
