Thread: per-tablespace random_page_cost/seq_page_cost

per-tablespace random_page_cost/seq_page_cost

From
Robert Haas
Date:
On Mon, Oct 19, 2009 at 6:33 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Per-table is not physically sensible.  Per-tablespace has some rationale
> to it.

I took a look at this and it seems fairly straightforward.  It
basically requires (1) deciding where and how to store per-tablespace
defaults,  (2) making those defaults conveniently available to
cost_seqscan(), cost_index(), cost_bitmap_heap_scan(), cost_tidscan(),
and genericcostestimate(), and (3) deciding on some syntax.

As to (1), my thought is to add two new float8 columns to
pg_tablespace.  The naming is a little awkward, because
random_page_cost and seq_page_cost would not fit with our (rather odd)
convention for naming system catalog columns.  I'm tempted to call
them spcrandompagecost and spcseqpagecost, but I wonder if anyone has
any strong preferences.

As to (2), it looks like we could pretty add reltablespace to
RangeTblEntry (for tables) and IndexOptInfo (for indices).  This could
be populated in addRangeTblEntry()/addRangeTblEntryForRelation() for
tables, and in get_relation_info() for indices, essentially for free.
Then the above-mentioned functions that need to use the page costs
could just call a function with a name like
get_tablespace_page_costs() and pass the tablespace OID.

As things stand today, that function would need to scan
pg_tablespace_oid_index to find the correct heap tuple, because there
is no catcache for pg_tablespace entries.  I'm not sure whether that's
something that would need to be changed.

As to (3), I was thinking about:

ALTER TABLESPACE name SET ( parameter = value [, ...] )

...where parameter is either seq_page_cost or random_page_cost.  The
parentheses are for parity with ALTER TABLE, which employs them so as
to allow change storage parameters and making other table
modifications with a single command.  I don't see any immediate need
to support that for ALTER TABLESPACE, which doesn't have many options
at present, but neither do I see any reason to pick a deliberately
incompatible syntax, in case someone wants to implement it in the
future.

Arguably, you would expect parameters set using this syntax to be
stored similar to reloptions - that is, as text[].  But as we're going
to need these values multiple times per table to plan any non-trivial
query, I don't want to inject unnecessary parsing overhead and code
complexity.

Thoughts?  Comments?  Reservations?

...Robert


Re: per-tablespace random_page_cost/seq_page_cost

From
Greg Stark
Date:
On Sun, Oct 25, 2009 at 9:05 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> Arguably, you would expect parameters set using this syntax to be
> stored similar to reloptions - that is, as text[].  But as we're going
> to need these values multiple times per table to plan any non-trivial
> query, I don't want to inject unnecessary parsing overhead and code
> complexity.

Two comments, perhaps complementary, though I'm not sure of either answer.

1 Would we rather the storage scheme allow for future GUCs to be
easily moved to per-tablespace as well without changing the catalog
schema for every option? (Someone might accuse me of trolling the
anti-EAV people here though...)

2 Would it make sense to slurp these options from the tablespace
options into the relcache when building the relcache entry for a
table? That would make the storage format in the tablespace options
much less relevant. It might even make the catcache less important
too.

--
greg


Re: per-tablespace random_page_cost/seq_page_cost

From
Josh Berkus
Date:
Robert,

> As to (1), my thought is to add two new float8 columns to
> pg_tablespace.  The naming is a little awkward, because
> random_page_cost and seq_page_cost would not fit with our (rather odd)
> convention for naming system catalog columns.  I'm tempted to call
> them spcrandompagecost and spcseqpagecost, but I wonder if anyone has
> any strong preferences.

I'm thinking an array, in case we want to make other tablespace cost
parameters in the future.*  Or, better, whatever structure we're
currently using for ROLEs.

(* for example, if someone does manage a filesystem with a separate
cache space per mount, then we'd want effective_cache_size to be
tablespace-based as well)

--Josh


Re: per-tablespace random_page_cost/seq_page_cost

From
Greg Stark
Date:
On Mon, Oct 26, 2009 at 3:05 PM, Josh Berkus <josh@agliodbs.com> wrote:
> I'm thinking an array, in case we want to make other tablespace cost
> parameters in the future.*  Or, better, whatever structure we're
> currently using for ROLEs.
>
> (* for example, if someone does manage a filesystem with a separate
> cache space per mount, then we'd want effective_cache_size to be
> tablespace-based as well)

Still far from convinced on that one. But effective_io_concurrency
should be included even in the first pass.


--
greg


Re: per-tablespace random_page_cost/seq_page_cost

From
Tom Lane
Date:
Greg Stark <gsstark@mit.edu> writes:
> Still far from convinced on that one. But effective_io_concurrency
> should be included even in the first pass.

I think a design that is limited to a prespecified set of GUCs is
broken by definition.  It'd be better to make it work like
ALTER DATABASE SET.
        regards, tom lane


Re: per-tablespace random_page_cost/seq_page_cost

From
Andres Freund
Date:
Hi,

On Tuesday 27 October 2009 00:42:39 Tom Lane wrote:
> Greg Stark <gsstark@mit.edu> writes:
> > Still far from convinced on that one. But effective_io_concurrency
> > should be included even in the first pass.
> I think a design that is limited to a prespecified set of GUCs is
> broken by definition.  It'd be better to make it work like
> ALTER DATABASE SET.
How should that work if there are conflicting settings in two tablespaces when 
tables from both are used?
Some settings make sense per tablespace, but I dont see a valid model to 
accept e.g. 'standard_conforming_strings' set to 'off' in one and  'on' in the 
other...

Andres


Re: per-tablespace random_page_cost/seq_page_cost

From
Alvaro Herrera
Date:
Tom Lane escribió:
> Greg Stark <gsstark@mit.edu> writes:
> > Still far from convinced on that one. But effective_io_concurrency
> > should be included even in the first pass.
> 
> I think a design that is limited to a prespecified set of GUCs is
> broken by definition.  It'd be better to make it work like
> ALTER DATABASE SET.

Well, not exactly like ALTER DATABASE SET because those are now stored
in pg_db_role_setting.  But a new spcoptions column storing an array of
key/value pairs seems a reasonable way to do it.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.


Re: per-tablespace random_page_cost/seq_page_cost

From
Tom Lane
Date:
Andres Freund <andres@anarazel.de> writes:
> On Tuesday 27 October 2009 00:42:39 Tom Lane wrote:
>> I think a design that is limited to a prespecified set of GUCs is
>> broken by definition.  It'd be better to make it work like
>> ALTER DATABASE SET.

> How should that work if there are conflicting settings in two tablespaces when 
> tables from both are used?

Well, most of the settings that would be sensible for this are used in
cost estimates that are basically per-table or per-index, so I don't
think it's a huge problem in practice.  But I should clarify my comment:
the set of GUCs used this way must not be wired into the catalog
structure.  I think that the code will only pay attention to certain
GUCs that are valid in-context, but we shouldn't have to redesign the
catalog each time we add one.
        regards, tom lane


Re: per-tablespace random_page_cost/seq_page_cost

From
Euler Taveira de Oliveira
Date:
Alvaro Herrera escreveu:
> Tom Lane escribió:
>> Greg Stark <gsstark@mit.edu> writes:
>>> Still far from convinced on that one. But effective_io_concurrency
>>> should be included even in the first pass.
>> I think a design that is limited to a prespecified set of GUCs is
>> broken by definition.  It'd be better to make it work like
>> ALTER DATABASE SET.
> 
> Well, not exactly like ALTER DATABASE SET because those are now stored
> in pg_db_role_setting.  But a new spcoptions column storing an array of
> key/value pairs seems a reasonable way to do it.
> 
+1. That's what I have in mind too.


--  Euler Taveira de Oliveira http://www.timbira.com/


Re: per-tablespace random_page_cost/seq_page_cost

From
Robert Haas
Date:
On Mon, Oct 26, 2009 at 11:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Andres Freund <andres@anarazel.de> writes:
>> On Tuesday 27 October 2009 00:42:39 Tom Lane wrote:
>>> I think a design that is limited to a prespecified set of GUCs is
>>> broken by definition.  It'd be better to make it work like
>>> ALTER DATABASE SET.
>
>> How should that work if there are conflicting settings in two tablespaces when
>> tables from both are used?
>
> Well, most of the settings that would be sensible for this are used in
> cost estimates that are basically per-table or per-index, so I don't
> think it's a huge problem in practice.  But I should clarify my comment:
> the set of GUCs used this way must not be wired into the catalog
> structure.  I think that the code will only pay attention to certain
> GUCs that are valid in-context, but we shouldn't have to redesign the
> catalog each time we add one.

These don't exactly fit into the GUC framework because AIUI a GUC is a
global variable, and the function of the GUC machinery is simply to
make sure that the global variable in question is set to the right
value at the right time.  These are really more like reloptions (that
may happen to have the same name as GUCs, I suppose) - always in
effect, but only for a particular object.

I confess that I'm a bit mystified about the design of the reloptions
stuff. It seems kind of odd to store structured data as text[]; it's
kind of the opposite of what I would normally recommend as good
database design.

...Robert


Re: per-tablespace random_page_cost/seq_page_cost

From
Tom Lane
Date:
Robert Haas <robertmhaas@gmail.com> writes:
> I confess that I'm a bit mystified about the design of the reloptions
> stuff. It seems kind of odd to store structured data as text[]; it's
> kind of the opposite of what I would normally recommend as good
> database design.

It's definitely a bit EAV-ish :-(.  But I don't see any particularly
easy way to modify it to store bool/int/float parameters in their native
types; do you?
        regards, tom lane


Re: per-tablespace random_page_cost/seq_page_cost

From
David Fetter
Date:
On Tue, Oct 27, 2009 at 09:13:29PM -0400, Tom Lane wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
> > I confess that I'm a bit mystified about the design of the
> > reloptions stuff. It seems kind of odd to store structured data as
> > text[]; it's kind of the opposite of what I would normally
> > recommend as good database design.
> 
> It's definitely a bit EAV-ish :-(.  But I don't see any particularly
> easy way to modify it to store bool/int/float parameters in their
> native types; do you?

More columns, each of the correct type, with the table constraint that
at most one may be populated is how I handle stuff like that.

Cheers
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


Re: per-tablespace random_page_cost/seq_page_cost

From
Alvaro Herrera
Date:
David Fetter escribió:
> On Tue, Oct 27, 2009 at 09:13:29PM -0400, Tom Lane wrote:
> > Robert Haas <robertmhaas@gmail.com> writes:
> > > I confess that I'm a bit mystified about the design of the
> > > reloptions stuff. It seems kind of odd to store structured data as
> > > text[]; it's kind of the opposite of what I would normally
> > > recommend as good database design.
> > 
> > It's definitely a bit EAV-ish :-(.  But I don't see any particularly
> > easy way to modify it to store bool/int/float parameters in their
> > native types; do you?
> 
> More columns, each of the correct type, with the table constraint that
> at most one may be populated is how I handle stuff like that.

So we would have "spcintoptions", "spcfloatoptions", and so on?  (I
don't see the need for the table constraint in this case.)

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.


Re: per-tablespace random_page_cost/seq_page_cost

From
Robert Haas
Date:
On Tue, Oct 27, 2009 at 9:20 PM, David Fetter <david@fetter.org> wrote:
> On Tue, Oct 27, 2009 at 09:13:29PM -0400, Tom Lane wrote:
>> Robert Haas <robertmhaas@gmail.com> writes:
>> > I confess that I'm a bit mystified about the design of the
>> > reloptions stuff. It seems kind of odd to store structured data as
>> > text[]; it's kind of the opposite of what I would normally
>> > recommend as good database design.
>>
>> It's definitely a bit EAV-ish :-(.  But I don't see any particularly
>> easy way to modify it to store bool/int/float parameters in their
>> native types; do you?
>
> More columns, each of the correct type, with the table constraint that
> at most one may be populated is how I handle stuff like that.

I don't see why we'd need to constrain more than one from being
populated, but yeah, that's basically what I was thinking: one column
per parameter, of the appropriate type.  That might not be such a good
model if the number of possible options was really large, but at this
point there's no reason to believe that will be the case for either
reloptions or the proposed spcoptions.

For things like autovacuum options, the actual representation probably
doesn't matter much because I'm guessing that the amount of work being
done by vacuum dwarfs the parsing cost, and it's a background task
anyway.  But this seems like a less solid argument for things like
fillfactor and the proposed per-tablespace
seq_page_cost/random_page_cost, which will be accessed by many queries
and in the latter case often more than once.  Ideally (or so it seems
to me) you'd like to fetch those things out of Form_pg_whatever rather
than parsing text strings to get at 'em.

...Robert


Re: per-tablespace random_page_cost/seq_page_cost

From
Tom Lane
Date:
Robert Haas <robertmhaas@gmail.com> writes:
> For things like autovacuum options, the actual representation probably
> doesn't matter much because I'm guessing that the amount of work being
> done by vacuum dwarfs the parsing cost, and it's a background task
> anyway.  But this seems like a less solid argument for things like
> fillfactor and the proposed per-tablespace
> seq_page_cost/random_page_cost, which will be accessed by many queries
> and in the latter case often more than once.  Ideally (or so it seems
> to me) you'd like to fetch those things out of Form_pg_whatever rather
> than parsing text strings to get at 'em.

I think efficiency arguments here are hogwash.  In the first place,
we'd certainly cache the results someplace (relcache or something like
it) if retrieve performance seems to be a bottleneck at all.  In the
second place, composite types are so hugely inefficient as to swamp any
gain you'd get from the columns being the right type once you got at
them.  (atoi and friends are cheap by comparison.)

It's possible that changing this is worthwhile on logical cleanliness
grounds; but I think it will be a net loss in efficiency, and definitely
a net loss in terms of code complexity at the C level.
        regards, tom lane


Re: per-tablespace random_page_cost/seq_page_cost

From
Robert Haas
Date:
On Tue, Oct 27, 2009 at 10:18 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> For things like autovacuum options, the actual representation probably
>> doesn't matter much because I'm guessing that the amount of work being
>> done by vacuum dwarfs the parsing cost, and it's a background task
>> anyway.  But this seems like a less solid argument for things like
>> fillfactor and the proposed per-tablespace
>> seq_page_cost/random_page_cost, which will be accessed by many queries
>> and in the latter case often more than once.  Ideally (or so it seems
>> to me) you'd like to fetch those things out of Form_pg_whatever rather
>> than parsing text strings to get at 'em.
>
> I think efficiency arguments here are hogwash.  In the first place,
> we'd certainly cache the results someplace (relcache or something like
> it) if retrieve performance seems to be a bottleneck at all.  In the
> second place, composite types are so hugely inefficient as to swamp any
> gain you'd get from the columns being the right type once you got at
> them.  (atoi and friends are cheap by comparison.)

We must be talking about different things, because I can't believe
this is true of what I'm thinking about.  I'm not suggesting having a
column called reloptions of composite type; I'm suggesting that an
option like fillfactor would have its very own table column, just like
relpages or relhasindex.  Surely that's gotta be faster than text; it
overlays onto a C struct, which is about as fast as it gets.

I agree that caching mitigates many of the problems with this from an
efficiency standpoint, possibly to the point where it isn't worth
caring about.  But it does seem grotty, and I feel like it has to cost
something: we read in an array of text[] and convert it to a C struct,
which is exactly the form it would already be in if we just made it
part of Form_pg_class in the first place.  The only way I can think
that the current representation could be faster is that when there are
NO reloptions at all, the parsing step can be skipped, and yet overall
Form_pg_class is smaller than it would be otherwise, which is of some
miniscule benefit.

> It's possible that changing this is worthwhile on logical cleanliness
> grounds; but I think it will be a net loss in efficiency, and definitely
> a net loss in terms of code complexity at the C level.

One of my concerns about the current representation is that it doesn't
seem easily generalizable to objects that are not in pg_class, such as
tablespaces.  I fear that supporting spcoptions as text[] along the
lines of reloptions will require quite a bit of refactoring to avoid
code duplication, whereas adding a few new columns to pg_tablespace
and maybe making a syscache for it looks pretty simple.  On the other
hand, that would leave us with completely different representations
for essentially the same sort of data, which isn't particularly
appealing either.

...Robert


Re: per-tablespace random_page_cost/seq_page_cost

From
Robert Haas
Date:
On Tue, Oct 27, 2009 at 9:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> I confess that I'm a bit mystified about the design of the reloptions
>> stuff. It seems kind of odd to store structured data as text[]; it's
>> kind of the opposite of what I would normally recommend as good
>> database design.
>
> It's definitely a bit EAV-ish :-(.  But I don't see any particularly
> easy way to modify it to store bool/int/float parameters in their native
> types; do you?

Looking at this a little more, it seems that part of the motivation
for the existing design is that user-created AMs might require
arbitrary options, which therefore can't be wired into the system
catalogs.  There's no equivalent for tablespaces (we could add one
some day, I suppose), so there's less intrinsic reason to think we
have to do it that way.

It doesn't actually look like it would be too terrible to un-hard-wire
the on-disk representation.  The existing transformRelOptions() and
untransformRelOptions() code could be made to handle whatever is
stored in reloptions[]; but we could arrange to remove (in the case of
transform) or insert (in the case untransform) the DefElems for any
options stored elsewhere before those functions are called.  There is
an existing hack to do this for "oids" which could probably be cleaned
up and made part of some more general structure.

However... as you basically already said, it's not entirely clear that
this solves any real problem.  The problem is not so much that the
design is too tightly coupled to a particular storage representation
as that it is too tightly coupled to pg_class - starting with the name
reloptions, which would be inapposite for options associated with a
tablespace, schema, etc.  That hasn't stopped the foreign data wrapper
stuff from reaching in and unceremoniously borrowing a few functions
like transformRelOptions, whose comment is now mildly incorrect in
enumerating the ways the function is used.

I don't see anything in this code that is very rel-specific, so I
think it would be possible to implement spcoptions by just defining
RELOPT_KIND_TABLESPACE and ignoring the irony, but that has enough of
an unsavory feeling that I'm sure someone is going to complain about
it...  I suppose we could go through and systematically rename all
instances of reloptions to ent(ity)options or storageoptions or
gen(eric)options or somesuch...

...Robert


Re: per-tablespace random_page_cost/seq_page_cost

From
Greg Stark
Date:
On Sat, Oct 31, 2009 at 6:04 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> Looking at this a little more, it seems that part of the motivation
> for the existing design is that user-created AMs might require
> arbitrary options, which therefore can't be wired into the system
> catalogs.  There's no equivalent for tablespaces (we could add one
> some day, I suppose), so there's less intrinsic reason to think we
> have to do it that way.

Can't custom modules define arbitrary options which they declare can
be defined per tablespace?

We could have a column for all booleans, a column for all integers,
etc. but that's not really any more normalized than having a single
column for all the types with a rule for how to marshal each value
type.


--
greg


Re: per-tablespace random_page_cost/seq_page_cost

From
Robert Haas
Date:
On Nov 1, 2009, at 7:43 AM, Greg Stark <gsstark@mit.edu> wrote:

> On Sat, Oct 31, 2009 at 6:04 PM, Robert Haas <robertmhaas@gmail.com>  
> wrote:
>> Looking at this a little more, it seems that part of the motivation
>> for the existing design is that user-created AMs might require
>> arbitrary options, which therefore can't be wired into the system
>> catalogs.  There's no equivalent for tablespaces (we could add one
>> some day, I suppose), so there's less intrinsic reason to think we
>> have to do it that way.
>
> Can't custom modules define arbitrary options which they declare can
> be defined per tablespace?

Yeah, probably we can support that for free, although I'm not sure  
there is much demand for it.

> We could have a column for all booleans, a column for all integers,
> etc. but that's not really any more normalized than having a single
> - how to marshal each value
> type.

That has no advantages and several disadvantages AFAICS.

I don't want to get sidetracked here. The real issue is the one I  
discussed in the portion of the email you didn't quote...

...Robert 


Re: per-tablespace random_page_cost/seq_page_cost

From
Alvaro Herrera
Date:
Robert Haas escribió:

> I don't see anything in this code that is very rel-specific, so I
> think it would be possible to implement spcoptions by just defining
> RELOPT_KIND_TABLESPACE and ignoring the irony, but that has enough of
> an unsavory feeling that I'm sure someone is going to complain about
> it...  I suppose we could go through and systematically rename all
> instances of reloptions to ent(ity)options or storageoptions or
> gen(eric)options or somesuch...

Maybe I missed part of the discussion, but do these really need to be
handled like reloptions instead of like datoptions?  Perhaps the
deciding factor is that we want to parse them once and store them in a
cache, so like reloptions; the others are used once per connection and
then thrown away.

If this is the case, then I think we could just decide that their name
is reloptions due to hysterical reasons and be done with it.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support


Re: per-tablespace random_page_cost/seq_page_cost

From
Dimitri Fontaine
Date:
Hi, excuse the quoting style... and the intrepid nature of the
following content...

--
dim

Le 1 nov. 2009 à 13:43, Greg Stark <gsstark@mit.edu> a écrit :
> We could have a column for all booleans, a column for all integers,
> etc. but that's not really any more normalized than having a single
> column for all the types with a rule for how to marshal each value
> type.

Thé other day, on IRC, someone wanted a dynamic table accepting value
in whichever column you name. That would probably mean having a
special INSERT INTO which ALTER TABLE ... ADD COLUMN ... for you.

Maybe INSERT INTO ... WITH ADD COLUMN OPTION;

This sure looks suspicious, but the asking came from another product
and it seems that could help here too. Oh and you get text columns I
guess, by default...

Re: per-tablespace random_page_cost/seq_page_cost

From
Alvaro Herrera
Date:
Dimitri Fontaine escribió:

> Thé other day, on IRC, someone wanted a dynamic table accepting
> value in whichever column you name. That would probably mean having
> a special INSERT INTO which ALTER TABLE ... ADD COLUMN ... for you.

That sounds more like something you'd do with hstore or something
similar.

Didn't they also want an option to create the table on insert if it
doesn't exist?

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.


Re: per-tablespace random_page_cost/seq_page_cost

From
Robert Haas
Date:
On Mon, Nov 2, 2009 at 4:11 PM, Dimitri Fontaine <dfontaine@hi-media.com> wrote:
> Hi, excuse the quoting style... and the intrepid nature of the following
> content...
>
> --
> dim
>
> Le 1 nov. 2009 à 13:43, Greg Stark <gsstark@mit.edu> a écrit :
>>
>> We could have a column for all booleans, a column for all integers,
>> etc. but that's not really any more normalized than having a single
>> column for all the types with a rule for how to marshal each value
>> type.
>
> Thé other day, on IRC, someone wanted a dynamic table accepting value in
> whichever column you name. That would probably mean having a special INSERT
> INTO which ALTER TABLE ... ADD COLUMN ... for you.
>
> Maybe INSERT INTO ... WITH ADD COLUMN OPTION;
>
> This sure looks suspicious, but the asking came from another product and it
> seems that could help here too. Oh and you get text columns I guess, by
> default...

If you want to start a discussion about a topic that is completely
unrelated to this one, then please start a new thread.

...Robert


Re: per-tablespace random_page_cost/seq_page_cost

From
Robert Haas
Date:
On Mon, Nov 2, 2009 at 12:40 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> Robert Haas escribió:
>
>> I don't see anything in this code that is very rel-specific, so I
>> think it would be possible to implement spcoptions by just defining
>> RELOPT_KIND_TABLESPACE and ignoring the irony, but that has enough of
>> an unsavory feeling that I'm sure someone is going to complain about
>> it...  I suppose we could go through and systematically rename all
>> instances of reloptions to ent(ity)options or storageoptions or
>> gen(eric)options or somesuch...
>
> Maybe I missed part of the discussion, but do these really need to be
> handled like reloptions instead of like datoptions?  Perhaps the
> deciding factor is that we want to parse them once and store them in a
> cache, so like reloptions; the others are used once per connection and
> then thrown away.

This may be a stupid question, but what are datoptions?

$ git grep datoptions
$

> If this is the case, then I think we could just decide that their name
> is reloptions due to hysterical reasons and be done with it.

Yeah.  It's particularly unfortunate that we call them "reloptions" in
the code but "storage parameters" in the documentation.  Neither is a
particularly good name, and having two different ones is extra-poor.
But I'm fine with leaving the names as they are and moving on, if no
one objects too much.  Speak now or don't complain about it after I
write the patch!

...Robert


Re: per-tablespace random_page_cost/seq_page_cost

From
Alvaro Herrera
Date:
Robert Haas escribió:
> On Mon, Nov 2, 2009 at 12:40 PM, Alvaro Herrera
> <alvherre@commandprompt.com> wrote:
> > Robert Haas escribió:
> >
> >> I don't see anything in this code that is very rel-specific, so I
> >> think it would be possible to implement spcoptions by just defining
> >> RELOPT_KIND_TABLESPACE and ignoring the irony, but that has enough of
> >> an unsavory feeling that I'm sure someone is going to complain about
> >> it...  I suppose we could go through and systematically rename all
> >> instances of reloptions to ent(ity)options or storageoptions or
> >> gen(eric)options or somesuch...
> >
> > Maybe I missed part of the discussion, but do these really need to be
> > handled like reloptions instead of like datoptions?  Perhaps the
> > deciding factor is that we want to parse them once and store them in a
> > cache, so like reloptions; the others are used once per connection and
> > then thrown away.
> 
> This may be a stupid question, but what are datoptions?
> 
> $ git grep datoptions

datoptions are gone now, replaced by pg_db_role_settings, but you could
find them in 8.4.  They were an array of options much like reloptions,
except that they didn't go through the reloptions.c code for parsing.

> > If this is the case, then I think we could just decide that their name
> > is reloptions due to hysterical reasons and be done with it.
> 
> Yeah.  It's particularly unfortunate that we call them "reloptions" in
> the code but "storage parameters" in the documentation.  Neither is a
> particularly good name, and having two different ones is extra-poor.
> But I'm fine with leaving the names as they are and moving on, if no
> one objects too much.  Speak now or don't complain about it after I
> write the patch!

Maybe after we move to Git we can rename them in the code?

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.


Re: per-tablespace random_page_cost/seq_page_cost

From
Robert Haas
Date:
On Tue, Nov 3, 2009 at 6:25 AM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
>> > If this is the case, then I think we could just decide that their name
>> > is reloptions due to hysterical reasons and be done with it.
>>
>> Yeah.  It's particularly unfortunate that we call them "reloptions" in
>> the code but "storage parameters" in the documentation.  Neither is a
>> particularly good name, and having two different ones is extra-poor.
>> But I'm fine with leaving the names as they are and moving on, if no
>> one objects too much.  Speak now or don't complain about it after I
>> write the patch!
>
> Maybe after we move to Git we can rename them in the code?

I'm OK with renaming it before I start working on the main patch, or
after it's committed, or never.  I just don't want to have to rebase
it in the middle.

...Robert


Re: per-tablespace random_page_cost/seq_page_cost

From
Dimitri Fontaine
Date:
Robert Haas <robertmhaas@gmail.com> writes:
>> Le 1 nov. 2009 à 13:43, Greg Stark <gsstark@mit.edu> a écrit :
>>>
>>> We could have a column for all booleans, a column for all integers,
>>> etc. but that's not really any more normalized than having a single
>>> column for all the types with a rule for how to marshal each value
>>> type.
>>
>> Thé other day, on IRC, someone wanted a dynamic table accepting value in
>> whichever column you name. That would probably mean having a special INSERT
>> INTO which ALTER TABLE ... ADD COLUMN ... for you.
>>
>> Maybe INSERT INTO ... WITH ADD COLUMN OPTION;
>>
>> This sure looks suspicious, but the asking came from another product and it
>> seems that could help here too. Oh and you get text columns I guess, by
>> default...
>
> If you want to start a discussion about a topic that is completely
> unrelated to this one, then please start a new thread.

Auto adding a column on INSERT when it does not exists would help tools
to add columns in there, to avoid having to follow EAV model.

Maybe this property would be tied to the table rather than the INSERT,
though, or maybe we'd be better without it at all. But it's related to
the case at hand, yes.

--
Dimitri Fontaine
PostgreSQL DBA, Architecte


Re: per-tablespace random_page_cost/seq_page_cost

From
Alvaro Herrera
Date:
Robert Haas escribió:
> On Tue, Nov 3, 2009 at 6:25 AM, Alvaro Herrera
> <alvherre@commandprompt.com> wrote:
> >> > If this is the case, then I think we could just decide that their name
> >> > is reloptions due to hysterical reasons and be done with it.
> >>
> >> Yeah.  It's particularly unfortunate that we call them "reloptions" in
> >> the code but "storage parameters" in the documentation.  Neither is a
> >> particularly good name, and having two different ones is extra-poor.
> >> But I'm fine with leaving the names as they are and moving on, if no
> >> one objects too much.  Speak now or don't complain about it after I
> >> write the patch!
> >
> > Maybe after we move to Git we can rename them in the code?
> 
> I'm OK with renaming it before I start working on the main patch, or
> after it's committed, or never.  I just don't want to have to rebase
> it in the middle.

I think "after we move to Git" goes well after "after your patch is
committed", so we're OK.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.


Re: per-tablespace random_page_cost/seq_page_cost

From
Robert Haas
Date:
On Tue, Nov 3, 2009 at 7:44 AM, Dimitri Fontaine <dfontaine@hi-media.com> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>>> Le 1 nov. 2009 à 13:43, Greg Stark <gsstark@mit.edu> a écrit :
>>>>
>>>> We could have a column for all booleans, a column for all integers,
>>>> etc. but that's not really any more normalized than having a single
>>>> column for all the types with a rule for how to marshal each value
>>>> type.
>>>
>>> Thé other day, on IRC, someone wanted a dynamic table accepting value in
>>> whichever column you name. That would probably mean having a special INSERT
>>> INTO which ALTER TABLE ... ADD COLUMN ... for you.
>>>
>>> Maybe INSERT INTO ... WITH ADD COLUMN OPTION;
>>>
>>> This sure looks suspicious, but the asking came from another product and it
>>> seems that could help here too. Oh and you get text columns I guess, by
>>> default...
>>
>> If you want to start a discussion about a topic that is completely
>> unrelated to this one, then please start a new thread.
>
> Auto adding a column on INSERT when it does not exists would help tools
> to add columns in there, to avoid having to follow EAV model.
>
> Maybe this property would be tied to the table rather than the INSERT,
> though, or maybe we'd be better without it at all. But it's related to
> the case at hand, yes.

I fail to see how.  Even if such a feature were accepted, which
frankly I doubt it would be since I think you could code it up
yourself by using a function that takes an hstore and performs the
column additions for you as need be, I seriously doubt that it could
be made to work for system tables.  And even if it could, it wouldn't
address my original point, which is that it would be faster to access
these properties by pulling them out of Form_pg_tablespace rather than
needing some more complicated system for extracting them - because you
certainly can't dynamically change the definition of a C struct that
is compiled into the executable dynamically at runtime.

Besides all that, what you are talking about here is a SQL-level
feature with SQL syntax.  For possibly-obvious reasons, we don't
implement commands like ALTER TABLE by transforming them into SQL
queries that update the system catalogs.  So even if someone did
implement this and it were accepted and it worked on system catalogs,
it still wouldn't be of any assistance to me in implementing ALTER
TABLESPACE ... SET.  Can we stop now?

...Robert


Re: per-tablespace random_page_cost/seq_page_cost

From
Robert Haas
Date:
On Tue, Nov 3, 2009 at 7:51 AM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> Robert Haas escribió:
>> On Tue, Nov 3, 2009 at 6:25 AM, Alvaro Herrera
>> <alvherre@commandprompt.com> wrote:
>> >> > If this is the case, then I think we could just decide that their name
>> >> > is reloptions due to hysterical reasons and be done with it.
>> >>
>> >> Yeah.  It's particularly unfortunate that we call them "reloptions" in
>> >> the code but "storage parameters" in the documentation.  Neither is a
>> >> particularly good name, and having two different ones is extra-poor.
>> >> But I'm fine with leaving the names as they are and moving on, if no
>> >> one objects too much.  Speak now or don't complain about it after I
>> >> write the patch!
>> >
>> > Maybe after we move to Git we can rename them in the code?
>>
>> I'm OK with renaming it before I start working on the main patch, or
>> after it's committed, or never.  I just don't want to have to rebase
>> it in the middle.
>
> I think "after we move to Git" goes well after "after your patch is
> committed", so we're OK.

Or if not, then it's my own fault.  :-)

...Robert