Thread: Checking if a table locked from pl/pgsql

Checking if a table locked from pl/pgsql

From
"Jose Luis LG"
Date:
<div class="Section1"><p class="MsoNormal"><span class="SpellE"><font face="Arial" size="2"><span lang="ES-TRAD"
style="font-size:10.0pt;font-family:Arial;mso-ansi-language:ES-TRAD">Hi</span></font></span><fontface="Arial"
size="2"><spanlang="ES-TRAD" style="font-size:10.0pt;font-family:Arial; 
mso-ansi-language:ES-TRAD">,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="ES-TRAD"
style="font-size:
10.0pt;font-family:Arial;mso-ansi-language:ES-TRAD"> </span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial;mso-ansi-language:EN-US">Could anybody tell me if it is <span class="SpellE">posible</span> to check
if<span class="GramE">a</span> if a table is being locked from pl/<span class="SpellE">pgsql</span>
functions.</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial;mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial;mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial;mso-ansi-language:EN-US">Thanks</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial;mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial;mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial;mso-ansi-language:EN-US">Regards</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial;mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial;mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial;mso-ansi-language:EN-US">Jose Luis</span></font></div>

Re: Checking if a table locked from pl/pgsql

From
Jan Wieck
Date:
Jose Luis LG wrote:
> Hi,
>
> Could anybody tell me if it is posible to check if a if a table is being
> locked from pl/pgsql functions.
   Yes, it is not.
   That  you ask for such a functionality leads to the question,   why can a lock exist long enough that you want  to
check at   all?
 


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com



Re: Checking if a table locked from pl/pgsql

From
Hal Davison
Date:
In an operational sense, if a user is updating a table for some accounting
function then decides to go to lunch leaving the table locked.

--Hal.

===========================================================
Hal Davison                 Internet Petroleum Distribution
Davison Consulting                LSE Linux V1.22
6850 Myakka Valley Tr    PostgreSQL 7.03 - Sun Forte - JAVA
Sarasota, Florida 34241         Phone: (941) 921-6578
http://www.faams.net              FAX: (941) 924-7135
===========================================================

On Mon, 25 Mar 2002, Jan Wieck wrote:

> Jose Luis LG wrote:
> > Hi,
> >
> > Could anybody tell me if it is posible to check if a if a table is being
> > locked from pl/pgsql functions.
> 
>     Yes, it is not.
> 
>     That  you ask for such a functionality leads to the question,
>     why can a lock exist long enough that you want  to  check  at
>     all?
> 
> 
> Jan
> 
> --
> 
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me.                                  #
> #================================================== JanWieck@Yahoo.com #
> 
> 
> 
> _________________________________________________________
> Do You Yahoo!?
> Get your free @yahoo.com address at http://mail.yahoo.com
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 



Re: Checking if a table locked from pl/pgsql

From
Jan Wieck
Date:
Hal Davison wrote:
>
> In an operational sense, if a user is updating a table for some accounting
> function then decides to go to lunch leaving the table locked.
   Exactly  what  I  expected.  You have a severe design flaw in   your application. Database transactions have to be
short and   never  held over actions that could infinitely block, such as   user interaction, period!
 
   You need application  level  advisory  locks.  The  abuse  of   database  locks  for  that  will  not  get you very
far.Your   application will not scale and suffer from problems like  the   one you already face.
 
   Ever   thought   about   adding   a  WEB  interface  to  that   application? If a user editing some account needs to
hold  a   DB   lock   all  the  time,  you'll  not  implement  it  with   Apache/PHP, that's for sure already. And
you'llhave to  make   alot  of  painfull pushups to do it with any other WEB server   technology.
 


Jan

>
> --Hal.
>
> ===========================================================
> Hal Davison                 Internet Petroleum Distribution
> Davison Consulting                LSE Linux V1.22
> 6850 Myakka Valley Tr    PostgreSQL 7.03 - Sun Forte - JAVA
> Sarasota, Florida 34241         Phone: (941) 921-6578
> http://www.faams.net             FAX: (941) 924-7135
> ===========================================================
>
> On Mon, 25 Mar 2002, Jan Wieck wrote:
>
> > Jose Luis LG wrote:
> > > Hi,
> > >
> > > Could anybody tell me if it is posible to check if a if a table is being
> > > locked from pl/pgsql functions.
> >
> >     Yes, it is not.
> >
> >     That  you ask for such a functionality leads to the question,
> >     why can a lock exist long enough that you want  to  check  at
> >     all?
> >
> >
> > Jan
> >
> > --
> >
> > #======================================================================#
> > # It's easier to get forgiveness for being wrong than for being right. #
> > # Let's break this rule - forgive me.                                  #
> > #================================================== JanWieck@Yahoo.com #
> >
> >
> >
> > _________________________________________________________
> > Do You Yahoo!?
> > Get your free @yahoo.com address at http://mail.yahoo.com
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>


--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com



Re: Checking if a table locked from pl/pgsql

From
Hal Davison
Date:
On Wed, 27 Mar 2002, Jan Wieck wrote:

> Hal Davison wrote:
> >
> > In an operational sense, if a user is updating a table for some accounting
> > function then decides to go to lunch leaving the table locked.
> 
>     Exactly  what  I  expected.  You have a severe design flaw in
>     your application. Database transactions have to be short  and
>     never  held over actions that could infinitely block, such as
>     user interaction, period!
> 
>     You need application  level  advisory  locks.  The  abuse  of
>     database  locks  for  that  will  not  get you very far. Your
>     application will not scale and suffer from problems like  the
>     one you already face.
> 
>     Ever   thought   about   adding   a  WEB  interface  to  that
>     application? If a user editing some account needs to  hold  a
>     DB   lock   all  the  time,  you'll  not  implement  it  with
>     Apache/PHP, that's for sure already. And you'll have to  make
>     alot  of  painfull pushups to do it with any other WEB server
>     technology.
> 
> 
> Jan

Jan,

I don't have this problem. A request was issued for an possible example as
to WHY someone would need to determine who has a table locked.

I am quite aware of the implications of table as well as row locking in a
production situation.

--Hal.




Re: Checking if a table locked from pl/pgsql

From
"Jose Luis LG"
Date:
In my case I have several clients updating a table at the same time with
each client having several threads updating tables.  With what mechanism
other than locks can you insure for example that at any instant
duplicate keys are not generated?

The lock in the application is released as soon as the thread finishes
work.
Jose

> -----Original Message-----
> From: Jan Wieck [mailto:janwieck@yahoo.com]
> Sent: miércoles, 27 de marzo de 2002 16:37
> To: Hal Davison
> Cc: Jan Wieck; Jose Luis LG; 'PostgreSQL-interfaces'
> Subject: Re: Checking if a table locked from pl/pgsql
>
> Hal Davison wrote:
> >
> > In an operational sense, if a user is updating a table for some
> accounting
> > function then decides to go to lunch leaving the table locked.
>
>     Exactly  what  I  expected.  You have a severe design flaw in
>     your application. Database transactions have to be short  and
>     never  held over actions that could infinitely block, such as
>     user interaction, period!
>
>     You need application  level  advisory  locks.  The  abuse  of
>     database  locks  for  that  will  not  get you very far. Your
>     application will not scale and suffer from problems like  the
>     one you already face.
>
>     Ever   thought   about   adding   a  WEB  interface  to  that
>     application? If a user editing some account needs to  hold  a
>     DB   lock   all  the  time,  you'll  not  implement  it  with
>     Apache/PHP, that's for sure already. And you'll have to  make
>     alot  of  painfull pushups to do it with any other WEB server
>     technology.
>
>
> Jan
>
> >
> > --Hal.
> >
> > ===========================================================
> > Hal Davison                 Internet Petroleum Distribution
> > Davison Consulting                LSE Linux V1.22
> > 6850 Myakka Valley Tr    PostgreSQL 7.03 - Sun Forte - JAVA
> > Sarasota, Florida 34241         Phone: (941) 921-6578
> > http://www.faams.net             FAX: (941) 924-7135
> > ===========================================================
> >
> > On Mon, 25 Mar 2002, Jan Wieck wrote:
> >
> > > Jose Luis LG wrote:
> > > > Hi,
> > > >
> > > > Could anybody tell me if it is posible to check if a if a table
is
> being
> > > > locked from pl/pgsql functions.
> > >
> > >     Yes, it is not.
> > >
> > >     That  you ask for such a functionality leads to the question,
> > >     why can a lock exist long enough that you want  to  check  at
> > >     all?
> > >
> > >
> > > Jan
> > >
> > > --
> > >
> > >
>
#======================================================================#
> > > # It's easier to get forgiveness for being wrong than for being
right.
> #
> > > # Let's break this rule - forgive me.
> #
> > > #==================================================
JanWieck@Yahoo.com
> #
> > >
> > >
> > >
> > > _________________________________________________________
> > > Do You Yahoo!?
> > > Get your free @yahoo.com address at http://mail.yahoo.com
> > >
> > >
> > > ---------------------------(end of
broadcast)-------------------------
> --
> > > TIP 6: Have you searched our list archives?
> > >
> > > http://archives.postgresql.org
> > >
> >
> >
> > ---------------------------(end of
broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
>
>
> --
>
>
#======================================================================#
> # It's easier to get forgiveness for being wrong than for being right.
#
> # Let's break this rule - forgive me.
#
> #================================================== JanWieck@Yahoo.com
#
>
>
>
>
> _________________________________________________________
>
> Do You Yahoo!?
>
> Get your free @yahoo.com address at http://mail.yahoo.com
>




Re: Checking if a table locked from pl/pgsql

From
Jan Wieck
Date:
Jose Luis LG wrote:
> In my case I have several clients updating a table at the same time with
> each client having several threads updating tables.  With what mechanism
> other than locks can you insure for example that at any instant
> duplicate keys are not generated?
>
> The lock in the application is released as soon as the thread finishes
> work.
> Jose
   As  said,  with  an  advisory lock. Let's make an example, we   edit customer account 4711.
   Before we enter the EDIT screen, we try to insert a row  into   a central lock table:
       INSERT INTO locktab (lockkey, holder, since)           VALUES ('CUST.4711', 'Jose', CURRENT_TIMESTAMP);
   There  is  a  unique constraint on lockkey, so if that INSERT   fails with a duplicate key error, we  don't  enter
the EDIT   screen  but  tell  Jose  "Sorry,  4711  is  locked, try again   later".
 
   If it succeeds, we read the current account  information  and   display  the  EDIT  screen.  But we commit the DB
transaction  before displaying it.
 
   When Jose leaves the EDIT  screen  (aborting  the  EDIT),  we   simply DELETE the locktab entry.
   When  he modifies the account information and clicks on SAVE,   we UPDATE account 4711 and DELETE the locktab entry
then.
   This way, all the DB transactions are very short, don't  hold   any resources over user interaction, but still 2
userscannot   edit the same account at the same time.
 
   In addition, we could  let  the  application  check  on  SAVE   first, if the entry in locktab is still there and
stillreads   'Jose'.  That way,  an  administrator  could  deal  with  the   situation  that  Jose  went to lunch but
Eileenneeds to edit   4711 immediately, because the customer lost his checkbook and   the  account  needs  to  be
disabledNOW.  When returing from   lunch, Jose might click SAVE and get the message that  Arnold   broke his lock and
hehas to restart the EDIT from scratch.
 
   Your  problem  is,  that  you  try  to  do an entire business   process in one database transaction. Business
processes are   sometimes  called  transactions  on  the  application  design   level, what's confusing. There is no
reason why  a  business   process  shouldn't  span multiple database transactions.  The   above mechanism is exactly
whatERP systems like SAP R/3 use.   And  it's been that way in R/2 or ADABAS (the app development   environment, not
thedatabase), back in the good old CICS and   UTM days on mainframes.
 


Jan

>
> > -----Original Message-----
> > From: Jan Wieck [mailto:janwieck@yahoo.com]
> > Sent: miércoles, 27 de marzo de 2002 16:37
> > To: Hal Davison
> > Cc: Jan Wieck; Jose Luis LG; 'PostgreSQL-interfaces'
> > Subject: Re: Checking if a table locked from pl/pgsql
> >
> > Hal Davison wrote:
> > >
> > > In an operational sense, if a user is updating a table for some
> > accounting
> > > function then decides to go to lunch leaving the table locked.
> >
> >     Exactly  what  I  expected.  You have a severe design flaw in
> >     your application. Database transactions have to be short  and
> >     never  held over actions that could infinitely block, such as
> >     user interaction, period!
> >
> >     You need application  level  advisory  locks.  The  abuse  of
> >     database  locks  for  that  will  not  get you very far. Your
> >     application will not scale and suffer from problems like  the
> >     one you already face.
> >
> >     Ever   thought   about   adding   a  WEB  interface  to  that
> >     application? If a user editing some account needs to  hold  a
> >     DB   lock   all  the  time,  you'll  not  implement  it  with
> >     Apache/PHP, that's for sure already. And you'll have to  make
> >     alot  of  painfull pushups to do it with any other WEB server
> >     technology.
> >
> >
> > Jan
> >
> > >
> > > --Hal.
> > >
> > > ===========================================================
> > > Hal Davison                 Internet Petroleum Distribution
> > > Davison Consulting                LSE Linux V1.22
> > > 6850 Myakka Valley Tr    PostgreSQL 7.03 - Sun Forte - JAVA
> > > Sarasota, Florida 34241         Phone: (941) 921-6578
> > > http://www.faams.net             FAX: (941) 924-7135
> > > ===========================================================
> > >
> > > On Mon, 25 Mar 2002, Jan Wieck wrote:
> > >
> > > > Jose Luis LG wrote:
> > > > > Hi,
> > > > >
> > > > > Could anybody tell me if it is posible to check if a if a table
> is
> > being
> > > > > locked from pl/pgsql functions.
> > > >
> > > >     Yes, it is not.
> > > >
> > > >     That  you ask for such a functionality leads to the question,
> > > >     why can a lock exist long enough that you want  to  check  at
> > > >     all?
> > > >
> > > >
> > > > Jan
> > > >
> > > > --
> > > >
> > > >
> >
> #======================================================================#
> > > > # It's easier to get forgiveness for being wrong than for being
> right.
> > #
> > > > # Let's break this rule - forgive me.
> > #
> > > > #==================================================
> JanWieck@Yahoo.com
> > #
> > > >
> > > >
> > > >
> > > > _________________________________________________________
> > > > Do You Yahoo!?
> > > > Get your free @yahoo.com address at http://mail.yahoo.com
> > > >
> > > >
> > > > ---------------------------(end of
> broadcast)-------------------------
> > --
> > > > TIP 6: Have you searched our list archives?
> > > >
> > > > http://archives.postgresql.org
> > > >
> > >
> > >
> > > ---------------------------(end of
> broadcast)---------------------------
> > > TIP 3: if posting/reading through Usenet, please send an appropriate
> > > subscribe-nomail command to majordomo@postgresql.org so that your
> > > message can get through to the mailing list cleanly
> > >
> >
> >
> > --
> >
> >
> #======================================================================#
> > # It's easier to get forgiveness for being wrong than for being right.
> #
> > # Let's break this rule - forgive me.
> #
> > #================================================== JanWieck@Yahoo.com
> #
> >
> >
> >
> >
> > _________________________________________________________
> >
> > Do You Yahoo!?
> >
> > Get your free @yahoo.com address at http://mail.yahoo.com
> >
>


--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com