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

Fri, 16 May 2003 19:45:36 +0530
Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
System triggers
Shridhar Daithankar <shridhar_daithankar@persistent.co.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

16 May 2003 12:56:39 -0400
Doug McNaught <doug@mcnaught.org>
Re: System triggers
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

Fri, 16 May 2003 22:42:35 +0530
Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
Re: System triggers
Shridhar Daithankar <shridhar_daithankar@persistent.co.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

16 May 2003 13:56:55 -0400
Doug McNaught <doug@mcnaught.org>
Re: System triggers
>>>>> "Doug" == Doug McNaught <doug@mcnaught.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


16 May 2003 11:01:48 -0700
Sailesh Krishnamurthy <sailesh@cs.berkeley.edu>
Re: System triggers
Sailesh Krishnamurthy <sailesh@cs.berkeley.edu> writes:

> >>>>> "Doug" == Doug McNaught <doug@mcnaught.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

16 May 2003 14:11:24 -0400
Doug McNaught <doug@mcnaught.org>
Re: System triggers
Doug McNaught wrote:
> Sailesh Krishnamurthy <sailesh@cs.berkeley.edu> writes:
> 
> 
>>>>>>>"Doug" == Doug McNaught <doug@mcnaught.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@Yahoo.com #


Tue, 20 May 2003 06:50:07 -0400
Jan Wieck <JanWieck@Yahoo.com>
Re: System triggers
Jan Wieck <JanWieck@Yahoo.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

20 May 2003 11:02:29 -0400
Doug McNaught <doug@mcnaught.org>
Re: System triggers