Thread: Getting ERROR: bogus varno: 2
Hi,
Getting "ERROR: bogus varno: 2" and below is the sample SQL.
- Create table "test_bogus" as below.
CREATE TABLE test_bogus(
id serial PRIMARY KEY,
display_name text NOT NULL,
description text NOT NULL,
object_type integer NOT NULL,
sp_oid integer NOT NULL DEFAULT 0
);
- Create procedure as below.
CREATE OR REPLACE FUNCTION update_sp_oid() RETURNS trigger AS $$
BEGIN
EXECUTE 'SELECT CASE WHEN MAX(sp_oid) > 0 THEN MAX(sp_oid) + 1 ELSE 1 END FROM test_bogus WHERE object_type = $1' USING NEW.object_type INTO NEW.sp_oid;
RETURN NEW;
END
$$ LANGUAGE plpgsql;
- Create trigger on table as below.
CREATE TRIGGER test_bogus_sp_oid
BEFORE UPDATE ON test_bogus
FOR EACH ROW
WHEN (OLD.object_type != NEW.object_type)
EXECUTE PROCEDURE update_sp_oid();
- Execute below sql to get the result and it shows error "bogus varno: 2".
SELECT t.oid,t.tgname AS name, t.xmin, t.*, relname,
CASE WHEN relkind = 'r' THEN TRUE ELSE FALSE END AS parentistable,
nspname, des.description, l.lanname, p.prosrc, p.proname AS tfunction,
trim(pg_catalog.pg_get_expr(t.tgqual, t.tgrelid), '()') AS whenclause,
(CASE WHEN tgconstraint != 0::OID THEN true ElSE false END) AS is_constraint_trigger,
(CASE WHEN tgenabled = 'O' THEN true ElSE false END) AS is_enable_trigger,
tgoldtable,
tgnewtable
FROM pg_trigger t
JOIN pg_class cl ON cl.oid=tgrelid
JOIN pg_namespace na ON na.oid=relnamespace
LEFT OUTER JOIN pg_description des ON (des.objoid=t.oid AND des.classoid='pg_trigger'::regclass)
LEFT OUTER JOIN pg_proc p ON p.oid=t.tgfoid
LEFT OUTER JOIN pg_language l ON l.oid=p.prolang
WHERE NOT tgisinternal
AND tgrelid = 22584::OID
AND t.oid = 22595::OID
ORDER BY tgname;
Below is the example, i have executed above mentioned command on psql prompt.
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 10.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit
(1 row)
postgres=# CREATE TABLE test_bogus(
postgres(# id serial PRIMARY KEY,
postgres(# display_name text NOT NULL,
postgres(# description text NOT NULL,
postgres(# object_type integer NOT NULL,
postgres(# sp_oid integer NOT NULL DEFAULT 0
postgres(# );
CREATE TABLE
postgres=#
postgres=#
postgres=# CREATE OR REPLACE FUNCTION update_sp_oid() RETURNS trigger AS $$
postgres$# BEGIN
postgres$# EXECUTE 'SELECT CASE WHEN MAX(sp_oid) > 0 THEN MAX(sp_oid) + 1 ELSE 1 END FROM test_bogus WHERE object_type = $1' USING NEW.object_type
INTO NEW.sp_oid;
postgres$# RETURN NEW;
postgres$# END
postgres$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=#
postgres=#
postgres=#
postgres=#
postgres=# CREATE TRIGGER test_bogus_sp_oid
postgres-# BEFORE UPDATE ON test_bogus
postgres-# FOR EACH ROW
postgres-# WHEN (OLD.object_type != NEW.object_type)
postgres-# EXECUTE PROCEDURE update_sp_oid();
CREATE TRIGGER
postgres=#
postgres=#
postgres=#
postgres=# SELECT rel.oid, rel.relname AS name
postgres-# FROM pg_class rel
postgres-# WHERE rel.relkind IN ('r','s','t') AND rel.relnamespace = 2200::oid
postgres-# ORDER BY rel.relname;
oid | name
-------+------------
22584 | test_bogus
(1 row)
postgres=# SELECT t.oid, t.tgname as name, (CASE WHEN tgenabled = 'O' THEN true ElSE false END) AS is_enable_trigger FROM pg_trigger t WHERE tgrelid =
21723::OID ORDER BY tgname;
oid | name | is_enable_trigger
-----+------+-------------------
(0 rows)
postgres=# SELECT t.oid, t.tgname as name, (CASE WHEN tgenabled = 'O' THEN true ElSE false END) AS is_enable_trigger FROM pg_trigger t WHERE tgrelid =
22584::OID ORDER BY tgname;
oid | name | is_enable_trigger
-------+-------------------+-------------------
22595 | test_bogus_sp_oid | t
(1 row)
postgres=# SELECT t.oid,t.tgname AS name, t.xmin, t.*, relname, CASE WHEN relkind = 'r' THEN TRUE ELSE FALSE END AS parentistable,
postgres-# nspname, des.description, l.lanname, p.prosrc, p.proname AS tfunction,
postgres-# trim(pg_catalog.pg_get_expr(t.tgqual, t.tgrelid), '()') AS whenclause,
postgres-# (CASE WHEN tgconstraint != 0::OID THEN true ElSE false END) AS is_constraint_trigger,
postgres-# (CASE WHEN tgenabled = 'O' THEN true ElSE false END) AS is_enable_trigger,
postgres-# tgoldtable,
postgres-# tgnewtable
postgres-# FROM pg_trigger t
postgres-# JOIN pg_class cl ON cl.oid=tgrelid
postgres-# JOIN pg_namespace na ON na.oid=relnamespace
postgres-# LEFT OUTER JOIN pg_description des ON (des.objoid=t.oid AND des.classoid='pg_trigger'::regclass)
postgres-# LEFT OUTER JOIN pg_proc p ON p.oid=t.tgfoid
postgres-# LEFT OUTER JOIN pg_language l ON l.oid=p.prolang
postgres-# WHERE NOT tgisinternal
postgres-# AND tgrelid = 22584::OID
postgres-# AND t.oid = 22595::OID
postgres-# ORDER BY tgname;
ERROR: bogus varno: 2
postgres=#
Is this error message expected or what should be the behaviour ? Let us know your thoughts.
Thanks,
Neel Patel
Neel Patel <neel.patel@enterprisedb.com> writes:
> Getting "*ERROR: bogus varno: 2*" and below is the sample SQL.
Hmm, reproduced here on HEAD.
> Is this error message expected or what should be the behaviour ?
It's certainly a bug. Don't know the cause yet, but it looks like
pg_get_expr() is getting confused:
#0 errfinish (dummy=0) at elog.c:411
#1 0x00000000008aea4f in elog_finish (elevel=<value optimized out>,
fmt=0xaa30e3 "bogus varno: %d") at elog.c:1365
#2 0x000000000084d761 in resolve_special_varno (node=0x2e70598,
context=0x7ffc79094e80, private=0x0,
callback=0x852b90 <get_special_variable>) at ruleutils.c:6862
#3 0x00000000008529d5 in get_variable (var=0x2e70598,
levelsup=<value optimized out>, istoplevel=false, context=0x7ffc79094e80)
at ruleutils.c:6652
#4 0x0000000000850c21 in get_rule_expr (node=0x2e70598,
context=0x7ffc79094e80, showimplicit=true) at ruleutils.c:7812
#5 0x00000000008521ba in get_oper_expr (node=0x2e70488,
context=0x7ffc79094e80, showimplicit=<value optimized out>)
at ruleutils.c:9076
#6 get_rule_expr (node=0x2e70488, context=0x7ffc79094e80,
showimplicit=<value optimized out>) at ruleutils.c:7921
#7 0x0000000000858a0d in deparse_expression_pretty (expr=0x2e70488,
dpcontext=0x2e70be8, forceprefix=<value optimized out>,
showimplicit=false, prettyFlags=2, startIndent=0) at ruleutils.c:3202
#8 0x000000000085a682 in pg_get_expr_worker (expr=<value optimized out>,
relid=33887, relname=0x2e70248 "test_bogus", prettyFlags=2)
at ruleutils.c:2393
regards, tom lane
I wrote:
> Neel Patel <neel.patel@enterprisedb.com> writes:
>> Is this error message expected or what should be the behaviour ?
> It's certainly a bug.
Oh, no, I take that back: it's not a bug, you're just abusing
pg_get_expr() to try to do something it can't do, which is make
sense of an expression involving more than one relation.
(OLD and NEW are different relations in a trigger WHEN clause.)
You can use pg_get_triggerdef() to decompile a trigger WHEN clause,
although that might do more than you want.
Not sure if there's any value in trying to make the failure
message more user-friendly. You can get weird errors by
misusing pg_get_expr() in other ways too, such as giving it
the wrong relation OID.
regards, tom lane