Temp rows - is it possible?

[Boris Popov]

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_activit y. 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

[Boris Popov]

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_activit y. 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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Ben]

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:

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?

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Boris Popov]

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:

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?

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

[elein]

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
On Fri, Nov 07, 2003 at 01:09:15PM -0800, 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_activit y. 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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[TANIDA Yutaka]

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 <bo***@procediu m.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_activit y. 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 <ta****@sra.co. jp>
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Mattias Kregert]

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" <ta****@sra.co. jp>
To: "Boris Popov" <bo***@procediu m.com>
Cc: <pg***********@ postgresql.org>
Sent: Monday, November 10, 2003 2:41 AM
Subject: Re: [GENERAL] Temp rows - is it possible?

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 <bo***@procediu m.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_activit y. 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 <ta****@sra.co. jp>


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Tom Lane]

"Mattias Kregert" <ma*****@kreger t.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

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

[Bruce Momjian]

Tom Lane wrote:

"Mattias Kregert" <ma*****@kreger t.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
pg***@candle.ph a.pa.us | [phone]
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Tom Lane]

Bruce Momjian <pg***@candle.p ha.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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly