commit callback, request

"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, event text, 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 where v = 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

=?ISO-8859-2?Q?Horv=E1th_S=E1ndor?= <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