Thread: System vs non-system casts

From:
"Jim C. Nasby"
Date:

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                
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?"


From:
Robert Treat
Date:

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


From:
Andrew - Supernews
Date:

On 2005-04-12, Robert Treat <> 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


From:
Tom Lane
Date:

Andrew - Supernews <> writes:
> On 2005-04-12, Robert Treat <> 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


From:
"Michael Paesold"
Date:

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 



From:
Alvaro Herrera
Date:

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)


From:
Tom Lane
Date:

Alvaro Herrera <> 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


From:
"Michael Paesold"
Date:

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 



From:
Alvaro Herrera
Date:

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)


From:
Andrew - Supernews
Date:

On 2005-04-12, Alvaro Herrera <> 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


From:
Tom Lane
Date:

Alvaro Herrera <> 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


From:
Alvaro Herrera
Date:

On Tue, Apr 12, 2005 at 05:04:03PM -0000, Andrew - Supernews wrote:
> On 2005-04-12, Alvaro Herrera <> 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)


From:
Tom Lane
Date:

Alvaro Herrera <> 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


From:
"Jim C. Nasby"
Date:

On Tue, Apr 12, 2005 at 01:04:20PM -0400, Tom Lane wrote:
> Alvaro Herrera <> 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                
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?"