Thread: Re: Indexes generated for primary key are not show

 

> -----Original Message-----
> From: Constantin Stefanov [mailto:cstef@mail.ru] 
> Sent: 09 February 2004 09:03
> To: Andreas Pflug
> Cc: pgadmin-support@postgresql.org
> Subject: Re: [pgadmin-support] Indexes generated for primary 
> key are not show
> 
> Why should such duplication be avoided? There is both 
> constraint (primary key) and index. 

Because if any generated SQL contains both object, it will be an invalid
script, not to mention the fact that you cannot properly recreate
constraints just by recreating the indexes.

> Constraint is something 
> logical, index is a way to implement it. When I 'connect' a 
> trigger to a table, there is trigger as a function, and 
> trigger usage for a table. Yes, index is more tightly 
> connected with its table, but in a case of a constraint it is 
> still an index but system-generated.

Yes, which is exactly why it's hidden - in the same way we hide the
rules that implement views.

> And if it would be an option to show such indexes, such 
> duplication is not a problem at all - if you turn this option 
> on (it may be off by default), you know what you are doing 
> and know that some constraints may be duplicated as indexes.

There speaks someone who doesn't provide support on these lists :-)

It will be a problem. People would switch on such an option whilst
playing and forget about it. We would then get lots of emails.

Regards, Dave.

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

Dave Page wrote:

>>Why should such duplication be avoided? There is both 
>>constraint (primary key) and index. 
> 
> Because if any generated SQL contains both object, it will be an invalid
> script, not to mention the fact that you cannot properly recreate
> constraints just by recreating the indexes.
Well, I did not think you were talking about generated SQL. I mean I
would like to see these indexes in left part of main pgadmin screen,
where all other (user-created) indexes are shown. I mostly do not look
at the generated SQL, so I did not think about it.
Maybe for such indexes there should be no generated SQL when you select
it (or some text that warns that this index is system-generated).

-- 
Constantin Stefanov

<span style="color: rgb(34, 34, 34); font-family: Consolas, 'Lucida     Console', monospace; font-size: 12px;
font-style:normal;     font-variant: normal; font-weight: normal; letter-spacing: normal;     line-height: normal;
orphans:auto; text-align: left; text-indent:     0px; text-transform: none; white-space: pre-wrap; widows: 1;
word-spacing:0px; -webkit-text-stroke-width: 0px; display: inline     !important; float: none; background-color:
rgb(255,255, 255);">I has wasted a lot of time today has trying to find my autogenerated indexes for primary keys,
whichare not shown in PGAdmin III browser tree, until write and execute<br /> SELECT * FROM pg_indexes i WHERE
i.tablename= <my table>;<br /> end have seen a real state of things.<br /><br /> I expect from database admin
toolto display clearly database objects which are in database, without trying to hide some of them by some ridiculous
reasonslike a executability of generated script.<br /> Please DO NOT HIDE ANYTHING! When You hide something, I can't
trustyour tool!<br /><br /> In SQL pane you can show DDL for object's like this inside a comment brackets /* and */
withpreamble inside 'This index are automatically generated with constraint:'</span><span style="color: rgb(34, 34,
34);font-family: Consolas, 'Lucida     Console', monospace; font-size: 12px; font-style: normal;     font-variant:
normal;font-weight: normal; letter-spacing: normal;     line-height: normal; orphans: auto; text-align: left;
text-indent:    0px; text-transform: none; white-space: pre-wrap; widows: 1;     word-spacing: 0px;
-webkit-text-stroke-width:0px; display: inline     !important; float: none; background-color: rgb(255, 255, 255);"><br
/><br/><br /> In late answer to:<br /><br /></span><blockquote><span style="color: rgb(51, 51, 51); font-family:
'HelveticaNeue', Helvetica, Arial, sans-serif; font-size:       12.6px; font-style: normal; font-variant: normal;
font-weight:      normal; letter-spacing: normal; line-height: 18px; orphans:       auto; text-align: left;
text-indent:0px; text-transform: none;       white-space: normal; widows: 1; word-spacing: 0px;
-webkit-text-stroke-width:0px; display: inline !important;       float: none; background-color: rgb(255, 255,
255);">Becauseif any generated SQL contains both object, it will be an invalid</span><br style="box-sizing: border-box;
border-radius:0px !important;       color: rgb(51, 51, 51); font-family: 'Helvetica Neue',       Helvetica, Arial,
sans-serif;font-size: 12.6px; font-style:       normal; font-variant: normal; font-weight: normal;
letter-spacing:normal; line-height: 18px; orphans: auto;       text-align: left; text-indent: 0px; text-transform:
none;      white-space: normal; widows: 1; word-spacing: 0px;       -webkit-text-stroke-width: 0px; background-color:
rgb(255,255,       255);" /><span style="color: rgb(51, 51, 51); font-family: 'Helvetica       Neue', Helvetica, Arial,
sans-serif;font-size: 12.6px;       font-style: normal; font-variant: normal; font-weight: normal;
letter-spacing:normal; line-height: 18px; orphans: auto;       text-align: left; text-indent: 0px; text-transform:
none;      white-space: normal; widows: 1; word-spacing: 0px;       -webkit-text-stroke-width: 0px; display: inline
!important;      float: none; background-color: rgb(255, 255, 255);">script, not to mention the fact that you cannot
properlyrecreate</span><br style="box-sizing: border-box; border-radius: 0px !important;       color: rgb(51, 51, 51);
font-family:'Helvetica Neue',       Helvetica, Arial, sans-serif; font-size: 12.6px; font-style:       normal;
font-variant:normal; font-weight: normal;       letter-spacing: normal; line-height: 18px; orphans: auto;
text-align:left; text-indent: 0px; text-transform: none;       white-space: normal; widows: 1; word-spacing: 0px;
-webkit-text-stroke-width:0px; background-color: rgb(255, 255,       255);" /><span style="color: rgb(51, 51, 51);
font-family:'Helvetica       Neue', Helvetica, Arial, sans-serif; font-size: 12.6px;       font-style: normal;
font-variant:normal; font-weight: normal;       letter-spacing: normal; line-height: 18px; orphans: auto;
text-align:left; text-indent: 0px; text-transform: none;       white-space: normal; widows: 1; word-spacing: 0px;
-webkit-text-stroke-width:0px; display: inline !important;       float: none; background-color: rgb(255, 255,
255);">constraintsjust by recreating the indexes.</span><br style="box-sizing:       border-box; border-radius: 0px
!important;color: rgb(51, 51,       51); font-family: 'Helvetica Neue', Helvetica, Arial,       sans-serif; font-size:
12.6px;font-style: normal; font-variant:       normal; font-weight: normal; letter-spacing: normal;       line-height:
18px;orphans: auto; text-align: left; text-indent:       0px; text-transform: none; white-space: normal; widows: 1;
 word-spacing: 0px; -webkit-text-stroke-width: 0px;       background-color: rgb(255, 255, 255);" /><div class="ql ql0"
style="box-sizing:border-box; border-radius:       0px !important; margin: 1em 1em 1em 0px; padding: 0px 0px 0px
0.4em;color: rgb(0, 0, 112); border-left-width: 2px;       border-left-style: solid; border-left-color: rgb(0, 0, 112);
     font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif;       font-size: 12.6px; font-style: normal;
font-variant:normal;       font-weight: normal; letter-spacing: normal; line-height: 18px;       orphans: auto;
text-align:left; text-indent: 0px;       text-transform: none; white-space: normal; widows: 1;       word-spacing: 0px;
-webkit-text-stroke-width:0px;       background-color: rgb(229, 229, 229);">Constraint is something<br
style="box-sizing:border-box; border-radius: 0px !important;" /> logical, index is a way to implement it. When I
'connect'a<br style="box-sizing: border-box; border-radius: 0px !important;" /> trigger to a table, there is trigger as
afunction, and<br style="box-sizing: border-box; border-radius: 0px !important;" /> trigger usage for a table. Yes,
indexis more tightly<br style="box-sizing: border-box; border-radius: 0px !important;" /> connected with its table, but
ina case of a constraint it is<br style="box-sizing: border-box; border-radius: 0px !important;" /> still an index but
system-generated.</div><spanstyle="color: rgb(51, 51, 51); font-family: 'Helvetica       Neue', Helvetica, Arial,
sans-serif;font-size: 12.6px;       font-style: normal; font-variant: normal; font-weight: normal;
letter-spacing:normal; line-height: 18px; orphans: auto;       text-align: left; text-indent: 0px; text-transform:
none;      white-space: normal; widows: 1; word-spacing: 0px;       -webkit-text-stroke-width: 0px; display: inline
!important;      float: none; background-color: rgb(255, 255, 255);">Yes, which is exactly why it's hidden - in the
sameway we hide the</span><br style="box-sizing: border-box; border-radius: 0px !important;       color: rgb(51, 51,
51);font-family: 'Helvetica Neue',       Helvetica, Arial, sans-serif; font-size: 12.6px; font-style:       normal;
font-variant:normal; font-weight: normal;       letter-spacing: normal; line-height: 18px; orphans: auto;
text-align:left; text-indent: 0px; text-transform: none;       white-space: normal; widows: 1; word-spacing: 0px;
-webkit-text-stroke-width:0px; background-color: rgb(255, 255,       255);" /><span style="color: rgb(51, 51, 51);
font-family:'Helvetica       Neue', Helvetica, Arial, sans-serif; font-size: 12.6px;       font-style: normal;
font-variant:normal; font-weight: normal;       letter-spacing: normal; line-height: 18px; orphans: auto;
text-align:left; text-indent: 0px; text-transform: none;       white-space: normal; widows: 1; word-spacing: 0px;
-webkit-text-stroke-width:0px; display: inline !important;       float: none; background-color: rgb(255, 255,
255);">rulesthat implement views.</span><br style="box-sizing: border-box;       border-radius: 0px !important; color:
rgb(51,51, 51);       font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif;       font-size: 12.6px; font-style:
normal;font-variant: normal;       font-weight: normal; letter-spacing: normal; line-height: 18px;       orphans: auto;
text-align:left; text-indent: 0px;       text-transform: none; white-space: normal; widows: 1;       word-spacing: 0px;
-webkit-text-stroke-width:0px;       background-color: rgb(255, 255, 255);" /><div class="ql ql0" style="box-sizing:
border-box;border-radius:       0px !important; margin: 1em 1em 1em 0px; padding: 0px 0px 0px       0.4em; color:
rgb(0,0, 112); border-left-width: 2px;       border-left-style: solid; border-left-color: rgb(0, 0, 112);
font-family:'Helvetica Neue', Helvetica, Arial, sans-serif;       font-size: 12.6px; font-style: normal; font-variant:
normal;      font-weight: normal; letter-spacing: normal; line-height: 18px;       orphans: auto; text-align: left;
text-indent:0px;       text-transform: none; white-space: normal; widows: 1;       word-spacing: 0px;
-webkit-text-stroke-width:0px;       background-color: rgb(229, 229, 229);">And if it would be an option to show such
indexes,such<br style="box-sizing:         border-box; border-radius: 0px !important;" /> duplication is not a problem
atall - if you turn this option<br style="box-sizing: border-box; border-radius: 0px !important;" /> on (it may be off
bydefault), you know what you are doing<br style="box-sizing: border-box; border-radius: 0px !important;" /> and know
thatsome constraints may be duplicated as indexes.</div><span style="color: rgb(51, 51, 51); font-family: 'Helvetica
  Neue', Helvetica, Arial, sans-serif; font-size: 12.6px;       font-style: normal; font-variant: normal; font-weight:
normal;      letter-spacing: normal; line-height: 18px; orphans: auto;       text-align: left; text-indent: 0px;
text-transform:none;       white-space: normal; widows: 1; word-spacing: 0px;       -webkit-text-stroke-width: 0px;
display:inline !important;       float: none; background-color: rgb(255, 255, 255);">There speaks someone who doesn't
providesupport on these lists :-)</span><br style="box-sizing: border-box; border-radius: 0px !important;       color:
rgb(51,51, 51); font-family: 'Helvetica Neue',       Helvetica, Arial, sans-serif; font-size: 12.6px; font-style:
normal;font-variant: normal; font-weight: normal;       letter-spacing: normal; line-height: 18px; orphans: auto;
text-align:left; text-indent: 0px; text-transform: none;       white-space: normal; widows: 1; word-spacing: 0px;
-webkit-text-stroke-width:0px; background-color: rgb(255, 255,       255);" /><br style="box-sizing: border-box;
border-radius:0px !important;       color: rgb(51, 51, 51); font-family: 'Helvetica Neue',       Helvetica, Arial,
sans-serif;font-size: 12.6px; font-style:       normal; font-variant: normal; font-weight: normal;
letter-spacing:normal; line-height: 18px; orphans: auto;       text-align: left; text-indent: 0px; text-transform:
none;      white-space: normal; widows: 1; word-spacing: 0px;       -webkit-text-stroke-width: 0px; background-color:
rgb(255,255,       255);" /><span style="color: rgb(51, 51, 51); font-family: 'Helvetica       Neue', Helvetica, Arial,
sans-serif;font-size: 12.6px;       font-style: normal; font-variant: normal; font-weight: normal;
letter-spacing:normal; line-height: 18px; orphans: auto;       text-align: left; text-indent: 0px; text-transform:
none;      white-space: normal; widows: 1; word-spacing: 0px;       -webkit-text-stroke-width: 0px; display: inline
!important;      float: none; background-color: rgb(255, 255, 255);">It will be a problem. People would switch on such
anoption whilst</span><br style="box-sizing: border-box; border-radius: 0px !important;       color: rgb(51, 51, 51);
font-family:'Helvetica Neue',       Helvetica, Arial, sans-serif; font-size: 12.6px; font-style:       normal;
font-variant:normal; font-weight: normal;       letter-spacing: normal; line-height: 18px; orphans: auto;
text-align:left; text-indent: 0px; text-transform: none;       white-space: normal; widows: 1; word-spacing: 0px;
-webkit-text-stroke-width:0px; background-color: rgb(255, 255,       255);" /><span style="color: rgb(51, 51, 51);
font-family:'Helvetica       Neue', Helvetica, Arial, sans-serif; font-size: 12.6px;       font-style: normal;
font-variant:normal; font-weight: normal;       letter-spacing: normal; line-height: 18px; orphans: auto;
text-align:left; text-indent: 0px; text-transform: none;       white-space: normal; widows: 1; word-spacing: 0px;
-webkit-text-stroke-width:0px; display: inline !important;       float: none; background-color: rgb(255, 255,
255);">playingand forget about it. We would then get lots of emails.</span><br style="box-sizing: border-box;
border-radius:0px !important;       color: rgb(51, 51, 51); font-family: 'Helvetica Neue',       Helvetica, Arial,
sans-serif;font-size: 12.6px; font-style:       normal; font-variant: normal; font-weight: normal;
letter-spacing:normal; line-height: 18px; orphans: auto;       text-align: left; text-indent: 0px; text-transform:
none;      white-space: normal; widows: 1; word-spacing: 0px;       -webkit-text-stroke-width: 0px; background-color:
rgb(255,255,       255);" /><br style="box-sizing: border-box; border-radius: 0px !important;       color: rgb(51, 51,
51);font-family: 'Helvetica Neue',       Helvetica, Arial, sans-serif; font-size: 12.6px; font-style:       normal;
font-variant:normal; font-weight: normal;       letter-spacing: normal; line-height: 18px; orphans: auto;
text-align:left; text-indent: 0px; text-transform: none;       white-space: normal; widows: 1; word-spacing: 0px;
-webkit-text-stroke-width:0px; background-color: rgb(255, 255,       255);" /><span style="color: rgb(51, 51, 51);
font-family:'Helvetica       Neue', Helvetica, Arial, sans-serif; font-size: 12.6px;       font-style: normal;
font-variant:normal; font-weight: normal;       letter-spacing: normal; line-height: 18px; orphans: auto;
text-align:left; text-indent: 0px; text-transform: none;       white-space: normal; widows: 1; word-spacing: 0px;
-webkit-text-stroke-width:0px; display: inline !important;       float: none; background-color: rgb(255, 255,
255);">Regards,Dave.</span><br /><span style="color: rgb(34, 34, 34); font-family: Consolas,       'Lucida Console',
monospace;font-size: 12px; font-style:       normal; font-variant: normal; font-weight: normal;       letter-spacing:
normal;line-height: normal; orphans: auto;       text-align: left; text-indent: 0px; text-transform: none;
white-space:pre-wrap; widows: 1; word-spacing: 0px;       -webkit-text-stroke-width: 0px; display: inline !important;
   float: none; background-color: rgb(255, 255, 255);"><a class="moz-txt-link-freetext"
href="mailto:pgadmin-support%40postgresql.org?In-Reply-To=%3C03AF4E498C591348A42FC93DEA9661B889F234%40mail.vale-housing.co.uk%3E&Subject=Re%3A%20Indexes%20generated%20for%20primary%20key%20are%20not%20show">mailto:pgadmin-support%40postgresql.org?In-Reply-To=%3C03AF4E498C591348A42FC93DEA9661B889F234%40mail.vale-housing.co.uk%3E&Subject=Re%3A%20Indexes%20generated%20for%20primary%20key%20are%20not%20show</a></span><br
/><spanstyle="color: rgb(34, 34, 34); font-family: Consolas,       'Lucida Console', monospace; font-size: 12px;
font-style:      normal; font-variant: normal; font-weight: normal;       letter-spacing: normal; line-height: normal;
orphans:auto;       text-align: left; text-indent: 0px; text-transform: none;       white-space: pre-wrap; widows: 1;
word-spacing:0px;       -webkit-text-stroke-width: 0px; display: inline !important;       float: none;
background-color:rgb(255, 255, 255);"> </span><a class="moz-txt-link-freetext"
href="http://grokbase.com/p/postgresql/pgadmin-support/0429k5bc6j/indexes-generated-for-primary-key-are-not-show">http://grokbase.com/p/postgresql/pgadmin-support/0429k5bc6j/indexes-generated-for-primary-key-are-not-show</a><br
/></blockquote>

Hello all,

18.05.2016 20:09, Ненашев Илья Николаевич wrote:
>    I has wasted a lot of time today has trying to find my autogenerated
> indexes for primary keys, which are not shown in PGAdmin III browser
> tree, until write and execute
> SELECT * FROM pg_indexes i WHERE i.tablename = <my table>;
> end have seen a real state of things.
>
> I expect from database admin tool to display clearly database objects

I'd desperately agree that the invisibility of primary indexes is quite 
a strange feature. How do you even check such index's size and other 
statistics then? (Ok, other than typing some sql by hand...)


Thank you,
Nikolai

> which are in database, without trying to hide some of them by some
> ridiculous reasons like a executability of generated script.
> Please DO NOT HIDE ANYTHING! When You hide something, I can't trust your
> tool!
>
> In SQL pane you can show DDL for object's like this inside a comment
> brackets /* and */ with preamble inside 'This index are automatically
> generated with constraint:'
>
>
> In late answer to:
>
>     Because if any generated SQL contains both object, it will be an invalid
>     script, not to mention the fact that you cannot properly recreate
[...]