[postgis-users] Problem with Slony implementation of PostGIS

[Brian Peschel]

(I am sending this to both the PostGIS and Slony support lists as I am 
not sure of the correct location).

I have run into a problem with how PostGIS and Slony are reacting with 
each other.  Let me say upfront, I am a PostGIS newbee and know just 
about nothing about Slony.  And I know this email is long, but I want to 
make sure I included everything.  This is on a Posgtres 8.3.1, PostGIS 
1.3, and Slony 1.2.20

I created a PostGIS table like this:
create table b_temp as select 11459815 as msg_no, 0 as rev_no, 
GeometryFromText('POLYGON((1 1, 2 2, 3 3, 1 1))') as geom1,  
GeometryFromText('POLYGON((1 1, 2 2, 3 3, 3 3, 1 1))') as geom2;

Notice that geom2 has a repeated point of geom1, but other than that 
they are the same:
select geom1 = geom2, ST_Equals(geom1, geom2), ST_OrderingEquals(geom1, 
geom2) from b_temp;
  ?column? | st_equals | st_orderingequals
----------+-----------+-------------------
  t        | t         | f

I then slon'd this table to another node, but didn't start slony on the 
other node so I could look at slony log tables.

Apparently, on an update, slon looks at the data before and after and 
compares if anything has changed.  Then sets the update command for the 
second node appropriately.  Say for example:
update b_temp set rev_no = 1;
Looking in sl_log_1, I see:
log_origin | log_xid  | log_tableid | log_actionseq | log_cmdtype 
|                                                                                                      
log_cmddata
------------+----------+-------------+---------------+-------------+----------------------------------------------------------
           1 | 82987244 |         217 |      55210696 | U           | 
"rev_no"='1' where "msg_no"='11459815' and "rev_no"='0'

That is about what I expected.  Things get interesting if you do a 
non-update.  For example, do this:
update b_temp set rev_no = 1;

Looking in sl_log_1, I see:
log_origin | log_xid  | log_tableid | log_actionseq | log_cmdtype 
|                                                                                                      
log_cmddata
------------+----------+-------------+---------------+-------------+----------------------------------------------------------------
           1 | 82987244 |         217 |      55210696 | U           | 
"msg_no"='11459815'where "msg_no"='11459815' and "rev_no"='0'

Again, not too surprising as the data in the table before and after the 
update are the same.  The problem comes in with updating the geometric.  
Say I want to remove the repeated point in geom2.  So I do this update:
update b_temp set geom2 = geom1;

Doing a point count in node 1 shows the geom changed:
select st_npoints(geom1), st_npoints(geom2) from b_temp;
  st_npoints | st_npoints
------------+------------
           4 |          4

Which is exactly what I expected.  But if you look on node 2 (after slon 
is started)
select st_npoints(geom1), st_npoints(geom2) from b_temp;
  st_npoints | st_npoints
------------+------------
           4 |          5

which is not what I expected.  Looking into sl_log_1 I see:
log_origin | log_xid  | log_tableid | log_actionseq | log_cmdtype 
|                                                                                                      
log_cmddata
------------+----------+-------------+---------------+-------------+----------------------------------------------------------------
           1 | 82987244 |         217 |      55210696 | U           | 
"msg_no"='11459815'where "msg_no"='11459815' and "rev_no"='0'

Also, not what I expected.

I believe the problem comes back to the PostGIS = operator that slony is 
using to compare the before and after data.  Because the PostGIS = 
operator ignores directionality, these two geometrics are considered the 
same even though they are not.  This causes issues where the data in my 
secondary node is different that the data in my primary node even though 
the two nodes are slon'd.  Which, in my mind, is a huge issue.

So, am I missing something?  Is there some way to get slony to use The 
PostGIS function st_orderingequals() rather than = or st_equals()?  Or 
is there some way to get slony to do a binary bit comparison of the two 
geometrics?  if it helps, this is the slony trigger on the table in the 
master database:

     _ks_slony_logtrigger_217 AFTER INSERT OR DELETE OR UPDATE ON b_temp 
FOR EACH ROW EXECUTE PROCEDURE _ks_slony.logtrigger('_ks_slony', '217', 
'kkv')

Thanks for the long read....

- Brian

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120327/4fb92e4d/attachment.html>