Re: Using postgres.log file for replication

ioanasoftware(at)yahoo(dot)ca (Ioana Danes) writes:
> I've been wondering if anybody tried to use the postgresql csv log
> file to replicate sql statements. I've been looking into it in the
> past days and after a brief testing it doesn't look bad at all...

It's *plausible*, but you have to ensure that you *never* use any
operations that could break the assumption that it is OK to apply the
queries in "logged order."

1. As has been mentioned separately, references to NOW() will break;
analagous problems will occur for any function that doesn't provide
immutable results, including:

- NOW(), obviously, and any time-based function
- currval('any_sequence')
- random() :-)

Thus, any reasonable usage of DEFAULT values on tables will cause
discrepancies.

2. Further, if there can be multiple requests acting on the database
concurrently, this can make it nigh unto impossible to ensure that
they are applied in a compatible order.

It makes my head hurt a bit to think about the kinds of cases where
this breaks down, but I know it's not difficult for concurrency to
make this break badly.

3. If you ever have queries that create data in nondeterministic
ways, that will cause a discrepancy.

For instance:
insert into table_2 (a,b,c)
select a,b,c from table_1 limit 100;

will NOT be able to be replicated consistently unless there were only
100 tuples to be found.

That being said, the following alteration to that query *could* work
out:
insert into table_2 (a,b,c)
select a,b,c from table_1 order by a,b,c limit 100;

(assuming that the result of the select is, itself, a relation, which
implies that there are no repeated values...)

I'm afraid I don't have sufficiently draconian powers over *our*
developers to ensure that they NEVER do anything that would violate
the above set of requirements.

Nor, frankly, would I want to. I strongly *APPROVE* of them using
NOW() and currval(), and we build applications to be able to support
multiple concurrent users.

The only piece where I might *imagine* I'd want to be "Lord of
Draconia" would be on #3, and, if offered "Draconian Powers," I'd
rather apply those powers to more vital matters :-).
--
output = ("cbbrowne" "@" "cbbrowne.com")
http://www3.sympatico.ca/cbbrowne/slony.html
Signs of a Klingon Programmer #6: "Debugging? Klingons do not
debug. Our software does not coddle the weak."