Thread: RE: [INTERFACES] Possible error in libpg++ interface, date[time] fields.

RE: [INTERFACES] Possible error in libpg++ interface, date[time] fields.

From
Per Rønne
Date:
I have just done an extra experiment.
 
From C++ I have:
 
Number of rows received is 10
Id =  1 E-mail = xerxes@diku.dk Deliverytime = 1999-04-09 17:03:37+02
Id =  2 E-mail = xerxes@diku.dk Deliverytime = 1999-04-09 17:03:38+02
Id =  3 E-mail = xerxes@diku.dk Deliverytime = 1999-04-09 17:03:38+02
Id =  4 E-mail = xerxes@diku.dk Deliverytime = 1999-04-09 17:03:38+02
Id =  6 E-mail = 111111@2222.33 Deliverytime = 1999-04-09 17:03:38+02
Id =  7 E-mail = 111111@2222.33 Deliverytime = 1999-04-09 17:03:38+02
Id =  8 E-mail = 111111@2222.33 Deliverytime = 1999-04-09 17:03:38+02
Id =  9 E-mail = 111111@2222.33 Deliverytime = 1999-04-09 17:03:38+02
Id = 10 E-mail = xerxes@diku.dk Deliverytime = 1999-04-09 17:03:38+02
Id =  5 E-mail = xerxes@diku.dk Deliverytime = 1999-04-21 13:00:00+02
$
 
I psql I have, and I do, as follows:
 
postdb=> select * from t;
id|email         |deliverytime         
--+--------------+----------------------
 1|xerxes@diku.dk|1999-04-09 17:03:37+02
 2|xerxes@diku.dk|1999-04-09 17:03:38+02
 3|xerxes@diku.dk|1999-04-09 17:03:38+02
 4|xerxes@diku.dk|1999-04-09 17:03:38+02
 6|111111@2222.33|2000-01-01 00:00:00+01
 7|111111@2222.33|1999-12-31 00:00:00+01
 8|111111@2222.33|1999-04-12 00:00:00+02
 9|111111@2222.33|1999-04-13 00:00:00+02
10|xerxes@diku.dk|1999-03-15 00:00:00+01
 5|xerxes@diku.dk|1999-04-21 13:00:00+02
(10 rows)
 
postdb=> update t set deliverytime = '1999-12-31 00:00:00+01' where id = 7;
UPDATE 0
postdb=> update deferredtransaction set deliverytime = '1999-12-31 00:00:00+01' where id = 7;
UPDATE 1
postdb=> select * from t;
id|email         |deliverytime         
--+--------------+----------------------
 1|xerxes@diku.dk|1999-04-09 17:03:37+02
 2|xerxes@diku.dk|1999-04-09 17:03:38+02
 3|xerxes@diku.dk|1999-04-09 17:03:38+02
 4|xerxes@diku.dk|1999-04-09 17:03:38+02
 6|111111@2222.33|2000-01-01 00:00:00+01
 8|111111@2222.33|1999-04-12 00:00:00+02
 9|111111@2222.33|1999-04-13 00:00:00+02
10|xerxes@diku.dk|1999-03-15 00:00:00+01
 5|xerxes@diku.dk|1999-04-21 13:00:00+02
 7|111111@2222.33|1999-12-31 00:00:00+01
(10 rows)
 
postdb=>
 
- where t is a view [BTW, it _should_ be possible to update through such a view!].
 
When having done this update, that is updated the deliverytime value to the date is was before, the C++ programs returns with this output:
 
$ per
Number of rows received is 10
Id =  1 E-mail = xerxes@diku.dk Deliverytime = 1999-04-09 17:03:37+02
Id =  2 E-mail = xerxes@diku.dk Deliverytime = 1999-04-09 17:03:38+02
Id =  3 E-mail = xerxes@diku.dk Deliverytime = 1999-04-09 17:03:38+02
Id =  4 E-mail = xerxes@diku.dk Deliverytime = 1999-04-09 17:03:38+02
Id =  6 E-mail = 111111@2222.33 Deliverytime = 1999-04-09 17:03:38+02
Id =  8 E-mail = 111111@2222.33 Deliverytime = 1999-04-09 17:03:38+02
Id =  9 E-mail = 111111@2222.33 Deliverytime = 1999-04-09 17:03:38+02
Id = 10 E-mail = xerxes@diku.dk Deliverytime = 1999-04-09 17:03:38+02
Id =  5 E-mail = xerxes@diku.dk Deliverytime = 1999-04-21 13:00:00+02
Id =  7 E-mail = 111111@2222.33 Deliverytime = 1999-04-21 13:00:00+02
Thus, it seems as if _some_ rows will always return the same deliverytime value as the delivery time value in the former row in the query.
 
Consequently I rephrase the C++ query by adding an order clause [order by id]. The I get this result:
 
$ per
Number of rows received is 10
Id =  1 E-mail = xerxes@diku.dk Deliverytime = 1999-04-09 17:03:37+02
Id =  2 E-mail = xerxes@diku.dk Deliverytime = 1999-04-09 17:03:38+02
Id =  3 E-mail = xerxes@diku.dk Deliverytime = 1999-04-09 17:03:38+02
Id =  4 E-mail = xerxes@diku.dk Deliverytime = 1999-04-09 17:03:38+02
Id =  5 E-mail = xerxes@diku.dk Deliverytime = 1999-04-21 13:00:00+02
Id =  6 E-mail = 111111@2222.33 Deliverytime = 1999-04-21 13:00:00+02
Id =  7 E-mail = 111111@2222.33 Deliverytime = 1999-04-21 13:00:00+02
Id =  8 E-mail = 111111@2222.33 Deliverytime = 1999-04-21 13:00:00+02
Id =  9 E-mail = 111111@2222.33 Deliverytime = 1999-04-21 13:00:00+02
Id = 10 E-mail = xerxes@diku.dk Deliverytime = 1999-04-21 13:00:00+02
$
 
Rows 6-10 now takes the values of row 5 - the last "right" row.
 
And if I swap the order I get the following result:
 
$ per
Number of rows received is 10
Id = 10 E-mail = xerxes@diku.dk Deliverytime = 
Id =  9 E-mail = 111111@2222.33 Deliverytime = 
Id =  8 E-mail = 111111@2222.33 Deliverytime = 
Id =  7 E-mail = 111111@2222.33 Deliverytime = 
Id =  6 E-mail = 111111@2222.33 Deliverytime = 
Id =  5 E-mail = xerxes@diku.dk Deliverytime = 1999-04-21 13:00:00+02
Id =  4 E-mail = xerxes@diku.dk Deliverytime = 1999-04-09 17:03:38+02
Id =  3 E-mail = xerxes@diku.dk Deliverytime = 1999-04-09 17:03:38+02
Id =  2 E-mail = xerxes@diku.dk Deliverytime = 1999-04-09 17:03:38+02
Id =  1 E-mail = xerxes@diku.dk Deliverytime = 1999-04-09 17:03:37+02
$
 
I conclude that there is a kind of mark on rows 6-10. In psql the rows are always treated correctly. I C++, through libpg++, a date or timebased field will be treated as if it has the same value as does the row before - and if there is no "before" row in the query, it will return a blank value! Even if the row is later updated with a new value in this field.
 

--
Per Erik Rønne
E-mail per@idp.dk (office)
Telephone +45 32 83 63 17 (office), +45 26 16 48 10 (mobile)

[SNIP the original message - article otherwise too long]