Re: core dump on 8.1 and no dump on REL8_1_STABLE

On Wed, 23 Nov 2005, Tom Lane wrote:

Teodor Sigaev <teodor@sigaev.ru> writes:

Attached dump cause core on 8.1 release and works fine on REL8_1_STABLE and HEAD.
Am I missed some fixes/commits?

In HEAD I get

\i tsearch2.sql
...
\i ltree.sql
...
\i tsearch2_crash.dump
CREATE TABLE
ALTER TABLE
psql:tsearch2_crash.dump:86: ERROR: syntax error at position 0 near "О©╫"
CONTEXT: COPY О©╫О©╫О©╫О©╫О©╫О©╫О©╫О©╫О©╫О©╫О©╫, line 1, column name_ltree: "О©╫.О©╫.О©╫.О©╫.О©╫.О©╫.О©╫.О©╫.О©╫.О©╫.О©╫.О©╫.О©╫.О©╫"
psql:tsearch2_crash.dump:96: NOTICE: ALTER TABLE / ADD PRIMARY KEY will create
implicit index "О©╫О©╫О©╫О©╫О©╫О©╫О©╫О©╫О©╫О©╫О©╫_pkey" for table "О©╫О©╫О©╫О©╫О©╫О©╫О©╫О©╫О©╫О©╫О©╫"
ALTER TABLE
CREATE INDEX
REVOKE
REVOKE
GRANT
GRANT
UPDATE 0

The "syntax error" doesn't seem very promising --- maybe this dump needs
a particular locale/encoding setting to work (or fail as the case may
be)?

yes, it works fine for KOI8. I just load this dump into HEAD.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

From pgsql-hackers-owner@postgresql.org Wed Nov 23 18:23:06 2005

X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
by svr1.postgresql.org (Postfix) with ESMTP id 03F71DBAA3
for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Wed, 23 Nov 2005 18:23:06 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
with ESMTP id 92598-01
for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
Wed, 23 Nov 2005 22:23:06 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from smtp018.mail.yahoo.com (smtp018.mail.yahoo.com [216.136.174.115])
by svr1.postgresql.org (Postfix) with SMTP id 121D3DBA93
for <pgsql-hackers@postgresql.org>; Wed, 23 Nov 2005 18:23:01 -0400 (AST)
Received: (qmail 47652 invoked from network); 23 Nov 2005 22:22:56 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
s=s1024; d=Yahoo.com;
h=Received:Message-ID:Date:From:User-Agent:X-Accept-Language:MIME-Version:To:CC:Subject:References:In-Reply-To:Content-Type:Content-Transfer-Encoding;
b=ikj/Ob+3p22lsO9ikWyS1RkIbs6fq47zO2HF4jU7ZStjg5F/smOj0F+YkTeoepYDBoiKtd5pr0vxODe6iSXdUmQgXKe4BvzaypEm8KFqgR2kKtlpW/QAy1arUUnD1DoFpx/SJgPFt1V4S//lgGTpWw5v99EeoYfapzNmNV0hgQI= ;
Received: from unknown (HELO jupiter.black-lion.info) (janwieck@68.80.245.191 with login)
by smtp018.mail.yahoo.com with SMTP; 23 Nov 2005 22:22:55 -0000
Received: from [172.21.8.23] (mars.black-lion.info [192.168.192.101])
(authenticated bits=0)
by jupiter.black-lion.info (8.12.10/8.12.9) with ESMTP id jANLtT1o054955;
Wed, 23 Nov 2005 16:55:29 -0500 (EST)
(envelope-from JanWieck@Yahoo.com)
Message-ID: <4384E54D.8000500@Yahoo.com>
Date: Wed, 23 Nov 2005 16:55:25 -0500
From: Jan Wieck <JanWieck@Yahoo.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7.1) Gecko/20040707
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: Peter Eisentraut <peter_e@gmx.net>, pgsql-hackers@postgresql.org,
Josh Berkus <josh@agliodbs.com>,
Jaime Casanova <systemguards@gmail.com>
Subject: Re: someone working to add merge?
References: <c2d9e70e0511110603q1799d811u6e4564be516b10ce@mail.gmail.com> <200511111924.41532.peter_e@gmx.net> <20561.1131734697@sss.pgh.pa.us> <200511112004.35876.peter_e@gmx.net> <20899.1131736841@sss.pgh.pa.us>
In-Reply-To: <20899.1131736841@sss.pgh.pa.us>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.359 required=5 tests=[AWL=-0.120,
DNS_FROM_RFC_ABUSE=0.479]
X-Spam-Score: 0.359
X-Spam-Level:
X-Archive-Number: 200511/1231
X-Sequence-Number: 76513

On 11/11/2005 2:20 PM, Tom Lane wrote:

Peter Eisentraut <peter_e@gmx.net> writes:

Tom Lane wrote:

Surely they require a unique constraint --- else the behavior isn't
even well defined, is it?

They require that the merge condition does not match for more than one
row, but since the merge condition can do just about anything, there is
no guarantee that a unique constraint encompasses it.

ISTM to be a reasonable implementation restriction that there be a
constraint by which the system can prove that there is at most one
matching row. Per other comments in this thread, we'd not be the only
implementation making such a restriction.

(Certainly, if I were a DBA and were told that the performance of MERGE
would go to hell in a handbasket if I had no such constraint, I'd make
sure there was one. I don't think there is very much of a use-case for
the general scenario.)

Such restriction does look reasonable. Especially because ...

The largest problem I see with MERGE is the question of BEFORE triggers.
Consider a BEFORE INSERT trigger that modifies a third table, after
which the constraint or whatever post-heap_insert-attempt we might use
detects a conflict. How do we undo the actions of the BEFORE trigger?
The only way to do that is to plan the query as a nestloop, with the
USING part as the outer loop. If the (updating) scan of the INTO
relation did not hit any tuple, then do the INSERT. We can only undo the
side effects of any BEFORE trigger by wrapping each and evey nested INTO
relation insert attempt into its own subtransaction.

Sure, we "could" of course do the insert and then rescan the whole thing
with read-committed to see if our row is now the only one ... needless
to say that in the case of a sequential scan inside the loop, that
nestloop will suck big times even without that second scan. But ... hmmm
... we could get away with that and if we don't find a constraint that
will ensure uniqueness, then we do a rescan to check for it. But I would
vote for a "please_no_notice_about_stupid_usage_of_merge" runtime option
that suppresses the corresponding NOTICE.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

initdb -E KOI8-R --locale ru_RU.KOI8-R -D $DIR

In HEAD I get

HEAD and REL8_1STABLE works fine, 8.1 release not (I don't test REL8_1_0, just
take a source package)

--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/

I reproduced the problem by loading this dump to 8.1.0.
I think that the problem is the same as this:
http://archives.postgresql.org/pgsql-hackers/2005-11/msg01016.php

I saw heap_tuple_toast_attrs() overwrites a new tuple. And, backend
crashed while executing ExecInsertIndexTuples().

The problem occurs by the table like the following structures.
(1)The table has a varlena column.
(2)The table has a column with check constraint after the varlena column.
(3)The table has a indexed column after the column with check constraint.
And, the problem occurs if a tuple for which TOAST is necessary is
inserted or updated.

Example:

create table test_tbl (
varlena_col text,
check_col text,
index_col text,
constraint test_tbl_chk check (check_col ~ '^[0-9]+$')
);

create index test_tbl_idx on test_tbl(index_col);

insert into test_tbl (varlena_col, check_col, index_col)
values('A', '0', 'B');

update test_tbl
set varlena_col = repeat('A', 2000)
where index_col = 'B';

The backend will be crashed or wrong data will be registered to the index.
If backend is not crashed, Index Scan might return the wrong result.

explain select count(*) from test_tbl where index_col = 'B';
QUERY PLAN
---------------------------------------------------------------------------
Aggregate (cost=8.50..8.51 rows=1 width=0)
-> Bitmap Heap Scan on test_tbl (cost=2.01..8.49 rows=3 width=0)
Recheck Cond: (index_col = 'B'::text)
-> Bitmap Index Scan on test_tbl_idx (cost=0.00..2.01 rows=3 width=0)
Index Cond: (index_col = 'B'::text)

select count(*) from test_tbl where index_col = 'B';
count
-------
0 (Wrong result. Correct result is 1.)

--- Atsushi Ogawa

Atsushi Ogawa <atsushi.ogawa@gmail.com> writes:

I reproduced the problem by loading this dump to 8.1.0.
I think that the problem is the same as this:
http://archives.postgresql.org/pgsql-hackers/2005-11/msg01016.php

Good catch --- I agree that bug probably explains it.

regards, tom lane