Обсуждение: BUG #14273: Tuple concurently updated error while creating function using async call from node js with postgresq

VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz
aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDI3MwpMb2dnZWQgYnk6ICAg
ICAgICAgIEtpc2hvciBIYXJndWRlCkVtYWlsIGFkZHJlc3M6ICAgICAgaGFy
Z3VkZWtpc2hvckBnbWFpbC5jb20KUG9zdGdyZVNRTCB2ZXJzaW9uOiA5LjQu
OApPcGVyYXRpbmcgc3lzdGVtOiAgIFVidW50dQpEZXNjcmlwdGlvbjogICAg
ICAgIAoKSGksDQoNCkkgYW0gZmFjaW5nIGEgc3RyYW5nZSBpc3N1ZSBvbiBw
b3N0Z3Jlc3FsLTkuNC44Lg0KDQpiZWxvdyBpcyB0aGUgZGVzY3JpcHRpb24g
b2YgdGhlIGlzc3VlLg0KDQpXZSBoYXZlIGNvbm5lY3RlZCBvdXIgbm9kZSBq
cyBhcHBsaWNhdGlvbiB3aXRoIHBvc3RncmVzcWwtOS40LjguV2UgaGF2ZQpz
ZXZlcmFsIGZ1bmN0aW9ucyB3aGljaCBhcmUgZ2V0dGluZyBjcmVhdGVkIGlu
IGRhdGFiYXNlIHVzaW5nIGFzeW5jIGNhbGwKZnJvbSBub2RlanMgdG8gcG9z
dGdyZXNxbCBzZXJ2ZXIuYnV0IHdoaWxlIGNyZWF0aW5nIHRob3NlIGZ1bmN0
aW9ucyAsV2UgYXJlCmVuY291bnRlcmVkIGEgZXJyb3Igb2YgIlR1cGxlIGNv
bmN1cnJlbnRseSB1cGRhdGVkIi5BbmQgYmVjYXVzZSBvZiB0aGlzCmVycm9y
IG91ciBub2RlIGpzIGFwcGxpY2F0aW9uIGlzIGdldHRpbmcgdGVybWluYXRl
ZC53aGljaCBpcyBub3QgYWZmb3JkYWJsZQp0byB1cy4NCg0KQmVsb3cgaXMg
dGhlIGV4YWN0IGVycm9yIGxpbmVzIGZyb20gbm9kZSBqcyBhcHAgbG9ncy4N
Cg0KIFtlcnJvcjogdHVwbGUgY29uY3VycmVudGx5IHVwZGF0ZWRdDQogIG5h
bWU6ICdlcnJvcicsDQogIGxlbmd0aDogODMsDQogIHNldmVyaXR5OiAnRVJS
T1InLA0KICBjb2RlOiAnWFgwMDAnLA0KICBkZXRhaWw6IHVuZGVmaW5lZCwN
CiAgaGludDogdW5kZWZpbmVkLA0KICBwb3NpdGlvbjogdW5kZWZpbmVkLA0K
ICBpbnRlcm5hbFBvc2l0aW9uOiB1bmRlZmluZWQsDQogIGludGVybmFsUXVl
cnk6IHVuZGVmaW5lZCwNCiAgd2hlcmU6IHVuZGVmaW5lZCwNCiAgc2NoZW1h
OiB1bmRlZmluZWQsDQogIHRhYmxlOiB1bmRlZmluZWQsDQogIGNvbHVtbjog
dW5kZWZpbmVkLA0KICBkYXRhVHlwZTogdW5kZWZpbmVkLA0KICBjb25zdHJh
aW50OiB1bmRlZmluZWQsDQogIGZpbGU6ICdoZWFwYW0uYycsDQogIGxpbmU6
ICc0MDk3JywNCiAgcm91dGluZTogJ3NpbXBsZV9oZWFwX3VwZGF0ZScgfQ0K
ZXJyb3I6IHR1cGxlIGNvbmN1cnJlbnRseSB1cGRhdGVkDQogICAgYXQgQ29u
bmVjdGlvbi5wYXJzZUUKKC92YXIvd3d3L3RlYS90ZWEvZ2FtbWEvbm9kZV9t
b2R1bGVzL3BnL2xpYi9jb25uZWN0aW9uLmpzOjUzOToxMSkNCiAgICBhdCBD
b25uZWN0aW9uLnBhcnNlTWVzc2FnZQooL3Zhci93d3cvdGVhL3RlYS9nYW1t
YS9ub2RlX21vZHVsZXMvcGcvbGliL2Nvbm5lY3Rpb24uanM6MzY2OjE3KQ0K
ICAgIGF0IFNvY2tldC48YW5vbnltb3VzPgooL3Zhci93d3cvdGVhL3RlYS9n
YW1tYS9ub2RlX21vZHVsZXMvcGcvbGliL2Nvbm5lY3Rpb24uanM6MTA1OjIy
KQ0KICAgIGF0IFNvY2tldC5lbWl0IChldmVudHMuanM6OTU6MTcpDQogICAg
YXQgU29ja2V0Ljxhbm9ueW1vdXM+IChfc3RyZWFtX3JlYWRhYmxlLmpzOjc2
NDoxNCkNCiAgICBhdCBTb2NrZXQuZW1pdCAoZXZlbnRzLmpzOjkyOjE3KQ0K
ICAgIGF0IGVtaXRSZWFkYWJsZV8gKF9zdHJlYW1fcmVhZGFibGUuanM6NDI2
OjEwKQ0KICAgIGF0IGVtaXRSZWFkYWJsZSAoX3N0cmVhbV9yZWFkYWJsZS5q
czo0MjI6NSkNCiAgICBhdCByZWFkYWJsZUFkZENodW5rIChfc3RyZWFtX3Jl
YWRhYmxlLmpzOjE2NTo5KQ0KICAgIGF0IFNvY2tldC5SZWFkYWJsZS5wdXNo
IChfc3RyZWFtX3JlYWRhYmxlLmpzOjEyNzoxMCkNCiAgICBhdCBUQ1Aub25y
ZWFkIChuZXQuanM6NTI4OjIxKQ0KDQoqKioqKioqKioqKioqKioqKioqKioq
KioqKioqKioqKioqKioqKioqKioqKg0KQmVsb3cgaXMgdGhlIGV4YWN0IGVy
cm9yIGxpbmVzIGZyb20gUEcgbG9ncy4NCg0KIHR1cGxlIGNvbmN1cnJlbnRs
eSB1cGRhdGVkDQoyMDE2LTA4LTAyIDEwOjEzOjA2IElTVDpbdW5rbm93bl06
dGVhZGJ1c2VyOnRlYWRiX21peGRiU1RBVEVNRU5UOiAgRFJPUApGVU5DVElP
TiBJRiBFWElTVFMgZ2V0X2NoYW5nZW92ZXJ2aWV3X25ld19hZGRlZF9jb21w
b25lbnRzKGludGVnZXIsCmNoYXJhY3RlciB2YXJ5aW5nLGNoYXJhY3RlciB2
YXJ5aW5nLGNoYXJhY3RlciB2YXJ5aW5nLCBudW1lcmljLCBudW1lcmljKTsN
CiAgICAgICAgDQogICAgICAgIENSRUFURSBPUiBSRVBMQUNFIEZVTkNUSU9O
CmdldF9jaGFuZ2VvdmVydmlld19uZXdfYWRkZWRfY29tcG9uZW50cygNCi4N
Ci4NCi4NCi4NCg0KV2UgdHJpZWQgdG8gZXhlY3V0ZSB0aG9zZSBmdW5jdGlv
biBieSBzZXR0aW5nIHRyYW5zYWN0aW9uX2lzb2xhdGlvbiB0bwpzZXJpYWxp
emFibGUgYnV0IGVuY291bnRlcmVkIHNhbWUgaXNzdWUuDQoNClBsZWFzZSBz
dWdnZXN0IG1lIHRoYXQsSG93IGNhbiBJIGJ5cGFzcyB0aGlzIGVycm9yLi4u
T1IgaXMgaXQgYSBidWcgYWJvdXQKc2V2ZXJpdHkgb2YgbWVzc2FnZSAoInR1
cGxlIGNvbmN1cnJlbnRseSB1cGRhdGVkIilvbiB0aGlzIGtpbmQgb2YgaXNz
dWUKLlNob3VsZCBpdCBiZSBvbmx5IHdhcm5pbmcgPw0KDQotLQ0KS2lzaG9y
Lg0KDQoNCg0KCgo=

hargudekishor@gmail.com wrote:

> We have connected our node js application with postgresql-9.4.8.We have
> several functions which are getting created in database using async call
> from nodejs to postgresql server.but while creating those functions ,We are
> encountered a error of "Tuple concurrently updated".

Yeah, this is known.  We haven't worried too much, because the use case
seems thin: why are you deleting and creating the function over and
over?  Wouldn't it be better to create the function once and be done
with it?

> And because of this error our node js application is getting
> terminated.which is not affordable to us.

Surely the application should be able to do something else upon
receiving an error, rather than crashing.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> hargudekishor@gmail.com wrote:
>> We have connected our node js application with postgresql-9.4.8.We have
>> several functions which are getting created in database using async call
>> from nodejs to postgresql server.but while creating those functions ,We are
>> encountered a error of "Tuple concurrently updated".

> Yeah, this is known.  We haven't worried too much, because the use case
> seems thin: why are you deleting and creating the function over and
> over?  Wouldn't it be better to create the function once and be done
> with it?

Note that each one of those create calls, before failing, will have had
to wait for the previous updater to commit or not.  We could imagine
installing alternate behavior such as "don't fail if the new state of the
pg_proc row is exactly what you wanted anyway"; but I do not see any
useful semantics that would not involve waiting to see if the prior
transaction commits.  And then there's the cost of the bloat that you're
creating in the pg_proc catalog.  So quite aside from the possibility of
a concurrent-update failure, you'd be much better off from a performance
standpoint if you take the trouble to not repetitively create the same
function.

            regards, tom lane

On Wed, Aug 3, 2016 at 3:00 AM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> hargudekishor@gmail.com wrote:
>
>> We have connected our node js application with postgresql-9.4.8.We have
>> several functions which are getting created in database using async call
>> from nodejs to postgresql server.but while creating those functions ,We are
>> encountered a error of "Tuple concurrently updated".
>
> Yeah, this is known.  We haven't worried too much, because the use case
> seems thin: why are you deleting and creating the function over and
> over?  Wouldn't it be better to create the function once and be done
> with it?

Hm. These days I have been pinged regarding the fact that it is
possible to reach this error on a relation that has a lot of updates
and where autovacuum is made more aggressive. Well, it increases the
possibility to face it. simple_heap_delete() is not used except for
catalogs, so could it be related to one of the pg_stat catalogs
complaining with VACUUM ANALYZE running in parallel?

>> And because of this error our node js application is getting
>> terminated.which is not affordable to us.
>
> Surely the application should be able to do something else upon
> receiving an error, rather than crashing.

That's for sure. A crash is a bad idea, and I recall that this is not
a critical failure.
--
Michael

Hi,

Thanks everyone for your valuable inputs.

I got it now,The issue may be happening due to pg_proc catalog table update
for same function by different connection at the same time(due to async
call of  node js to multiple db connections for same functions creation ).


--
Kishor


On Wed, Aug 3, 2016 at 5:44 AM, Michael Paquier <michael.paquier@gmail.com>
wrote:

> On Wed, Aug 3, 2016 at 3:00 AM, Alvaro Herrera <alvherre@2ndquadrant.com>
> wrote:
> > hargudekishor@gmail.com wrote:
> >
> >> We have connected our node js application with postgresql-9.4.8.We have
> >> several functions which are getting created in database using async call
> >> from nodejs to postgresql server.but while creating those functions ,We
> are
> >> encountered a error of "Tuple concurrently updated".
> >
> > Yeah, this is known.  We haven't worried too much, because the use case
> > seems thin: why are you deleting and creating the function over and
> > over?  Wouldn't it be better to create the function once and be done
> > with it?
>
> Hm. These days I have been pinged regarding the fact that it is
> possible to reach this error on a relation that has a lot of updates
> and where autovacuum is made more aggressive. Well, it increases the
> possibility to face it. simple_heap_delete() is not used except for
> catalogs, so could it be related to one of the pg_stat catalogs
> complaining with VACUUM ANALYZE running in parallel?
>
> >> And because of this error our node js application is getting
> >> terminated.which is not affordable to us.
> >
> > Surely the application should be able to do something else upon
> > receiving an error, rather than crashing.
>
> That's for sure. A crash is a bad idea, and I recall that this is not
> a critical failure.
> --
> Michael
>