Re: knngist - 0.8

On Mon, Oct 18, 2010 at 04:13:04PM +0100, Mark Cave-Ayland wrote:
> David Fetter wrote:
> 
> >>For my vote, I'd prefer either the Oid of a custom type or an array
> >>of Oid, Datum pairs - i.e. something we can extend in the future if
> >>required.
> >
> >This sounds a lot like a foreign key to another table.  Are you not
> >proposing doing that because of performance considerations?
> >
> >Cheers,
> >David.
> 
> Well, in PostGIS a typmod contains 3 pieces of information:
> 
> 1) the SRID
> 2) the dimension
> 3) the geometry type
> 
> The SRID is technically already a foreign key into another table,
> with dimension and SRID as other information. At the moment, we
> bit-pack the dimension and geometry type into the SRID and use that
> as the typmod but this only leaves 21 bits IIRC for the SRID. The
> additional complication is that SRIDs at the higher end of the range
> are allowed for anyone to use, and so people may have their own
> customised spheroids defined in this region of the table.

Sounds like you need space for all of these separately, and once you
have that, you'll probably start thinking about other possible pieces
of information you could store, especially as you start to store new
data types and have new operators on them.

It sounds to me as thought the typemod, or something like it, needs to
be able to store, in general, completely arbitrary information,
although not likely much over a page or two of memory.  Cf.  Robert
Haas's recent comment about Klingon.  While we could make this a
completely opaque structure from the SQL level, I'm not sure it's a
good idea.

> If we had a foreign key into another table, we'd need to ensure that
> no one could tamper with it as otherwise all chaos would break lose,
> e.g. breaking the geometry type constraint on a column.

Our system catalog tables have the nice property that no one can
accidentally modify them by hand, and "all chaos," as you put it, can
reasonably be expected to occur should they choose to do so.

> Heck, we even have people deleting the geometry_columns table
> sometimes because they are not aware of what it does.  By storing
> this information in the PG catalog then this can't happen, plus the
> information is available easily in Form_pg_attribute without having
> to implement our own cache, with its own related problems such as
> how/when to invalidate etc.

:)

> There is also a chance that we'd want to include additional
> information in the future related to geometry validity, for example,
> which would mean further reducing the range allowed within the
> spatial_ref_sys table in its existing form.

Right.

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate