Thread: possible DOMAIN implementation
Hello,
I have spend some thinking about implementation of DOMAIN capability.
Here are my ideas.
What is a domain? It is an alias for a type with size, constraints and
default values. It is like one column of a table. And this is the main
idea of my "implementation". It should be possible to implement it using
existing system tables.
New rules for grammar can be easily created from already existing pieces
(column definition of a table).
How to store information about a domain in system tables?
When a new domain is created it will:
- put a record into pg_type with typnam = domain name, new code for
typtype = 'd' and typrelid = oid of a new record in pg_class (next line)
- put a record into pg_class to create a fictional table with a new
relkind ('d'?), relnatts = 1, relname can be system generated
(pg_d_<domainname>)
- put a records into pg_attribute and pg_attrdef with "column
(attribute) definition" - real type, size, default value etc., owner
will the fictional table from the previous step
Then it will be required to modify functions that works with types. When
typtype of a retrieved type is 'd' then it will perform lookups into
pg_class, pg_attribute and pg_attrdef to find the real definition of the
domain. These additional lookups will also create a performace penalty
of using domains. But every feature has its costs. I know this paragraph
about the real implementation is very short, but I think there are
people that know the "type mechanism" better then I know. And can easier
tell if it is possible to go this way.
I hope you understand my explanation. It is also possible that I don't
know some aspects of the backend code that makes my idea wrong.
Dan
----------------------------------------------
Ing. Daniel Horak
network and system administrator
e-mail: horak@sit.plzen-city.cz
privat e-mail: dan.horak@email.cz ICQ:36448176
----------------------------------------------
Horák Daniel <horak@sit.plzen-city.cz> writes:
> When a new domain is created it will:
> - put a record into pg_type with typnam = domain name, new code for
> typtype = 'd' and typrelid = oid of a new record in pg_class (next line)
> - put a record into pg_class to create a fictional table with a new
> relkind ('d'?), relnatts = 1, relname can be system generated
> (pg_d_<domainname>)
Ugh. Don't overload pg_class with things that are not tables. I see no
reason that either pg_class or pg_attribute should be involved in the
definition of a domain. Make new system tables if you need to, but
don't confuse the semantics of critical tables.
> - put a records into pg_attribute and pg_attrdef with "column
> (attribute) definition" - real type, size, default value etc., owner
> will the fictional table from the previous step
> Then it will be required to modify functions that works with types. When
> typtype of a retrieved type is 'd' then it will perform lookups into
> pg_class, pg_attribute and pg_attrdef to find the real definition of the
> domain. These additional lookups will also create a performace penalty
> of using domains.
Why shouldn't this info be directly available from the pg_type row?
regards, tom lane
John Reid <jgreid@uow.edu.au> writes:
>> Ugh. Don't overload pg_class with things that are not tables. I see no
>> reason that either pg_class or pg_attribute should be involved in the
>> definition of a domain. Make new system tables if you need to, but
>> don't confuse the semantics of critical tables.
> This is required due to the way inheritance is currently handled?
Not inheritance specifically. I'nm just looking at it on general design
principles: all the rows of a table should be the same kind of thing.
We shade that a little to allow views, sequences, etc, in pg_class, but
at least they're all things that have columns and so forth.
> From what I can remember inheritance works in postgresql at the class level.
> C.J. Date et al *strongly* argue that inheritance should be based on types,
> not relations/classes. This is still the case in 7.1?
Postgres doesn't really distinguish between tables and composite types
--- there's a one-for-one relationship between 'em. So we haven't had
to think hard about that point. If we did allow composite types without
associated tables, we probably would want tables to inherit from 'em
(which would mean some rethinking of the inheritance representation).
regards, tom lane
Hi,
Haven't looked at this for a while, but I think some larger issues might raise
their (ugly?) heads here.
Domains are effectively types that inherit attributes of parent type, with
some additional information, so should be handled at the level of pg_type.
However might make sense to look at some other matters at the same time - I'm
thinking specifically of general inheritance and abstract data types. AFAICT,
these are all closely related. I started looking at this a while ago, but was
side-tracked by the winds of change ;-)
Tom Lane wrote:
> Horák Daniel <horak@sit.plzen-city.cz> writes:
> > When a new domain is created it will:
> > - put a record into pg_type with typnam = domain name, new code for
> > typtype = 'd' and typrelid = oid of a new record in pg_class (next line)
> - put a record into pg_class to create a fictional table with a new
> > relkind ('d'?), relnatts = 1, relname can be system generated
> > (pg_d_<domainname>)
>
> Ugh. Don't overload pg_class with things that are not tables. I see no
> reason that either pg_class or pg_attribute should be involved in the
> definition of a domain. Make new system tables if you need to, but
> don't confuse the semantics of critical tables.
This is required due to the way inheritance is currently handled?
> > - put a records into pg_attribute and pg_attrdef with "column
> > (attribute) definition" - real type, size, default value etc., owner
> > will the fictional table from the previous step
ditto?
> > Then it will be required to modify functions that works with types. When
> > typtype of a retrieved type is 'd' then it will perform lookups into
> > pg_class, pg_attribute and pg_attrdef to find the real definition of the
> > domain. These additional lookups will also create a performace penalty
> > of using domains.
>
> Why shouldn't this info be directly available from the pg_type row?
From what I can remember inheritance works in postgresql at the class level.
C.J. Date et al *strongly* argue that inheritance should be based on types,
not relations/classes. This is still the case in 7.1? If the inheritance
mechanism could be changed to support types, the concept of inheritance for
classes should not be broken as these have entries in pg_type - possible some
code might be though :-(
Please note that I'm looking at forest scales (and also through the haze of
memory) - the trees might have an entirely viewpoint ;-)
cheers,
John
--
----------------------------------------------------------------------
john reid e-mail john_reid@uow.edu.au
technical officer room G02, building 41
school of geosciences phone +61 02 4221 3963
university of wollongong fax +61 02 4221 4250
uproot your questions from their ground and the dangling roots will be
seen. more questions! -mentat zensufi
apply standard disclaimers as desired...
----------------------------------------------------------------------
Hi, Tom Lane wrote: > > John Reid <jgreid@uow.edu.au> writes: > >> Ugh. Don't overload pg_class with things that are not tables. I see no > >> reason that either pg_class or pg_attribute should be involved in the > >> definition of a domain. Make new system tables if you need to, but > >> don't confuse the semantics of critical tables. > > > This is required due to the way inheritance is currently handled? > > Not inheritance specifically. I'nm just looking at it on general design > principles: all the rows of a table should be the same kind of thing. > We shade that a little to allow views, sequences, etc, in pg_class, but > at least they're all things that have columns and so forth. These could actually be defined in pg_type (or an inherited class pg_class_def)? > > > From what I can remember inheritance works in postgresql at the class level. > > C.J. Date et al *strongly* argue that inheritance should be based on types, > > not relations/classes. This is still the case in 7.1? > > Postgres doesn't really distinguish between tables and composite types > --- there's a one-for-one relationship between 'em. So we haven't had > to think hard about that point. If we did allow composite types without > associated tables, we probably would want tables to inherit from 'em > (which would mean some rethinking of the inheritance representation). Yes. I had a superficial look at SQL99 abstract data types a while back, but didn't get very far. I didn't raise any of the issues I came across at the time as everyone was busy with the 7.1 release. My interest is primarily in GIS data storage, which is a bit more involved than most applications. Ability to define complex types without having to instantiate them (or else implement them as user defined type when they are really a class) would be especially handy for GIS schemas. Not quite sure what else yet ;-) IMHO, it is probably worth looking at this further - it seems to me that these issues will have a significant impact when dealing with implementation of the SQL99 standard, so probably easier to deal with them now/soon? FWIW, some *really sketchy* ideas from when I looked at this: 1) pg_inherits should point at pg_type 2) some (most?) of the functionality of pg_class should be moved into pg_type ((2a) maybe using inherited class pg_class_def?) 3) pg_class should purely contain relation specific stuff only (type, indexes, owner) another alternative would be introduce a new system table pg_relation for relations, making pg_class the equivalent of pg_type but used for handling complex types. Then again, this is effectively the same as (2a)? Might make sense to think about renaming the tables anyway, as to me pg_class seems to imply the class definition, rather than the instantiation. Then we would have pg_type pg_class inherits pg_type pg_relation I could forsee some real chicken or the egg problems in system initialization. How are these handled currently? cheers, John