Re: [HACKERS] PATCH: psql show index with type info

[Amos Bird]

Ah, thanks for the suggestions. I'll revise this patch soon :)

Fabien COELHO  writes:

>> Done.
>
> Ok. The file should be named "v2".
>
>> Would you like to be the reviewer?
>
> Dunno. At least I wanted to have a look at it!
>
> My 0.02€:
>
> I think that the improvement provided is worthwhile.
>
> Two questions: Why no documentation update? Why no non-regressions 
> tests?
>
> As far as the output is concerned, ISTM that "btree index", "hash index" 
> and so would sound nicer than "index: sub-type".
>
> I'm wondering about the sub-query implementation: Maybe an outer join 
> could bring the same result with a more relational & elegant query.
>
> The "gettext_noop" call does not seem to make sense: the point is to 
> translate the string, and there is no way to translate "index:  query>". The result of the query should be translated if this is what is 
> wanted, and that can only be by hand:
>
>CASE amname || ' index'
>  WHEN 'hash index' THEN '%s' ...
>  WHEN ...
>  ELSE amname || ' index' # untranslated...
>END
>
> gettext_noop('hash index') # get translation for 'hash index'
> ...



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PATCH: psql show index with type info

[Amos Bird]

Done. Would you like to be the reviewer? Thanks! diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 0f9f497..a6dc599 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3284,7 +3284,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 	  " WHEN " CppAsString2(RELKIND_RELATION) " THEN '%s'"
 	  " WHEN " CppAsString2(RELKIND_VIEW) " THEN '%s'"
 	  " WHEN " CppAsString2(RELKIND_MATVIEW) " THEN '%s'"
-	  " WHEN " CppAsString2(RELKIND_INDEX) " THEN '%s'"
+	  " WHEN " CppAsString2(RELKIND_INDEX) " THEN %s"
 	  " WHEN " CppAsString2(RELKIND_SEQUENCE) " THEN '%s'"
 	  " WHEN 's' THEN '%s'"
 	  " WHEN " CppAsString2(RELKIND_FOREIGN_TABLE) " THEN '%s'"
@@ -3296,7 +3296,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
 	  gettext_noop("table"),
 	  gettext_noop("view"),
 	  gettext_noop("materialized view"),
-	  gettext_noop("index"),
+	  gettext_noop("'index: '||(select amname from pg_am a where a.oid = c.relam)"),
 	  gettext_noop("sequence"),
 	  gettext_noop("special"),
 	  gettext_noop("foreign table"),
.

Fabien COELHO  writes:

>> I'm not sure where to add documentations about this patch or if needed one. 
>> Please help
>> me if you think this patch is useful.
>
> This patch does not apply anymore. Are you planning to update it?

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PATCH: psql show index with type info

[Amos Bird]

Yeah, I'm thinking about that too. Here is a full list of the original
type values,

"Schema"
"Name"
"table"
"view"
"materialized view"
"index"
"sequence"
"special"
"foreign table"
"table"

What else do you think will benefit from extra type information?

regards,
Amos

Stephen Frost <sfr...@snowman.net> writes:

> Amos,
>
> * Amos Bird (amosb...@gmail.com) wrote:
>> Well, the prefix is used to differentiate other \d commands, like
>> this,
>
> Ah, ok, fair enough.
>
> Should we consider differentiating different table types also?  I
> suppose those are primairly just logged and unlogged, but I could see
> that being useful information to know when doing a \dt.  Not a big deal
> either way though, and this patch stands on its own certainly.
>
> Thanks!
>
> Stephen



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PATCH: psql show index with type info

[Amos Bird]

Hello Stephen,

Well, the prefix is used to differentiate other \d commands, like
this,

amos=# \ditv
  List of relations
 Schema |Name| Type | Owner |  Table
++--+---+-
 public | i  | table| amos  |
 public | ii | index: gist  | amos  | i
 public | j  | table| amos  |
 public | jj | index: gin   | amos  | i
 public | jp | index: btree | amos  | i
 public | js | index: brin  | amos  | i
 public | numbers| table| amos  |
 public | numbers_mod2   | index: gin   | amos  | numbers
 public | numbers_mod2_btree | index: btree | amos  | numbers
 public | ts | table| amos  |
(10 rows)

regards,
Amos

Stephen Frost <sfr...@snowman.net> writes:

> Greetings,
>
> * Amos Bird (amosb...@gmail.com) wrote:
>> psql currently supports \di+ to view indexes,
>> 
>>   List of relations
>>  Schema |Name| Type  | Owner |  Table  |  Size  | Description
>> ++---+---+-++-
>>  public | ii | index | amos  | i   | 131 MB |
>>  public | jj | index | amos  | i   | 12 MB  |
>>  public | kk | index | amos  | i   | 456 kB |
>>  public | numbers_mod2   | index | amos  | numbers | 10 MB  |
>>  public | numbers_mod2_btree | index | amos  | numbers | 214 MB |
>> (5 rows)
>> 
>> The co
>> lumn "Type" is kinda useless (all equals to index). Representing
>> the actual index type will be more interesting,
>
> Agreed.
>
>>  Schema |Name| Type | Owner |  Table  |  Size  | 
>> Description
>> ++--+---+-++-
>>  public | ii | index: gist  | amos  | i   | 131 MB |
>>  public | jj | index: gin   | amos  | i   | 12 MB  |
>>  public | kk | index: btree | amos  | i   | 456 kB |
>>  public | numbers_mod2   | index: gin   | amos  | numbers | 10 MB  |
>>  public | numbers_mod2_btree | index: btree | amos  | numbers | 214 MB |
>> (5 rows)
>> 
>> I'm not sure where to add documentations about this patch or if needed one. 
>> Please help
>> me if you think this patch is useful.
>
> I'm not sure why it's useful to keep the 'index:'?  I would suggest we
> just drop that and keep only the actual index type (gist, gin, etc).
>
> Thanks!
>
> Stephen



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] PATCH: psql show index with type info

[Amos Bird]

psql currently supports \di+ to view indexes,

  List of relations
 Schema |Name| Type  | Owner |  Table  |  Size  | Description
++---+---+-++-
 public | ii | index | amos  | i   | 131 MB |
 public | jj | index | amos  | i   | 12 MB  |
 public | kk | index | amos  | i   | 456 kB |
 public | numbers_mod2   | index | amos  | numbers | 10 MB  |
 public | numbers_mod2_btree | index | amos  | numbers | 214 MB |
(5 rows)

The co
lumn "Type" is kinda useless (all equals to index). Representing
the actual index type will be more interesting,

 Schema |Name| Type | Owner |  Table  |  Size  | 
Description
++--+---+-++-
 public | ii | index: gist  | amos  | i   | 131 MB |
 public | jj | index: gin   | amos  | i   | 12 MB  |
 public | kk | index: btree | amos  | i   | 456 kB |
 public | numbers_mod2   | index: gin   | amos  | numbers | 10 MB  |
 public | numbers_mod2_btree | index: btree | amos  | numbers | 214 MB |
(5 rows)

I'm not sure where to add documentations about this patch or if needed one. 
Please help
me if you think this patch is useful.

Best regards,
Amos

diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index e2e4cbc..ac27662 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3169,7 +3169,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
  " WHEN 'r' THEN '%s'"
  " WHEN 'v' THEN '%s'"
  " WHEN 'm' THEN '%s'"
- " WHEN 'i' THEN '%s'"
+ " WHEN 'i' THEN %s"
  " WHEN 'S' THEN '%s'"
  " WHEN 's' THEN '%s'"
  " WHEN 'f' THEN '%s'"
@@ -3181,7 +3181,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
  gettext_noop("table"),
  gettext_noop("view"),
  gettext_noop("materialized view"),
- gettext_noop("index"),
+ gettext_noop("'index: '||(select amname from pg_am a where a.oid = c.relam)"),
  gettext_noop("sequence"),
  gettext_noop("special"),
  gettext_noop("foreign table"),

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] make default TABLESPACE belong to target table.

[Amos Bird]

Amit Kapila writes:

> Another point to think in this regard is what if tomorrow somebody
> requests something similar for Create Materialized View? Isn't it
> better to introduce a GUC default_tablespace_parent or
> default_parent_tablespace?

That's exactly what I have in mind :)


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] make default TABLESPACE belong to target table.

[Amos Bird]

Jim Nasby writes:

> On 11/27/16 2:01 AM, Amos Bird wrote:
>>
>>> I was thinking about introducing a new GUC, named something like
>>> default_index_tablespace, which would need to have at least these
>>> behaviors:
>>
>> Should we also consider the chidren tables as mentioned by Corey?
>
> Wouldn't point #2 handle that?

Hmm, does index tablespace also take care of child tables' tablespace? I
was thinking it only handles indexes.

regards,
Amos


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] make default TABLESPACE belong to target table.

[Amos Bird]

> I was thinking about introducing a new GUC, named something like
> default_index_tablespace, which would need to have at least these
> behaviors:

Should we also consider the chidren tables as mentioned by Corey?


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] make default TABLESPACE belong to target table.

[Amos Bird]

> The only scenario where this would be useful is when using ALTER TABLE
> ADD CONSTRAINT in which case a fresh index is built (not USING INDEX).
> That's a bit narrow, because it would mean that you would either
> append a TABLESPACE clause to this existing clause, or create a
> storage parameter to enforce all indexes created for a relation on a
> wanted tablespace... For the other cases you could just do something
> like that, and that's what the large majority of people would care
> about:
> SET default_tablespace TO 'foo';
> CREATE TABLE foobar (id int PRIMARY KEY);
> But that's not the one you are interesting in, so likely a storage
> parameter is what pops up in my mind, with parameter defined at table
> creation: CREATE TABLE foo (id primary key) WITH
> (constraint_default_tablespace = foo) TABLESPACE bar;
> In this case the parent relation gets created in tablespace bar, but
> its primary key gets in tablespace foo.

How about making a storage parameter "default_tablespace" that also
covers CREATE INDEX and other stuff?



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] make default TABLESPACE belong to target table.

[Amos Bird]

> I had a similar problem in writing the range_partitioning extension: CREATE
> TABLE y (LIKE x INCLUDING ALL) didn't set the tablespace of y to match x.
> I don't have a solution, I'm just indicating a similar need in userland.

Cool, I didn't think of that. It seems this feature is at least useful
for extension devs like us. I'll start coding a POC patch. What do you
think of making default tablespace derived from parent table?



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] make default TABLESPACE belong to target table.

[Amos Bird]

> So you would like locate those index tablespaces into the same
> tablespace as its parent relation when the index is created for a
> unique index or as a primary key?

Yes, and I'd like this behavior take effect when default_tablespace is
set to something like "parent".

> But what would be the difference with default_tablespace?

What do you mean? AFAIK, default_tablespace option cannot tell which
tablespace the parent table is in.

> I think that you are looking for a replacement for something that is
> already doable.

Hmm, I've done my research and asked around IRC channels. There is
little info come to my mind. could you give me some hint?

Michael Paquier writes:

> On Fri, Nov 25, 2016 at 4:48 PM, Amos Bird <amosb...@gmail.com> wrote:
>> I've been using postgres for a long time. Recently I'm doing table
>> sharding over a bunch of pgsql instances. I'm using multiple tablespaces
>> one per disk to utilize all the IO bandwidth. Things went on pretty
>> well, however there is a troublesome problem I have when adding
>> auxiliary structures to sharded tables, such as Indexes. These objects
>> have their storage default to the database's tablespace, and it's
>> difficult to shard them by hand.
>>
>> I'd like to implement this small feature --- making table's auxiliary
>> structures store their data to the target table's tablespace by default.
>> I've done a thorough search over the mailing list and there is nothing
>> relevant. Well I may miss some corners :-)
>
> So you would like locate those index tablespaces into the same
> tablespace as its parent relation when the index is created for a
> unique index or as a primary key? Perhaps we could have a
> session-level parameter that enforces the creation of such indexes on
> the same tablespace as the table... But what would be the difference
> with default_tablespace? I think that you are looking for a
> replacement for something that is already doable.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] make default TABLESPACE belong to target table.

[Amos Bird]

Dear pgsql community,

I've been using postgres for a long time. Recently I'm doing table
sharding over a bunch of pgsql instances. I'm using multiple tablespaces
one per disk to utilize all the IO bandwidth. Things went on pretty
well, however there is a troublesome problem I have when adding
auxiliary structures to sharded tables, such as Indexes. These objects
have their storage default to the database's tablespace, and it's
difficult to shard them by hand.

I'd like to implement this small feature --- making table's auxiliary
structures store their data to the target table's tablespace by default.
I've done a thorough search over the mailing list and there is nothing
relevant. Well I may miss some corners :-)

What do you think?

Regards,
Amos


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers