Opened 10 years ago

Closed 7 years ago

#672 closed defect (fixed)

v.distance -a with upload=to_attr and table= option: problems in table creation and value upload

Reported by: mlennert Owned by: grass-dev@…
Priority: major Milestone: 6.4.3
Component: Vector Version: unspecified
Keywords: v.distance Cc:
CPU: Unspecified Platform: Unspecified

Description

When trying to create a distance matrix and upload that to a table, I've stumbled upon what seems to be a very old bug in v.distance:

1) The table cannot be created because of a missing TO_ATTR: case in the switch statement in vector/v.distance/main.c, line 790.

2) Even when such a TO_ATTR: case is added, the column in the new table is filled with NULL values. I have not been able, yet, to figure out where this problem comes from.

Attachments (1)

v.distance.diff (1.5 KB) - added by mlennert 9 years ago.
patch allowing upload of to_attr data to newly created table

Download all attachments as: .zip

Change History (8)

comment:1 Changed 10 years ago by hamish

Keywords: v.distance added

comment:2 in reply to:  description ; Changed 9 years ago by mlennert

Replying to mlennert:

When trying to create a distance matrix and upload that to a table, I've stumbled upon what seems to be a very old bug in v.distance:

1) The table cannot be created because of a missing TO_ATTR: case in the switch statement in vector/v.distance/main.c, line 790.

2) Even when such a TO_ATTR: case is added, the column in the new table is filled with NULL values. I have not been able, yet, to figure out where this problem comes from.

How to reproduce bug in NC demo data set:

g.copy vect=hospitals@PERMANENT,myhosp
v.distance from=myhosp to=myhosp upload=cat,dist,to_attr to_column=CANCER column=to_cat,dist,to_cancer table=distance_cancer

The attached patch solves 1) & 2) for dbf and postgresql backends, but not 2) for SQLite. When using an SQLite backend, the trouble seems to come from the fact that the same database and table are accessed twice. Maybe some playing around with db_close_database_shutdown_driver might help, but I haven't had the opportunity to play around with that. Anybody have any hints about this ?

In any case I find the attached patch useful for the backends where it works. It's against devel6, but should be straightforward to port to trunk.

Moritz

Changed 9 years ago by mlennert

Attachment: v.distance.diff added

patch allowing upload of to_attr data to newly created table

comment:3 in reply to:  2 Changed 9 years ago by mlennert

Replying to mlennert:

When using an SQLite backend, the trouble seems to come from the fact that the same database and table are accessed twice. Maybe some playing around with db_close_database_shutdown_driver might help, but I haven't had the opportunity to play around with that. Anybody have any hints about this ?

I can now confirm that this is a database locking issue. When doing

g.copy vect=myhosp,myhosp2
cp $GISDBASE/$MAPSET/sqlite.db ~/
v.db.connect -o myhosp2 driver=sqlite database=~/sqlite.db key=cat table=myhosp2
v.distance from=myhosp to=myhosp2 upload=cat,dist,to_attr to_column=CANCER column=to_cat,dist,to_cancer table=distance_cancer

it works, i.e distance_cancer contains values in the to_cancer column.

I would imagine that this kind of sqlite database locking will be a problem in other modules as well, so this probably needs a wider solution than just within this module. Anyone out there with enough experience with SQLite to attack this ? This is very important if SQLite is to become the default db backend...

Moritz

comment:4 Changed 7 years ago by neteler

Milestone: 6.4.06.4.3

Potential answer to the SQLite locking problem:

On Tue, Jul 17, 2012 at 11:58 AM, Markus Metz <markus.metz.giswork@…> wrote:

"When SQLite creates a journal file on Unix, it opens the directory that contains that file and calls fsync() on the directory, in an effort to push the directory information to disk." [0]

If another process is modifying files or just keeping files open in this directory while sqlite calls fsync(), opening the sqlite database will fail.

Markus M

[0] http://www.sqlite.org/lockingv3.html

comment:5 in reply to:  4 Changed 7 years ago by mlennert

Replying to neteler:

Potential answer to the SQLite locking problem:

On Tue, Jul 17, 2012 at 11:58 AM, Markus Metz <markus.metz.giswork@…> wrote:

"When SQLite creates a journal file on Unix, it opens the directory that contains that file and calls fsync() on the directory, in an effort to push the directory information to disk." [0]

If another process is modifying files or just keeping files open in this directory while sqlite calls fsync(), opening the sqlite database will fail.

Markus M

IIUC, MarkusM has fixed this with r52770. At least for me, in grass7, this seems to work. Probably should be backported, so leaving this open for now.

Moritz

comment:6 Changed 7 years ago by neteler

Fixed in devbr6 and relbr64 in r53318 and r53319. Can the ticket be closed?

comment:7 in reply to:  6 Changed 7 years ago by mlennert

Resolution: fixed
Status: newclosed

Replying to neteler:

Fixed in devbr6 and relbr64 in r53318 and r53319. Can the ticket be closed?

Tested in both and it seems to work. Closing.

Thanks !

Note: See TracTickets for help on using tickets.