Re: [GENERAL] to_timestamp() and timestamp without time zone
I meant that time-calculations themselves have lots of issues and subtleties. Fair enough, and I agree there is no magic API to solve the difficulties of adapting rational, logic based systems to a Calendar system last edited by the Pope and based upon the imperfect movement of Sol relative to Earth. But we've already detailed why this specific case could use some more attention. Calculations for long prior dates/times have things like a few minute jump when (at least in the US) an interval crosses Sunday, November 18, 1883 (the day of two noons). And although October 1582 (Catholic regions) or September 1752 (Protestant regions/Unix-assumption) or later (Orthodox) are missing 10-days, PostgreSQL follows the SQL standard which does not show those dates as missing at all. This really falls into application knowledge since the vast majority of use-cases use fairly recent dates. Those who deal with long-ago dates should be expected to understand the limitations of their reality and would devise means to accommodate them. Likewise, from the omitted next paragraph, those who are relying on time need to take into consideration that changes happen. The effort to deal with that change is then trade-off against the cost of the failure occurring. In some/most cases, over a short timeframe, the proper solution is to be flexible and/or relative. Examples: Scheduler: Run the schedule the first chance you get when the system hour/minute is after/greater than the schedule hour/minute. You may or may not need to ensure that schedule hour/minute entries are sorted so those with a longer delay are completed first - just as they would be in normal circumstances. Hospital: Dispense the next dose 5 hours from now (about the broadest unit you can safely use is DAY). In this case the software should be able to count forward minute-by-minute, using the TimeZone rules to skip around if necessary, and determine whether 5 hours from 0:30 is 4:30, 5:30, or 6:30. The API implements this counting via the addition operator. In theory adding months/years should be forbidden and a procedure that applies a consistent rule set should be used instead. Some standard ones can be provided and the user can always create their own. One possible rule would be that adding or subtracting months to a date that is the last day of its month always returns the last of the resultant month. Another rule/function could implement the current behavior where the day does not change (and then you have two variations - if the new date is invalid you either fail or coerce). While the discussion or core vs. extension comes up consider that many users and evaluators are going to look at the core first and, as I've said before, if they see something that appears like it will work they will just use it. So you'd either want to have no (or very minimal) time-oriented API or have it be full-featured (and also have a save me from myself quality to it - given time's complexities). All this said, I am getting worked up by this particular thread but, in reality, the status-quo does not truly harm me that I know of - but my usage of PostgreSQL is very light/flexible (lots of spare capacity). I'm coming at this both from a desire to learn more and also what would I do if I was starting from scratch?. The best approach, since we are not starting from scratch, would be for interested and capable parties to work on a full-blown time extension that, while maybe less user friendly, is safer to use and much more explicit. However, there are likely to be some components in such an extension that would be forward-only and thus could be introduced to the core API without any difficulty (a to_timestamp_abstract(text, text)-timestamp function comes to mind - note the name change; see other recent post for reason). And since interested and capable are not mutually inclusive those who are interested but not capable would probably appreciate more than just here is a workaround from the community. At the same time, interested parties need to put together a precise and coherent proposal that can be discussed and acted upon - with a clear (even if possibly incorrect) assertion about why something is either wrong or difficult to use. There are different definitions of when a year starts so be sure not to grab the wrong week-number or day-number - ISO and Julian are not the same. Agreed; but people who are going to choose a calendar other than the Gregorian Calendar should be expected to learn and abide by the rules of that Calendar. The responsibility of the API is to correctly apply those rules (and help the user abide by them where possible). And, of course, everything starts with the ethnocentric assumption of what calendar system to use. From my experience, there is not a lot of good SQL support for data using Islamic, Chinese, Hebrew, Hindu, Iranian, Coptic or Ethiopian calendars. Supply and Demand. Iran is thinking about creating
Re: [GENERAL] to_timestamp() and timestamp without time zone
First: I would suggest your use of Local Time is incorrect and that you
would be better off thinking of it as Abstract Time. My responses below
go into more detail but in short you obtain a Local time by Localizing
and Abstract time. The process of Localization requires a relevant
Locale input which, for date/time values, is a TimeZone. Since you
define your Local Time as being Without Timezone this is an
inconsistency and so, because we want to define something without a TimeZone
we need to rename Local Time to Abstract Time. And, no, Wall Time
will not work either since a Wall exists Somewhere and thus is
Localized.
You say: (I am applying the above directly to your two definitions)
1) TimestampTZ is an INSTANT - but what exactly is an Instant? In
this case we have a Wall Calendar and a Wall Clock as a means of
describing an instant. However, that Wall has to be Somewhere and, in
combination, the calendar and clock have to display real and valid values
according to that physical location. So Instant, by definition, means
Local, which requires a TimeZone. So TimeStampTZ DOES imply a TimeZone
via a definition of INSTANT.
2) Timestamp is a[n] [ABSTRACT]DATETIME (reworded to remove the
prefix LOCAL which, from above, I feel is misleading). That this does not
use TimeZone is correct.
On the other hand, TIMESTAMP WITHOUT TIMEZONE is a wholy different concept
(neither 'wider' or narrow' type than the other). It's just the local
calendar time, it's (conceptually) like a tuple of
numbers{year,month,day,hour,min,sec, fracsecs}, the date and time as it's
understood in the business-civil world, with the timezone information
missing.
This is the type that should be used for that concept, when it's critical
for me that 'If I stored 2011,09,11 at 23:59:59, I want to get that
precise calendar date.
Your definition of calendar time is incomplete (though I do get your
point). The date component is local because you have (implicitly)
specified that you are using a Gregorian Calendar-like rule set. However,
by omitting the Location Time Rules (TimeZone) you are in fact creating an
Abstract Time and not anything that is guaranteed to be valid (meaningful)
when Localized. As soon as you say local you must tell the computer
what local means by specifying a TimeZone. Otherwise you simply have an
Abstract Time based on the (I think) Babylonian system.
Docs should make clear this, and the fact that Postgresql currently lacks a
FULL datetime type. This would correspond conceptually to the tuple
{INSTANT,TIMEZONE} (which implies univocally also a LOCALDATETIME). Nor does
it implemented the SQL standard TIMESTAMP WITH TIMEZONE (actually a
{INSTANT,OFFSET} tuple). I'm ok with that, I agree that the later is
crippled, and the former is complex and not standard, and that both have
little support from upper layers.
Fair enough; but in reality, other than the 25 hour day issue the chosen
implementation is quite useful. Once you have created a valid instance of a
timestamptz you can change it to any TimeZone, using the proper rules, and
are guaranteed to still have a valid value. So you might as well normalize
the storage TimeZone as PostgreSQL does. My only qualm is coercing the
input so that a valid timestamptz is always created. But even that isn't
a big deal if you indeed want to ensure that the value entered is a valid
timestampz.
In this case you are using a function that returns a timestamptz while you
are working with timestamp. You just said that they are completely
different so the fact that this fails should be of no surprise. That a
suitable function, that returns a timestamp, does not exist is the only
real complaint. It has already been shown that the described behavior of a
PostgreSQL timestamp is consistent with what you describe it should be.
That it can be auto-casted to a timestamptz is a debatable point. But
this again comes simply back to the decision to coerce the input of
timestamptz. That is, in the vast majority of cases where the conversion
makes sense the ability to directly cast is great. Casting is always
necessary IF you want to convert your Abstract Time (i.e., timestamp) into
a Local Time (i.e., timestamptz).
I'm ignoring the concept of OFFSET intentionally as that is likely to
confuse the issue and I haven't had time to fully contemplate that aspect of
things.
David J.
Note: I am writing this post and a response to Steve at the same time (no
pun intended.)
Re: [GENERAL] glitch installing xml support in 9.1.beta2
On Fri, 2011-06-24 at 11:22 -0600, Rob Sargent wrote: OpenSuse 11.4 x86-64 gmake install builds and places the requisite pieces as expected. Running psql --username postgres -d postgres -f xml2--1.0,sql results in psql:xml2--1.0.sql:8: ERROR: function xml_valid already exists with same argument types psql:xml2--1.0.sql:12: ERROR: could not access file MODULE_PATHNAME: No such file or directory psql:xml2--1.0.sql:16: ERROR: could not access file MODULE_PATHNAME: No such file or directory psql:xml2--1.0.sql:20: ERROR: could not access file MODULE_PATHNAME: No such file or directory psql:xml2--1.0.sql:24: ERROR: could not access file MODULE_PATHNAME: No such file or directory psql:xml2--1.0.sql:28: ERROR: could not access file MODULE_PATHNAME: No such file or directory psql:xml2--1.0.sql:34: ERROR: could not access file MODULE_PATHNAME: No such file or directory (This from second run, so xml_valid worked the first time). I see that the xml2.control file defines module_pathname = '$libdir/pgxml', but how is that passed to the psql run? Or am I to manually update the sql scripts? . You don't use it explicitely. You use CREATE EXTENSION, and it will use it. psql --username postgres -d postgres -c CREATE EXTENSION xml2 and it should work. That's one of the great things in 9.1. Here's my configuration of the main build ./configure --with-libraries=/usr/local/lib --with-python --with-openssl --with-ldap --with-ossp-uuid --with-libxml --with-libxslt --enable-debug --prefix=/opt/PostgreSQL/9.1 Haven't gone into uuid stuff yet... Cheers, -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- 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] glitch installing xml support in 9.1.beta2
Guillaume Lelarge wrote: On Fri, 2011-06-24 at 11:22 -0600, Rob Sargent wrote: OpenSuse 11.4 x86-64 gmake install builds and places the requisite pieces as expected. Running psql --username postgres -d postgres -f xml2--1.0,sql results in psql:xml2--1.0.sql:8: ERROR: function xml_valid already exists with same argument types psql:xml2--1.0.sql:12: ERROR: could not access file MODULE_PATHNAME: No such file or directory psql:xml2--1.0.sql:16: ERROR: could not access file MODULE_PATHNAME: No such file or directory psql:xml2--1.0.sql:20: ERROR: could not access file MODULE_PATHNAME: No such file or directory psql:xml2--1.0.sql:24: ERROR: could not access file MODULE_PATHNAME: No such file or directory psql:xml2--1.0.sql:28: ERROR: could not access file MODULE_PATHNAME: No such file or directory psql:xml2--1.0.sql:34: ERROR: could not access file MODULE_PATHNAME: No such file or directory (This from second run, so xml_valid worked the first time). I see that the xml2.control file defines module_pathname = '$libdir/pgxml', but how is that passed to the psql run? Or am I to manually update the sql scripts? . You don't use it explicitely. You use CREATE EXTENSION, and it will use it. psql --username postgres -d postgres -c CREATE EXTENSION xml2 and it should work. That's one of the great things in 9.1. Very cool. Obviously I missed a release note or two. Thanks -- 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] blks_read/blks_hit stats
On 11-06-15 12:38 PM, Nigel Heron wrote: Hi everyone, I'm playing with the stats views and functions to graph them in cacti.. Adding up *_blks_hit (heap, idx, toast and tidx) from pg_statio doesn't match blks_hit in pg_stat_database. Sometimes the sum is higher, sometimes lower. Do they have similar names but represent different metrics? Same issue with blks_read. eg. SELECT SUM(pg_statio_all_tables.heap_blks_hit)::bigint + SUM(pg_statio_all_tables.idx_blks_hit)::bigint + SUM(pg_statio_all_tables.toast_blks_hit)::bigint + SUM(pg_statio_all_tables.tidx_blks_hit)::bigint AS blks_hit FROM pg_statio_all_tables; blks_hit 1275299563 (1 row) SELECT blks_hit FROM pg_stat_database where datname='mydb'; blks_hit --- 674295210 (1 row) here's 2 graphs from different databases on the same cluster (8.4.2). first 4 stacked graph items are from pg_statio_all_tables and the red line is from pg_stat_database. blks_hit is way under the sum: http://www.psycode.com/gallery/d/88438-1/blks_read1.png blks_hit seems pretty close to the sum of table+idx (but no toast): http://www.psycode.com/gallery/d/88440-1/blks_read2.png I understand now.. querying pg_statio_all_tables includes stats from the cluster wide objects (pg_database, etc.), those stats increase from queries on other databases as well, hence the gap between the sum and blks_hit from pg_stat_database. I think changing the 1st query to this should do the trick.. SELECT SUM(pg_statio_all_tables.heap_blks_hit)::bigint + SUM(pg_statio_all_tables.idx_blks_hit)::bigint + SUM(pg_statio_all_tables.toast_blks_hit)::bigint + SUM(pg_statio_all_tables.tidx_blks_hit)::bigint AS blks_hit FROM pg_statio_all_tables WHERE relid NOT IN ( SELECT rel.oid FROM pg_class rel INNER JOIN pg_tablespace spc ON (rel.reltablespace=spc.oid) WHERE spc.spcname='pg_global' ) i'll put the cluster wide object stats on another graph -nigel. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] An amusing MySQL weakness--not!
Earlier today I was working on a MySQL database (not by choice, I assure you), and I typed a statement like this: Update tablexyz set field1 = '15' where field2 - 20; The '-' was supposed to be an equal sign, but MySQL executed it anyway. (Field2 is an integer.) I was not amused. PostgreSQL reports this as an error, of course. -- Mike Nolan no...@tssi.com
Re: [GENERAL] An amusing MySQL weakness--not!
Michael == Michael Nolan htf...@gmail.com writes: Michael Earlier today I was working on a MySQL database (not by choice, I assure Michael you), Friends don't let friends use MySQL. -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 mer...@stonehenge.com URL:http://www.stonehenge.com/merlyn/ Smalltalk/Perl/Unix consulting, Technical writing, Comedy, etc. etc. See http://methodsandmessages.posterous.com/ for Smalltalk discussion -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
