Re: FailedAssertion() in 8.2beta1

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) ON UPDATE 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
ON INSERT 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_list SET 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(at)sai(dot)msu(dot)ru