Обсуждение: 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