php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #40544 PostgreSQL connection hangs after die()
Submitted: 2007-02-19 14:57 UTC Modified: 2013-06-26 21:32 UTC
Votes:2
Avg. Score:5.0 ± 0.0
Reproduced:1 of 2 (50.0%)
Same Version:1 (100.0%)
Same OS:0 (0.0%)
From: kees at tweakers dot net Assigned:
Status: Wont fix Package: PostgreSQL related
PHP Version: 5.2CVS-2008-10-31 OS: Linux (Debian)
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: kees at tweakers dot net
New email:
PHP Version: OS:

 

 [2007-02-19 14:57 UTC] kees at tweakers dot net
Description:
------------
After opening a db connection to postgresql and executing a query, and after that query a 'die()' php doesnt return to the CLI but hangs

Reproduce code:
---------------
<?
$conn = pg_connect('user=acm dbname=loganalysis');

echo "Starting\n";

$q = "
CREATE TEMPORARY TABLE test (test integer);
COPY test FROM STDIN WITH DELIMITER ',' CSV QUOTE '\"' ESCAPE '\"'";
pg_query($conn, $q);
die("And now he hangs in a busy wait\n");
?>


Expected result:
----------------
acm@abaris:~$ php test.3.php
Starting
And now he hangs in a busy wait
acm@abaris:~$


Actual result:
--------------
acm@abaris:~$ php test.3.php
Starting
And now he hangs in a busy wait

[no prompt, you have to ctrl-c to exit]

Last part of strace:
recv(3, "C\0\0\0\21CREATE TABLE\0G\0\0\0\t\0\0\1\0\0", 16384, 0) = 28
write(1, "And now he hangs in a busy wait\n", 32And now he hangs in a busy wait
) = 32
close(6)                                = 0
close(5)                                = 0
close(4)                                = 0

He probably wants to do a close(3) here as that is the postgresql connection, but it never closes, and netstat will show an open connection:
Active UNIX domain sockets (servers and established)
Proto RefCnt Flags       Type       State         I-Node PID/Program name    Path
unix  3      [ ]         STREAM     CONNECTED     549701   -                   /var/run/postgresql/.s.PGSQL.5432
unix  3      [ ]         STREAM     CONNECTED     549700   26884/php

Tested with PHP 5.2.0-8 (debian package) and PHP 4.4.2


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2007-02-19 15:19 UTC] acm at tweakers dot net
Btw, when hanging gdb to the php-process and type 'bt' you get this:

#0  0xa7ba88c0 in free () from /lib/tls/libc.so.6
#1  0xa7ec8ea7 in PQclear () from /usr/lib/libpq.so.5
#2  0x08086fc8 in _close_pgsql_link (rsrc=0x81e43ec) at /usr/src/php-4.4.2/ext/pgsql/pgsql.c:277
#3  0x08139fb2 in list_entry_destructor (ptr=0x81e43ec) at /usr/src/php-4.4.2/Zend/zend_list.c:177
#4  0x08137977 in zend_hash_apply_deleter (ht=0x81a49e0, p=0x81e43b4)
    at /usr/src/php-4.4.2/Zend/zend_hash.c:611
#5  0x08137b97 in zend_hash_graceful_reverse_destroy (ht=0x81a49e0)
    at /usr/src/php-4.4.2/Zend/zend_hash.c:677
#6  0x0812b9ed in shutdown_executor () at /usr/src/php-4.4.2/Zend/zend_execute_API.c:211
#7  0x08133801 in zend_deactivate () at /usr/src/php-4.4.2/Zend/zend.c:689
#8  0x08107862 in php_request_shutdown (dummy=0x0) at /usr/src/php-4.4.2/main/main.c:999
#9  0x0814ee56 in main (argc=2, argv=0xafb6d114) at /usr/src/php-4.4.2/sapi/cli/php_cli.c:881
 [2007-02-19 15:52 UTC] kees at tweakers dot net
tested with the snapshot:

acm@abaris:/usr/src/php5.2-200702191330$ sapi/cli/php test.3.php
Starting
And now he hangs in a busy wait

[ctrl-c]

acm@abaris:/usr/src/php5.2-200702191330$ sapi/cli/php -v
PHP 5.2.2-dev (cli) (built: Feb 19 2007 16:49:22)
Copyright (c) 1997-2007 The PHP Group
Zend Engine v2.2.0, Copyright (c) 1998-2007 Zend Technologies

ldd sapi/cli/php
        libpq.so.5 => /usr/lib/libpq.so.5 (0xa7f3c000)
 [2007-03-07 22:09 UTC] yohgaki at ohgaki dot net
Since COPY is not a actual SQL statement, PostgreSQL waits forever until COPY ends because the input is STDIN. (SQL Statements like SELECT, UPDATE, DELETE, INSERT will be aborted if there is deadlock)

Since PQfinish() (Used while shutting down pgsql connections) does not have option to wait certain amount of time to force closing connection, I'm not sure if it is feasible shutting down w/o waiting to finish COPY, etc. It's possible to force close connection manually using internal PGconn strucutre's connection parameter, but there would be portability issues if it use PGconn internal parameters.

I'll see if there is I can do about this in feasible manner.
 [2007-03-08 04:24 UTC] yohgaki at ohgaki dot net
I didn't look the backtrace carefully. It stops when PQclear() is called on the backtrace, while my PHP 5.2 stopeed at PQgetReuslt(). (Both of them are called when request is shutting down)

For at least PHP 5.2, it would be solved by calling PQcanel() when cleaning up resource, but with compatibility issue. By calling PQcanel() before clean up resource, all async query which is not finished yet will be discarded instead of finishing its query. 

I'll add new ini option that enables PQcancel() in list_entry_destructor. Any comments?
 [2007-03-09 10:11 UTC] tony2001@php.net
>By calling PQcanel() before clean up resource, all async query which
>is not finished yet will be discarded instead of finishing its query. 

And that's something I would call expected, because "rollback on shutdown" is much safer than "commit on shutdown".

>I'll add new ini option that enables PQcancel() in >list_entry_destructor. Any comments?

More INI options? No, thanks.
 [2007-04-05 01:28 UTC] yohgaki at ohgaki dot net
> And that's something I would call expected, because "rollback on
> shutdown" is much safer than "commit on shutdown".

As I wrote, under normal condition, current code(commit on shutdown) does make more sense than rollback on shutdown because PostgreSQL supports async query.

'Rollback on shutdown' is like 'Don't flush buffer before closing file'. It does not acceptable for most people. (And more efficient if it finish pending query at shutdown, too. If you are curious, take some simple benchmarks)

However, under shared environment, it is not acceptable to consume all connection by COPY FROM SDTIN. It is better to have a way to avoid such action.

There are 2 options:

1) Leave it alone (and make DoS possible under shared environment)

2) Give administrators a option that cancel current and pending async query.

I prefer first option. I'll ask PostgreSQL developer if it's possible to have GRANT option for COPY in the future.
 [2007-04-05 07:48 UTC] tony2001@php.net
>'Rollback on shutdown' is like 'Don't flush buffer before closing file'.
I disagree, you need to commit everything explicitly.
If you didn't commit the transaction, it should be rolled back.

 [2008-10-31 10:04 UTC] kees at tweakers dot net
Using the latest snapshots results in the same behaviour; the script hangs and the connection stays open.
 [2012-03-29 10:09 UTC] yohgaki@php.net
Old problem, but I suppose this hasn't changed. 
I prefer to leave this issue as I wrote 5 years ago.

Any comments?
 [2013-06-26 21:32 UTC] yohgaki@php.net
-Status: Open +Status: Wont fix
 [2013-06-26 21:32 UTC] yohgaki@php.net
This is caused by COPY FROM STDIN which reads input from stdin. Standard input 
is controlled by PHP, not pgsql module. Therefore, it will keep reading since 
STDIN would not reach EOF.

I think there aren't any feasible solution for this.
If there is, please submit solution.

If one would like to use COPY, use pg_copy_from()
http://php.net/manual/ja/function.pg-copy-from.php

pg_copy_from() may be better to support stream, though.
 
PHP Copyright © 2001-2020 The PHP Group
All rights reserved.
Last updated: Thu Nov 26 15:01:23 2020 UTC