Where Your SQL Runs

Three connections, one search_path, and the five guards on every query

What this lesson covers

  • What a connection actually is, and which three you can query
  • Why “relation does not exist” is usually not a typo
  • Your workspace holds your data — in five geometry types at once
  • The five guards, and why 100 rows back means nothing
  • Switch connection and the same question answers differently

A connection is a database plus a search_path

The reference book opens with twenty-four pages on Docker, docker-postgis, pgAdmin and ogr2ogr, because before you can write spatial SQL you have to own a database. There is nothing here to install — you already have three, and they are different in ways that matter.

Connection What it is What lives there
map a remote PostGIS server, reached over a tunnel OpenStreetMap: 41 tables, 440 million buildings, schema osm
workspace your own database everything you import, plus reference sets like population and building footprints
sedona an engine, not a server GeoParquet and CSV files queried by path — no tables at all

There is a fourth, portal. It holds the platform's own records — accounts, workspaces, billing. It is not lesson material and no query in this course touches it.

The practical consequence: the same SQL means different things on different connections. SELECT * FROM building_polygon finds 440 million rows on map and nothing at all on workspace, and neither answer is a bug.

What geometry can this connection even see?

SELECT f_table_schema AS schema,
       f_table_name   AS table_name,
       f_geometry_column AS geom_column,
       type           AS geometry_type,
       srid
FROM geometry_columns
ORDER BY f_table_schema, f_table_name;

geometry_columns is a view every PostGIS database publishes: one row per geometry column it knows about, with its type and SRID. It is the fastest way to learn what a connection actually holds — run it on workspace and you get your own tables; the same query on map would list OpenStreetMap's forty-one. Neither result is more correct.

Run this query yourself

Why "relation does not exist" is usually not a typo

Postgres does not look for a table everywhere. It walks search_path, a list of schemas, and stops at the first match:

SET search_path TO ws_acme, public;   -- illustrative: the editor sets this for you
SELECT * FROM buildings;              -- finds ws_acme.buildings, never public.buildings

That single setting is the tenancy boundary in this product. Two workspaces run on one server, each with its own schema, and the connection decides which one the word buildings refers to. Nothing in your query changes — only what it resolves to.

So when a table "does not exist", the useful question is not did I spell it right but which schema was I asking. osm.* tables only exist on map; geo_* tables only on workspace. Qualify the schema (osm.building_polygon) and the question disappears.

What am I connected to?

SELECT current_database()             AS db,
       current_schema()               AS schema,
       current_setting('search_path') AS search_path,
       PostGIS_Lib_Version()          AS postgis,
       version()                      AS server;

Five settings that explain most confusion in a shared editor: which database, which schema, the full search_path Postgres will walk, the PostGIS version behind the spatial functions, and the server build. Run it first in any session you did not open yourself — it turns “that table does not exist” into “I am on the wrong connection”.

Run this query yourself

The same editor, a different database

SELECT table_name,
       (SELECT count(*)
          FROM information_schema.columns AS c
         WHERE c.table_schema = t.table_schema
           AND c.table_name   = t.table_name) AS columns
FROM information_schema.tables AS t
WHERE table_schema = 'osm'
ORDER BY table_name
LIMIT 45;

This block runs on map, not workspace — look at the connection label above it. It is a different PostgreSQL server with a different schema, and the 41 osm.* tables listed here do not exist on the connection you were querying a moment ago. Nothing about your SQL changed; the answer to "what tables are there" did.

Run this query yourself

Your own features — all five geometry types at once

SELECT dataset_uuid,
       GeometryType(geom) AS geometry_type,
       geom
FROM geo_dataset_features
WHERE deleted_at IS NULL
  AND geom IS NOT NULL
LIMIT 100;

geo_dataset_features is the workspace's feature store: everything you import lands here, from every dataset, in one table. Which means a single column holds points, lines and polygons together — legal in PostGIS, and a fact you have to design around. Note deleted_at IS NULL: rows are retired, not removed, so every query on this table needs that predicate or it counts ghosts.

Run this query yourself

The five guards on every query

Your SQL is not sent to the database as written. Before it runs it is parsed, checked and wrapped — and knowing exactly how saves you from blaming your query for the editor's rules:

-- what actually reaches Postgres
BEGIN;
SET TRANSACTION READ ONLY;
SET LOCAL statement_timeout = '30s';
SELECT to_jsonb(t) AS row FROM ( <your query> ) t LIMIT 101;
COMMIT;
  1. One statement. Your SQL is parsed into a syntax tree and rejected unless it is a single SELECT. WITH … SELECT and UNION ALL still count as one.
  2. Read only. The transaction cannot write, so UPDATE, INSERT and CREATE fail even if the guard somehow let them through.
  3. Thirty seconds. Past that the statement is cancelled — which is what an unbounded query against 440 million rows looks like from the outside.
  4. A hundred rows. It asks for 101 so it can tell you whether there were more.
  5. Wrapped as JSON. to_jsonb(t) is why geometry comes back as GeoJSON rather than a hex blob, and why every column keeps its name.

Why EXPLAIN does not work here

EXPLAIN ANALYZE SELECT * FROM osm.building_polygon LIMIT 10;

That is an ExplainStmt, not a SelectStmt, so guard 1 rejects it before it reaches the database. The reference book leans on EXPLAIN (ANALYZE, BUFFERS) throughout; here you reason about plans from timings and row counts instead, which lesson 22 covers in full.

The trade is deliberate. A shared editor that anyone can point at a 440-million-row table needs a ceiling more than it needs EXPLAIN.

Ask for 500 rows

SELECT generate_series(1, 500) AS n;

This query generates exactly 500 rows and there is no doubt about it — the count is written into the SQL. You will get 100. The editor asks the database for 101 so it can tell the difference between “that is all of them” and “there are more”, then shows you the first hundred. Nothing errors, nothing warns you in the result itself.

Run this query yourself

Rules of thumb

  • A connection is a database plus a search_path. Same server, different path, different tables.
  • Every query runs READ ONLY, wrapped, capped at 100 rows, cancelled at 30 seconds.
  • Single SELECT only — the AST guard rejects everything else, EXPLAIN included.
  • A hundred rows back does not mean a hundred rows exist.

Run these queries against a live PostGIS. Open the lesson in the app — the same SQL, an editor, and your own data.

← Prerequisites: Joins and Big O · Geometry Is a Type, Not Two Numbers → · All lessons

View as markdown · Edit