Thread: new format for describe (repost #2)
(This didnt' seem to get through the first two times :( )
Hi,
I have done up a new way of dumping table data:
Indexes:
"users_users_pkey" PRIMARY KEY btree (userid),
"users_users_username_key" UNIQUE btree (username),
"expiry_users_users_key" btree (expiry),
"users_users_email_lower_idx" btree (lower(email)),
"users_users_referrer_idx" btree (referrer),
"users_users_susp_off_idx" btree (suspended_off) WHERE (suspended_off IS
NOT NULL)
Check Constraints:
"users_users_sex" CHECK ((sex = 'M'::bpchar) OR (sex = 'F'::bpchar))
Foreign Key Constraints:
"$1" FOREIGN KEY (referrer) REFERENCES users_users(userid) ON DELETE SET
NULL
Rules:
"test_rule" AS ON INSERT TO users_users DO INSTEAD NOTHING;,
"test_rule2" AS ON INSERT TO users_users DO INSTEAD NOTHING;
Triggers:
"RI_ConstraintTrigger_1105134",
"RI_ConstraintTrigger_1105135",
"RI_ConstraintTrigger_1105138",
"RI_ConstraintTrigger_1105139",
"RI_ConstraintTrigger_1105396",
"RI_ConstraintTrigger_1105397"
I think this is a massive improvement, and far more readable, compared to
this:
Indexes: users_users_pkey primary key btree (userid),
users_users_username_key unique btree (username),
expiry_users_users_key btree (expiry),
users_users_email_lower_idx btree (lower(email)),
users_users_referrer_idx btree (referrer),
users_users_susp_off_idx btree (suspended_off) WHERE (suspended_off
IS NOT NULL)
Check constraints: "users_users_sex" ((sex = 'M'::bpchar) OR (sex =
'F'::bpchar))
Foreign Key constraints: $1 FOREIGN KEY (referrer) REFERENCES
users_users(userid) ON DELETE SET NULL
Rules: test_rule,
test_rule2
Triggers: RI_ConstraintTrigger_1105134,
RI_ConstraintTrigger_1105135,
RI_ConstraintTrigger_1105138,
RI_ConstraintTrigger_1105139,
RI_ConstraintTrigger_1105396,
RI_ConstraintTrigger_1105397
Shall I go ahead and tidy it up for submission?
I've also got 'pg_get_triggerdef' in the works so we can actually show
trigger definitions as well...
Chris
Attachment
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> I have done up a new way of dumping table data:
I don't care for the forced double quotes. Maybe if you double-quote
only identifiers that need it?
Upper-casing the keywords seems like a visual improvement.
I'm ambivalent about forcing the titles ("Indexes:" et al) onto their
own lines. It's more readable, maybe, but it makes the display take up
more vertical space. Having to scroll back negates any readability
improvement IMHO...
> Rules:
> "test_rule" AS ON INSERT TO users_users DO INSTEAD NOTHING;,
> "test_rule2" AS ON INSERT TO users_users DO INSTEAD NOTHING;
The semicolon-comma bit is weird-looking, and confusing. Maybe we
should lose the commas altogether.
regards, tom lane
> I don't care for the forced double quotes. Maybe if you double-quote
> only identifiers that need it?
I can do that. I did think about it, but it did seem to me that at a
glance, you can instantly pick out the names of the items from all the text,
rather than having to sort of search it. ie. it's consistent...
Ideally, I would have liked to have changed Index, Check Constraints and
Foreign Key Constraints into Constraints and Indexes, however I'm sure you
would prefer it if I made it clear that unique and primary constraints are,
in fact, indexes... I should suppress the 'btree' for primary and unique as
well since it's irrelevant.
> Upper-casing the keywords seems like a visual improvement.
Yep.
> I'm ambivalent about forcing the titles ("Indexes:" et al) onto their
> own lines. It's more readable, maybe, but it makes the display take up
> more vertical space. Having to scroll back negates any readability
> improvement IMHO...
Yes, but it improves horizontal utilization. Especially for long things
like Foreign Keys, it's annoying how they all start about 20 characters in
from the left - makes it quite hard to read...
Remember that most users in 7.3 shouldn't have all those triggers listed and
won't have rules. That will make it shorter in the general case...
> > Rules:
> > "test_rule" AS ON INSERT TO users_users DO INSTEAD NOTHING;,
> > "test_rule2" AS ON INSERT TO users_users DO INSTEAD NOTHING;
>
> The semicolon-comma bit is weird-looking, and confusing. Maybe we
> should lose the commas altogether.
yes, it is. The only way around it really is to mess about with not dumping
the last character of the pg_get_ruledef. Irritatingly enough,
pg_get_ruledef appends a semi-colon to its result, whereas all the others do
not...
Chris
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> ... Irritatingly enough, pg_get_ruledef appends a semi-colon to its
> result, whereas all the others do not...
Could we change that without breaking things? Not sure ...
regards, tom lane
> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes: > > ... Irritatingly enough, pg_get_ruledef appends a semi-colon to its > > result, whereas all the others do not... > > Could we change that without breaking things? Not sure ... I'm just going to hack something in to the sql query or the C code in psql to eliminate it...it's probably helpful to leave it there for back-compatibility, but then again, it is kind of annoying. And we don't know the dependencies... Chris
Christopher Kings-Lynne writes:
> I have done up a new way of dumping table data:
Perhaps the visualisation could be improved even more if the data where
output in table form?
> Indexes:
> "users_users_pkey" PRIMARY KEY btree (userid),
> "users_users_username_key" UNIQUE btree (username),
> "expiry_users_users_key" btree (expiry),
> "users_users_email_lower_idx" btree (lower(email)),
> "users_users_referrer_idx" btree (referrer),
> "users_users_susp_off_idx" btree (suspended_off) WHERE (suspended_off IS NOT NULL)
Indexes
Name | Type | Columns | Attributes
------------------+-------+---------+-------------
users_users_pkey | btree | userid | primary key
...
(To hell with vertical space ;-) )
> Check Constraints:
> "users_users_sex" CHECK ((sex = 'M'::bpchar) OR (sex = 'F'::bpchar))
Name | Condition
-----------------+--------------------------------------------
users_users_sex | (sex = 'M'::bpchar) OR (sex = 'F'::bpchar)
> Foreign Key Constraints:
> "$1" FOREIGN KEY (referrer) REFERENCES users_users(userid) ON DELETE SET NULL
Name | Referencing Col | Referenced Tbl | Referenced Col | Delete Action
------+-----------------+----------------+----------------+---------------...
$1 | referrer | users_users | userid | SET NULL
> Rules:
> "test_rule" AS ON INSERT TO users_users DO INSTEAD NOTHING;,
> "test_rule2" AS ON INSERT TO users_users DO INSTEAD NOTHING;
Name | Table | Event | Action
-----------+-------------+--------+-----------------
test_rule | users_users | INSERT | INSTEAD NOTHING
--
Peter Eisentraut peter_e@gmx.net
> Perhaps the visualisation could be improved even more if the data where > output in table form? Urg. That really will make it ugly! It's also _harder_ to read in table form, not easier. I mean, what's easier to read at a glance: "$1" FOREIGN KEY (referrer) REFERENCES users_users(userid) ON DELETE SET NULL or > Foreign Keys > Name | Referencing Col | Referenced Tbl | Referenced Col | Delete Action > ------+-----------------+----------------+----------------+--------------- ... > $1 | referrer | users_users | userid | SET NULL Chris
Christopher Kings-Lynne writes: > I mean, what's easier to read at a glance: > > "$1" FOREIGN KEY (referrer) REFERENCES users_users(userid) ON DELETE SET > NULL > > or > > > Foreign Keys > > Name | Referencing Col | Referenced Tbl | Referenced Col | Delete Action > > ------+-----------------+----------------+----------------+--------------- > ... > > $1 | referrer | users_users | userid | SET NULL The second. -- Peter Eisentraut peter_e@gmx.net
On Thu, 2003-03-06 at 18:07, Peter Eisentraut wrote: > Christopher Kings-Lynne writes: > > > I mean, what's easier to read at a glance: > > > > "$1" FOREIGN KEY (referrer) REFERENCES users_users(userid) ON DELETE SET > > NULL > > > > or > > > > > Foreign Keys > > > Name | Referencing Col | Referenced Tbl | Referenced Col | Delete Action > > > ------+-----------------+----------------+----------------+--------------- > > ... > > > $1 | referrer | users_users | userid | SET NULL > > The second. Really? The name is easier to pick out, but other than that.... I guess it's what you're used to looking at. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Attachment
Peter Eisentraut wrote: > Christopher Kings-Lynne writes: > > > I mean, what's easier to read at a glance: > > > > "$1" FOREIGN KEY (referrer) REFERENCES users_users(userid) ON DELETE SET > > NULL > > > > or > > > > > Foreign Keys > > > Name | Referencing Col | Referenced Tbl | Referenced Col | Delete Action > > > ------+-----------------+----------------+----------------+--------------- > > ... > > > $1 | referrer | users_users | userid | SET NULL > > The second. If there is only one line, the top one may be easier to read, but for many lines, the table format is probably clearer. Ideally, it would be good to see a before/after output for a typical table so we can see the changes more easily. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Tom Lane wrote: > "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes: > > ... Irritatingly enough, pg_get_ruledef appends a semi-colon to its > > result, whereas all the others do not... > > Could we change that without breaking things? Not sure ... I think we could change it. Only pg_dump uses that now, as far as I know, that there isn't much logic for pg_get_ruledef() to be appending semicolons. If folks want that, they should add them themselves. I already changed pg_get_ruledef() to not return non-default referential integrity actions, so we have been modifying it regularly. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
> If there is only one line, the top one may be easier to read, but for > many lines, the table format is probably clearer. Oh my - I so have to disagree! The table format is going to add at least 3 extra lines per group, remember. Also, it's very, very difficult to read the tables when there are multiple columns referring to other multiple columns - it'll look shocking... Chris
> I think we could change it. Only pg_dump uses that now, as far as I > know, that there isn't much logic for pg_get_ruledef() to be appending > semicolons. If folks want that, they should add them themselves. > > I already changed pg_get_ruledef() to not return non-default referential > integrity actions, so we have been modifying it regularly. OK, we can change it then. I was worrying more about applications that use that function. Chris