Re: (Never?) Kill Postmaster? - Mailing list pgsql-general

Tom Lane wrote:
> * The only place internal_flush would call errmsg is here:
>
>                 ereport(COMMERROR,
>                         (errcode_for_socket_access(),
>                          errmsg("could not send data to client: %m")));
>
> So why is it unable to send data to the client?
>
The user accesses the database from his home office. He is connected to
the internet using a dial-in connection which seems to be disconnected
from time to time. The problem seems to be specific for this user, so it
may have something to do with his type of connection.

> * How the heck would strerror() be blocking on a thread mutex, when
> the backend is single-threaded?
>
> We recently discovered that it was possible for pltcl to cause the
> backend to become multithreaded:
> http://archives.postgresql.org/pgsql-patches/2007-09/msg00194.php
> I see from your dump that this session has been using plperl, so I
> wonder whether plperl has the same sort of issue.  Can you determine
> exactly what's been done with plperl in this session?
>

Not exactly. There are several triggers whose functions are implemented
in perl:

    elog(ERROR, 'set_serial needs at least 2 arguments.')
        if $_TD->{argc} < 2;

    my ($colname, $seqname) = @{$_TD->{args}};
    my $result = spi_exec_query("SELECT nextval('$seqname')");
    $_TD->{new}{$colname} = $result->{rows}[0]{nextval};
    return 'MODIFY';

and

    elog(ERROR, 'log_changes needs at least 2 arguments.')
        if $_TD->{argc} < 2;

    my @args = @{$_TD->{args}};

    shift(@args) =~ /([^.]*(?=\.))?\.?(.*)/;
    my $log_table = defined($1) ? qq("$1"."$2") : qq("$2");

    if ($_TD->{event} eq 'UPDATE') {
        my $id = join(',', @{$_TD->{old}}{@args});
        $id =~ s/'/\\'/g;
        while (my ($key, $value) = each %{$_TD->{old}}) {
            if ($value ne $_TD->{new}{$key}) {
                my $newvalue = $_TD->{new}{$key};
                $value =~ s/'/\\'/g;
                $newvalue =~ s/'/\\'/g;
                my $query = sprintf(qq(INSERT INTO $log_table VALUES
(current_timestamp, session_user, '%s', '%s', '%s', '%s', '%s')),
                                    $id,
                                    $_TD->{relname},
                                    $key,
                                    $value,
                                    $newvalue);
                spi_exec_query($query);
            }
        }
    }
    else {
        my $id = join(',', @{$_TD->{$_TD->{event} eq 'INSERT' ? 'new' :
'old'}}{@args});
        $id =~ s/'/\\'/g;
        my $query = sprintf(qq(INSERT INTO $log_table VALUES
(current_timestamp, session_user, '%s', '%s', '(%s)', null, null)),
                            $id,
                            $_TD->{relname},
                            $_TD->{event});
        spi_exec_query($query);
    }
    return;

> Also, can you confirm that there is actually more than one thread active
> in this process?  On Linux "ps -Lw" would show threads.
>

Since "ps -Lw" only showed me my own processes, I did "ps -AT -o
pid,ppid,lwp,nlwp,command" instead. This is the relevant line:

  PID  PPID   LWP NLWP COMMAND
...
 7129  3934  7129    1 postgres: dpyrek uspi 88.70.241.205(10612) SELECT
...

I'm not sure if this data is correct because NLWP (the number of threads
in the process) is 1 for all processes.

Is it necessary to recompile the server with debugging information
enabled? As I already mentioned this is a productive database, so I
would have to do it by night which would be somewhat uncomfortable for
me ... Not to mention the bad feeling when changing a productive
database server ...

Regards,
    Christian

--
Deriva GmbH                         Tel.: +49 551 489500-42
Financial IT and Consulting         Fax:  +49 551 489500-91
Hans-Böckler-Straße 2                  http://www.deriva.de
D-37079 Göttingen

Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer