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

Ok, I'm running with all available updates and kernel 2.6.18-308.4.1.el5
but am still having the same problem.

--
Brian McNally

On 08/07/2012 05:29 PM, Sergey Konoplev wrote:
> On Wed, Aug 8, 2012 at 4:27 AM, Brian McNally <bmcnally@uw.edu> wrote:
>> RHEL 5.7 on both. 2.6.18-274.el5 on the system that works,
>> 2.6.18-238.5.1.el5 on the system that doesn't. I will try upgrading.
>
> Yeah, I think it is worth trying.
>
>>
>> --
>> Brian McNally
>>
>>
>> On 08/07/2012 05:19 PM, Sergey Konoplev wrote:
>>>
>>> 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.
>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>>
>>
>
>
>