Re: Constant "JTable" update

Hello to all the pgJDBC communauty!

My problem seems to be quite simple to solve, but I would like to get
you opinion on the different solutions available for the result to be
programmed as nicer as it can :

My PostgreSQL database is used as a data storage for measure. On
process "feeds" the database, inserting measure in a table (measure). On
the other hand, I have a java program which is used to display such
results in tables (or charts). Up to there, nothing seems to be easier.
But I have to display data in "real time". The java program has to be
notified that a new measure (or data content in the measure table) was
inserted (repectively has changed).

The straightforward solution seems to have database listeners on the
table that wakes-up a notifier in the java program. But such kind of
mechanism seems not to be implemented in the JDBC API (up to my small
knowledge).
The second tortuous solutions (the one I use) is to query the database
relatively often to get the freshest results. This is quite bandwidth
consumming (assuming that some users should use a quite small bandwidth
connection). Using this kind of solution makes the Java application
slow...

My questions are : is there any other solutions (Use of cursor maybe)?
is the first solution can be implemented while combining JDBC et
PostgreSQL functionnalities?

Thank you for any remarks/answers/attention paid for these problems.
--
Jean-Christophe ARNU
s/w developer
Paratronic France

----- Original Message -----
From: "Jean-Christophe ARNU" <arnu(at)paratronic(dot)fr>
To: <pgsql-jdbc(at)postgresql(dot)org>
Sent: Thursday, November 08, 2001 10:38 PM
Subject: [JDBC] Constant "JTable" update

> The straightforward solution seems to have database listeners on the
> table that wakes-up a notifier in the java program. But such kind of
> mechanism seems not to be implemented in the JDBC API (up to my small
> knowledge).

I haven´t seen such a mechanism, either :-)

> The second tortuous solutions (the one I use) is to query the database
> relatively often to get the freshest results. This is quite bandwidth
> consumming (assuming that some users should use a quite small bandwidth
> connection). Using this kind of solution makes the Java application
> slow...
First of all, this is how I interpreted your config.
You do a executeQuery once the rendering of the chart is done for one
execution?
And you process the entire ResultSet everytime, even though no changes are
made?

If this is the case, I would suggest a change in the following direction:
1. Create a little status table containing just one column: create table
last_change (lastchange datetime). Also add one row to the table
2. Create a trigger on the measurer table, that updates the date of the
status table.
3. Design your algorithm something like this

check status by executing a select on status table.

if changed {
store the date from status query
execute data query
render chart
}

This would reduce the bandwith by not sending the resultset when no changes
are made.

By measuring the average change in time between , say the last five updates
to the status table, you
could even put the rendering of the chart in its on thread and let it sleep
a little shorter than the average time

Regards,
Per-Olof Norén

Is there anything in the jdbc spec which supports this?

I have had a quick look, and haven't found anything.

I suppose this could be a postgres specific extension if it doesn't
exist in the database

Alternatively instead of getting all the rows to the database you could
do this another way

Create the table with a sequential id

Ie create table foo (id serial, ....)

Get the largest id of the rows

while(1){
newmax = 0;

select max (id) from foo;

if max > newmax, then you have more data

select * from table where id > max

newmax = max
}

This is just pseudo code, but it should be close

Dave
-----Original Message-----
From: pgsql-jdbc-owner(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of Per-Olof Norén
Sent: November 9, 2001 8:05 AM
To: Jean-Christophe ARNU; pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] Constant "JTable" update

----- Original Message -----
From: "Jean-Christophe ARNU" <arnu(at)paratronic(dot)fr>
To: <pgsql-jdbc(at)postgresql(dot)org>
Sent: Thursday, November 08, 2001 10:38 PM
Subject: [JDBC] Constant "JTable" update

> The straightforward solution seems to have database listeners on the
> table that wakes-up a notifier in the java program. But such kind of
> mechanism seems not to be implemented in the JDBC API (up to my small
> knowledge).

I haven´t seen such a mechanism, either :-)

> The second tortuous solutions (the one I use) is to query the database
> relatively often to get the freshest results. This is quite bandwidth
> consumming (assuming that some users should use a quite small
bandwidth
> connection). Using this kind of solution makes the Java application
> slow...
First of all, this is how I interpreted your config.
You do a executeQuery once the rendering of the chart is done for one
execution?
And you process the entire ResultSet everytime, even though no changes
are
made?

If this is the case, I would suggest a change in the following
direction:
1. Create a little status table containing just one column: create table
last_change (lastchange datetime). Also add one row to the table
2. Create a trigger on the measurer table, that updates the date of the
status table.
3. Design your algorithm something like this

check status by executing a select on status table.

if changed {
store the date from status query
execute data query
render chart
}

This would reduce the bandwith by not sending the resultset when no
changes
are made.

By measuring the average change in time between , say the last five
updates
to the status table, you
could even put the rendering of the chart in its on thread and let it
sleep
a little shorter than the average time

Regards,
Per-Olof Norén

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Have you looked at the LISTEN and NOTIFY sql commands in Postgres? I
know the jdbc driver has some support for them, but I haven't ever tried
to use them so I don't know how or if they work through jdbc.

thanks,
--Barry

Per-Olof Norén wrote:

> ----- Original Message -----
> From: "Jean-Christophe ARNU" <arnu(at)paratronic(dot)fr>
> To: <pgsql-jdbc(at)postgresql(dot)org>
> Sent: Thursday, November 08, 2001 10:38 PM
> Subject: [JDBC] Constant "JTable" update
>
>
>
>>The straightforward solution seems to have database listeners on the
>>table that wakes-up a notifier in the java program. But such kind of
>>mechanism seems not to be implemented in the JDBC API (up to my small
>>knowledge).
>>
>
> I haven´t seen such a mechanism, either :-)
>
>
>
>>The second tortuous solutions (the one I use) is to query the database
>>relatively often to get the freshest results. This is quite bandwidth
>>consumming (assuming that some users should use a quite small bandwidth
>>connection). Using this kind of solution makes the Java application
>>slow...
>>
> First of all, this is how I interpreted your config.
> You do a executeQuery once the rendering of the chart is done for one
> execution?
> And you process the entire ResultSet everytime, even though no changes are
> made?
>
> If this is the case, I would suggest a change in the following direction:
> 1. Create a little status table containing just one column: create table
> last_change (lastchange datetime). Also add one row to the table
> 2. Create a trigger on the measurer table, that updates the date of the
> status table.
> 3. Design your algorithm something like this
>
>
> check status by executing a select on status table.
>
> if changed {
> store the date from status query
> execute data query
> render chart
> }
>
> This would reduce the bandwith by not sending the resultset when no changes
> are made.
>
> By measuring the average change in time between , say the last five updates
> to the status table, you
> could even put the rendering of the chart in its on thread and let it sleep
> a little shorter than the average time
>
> Regards,
> Per-Olof Norén
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>