Thread: pg temp tables

Hi,
I have been going around telling everyone that there is no point using
physical tables in postgres for temporary storage within a procedure.
Why bother bothering the system with something which is only used in
one procedure I said to myself... I have just learnt that with MS Sql
Server, this is not the case, and that there are locks on some system
table and temp tables eat up memory and lots of other unfortunate
things. Can someone give me a 101 on temp table considerations? Or
rather give me "the good link"?
Cheers
Anton

On Saturday 03 March 2007 10:33, Anton Melser wrote:
> Hi,
> I have been going around telling everyone that there is no point using
> physical tables in postgres for temporary storage within a procedure.
> Why bother bothering the system with something which is only used in
> one procedure I said to myself... I have just learnt that with MS Sql
> Server, this is not the case, and that there are locks on some system
> table and temp tables eat up memory and lots of other unfortunate
> things. Can someone give me a 101 on temp table considerations? Or
> rather give me "the good link"?

The main issue against using temp tables involve bloat of some of the system
catalogs, but it's no worse than doing create/drop cycles with standard
tables, and better because they don't suffer as much i/o load.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

On 06/03/07, Robert Treat <xzilla@users.sourceforge.net> wrote:
> On Saturday 03 March 2007 10:33, Anton Melser wrote:
> > Hi,
> > I have been going around telling everyone that there is no point using
> > physical tables in postgres for temporary storage within a procedure.
> > Why bother bothering the system with something which is only used in
> > one procedure I said to myself... I have just learnt that with MS Sql
> > Server, this is not the case, and that there are locks on some system
> > table and temp tables eat up memory and lots of other unfortunate
> > things. Can someone give me a 101 on temp table considerations? Or
> > rather give me "the good link"?
>
> The main issue against using temp tables involve bloat of some of the system
> catalogs, but it's no worse than doing create/drop cycles with standard
> tables, and better because they don't suffer as much i/o load.

Thanks for your reply. I am managing a db that has some export scripts
that don't do a drop/create, but rather a delete from at the start of
the proc (6 or 7 tables used for this, and only this). Now given that
there is no vacuuming at all going on - this is clearly suboptimal but
in the general case is this better/worse than using temporary tables?
Thanks again,
Anton

"Anton Melser" <melser.anton@gmail.com> writes:
> Thanks for your reply. I am managing a db that has some export scripts
> that don't do a drop/create, but rather a delete from at the start of
> the proc (6 or 7 tables used for this, and only this). Now given that
> there is no vacuuming at all going on - this is clearly suboptimal but
> in the general case is this better/worse than using temporary tables?

Delete all rows, you mean?  Have you considered TRUNCATE?

            regards, tom lane

On 06/03/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Anton Melser" <melser.anton@gmail.com> writes:
> > Thanks for your reply. I am managing a db that has some export scripts
> > that don't do a drop/create, but rather a delete from at the start of
> > the proc (6 or 7 tables used for this, and only this). Now given that
> > there is no vacuuming at all going on - this is clearly suboptimal but
> > in the general case is this better/worse than using temporary tables?
>
> Delete all rows, you mean?  Have you considered TRUNCATE?

Hi,
... I have considered lots of things - but I didn't write the scripts!
Now that you mention it, I do remember that truncate is much better
than
delete from mytable;
That is not what they wrote but hey. But even then, what are the
advantages/disadvantages of temp tables? Is there a document somewhere
I can consult which will give me the lowdown on permanent (but
temporary) versus temporary tables in pg?
Cheers
Anton