[postgis-users] create tables smarter
Chris Hermansen
chris.hermansen at timberline.ca
Wed Jun 4 15:26:14 PDT 2008
"try and see", those are words I like.
But first I googled, and I saw past discussions of the futility of
trying to create triggers on systems tables.
However, nothing I saw indicated I couldn't create a rule on a systems
table, so I decided to try it.
First, I decided to do a straightforward experiment to ensure myself
that I more or less understood PostgreSQL rules.
I created two tables, one called "data", one called "tracks", and a rule
that was triggered every time a row was inserted into "data", noting
that insertion in "tracks":
clh=# create table data (item text, qty integer);
CREATE TABLE
clh=# create table tracks (item text);
CREATE TABLE
clh=# create rule table_rule as on insert to data do also
clh-# insert into tracks values (new.item);
CREATE RULE
clh=# insert into data values ('software',37);
INSERT 0 1
clh=# insert into data values ('bullets', 2339);
INSERT 0 1
clh=# insert into data values ('gin',64556);
INSERT 0 1
clh=# select * from data;
item | qty
----------+-------
software | 37
bullets | 2339
gin | 64556
(3 rows)
clh=# select * from tracks;
item
----------
software
bullets
gin
(3 rows)
clh=#
OK, so far, so good. Let's try the same thing with pg_tables:
clh=# drop rule table_rule on data;
DROP RULE
clh=# drop table tracks;
DROP TABLE
clh=# drop table data;
DROP TABLE
clh=# create table tracks (tablename name);
CREATE TABLE
clh=# create rule table_rule as on insert to pg_tables do also
insert into tracks values (new.tablename);
CREATE RULE
clh=# create table fink (a int, b text);
CREATE TABLE
clh=# create table zink (c float, d date);
CREATE TABLE
clh=# select * from tracks;
tablename
-----------
(0 rows)
clh=#
Well, so much for that idea. We can create rules on system tables, they
just don't work.
Bummer.
Paul Ramsey wrote:
> Triggers on system tables aren't allowed, try and see. I like that
> GEOMETRY_COLUMNS provides a visible and standard way for 3rd party
> apps to understand the geometry in the database, and I hate having to
> maintain it. I've been wanting to make it magical for many years.
>
> There's other ways to do it, of course. My favorite stop-gap idea is
> to add some code to the hooks called by ANALYZE to update the
> GEOMETRY_COLUMNS table at the same time. That way pg_autovacuum would
> occasionally update it in the background, so if things went out of
> synch due to users changing things, they'd come back again in a bit.
>
> P
>
> On Wed, Jun 4, 2008 at 1:40 PM, Chris Hermansen
> <chris.hermansen at timberline.ca> wrote:
>
>> Paul says "the maintenance of GEOMETRY_COLUMNS has been a longstanding
>> bug'a'bear"...
>>
>> Why don't I find that surprising :-)
>>
>> But anyway, Paul also says that triggers on system tables could have
>> been used. And I guess that makes me wonder, is there anything to stop
>> a person from putting triggers on system tables in PostgreSQL? Does
>> that cause a problem somewhere else down the line?
>>
>> Paul Ramsey wrote:
>>
>>> Trust me, Chris, the maintenance of GEOMETRY_COLUMNS has been a
>>> longstanding bug'a'bear and in retrospect we probably would have been
>>> better off without it. In order to get an automagic GEOMETRY_COLUMNS
>>> though, we either required (a) triggers on system tables or (b)
>>> parameterized user-defined types. And now it looks like we'll have
>>> (b) in 8.4, so we can start to create our magic GEOMETRY_COLUMNS
>>> table.
>>>
>>> P
>>>
>>> On Wed, Jun 4, 2008 at 1:18 PM, Chris Hermansen
>>> <chris.hermansen at timberline.ca> wrote:
>>>
>>>
>>>> Hi Frank, folks;
>>>>
>>>> This is the kind of thing that would be really great to see:
>>>>
>>>> * a way of creating geometry that relied on SQL and not on calling
>>>> stored procedures
>>>> * a way of deleting same
>>>> * a system table that held ancilliary information
>>>>
>>>> It bugs me that I can DROP a table and still have a row referring to
>>>> that table in geometry_columns.
>>>>
>>>> I also find it kind of perverse that some desktop viewers require a
>>>> specific structure in a table containing geometry in order to view it.
>>>>
>>>> Not to sound negative or anything! Heaven forfend! It's just that
>>>> PostGIS is so much better than having to use a programmatic access like
>>>> SDE when I want to use my geometry, that I find it hard to accept that
>>>> it hasn't gone that last few centimetres.
>>>>
>>>> Frank Warmerdam wrote:
>>>>
>>>>
>>>>> Lee Hachadoorian wrote:
>>>>>
>>>>>
>>>>>> Andreas,
>>>>>>
>>>>>> AddGeometryColumn does two things: it adds a column of type geometry,
>>>>>> and it adds a row to table geometry_columns. If I understand what it
>>>>>> is you want to do, you can do the SELECT and then add the row to
>>>>>> geometry_columns with an INSERT statement:
>>>>>>
>>>>>>
>>>>> Folks,
>>>>>
>>>>> I did a presentation at PGCon and the issue of AddGeometryColumn() being
>>>>> necessary to populate the geometry_columns table came up. Some of the
>>>>> postgres techies suggested there has been work so that extension defined
>>>>> types could actually take extra arguments when used in the CREATE TABLE
>>>>> statement and that this might let us avoid the need to use
>>>>> AddGeometryColumn().
>>>>>
>>>>> ie.
>>>>>
>>>>> CREATE TABLE ROADS
>>>>> (id INT,
>>>>> name VARCHAR(255),
>>>>> geom GEOMETRY(<dimension>,<srid>,<type>) )
>>>>>
>>>>> Then the callback for the GEOMETRY type would take care of extending the
>>>>> geometry_columns table, presumably picking up the schema, table and
>>>>> column
>>>>> name from the context.
>>>>>
>>>>> In an ideal world, the dimension, srid and type would even show up when
>>>>> you describe the table, and would be automatically propagated to new
>>>>> tables
>>>>> created from a select.
>>>>>
>>>>> I don't know how hard this is, or if I overestimated how these custom
>>>>> type
>>>>> definition parsers work but I think it would be an appealing direction of
>>>>> development for postgis.
>>>>>
>>>>> Forgive me if I'm stating something already well understand in the
>>>>> postgis
>>>>> community. I'm a bit of a diletante in this world. :-)
>>>>>
>>>>> Best regards,
>>>>>
>>>>>
>>>> --
>>>> Regards,
>>>>
>>>> Chris Hermansen mailto:chris.hermansen at timberline.ca
>>>> tel+1.604.714.2878 · fax+1.604.733.0631 · mob+1.778.232.0644
>>>> Timberline Natural Resource Group · http://www.timberline.ca
>>>> 401 · 958 West 8th Avenue · Vancouver BC · Canada · V5Z 1E5
>>>>
>>>> _______________________________________________
>>>> postgis-users mailing list
>>>> postgis-users at postgis.refractions.net
>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>>
>>>>
>>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>
>>>
>> --
>> Regards,
>>
>> Chris Hermansen mailto:chris.hermansen at timberline.ca
>> tel+1.604.714.2878 · fax+1.604.733.0631 · mob+1.778.232.0644
>> Timberline Natural Resource Group · http://www.timberline.ca
>> 401 · 958 West 8th Avenue · Vancouver BC · Canada · V5Z 1E5
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
--
Regards,
Chris Hermansen mailto:chris.hermansen at timberline.ca
tel+1.604.714.2878 · fax+1.604.733.0631 · mob+1.778.232.0644
Timberline Natural Resource Group · http://www.timberline.ca
401 · 958 West 8th Avenue · Vancouver BC · Canada · V5Z 1E5
More information about the postgis-users
mailing list