Re: logical changeset generation v3 - comparison to Postgres-R change set format

On 11/17/2012 03:00 PM, Markus Wanner wrote:
> On 11/17/2012 02:30 PM, Hannu Krosing wrote:
>> Is it possible to replicate UPDATEs and DELETEs without a primary key in
>> PostgreSQL-R
> No. There must be some way to logically identify the tuple.
It can be done as selecting on _all_ attributes and updating/deleting
just the first matching row

create cursor ...
select from t ... where t.* = (....)
fetch one ...
delete where current of ...

This is on distant (round 3 or 4) roadmap for this work, just was
interested
if you had found any better way of doing this :)

Hannu

> Note,
> though, that theoretically any (unconditional) unique key would suffice.
> In practice, that usually doesn't matter, as you rarely have one or more
> unique keys without a primary.
>
> Also note that the underlying index is useful for remote application of
> change sets (except perhaps for very small tables).
>
> In some cases, for example for n:m linking tables, you need to add a
> uniqueness key that spans all columns (as opposed to a simple index on
> one of the columns that's usually required, anyway). I hope for
> index-only scans eventually mitigating this issue.
>
> Alternatively, I've been thinking about the ability to add a hidden
> column, which can then be used as a PRIMARY KEY without breaking legacy
> applications that rely on SELECT * not returning that primary key.
>
> Are there other reasons to want tables without primary keys that I'm
> missing?
>
> Regards
>
> Markus Wanner