Postico 2: Error "bogus varno: 2" when viewing Structure or DDL tab
- Dominant language
- Python
- Stars
- 486
- Forks
- 8
- PR merge metrics
- No merged PRs in 30d
Description
## What did you do?
- I clicked on table, then selected the "Structure" or "DDL" tab
- The screens are not showed properly due to a bug in the metadata query. It shows a error: "PostgreSQL said: bogus varno: 2"
## What did you expect to happen?
- I expected to see the "Structure" and "DDL" tab contents.
## What software versions are you using?
Postico version:
2.0beta (8052)
macOS version:
macOS Catalina version 10.15.7
PostgreSQL version:
PostgreSQL 13.2 on x86_64-apple-darwin19.6.0, compiled by Apple clang version 12.0.0 (clang-1200.0.32.29), 64-bit
## Additional info:
The problem occurs when trying to open a table with self referencing foreign keys and recursive functions with triggers.
I looked at the query log and found the problem in the following query (the problem is on the select for trigger_when_conditions' column):
```sql
SELECT
pg_namespace.nspname
,rel.relname
,rel.relkind
,rel.oid
,pg_extension.extname
,array(SELECT attname FROM pg_attribute WHERE (attnum>0 OR attname='oid') AND attrelid=rel.oid AND NOT attisdropped
ORDER BY attnum) AS column_names
,array(SELECT format_type(atttypid, atttypmod) FROM pg_attribute WHERE (attnum>0 OR attname='oid') AND attrelid=rel.oid AND NOT attisdropped
ORDER BY attnum) AS column_types
,array(
SELECT
col_description(attrelid, attnum)
FROM pg_attribute WHERE (attnum>0 OR attname='oid') AND attrelid=rel.oid AND NOT attisdropped
ORDER BY attnum
) AS column_comments
,array(SELECT oid FROM pg_trigger WHERE pg_trigger.tgrelid = rel.oid AND NOT pg_trigger.tgisinternal ORDER BY pg_trigger.oid) AS trigger_oids
,array(SELECT tgname FROM pg_trigger WHERE pg_trigger.tgrelid = rel.oid AND NOT pg_trigger.tgisinternal ORDER BY pg_trigger.oid) AS trigger_names
,array(
SELECT
obj_description(pg_trigger.oid, 'pg_trigger')
FROM pg_trigger WHERE pg_trigger.tgrelid = rel.oid AND NOT pg_trigger.tgisinternal ORDER BY pg_trigger.oid
) AS trigger_comments
,array(SELECT tgconstraint FROM pg_trigger WHERE pg_trigger.tgrelid = rel.oid AND NOT pg_trigger.tgisinternal ORDER BY pg_trigger.oid) AS trigger_constraints
,array(SELECT tgtype FROM pg_trigger WHERE pg_trigger.tgrelid = rel.oid AND NOT pg_trigger.tgisinternal ORDER BY pg_trigger.oid) AS trigger_types
,array(SELECT tgfoid FROM pg_trigger WHERE pg_trigger.tgrelid = rel.oid AND NOT pg_trigger.tgisinternal ORDER BY pg_trigger.oid) AS trigger_function_oids
,array(
SELECT nspname FROM pg_proc
LEFT JOIN pg_trigger ON pg_trigger.tgfoid = pg_proc.oid
LEFT JOIN pg_namespace ON pg_namespace.oid = pg_proc.pronamespace
WHERE pg_trigger.tgrelid = rel.oid AND NOT pg_trigger.tgisinternal ORDER BY pg_trigger.oid
) as trigger_function_schema_names
,array(
SELECT proname FROM pg_proc
LEFT JOIN pg_trigger ON pg_trigger.tgfoid = pg_proc.oid
LEFT JOIN pg_namespace ON pg_namespace.oid = pg_proc.pronamespace
WHERE pg_trigger.tgrelid = rel.oid AND NOT pg_trigger.tgisinternal ORDER BY pg_trigger.oid
) as trigger_function_names
,array(SELECT tgdeferrable FROM pg_trigger WHERE pg_trigger.tgrelid = rel.oid AND NOT pg_trigger.tgisinternal ORDER BY pg_trigger.oid) AS trigger_is_deferrable_values
,array(SELECT tginitdeferred FROM pg_trigger WHERE pg_trigger.tgrelid = rel.oid AND NOT pg_trigger.tgisinternal ORDER BY pg_trigger.oid) AS trigger_is_init_deferred_values
,array(SELECT tgnargs FROM pg_trigger WHERE pg_trigger.tgrelid = rel.oid AND NOT pg_trigger.tgisinternal ORDER BY pg_trigger.oid) AS trigger_nargs_values
,array(SELECT "encode"(tgargs::bytea, 'escape') FROM pg_trigger WHERE pg_trigger.tgrelid = rel.oid AND NOT pg_trigger.tgisinternal ORDER BY pg_trigger.oid
) AS trigger_args_values
,array(SELECT tgoldtable FROM pg_trigger WHERE pg_trigger.tgrelid = rel.oid AND NOT pg_trigger.tgisinternal ORDER BY pg_trigger.oid) AS trigger_old_table_values
,array(SELECT tgnewtable FROM pg_trigger WHERE pg_trigger.tgrelid = rel.oid AND NOT pg_trigger.tgisinternal ORDER BY pg_trigger.oid) AS trigger_new_table_values
,array(SELECT pg_get_expr(tgqual, tgrelid) FROM pg_trigger WHERE pg_trigger.tgrelid = rel.oid AND NOT pg_trigger.tgisinternal ORDER BY pg_trigger.oid) AS trigger_when_conditions
,array(SELECT array_to_string(array_agg(quote_ident(attname)), ',') FROM pg_trigger
LEFT JOIN pg_attribute ON attrelid = pg_trigger.tgrelid AND attnum=ANY(pg_trigger.tgattr)
WHERE pg_trigger.tgrelid=rel.oid AND NOT pg_trigger.tgisinternal
GROUP BY pg_trigger.oid
ORDER BY pg_trigger.oid
) AS trigger_column_names
FROM pg_class rel
LEFT JOIN pg_depend ON pg_depend.refclassid = 'pg_catalog.pg_extension'::regclass
AND pg_depend.classid = 'pg_catalog.pg_class'::regclass
AND pg_depend.objid = rel.oid
LEFT JOIN pg_extension ON pg_depend.refobjid = pg_extension.oid
LEFT JOIN pg_index ON (indrelid = rel.oid)
JOIN pg_namespace ON (rel.relnamespace = pg_namespace.oid )
WHERE rel.relkind IN ('r','v','m','f','p') AND nspname = 'public' AND rel.relname = 'tbl_account' GROUP BY rel.relname, rel.relkind, rel.oid
,pg_extension.extname
,pg_namespace.nspname
;
```
I'm leaving a minimal database example to help to spot the bug:
[db_postico_bug.sql.zip](https://github.com/jakob/Postico/files/6410044/db_postico_bug.sql.zip)
Contributor guide
No contributing guide indexed for this repository
Research direction
Start by loading the supplied db_postico_bug.sql.zip reproduction and running the metadata query from the issue, focusing on the trigger_when_conditions expression using pg_get_expr. Confirm the failure when opening the Structure or DDL tabs, then verify that both tabs render correctly for the self-referencing and recursive-trigger case.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql
- Domain
- database
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 38/100