Re: AW: [SQL] Table Attribute Help
I have been trying to do exactly that, with this being the closest I can
come:
football=# SELECT a.attname, t.typname, a.attlen, a.atttypmod FROM
pg_class c, pg_attribute a, pg_type t WHERE c.relname = 'tblplayer' AND
a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid ORDER BY
a.attnum;
attname| typname | attlen | atttypmod
---+-++---
play_id | int4| 4 |-1
play_name | varchar | -1 |34
play_username | varchar | -1 |20
play_password | varchar | -1 |20
play_online | bool| 1 |-1
I assume that attlen is the length in bytes of the field, with -1 being
variable length. Those who have a variable length have their length + 4
in the atttypmod field. So here is the query I used and its output for
this type of result (its a biggie):
number | attribute |type |modifier
+---+-+
1 | play_id | int4| not null default nextval('tb...
2 | play_name | varchar(30) | not null
3 | play_username | varchar(16) | not null
4 | play_password | varchar(16) | not null
5 | play_online | bool| default 'f'
SELECT a.attnum as number,
a.attname as attribute,
CASE WHEN t.typname = 'varchar' THEN
t.typname || '(' || a.atttypmod - 4 || ')'
ELSE
t.typname
END as type,
CASE WHEN a.attnotnull = 't' THEN
'not null '::text ELSE ''::text
END || 'default ' ||
CASE WHEN a.atthasdef = 't' THEN
substring(d.adsrc for 128)::text
ELSE ''::text END as modifier
FROM pg_class c,
pg_attribute a,
pg_type t,
pg_attrdef d
WHEREc.relname = '<>' AND
a.attnum > 0 AND
a.attrelid = c.oid AND
a.atttypid = t.oid AND
c.oid = d.adrelid AND
d.adnum = a.attnum
UNION ALL
SELECT a.attnum as number,
a.attname as attribute,
CASE WHEN t.typname = 'varchar' THEN
t.typname || '(' || a.atttypmod - 4 || ')'
ELSE
t.typname
END as type,
CASE WHEN a.attnotnull = 't' THEN
'not null '::text
ELSE
''::text
END as modifier
FROM pg_class c,
pg_attribute a,
pg_type t
WHEREc.relname = '<>' AND
a.attnum > 0 AND
a.attrelid = c.oid AND
a.atttypid = t.oid AND
a.attname NOT IN (SELECT a.attname
FROM pg_class c,
pg_attribute a,
pg_attrdef d
WHERE c.relname = '<>' AND
a.attnum > 0 AND
a.attrelid = c.oid AND
a.atttypid = t.oid AND
c.oid = d.adrelid AND
d.adnum = a.attnum)
ORDER BY a.attnum;
-
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
On Mon, 9 Oct 2000, Brian C. Doyle wrote:
> That is great thank you.
>
> How would I grab the attribute type for an attribute with it so that the
> out put would look like
>
>attname atttype
> -- --
> userid varchar(30)
>
> I know that is not correct but is it possible to get that out put
>
>
> At 05:27 PM 10/9/00 +0200, you wrote:
> >yes it's possible,
> >
> >SELECT pg_attribute.attname
> >FROM pg_class, pg_attribute
> >WHERE
> >pg_class.relname = 'xxx' and pg_attribute.attrelid = pg_class.oid
> >
> >and pg_attribute.attnum>=1 order by pg_attribute.attnum;
> >
> >xxx is your tablename!
> >
> >
> >-Ursprüngliche Nachricht-
> >Von: Brian C. Doyle [mailto:[EMAIL PROTECTED]]
> >Gesendet: Montag, 9. Oktober 2000 17:21
> >An: [EMAIL PROTECTED]
> >Betreff: [SQL] Table Attribute Help
> >
> >
> >Hello all,
> >
> >I am trying to find a query to retrive the attributes of a table as in \d
> >tablename but as a select command. Is this possible?
>
Re: [SQL] Q: spinlock on Alpha? (PG7.0.2)
On Mon, 2 Oct 2000, Tom Lane wrote:
> "Emils Klotins" <[EMAIL PROTECTED]> writes:
> > RedHat Linux 6.2 on Alphaserver DS10 (egcs-1.1.2, glibc-2.1.3,
> > libstdc++-2.9.0).
> > Postgresql-7.0.2 source
>
> > Compiles and installs just fine. However, both the regular initdb and
> > parallel regression testing's initdb stage fails with a core file and
> > message:
>
> > FATAL: s_lock (2030d360) at spin.c:116, stuck spinlock. Aborting.
> > FATAL: s_lock (2030d360) at spin.c:116, stuck spinlock. Aborting.
>
> I was just fooling with PG on a RedHat Alpha box that DEC kindly
> loaned to the project. It looks like the above problem is caused
> by compiler optimization; although src/template/linux_alpha
> optimistically sets CFLAGS=-O2, I had to back off to -O1 to avoid
> that same spinlock complaint, and I couldn't get 7.0.* to pass
> regression tests with anything but -O0. (And even there, there
> were a bunch of failures in the datetime-related tests; it looks
> like our abstime datatype breaks rather badly on this platform.)
To get pgsql running correctly (spinlocks and regression tests) on
Linux/Alpha, one needs the patches off of my web site (as someone already
pointed out in another response to this thread). You can reduce the
optimization levels on the compiler and make some progress, but w/o the
patches, you get nowhere in the end.
Also, some versions of egcs have been known to have problems on
Linux/Alpha, especially when it comes to optimization levels beyond the
default (none). I do know that gcc 2.95.2 (as distributed w/Debian 2.2)
works just fine with -O2. I seem to remember someone mentioning the
compiler with RedHat 6.2 does not work, and they had to upgrade to the
2.95.2 version before the optimization flags were safe.
Of course, there is no release of RH7 for alpha yet (that I know
of), but on the Intel side there are some rumblings that RedHat used a
somewhat broken version of gcc. On top of that, I never quite figured out
the alignment between the egcs and the gcc version numbers (i.e. how does
egcs 1.1.2 relate to gcc 2.95.2?).
I can tell you that with the patches on my web site and the gcc
2.95.2 compiler, everything works great with pgsql. Beyond that, I can not
promise you anything. If anyone wants to test on other compilers, I am
more than glad to help in anyway that I can. And if they report back to me
the results, I will add them to my web page.
> I haven't had time yet to try current sources on that box, but
> I'm optimistic that the new function manager will solve a lot of
> portability problems on Alphas. Still, I don't understand why -O2
> breaks spinlocks --- maybe egcs is misoptimizing around the inline
> assembly code of tas() ?
The last current sources I tested, about a month ago (8/15),
compiled w/o a single problem on my system (Debian 2.2, gcc 2.95.2), w/o
any patches and w/-O2. In other words, the need for special patches for
Linux/Alpha was removed, and now compiles out of the box. Once I get a bit
of time I plan to test more current snapshots, and will of course report
my results.
And yes, the new function manager made about 90% of the 7.0.2
Linux/Alpha patches unneeded. The rest I '#ifdef {linux-alpha}'ed and
submitted a patch (quite short if I remember), which was accepted. Even
patched the regression tests results for geometry to match the Linux/Alpha
output (which actually matched a few other platforms as well). So now even
all regression tests as well. :)
As for the spinlock, it is a compiler problem. At one time it had
non-local labels and therefore could not be inlined (i.e. multiple labels
w/the same name resulteD), but I fixed it by replacing all the labels with
local ones about a year or more ago. Compilers on Linux/Alpha have a
history of misbehaving when it came to the more aggressive optimization.
Only recently has most of the wrinkles been ironed out.
That is my two cents, hope they are of use. TTYL.
---
| "For to me to live is Christ, and to die is gain."|
|--- Philippians 1:21 (KJV) |
---
| Ryan Kirkpatrick | Boulder, Colorado | http://www.rkirkpat.net/ |
---
Re: [SQL] Q: spinlock on Alpha? (PG7.0.2) - solved
On Tue, 3 Oct 2000, Emils Klotins wrote: > From: Fabrice Scemama <[EMAIL PROTECTED]> > > Try this: > > http://www.rkirkpat.net/software/ > > > parallel regression testing's initdb stage fails with a core file and > > > message: > > > > > > FATAL: s_lock (2030d360) at spin.c:116, stuck spinlock. Aborting. > > > FATAL: s_lock (2030d360) at spin.c:116, stuck spinlock. Aborting. > > > > > Thanks, it worked! You are welcome... Some one beat me to directing you to my own site. :) > Now everything works fine and the only regression tests that fail > are > tinterval > abstime > geometry > > I wonder if I might have any problems because of those? The failure with geometry is an off by one error in the nth decimal place, in other words harmless (providing you are not calculating trajectories for an interplanetary space mission :). The other two should not have failed. Have you tried diffing the results against the expected results? Often times timezones being misconfigured can cause strange problems. If there is nothing obvious in the resulting diffs (i.e. nothing that can be traced to a local problem), please email the regression results to me (an attached .tgz is fine), and I will take a look. TTYL. --- | "For to me to live is Christ, and to die is gain."| |--- Philippians 1:21 (KJV) | --- | Ryan Kirkpatrick | Boulder, Colorado | http://www.rkirkpat.net/ | ---
