SEO

The following bug has been logged online:

Bug reference: 4141
Logged by: Mathias Seiler
Email address: mathias.seiler@gmail.com
PostgreSQL version: 8.3.1
Operating system: Ubuntu: 2.6.20-16-server #2 SMP Tue Feb 12 05:48:21 UTC
2008 i686 GNU/Linux
Description: Aliases in rules vanished, but they work
Details:

Please consider the following:

postgres=# CREATE table foo (
postgres(# id serial,
postgres(# bar text
postgres(# );
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
postgres-# 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
postgres-# 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
WHERE f.id = old.id

postgres=# SELECT version();
version

----------------------------------------------------------------------------
-------------------
PostgreSQL 8.3.1 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2
(Ubuntu 4.1.2-0ubuntu4)
(1 row)

postgres=#

I used an alias to reference "foo" in the rule definition. 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 btw. It deletes all rows in "foo_view" from the table
foo as expected and without any warnings or error messages.

If I should provide more details about my environment (which is pretty
default), let me know.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

"Mathias Seiler" <mathias.seiler@gmail.com> writes:
> postgres=# CREATE RULE foo_delete AS ON DELETE TO foo_view do instead
> postgres-# 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
> WHERE f.id = old.id

Hmm, looks like ruleutils.c got overlooked in the patch that added
support for aliases on UPDATE/DELETE target relations. Will fix.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs