Re: [HACKERS] FailedAssertion() in 8.2beta1
Sergey E. Koposov [EMAIL PROTECTED] writes: I've found a bug with 8.2beta1: Can you put together a self-contained test case for this? The planner is evidently generating an incorrect plan from that messy view, but trying to reverse-engineer the complete scenario from what you've told us looks painful. (No, I don't think the log setting is related.) regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] FailedAssertion() in 8.2beta1
On Sat, 7 Oct 2006, Tom Lane wrote:
Sergey E. Koposov [EMAIL PROTECTED] writes:
I've found a bug with 8.2beta1:
Can you put together a self-contained test case for this? The planner
I'll try, but it will be quite hard.
is evidently generating an incorrect plan from that messy view, but
trying to reverse-engineer the complete scenario from what you've told
us looks painful. (No, I don't think the log setting is related.)
At least the plan for the problematic query looks like this
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)
Will it be enough to provide the testcase for just that 'expain UPDATE' ?
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: [EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] 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: [EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] FailedAssertion() in 8.2beta1
Sergey E. Koposov [EMAIL PROTECTED] writes: Will it be enough to provide the testcase for just that 'expain UPDATE' ? Whatever makes it crash ;-) My guess is that there's some rewriter interaction involved, which means that the rule itself is part of the problem --- you'll likely not be able to duplicate it without a similar rule. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] FailedAssertion() in 8.2beta1
On Sat, 7 Oct 2006, Tom Lane wrote:
Sergey E. Koposov [EMAIL PROTECTED] writes:
Will it be enough to provide the testcase for just that 'expain UPDATE' ?
Whatever makes it crash ;-)
So, the database schema with little data and a few functions is here
http://lnfm1.sai.msu.ru/~math/public_misc/cas.dump
And the java program crashing the backend is attached. (it is generally
one prepared statement , which i didn't succeded to crash from psql)
(it's possible to rewrite it in C with libpq, but I cannot do that very
easily).
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: [EMAIL PROTECTED]import java.sql.*;
public class xx
{
public static void main(String args[]) throws Exception
{
Class.forName(org.postgresql.Driver);
Connection conn =
DriverManager.getConnection(jdbc:postgresql://localhost:5432/cas,math,);
//Thread.sleep(1);
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
stmt.execute(set search_path to cas_metadata_sega, cas_metadata);
String query=UPDATE table_list SET description = ? WHERE +
id = cas_get_table_id ( ?,? );
String desc=\\tag{image SRC=\/vizier/new2.gif\}3rd release of DENIS
(2005Sep);
PreparedStatement pstmt = conn.prepareStatement(query);
String catalog=cas_data_sega;
String table=b_denis_denis5;
pstmt.setString(1,desc);
pstmt.setString(2,catalog);
pstmt.setString(3,table);
pstmt.executeUpdate();
pstmt.close();
conn.rollback();
}
}
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] FailedAssertion() in 8.2beta1
On Sat, 7 Oct 2006, Sergey E. Koposov wrote: And the java program crashing the backend is attached. (it is generally one prepared statement , which i didn't succeded to crash from psql) (it's possible to rewrite it in C with libpq, but I cannot do that very easily). As I did before, I send the strace ouput showing what jdbc is sending to the backend. send(5, \0\0\0E\0\3\0\0user\0math\0database\0xx\0client_encoding\0UNICODE\0DateStyle\0ISO\0\0, 69, 0) = 69 send(5, P\0\0\0\20S_1\0BEGIN\0\0\0B\0\0\0\17\0S_1\0\0\0\0\0\0\0E\0\0\0\t\0\0\0\0\0P\0\0\0:\0set search_path to cas_metadata_sega, cas_metadata\0\0\0B\0\0\0\f\0\0\0\0\0\0\0\0D\0\0\0\6P\0E\0\0\0\t\0\0\0\0\0S\0\0\0\4, 137, 0) = 137 send(5, P\0\0\0a\0UPDATE table_list SET description = $1 WHERE id = cas_get_table_id ( $2,$3 )[EMAIL PROTECTED] SRC=\/vizier/new2.gif\}3rd release of DENIS (2005Sep)\0\0\0\rcas_data_sega\0\0\0\16b_denis_denis5\0\0D\0\0\0\6P\0E\0\0\0\t\0\0\0\0\1S\0\0\0\4, 242, 0) = 242 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: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] FailedAssertion() in 8.2beta1
Tom Lane [EMAIL PROTECTED] writes: Sergey E. Koposov [EMAIL PROTECTED] writes: I've found a bug with 8.2beta1: Can you put together a self-contained test case for this? The planner is evidently generating an incorrect plan from that messy view, but trying to reverse-engineer the complete scenario from what you've told us looks painful. (No, I don't think the log setting is related.) Would a core dump file (and his binary) be useful? Earlier I was going to suggest he execute these commands from gdb: f 5 p *rtentry p *estate But I fear even that won't be enough to actually track down where the state got corrupted. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] FailedAssertion() in 8.2beta1
Sergey E. Koposov [EMAIL PROTECTED] writes: As I did before, I send the strace ouput showing what jdbc is sending to the backend. Thanks. I've been able to reproduce it now, and I think the plan is actually OK, but for some reason the wrong rtable list is getting sent along to the executor. Looking ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] FailedAssertion() in 8.2beta1
Sergey E. Koposov [EMAIL PROTECTED] writes: And the java program crashing the backend is attached. (it is generally one prepared statement , which i didn't succeded to crash from psql) Right, because the bug was in exec_bind_message, which you can't invoke from psql :-(. Fixed, but we really need to think harder about having a test suite that makes use of the Parse/Bind/Execute protocol path... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] FailedAssertion() in 8.2beta1
On Sat, 7 Oct 2006, Tom Lane wrote: Sergey E. Koposov [EMAIL PROTECTED] writes: And the java program crashing the backend is attached. (it is generally one prepared statement , which i didn't succeded to crash from psql) Right, because the bug was in exec_bind_message, which you can't invoke from psql :-(. Fixed, but we really need to think harder about having a test suite that makes use of the Parse/Bind/Execute protocol path... Thanks Tom, the problem went away. 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: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
