g.rename extremely slow, speed improvement

[gisboa]

Renaming a large vector table connected to a postgresql database using v.rename is very time consuming, mostly due to database activity. Suggestion: send 'alter table rename ...' to postgresql to rename the table, and change the dbln contents, so only renaming operations are involved, no actual data is being moved in this way

[glynn]

Replying to gisboa:

Renaming a large vector table connected to a postgresql database using v.rename is very time consuming, mostly due to database activity. Suggestion: send 'alter table rename ...' to postgresql to rename the table, and change the dbln contents, so only renaming operations are involved, no actual data is being moved in this way

The DBF driver doesn't support 'ALTER TABLE RENAME ...'. And there's no way for a client to query the capabilities of a particular driver.

Suggestion 1: drop support for the DBF driver. Its presence means that everything that uses the DBMI has to limit itself to the tiny subset of SQL which the DBF driver actually understands (e.g.: no joins), often having to adopt inefficient workarounds.

Suggestion 2: stop using db_execute() and extend the DBMI with specific commands for each operation. Drivers for real, mostly SQL-compatible backends can just send the appropriate command while the DBF driver can do the necessary hacks.

Suggestion 3: upgrade the DBF driver so that it supports at least the basic features of SQL, e.g. joins.

[mlennert]

See also #2587

[glynn]

Replying to glynn:

Suggestion 1: drop support for the DBF driver. Its presence means that everything that uses the DBMI has to limit itself to the tiny subset of SQL which the DBF driver actually understands (e.g.: no joins), often having to adopt inefficient workarounds.

+1

Suggestion 3: upgrade the DBF driver so that it supports at least the basic features of SQL, e.g. joins.

This is far from straightforward. You'd need to write a query optimiser, and you'd need to maintain indices. Ultimately, efficient implementation of joins is the heart of any RDBMS.

Extending it to support isolated features such as renaming tables or columns probably wouldn't be particularly hard, although it's debatable whether there's any point given that the lack of support for joins makes it inferior to SQLite in every regard.

[mlennert]

Replying to glynn:

Replying to glynn:

Suggestion 1: drop support for the DBF driver. Its presence means that everything that uses the DBMI has to limit itself to the tiny subset of SQL which the DBF driver actually understands (e.g.: no joins), often having to adopt inefficient workarounds.

+1

+1 from me, too. However, not sure if this is an option for grass7. Probably we have to wait until grass8 for that.

[neteler]

An option might be to #ifdef the code along with a "configure" flag for custom compilation.

Also as a precursor to complete removal in future.