Thread: Temp Tables
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. Thank you Marc
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
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
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>...
> 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
> 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
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
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