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

Sat May 1 20:42:34 PDT 2004

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_celcius::text,temperature_fahrenheit::text,value_temperature_celcius::text,value_temperature_fahrenheit::text,lon::text,lat::text,title::text,institution::text,institution_url::text,institution_dods_url::text,source::text,refs::text,contact::text,report_time_stamp::text,asbinary(force_collection(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