Thread: Temporary tables and indexes

Hi :
 Are the indices of a temporary table automatically
"dropped" together its corresponding temporary table
after a database session?

ludwig.

__________________________________________________
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com

Ludwig,

>   Are the indices of a temporary table automatically
> "dropped" together its corresponding temporary table
> after a database session?

I'm kind of surprised that it's possible to index a temporary table.There's not much point in doing so.

Yes, the indexes would be dropped as well.

-Josh Berkus

Sure there is!   There are queries that benefit from having a temporary table created for a subquery and the temporary
tableindexed before the join.  Since we can't easily return result sets from functions yet, it's not probably used that
much,but from within a function, I can see why you might want to do this from time to time. 

Ian A. Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
(253) 798-3549
iharding@tpchd.org

WWSD - What Would Scooby Doo?

>>> "Josh Berkus" <josh@agliodbs.com> 10/08/02 08:36AM >>>
Ludwig,

>   Are the indices of a temporary table automatically
> "dropped" together its corresponding temporary table
> after a database session?

I'm kind of surprised that it's possible to index a temporary table.There's not much point in doing so.

Yes, the indexes would be dropped as well.

-Josh Berkus

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

"Josh Berkus" <josh@agliodbs.com> writes:
> I'm kind of surprised that it's possible to index a temporary table.
> There's not much point in doing so.

Why not?  You seem to be equating "temporary" with "small", but I don't
see why that must be so.
        regards, tom lane

Tom,

> > I'm kind of surprised that it's possible to index a temporary
> table.
> > There's not much point in doing so.
> 
> Why not?  You seem to be equating "temporary" with "small", but I
> don't
> see why that must be so.

Nah.  I'm equating "temporary" with "query twice and throw away", which
isn't necessarily what everyone else does.

BTW, Ludwig, in postgresql versions < 7.2.0, I noticed that sometimes
indexes didn't seem to completely go away (i.e. I would get a name
conflict if I tried to drop & re-create in the same transaction)
without a VACUUM.  If you're using 7.1.3, you should upgrade for this
reason.

-Josh Berkus