[SQL] on connect/on disconnect

[Robert Edwards]

(this is my first post to this list...)

I am wondering if Postgres, and/or SQL in general, has a facility to
run a function at connection set-up time (after a successful connection
attempt) and/or at session completion (or disconnect)?

I want to pre-populate a table (actually an INSERT rule on a view)
with some user-specific data that is unlikely to change during the
session and which is "difficult" to process (ie. affects performance
to do it too often).

Clearly, I can do this manually anyway as the first operation after
a connection is established, but I would like also to clear it out
when the session terminates (so, I guess I am really interested in
a "trigger" of some sort on end-of-session).

Purely session/connection-based temporary tables would also do what
I need, but temporary tables don't seem to be able to work that way.

Cheers,

Bob Edwards.

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] on connect/on disconnect

[Aaron Bono]

On 9/10/06, Robert Edwards <[EMAIL PROTECTED]> wrote:
(this is my first post to this list...)I am wondering if Postgres, and/or SQL in general, has a facility torun a function at connection set-up time (after a successful connectionattempt) and/or at session completion (or disconnect)?
I want to pre-populate a table (actually an INSERT rule on a view)with some user-specific data that is unlikely to change during thesession and which is "difficult" to process (ie. affects performance
to do it too often).Clearly, I can do this manually anyway as the first operation aftera connection is established, but I would like also to clear it outwhen the session terminates (so, I guess I am really interested in
a "trigger" of some sort on end-of-session).Purely session/connection-based temporary tables would also do whatI need, but temporary tables don't seem to be able to work that way.
 What kind of operation are you wanting to do?  Would it work if an application like a web site used connection pooling - thus sharing the session across application users and rarely if ever connecting/disconnecting?
==   Aaron Bono   Aranya Software Technologies, Inc.   http://www.aranya.com   
http://codeelixir.com==

[SQL] where clause subqueries vs multiple rows results

[James Cloos]

I've a query which I'd have liked to word akin to:

  SELECT guid FROM child WHERE the_fkey =
 ( SELECT id FROM parent WHERE name ~ 'some_regex' )
 ORDER BY the_fkey, my_pkey;

I got around it by doing the SELECT id first, and then doing a SELECT
guid for each row returned, appending the results together.

Can that be done in a single query, insead of 1+n queries?

Thanks,

-JimC
-- 
James Cloos <[EMAIL PROTECTED]> OpenPGP: 0xED7DAEA6

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] on connect/on disconnect

[Robert Edwards]

Hi Aaron,

Thanks for your response. I guess I wasn't clear about "users". I am
referring to database users, not application users (although they
are the same in my application in any case - that is, each application
user is known to the database as a different database user).

As I understand it, connection pooling, as used by a web app, still
needs to connect "per-database-user" (and usually this is the same
"web-server" user). If the web app is connecting to the database
server as different database users, then different connections would
be set up. If the number of open connections exceeds the number
allowed to the database server, then older unused connections would
be terminated to allow new ones to be created. Is this correct?

Or is it possible, over the same connection, to change the database
user? My understanding of the frontend/backend protocol is that this
is not allowed.

Anyway, I still need to know if running functions during connection
setup and tear-down, or change of user, is possible or not.

Cheers,

Bob Edwards.

Aaron Bono wrote:
On 9/10/06, *Robert Edwards* <[EMAIL PROTECTED] 
> wrote:


(this is my first post to this list...)

I am wondering if Postgres, and/or SQL in general, has a facility to
run a function at connection set-up time (after a successful connection
attempt) and/or at session completion (or disconnect)?

I want to pre-populate a table (actually an INSERT rule on a view)
with some user-specific data that is unlikely to change during the
session and which is "difficult" to process (ie. affects performance
to do it too often).

Clearly, I can do this manually anyway as the first operation after
a connection is established, but I would like also to clear it out
when the session terminates (so, I guess I am really interested in
a "trigger" of some sort on end-of-session).

Purely session/connection-based temporary tables would also do what
I need, but temporary tables don't seem to be able to work that way.


 
What kind of operation are you wanting to do?  Would it work if an 
application like a web site used connection pooling - thus sharing the 
session across application users and rarely if ever 
connecting/disconnecting?


==
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
==



---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] where clause subqueries vs multiple rows results

[Aaron Bono]

On 9/10/06, James Cloos <[EMAIL PROTECTED]> wrote:
I've a query which I'd have liked to word akin to:  SELECT guid FROM child WHERE the_fkey = ( SELECT id FROM parent WHERE name ~ 'some_regex' ) ORDER BY the_fkey, my_pkey;I got around it by doing the SELECT id first, and then doing a SELECT
guid for each row returned, appending the results together.Can that be done in a single query, insead of 1+n queries? Will this work?SELECT guid FROM child INNER JOIN parent ON (
    child.the_fkey = parent.id    AND )WHERE name ~ 'some_regex'ORDER BY     child.the_fkey,     child.my_pkey;I am not sure what table name is from but since you say it is n+1 queries I assume it is from the child table?  It would help to know what columns are on which tables.
==   Aaron Bono   Aranya Software Technologies, Inc.   http://www.aranya.com   
http://codeelixir.com==

Re: [SQL] where clause subqueries vs multiple rows results

[Aaron Bono]

Oops, see correction below...On 9/10/06, Aaron Bono <[EMAIL PROTECTED]> wrote:
On 9/10/06, James Cloos <[EMAIL PROTECTED]> wrote:

I've a query which I'd have liked to word akin to:  SELECT guid FROM child WHERE the_fkey = ( SELECT id FROM parent WHERE name ~ 'some_regex' ) ORDER BY the_fkey, my_pkey;I got around it by doing the SELECT id first, and then doing a SELECT
guid for each row returned, appending the results together.Can that be done in a single query, insead of 1+n queries? Will this work?SELECT guid FROM child 
INNER JOIN parent ON (
    child.the_fkey = parent.id)WHERE name ~ 'some_regex'ORDER BY 
    child.the_fkey,     child.my_pkey;I am not sure what table name is from but since you say it is n+1 queries I assume it is from the child table?  It would help to know what columns are on which tables.
==   Aaron Bono   Aranya Software Technologies, Inc.   
http://www.aranya.com   
http://codeelixir.com==

-- ==   Aaron Bono   Aranya Software Technologies, Inc.   
http://www.aranya.com   http://codeelixir.com==

Re: [SQL] where clause subqueries vs multiple rows results

[Richard Broersma Jr]

> I've a query which I'd have liked to word akin to:
> 
>   SELECT guid FROM child WHERE the_fkey =
>  ( SELECT id FROM parent WHERE name ~ 'some_regex' )
>  ORDER BY the_fkey, my_pkey;
> 
> I got around it by doing the SELECT id first, and then doing a SELECT
> guid for each row returned, appending the results together.
> 
> Can that be done in a single query, insead of 1+n queries?

select guid 
from child C join parent P
on (C.the_fkey = P.di)
Where P.name ~ 'some_regex'
order by C.the_fkey, P.my_pkey;

Perhaps this might work.

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [SQL] on connect/on disconnect

[Aaron Bono]

On 9/10/06, Robert Edwards <[EMAIL PROTECTED]> wrote:
As I understand it, connection pooling, as used by a web app, stillneeds to connect "per-database-user" (and usually this is the same"web-server" user). If the web app is connecting to the database
server as different database users, then different connections wouldbe set up. If the number of open connections exceeds the numberallowed to the database server, then older unused connections wouldbe terminated to allow new ones to be created. Is this correct?
Don't mean to get off topic but to answer your question...Typically the web app has a single log in user.  You don't  have the user log into the database.  Connection pooling typically does not terminate connections to the database unless it decides to  shrink the connection pool do to lack of use.  It doesn't disconnect when it exceeds the number of allowed users - it reuses the connections as much as possible to reduce the overhead of making connections.
Or is it possible, over the same connection, to change the databaseuser? My understanding of the frontend/backend protocol is that this
is not allowed.I could not find this in the documentation - not sure.
Anyway, I still need to know if running functions during connectionsetup and tear-down, or change of user, is possible or not. If there is a table that keeps track of connection/disconnection you
could put a trigger on that but I cannot find anything about this in the documentation.Guess we will need feedback from someone more knowledgable than I... ==
   Aaron Bono   Aranya Software Technologies, Inc.   http://www.aranya.com   http://codeelixir.com==