Re: [HACKERS] Why is AccessShareLock held until end of transaction?

2014-03-11 Thread Simon Riggs
On 11 March 2014 03:41, Tom Lane t...@sss.pgh.pa.us wrote:
 Joe Conway m...@joeconway.com writes:
 I am probably missing something obvious, but why does the
 AccessShareLock remain held on a table after a SELECT statement is
 complete when in a transaction block?

 *Any* lock acquired by user command is held till end of transaction;
 AccessShareLock isn't special.

 In general, releasing early would increase the risk of undesirable
 behaviors such as tables changing definition mid-transaction.

I thought good question at first, but the workaround is simple...
just don't use multi-step transactions, submit each request as a
separate transaction.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Why is AccessShareLock held until end of transaction?

2014-03-11 Thread Atri Sharma
On Tue, Mar 11, 2014 at 10:56 PM, Simon Riggs si...@2ndquadrant.com wrote:

 On 11 March 2014 03:41, Tom Lane t...@sss.pgh.pa.us wrote:
  Joe Conway m...@joeconway.com writes:
  I am probably missing something obvious, but why does the
  AccessShareLock remain held on a table after a SELECT statement is
  complete when in a transaction block?
 
  *Any* lock acquired by user command is held till end of transaction;
  AccessShareLock isn't special.
 
  In general, releasing early would increase the risk of undesirable
  behaviors such as tables changing definition mid-transaction.

 I thought good question at first, but the workaround is simple...
 just don't use multi-step transactions, submit each request as a
 separate transaction.


 Wouldnt that tend to get inefficient?

Regards,

Atri



-- 
Regards,

Atri
*l'apprenant*


Re: [HACKERS] Why is AccessShareLock held until end of transaction?

2014-03-11 Thread Simon Riggs
On 11 March 2014 17:29, Atri Sharma atri.j...@gmail.com wrote:



 On Tue, Mar 11, 2014 at 10:56 PM, Simon Riggs si...@2ndquadrant.com wrote:

 On 11 March 2014 03:41, Tom Lane t...@sss.pgh.pa.us wrote:
  Joe Conway m...@joeconway.com writes:
  I am probably missing something obvious, but why does the
  AccessShareLock remain held on a table after a SELECT statement is
  complete when in a transaction block?
 
  *Any* lock acquired by user command is held till end of transaction;
  AccessShareLock isn't special.
 
  In general, releasing early would increase the risk of undesirable
  behaviors such as tables changing definition mid-transaction.

 I thought good question at first, but the workaround is simple...
 just don't use multi-step transactions, submit each request as a
 separate transaction.


 Wouldnt that tend to get inefficient?

Please outline your alternate proposal so we can judge the comparative
efficiency.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Why is AccessShareLock held until end of transaction?

2014-03-11 Thread Atri Sharma
On Tue, Mar 11, 2014 at 11:07 PM, Simon Riggs si...@2ndquadrant.com wrote:

 On 11 March 2014 17:29, Atri Sharma atri.j...@gmail.com wrote:
 
 
 
  On Tue, Mar 11, 2014 at 10:56 PM, Simon Riggs si...@2ndquadrant.com
 wrote:
 
  On 11 March 2014 03:41, Tom Lane t...@sss.pgh.pa.us wrote:
   Joe Conway m...@joeconway.com writes:
   I am probably missing something obvious, but why does the
   AccessShareLock remain held on a table after a SELECT statement is
   complete when in a transaction block?
  
   *Any* lock acquired by user command is held till end of transaction;
   AccessShareLock isn't special.
  
   In general, releasing early would increase the risk of undesirable
   behaviors such as tables changing definition mid-transaction.
 
  I thought good question at first, but the workaround is simple...
  just don't use multi-step transactions, submit each request as a
  separate transaction.
 
 
  Wouldnt that tend to get inefficient?

 Please outline your alternate proposal so we can judge the comparative
 efficiency.



I dont have an alternate proposal yet. I was just wondering if per step
transactions could lead to a drop in performance.

If that is the best way to go, I am all for it.

Regards,

Atri



-- 
Regards,

Atri
*l'apprenant*


Re: [HACKERS] Why is AccessShareLock held until end of transaction?

2014-03-11 Thread Joe Conway
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/11/2014 12:26 PM, Simon Riggs wrote:
 On 11 March 2014 03:41, Tom Lane t...@sss.pgh.pa.us wrote:
 Joe Conway m...@joeconway.com writes:
 I am probably missing something obvious, but why does the 
 AccessShareLock remain held on a table after a SELECT statement
 is complete when in a transaction block?
 
 *Any* lock acquired by user command is held till end of
 transaction; AccessShareLock isn't special.
 
 In general, releasing early would increase the risk of
 undesirable behaviors such as tables changing definition
 mid-transaction.
 
 I thought good question at first, but the workaround is
 simple... just don't use multi-step transactions, submit each
 request as a separate transaction.

Yeah, I told them that already. Unfortunately in this environment it
is not an option. It isn't a huge problem, but I did find it
surprising (as did the client) that a purely read-only transaction
could cause a deadlock with a concurrent CREATE TABLE.

It would seem that once the SELECT statement has finished we could
drop the AccessShareLock, but I guess that would open a can of works
that we don't want to contemplate.

Joe


- -- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting,  24x7 Support
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.14 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBAgAGBQJTH0tlAAoJEDfy90M199hlxtUP/isxPT8ZRPf8X/vM3+vS4XR2
CTwNB292c9TLADSfi4lHFCXu8kqOpx29/9PJHUHrhTrCQE10USdC5uBN04u9si0a
SL5cmwtSeSn3YacgksNpPz0u9spGVdO4XqcMq9oh5gcsSeRf14NXIPAvUk7yRPTA
leVo7CArOfyld0QdRNw3JP50tAoHYJQynomkClg/9U+jYtk/aBpCSe/KL++d5esl
xt8iGZQ/wdZu+vWSdeaJMvGUYNOu4ts7wgtrqvLv9qLXDAiftfIC6NuakKY3WHY6
2OYz64Xd+wH0ZWEhYnSjkQR354RXSm0JQNos02nAjviDON6r6OJk3ny7Rw/mKbAw
ZR2Ze3EFYcnMeV9Rrg1DccDzqWK9lq7tHD++IfbQ/36xvOcxh4pQuZQt9erTJ4q1
l9MrHE8PA4mVDgcGlhcdzDl+/po/0ghy/HWgH72NjGpEX+fChh7Pad9ZCO5r33Du
V3EZXfdLwnokx/VRi0N61ZeBJCCKWSST3SrZKJk5ao7y8dQPIICryLJlM9sTxlXf
2wiQlybElpaqWxy+Ou3M7EYdPvGNOLHMCt8yUK5n+RFTEtljKNwy1E9NvJWWiVl9
SfA/6GXXsGlO0rQ723R1vPAFHtTo82ibQaiCNujVPu/2yecKl4MsdtaZApkilLqx
EPoWWGrs3cURvar6gmju
=DOcV
-END PGP SIGNATURE-


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Why is AccessShareLock held until end of transaction?

2014-03-10 Thread Tom Lane
Joe Conway m...@joeconway.com writes:
 I am probably missing something obvious, but why does the
 AccessShareLock remain held on a table after a SELECT statement is
 complete when in a transaction block?

*Any* lock acquired by user command is held till end of transaction;
AccessShareLock isn't special.

In general, releasing early would increase the risk of undesirable
behaviors such as tables changing definition mid-transaction.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers