Thread: Temp tables are curious creatures....
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 relationsName | 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 relationsName | Type | Owner ------+-------+-------junk | table | rbtjunk | table | rbt
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, Pennsylvania19026
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 integerAS '/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, Pennsylvania19026