Skip site navigation (1) Skip section navigation (2)

Re: Multi calendar system for pgsql

From: Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>
To: Mohsen Alimomeni <m(dot)alimomeni(at)gmail(dot)com>
Cc: PostgreSQL Developers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Multi calendar system for pgsql
Date: 2009-02-18 16:53:46
Message-ID: alpine.DEB.2.00.0902181739150.6441@localhost.localdomain (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
> I want to try to add a multi calendar system for pgsql. I want to know if it will be accepted as a patch to pgsql?
> More details:
> Multi calendar systems are useful for several languages and countries using different calendar: Hijri, Persian, Hebrew,
> etc.
> For implementation I think it is better to add this support in date fields like this: 
> create table tb (dt date calendar persian);
> if no calendar is defined, it will be Gregorian so no problem is made for old sql commands.

I don't think that new keywords should be added for that if it does not 
belong to the SQL standard, especially with something as open ended and 
sensitive as a cultural related keyword: there are dozens calendars listed 
on wikipedia...

ISTM that this is either a localization problem, possibly fully 
independent from pg, or a conversion issue with a simple function which 
may be develop as an extension outside pg, say:

 	SELECT PersianDate('2008-02-18'::DATE);

>From pgsql-hackers-owner(at)postgresql(dot)org  Wed Feb 18 13:08:59 2009
Received: from localhost (unknown [])
	by (Postfix) with ESMTP id 8C7A9633167
	for <pgsql-hackers-postgresql(dot)org(at)mail(dot)postgresql(dot)org>; Wed, 18 Feb 2009 13:08:59 -0400 (AST)
Received: from ([])
 by localhost ( []) (amavisd-maia, port 10024)
 with ESMTP id 10027-02
 for <pgsql-hackers-postgresql(dot)org(at)mail(dot)postgresql(dot)org>;
 Wed, 18 Feb 2009 13:08:52 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from ( [])
	by (Postfix) with ESMTP id 7E236632798
	for <pgsql-hackers(at)postgresql(dot)org>; Wed, 18 Feb 2009 13:08:51 -0400 (AST)
Received: by with SMTP id y26so2447045ele.10
        for <pgsql-hackers(at)postgresql(dot)org>; Wed, 18 Feb 2009 09:08:48 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;; s=gamma;
DomainKey-Signature: a=rsa-sha1; c=nofws;; s=gamma;
MIME-Version: 1.0
Received: by with SMTP id w19mr40594ybd.201.1234976928589; Wed, 
	18 Feb 2009 09:08:48 -0800 (PST)
In-Reply-To: <8393(dot)1234975601(at)sss(dot)pgh(dot)pa(dot)us>
References: <1234483591(dot)9467(dot)188(dot)camel(at)jd-laptop(dot)pragmaticzealot(dot)org>
Date: Wed, 18 Feb 2009 12:08:48 -0500
Message-ID: <603c8f070902180908j3ae46774g535d96ece2c90e74(at)mail(dot)gmail(dot)com>
Subject: Re: The science of optimization in practical terms?
From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Sam Mason <sam(at)samason(dot)me(dot)uk>, pgsql-hackers(at)postgresql(dot)org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Archive-Number: 200902/961
X-Sequence-Number: 134251

On Wed, Feb 18, 2009 at 11:46 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> Yeah, I thought about this too, but it seems like overkill for the
>> problem at hand, and as you say it's not clear you'd get any benefit
>> out of the upper bound anyway.  I was thinking of something simpler:
>> instead of directly multiplying 0.005 into the selectivity every time
>> you find something incomprehensible, keep a count of the number of
>> incomprehensible things you saw and at the end multiply by 0.005/N.
>> That way more unknown quals look more restrictive than fewer, but
>> things only get linearly wacky instead of exponentially wacky.
> clauselist_selectivity could perhaps apply such a heuristic, although
> I'm not sure how it could recognize "default" estimates from the various
> specific estimators, since they're mostly all different.

Presumably the estimators would need to be modified to provide some
information on their level of confidence in their estimate (possibly
this could be more general than whether the number is a default or
not, though I'm not sure what we'd do with that information).  But it
may not be necessary to go through that pain if we implement your idea

> Personally I've not seen all that many practical cases where the
> estimator simply hasn't got a clue at all.  What's far more commonly
> complained of IME is failure to handle *correlated* conditions in
> an accurate fashion.  Maybe we should just discount the product
> selectivity all the time, not only when we think the components are
> default estimates.

That has something going for it, although off the top of my head I'm
not sure exactly what formula would make sense.  Presumably we want
the overall selectivity estimate to be less than the estimate for
individual clause taken individually, but how much less?  It doesn't
seem right to estimate the selectivity of S_1...S_n as MIN(S_1 ...
S_n) / n, because that will give you weird results with things like a
= 1 AND a != 2.  You might need to divide the estimates into two
buckets: those that reduce selectivity by a lot, and those that reduce
it only slightly, then multiply the latter bucket and, say, divide
through by the cardinality of the former bucket.  But the exact
details of the math are not obvious to me.

I'm talking off the top of my head here, maybe you have a more clear
thought as to how this would work?


In response to


pgsql-hackers by date

Next:From: Bruce MomjianDate: 2009-02-18 17:12:20
Subject: Re: pg_migrator progress
Previous:From: Tom LaneDate: 2009-02-18 16:46:41
Subject: Re: The science of optimization in practical terms?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group