Thread: Temp rows - is it possible?
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
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: > 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?
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?
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_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
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 applicationsessions 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? > 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> > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
"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
Attachment
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
When grilled further on (Tue, 11 Nov 2003 11:39:02 +0530), Shridhar Daithankar <shridhar_daithankar@myrealbox.com> confessed: > > 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 > Thanks Shridhar, I didn't know there was a LIKE clause, and it's even right above INHERITS in the docs... I also didn't realize that the parent table would see all the child tables data when using INHERITS. I should be using LIKE... Cheers, Rob -- 09:58:11 up 102 days, 3:16, 5 users, load average: 1.05, 1.11, 1.41
Attachment
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.
Added to TODO:
* Ignore temporary tables from other session when processing
inheritance
--
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