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.xxido ut(_tzreplic.xx id)
NOTICE: drop cascades to function _tzreplic.xxidi n(cstring)
DEBUG: CommitTransacti onCommand

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

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

policy=# select * from pg_stat_activit y;
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 | AccessExclusive Lock | t
20369 | 17142 | | 19467 | AccessExclusive Lock | t
20371 | 17142 | | 19467 | AccessExclusive Lock | t
20372 | 17142 | | 19467 | AccessExclusive Lock | t
20374 | 17142 | | 19467 | AccessExclusive Lock | t
20376 | 17142 | | 19467 | AccessExclusive Lock | t
20378 | 17142 | | 19467 | AccessExclusive Lock | t
20379 | 17142 | | 19467 | AccessExclusive Lock | t
20385 | 17142 | | 19467 | AccessExclusive Lock | t
20387 | 17142 | | 19467 | AccessExclusive Lock | t
20389 | 17142 | | 19467 | AccessExclusive Lock | t
20390 | 17142 | | 19467 | AccessExclusive Lock | t
20400 | 17142 | | 19467 | AccessExclusive Lock | t
20402 | 17142 | | 19467 | AccessExclusive Lock | t
20404 | 17142 | | 19467 | AccessExclusive Lock | t
20405 | 17142 | | 19467 | AccessExclusive Lock | t
20407 | 17142 | | 19467 | AccessExclusive Lock | t
20413 | 17142 | | 19467 | AccessExclusive Lock | t
20415 | 17142 | | 19467 | AccessExclusive Lock | t
20421 | 17142 | | 19467 | AccessExclusive Lock | t
20423 | 17142 | | 19467 | AccessExclusive Lock | t
20425 | 17142 | | 19467 | AccessExclusive Lock | t
20426 | 17142 | | 19467 | AccessExclusive Lock | t
20428 | 17142 | | 19467 | AccessExclusive Lock | t
20434 | 17142 | | 19467 | AccessExclusive Lock | t
20436 | 17142 | | 19467 | AccessExclusive Lock | t
20438 | 17142 | | 19467 | AccessExclusive Lock | t
20439 | 17142 | | 19467 | AccessExclusive Lock | t
20449 | 17142 | | 19467 | AccessExclusive Lock | t
20451 | 17142 | | 19467 | AccessExclusive Lock | t
20461 | 17142 | | 19467 | AccessExclusive Lock | t
20463 | 17142 | | 19467 | AccessExclusive Lock | t
20473 | 17142 | | 19467 | AccessExclusive Lock | t
20475 | 17142 | | 19467 | AccessExclusive Lock | t
20477 | 17142 | | 19467 | AccessExclusive Lock | t
20478 | 17142 | | 19467 | AccessExclusive Lock | t
20480 | 17142 | | 19467 | AccessExclusive Lock | t
20483 | 17142 | | 19467 | AccessExclusive Lock | t
20484 | 17142 | | 19467 | AccessExclusive Lock | t
20485 | 17142 | | 19467 | AccessExclusive Lock | t
20487 | 17142 | | 19467 | AccessExclusive Lock | t
20489 | 17142 | | 19467 | AccessExclusive Lock | t
20492 | 17142 | | 19467 | AccessExclusive Lock | t
20494 | 17142 | | 19467 | AccessExclusive Lock | t
20496 | 17142 | | 19467 | AccessExclusive Lock | t
20497 | 17142 | | 19467 | AccessExclusive Lock | t
20498 | 17142 | | 19467 | AccessExclusive Lock | t
20500 | 17142 | | 19467 | AccessExclusive Lock | t
20502 | 17142 | | 19467 | AccessExclusive Lock | t
20503 | 17142 | | 19467 | AccessExclusive Lock | t
20504 | 17142 | | 19467 | AccessExclusive Lock | t
20506 | 17142 | | 19467 | AccessExclusive Lock | t
20508 | 17142 | | 19467 | AccessExclusive Lock | t
20510 | 17142 | | 19467 | AccessExclusive Lock | t
20512 | 17142 | | 19467 | AccessExclusive Lock | t
20514 | 17142 | | 19467 | AccessExclusive Lock | 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 Reply

3374

Tom Lane

"David Parker" <dp*****@tazzne tworks.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

25584

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

2628

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 out). This happens randomily throughout the script and can be on a SELECT, INSERT, or UPDATE statement, but doesn't cause the Pg server to go down (which is good). Any suggestions on where to start looking? Perl version 5.6.1

PostgreSQL Database

5798

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 schema schema1? thanks

DB2 Database

26147

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 null references another, FK_LASTLYOID bigint not null references lastly, unique (FK_OTHEROID,FK_ANOTHEROID))

DB2 Database

8886

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 commandline app, it will hang when it comes to the command-prompt (there's no return to send it to readline). Here's the code: public bool RunCommands(string cmds, ArrayList files) {

C# / C Sharp

1735

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 staging and production web servers the process hangs. I'm guessing it's during the JIT compilation because if the process DOESN'T hang, the problem never occurs after the first time you hit the web application.

ASP.NET

4493

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 the Main() function: // if this is not the first instance of this application, exit // immediately. Allow only one instance in the system if (Process.GetProcessesByName(Process.GetCurrentProcess().ProcessName).Length > 1)

.NET Framework

10835

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", which depends on it. SQLSTATE=42893 Then I tried to do

DB2 Database

2414

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 drop a schema in one go ?

Oracle Database

9601

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

10635

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

10115

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

9198

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

7653

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

6881

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

5550

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

4332

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

3861

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP