Re: [GENERAL] table as log (multiple writers and readers)

Gurjeet Singh wrote: On Wed, Apr 23, 2008 at 12:29 AM, David Wilson <[EMAIL PROTECTED] > wrote: On Tue, Apr 22, 2008 at 2:48 PM, Joris Dobbelsteen <[EMAIL PROTECTED] > wrote: > > Ah, yes, all visible rows... > My p

Re: [GENERAL] table as log (multiple writers and readers)

On Wed, Apr 23, 2008 at 12:29 AM, David Wilson <[EMAIL PROTECTED]> wrote: > On Tue, Apr 22, 2008 at 2:48 PM, Joris Dobbelsteen > <[EMAIL PROTECTED]> wrote: > > > > Ah, yes, all visible rows... > > My point is that, unless you use a transaction with serializable > isolation, > > this all visible

Re: [GENERAL] table as log (multiple writers and readers)

On Tue, Apr 22, 2008 at 2:48 PM, Joris Dobbelsteen <[EMAIL PROTECTED]> wrote: > > Ah, yes, all visible rows... > My point is that, unless you use a transaction with serializable isolation, > this all visible rows for the second statement might be different from those > that you copied into the lo

Re: [GENERAL] table as log (multiple writers and readers)

David Wilson wrote: On Tue, Apr 22, 2008 at 9:52 AM, Joris Dobbelsteen <[EMAIL PROTECTED]> wrote: Describe the mechanism, because I don't really believe it yet. I think you need to do a advisory lock around every commit of every transaction that writes to the log table. Consider some number o

Re: [GENERAL] table as log (multiple writers and readers)

On Tue, Apr 22, 2008 at 9:52 AM, Joris Dobbelsteen <[EMAIL PROTECTED]> wrote: > > Describe the mechanism, because I don't really believe it yet. I think you > need to do a advisory lock around every commit of every transaction that > writes to the log table. Consider some number of reader process

Re: [GENERAL] table as log (multiple writers and readers)

David Wilson wrote: On Mon, Apr 21, 2008 at 7:55 PM, Joris Dobbelsteen <[EMAIL PROTECTED]> wrote: If you want to clean up the the staging table I have some concerns about the advisory lock. I think you mean exclusive table lock. Either works, really. An advisory lock is really just a lock ove

Re: [GENERAL] table as log (multiple writers and readers)

On Mon, Apr 21, 2008 at 7:55 PM, Joris Dobbelsteen <[EMAIL PROTECTED]> wrote: > > If you want to clean up the the staging table I have some concerns about > the advisory lock. I think you mean exclusive table lock. Either works, really. An advisory lock is really just a lock over which you have c

Re: [GENERAL] table as log (multiple writers and readers)

David Wilson wrote: (I originally missed replying to all here; sorry about the duplicate, Vance, but figured others might be interested. On Wed, Apr 16, 2008 at 1:55 PM, Vance Maverick <[EMAIL PROTECTED]> wrote: Another approach would be to queue the log entries in a "staging" table, so that

Re: [GENERAL] table as log (multiple writers and readers)

Thanks to all for your help. I've adopted the scheme involving a "staging" table -- the writer processes insert into that, then a single "publisher" process pulls from that and writes to the log, giving a clean serial order for any reader of the log. Vance On Mon, 2008-04-21 at 23:59 +0200,

Re: [GENERAL] table as log (multiple writers and readers)

Craig Ringer wrote: [snip] If you really want to make somebody cry, I guess you could do it with dblink - connect back to your own database from dblink and use a short transaction to commit a log record, using table-based (rather than sequence) ID generation to ensure that records were inserted

Re: [GENERAL] table as log (multiple writers and readers)

Andrew Sullivan wrote: On Thu, Apr 17, 2008 at 12:44:51PM +0800, Craig Ringer wrote: It won't work with multiple concurrent writers. There is no guarantee that an INSERT with a timestamp older than the one you just saw isn't waiting to commit. This is pretty unlikely -- I won't say impossible,

Re: [GENERAL] table as log (multiple writers and readers)

[EMAIL PROTECTED] (Andrew Sullivan) writes: > Oh, one other thing > > On Thu, Apr 17, 2008 at 12:44:51PM +0800, Craig Ringer wrote: > >> > One way I can think of doing it is to write a seen_log that notes what the >> > client has already seen with a timestamp of (say) 1 minute. Then you can >> >

Re: [GENERAL] table as log (multiple writers and readers)

Oh, one other thing On Thu, Apr 17, 2008 at 12:44:51PM +0800, Craig Ringer wrote: > > One way I can think of doing it is to write a seen_log that notes what the > > client has already seen with a timestamp of (say) 1 minute. Then you can > > say "go forward from this time excluding ids (ids her

Re: [GENERAL] table as log (multiple writers and readers)

On Thu, Apr 17, 2008 at 12:44:51PM +0800, Craig Ringer wrote: > It won't work with multiple concurrent writers. There is no guarantee > that an INSERT with a timestamp older than the one you just saw isn't > waiting to commit. This is pretty unlikely -- I won't say impossible, because I'm sure the

Re: [GENERAL] table as log (multiple writers and readers)

Andrew Sullivan wrote: > On Thu, Apr 17, 2008 at 12:35:33AM +0800, Craig Ringer wrote: >> That's subject to the same issues, because a transaction's >> current_timestamp() is determined at transaction start. > > But clock_timestamp() (and its ancestors in Postgres) don't have that > restriction.

Re: [GENERAL] table as log (multiple writers and readers)

On Thu, Apr 17, 2008 at 12:35:33AM +0800, Craig Ringer wrote: > That's subject to the same issues, because a transaction's > current_timestamp() is determined at transaction start. But clock_timestamp() (and its ancestors in Postgres) don't have that restriction. I dunno that it's enough for yo

Re: [GENERAL] table as log (multiple writers and readers)

brian wrote: I don't mean to rely on *only* the timestamp, but for the reader to remember both the last ID and the timestamp for that particular transaction. When the next read occurs it should check to see if there's an earlier timestamp with a higher ID than that remembered. [snip] Wait-

Re: [GENERAL] table as log (multiple writers and readers)

(I originally missed replying to all here; sorry about the duplicate, Vance, but figured others might be interested. On Wed, Apr 16, 2008 at 1:55 PM, Vance Maverick <[EMAIL PROTECTED]> wrote: > > Another approach would be to queue the log entries in a "staging" table, > so that a single process

Re: [GENERAL] table as log (multiple writers and readers)

Craig Ringer wrote: brian wrote: Use a timestamp column also. That's subject to the same issues, because a transaction's current_timestamp() is determined at transaction start. So, in a situation like this: WRITER 1WRITER 2READER 1 BEG

Re: [GENERAL] table as log (multiple writers and readers)

Vance Maverick wrote: Another approach would be to queue the log entries in a "staging" table, so that a single process could move them into the log. This is fairly heavyweight, but it would guarantee the consistent sequencing of the log as seen by a reader (even if the order of entries in the

Re: [GENERAL] table as log (multiple writers and readers)

Craig Ringer wrote: > brian wrote: > > > Use a timestamp column also. > > That's subject to the same issues. [...] > I don't think it's even OK in the case of a single-statement INSERT (where the > transaction is implicit) and/or with the use of clock_timestamp() ... though > I'm less sure about

Re: [GENERAL] table as log (multiple writers and readers)

brian wrote: Use a timestamp column also. That's subject to the same issues, because a transaction's current_timestamp() is determined at transaction start. So, in a situation like this: WRITER 1WRITER 2READER 1 BEGIN BEGIN

Re: [GENERAL] table as log (multiple writers and readers)

Vance Maverick wrote: I want to create a table with the semantics of a log. There may be multiple concurrent writers, because log entries will be generated by triggers. And there will be multiple concurrent readers -- mostly remote processes watching the log over time. I'd like to guarantee th

[GENERAL] table as log (multiple writers and readers)

I want to create a table with the semantics of a log. There may be multiple concurrent writers, because log entries will be generated by triggers. And there will be multiple concurrent readers -- mostly remote processes watching the log over time. I'd like to guarantee that each of those readers