On Thu, 2012-05-03 at 00:58 -0500, Jim Nasby wrote:
> On 4/29/12 6:03 AM, Simon Riggs wrote:
> >> The DML-WITH-LIMIT-1 is required to do single logical updates on tables
> >> > with non-unique rows.
> >> > And as for any logical updates we will have huge performance problem
> >> > when doing UPDATE or DELETE on large table with no indexes, but
> >> > fortunately this problem is on slave, not master;)
> > While that is possible, I would favour the do-nothing approach. By
> > making the default replication mode = none, we then require a PK to be
> > assigned before allowing replication mode = on for a table. Trying to
> > replicate tables without PKs is a problem that can wait basically.
> >
>
> Something that a in-core method might be able to do that an external one
> can't would be to support a method of uniquely identifying rows in tables
> with no PK's. A gross example (that undoubtedly wouldn't work in the real
> world) would be using TID's.
> A real-world implementation might be based on a hidden serial column.
For logical we don't really need to uniquely identify such rows - it
should sufficient if we just update exactly one of the matching rows.
The way to do this is to put all fields of the OLD.* tuple in the WHERE
clause and then update just one matching row.
IIRC updating (or deleting) CURRENT OF a cursor is currently supported
only in pl/pgsql so this needs to be done using a plpgsql cursor.
If the table has no indexes or index lookup returns lots of rows, then
this is bound to be slow, but in this case it was probably slow on
master too :)
> --
> Jim C. Nasby, Database Architect jim@nasby.net
> 512.569.9461 (cell) http://jim.nasby.net
>