[Mapserver-users] RE: [postgis-users] RE: gone are my query results

Sat May 1 21:08:54 PDT 2004

Got your back, Charlton :)
The problem is that your PostgreSQL oids are larger than the mapserver  
row number variable can store. The oid is larger than a signed 32 bit  
integer can store.
So, solution.
Do not use oid as your "unique" field. Make use of the "using unique"  
clause in your data statement, and reference a different unique field  
in your table. Make sure that field is indexed. If you have loaded your  
data with shp2pgsql, you probably have a 'gid' field you can use that  
is a primary key (and therefore already indexed).
Yours,
Paul

On Saturday, May 1, 2004, at 08:42 PM, Charlton Purvis wrote:

> OK, Greg, I think we might be onto something.  But it still looks very  
> grim.
>
> I turned on all the logging, and I notice that MS sends it this query:
>
> LOCATION:  pg_parse_query, postgres.c:464
> LOG:  00000: statement: DECLARE mycursor BINARY CURSOR FOR SELECT  
> temperature_celcius::text,asbinary(force_collection(force_2d(the_geom)) 
> ,'NDR'),OID::text from sst_map WHERE  
> (date_trunc('hour',report_time_stamp + interval '1 hour' * -04) =  
> date_trunc('hour',timestamp without time zone '2004-05-01 22:00:00')  
> and (label_z <= 5 or label_z is null)) and (the_geom && setSRID(  
> 'BOX3D(-83.0454044480208 24.3838940085927,-82.6541628156105  
> 24.775135641003)'::BOX3D,find_srid('','sst_map','the_geom') ))
>
> OK.  That's good.  Because if I stick the SELECT part at a psql  
> prompt, I get back the row I want.  And I also notice that its OID is  
> 2185295089.
>
> But if I look at the last sql statement in the log, it's another  
> cursor, but the OID is wrong.
>
> LOCATION:  pg_parse_query, postgres.c:464
> LOG:  00000: statement: DECLARE mycursor BINARY CURSOR FOR SELECT  
> station_id::text,time_stamp::text,z::text,label_z::text,temperature_cel 
> cius::text,temperature_fahrenheit::text,value_temperature_celcius::text 
> ,value_temperature_fahrenheit::text,lon::text,lat::text,title::text,ins 
> titution::text,institution_url::text,institution_dods_url::text,source: 
> :text,refs::text,contact::text,report_time_stamp::text,asbinary(force_c 
> ollection(force_2d(the_geom)),'NDR') from sst_map WHERE OID =  
> 2147483647
>
> So had they kept the OID, I would be in good shape.
>
> This isn't looking good!!!
>
> Charlton
>
> 	-----Original Message-----
> 	From: Gregory S. Williamson [mailto:gsw at globexplorer.com]
> 	Sent: Sat 5/1/2004 11:11 PM
> 	To: PostGIS Users Discussion; mapserver-users at lists.gis.umn.edu
> 	Cc:
> 	Subject: RE: [postgis-users] RE: gone are my query results
> 	
> 	
>
>
> 	If you turn on the logging of statements in postgres and run the  
> mapserver side of things, does the query show up in the logs ?
> 	
> 	If the query does not appear then I'd suspect networking or  
> permissions or some darn thing keeping postgres from ever seeing the  
> query.
> 	
> 	It it does show up then maybe the problem is in the return trip to MS  
> or in the processing of that data (perhaps your OID suspicions), since  
> it sounds as if the query itself can be processed.
> 	
> 	Any changes to *anything* on either server and anything in between ?  
> (not likely but always worth ruling out)
> 	
> 	Or, shot in the dark, something messing with TOLERANCE so no points  
> are selected in a query ?
> 	
> 	Very puzzling, I agree.
> 	
> 	Greg W.
> 	
> 	-----Original Message-----
> 	From:   Charlton Purvis on behalf of Charlton Purvis
> 	Sent:   Sat 5/1/2004 7:59 PM
> 	To:     PostGIS Users Discussion; mapserver-users at lists.gis.umn.edu
> 	Cc:
> 	Subject:        RE: [postgis-users] RE: gone are my query results
> 	Thanks for your reply, Gregory.  I'm forwarding this onto the MS  
> folks to see if they have any pearls of wisdom.
> 	
> 	Everything looks great in the db.  If I render a map, I see all the  
> points correctly.  If I take that query and plug it in to the command  
> line, I see the rows.  But the moment I try to do either a cgi query,  
> WFS query, or a PHP query, I get nothing back.  No error message.
> 	
> 	I'm actually getting panicky which isn't any fun.  Has anyone seen  
> anything like this before?
> 	
> 	Queries rely on OID's and SRID's.  Is there any way they could have  
> gotten mucked up?  Can I somehow refresh them?
> 	
> 	Thanks,
> 	
> 	Charlton
> 	
> 	-----Original Message-----
> 	From: Gregory S. Williamson [mailto:gsw at globexplorer.com]
> 	Sent: Sat 5/1/2004 6:20 PM
> 	To: PostGIS Users Discussion
> 	Cc:
> 	Subject: [postgis-users] RE: gone are my query results
> 	
> 	
> 	
> 	
> 	If you do the query directly to postgres (via psql or pgAdmin or  
> whatever) does the query return results ?
> 	
> 	If so, then my guess is that this is not a database error, at least  
> not directly. If ot then yes, it would seem that somehow the postGIS  
> itself has gottent snarfled.
> 	
> 	The change you made relating the geometryColumns doesn't sound to me  
> like it should effect queries unless somehow the SRID info for the  
> tables in your mapserver queries got clobbered; should be easy enough  
> to confirm that this data is ok.
> 	
> 	I am not familiar enough with PHP and mapserver to have a clue; does  
> the mapserver log itself show anything ?
> 	
> 	Not much help, I'm afraid, but perhaps posting this to the MMS list  
> would add some more knowledge to the issue at hand ?
> 	
> 	Greg Williamson
> 	DBA
> 	GlobeXplorer LLC
> 	
> 	-----Original Message-----
> 	From:   Charlton Purvis on behalf of Charlton Purvis
> 	Sent:   Sat 5/1/2004 2:46 PM
> 	To:     PostGIS Users Discussion
> 	Cc:
> 	Subject:        gone are my query results
> 	Hi, folks:
> 	
> 	Bizarre and unnerving problem here.
> 	
> 	Up until last night, mapping was fine and point-query was fine.  Now  
> it appears that the mapping is still great, but the point query and  
> WFS are toast.  I don't get any errors in pgsql.log or php.log.  I get  
> . . . nothing.  Even going mapserv cgi query returns empty for a query  
> I know should work.
> 	
> 	Maybe I'm being taught a lesson.  In a separate database, I noticed  
> that addgeometrycolumn was taking too long -- I create about 400  
> smaller tables per day.  So I commented out fix_geometry_columns();  
> from the addgeometrycolumn function and did it once I had finished  
> creating all my new tables.  So am I paying the price?  Has this  
> corrupted this database as well as my other?
> 	
> 	If all this is OID stuff, is there any way to refresh all OID's?
> 	
> 	Restarted . . . yes.  VACUUM and ANALYZE'd . . . yes.
> 	
> 	Thanks,
> 	
> 	Charlton
> 	
> 	
> 	
> 	_______________________________________________
> 	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
> 	
>
>
> _______________________________________________
> Mapserver-users mailing list
> Mapserver-users at lists.gis.umn.edu
> http://lists.gis.umn.edu/mailman/listinfo/mapserver-users
>
      Paul Ramsey
      Refractions Research
      Email: pramsey at refractions.net
      Phone: (250) 885-0632