Segmentation violation in virtualpg on syntax error creating virtual table.

[Peter da Silva]

I just started testing virtualpg and one thing seems to be an issue: if I make a mistake in the syntax of "create virtual table" the error diagnostic is a core dump. I haven't had an aopportunity to dig further yet, but I wondered if this was a known problem.

virtualpg-1.0.1 on FreeBSD 11.0-RELEASE-p8 with libsqlite3.18.0.

[a.fu...@lqt.it]

Hi Peter,

I suppose that VirtualPG probably is the less frequently used
module of the Spatialite family, so it's not at all impossible
that it could contain unreported bugs.

Could you kindly supply for debugging purposes some example
of syntaxes surely causing a crash ?

bye Sandro

[Peter da Silva]

sqlite> create virtual table pg_class using VirtualPostgres ('host=localhost port=5432 dbname=peter user=peter password=deleted', pg_catalog);
Segmentation fault (core dumped)

Leaving the PGSQL table name off is definitely an error, but this is not a great diagnostic.

sqlite> create virtual table pg_class using VirtualPostgres ('host=localhost port=5432 dbname=peter user=peter password=deleted', pg_catalog, pg_crass);
Segmentation fault (core dumped)

Typo in table name, same thing.

Typo in schema name, same thing.

[Alessandro Furieri]

Hi Peter,

there was a Deadly Sin affecting the code; more precisely

there was an offence against the Second Commandment.

Please see: The Ten Commandments for C Programmers

(Annotated Edition) [1]

[1] https://www.lysator.liu.se/c/ten-commandments.html

------------

2. Thou shalt not follow the NULL pointer, for chaos 

and madness await thee at its end.

Clearly the holy scriptures were mis-transcribed here, 

as the words should have been ``null pointer'', to 

minimize confusion between the concept of null 

pointers and the macro NULL (of which more anon). 

Otherwise, the meaning is plain. 

A null pointer points to regions filled with dragons, 

demons, core dumps, and numberless other foul creatures, 

all of which delight in frolicing in thy program if 

thou disturb their sleep. 

A null pointer doth not point to a 0 of any type, 

despite some blasphemous old code which impiously 

assumes this.

------------

Post Mortem Report

==================

The VirtualPG module starts by attempting to

establish a connection to PostgreSQL.

If the connection is successfully established

then a SQL query on behalf of the Postgres

metadata tables is executed so to extract

the fully qualified list of all Columns 

belonging to the required Table.

If such Table doesn't exists (wrong/misspelled

Schema-name and/or Table-name) the above query

will obviously return an empty (0 rows) resultset.

And even more important, the C data structure

containing the Columns list will remain

uninitialized (pointing to NULL).

The old implementation lacked any elementary

precaution intended to handle in a proper

way the failing query, and a program crash

was the obvious consequence.

Applied patch: now the failing query nicely

reports an error and exit gracefully.

bye Sandro

[Peter da Silva]

Thanks.

Another question came up... does virtualpg cache the database connection or open a new one for each table? Does it cache the table details? We found that frequent queries against the schema were a problem because they blocked vacuuming. 

[a.fu...@lqt.it]

On Sat, 3 Jun 2017 07:10:51 -0500, Peter da Silva wrote:
> Thanks.
>
> Another question came up... does virtualpg cache the database
> connection or open a new one for each table?
>

an independent Postgres connection is established for each
single VirtualPG table.
implementing a shared Postgres connection servicing multiple
VirtualPG tables isn't intrinsically impossible, but will
surely add a lot of further complexity.

VirtualPG is not intended as a tool permanently wrapping
a complex Postgres schema into SQLite (that make little
sense, and that will certainly lead to poor performances).

The intended scope of VirtualPG is to make easy cloning
a SQLite DB by extracting data from a Postgres master.
something like an SQL script repeatedly performing the
following steps:
1. create a VirtualPG table
2. create and populating a corresponding SQLite table:
CREATE TABLE xxxx AS SELECT * FROM zzzz;
3. drop the VirtualPG table
4. return to step 1 processing the next table amd so on.

> Does it cache the table details?
>

yes; the Postgres own metadata table are queried
just once (when creating the VirtualPG table).
after this VirtualPG will assume during all its
life cycle that the table layout will remain
unchanged.

> We found that frequent queries against the schema were a
> problem because they blocked vacuuming.
>

surely yes: and exactly for this reason the best
strategy to use VirtualPG is to always consider it
as a transient short-lived object:
a. Create the VirtualPG table.
b. clone the target table into a real SQLite table.
c. Drop the VirtualPG table.

bye Sandro