Обсуждение: delay of function

hallo,
I use PostGIS ... .

I have table:
CREATE TABLE basic_metadata(name text,size text, ......);

Inside I want insert some records (name of new table, size of new table and etc.), so I created trigger on the table
geometry_columns:
CREATE TRIGGER ins_metdta AFTER INSERT ON geometry_columns FOR EACH ROW EXECUTE PROCEDURE insert_new();

CREATE OR REPLACE FUNCTION insert_new() RETURNS trigger AS $$
DECLARE
str text;
BEGIN
  str := 'insert into basic_metadata(name,size) values
(\''||new.f_table_name||'\',\''||pg_size_pretty(pg_relation_size(new.f_table_name))||'\');';
  raise notice '%', str;
  execute str;
  return null;
END;
$$ LANGUAGE PLPGSQL;

Every time, when is something inserted into table geometry_columns would trigger insert some data into the table
basic_metadata,but it fail, becouse in that time when is inserted name of new table into geometry_columns is not
createdthat new table physically and therefore I can't call function pg_relation_size(). It is created after that. So I
needwait with calling that trigger until that new table is done or ...... ????? Does anybody have some solution? 

Thanks for your advices,
Jaromír Kamler

On Wed, Mar 01, 2006 at 05:57:34PM +0100,  Jaromr Kamler wrote:
> I use PostGIS ... .

If you have PostGIS-specific questions then you might be better off
asking them in the postgis-users mailing list.

http://postgis.refractions.net/mailman/listinfo/postgis-users

> I have table:
> CREATE TABLE basic_metadata(name text,size text, ......);
>
> Inside I want insert some records (name of new table, size of new table and etc.), so I created trigger on the table
geometry_columns:
> CREATE TRIGGER ins_metdta AFTER INSERT ON geometry_columns FOR EACH ROW EXECUTE PROCEDURE insert_new();

Non-PostGIS users might not understand what you're doing here.  To
add a PostGIS geometry column to a table you call the AddGeometryColumn
function, which among other things inserts a record into geometry_columns.
It looks like you want that insert to trigger additional bookkeeping.

> CREATE OR REPLACE FUNCTION insert_new() RETURNS trigger AS $$
> DECLARE
> str text;
> BEGIN
>   str := 'insert into basic_metadata(name,size) values
(\''||new.f_table_name||'\',\''||pg_size_pretty(pg_relation_size(new.f_table_name))||'\');';
>   raise notice '%', str;
>   execute str;
>   return null;
> END;
> $$ LANGUAGE PLPGSQL;

Is there a reason you're using EXECUTE instead of inserting the
values directly?  If you do use EXECUTE then quote the values you're
inserting with quote_literal() instead of adding the quotes yourself.
Simplistic quoting such as the above can result in syntax errors
and make you susceptible to SQL injection attacks (which are variants
of the same problem: unanticipated data resulting in unanticipated
behavior).

What's the purpose of inserting the relation size?  For a new
relation it'll be zero, and any in case it'll be obsolete as soon
as you start inserting, updating, and deleting data.

> Every time, when is something inserted into table geometry_columns
> would trigger insert some data into the table basic_metadata, but
> it fail, becouse in that time when is inserted name of new table
> into geometry_columns is not created that new table physically and
> therefore I can't call function pg_relation_size(). It is created
> after that. So I need wait with calling that trigger until that new
> table is done or ...... ????? Does anybody have some solution?

Can you explain what you're doing and what error you're getting?
Ordinarily the table should exist by the time you call a PostGIS
function that inserts into geometry_columns, but there might be a
circumstance I'm overlooking.

--
Michael Fuhr

Thanks for your reply.
This is not specific PostGIS question. I do this, because I need collect some metadata about new tables(data about new
spatiallayer, like name, extend, time of creation, SRID, IP, owner, size and etc.). I know, that in the time, when is
thattrigger activated is not created that new table, ..., and this is my guestion. How I can call some function after
creationthat new table? I need wait until table is created and all records are inserted or I need find other way, but I
donot know PostgreSQL so good. 

Yours sincerely,
Jaromír Kamler

On Wed, Mar 01, 2006 at 10:58:24AM -0700, Michael Fuhr wrote:
> On Wed, Mar 01, 2006 at 05:57:34PM +0100,  Jaromr Kamler wrote:
> > I have table:
> > CREATE TABLE basic_metadata(name text,size text, ......);
> >
> > Inside I want insert some records (name of new table, size of new table and etc.), so I created trigger on the
tablegeometry_columns: 
> > CREATE TRIGGER ins_metdta AFTER INSERT ON geometry_columns FOR EACH ROW EXECUTE PROCEDURE insert_new();
>
> Non-PostGIS users might not understand what you're doing here.  To
> add a PostGIS geometry column to a table you call the AddGeometryColumn
> function, which among other things inserts a record into geometry_columns.
> It looks like you want that insert to trigger additional bookkeeping.

What's the purpose of the basic_metadata table?  Could you use a
view instead?

--
Michael Fuhr

On Wed, Mar 01, 2006 at 07:33:54PM +0100,  Jaromr Kamler wrote:
> This is not specific PostGIS question.

Then the purpose of the trigger on geometry_columns isn't clear.
PostGIS is a third-party extension that creates and maintains
geometry_columns for its own purposes; that table has no special
meaning to PostgreSQL.  Only PostGIS administrative functions would
ordinarily cause that trigger to fire.

> I do this, because I need collect some metadata about new tables(data
> about new spatial layer, like name, extend, time of creation, SRID, IP,
> owner, size and etc.). I know, that in the time, when is that trigger
> activated is not created that new table, ..., and this is my guestion.

Most of this metadata is available in the system catalogs or via
functions, so you needn't maintain it elsewhere.  Some of it can
be logged in the server's log files.  Here's some useful documentation:

http://www.postgresql.org/docs/8.1/interactive/catalogs.html
http://www.postgresql.org/docs/8.1/interactive/information-schema.html
http://www.postgresql.org/docs/8.1/interactive/functions-info.html
http://www.postgresql.org/docs/8.1/interactive/functions-admin.html
http://www.postgresql.org/docs/8.1/interactive/runtime-config-logging.html

(These links are to the 8.1 documentation; use the documentation
for whatever version you're running.)

> How I can call some function after creation that new table? I need wait
> until table is created and all records are inserted or I need find other
> way, but I do not know PostgreSQL so good.

PostgreSQL doesn't support triggers for actions like CREATE TABLE,
but it's still not clear that you need it.  Of the information you
listed above, only the time of creation and the IP address of the
creator (if that's what you mean) aren't available via functions
or the system catalogs, and they can be recorded in the server's
logs.  I'm not sure what you mean by "extend" as metadata.

Could you provide a high-level explanation of the problem you're
trying to solve?  The "what" instead of the "how."

--
Michael Fuhr

>Could you provide a high-level explanation of the problem you're
>trying to solve? The "what" instead of the "how."

I am trying to build data storage for spatial data. Users can export or import inside the storage their spatial data
andother users can viewed that data on their PC's by some software (GRASS, QGIS, ...). But in that data storage may be
manyof tables (spatial layers) and nobody will watching all of them (different projection, accurancy, extend-maximal
valueof north, east and minimal value of south and west). There must by table with basic informations about all tables
indata storage. So that, when someone import any spatial table, some function must write this basic informations inside
tablebasic_metadata. I was trying do this by trigger, like you see, but it looks like bad way, becouse function
AddGeomColl()firstly insert record in geometry_columns (i fire my wrong trigger) and after that is created table. I
mustfire some function after insert all records in new table, but I don't know how. Any ideas? 


Thanks,
Jaromír Kamler

On Wed, Mar 01, 2006 at 10:44:53PM +0100,  Jaromr Kamler wrote:
> There must by table with basic informations about all tables in
> data storage.  So that, when someone import any spatial table,
> some function must write this basic informations inside table
> basic_metadata.

Do you have a requirement to provide metadata that's not available
via the system catalogs?  Is there a reason basic_metadata can't
be a view?  Are you familiar with how views work?

http://www.postgresql.org/docs/8.1/interactive/tutorial-views.html
http://www.postgresql.org/docs/8.1/interactive/sql-createview.html

> I was trying do this by trigger, like you see, but it looks like
> bad way, becouse function AddGeomColl() firstly insert record in
> geometry_columns (i fire my wrong trigger) and after that is created
> table.

What version of PostGIS are you using?  Neither 1.0 nor 1.1 have
an AddGeomColl function -- do you mean AddGeometryColumn?  If so
then why do you think it inserts into geometry_columns and then
creates a table?  AddGeometryColumn is for adding a geometry column
to an existing table -- if you look at the function's code in
lwpostgis.sql you'll see that it executes ALTER TABLE to add a
column to an existing table, then it inserts a record into
geometry_columns.  If the table doesn't exist then ALTER TABLE will
fail before the insert into geometry_columns, so if the function
reaches that insert then the table must exist.  Does your system
behave differently?  If so then can you provide a test case?

> I must fire some function after insert all records in new table,
> but I don't know how. Any ideas?

The database can't possibly know when you're done inserting records
so you'll have to call the function yourself with SELECT.  But why
do you need to?  To get the table's size?  If you make basic_metadata
a view then its query could call pg_total_relation_size (or whatever).

--
Michael Fuhr

[Please copy the mailing list on replies.]

On Thu, Mar 02, 2006 at 11:00:00AM +0100,  Jaromr Kamler wrote:
> >Do you have a requirement to provide metadata that's not available
> >via the system catalogs? Is there a reason basic_metadata can't
> >be a view? Are you familiar with how views work?
>
> I don't need know only size of table, but I need know accurancy
> (complex statistic function) and extend (functions) of spatial data
> and etc. like I writed. For detection this, I must call some PostGIS
> functions on geometry column (common column the_geom) after inserted
> all records. Is view able do this?

If you can write a query to return what you want then you can put
that query in a view.  However, if the query is expensive then the
view will be expensive because each time you select from the view
the database will run the query.

> I need table (or view) like this: CREATE TABLE basic_metadata
> (name text, owner name, accurancy real, north real, west real, east
> real, south real, ...). North...south is extend of map. Is view
> able do this? I am afraid, that it will by slowly when I watch view,
> there si many of spatial tables with many GB. I thing that table
> is better solution ... .

You might be interested in reading about materialized views.
PostgreSQL doesn't provide them natively but it does provide the
mechanisms to implement them.

http://www.jonathangardner.net/PostgreSQL/materialized_views/matviews.html

You might also find the PostGIS function estimated_extent() useful
for determining the extent of a geometry column, and you can use
Gevel to examine GiST indexes.

http://postgis.refractions.net/docs/ch06.html#id2793703
http://www.sai.msu.su/~megera/oddmuse/index.cgi/Gevel

If you have questions about examining PostGIS data and indexes then
I'd recommend asking in postgis-users where the PostGIS developers
hang out.

> > The database can't possibly know when you're done inserting records
> > so you'll have to call the function yourself with SELECT.
>
> I can't call function in my own by SELECT every time, when someone
> insert inside data storage new table. Maybe I can use for this Linux
> daemon cron.

You might be able to use triggers; see the materialized views link
above for some ideas.  However, if every insert fires an expensive
trigger then you might have performance problems.  If the information
you're after is expensive to obtain and subject to change, then a
cron job might be the way to go.

--
Michael Fuhr