psql: show which tables contraints are for with a \d

Started by Greg Sabino Mullanealmost 24 years ago6 messagespatches

greg@turnstep.com

almost 24 years ago

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
NotDashEscaped: You need GnuPG to verify this message

This (inline) patch makes the display of trigggers a little less
hideous when you have many foreign keys, which causese the
creation of many RI_ConstraintTrigger_XXXXX triggers. If the
trigger is actually a constraint, it will show table the
constraint is for. Not much, but a little better than seeing
row after row of auto-generated trigger names.

Old way:

greg=# \d alphabet
Table "public.alphabet"
Column | Type | Modifiers
--------+---------+-----------
aa | integer | not null
bb | integer |
cc | integer |
Indexes: alphabet_pkey primary key btree (aa),
alphabet_bb_key unique btree (bb),
alphabet_cc_key unique btree (cc)
Triggers: RI_ConstraintTrigger_16880,
RI_ConstraintTrigger_16881,
RI_ConstraintTrigger_16884,
RI_ConstraintTrigger_16885,
RI_ConstraintTrigger_16888,
my_little_trigger,
another_custom_trigger

greg=# \d alphabet
Table "public.alphabet"
Column | Type | Modifiers
--------+---------+-----------
aa | integer | not null
bb | integer |
cc | integer |
Indexes: alphabet_pkey primary key btree (aa),
alphabet_bb_key unique btree (bb),
alphabet_cc_key unique btree (cc)
Triggers: RI_ConstraintTrigger_16880 (Constraint on public.foobar),
RI_ConstraintTrigger_16881 (Constraint on public.foobar),
RI_ConstraintTrigger_16884 (Constraint on public.mikey),
RI_ConstraintTrigger_16885 (Constraint on greg.watson),
RI_ConstraintTrigger_16888 (Constraint on greg.crick),
my_little_trigger,
another_custom_trigger

Index: describe.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/describe.c,v
retrieving revision 1.60
diff -r1.60 describe.c
972c972,975
< 					"SELECT t.tgname\n"
---

"SELECT t.tgname || \n"
" COALESCE((SELECT ' (' || '%s ' || n.nspname || '.' || c.relname || ')'\n"
" FROM pg_class c, pg_namespace n\n"
" WHERE c.relnamespace = n.oid AND c.oid = t.tgconstrrelid), '')\n"

975c978
< oid);
---

_("Constraint on"), oid);

Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200208151547
-----BEGIN PGP SIGNATURE-----
Comment: For info see http://www.gnupg.org

iD8DBQE9XBZKvJuQZxSWSsgRAvVEAKCkZ+XXSsdRa0c4aAtThFo9A5So8gCg8ONH
WFL0VcOxndDiRcIUezggao8=
=U/NJ
-----END PGP SIGNATURE-----

Rod Taylor

rbt@rbt.ca

almost 24 years ago

In reply to: Greg Sabino Mullane (#1)

Re: psql: show which tables contraints are for with a \d

It should be noted that my patch in the queue displays the actual
foreign key definition and suppresses 'Constraint' Triggers.

This is a touch better than the below with the side effect that if a
user issues a CREATE CONSTRAINT TRIGGER manually it is mistakenly
hidden.

Anyway, which one is applied is up to you guys.

Show quoted text

On Thu, 2002-08-15 at 16:53, Greg Sabino Mullane wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
NotDashEscaped: You need GnuPG to verify this message

This (inline) patch makes the display of trigggers a little less
hideous when you have many foreign keys, which causese the
creation of many RI_ConstraintTrigger_XXXXX triggers. If the
trigger is actually a constraint, it will show table the
constraint is for. Not much, but a little better than seeing
row after row of auto-generated trigger names.

Old way:

greg=# \d alphabet
Table "public.alphabet"
Column | Type | Modifiers
--------+---------+-----------
aa | integer | not null
bb | integer |
cc | integer |
Indexes: alphabet_pkey primary key btree (aa),
alphabet_bb_key unique btree (bb),
alphabet_cc_key unique btree (cc)
Triggers: RI_ConstraintTrigger_16880,
RI_ConstraintTrigger_16881,
RI_ConstraintTrigger_16884,
RI_ConstraintTrigger_16885,
RI_ConstraintTrigger_16888,
my_little_trigger,
another_custom_trigger

greg=# \d alphabet
Table "public.alphabet"
Column | Type | Modifiers
--------+---------+-----------
aa | integer | not null
bb | integer |
cc | integer |
Indexes: alphabet_pkey primary key btree (aa),
alphabet_bb_key unique btree (bb),
alphabet_cc_key unique btree (cc)
Triggers: RI_ConstraintTrigger_16880 (Constraint on public.foobar),
RI_ConstraintTrigger_16881 (Constraint on public.foobar),
RI_ConstraintTrigger_16884 (Constraint on public.mikey),
RI_ConstraintTrigger_16885 (Constraint on greg.watson),
RI_ConstraintTrigger_16888 (Constraint on greg.crick),
my_little_trigger,
another_custom_trigger
Index: describe.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/describe.c,v
retrieving revision 1.60
diff -r1.60 describe.c
972c972,975
< 					"SELECT t.tgname\n"
---
"SELECT t.tgname || \n"
" COALESCE((SELECT ' (' || '%s ' || n.nspname || '.' || c.relname || ')'\n"
" FROM pg_class c, pg_namespace n\n"
" WHERE c.relnamespace = n.oid AND c.oid = t.tgconstrrelid), '')\n"

975c978
< oid);
---

_("Constraint on"), oid);

Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200208151547
-----BEGIN PGP SIGNATURE-----
Comment: For info see http://www.gnupg.org

iD8DBQE9XBZKvJuQZxSWSsgRAvVEAKCkZ+XXSsdRa0c4aAtThFo9A5So8gCg8ONH
WFL0VcOxndDiRcIUezggao8=
=U/NJ
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Tom Lane

tgl@sss.pgh.pa.us

almost 24 years ago

In reply to: Rod Taylor (#2)

Re: psql: show which tables contraints are for with a \d

Rod Taylor <rbt@zort.ca> writes:

It should be noted that my patch in the queue displays the actual
foreign key definition and suppresses 'Constraint' Triggers.

This is a touch better than the below with the side effect that if a
user issues a CREATE CONSTRAINT TRIGGER manually it is mistakenly
hidden.

I was thinking of looking into pg_depend to discover whether a trigger
is associated with a constraint, and hiding the trigger if and only if
we have a constraint to show instead. Anyone want to pursue that?

regards, tom lane

Rod Taylor

rbt@rbt.ca

almost 24 years ago

In reply to: Tom Lane (#3)

Re: psql: show which tables contraints are for with a \d

On Fri, 2002-08-16 at 01:15, Tom Lane wrote:

Rod Taylor <rbt@zort.ca> writes:

It should be noted that my patch in the queue displays the actual
foreign key definition and suppresses 'Constraint' Triggers.

This is a touch better than the below with the side effect that if a
user issues a CREATE CONSTRAINT TRIGGER manually it is mistakenly
hidden.

I was thinking of looking into pg_depend to discover whether a trigger
is associated with a constraint, and hiding the trigger if and only if
we have a constraint to show instead. Anyone want to pursue that?

I had assumed that since the keyword CONSTRAINT isn't documented on the
CREATE TRIGGER page, that the check for isconstraint would be enough?

Erm, except legacy stuff would be hidden.

Sure, I'll fix it up.

Bruce Momjian

bruce@momjian.us

almost 24 years ago

In reply to: Rod Taylor (#2)

Re: psql: show which tables contraints are for with a \d

Rod, your patch is in CVS now, right?

---------------------------------------------------------------------------

Rod Taylor wrote:

It should be noted that my patch in the queue displays the actual
foreign key definition and suppresses 'Constraint' Triggers.

This is a touch better than the below with the side effect that if a
user issues a CREATE CONSTRAINT TRIGGER manually it is mistakenly
hidden.

Anyway, which one is applied is up to you guys.

On Thu, 2002-08-15 at 16:53, Greg Sabino Mullane wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
NotDashEscaped: You need GnuPG to verify this message

This (inline) patch makes the display of trigggers a little less
hideous when you have many foreign keys, which causese the
creation of many RI_ConstraintTrigger_XXXXX triggers. If the
trigger is actually a constraint, it will show table the
constraint is for. Not much, but a little better than seeing
row after row of auto-generated trigger names.

Old way:

greg=# \d alphabet
Table "public.alphabet"
Column | Type | Modifiers
--------+---------+-----------
aa | integer | not null
bb | integer |
cc | integer |
Indexes: alphabet_pkey primary key btree (aa),
alphabet_bb_key unique btree (bb),
alphabet_cc_key unique btree (cc)
Triggers: RI_ConstraintTrigger_16880,
RI_ConstraintTrigger_16881,
RI_ConstraintTrigger_16884,
RI_ConstraintTrigger_16885,
RI_ConstraintTrigger_16888,
my_little_trigger,
another_custom_trigger

greg=# \d alphabet
Table "public.alphabet"
Column | Type | Modifiers
--------+---------+-----------
aa | integer | not null
bb | integer |
cc | integer |
Indexes: alphabet_pkey primary key btree (aa),
alphabet_bb_key unique btree (bb),
alphabet_cc_key unique btree (cc)
Triggers: RI_ConstraintTrigger_16880 (Constraint on public.foobar),
RI_ConstraintTrigger_16881 (Constraint on public.foobar),
RI_ConstraintTrigger_16884 (Constraint on public.mikey),
RI_ConstraintTrigger_16885 (Constraint on greg.watson),
RI_ConstraintTrigger_16888 (Constraint on greg.crick),
my_little_trigger,
another_custom_trigger
Index: describe.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/describe.c,v
retrieving revision 1.60
diff -r1.60 describe.c
972c972,975
< 					"SELECT t.tgname\n"
---
"SELECT t.tgname || \n"
" COALESCE((SELECT ' (' || '%s ' || n.nspname || '.' || c.relname || ')'\n"
" FROM pg_class c, pg_namespace n\n"
" WHERE c.relnamespace = n.oid AND c.oid = t.tgconstrrelid), '')\n"

975c978
< oid);
---

_("Constraint on"), oid);

Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200208151547
-----BEGIN PGP SIGNATURE-----
Comment: For info see http://www.gnupg.org

iD8DBQE9XBZKvJuQZxSWSsgRAvVEAKCkZ+XXSsdRa0c4aAtThFo9A5So8gCg8ONH
WFL0VcOxndDiRcIUezggao8=
=U/NJ
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

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

Rod Taylor

rbt@rbt.ca

almost 24 years ago

In reply to: Bruce Momjian (#5)

Re: psql: show which tables contraints are for with a \d

Don't know if it could be considered my patch anymore (Tom did quite a
bit of work) but yes, this item has been applied.

Show quoted text

On Tue, 2002-08-27 at 15:24, Bruce Momjian wrote:

Rod, your patch is in CVS now, right?

---------------------------------------------------------------------------

Rod Taylor wrote:
It should be noted that my patch in the queue displays the actual
foreign key definition and suppresses 'Constraint' Triggers.

This is a touch better than the below with the side effect that if a
user issues a CREATE CONSTRAINT TRIGGER manually it is mistakenly
hidden.

Anyway, which one is applied is up to you guys.

On Thu, 2002-08-15 at 16:53, Greg Sabino Mullane wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
NotDashEscaped: You need GnuPG to verify this message

This (inline) patch makes the display of trigggers a little less
hideous when you have many foreign keys, which causese the
creation of many RI_ConstraintTrigger_XXXXX triggers. If the
trigger is actually a constraint, it will show table the
constraint is for. Not much, but a little better than seeing
row after row of auto-generated trigger names.

Old way:

greg=# \d alphabet
Table "public.alphabet"
Column | Type | Modifiers
--------+---------+-----------
aa | integer | not null
bb | integer |
cc | integer |
Indexes: alphabet_pkey primary key btree (aa),
alphabet_bb_key unique btree (bb),
alphabet_cc_key unique btree (cc)
Triggers: RI_ConstraintTrigger_16880,
RI_ConstraintTrigger_16881,
RI_ConstraintTrigger_16884,
RI_ConstraintTrigger_16885,
RI_ConstraintTrigger_16888,
my_little_trigger,
another_custom_trigger

greg=# \d alphabet
Table "public.alphabet"
Column | Type | Modifiers
--------+---------+-----------
aa | integer | not null
bb | integer |
cc | integer |
Indexes: alphabet_pkey primary key btree (aa),
alphabet_bb_key unique btree (bb),
alphabet_cc_key unique btree (cc)
Triggers: RI_ConstraintTrigger_16880 (Constraint on public.foobar),
RI_ConstraintTrigger_16881 (Constraint on public.foobar),
RI_ConstraintTrigger_16884 (Constraint on public.mikey),
RI_ConstraintTrigger_16885 (Constraint on greg.watson),
RI_ConstraintTrigger_16888 (Constraint on greg.crick),
my_little_trigger,
another_custom_trigger
Index: describe.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/describe.c,v
retrieving revision 1.60
diff -r1.60 describe.c
972c972,975
< 					"SELECT t.tgname\n"
---
"SELECT t.tgname || \n"
" COALESCE((SELECT ' (' || '%s ' || n.nspname || '.' || c.relname || ')'\n"
" FROM pg_class c, pg_namespace n\n"
" WHERE c.relnamespace = n.oid AND c.oid = t.tgconstrrelid), '')\n"

975c978
< oid);
---

_("Constraint on"), oid);

Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200208151547
-----BEGIN PGP SIGNATURE-----
Comment: For info see http://www.gnupg.org

iD8DBQE9XBZKvJuQZxSWSsgRAvVEAKCkZ+XXSsdRa0c4aAtThFo9A5So8gCg8ONH
WFL0VcOxndDiRcIUezggao8=
=U/NJ
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
-- 
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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly