Thread: per-tablespace random_page_cost/seq_page_cost
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
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
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
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
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
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
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.
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
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/
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
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
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
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.
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
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
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
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
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
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
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
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...
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.
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
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
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.
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
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
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.
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
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