Thread: BUG #6391: insert does not insert correct value

The following bug has been logged on the website:

Bug reference:      6391
Logged by:          John
Email address:      john.udick@zionsbancorp.com
PostgreSQL version: 8.4.1
Operating system:   CentOS
Description:=20=20=20=20=20=20=20=20

I would expect that at the time/date of the of now() and clock_timestamp()
to be equal; which they are. However for the insert this is not true.

select localtimestamp::date, now()::date, clock_timestamp()::date

"2012-01-10";"2012-01-10";"2012-01-10"

multiple row insert:
insert into v002235.array_stg values (1, date_trunc('hours',now()),
current_date, array['101','113'], 'Y')
                                   , (2, localtimestamp::date, current_date
, array['101','113'], 'Y')
                                   , (3, now(), clock_timestamp()::DATE,
array['101','113'], 'Y')


Results
1;"1999-12-31";"1999-12-31";"{101,113}";"Y"
2;"1999-12-31";"1999-12-31";"{101,113}";"Y"
3;"1999-12-31";"2012-01-10";"{101,113}";"Y"

Why is this not the case? I would expect that my records should all have the
same value, not some arbitrary date from the past.

<john.udick@zionsbancorp.com> wrote:

> PostgreSQL version: 8.4.1

You are missing years of bug fixes.  You should be staying more
up-to-date on minor releases.

http://www.postgresql.org/support/versioning/

To review what has been fixed between 8.4.1 and 8.4.10, please look
through the links from 8.4.2 and up on this page and see if any of
them seem to be related:

http://www.postgresql.org/docs/8.4/static/release.html

Or just apply all of those bug fixes and try again.

> I would expect that at the time/date of the of now() and
> clock_timestamp() to be equal;

You would be wrong.  The fine manual describes them here:

http://www.postgresql.org/docs/current/interactive/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

Picking sentences out of that page:

| now() is a traditional PostgreSQL equivalent to
| transaction_timestamp().

| transaction_timestamp() is equivalent to CURRENT_TIMESTAMP, but is
| named to clearly reflect what it returns.

| clock_timestamp() returns the actual current time, and therefore
| its value changes even within a single SQL command.

now() returns the timestamp for the start of the transaction, while
clock_timestamp() grabs a new value from the system clock for every
execution.

None of that, however, seems to be very directly related to the
issue you describe below.

> [results obtained in some unknown fashion from a table of unknown
> description doesn't have expected dates]

> Why is this not the case? I would expect that my records should
> all have the same value, not some arbitrary date from the past.

You haven't provided near enough information for anyone to hazard a
guess why this might be.  Are they time columns instead of
timestamp?  Do you have triggers which modify the values?  There are
all sorts of things that might be happening that you're not telling
us about.

A self-contained test case on a fairly recent minor release would be
useful, if you can create one.  Something like this:

intclient=# create table t (id int primary key, d1 date not null, d2
date not null);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"t_pkey" for table "t"
CREATE TABLE
intclient=# insert into t
  values
    (1, date_trunc('hours',now()), current_date),
    (2, localtimestamp::date, current_date),
    (3, now(), clock_timestamp()::DATE);
INSERT 0 3
intclient=# select * from t;
 id |     d1     |     d2
----+------------+------------
  1 | 2012-01-11 | 2012-01-11
  2 | 2012-01-11 | 2012-01-11
  3 | 2012-01-11 | 2012-01-11
(3 rows)

You might find this page helpful:

http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

-Kevin

john.udick@zionsbancorp.com writes:
> I would expect that at the time/date of the of now() and clock_timestamp()
> to be equal; which they are.

Well, they are not in general, but that doesn't appear to be your
problem.  You have not shown us a reproducible test case, but I wonder
whether your table has a trigger that is doing date_trunc('year',...)
or something like that on the column value.

            regards, tom lane

John Udick <John.Udick@zionsbancorp.com> wrote:

> We did notice that although now() failed, changing this to
> 'now()'::date works (using single quotes around now()).

That seem unrelated to the now() function.  Casting a literal to a
date/time/timestamp supports parsing out certain keywords, like
'now', 'today', 'yesterday', and 'tomorrow'.  It's probably ignoring
those parentheses.

> Also, apparently we are not truly a Postgres database, but EMC's
> Greenplum database. I will be working with our admins and EMC,
> unless someone in Postgres can point out a specific version that
> resolved this issue.

> "PostgreSQL 8.2.15 (Greenplum Database 4.1.1.5 build 1) on
> x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled
> on Sep 20 2011 11:09:08"

Yeah, I don't know how much the community lists can help with that
implementation.  It seems there is a bug in that version, but it
might not be present in the community version.

-Kevin

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> John Udick <John.Udick@zionsbancorp.com> wrote:
>> "PostgreSQL 8.2.15 (Greenplum Database 4.1.1.5 build 1) on
>> x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled
>> on Sep 20 2011 11:09:08"

> Yeah, I don't know how much the community lists can help with that
> implementation.  It seems there is a bug in that version, but it
> might not be present in the community version.

The provided example works for me on PG 8.2.23:

regression=# select * from t order by 1;
 id |     d1     |     d2
----+------------+------------
  1 | 2012-01-11 | 2012-01-11
  2 | 2012-01-11 | 2012-01-11
  3 | 2012-01-11 | 2012-01-11
(3 rows)

and it does not remind me of any known/fixed bug in community 8.2.x.
So I'd have to say this is a Greenplum bug.

            regards, tom lane

Following the example provided, this still appears to be a bug. The results=
 are very different when we run the example. We did notice that although no=
w() failed, changing this to 'now()'::date works (using single quotes aroun=
d now()). I will be working with our administrators to review patches and v=
ersion levels.=20

Also, apparently we are not truly a Postgres database, but EMC's Greenplum =
database. I will be working with our admins and EMC, unless someone in Post=
gres can point out a specific version that resolved this issue.=20

drop table if exists t;
create table t=20
  (id int primary key,=20
   d1 date not null,=20
   d2 date not null);
=20=20=20
insert into t
  values
    (1, now(), current_date),
    (2, localtimestamp::date, date_in(date_out(current_date))),
    (3, 'now()'::date, clock_timestamp()::DATE);

select * from t order by 1;

 id |     d1     |     d2=20=20=20=20=20
----+------------+------------
  1 | 1999-12-31 | 1999-12-31
  2 | 1999-12-31 | 1999-12-31
  3 | 2012-01-11 | 2012-01-11

Additional information - select version();
"PostgreSQL 8.2.15 (Greenplum Database 4.1.1.5 build 1) on x86_64-unknown-l=
inux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Sep 20 2011 11:09:08"

John Udick | Enterprise Data Warehouse Development

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20
Sent: Wednesday, January 11, 2012 8:21 AM
To: John Udick
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #6391: insert does not insert correct value=20

john.udick@zionsbancorp.com writes:
> I would expect that at the time/date of the of now() and clock_timestamp()
> to be equal; which they are.

Well, they are not in general, but that doesn't appear to be your
problem.  You have not shown us a reproducible test case, but I wonder
whether your table has a trigger that is doing date_trunc('year',...)
or something like that on the column value.

            regards, tom lane

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
THIS ELECTRONIC MESSAGE, INCLUDING ANY ACCOMPANYING DOCUMENTS, IS CONFIDENT=
IAL and may contain information that is privileged and exempt from disclosu=
re under applicable law. If you are neither the intended recipient nor resp=
onsible for delivering the message to the intended recipient, please note t=
hat any dissemination, distribution, copying or the taking of any action in=
 reliance upon the message is strictly prohibited. If you have received thi=
s communication in error, please notify the sender immediately.  Thank you.