In response to Tom Lane :
Ben Morrow b...@morrow.me.uk writes:
I am trying to implement a fairly standard 'audit table' setup, but
using rules instead of triggers (since it should be more efficient).
Rules are sufficiently tricky that I would never, ever rely on them for
auditing. Use a
Quoth t...@sss.pgh.pa.us (Tom Lane):
Ben Morrow b...@morrow.me.uk writes:
I am trying to implement a fairly standard 'audit table' setup, but
using rules instead of triggers (since it should be more efficient).
Rules are sufficiently tricky that I would never, ever rely on them for
I am trying to implement a fairly standard 'audit table' setup, but
using rules instead of triggers (since it should be more efficient).
However, I'm running into problems when one of the audited tables has a
'serial' column that is allowed to default:
create table foo (id serial, bar text);
Ben Morrow b...@morrow.me.uk writes:
I am trying to implement a fairly standard 'audit table' setup, but
using rules instead of triggers (since it should be more efficient).
Rules are sufficiently tricky that I would never, ever rely on them for
auditing. Use a simple AFTER trigger instead.
Adrian Klaver wrote:
On Thursday 17 September 2009 8:35:52 am Nico Mandery wrote:
Hello list,
I am trying to wirte a rule which calls a PLPgSQL-function upon an
Insert in a table. Here is a somewhat simplified example of what i got
so far:
CREATE TABLE mytable (
mytable_id serial
On Thursday 17 September 2009 8:35:52 am Nico Mandery wrote:
Hello list,
I am trying to wirte a rule which calls a PLPgSQL-function upon an
Insert in a table. Here is a somewhat simplified example of what i got
so far:
CREATE TABLE mytable (
mytable_id serial PRIMARY KEY,
I have the following situation that I would appreciate your input on:
I have a table with a column that I use to uniquely identify its rows.
The table also has a rule on insert that stores the row identifier into
another table for reference at some other point.
The table is defined as
CREATE
Ray Madigan [EMAIL PROTECTED] writes:
Does anyone have any suggestion?
Use a trigger to propagate the data to the other table. You can't make
this work reliably with a rule, because rules are macros and hence
inherently subject to double-evaluation problems when dealing with
volatile functions.
Bath, David wrote:
Folks,
I'm looking at using pg to be the main platform for integrating info
from other RDBMS products (particularly Oracle) as pg seems to be
the most flexible RDBMS around.
Disregarding writing to foreign products, query-plan efficiencies,
or differences of SQL dialect, I'd
On 10/6/05 9:07 PM, Bath, David [EMAIL PROTECTED] wrote:
Folks,
I'm looking at using pg to be the main platform for integrating info
from other RDBMS products (particularly Oracle) as pg seems to be
the most flexible RDBMS around.
Disregarding writing to foreign products, query-plan
Folks,
I'm looking at using pg to be the main platform for integrating info
from other RDBMS products (particularly Oracle) as pg seems to be
the most flexible RDBMS around.
Disregarding writing to foreign products, query-plan efficiencies,
or differences of SQL dialect, I'd like to have a way
Anslag
Stephan Szabo [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
On Wed, 19 May 2004, Jie Liang wrote:
According to the document of rule:
CREATE RULE rulename AS ON delete TO mytablename DO
(
delete from aaa where id=OLD.id;
Delete from bbb where id=OLD.id;
Delete
According to the document of rule:
CREATE RULE rulename AS ON delete TO mytablename DO
(
delete from aaa where id=OLD.id;
Delete from bbb where id=OLD.id;
Delete from ccc where id=OLD.id
);
Should work, but it doesn't, what wrong with it?
Even I use { }
Jie Liang
On Wed, 19 May 2004, Jie Liang wrote:
According to the document of rule:
CREATE RULE rulename AS ON delete TO mytablename DO
(
delete from aaa where id=OLD.id;
Delete from bbb where id=OLD.id;
Delete from ccc where id=OLD.id
);
Should work, but it doesn't, what wrong with it?
What is
We have an interesting case where we want to use Postgres both as a database
and a front end to a proprietary database. For the latter, we wrote
functions that access the proprietary database. Then we defined views on the
proprietary database and wrote rules for insert/update/delete on those views
tables but it introduces too much overhead. In any case, we need the return
value (or an exception) from the function but there does not seem a way to
return it.
Can't you use erreport() to return an exception message?
---(end of broadcast)---
Gunter Diehl [EMAIL PROTECTED] writes:
create funtion f1(v, v) returns void as '...'
create rule vupt as on update to v do instead select f1(new, old)
While accepting this definitions, pg says it can not handle whole-tuple
references at runtime.
FWIW, the old case works fine. new doesn't
Josh,
Thanks for the info.
I need to change an insert into an update when the key already exists.
I have been using a rules to test it on a small set (table) and it
works.
Rules can't use indexes just scared me. I will have to test on a
larger set.
Also, I had the impression that if a trigger
Ian,
Anyway, I have a similar requirement, to intercept insert/update/delete and
redirect the data if a condition is met. Right now I am trying an INSTEAD
rule that puts the condtion in the WHERE of the rule definition. It seems to
work OK, but if the condition has exeptions where I would
Ian,
Thanks! I would have, but my messages bounce from SQL, even though I am
subscribed ( I get the messages, for crying out loud!)
send an e-mail to [EMAIL PROTECTED]
PS RECORD doesn't work but I think that is because I am on 7.2.1
OK. I may have tested that on 7.4 devel by
Hi all!
Is there a guideline on the use of rules compared to triggers when both
can be use to achieve the same result?
JLL
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
My book has a section on that:
http://www.postgresql.org/docs/awbook.html
Triggers are mostly for testing/modifying the row being
inserted/updated, while rules are better for affecting other rows or
other tables.
Jean-Luc Lachance wrote:
Hi all!
Is there a guideline on the use of
Thanks for the info.
Do you mean that if an update affects more than one row I should use
triggers because the rules will be executed only once?
JLL
Richard Huxton wrote:
On Friday 06 Dec 2002 4:03 pm, Jean-Luc Lachance wrote:
Hi all!
Is there a guideline on the use of rules compared
No, the rule will affect all the rows using one query.
---
Jean-Luc Lachance wrote:
Thanks for the info.
Do you mean that if an update affects more than one row I should use
triggers because the rules will be executed
Josh Berkus wrote:
Bruce, Richard,
Triggers are mostly for testing/modifying the row being
inserted/updated, while rules are better for affecting other rows or
other tables.
Hmmm. Thought that there were also some other criteria:
1) Rules can't use indexes to do their processing,
Bruce,
1) Rules can't use indexes to do their processing, so Rules which query
large
secondary tables can be a bad idea (maybe this has changed?)
I don't think this is true. Rewrite is before optimizer so it should be
optimized just the same.
I was speaking if the Rule has to do a
Josh Berkus wrote:
Bruce,
1) Rules can't use indexes to do their processing, so Rules which query
large
secondary tables can be a bad idea (maybe this has changed?)
I don't think this is true. Rewrite is before optimizer so it should be
optimized just the same.
I was
Bruce Momjian wrote:
3) There are no AFTER Rules, making, for example, a rule with a table
check on
the new data impractical, so you'd want to use Triggers or Constraints
We have changed ordering in 7.3 where I think INSERT rules are _after_
the insert.
How would that
Jerome Chochon wrote:
Thanks for your answer but i have another question.
Which one is the faster ?
If i write this trigger...
CREATE TRIGGER trigger_name
BEFORE DELETE
ON table_name
FOR EACH ROW EXECUTE PROCEDURE function();
and this rule:
CREATE RULE name_rule AS
ON DELETE TO
Hi all,
can you tell me in what order rules and triggers are executed?
First, what comes first, the rules, or the triggers?
And then, in what order are all the rules / triggers executed?
Regards,
Michael Paesold
---(end of broadcast)---
TIP 6:
them
in when you created them but I pulled that straight out of my arse. :)
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of Michael Paesold
Sent: Tuesday, September 10, 2002 1:53 PM
To: [EMAIL PROTECTED]
Subject: [SQL] Rules and Triggers
Hi all
Adam Erickson wrote:
Correct me if I'm wrong, but rules constrain the SQL (ie. validation).
Triggers are carried out after the SQL is executed and the data is
modified.
So, to answer your question, I think rules come first. As to which order
the rules / triggers are executed probably
On Tue, Sep 10, 2002 at 09:45:16PM +0200, Michael Paesold wrote:
Adam Erickson wrote:
Correct me if I'm wrong, but rules constrain the SQL (ie. validation).
Triggers are carried out after the SQL is executed and the data is
modified.
So, to answer your question, I think rules come
Hi,
I have the following things in my database:
CREATE SEQUENCE REO_ID_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647
CACHE 1;
CREATE TABLE reo (
REO_ID int4 DEFAULT nextval('REO_ID_seq'::text) NOT NULL,
TYPE varchar(64) NOT NULL,
CONSTRAINT REO_ID_reo_ukey UNIQUE (REO_ID),
Ahti Legonkov wrote:
Does anyone know why since postgres 7.2 the rules are executed *after*
the insert?
Because people where still complaining that they changed to execute
*before* in v6.4.
Jan
--
#==#
# It's easier to
Ahti Legonkov [EMAIL PROTECTED] writes:
CREATE TABLE reo (
REO_ID int4 DEFAULT nextval('REO_ID_seq'::text) NOT NULL,
TYPE varchar(64) NOT NULL,
CONSTRAINT REO_ID_reo_ukey UNIQUE (REO_ID),
CONSTRAINT reo_pkey PRIMARY KEY (REO_ID)
);
CREATE TABLE lreo (
CITY varchar(64),
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Well ... i'm testing carefully the two rules ==
CREATE RULE piezas_add AS
ON insert TO piezas
DO update materia_prima set usadas=(usadas+1)
where n_material=new.n_material;
CREATE RULE piezas_delete AS
ON delete
On Tue, May 22, 2001 at 10:44:06AM +0300, Mart?n Marqu?s wrote:
On Mar 22 May 2001 20:19, J.Fernando Moyano wrote:
Deleting is broken if it deletes more than one row. The rule is executed
only one time each delete command, and not one time each deleted row.
It's this OK ??? Am i wrong
Is it posible to make a rule execute more then one query?
Something like:
CREATE RULE rule_name AS ON INSERT TO table1
DO INSTEAD
INSERT INTO table2 VALUES
(new.value1,new.value2)
INSERT INTO table3 VALUES
(x,y)
If not, is there a way to do this? Triggers maybe?
Saludos... :-)
--
El mejor
Martín Marqués wrote:
Is it posible to make a rule execute more then one query?
Something like:
CREATE RULE rule_name AS ON INSERT TO table1
DO INSTEAD
INSERT INTO table2 VALUES
(new.value1,new.value2)
INSERT INTO table3 VALUES
(x,y)
Yes:
CREATE RULE rule_name AS ON INSERT
If I have a rule, is the rule inside a tranaction along with the table that
it references. For example, if I have a rule that deletes an entry from
table B, whenever an entry in table A gets deleted, then is the delete for
table A and table B wrapped inside the same transaction?
Same question I
Peeter Smitt wrote:
Hi
I'm trying to make updateable view useing rules.
CREATE RULE update_rule AS ON UPDATE TO table DO INSTEAD
SELECT fun1(new);
Thing is that backend gives this error.
ERROR: parser: parse error at or near ")"
What i'm doing wrong? Are there any other ways to
Tom Lane wrote:
Jan Wieck [EMAIL PROTECTED] writes:
Tom Lane wrote:
Hm. Perhaps the "cannot update view" test is too strict --- it's not
bright enough to realize that the two rules together cover all cases,
so it complains that you *might* be trying to update the view. As the
code
Tom Lane wrote:
"Joel Burton" [EMAIL PROTECTED] writes:
create rule dev_ins as on update to dev_col_comments where
old.description isnull do instead insert into pg_description ( objoid,
description) values (old.att_oid, new.description);
create rule dev_upd as on update to
"Joel Burton" [EMAIL PROTECTED] writes:
In any event, though, the rule above crashes my backend, as do
simpler versions I wrote that try your CREATE RULE DO INSTEAD (
INSERT; UPDATE; ) idea.
Ugh :-(
What information can I provide to the list to troubleshoot this?
A gdb backtrace from the
On Wednesday 29 November 2000 19:42, Tom Lane wrote:
Hm. Perhaps the "cannot update view" test is too strict --- it's not
bright enough to realize that the two rules together cover all cases,
so it complains that you *might* be trying to update the view. As the
code stands, you must
Mark Hollomon [EMAIL PROTECTED] writes:
On Wednesday 29 November 2000 19:42, Tom Lane wrote:
Hm. Perhaps the "cannot update view" test is too strict --- it's not
bright enough to realize that the two rules together cover all cases,
so it complains that you *might* be trying to update the
Am I misunderstanding how to use rule w/conditionals, or is there a
bug in this?
--
I love to use Pgsql comments, but find the 'comment on field...'
language a bit of a pain for documenting a large database at the
last minute. So, I wrote a query that pulls together all the fields in a
"Joel Burton" [EMAIL PROTECTED] writes:
create rule dev_ins as on update to dev_col_comments where
old.description isnull do instead insert into pg_description ( objoid,
description) values (old.att_oid, new.description);
create rule dev_upd as on update to dev_col_comments where
Mark Volpe [EMAIL PROTECTED] writes:
When I try this out, however, the rule seems to use the original
value, rather than the "corrected" value.
Queries added by non-INSTEAD rules are always performed before the
initially-given query, so you're right, the rule will see the unmodified
value.
The actual trigger function I'm working with is over two screens long and
rather expensive to be calling twice! Perhaps I need to add another trigger
that updates the log table with the correct values after the fact. Recursive
triggers, fun! Thanks for the help,
Mark
Ang Chin Han wrote:
On Wed, Aug 09, 2000 at 12:04:13PM -0400, Mark Volpe wrote:
I have a table with a trigger that can potentially modify a row before it gets
inserted or updated:
[snip]
I have another table that tracks changes in the first table with rules:
AFAIK, rules get rewritten first, before triggers
I'm doing this:
CREATE TABLE data ( val int4 );
CREATE TABLE pos ( ) INHERITS ( data );
CREATE TABLE neg ( ) INHERITS ( data );
CREATE RULE data_to_pos AS
ON INSERT TO data WHERE NEW.val 0
DO INSTEAD
INSERT INTO pos ( val ) VALUES ( NEW.val );
CREATE RULE data_to_neg AS
ON
Hello all,
Forgive my ignorance. I'm trying to understand the RULES in PostgreSQL and
I have several questions. Why the rules if they are not part of SQL92 or
SQL3? Where do they come from? Are they from before the SQL history of
Postgres? Many things that can be done with RULES can be done with
54 matches
Mail list logo
