Opened 2 years ago

Closed 22 months ago

#969 closed defect (fixed)

Deleting coverage when there is only one in petascopedb fails

Reported by: dmisev Owned by: bphamhuu
Priority: major Milestone: 9.1.x
Component: petascope Version: development
Keywords: Cc: mdumitru, bphamhuu
Complexity: Medium

Description

# 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

Change History (10)

comment:1 in reply to: ↑ description Changed 23 months ago by bphamhuu

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,

comment:2 Changed 23 months ago by bphamhuu

  • Cc bphamhuu added

comment:3 Changed 23 months ago by vmerticariu

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.

comment:4 Changed 23 months ago by dmisev

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

comment:5 Changed 23 months ago by dmisev

Ok Bang, so to reproduce:

comment:6 Changed 22 months ago by bphamhuu

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=# 

comment:7 Changed 22 months ago by bphamhuu

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).

comment:8 Changed 22 months ago by dmisev

  • Summary changed from Deleting coverage ingested with wcst_import fails to Deleting coverage when there is only one in petascopedb fails

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.

comment:9 Changed 22 months ago by bphamhuu

  • Owner changed from vmerticariu to bphamhuu
  • Status changed from new to assigned

comment:10 Changed 22 months ago by bphamhuu

  • Resolution set to fixed
  • Status changed from assigned to closed

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.

Note: See TracTickets for help on using tickets.