Thread: weird strncmp bug while executing trigger?

If PostgreSQL failed to compile on your computer or you found a bug that
is likely to be specific to one platform then please fill out this form
and e-mail it to pgsql-ports@postgresql.org.

To report any other bug, fill out the form below and e-mail it to
pgsql-bugs@postgresql.org.

If you not only found the problem but solved it and generated a patch
then e-mail it to pgsql-patches@postgresql.org instead.  Please use the
command "diff -c" to generate the patch.

You may also enter a bug report at http://www.postgresql.org/ instead of
e-mail-ing this form.

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name        :    Anand Ranganathan
Your email address    : anand@blandings.com


System Configuration
---------------------
  Architecture (example: Intel Pentium)      : Intel Pentium

  Operating System (example: Linux 2.0.26 ELF)     : Linux 2.2.19 ELF (Debian unstable)

  PostgreSQL version (example: PostgreSQL-7.1.3):   PostgreSQL-7.2.1

  Compiler used (example:  gcc 2.95.2)        :  gcc 2.95.4 20011002 (Debian prerelease)


Please enter a FULL description of your problem:
------------------------------------------------
When I run the select statement given below, the postgres server dies on
a SIGSEGV. This is quite annoying.




Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

Between the dashed lines is the output of pg_dump
------------pg_dump output---------------------------
--
-- Selected TOC Entries:
--
\connect - anand

--
-- TOC Entry ID 11 (OID 16623)
--
-- Name: "plpgsql_call_handler" () Type: FUNCTION Owner: anand
--

CREATE FUNCTION "plpgsql_call_handler" () RETURNS opaque AS '/bp/vendor/lib/plpgsql.so', 'plpgsql_call_handler'
LANGUAGE'C'; 

--
-- TOC Entry ID 12 (OID 16624)
--
-- Name: plpgsql Type: PROCEDURAL LANGUAGE Owner:
--

CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER 'PL/pgSQL';

--
-- TOC Entry ID 13 (OID 16625)
--
-- Name: "insert_comment" () Type: FUNCTION Owner: anand
--

CREATE FUNCTION "insert_comment" () RETURNS opaque AS 'DECLARE profile RECORD;
            BEGIN
                SELECT INTO profile * FROM uid_profile WHERE uid = new.uid;
                IF profile.signature <> ''''
                THEN
                new.description = new.description || ''<br>-- <br>'' || profile.signature;
                END IF;
                RETURN new;
            END;' LANGUAGE 'plpgsql';

--
-- TOC Entry ID 4 (OID 16626)
--
-- Name: uid_profile Type: TABLE Owner: anand
--

CREATE TABLE "uid_profile" (
    "uid" integer NOT NULL,
    "username" text,
    "karma" integer DEFAULT 0,
    "user_type" character(1) DEFAULT 'R',
    "last_access" timestamp with time zone DEFAULT "timestamp"('now'::text),
    "mod_worthy" integer DEFAULT 0,
    "min_threshold" integer DEFAULT 1,
    "display_type" character(1) DEFAULT 'F',
    "signature" text DEFAULT '',
    "timezone" text DEFAULT 'PST',
    "spam_mail" text DEFAULT '',
    Constraint "uid_profile_pkey" Primary Key ("uid")
);

--
-- TOC Entry ID 2 (OID 16768)
--
-- Name: comments_cid_seq Type: SEQUENCE Owner: anand
--

CREATE SEQUENCE "comments_cid_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1;

--
-- TOC Entry ID 5 (OID 16770)
--
-- Name: comments Type: TABLE Owner: anand
--

CREATE TABLE "comments" (
    "aid" integer,
    "subject" text,
    "description" text,
    "uid" integer,
    "when_posted" timestamp with time zone DEFAULT "timestamp"('now'::text),
    "modvalue" integer DEFAULT 1,
    "lastmod" integer DEFAULT 1,
    "parent" integer DEFAULT 0,
    "posting_uid" integer,
    "posting_user" text,
    "cid" integer DEFAULT nextval('"comments_cid_seq"'::text) NOT NULL,
    Constraint "comments_pkey" Primary Key ("cid")
);

--
-- Data for TOC Entry ID 14 (OID 16626)
--
-- Name: uid_profile Type: TABLE DATA Owner: anand
--


COPY "uid_profile" FROM stdin;
1000001    axl rose    31    E    2002-03-08 19:35:38-08    70    1    F        PST
\.
--
-- Data for TOC Entry ID 15 (OID 16770)
--
-- Name: comments Type: TABLE DATA Owner: anand
--


COPY "comments" FROM stdin;
\.
--
-- TOC Entry ID 6 (OID 17046)
--
-- Name: "comments_uid_index" Type: INDEX Owner: anand
--

CREATE INDEX comments_uid_index ON comments USING btree (uid);

--
-- TOC Entry ID 7 (OID 17047)
--
-- Name: "comments_modvalue_index" Type: INDEX Owner: anand
--

CREATE INDEX comments_modvalue_index ON comments USING btree (modvalue);

--
-- TOC Entry ID 8 (OID 17048)
--
-- Name: "comments_parent_index" Type: INDEX Owner: anand
--

CREATE INDEX comments_parent_index ON comments USING btree (parent);

--
-- TOC Entry ID 9 (OID 17049)
--
-- Name: "comments_posting_user_index" Type: INDEX Owner: anand
--

CREATE INDEX comments_posting_user_index ON comments USING btree (posting_user);

--
-- TOC Entry ID 10 (OID 17050)
--
-- Name: "comments_aid_index" Type: INDEX Owner: anand
--

CREATE INDEX comments_aid_index ON comments USING btree (aid);

--
-- TOC Entry ID 16 (OID 17072)
--
-- Name: comment_insert_trigger Type: TRIGGER Owner: anand
--

CREATE TRIGGER "comment_insert_trigger" BEFORE INSERT ON "comments"  FOR EACH ROW EXECUTE PROCEDURE "insert_comment"
();

--
-- TOC Entry ID 3 (OID 16768)
--
-- Name: comments_cid_seq Type: SEQUENCE SET Owner: anand
--

SELECT setval ('"comments_cid_seq"', 62, true);

------------end pg_dump output-----------------------

After this is loaded into a database (mine is called dot):
createdb dot
psql dot < dump_file
execute this command at the
"psql dot" prompt.

INSERT INTO comments (aid, uid, subject, description, parent, posting_uid, posting_user) VALUES (12, 1000001, 'hello
world','here is a comment', 0, 1000001, 'a'); 

When I do that the postgres server that the psql process is connected to
dies on a sig segv.

Here's the backtrace from gdb:

(gdb) bt
#0  0x4012a6b3 in strncmp () from /lib/libc.so.6
#1  0x403980fa in ?? ()
#2  0x40395916 in ?? ()
#3  0x080baafb in ExecCallTriggerFunc (trigdata=0xbfffe718, finfo=0x82486c8,
    per_tuple_context=0x825a5b8) at trigger.c:886
#4  0x080bac4f in ExecBRInsertTriggers (estate=0x82480a0, relinfo=0x8248470,
    trigtuple=0x8247e38) at trigger.c:934
#5  0x080c220c in ExecAppend (slot=0x82484b8, tupleid=0x0, estate=0x82480a0)
    at execMain.c:1233
#6  0x080c2139 in ExecutePlan (estate=0x82480a0, plan=0x8248018,
    operation=CMD_INSERT, numberTuples=0, direction=ForwardScanDirection,
    destfunc=0x8248688) at execMain.c:1119
#7  0x080c1587 in ExecutorRun (queryDesc=0x8247be8, estate=0x82480a0,
    feature=3, count=0) at execMain.c:233
#8  0x0810dfbe in ProcessQuery (parsetree=0x8245fb0, plan=0x8248018,
    dest=Remote, completionTag=0xbfffe890 "") at pquery.c:259
#9  0x0810c860 in pg_exec_query_string (
    query_string=0x8245938 "INSERT INTO comments (aid, uid, subject, description, parent, posting_uid, posting_user)
VALUES(12, 1000001, 'hello world', 'here is a comment', 0, 1000001, 'a');", dest=Remote, parse_context=0x821c2e8) 
    at postgres.c:811
#10 0x0810d83e in PostgresMain (argc=4, argv=0xbfffeac0,
    username=0x8217e71 "anand") at postgres.c:1926
#11 0x080f4ebe in DoBackend (port=0x8217d40) at postmaster.c:2243
#12 0x080f480f in BackendStartup (port=0x8217d40) at postmaster.c:1874
#13 0x080f3a92 in ServerLoop () at postmaster.c:995
#14 0x080f35ab in PostmasterMain (argc=2, argv=0x81ed928) at postmaster.c:771
#15 0x080d3095 in main (argc=2, argv=0xbffff444) at main.c:206
(gdb)



If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------

Anand <anand@blandings.com> writes:
> When I run the select statement given below, the postgres server dies on
> a SIGSEGV. This is quite annoying.

Hmm ... works for me.

You should check that the plpgsql.so mentioned by the CREATE FUNCTION
call is the same version of Postgres as your backend.  Weird things can
happen if an out-of-date plpgsql.so gets loaded.  I have not seen this
particular behavior reported before, though, so maybe that's not it.

            regards, tom lane

Tom Lane quoth:
> Anand <anand@blandings.com> writes:
> > When I run the select statement given below, the postgres server dies on
> > a SIGSEGV. This is quite annoying.
>
> Hmm ... works for me.
>
> You should check that the plpgsql.so mentioned by the CREATE FUNCTION
> call is the same version of Postgres as your backend.  Weird things can
> happen if an out-of-date plpgsql.so gets loaded.  I have not seen this
> particular behavior reported before, though, so maybe that's not it.
>
>             regards, tom lane

You were right, there was a bad plpgsql.so in the $prefix/lib directory
from a previous installation (i thought i'd cleaned it all out...). So,
I did clean it all out and reinstalled postgresql. So, now there's a new
problem. 7.2.1 seems to install plpgsql.so in $prefix/lib/postgresql
(that and plpython.so). All the others are installed in $prefix/lib. So
here's the output of some commands (in my case $prefix == /bp/vendor)

334 emsworth:plpgsql/src> /bp/vendor/bin/pg_config --pkglibdir
/bp/vendor/lib/postgresql
335 emsworth:plpgsql/src> /bp/vendor/bin/pg_config --libdir
/bp/vendor/lib
336 emsworth:plpgsql/src> /bp/vendor/bin/pg_config --version
PostgreSQL 7.2.1
337 emsworth:plpgsql/src> /bp/vendor/bin/postmaster --version
postmaster (PostgreSQL) 7.2.1

Now when I try that select statement, I get:
ERROR:  stat failed on file '/bp/vendor/lib/plpgsql.so': No such file or directory

Any ideas what might be wrong?
Thanks a lot.
Anand
--
http://www.kurukshetra.org
All the gup that's fit to chaap

Anand <anand@blandings.com> writes:
> Am I doing something wrong here? Or is this genuinely a bug?

You should allow 7.2's createlang to create the function and language
declaration --- that will produce the correct, forward-portable
$libdir-relative path for the shared library.

It is undoubtedly a bug that prior releases generated absolute paths
for shared library references, but unless you have a time machine
handy there is little we can do about that :-(

            regards, tom lane