Обсуждение: 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