Re: [GENERAL] Constraint exclusion and overlapping range checks
On Sep 7, 2013, at 6:54, Steve Atkins wrote: > On Sep 6, 2013, at 9:37 PM, François Beausoleil wrote: > >> Le 2013-09-07 à 00:29, Steve Atkins a écrit : >> >>> If I have a partitioned table that has some range constraints that look >>> kinda like they're intended for constraint exclusion, but aren't quite >>> non-overlapping, will that break anything? >>> >>> e.g. >>> >>> create table jan ( …, check(created >= '2013-01-01' and created < >>> '2013-02-01'), check(id >=0 and id < 1100) ) inherits(foo); >>> create table feb ( …, check(created >= '2013-02-01' and created < >>> '2013-03-01'), check(id >=100 and id < 2100) ) inherits(foo); >>> create table mar ( …, check(created >= '2013-03-01' and created < >>> '2013-04-01'), check(id >=200 and id < 3100) ) inherits(foo); >>> >>> Querying by created should be fine, and take advantage of constraint >>> exclusion, but will querying by id work? And if it does work, will it take >>> any advantage of those constraints at all, or just search all the child >>> partitions? >> >> I don't know, but I suspect a quick EXPLAIN ANALYZE will tell you, even with >> empty tables. > > Explain suggests it'll work fine, and make good use of the constraints to > prune partitions from the plan. But the docs are pretty specific about > overlapping range constraints being a bad thing so I'm wondering if there's > potential for problems. For values that are in the overlapping parts of the partition, the database will have to look in both table partitions to find a record that you're searching for instead of a single table partition. That partially defeats the purpose of using exclusion constraints. Next to that, putting data in the tables becomes ambiguous for records that match both constraints - in which table should the records go? That is something that you need to do programatically anyway, so with the knowledge of how to decide which records go where, you could also define your exclusion constraints to not be ambigous. I don't see any benefit of having ambiguous exclusion constraints - IMHO you're better off fixing them. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [pgeu-general] [GENERAL] Call for design: PostgreSQL mugs
On 07/09/2013 01:34, Berend Tober wrote: > Peter Geoghegan wrote: >> On Thu, Sep 5, 2013 at 8:22 AM, Merlin Moncure >> wrote: >>> I'm still partial to this guy: >>> >>> http://www.commandprompt.com/images/mammoth_versus_dolphin_500.jpg >> >> >> I dislike that image, and always have. ... > > I agree with Mr. Geoghegan. > > That image should be eradicated from the portfolio of PostgreSQL > professionals. +1 on this from me too. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Hash Support Function
Dear all I'm developing a new type, and want to have hash index on it. I must write a hash function for the new type, according to the PG manual, section 35.14.3. However, there is no example for this function. Actually signature of the hash support function would be sufficient for me. Any help appreciated, in advance Regards, Soroosh
Re: [pgeu-general] [GENERAL] Call for design: PostgreSQL mugs
On Sat, Sep 7, 2013 at 7:01 PM, Raymond O'Donnell wrote: > On 07/09/2013 01:34, Berend Tober wrote: >> Peter Geoghegan wrote: >>> On Thu, Sep 5, 2013 at 8:22 AM, Merlin Moncure >>> wrote: I'm still partial to this guy: http://www.commandprompt.com/images/mammoth_versus_dolphin_500.jpg >>> >>> >>> I dislike that image, and always have. ... >> >> I agree with Mr. Geoghegan. >> >> That image should be eradicated from the portfolio of PostgreSQL >> professionals. > > +1 on this from me too. Makes sense to remove it. Postgres should be promoted using only its strengths, and not comparisons with any other projects or products. -- Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Levenshtein Distance with more than 255 characters
On Sat, Sep 7, 2013 at 6:28 AM, Janek Sendrowski wrote: > Do you know the destination. I cant find it. Here it is: $ find . -name "*.[c|h]" | xgrep MAX_LEVENSHTEIN_STRLEN ./contrib/fuzzystrmatch/levenshtein.c:#define MAX_LEVENSHTEIN_STRLEN255 ./contrib/fuzzystrmatch/levenshtein.c:if (m > MAX_LEVENSHTEIN_STRLEN || ./contrib/fuzzystrmatch/levenshtein.c:n > MAX_LEVENSHTEIN_STRLEN) ./contrib/fuzzystrmatch/levenshtein.c: MAX_LEVENSHTEIN_STRLEN))); -- Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Constraint exclusion and overlapping range checks
Alban Hertroys writes: > On Sep 7, 2013, at 6:54, Steve Atkins wrote: >>> If I have a partitioned table that has some range constraints that look >>> kinda like they're intended for constraint exclusion, but aren't quite >>> non-overlapping, will that break anything? > Next to that, putting data in the tables becomes ambiguous for records that > match both constraints - in which table should the records go? That is > something that you need to do programatically anyway, so with the knowledge > of how to decide which records go where, you could also define your exclusion > constraints to not be ambigous. > I don't see any benefit of having ambiguous exclusion constraints - IMHO > you're better off fixing them. I agree with that advice in principle; but if the true partitioning constraint is too complicated, you might defeat the planner's ability to prove that particular tables don't need to be scanned as a consequence of a particular WHERE clause. The simple range constraints Steve showed should work fine with constraint exclusion. The proofs are done separately for each sub-table, so the fact that the ranges overlap doesn't bother the planner. We might in future have a more efficient partitioning method that does assume non-overlapping ranges ... but it's not there today. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hash Support Function
Soroosh Sardari writes: > I'm developing a new type, and want to have hash index on it. > I must write a hash function for the new type, according to the PG manual, > section 35.14.3. > However, there is no example for this function. Actually signature of the > hash support function > would be sufficient for me. hashfunc(yourtype) returns integer strict immutable For examples see hashint4, hashfloat8, hashtext, and many others. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is this a bug in ECPG?
2013-09-06 09:57 keltezéssel, Boszormenyi Zoltan írta: 2013-09-06 02:57 keltezéssel, Wang, Jing írta: Hi, ECPG don't support the following scenario: char cur_name = "cur1"; EXEC SQL DECLARE :cur_name CURSOR FOR ... EXEC SQL OPEN :cur_name; EXEC SQL FETCH cur1 INTO... ... EXEC SQL CLOSE :cur_name; An compile error will occur for above codes because ECPG can't find the "cur1". Of course it cannot find it, ECPG doesn't interpret the content of the variable, so it cannot know it is "cur1". But it does support the following scenario: char cur_name = "cur1"; EXEC SQL DECLARE :cur_name CURSOR FOR ... EXEC SQL OPEN :cur_name; EXEC SQL FETCH :cur_name INTO... ... EXEC SQL CLOSE cur1; Does the second scenario is a bug for ECPG? Actually, it looks like it is a bug. BTW, my "ECPG cursor readahead" patch unintentionally fixed this. https://commitfest.postgresql.org/action/patch_view?id=1195 Will anyone use the second scenario to close an CURSOR that is named by a variable? Not very likely. Kind regards, Jing -- -- Zoltán Böszörményi Cybertec Schönig & Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web:http://www.postgresql-support.de http://www.postgresql.at/ -- -- Zoltán Böszörményi Cybertec Schönig & Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de http://www.postgresql.at/
Re: [GENERAL] Constraint exclusion and overlapping range checks
On Sep 7, 2013, at 7:20 AM, Tom Lane wrote: > Alban Hertroys writes: >> On Sep 7, 2013, at 6:54, Steve Atkins wrote: If I have a partitioned table that has some range constraints that look kinda like they're intended for constraint exclusion, but aren't quite non-overlapping, will that break anything? > >> Next to that, putting data in the tables becomes ambiguous for records that >> match both constraints - in which table should the records go? That is >> something that you need to do programatically anyway, so with the knowledge >> of how to decide which records go where, you could also define your >> exclusion constraints to not be ambigous. > >> I don't see any benefit of having ambiguous exclusion constraints - IMHO >> you're better off fixing them. If the check constraints are there for reasons other than partitioning and exclusion there isn't anything to fix. But if the constraint exclusion code can use those overlapping range constraints to reduce queries down to one or two partitions that seems like a fairly useful benefit. > > I agree with that advice in principle; but if the true partitioning > constraint is too complicated, you might defeat the planner's ability to > prove that particular tables don't need to be scanned as a consequence of > a particular WHERE clause. The simple range constraints Steve showed > should work fine with constraint exclusion. The proofs are done > separately for each sub-table, so the fact that the ranges overlap doesn't > bother the planner. We might in future have a more efficient partitioning > method that does assume non-overlapping ranges ... but it's not there > today. Constraint exclusion is a global setting, so I'm mostly concerned about the planner mistaking range constraints that are there for other reasons breaking queries. That it doesn't - and can even usefully use those range constraints to optimize queries - isn't really surprising, but is reassuring. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
