Re: Pet Peeves

> On Saturday 31 January 2009 8:47:28 pm Adam Rich wrote:
> > On Thu, 29 Jan 2009 13:16:17 +0000
> >
> > Gregory Stark <stark(at)enterprisedb(dot)com> wrote:
> > > So, what do people say? Is Postgres perfect in your world or does
> it
> > > do some things which rub you the wrong way?
> >
> > I see all the major ones have already been mentioned, so here's some
> > minor ones.
> >
> > - lack of system-level and DDL triggers
> > - inability to limit triggers to certain columns
> > - inability to know the DML operation causing a trigger
> From:
> http://www.postgresql.org/docs/8.3/interactive/plpgsql-trigger.html
> TG_OP
>
> Data type text; a string of INSERT, UPDATE, or DELETE telling for
> which
> operation the trigger was fired.
>
> This is also available in plpythonu, I don't know about the other PL's.
>

Thanks, I knew this was available for python & perl PLs, I wasn't aware
it was I plpgsql too. Still, it would be nice to have something akin to
oracle's IF(UPDATING('col_name')) THEN

> > - date_part/extract returning floats instead of integer
> Maybe this what you are looking for ?:
> http://www.postgresql.org/docs/8.3/interactive/datatype-datetime.html
> Note: When timestamp values are stored as double precision floating-
> point
> numbers (currently the default), the effective limit of precision might
> be less
> than 6. timestamp values are stored as seconds before or after midnight
> 2000-01-01. Microsecond precision is achieved for dates within a few
> years of
> 2000-01-01, but the precision degrades for dates further away. When
> timestamp
> values are stored as eight-byte integers (a compile-time option),
> microsecond
> precision is available over the full range of values. However eight-
> byte
> integer timestamps have a more limited range of dates than shown above:
> from
> 4713 BC up to 294276 AD. The same compile-time option also determines
> whether
> time and interval values are stored as floating-point or eight-byte
> integers.
> In the floating-point case, large interval values degrade in precision
> as the
> size of the interval increases.
>

Nope, I mean if you use date_part to extract a piece of a date, you
get a float instead of an integer. It trips me up everytime I try
something like this:

select * from table
where (weekmask & (1 << date_part('DOW', $1))) <> 0

To my surprise, the << operator fails because it requires an integer
argument, but date_part provides only a double floating point.

I realize this is documented as intended behavior, but why? Is there
any scenario where DOW (or day, year, hour, or *any* field really)
would be returning a fractional number?

> > - parts of the SQL statement (e.g. 'for update of') requiring table
> > aliases when present instead of table names.
> > - lack of queryable high-water marks useful for tuning
> > - lack of an auto-tuner, for that matter.
> > - inability to log (e.g. long-running queries) to a table
> > - lack of custom session-level variables (without editing
> postgresql.conf)
> > - lack of autonomous transactions
>
>
>
> --
> Adrian Klaver
> aklaver(at)comcast(dot)net