On 5/23/2005 5:06 PM, Sven Willenberger wrote:

> PostgreSQL8.0.3 and slony 1.0.5
> 
> I have a smallish table (< 1 million rows) with a primary key that is
> made up of three fields, let's call them group,name,store; Now if on the
> origin I run "DELETE FROM mytable WHERE group = 3;", I see in the
> sl_log_1 file the statement has now been expanded to include the full
> primary key eseentially making several delete statements, i.e.
> DELETE FROM mytable WHERE group = '3' AND name = 'fred' and store =
> '56';
> DELETE FROM mytable WHERE group = '3' AND name = 'martha' and store =
> '86';
> DELETE FROM mytable WHERE group = '3' and name = 'george' and store =
> '56';
> 
> For small delete sets this is tolerable, but when I have a 100k row
> delete this results in replication almost grinding to a halt while the
> subscriber issues 100k delete commands instead of the rather simple
> DELETE FROM mytable WHERE group = '3';
> 
> I guess my question is, when only one of the fields of the primary key
> is used as a condition for a delete statement, is there a way to have
> slony issue just that simple command rather than expand the statement to
> include all the columns of the primary key?

The general problem with this kind of SQL duplication is to filter out 
the "not so simple" statements. Think what happens with a

   DELETE FROM mytable WHERE last_used < (now() - '1 year'::interval)

The affected set of tuples depends on the actual machine clock when it 
is executed. So it becomes a little more complicated than just looking 
at what fields are used in a condition.

Anyhow, even if we solve that by knowing how to identify things that 
must be "expanded", we have a totally different show stopper. How do you 
capture that original SQL statement in the first place? There is no way 
to setup any sort of trigger or rule that can see your delete statement. 
Actually, we would also need access to the statements parsetree, since 
otherwise we would have to create a full SQL parser that is backward 
compatible with all supported PostgreSQL versions at the same time.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck at Yahoo.com #