<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
</head>
<body text="#000000" bgcolor="#ffffff">
(I am sending this to both the PostGIS and Slony support lists as I
am not sure of the correct location).<br>
<br>
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<br>
<br>
I created a PostGIS table like this:<br>
<tt>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;<br>
</tt><br>
Notice that geom2 has a repeated point of geom1, but other than that
they are the same:<br>
<tt>select geom1 = geom2, ST_Equals(geom1, geom2),
ST_OrderingEquals(geom1, geom2) from b_temp;<br>
?column? | st_equals | st_orderingequals <br>
----------+----<font color="#3366ff">-</font>------+-------------------<br>
t | t | f<br>
</tt><br>
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.<br>
<br>
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:<br>
<tt>update b_temp set rev_no = 1;<br>
</tt>Looking in sl_log_1, I see:<br>
<tt>log_origin | log_xid | log_tableid | log_actionseq |
log_cmdtype
|
log_cmddata
<br>
------------+----------+-------------+---------------+-------------+----------------------------------------------------------<br>
1 | 82987244 | 217 | 55210696 | U
| "rev_no"='1' where "msg_no"='11459815' and "rev_no"='0'
<br>
</tt><br>
That is about what I expected. Things get interesting if you do a
non-update. For example, do this:<br>
<tt>update b_temp set rev_no = 1;<br>
</tt><br>
Looking in sl_log_1, I see:<br>
<tt>log_origin | log_xid | log_tableid | log_actionseq |
log_cmdtype
|
log_cmddata <br>
------------+----------+-------------+---------------+-------------+----------------------------------------------------------------<br>
1 | 82987244 | 217 | 55210696 | U
| "msg_no"='11459815'</tt><tt> where "msg_no"='11459815' and
"rev_no"='0' </tt><br>
<br>
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:<br>
<tt>update b_temp set geom2 = geom1;</tt><br>
<br>
Doing a point count in node 1 shows the geom changed:<br>
<tt>select st_npoints(geom1), st_npoints(geom2) from b_temp;<br>
st_npoints | st_npoints <br>
------------+------------<br>
4 | 4</tt><br>
<br>
Which is exactly what I expected. But if you look on node 2 (after
slon is started)<br>
<tt>select st_npoints(geom1), st_npoints(geom2) from b_temp;<br>
st_npoints | st_npoints <br>
------------+------------<br>
4 | 5<br>
</tt><br>
which is not what I expected. Looking into sl_log_1 I see:<br>
<tt>log_origin | log_xid | log_tableid | log_actionseq |
log_cmdtype
|
log_cmddata <br>
------------+----------+-------------+---------------+-------------+----------------------------------------------------------------<br>
1 | 82987244 | 217 | 55210696 | U
| "msg_no"='11459815'</tt><tt> where "msg_no"='11459815' and
"rev_no"='0' </tt><br>
<br>
Also, not what I expected.<br>
<br>
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.<br>
<br>
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:<br>
<br>
<tt> _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')<br>
</tt><br>
Thanks for the long read....<br>
<br>
- Brian<br>
<br>
</body>
</html>