Re: Problem running "ALTER TABLE...", ALTER TABLE waiting - Mailing list pgsql-general

On Wed, Aug 8, 2012 at 3:03 AM, Brian McNally <bmcnally@uw.edu> wrote:
> [root@gvsdb-dev tmp]# gdb /usr/pgsql-9.0/bin/postmaster 1160
> GNU gdb (GDB) Red Hat Enterprise Linux (7.0.1-32.el5_6.2)
> (gdb) bt
> #0  0x000000378f8d5497 in semop () from /lib64/libc.so.6
> #1  0x00000000005bc1c3 in PGSemaphoreLock (sema=0x2b1e695789e8,
> interruptOK=1 '\001') at pg_sema.c:420
> #2  0x00000000005ec8a1 in ProcSleep (locallock=0x95e00d0,
> lockMethodTable=<value optimized out>) at proc.c:973
> #3  0x00000000005eb45c in WaitOnLock (locallock=0x95e00d0, owner=0x95573b0)
> at lock.c:1223
> #4  0x00000000005ebb8c in LockAcquireExtended (locktag=0x7fffd9671d40,
> lockmode=8, sessionLock=<value optimized out>, dontWait=0 '\000',
>     reportMemoryError=1 '\001') at lock.c:848
> #5  0x00000000005e988b in LockRelationOid (relid=17211, lockmode=8) at
> lmgr.c:79
> #6  0x0000000000467ee5 in relation_open (relationId=17211,
> lockmode=-647554384) at heapam.c:906

What kernel version is on this machine and which one is on the server
where everything works fine? As I understand Red Hat is installed on
both of them, am I correct?

I have found several mentions of similar situations related to a
possible kernel bug.

> #7  0x00000000004f057c in transformAlterTableStmt (stmt=0x9558c70,
> queryString=0x95e3310 "alter table samples add column esp_race text;")
>     at parse_utilcmd.c:1948
> #8  0x00000000005fae4c in standard_ProcessUtility (parsetree=0x95e3f48,
>     queryString=0x95e3310 "alter table samples add column esp_race text;",
> params=0x0, isTopLevel=1 '\001', dest=0x95e4288,
>     completionTag=0x7fffd9672110 "") at utility.c:706
> #9  0x00000000005f81e9 in PortalRunUtility (portal=0x9636c20,
> utilityStmt=0x95e3f48, isTopLevel=1 '\001', dest=0x95e4288,
>     completionTag=0x7fffd9672110 "") at pquery.c:1191
> #10 0x00000000005f9228 in PortalRunMulti (portal=0x9636c20, isTopLevel=1
> '\001', dest=0x95e4288, altdest=0x95e4288,
>     completionTag=0x7fffd9672110 "") at pquery.c:1296
> #11 0x00000000005f9c45 in PortalRun (portal=0x9636c20,
> count=9223372036854775807, isTopLevel=1 '\001', dest=0x95e4288,
> altdest=0x95e4288,
>     completionTag=0x7fffd9672110 "") at pquery.c:822
> #12 0x00000000005f6745 in exec_simple_query (query_string=0x95e3310 "alter
> table samples add column esp_race text;") at postgres.c:1060
> #13 0x00000000005f6ff4 in PostgresMain (argc=<value optimized out>,
> argv=<value optimized out>, username=<value optimized out>)
>     at postgres.c:3978
> #14 0x00000000005c6e35 in ServerLoop () at postmaster.c:3565
> #15 0x00000000005c7b3c in PostmasterMain (argc=5, argv=0x951dbb0) at
> postmaster.c:1097
> #16 0x00000000005714be in main (argc=5, argv=<value optimized out>) at
> main.c:188
> ===
>
> --
> Brian McNally
>
>
> On 08/02/2012 05:57 AM, Sergey Konoplev wrote:
>>
>> Hi Brian,
>>
>> On Wed, Aug 1, 2012 at 10:21 PM, Brian McNally <bmcnally@uw.edu> wrote:
>>>
>>> I was able to upgrade Postgres to 9.0.8 today and the ALTER TABLE...
>>> command
>>> still hangs. Stracing the hung PID doesn't reveal much:
>>>
>>> [root@gvsdb-dev ~]# strace -fp 13107
>>> Process 13107 attached - interrupt to quit
>>> semop(843382828, 0x7fffd9671ab0, 1
>>>
>>> ps still shows the same waiting process:
>>>
>>> postgres 13107 13067  0 11:14 ?        00:00:00 postgres: postgres
>>> exomeSNP
>>> [local] ALTER TABLE waiting
>>
>>
>> Can you attach to the hanging process with gdb and show a backtrace?
>>
>>
>> http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD
>>
>>>
>>> --
>>> Brian McNally
>>>
>>>
>>> On 07/20/2012 12:06 AM, Sergey Konoplev wrote:
>>>>
>>>>
>>>> On Fri, Jul 20, 2012 at 10:42 AM, Brian McNally <bmcnally@uw.edu> wrote:
>>>>>
>>>>>
>>>>> Thanks for the help. I don't get any results from that query either
>>>>> though.
>>>>
>>>>
>>>>
>>>> Okay, it looks like a bug for me.
>>>>
>>>> What I would do is to upgrade Pg to the latest minor release 9.0.8.
>>>> Probably this issue has already been solved.
>>>>
>>>> If it will not help run strace -p <hanging_pid> and show its output.
>>>>
>>>>
>>>
>>
>>
>>
>



--
Sergey Konoplev

a database architect, software developer at PostgreSQL-Consulting.com
http://www.postgresql-consulting.com

Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204