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:

22 April 1999, 08:37:42

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@idp.dk (office)
Telephone +45 32 83 63 17 (office), +45 26 16 48 10 (mobile)

-----Original Message-----
From: Per Rønne [mailto:per@idp.dk]
Sent: 22. april 1999 10:43
To: Per Rønne; 'pgsql-interfaces@postgresql.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@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]