timestamp with timezone not saved properly

[bradk]

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

[Alexey]

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.

[bradk]

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]

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.

[Alexey]

I've received your sample. Now it is being investigated. Look forward to hearing from me again.

[bradk]

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.

[Alexey]

I was able to run your project successfully, but the date is inserted correctly. Using PostgreSQL with default settings.

[bradk]

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.

[Alexey]

Yes, I think we will have this documented.