Re: System triggers

Hi,

I was going thr. a oracle refresher course today. I came across this concept
of system triggers which can be installed on things like database/connection
startup.

I think it would be a good addition to psotgresql. I was planning to hack
dblink for multiple named persistent connections. ( HI Joe, I am still
here..:-))) In that case, it would be a very easy thing to do to open a
remote database connection whenever a new connection is opened to database.
This way remote database would be totally transparent to end user..

Can this be taken for future versions?

Shridhar

Shridhar Daithankar <shridhar_daithankar(at)persistent(dot)co(dot)in> writes:

> Hi,
>
> I was going thr. a oracle refresher course today. I came across this concept
> of system triggers which can be installed on things like database/connection
> startup.
>
> I think it would be a good addition to psotgresql. I was planning to hack
> dblink for multiple named persistent connections. ( HI Joe, I am still
> here..:-))) In that case, it would be a very easy thing to do to open a
> remote database connection whenever a new connection is opened to database.
> This way remote database would be totally transparent to end user..

IMHO, and I'm not a core developer, there's no need for "database
startup triggers"--whatever process starts the database can run an SQL
script at that time.

"Connection startup triggers" might be useful, and there's no good way
of doing them outside the database unless you wire it into every
client app, which kind of defeats the purpose...

> Can this be taken for future versions?

I'm sure your patches would be considered for inclusion. :)

-Doug

On Friday 16 May 2003 22:26, Doug McNaught wrote:
> "Connection startup triggers" might be useful, and there's no good way
> of doing them outside the database unless you wire it into every
> client app, which kind of defeats the purpose...

Umm.. As far as I know, if FE-BE protocol provides support for connection
level triggers and libpq implements it, almost every client access library
would get them automagically..

Did I misunderstand something?

Shridhar

Shridhar Daithankar <shridhar_daithankar(at)persistent(dot)co(dot)in> writes:

> On Friday 16 May 2003 22:26, Doug McNaught wrote:
> > "Connection startup triggers" might be useful, and there's no good way
> > of doing them outside the database unless you wire it into every
> > client app, which kind of defeats the purpose...
>
> Umm.. As far as I know, if FE-BE protocol provides support for connection
> level triggers and libpq implements it, almost every client access library
> would get them automagically..

I don't see that the FE/BE protocol has anything to do with it. When
a new backend starts up, it would check pg_trigger for any connection
startup triggers and execute them--the client wouldn't know anything.

Of course, what happens to the output (if any) from those triggers,
given that the client probably doesn't expect it, is an interesting
question...

-Doug

>>>>> "Doug" == Doug McNaught <doug(at)mcnaught(dot)org> writes:

Doug> Of course, what happens to the output (if any) from those
Doug> triggers, given that the client probably doesn't expect it,
Doug> is an interesting question...

What are the actions of pgsql triggers ? I would have thought they
result in Update/Delete/Insert operations on other tables. Why should
that result in output for any client ?

--
Pip-pip
Sailesh
http://www.cs.berkeley.edu/~sailesh

Sailesh Krishnamurthy <sailesh(at)cs(dot)berkeley(dot)edu> writes:

> >>>>> "Doug" == Doug McNaught <doug(at)mcnaught(dot)org> writes:
>
> Doug> Of course, what happens to the output (if any) from those
> Doug> triggers, given that the client probably doesn't expect it,
> Doug> is an interesting question...
>
> What are the actions of pgsql triggers ? I would have thought they
> result in Update/Delete/Insert operations on other tables. Why should
> that result in output for any client ?

They still (IIRC) return results (like 'INSERT 1 10002') to the
client end of the connection, and there's no a-priori reason they
couldn't do SELECTS as well...

-Doug

Doug McNaught wrote:
> Sailesh Krishnamurthy <sailesh(at)cs(dot)berkeley(dot)edu> writes:
>
>
>>>>>>>"Doug" == Doug McNaught <doug(at)mcnaught(dot)org> writes:
>>>>>>
>> Doug> Of course, what happens to the output (if any) from those
>> Doug> triggers, given that the client probably doesn't expect it,
>> Doug> is an interesting question...
>>
>>What are the actions of pgsql triggers ? I would have thought they
>>result in Update/Delete/Insert operations on other tables. Why should
>>that result in output for any client ?
>
>
> They still (IIRC) return results (like 'INSERT 1 10002') to the
> client end of the connection, and there's no a-priori reason they
> couldn't do SELECTS as well...

They don't, I guess you mix something here with rules or INSERT INTO.

Triggers are fired during or after INSERT, UPDATE and DELETE operations
(so far). Other than reducing the number of tuples affected by returning
NULL in a BEFORE trigger, they do not change the response to the client
at all. They can cause NOTIFY or ERROR messages to be added to the
client communication stream, but that's something to expect at any time.
No trigger will cause an INSERT all of the sudden to return result rows.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:

> Doug McNaught wrote:

> > They still (IIRC) return results (like 'INSERT 1 10002') to the
> > client end of the connection, and there's no a-priori reason they
> > couldn't do SELECTS as well...
>
> They don't, I guess you mix something here with rules or INSERT INTO.

Could be--thanks for the explanation.

-Doug