Re: core dump on 8.1 and no dump on REL8_1_STABLE

On Wed, 23 Nov 2005, Tom Lane wrote:

> Teodor Sigaev <teodor(at)sigaev(dot)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(at)postgresql(dot)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(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
>From pgsql-hackers-owner(at)postgresql(dot)org Wed Nov 23 18:23:06 2005
X-Original-To: pgsql-hackers-postgresql(dot)org(at)localhost(dot)postgresql(dot)org
Received: from localhost (av.hub.org [200.46.204.144])
by svr1.postgresql.org (Postfix) with ESMTP id 03F71DBAA3
for <pgsql-hackers-postgresql(dot)org(at)localhost(dot)postgresql(dot)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(dot)org(at)localhost(dot)postgresql(dot)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(at)postgresql(dot)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(at)68(dot)80(dot)245(dot)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(at)Yahoo(dot)com)
Message-ID: <4384E54D(dot)8000500(at)Yahoo(dot)com>
Date: Wed, 23 Nov 2005 16:55:25 -0500
From: Jan Wieck <JanWieck(at)Yahoo(dot)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(at)sss(dot)pgh(dot)pa(dot)us>
CC: Peter Eisentraut <peter_e(at)gmx(dot)net>, pgsql-hackers(at)postgresql(dot)org,
Josh Berkus <josh(at)agliodbs(dot)com>,
Jaime Casanova <systemguards(at)gmail(dot)com>
Subject: Re: someone working to add merge?
References: <c2d9e70e0511110603q1799d811u6e4564be516b10ce(at)mail(dot)gmail(dot)com> <200511111924(dot)41532(dot)peter_e(at)gmx(dot)net> <20561(dot)1131734697(at)sss(dot)pgh(dot)pa(dot)us> <200511112004(dot)35876(dot)peter_e(at)gmx(dot)net> <20899(dot)1131736841(at)sss(dot)pgh(dot)pa(dot)us>
In-Reply-To: <20899(dot)1131736841(at)sss(dot)pgh(dot)pa(dot)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(at)gmx(dot)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(at)Yahoo(dot)com #