jakob / jakob/Postico

Postico 2: Error "bogus varno: 2" when viewing Structure or DDL tab

Open
#763 4 comments 0 reactions 0 assignees View on GitHub
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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.