Re: How does psql actually implement the \d commands

On Apr 9, 5:51 am, laurenz(dot)a(dot)(dot)(dot)(at)wien(dot)gv(dot)at ("Albe Laurenz") wrote:
> Andrew Falanga wrote:
>
> > I know about the -E option to psql and did that to get the following,
> > which is what psql does for a \d <tablename>:
>
> > ********* QUERY **********
> > SELECT c.oid,
> > n.nspname,
> > c.relname
> > FROM pg_catalog.pg_class c
> > LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
> > WHERE pg_catalog.pg_table_is_visible(c.oid)
> > AND c.relname ~ '^(personaldata)$'
> > ORDER BY 2, 3;
> > **************************
>
> > ********* QUERY **********
> > SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules
> > FROM pg_catalog.pg_class WHERE oid = '17408'
> > **************************
>
> > ********* QUERY **********
> > SELECT a.attname,
> > pg_catalog.format_type(a.atttypid, a.atttypmod),
> > (SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d
> > WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND
> > a.atthasdef),
> > a.attnotnull, a.attnum
> > FROM pg_catalog.pg_attribute a
> > WHERE a.attrelid = '17408' AND a.attnum > 0 AND NOT a.attisdropped
> > ORDER BY a.attnum
> > **************************
>
> > ********* QUERY **********
> > SELECT c.relname FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
> > WHERE c.oid=i.inhparent AND i.inhrelid = '17408' ORDER BY inhseqno ASC
> > **************************
>
> > Now, I tried to execute these queries one at a time and they failed,
> > somewhat miserably. In what order does PostgreSQL actually execute
> > them? Are they implemented as sub-queries? If so, in what order are
> > they executed?
>
> They do not fail here, and they should not fail.
> They should be executed as above, in this order.
>
> What are the miserable error messages you get?
>
> Yours,
> Laurenz Albe
>
> --
> Sent via pgsql-general mailing list (pgsql-gene(dot)(dot)(dot)(at)postgresql(dot)org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general

Sorry for the long delay in responding, lot's happening now.

Ok, here's what I get (output from psql):

mch=# SELECT c.oid,
mch-# n.nspname,
mch-# c.relname
mch-# FROM pg_catalog.pg_class c
mch-# LEFT JOIN pg_catalog.pg_namespace n ON n.oid =
c.relnamespace
mch-# WHERE pg_catalog.pg_table_is_visible(c.oid)
mch-# AND c.relname ~ '^(personaldata)$'
mch-# ORDER BY 2, 3;
oid | nspname | relname
-------+---------+--------------
17408 | public | personaldata
(1 row)

mch=# SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules
mch-# FROM pg_catalog.pg_class WHERE oid = '17408' ;
relhasindex | relkind | relchecks | reltriggers | relhasrules
-------------+---------+-----------+-------------+-------------
f | r | 0 | 0 | f
(1 row)

mch=# SELECT a.attname,
mch-# pg_catalog.format_type(a.atttypid, a.atttypmod),
mch-# (SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef
d
mch(# WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND
mch(# a.atthasdef),
mch-# a.attnotnull, a.attnum
mch-# FROM pg_catalog.pg_attribute a
mch-# WHERE a.attrelid = '17408' AND a.attnum > 0 AND NOT
a.attisdropped
mch-# ORDER BY a.attnum ;
attname | format_type | ?column? | attnotnull |
attnum
-------------------+------------------------+----------+------------
+--------
odn | integer | | f
| 1
placeofbirth | character varying(40) | | f
| 2
ps | character varying(50) | | f
| 3
po | character varying(50) | | f
| 4
village | character varying(50) | | f
| 5
lastname | character varying(50) | | f
| 6
firstname | character varying(50) | | f
| 7
address | character varying(100) | | f
| 8
father_lastname | character varying(50) | | f
| 9
father_firstname | character varying(50) | | f
| 10
husband_lastname | character varying(50) | | f
| 11
husband_firstname | character varying(50) | | f
| 12
billingaddress | character varying(50) | | f
| 13
nationality | character varying(50) | | f
| 14
jat | character varying(50) | | f
| 15
religion | character varying(25) | | f
| 16
occupation | character varying(50) | | f
| 17
age | integer | | f
| 18
sex | character(1) | | f
| 19
(19 rows)

mch=# SELECT c.relname FROM pg_catalog.pg_class c,
pg_catalog.pg_inherits i
mch-# WHERE c.oid=i.inhparent AND i.inhrelid = '17408' ORDER BY
inhseqno ASC ;
relname
---------
(0 rows)

So, obviously, I was doing something wrong because when I tried this
before, the queries failed. I don't now know what I was doing wrong,
but obviously, I was doing something wrong. So, I guess the only
question I have now is, since PostreSQL uses these four queries to
display the output from \d <tablename>, what does Postgres do
internally that makes the output look like this:

Table "public.personaldata"
Column | Type | Modifiers
-------------------+------------------------+-----------
odn | integer |
placeofbirth | character varying(40) |
ps | character varying(50) |
po | character varying(50) |
village | character varying(50) |
lastname | character varying(50) |
firstname | character varying(50) |
address | character varying(100) |
father_lastname | character varying(50) |
father_firstname | character varying(50) |
husband_lastname | character varying(50) |
husband_firstname | character varying(50) |
billingaddress | character varying(50) |
nationality | character varying(50) |
jat | character varying(50) |
religion | character varying(25) |
occupation | character varying(50) |
age | integer |
sex | character(1) |

???????

Thanks,
Andy