Thread: System vs non-system casts
In working on the newsysviews project we've discovered that there's no definitive way to determine if a cast is a system cast (system as in part of postgresql/created by createdb) or not. What pg_dump does (and what we're doing now for lack of a better solution) is any cast that doesn't involve a user-created type, or a user-created conversion function is considered a system cast. This means if a user creates a cast between two different system types using a system function (to use a bad example, say text->int), that cast won't show up in pg_user_casts, and more important, it won't be backed up by pg_dump. This seems sub-optimal. :) Is there a reasonable way to fix this? For most objects, you can determine if it's a system object or not based on the schema it lives in. So, one possibility is to put casts into schemas. This would have the added effect of allowing you to 'hide' a cast by removing it's schema from search_path. Another possibility would be to add an is_system column to pg_cast. Casts created by the system as part of database creation (or at least the initial creation of the template databases) would have this field set to true, whereas user created casts would have it set to false. Instead of having two seperate methods to create casts, you could do a bulk update of pg_cast as part of database creation. Thoughts? -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
On Sunday 10 April 2005 19:10, Jim C. Nasby wrote:
> In working on the newsysviews project we've discovered that there's no
> definitive way to determine if a cast is a system cast (system as in
> part of postgresql/created by createdb) or not. What pg_dump does (and
> what we're doing now for lack of a better solution) is any cast that
> doesn't involve a user-created type, or a user-created conversion
> function is considered a system cast. This means if a user creates a
> cast between two different system types using a system function (to use
> a bad example, say text->int), that cast won't show up in pg_user_casts,
> and more important, it won't be backed up by pg_dump.
>
> This seems sub-optimal. :)
>
> Is there a reasonable way to fix this? For most objects, you can
> determine if it's a system object or not based on the schema it lives
> in. So, one possibility is to put casts into schemas. This would have
> the added effect of allowing you to 'hide' a cast by removing it's
> schema from search_path.
>
> Another possibility would be to add an is_system column to pg_cast.
> Casts created by the system as part of database creation (or at least
> the initial creation of the template databases) would have this field
> set to true, whereas user created casts would have it set to false.
> Instead of having two seperate methods to create casts, you could do a
> bulk update of pg_cast as part of database creation.
>
> Thoughts?
One potential ugly way to do it would be to use the magical "last system oid"
as a differentiator between those created by pg and those created by the
user. It would be different for every version so that might be a problem.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
On 2005-04-12, Robert Treat <xzilla@users.sourceforge.net> wrote: > One potential ugly way to do it would be to use the magical "last system oid" > as a differentiator between those created by pg and those created by the > user. It would be different for every version so that might be a problem. That is what pg_dump does when talking to pre-7.2 databases. But it isn't entirely reliable because oid wrap can allow the user to create objects with oids < datlastsysoid. (If it was BootstrapOid you were thinking of, that doesn't work at all because the system casts are added after the bootstrap stage.) -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
Andrew - Supernews <andrew+nonews@supernews.com> writes:
> On 2005-04-12, Robert Treat <xzilla@users.sourceforge.net> wrote:
>> One potential ugly way to do it would be to use the magical "last system oid"
> That is what pg_dump does when talking to pre-7.2 databases. But it isn't
> entirely reliable because oid wrap can allow the user to create objects
> with oids < datlastsysoid.
Also, it would ideally be possible to deliberately create a new cast
that pg_dump would ignore --- you can do this for other object kinds
by creating them in the pg_catalog schema.
It's a little bit odd to think of casts as belonging to schemas,
since they don't have names in the normal sense. We could probably
bull ahead and do it anyway though.
The other possible solution that comes to mind is to invent the notion
that a cast has a specific owner (which arguably it should have anyway)
and then say that "system casts" are those whose owner is the original
superuser.
The former approach seems preferable if you want the schema search path
to affect the findability of casts, and the latter approach if you
don't. Right at the moment I'm too tired to figure out which one of
those things I believe ... any thoughts?
regards, tom lane
Tom Lane wrote: > Also, it would ideally be possible to deliberately create a new cast > that pg_dump would ignore --- you can do this for other object kinds > by creating them in the pg_catalog schema. > > It's a little bit odd to think of casts as belonging to schemas, > since they don't have names in the normal sense. We could probably > bull ahead and do it anyway though. > > The other possible solution that comes to mind is to invent the notion > that a cast has a specific owner (which arguably it should have anyway) > and then say that "system casts" are those whose owner is the original > superuser. > > The former approach seems preferable if you want the schema search path > to affect the findability of casts, and the latter approach if you > don't. Right at the moment I'm too tired to figure out which one of > those things I believe ... any thoughts? Just my toughts: I believe it's better when cast selection does not depend on the search_path. It seems dangerous for objects that you don't usually qualify with a schema. With all other objects in schemas I can think of, you can easily write the full-qualified name. So I vote for the latter. Best Regards, Michael Paesold
On Tue, Apr 12, 2005 at 08:39:09AM +0200, Michael Paesold wrote: > Tom Lane wrote: > >The other possible solution that comes to mind is to invent the notion > >that a cast has a specific owner (which arguably it should have anyway) > >and then say that "system casts" are those whose owner is the original > >superuser. > > Just my toughts: I believe it's better when cast selection does not depend > on the search_path. It seems dangerous for objects that you don't usually > qualify with a schema. With all other objects in schemas I can think of, > you can easily write the full-qualified name. > > So I vote for the latter. So casts created by the original superuser don't get dumped? That's not good IMHO. But yes, schema-qualifying casts seems weird:'123'::someschema.user_type Is that even accepted by the grammar? -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "La fuerza no está en los medios físicos sino que reside en una voluntad indomable" (Gandhi)
Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> But yes, schema-qualifying casts seems weird:
> '123'::someschema.user_type
> Is that even accepted by the grammar?
Yes, but it'd be taken as a qualification on the type name not the cast
per se. Offhand I'm not sure where we could even put a schema name for
the cast itself in the CAST syntax ... so that idea probably doesn't fly
at all.
regards, tom lane
Alvaro Herrera wrote: > On Tue, Apr 12, 2005 at 08:39:09AM +0200, Michael Paesold wrote: >> Tom Lane wrote: > >> >The other possible solution that comes to mind is to invent the notion >> >that a cast has a specific owner (which arguably it should have anyway) >> >and then say that "system casts" are those whose owner is the original >> >superuser. >> >> Just my toughts: I believe it's better when cast selection does not >> depend >> on the search_path. It seems dangerous for objects that you don't usually >> qualify with a schema. With all other objects in schemas I can think of, >> you can easily write the full-qualified name. >> >> So I vote for the latter. > > So casts created by the original superuser don't get dumped? That's not > good IMHO. Well perhaps there is an even better solution? > But yes, schema-qualifying casts seems weird: > '123'::someschema.user_type > > Is that even accepted by the grammar? It's the type you qualify here, not the cast, isn't it? Nevertheless don't only think about explicit casts. With implicit casts you will usually not write out a cast at all. Best Regards, Michael Paesold
On Tue, Apr 12, 2005 at 06:38:41PM +0200, Michael Paesold wrote: > Alvaro Herrera wrote: > > >On Tue, Apr 12, 2005 at 08:39:09AM +0200, Michael Paesold wrote: > >>Tom Lane wrote: > > > >>>The other possible solution that comes to mind is to invent the > >>>notion that a cast has a specific owner (which arguably it should > >>>have anyway) and then say that "system casts" are those whose owner > >>>is the original superuser. > >> > >>Just my toughts: I believe it's better when cast selection does not > >>depend on the search_path. It seems dangerous for objects that you > >>don't usually qualify with a schema. With all other objects in > >>schemas I can think of, you can easily write the full-qualified > >>name. > >> > >>So I vote for the latter. > > > >So casts created by the original superuser don't get dumped? That's > >not good IMHO. > > Well perhaps there is an even better solution? What about the simple one of having a bool "pg_cast.castissystem" column, or something similar? > >But yes, schema-qualifying casts seems weird: > >'123'::someschema.user_type > > > >Is that even accepted by the grammar? > > It's the type you qualify here, not the cast, isn't it? Yes, sorry. I'm low on caffeine apparently. Point on implicit casts taken too. -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "I personally became interested in Linux while I was dating an English major who wouldn't know an operating system if it walked up and bit him." (Val Henson)
On 2005-04-12, Alvaro Herrera <alvherre@dcc.uchile.cl> wrote: >> Well perhaps there is an even better solution? > > What about the simple one of having a bool "pg_cast.castissystem" > column, or something similar? Checking the OID might be sufficient if it were possible to make the OID counter restart at some value known to be greater than any datlastsysoid, rather than restarting at BootstrapOid. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> What about the simple one of having a bool "pg_cast.castissystem"
> column, or something similar?
This one is sounding pretty good to me, though I'd be inclined to call
it "castisbuiltin" or some such.
regards, tom lane
On Tue, Apr 12, 2005 at 05:04:03PM -0000, Andrew - Supernews wrote: > On 2005-04-12, Alvaro Herrera <alvherre@dcc.uchile.cl> wrote: > >> Well perhaps there is an even better solution? > > > > What about the simple one of having a bool "pg_cast.castissystem" > > column, or something similar? > > Checking the OID might be sufficient if it were possible to make the OID > counter restart at some value known to be greater than any datlastsysoid, > rather than restarting at BootstrapOid. In fact AFAICT it does start at BootstrapObjectIdData, which is 16384 by default. -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) Management by consensus: I have decided; you concede. (Leonard Liu)
Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> On Tue, Apr 12, 2005 at 05:04:03PM -0000, Andrew - Supernews wrote:
>> Checking the OID might be sufficient if it were possible to make the OID
>> counter restart at some value known to be greater than any datlastsysoid,
>> rather than restarting at BootstrapOid.
> In fact AFAICT it does start at BootstrapObjectIdData, which is 16384 by
> default.
This line of thought is pretty much a non-starter, because many "system"
objects are created after the initial bootstrap phase, during standalone
backend sessions that are not fundamentally different from ordinary
operations. For instance I don't think we want a solution in which
casts created as part of the information_schema couldn't be marked as
builtins. (Right at the moment there don't seem to be any such casts,
but certainly there might someday be a need for one.)
Alvaro's "pg_cast.castissystem" idea can be made to work with this,
if necessary by brute-force UPDATEs of pg_cast. Playing games with
the values of OIDs can't ...
regards, tom lane
On Tue, Apr 12, 2005 at 01:04:20PM -0400, Tom Lane wrote: > Alvaro Herrera <alvherre@dcc.uchile.cl> writes: > > What about the simple one of having a bool "pg_cast.castissystem" > > column, or something similar? > > This one is sounding pretty good to me, though I'd be inclined to call > it "castisbuiltin" or some such. > > regards, tom lane I hadn't thought of using owner to differentiate casts (one of Tom's original suggestions). I like the idea of having ownership of casts (unlike putting casts into schemas, this shouldn't have an operational impact, and it brings casts more inline with the other system objects that do record ownership). But I think using owner to indicate if a cast should be considered part of the system or not is pretty obtuse. I'm fine with having an 'is system' or 'is builtin' flag. Incidentally, what's the proper nomenclature for built-in/system objects? Are they 'system' objects or 'built-in' objects? Currently we're calling them system objects in the newsysviews project. What do people think about adding cast ownership at the same time? -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"