Re: [HACKERS] Parsing config files in a directory
2009/10/27 Simon Riggs si...@2ndquadrant.com: On Tue, 2009-10-27 at 00:38 -0400, Greg Smith wrote: new feature One additional point that would be useful is a way to match up the usage of custom_variable_classes with this new style of .conf file processing. At the moment if you wish to add a custom variable class everybody needs to edit the *same* parameter. Finding which one to edit could be a little difficult with a whole directory to search in. I propose a new form of processing for that variable: each new parameter instance is added to last one, rather than replacing it. e.g. custom_variable_class = 'x' custom_variable_class = 'y' custom_variable_class = 'z' is equivalent to custom_variable_classes = 'x,y,z' That allows NewFeatureX to drop in a file called newfeaturex.conf, which looks like this custom_variable_class = 'newfeaturex' newfeaturex.param1 = x newfeaturex.param2 = y newfeaturex.param3 = z This requires no editing of any other files, just a straight drop in. That will make it much easier to produce real installers/deinstallers for add-in modules. I understand, but this behave is confusing. Maybe better is using some trivial keywords like append, delete, reset so append custom_variable_class = 'x' append custom_variable_class = 'y' append custom_variable_class = 'z' is custom_variable_classes = 'x,y,z' Regards Pavel -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Proposal - temporal contrib module
I would like to add a temporal contrib module. The most important piece would be adding a period data type and some support functions. Jeff Davis and I both have temporal projects on pgFoundry, and we've been collaborating for a while. But there are some areas we'd like to get some advice on. Disk format - A period can be represented as [closed-closed], (open-open), [closed-open) or (open-closed] intervals. Right now we convert these to the most common form, closed-open and store as two timestamptz's. Nulls - A common use case for periods is for modeling valid time. Often the end point is not known. For instance, you know when an employee has been hired but the termination time typically wouldn't be known ahead of time. We can either represent these with a null end time or with infinity. But I'm not sure how to deal with them. Obviously we can test for containment and overlap. But what about length or set operations? Non-contiguous Sets - A period defines a contiguous set of time. But many times we need to work with non-contiguous sets (work shifts in a week, bus schedules, etc). Right now, I'm using period arrays. But period arrays can contain overlapping and adjacent periods. And we have no way to indicate that a period array has been coalesced into a non-contiguous set. And what indexing strategies could be used with non-contiguous sets? Temporal Keys - We need two types of temporal keys. A primary key, exclusion type prevents overlap so someone isn't at two places at the same time. And a foreign key, inclusion type so we can check that the valid time of a child is contained with in the valid time of the parent. Jeff is working on the former, but there is no easy way to do the latter. There is actually a lot of theory out there but very few implementations. Although not an official standard, we try to follow the TSQL2 spec pretty closely. Further reading: Developing Time-Oriented Database Applications - Snodgrass http://www.cs.arizona.edu/~rts/tdbbook.pdf TSQL2 spec ftp://ftp.cs.arizona.edu/tsql/tsql2/spec.pdf Temporal Data and the Relational Model - Date et al http://books.google.com/books?isbn=1558608559 Dozens of publications http://timecenter.cs.aau.dk/pub.htm Regards, Scott Bailey -- 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] [PATCHES] updated hash functions for postgresql v1
On Wed, 2009-10-28 at 12:51 -0700, Jeff Davis wrote: Trying to develop and document a set of standardized, stable hash functions covering a wide range of possible use cases sounds like it may be better served by an extension. I suspect that some of the participants in this thread have PL/Proxy in mind. PL/Proxy should probably ship its own set of hash functions. If we ever get built-in partitioning by hash (see thread nearby and most previous ones like it), we should also think about providing a hash function that doesn't change output over versions and is independent of hash index implementation concerns. -- 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] Proposal - temporal contrib module
2009/10/29 Scott Bailey arta...@comcast.net: I would like to add a temporal contrib module. The most important piece would be adding a period data type and some support functions. Jeff Davis and I both have temporal projects on pgFoundry, and we've been collaborating for a while. But there are some areas we'd like to get some advice on. I thing, so it is very good idea. Temporar operation is very common task. Regards Pavel Stehule Disk format - A period can be represented as [closed-closed], (open-open), [closed-open) or (open-closed] intervals. Right now we convert these to the most common form, closed-open and store as two timestamptz's. Nulls - A common use case for periods is for modeling valid time. Often the end point is not known. For instance, you know when an employee has been hired but the termination time typically wouldn't be known ahead of time. We can either represent these with a null end time or with infinity. But I'm not sure how to deal with them. Obviously we can test for containment and overlap. But what about length or set operations? Non-contiguous Sets - A period defines a contiguous set of time. But many times we need to work with non-contiguous sets (work shifts in a week, bus schedules, etc). Right now, I'm using period arrays. But period arrays can contain overlapping and adjacent periods. And we have no way to indicate that a period array has been coalesced into a non-contiguous set. And what indexing strategies could be used with non-contiguous sets? Temporal Keys - We need two types of temporal keys. A primary key, exclusion type prevents overlap so someone isn't at two places at the same time. And a foreign key, inclusion type so we can check that the valid time of a child is contained with in the valid time of the parent. Jeff is working on the former, but there is no easy way to do the latter. There is actually a lot of theory out there but very few implementations. Although not an official standard, we try to follow the TSQL2 spec pretty closely. Further reading: Developing Time-Oriented Database Applications - Snodgrass http://www.cs.arizona.edu/~rts/tdbbook.pdf TSQL2 spec ftp://ftp.cs.arizona.edu/tsql/tsql2/spec.pdf Temporal Data and the Relational Model - Date et al http://books.google.com/books?isbn=1558608559 Dozens of publications http://timecenter.cs.aau.dk/pub.htm Regards, Scott Bailey -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Grouping sets
Hello I got a possibility to continue on GROUPING SETS see http://wiki.postgresql.org/wiki/Grouping_Sets My last patch was based on an sharing functionality with non recursive CTE. I would to recapitulate all options here and I would to prepare this patch for next commitfest. a) using actualised patch - review: http://archives.postgresql.org/message-id/162867790811240316y52227d88xe53527399b329...@mail.gmail.com + it is simple (share lot of code) + it is near to natural implementation - result is in good order + allows full implementation of ANSI SQL syntax - GROUP BY [ALL|DISTINCT] GROUPING SETS(.. - is serial - I read some objections - it is only syntactic sugar b) modification of a (directly using tuple store without CTE code) + add some new code - it isn't syntactic sugar c) go back to my first patch and use feeders - add some complexity to GROUP BY planner ( :-( ) +/- add new concept to PostgreSQL - semi parallel execution - when datatype isn't hashable, then have to use serial processing - have to solve some corner behave of our hashtable support - should be very fast I still prefer option a (with some cleaning) I invite any comments and helpers. Regards Pavel Stehule -- 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] [PATCHES] updated hash functions for postgresql v1
On Thu, 2009-10-29 at 09:47 +0200, Peter Eisentraut wrote: On Wed, 2009-10-28 at 12:51 -0700, Jeff Davis wrote: Trying to develop and document a set of standardized, stable hash functions covering a wide range of possible use cases sounds like it may be better served by an extension. I suspect that some of the participants in this thread have PL/Proxy in mind. Yes, I think that pl/proxy is the prime example of such use. PL/Proxy should probably ship its own set of hash functions. Or maybe we could just extract the hashes form some version of stock postgresql (say 8.3) and then make those available in contrib under the name stable_hashes ? If we ever get built-in partitioning by hash (see thread nearby and most previous ones like it), we should also think about providing a hash function that doesn't change output over versions and is independent of hash index implementation concerns. And maybe even documented ;) -- Hannu Krosing http://www.2ndQuadrant.com PostgreSQL Scalability and Availability Services, Consulting and Training -- 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] Proposal - temporal contrib module
Scott Bailey wrote: I would like to add a temporal contrib module. The most important piece would be adding a period data type and some support functions. Jeff Davis and I both have temporal projects on pgFoundry, and we've been collaborating for a while. I presume you're going to need some backend support and possibly new syntax for some of the operations, right? That seems more urgent to discuss than the possible inclusion into contrib. I'm very pleased to see people working on temporal issues, BTW! I used to work on a database that did a lot of temporal operations, but the DBMS didn't have any temporal data types or operations so we had to use a lot of triggers etc. to achieve that, and it didn't perform well. Nulls - A common use case for periods is for modeling valid time. Often the end point is not known. For instance, you know when an employee has been hired but the termination time typically wouldn't be known ahead of time. We can either represent these with a null end time or with infinity. But I'm not sure how to deal with them. Obviously we can test for containment and overlap. But what about length or set operations? Hmm. Infinity feels like a better match. The behavior of length and set operations falls out of that naturally. For example, length of a period with an infinite beginning or end is infinite. For set operations, for example the intersection of [123, infinity] and [100, 160] would be [123, 160]. Non-contiguous Sets - A period defines a contiguous set of time. But many times we need to work with non-contiguous sets (work shifts in a week, bus schedules, etc). Right now, I'm using period arrays. But period arrays can contain overlapping and adjacent periods. And we have no way to indicate that a period array has been coalesced into a non-contiguous set. And what indexing strategies could be used with non-contiguous sets? I'd stick to your current definition that a period is a contiguous set of time. A non-contiguous set consists of multiple contiguous periods, so it can be represented as multiple rows in a table. Temporal Keys - We need two types of temporal keys. A primary key, exclusion type prevents overlap so someone isn't at two places at the same time. And a foreign key, inclusion type so we can check that the valid time of a child is contained with in the valid time of the parent. Jeff is working on the former, but there is no easy way to do the latter. I'm very excited about this. Foreign keys don't seem that hard, you'll need foreign key triggers like we have today, but check for within instead of equal. Temporal Data and the Relational Model - Date et al http://books.google.com/books?isbn=1558608559 +1 for the approach in this book. I'm not familiar enough with the TSQL2 spec to say whether it follows it. It should also be kept in mind that although this class of problems are generally thought of as temporal issues, IOW dealing with time, the same approach works with ranges of integers or any other datatype with a well-defined sort order. It would be nice if the temporal data type would allow that too. -- Heikki Linnakangas 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
[HACKERS] FTP/GIT/WWW server move
On Friday 30 November at 1200 GMT, the server that hosts our primary FTP server, GIT server and one of the website mirrors will be moved to a new data center. Downtime is expected to be two to three hours. Apologies for any inconvenience this may cause. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PGDay.EU 2009 Conference: http://2009.pgday.eu/start -- 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] Proposal - temporal contrib module
Heikki Linnakangas wrote: Scott Bailey wrote: I would like to add a temporal contrib module. I'm very pleased to see people working on temporal issues, BTW! Me too - common use-case and difficult to handle without the right types/operators. Nulls - A common use case for periods is for modeling valid time. Often the end point is not known. For instance, you know when an employee has been hired but the termination time typically wouldn't be known ahead of time. We can either represent these with a null end time or with infinity. But I'm not sure how to deal with them. Obviously we can test for containment and overlap. But what about length or set operations? Hmm. Infinity feels like a better match. The behavior of length and set operations falls out of that naturally. For example, length of a period with an infinite beginning or end is infinite. For set operations, for example the intersection of [123, infinity] and [100, 160] would be [123, 160]. There are cases where one time is genuinely unknown, and there we need a null. For the until further notice scenarios, infinity seems the sensible choice. Where a null is present length is clearly null, and sets I guess should propagate the nulls. [123,null] intersecting [100,160] should be [123,null]. That's assuming we've got a guarantee that from=to for all periods. Temporal Keys - We need two types of temporal keys. A primary key, exclusion type prevents overlap so someone isn't at two places at the same time. You're going to upset a lot of managers if they can't do that ;-) -- Richard Huxton Archonet Ltd -- 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] Syntax for partitioning
Hi, So, I'll take over the work if there are no ones to do it. I'm thinking to add syntax support first. Table partitioning was proposed many times, but it is still not applied into core. The reason is it is too difficult to make perfect partitioning feature at once. I think syntax support is a good start. Guess we are back to square one again on Partitioning :), but as long as someone is willing to walk the whole nine yards with it, that would be just great! I had proposed Oracle style syntax a while back and had also submitted a WIP patch then. Again then my motive was to move forward in a piece-meal fashion on this feature. First solidify the syntax, keep using the existing inheritance mechanism and go one step at a time. I think a feature like Partitioning needs this kind of an approach, because it might turn out to be a lot of work with a lot of very many sub items. So +1 on solidifying the syntax first and then sorting out the other minute, intricate details later.. Regards, Nikhils First, I will add syntax for CREATE TABLE, ALTER TABLE ADD/DROP PARTITION. The syntax is borrowed from from Oracle and MySQL. Their characteristics are using LESS THAN in range partitioning. The keyword PARTITION is added to the full-reserved keyword list to support ADD/DROP PARTITION. Those syntax is merely a syntax sugar for INHERITS with CHECK. Declarations are translated into CHECK constraints. I have a plan to adjust pg_dump to dump definitions of partitioning in the correct format, but the actual implementation will be still based on constraint exclusion. In addition, hash partitioning is not implemented; syntax is parsed but not implemented error are raised for now. Here is syntax I propose: ALTER TABLE table_name ADD PARTITION name ...; ALTER TABLE table_name DROP PARTITION [IF EXISTS] name [CASCADE | RESTRICT]; Range partitioning: CREATE TABLE table_name ( columns ) PARTITION BY RANGE ( a_expr ) ( PARTITION name VALUES LESS THAN [(] const [)], PARTITION name VALUES LESS THAN [(] MAXVALUE [)] -- overflow partition ); List partitioning: CREATE TABLE table_name ( columns ) PARTITION BY LIST ( a_expr ) ( PARTITION name VALUES [IN] ( const [, ...] ), PARTITION name VALUES [IN] [(] DEFAULT [)] -- overflow partition ); Hash partitioning: CREATE TABLE table_name ( columns ) PARTITION BY HASH ( a_expr ) PARTITIONS num_partitions; CREATE TABLE table_name ( columns ) PARTITION BY HASH ( a_expr ) ( PARTITION name, ... ); Note: * Each partition can have optional WITH (...) and TABLESPACE clauses. * '(' and ')' are optional to support both Oracle and MySQL syntax. Comments welcome. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] Parsing config files in a directory
On Tue, 2009-10-27 at 20:40 -0700, Josh Berkus wrote: If you require that a tool (or SET PERISTENT) parse through a file in order to change one setting, then you've just doubled or tripled the code size of the tool, as well as added a host of failure conditions which wouldn't have existed otherwise. Hehe, this tool already exists. It's called postconf. And it actually works. With PostgreSQL! Just rename your postgresql.conf to main.cf for the time being. ;-) -- 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] Parsing config files in a directory
On Wed, 2009-10-28 at 09:39 -0700, Greg Stark wrote: On Wed, Oct 28, 2009 at 7:33 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Greg Smith escribió: This sounds familiar...oh, that's right, this is almost the same algorithm pgtune uses. And it sucks, It's also a blatant violation of packaging rules for Debian if not every distribution. If you edit the user's configuration file then there's no way to install a modified default configuration file. You can't tell the automatic modifications apart from the user's modifications. So the user will get a prompt asking if he wants the new config file or to keep his modifications which he never remembered making. This is not quite accurate. What the Debian policy says is that local changes to configuration files must be preserved during package upgrades. You are free to implement this in a variety of ways. One way to do it is to mark the file a conffile with dpkg, and then dpkg will handle the upgrades. If you mark a configuration file a conffile, then packages' maintainer scripts are not allowed to touch the file (because dpkg handles it). But this is irrelevant for the postgresql package, because postgresql.conf is not a conffile, primarily because it is created by the postgresql package's maintainer script in the first place (via initdb). Moreover, it is not illegal for a program or a routine that is explicitly invoked by a user to modify a configuration file (or a conffile even). The only policy is again packages' maintainer scripts (preinst, postint, prerm, postrm, for those reading along) modifying conffiles *automatically* during package installation or removal. So from a Debian packaging policy point of view, none of the schemes described so far appear to be disallowed outright. -- 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] Parsing config files in a directory
Le dimanche 25 octobre 2009 10:08:33, Peter Eisentraut a écrit : On lör, 2009-10-24 at 13:32 -0400, Greg Smith wrote: Regardless, the UI I was hoping for was to make the default postgresql.conf file end with a line like this: directory 'conf' I think something like is this is definitely more understandable for users and less overkill in the implementation. As a file point, I would prefer something like include 'whatever/*.conf' that is, listing the files as a glob pattern instead of naming a directory. Because packaging tools, editors, etc. will leave backup and temporary files lying around that you don't want to include, and we don't want to get involved into knowing all the naming patterns that people might want to use for those. Actually, include 'postgresql.conf.d/*.conf' +1 sounds nice as a default. -- Cédric Villemain Administrateur de Base de Données Cel: +33 (0)6 74 15 56 53 http://dalibo.com - http://dalibo.org signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] Parsing config files in a directory
Pavel Stehule wrote: 2009/10/27 Simon Riggs si...@2ndquadrant.com: On Tue, 2009-10-27 at 00:38 -0400, Greg Smith wrote: new feature One additional point that would be useful is a way to match up the usage of custom_variable_classes with this new style of .conf file processing. At the moment if you wish to add a custom variable class everybody needs to edit the *same* parameter. Finding which one to edit could be a little difficult with a whole directory to search in. I propose a new form of processing for that variable: each new parameter instance is added to last one, rather than replacing it. e.g. custom_variable_class = 'x' custom_variable_class = 'y' custom_variable_class = 'z' is equivalent to custom_variable_classes = 'x,y,z' That allows NewFeatureX to drop in a file called newfeaturex.conf, which looks like this custom_variable_class = 'newfeaturex' newfeaturex.param1 = x newfeaturex.param2 = y newfeaturex.param3 = z This requires no editing of any other files, just a straight drop in. That will make it much easier to produce real installers/deinstallers for add-in modules. I understand, but this behave is confusing. Maybe better is using some trivial keywords like append, delete, reset so append custom_variable_class = 'x' append custom_variable_class = 'y' append custom_variable_class = 'z' is custom_variable_classes = 'x,y,z' Why not allow something like += or .= instead of the = to denote appending to a list? custom_variable_classes += 'x' seems a whole lot nicer to me. cheers andrew -- 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] Parsing config files in a directory
2009/10/29 Andrew Dunstan and...@dunslane.net: Pavel Stehule wrote: 2009/10/27 Simon Riggs si...@2ndquadrant.com: On Tue, 2009-10-27 at 00:38 -0400, Greg Smith wrote: new feature One additional point that would be useful is a way to match up the usage of custom_variable_classes with this new style of .conf file processing. At the moment if you wish to add a custom variable class everybody needs to edit the *same* parameter. Finding which one to edit could be a little difficult with a whole directory to search in. I propose a new form of processing for that variable: each new parameter instance is added to last one, rather than replacing it. e.g. custom_variable_class = 'x' custom_variable_class = 'y' custom_variable_class = 'z' is equivalent to custom_variable_classes = 'x,y,z' That allows NewFeatureX to drop in a file called newfeaturex.conf, which looks like this custom_variable_class = 'newfeaturex' newfeaturex.param1 = x newfeaturex.param2 = y newfeaturex.param3 = z This requires no editing of any other files, just a straight drop in. That will make it much easier to produce real installers/deinstallers for add-in modules. I understand, but this behave is confusing. Maybe better is using some trivial keywords like append, delete, reset so append custom_variable_class = 'x' append custom_variable_class = 'y' append custom_variable_class = 'z' is custom_variable_classes = 'x,y,z' Why not allow something like += or .= instead of the = to denote appending to a list? custom_variable_classes += 'x' seems a whole lot nicer to me. -1 not all people knows C like languages. I don't thing so this is readable for non programmers. Pavel cheers andrew -- 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] Parsing config files in a directory
2009/10/29 Andrew Dunstan and...@dunslane.net: Pavel Stehule wrote: 2009/10/27 Simon Riggs si...@2ndquadrant.com: On Tue, 2009-10-27 at 00:38 -0400, Greg Smith wrote: new feature One additional point that would be useful is a way to match up the usage of custom_variable_classes with this new style of .conf file processing. At the moment if you wish to add a custom variable class everybody needs to edit the *same* parameter. Finding which one to edit could be a little difficult with a whole directory to search in. I propose a new form of processing for that variable: each new parameter instance is added to last one, rather than replacing it. e.g. custom_variable_class = 'x' custom_variable_class = 'y' custom_variable_class = 'z' is equivalent to custom_variable_classes = 'x,y,z' That allows NewFeatureX to drop in a file called newfeaturex.conf, which looks like this custom_variable_class = 'newfeaturex' newfeaturex.param1 = x newfeaturex.param2 = y newfeaturex.param3 = z This requires no editing of any other files, just a straight drop in. That will make it much easier to produce real installers/deinstallers for add-in modules. I understand, but this behave is confusing. Maybe better is using some trivial keywords like append, delete, reset so append custom_variable_class = 'x' append custom_variable_class = 'y' append custom_variable_class = 'z' is custom_variable_classes = 'x,y,z' Why not allow something like += or .= instead of the = to denote appending to a list? custom_variable_classes += 'x' seems a whole lot nicer to me. I would see that as making the config more programatic so while that might not look too weird to a developer, it could be confusing for a DBA or system administrator. I don't think that's how config files should work, and it also adds gotchas like the following. custom_variable_classes = 'x' custom_variable_classes += 'y' custom_variable_classes = 'z' That would result in the first 2 assignments being undone. Regards Thom -- 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] Parsing config files in a directory
Pavel Stehule wrote: 2009/10/29 Andrew Dunstan and...@dunslane.net: Pavel Stehule wrote: 2009/10/27 Simon Riggs si...@2ndquadrant.com: On Tue, 2009-10-27 at 00:38 -0400, Greg Smith wrote: new feature One additional point that would be useful is a way to match up the usage of custom_variable_classes with this new style of .conf file processing. At the moment if you wish to add a custom variable class everybody needs to edit the *same* parameter. Finding which one to edit could be a little difficult with a whole directory to search in. I propose a new form of processing for that variable: each new parameter instance is added to last one, rather than replacing it. e.g. custom_variable_class = 'x' custom_variable_class = 'y' custom_variable_class = 'z' is equivalent to custom_variable_classes = 'x,y,z' That allows NewFeatureX to drop in a file called newfeaturex.conf, which looks like this custom_variable_class = 'newfeaturex' newfeaturex.param1 = x newfeaturex.param2 = y newfeaturex.param3 = z This requires no editing of any other files, just a straight drop in. That will make it much easier to produce real installers/deinstallers for add-in modules. I understand, but this behave is confusing. Maybe better is using some trivial keywords like append, delete, reset so append custom_variable_class = 'x' append custom_variable_class = 'y' append custom_variable_class = 'z' is custom_variable_classes = 'x,y,z' Why not allow something like += or .= instead of the = to denote appending to a list? custom_variable_classes += 'x' seems a whole lot nicer to me. -1 not all people knows C like languages. I don't thing so this is readable for non programmers. Really, they don't know any Perl or Python or Java either? Maybe. Anyway, it seems to me a whole lot better than inventing a new thing that makes custom_variable_class as something to append to custom_variable_classes. If you're going to insist on using append foo = 'x' at least let it apply to the list that is actually being appended to, so we don't need to keep track of singular and plural forms. That's the part of your suggestion I really object to. cheers andrew -- 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] Show schema size with \dn+
On Wed, 2009-10-28 at 15:11 +0100, Anders Steinlein wrote: Is there any interest in expanding \dn+ to show schema size, similar to table sizes using \dt+ in 8.4? We use separate schemas for each user, so this would allow us to quickly look up the sizes of each user's data. A schema doesn't really have a size, because a schema is not a storage subdivision. -- 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] [PATCHES] updated hash functions for postgresql v1
Hannu Krosing ha...@2ndquadrant.com writes: Or maybe we could just extract the hashes form some version of stock postgresql (say 8.3) and then make those available in contrib under the name stable_hashes ? A better name would be wishful_thinking ... contrib does not have control over some of the main risk factors, such as changes to the internal representation of datatypes. 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] Show schema size with \dn+
On Thu, Oct 29, 2009 at 11:19 AM, Peter Eisentraut pete...@gmx.net wrote: On Wed, 2009-10-28 at 15:11 +0100, Anders Steinlein wrote: Is there any interest in expanding \dn+ to show schema size, similar to table sizes using \dt+ in 8.4? We use separate schemas for each user, so this would allow us to quickly look up the sizes of each user's data. A schema doesn't really have a size, because a schema is not a storage subdivision. Are you sure ? I believe that we can pass more information structuralized facilitating the life of admins as we This item of TODO can be constructed with these easinesses[1] - Make psql's \d commands more consistent in its handling of schemas [1] http://archives.postgresql.org/pgsql-hackers/2004-11/msg00014.php -- Marcelo Costa www.marcelocosta.net - “You can't always get what want”, Doctor House in apology to Mike Jagger
Re: [HACKERS] Parsing config files in a directory
Andrew Dunstan and...@dunslane.net writes: Anyway, it seems to me a whole lot better than inventing a new thing that makes custom_variable_class as something to append to custom_variable_classes. If you're going to insist on using append foo = 'x' at least let it apply to the list that is actually being appended to, so we don't need to keep track of singular and plural forms. That's the part of your suggestion I really object to. The scheme really really has to have a set and an append operation. Otherwise, undesirable things happen whenever the conf file is re-read. I would envision postgresql.conf containing custom_variable_classes = '' and then individual config files containing custom_variable_classes += 'foo' Exact syntax isn't that important, although I confess to liking += better than a keyword. Another possibility is that the reset to empty is somehow implicit at the start of reading the conf file. But I'd still think it's better if the appending operations are visibly different from ordinary assignment. 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] Syntax for partitioning
On 29 Oct 2009, at 02:15, Itagaki Takahiro wrote: I'd like to improve partitioning feature in 8.5. Kedar-san's previous work is wonderful, but I cannot see any updated patch. http://archives.postgresql.org/message-id/bd8134a40906080702s96c90a9q3bbb581b9bd0d...@mail.gmail.com So, I'll take over the work if there are no ones to do it. I'm thinking to add syntax support first. Table partitioning was proposed many times, but it is still not applied into core. The reason is it is too difficult to make perfect partitioning feature at once. I think syntax support is a good start. First, I will add syntax for CREATE TABLE, ALTER TABLE ADD/DROP PARTITION. The syntax is borrowed from from Oracle and MySQL. Their characteristics are using LESS THAN in range partitioning. The keyword PARTITION is added to the full-reserved keyword list to support ADD/DROP PARTITION. Those syntax is merely a syntax sugar for INHERITS with CHECK. Declarations are translated into CHECK constraints. I have a plan to adjust pg_dump to dump definitions of partitioning in the correct format, but the actual implementation will be still based on constraint exclusion. In addition, hash partitioning is not implemented; syntax is parsed but not implemented error are raised for now. Here is syntax I propose: ALTER TABLE table_name ADD PARTITION name ...; ALTER TABLE table_name DROP PARTITION [IF EXISTS] name [CASCADE | RESTRICT]; Range partitioning: CREATE TABLE table_name ( columns ) PARTITION BY RANGE ( a_expr ) ( PARTITION name VALUES LESS THAN [(] const [)], PARTITION name VALUES LESS THAN [(] MAXVALUE [)] -- overflow partition ); List partitioning: CREATE TABLE table_name ( columns ) PARTITION BY LIST ( a_expr ) ( PARTITION name VALUES [IN] ( const [, ...] ), PARTITION name VALUES [IN] [(] DEFAULT [)] -- overflow partition ); Hash partitioning: CREATE TABLE table_name ( columns ) PARTITION BY HASH ( a_expr ) PARTITIONS num_partitions; CREATE TABLE table_name ( columns ) PARTITION BY HASH ( a_expr ) ( PARTITION name, ... ); Note: * Each partition can have optional WITH (...) and TABLESPACE clauses. * '(' and ')' are optional to support both Oracle and MySQL syntax. Comments welcome. +1000 Thanks ! (most anticipated feature for 8.5, here, next to replication [well, I am interested in multi master, but that's not going to happen :P ] ) -- 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] [PATCHES] updated hash functions for postgresql v1
On Thu, 2009-10-29 at 09:32 -0400, Tom Lane wrote: Hannu Krosing ha...@2ndquadrant.com writes: Or maybe we could just extract the hashes form some version of stock postgresql (say 8.3) and then make those available in contrib under the name stable_hashes ? A better name would be wishful_thinking ... contrib does not have control over some of the main risk factors, such as changes to the internal representation of datatypes. Good point. But the internal layout of data types popular for use as hash key changes rarely. The hash function change, however, is a showstopper for everyone concerned. -- 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] WIP: push AFTER-trigger execution into ModifyTable node
Robert Haas robertmh...@gmail.com writes: To be honest, I'm not entirely comfortable with either behavior. Pipelining a delete out of one table into an insert into another table seems VERY useful to me, and I'd like us to have a way to do that. On the other hand, in more complex cases, the fact that the effects of a statement are normally not visible to that statement could lead to some fairly confusing behavior, especially when triggers are involved. Yup, that's right. The trigger timing is really the least of the issues. If you consider that the various WITH clauses run concurrently with each other and the main statement, rather than sequentially, then (1) you have implementation-dependent, and not very desirable, behaviors if any of the statements modify the same table; (2) none of the statements will see each others' effects, which is certainly going to confuse some people; (3) I'm afraid that there's no good way to ensure that the modifying statements run to completion if the top-level SELECT stops short of reading that CTE to its end. Pipelined execution would be nice but I really doubt that it's worth what we'd have to give up to have it. The one-at-a-time behavior will be simple to understand and reliable to use. Concurrent execution won't be either. 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] Parsing config files in a directory
Thom Brown thombr...@gmail.com writes: custom_variable_classes = 'x' custom_variable_classes += 'y' custom_variable_classes = 'z' That would result in the first 2 assignments being undone. That's why I don't see how having as many files as you want to *for tool based* configuration is a improvement of any sort. It's certainly a cool piece of feature for those DBA who want to organise their setup manually without having to add a lot of includes into their current file. That's fine and convenient. Ok. But as far as tools is concerned, that only looks awful from here. I think we have 2 different needs here, and we're trying to abuse a single mechanism for both. Maybe we need to accept that including all files in a dir is nice for human organisation of config files but not so much for automated tools. What they need is a clear API. Or two: We need one API for editing setup of a live server, that's SET PERSISTENT ... TO ... and friends (SET PERSISTENT ... APPEND TO ...), and another one for tools which wants to act from a given setup with no live server to talk to arround. Is this second need really important? I guess it is, but the question would better be explicit I think. If it is, then we need to provide some kind of multi-language library for editing our setup, or something so simple you don't need one. Regards, -- dim -- 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] Parsing config files in a directory
Really, they don't know any Perl or Python or Java either? Maybe. Anyway, it seems to me a whole lot better than inventing a new thing that makes custom_variable_class as something to append to custom_variable_classes. If you're going to insist on using append foo = 'x' at least let it apply to the list that is actually being appended to, so we don't need to keep track of singular and plural forms. That's the part of your suggestion I really object to. I have to agree with you. From language view is += syntax better. Actually, i would to see some more verbose syntax. There are two significantly different behave and only one difference is one symbol. But I don't know better syntax. Regards Pavel cheers andrew -- 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] Parsing config files in a directory
On Thu, 2009-10-29 at 12:31 +, Thom Brown wrote: 2009/10/29 Andrew Dunstan and...@dunslane.net: Why not allow something like += or .= instead of the = to denote appending to a list? custom_variable_classes += 'x' seems a whole lot nicer to me. I would see that as making the config more programatic so while that might not look too weird to a developer, it could be confusing for a DBA or system administrator. I don't think that's how config files should work, and it also adds gotchas like the following. The DBAs I know would think we were absolutely off our rocker for such a thing. At least it would make great humor at presentations. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering If the world pushes look it in the eye and GRR. Then push back harder. - Salamander -- 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] Parsing config files in a directory
On Thu, Oct 29, 2009 at 9:44 AM, Tom Lane t...@sss.pgh.pa.us wrote: Andrew Dunstan and...@dunslane.net writes: Anyway, it seems to me a whole lot better than inventing a new thing that makes custom_variable_class as something to append to custom_variable_classes. If you're going to insist on using append foo = 'x' at least let it apply to the list that is actually being appended to, so we don't need to keep track of singular and plural forms. That's the part of your suggestion I really object to. The scheme really really has to have a set and an append operation. Otherwise, undesirable things happen whenever the conf file is re-read. I would envision postgresql.conf containing custom_variable_classes = '' and then individual config files containing custom_variable_classes += 'foo' Exact syntax isn't that important, although I confess to liking += better than a keyword. Another possibility is that the reset to empty is somehow implicit at the start of reading the conf file. But I'd still think it's better if the appending operations are visibly different from ordinary assignment. I was just looking through the code for this last night and it appears that parser generally allows either setting = value or setting value. We usually write work_mem = 4M and include foo.conf but it looks like work_mem 4M and include = foo.conf work just as well. If you think of custom_variable_class(es) as a declaration, it's not so bad: custom_variable_class 'foo' Actually, custom_variable_classes and include already have special-case handling in there that exists for no other GUC. Another option would be to introduce a section syntax, something like what M$ does. We could define a line that contains just [foo] to mean define foo as a custom variable class and automatically put all the rest of the settings in this section into that namespace. ...Robert -- 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] Parsing config files in a directory
Joshua D. Drake wrote: On Thu, 2009-10-29 at 12:31 +, Thom Brown wrote: 2009/10/29 Andrew Dunstan and...@dunslane.net: Why not allow something like += or .= instead of the = to denote appending to a list? custom_variable_classes += 'x' seems a whole lot nicer to me. I would see that as making the config more programatic so while that might not look too weird to a developer, it could be confusing for a DBA or system administrator. I don't think that's how config files should work, and it also adds gotchas like the following. The DBAs I know would think we were absolutely off our rocker for such a thing. At least it would make great humor at presentations. The whole config file is a joke. We'd never do it the way we do if we were designing it from scratch, but we seem to be incapable of biting the bullet and replacing it with something sane, which is why I have ignored most of the current debate. cheers andrew -- 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] Parsing config files in a directory
Robert Haas robertmh...@gmail.com writes: Another option would be to introduce a section syntax, something like what M$ does. We could define a line that contains just [foo] to mean define foo as a custom variable class and automatically put all the rest of the settings in this section into that namespace. That seems like a pretty darn bad idea, unless we munge the parser to terminate the section when exiting a particular include file. Otherwise files that don't set custom_variable_class will have surprising interactions with those that do. I don't see any particularly great benefit in allowing people to omit the prefix anyway --- what if you want to set some custom and some standard variables? With the above definition you can't do that. The fact that custom_variable_classes interacts with other declarations around it is already an ugly misfeature of the design. Let us please not add more such interactions. 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] Parsing config files in a directory
Andrew Dunstan and...@dunslane.net writes: The whole config file is a joke. We'd never do it the way we do if we were designing it from scratch, Why not, pray tell? We did design it from scratch, once upon a time, and I don't see that the design is so obviously broken that we'd not do the same thing if starting over. but we seem to be incapable of biting the bullet and replacing it with something sane, which is why I have ignored most of the current debate. I guess we'll just go without the benefit of your superior intelligence then. 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] Show schema size with \dn+
* Peter Eisentraut (pete...@gmx.net) wrote: On Wed, 2009-10-28 at 15:11 +0100, Anders Steinlein wrote: Is there any interest in expanding \dn+ to show schema size, similar to table sizes using \dt+ in 8.4? We use separate schemas for each user, so this would allow us to quickly look up the sizes of each user's data. A schema doesn't really have a size, because a schema is not a storage subdivision. Erm, it's a namespace inside of which objects exist. Sure, the individual tables might be on different tablespaces, but the schema's size is a direct aggregation of the objects which it contains. The argument that it doesn't have a size is a bit like arguing that 'du' should report the directory's size rather than the combined size of everything in it. I look forward to someone adding this, it'd be nice to have. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Parsing config files in a directory
On Thu, 2009-10-29 at 11:42 -0400, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: The whole config file is a joke. We'd never do it the way we do if we were designing it from scratch, Why not, pray tell? We did design it from scratch, once upon a time, and I don't see that the design is so obviously broken that we'd not do the same thing if starting over. Because, there is no right answer. There is not a defined standard to attach to. The requirements all over the place and in this case there are a large number of contributors that actually have an opinion (unlike say the Hot Standby implementation). but we seem to be incapable of biting the bullet and replacing it with something sane, which is why I have ignored most of the current debate. I guess we'll just go without the benefit of your superior intelligence then. I think the point is, this discussion has happened many times. People get kind of tired of burning cycles on the same thing over and over. I personally don't have any problem with the existing postgresql.conf except for the following: 1. I should be able to edit it from PostgreSQL via a query (assuming super user privs or some such thing) In fact I would be perfectly happy to NEVER open an editor again to edit the postgresql.conf and just have a revision system in conjunction with a SET LOCAL|GLOBAL command. Where local would be for the session and GLOBAL would be permanent. Then again, I don't really care that I have to open an editor either. I also understand that a SET LOCAL|GLOBAL is likely to wreak havoc on an include implementation. Of course a SET LOCAL|GLOBAL implementation eliminates the need for an include implementation. Queue up the pitchforks now. 2. The file contains a bunch of information that shouldn't be there. We should have the bare minimum required for PostgreSQL to start. As far as this on-going postgres vs postgresql debate. There are some already pretty well defined industry (if not specific body) standards for configuration files. The ones I run into the most are: XML : Pretty much anything written in Java is going to use this Config : This is what you see in a lot of Perl or Python installs where you have something like this: [CONNECTIONS] listen_addresses = [PERFORMANCE] checkpoint_segments = etc... In regards to parsing files in a directory. It makes sense. Why the implementation is so difficult is beyond me. Can't we just look at Apache and say, Gee, it may not be perfect but it does everything we need, let's use their implementation.? Sincerely, Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering If the world pushes look it in the eye and GRR. Then push back harder. - Salamander -- 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] Parsing config files in a directory
Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: The whole config file is a joke. We'd never do it the way we do if we were designing it from scratch, Why not, pray tell? We did design it from scratch, once upon a time, and I don't see that the design is so obviously broken that we'd not do the same thing if starting over. but we seem to be incapable of biting the bullet and replacing it with something sane, which is why I have ignored most of the current debate. I guess we'll just go without the benefit of your superior intelligence then. *sigh* Time passes, and surely there are plenty of thing we wouldn't do the same today if we had a chance to do them again from scratch. That's not slamming anyone who was involved in the past. People made decisions based on knowledge and experience at the time. Despite your sarcasm, I don't claim any superior intelligence, but I also don't see the sorts of things people are talking about making any great improvement. I play with config files for a LOT of different pieces of software, because a lot of what I do involves integrating disparate systems. Years ago flatish config files were pretty common, but that's much less true today. Even fairly old pieces of software like apache have some provision for structure. My personal opinion (and that's all it is) is that until we tackle that, the rest is just tinkering. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] about GiST indexes
Hello to all, I'm a young programmer student, and I would like to realize TPR-index on Postgresql system. Of course I want to use GiST template, rather than writing core :) I have some questions, and I would be very grateful if anybody answers my question. 1) where could I know the prototypes of such functions as union, pentaly and etc. And to read more detally than in PostgreSql documentation ? 2) How could I save in index structure index created time, and to get this value when I envoking such procedures such as union, penalty, pick split ? Best regards, Sergej Galkin
Re: [HACKERS] Parsing config files in a directory
On Thu, Oct 29, 2009 at 11:38 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Another option would be to introduce a section syntax, something like what M$ does. We could define a line that contains just [foo] to mean define foo as a custom variable class and automatically put all the rest of the settings in this section into that namespace. That seems like a pretty darn bad idea, unless we munge the parser to terminate the section when exiting a particular include file. Yeah, that's what I had in mind. Otherwise files that don't set custom_variable_class will have surprising interactions with those that do. I don't see any particularly great benefit in allowing people to omit the prefix anyway --- what if you want to set some custom and some standard variables? With the above definition you can't do that. Honestly, I don't see much downside to that - why would you want to do such a thing? But, anyway, you could still allow an explicit namespace to be provided, and pick one (say main) for the default namespace. Anyway, I don't feel strongly about it. The main thing is that if all the custom variable classes have to be declared in one place, then you can't really have independent config files that drop into a directory somewhere for add-on modules, because you'll still have to munge the main file to set up the custom_variable_classes. The fact that custom_variable_classes interacts with other declarations around it is already an ugly misfeature of the design. Let us please not add more such interactions. It is definitely ugly, but that's partly because it's implemented as a kludge to keep the lexer simple. Declarative and section-dividing constructs are useful, which is why other config files (like Apache) have them. But do WE need them? I don't know. Honestly, we don't have that much configuration, and most of what we do have is stored in the database itself. That's a feature, and I have no desire to change it. If we could make the cluster start up without needing postgresql.conf, I'd argue for getting rid of it and keeping global GUC settings in the database just as we do for per-database, per-user, per-user-and-database, and per-function GUCs, as well as reloptions. But I think we've determined that there's really no reasonable way to make that happen. ...Robert -- 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] Syntax for partitioning
Itagaki Takahiro wrote: The keyword PARTITION is added to the full-reserved keyword list to support ADD/DROP PARTITION. Any chance to avoid that? PARTITION seems like something people might well use as a column or variable name. OTOH, it is reserved in SQL2008 and SQL2003. -- Heikki Linnakangas 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] [PATCHES] updated hash functions for postgresql v1
On Thu, 2009-10-29 at 09:32 -0400, Tom Lane wrote: Hannu Krosing ha...@2ndquadrant.com writes: Or maybe we could just extract the hashes form some version of stock postgresql (say 8.3) and then make those available in contrib under the name stable_hashes ? A better name would be wishful_thinking ... contrib does not have control over some of the main risk factors, such as changes to the internal representation of datatypes. My understanding was, that contrib is actually the only possibility to have something maintained in sync with core postgreSQL. regards, tom lane -- Hannu Krosing http://www.2ndQuadrant.com PostgreSQL Scalability and Availability Services, Consulting and Training -- 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] Parsing config files in a directory
Joshua D. Drake j...@commandprompt.com writes: In regards to parsing files in a directory. It makes sense. Why the implementation is so difficult is beyond me. Can't we just look at Apache and say, Gee, it may not be perfect but it does everything we need, let's use their implementation.? Reading files in a directory does not seem too hard, IIUC Magnus already implemented it. What's hard is to convince anyone it helps solving the tool editing problem (call it SET GLOBAL if you want). Regards, -- dim -- 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] about GiST indexes
Hi, Sergej Galkin sergej.gal...@gmail.com writes: 1) where could I know the prototypes of such functions as union, pentaly and etc. And to read more detally than in PostgreSql documentation ? Have you read that yet? http://www.postgresql.org/docs/8.4/interactive/gist-implementation.html 2) How could I save in index structure index created time, and to get this value when I envoking such procedures such as union, penalty, pick split ? I'm not sure I understand the questiom. Is the created time part of the data you're indexing? -- dim -- 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] Syntax for partitioning
On Thu, Oct 29, 2009 at 3:35 AM, Nikhil Sontakke nikhil.sonta...@enterprisedb.com wrote: So +1 on solidifying the syntax first and then sorting out the other minute, intricate details later.. I like that idea as well but I have a concern. What will we do with pg_dump. If the PARTITION commands are just syntactic sugar for creating constraints and inherited tables then pg_dump will have to generate the more generic commands for those objects. When we eventually have real partitioning then restoring such a dump will not create real partitions, just inherited tables. Perhaps we need some kind of option to reverse-engineer partitioning commands from the inheritance structure, but I fear having pg_dump reverse engineer inherited tables to produce partitioning commands will be too hard and error-prone. Hopefully that's too pessimistic though, if they were produced by PARTITION commands they should be pretty regular. -- greg -- 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] Syntax for partitioning
On Thursday 29 October 2009 18:33:22 Greg Stark wrote: On Thu, Oct 29, 2009 at 3:35 AM, Nikhil Sontakke nikhil.sonta...@enterprisedb.com wrote: So +1 on solidifying the syntax first and then sorting out the other minute, intricate details later.. I like that idea as well but I have a concern. What will we do with pg_dump. If the PARTITION commands are just syntactic sugar for creating constraints and inherited tables then pg_dump will have to generate the more generic commands for those objects. When we eventually have real partitioning then restoring such a dump will not create real partitions, just inherited tables. Perhaps we need some kind of option to reverse-engineer partitioning commands from the inheritance structure, but I fear having pg_dump reverse engineer inherited tables to produce partitioning commands will be too hard and error-prone. Hopefully that's too pessimistic though, if they were produced by PARTITION commands they should be pretty regular. One could have a system catalog containing the partitioning information and generate the constraints et al. from that and mark them in pg_depend... Andres -- 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] Proposal - temporal contrib module
On Thu, 2009-10-29 at 00:31 -0700, Scott Bailey wrote: Nulls - A common use case for periods is for modeling valid time. Often the end point is not known. For instance, you know when an employee has been hired but the termination time typically wouldn't be known ahead of time. We can either represent these with a null end time or with infinity. But I'm not sure how to deal with them. Obviously we can test for containment and overlap. But what about length or set operations? I think we should allow the database designer flexibility here. For instance, there are good arguments for using separate relations for things with a known begin time and no known end, and things with a definite begin and end time. However, infinity also makes sense, particularly in cases where something that is true can never again be false. NULL support is a little stranger. We can allow it by extending the data representation, but the semantics might not match what people expect from NULL. Should it be treated like a NULL in an array, or a NULL in a record value, or what? If we allow NULL on one side of a period, that may require some backend support, depending on the semantics. My feeling right now is to not provide a way for one side of the period to be NULL, but if we come up with clear enough semantics maybe it's useful. I tend to think it would cause more confusion than anything. For any kind of built-in audit log functionality (transaction-time based), I don't see any utility for NULL. Temporal Keys - We need two types of temporal keys. A primary key, exclusion type prevents overlap so someone isn't at two places at the same time. And a foreign key, inclusion type so we can check that the valid time of a child is contained with in the valid time of the parent. Jeff is working on the former, but there is no easy way to do the latter. I believe temporal foreign keys can be implemented the same way foreign keys are now (except with contained by rather than equals). We should provide some support to make that easier, but I don't think that's a major issue. Regards, Jeff Davis -- 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] Proposal - temporal contrib module
On Thu, 2009-10-29 at 09:37 +, Richard Huxton wrote: There are cases where one time is genuinely unknown, and there we need a null. The semantics of a period with one side NULL require a more clear definition. I don't personally see a lot of utility in trying to support NULL semantics, but if we want to support it, it needs to be clearly defined. Does TSQL-2 offer any guidance here? That's assuming we've got a guarantee that from=to for all periods. Of course. Except that means that a NULL on one side of a period is a little less unknown than other kinds of NULLs ;) Regards, Jeff Davis -- 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] Proposal - temporal contrib module
I would like to add a temporal contrib module. The most important piece would be adding a period data type and some support functions. Jeff Davis and I both have temporal projects on pgFoundry, and we've been collaborating for a while. I presume you're going to need some backend support and possibly new syntax for some of the operations, right? That seems more urgent to discuss than the possible inclusion into contrib. Jeff Davis is already working on solving these issues for 8.5. But rather than wait until 8.6 or later to get a period data type added to core, I felt it was important to get the period type out in front of people to start using and testing. Plus we wanted to gauge interest from the community. Should we forge ahead and try to become the first general purpose database with support for temporal databases? Or should we wait another 20 years and see if an official specification materializes? I'm very pleased to see people working on temporal issues, BTW! I used to work on a database that did a lot of temporal operations, but the DBMS didn't have any temporal data types or operations so we had to use a lot of triggers etc. to achieve that, and it didn't perform well. Nulls - A common use case for periods is for modeling valid time. Often the end point is not known. For instance, you know when an employee has been hired but the termination time typically wouldn't be known ahead of time. We can either represent these with a null end time or with infinity. But I'm not sure how to deal with them. Obviously we can test for containment and overlap. But what about length or set operations? Hmm. Infinity feels like a better match. The behavior of length and set operations falls out of that naturally. For example, length of a period with an infinite beginning or end is infinite. For set operations, for example the intersection of [123, infinity] and [100, 160] would be [123, 160]. Two different answers from two respondents. And is there a conceptual difference between NULL and +/- infinity? Nothing lasts forever. So when would it make sense to use one verses the other? So in the example I gave Non-contiguous Sets - A period defines a contiguous set of time. But many times we need to work with non-contiguous sets (work shifts in a week, bus schedules, etc). Right now, I'm using period arrays. But period arrays can contain overlapping and adjacent periods. And we have no way to indicate that a period array has been coalesced into a non-contiguous set. And what indexing strategies could be used with non-contiguous sets? I'd stick to your current definition that a period is a contiguous set of time. A non-contiguous set consists of multiple contiguous periods, so it can be represented as multiple rows in a table. That's pretty much my sentiments exactly. But Jeff wanted to be sure that we didn't make a decision now that would limit it's usefulness later. Temporal Keys - We need two types of temporal keys. A primary key, exclusion type prevents overlap so someone isn't at two places at the same time. And a foreign key, inclusion type so we can check that the valid time of a child is contained with in the valid time of the parent. Jeff is working on the former, but there is no easy way to do the latter. I'm very excited about this. Foreign keys don't seem that hard, you'll need foreign key triggers like we have today, but check for within instead of equal. Temporal Data and the Relational Model - Date et al http://books.google.com/books?isbn=1558608559 +1 for the approach in this book. I'm not familiar enough with the TSQL2 spec to say whether it follows it. It should also be kept in mind that although this class of problems are generally thought of as temporal issues, IOW dealing with time, the same approach works with ranges of integers or any other datatype with a well-defined sort order. It would be nice if the temporal data type would allow that too. The period concept relates very closely to mathematical intervals. (In fact, I would argue that the SQL interval should actually be named period and the SQL period should be named interval so they matched their mathematical counterparts.) My primary concern is timestamp intervals, but I see no reason the exact same concepts wouldn't apply to intervals of integers, floats, dates, etc. And actually there is a fair amount of overlap with spatial. The main difference being the number of dimensions. But the concepts of overlap, containment, and set operations like union and intersection are the same. Scott Bailey -- 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] Proposal - temporal contrib module
On Thu, 2009-10-29 at 10:54 +0200, Heikki Linnakangas wrote: I presume you're going to need some backend support and possibly new syntax for some of the operations, right? That seems more urgent to discuss than the possible inclusion into contrib. There are various areas that need work inside the backend: * Semantics 1. Allow temporal keys -- this is accomplished via operator exclusion constraints, which I hope can be committed in the next commit fest. 2. Allow postgres to understand types like PERIOD, so that it can find important operators like @, , . * Syntax Sugar 1. Temporal keys 2. Temporal FKs 3. Temporal Join 4. Creating a simple audit log 5. Possible PERIOD constructor syntax sugar? * Performance 1. Modified merge join And I believe the rest can be done using the existing type infrastructure, i.e. as a contrib module. I think it makes a lot of sense to discuss and develop the backend changes and PERIOD data type in parallel. Hmm. Infinity feels like a better match. The behavior of length and set operations falls out of that naturally. For example, length of a period with an infinite beginning or end is infinite. For set operations, for example the intersection of [123, infinity] and [100, 160] would be [123, 160]. I agree. If TSQL-2 addresses NULL semantics clearly enough, we might want to allow it. I think it will just cause confusion, however. I'd stick to your current definition that a period is a contiguous set of time. A non-contiguous set consists of multiple contiguous periods, so it can be represented as multiple rows in a table. I think there is a lot of value in non-contiguous sets, but PERIOD is a good start. It should also be kept in mind that although this class of problems are generally thought of as temporal issues, IOW dealing with time, the same approach works with ranges of integers or any other datatype with a well-defined sort order. Agreed. It would be nice if the temporal data type would allow that too. If I understand what you're saying, you're alluding to a type where you can do things like: RANGE(timestamptz) which would be equivalent to a PERIOD. Typmod almost provides enough flexibility, but it can't store a full OID, so we'd need to get creative. There are probably some other issues here as well, because the current type system isn't really designed for this kind of thing. Do you have any ideas or guidance here? Regards, Jeff Davis -- 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] Parsing config files in a directory
All of this *also* applies to shared_preload_libraries. We also need to be able to specify new load libraries without editing the same darn parameter. --- On Wed, 2009-10-28 at 22:00 +, Simon Riggs wrote: On Tue, 2009-10-27 at 00:38 -0400, Greg Smith wrote: new feature One additional point that would be useful is a way to match up the usage of custom_variable_classes with this new style of .conf file processing. At the moment if you wish to add a custom variable class everybody needs to edit the *same* parameter. Finding which one to edit could be a little difficult with a whole directory to search in. I propose a new form of processing for that variable: each new parameter instance is added to last one, rather than replacing it. e.g. custom_variable_class = 'x' custom_variable_class = 'y' custom_variable_class = 'z' is equivalent to custom_variable_classes = 'x,y,z' That allows NewFeatureX to drop in a file called newfeaturex.conf, which looks like this custom_variable_class = 'newfeaturex' newfeaturex.param1 = x newfeaturex.param2 = y newfeaturex.param3 = z This requires no editing of any other files, just a straight drop in. That will make it much easier to produce real installers/deinstallers for add-in modules. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Hy
Dear, I have few suggestions about new features of postgresql. 1.Replication and clustering included in core of postgresql 2.other support beter olap and data warehouse solutions. support bulk load with c-api. thanks marko
Re: [HACKERS] Hy
On Thu, Oct 29, 2009 at 3:46 PM, Marko marko_iva...@yahoo.com wrote: Dear, I have few suggestions about new features of postgresql. 1.Replication and clustering included in core of postgresql There are projects being worked on in this area, and add-on products like Slony that can be used as well. You might also want to look at this email: http://archives.postgresql.org/pgsql-hackers/2008-05/msg00913.php It's not an easy feature to implement, which is why it isn't done yet. 2.other support beter olap and data warehouse solutions. support bulk load with c-api. This is quite vague. You'll have to provide some more specifics if you want to be taken seriously. In general, this mailing list is for discussions of PostgreSQL development. If you have the ability to write the code that is needed to implement these features yourself, you can propose patches here. If not, there are organizations listed on postgresql.org who will do it for you in exchange for money. If you can't write the code yourself and don't have the money to pay someone else to do it, then posting here is not going to help you unless someone here thinks it's such a good idea that they're willing to do it for free. That might happen for a small adjustment to the system but is unlikely to happen for a major new feature. I would suggest that you start with a different mailing list, like pgsql-general, and say How do I...? rather than PostgreSQL should PostgreSQL does a lot of things very well already. If you find a specific area that could benefit from a specific improvement, then you can come back and propose that here. ...Robert -- 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] Syntax for partitioning
On tor, 2009-10-29 at 11:15 +0900, Itagaki Takahiro wrote: Range partitioning: CREATE TABLE table_name ( columns ) PARTITION BY RANGE ( a_expr ) ( PARTITION name VALUES LESS THAN [(] const [)], PARTITION name VALUES LESS THAN [(] MAXVALUE [)] -- overflow partition ); Maybe this needs to mention the actual operator name instead of LESS THAN, in case the operator is not named or the user wants to use a different one. Hash partitioning: CREATE TABLE table_name ( columns ) PARTITION BY HASH ( a_expr ) PARTITIONS num_partitions; CREATE TABLE table_name ( columns ) PARTITION BY HASH ( a_expr ) ( PARTITION name, ... ); Unless someone comes up with a maintenance plan for stable hash functions, we should probably not dare look into this yet. -- 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] Syntax for partitioning
On Fri, 2009-10-30 at 00:10 +0200, Peter Eisentraut wrote: On tor, 2009-10-29 at 11:15 +0900, Itagaki Takahiro wrote: Range partitioning: CREATE TABLE table_name ( columns ) PARTITION BY RANGE ( a_expr ) ( PARTITION name VALUES LESS THAN [(] const [)], PARTITION name VALUES LESS THAN [(] MAXVALUE [)] -- overflow partition ); Maybe this needs to mention the actual operator name instead of LESS THAN, in case the operator is not named or the user wants to use a different one. I can't help but wonder if the PERIOD type might be better for representing a partition range. It would make it easier to express and enforce the constraint that no two partition ranges overlap ;) Regards, Jeff Davis -- 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] \d+ for long view definitions?
On tis, 2009-09-01 at 14:29 -0400, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: I think we should always or never show the view definition, not sometimes. Yeah. I can live with \d not showing it and \d+ showing it --- as Peter already mentioned, that would be consistent with \df behavior. Making it depend on the length is just weird. The patch for this endeavor is: diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -1306,7 +1306,7 @@ describeOneTableDetails(const char *schemaname, printTableAddHeader(cont, headers[i], true, 'l'); /* Check if table is a view */ - if (tableinfo.relkind == 'v') + if (tableinfo.relkind == 'v' verbose) { PGresult *result; Any final words? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] grant with hierarchy option
There is a gap in the permission scheme for inheritance setups. Say you have this: CREATE TABLE persons (...); CREATE TABLE employees (...) INHERITS (persons); GRANT SELECT ON persons TO foo; Then user foo can extract who the employees are using SELECT * FROM persons EXCEPT SELECT * FROM ONLY persons; With UPDATE permissions one could then even do moderately evil things like only changing the employees. Proper encapsulation would require that using ONLY is only permitted when specifically allowed. That is what GRANT SELECT WITH HIERARCHY OPTION does, per SQL standard. It is basically a funny spelling for another privilege type that means may use ONLY. I think this would be the proper and useful thing to do, especially in conjunction with the new recursive grant behavior. There would probably be some upgrading issues. For example, GRANTs imported via pg_dump from 8.4 would probably need to change SELECT to SELECT WITH HIERARCHY OPTION, and even that technically wouldn't cover all cases. Comments? -- 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] WIP: push AFTER-trigger execution into ModifyTable node
On Thu, Oct 29, 2009 at 7:17 AM, Tom Lane t...@sss.pgh.pa.us wrote: Pipelined execution would be nice but I really doubt that it's worth what we'd have to give up to have it. The one-at-a-time behavior will be simple to understand and reliable to use. Concurrent execution won't be either. I think the ideal way to get pipelined execution will be to detect the cases where it's safe, ie, no deletes, inserts, or updates, no recursive calls, and only one call site, and inline the sql directly into the callsite. Actually we could do that even if there are two callsites if there are no volatile functions but estimating whether that would be a win or a loss would be hard. -- greg -- 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] Syntax for partitioning
Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: The keyword PARTITION is added to the full-reserved keyword list to support ADD/DROP PARTITION. Any chance to avoid that? PARTITION seems like something people might well use as a column or variable name. OTOH, it is reserved in SQL2008 and SQL2003. CREATE TABLE does not require PARTITION to be a reserved keyword, but there are conflicts in ALTER TABLE ADD/DROP PARTITION: * ALTER TABLE ... DROP [COLUMN] name [CASCADE | RESTRICT] * ALTER TABLE ... DROP PARTITION name [CASCADE | RESTRICT] There are some solutions: 1. Change COLUMN not to an optional word (unlikely) 2. Change syntax of DROP PARTITION to DROP TABLE PARITION or so 3. Change ALTER TABLE ADD/DROP PARTITION to top level = CREATE/DROP PARTITION name ON table_name Any better ideas? Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- 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] Syntax for partitioning
On Thu, Oct 29, 2009 at 9:51 PM, Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: The keyword PARTITION is added to the full-reserved keyword list to support ADD/DROP PARTITION. Any chance to avoid that? PARTITION seems like something people might well use as a column or variable name. OTOH, it is reserved in SQL2008 and SQL2003. CREATE TABLE does not require PARTITION to be a reserved keyword, but there are conflicts in ALTER TABLE ADD/DROP PARTITION: * ALTER TABLE ... DROP [COLUMN] name [CASCADE | RESTRICT] * ALTER TABLE ... DROP PARTITION name [CASCADE | RESTRICT] There are some solutions: 1. Change COLUMN not to an optional word (unlikely) 2. Change syntax of DROP PARTITION to DROP TABLE PARITION or so 3. Change ALTER TABLE ADD/DROP PARTITION to top level = CREATE/DROP PARTITION name ON table_name Any better ideas? I'm not sure if this is better, but what about: CREATE PARTITION name ON TABLE name DROP PARTITION name Since partitions will live in pg_class and are in some sense top level objects, it seems like it would make sense to use a syntax that is similar to the one we use for indices... we can't say DROP COLUMN name, because the table must be specified. But a partition name must be unambiguous, so making the user write it out explicitly doesn't seem friendly. ...Robert -- 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] Syntax for partitioning
Peter Eisentraut pete...@gmx.net wrote: On tor, 2009-10-29 at 11:15 +0900, Itagaki Takahiro wrote: Range partitioning: CREATE TABLE table_name ( columns ) PARTITION BY RANGE ( a_expr ) ( PARTITION name VALUES LESS THAN [(] const [)], PARTITION name VALUES LESS THAN [(] MAXVALUE [)] -- overflow partition ); Maybe this needs to mention the actual operator name instead of LESS THAN, in case the operator is not named or the user wants to use a different one. How about to use sortby or index_elem here? PARTITION BY RANGE '(' sortby-or-index_elem ')' '(' RangePartitions ')' sortby: a_expr USING qual_all_Op opt_nulls_order | a_expr opt_asc_desc opt_nulls_order index_elem: ColId opt_class opt_asc_desc opt_nulls_order | func_expr opt_class opt_asc_desc opt_nulls_order | '(' a_expr ')' opt_class opt_asc_desc opt_nulls_order We should allow only btree operator class here because we need to extract GREATER-THAN-OR-EQUAL operator from LESS THAN. In addition, we will be able to optimize parition search in the future if we restrict a range partition key should be comparable scalar value. Multidimensional partitioning will be implemented with another approach, something like PARTITION BY GIST, because it would require different oprimization from range partitioning. BTW, PARTITION BY pg_am.amname crossed my mind here, but it is not well-investigated yet. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- 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] Syntax for partitioning
On Thu, Oct 29, 2009 at 7:14 PM, Robert Haas robertmh...@gmail.com wrote: CREATE TABLE does not require PARTITION to be a reserved keyword, but there are conflicts in ALTER TABLE ADD/DROP PARTITION: * ALTER TABLE ... DROP [COLUMN] name [CASCADE | RESTRICT] * ALTER TABLE ... DROP PARTITION name [CASCADE | RESTRICT] There are some solutions: Do we need a DROP PARTITION command at all? What would it even do? Drop the partition from the parent table and throw it away in one step? I think in actual practice people usually remove the partition from the parent table first, then do things like archive it before actually throwing it away. -- greg -- 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] grant with hierarchy option
Peter Eisentraut pete...@gmx.net writes: There is a gap in the permission scheme for inheritance setups. Say you have this: CREATE TABLE persons (...); CREATE TABLE employees (...) INHERITS (persons); GRANT SELECT ON persons TO foo; Then user foo can extract who the employees are using SELECT * FROM persons EXCEPT SELECT * FROM ONLY persons; And this is a problem why exactly? It's entirely likely that employee-ness can be determined just from what is visible in the persons view, anyway. Not to mention tableoid. I think this would be the proper and useful thing to do, especially in conjunction with the new recursive grant behavior. There would probably be some upgrading issues. For example, GRANTs imported via pg_dump from 8.4 would probably need to change SELECT to SELECT WITH HIERARCHY OPTION, and even that technically wouldn't cover all cases. That sounds like this will break everything in sight, especially pre-existing dump files :-( 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] Syntax for partitioning
Greg Stark gsst...@mit.edu wrote: * ALTER TABLE ... DROP [COLUMN] name [CASCADE | RESTRICT] * ALTER TABLE ... DROP PARTITION name [CASCADE | RESTRICT] Do we need a DROP PARTITION command at all? What would it even do? Currently no. So, it would be good to treat PARTITION as just a synonym of TABLE. Not only DROP PARTITION but also ALTER PARTITION will work. CREATE PARTITION name ON table_name DROP PARTITION name ALTER PARTITION name ... We might need to specify partition keys with another syntax. ALTER TABLE will have only one new command PARTITION BY. and we reuse TABLE command for PARTITION in other operations. ALTER TABLE table_name PARTITION BY RANGE (expr) (...) Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers