Database views fail silently on function fields

[Martin Collins]

I am creating database views as a basis for reports. I've done three that work fine. The fourth does not. When I click on its menu item I get this error:

ProgrammingError: relation "citrus_fruit_received_report" does not exist
LINE 1: SELECT "citrus_fruit_received_report".id FROM "citrus_fruit_...

Sure enough, the view is not in the database.
Now I've gone through my code, the query works fine in pgadmin, if I make _auto=True I get a working object, the field names and types all match.

Stepping through in the debugger I see that the view is created in the database but then it gets dropped again. I cannot see why, or how. What could cause this?

[Martin Collins (mkc-steadfast)]

I have discovered that it is a function field in my query that causes this problem, even though it is stored and PostgreSQL has no problem making a view with it.
I think this should be allowed, and furthermore a meaningful message should be given if a (non-stored) function field is used in a view query.

I'm running 6.0.3 BTW.

[xrg (xrg)]

On Saturday 03 September 2011, you wrote:
> Public bug reported:
>
> I am creating database views as a basis for reports. I've done three
> that work fine. The fourth does not. When I click on its menu item I get
> this error:
>
> ProgrammingError: relation "citrus_fruit_received_report" does not exist
> LINE 1: SELECT "citrus_fruit_received_report".id FROM "citrus_fruit_...
>

Let me try to /guess/ from your sayings: (if I'm wrong, please correct me)

you have a real orm model like:
 class foo(osv.osv):
   _columns {
      'name': fields.char('Name', ...),
      'frob': fields.function(some_fn, 'Fr-ob', store=False ...),
  }

and then you define a view/report like:
  class bar(osv.osv):
    _auto = False,

    _columns = {
       'name': fields.char('Name of foo'),
       'frob': fields.char('Content of foo frob' ...),
    }

    def _init(self, cr):
      cr.execute(" CREATE VIEW bar AS SELECT name, frob FROM foo ")
      ......

Well, if it is so, this is not a bug, but a missing ORM API implementation.
The framework is NOT supposed to check your "bar" view, you are manually
creating it with a direct SQL query and you are 100% responsible for it.

I agree that the framework, in some future version, _should_ provide something
more clever for those views. The current implementation is naive and has
already allowed many mishaps with the views. Then, the "holy grail" question
is how to mix those function fields and SQL code. (Dr. Ferdinand, are you
reading this? )

[Martin Collins (mkc-steadfast)]

> 'frob': fields.function(some_fn, 'Fr-ob', store=False ...),

No, store=True.

> you are manually creating it with a direct SQL query and you are 100% responsible
> for it.

Indeed, that's why I construct queries in pgadmin before inserting them into my OpenERP code.

[Ferdinand (office-chricar)]

see also
https://bugs.launchpad.net/bugs/853198

as views seem to be necessary for all sort of analytics and reports a modification of a table should not fail because of a dependent view .