Thread: 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