You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@texera.apache.org by "bobbai00 (via GitHub)" <gi...@apache.org> on 2026/07/05 18:43:50 UTC
[PR] fix(local-dev): detect stale DB schema from the full DDL, not just `feedback` [texera]
bobbai00 opened a new pull request, #6196:
URL: https://github.com/apache/texera/pull/6196
### What changes were proposed in this PR?
Fixes the schema-currency check in `bin/local-dev/main.sh` (`infra_ensure_db_schema`). It previously probed a single hard-coded table (`feedback`) as a proxy for "schema is current", so any table added *after* `feedback` was invisible: a carried-over Postgres volume reported `postgres: schema already current`, the DDL was skipped, jOOQ codegen (`common/dao`) ran against a stale schema, and `sbt dist` failed with a **misleading** `not found: value <TABLE>` error that gave no hint the real cause was a stale DB.
Now the expected table set is derived from `sql/texera_ddl.sql` itself, so the check can't drift when a new table is added. Because `texera_ddl.sql` is **not** idempotent (it `DROP`s and recreates the database and every table), it is only run to bootstrap a genuinely empty volume; on a partially-stale volume the script fails fast listing the missing tables and how to resolve them, instead of silently skipping (misleading build failure) or destructively wiping local data.
### Any related issues, documentation, discussions?
Closes #6194
### How was this PR tested?
Manually, against the `bin/local-dev.sh` stack:
- Verified the DDL parser extracts all 32 tables (including `workflow_cover_image`, the table whose absence triggered the original failure).
- Stale volume (missing `workflow_cover_image`): the check now fails fast with the missing-table list and remediation steps rather than reporting "schema already current".
- Current volume (all tables present): reports `schema already current` and skips, as before.
- Empty volume: bootstraps from `texera_ddl.sql`, as before.
- `bash -n bin/local-dev/main.sh` passes.
### Was this PR authored or co-authored using generative AI tooling?
Generated-by: Claude Opus 4.8 (1M context)
--
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: notifications-unsubscribe@texera.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] fix(local-dev): detect stale DB schema from the full DDL [texera]
Posted by "Yicong-Huang (via GitHub)" <gi...@apache.org>.
Yicong-Huang commented on code in PR #6196:
URL: https://github.com/apache/texera/pull/6196#discussion_r3525557235
##########
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:
can we apply the diff ddls?
--
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: notifications-unsubscribe@texera.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] fix(local-dev): detect stale DB schema from the full DDL, not just `feedback` [texera]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #6196:
URL: https://github.com/apache/texera/pull/6196#issuecomment-4887183818
<!-- texera-reviewer-suggestion -->
### Automated Reviewer Suggestions
Based on the `git blame` history of the changed files, we recommend the following reviewers:
- **Contributors with relevant context:** `@Yicong-Huang`
You can notify them by mentioning `@Yicong-Huang` in a comment.
--
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: notifications-unsubscribe@texera.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] fix(local-dev): detect stale DB schema from the full DDL [texera]
Posted by "Yicong-Huang (via GitHub)" <gi...@apache.org>.
Yicong-Huang commented on code in PR #6196:
URL: https://github.com/apache/texera/pull/6196#discussion_r3525644354
##########
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:
sorry, see #6145
--
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: notifications-unsubscribe@texera.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] fix(local-dev): detect stale DB schema from the full DDL [texera]
Posted by "bobbai00 (via GitHub)" <gi...@apache.org>.
bobbai00 closed pull request #6196: fix(local-dev): detect stale DB schema from the full DDL
URL: https://github.com/apache/texera/pull/6196
--
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: notifications-unsubscribe@texera.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] fix(local-dev): detect stale DB schema from the full DDL [texera]
Posted by "bobbai00 (via GitHub)" <gi...@apache.org>.
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: notifications-unsubscribe@texera.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] fix(local-dev): detect stale DB schema from the full DDL [texera]
Posted by "Yicong-Huang (via GitHub)" <gi...@apache.org>.
Yicong-Huang commented on PR #6196:
URL: https://github.com/apache/texera/pull/6196#issuecomment-4888952972
@bobbai00 why do we close this PR? is it not needed any more? how about the issue #6194?
--
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: notifications-unsubscribe@texera.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org