Re: How to create read-only view on 9.3

On Tue, Aug 13, 2013 at 5:37 AM, Tomonari Katsumata
<katsumata.tomonari@po.ntts.co.jp> wrote:
> Hi Szymon,
>
> Thank you for response.
>
>
>>> Could you show an example?
>>
> I do below things on one server.
> The path to database cluster and port are
> different with each other.
>
> [9.2.4]
> initdb --no-locale -E UTF8
> pg_ctl start
> createdb testdb
> psql testdb -c "create table tbl(i int)"
> psql testdb -c "insert into tbl values (generate_series(1,10))"
> psql testdb -c "create view v as select * from tbl"
>
> [9.3beta2]
> pg_dump -p <port of 9.2.4> testdb > /tmp/92dmp.dmp
> initdb --no-locale -E UTF8
> pg_ctl start
> createdb testdb
> psql testdb -f /tmp/92dmp.dmp
>
>
> After all, the view v became updatable view.

I chatted about this on IRC for a bit.  Apparently, updatability of
views is a mandatory feature in the sql standard and by relying on the
read-only-ness you were relying on non-standard behavior essentially.
I admit this is a pretty big pain (and I'm a real stickler for
backwards compatibility) but it's pretty hard to argue with the
standard.   Workarounds are to revoke various privileges.

merlin