RE: [Geoserver-devel] RE: Geoserver doing entire table scans?

GeoServer GeoServer Developer
,
1

you are assuming ... that you have sufficient transaction isolation that a

near-simultaneous insert won't incorrectly bump up your key number
between your insert and your FID reading query.

Hmmm - isn't this a reasonably safe assumption if the primary key is explicitly declared to be UNIQUE, and in the implementation of sequences, serials, the 'nextval()' and the 'currval()' functions?

Perhaps a related 'interesting' problem is keeping table spatial metadata, like a geometry_columns.bbox, up to date in the case of others trying to perform features transactions.... what happens if one deletes a feature that was on the envelope; what is the new envelope? Maybe some GIST and R-tree wizards out there can help...

Chris

-----Original Message-----
From: Paul Ramsey [mailto:pramsey@anonymised.com]
Sent: Tue 1/11/2005 3:52 PM
To: Gregory S. Williamson
Cc: Jean-Henry Berevoescu; geoserver-devel@lists.sourceforge.net; Michael Leong; Chris Holmes; Chris G. Nicholas
Subject: Re: [Geoserver-devel] RE: Geoserver doing entire table scans?
Well, if you are working against tables with primary keys, geoserver
will be picking up the primary key and using that as the FID. And
primary keys are always set up with indexes. So in many cases your
trick will work. Of course, you are assuming your keys only increment
upwards, and that you have sufficient transaction isolation that a
near-simultaneous insert won't incorrectly bump up your key number
between your insert and your FID reading query.
All in all, it is a nasty problem. I feel stupid, using the key of a
newly inserted row in other rows as a foreign key is something that
happens all the time in all kinds of database applications. Why is this
not obvious and easy? I feel like I am missing something.

On 11-Jan-05, at 3:16 PM, Gregory S. Williamson wrote:

Thanks to Chris Holmes for the swift response from vacationland!

I am not sure if this will be really useful, but tinkering with
postgres a bit found:

gex_vector=# explain analyze select gid from parcels order by gid desc
limit 1;
                                                                    
QUERY PLAN
-----------------------------------------------------------------------
---------
-----------------------------------------------------------------
Limit (cost=0.00..0.42 rows=1 width=4) (actual time=0.022..0.023
rows=1 loops=
1)
   -> Index Scan Backward using parcels_pkey on parcels
(cost=0.00..2960401.54
rows=7091657 width=4) (actual time=0.020..0.020 rows=1 loops=1)
Total runtime: 0.052 ms
(3 rows)

Trying to select min(gid) or max(god) both result in lengthy
sequential scans. This is with postgres 7.4.

This behavior proves to be consistant on columns with indexes on
simple integers, e.g.:
gex_vector=# \d parcels
                                     Table "public.parcels"
   Column | Type | Modifiers
------------+------------------------
+------------------------------------------
----------------
gid | integer | not null default
nextval('public.parcels_gid_seq'::text)
s_pin | character varying(50) |
...

Not sure if you can guarantee knowing/having such a column at your
disposal.

The sequence number trick works superbly within a transaction for
getting the serial id of a newly inserted row, e.g. do the insert and
then ask for curval('public.parcels_gid_seq'::text) is guaranteed to
be accurate even with other threads running against the same table,
but again, only works if you know (or can determine) the sequence
name. A rollback means a skipped number in the sequence but usually
that doesn't matter. If you not in a transaction there would be a
chance I suppose of getting the result of another thread's insert.

Greg

-----Original Message-----
From: Chris Holmes [mailto:cholmes@anonymised.com]
Sent: Tue 1/11/2005 2:27 PM
To: Gregory S. Williamson
Cc: Michael Leong; geoserver-devel@lists.sourceforge.net; Jean-Henry
Berevoescu; Chris G. Nicholas
Subject: Re: [Geoserver-devel] RE: Geoserver doing entire table scans?
So it looks like my hacked solution to a fairly obscure WFS spec
problem
is finally exposed - sorry about the hack.

So the reason is does this is the requirement of the spec to return the
new featureid of the inserted Feature. We knew that our solution would
eventually hit the scalable ceiling, so it looks like it's time to fix
it.
The problem is that there's no default way that we get the featureid
(fid)
- if there's a primary key we use that, if there's not we use the oid,
ect. So the easiest thing to do was to take a diff before and after
the
insert. And thus a table scan, to just get the fids, since after the
insert there would definitely be a new one, and that would the proper
fid.

The easy solution to this problem is if you don't care about getting
the
featureid back from the insert statement. You can just comment out the
addFeatures method of PostgisFeatureStore and it will then inherit from
JDBCFeatureStore and use its addFeature method, which does not try to
get
the proper FID. And actually looking at the code someone already
commented it out on 2.0.x of GeoTools, and it looks like GeoServer
1.2.3
and 1.2.4 picked up the change (I just tested and it didn't do a table
scan and it returned a bogus fid). So if you upgrade to 1.2.3 it
should
fix your problem, as long as you don't actually need the featureid
returned.

But we really should not have put out a release that doesn't follow the
spec correctly. Anyone know a good way to figure out what what the
fid of
an inserted statement is? The execute stuff in the JDBC Statement
says it
returns the 'row count', which I suppose is just how many rows were
affected, not the number of the row affected. I suppose we might be
able
to make a constraint like the proper fid for an insert statement only
works on an autoincrement table, and then hopefully there's a way to
dig
out what the next value would be from JDBC.

I'm on vacation at the moment, so I can't dig into the latter, but it
really should be fixed. If you do need the featureids for your inserts
then you can start digging into it, and I should be able to help out
in a
couple weeks. If not just try using a version later than 1.2.3 (you
probably can just grab the geotools.jar from it), or compile your
geotools
jar with the addFeatures method of PostgisFeatureStore commented out.

best regards,

Chris

On Fri, 7 Jan 2005, Gregory S. Williamson wrote:

What does "Property namespaces returned a null value" mean to anyone ?

Certainly "SELECT gid FROM parcels" will do a full table scan; no
other way to do such a thing ... the question would be why the
geoserver is wanting to do this ... if it is just once as it sets
something upmaybe survivable but obviously this won't be allowable in
runtime.

Greg

-----Original Message-----
From: Michael Leong
Sent: Fri 1/7/2005 3:29 PM
To: geoserver-devel@anonymised.com
Cc: Gregory S. Williamson; Jean-Henry Berevoescu; Chris G. Nicholas
Subject: Geoserver doing entire table scans?
Hi,

We're running geoserver using postgis as the backend. When I try to
add a huge table as a WFS feature via the web interface, I get a http
500 out of memory error.

I've set geoserver from SPEED to FILE. I've also noticed that
Geoserver
appears to do an entire table scan whenever I try to add a new feature
because we see "SELECT GID from xxx" on the postgres log. Here's an
example:

2005-01-07 15:13:56 LOG: duration: 620088.450 ms statement: Select
gid
from parcels

Here's the stack trace:

2005-01-07 15:02:18
StandardContext[/balancer]org.apache.webapp.balancer.BalancerFilter:
init(): ruleChain: [org.apache.webapp.balancer.RuleChain:
[org.apache.webapp.balancer.rules.URLStringMatchRule: Target string:
News / Redirect URL: http://www.cnn.com],
[org.apache.webapp.balancer.rules.RequestParameterRule: Target param
name: paramName / Target param value: paramValue / Redirect URL:
http://www.yahoo.com],
[org.apache.webapp.balancer.rules.AcceptEverythingRule: Redirect URL:
http://jakarta.apache.org]]
2005-01-07 15:02:50 ApplicationDispatcher[/geoserver]
Servlet.service()
for servlet jsp threw exception
javax.servlet.jsp.JspException: Property namespaces returned a null
value
         at
org.apache.struts.taglib.html.OptionsTag.getIterator(OptionsTag.java:
416)
         at
org.apache.struts.taglib.html.OptionsTag.doEndTag(OptionsTag.java:289)
         at
org.apache.jsp.WEB_002dINF.pages.data.namespaces.Select_jsp._jspx_meth
_html_options_0(Select_jsp.java:218)
         at
org.apache.jsp.WEB_002dINF.pages.data.namespaces.Select_jsp._jspx_meth
_html_select_0(Select_jsp.java:191)
         at
org.apache.jsp.WEB_002dINF.pages.data.namespaces.Select_jsp._jspx_meth
_html_form_0(Select_jsp.java:113)
         at
org.apache.jsp.WEB_002dINF.pages.data.namespaces.Select_jsp._jspServic
e(Select_jsp.java:75)
         at
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:94)
         at
javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
         at
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.
java:298)
         at
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:
292)
         at
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:236)
         at
javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
         at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appli
cationFilterChain.java:237)
         at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFi
lterChain.java:157)
         at
org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispa
tcher.java:703)
         at
org.apache.catalina.core.ApplicationDispatcher.doInclude(ApplicationDi
spatcher.java:589)
         at
org.apache.catalina.core.ApplicationDispatcher.include(ApplicationDisp
atcher.java:499)
         at
org.apache.jasper.runtime.JspRuntimeLibrary.include(JspRuntimeLibrary.
java:966)
         at
org.apache.jasper.runtime.PageContextImpl.include(PageContextImpl.java
:581)
         at
org.apache.struts.tiles.TilesUtilImpl.doInclude(TilesUtilImpl.java:
137)
         at
org.apache.struts.tiles.TilesUtil.doInclude(TilesUtil.java:177)
         at
org.apache.struts.taglib.tiles.InsertTag.doInclude(InsertTag.java:756)
         at
org.apache.struts.taglib.tiles.InsertTag$InsertHandler.doEndTag(Insert
Tag.java:881)
         at
org.apache.struts.taglib.tiles.InsertTag.doEndTag(InsertTag.java:473)
         at
org.apache.jsp.WEB_002dINF.pages.layouts.mainLayout_jsp._jspx_meth_til
es_insert_6(mainLayout_jsp.java:1369)
         at
org.apache.jsp.WEB_002dINF.pages.layouts.mainLayout_jsp._jspService(ma
inLayout_jsp.java:513)
         at
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:94)
         at
javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
         at
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.
java:298)
         at
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:
292)
         at
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:236)
         at
javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
         at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appli
cationFilterChain.java:237)
         at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFi
lterChain.java:157)
         at
org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispa
tcher.java:703)
         at
org.apache.catalina.core.ApplicationDispatcher.processRequest(Applicat
ionDispatcher.java:463)
         at
org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDi
spatcher.java:398)
         at
org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDisp
atcher.java:312)
         at
org.apache.struts.action.RequestProcessor.doForward(RequestProcessor.j
ava:1069)
         at
org.apache.struts.tiles.TilesRequestProcessor.doForward(TilesRequestPr
ocessor.java:274)
         at
org.apache.struts.tiles.TilesRequestProcessor.processTilesDefinition(T
ilesRequestProcessor.java:254)
         at
org.apache.struts.tiles.TilesRequestProcessor.processForwardConfig(Til
esRequestProcessor.java:309)
         at
org.apache.struts.action.RequestProcessor.process(RequestProcessor.jav
a:279)
         at
org.apache.struts.action.ActionServlet.process(ActionServlet.java:
1482)
         at
org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:507)
         at
javax.servlet.http.HttpServlet.service(HttpServlet.java:697)
         at
javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
         at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appli
cationFilterChain.java:237)
         at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFi
lterChain.java:157)
         at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperVa
lve.java:214)
         at
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValve
Context.java:104)
         at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java
:520)
         at
org.apache.catalina.core.StandardContextValve.invokeInternal(StandardC
ontextValve.java:198)
         at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextVa
lve.java:152)
         at
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValve
Context.java:104)
         at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java
:520)
         at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.ja
va:137)
         at
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValve
Context.java:104)
         at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.ja
va:117)
         at
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValve
Context.java:102)
         at
org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:
535)
         at
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValve
Context.java:102)
         at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java
:520)
         at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValv
e.java:109)
         at
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValve
Context.java:104)
         at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java
:520)
         at
org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
         at
org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:
160)
         at
org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:
793)
         at
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.proces
sConnection(Http11Protocol.java:702)
         at
org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:
571)
         at
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPo
ol.java:644)
         at java.lang.Thread.run(Thread.java:534)

2005-01-07 15:05:24 StandardWrapperValve[action]: Servlet.service()
for
servlet action threw exception
java.lang.OutOfMemoryError

2005-01-07 15:09:52 StandardWrapperValve[action]: Servlet.service()
for
servlet action threw exception
java.lang.NullPointerException
         at
org.vfny.geoserver.action.data.DataFeatureTypesNewAction.execute(DataF
eatureTypesNewAction.java:77)
         at
org.vfny.geoserver.action.ConfigAction.execute(ConfigAction.java:98)
         at
org.apache.struts.action.RequestProcessor.processActionPerform(Request
Processor.java:484)
         at
org.apache.struts.action.RequestProcessor.process(RequestProcessor.jav
a:274)
         at
org.apache.struts.action.ActionServlet.process(ActionServlet.java:
1482)
         at
org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
         at
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
         at
javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
         at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appli
cationFilterChain.java:237)
         at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFi
lterChain.java:157)
         at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperVa
lve.java:214)
         at
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValve
Context.java:104)
         at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java
:520)
         at
org.apache.catalina.core.StandardContextValve.invokeInternal(StandardC
ontextValve.java:198)
         at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextVa
lve.java:152)
         at
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValve
Context.java:104)
         at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java
:520)
         at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.ja
va:137)
         at
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValve
Context.java:104)
         at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.ja
va:117)
         at
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValve
Context.java:102)
         at
org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:
535)
         at
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValve
Context.java:102)
         at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java
:520)
         at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValv
e.java:109)
         at
org.apache.catalina.core.StandardValveContext.invokeNext(StandardValve
Context.java:104)
         at
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java
:520)
         at
org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
         at
org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:
160)
         at
org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:
793)
         at
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.proces
sConnection(Http11Protocol.java:702)
         at
org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:
571)
         at
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPo
ol.java:644)
         at java.lang.Thread.run(Thread.java:534)

-------------------------------------------------------
The SF.Net email is sponsored by: Beat the post-holiday blues
Get a FREE limited edition SourceForge.net t-shirt from ThinkGeek.
It's fun and FREE -- well, almost....http://www.thinkgeek.com/sfshirt
_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

--

-------------------------------------------------------
The SF.Net email is sponsored by: Beat the post-holiday blues
Get a FREE limited edition SourceForge.net t-shirt from ThinkGeek.
It's fun and FREE -- well, almost....http://www.thinkgeek.com/sfshirt
_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel