RE: [INTERFACES] Possible error in libpg++ interface, date[time] fields. - Mailing list pgsql-interfaces
From | Per Rønne |
---|---|
Subject | RE: [INTERFACES] Possible error in libpg++ interface, date[time] fields. |
Date | |
Msg-id | B15FE41977A9D21195F00000836A4275119CCA@mars.idp.dk Whole thread Raw |
List | pgsql-interfaces |
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.
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
$
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)
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)
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
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
$
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
$
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]
pgsql-interfaces by date: