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

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

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

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

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

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