Thread: still problem: inserting into writeable views

Hi everybody,

I know this has been written about before, but no solution has been
given. Writing (insert, update, delete) to a view is generally not
possible in PostgreSQL. But if rules have been set up to redirect write
access to a view to some other (real) table, it works fine. Even using
Squirrel SQL, which has been written in Java and uses
org.postgresql.Driver, can write to writeable views.

Using JDBC (org.postgresql.Driver) produces an error
org.postgresql.util.PSQLException: FEHLER: kann nicht in eine Sicht einfügen
(German version, translates as 'ERROR: cannot insert into a view')

Why can't the JDBC driver just leave the decision whether a table/view
is writeable or not to the database instead of interfering?

Regards,
Holger

--
Holger Jakobs, Bergisch Gladbach, Germany
Telefon (0 22 02) 81 71 57, mobil (01 78) 9 75 90 12

Mails sollte man signieren und nach Möglichkeit verschlüsseln
siehe http://www.gnupg.org/gph/de/manual/f20.html

Holger Jakobs wrote:

> Using JDBC (org.postgresql.Driver) produces an error
> org.postgresql.util.PSQLException: FEHLER: kann nicht in eine Sicht einfügen
> (German version, translates as 'ERROR: cannot insert into a view')
>
> Why can't the JDBC driver just leave the decision whether a table/view
> is writeable or not to the database instead of interfering?

I don't see that error message in the JDBC driver's de.po, and there's
no equivalent check or message in the driver that I can see. Are you
sure the error is generated by the driver and not by the backend?

The driver does not modify your INSERTs if you construct & execute them
as queries/updates.

If you are generating your INSERTs implicitly via use of an updateable
ResultSet, then the only extra requirement enforced by the driver is
that it can parse the original SELECT to etermine the table/view to update.

So as far as I know, the driver *does* leave this decision up to the
server. Why do you think this is a driver problem? Do you have a
testcase demonstrating it?

-O

Oliver Jowett wrote:
> Holger Jakobs wrote:
>
>> Using JDBC (org.postgresql.Driver) produces an error
>> org.postgresql.util.PSQLException: FEHLER: kann nicht in eine Sicht
>> einfügen
>> (German version, translates as 'ERROR: cannot insert into a view')
>>
>> Why can't the JDBC driver just leave the decision whether a table/view
>> is writeable or not to the database instead of interfering?
>
> I don't see that error message in the JDBC driver's de.po, and there's
> no equivalent check or message in the driver that I can see. Are you
> sure the error is generated by the driver and not by the backend?

I just checked the server's de.po and this is almost certainly a
backend-generated error, not a driver-generated error.
src/backend/po/de.po has:

#: rewrite/rewriteHandler.c:1811
msgid "cannot insert into a view"
msgstr "kann nicht in eine Sicht einfügen"

-O