[postgis-users] Slony and PostGIS problem

[Brian Peschel]

I hadn't really thought of deliberately inserting a bad geometric.  So, 
I just tried it:

insert into ml_polygon (msg_no, rev_no, geom) values (-1, -1, 
ST_SetSRID('POLYGON((-90.869358 38.839444,-90.869358 
38.839447,-90.869358 38.839444))', 4269));
select msg_no, rev_no, st_npoints(geom) from ml_polygon where msg_no = -1;
  msg_no | rev_no | st_npoints
--------+--------+------------
      -1 |     -1 |          3

Then on node 2:
select msg_no, rev_no, st_npoints(geom) from ml_polygon where msg_no = -1;
  msg_no | rev_no | st_npoints
--------+--------+------------
      -1 |     -1 |          3
(1 row)
select msg_no, rev_no, st_astext(geom) from ml_polygon where msg_no = -1;
ERROR:  geometry requires more points

So, slony copied the insert over.

So, I tried a delete:
delete from ml_polygon where msg_no = -1;
DELETE 1
MO=> commit;
COMMIT
MO=> select msg_no, rev_no, st_npoints(geom) from ml_polygon where 
msg_no = -1;
  msg_no | rev_no | st_npoints
--------+--------+------------
(0 rows)

And on node 2:
select msg_no, rev_no, st_npoints(geom) from ml_polygon where msg_no = -1;
  msg_no | rev_no | st_npoints
--------+--------+------------
(0 rows)


This makes me slightly less concerned about the data.  I do not do 
updates of this table.  The only reason I was doing an update here was 
to "fix" the bad geometrics.

Still not sure why the original update didn't work though....

- B

On 03/23/2012 11:10 AM, Paul Ramsey wrote:
> On Fri, Mar 23, 2012 at 6:18 AM, Brian Peschel<brianp at occinc.com>  wrote:
>> Figured I would start here since I suspect this is a PostGIS problem more
>> than a Slon problem.
>>
>> My main node is a Fedora 8, Postgres 8.3.1, Slon 1.2.20, and PostGIS 1.3.
>>   This is being replicated via slon to an Ubuntu 10, Postgres 8.4.11, Slon
>> 1.2.20, and PostGIS 1.4.  My table looks like this:
>> Table "public.ml_polygon"
>>   Column |   Type   | Modifiers
>> --------+----------+-----------
>>   msg_no | integer  | not null
>>   rev_no | integer  | not null
>>   geom   | geometry |
>> Indexes:
>>     "ml_polygon_pkey" PRIMARY KEY, btree (msg_no, rev_no)
>>     "ml_geom_idx" gist (geom)
>> Check constraints:
>>     "enforce_dims_geom" CHECK (ndims(geom) = 2)
>>     "enforce_srid_geom" CHECK (srid(geom) = 4269)
>>
>> I have some bad geoms in the database, basically polygons with invalid point
>> counts:
>>   70650590 |      1 | POLYGON((-90.869358 38.839444,-90.869358
>> 38.839447,-90.869358 38.839444))
>>
>> In PostGIS 1.3, this is perfectly acceptable (even though it is bad).  In
>> 1.4, this doesn't work and a select gives you this error:
>> ERROR:  geometry requires more points
>>
>> which also appears in my Postgres log.  Google tells me this was an issue
>> with early PostGIS 1.4 versions.  Okay.
>>
>> But I need to fix these.  So on my main node, I did this:
>> update ml_polygon set geom = ST_SetSRID('POLYGON((-90.869358
>> 38.839444,-90.869358 38.839447,-90.869358 38.839447,-90.869358 38.839444))',
>> 4269) where msg_no = 70650590 and rev_no = 1;
>>
>> which happily updated my main node.  And nothing showed up on by second
>> node.  No errors messages anywhere I could find.  New inserts in this table
>> in my main node showed up on the second node, so I know slony is working,
>> but this update never showed up.
>>
>> I did a different update (to an existing 16 point polygon) and the updated
>> showed up in my second node.  I was worried that slony wouldn't pick up
>> updates of a geometry column, but this proved to me that wasn't the problem.
>>   I re-did the first insert and, surprise, surprise, it worked fine.
>>
>> So, I tried a different record with the same 3 point problem, and it showed
>> up on the second node.
>>
>> Needless to say, I am very confused.  Not only is the constancy of what
>> works and fails a problem, but the fact the slony copy (apparently) failed
>> with no error messages concerned me also.  Can I trust the data in my second
>> node?
> Based on the description I would think not. If you insert another new
> bad geometry in the master, does it show up on the slave? If the bad
> geometries can't traverse from 1.3 to 1.4 you'll have a systemic chunk
> of missing data.
>
>
>> Any thoughts?
>> - Brian
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

-- 
Brian Peschel
One Call Concepts, Inc
Systems Division

Voice: 414-259-1047 ext. 93
Fax: 414-777-3666