Re: FailedAssertion() in 8.2beta1 - Mailing list pgsql-hackers

On Sat, 7 Oct 2006, Sergey E. Koposov wrote:

> cas=# explain UPDATE table_list SET description = 'tag{image 
> SRC="/vizier/new2.gif"}3rd release of DENIS (2005Sep)' WHERE  id = 
> cas_get_table_id ('cas_data_sega','b_denis_denis5' );
>                                                         QUERY PLAN 
>
----------------------------------------------------------------------------------------------------------------------------
> Nested Loop  (cost=0.01..17.11 rows=2 width=82)
>   ->  Index Scan using table_user_list_pkey on table_user_list 
> (cost=0.00..8.02 rows=1 width=10)
>         Index Cond: (cas_get_table_id('cas_data_sega'::character varying, 
> 'b_denis_denis5'::character varying) = id)
>   ->  Append  (cost=0.00..9.07 rows=2 width=76)
>         ->  Index Scan using table_user_list_pkey on table_user_list 
> (cost=0.00..8.02 rows=1 width=76)
>               Index Cond: (id = cas_get_table_id('cas_data_sega'::character 
> varying, 'b_denis_denis5'::character varying))
>         ->  Seq Scan on table_list  (cost=0.00..1.04 rows=1 width=51)
>               Filter: (id = cas_get_table_id('cas_data_sega'::character 
> varying, 'b_denis_denis5'::character varying))
> (8 rows)
>
> As I see from it, it generates two seq. scans for one table (table_user_list)
>

I meant index scans.

By the way, I sent again the full info about the used tables .

cas=# \d cas_metadata_sega.table_user_list                                                               Table
"cas_metadata_sega.table_user_list"  Column    |       Type        |                        Modifiers 
 
-------------+-------------------+--------------------------------------------------------- id          | integer
   | not null default nextval('table_list_id_seq'::regclass) catalog_id  | bigint            | name        | character
varying| not null info        | character varying | description | character varying | 
 
Indexes:    "table_user_list_pkey" PRIMARY KEY, btree (id)    "table_user_list_catalog_id_key" UNIQUE, btree
(catalog_id,name)
 
Foreign-key constraints:    "table_user_list_catalog_id_fkey" FOREIGN KEY (catalog_id) REFERENCES catalog_user_list(id)
ONUPDATE CASCADE ON DELETE CASCADE
 

cas=# \d cas_metadata_sega.table_list     View "cas_metadata_sega.table_list"   Column    |       Type        |
Modifiers
 
-------------+-------------------+----------- id          | integer           | catalog_id  | bigint            | name
     | character varying | info        | character varying | description | character varying | 
 
View definition: SELECT table_user_list.id, table_user_list.catalog_id, table_user_list.name, table_user_list.info,
table_user_list.description  FROM table_user_list
 
UNION ALL SELECT table_list.id, table_list.catalog_id, table_list.name, table_list.info, table_list.description   FROM
cas_metadata.table_list;
Rules: rule_delete_table AS    ON DELETE TO table_list DO INSTEAD  DELETE FROM table_user_list rule_insert_table AS
ONINSERT TO table_list DO INSTEAD  INSERT INTO table_user_list (catalog_id, name, info, description)  SELECT
new.catalog_id,new.name, new.info, new.description rule_update_table AS    ON UPDATE TO table_list DO INSTEAD  UPDATE
table_user_listSET catalog_id = new.catalog_id, name = new.name, info = new.info, description = new.description  WHERE
table_user_list.id= new.id
 

cas=# \d cas_metadata.table_list                              Table "cas_metadata.table_list"   Column    |       Type
     |                        Modifiers 
 
-------------+-------------------+--------------------------------------------------------- id          | integer
   | not null default nextval('table_list_id_seq'::regclass) catalog_id  | bigint            | name        | character
varying| not null info        | character varying | description | character varying | 
 
Indexes:    "table_list_pkey" PRIMARY KEY, btree (id)    "table_list_catalog_id_key" UNIQUE, btree (catalog_id, name)
"table_list_catalog_id_idx" btree (catalog_id)    "table_list_name_idx" btree (name)
 
Foreign-key constraints:    "table_list_catalog_id_fkey" FOREIGN KEY (catalog_id) REFERENCES
cas_metadata.catalog_list(id)ON UPDATE CASCADE ON DELETE CASCADE
 


Regards,    Sergey

*******************************************************************
Sergey E. Koposov
Max Planck Institute for Astronomy/Sternberg Astronomical Institute
Tel: +49-6221-528-349
Web: http://lnfm1.sai.msu.ru/~math
E-mail: math@sai.msu.ru