[YSQL] Add support for advisory_locks

[ddorian]

Jira Link: DB-2071

Advisory locks is not yet supported in Yugabyte (as of 2024.1). This issue tracks the support for Advisory locks.

With the fix for #19974 (commit 809199f5), the use of advisory locks fails with "advisory locks are not yet implemented" error. If the application doesn't strictly need advisory locks or doesn't want to fail the statement(as a temporary stop-gap), the GUC - yb_silence_advisory_locks_not_supported_error can be used as a stop-gap solution to avoid disruption in their application until they modify the application to remove usage of advisory locks (or till Advisory locks is natively supported in YB).

script.py

def pg_advisory():
    import psycopg2
    conn1 = psycopg2.connect("dbname='postgres' user='postgres' host=127.0.0.1 password='postgres' port=5432")
    conn2 = psycopg2.connect("dbname='postgres' user='postgres' host=127.0.0.1 password='postgres' port=5432")
    cur1 = conn1.cursor()
    cur2 = conn2.cursor()
    cur1.execute("SELECT pg_try_advisory_lock(1);")
    print("session_1_lock", cur1.fetchone())
    cur1.execute("SELECT pg_try_advisory_xact_lock(17);")
    print("transaction_1_lock", cur1.fetchone())
    cur2.execute("SELECT pg_try_advisory_lock(1);")
    print("session_2_lock", cur2.fetchone())
    cur2.execute("SELECT pg_try_advisory_xact_lock(17);")
    print("transaction_2_lock", cur2.fetchone())
    exit()

pg_advisory()

Yugabyte output:

session_1_lock (True,)
transaction_1_lock (True,)
session_2_lock (True,)
transaction_2_lock (True,)

Postgresql output:

session_1_lock (True,)
transaction_1_lock (True,)
session_2_lock (False,)
transaction_2_lock (False,)

[Amandeepsinghghai]

Hi, Coming from the slack channel https://yugabyte-db.slack.com/archives/CG0KQF0GG/p1596795769135800.

Summarizing the chat here incase the history is not maintained -

• Our application is split into microservices, each of which use a separate schema on the same YSQL database.
• We use golang-migrate which generates an advisory lock based on the database name + schema name (not the whole database).
• We use K8S Init Containers to run our migrations. However, when our microservices are starting up in parallel, only the first one's migrations succeed, and then rest fail.

Do we need to perhaps write some custom code to use a separate Locks table (referring to golang-migrate's cockroachdb implementation vs their postgres implementation)? We're creating a workaround to prevent our microservices from starting up simultaneously, but that prevents us from having our microservices be independently deployable.

[ddorian]

Hi @Amandeepsinghghai

Yes some custom code would be needed here. I think even the Mysql implementation should do in our case. Can you try that ?

[Amandeepsinghghai]

Hey @ddorian, thanks for the reply. By MYSQL, I am guessing you mean this.

Isn't "SELECT GET_LOCK(?, 10)" NOT part of the Postgres standard or maybe I missing something here?

Also looping in my teammate cc @RabidFire

[ddorian]

@Amandeepsinghghai yes, GET_LOCK is not supported in PostgreSQL/YugabyteDB.

The CRDB workaround should work in our case too.

[Amandeepsinghghai]

Okay. Let me see if that works.

Also, looking through the CRDB implementation, one key difference I see is that instead of schemas, Cockroach DB has separate databases.

[ddorian]

@Amandeepsinghghai that's because CRDB doesn't support user defined schemas.

While we do support them. So multiple schemas should work with us.

[jameshuynh]

Facing the same issue when provision postgres database using TF - https://registry.terraform.io/providers/cyrilgdn/postgresql/latest/docs/resources/postgresql_database

[yogendra]

@jameshuynh I would recommend silencing the advisory lock request errors with

ALTER ROLE <DB_UserName> SET yb_silence_advisory_locks_not_supported_error=on;

Advisory locks are for avoiding concurrent DDLs. In tools like terraform you are already locking the concurrent script execution, so its safe to silence the errors. Additionally, I have seen that advisory locks being a session level construct have issues when executing on PG also in certain configurations.

Silencing advisory locks support error does not impact any DMLs.

[sameer-m-dev]

Hi YB team, are we planning on adding this feature anytime soon?

[sunsided]

Just stumbled upon this while setting up hopscotch (see here).

[rthallamko3]

@sameer-m-dev , The feature is on our roadmap, we are designing the feature currently and are making progress towards adding the support.