Re: logical changeset generation v6.4

On 10/18/2013 08:50 PM, Andres Freund wrote:
> On 2013-10-18 08:11:29 -0400, Robert Haas wrote:
...
>> 2. If that seems too complicated, how about just logging the whole old
>> tuple for version 1?
> I think that'd make the patch much less useful because it bloats WAL
> unnecessarily for the primary user (replication) of it. I'd rather go
> for primary keys only if that proves to be the contentious point.
>
> How about modifying the selection to go from:
> * all rows if ALTER TABLE ... REPLICA IDENTITY NOTHING|FULL;
> * index chosen by ALTER TABLE ... REPLICA IDENTITY USING indexname
> * [later, maybe] ALTER TABLE ... REPLICA IDENTITY (cola, colb)
> * primary key
> * candidate key with the smallest oid
>
> Including the candidate key will help people using changeset extration
> for auditing that do not have primary key. That really isn't an
> infrequent usecase.
As I understand it for a table with *no* unique index,
the "candidate key" is the full tuple, so if we get an UPDATE for
it then this should be replicated as
"UPDATE first row matching (NOT DISTINCT FROM) all columns"
which on replay side will be equivalent to
CREATE CURSOR ...; FETCH 1 ...; UPDATE ... WHERE CURRENT...'

I know that this will slow down replication, as you can not use direct
index updates internally - at least not easily - but need to let postgreSQL
actually plan this, but such single row update is no faster on origin
either.

Of course when it is a full-table update on a table with no
indexes, then doing the same one tuple at a time is really slow.

--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ