GeoServer不会写入我的PostgreSQL可更新视图 [英] GeoServer won't write to my PostgreSQL updateable view

问题描述

接下来是这个先前的问题在PostgreSQL 8.4上，并且在可更新视图方面遇到麻烦.

Following on from this earlier question I'm on PostgreSQL 8.4 and am having trouble with updatable views.

我有一个观点:

CREATE VIEW filedata_view
AS SELECT num, id, ST_TRANSFORM(the_geom,900913) AS the_geom
FROM filedata

并想从我的应用程序抛出Geoserver中对其进行更新.但出现错误:

And want to update it from my application throw Geoserver. But get a error:

<ServiceExceptionReport version="1.2.0" xmlns="http://www.opengis.net/ogc" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.opengis.net/ogc http://schemas.opengis.net/wfs/1.0.0/OGC-exception.xsd">
 <ServiceException> {http://www.opengeospatial.net/cite}filedata_view is read-only    </ServiceException>
</ServiceExceptionReport>

因此PostgresSql中的视图不可更新.我需要创建规则或触发器来更新视图.

So views in PostgresSql are not updatable. I need create a rule or trigger to update the view.

我尝试过:

CREATE OR REPLACE RULE ins_view_2 AS
ON UPDATE TO filedata_view DO INSTEAD  UPDATE filedata SET the_geom=ST_TRANSFORM(NEW.the_geom,70066)
WHERE num=NEW.num

但没有帮助，我仍然遇到相同的错误.

but it didn't help, I'm still getting the same error.

我的错误在哪里?

推荐答案

首先，我完全同意Frank.使用9.1和一个表触发器.但是，该视图和视图都可能无法解决您的问题.

First, I couldn't agree more with Frank. Use 9.1, and a table trigger. However, it's possible that neither that nor a view will solve your problem.

尝试在psql上对手册进行操作UPDATE.如果这样可行，并且如果您使用相同的用户ID与opengeospatial进行连接，那么我想说的问题可能是opengeospatial过于聪明，以至于不利于自己，并且知道"无法更新视图.要么，要么尝试INSERT，而您没有在视图中添加匹配的INSERT规则.

Try doing a manual UPDATE on your view from psql. If that works, and if you connect using the same user ID with opengeospatial, then I'd say the issue could be opengeospatial being too clever for its own good and "knowing" that views can't be updated. Either that, or it's trying an INSERT and you haven't added a matching INSERT rule on your view.

消息"filedata_view是只读的"不是PostgreSQL可能产生的消息.我想知道opengeospatial是使用JDBC元数据(假设它是Java)还是使用INFORMATION_SCHEMA或类似方式来查询架构，是否确定filedata_view是视图，并断定它不能更新它.

The message "filedata_view is read-only" isn't a message PostgreSQL may produce. I'm wondering if opengeospatial is using JDBC metadata (assuming it's Java) or INFORMATION_SCHEMA or similar to query the schema, is determining that filedata_view is a view, and is concluding that it therefore can't update it.

如果它是来自PostgreSQL的消息，它将改为:

If it were a message from PostgreSQL it would instead say:

# UPDATE customer_v SET customer_number = 1234; 
ERROR:  cannot update view "the_view" 
HINT:  You need an unconditional ON UPDATE DO INSTEAD rule or an INSTEAD OF UPDATE trigger.

在postgresql.conf中启用log_statement = 'all'并重新加载postgresql可能很有帮助.重新测试，然后在日志中查看opengeospatial到底在做什么.

It might be informative to enable log_statement = 'all' in postgresql.conf and reload postgresql. Re-test, then look in the logs see what exactly opengeospatial is doing.

如果事实证明它正在检测视图，则可以通过将ON SELECT规则添加到空表来解决该问题.该表将像视图一样工作，但是GeoServer无法将其视为一个视图，并且可能同意写入该视图.

If it turns out it's detecting a view, you might be able to work around the problem with an ON SELECT rule added to an empty table. The table will work just like a view, but GeoServer won't be able to tell it is a view and might agree to write to it.

这篇关于GeoServer不会写入我的PostgreSQL可更新视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！