Обсуждение: problem trying to create a temp table

<table align="center" bgcolor="#ffffff" border="0" cellpadding="0" cellspacing="0" style="empty-cells: show;"
width="100%"><tr><td;=";" </td="</td" align="left" background="cid:top@048ae683021f97453979805b18a66051" colspan="1"
height="72"style="text-align: left; background-repeat: no-repeat;"></td></tr><tr><td style="vertical-align: top;"><div
style="padding:5px; overflow-x: auto;"></div></td></tr></table><span style="font-family:Verdana; color:#000000;
font-size:10pt;"><div>ALL,</div><div> </div><div>Using9.1.2 on Windows 7 X64 for development.</div><div> </div><div>I'm
tryingto create a temporary table used to store session variables for each user when they login.  I'm moving from SQL
Anywhereand they have a CREATE VARIABLE which does this, so I thought a temp table would work fine, especially since
eachuser will have their own copy.</div><div> </div><div>CREATE  TEMP TABLE iss.sessionsettings<br />(<br />  VarName
charactervarying(20) NOT NULL,<br />  value character varying(128),<br />  CONSTRAINT pk_sessionsettings_varname
PRIMARYKEY (VarName )<br />)<br />WITH (<br />  OIDS=FALSE<br />);<br />ALTER TABLE iss.sessionsettings<br />  OWNER TO
postgres;</div><div> </div><div>WhenI try and run this I get the following error message.</div><div> </div><div>ERROR: 
cannotcreate temporary relation in non-temporary schema</div><div><br />********** Error **********</div><div>ERROR:
cannotcreate temporary relation in non-temporary schema<br />SQL state: 42P16</div><div> </div><div>What do I need to
doto create temporary tables to the schema.  I guess I could create a separate schema for temp tables if
needed.</div><div> </div><div> </div><div>BestRegards</div><div> </div><div>Michael Gould</div><div>Intermodal Software
Solutions,LLC</div><div>904-226-0978</div></span> 

On 24/02/12 13:26, mgould@isstrucksoftware.net wrote:

> ALL,
> Using 9.1.2 on Windows 7 X64 for development.
> I'm trying to create a temporary table used to store session variables
>  CREATE TEMP TABLE iss.sessionsettings

> When I try and run this I get the following error message.
> ERROR: cannot create temporary relation in non-temporary schema
Temp tables get their own schema, and each session (connection) gets
its own temp schema. So - don't qualify them by schema.

--
   Richard Huxton
   Archonet Ltd

<table align=3D"center" style=3D"empty-cells: show;" bgcolor=3D"#ffffff" bo=
rder=3D"0" cellpadding=3D"0" cellspacing=3D"0" width=3D"100%"><td style=
=3D"text-align: left; background-repeat: no-repeat;" align=3D"left"; height=
=3D"72" colspan=3D"1" background=3D"cid:top@18707919e232053e6d9810030e7d0ce=
6"<div style=3D"padding: 5=
px; overflow-x: auto;"><span style=3D=
"font-family:Verdana; color:#000000; font-size:10pt;">How do I access =
it.  I just did that and when I try and access it with a
 
ERROR:  relation "sessionsetting" does not existLINE 1: selec=
t * from "sessionsetting"       &nbs=
p;            &=
nbsp; ^
********** Error **********
ERROR: relation "sessionsetting" does not existSQL state: 42P01<BR=
>Character: 15 
  or
ERROR: relation "sessionsetting" does not existLINE 1: select * fr=
om "sessionsetting"^
********** Error **********
ERROR: relation sessionsetting does not existSQL state: 42P01C=
haracter: 15
 
Best Regards,
Michael Gould
Intermodal Software Solutions, LLC
904-226-0978
 
 
<BLOCKQUOTE style=3D"BORDER-LEFT: blue 2px solid; PADDING-LEFT: 8px; FONT-F=
AMILY: verdana; COLOR: black; MARGIN-LEFT: 8px; FONT-SIZE: 10pt" id=3Dreply=
Blockquote webmail=3D"1">
-------- Original Message --------Subject: Re:=
 [GENERAL] problem trying to create a temp tableFrom: Richard Huxton &l=
t;dev@archonet.com>Date: Fri=
, February 24, 2012 6:32 amTo: <a href=3D"mailto:mgould@isstrucksoftwar=
e.net">mgould@isstrucksoftware.netCc: "<a href=3D"mailto:pgsql-gene=
ral@postgresql.org">pgsql-general@postgresql.org" <<a href=3D"mailto=
:pgsql-general@postgresql.org">pgsql-general@postgresql.org>=
On 24/02/12 13:26, mgould@is=
strucksoftware.net wrote:> ALL,> Using 9.1.2 on Windo=
ws 7 X64 for development.> I'm trying to create a temporary table us=
ed to store session variables> CREATE TEMP TABLE iss.sessionsettings=
> When I try and run this I get the following error message.=
> ERROR: cannot create temporary relation in non-temporary schemaTem=
p tables get their own schema, and each session (connection) getsits ow=
n temp schema. So - don't qualify them by schema.-- Richard Hux=
tonArchonet Ltd<!-- wmL=
etter_tail_start -->=

On Fri, Feb 24, 2012 at 7:32 AM, Richard Huxton <dev@archonet.com> wrote:
> On 24/02/12 13:26, mgould@isstrucksoftware.net wrote:
>
>> ALL,
>> Using 9.1.2 on Windows 7 X64 for development.
>> I'm trying to create a temporary table used to store session variables
>>  CREATE TEMP TABLE iss.sessionsettings
>
>
>> When I try and run this I get the following error message.
>> ERROR: cannot create temporary relation in non-temporary schema
>
> Temp tables get their own schema, and each session (connection) gets
> its own temp schema. So - don't qualify them by schema.
>
> --
>  Richard Huxton
>  Archonet Ltd
>

Is that to avoid naming conflicts between simultaneous users?

Andrew

Andrew,

That is acutally what the second run was supposed to be.  I copied the
original on instead of the second instance, but the results were the
same.


Michael Gould
Intermodal Software Solutions, LLC
904-226-0978


-------- Original Message --------
Subject: Re: [GENERAL] problem trying to create a temp table
From: Andrew Gould <andrewlylegould@gmail.com>
Date: Fri, February 24, 2012 6:41 am
To: mgould@isstrucksoftware.net
Cc: Richard Huxton <dev@archonet.com>, "pgsql-general@postgresql.org"
<pgsql-general@postgresql.org>

On Fri, Feb 24, 2012 at 7:36 AM, <mgould@isstrucksoftware.net> wrote:
How do I access it.  I just did that and when I try and access it with a


ERROR:  relation "sessionsetting" does not exist
LINE 1: select * from "sessionsetting"
                      ^

********** Error **********
ERROR: relation "sessionsetting" does not exist
SQL state: 42P01
Character: 15

  or
ERROR: relation "sessionsetting" does not exist
LINE 1: select * from "sessionsetting"
^

********** Error **********
ERROR: relation sessionsetting does not exist
SQL state: 42P01
Character: 15


Best Regards,

Michael Gould
Intermodal Software Solutions, LLC
904-226-0978



-------- Original Message --------
Subject: Re: [GENERAL] problem trying to create a temp table
From: Richard Huxton <dev@archonet.com>
Date: Fri, February 24, 2012 6:32 am
To: mgould@isstrucksoftware.net
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>

On 24/02/12 13:26, mgould@isstrucksoftware.net wrote:

> ALL,
> Using 9.1.2 on Windows 7 X64 for development.
> I'm trying to create a temporary table used to store session variables
> CREATE TEMP TABLE iss.sessionsettings

> When I try and run this I get the following error message.
> ERROR: cannot create temporary relation in non-temporary schema
Temp tables get their own schema, and each session (connection) gets
its own temp schema. So - don't qualify them by schema.

--
Richard Huxton
Archonet Ltd







Try to access the table without putting the table name in double quotes.
 Does that make a difference?

Andrew

On 24/02/12 13:36, mgould@isstrucksoftware.net wrote:
> How do I access it. I just did that and when I try and access it with a
> ERROR: relation "sessionsetting" does not exist
> LINE 1: select * from "sessionsetting"

=> CREATE SCHEMA foo;
CREATE SCHEMA
=> CREATE TABLE foo.table1 (id int);
CREATE TABLE
=> SET search_path = foo;
SET
=> INSERT INTO table1 VALUES (1),(2),(3);
INSERT 0 3
=> CREATE TEMP TABLE table1 (id int);
CREATE TABLE
=> INSERT INTO table1 VALUES (4),(5),(6);
INSERT 0 3
=> SELECT * FROM table1;
  id
----
   4
   5
   6
(3 rows)
=> DROP TABLE table1;
DROP TABLE
=> SELECT * FROM table1;
  id
----
   1
   2
   3
(3 rows)

Try "SELECT * FROM pg_namespace" to see the various temp schemas being
created.

--
   Richard Huxton
   Archonet Ltd

On 24/02/12 13:37, Andrew Gould wrote:
> On Fri, Feb 24, 2012 at 7:32 AM, Richard Huxton<dev@archonet.com>  wrote:
>>
>> Temp tables get their own schema, and each session (connection) gets
>> its own temp schema. So - don't qualify them by schema.
>
> Is that to avoid naming conflicts between simultaneous users?

Yes. I believe it also invisibly adds it to your search_path too, the
same as it does with the pg_catalog schema.

--
   Richard Huxton
   Archonet Ltd

On Friday, February 24, 2012 5:46:06 am mgould@isstrucksoftware.net wrote:
> Andrew,
>
> That is acutally what the second run was supposed to be.  I copied the
> original on instead of the second instance, but the results were the
> same.

Are you doing all this in the same session?:

test(5432)postgres=#create temp table temp_test(id int, fld_1 text);
CREATE TABLE

test(5432)postgres=#SELECT * from temp_test;
 id | fld_1
----+-------
(0 rows)

If so what are the exact commands you are executing?

>
>
> Michael Gould
> Intermodal Software Solutions, LLC
> 904-226-0978
>

--
Adrian Klaver
adrian.klaver@gmail.com