Syntax for partitioning
in [PgSql]
|
Prev: [HACKERS] pg_read_file() and non-ascii input file
Next: [HACKERS] Proposal - temporal contrib module
From: Jeff Davis on 30 Oct 2009 12:12 On Fri, 2009-10-30 at 10:03 +0200, Peter Eisentraut wrote: > I can't help but wonder if the period type might better be a generic > container for pairs of scalar, totally-ordered types. That would be ideal. However, it doesn't really look like our type system was built to handle that kind of thing. We could use typmod, I suppose, but even that won't hold a full Oid. Any ideas/suggestions? Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: pg on 30 Oct 2009 11:03 >> PARTITION BY RANGE ( a_expr ) >> ... >> PARTITION BY HASH ( a_expr ) >> PARTITIONS num_partitions; > Unless someone comes up with a maintenance plan for stable hashfunctions, we should probably not dare look into this yet. What would cover the common use case of per-day quals and drops over an extended history period, say six or nine months? You don't get quite the same locality of reference, generally, with an unpartitioned table, due to slop in the arrival of rows. Ideally, you don't want to depend on an administrator, or even an administrative script, to continually intervene in the structure of a table, as would be the case with partitioning by range, and you don't want to coalesce multiple dates, as an arbitrary hash might do. What the administrator would want would be to decide what rows were too old to keep, then process (e.g. archive, summarize, filter) and delete them. Suppose that the number of partitions were taken as a hint rather than as a naming modulus, and that any quasi-hash function had to be specified explicitly (although storage assignment could be based on a hash of the quasi-hash output). If a_expr were allowed to include a to-date conversion of a timestamp, day-by-day partitioning would fall out naturally. If, in addition, single-parameter (?) functions were characterized as range-preserving and order-preserving, plan generation could be improved for time ranges on quasi-hash-partitioned tables, without a formal indexing requirement. There are cases where additional partition dimensions would be useful, for eventual parallelized operation on large databases, and randomizing quasi-hash functions would help. IMHO stability is not needed, except to the extent that hash functions have properties that lend themselves to plan generation and/or table maintenance. It is not clear to me what purpose there would be in dropping a partition.. This would be tantamount to deleting all of the rows in a partition, if it were analogous to dropping a table, and would require some sort of compensatory aggregation of existing partitions (in effect, a second partitioning dimension), if it were merely structural. Perhaps I'm missing something here. David Hudson
From: Heikki Linnakangas on 30 Oct 2009 13:12 Jeff Davis wrote: > On Fri, 2009-10-30 at 10:03 +0200, Peter Eisentraut wrote: >> I can't help but wonder if the period type might better be a generic >> container for pairs of scalar, totally-ordered types. > > That would be ideal. However, it doesn't really look like our type > system was built to handle that kind of thing. > > We could use typmod, I suppose, but even that won't hold a full Oid. Any > ideas/suggestions? Wait, it doesn't? A typmod is a 32-bit integer, like Oids. Am I missing something? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Jeff Davis on 30 Oct 2009 14:14 On Fri, 2009-10-30 at 19:12 +0200, Heikki Linnakangas wrote: > Wait, it doesn't? A typmod is a 32-bit integer, like Oids. Am I missing > something? Oid is unsigned, typmod is signed. We might be able to get away with it, but -1 is treated specially in some places outside of the type-specific functions, e.g. exprTypmod(). I haven't looked at all of these places yet, so maybe a few simple changes would allow us to treat typmod as a full 32 bits. Or perhaps it could just be expanded to a signed 64-bit int. What do you think? Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Stephen Frost on 30 Oct 2009 16:51
* Jeff Davis (pgsql(a)j-davis.com) wrote: > Oid is unsigned, typmod is signed. We might be able to get away with it, > but -1 is treated specially in some places outside of the type-specific > functions, e.g. exprTypmod(). Any negative result returned from the input handling function is considered an error, as I recall. It's more than just '-1'.. > I haven't looked at all of these places yet, so maybe a few simple > changes would allow us to treat typmod as a full 32 bits. Or perhaps it > could just be expanded to a signed 64-bit int. What do you think? That was shot down previously due to the way typmods are passed around currently.. Not that it wouldn't be really nice.. Thanks, Stephen |