|
Home > Archive > PostgreSQL Discussion > September 2005 > Postgres locks table schema?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Postgres locks table schema?
|
|
| Stas Oskin 2005-09-21, 1:26 pm |
| Hi.
When running SQL scripts, which change the tables' schema, we sometimes
experience a lock of these tables' schema. The lock occurs on the first
command of the script, on the first modified table. When this occurs, no
further schema modification for this table is possible, until the server is
restarted, which is very problematic for a production environment.
Perhaps someone is familiar with this behavior, and how it can be resolved,
without the restart of the server?
The server used is PostgreSQL 8.0.3, and the OS is Fedora Core 3.
Thanks.
Stas Oskin.
| |
| Tom Lane 2005-09-21, 8:23 pm |
| "Stas Oskin" <stas@neo.co.il> writes:
> When running SQL scripts, which change the tables' schema, we sometimes
> experience a lock of these tables' schema. The lock occurs on the first
> command of the script, on the first modified table. When this occurs, no
> further schema modification for this table is possible, until the server is
> restarted, which is very problematic for a production environment.
Try committing the transaction that performed the schema modification
;-)
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
| |
| Scott Marlowe 2005-09-21, 8:23 pm |
| On Wed, 2005-09-21 at 12:51, Stas Oskin wrote:
> Hi.
>
>
>
> When running SQL scripts, which change the tables' schema, we
> sometimes experience a lock of these tables' schema. The lock occurs
> on the first command of the script, on the first modified table. When
> this occurs, no further schema modification for this table is
> possible, until the server is restarted, which is very problematic for
> a production environment.
>
>
>
> Perhaps someone is familiar with this behavior, and how it can be
> resolved, without the restart of the server?
>
>
>
> The server used is PostgreSQL 8.0.3, and the OS is Fedora Core 3.
Have you got a test case that reproduces this in some way? I'm guessing
Tom's got it right on committing the transaction, but without knowing
EXACTLY what you're doing, we're just guessing here.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
| |
| Stas Oskin 2005-09-22, 7:23 am |
| Hi.
I will give it a try. Although, I do remember that the lock occurs on the
first transaction - so basically there should be nothing to commit yet?
Thanks,
Stas.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, September 21, 2005 9:35 PM
To: Stas Oskin
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Postgres locks table schema?
"Stas Oskin" <stas@neo.co.il> writes:
> When running SQL scripts, which change the tables' schema, we sometimes
> experience a lock of these tables' schema. The lock occurs on the first
> command of the script, on the first modified table. When this occurs, no
> further schema modification for this table is possible, until the server
is
> restarted, which is very problematic for a production environment.
Try committing the transaction that performed the schema modification
;-)
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
|
|
|
|
|