Обсуждение: BUG #9202: C Functions crash database too easily

The following bug has been logged on the website:

Bug reference:      9202
Logged by:          Rick Otten
Email address:      rotten@windfish.net
PostgreSQL version: 9.3.2
Operating system:   Ubuntu 12.04
Description:

While setting up a third party (commerical) provided C library functions
I've observed PostgreSQL 9.3.2 to be very sensitive to simple typos.

1)  If you create the function with a different number of arguments than the
C function expects, the create works just fine.  However, every time you do
a select, the database chokes:

2014-02-11 13:03:27.989 EST mydb [local] postgresLOG:  statement: create
function myschema.my_6_argument_function(character, character, character,
character, character)
    returns character as '/some/path/lib/some.so', 'My_6_Arg_Function'
    LANGUAGE C STRICT;
...
2014-02-11 13:03:33.919 EST   LOG:  server process (PID 9090) was terminated
by signal 11: Segmentation fault
2014-02-11 13:03:33.920 EST   DETAIL:  Failed process was running: select
myschema.my_6_argument_function('arg1', 'arg2', 'arg3', 'arg4', 'arg5');
2014-02-11 13:03:33.920 EST   LOG:  terminating any other active server
processes
2014-02-11 13:03:33.922 EST [unknown]  [unknown]LOG:  connection received:
host=[local]
2014-02-11 13:03:33.922 EST mydb [local] postgresFATAL:  the database system
is in recovery mode
2014-02-11 13:03:33.929 EST   WARNING:  terminating connection because of
crash of another server process
2014-02-11 13:03:33.929 EST   DETAIL:  The postmaster has commanded this
server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2014-02-11 13:03:33.929 EST   HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
2014-02-11 13:03:33.933 EST   LOG:  all server processes terminated;
reinitializing
2014-02-11 13:03:34.061 EST   LOG:  database system was interrupted; last
known up at 2014-02-11 13:02:37 EST
2014-02-11 13:03:34.061 EST   LOG:  database system was not properly shut
down; automatic recovery in progress
2014-02-11 13:03:34.066 EST   LOG:  redo starts at 10/203C360
2014-02-11 13:03:34.067 EST   LOG:  record with zero length at 10/20433C8
2014-02-11 13:03:34.067 EST   LOG:  redo done at 10/2043398
2014-02-11 13:03:34.067 EST   LOG:  last completed transaction was at log
time 2014-02-11 13:03:27.994445-05
2014-02-11 13:03:34.101 EST   LOG:  autovacuum launcher started
2014-02-11 13:03:34.101 EST   LOG:  database system is ready to accept
connections

2) The same thing happens if you try to create the same function twice:

2014-02-12 15:20:45.626 EST mydb [local] postgresLOG:  statement: create
function
         myschema.myfunction(character, character)
    returns
        character as '/some/path/somelib', 'MY_Function'
    LANGUAGE C STRICT;
2014-02-12 15:20:45.626 EST mydb [local] postgresWARNING:  using index
"pg_event_trigger_evtname_index" despite IgnoreSystemIndexes
2014-02-12 15:20:45.627 EST mydb [local] postgresPANIC:  function
"myfunction" already exists with same argument types
2014-02-12 15:20:45.627 EST mydb [local] postgresSTATEMENT:  create
function
         myschema.myfunction(character, character)
    returns
        character as '/some/path/somelib', 'My_Function'
    LANGUAGE C STRICT;
2014-02-12 15:20:45.768 EST   LOG:  server process (PID 2316) was terminated
by signal 6: Aborted
2014-02-12 15:20:45.768 EST   DETAIL:  Failed process was running: create
function
         myschema.myfunction(character, character)
    returns
        character as '/some/path/somelib', 'MY_Function'
    LANGUAGE C STRICT;
2014-02-12 15:20:45.768 EST   LOG:  terminating any other active server
processes
2014-02-12 15:20:45.777 EST [unknown]  [unknown]LOG:  connection received:
host=[local]
2014-02-12 15:20:45.778 EST mydb [local] postgresFATAL:  the database system
is in recovery mode
2014-02-12 15:20:45.778 EST   WARNING:  terminating connection because of
crash of another server process
2014-02-12 15:20:45.778 EST   DETAIL:  The postmaster has commanded this
server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2014-02-12 15:20:45.778 EST   HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
2014-02-12 15:20:45.783 EST   LOG:  all server processes terminated;
reinitializing
2014-02-12 15:20:45.926 EST   LOG:  database system was interrupted; last
known up at 2014-02-12 07:05:01 EST
2014-02-12 15:20:45.927 EST   LOG:  database system was not properly shut
down; automatic recovery in progress
2014-02-12 15:20:45.934 EST   LOG:  record with zero length at 10/4000090
2014-02-12 15:20:45.934 EST   LOG:  redo is not required
2014-02-12 15:20:45.968 EST   LOG:  autovacuum launcher started
2014-02-12 15:20:45.969 EST   LOG:  database system is ready to accept
connections


For case (1) I think you shouldn't be able to create a C function without
the right number of arguments.  Or at least if you do a select, instead of
crashing the database, PostgreSQL should catch the error and reports it.

For case (2) accidentally creating an object with the same name as an
existing object seems like a regular sort of thing that could happen.   It
shouldn't crash the database when the name collision occurs.

rotten@windfish.net writes:
> While setting up a third party (commerical) provided C library functions
> I've observed PostgreSQL 9.3.2 to be very sensitive to simple typos.

Coding in C in general is very sensitive to simple typos.  Perhaps you
should use some higher-level PL if you find C too uncooperative.

> 2) The same thing happens if you try to create the same function twice:

Hm, that seems odd, since merely creating the function doesn't call it.
But then again, stuff like this suggests that you're abusing something
rather badly:

> 2014-02-12 15:20:45.626 EST mydb [local] postgresWARNING:  using index
> "pg_event_trigger_evtname_index" despite IgnoreSystemIndexes

How did IgnoreSystemIndexes come to be set?

> 2014-02-12 15:20:45.627 EST mydb [local] postgresPANIC:  function
> "myfunction" already exists with same argument types

Interesting; that should only be an ERROR not a PANIC.  I'm guessing
that something in your loadable module left the system thinking it
was in a critical section, which would cause errors to be promoted
to panics like that.

In general it looks like these issues arise from your C code having
tromped on some global variables it shouldn't have.  Wild store through
a wrong pointer, perhaps?

> For case (1) I think you shouldn't be able to create a C function without
> the right number of arguments.

I know of no way that we could (portably) infer the number of arguments
a C function expects from looking at the loadable library.  Even if we
could, that alone wouldn't be much of a safety improvement.  There are
too many other ways to crash things with an incorrect C function.
IME wrong data types and failure to handle null arguments properly
(neither checking for them explicitly, nor marking the function STRICT)
are bigger hazards than wrong-number-of-arguments.

            regards, tom lane

rotten wrote
> While setting up a third party (commerical) provided C library functions
> I've observed PostgreSQL 9.3.2 to be very sensitive to simple typos.
>
> 1)  If you create the function with a different number of arguments than
> the
> C function expects, the create works just fine.  However, every time you
> do
> a select, the database chokes:

I would hope that the only typos you would be encountering in this use-case
are file-name typos which would correctly generate "file not found" errors.
Complain to the third-party that they are not making your installation easy
enough because they have not provided fully tested scripts for you to run to
setup their library - or ideally the capability to issue "CREATE EXTENSION".

As Tom said, sometimes the solutions are procedural/human in nature and not
something easily solved via technology; especially when portability is a
concern as it is in this project.

Not being a C programmer I cannot comment on how to improve usability in
these situations but the fact remains that as a developer you should be
testing these things in a controlled development environment; and as a user
you should expect it to work (and be provided tests to verify that) and also
to be able to install via file/extension name specification only.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/BUG-9202-C-Functions-crash-database-too-easily-tp5791841p5791875.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

David Johnston <polobo@yahoo.com> writes:
> I would hope that the only typos you would be encountering in this use-case
> are file-name typos which would correctly generate "file not found" errors.
> Complain to the third-party that they are not making your installation easy
> enough because they have not provided fully tested scripts for you to run to
> setup their library - or ideally the capability to issue "CREATE EXTENSION".

Yeah --- a C function should certainly come with a matching, tested
CREATE FUNCTION command in some kind of script.

After replying to Rick, I thought briefly about the possibility of
embedding the appropriate CREATE, as a string, in the loadable module.
This wouldn't exactly be bulletproof though --- there's still about as
much chance of human error causing the string constant to not match
the code as there is of an external file not matching the code.  Also,
our experience with extensions suggests that there's usually a whole
bunch of other SQL commands (such as CREATE TYPE, CREATE OPERATOR, etc)
that also have to match up.  Not to mention the possibility of version
upgrade scripts.  So I think the packaging technology we have,
namely SQL script file(s) that should match the code in a loadable
library, is probably really pretty reasonable from this standpoint.

It sounds like Rick is trying to deal with some code that wasn't
actually packaged that way :-(

            regards, tom lane