Yugabyte-db: [YSQL] Enable UUID extension by default

[RashmiPanthangi]

YB v2.0

Currently, the UUID extension is not enabled by default. Could you please enable this extension by default?

If we run YB in docker, could you please provide steps to enable uuid-ossp extension?

[ndeodhar]

Hi @RashmiPanthangi ,

We don't support uuid-ossp extension yet. However, we have pgcrypto extension which provides UUID function. You can see this for more details on how to use it:
https://docs.yugabyte.com/latest/api/ysql/extensions/

Related issue for uuid-ossp is being tracked here: #1867

[ndeodhar]

To clarify my previous comment, you can still use uuid-ossp with YugabyteDB using some manual steps. Specifically, you'll need to have vanilla postgres installed and then you can copy over uuid-ossp.so and couple of other files into YugaByte lib folder and then use it. See this for detailed steps: https://docs.yugabyte.com/latest/api/ysql/extensions/#uuid-ossp

The issue #1867 is to track supporting it in YugabyteDB without the manual steps.

If you simply want UUID function, then pgcrypto extension's gen_random_uuid() is the preferred way. That's what postgres documentation indicates as well:

Note: If you only need randomly-generated (version 4) UUIDs, consider using the gen_random_uuid() function from the pgcrypto module instead.

This is from https://www.postgresql.org/docs/11/uuid-ossp.html

[Bessonov]

While activating pgcrypto makes sense, I would like to propose a parameter or like to provide multiple extensions, which should be activated on start.

[rkarthik007]

I like this idea @Bessonov. Something to consider as well cc @ndeodhar @m-iancu

[ndeodhar]

Hi @Bessonov , to make sure that I understand your request correctly - you want to able to specify which extensions should be automatically loaded during start up (for example, while starting tserver)? And that way, you don't need to specify CREATE EXTENSION ...?

[Bessonov]

@ndeodhar yes, correct :) I think it's OK to make an one-shot call from privileged user to activate extensions etc. for long living on-premise instances of YB or Postgres. But it's not an appropriate way for dockerized workloads. Especially, because you can't use unprivileged user and do that in migration tools.

Just to be clear, it's not about a special parameter, but more about "a way" to activate extensions or about to do that without any manual action. For example, if there is a way to run CREATE EXTENSION statements on start up without need to pass root credentials - that's fine for me too.

[ndeodhar]

That makes sense @Bessonov, thanks for the input!

[Bessonov]

Although it's isn't mentioned in the docs, yb supports postgres' if not exists:

db=# CREATE EXTENSION pgcrypto;
ERROR:  extension "pgcrypto" already exists

db=# CREATE EXTENSION IF NOT EXISTS pgcrypto;
NOTICE:  extension "pgcrypto" already exists, skipping
CREATE EXTENSION

So it could help to activate extensions on startup.