How does psql actually implement the \d commands

Started by Andrew Falangaabout 18 years ago5 messagesgeneral

af300wsm@gmail.com

about 18 years ago

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

Laurenz Albe

laurenz.albe@cybertec.at

about 18 years ago

In reply to: Andrew Falanga (#1)

Re: How does psql actually implement the \d commands

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

Andrew Falanga

af300wsm@gmail.com

almost 18 years ago

In reply to: Andrew Falanga (#1)

Re: How does psql actually implement the \d commands

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 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:

???????

Thanks,
Andy

Laurenz Albe

laurenz.albe@cybertec.at

almost 18 years ago

In reply to: Andrew Falanga (#3)

Re: How does psql actually implement the \d commands

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

Andrew Falanga

af300wsm@gmail.com

almost 18 years ago

In reply to: Andrew Falanga (#1)

Re: How does psql actually implement the \d commands

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