Postgres Pro
Downloads
Home   >   mailing lists

Thread: Lost Access To Table

Lost Access To Table

From
"Booth, Robert"
Date:
I have a strange problem.  I have lost the ability to query a table.  Every
time I try to query it the system just hangs.  I can use psql to view the
table and it's structure but I can't access any data.  I've even tried to
vacuum the table and I get the same hang.  Is there anything else I should
be looking to do?

Rob

Re: Lost Access To Table

From
"Nigel J. Andrews"
Date:

On Tue, 4 Jun 2002, Booth, Robert wrote:

> I have a strange problem.  I have lost the ability to query a table.  Every
> time I try to query it the system just hangs.  I can use psql to view the
> table and it's structure but I can't access any data.  I've even tried to
> vacuum the table and I get the same hang.  Is there anything else I should
> be looking to do?

It sounds like you should be worrying about your filesystem. When you kill
whatever process you're using to query the table presumably the associated
backend stays hanging around.

It'd be a good idea to make sure you have a back up and try rebuilding your
database on a different filesystem/machine.


--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants

Re: Lost Access To Table

From
Tom Lane
Date:
"Booth, Robert" <Robert_Booth@intuit.com> writes:
> I have a strange problem.  I have lost the ability to query a table.  Every
> time I try to query it the system just hangs.

Please define "hang" --- is the whole system locked up, or do you just
not get a response to that query?  What do you do to recover?

My bet is that there's an open transaction somewhere holding an
exclusive lock on the table ...

            regards, tom lane

Re: Lost Access To Table

From
"Booth, Robert"
Date:
The client that is running the query hangs up, no response.  If I'm doing
this is psql then Ctrl-C will break out of it.  I have regained
functionality by shutting down and restarting the postmaster, but I was only
able to do this because this is my development machine.

If there was an open transaction how can I detect it and see if it's valid?

Rob

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Tuesday, June 04, 2002 4:28 PM
> To: Booth, Robert
> Cc: Pgsql-General
> Subject: Re: [GENERAL] Lost Access To Table
>
>
> "Booth, Robert" <Robert_Booth@intuit.com> writes:
> > I have a strange problem.  I have lost the ability to query
> a table.  Every
> > time I try to query it the system just hangs.
>
> Please define "hang" --- is the whole system locked up, or do you just
> not get a response to that query?  What do you do to recover?
>
> My bet is that there's an open transaction somewhere holding an
> exclusive lock on the table ...
>
>             regards, tom lane
>