Temp rows - is it possible?

Hello Dennis,

Friday, November 7, 2003, 1:29:32 PM, you wrote:

DG> Boris Popov wrote:

Hello pgsql-general,

I'm trying to implement a table with rows that are automatically
deleted when the session that inserted them disconnects, sort of like
our own alternative to pg_stat_activity. Is it possible and what
approach should I be trying to achieve such a thing?

DG> who do you want it visible to? If you don't want it visible to
DG> anybody but the session you are writing them from, just don't
DG> commit them and use the right kind of transaction that allows you
DG> to see them from the session you are in.

I do want them to be visible to everybody. This is a sessions pool,
where sessions are inserted when our app connects and removed when it
disconnects, however this would only work for graceful disconnects,
which we all know isn't always the case. So I want a table that is
somehow notified of a session disconnect and deletes rows created by
that session.

Any ideas?

--
-Boris

If the table doesn't have to be 100% accurate, you could always timestamp
the rows and have connected clients update their row, while old rows get
reaped periodicaly.

On Fri, 7 Nov 2003, Boris Popov wrote:

Hello Ben,

Friday, November 7, 2003, 2:53:09 PM, you wrote:

B> If the table doesn't have to be 100% accurate, you could always timestamp
B> the rows and have connected clients update their row, while old rows get
B> reaped periodicaly.

I was hoping for a more natural solution. Implementing a heartbeat in
the application is a complication I'd like to avoid at all cost.

-Boris

B> On Fri, 7 Nov 2003, Boris Popov wrote:

What you really want is an end of session callback.
There is not one in PostgreSQL. However, if this is
for session management, you can handle this in your
application by bracketing the connection code with
the table management.

That is, in your app (or rather in your session pooling
code) follow up each close with a DELETE of the rows
in question. The only tricky part is deciding on the
key so that it is known both before and after the connection.

Does this make sense?

elein

I found one way to do by combining temporary table and inhertis.
Temporary table will automatically dropped when disconnects, and
table can show inherited tables result, too.I assume SQL_Inheritance is
on.

Or you can use union too.

ex.

create table a(...);
insert into a(...); # fixed values

create table b() inherits (a);
insert into b values(...); # temporary values

select * from a; # You can get both global and temporary values.

On Fri, 07 Nov 2003 13:09:15 -0800
Boris Popov <boris@procedium.com> wrote:

Hello pgsql-general,

I'm trying to implement a table with rows that are automatically
deleted when the session that inserted them disconnects, sort of like
our own alternative to pg_stat_activity. Is it possible and what
approach should I be trying to achieve such a thing?

Thanks!

--
-Boris

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

--
TANIDA Yutaka <tanida@sra.co.jp>

This is great! I have been looking for this too... I think this should go in the manual as an example of how application sessions can be recorded in the db. Very useful!

/M

----- Original Message -----
From: "TANIDA Yutaka" <tanida@sra.co.jp>
To: "Boris Popov" <boris@procedium.com>
Cc: <pgsql-general@postgresql.org>
Sent: Monday, November 10, 2003 2:41 AM
Subject: Re: [GENERAL] Temp rows - is it possible?

"Mattias Kregert" <mattias@kregert.se> writes:

This is great!

create table a(...);
insert into a(...); # fixed values

create table b() inherits (a);
insert into b values(...); # temporary values

select * from a; # You can get both global and temporary values.

I don't think it's actually reliable. B was meant to be a temp table,
right? The problem is that B will be globally visible to all sessions
as being a child table of A, but because temp tables are processed in
backend-local buffers, it will be quite erratic whether other sessions
can see the rows you've inserted. In an experiment just now, another
session could not see the rows in B until I'd inserted several thousand
of them (enough to overrun the local buffers) ... and then the other
session could see some but not all of them.

We recently decided we had to forbid foreign-key references from temp
tables to permanent tables because of this effect. I wonder whether
we won't end up forbidding temp tables as children of permanent tables
too.

regards, tom lane

Tom Lane wrote:

"Mattias Kregert" <mattias@kregert.se> writes:

This is great!

create table a(...);
insert into a(...); # fixed values

create table b() inherits (a);
insert into b values(...); # temporary values

select * from a; # You can get both global and temporary values.

I don't think it's actually reliable. B was meant to be a temp table,
right? The problem is that B will be globally visible to all sessions
as being a child table of A, but because temp tables are processed in
backend-local buffers, it will be quite erratic whether other sessions
can see the rows you've inserted. In an experiment just now, another
session could not see the rows in B until I'd inserted several thousand
of them (enough to overrun the local buffers) ... and then the other
session could see some but not all of them.

We recently decided we had to forbid foreign-key references from temp
tables to permanent tables because of this effect. I wonder whether
we won't end up forbidding temp tables as children of permanent tables
too.

Yep, I think we will have to do that. TODO item?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Tom Lane wrote:

We recently decided we had to forbid foreign-key references from temp
tables to permanent tables because of this effect. I wonder whether
we won't end up forbidding temp tables as children of permanent tables
too.

Yep, I think we will have to do that. TODO item?

Plan B would be to arrange for the planner to ignore temp tables of
other backends whenever it is searching for child tables. Then the
behavior would be predictable: you never see any rows inserted in other
people's temp child tables (and cannot update or delete 'em, either).
I'm not sure if this is the behavior the OP wanted, but it seems at
least marginally useful.

regards, tom lane

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Tom Lane wrote:

We recently decided we had to forbid foreign-key references from temp
tables to permanent tables because of this effect. I wonder whether
we won't end up forbidding temp tables as children of permanent tables
too.

Yep, I think we will have to do that. TODO item?

Plan B would be to arrange for the planner to ignore temp tables of
other backends whenever it is searching for child tables. Then the
behavior would be predictable: you never see any rows inserted in other
people's temp child tables (and cannot update or delete 'em, either).
I'm not sure if this is the behavior the OP wanted, but it seems at
least marginally useful.

Agreed. It seems wrong that a session should ever see other people's
temp tables as children.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Hello Bruce,

Monday, November 10, 2003, 11:08:47 AM, you wrote:

BM> Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Tom Lane wrote:

We recently decided we had to forbid foreign-key references from temp
tables to permanent tables because of this effect. I wonder whether
we won't end up forbidding temp tables as children of permanent tables
too.

Yep, I think we will have to do that. TODO item?

Plan B would be to arrange for the planner to ignore temp tables of
other backends whenever it is searching for child tables. Then the
behavior would be predictable: you never see any rows inserted in other
people's temp child tables (and cannot update or delete 'em, either).
I'm not sure if this is the behavior the OP wanted, but it seems at
least marginally useful.

BM> Agreed. It seems wrong that a session should ever see other people's
BM> temp tables as children.

So going back to the original problem, do you think there should be a
way to implement temp rows in tables visible to everyone? I worked
around the original problem I had by using custom entries in
pg_listener (listen "identifier") and that works well because they
disappear as soon as backend detects the disconnect, but I'd really
like to be able to do exact same thing outside of pg_listener and be
able to reference that table from other permanent tables, which is
currently impossible with pg_listener as its a part of system catalog.

--
-Boris

Boris Popov wrote:

Hello Bruce,

Monday, November 10, 2003, 11:08:47 AM, you wrote:

BM> Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Tom Lane wrote:

We recently decided we had to forbid foreign-key references from temp
tables to permanent tables because of this effect. I wonder whether
we won't end up forbidding temp tables as children of permanent tables
too.

Yep, I think we will have to do that. TODO item?

Plan B would be to arrange for the planner to ignore temp tables of
other backends whenever it is searching for child tables. Then the
behavior would be predictable: you never see any rows inserted in other
people's temp child tables (and cannot update or delete 'em, either).
I'm not sure if this is the behavior the OP wanted, but it seems at
least marginally useful.

BM> Agreed. It seems wrong that a session should ever see other people's
BM> temp tables as children.

So going back to the original problem, do you think there should be a
way to implement temp rows in tables visible to everyone? I worked
around the original problem I had by using custom entries in
pg_listener (listen "identifier") and that works well because they
disappear as soon as backend detects the disconnect, but I'd really
like to be able to do exact same thing outside of pg_listener and be
able to reference that table from other permanent tables, which is
currently impossible with pg_listener as its a part of system catalog.

We have basically coupled "rows only exist during your session" and
"rows only visible to your session". I don't see much demand in
decoupling that, and I don't know a good way to do in application code
either. Sorry.

In your requested setup, once your session exists, all the session rows
disappear for everyone --- that seems to be a strange application
requirement.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

When grilled further on (Mon, 10 Nov 2003 09:39:32 -0500),
Tom Lane <tgl@sss.pgh.pa.us> confessed:

We recently decided we had to forbid foreign-key references from temp
tables to permanent tables because of this effect. I wonder whether
we won't end up forbidding temp tables as children of permanent tables
too.

Forbidding temp tables that inherit? That would suck (as someone who uses
them). Would there be an alternate method to easily create a temp table that is
identical to another?

Cheers,
Rob

--
13:44:43 up 101 days, 7:03, 5 users, load average: 3.37, 2.99, 2.55

Hello Bruce,

Monday, November 10, 2003, 12:43:29 PM, you wrote:

BM> Boris Popov wrote:

Hello Bruce,

Monday, November 10, 2003, 11:08:47 AM, you wrote:

BM> Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Tom Lane wrote:

We recently decided we had to forbid foreign-key references from temp
tables to permanent tables because of this effect. I wonder whether
we won't end up forbidding temp tables as children of permanent tables
too.

Yep, I think we will have to do that. TODO item?

Plan B would be to arrange for the planner to ignore temp tables of
other backends whenever it is searching for child tables. Then the
behavior would be predictable: you never see any rows inserted in other
people's temp child tables (and cannot update or delete 'em, either).
I'm not sure if this is the behavior the OP wanted, but it seems at
least marginally useful.

BM> Agreed. It seems wrong that a session should ever see other people's
BM> temp tables as children.

So going back to the original problem, do you think there should be a
way to implement temp rows in tables visible to everyone? I worked
around the original problem I had by using custom entries in
pg_listener (listen "identifier") and that works well because they
disappear as soon as backend detects the disconnect, but I'd really
like to be able to do exact same thing outside of pg_listener and be
able to reference that table from other permanent tables, which is
currently impossible with pg_listener as its a part of system catalog.

BM> We have basically coupled "rows only exist during your session" and
BM> "rows only visible to your session". I don't see much demand in
BM> decoupling that, and I don't know a good way to do in application code
BM> either. Sorry.

BM> In your requested setup, once your session exists, all the session rows
BM> disappear for everyone --- that seems to be a strange application
BM> requirement.

Imagine a table containing miscellaneous information about connected
clients. For instance I could have an app that does:

insert into sessions (ip_addr,client_version)
values ('192.168.0.33','1.0.1');

but lifetime of those rows has to correspond with lifetime of actual
connections, as soon as client disconnects (pulls the network cable or
crashes) that row should be cleaned up.

I can do (listen "session:192.168.0.33:1.0.1";) and then just parse
the relname from pg_listener to get the same effect, but you see why
I'd like a different solution?

--
-Boris

Boris Popov wrote:

BM> In your requested setup, once your session exists, all the session rows
BM> disappear for everyone --- that seems to be a strange application
BM> requirement.

Imagine a table containing miscellaneous information about connected
clients. For instance I could have an app that does:

insert into sessions (ip_addr,client_version)
values ('192.168.0.33','1.0.1');

but lifetime of those rows has to correspond with lifetime of actual
connections, as soon as client disconnects (pulls the network cable or
crashes) that row should be cleaned up.

I can do (listen "session:192.168.0.33:1.0.1";) and then just parse
the relname from pg_listener to get the same effect, but you see why
I'd like a different solution?

Yes, I can see that being useful --- but I doubt we are going to modify
the db system to enable behavior for this case unless we can do it in an
area that doesn't make the db less useful for more general purposes.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Mon, Nov 10, 2003 at 04:51:53PM -0500, Bruce Momjian wrote:

Boris Popov wrote:

I can do (listen "session:192.168.0.33:1.0.1";) and then just parse
the relname from pg_listener to get the same effect, but you see why
I'd like a different solution?

Yes, I can see that being useful --- but I doubt we are going to modify
the db system to enable behavior for this case unless we can do it in an
area that doesn't make the db less useful for more general purposes.

Probably having a disconnect callback could solve this problem.
This is not the first time someone asks for this feature.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"�Qu� importan los a�os? Lo que realmente importa es comprobar que
a fin de cuentas la mejor edad de la vida es estar vivo" (Mafalda)

Alvaro Herrera wrote:

On Mon, Nov 10, 2003 at 04:51:53PM -0500, Bruce Momjian wrote:

Boris Popov wrote:

I can do (listen "session:192.168.0.33:1.0.1";) and then just parse
the relname from pg_listener to get the same effect, but you see why
I'd like a different solution?

Yes, I can see that being useful --- but I doubt we are going to modify
the db system to enable behavior for this case unless we can do it in an
area that doesn't make the db less useful for more general purposes.

Probably having a disconnect callback could solve this problem.
This is not the first time someone asks for this feature.

Actually, a connect/disconnection function call would be best. Is this
a TODO?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tom,

On Mon, 10 Nov 2003 09:39:32 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:

select * from a; # You can get both global and temporary values.

I don't think it's actually reliable. B was meant to be a temp table,
right?

Ugh.... Yes.

create *temp* table b() inherits (a);

--
TANIDA Yutaka <tanida@sra.co.jp>

On Tuesday 11 November 2003 02:16, Robert Creager wrote:

When grilled further on (Mon, 10 Nov 2003 09:39:32 -0500),

Tom Lane <tgl@sss.pgh.pa.us> confessed:

We recently decided we had to forbid foreign-key references from temp
tables to permanent tables because of this effect. I wonder whether
we won't end up forbidding temp tables as children of permanent tables
too.

Forbidding temp tables that inherit? That would suck (as someone who uses
them). Would there be an alternate method to easily create a temp table
that is identical to another?

You can use LIKE clause in create table.

See http://developer.postgresql.org/docs/postgres/sql-createtable.html

HTH

Shridhar