Thread: display temp table structure?
I was trying to check my temp table structure, but failed (7.1 beta4) and I found the following item in the TODO list (without '-'). * allow psql \d to show temporary table structure Is there any workaround or equivalent SQL available at this moment? Thanx -- LM.Liu
[ Charset ISO-8859-1 unsupported, converting... ] > > I was trying to check my temp table structure, but failed (7.1 beta4) > and I found the following item in the TODO list (without '-'). > > * allow psql \d to show temporary table structure > > Is there any workaround or equivalent SQL available at this moment? You can do a \dS in psql and find the correct pg_temp* table. -- 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
Bruce Momjian wrote: > [ Charset ISO-8859-1 unsupported, converting... ] > > > > I was trying to check my temp table structure, but failed (7.1 beta4) > > and I found the following item in the TODO list (without '-'). > > > > * allow psql \d to show temporary table structure > > > > Is there any workaround or equivalent SQL available at this moment? > > You can do a \dS in psql and find the correct pg_temp* table. Thanx Bruce What I found is something like pg_temp.25865.0. Do we have some kind of mapping table between the "real temp table name" and pg_temp*. If there are more than one temp tables, I need a way to differentiate them. Thanks for your help -- LM.Liu
> Thanx Bruce > > What I found is something like pg_temp.25865.0. Do we have some kind of > mapping table between the "real temp table name" and pg_temp*. If there > are more than one temp tables, I need a way to differentiate them. > > Thanks for your help There is no user-visible mapping, though the number is the process id. You could use that. I think you can get the backend process id somehow, but I am not sure how. Anyone? -- 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
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> There is no user-visible mapping, though the number is the process id.
> You could use that. I think you can get the backend process id somehow,
> but I am not sure how. Anyone?
If you're using libpq, there is a function to retrieve the backend's PID
from the PGconn (PQbackendPID). I don't think any of the other client
libraries provide this (yet).
regards, tom lane
Tom Lane wrote: > > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > There is no user-visible mapping, though the number is the process id. > > You could use that. I think you can get the backend process id somehow, > > but I am not sure how. Anyone? > > If you're using libpq, there is a function to retrieve the backend's PID > from the PGconn (PQbackendPID). I don't think any of the other client > libraries provide this (yet). I installed getpid() as a user-defined function (while testing an idea) - it seemed to work and I assumed it was the backend pid I was getting. - Richard Huxton
> Tom Lane wrote: > > > > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > > There is no user-visible mapping, though the number is the process id. > > > You could use that. I think you can get the backend process id somehow, > > > but I am not sure how. Anyone? > > > > If you're using libpq, there is a function to retrieve the backend's PID > > from the PGconn (PQbackendPID). I don't think any of the other client > > libraries provide this (yet). > > I installed getpid() as a user-defined function (while testing an idea) > - it seemed to work and I assumed it was the backend pid I was getting. Yes, that is the _nify_ fix I was thinking about. Use the proper path for libc and: CREATE FUNCTION getpid () returns int4 as '/lib/libc.so.6' LANGUAGE 'C'; SELECT getpid(); -- 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
Bruce Momjian wrote: > > Tom Lane wrote: > > > > > > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > > > There is no user-visible mapping, though the number is the process id. > > > > You could use that. I think you can get the backend process id somehow, > > > > but I am not sure how. Anyone? It seems that client application has to keep a mapping for all the temporary tables it created. I wonder if that's the case, how can you later implement \d temp_table_name (in the TODO list)? If backend does not keep temp_table_name, this looks like somthing impossible to do (to me). (I hope my question makes sense.) BTW, thanks for the workaround. > > > > If you're using libpq, there is a function to retrieve the backend's PID > > > from the PGconn (PQbackendPID). I don't think any of the other client > > > libraries provide this (yet). > > > > I installed getpid() as a user-defined function (while testing an idea) > > - it seemed to work and I assumed it was the backend pid I was getting. > > Yes, that is the _nify_ fix I was thinking about. Use the proper path > for libc and: > > CREATE FUNCTION getpid () returns int4 as '/lib/libc.so.6' LANGUAGE 'C'; > SELECT getpid(); > -- LM.Liu
Limin Liu <limin@pumpkinnet.com> writes:
> If backend does not keep temp_table_name, this looks like somthing
> impossible to do (to me). (I hope my question makes sense.)
The backend does keep a temp table name mapping, but it's hidden in an
internal data structure that there's no way to read out.
Rather than invent some way to make that mapping visible, I have been
thinking that it might be possible to dispense with the special mapping
altogether once we have schemas (which hopefully will happen for 7.2).
I am imagining that temp tables might become ordinary tables that live
in a per-backend schema. I haven't tried to work out the details yet,
though.
regards, tom lane