Release announcement: VirtualPG 2.0.0-RC0

209 views
Skip to first unread message

Alessandro Furieri

unread,
Aug 3, 2018, 3:37:31 PM8/3/18
to SpatiaLite Users
Hi list,

the first component of the NEXT GENERATION family ready to enter 
the Release Candidate status is VirtualPostgres.

relevant news introduced by version 2.0.0:
- fixed several bugs discovered in previous versions
- now SQL Transactions (BEGIN/COMMIT/ROLLBACK) are effective
  also for PostgreSQL tables connected in READ-WRITE mode.
- the user is now enable to freely choose between TEXT
  and Julian Numbers for representing DATE, TIME and TIMESTAMP
  values.
- but the coolest new is that now VirtualPG isn't simply a
  dynamic loadable extension to SQLite, but becomes a genuine
  library intended to be linked to C/C++ applications.
  
this opens the doors for a strict integration between virtualpg
and the GUI, and consequently, the NEXTGEN GUI will become an 
effective client not only for SQLite, but for PostgreSQL and 
PostGIS as well. 
This is supposed to be a major enhancement, because it will 
greatly simplify sharing a common set of geospatial data 
on both DBMSes making any related data exchange problem 
basically simple and user friendly.

NOTE: the BETA release of the GUI now available already
implements full Postgres support (supposed to be in its
final state).

the update source tarballs released in this step are 
as follows:


pre-built binaries for Windows platforms are available
for downloading from here:

NOTE: the mod_virtualpg binary package will internally 
include a working copy of "psql", the standard PostgreSQL 
own command line front end tool.


here is the documentation about VirtualPG 2.0.0:


you can consult the tutorial about the available PostgreSQL
integration in the GUI from here:


enjoy, and happy testing
Sandro

linux...@gmail.com

unread,
Aug 3, 2018, 5:28:14 PM8/3/18
to SpatiaLite Users
On Friday, August 3, 2018 at 9:37:31 PM UTC+2, Alessandro Furieri wrote:
enjoy, and happy testing

While building the Debian packages for the new release, I noticed that the version requirements for librasterlite2 & libspatialite are not enforced at configure time.

The build fails later due to missing symbols introduced in librasterlite2 1.1.0~beta0 & libspatialite 5.0.0~beta0.

Ideally the build is not even started because configure notices that the version requirements are not met.

Kind Regards,

Bas

mj10777

unread,
Aug 3, 2018, 5:35:21 PM8/3/18
to SpatiaLite Users


On Friday, 3 August 2018 23:28:14 UTC+2, linux...@gmail.com wrote:
On Friday, August 3, 2018 at 9:37:31 PM UTC+2, Alessandro Furieri wrote:
enjoy, and happy testing

While building the Debian packages for the new release, I noticed that the version requirements for librasterlite2 & libspatialite are not enforced at configure time.
Do you have a list of which requirements were not caught ? 
When I configured librasterlite2 1.1.0~beta0, the missing version of openjpeg (>= 2.1) was caught and the configuration halted.

Mark

linux...@gmail.com

unread,
Aug 4, 2018, 5:39:04 AM8/4/18
to SpatiaLite Users
On Friday, August 3, 2018 at 11:35:21 PM UTC+2, mj10777 wrote:
On Friday, 3 August 2018 23:28:14 UTC+2, linux...@gmail.com wrote:
On Friday, August 3, 2018 at 9:37:31 PM UTC+2, Alessandro Furieri wrote:
enjoy, and happy testing

While building the Debian packages for the new release, I noticed that the version requirements for librasterlite2 & libspatialite are not enforced at configure time.
Do you have a list of which requirements were not caught ? 

In this particular case, spatialite-gui failed to enforce libspatialite >= 5.0.0 and librasterlite2 >= 1.1.0.

configure.ac only has rules to check if libspatialite & librasterlite are installed, it does not check their version or for the required symbols.

Andrea Borruso

unread,
Aug 4, 2018, 5:42:24 AM8/4/18
to SpatiaLite Users
Hi Sandro,


On Friday, 3 August 2018 21:37:31 UTC+2, Alessandro Furieri wrote:

pre-built binaries for Windows platforms are available
for downloading from here:

NOTE: the mod_virtualpg binary package will internally 
include a working copy of "psql", the standard PostgreSQL 
own command line front end tool.

I have downloaded spatialite_gui-NG-win-amd64. When I run it, it closes itself immediately.

I'm using it in Win 10 pro, release 17134.165.

Thank you

mj10777

unread,
Aug 4, 2018, 5:52:32 AM8/4/18
to SpatiaLite Users
Could you try to start spatialite_gui from a Terminal?
When done so, information is (mostly) printed out that one normally does not see. 
Among that any errors. so this information would be useful.

For Windows the following should work (have not used windows for quite some time)
spatialite_gui.exe 2> error.spatialite_gui.txt > messages.spatialite_gui.txt

The '2>' will write all errors to 'error.spatialite_gui.txt'
The '>' will write the other (non-error) messages to 'messages.spatialite_gui.txt'.

The 'error.spatialite_gui.txt' would be useful to determine the cause of the problem.

Mark



Thank you

andy

unread,
Aug 4, 2018, 6:00:49 AM8/4/18
to spatiali...@googlegroups.com
Hi,


On Sat, 4 Aug 2018 at 11:52, 'mj10777' via SpatiaLite Users <spatiali...@googlegroups.com> wrote:

For Windows the following should work (have not used windows for quite some time)
spatialite_gui.exe 2> error.spatialite_gui.txt > messages.spatialite_gui.txt

The '2>' will write all errors to 'error.spatialite_gui.txt'
The '>' will write the other (non-error) messages to 'messages.spatialite_gui.txt'.

The 'error.spatialite_gui.txt' would be useful to determine the cause of the problem.

I have two empy txt files :(

Thank you 

--
___________________

Andrea Borruso
website: https://medium.com/tantotanto
38° 7' 48" N, 13° 21' 9" E, EPSG:4326
___________________

"cercare e saper riconoscere chi e cosa,
 in mezzo all’inferno, non è inferno, 
e farlo durare, e dargli spazio"

Italo Calvino

mj10777

unread,
Aug 4, 2018, 6:11:24 AM8/4/18
to SpatiaLite Users


On Saturday, 4 August 2018 12:00:49 UTC+2, Andrea Borruso wrote:
Hi,


On Sat, 4 Aug 2018 at 11:52, 'mj10777' via SpatiaLite Users <spatiali...@googlegroups.com> wrote:

For Windows the following should work (have not used windows for quite some time)
spatialite_gui.exe 2> error.spatialite_gui.txt > messages.spatialite_gui.txt

The '2>' will write all errors to 'error.spatialite_gui.txt'
The '>' will write the other (non-error) messages to 'messages.spatialite_gui.txt'.

The 'error.spatialite_gui.txt' would be useful to determine the cause of the problem.

I have two empy txt files :(
Pity. (I assume that spatialite_gui.exe was found)

Have a look at this link about 'Event Viewer' 
- in this case you would be looking for an 'Application' event.


Mark

andy

unread,
Aug 4, 2018, 6:31:14 AM8/4/18
to spatiali...@googlegroups.com

Hi Mark,

On Sat, 4 Aug 2018 at 12:11, ‘mj10777’ via SpatiaLite Users spatiali...@googlegroups.com wrote:

Have a look at this link about 'Event Viewer' 
- in this case you would be looking for an 'Application' event.


below the error in Event Viewer. 

Thank you

- <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
- <System>
  <Provider Name="Application Error" /> 
  <EventID Qualifiers="0">1000</EventID> 
  <Level>2</Level> 
  <Task>100</Task> 
  <Keywords>0x80000000000000</Keywords> 
  <TimeCreated SystemTime="2018-08-04T10:21:13.078996300Z" /> 
  <EventRecordID>21019</EventRecordID> 
  <Channel>Application</Channel> 
  <Computer>andy</Computer> 
  <Security /> 
  </System>
- <EventData>
  <Data>spatialite_gui.exe</Data> 
  <Data>0.0.0.0</Data> 
  <Data>00000000</Data> 
  <Data>spatialite_gui.exe</Data> 
  <Data>0.0.0.0</Data> 
  <Data>00000000</Data> 
  <Data>c0000005</Data> 
  <Data>0000000001747ed0</Data> 
  <Data>192c</Data> 
  <Data>01d42bdcde728ae9</Data> 
  <Data>C:\spatialite_gui-NG-win-amd64\spatialite_gui.exe</Data> 
  <Data>C:\spatialite_gui-NG-win-amd64\spatialite_gui.exe</Data> 
  <Data>7cfdd573-a4bd-4903-8fa0-1be3d942c056</Data> 
  <Data /> 
  <Data /> 
  </EventData>
  </Event>

mj10777

unread,
Aug 4, 2018, 6:50:13 AM8/4/18
to SpatiaLite Users


On Saturday, 4 August 2018 12:31:14 UTC+2, Andrea Borruso wrote:

Hi Mark,




EventID=1000 seems to be a very general error that is being reported.


This is a very generic error and it doesn't tell much about what caused it.

-----
Question: have you used spatialite_gui during any time when RasterLite2 was supported?

It not, this may be a lookup error for missing dependencies (RasterLite2 uses many image formats)
- that was what I was expecting as an error message

Sorry, but for Windows I cannot help you further.

(Sandro: I assume the 'spatialite_gui-NG-win-amd64.7z' does not contain a setup program that installs dependencies) 

Mark

a.fu...@lqt.it

unread,
Aug 4, 2018, 6:52:05 AM8/4/18
to spatiali...@googlegroups.com
I confirm; the most recent Win binaries immediately crash on
Win10 reporting on the event log an exception code 0xc0000005;
it happen for both the 32 and 64 bit executables.

note: the same executables run smoothly on Win7

after a quick search I discovered that 0xc0000005 is a very
generic error related to some illegal memory access during
program initialization.
this 0xc0000005 error seems to be a very common issue for
many packages on Win10.

the most common causes seems to be:
- bad interferences with antivirus software
- corrupted system registry
- mismatching DLLs

Just for the sake of curiosity, the old GUI 4.3.0 runs
without any problem on Win10, but it was built by using
a different version of the MinGW/gcc compiler.

I'm still investigating about this.

bye Sandro

Silvio Grosso

unread,
Aug 4, 2018, 7:35:05 AM8/4/18
to SpatiaLite Users
Hello everyone,

Just tested today's installer on Windows 10 - Home (version: 10.0.17134 N/D -  build 17134).
CPU Intel I7
RAM 8 gb
Kaspersky antivirus

Its GUI starts fine: no error whatsoever (spatialite_gui_2.1.0.beta0). I have tried it several times.

Unfortunately, it is not possible to resize the floating windows (e.g. the one to load shapefiles) by dragging their borders, with my mouse's cursor. Some menus in the Spatialite Gui are too constrained. In short, they are not wide enough, and currently I am unable to increase their size by dragging the window borders.

See this screenshot:

Thanks a lot for this new release.
The improved integration with PostgreSQL is extremely appreciated as well ! :-)


On Friday, August 3, 2018 at 9:37:31 PM UTC+2, Alessandro Furieri wrote:

a.fu...@lqt.it

unread,
Aug 4, 2018, 8:12:09 AM8/4/18
to spatiali...@googlegroups.com
On Sat, 4 Aug 2018 04:35:04 -0700 (PDT), Silvio Grosso wrote:
> Hello everyone,
>
> Just tested today's installer on Windows 10 - Home (version:
> 10.0.17134 N/D -  build 17134).
>
> CPU Intel I7RAM 8 gb
> Kaspersky antivirus
>
> Its GUI starts fine: no error whatsoever
> (spatialite_gui_2.1.0.beta0).
> I have tried it several times.
>

I start suspecting that this issue could be caused by different
versions of the MS Visual C++ Redistributables.

on my Win10 I find that just the 2005 and 2015 (both 32 and
bit) are installed.

but on my Win7 I find many more versions: 2005, 2008, 2010,
2012, 2013 and 2015.

Silvio, could you kindly check which versions are installed
on your lucky Win 10 ?

bye Sandro

Totò Fiandaca

unread,
Aug 4, 2018, 8:44:37 AM8/4/18
to SpatiaLite Users
silvio also worked by me, started with a db connected (last opened with 4.5) but since I closed spatialite_gui 5 disconnecting the db no longer works

win 10 amd64 b - bitdefender antivirus

a.fu...@lqt.it

unread,
Aug 4, 2018, 8:56:03 AM8/4/18
to spatiali...@googlegroups.com
On Sat, 4 Aug 2018 05:44:37 -0700 (PDT), Totò Fiandaca wrote:
> but since I closed spatialite_gui 5 disconnecting the db no
> longer works
>

Toto',

what do you really intend to say ?
the db becomes corrupted after closing spatialite_gui 5 ?
or the disconnection process hangs up and never terminates ?
or whatever else ?

ciao Sandro

Silvio Grosso

unread,
Aug 4, 2018, 9:33:09 AM8/4/18
to SpatiaLite Users
Hi Sandro,

On my computer with Windows 10 - Home (64 bit version) I have installed all sort of softwares these past two years (since I have bougth it): From Qgis to PostgreSQL etc etc

To reply to your question, here is a screenshot I got by taking a look at my softwares:

I don't know how to get this similar list from the CMD.exe but I hope this screenshot suffices :-)

Btw, I am testing the 64bit version of SpatiaLite.
My connections works fine also when I relaunch the software after having closed it.

Totò Fiandaca

unread,
Aug 4, 2018, 10:36:33 AM8/4/18
to SpatiaLite Users
Scrivo in italiano perché in inglese dovrei usare il traduttore di google.

quello che ho notato:
appena scaricato spatialite_gui 5 e avviato trovo che è connesso ad un db (ultimo db che ho aperto con spatialite_gui 4.5), quindi funziona.
chiudo spatialite_gui 5 senza scollegarmi dal DB, riavvio spatialite_gui 5 e funziona.

chiudo spatialite_gui 5 dopo aver scollegato il database, provo ad avviarlo ma si chiude istantaneamente.

quindi il problema che noto è che se chiudo spatialite_gui 5 con nessun db collegato al successivo avvio non parte.

saluti

a.fu...@lqt.it

unread,
Aug 4, 2018, 11:06:06 AM8/4/18
to spatiali...@googlegroups.com
On Sat, 4 Aug 2018 07:36:33 -0700 (PDT), Totò Fiandaca wrote:
> Scrivo in italiano perché in inglese dovrei usare il traduttore di
> google.
>

Very well !!!
Toto' finally catched the worm :-D

on both Win7 and Win10 spatialite_gui NEXTGEN systematically
crashes immediately after starting if there is no database
currently connected.

the defect doesn't seems to affect Linux platforms.

useful workaround for Windows 10 users like me and Andrea;
just istall any previous version of the GUI, create and
connect some DB, then exit.
now lauch spatialite_gui NEXTGEN; it will nicely work ;-)

bye Sandro

Andrea Borruso

unread,
Aug 4, 2018, 11:24:49 AM8/4/18
to SpatiaLite Users
Thank you Totò and Sandro

a.fu...@lqt.it

unread,
Aug 4, 2018, 5:44:32 PM8/4/18
to spatiali...@googlegroups.com
Now the bug affecting Windows is definitely fixed; both the
source tarballs and the executables binaries for Windows
available for download have been patched.

as it emerged at the end of a tiresome debugging session,
it simply was once again the most classic programming error:
dereferencing a NULL pointer :-P

useful hint for windows developers: Dr. Memory [1] played a
strategic role in identifying the exact code line causing
the crash ... it's not as mature as Valgrind and it only
supports 32 bit executables, but it's surely the top of the
top for debugging puzzling bugs on Windows.

bye Sandro

[1] http://drmemory.org/


Silvio Grosso

unread,
Aug 5, 2018, 5:14:50 AM8/5/18
to SpatiaLite Users
Hi Sandro,

Just tested this new build (spatialite_gui > amd64) on:
- Windows 8.1 (64 bit): build 9600. Everything is fine in that the GUI starts and I can write my SQL code.
- Windows 10 (64 bit): build 17134. Everything is fine as well as regards starting the GUI.
Compared to the 8.1 installtion many floating windows have their menu too small and it is not possible to resize their size by dragging their borders.
For instance, this occurs with the window to load shapefile; the window for connecting to PostgreSQL etc
On Windows 8.1 this glitch does not occur...
I suppose it is a wxPython GUI problem related to my Windows 10 system.

This being said, I suppose most power users are using the CLI version, not the GUI, on top of typing their code on Linux :-)

br...@frogmouth.net

unread,
Aug 5, 2018, 5:17:54 AM8/5/18
to spatiali...@googlegroups.com

The “too small window” might be associated with a HiDPI (like 4K) screen resolution. Is that the case for your Win10 machine?

 

Brad

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.
To post to this group, send email to spatiali...@googlegroups.com.
Visit this group at https://groups.google.com/group/spatialite-users.
For more options, visit https://groups.google.com/d/optout.

Silvio Grosso

unread,
Aug 5, 2018, 6:11:56 AM8/5/18
to SpatiaLite Users
Hello Brad,

> The “too small window” might be associated with a HiDPI (like 4K) screen resolution. Is that the case for your Win10 machine?

 

On Windows 10 (home edition) - 64 bit, my laptop (ASUS X302UJ) has a display like this:
13.3" 16:9 HD (1366x768)/Full HD (1920x1080)

For what  it is worth, I have never had any problem so far with different softwares (Qgis - Qt; Gimp - Gtk; Audacity - wxPython).

Here is the window to connect to PostgreSQL where all uppermost menu are squeezed (https://www.dropbox.com/s/jw8qtn6qgy9i5em/GUI_CONNECT_SPATIALITE.png?dl=0):

a.fu...@lqt.it

unread,
Aug 5, 2018, 6:41:26 AM8/5/18
to spatiali...@googlegroups.com
On Sun, 5 Aug 2018 03:11:56 -0700 (PDT), Silvio Grosso wrote:
> Hello Brad,
>
>> The “too small window” might be associated with a HiDPI (like
> 4K) screen resolution. Is that the case for your Win10 machine?
>
>  
> On Windows 10 (home edition) - 64 bit, my laptop (ASUS X302UJ) has a
> display like this:
> 13.3" 16:9 HD (1366x768)/Full HD (1920x1080)
>
> For what  it is worth, I have never had any problem so far with
> different softwares (Qgis - Qt; Gimp - Gtk; Audacity - wxPython).
>
> Here is the window to connect to PostgreSQL where all uppermost menu
> are squeezed
>

Hi Silvio,

you'll find attached a screenshot of the same dialog from my
Win10 laptop. display resolution; 1366x768 (same as your).

I notice a may be revealing detail:
- my image is 353x378
- whilst your is 410x511

conclusion: it seems that your are using a custom size
for the default system font (bigger than the expected
usual standard setting).

bye Sandro

a.fu...@lqt.it

unread,
Aug 5, 2018, 6:43:36 AM8/5/18
to spatiali...@googlegroups.com
sorry, I forgot to attach the screenshot ... OOPS
Immagine.png

Silvio Grosso

unread,
Aug 5, 2018, 7:00:55 AM8/5/18
to SpatiaLite Users
Hi Sandro,

Sandro Furieri wrote:
> or your default system font (bigger than the expected  usual standard setting).

Sorry for the trouble...

Actually, I have never modified my display settings.
However, I have just taken a look right now and they are set as "suggested" by Microsoft, that is they are  applied in their "default mode" (mine is 150%, for the dimensions of the fonts etc).

All in all, this is not a big issue...
In all truth, I really hate the GUIs for working with databases...
Right now, I have stopped working with PgAdmin in favour of psql.
To be even more precise, I had previously dumped PgAdmin (3 and 4 versions) to rely completely on Dbeaver, a much powerful GUI IMHO, but now I am a "psql guy" :-)

On Friday, August 3, 2018 at 9:37:31 PM UTC+2, Alessandro Furieri wrote:

Roman

unread,
Aug 9, 2018, 10:53:39 AM8/9/18
to spatiali...@googlegroups.com
Hello,
I've encountered problem with connections to postgis. When I connect my database in pgAdmin, in server status I see two connections from pgAdmin. When I open same database in spatialite-gui, another 93 connections is created and then many fatal errors "sorry, too many clients already".
Why spatialite-gui requires so many connections? Does in take separate connection for each layer / table?

Some details: database has 6 schemas and tens of tables.

Roman

--

Roman

unread,
Aug 9, 2018, 10:58:45 AM8/9/18
to spatiali...@googlegroups.com
It looks like it's a problem with wxwidgets versus scaling on high DPI displays. Silvio says he uses 150%. Maybe newer version of wxwidgets could solve it. Spatialite-gui currently uses version 3.0.4, in latest 3.1.1 they mension "better support for high DPI 
  monitors"

Roman

a.fu...@lqt.it

unread,
Aug 9, 2018, 1:25:38 PM8/9/18
to spatiali...@googlegroups.com
On Thu, 9 Aug 2018 16:53:26 +0200, Roman wrote:
> Hello,
> I've encountered problem with connections to postgis. When I connect
> my database in pgAdmin, in server status I see two connections from
> pgAdmin. When I open same database in spatialite-gui, another 93
> connections is created and then many fatal errors "sorry, too many
> clients already".
> Why spatialite-gui requires so many connections? Does in take
> separate
> connection for each layer / table?
>
> Some details: database has 6 schemas and tens of tables.
>

Hi Roman,

short answer:
yes, the VirtualPG driver strictly requires a separate connection for
each PostgreSQL Table (or View) being mapped.
if the PostgreSQL DBMS contains many tenths of Tables and Views there
is a serious risk to exceed the maximum number of connections supported
by the DBMS Server (usually 100, accordingly to the default setting),
and in this case the Server will simply return this error:
"sorry, too many clients already"


long answer:
Virtual Tables as implemented by SQLite cannot share memory objects
with the main SQL core; each single Virtual Table must allocate its
separate memory space. This obviously forbids to share the same
PostgreSQL connection between more tables, and consequently each
Virtual Table must establish its own connection to Postgres.

possible workarounds I can foresee to circumvent this issue:

a) reconfigure your Postgres Server by increasing the maximum
number of supported connections. (carefully evaluate the
possible performance impact of such an action).

b) install some additional tool as pgBouncer or pgPool
supporting connection pooling.

c) and finally we could evaluate the opportunity to change
the current implementation of VirtualPG.
there is no real need to keep an always opened connection
for each Virtual Table.
connections could be effectively established only when
some query affecting a specific VirtualPostgres Table starts,
and immediately finalized when the current query ends.
this will surely introduce a moderate overhead (few seconds
in the worst case) for each query interesting a VirtualPG
table, but will strongly reduce the overall number of
connections: the GUI is single threaded, so even in the
case of very complex queries affecting many Postgres Tables
at the same time it should be very improbable requiring
more than a handful of connections.

bye Sandro

Roman

unread,
Aug 10, 2018, 3:20:43 AM8/10/18
to spatiali...@googlegroups.com
Thank you for your answer.

Another option could be:

d) don't open all tables but only selected ones. That would require change in UI - dialog with tree like in QGIS.

Roman

Reply all
Reply to author
Forward
0 new messages