Thread: commit callback, request
Hello Is possible make transaction commit trigger without patching code now? I finding way , but all usable interfaces are static. I remember on diskussion about it and about changes in LISTEN/NOTIFY implementation. Is there any progress? I need it for simulation of Oracle dbms_alert.signal function. Whole dbms_alert package is similar our LISTEN/NOTIFY. Difference is dbms_alert is server side solution and L/N is client side. Is any chance so this interface will be in 8.2? Regards Pavel Stehule _________________________________________________________________ Don�t just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/
"Pavel Stehule" <pavel.stehule@hotmail.com> writes:
> Is possible make transaction commit trigger without patching code now?
You can get pretty close with a deferred trigger. I don't think
there's any way to have a guaranteed "at commit" trigger --- as soon
as (1) there are two of them and (2) one can get an error, the
transaction could fail after running an alleged "at commit" trigger.
regards, tom lane
>"Pavel Stehule" <pavel.stehule@hotmail.com> writes: > > Is possible make transaction commit trigger without patching code now? > >You can get pretty close with a deferred trigger. I don't think >there's any way to have a guaranteed "at commit" trigger --- as soon >as (1) there are two of them and (2) one can get an error, the >transaction could fail after running an alleged "at commit" trigger. > > regards, tom lane hm. I don't have big problem with false notifications. Who want to use dbms_alert have to calculate with this possibility. But triggers has disadventage - I have to sometimes clean any table, which cary triggers :-(. It's solution. I hope 8.2 will has any general mechanis. Is it possible enhance SPI to parametrized NOTIFY? Default mode send message via libpq, nonstandard raise any callback. Thank You Pavel _________________________________________________________________ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/
Refered triggers works well, better than I expected. It's not equal NOTIFY,
but it works.
Thank You
Pavel Stehule
CREATE OR REPLACE FUNCTION dbms_alert._defered_signal() RETURNS trigger AS
$$
BEGIN PERFORM dbms_alert._signal(NEW.event, NEW.message); DELETE FROM ora_alerts WHERE id=NEW.id; RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER VOLATILE;
CREATE OR REPLACE FUNCTION dbms_alert.signal(_event text, _message text)
RETURNS void AS $$
BEGIN PERFORM 1 FROM pg_catalog.pg_class c WHERE pg_catalog.pg_table_is_visible(c.oid) AND
c.relkind='r'AND c.relname = 'ora_alerts'; IF NOT FOUND THEN CREATE TEMP TABLE ora_alerts(id serial PRIMARY KEY,
eventtext, message
text); REVOKE ALL ON TABLE ora_alerts FROM PUBLIC; CREATE CONSTRAINT TRIGGER ora_alert_signal AFTER INSERT ON
ora_alerts INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE
dbms_alert._defered_signal(); END IF; INSERT INTO ora_alerts(event, message) VALUES(_event, _message);
END;
$$ LANGUAGE plpgsql VOLATILE STRICT SECURITY DEFINER;
drop table test_alert cascade;
create table test_alert(v varchar);
create or replace function checkdata() returns void as $$
declare r record; d record;
begin perform dbms_alert.register('refresh'); while true loop select into r * from
dbms_alert.waitone('refresh',100000); perform pg_sleep(0.1); -- I need wait moment select into d * from test_alert
wherev = r.message; raise notice 'found %', d; end loop;
end;
$$ language plpgsql;
create or replace function ins(varchar) returns void as $$
begin insert into test_alert values($1); perform dbms_alert.signal('refresh',$1);
end;
$$ language plpgsql;
_________________________________________________________________
Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/
The only solution I know if is this patch: http://gorda.di.uminho.pt/community/pgsqlhooks/ Chris Pavel Stehule wrote: > Hello > > Is possible make transaction commit trigger without patching code now? I > finding way , but all usable interfaces are static. I remember on > diskussion about it and about changes in LISTEN/NOTIFY implementation. > Is there any progress? > > I need it for simulation of Oracle dbms_alert.signal function. Whole > dbms_alert package is similar our LISTEN/NOTIFY. Difference is > dbms_alert is server side solution and L/N is client side. Is any > chance so this interface will be in 8.2? > > Regards > Pavel Stehule > > _________________________________________________________________ > Don’t just search. Find. Check out the new MSN Search! > http://search.msn.click-url.com/go/onm00200636ave/direct/01/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster -- Christopher Kings-Lynne Technical Manager CalorieKing Tel: +618.9389.8777 Fax: +618.9389.8444 chris.kings-lynne@calorieking.com www.calorieking.com
Hi! I have needed deferrable check, but it is not implemented. I thought, the deferrable trigger is the solution, but it is exists only for referential integrity. I have started to study the postgresql source code for implementing deferrable triggers for any purpose. Now i try the constraint trigger, and it is good for using instead of deferrable check, but it is not intended general use. In the documentation: "CREATE CONSTRAINT TRIGGER is used within CREATE TABLE/ALTER TABLE and by pg_dump to create the special triggers for referential integrity. It is not intended for general use." My question is, may i use it for this purpose (instead of deferrable check)? What means "it is not intended for generally use"? Regards Horváth Sándor
Horváth Sándor <horvath.sandor@ritek.hu> writes:
> In the documentation:
> "CREATE CONSTRAINT TRIGGER is used within CREATE TABLE/ALTER TABLE and
> by pg_dump to create the special triggers for referential integrity. It
> is not intended for general use."
> What means "it is not intended for generally use"?
What it really means is that we don't promise to keep this compatible in
the future. There will probably always be something similar, but you
might have to change your application to use it.
regards, tom lane