Appears psql needs to know how to differentiate between it's own temp
tables and those of another connection.  On the plus side, this takes
care of a TODO item to add temp table listings to psql.

Connection 1:

template1=# create temp table junk(col1 int4);
CREATE
template1=# select * from junk;
 col1 
------
(0 rows)


Connection 2:
template1=# \d
  List of relations
 Name | Type  | Owner 
------+-------+-------
 junk | table | rbt
(1 row)

template1=# select * from junk;
ERROR:  Relation "junk" does not exist

template1=# create temp table junk (col4 text);
CREATE

  List of relations
 Name | Type  | Owner 
------+-------+-------
 junk | table | rbt
 junk | table | rbt




29 Apr 2002 20:32:23 -0400
Rod Taylor <rbt@zort.ca>
Temp tables are curious creatures....
Rod Taylor <rbt@zort.ca> writes:
> Appears psql needs to know how to differentiate between it's own temp
> tables and those of another connection.

More generally, psql is as yet clueless about schemas.

regression=# create schema foo;
CREATE
regression=# create schema bar;
CREATE
regression=# create table foo.tab1 (f1 int);
CREATE
regression=# create table bar.tab1 (f2 int);
CREATE
regression=# \d tab1
         Table "tab1"
 Column |  Type   | Modifiers
--------+---------+-----------
 f1     | integer |
 f2     | integer |

This is ... um ... wrong.  I am not real sure what the right behavior
is, however.  Should \d accept patterns like schema.table (and how
should its wildcard pattern matching fit with that?)  If you don't
specify a schema, should it only show tables visible in your search
path?

			regards, tom lane

Mon, 29 Apr 2002 21:35:04 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Temp tables are curious creatures.... 

I think you have to use the backend pid to find your own.  I think
there is a libpq function that returns the backend pis so psql can
frame the proper query.

---------------------------------------------------------------------------

Rod Taylor wrote:
> Appears psql needs to know how to differentiate between it's own temp
> tables and those of another connection.  On the plus side, this takes
> care of a TODO item to add temp table listings to psql.
> 
> Connection 1:
> 
> template1=# create temp table junk(col1 int4);
> CREATE
> template1=# select * from junk;
>  col1 
> ------
> (0 rows)
> 
> 
> Connection 2:
> template1=# \d
>   List of relations
>  Name | Type  | Owner 
> ------+-------+-------
>  junk | table | rbt
> (1 row)
> 
> template1=# select * from junk;
> ERROR:  Relation "junk" does not exist
> 
> template1=# create temp table junk (col4 text);
> CREATE
> 
>   List of relations
>  Name | Type  | Owner 
> ------+-------+-------
>  junk | table | rbt
>  junk | table | rbt
> 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
> 

-- 
  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

Mon, 29 Apr 2002 21:35:21 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Temp tables are curious creatures....
On Tue, 2002-04-30 at 03:35, Tom Lane wrote:
> Rod Taylor <rbt@zort.ca> writes:
> > Appears psql needs to know how to differentiate between it's own temp
> > tables and those of another connection.
> 
> More generally, psql is as yet clueless about schemas.
> 
> regression=# create schema foo;
> CREATE
> regression=# create schema bar;
> CREATE
> regression=# create table foo.tab1 (f1 int);
> CREATE
> regression=# create table bar.tab1 (f2 int);
> CREATE
> regression=# \d tab1
>          Table "tab1"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  f1     | integer |
>  f2     | integer |
>
> This is ... um ... wrong.  I am not real sure what the right behavior
> is, however.  Should \d accept patterns like schema.table (and how
> should its wildcard pattern matching fit with that?)  If you don't
> specify a schema, should it only show tables visible in your search
> path?

Yes.


For me the intuitive answer would be

regression=# \d tab1
          Table "foo.tab1"
  Column |  Type   | Modifiers
 --------+---------+-----------
  f1     | integer |

          Table "bar.tab1"
  Column |  Type   | Modifiers
 --------+---------+-----------
  f2     | integer |


i.e. default wildcarding of missing pieces

-------------
Hannu



30 Apr 2002 12:37:24 +0200
Hannu Krosing <hannu@tm.ee>
Re: Temp tables are curious creatures....
On Tue, 2002-04-30 at 03:35, Bruce Momjian wrote:
> 
> I think you have to use the backend pid to find your own.  I think
> there is a libpq function that returns the backend pis so psql can
> frame the proper query.

Is anyoune working on information schema (or pg_xxx views) for use in
psql and other development frontends?

Also, are there plans to have SQL-accessible backend_pid function in the
backend by default ?

On RH 7.1 I can create it as:

CREATE FUNCTION getpid() RETURNS integer
 AS '/lib/libc.so.6','getpid'
LANGUAGE 'C';

But I'd like it to be a builtin from the start so one can query it
without relying on libpq

---------------------------------------------------------------------------
Hannu



30 Apr 2002 13:09:57 +0200
Hannu Krosing <hannu@tm.ee>
Re: Temp tables are curious creatures....
> Is anyoune working on information schema (or pg_xxx views) for use
in
> psql and other development frontends?

I had started to try an information schema.  Didn't make it very far.
Way too much information missing to come anywhere near spec -- so I've
started trying to fill in those holes.

Give me some time to finish my current set of patches and I'll go back
at the information schema (hopefully with more luck).


Tue, 30 Apr 2002 08:18:14 -0400
"Rod Taylor" <rbt@zort.ca>
Re: Temp tables are curious creatures....

Add to TODO:

	* Add getpid() function to backend 
We have this in libpq, but it should be in the backend code as a
function call too.

---------------------------------------------------------------------------

Hannu Krosing wrote:
> On Tue, 2002-04-30 at 03:35, Bruce Momjian wrote:
> > 
> > I think you have to use the backend pid to find your own.  I think
> > there is a libpq function that returns the backend pis so psql can
> > frame the proper query.
> 
> Is anyoune working on information schema (or pg_xxx views) for use in
> psql and other development frontends?
> 
> Also, are there plans to have SQL-accessible backend_pid function in the
> backend by default ?
> 
> On RH 7.1 I can create it as:
> 
> CREATE FUNCTION getpid() RETURNS integer
>  AS '/lib/libc.so.6','getpid'
> LANGUAGE 'C';
> 
> But I'd like it to be a builtin from the start so one can query it
> without relying on libpq
> 
> ---------------------------------------------------------------------------
> Hannu
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

-- 
  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

Sat, 25 May 2002 18:43:59 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Temp tables are curious creatures....