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
