Обсуждение: Possible problem with pg_get_viewdef on Postgres V8.0.0 rc4

I have a view  definition  that creates a view on both Postgres 7 and
Postgres 8. The view definition works OK in that it does not return an
error and the resulting view seems to operate correctly on both versions of
Postgres.

However Pgadmin 1.2.0 works fine with the Postgres 7 database containing
the view but fails with the same database created on Postgres 8.0.0.rc4.

The cause of the pgadmin failure appears to be a call to
pg_get_viewdef(c.oid, true) used by pgadmin to populate its view display.
This function applied to my view works correctly on Postgres 7 but
generates the error Bogus Varno: 3
in Postgres 8.

The original query definition (as generated by Pgadmin 1.2.0 from Postgres
7.4.2) causing the problem was:

CREATE OR REPLACE VIEW full_product_view AS
 SELECT slimprdmgrrspperid AS _prd_slimprdmgrrspperid_,
( SELECT (rspper.lstnme::text || ' '::text) || rspper.frstnme::text
           FROM rspper
          WHERE rspper.rspperid = slimprdmgrrspperid) AS
_prd_slimprdmgrrspperid_d,
prdid AS _prd_prdid_, slimprdnmgnnmeid AS _prd_slimprdnmgnnmeid_,
actvle AS _slimprdnmgnnmeid_gennme_actvle_, catnmeclssid AS
_slimprdnmgnnmeid_gennme_catnmeclssid_
   FROM prd
   JOIN gennme ON gennme.gennmeid = prd.slimprdnmgnnmeid;


AFAICT the pg_get_viewdef function called by pgadmin objects to the scalar
subselect in the above view defintion. Rewriting my view query to include
the table qualification on the scalar sub select cures the problem with
pg_get_viewdef(c.oid, true).

The rewritten query definition is:

CREATE OR REPLACE VIEW full_product_view AS
 SELECT slimprdmgrrspperid AS _prd_slimprdmgrrspperid_,
( SELECT (rspper.lstnme::text || ' '::text) || rspper.frstnme::text
           FROM rspper
          WHERE rspper.rspperid = prd.slimprdmgrrspperid) AS
_prd_slimprdmgrrspperid_d,
prdid AS _prd_prdid_, slimprdnmgnnmeid AS _prd_slimprdnmgnnmeid_,
actvle AS _slimprdnmgnnmeid_gennme_actvle_, catnmeclssid AS
_slimprdnmgnnmeid_gennme_catnmeclssid_
   FROM prd
   JOIN gennme ON gennme.gennmeid = prd.slimprdnmgnnmeid;

   I don't know if this behaviour is expected.

   Regards
   Laurie



   :.________________
   CONFIDENTIALITY : This  e-mail  and  any attachments are confidential
   and may be privileged. If  you are not a named recipient, please notify
   the sender immediately and do not disclose the contents to another
   person, use it for any purpose or store or copy the information in any
   medium.

laurie.burrow@powerconv.alstom.com writes:
> The cause of the pgadmin failure appears to be a call to
> pg_get_viewdef(c.oid, true) used by pgadmin to populate its view display.
> This function applied to my view works correctly on Postgres 7 but
> generates the error Bogus Varno: 3
> in Postgres 8.

Could we see a full example?  The view definition is of little use when
you didn't provide the definitions of the tables it references.

            regards, tom lane

Tom Lane wrote:

> Could we see a full example?  The view definition is of little use when
> you didn't provide the definitions of the tables it references.

   Mea Culpa!

   The view references prd, and rspper tables whose defintions are shown
   below.

   I've included the gennme table as it is referenced by prd, and the
   nmeclss table as it is referenced by gennme

   CREATE TABLE prd
(
  slimprdmgrrspperid int4,
  prdid int4 NOT NULL DEFAULT nextval('PrdPrdID_seq'::text),
  slimprdnmgnnmeid int4 NOT NULL,
  CONSTRAINT prdpk PRIMARY KEY (prdid),
  CONSTRAINT gennmefk5 FOREIGN KEY (slimprdnmgnnmeid) REFERENCES gennme
(gennmeid) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT rspperfk2 FOREIGN KEY (slimprdmgrrspperid) REFERENCES rspper
(rspperid) ON UPDATE CASCADE ON DELETE NO ACTION
)
WITH OIDS;

CREATE TABLE rspper
(
  rspperid int4 NOT NULL DEFAULT nextval('RspPerRspPerID_seq'::text),
  ctctnt text,
  lstnme varchar(100) NOT NULL,
  eml varchar(100),
  frstnme varchar(100) NOT NULL,
  addr varchar(200),
  phn varchar(100),
  mob varchar(100),
  CONSTRAINT rspperpk PRIMARY KEY (rspperid),
  CONSTRAINT rspperak1_uc2 UNIQUE (frstnme, lstnme, phn)
)
WITH OIDS;

CREATE TABLE gennme
(
  gennmeid int4 NOT NULL DEFAULT nextval('GenNmeGenNmeID_seq'::text),
  actvle varchar(200) NOT NULL,
  lblloc varchar(1000),
  catnmeclssid varchar(200) NOT NULL,
  CONSTRAINT gennmepk PRIMARY KEY (gennmeid),
  CONSTRAINT nmeclssfk1 FOREIGN KEY (catnmeclssid) REFERENCES nmeclss
(nmeclssid) ON UPDATE CASCADE ON DELETE NO ACTION
)

CREATE TABLE nmeclss
(
  nmeclssid varchar(200) NOT NULL DEFAULT nextval
('NmeClssNmeClssID_seq'::text),
  CONSTRAINT nmeclsspk PRIMARY KEY (nmeclssid)
)
WITH OIDS;

Regards
Laurie

:.________________
CONFIDENTIALITY : This  e-mail  and  any attachments are confidential and
may be privileged. If  you are not a named recipient, please notify the
sender immediately and do not disclose the contents to another person, use
it for any purpose or store or copy the information in any medium.

laurie.burrow@powerconv.alstom.com writes:
> Tom Lane wrote:
>> Could we see a full example?  The view definition is of little use when
>> you didn't provide the definitions of the tables it references.

>    Mea Culpa!

>    The view references prd, and rspper tables whose defintions are shown
>    below.

OK, I found the problem.  If you need a patch right away, it's attached.

            regards, tom lane


*** src/backend/utils/adt/ruleutils.c.orig    Sun Dec 12 19:33:06 2004
--- src/backend/utils/adt/ruleutils.c    Thu Jan 13 12:19:10 2005
***************
*** 182,188 ****
  static Node *get_rule_sortgroupclause(SortClause *srt, List *tlist,
                           bool force_colno,
                           deparse_context *context);
! static void get_names_for_var(Var *var, deparse_context *context,
                    char **schemaname, char **refname, char **attname);
  static RangeTblEntry *find_rte_by_refname(const char *refname,
                      deparse_context *context);
--- 182,188 ----
  static Node *get_rule_sortgroupclause(SortClause *srt, List *tlist,
                           bool force_colno,
                           deparse_context *context);
! static void get_names_for_var(Var *var, int levelsup, deparse_context *context,
                    char **schemaname, char **refname, char **attname);
  static RangeTblEntry *find_rte_by_refname(const char *refname,
                      deparse_context *context);
***************
*** 1998,2004 ****
                  char       *refname;
                  char       *attname;

!                 get_names_for_var(var, context,
                                    &schemaname, &refname, &attname);
                  tell_as = (attname == NULL ||
                             strcmp(attname, colname) != 0);
--- 1998,2004 ----
                  char       *refname;
                  char       *attname;

!                 get_names_for_var(var, 0, context,
                                    &schemaname, &refname, &attname);
                  tell_as = (attname == NULL ||
                             strcmp(attname, colname) != 0);
***************
*** 2392,2397 ****
--- 2392,2401 ----
  /*
   * Get the schemaname, refname and attname for a (possibly nonlocal) Var.
   *
+  * In some cases (currently only when recursing into an unnamed join)
+  * the Var's varlevelsup has to be interpreted with respect to a context
+  * above the current one; levelsup indicates the offset.
+  *
   * schemaname is usually returned as NULL.    It will be non-null only if
   * use of the unqualified refname would find the wrong RTE.
   *
***************
*** 2404,2420 ****
   * distinguish this case.)
   */
  static void
! get_names_for_var(Var *var, deparse_context *context,
                    char **schemaname, char **refname, char **attname)
  {
      deparse_namespace *dpns;
      RangeTblEntry *rte;

      /* Find appropriate nesting depth */
!     if (var->varlevelsup >= list_length(context->namespaces))
!         elog(ERROR, "bogus varlevelsup: %d", var->varlevelsup);
      dpns = (deparse_namespace *) list_nth(context->namespaces,
!                                           var->varlevelsup);

      /* Find the relevant RTE */
      if (var->varno >= 1 && var->varno <= list_length(dpns->rtable))
--- 2408,2427 ----
   * distinguish this case.)
   */
  static void
! get_names_for_var(Var *var, int levelsup, deparse_context *context,
                    char **schemaname, char **refname, char **attname)
  {
+     int            netlevelsup;
      deparse_namespace *dpns;
      RangeTblEntry *rte;

      /* Find appropriate nesting depth */
!     netlevelsup = var->varlevelsup + levelsup;
!     if (netlevelsup >= list_length(context->namespaces))
!         elog(ERROR, "bogus varlevelsup: %d offset %d",
!              var->varlevelsup, levelsup);
      dpns = (deparse_namespace *) list_nth(context->namespaces,
!                                           netlevelsup);

      /* Find the relevant RTE */
      if (var->varno >= 1 && var->varno <= list_length(dpns->rtable))
***************
*** 2467,2473 ****
                                              var->varattno-1);
                  if (IsA(aliasvar, Var))
                  {
!                     get_names_for_var(aliasvar, context,
                                        schemaname, refname, attname);
                      return;
                  }
--- 2474,2480 ----
                                              var->varattno-1);
                  if (IsA(aliasvar, Var))
                  {
!                     get_names_for_var(aliasvar, netlevelsup, context,
                                        schemaname, refname, attname);
                      return;
                  }
***************
*** 2867,2873 ****
                  char       *refname;
                  char       *attname;

!                 get_names_for_var(var, context,
                                    &schemaname, &refname, &attname);
                  if (refname && (context->varprefix || attname == NULL))
                  {
--- 2874,2880 ----
                  char       *refname;
                  char       *attname;

!                 get_names_for_var(var, 0, context,
                                    &schemaname, &refname, &attname);
                  if (refname && (context->varprefix || attname == NULL))
                  {