Re: [HACKERS] Online DW

On 11 June 2016 at 12:29, Sridhar N Bamandlapally <sridhar(dot)bn1(at)gmail(dot)com>
wrote:

> Ok, let me put this way,
>
> I need every transaction coming from application sync with both production
> and archive db,
> but the transactions I do to clean old data(before 7 days) on production
> db in daily maintenance window should not sync with archive db,
>

Would you please remove pgsql-hackers from the Cc list for future replies?
This isn't specific discussion of a concrete feature patch or detailed
design or bug, so it's more appropriate for pgsql-general.

I think everyone's understood what you want, but you can't just throw
random syntax at the wall and expect someone to magically make it work.
What you propose will not work with PostgreSQL's block based replication
model. It just can't. You can't do this with built-in streaming replication.

You can, however, do roughly what you want with Londiste by (ab)using
session_replication_role so that only some transactions get replicated. It
is something that would be reasonably practical to add to pglogical too; in
fact, you can already set pglogical up so it only replicates INSERTs and
UPDATEs but not DELETEs, there's just no
per-session/per-transaction/per-statement control over that yet. You could
probably achieve it fairly easily with replication origin filtering and a
custom replication origin for things you want to skip replication for
though.

> I say, every online archive db is use case for this.
>
>
Yeah, I don't think anyone's saying it wouldn't be useful. The point is
that your proposal is extremely vague and lacks any useful detail, let
alone a proposed implementation or a patch. I wish PostgreSQL had
autonomous transactions, a shared-across-sessions JVM or Mono runtime with
first-class Java or C# support, built-in commands to dump table definitions
over SQL, and lots more, but it does no good to talk about them unless I'm
willing to implement them or find someone else who will.

You are being pointed to alternatives that would meet your needs, but seem
to be ignoring that because it's not the solution you have already decided
you need for your problem.

I doubt anybody will implement this for you, especially since I don't think
it's really possible in PostgreSQL's block-based physical replication
architecture. So saying what you want repeatedly probably won't achieve
anything. What are you going to do about it?

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services