Thread: where did that date and time come from??
When I vacuum I get two messages: NOTICE: Index pg_class_relname_index: NUMBER OF INDEX' TUPLES (108) IS NOT THE SAME AS HEAP' (82) NOTICE: Index pg_class_oid_index: NUMBER OF INDEX' TUPLES (108) IS NOT THE SAME AS HEAP' (82) I had followed some advice I read in one of these postgres mailing lists (from this weekend) regarding case problems when transferring from access97, and I executed both of those updates. I can't find them now, nor can I remember them, but they replaced certain values in the tables corresponding to the indexes above with the lowercase equivalent of those values. I'd like to try to reverse the effect - does anyone have a copy of that email from sometime this weekend? jt
On Mon, 19 Apr 1999, JT Kirkpatrick wrote: > Whoa baby, I lied through my teeth! Inserting DID NOT work fine when done > in psql on the server itself - it also returned the april 14 date and time > as default. So I tested around a bit. I redefined a new test table using > default now() and guess what - it works!!!!! But the documentation says > that I can define fields as default 'now'. doesn't seem to work right as > far as I can tell, but defining them as default now() does. . . I'm just guessing here, but I think I saw somewhere in the Postgres dox that said if something weren't declared correctly, that it would be interpreted as a constant and not a function call. If my memory serves me, this would mean that SQL was actually parsed as default 'apr 14, 10am.' Could be wrong. I've done a lot of reading recently, and, for all I know, that could be an Oracle quirk. :P Caveat emptor... -- Jason Lee | for all my labors and best laid plans I'd only earned www.screech.org | a reprimand, forevermore to understand that dreams come screech@screech.org | true can kill a man if never graced by sovereign hands
For what it's worth, my experience with this error is that your backend crashed during the update - why it should do that on such a small set of data, I cannot begin to guess. But I have always found the data to be okay, though the query executing at the time will need to be completed. Just drop the indexes, complete the query, and recreate the indexes (I suggets this order for performance reasons, unless your update depends on using the indexes to perform efficiently). You may also want to increase the amount of memory allocated to the backend, both -B and -S (buffers, and sort buffers) From: JT Kirkpatrick <jt-kirkpatrick@mpsllc.com> Date: Mon, 19 Apr 1999 18:24:16 -0400 Organization: MPS, LLC Content-Type: text/plain; charset="us-ascii" Sender: owner-pgsql-general@postgreSQL.org Precedence: bulk When I vacuum I get two messages: NOTICE: Index pg_class_relname_index: NUMBER OF INDEX' TUPLES (108) IS NOT THE SAME AS HEAP' (82) NOTICE: Index pg_class_oid_index: NUMBER OF INDEX' TUPLES (108) IS NOT THE SAME AS HEAP' (82) I had followed some advice I read in one of these postgres mailing lists (from this weekend) regarding case problems when transferring from access97, and I executed both of those updates. I can't find them now, nor can I remember them, but they replaced certain values in the tables corresponding to the indexes above with the lowercase equivalent of those values. I'd like to try to reverse the effect - does anyone have a copy of that email from sometime this weekend? jt -- Karl DeBisschop <kdebisschop@spaceheater.infoplease.com> 617.832.0332 (Fax: 617.956.2696) Information Please - your source for FREE online reference http://www.infoplease.com - Your Ultimate Fact Finder http://kids.infoplease.com - The Great Homework Helper
> I have a database that has four fields -
> Create table dispnote (
> Autoinv int4
> noteby char(8) not null default 'current_user',
> added datetime not null default 'now',
> notes varchar(255),
> constraint dispnotepk primary key (autoinv, added),
> constraint dispnotessms foreign key (auoinv) references ssms
> );
> ... But when I insert a record it came up with a date of april 14, 10am! My
> server and my client both have the correct date and time (april 19, 3pm) -
> where did it get the wrong date and time from??
This is (still) a problem with Postgres optimizing untyped constants
by evaluating them at table creation time rather than at run time.
Redefine your datetime field as
...
added datetime not null default text 'now',
...
to force the type of the default value to be a string, which is then
converted at run time as you would expect. Fixing this behavior would
seem to be a good project for someone (maybe me :) for v6.6...
- Tom
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
Thomas Lockhart wrote: > > I have a database that has four fields - > > Create table dispnote ( > > Autoinv int4 > > noteby char(8) not null default 'current_user', > > added datetime not null default 'now', > > notes varchar(255), > > constraint dispnotepk primary key (autoinv, added), > > constraint dispnotessms foreign key (auoinv) references ssms > > ); > > ... But when I insert a record it came up with a date of april 14, 10am! My > > server and my client both have the correct date and time (april 19, 3pm) - > > where did it get the wrong date and time from?? > > This is (still) a problem with Postgres optimizing untyped constants > by evaluating them at table creation time rather than at run time. > Redefine your datetime field as > > ... > added datetime not null default text 'now', > ... > > to force the type of the default value to be a string, which is then > converted at run time as you would expect. Fixing this behavior would > seem to be a good project for someone (maybe me :) for v6.6... > How about this? added datetime default datetime(now()) not null, Is this one OK? Regards, Chai
> How about this?
> added datetime default datetime(now()) not null,
Yes, this apparently works by converting from timestamp to datetime.
In general datetime has better dynamic range than does timestamp, but
for calculating 'now' both are equivalent in performance.
- Tom
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California