Re: queries on xmin

On 6/11/09, Matt Amos <zerebubuth(at)gmail(dot)com> wrote:
> On Thu, Jun 11, 2009 at 2:48 PM, Marko Kreen<markokr(at)gmail(dot)com> wrote:
> > On 6/11/09, Matt Amos <zerebubuth(at)gmail(dot)com> wrote:
> >> On Thu, Jun 11, 2009 at 1:13 PM, Brett Henderson<brett(at)bretth(dot)com> wrote:
> >> >> See pgq.batch_event_sql() function in Skytools [2] for how to
> >> >> query txids between snapshots efficiently and without being affected
> >> >> by long transactions.
> >> >
> >> > I'll take a look.
> >>
> >> it was looking at the skytools stuff which got me thinking about using
> >> txids in the first place. someone on the osm-dev list had suggested
> >> using PgQ, but we weren't keen on the schema changes that would have
> >> been necessary.
> >
> > Except the trigger, PgQ does not need any schema changes?
>
>
> i've been having a look and it seems to me that PgQ requires some
> extra tables as well as the trigger. am i missing something?

Well, my point was you don't need to change your existing tables.

PgQ indeed has few internal and per-queue tables but they come
with pgq.sql, live under 'pgq' schema and are maintained by it.

There is no need for you to worry about them.

> PgQ might be a good solution, but i'm worried that after calling
> pgq.finish_batch() the batch is released. this would mean it wouldn't
> be possible to regenerate older files (e.g: a few days to a week) in
> case something unexpected went wrong. it might not be a major problem,
> though.

You can register a 'safely consumer' on each queue, that lags specified
amount of time to avoid pgq rotation for that queue, or set rotation
time big enough to guarantee that old batches stay around.

> i think we could get the same functionality without the extra daemons,
> by putting a trigger on those tables for insert and recorded the
> object id, version and 64-bit txid in another table.

We have tried storing id-s into queue and later joining them with data
rows, but it seems more efficient to duplicate data into queue and thus
avoid joins when reading from it. The queue space will recover soon anyway.

On light loads it probably does not matter either way.

> but if we're
> going to alter the schema we might as well put the txid column
> directly into those tables...

It's permanent space loss in data tables vs. temporary space loss in
queue tables. If former fits your situation better, go ahead.

--
marko