Thread: shared Locks

Hi group,

I have the following problem:

We have developed a  ERP/PPS Developed with pgsql over the last 4 years.
Now we introduce it on some of our customers {pgsql works great and gets
good ratings :-)} and so we have to change Tablestructure and so on very
often. For technologie reasons every clients starts a Connection and
holds it until the client terminates his program.

So if we want to change a table structure (add a field or sth like this)
many clients own AccessShareLock's because it seams that a simple SELECT
* FROM table will grant a AccessShareLock and don't release it unitl the
connection is terminated. Is that true? Is it is possible to release
this lock without a disconnect? {Problem is that about 30 users has to
disconnect sometimes. :-( }


thnx for comments,
Daniel

On Tue, Sep 20, 2005 at 11:18:48AM +0200, Daniel Schuchardt wrote:
> So if we want to change a table structure (add a field or sth like this)
> many clients own AccessShareLock's because it seams that a simple SELECT
> * FROM table will grant a AccessShareLock and don't release it unitl the
> connection is terminated. Is that true? Is it is possible to release
> this lock without a disconnect? {Problem is that about 30 users has to
> disconnect sometimes. :-( }

I think you'll find that locks are held to the end of the transaction.
You're not holding a transaction open but not doing anything, are you?

Otherwise, provide an example including output of pg_locks.

Hope this helps,

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Martijn van Oosterhout schrieb:

>On Tue, Sep 20, 2005 at 11:18:48AM +0200, Daniel Schuchardt wrote:
>
>
>>So if we want to change a table structure (add a field or sth like this)
>>many clients own AccessShareLock's because it seams that a simple SELECT
>>* FROM table will grant a AccessShareLock and don't release it unitl the
>>connection is terminated. Is that true? Is it is possible to release
>>this lock without a disconnect? {Problem is that about 30 users has to
>>disconnect sometimes. :-( }
>>
>>
>
>I think you'll find that locks are held to the end of the transaction.
>You're not holding a transaction open but not doing anything, are you?
>
>
Yes you'r right here. Because we use Cursor Fetch, every statement
starts a transaction. So your right I tested it and this forces a table
lock. Hm... i will look how to do this in another way.

thnx,
Daniel

On Tue, Sep 20, 2005 at 12:01:46PM +0200, Daniel Schuchardt wrote:
> Martijn van Oosterhout schrieb:
> >I think you'll find that locks are held to the end of the transaction.
> >You're not holding a transaction open but not doing anything, are you?
> >
> >
> Yes you'r right here. Because we use Cursor Fetch, every statement
> starts a transaction. So your right I tested it and this forces a table
> lock. Hm... i will look how to do this in another way.

Just COMMIT when you're done. This does kill the cursor though...

If you put a timeout in your app so that it commits that transaction
after, say, 30 seconds idle then your ALTER commands will only wait for
a while. Although, your ALTER will in turn block the following users...

Is your biggest problem that people tend to leave connections open
overnight or something? I simple timeout would work fine if there you
only want to make changes when there are just a few active users.
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Martijn van Oosterhout schrieb:

>
>>Yes you'r right here. Because we use Cursor Fetch, every statement
>>starts a transaction. So your right I tested it and this forces a table
>>lock. Hm... i will look how to do this in another way.
>>
>>
>
>Just COMMIT when you're done. This does kill the cursor though...
>
>If you put a timeout in your app so that it commits that transaction
>after, say, 30 seconds idle then your ALTER commands will only wait for
>a while. Although, your ALTER will in turn block the following users...
>
>Is your biggest problem that people tend to leave connections open
>overnight or something? I simple timeout would work fine if there you
>only want to make changes when there are just a few active users.
>
>
Y i will try it this way. There are some other problems : Some
connections catch CNC-Center and other mashine data all over the time.
So its not that easy at all. But i will try it with a commit on idle.
And Reconnect on that 24h connections. (They sleep most of the time so
it would be a better technic to terminate the connection and reconnect
all 5 mins. It will save resources too)

Daniel