[BUGS] BUG #4656: Indexes not used when comparing nextval() and currval() to integers

[Mathias Seiler]

The following bug has been logged online:

Bug reference:  4656
Logged by:  Mathias Seiler
Email address:  mathias.sei...@gmail.com
PostgreSQL version: 8.3.6
Operating system:   Debian Linux Lenny (testing)
Description:Indexes not used when comparing nextval() and currval()
to integers
Details: 

Hello there

I'm not sure if I'm doing something terribly wrong here, but I when I
noticed a slowdown during a large transaction I dig into the problem and
found that when I use this prepared statement:

UPDATE booking_entries SET date = ? where id =
currval('booking_entries_id_seq'::regclass);

The index over the column "id" is not used. This obviously results in a full
table scan, which gets very slow after a few thousand entries.

So I tried to cast the returning value from currval() to integer (which is
the same type of id) but this still doesn't use the index (which is there):

EXPLAIN UPDATE booking_entries SET booking_date = now() where id =
nextval('booking_entries_id_seq'::regclass)::int4;
   QUERY PLAN
-
 Seq Scan on booking_entries  (cost=0.00..351.95 rows=1 width=89)
   Filter: (id = (nextval('booking_entries_id_seq'::regclass))::integer)
(2 rows)

set enable_seqscan = false;
SET

EXPLAIN UPDATE booking_entries SET booking_date = now() where id =
nextval('booking_entries_id_seq'::regclass)::int4;
   QUERY PLAN   
   


 Seq Scan on booking_entries  (cost=1.00..10163.01 rows=1
width=89)
   Filter: (id = (nextval('booking_entries_id_seq'::regclass))::integer)
(2 rows)

EXPLAIN UPDATE booking_entries SET booking_date = now() where id = 1;
 QUERY PLAN 


-
 Index Scan using booking_entries_pkey on booking_entries  (cost=0.00..8.28
rows=1 width=89)
   Index Cond: (id = 1)
(2 rows)


What's going wrong? Could this be a bug?

Kind Regards

P.S.

SELECT version();
  version   
   


 PostgreSQL 8.3.6 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian
4.3.3-3) 4.3.3
(1 row)

uname -s -r -v -m -o
Linux 2.6.26-1-686 #1 SMP Mon Dec 15 18:15:07 UTC 2008 i686 GNU/Linux

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

[BUGS] BUG #4141: Aliases in rules vanished, but they work

[Mathias Seiler]

The following bug has been logged online:

Bug reference:  4141
Logged by:  Mathias Seiler
Email address:  [EMAIL PROTECTED]
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