Thread: psql: show which tables contraints are for with a \d
-----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-----
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
>
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
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.
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
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.
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
>