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

Further experiments show that the row marks mentioned - NULL values in the
row, at least NULL values in fields _before_ the field in question.

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

-----Original Message-----
From: Per Rønne [mailto:per(at)idp(dot)dk]
Sent: 22. april 1999 10:43
To: Per Rønne; 'pgsql-interfaces(at)postgresql(dot)org'
Subject: RE: [INTERFACES] Possible error in libpg++ interface, date[time]
fields.

I have just done an extra experiment.

>From C++ I have:

Number of rows received is 10
Id = 1 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:37+02
Id = 2 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:38+02
Id = 3 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:38+02
Id = 4 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:38+02
Id = 6 E-mail = 111111(at)2222(dot)33 <mailto:111111(at)2222(dot)33> Deliverytime =
1999-04-09 17:03:38+02
Id = 7 E-mail = 111111(at)2222(dot)33 <mailto:111111(at)2222(dot)33> Deliverytime =
1999-04-09 17:03:38+02
Id = 8 E-mail = 111111(at)2222(dot)33 <mailto:111111(at)2222(dot)33> Deliverytime =
1999-04-09 17:03:38+02
Id = 9 E-mail = 111111(at)2222(dot)33 <mailto:111111(at)2222(dot)33> Deliverytime =
1999-04-09 17:03:38+02
Id = 10 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:38+02
Id = 5 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)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(at)diku(dot)dk|1999-04-09 <mailto:1|xerxes(at)diku(dot)dk|1999-04-09>
17:03:37+02
2|xerxes(at)diku(dot)dk|1999-04-09 <mailto:2|xerxes(at)diku(dot)dk|1999-04-09>
17:03:38+02
3|xerxes(at)diku(dot)dk|1999-04-09 <mailto:3|xerxes(at)diku(dot)dk|1999-04-09>
17:03:38+02
4|xerxes(at)diku(dot)dk|1999-04-09 <mailto:4|xerxes(at)diku(dot)dk|1999-04-09>
17:03:38+02
6|111111(at)2222(dot)33|2000-01-01 <mailto:6|111111(at)2222(dot)33|2000-01-01>
00:00:00+01
7|111111(at)2222(dot)33|1999-12-31 <mailto:7|111111(at)2222(dot)33|1999-12-31>
00:00:00+01
8|111111(at)2222(dot)33|1999-04-12 <mailto:8|111111(at)2222(dot)33|1999-04-12>
00:00:00+02
9|111111(at)2222(dot)33|1999-04-13 <mailto:9|111111(at)2222(dot)33|1999-04-13>
00:00:00+02
10|xerxes(at)diku(dot)dk|1999-03-15 <mailto:10|xerxes(at)diku(dot)dk|1999-03-15>
00:00:00+01
5|xerxes(at)diku(dot)dk|1999-04-21 <mailto:5|xerxes(at)diku(dot)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(at)diku(dot)dk|1999-04-09 <mailto:1|xerxes(at)diku(dot)dk|1999-04-09>
17:03:37+02
2|xerxes(at)diku(dot)dk|1999-04-09 <mailto:2|xerxes(at)diku(dot)dk|1999-04-09>
17:03:38+02
3|xerxes(at)diku(dot)dk|1999-04-09 <mailto:3|xerxes(at)diku(dot)dk|1999-04-09>
17:03:38+02
4|xerxes(at)diku(dot)dk|1999-04-09 <mailto:4|xerxes(at)diku(dot)dk|1999-04-09>
17:03:38+02
6|111111(at)2222(dot)33|2000-01-01 <mailto:6|111111(at)2222(dot)33|2000-01-01>
00:00:00+01
8|111111(at)2222(dot)33|1999-04-12 <mailto:8|111111(at)2222(dot)33|1999-04-12>
00:00:00+02
9|111111(at)2222(dot)33|1999-04-13 <mailto:9|111111(at)2222(dot)33|1999-04-13>
00:00:00+02
10|xerxes(at)diku(dot)dk|1999-03-15 <mailto:10|xerxes(at)diku(dot)dk|1999-03-15>
00:00:00+01
5|xerxes(at)diku(dot)dk|1999-04-21 <mailto:5|xerxes(at)diku(dot)dk|1999-04-21>
13:00:00+02
7|111111(at)2222(dot)33|1999-12-31 <mailto:7|111111(at)2222(dot)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(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:37+02
Id = 2 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:38+02
Id = 3 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:38+02
Id = 4 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:38+02
Id = 6 E-mail = 111111(at)2222(dot)33 <mailto:111111(at)2222(dot)33> Deliverytime =
1999-04-09 17:03:38+02
Id = 8 E-mail = 111111(at)2222(dot)33 <mailto:111111(at)2222(dot)33> Deliverytime =
1999-04-09 17:03:38+02
Id = 9 E-mail = 111111(at)2222(dot)33 <mailto:111111(at)2222(dot)33> Deliverytime =
1999-04-09 17:03:38+02
Id = 10 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:38+02
Id = 5 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-21 13:00:00+02
Id = 7 E-mail = 111111(at)2222(dot)33 <mailto:111111(at)2222(dot)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(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:37+02
Id = 2 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:38+02
Id = 3 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:38+02
Id = 4 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:38+02
Id = 5 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-21 13:00:00+02
Id = 6 E-mail = 111111(at)2222(dot)33 <mailto:111111(at)2222(dot)33> Deliverytime =
1999-04-21 13:00:00+02
Id = 7 E-mail = 111111(at)2222(dot)33 <mailto:111111(at)2222(dot)33> Deliverytime =
1999-04-21 13:00:00+02
Id = 8 E-mail = 111111(at)2222(dot)33 <mailto:111111(at)2222(dot)33> Deliverytime =
1999-04-21 13:00:00+02
Id = 9 E-mail = 111111(at)2222(dot)33 <mailto:111111(at)2222(dot)33> Deliverytime =
1999-04-21 13:00:00+02
Id = 10 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)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(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
Id = 9 E-mail = 111111(at)2222(dot)33 <mailto:111111(at)2222(dot)33> Deliverytime =
Id = 8 E-mail = 111111(at)2222(dot)33 <mailto:111111(at)2222(dot)33> Deliverytime =
Id = 7 E-mail = 111111(at)2222(dot)33 <mailto:111111(at)2222(dot)33> Deliverytime =
Id = 6 E-mail = 111111(at)2222(dot)33 <mailto:111111(at)2222(dot)33> Deliverytime =
Id = 5 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-21 13:00:00+02
Id = 4 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:38+02
Id = 3 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:38+02
Id = 2 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)dk> Deliverytime =
1999-04-09 17:03:38+02
Id = 1 E-mail = xerxes(at)diku(dot)dk <mailto:xerxes(at)diku(dot)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(at)idp(dot)dk (office)
Telephone +45 32 83 63 17 (office), +45 26 16 48 10 (mobile)

[SNIP the original message - article otherwise too long]