[SR-Users] Postgres: Exclusive Transaction Locks vs. Vacuumer

[Daniel-Constantin Mierla]

Hello,

I haven't added that part of code, nor using postgres to be able to
comment on specific points, but my blind guess is that there are many
queries done in between the table locks, like a check if a record exist,
followed by insert/update/delete/..., so just locking on a row might not
prevent races. Not sure if Peter is still around for more comments, iirc
he worked on that, ...

Cheers,
Daniel


On 21/10/16 11:00, Stefan Kohlhauser wrote:
> Hey everyone!
>
> We noticed that under high load of registrations and using the pua module we can reach a state where the PUA table bloats our Postgres to several GB. However, a SELECT on pua only shows a few hundred entries. The other half million or so entries are obsoleted but not cleaned up.
> The reason for this seems to be that the autovacuumer of the Postgres can't get to work because the PUA table is almost permanently locked.
> Our Postgres logs are full with errors about the autovacuumer being canceled because it couldn't get to work.
>
> Looking at modules/db_postgres/km_dbase.c::db_postgres_start_transaction() I noticed that there are two locking modes used:
> str lock_write_end_str = str_init(" IN EXCLUSIVE MODE");
> str lock_full_end_str = str_init(" IN ACCESS EXCLUSIVE MODE");
>
> According to
> https://www.postgresql.org/docs/9.2/static/explicit-locking.html
> these modes mean forbidding everything else except reads and forbidding everything else.
> However, according to this documentation ROW EXCLUSIVE is the standard lock for entry altering operations (INSERT, UPDATE, DELETE). And this would still allow the autovacuumer to do its job.
>
> Is there a specific reason why these more restricting locking modes are used instead of ROW EXCLUSIVE?
>
> Thanks.
>
> Best regards,
> Stefan
>
> _______________________________________________
> SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list
> sr-users at lists.sip-router.org
> http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users

-- 
Daniel-Constantin Mierla
http://twitter.com/#!/miconda - http://www.linkedin.com/in/miconda
Kamailio Advanced Training, Berlin, Oct 24-26, 2016 - http://www.asipto.com