Обсуждение: Temporary table

Hello,

I´m have some problems with a temporary table, i need create a table, insert
some values, make a select and at end of transaction the table must droped,
but after i created a table there not more exist, is this normal ?

How to reproduce :


    CREATE TEMP TABLE cademp (
           codemp INTEGER,
           codfil INTEGER,
           nomemp varchar(50)
    ) ON COMMIT DROP;

    INSERT INTO cademp (codemp, codfil, nomemp) values (1,1,'TESTE');
    INSERT INTO cademp (codemp, codfil, nomemp) values (1,2,'TESTE1');

    Select * from cademp;



In this case, the table cademp doesn´t exist at the first insert, in the
same transaction.




Tks,

Franklin

"Franklin Haut" <franklin.haut@gmail.com> writes:
> How to reproduce :

>     CREATE TEMP TABLE cademp (
>            codemp INTEGER,
>            codfil INTEGER,
>            nomemp varchar(50)
>     ) ON COMMIT DROP;

>     INSERT INTO cademp (codemp, codfil, nomemp) values (1,1,'TESTE');
>     INSERT INTO cademp (codemp, codfil, nomemp) values (1,2,'TESTE1');

>     Select * from cademp;

You need a BEGIN/COMMIT around that, or else rethink using ON COMMIT DROP.
As is, the temp table goes away instantly when the CREATE commits.

            regards, tom lane

Franklin Haut wrote:
> Hello,
>
> I´m have some problems with a temporary table, i need create a table,
> insert some values, make a select and at end of transaction the table
> must droped, but after i created a table there not more exist, is
> this normal ?
>
> How to reproduce :
>
>
>     CREATE TEMP TABLE cademp (
>            codemp INTEGER,
>            codfil INTEGER,
>            nomemp varchar(50)
>     ) ON COMMIT DROP;
>
>     INSERT INTO cademp (codemp, codfil, nomemp) values (1,1,'TESTE');
>     INSERT INTO cademp (codemp, codfil, nomemp) values (1,2,'TESTE1');
>
>     Select * from cademp;
>
>
>
> In this case, the table cademp doesn´t exist at the first insert, in
> the same transaction.
>

It is NOT the same transaction.  By default, each STATEMENT is it's own
transaction.

Stick a BEGIN; before the create table, and a commit; after the select.

Larry Rosenman
>
>
>
> Tks,
>
> Franklin
>
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 6: explain analyze is your
> friend



--
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 512-248-2683                 E-Mail: ler@lerctr.org
US Mail: 430 Valona Loop, Round Rock, TX 78681-3893

Ok, it works.


Thanks

Franklin

-----Mensagem original-----
De: Larry Rosenman [mailto:ler@lerctr.org]
Enviada em: sexta-feira, 23 de junho de 2006 19:08
Para: 'Franklin Haut'; pgsql-performance@postgresql.org
Assunto: RE: [PERFORM] Temporary table

Franklin Haut wrote:
> Hello,
>
> I´m have some problems with a temporary table, i need create a table,
> insert some values, make a select and at end of transaction the table
> must droped, but after i created a table there not more exist, is this
> normal ?
>
> How to reproduce :
>
>
>     CREATE TEMP TABLE cademp (
>            codemp INTEGER,
>            codfil INTEGER,
>            nomemp varchar(50)
>     ) ON COMMIT DROP;
>
>     INSERT INTO cademp (codemp, codfil, nomemp) values (1,1,'TESTE');
>     INSERT INTO cademp (codemp, codfil, nomemp) values (1,2,'TESTE1');
>
>     Select * from cademp;
>
>
>
> In this case, the table cademp doesn´t exist at the first insert, in
> the same transaction.
>

It is NOT the same transaction.  By default, each STATEMENT is it's own
transaction.

Stick a BEGIN; before the create table, and a commit; after the select.

Larry Rosenman
>
>
>
> Tks,
>
> Franklin
>
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 6: explain analyze is your
> friend



--
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 512-248-2683                 E-Mail: ler@lerctr.org
US Mail: 430 Valona Loop, Round Rock, TX 78681-3893