Multi calendar system for pgsql

Mohsen Alimomeni <m.alimomeni@gmail.com> writes:

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?

There's probably about zero chance of accepting such a thing into core,
but maybe you could do it as an add-on (pgfoundry project).

For implementation I think it is better to add this support in date fields
like this:
create table tb (dt date calendar persian);

Consider specifying the calendar as a typmod, eg

create table tb (dt cdate(persian));

since the necessary extension hooks already exist for that.

regards, tom lane

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);

--
Fabien.

From pgsql-hackers-owner@postgresql.org Wed Feb 18 13:08:59 2009

Received: from localhost (unknown [200.46.204.183])
by mail.postgresql.org (Postfix) with ESMTP id 8C7A9633167
for <pgsql-hackers-postgresql.org@mail.postgresql.org>; Wed, 18 Feb 2009 13:08:59 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
with ESMTP id 10027-02
for <pgsql-hackers-postgresql.org@mail.postgresql.org>;
Wed, 18 Feb 2009 13:08:52 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from el-out-1112.google.com (el-out-1112.google.com [209.85.162.177])
by mail.postgresql.org (Postfix) with ESMTP id 7E236632798
for <pgsql-hackers@postgresql.org>; Wed, 18 Feb 2009 13:08:51 -0400 (AST)
Received: by el-out-1112.google.com with SMTP id y26so2447045ele.10
for <pgsql-hackers@postgresql.org>; Wed, 18 Feb 2009 09:08:48 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
d=gmail.com; s=gamma;
h=domainkey-signature:mime-version:received:in-reply-to:references
:date:message-id:subject:from:to:cc:content-type
:content-transfer-encoding;
bh=M6ovqROM3bgWiQUb2+8NA5s0kIWwFgY0TW6lFaRjoNo=;
b=tosOrCdTtBZoiWqkxWMxbn/ed2oH30+KL08+/3yC/HWHiSDUh5B+3kjRzjGdBUyIta
S6Ng48qdfi4wXjxGdmPyAAGPnPABOChVleH5kPwKEpuPM0tKQ2FqoDmmjSkrOHT7xeqy
qwmsBwlveYZOxk0ErXBvuXL3MaZuNKXoOH7sI=
DomainKey-Signature: a=rsa-sha1; c=nofws;
d=gmail.com; s=gamma;
h=mime-version:in-reply-to:references:date:message-id:subject:from:to
:cc:content-type:content-transfer-encoding;
b=fE2cVmEm7eLroleFI0NXM7YxbLuJk1+FLASMpTT23ybk6W4jWxqzdvba1HMG3DqdUC
Vuj4JRHLMJDoSRMWELAXy8cVXd1jx3EJB2q2GllYbFT3TIjpjXThVds9LLuoFKb2v39V
8bb5DQOL6H2rfslaOEYygZB+NNA2Isa2OkQm0=
MIME-Version: 1.0
Received: by 10.150.149.19 with SMTP id w19mr40594ybd.201.1234976928589; Wed,
18 Feb 2009 09:08:48 -0800 (PST)
In-Reply-To: <8393.1234975601@sss.pgh.pa.us>
References: <1234483591.9467.188.camel@jd-laptop.pragmaticzealot.org>
<603c8f070902131320n47904f8cr2f95a5f01e496e85@mail.gmail.com>
<Pine.GSO.4.64.0902151238070.1312@westnet.com>
<603c8f070902151954v42cce4d0qf68dbec090a64357@mail.gmail.com>
<00BD3A7D-CBEC-4B26-A23E-A0C83B8EB63D@decibel.org>
<603c8f070902172123m3c424e9bxcf46ad4e4b380bf0@mail.gmail.com>
<807.1234938865@sss.pgh.pa.us>
<20090218151344.GQ32672@frubble.xen.chris-lamb.co.uk>
<603c8f070902180757m15b0bd23ib7256cc8ef44447e@mail.gmail.com>
<8393.1234975601@sss.pgh.pa.us>
Date: Wed, 18 Feb 2009 12:08:48 -0500
Message-ID: <603c8f070902180908j3ae46774g535d96ece2c90e74@mail.gmail.com>
Subject: Re: The science of optimization in practical terms?
From: Robert Haas <robertmhaas@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Sam Mason <sam@samason.me.uk>, pgsql-hackers@postgresql.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-Spam-Level:
X-Archive-Number: 200902/961
X-Sequence-Number: 134251

On Wed, Feb 18, 2009 at 11:46 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.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
below.

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?

...Robert

On Wed, Feb 18, 2009 at 07:50:31PM +0330, Mohsen Alimomeni wrote:

Multi calendar systems are useful for several languages and countries using
different calendar: Hijri, Persian, Hebrew, etc.

When would the differences between these calenders actually show
up? I can only think of it affecting input/output routines and the
date_part,date_trunc,to_date and to_char routines. But am I missing
something?

If that's all, then how about just treating the current PG
date types as Julian days (as far as I know, that's how it's
treated internally anyway) and providing a multi-calender set of
date_part,date_trunc,to_date and to_char routines. I.e. leave out the
input/output routines.

Doing this would be much easier, but less fun, than creating whole new
types and having to modify the parser as well to recognize the new
syntax.

--
Sam http://samason.me.uk/

m.alimomeni@gmail.com (Mohsen Alimomeni) writes:

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?

I would expect there to be nearly zero chance of such, at least in the
form of a change to how dates are stored.

As long as there is commonality in epochs and some continuity of
calculations of dates relative to epochs, there shouldn't be any
fundamental problem in adding on functions to do the following sorts
of things:

- Calculate what the UNIX date is for a given date in a given
calendar

- Output a UNIX date in the form indicated by a given calendar

You should avail yourself of the book, _Calendrical Calculations_, by
Edward M Reingold and Nachum Deerschowitz; it presents details of
calculations and conversions of dates between the following calendars:

- Gregorian
- Julian
- Coptic
- Ethiopic
- ISO
- Islamic
- Hebrew
- Ecclesiastical Calendars, for dates of Christian holidays such as Easter
- Old Hindu
- Modern Hindu
- Mayan
- Balinese Pawukon
- Persian
- Baha'i
- French Revolution
- Chinese

It would seem a whole lot preferable to create functions like (and
there may be better names!):

create function parse_date (locale, text) returns timestamp
create function output_date (local, timestamp) returns text

Thus, you might expect the following:

select parse_date('Islamic', 'Miharram 1, AH 1');

parse_date
-------------------------
622-07-16 00:00:00

Or

select output_date('Persian', '622-03-19'::timestamp);

output_date
-------------------------
1 Farvardin AH 1

(It is entirely likely that I'm fracturing spellings of things!
Apologies if I am!)

http://emr.cs.uiuc.edu/home/reingold/calendar-book/index.shtml
--
(reverse (concatenate 'string "moc.enworbbc" "@" "enworbbc"))
http://cbbrowne.com/info/x.html
"Thank you for calling PIXAR! If you have a touch tone phone, you can
get information or reach anybody here easily! If your VCR at home is
still blinking '12:00', press '0' at any time during this message and
an operator will assist you."
-- PIXAR'S toll-free line (1-800-888-9856)

Hi,
To implement my local calendar, I tried adding a new type (pdate) to pgsql
as an extension. At first I used a struct of size 6, and I returned a
pointer to it in pdate_in with no problem. Now I changed the type to int32,
returning PG_RETURN_INT32. I removed all palloc calls. but the server
crashes with segmentation fault before returning in pdate_in.
I changed PG_RETURN_INT32 to PG_RETURN_POINTER, but the problem isn'tsolved.
I checked everything, read chapter 34 of docs, but I couldn't solve the
problem. I attached my files, if anyone can take a look at them.

thanks,
Mohsen Alimomeni

Mohsen Alimomeni <m.alimomeni@gmail.com> writes:

Hi,
To implement my local calendar, I tried adding a new type (pdate) to pgsql
as an extension. At first I used a struct of size 6, and I returned a
pointer to it in pdate_in with no problem. Now I changed the type to int32,
returning PG_RETURN_INT32. I removed all palloc calls. but the server
crashes with segmentation fault before returning in pdate_in.

You want to set PASSEDBYVALUE

(And you probably want to adjust alignment though I don't think it's causing
any problem aside from wasted space)

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!