Data Visibility Between Subquery and Outer Statement in PostgreSQL

The difference lies between a query and a transaction. A transaction can contain any number of queries. To illustrate the difference, I set up a small example:

CREATE TABLE table_to_be_updated (
    id serial PRIMARY KEY,
    other_column text,
    column_changing text
);

INSERT INTO table_to_be_updated (other_column, column_changing)
VALUES
('value', 'old_value'),
('value', 'other_value'),
('nonvalue', 'doesnt matter');

Then run two transactions concurrently (issuing the commands one by one, the middle line wants to depict the timeline):

                                | <-- BEGIN;
                                |
                                |
                                |     UPDATE table_to_be_updated
BEGIN; -----------------------> |     SET column_changing = 'new_value'
                                |     WHERE
                                |         other_column = 'value' AND
                                |         column_changing = 'old_value';
                                |
                                |
SELECT column_changing -------> |     -- update not yet committed
FROM table_to_be_updated        |
WHERE other_column = 'value';   | <-- COMMIT;
                                |
                                |
SELECT column_changing -------> |
FROM table_to_be_updated        |
WHERE other_column = 'value';   |
                                |
                                |
COMMIT; ----------------------> |

Running these in READ COMMITTED isolation level, the first query returns a row with 'old_value', while the second one shows a row with 'new_value'. On an other run, I change the left-hand-side transaction isolation level:

SET transaction ISOLATION LEVEL REPEATABLE READ;

(The command must be the first statement in a transaction.) Now both SELECTs return the same rowset, while a third one after committing both transactions will show the new row.

I'm not able to change the structure of this query. The reason I need Geoserver is because it will convert the results to a different format (GeoJSON).

PostGIS supports converting results to a different format. In your case, you want,

• ST_AsGeoJSON

Moreover, it supports a ton of other Geometry Exports (current as of 2.5) too,

• ST_AsBinary — Return the Well-Known Binary (WKB) representation of the geometry/geography without SRID meta data.
• ST_AsEncodedPolyline — Returns an Encoded Polyline from a LineString geometry.
• ST_AsEWKB — Return the Well-Known Binary (WKB) representation of the geometry with SRID meta data.
• ST_AsEWKT — Return the Well-Known Text (WKT) representation of the geometry with SRID meta data.
• ST_AsGML — Return the geometry as a GML version 2 or 3 element.
• ST_AsHEXEWKB — Returns a Geometry in HEXEWKB format (as text) using either little-endian (NDR) or big-endian (XDR) encoding.
• ST_AsKML — Return the geometry as a KML element. Several variants. Default version=2, default maxdecimaldigits=15
• ST_AsLatLonText — Return the Degrees, Minutes, Seconds representation of the given point.
• ST_AsSVG — Returns a Geometry in SVG path data given a geometry or geography object.
• ST_AsText — Return the Well-Known Text (WKT) representation of the geometry/geography without SRID metadata.
• ST_AsTWKB — Returns the geometry as TWKB, aka "Tiny Well-Known Binary"
• ST_AsX3D — Returns a Geometry in X3D xml node element format: ISO-IEC-19776-1.2-X3DEncodings-XML
• ST_GeoHash — Return a GeoHash representation of the geometry.
• ST_AsGeobuf — Return a Geobuf representation of a set of rows.
• ST_AsMVTGeom — Transform a geometry into the coordinate space of a Mapbox Vector Tile.
• ST_AsMVT — Return a Mapbox Vector Tile representation of a set of rows.