Temp Tables

mdb002@yahoo.com (mdb) writes:

I am running a client server app and am using temp tables to do some
data manipulation. However, Postgresql seems to prevent multiple temp
tables from being created with the same name. The temp tables are
created by seperate connections to the database, but this does not
seem to matter.

Surely not.

In window 1:
regression=# create temp table foo (f1 int);
CREATE
regression=#

In window 2:
regression=# create temp table foo (f1 float, f2 float);
CREATE
regression=#

So I don't see a problem. Would you give us an example of exactly what
you are doing?

regards, tom lane

mdb wrote:

Hello,

I am running a client server app and am using temp tables to do some
data manipulation. However, Postgresql seems to prevent multiple temp
tables from being created with the same name. The temp tables are
created by seperate connections to the database, but this does not
seem to matter. Is this correct? Do I need to make the names unique
by using a user id? Am I doing something wrong? I am using
Postgresql 7.1.3 and odbc 7.1.9.

Temp tables can be created with identical names in different
connections. I have no idea why it is failing. Are you trying to
create the same temp table in the same session? Could you show us the
failure?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

mdb wrote:

Hello,

I am running a client server app and am using temp tables to do some
data manipulation. However, Postgresql seems to prevent multiple temp
tables from being created with the same name. The temp tables are
created by seperate connections to the database, but this does not
seem to matter. Is this correct? Do I need to make the names unique
by using a user id? Am I doing something wrong? I am using
Postgresql 7.1.3 and odbc 7.1.9.

Temp tables can be created with identical names in different
connections. I have no idea why it is failing. Are you trying to
create the same temp table in the same session? Could you show us the
failure?

I believe (and I'm no expert) that the table names must be unique by user so
if your client/server app uses the same generic user name (like your web
server's user for example), I don't think the particular session matters.
It's a user_name+temp_table_name thing.

But I could be wrong. :-)

Len Morgan

mdb002@yahoo.com (mdb) wrote in message news:<9e6ea67a.0203041248.24859ff2@posting.google.com>...

Hello,

I am running a client server app and am using temp tables to do some
data manipulation. However, Postgresql seems to prevent multiple temp
tables from being created with the same name. The temp tables are
created by seperate connections to the database, but this does not
seem to matter. Is this correct? Do I need to make the names unique
by using a user id? Am I doing something wrong? I am using
Postgresql 7.1.3 and odbc 7.1.9.

Common mistake. Basically, you should be doing this inside of a
transaction block so that that the temp table is hidden from all the
other users, like so:

begin;
create temp table test (name text, id int);
(do mysterious and wonderful things in SQL here)
end;

the temp table will be built, seen only by your transaction, then
automatically dropped on exit.

note that SEQUENCES should not be created, i.e. don't do 'create temp
table yada (name text, id serial)' because that requires the building
of a sequence, which isn't a temporary kind of thing. you can
reference a sequence within a transacation block in a temp table
though, like so

create temp table test (name text, id int not null default
nextval('"test_id_seq"'::text));

Good luck

Scott,
Thanks for the response.

However, I have further narrowed down the problem. The temp tables
were not the problem. I was creating views of the temp tables which
was the problem. Now why would I create a view of temp tables you
ask? I am using VB, before I get blasted for this heresy it was
originally an access and vb application that we are upgrading to
PostgreSQL. The code is substantial and to recode everthing would be
a substantial undertaking. Back to the problem the VB connection does
not see the temp table if I do not use a view. A little bit about the
temp table it is used to emulate an access crosstab query and columns
are added and removed (table deleted and columns added) multiple times
as the users enter data. I display the data in DBGrid using a
recordset and setting the dbgrid datasource property (no
dataenvironment or datacontrol used). Has anybody used temp tables to
display data with vb and how do you do it?

Marc
smarlowe@ihs.com (Scott Marlowe) wrote in message news:<a03059a3.0203071416.7bf79d27@posting.google.com>...

The problem was the name of the temp table. When I created the view
inside a stored procedure the view was created "normally" and could be
referenced by the name I gave it. The temp tables can not be
referenced by the name I gave them. VB/ODBC did not produce an error
when I tried to open a recordset with the temp table name I used (ex.
table does not exist) it just did not open the recordset and continued
on. A little misleading.

I did find a work around posted by Richard Huxton/Bruce Momjian -

CREATE FUNCTION getpid () returns int4 as '/lib/libc.so.6' LANGUAGE
'C';

This returns the process id. The temp table named uses the process id
(pg_temp.PROCESS_ID.TEMP_TABLE_NUMBER) using the returned process id
and a counter in the VB program (the temp table is destroyed and
created multiple times) I was able to select using PostgreSQL
generated name.

Thank You to all that responded.

Bruce, Tom and everyone else involved with PostgreSQL,

This is my first experience with your database, despite a learning
curve I am going through right now I am throughly impressed with
Postgre. It is an awesome database.

Marc