Re: [GENERAL] Rule Question
On Wed, Jul 24, 2013 at 11:44 PM, Andrew Bartley ambart...@gmail.com wrote: Hope this question is not too stupid but.. I am trying to do something like this create table cats (a text,b text); create rule cats_test as on update to cats do set a = new.b; Can i manipulate column a sort of like this... or is there a better way. I would like to do this as the construction of the new.b value is complex and time consuming, so I just want to do it once. update cats set b = something_complex_and_time_consuming(b); AFAIK, the best way here is to use trigger that does new.a = new.b. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA Profile: http://www.linkedin.com/in/grayhemp Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979 Skype: gray-hemp Jabber: gray...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rule Question
On Thu, Jul 25, 2013 at 8:44 AM, Andrew Bartley ambart...@gmail.com wrote: create rule cats_test as on update to cats do set a = new.b; I would use a column trigger attached to the 'a' column. Rules are better for query rewriting rather than from semantic changes. That's my opinion. Luca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rule Question
I am trying to do something like this
create table cats (a text,b text);
create rule cats_test as on update to cats do set a = new.b;
Can i manipulate column a sort of like this... or is there a
better way.
I think the easiest way to do this is to use a trigger like this:
CREATE FUNCTION update_column() RETURNS TRIGGER AS $update_column$
BEGIN
IF TG_OP = 'INSERT' OR
(TG_OP = 'UPDATE' AND
(NEW.b != OLD.b OR
(NEW.b IS NULL AND OLD.b IS NOT NULL) OR
(NEW.b IS NOT NULL AND OLD.b IS NULL)
)
) THEN
NEW.a = NEW.b;
END IF;
RETURN NEW;
END;
$update_column$ LANGUAGE plpgsql;
CREATE TRIGGER update_column BEFORE INSERT OR UPDATE ON cats
FOR EACH ROW
EXECUTE PROCEDURE update_column();
So for instance, if you insert a new column b value
INSERT INTO cats (b) VALUES ('byebye');
you'll get a='byebye' and b='byebye', and if you update this value
UPDATE cats SET b = 'goodbye' WHERE a = 'byebye';
you'll get a='goodbye' and b='goodbye'. Anyway, this is just an example.
I suggest that you look at the CREATE TRIGGER page in the documentation
http://www.postgresql.org/docs/9.2/static/sql-createtrigger.html
as you can also consider conditional triggers to be executed, for
example, only when the b column is updated.
Hope it can help.
Giuseppe.
--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe.brocc...@2ndquadrant.it | www.2ndQuadrant.it
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rule Question
The original post was related to the update of b, so I guess it is
better to limit the trigger scope to update on such column:
CREATE OR REPLACE FUNCTION b_mirror() RETURNS TRIGGER AS
$mirror$
BEGIN
NEW.a = NEW.b;
RETURN NEW;
END;
$mirror$ LANGUAGE plpgsql;
CREATE TRIGGER tr_b_mirror AFTER UPDATE OF b ON cats FOR EACH ROW
EXECUTE PROCEDURE b_mirror();
It is worth noting that the trigger could be an after one, since if I
get it right the tuple has to be always updated, and there is no
condition that prevents that. Moreover, it is possible to check for
null values as in the suggested example below.
Luca
On Thu, Jul 25, 2013 at 1:18 PM, Giuseppe Broccolo
giuseppe.brocc...@2ndquadrant.it wrote:
I am trying to do something like this
create table cats (a text,b text);
create rule cats_test as on update to cats do set a = new.b;
Can i manipulate column a sort of like this... or is there a better
way.
I think the easiest way to do this is to use a trigger like this:
CREATE FUNCTION update_column() RETURNS TRIGGER AS $update_column$
BEGIN
IF TG_OP = 'INSERT' OR
(TG_OP = 'UPDATE' AND
(NEW.b != OLD.b OR
(NEW.b IS NULL AND OLD.b IS NOT NULL) OR
(NEW.b IS NOT NULL AND OLD.b IS NULL)
)
) THEN
NEW.a = NEW.b;
END IF;
RETURN NEW;
END;
$update_column$ LANGUAGE plpgsql;
CREATE TRIGGER update_column BEFORE INSERT OR UPDATE ON cats
FOR EACH ROW
EXECUTE PROCEDURE update_column();
So for instance, if you insert a new column b value
INSERT INTO cats (b) VALUES ('byebye');
you'll get a='byebye' and b='byebye', and if you update this value
UPDATE cats SET b = 'goodbye' WHERE a = 'byebye';
you'll get a='goodbye' and b='goodbye'. Anyway, this is just an example. I
suggest that you look at the CREATE TRIGGER page in the documentation
http://www.postgresql.org/docs/9.2/static/sql-createtrigger.html
as you can also consider conditional triggers to be executed, for example,
only when the b column is updated.
Hope it can help.
Giuseppe.
--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe.brocc...@2ndquadrant.it | www.2ndQuadrant.it
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rule Question
Luca Ferrari fluca1...@infinito.it writes: The original post was related to the update of b, so I guess it is better to limit the trigger scope to update on such column: CREATE OR REPLACE FUNCTION b_mirror() RETURNS TRIGGER AS $mirror$ BEGIN NEW.a = NEW.b; RETURN NEW; END; $mirror$ LANGUAGE plpgsql; CREATE TRIGGER tr_b_mirror AFTER UPDATE OF b ON cats FOR EACH ROW EXECUTE PROCEDURE b_mirror(); It is worth noting that the trigger could be an after one, No, it has to be a BEFORE trigger, else it's too late to affect the row value that gets stored. Other than that I think this is the best solution --- there's no reason to make the trigger any more complicated than this. BTW, I didn't see anyone pointing out the real reason why a rule isn't a suitable solution for the OP's problem. Namely, that a rule is a macro, so if you have create rule cats_test as on update to cats do set a = new.b; the effect of that will be that the *expression* for the new value of b will be inserted into the rule. So you'll end up with double evaluation of that expression, exactly what he wanted to avoid. A trigger is handed the fully calculated intended-new-row value, so it doesn't have this issue. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rule Question
On Thu, Jul 25, 2013 at 3:02 PM, Tom Lane t...@sss.pgh.pa.us wrote: Luca Ferrari fluca1...@infinito.it writes: The original post was related to the update of b, so I guess it is better to limit the trigger scope to update on such column: CREATE OR REPLACE FUNCTION b_mirror() RETURNS TRIGGER AS $mirror$ BEGIN NEW.a = NEW.b; RETURN NEW; END; $mirror$ LANGUAGE plpgsql; CREATE TRIGGER tr_b_mirror AFTER UPDATE OF b ON cats FOR EACH ROW EXECUTE PROCEDURE b_mirror(); It is worth noting that the trigger could be an after one, No, it has to be a BEFORE trigger, else it's too late to affect the row value that gets stored. Ops..I wrote it without my brain with me: of course it has to be a before one. Thanks, Luca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rule Question
On Thu, Jul 25, 2013 at 4:18 AM, Giuseppe Broccolo giuseppe.brocc...@2ndquadrant.it wrote: (TG_OP = 'UPDATE' AND (NEW.b != OLD.b OR (NEW.b IS NULL AND OLD.b IS NOT NULL) OR (NEW.b IS NOT NULL AND OLD.b IS NULL) ) ) THEN Unrelated to the OP's question, the suggestion above could be more simply rewritten as TG_OP = 'UPDATE' AND NEW.b IS DISTINCT FROM OLD.b
Re: [GENERAL] Rule Question
Unrelated to the OP's question, the suggestion above could be more simply rewritten as TG_OP = 'UPDATE' AND NEW.b IS DISTINCT FROM OLD.b You're right! :) Giuseppe. -- Giuseppe Broccolo - 2ndQuadrant Italy PostgreSQL Training, Services and Support giuseppe.brocc...@2ndquadrant.it | www.2ndQuadrant.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rule Question
Thanks All, And thanks Tom, I did not realise a rule worked in that manner. Will now take that into account in the future. Thanks Andrew On 26 July 2013 02:02, Giuseppe Broccolo giuseppe.brocc...@2ndquadrant.itwrote: Unrelated to the OP's question, the suggestion above could be more simply rewritten as TG_OP = 'UPDATE' AND NEW.b IS DISTINCT FROM OLD.b You're right! :) Giuseppe. -- Giuseppe Broccolo - 2ndQuadrant Italy PostgreSQL Training, Services and Support giuseppe.broccolo@2ndQuadrant.**it | www.2ndQuadrant.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/**mailpref/pgsql-generalhttp://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rule question
Klint Gore wrote: [see below or the top posting police will arrive on my doorstep :)] Devi wrote: Hi, CREATE RULE dosen't require any lock. It is carried out in the parser level. But there will be ACCESS SHARE lock over the tables which are being queried are acquired automatically. Thanks DEVI.G - Original Message - From: Tim Rupp [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: Friday, February 29, 2008 8:47 AM Subject: [GENERAL] rule question Hey list, Does CREATE RULE require an exclusive lock on the table it's making a rule for? For instance, if an insert is being done on the table, and you do 'create rule', it will wait for said insert to finish? Thanks, -Tim Seems to me like needs an exclusive lock. I setup 2 sessions. first one idle in transaction after an insert and then issued the create rule in the other. the 2nd one sat there. pg_locks in the 1st one said # select * from pg_locks where relation = 20404; locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid |mode | granted --+--+--+--+---++---+-+---+--++--+-+ - relation |16770 |20404 | | | | | | | | 1/921 | 632 | RowExclusiveLock| t relation |16770 |20404 | | | | | | | | 2/771 | 3812 | AccessExclusiveLock | f (2 rows) (ignore the formatting the important bit is pid, mode, granted) 3812 is the pid of my create rule according to pg_backend_pid() and 632 is my insert transaction. Execution of the rule follows what you were saying. klint. Thanks for the info guys, I'll use it to observe my own setup here. One other question. If the lock needed is exclusive, and more inserts come in after it is requested, will Postgres schedule the rule to be created before those new inserts are allowed to happen? Or can the rule request sit there and wait indefinitely for it's exclusive lock. Thanks! -Tim ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] rule question
On Fri, Feb 29, 2008 at 4:08 AM, Tim Rupp [EMAIL PROTECTED] wrote: One other question. If the lock needed is exclusive, and more inserts come in after it is requested, will Postgres schedule the rule to be created before those new inserts are allowed to happen? Or can the rule request sit there and wait indefinitely for it's exclusive lock. PostgreSQL will process the transactions in order. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] rule question
Hi, CREATE RULE dosen't require any lock. It is carried out in the parser level. But there will be ACCESS SHARE lock over the tables which are being queried are acquired automatically. Thanks DEVI.G - Original Message - From: Tim Rupp [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: Friday, February 29, 2008 8:47 AM Subject: [GENERAL] rule question Hey list, Does CREATE RULE require an exclusive lock on the table it's making a rule for? For instance, if an insert is being done on the table, and you do 'create rule', it will wait for said insert to finish? Thanks, -Tim ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.516 / Virus Database: 269.21.1/1302 - Release Date: 2/27/2008 4:34 PM ---(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: [GENERAL] rule question
[see below or the top posting police will arrive on my doorstep :)] Devi wrote: Hi, CREATE RULE dosen't require any lock. It is carried out in the parser level. But there will be ACCESS SHARE lock over the tables which are being queried are acquired automatically. Thanks DEVI.G - Original Message - From: Tim Rupp [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: Friday, February 29, 2008 8:47 AM Subject: [GENERAL] rule question Hey list, Does CREATE RULE require an exclusive lock on the table it's making a rule for? For instance, if an insert is being done on the table, and you do 'create rule', it will wait for said insert to finish? Thanks, -Tim Seems to me like needs an exclusive lock. I setup 2 sessions. first one idle in transaction after an insert and then issued the create rule in the other. the 2nd one sat there. pg_locks in the 1st one said # select * from pg_locks where relation = 20404; locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid |mode | granted --+--+--+--+---++---+-+---+--++--+-+ - relation |16770 |20404 | | | | | | | | 1/921 | 632 | RowExclusiveLock| t relation |16770 |20404 | | | | | | | | 2/771 | 3812 | AccessExclusiveLock | f (2 rows) (ignore the formatting the important bit is pid, mode, granted) 3812 is the pid of my create rule according to pg_backend_pid() and 632 is my insert transaction. Execution of the rule follows what you were saying. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
