Re: [SQL] new rule syntax?
A. R. Van Hook wrote: I have two tables defined as: checks (ckidint NOT null PRIMARY KEY, payto text, notes text, ckdate date, printed int default 0, tdate timestamp not null) checkitems (itemint not null, ckidint NOT null references checks, itemtypeint not null, amt numeric(7,3), primary key (item, ckid)) in previous versions (<8.1) the following rule declaration seemed to work fine Others have addresses your query with the rule. Can I ask why you're not using a DELETE CASCADE on the fkey? ... ckidint NOT null references checks ON DELETE CASCADE, ... -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Trigger on a column
Hi, My trigger's running on a table that works perfectly. This trigger calls a function that inserts or modifies a row of a different table. My problem is that I need to optimize this trigger, and the only way that I've found is to fire the trigger when certain table fields are modified. I do it like this: CREATE TRIGGER tr_barra_aps AFTER INSERT OR DELETE OR UPDATE OF status ON am_access_point EXECUTE PROCEDURE f_barra_aps(); but I get this error: ERROR: syntax error at or near "status" at character 62 The status field is correct. Can anybody help me? Thanks -- Josep Sanmarti Analista de Projectes OpenWired Caballero 87 - Bajos 08029 - Barcelona Tel. 93 495 0990 Fax. 93 419 4591 Openwired Alejandro Villegas,29 28043 - MADRID - ESPAÑA Teléfono: 91 300 51 09 Fax: 91 300 28 13 http://www.openwired.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Trigger on a column
Hi,
readding around I just found this:
The CREATE TRIGGER statement in PostgreSQL implements a subset of the
SQL99 standard. (There are no provisions for triggers in SQL92.) The
following functionality IS MISSING:
* SQL99 allows triggers to fire on updates to specific columns (e.g.,
AFTER UPDATE OF col1, col2).
* .
Does anybody know if that's true on version postgresql (PostgreSQL)
8.1.2? Because I don't actually know from what version that sentence is.
Thanks in advance
William Leite Araújo wrote:
http://www.postgresql.org/docs/8.1/interactive/sql-createtrigger.html :
CREATE TRIGGER -- define a new trigger
Synopsis
CREATE TRIGGER /name/ { BEFORE | AFTER } { /event/ [ OR ... ] }
ON /table/ [ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE /funcname/ ( /arguments/ )
There is not a way to create column level trigger. If the trigger
don't block the insert/update/delete, you can try an "after trigger"
that do all insertions once.
2006/2/6, Josep Sanmartí <[EMAIL PROTECTED]
>:
Hi,
My trigger's running on a table that works perfectly. This trigger
calls
a function that inserts or modifies a row of a different table. My
problem is that I need to optimize this trigger, and the only way
that
I've found is to fire the trigger when certain table fields are
modified. I do it like this:
CREATE TRIGGER tr_barra_aps AFTER INSERT OR DELETE OR UPDATE OF status
ON am_access_point EXECUTE PROCEDURE f_barra_aps();
but I get this error:
ERROR: syntax error at or near "status" at character 62
The status field is correct. Can anybody help me?
Thanks
--
Josep Sanmarti
Analista de Projectes
OpenWired
Caballero 87 - Bajos
08029 - Barcelona
Tel. 93 495 0990
Fax. 93 419 4591
Openwired
Alejandro Villegas,29
28043 - MADRID - ESPAÑA
Teléfono: 91 300 51 09
Fax: 91 300 28 13
http://www.openwired.com
---(end of
broadcast)---
TIP 5: don't forget to increase your free space map settings
--
William Leite Araújo
--
Josep Sanmarti
Analista de Projectes
OpenWired
Caballero 87 - Bajos
08029 - Barcelona
Tel. 93 495 0990
Fax. 93 419 4591
Openwired
Alejandro Villegas,29
28043 - MADRID - ESPAÑA
Teléfono: 91 300 51 09
Fax: 91 300 28 13
http://www.openwired.com
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] Trigger on a column
Josep Sanmartí wrote: Hi, readding around I just found this: The CREATE TRIGGER statement in PostgreSQL implements a subset of the SQL99 standard. (There are no provisions for triggers in SQL92.) The following functionality IS MISSING: * SQL99 allows triggers to fire on updates to specific columns (e.g., AFTER UPDATE OF col1, col2). * . Does anybody know if that's true on version postgresql (PostgreSQL) 8.1.2? Because I don't actually know from what version that sentence is. Yes it is true for the current version. Up-to-date documentation always ships with a release and is also available on the website. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Function Dependency
On Sun, Feb 05, 2006 at 07:18:33PM -0700, Tony Wasson wrote: > > I am maintaining an application that has over 400 procedures and functions > > written in plsql, and around 100 tables. > > I want to generate a function dependency chart to depict the following: > > > > 1. Inter function/procedure dependencies > > 2. function-tables dependencies > > 3. function-sequences depencies ... > I too would be interested in a standardized tool to do this. I had a > similar situation and I ended up writing a perl script to parse my SQL > and make a graphviz dot file. I then used graphviz to make a function > dependency chart. I can't promise it would catch every single case, > but I can provide you with the code if you wish to give it a whirl. Tony, what do you think about sending a copy of this code to the pg_autodoc author ? I'm sure they'd be interested to have a look at this. http://www.rbt.ca/autodoc/index.html I have CC'ed this to the author. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] no notnull values, invalid stats?
Hello, The following message occasionally appears in my postgresql log - from temporal corellation, it might be caused by autovacuum. NOTICE: no notnull values, invalid stats Is that anything I should care about? I'm running debianized postgresql 8.1.0-3. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Trigger efficiency
Hi, I've the following problem and I don't know how to solve it: There is a table with about 10-12 fields, a couple of those fields are updated very often (about 30 times / minute or even more). This is my table (more or less): crete table monitor( time Timestamp, time2 timestamp, ... status int, ); I need to know how many rows are in the table and keep that number to another table every time that there is an INSERT or DELETE on that table, so I made a trigger that fires on insert and delete events and calls a function that makes de update on the new table, that works well. My problem starts when I have to count the rows depending on the status field: I added a UPDATE on my trigger and it works! :).. my trigger: CREATE TRIGGER tr_barra_aps AFTER INSERT OR DELETE OR UPDATE ON monitor EXECUTE PROCEDURE f_barra_aps(); But now, this trigger fires too often and there is an extra load on the system that makes everything run slower. I posted before a question asking about firing triggers on specific columns...thats not possible. I don't know how to solve it, perhaps I should do it without triggers.any help will be appreciated thanks! -- Josep Sanmarti Analista de Projectes OpenWired Caballero 87 - Bajos 08029 - Barcelona Tel. 93 495 0990 Fax. 93 419 4591 Openwired Alejandro Villegas,29 28043 - MADRID - ESPAÑA Teléfono: 91 300 51 09 Fax: 91 300 28 13 http://www.openwired.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] no notnull values, invalid stats?
Markus Schaber wrote: > Hello, > > The following message occasionally appears in my postgresql log - from > temporal corellation, it might be caused by autovacuum. > > NOTICE: no notnull values, invalid stats I see no such message in 8.1 sources. Is this verbatim or did you translate it? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] no notnull values, invalid stats?
Hi, Alvaro, Alvaro Herrera wrote: >>The following message occasionally appears in my postgresql log - from >>temporal corellation, it might be caused by autovacuum. >> >>NOTICE: no notnull values, invalid stats > > I see no such message in 8.1 sources. Is this verbatim or did you > translate it? It is verbatim from /var/log/postgresql/postgresql-8.1-main.log. But I have PostGIS installed in some of the databases, so it might be from there. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Trigger efficiency
I do not know of a way to make your trigger run less often, but you could only have it do the insert if something changes. This is what we do: CREATE or replace FUNCTION UPDATE_SERVER_HST() RETURNS TRIGGER AS ' begin if (OLD.ADD_DATE is distinct from NEW.ADD_DATE or OLD.HOSTNAME is distinct from NEW.HOSTNAME ) then insert into SERVER_HST( OPERATION, HST_USR_ID, HST_ADD_DATE, SERVER_ID, UPDATE_COUNT, ADD_DATE, HOSTNAME) values( substr( TG_OP, 1, 1 ), GET_CONNECTION_USR(), now() , OLD.SERVER_ID, OLD.UPDATE_COUNT, OLD.ADD_DATE, OLD.HOSTNAME); end if; RETURN NULL; -- result is ignored since this is an AFTER trigger end; ' language plpgsql security definer; That way the insert only happens if there is a change. Since this is a pain we have a program that writes our history and update_count triggers for us based on what we run through our modeling tool. Craig On Monday 06 February 2006 06:48, Josep Sanmartí wrote: > Hi, > > I've the following problem and I don't know how to solve it: There is a > table with about 10-12 fields, a couple of those fields are updated very > often (about 30 times / minute or even more). This is my table (more or > less): > > crete table monitor( > time Timestamp, > time2 timestamp, > ... > status int, > ); > > I need to know how many rows are in the table and keep that number to > another table every time that there is an INSERT or DELETE on that > table, so I made a trigger that fires on insert and delete events and > calls a function that makes de update on the new table, that works well. > My problem starts when I have to count the rows depending on the status > field: I added a UPDATE on my trigger and it works! :).. my trigger: > > CREATE TRIGGER tr_barra_aps AFTER INSERT OR DELETE OR UPDATE > ON monitor EXECUTE PROCEDURE f_barra_aps(); > > But now, this trigger fires too often and there is an extra load on the > system that makes everything run slower. I posted before a question > asking about firing triggers on specific columns...thats not possible. I > don't know how to solve it, perhaps I should do it without > triggers.any help will be appreciated > > thanks! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] no notnull values, invalid stats?
On Mon, Feb 06, 2006 at 03:32:32PM +0100, Markus Schaber wrote:
> Alvaro Herrera wrote:
> >>The following message occasionally appears in my postgresql log - from
> >>temporal corellation, it might be caused by autovacuum.
> >>
> >>NOTICE: no notnull values, invalid stats
> >
> > I see no such message in 8.1 sources. Is this verbatim or did you
> > translate it?
>
> It is verbatim from /var/log/postgresql/postgresql-8.1-main.log.
>
> But I have PostGIS installed in some of the databases, so it might be
> from there.
That is indeed a PostGIS message; it's in compute_geometry_stats()
in lwgeom/lwgeom_estimate.c. I think it means that during an
analyze, all of the sampled rows had NULL values in their geometry
columns (i.e., no not-NULL values were found); that would explain
the correlation with autovacuum. Here's an example that elicits
the notice:
postgis=> CREATE TABLE foo ();
CREATE TABLE
postgis=> SELECT AddGeometryColumn('foo', 'geom', -1, 'GEOMETRY', 2);
addgeometrycolumn
public.foo.geom SRID:-1 TYPE:GEOMETRY DIMS:2
(1 row)
postgis=> INSERT INTO foo VALUES (NULL);
INSERT 0 1
postgis=> ANALYZE foo;
NOTICE: no notnull values, invalid stats
ANALYZE
postgis=> UPDATE foo SET geom = GeomFromText('POINT(0 0)');
UPDATE 1
postgis=> ANALYZE foo;
ANALYZE
--
Michael Fuhr
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
