bobbai00 commented on code in PR #6196:
URL: https://github.com/apache/texera/pull/6196#discussion_r3525631751
##########
bin/local-dev/main.sh:
##########
@@ -1343,30 +1353,67 @@ infra_ensure_db_schema() {
tui_warn "postgres: not ready after 30s -- skipping schema check"
return 0
fi
- # `feedback` is one of the newer tables; if it exists we assume the
- # whole schema is current. (texera_ddl.sql is idempotent with
- # CREATE TABLE IF NOT EXISTS, so re-applying it is safe even if some
- # tables already exist, but skipping the copy + exec is faster.)
- local has_feedback=""
- has_feedback=$(docker exec "$pg" psql -U texera -d texera_db -tAc \
- "SELECT 1 FROM pg_tables WHERE schemaname='texera_db' AND
tablename='feedback'" \
- 2>/dev/null || true)
- if [[ "$has_feedback" == "1" ]]; then
- tui_skip "postgres: schema already current"
- return 0
- fi
- tui_step "postgres: applying sql/texera_ddl.sql (one-time bootstrap)"
+
local ddl="$REPO_ROOT/sql/texera_ddl.sql"
if [[ ! -f "$ddl" ]]; then
tui_warn "postgres: $ddl not found -- skipping (jOOQ codegen may fail)"
return 0
fi
- docker cp "$ddl" "$pg":/tmp/texera_ddl.sql >/dev/null
- if docker exec -u postgres "$pg" psql -U texera -f /tmp/texera_ddl.sql
>/dev/null 2>&1; then
- tui_ok "postgres: schema bootstrapped"
- else
- tui_warn "postgres: ddl exec returned non-zero (check container logs)"
+
+ # Tables the current schema defines, parsed straight from the DDL.
+ # Handles both bare and double-quoted names (e.g. "user").
+ local expected
+ expected=$(grep -ioE 'CREATE TABLE IF NOT
EXISTS[[:space:]]+"?[a-zA-Z_][a-zA-Z0-9_]*"?' "$ddl" \
+ | sed -E 's/^.*NOT EXISTS[[:space:]]+//I; s/"//g' | sort -u)
+ if [[ -z "$expected" ]]; then
+ tui_warn "postgres: could not parse expected tables from $ddl --
skipping schema check"
+ return 0
+ fi
+
+ # Tables actually present in the live schema.
+ local live
+ live=$(docker exec "$pg" psql -U texera -d texera_db -tAc \
+ "SELECT tablename FROM pg_tables WHERE schemaname='texera_db'"
2>/dev/null | sort -u || true)
+
+ local t have=0 missing=()
+ while IFS= read -r t; do
+ [[ -z "$t" ]] && continue
+ if grep -qxF "$t" <<<"$live"; then
+ have=$((have+1))
+ else
+ missing+=("$t")
+ fi
+ done <<<"$expected"
+
+ if (( ${#missing[@]} == 0 )); then
+ tui_skip "postgres: schema already current"
+ return 0
+ fi
+
+ if (( have == 0 )); then
+ # Empty volume — safe to bootstrap from the (destructive) DDL.
+ tui_step "postgres: applying sql/texera_ddl.sql (one-time bootstrap)"
+ docker cp "$ddl" "$pg":/tmp/texera_ddl.sql >/dev/null
+ if docker exec -u postgres "$pg" psql -U texera -f /tmp/texera_ddl.sql
>/dev/null 2>&1; then
+ tui_ok "postgres: schema bootstrapped"
+ else
+ tui_warn "postgres: ddl exec returned non-zero (check container
logs)"
+ fi
+ return 0
fi
+
+ # Partially-populated volume that predates the current schema. Running
+ # texera_ddl.sql here would DROP the database, so refuse and tell the user
+ # how to resolve it — far better than the misleading downstream compile
+ # error the old code produced (#6194).
+ tui_err "postgres: schema is stale — missing ${#missing[@]} table(s):
${missing[*]}"
+ printf " ${DIM}The DB volume predates the current schema.
sql/texera_ddl.sql is destructive\n"
+ printf " (it DROPs & recreates the database) so it is not auto-applied.\n"
+ printf " Apply the matching migration(s) from sql/updates/*.sql, e.g.:\n"
+ printf " docker exec -i %s psql -U texera -d texera_db <
sql/updates/<N>.sql\n" "$pg"
+ printf " or reset the volume (DESTROYS local data) and re-run:\n"
+ printf " docker compose -p %s down -v && bin/local-dev.sh
up${RESET}\n" "$DOCKER_PROJECT"
+ exit 1
Review Comment:
Good call — done in fa2a555. Instead of erroring out, a stale volume now
auto-applies just the additive `CREATE TABLE ... IF NOT EXISTS` statements from
`texera_ddl.sql` (the destructive DROP DATABASE/DROP TABLE parts are excluded),
so existing tables are skipped and only the missing ones are created — data
preserved, and applied in FK-dependency order. Verified against a live DB:
dropping a table then re-running recreates only that table and leaves the
others untouched. (Also broadened the parse to plain `CREATE TABLE` so
`operator_port_executions`/`operator_port_cache` are covered.)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
