D 2018-08-05T10:05:13.715
L PostgreSQL
P d6bd01bb8d3df16dabc1bc782fac422c83f0cc73
U sandro
W 13337
Back to <a href="https://www.gaia-gis.it/fossil/spatialite_gui/wiki?name=spatialite-gui">main Wiki page</a><hr><br>
<h1>The GUI as a PostgreSQL client</h1>
Starting from version <b>2.1.0</b> the GUI has the capability to support connections to external <b>PostgreSQL / PostGIS</b> databases.<br>
This functionality is mainly based on <a href="https://www.gaia-gis.it/fossil/virtualpg/wiki?name=tutorial">libvirtualpg</a>, that is now a required dependency for the GUI tool.
The present tutorial is intended to quickly explain how the PostgreSQL support is implemented by <b>spatialite_gui</b>.<br><br>
<table cellspacing="6" cellpadding="6" bgcolor="#f0ffe0" width="100%" border="1">
<tr><td colspan="2" align="center"><h3>1 - Establishing a Connecting to a PostgreSQL DBMS</h3><hr></td></tr>
<tr>
<td><b>1.a</b> first press the appropriate button from the Toolbar</td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/gui1.png" alt="connection 1"></td>
</tr>
<tr>
<td><b>1.b</b> then select the appropriate item from the main Menu</td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/gui2.png" alt="connection 2"></td>
</tr>
</tr>
<tr>
<td><b>1.c</b> and finally select the appropriate item from the context Menu that will appear by clicking the <b>left</b> button of the mouse over the <b>PostreSQL</b> main node from the Table Tree.</td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/gui3.png" alt="connection 3"></td>
</tr><tr>
<td><b>1.d</b> in all three cases the PostgreSQL connection dialog will appear.<br>
Just insert the required connection arguments and confirm.<br><br>
When invalid arguments are given, an error message will be then displayed, otherwise the connection will be immediately established.<br><br><br><hr><br><br><br>
If you are attempting to connect a remote server, you may find it useful to first reading the tutorial about <a href="https://www.gaia-gis.it/fossil/virtualpg/wiki?name=port-forwarding">SSH tunneling and Port Forwarding</a>
<br><br><br>
<hr>
<table bgcolor="#ffcc80" cellspacing="6" cellpadding="6" border="1" width="100%">
<tr><td align="center"><h3>Special note for Windows users</h3></td></tr>
<tr><td>
Please read the <a href="#addendum">Addendum</a> about LibPQ late binding.
</td></tr>
</table></td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/gui4.png" alt="connection 4"></td>
</tr>
<tr><td colspan="2" align="center"><h3>2 - Expanding the PostgreSQL tree</h3><hr></td></tr>
<tr>
<td><b>2.a</b> once a new Postgres connection has been successfully established, the Postgres node on the main Table Tree will contain all Tables and Views defined in the Postgres Database, accordingly to the usual arrangement adopted by Postgres:
<ul>
<li>Connection <i>aka</i> <b>Database</b>
<ul>
<li><b>Schema</b>
<ul>
<li><b>Table</b> or <b>View</b></li>
</ul></li>
</ul></li>
</ul><br><br>
Each kind of database object will be clearly identified by an appropriate icon; Views will be distinguished from Tables, and <b>PostGIS</b> Tables (containing <b>Geometry</b> columns) will be clearly distinguished from ordinary Tables.<br><br>
<b>Note</b>: all Postgres Tables and Views (namely: <b>VirtualPostgres</b> Tables) will always have a conventional name starting with the <b>vpg_</b> prefix.<br>
Whenever possible the original Postgres Name will be preserved, otherwise a numeric suffix it will be added, ensuring a unique name .<br><br><br>
<hr>
<br><br>
<b>Note</b>: all database objects in the Postgres Tree are dynamic, and will automatically disappear when the corresponding connection will be closed, or when the current session ends.</td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/gui5.png" alt="table tree"></td>
</tr>
<tr>
<td><b>2.b</b> if, for any possible reason (such as on: a system crash, power failure, etc.), power failure and alike), the current session will fail to correctly perform the expected final cleanup, your SQLite database will remain in a dirty and inconsistent state.<br>
As the side figure shows, when restarting a new session all <b>orphaned VirtualPostgres tables</b> will remain from any aborted session(s).<br><br>
If this happens, don't despair: all you just need to do is to select the appropriate item from the Postgres context menu and the DB will be immediately recovered to a safe, consistent and clean state.</td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/orphans.png" alt="orphan tables"></td>
</tr>
<tr><td colspan="2" align="center"><h3>3 - Metadata of PostgreSQL objects</h3><hr></td></tr>
<tr>
<td><b>3.a</b> you can easily query the metadata for each Postgres object; just select the appropriate item from the context menu.</td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/gui6.png" alt="metadata info"></td>
</tr>
<tr>
<td><b>3.b</b> Metadata for a <b>Connection</b> <i>aka</i> <b>Database</b> object will report the initial connection arguments.<br><br><br>
<hr>
<br><br>
<b>Note</b>: you can always connect more than one Postgres Database in the same session; however connecting the same Database more than once is not permitted.</td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/gui7.png" alt="connection metadata"></td>
</tr>
<tr>
<td><b>3.c</b> Metadata for a <b>Schema</b> object aren't really interesting, since only the name will be shown.</td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/gui8.png" alt="schema metadata"></td>
</tr>
<tr>
<td><b>3.d</b> Metadata for <b>Table</b> or <b>View</b> objects will report all <b>GRANT permissions</b> for the current User.<br><br><br>
<hr>
<br><br>
<b>Note</b>: also in the case of a <b>READ-WRITE</b> enabled connection, all Postgres Tables lacking the permission to execute <b>INSERT</b>, <b>UPDATE</b> or <b>DELETE</b> statements will be always considered to be <b>READ-ONLY</b>.<br>
Exactly as any other Table where no <b>PRIMARY KEY</b> has been defined.</td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/gui9.png" alt="table metadata"></td>
</tr>
<tr>
<td><b>3.e</b> Metadata for a <b>PostGIS Table</b> objects will report a full qualification of the <b>GEOMETRY</b> column(s), this including the GeometryType, the Srid and the CoordinateDimensions.</td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/gui10.png" alt="postgis metadata"></td>
</tr>
<tr>
<td><b>3.f</b> all <b>PostGIS Tables</b> will always have a <b>companion Table</b> sharing the same identical name except for the <b>_postgis</b> suffix.<br><br><br>
<b>Note</b>: these are not the original <b>VirtualPostgres Tables</b> wich contain the original Postgres binary format; they are instead Spatialite's own <b>Spatial Views</b> containing Spatialite binary Geometries. The conversion between the two formats are done in the background and thus, for the user, in a compleatly transparent way.</td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/gui11.png" alt="postgis wrapper metadata"></td>
</tr>
<tr><td colspan="2" align="center"><h3>4 - Handling PostgreSQL Tables and Views</h3><hr></td></tr>
<tr>
<td><b>4.a</b> PostgreSQL Tables and Views are considered, as far as possible, as if they were native SQLite objects.<br>
So the usual operations supported on SQLite's own objects will be supported also in the case of PostgreSQL objects.</td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/gui12.png" alt="table operations"></td>
</tr>
<tr>
<td><b>4.b</b> the above criteria will apply also to PostGIS Geometries.<br><br><br>
<b>Hint</b>: exporting a full Shapefile from a PostGIS Geometry was never more direct and simple than this.</td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/gui13.png" alt="geometry operations"></td>
</tr>
<tr>
<td><b>4.c</b> be warned; directly querying a <b>PostGIS Table</b> (i.e. a VirtualPostgres Table containing PostGIS Geometries) never is a good idea.
You'll be always asked twice before starting such a stupid (and potentially dangerous) action.</td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/gui17.png" alt="direct postgis query"></td>
</tr>
<tr>
<td><b>4.d</b> the reason for such a behaviour is clearly shown in the side figure.<br>
A VirtualPostgres Table will expose all Geometries as <b>EWKB text strings</b> using an hexadecimal notation; in this case we have simple POINTS, and the EWKB strings are reasonably compact.<br>
But unhappily complex LINESTRING or POLYGON Geometries may easily require several million characters, and this will easily over stress the GUI.<br>
The execution speed of such a query will certainly be very sluggish, and some instability trouble could eventually arise.<br><br><br>
<b>Short conclusion</b>: never ever attempt to directly query a PostGIS Table !</td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/gui15.png" alt="direct postgis query"></td>
</tr>
<tr>
<td><b>4.e</b> a companion Spatial View is always defined for every PostGIS Table, and is specifically intended to support safe and trouble free queries.<br>
That's not only: the companion Spatial View will take care to silently convert binary Geometries to and from the usual SpatiaLite's own format, thus greatly simplifying the eventual writing of customized SQL queries using Spatial Functions.</td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/gui16.png" alt="indirect postgis query"></td>
</tr>
<tr>
<td><b>4.f</b> the companion Spatial View will always be a <b>Writable View</b> if the corresponding PostGIS Table is appropriately enabled and if the connection supports READ-WRITE operations.<br><br><br>
<b>Note</b>: appropriate Triggers will be automatically defined so to let the Spatial View to become a Writable View fully supporting <b>INSERT</b>, <b>UPDATE</b> and <b>DELETE</b> operations.</td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/gui14.png" alt="postgis editing"></td>
</tr>
<tr><td colspan="2" align="center"><h3><a name="addendum">Addendum</a>: special note for Windows users</h3><hr></td></tr>
<tr><td colspan = "2">
All previous versions of the GUI binaries released in the past were always based on a single monolithic <b>.exe</b> fully depending on static linkage and not requiring any DLL at all.<br><br>
Unhappily this is no longer possible, because <b>libpq</b> (<i>the PostgreSQL own client library</i>) is uniquely available as a DLL on Windows, and there is absolutely no way to build a corresponding static library.<br>
More precisely: a static library is actually built when compiling PostgreSQL, but on Windows it simply is a stub still requiring to load the DLL at run time.<br><br><br>
All this considered, now <b>spatialite_gui.exe</b> is kind of an hybrid; it mostly depends on static linkage, except than for <b>libpq.dll</b>.<br>
So a reasonable compromise has been adopted in order to mitigate the negative impact of such an odd layout:
<ul>
<li>the binaries for Windows platforms will be built in such a way that <b>libpq.dll</b> will support what is generally known as <b>late binding</b> aka <b>deferred binding</b>.</li>
<li>this way the main core of the GUI will continue to nicely work even when <b>libpq.dll</b> hasn't yet been loaded.</li>
<li><b>libpq.dll</b> will be actually loaded only when strictly needed, i.e. when the user requires to establish a PostgreSQL connection.</li>
<li>if the DLL fails to be loaded for any reason, then the GUI will continue unaffected, except for that the PostgreSQL support will continue to be kept disabled.</li>
</ul>
</td></tr>
<tr><td colspan="2" align="center"><h3>How it practically works</h3</td></tr>
<tr>
<td><b>A.1</b> when required to access PostgreSQL for the first time, the GUI will actually attempt to load <b>libpq.dll</b>:
<ul>
<li>a first automatic attempt will be performed accordingly to the standard platform rules.<br>
<b>Hint</b>: on Windows it usually is a very nice solution to place the DLL in the same folder where the GUI executable is.</li>
<li>if such an attempt fails, then the user is asked for manually searching <b>libpq.dll</b>, as shown by the side figure.</li>
</ul></td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/libpq-deferred1.png" alt="libpq not found"></td>
</tr>
<tr>
<td><b>A.2</b> now you can use the standard File Selection dialog in order to locate and open <b>libpq.dll</b>.<br><br><br>
<b>Warning</b>: a very common pitfall is attempting to load a 32 bit DLL on a 64 bit GUI, or the opposite.<br>
The code model of both the executable and the DLL must necessarily match.</td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/libpq-deferred2.png" alt="libpq open"></td>
</tr>
<tr>
<td><b>A.3</b> if <b>libpq.dll</b> has been successfully loaded a confirmation message will be shown.<br>
The association between the GUI and the DLL will be permanently registered, so to allow for an automatic loading in all the following sessions.</td>
<td><img src="https://www.gaia-gis.it/gaia-sins/gui-postgres/libpq-deferred3.png" alt="libpq loaded"></td>
</tr>
</table>
Back to <a href="https://www.gaia-gis.it/fossil/spatialite_gui/wiki?name=spatialite-gui">main Wiki page</a>
Z 7bb7a0ad0d0b0b7481ce1bf21bb79ccb