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.

[Amit Kapila]

On Mon, Nov 28, 2016 at 6:19 PM, Amit Kapila  wrote:
> On Sat, Nov 26, 2016 at 9:46 PM, Tom Lane  wrote:
>>
>> If we just did points 1 and 2 then a bool GUC would suffice.  I'm
>> not sure how to handle all three cases cleanly.  We could define
>> default_index_tablespace as empty to get point 1 or a tablespace
>> name to get point 3, but that leaves us having to use some magic
>> string for point 2, which would be messy --- what if it conflicts
>> with someone's choice of a tablespace name?
>>
>
> Yeah, I think coming with a clean way to handle all three might be
> messy.  How about if just handle 2 and 3?
>

Or maybe just 1 and 2 with a bool GUC.  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?



-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com


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

[Amit Kapila]

On Sat, Nov 26, 2016 at 9:46 PM, Tom Lane  wrote:
> Amit Kapila  writes:
>> What will such a storage parameter (default_tablespace) mean at table
>> level and how it will different from existing default_tablespace?  I
>> think the usage asked by Amos is quite genuine, but not sure if
>> introducing default_tablespace as a storage level parameter is the
>> best way to address it.  Another way could be that we allow the user
>> to specify something like tablespace_name /> table> or something like that.
>
> That seems overcomplicated, and it will also pose quite some hazard
> for pg_dump for example.  It feels like "action at a distance", in
> that creation of an index will now depend on properties that aren't
> immediately obvious.
>
> I was thinking about introducing a new GUC, named something like
> default_index_tablespace, which would need to have at least these
> behaviors:
>
> 1. index tablespace is same as default_tablespace (the backwards
> compatible, and therefore the default, behavior).
>
> 2. index tablespace is same as table's tablespace.
>
> 3. default_index_tablespace names a specific tablespace.
>
> Point 3 isn't in the current request but I'm pretty sure I've heard
> it requested in the past, so that people can conveniently put all
> tables in tablespace X and all indexes in tablespace Y.
>
> If we just did points 1 and 2 then a bool GUC would suffice.  I'm
> not sure how to handle all three cases cleanly.  We could define
> default_index_tablespace as empty to get point 1 or a tablespace
> name to get point 3, but that leaves us having to use some magic
> string for point 2, which would be messy --- what if it conflicts
> with someone's choice of a tablespace name?
>

Yeah, I think coming with a clean way to handle all three might be
messy.  How about if just handle 2 and 3?  If the table is created
with default_tablespace, then automatically it will be created in
default_tablespace.  Do you think maintaining backward compatibility
is important in this case?


-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com


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

[Jim Nasby]

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?


2. index tablespace is same as table's tablespace.


As for a magic value, based on other recent discussion the idea of "-1" 
came to me, since per the docs[1] that's not a valid identifier... but 
experimentation shows it's fine if you double-quote it. :/


1: 
https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


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

[Michael Paquier]

On Sun, Nov 27, 2016 at 1:16 AM, Tom Lane  wrote:
> If we just did points 1 and 2 then a bool GUC would suffice.  I'm
> not sure how to handle all three cases cleanly.  We could define
> default_index_tablespace as empty to get point 1 or a tablespace
> name to get point 3, but that leaves us having to use some magic
> string for point 2, which would be messy --- what if it conflicts
> with someone's choice of a tablespace name?

Just using a special value for default_tablespace would be enough as well.
-- 
Michael


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

[Tom Lane]

Amit Kapila  writes:
> What will such a storage parameter (default_tablespace) mean at table
> level and how it will different from existing default_tablespace?  I
> think the usage asked by Amos is quite genuine, but not sure if
> introducing default_tablespace as a storage level parameter is the
> best way to address it.  Another way could be that we allow the user
> to specify something like tablespace_name / table> or something like that.

That seems overcomplicated, and it will also pose quite some hazard
for pg_dump for example.  It feels like "action at a distance", in
that creation of an index will now depend on properties that aren't
immediately obvious.

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

1. index tablespace is same as default_tablespace (the backwards
compatible, and therefore the default, behavior).

2. index tablespace is same as table's tablespace.

3. default_index_tablespace names a specific tablespace.

Point 3 isn't in the current request but I'm pretty sure I've heard
it requested in the past, so that people can conveniently put all
tables in tablespace X and all indexes in tablespace Y.

If we just did points 1 and 2 then a bool GUC would suffice.  I'm
not sure how to handle all three cases cleanly.  We could define
default_index_tablespace as empty to get point 1 or a tablespace
name to get point 3, but that leaves us having to use some magic
string for point 2, which would be messy --- what if it conflicts
with someone's choice of a tablespace name?

regards, tom lane


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

[Amit Kapila]

On Sat, Nov 26, 2016 at 8:57 AM, Michael Paquier
 wrote:
> On Sat, Nov 26, 2016 at 11:25 AM, Amos Bird  wrote:
>> How about making a storage parameter "default_tablespace" that also
>> covers CREATE INDEX and other stuff?
>
> That's exactly the idea, the one at relation-level gets priority on
> the global one defined in postgresql.conf.
>

What will such a storage parameter (default_tablespace) mean at table
level and how it will different from existing default_tablespace?  I
think the usage asked by Amos is quite genuine, but not sure if
introducing default_tablespace as a storage level parameter is the
best way to address it.  Another way could be that we allow the user
to specify something like tablespace_name / or something like that.

-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com


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

[Michael Paquier]

On Sat, Nov 26, 2016 at 11:25 AM, Amos Bird  wrote:
> How about making a storage parameter "default_tablespace" that also
> covers CREATE INDEX and other stuff?

That's exactly the idea, the one at relation-level gets priority on
the global one defined in postgresql.conf.
-- 
Michael


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

[Corey Huinker]

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

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.

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

[Michael Paquier]

On Fri, Nov 25, 2016 at 10:47 PM, Amos Bird  wrote:
>> 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".

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


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

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

[Michael Paquier]

On Fri, Nov 25, 2016 at 4:48 PM, Amos Bird  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.
-- 
Michael


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