GeoServer won't write to my PostgreSQL updateable view

Question

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

I have a view:

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

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>

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

I tried this:

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.

Where is my mistake?

Jadwiga

1) Please add the table definitions and some data to your question. 2) I appears that you forget the id-field in your update (is it the FK ...?) 3) you should also create rules for the insert and delete cases. 4) the error originates from your framework, which checks the catalogs and concludes that filedata_view is a view (and thinks that views are RO), try to invoke it from the commandline first. 5) always use fully qualified names in rules (eg: where fd.num=NEW.num) – Querulous

Answer 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.

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.

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.

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.

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.

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.

Alleged

All what u say is right. In psql i can insert record in VIEW using RULE. But Geoserver dont understand that and cant insert. I find easy but not rational way: dont use VIEW just create another table and use it in work with geoserver. I hope Geoserver will work better with pSql 9.1/ And thank you to correct my english. – Jadwiga

@KliverMax GeoServer will only improve if you report this problem to them. If they are detecting views and assuming all views are read only it won't improve with PostgreSQL 9.1 . You might want to try using an ON SELECT rule on a table to make a view that GeoServer can't tell is actually a view. – Alleged

Answer 2

Don't use a rule for this, but a trigger. You need at least version 9.1, older versions don't support triggers on views.

A trigger is a specification that the database should automatically execute a particular function whenever a certain type of operation is performed. Triggers can be attached to both tables and views.

On tables, triggers can be defined to execute either before or after any INSERT, UPDATE, or DELETE operation, either once per modified row, or once per SQL statement. UPDATE triggers can moreover be set to fire only if certain columns are mentioned in the SET clause of the UPDATE statement. Triggers can also fire for TRUNCATE statements. If a trigger event occurs, the trigger's function is called at the appropriate time to handle the event.

Rumor is that rules will be EOL in a while.

Ozone

Wow @Frank you are a true living knowledge base as far as Postgres is considered! – Basic

Looks like the OP is using 8.4 - what will be your advice for that version ? – Ventriculus

Yes i can use only 8.4 version. – Jadwiga

"Dont use a rule, use a trigger" sounds too much imperative to me. It is the popular opinion. Rules are not bad, they are just too hard to understand for most people. And very hard to use. But if needed, and used correctly, they can offer a relatively clean alternative to the pinball-machine that can result from trigger abuse. – Querulous

Answer 3

If you have Postgres >= 9.3 you can update features coming from a View with GeoServer, at least if the view is a subset of another table (I don't think this will work with joins or compound fields..).

Here's how: http://osgeo-org.1560.x6.nabble.com/postgresql-postgis-views-and-primary-keys-td3796362.html

This really worked for me!

Plexiglas