timestamp with timezone not saved properly

timestamp with timezone not saved properly

Postby bradk » Mon 12 Feb 2007 21:08

If I pass a System.DateTime.UTCNow(); value to a plpgsql proc parameter of type timestamp with timezone, the value is not saved properly in postgres. Here is a value I inserted a few minutes ago, as selected from a PGAdmin window:

151945-03-12 14:42:14.56351-05

You can see the year is hugely out of scale. This is something we just noticed on Postgres 8.2.1 64 bit. We have the system set to store date-times as integers.

When the value is read back into .Net it displays properly (somehow), but it appears terribly wrong in postgres. We use a purchased copy of version 2.50.

Update: below is a server log of the call. You can see parameter $5 is not valid timestamptz.

Code: Select all
LOG:  duration: 0.068 ms  bind PRSTMT404779712537666500/PORTAL404779712537666500: SELECT * from subscription_add($1, $2, $3, $4, $5, $6, $7, $8) AS return_value

DETAIL:  parameters: $1 = '1359035820', $2 = '94bf75b0-3dc1-4029-b129-9f823cdced00', $3 = '9e5fe88c-d469-4612-999f-572bd8a41348', $4 = 'http://blah', $5 = '151945-03-13 05:53:16.44234-05', $6 = 'owner data', $7 = 'httpBasic', $8 = 'f'


Here's the function definition
Code: Select all
CREATE OR REPLACE FUNCTION subscription_add(IN _hashkey integer, IN _subscriptionid character varying, IN _topicid character varying, IN _notifyaddress character varying, IN _expiration timestamp with time zone, IN _ownerdata character varying, IN _notifychannel character varying, IN _hasexpiration boolean, OUT _retval boolean) AS



And the .Net Code

Code: Select all
      public bool add(string config, string _subscriptionid, string _topicid, string _notifyaddress, System.DateTime _expiration, string _ownerdata, string _notifychannel, bool _hasexpiration)
      {
         try
         {
            string hashSource = "eventpubsub:topic:" + _topicid.ToString();
            Database db = CreateHashedDatabase(config, hashSource);
            string sqlCommand = "subscription_add";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(dbCommand, "_hashkey", DbType.Int32, _hashkey);
            db.AddInParameter(dbCommand, "_subscriptionid", DbType.String, _subscriptionid);
            db.AddInParameter(dbCommand, "_topicid", DbType.String, _topicid);
            db.AddInParameter(dbCommand, "_notifyaddress", DbType.String, _notifyaddress);
            db.AddInParameter(dbCommand, "_expiration", DbType.DateTime, _expiration);
            db.AddInParameter(dbCommand, "_ownerdata", DbType.String, _ownerdata);
            db.AddInParameter(dbCommand, "_notifychannel", DbType.String, _notifychannel);
            db.AddInParameter(dbCommand, "_hasexpiration", DbType.Boolean, _hasexpiration);
            db.AddOutParameter(dbCommand, "_retval", DbType.Boolean, 1);
            db.ExecuteNonQuery(dbCommand);
            return (bool)db.GetParameterValue(dbCommand, "_retval");
         }
         catch (Exception ex)
         {
            Logger.Error(11000, ex.ToString());
            throw(ex);
         }
      }
Code: Select all
bradk
 
Posts: 34
Joined: Thu 20 Apr 2006 01:42

Postby Alexey » Wed 14 Feb 2007 14:04

Please send us your test project to reproduce the problem, including the your database dump.
Use e-mail address provided in the Readme file.
Do not use third party components.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby bradk » Wed 14 Feb 2007 14:14

I can do that but it will take some time to remove all of the dependencies. Were you not able to reproduce it ? I believe its a simple matter of calling a plpgsql function which has an IN parameter of type timestamptz, on a 64 bit compile of postgres 8.2.1. I have verified the error does not occur on a 32 bit compile of 8.2.1. This is fairly critical as we are supposed to ship software soon and just found this problem. We will have to re-install postgres on all of our machines if there is no fix for your provider. Thanks for any assistance.
bradk
 
Posts: 34
Joined: Thu 20 Apr 2006 01:42

Postby bradk » Wed 14 Feb 2007 16:46

I have sent a sample to the address in the support.html file. The problem occurs against your entlibquickstarts database, with the addition of a simple function to insert a product and set the lastupdate column.
bradk
 
Posts: 34
Joined: Thu 20 Apr 2006 01:42

Postby Alexey » Thu 15 Feb 2007 07:45

I've received your sample. Now it is being investigated. Look forward to hearing from me again.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby bradk » Thu 15 Feb 2007 14:28

It appears the problem may be related to this PostgreSQL configuration option we are using on 64 bit:

CONFIGURE = '--prefix=/usr/local/pgsql-8.2.1' '--enable-integer-datetimes' '--with-krb5' '--with-perl' '--with-pam'

We ran a test this morning using floating point date-times and the error did not occur. We will continue testing to verify this, and look forward to your verification of this as well.
bradk
 
Posts: 34
Joined: Thu 20 Apr 2006 01:42

Postby Alexey » Fri 16 Feb 2007 10:13

I was able to run your project successfully, but the date is inserted correctly. Using PostgreSQL with default settings.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby bradk » Fri 16 Feb 2007 13:58

OK, you way want to at least document that this problem exists when using integer date/times on 64 bit. Might save the next guy a few days touble.
bradk
 
Posts: 34
Joined: Thu 20 Apr 2006 01:42

Postby Alexey » Mon 19 Feb 2007 09:24

Yes, I think we will have this documented.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect for PostgreSQL