DAL: INSERT-query's devilry

[Val K]

Hi!
I am falling in love with  web2py!!! But I had got a little problem when migrated to 2.9.11-stable+timestamp.2014.09.15.23.35.11.
After submit  INSERT-form, I had got an error:  
<class 'psycopg2.NotSupportedError'> cannot perform INSERT RETURNING on relation "table_view" HINT: You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause.
I use web2py+Postgresql. In summary of my little research I was surprised, that DAL construct  INSERT-query with RETURNING.
It's no problem while INSERT into TABLE, but not  for VIEW (i.e. SQL VIEW, not web2py view)
"If you want to support RETURNING queries on the view, you need to make the rules include RETURNING clauses that compute the view rows. This is usually pretty trivial for views on a single table, but it's a bit tedious for join views"
RETURNING from join view looks like this:
...
RETURNING    ins_table.id,
                   ins_table.f1,
                   ins_table.f2,
                  (SELECT table1.f1 FROM  table1 WHERE  table1.id= ins_table.id),
                  (SELECT table1.f2 FROM  table1 WHERE  table1.id= ins_table.id);

Keep in mind,  that (SELECT table1.f1,  table1.f2 FROM  table1 WHERE  table1.id= ins_table.id) - not works! i.e. оne field  - one SELECT.
RETURNING clause should include all fields from  VIEW, order is important.

The problem is that I have to add  RETURNING clause to INSERT RULE of all VIEWs (if they have one) to except this error.

I suggest following:
include this error in exceptions (in DAL module), if it happens  - use currval(id_sequence) to get ID of new record, i.e. something from earlier versions.

With very best regards

[Val K]

OK guys!

My concrete suggestion:

• Add attrubute _insert_with_returning to Table class
  
• Fix adapter Postgres.py :
  

def _insert(self, table, fields):               table_rname = table.sqlsafe         if fields:             keys = ','.join(f.sqlsafe_name for f, v in fields)             values = ','.join(self.expand(v, f.type) for f, v in fields)             if table._id and table._insert_with_returning:                 self._last_insert = (table._id, 1)                 return 'INSERT INTO %s(%s) VALUES (%s) RETURNING %s;' % (                     table_rname, keys, values, table._id.name)             else:                 self._last_insert = None                 return 'INSERT INTO %s(%s) VALUES (%s);' % (table_rname, keys, values)         else:             self._last_insert             return self._insert_empty(table)

That's All Folks

[Niphlod]

IMHO inserting to a view shouldn't be supported at all.

[Val K]

Thanks for your reply!
You are right, if we are talking about some application with full built-in logic and it would like to be independent on any DBEngine. But IMHO there are a lot of opposite situations: DB-App with many different clients. In this case VIEW is very helpful solution, it minimizes client's thickness and much safer.
It'd be better have a choice, no?

P.S. I've solved my problem (1.5 lines of code), it works fine!

[Massimo Di Pierro]

Can you tell us about the fix?

[Johann Spies]

On 10 March 2015 at 01:51, Val K <valq...@gmail.com> wrote:

It's no problem while INSERT into TABLE, but not  for VIEW

"If you want to support RETURNING queries on the view, you need to make the rules include RETURNING clauses that compute the view rows. This is usually pretty trivial for views on a single table, but it's a bit tedious for join views"
RETURNING from join view looks like this:

In this case I would just use raw sql and not DAL' s insert.

Regards

Johann

--

Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)

[Val K]

OK, here is my fix:

pydal.py/base.py:

TABLE_ARGS=set((... , 'insert_with_returning'))

pydal.py/objects.py:

class Table(object):
    ...
    def __init__(...):
        ...
        self._insert_with_returning=args.get('insert_with_returning', True)
        ...

pydal.py/adapters/postgres.py: