Thread: How does psql actually implement the \d commands
Hi,
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?
Andy
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
On Apr 9, 5:51 am, laurenz.a...@wien.gv.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...@postgresql.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
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>: > > [...] > > > > > 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? > > Ok, here's what I get (output from psql): > [...] > > 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 > 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) > [...] > > 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) | Format the output. For example, the "17408" in the query above is a result from the first query. If you had triggers, constraints, rules or indexes associated with the table or the table would INHERIT another table, you'd probably see much more clearly what the other queries do. Yours, Laurenz Albe
On Apr 11, 5:45 am, laurenz.a...@wien.gv.at ("Albe Laurenz") wrote:
>
> Format the output.
>
> For example, the "17408" in the query above is a result from the
> first query.
>
> If you had triggers, constraints, rules or indexes associated
> with the table or the table would INHERIT another table, you'd probably
> see much more clearly what the other queries do.
>
> Yours,
> Laurenz Albe
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general
Thanks a lot. This does help clear it up.
Andy