pgsql-4141

Version:

8.3.1

Bug Link:

http://postgresql.1045698.n5.nabble.com/BUG-4141-Aliases-in-rules-vanished-but-they-work-td2126266.html

Patch Link:

http://archives.postgresql.org/pgsql-committers/2008-05/msg00023.php

Symptom:

Aliases in rules vanished from dump,(although they work if used directly), and later when import it you will get an error.

Failure type:

incomplete result

Is there any log message?

Yes

How it is diagnosed:

We reproduced the failure.

How to reproduce:

postgres=# CREATE table foo ( id serial, bar text );

NOTICE:  CREATE TABLE will create implicit sequence "foo_id_seq" for serial

column "foo.id"

CREATE TABLE

postgres=# \d

           List of relations

 Schema |    Name    |   Type   | Owner

--------+------------+----------+-------

 public | foo        | table    | root

 public | foo_id_seq | sequence | root

(2 rows)

postgres=# CREATE VIEW foo_view AS  SELECT * from foo limit 10;

CREATE VIEW 

postgres=# \d foo_view

    View "public.foo_view"

 Column |  Type   | Modifiers

--------+---------+-----------

 id     | integer |

 bar    | text    |

View definition:

 SELECT foo.id, foo.bar

   FROM foo

 LIMIT 10;

postgres=# CREATE RULE foo_delete AS ON DELETE TO foo_view do instead

         DELETE FROM foo f where f.id = old.id;

CREATE RULE

postgres=# \d foo_view

    View "public.foo_view"

 Column |  Type   | Modifiers

--------+---------+-----------

 id     | integer |

 bar    | text    |

View definition:

 SELECT foo.id, foo.bar

   FROM foo

 LIMIT 10;

Rules:

 foo_delete AS

    ON DELETE TO foo_view DO INSTEAD  DELETE FROM foo <--should be DELETE FROM foo f...

  WHERE f.id = old.id    

Here's the problem: If you dump this view (with rules) you get the same as above, a

"DELETE FROM foo WHERE ...": the alias is missing. As a result, the dump is

corrupted and produces errors while importing it, saying: "missing

FROM-clause entry for table "f" ...".

The view rule works. It deletes all rows in "foo_view" from the table

foo as expected and without any warnings or error messages

Root Cause:

They forgot to print the alias on target table to the dump file, later when restore from the dump, it encounter a parse error.

get_update_query_def(Query *query, deparse_context *context)

{

        StringInfo        buf = context->buf;

        char           *sep;

        RangeTblEntry *rte;

        ListCell   *l;

        /*

         * Start the query with UPDATE relname SET

         */

        rte = rt_fetch(query->resultRelation, query->rtable);

        Assert(rte->rtekind == RTE_RELATION);

        if (PRETTY_INDENT(context))

        {

                appendStringInfoChar(buf, ' ');

                context->indentLevel += PRETTYINDENT_STD;

        }

-        appendStringInfo(buf, "UPDATE %s%s SET ",

-                                         only_marker(rte),

-                                         generate_relation_name(rte->relid));

+        appendStringInfo(buf, "UPDATE %s%s",

+                                         only_marker(rte),

+                                         generate_relation_name(rte->relid));

+         if (rte->alias != NULL)

+                 appendStringInfo(buf, " %s",

+                                                  quote_identifier(rte->alias->aliasname));

+         appendStringInfoString(buf, " SET ");

          ...

}

get_delete_query_def(Query *query, deparse_context *context)

{

        StringInfo        buf = context->buf;

        RangeTblEntry *rte;

        rte = rt_fetch(query->resultRelation, query->rtable);

        Assert(rte->rtekind == RTE_RELATION);

        if (PRETTY_INDENT(context))

        {

                context->indentLevel += PRETTYINDENT_STD;

                appendStringInfoChar(buf, ' ');

        }

        appendStringInfo(buf, "DELETE FROM %s%s",

                                         only_marker(rte),

                                         generate_relation_name(rte->relid));

+         if (rte->alias != NULL)
+                 appendStringInfo(buf, " %s",
+                                                  quote_identifier(rte->alias->aliasname));

        ...

}

Where the error message is printed:

qualifiedNameToVar(...)

{

        RangeTblEntry *rte;

        int                        sublevels_up;

        rte = refnameRangeTblEntry(...);

        if (rte == NULL)

        {

                if (!implicitRTEOK)

                        return NULL;

                rte = addImplicitRTE(...);<--essentially print error message

        }

        return scanRTEForColumn(pstate, rte, colname, location);

}

addImplicitRTE(ParseState *pstate, RangeVar *relation, int location)

{

        RangeTblEntry *rte;

        /* issue warning or error as needed */

        warnAutoRange(pstate, relation, location);

        ...

}

warnAutoRange(ParseState *pstate, RangeVar *relation, int location)

{

        RangeTblEntry *rte;

        ...

        rte = searchRangeTable(pstate, relation);

        ...

        if (!add_missing_from)

        {

                if (rte)

                        ereport(...);

                else

                        ereport(ERROR,..

                                 errmsg("missing FROM-clause entry in subquery for table                                                 \"%s\"",relation->relname) :...));

        }

        else

        {

                /* just issue a warning */

                ...

        }

}

Pattern to handle:

return value check