process hang during drop schema - PostgreSQL Database

David Parker

I have a process that hangs doing a "drop schema cascade delete". This happens in a slonik command, which runs fine if I run it directly from the command line, but hangs if run from inside my process. I'm pretty sure I'm doing something silly and it's not a slony-specific thing, and I'm hoping that somebody can point me to a server debugging technique I can use to track this down. I'm using 7.4.5.

My server, run with "-d 5", stops logging at this point:

[...]
NOTICE: drop cascades to cast from _tzreplic.xxid to xid
NOTICE: drop cascades to cast from xid to _tzreplic.xxid
NOTICE: drop cascades to function _tzreplic.xxidout(_tzreplic.xxid)
NOTICE: drop cascades to function _tzreplic.xxidin(cstring)
DEBUG: CommitTransactionCommand

There's nothing after that (until I log in with psql or something).

This is what things look like in pg_stat_activity and pg_locks (pid 19472 is the current
psql session, and 19467 is the hung process):

policy=# select * from pg_stat_activity;
datid | datname | procpid | usesysid | usename | current_query | query_start
-------+---------+---------+----------+-------------+---------------+-------------
17142 | policy | 19143 | 101 | tazz:pool | |
17142 | policy | 19467 | 102 | tazz:dbmgr | |
17142 | policy | 19472 | 103 | tazz:engmon | |
(3 rows)

policy=# select * from pg_locks order by pid,relation;
relation | database | transaction | pid | mode | granted
----------+----------+-------------+-------+---------------------+---------
20367 | 17142 | | 19467 | AccessExclusiveLock | t
20369 | 17142 | | 19467 | AccessExclusiveLock | t
20371 | 17142 | | 19467 | AccessExclusiveLock | t
20372 | 17142 | | 19467 | AccessExclusiveLock | t
20374 | 17142 | | 19467 | AccessExclusiveLock | t
20376 | 17142 | | 19467 | AccessExclusiveLock | t
20378 | 17142 | | 19467 | AccessExclusiveLock | t
20379 | 17142 | | 19467 | AccessExclusiveLock | t
20385 | 17142 | | 19467 | AccessExclusiveLock | t
20387 | 17142 | | 19467 | AccessExclusiveLock | t
20389 | 17142 | | 19467 | AccessExclusiveLock | t
20390 | 17142 | | 19467 | AccessExclusiveLock | t
20400 | 17142 | | 19467 | AccessExclusiveLock | t
20402 | 17142 | | 19467 | AccessExclusiveLock | t
20404 | 17142 | | 19467 | AccessExclusiveLock | t
20405 | 17142 | | 19467 | AccessExclusiveLock | t
20407 | 17142 | | 19467 | AccessExclusiveLock | t
20413 | 17142 | | 19467 | AccessExclusiveLock | t
20415 | 17142 | | 19467 | AccessExclusiveLock | t
20421 | 17142 | | 19467 | AccessExclusiveLock | t
20423 | 17142 | | 19467 | AccessExclusiveLock | t
20425 | 17142 | | 19467 | AccessExclusiveLock | t
20426 | 17142 | | 19467 | AccessExclusiveLock | t
20428 | 17142 | | 19467 | AccessExclusiveLock | t
20434 | 17142 | | 19467 | AccessExclusiveLock | t
20436 | 17142 | | 19467 | AccessExclusiveLock | t
20438 | 17142 | | 19467 | AccessExclusiveLock | t
20439 | 17142 | | 19467 | AccessExclusiveLock | t
20449 | 17142 | | 19467 | AccessExclusiveLock | t
20451 | 17142 | | 19467 | AccessExclusiveLock | t
20461 | 17142 | | 19467 | AccessExclusiveLock | t
20463 | 17142 | | 19467 | AccessExclusiveLock | t
20473 | 17142 | | 19467 | AccessExclusiveLock | t
20475 | 17142 | | 19467 | AccessExclusiveLock | t
20477 | 17142 | | 19467 | AccessExclusiveLock | t
20478 | 17142 | | 19467 | AccessExclusiveLock | t
20480 | 17142 | | 19467 | AccessExclusiveLock | t
20483 | 17142 | | 19467 | AccessExclusiveLock | t
20484 | 17142 | | 19467 | AccessExclusiveLock | t
20485 | 17142 | | 19467 | AccessExclusiveLock | t
20487 | 17142 | | 19467 | AccessExclusiveLock | t
20489 | 17142 | | 19467 | AccessExclusiveLock | t
20492 | 17142 | | 19467 | AccessExclusiveLock | t
20494 | 17142 | | 19467 | AccessExclusiveLock | t
20496 | 17142 | | 19467 | AccessExclusiveLock | t
20497 | 17142 | | 19467 | AccessExclusiveLock | t
20498 | 17142 | | 19467 | AccessExclusiveLock | t
20500 | 17142 | | 19467 | AccessExclusiveLock | t
20502 | 17142 | | 19467 | AccessExclusiveLock | t
20503 | 17142 | | 19467 | AccessExclusiveLock | t
20504 | 17142 | | 19467 | AccessExclusiveLock | t
20506 | 17142 | | 19467 | AccessExclusiveLock | t
20508 | 17142 | | 19467 | AccessExclusiveLock | t
20510 | 17142 | | 19467 | AccessExclusiveLock | t
20512 | 17142 | | 19467 | AccessExclusiveLock | t
20514 | 17142 | | 19467 | AccessExclusiveLock | t
| | 1301 | 19467 | ExclusiveLock | t
16759 | 17142 | | 19472 | AccessShareLock | t
| | 1304 | 19472 | ExclusiveLock | t
(59 rows)

To my untrained eye, it doesn't look as though there is any lock contentionhere, but haven't
dealt with postgres locking before, so....

Is there another table I should be looking at, or another debug switch I should be setting?

TIA for any suggestions.

- DAP
----------------------------------------------------------------------------------
David Parker Tazz Networks (401) 709-5130
*

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #1

Subscribe Post Reply

3349

Tom Lane

"David Parker" <dp*****@tazznetworks.com> writes:

To my untrained eye, it doesn't look as though there is any lock
contention here,

Me either; whatever that process is doing, it doesn't seem to be waiting
for a lock. Is it accumulating CPU time?

One way to get some info is to attach to the backend process with gdb
and get a stack trace:
gdb /path/to/postgres
attach PID
bt
quit

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #2

Similar topics

Drop all table

by: Sabrina | last post by:

Hi everybody, I need some help in SQL Server. I am looking for a command that will "Drop all user table" in a user database. Can anyone help me? Thank you very much Sabrina

Microsoft SQL Server

process hangs using perl?

by: Patrick Hatcher | last post by:

Running a perl script against my dev server (pg 7.3.4) that currently runs fine on the production server. And for some reason Pg always seems to hang during the process (see below for Top print...

PostgreSQL Database

drop schema problems

by: Ian Dobson | last post by:

Hi, I need to drop a schema from a database but it has 400 tables in it. Is there an easy way to do it other than drop table schema1.table1 drop table schema1.table2 etc.. to 400 and then drop...

DB2 Database

ALTER TABLE <TABLE> DROP UNIQUE <INDNAME> doesn't recognize index name!

by: BuddhaBuddy | last post by:

Platform is DB2/NT 7.2.9 The table was created like this: CREATE TABLE MYTEST ( MYTESTOID bigint not null primary key, FK_OTHEROID bigint not null references other, FK_ANOTHEROID bigint not...

DB2 Database

Readline hangs in process.StandardOutput

by: JC | last post by:

I'm trying to create a GUI wrapper for dumpbin, and so I'm using the Process class to run the command-line application. The problem is that if I use Readline() to get the output from the...

C# / C Sharp

JIT compilation hangs ASP.NET process

by: Scott | last post by:

So I can't figure out how to Google for this problem, I'll try asking for a solution here. Whenever I deploy changes, whether they are changed code-behind DLL's or ASPX/ASCX/ASAX pages to my...

ASP.NET

Problem with Process.GetCurrentProcess() when starting a service

by: Benzi Eilon | last post by:

I have written a C# application which should run as a Windows Service. I must avoid having multiple instances of the application on one machine so I inserted the following code at the beginning of...

.NET Framework

can not drop table but can not find dependence either

by: uwcssa | last post by:

I try to drop a table as: I got: During SQL processing it returned: SQL0478N The object type "TABLE" cannot be dropped because there is an object "sch.SQL070515104729271", of type "FUNCTION",...

DB2 Database

Drop a schema ?

by: ishagoel | last post by:

is there is any method to drop the entire schema . I have a requirment , in which i want to re-create the entire database the only way is to drop the entire schema . Is there any method to...

Oracle Database

Cloud Servers without Credit Card and Email Registration: A Simpler Way to Get on the Cloud

by: CloudSolutions | last post by:

Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...

General

Access Europe: Command bars, the Access Shortcut Tool and a simple Audit Log - Wed 3 April

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

General

One-click Importing Excel Data into a*Database

by: ryjfgjl | last post by:

In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...

Microsoft Excel

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Basic Javascript concepts

by: aa123db | last post by:

Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...

Javascript

Batch import of multiple excel files into the database

by: ryjfgjl | last post by:

If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...

Data Management

Merging data from multiple Excel files

by: ryjfgjl | last post by:

In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...

Data Management

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware