Deleting coverage when there is only one in petascopedb fails

[Dimitar Misev]

# DELETE FROM ps_coverage WHERE name='AGDC';
ERROR:  query returned no rows
CONTEXT:  PL/pgSQL function select_field(text,text,anyelement,text) line 14 at EXECUTE statement
PL/pgSQL function drop_quantity(integer) line 54 at assignment
SQL statement "SELECT drop_quantity(OLD.field_id)"
PL/pgSQL function range_component_drop() line 28 at PERFORM

[Bang Pham Huu]

Hi,

In here, what is the status? It is fixed or not because when I try to reproduce with PostgreSQL as Dimitar tried before (i.e. delete an coverage from petascope database in command line), it worked even when the coverage's name is not in table "ps_coverage". (just return DELETE 0 without any problem).

Thanks,

[Vlad Merticariu]

This problem does not appear anymore. However, connected to this, if petascopedb contains 1 single coverage and you try to delete it (DELETE FROM ps_coverage WHERE name='myCov' the query fails. If there is more than 1 coverage in the database, delete works without issues.

[Dimitar Misev]

Here's an ingredients file and sample data, can you try to reproduce it?

​http://kahlua.eecs.jacobs-university.de/~dmisev/ticket_969.tar.gz

[Dimitar Misev]

Ok Bang, so to reproduce:

• dropdb petascopedb
• update_petascopedb.sh
• import data at ​​http://kahlua.eecs.jacobs-university.de/~dmisev/ticket_969.tar.gz with wcst_import.sh
• try to delete coverage AGDC

[Bang Pham Huu]

Ok, Dimitar - sorry for late replying . Yes, it is strange when could not delete by Postgresql with your data. I will try to find out.

select * from ps_coverage;
 id | name | gml_type_id | native_format_id | description_id 
----+------+-------------+------------------+----------------
  1 | AGDC |           5 |              100 |               
(1 row)

petascopedb=# delete from ps_coverage where name='AGDC'
;
ERROR:  query returned no rows
CONTEXT:  PL/pgSQL function select_field(text,text,anyelement,text) line 14 at EXECUTE statement
PL/pgSQL function drop_quantity(integer) line 54 at assignment
SQL statement "SELECT drop_quantity(OLD.field_id)"
PL/pgSQL function range_component_drop() line 28 at PERFORM
petascopedb=# 

[Bang Pham Huu]

ok, at least I have solution for this kind of error (it is pretty basic but with a messing up so cost me one day). The problem is when delete the coverage as I traced back it will follow by:
+ Trigger: range_component_drop.
+ Function: drop_quantity
+ Function: select_field

And in function select_field just return the uom_id from table ps_quantity. But the query will not work inside this function

   _qry := 'SELECT ' || quote_ident(selected_field) ||
                 ' FROM ' || quote_ident(selected_table) ||
                      ' ' || where_clause;
        RAISE DEBUG '%: %', ME, _qry;

        EXECUTE _qry INTO STRICT _result_value;
        RETURN  _result_value;

Even you can see the value of _qry is just "select uom_id from ps_quantity where id = 12" and get result from pgsql but when query from this function the result is NULL. And that is the error (this error will not happen if there are 2 or more coverages inside ps_coverage).

The solution from me is updated with modern PSQL (in here there are difference between plain SQL and PLPGSQL which we are using - this is important to avoid mixing up). And this dynamic query work with update like this

EXECUTE format('SELECT %I FROM %I %I', 
selected_field, selected_table, where_clause) 
into _result_value;

I will check a few test cases (you can do also) and make a new patch for this (update file ultilities.sql with new query).

[Dimitar Misev]

A ok so it has nothing to do with wcst_import, it's because there is only one coverage in petascopedb. Great you can make the patch.

[Bang Pham Huu]

Good Dimitar when you changed the title also (it is the problem with petascopedb and mechanism of a lot trigger and functions in Postgresql behind this). I have submitted a patch, please test by yourself and I will close ticket in here as it has passed tests from me.