>
> I have a C application that caches data from some database tables.
> The tables have a trigger that update the row with a generator id
> whenever the row changes. Every time the cache is refreshed from
> tables to memory, the last id read is kept track of. Next time, the
> cache is refreshed, to avoid reading the entire dataset, only the rows
> having id greater than the last id are read. The problem with this
> is that after the cache is refreshed, a transaction with a lower
> generator id might commit and the caching scheme will miss this row.
>
> Maybe this is not a good way to do this. Anyone has other suggestions ?
>
> Thanks!

what about this -
you have an external table. the trigger(s) in question insert their IDs into
this table.
Now inserts into this table are not transaction based.
You may also fire an event on insert. The event won't fire until commit
which means when the event fires, the real (internal) record is now visible
to other transactions.
You can now respond to the event to get the last record in the external
table and add this id to the cache.
Alan
PS I still have to wonder why your design is not using the database server
as it's "cache" since that's what it's designed for.

>
> --- In firebird-support@yahoogroups.com, "Alan McDonald" <alan@m...>
> wrote:
> >
> > > I am using this to keep track of changes so that they can be cached
> > > for example in an application. To do this, it's easy to select all the
> > > rows that have the id greater than the id I saw last time.
> > > Therefore I need to know if any transaction with a lower id might now
> > > commit in the future.
> > >
> > > Thanks!
> >
> > since generators are fired outside transaction control there is no
> > relationship here (necessarily)...
> > Tell us what component set you are using and a little more about
> what this
> > caching is all about and someone may have some suggestions for you.
> > you talk about a lower id commiting but also about selecting higher
> ids so I
> > can't quite follow.
> > But there is already some very good caching going on in some component
> > sets..
> > tell us more about the size of the datasets you are experiencing,
> whether
> > local or remote connections etc
> > Alan
> >
> >
> > >
> > > --- In firebird-support@yahoogroups.com, "Alan McDonald" <alan@m...>
> > > wrote:
> > > >
> > > > >
> > > > > Is there any way to find out if there are any uncommitted
> > > transactions
> > > > > that have called gen_id on a generator ?
> > > > > Or if I get an id using the gen_id call, can I somehow wait till
> > > all
> > > > > current transactions either commit or abort ?
> > > > >
> > > > > Thanks!
> > > > >
> > > >
> > > > uh oh... what are you trying to do... sounds fishy
> > > > Alan
> > > >