Re: Temp rows - is it possible? - Mailing list pgsql-general

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