Ebean PGvector

[Ryszard Trojnacki]

Hi Rob,

I have done some work to make Ebean work with pgvector extension for Postgres.

So far I have:

1. Extended ebean-datasource module to add posibility to register PGvector types.

2. Added module for main ebean.

Ad. 1. This just adds a new listener that allows to do something with newly created connection for example do: PGvector.registerTypes(connection);

Overall I'm not sure if this is the right way to do this.

This allows for manually changing new connection settings. But for a module this makes no sens. The module should be initalized without extra manually written code like: io.ebean.core.type.ExtraTypeFactory that is in META-INF/services.

Should I change this to ServiceLoader mode?

https://github.com/Ryszard-Trojnacki/ebean-datasource/tree/feature/extra-connection-initialize

Ad. 2. I have added a module ebean-pgvector that adds scalar types, but to do that I had also to:

- add extra types: ExtraDbTypes,
- add types to enum DbType,
- register new types in PostgresPlatform.

And now when everything is done I'm trying to run tests, but I have problem with this.

I have added PGvectorSetup and registered it in PlatformAutoConfig. I have setup this in application-test.yml, but it (Docker container) doesn't start.

What I'm missing?

https://github.com/Ryszard-Trojnacki/ebean/tree/feature/pgvector

-- 

Greetings,

Ryszard Trojnacki

[Rob Bygrave]

Sorry for the late reply, I've got a couple more things to sort out ... so I should get to this in 2 days time.

Cheers, Rob.

--

---
You received this message because you are subscribed to the Google Groups "Ebean ORM" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ebean+un...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/ebean/6d380161-a498-46a7-b897-1e26f394fb54n%40googlegroups.com.

[Ryszard Trojnacki]

> so I should get to this in 2 days time.

Happy to hear that.

[Rob Bygrave]

> 1. Extended ebean-datasource module to add posibility to register PGvector types.

Yes, create a PR. I think we might tweak it a little bit but yes submit the PR.

> Overall I'm not sure if this is the right way to do this. ... Should I change this to ServiceLoader mode?

Regardless of if it supports a ServiceLoader or not we need the API, so I think submit the PR.

Q: Would we then allow the use of ServiceLoader to auto register a "DataSourcePoolNewConnectionListener" ?

A: Yes, I think we can do that. It would make sense to support that I believe.

> I have added PGvectorSetup and registered it in PlatformAutoConfig. I have setup this in application-test.yml, but it (Docker container) doesn't start.

I'm not sure. It looks like the `platform: pgvector` might be an issue though as that should reference a platform known to ebean-test-containers so maybe that should be postgres instead. [but maybe we extend ebean-test-containers with specific support for pgvector such that we can programmatically setup the test container - the programmatic setup is my preferred approach these days generally so it would be good to support that].

So yes, submit a PR for ebean-datasource and lets go from there.

Cheers, Rob.

To view this discussion visit https://groups.google.com/d/msgid/ebean/9220af01-f8cf-4b27-89a2-2ce52cd9ffe5n%40googlegroups.com.

[Ryszard Trojnacki]

I have created a PR: https://github.com/ebean-orm/ebean-datasource/pull/139

I will now go with the second approach with `ServiceLoader` (expect a PR in next few days with this).

[Rob Bygrave]

> I will now go with the second approach with `ServiceLoader` (expect a PR in next few days with this).

Done via - https://github.com/ebean-orm/ebean-datasource/pull/140

To view this discussion visit https://groups.google.com/d/msgid/ebean/d998f01d-ebea-4733-99da-605ea3aac747n%40googlegroups.com.

[Ryszard Trojnacki]

Great!

I will go now with `ebean-pgvector-types`.

To create this module I needed to add new types to `ebean-api`:

- DbType.java,

- ExtraDbTypes.java.

This seems easy, but I not sure, if the constant values is ExtraDbTypes are correct?

And the rest is done like with `ebean-net-postgis-types`.

I have tried once again to go with the tests and I see, that I need probably to update first ebean-test-containers for pgvector to make this work. Am I right?

(I will go with this after work)

[Rob Bygrave]

> This seems easy, but I not sure, if the constant values is ExtraDbTypes are correct?

Yes this is correct thing to do. Those constant values just can't clash with the ones in java.sql.Types (and no they don't so it's all good there).

> that I need probably to update first ebean-test-containers for pgvector to make this work. Am I right?

Yes, I think so. It will likely look similar to the PostgisContainer extends BasePostgresContainer.

Cheers, Rob.

To view this discussion visit https://groups.google.com/d/msgid/ebean/d6ec07be-1eeb-4b04-bd00-54e34996de13n%40googlegroups.com.

[Ryszard Trojnacki]

I have added new PR: https://github.com/ebean-orm/ebean-test-containers/pull/133 - `PGvectorContainer`.

With this new `ebean-test-containers` my tests are starting now.

This test container tests (`PGvectorContainerTest`) could be updated in future with Ebean PGvector tests, but for now this is imposible without registration of those types.

I think in a day or two I will finish the `ebean-pgvector-types` module.

[Rob Bygrave]

>  PR: https://github.com/ebean-orm/ebean-test-containers/pull/133 - `PGvectorContainer`.

Awesome, merged and released as version 7.17

To view this discussion visit https://groups.google.com/d/msgid/ebean/27fdb47f-2c9b-415f-be3a-b12760654861n%40googlegroups.com.

[Ryszard Trojnacki]

I have made some tests, fixed some bugs and there it is:

https://github.com/ebean-orm/ebean/pull/3696

Now I would like go to querybeans, but there is a problem.

To make querybeans I need to make `Ptypes` for each PGvector type, but this would mean that I need to add dependency to `ebean-querybean` module to PGvector.

And here are two options:

1. Add a `provided` dependency. Not sure if this would work.

2. Add another module for PGvector querybeans.

What should I do here?

The same will be for postgis types. 

I would like also to make querybeans for them, but PostGIS types are  much bigger in terms of operators and I would add only a subset for them.

[Rob Bygrave]

> 1. Add a `provided` dependency.

Pretty sure we can go with this approach first and see how it goes but I think it will work well. 

To view this discussion visit https://groups.google.com/d/msgid/ebean/58da0204-df88-49a8-9a29-8a3905d8c6a6n%40googlegroups.com.

[Ryszard Trojnacki]

I started to working on `querybean`, but I have few doubts.

I have checked the PGvector documentation and it seems that there are few cases:

1. Ordering by vector distance: ORDER BY embedding <-> '[3,1,2]' LIMIT 5;

2. Selecting by distance (score): SELECT 1 - (embedding <=> '[3,1,2]') AS cosine_similarity

3. Limiting by distance (score): WHERE embedding <-> '[3,1,2]' < 5;

Ad. 3. This is the case that querybean would work, because it is in WHERE part. 

But even for that I'm not sure, because this is a operator that returns a number, not boolean.

Ebean querybeans return boolean results mainly for filtering.

This issue is quite simple, because it can be resolved in two ways:

a) make 3/4 parameters querybean function, that takes other vector, operator and number ( WHERE embedding <-> param_1 _op_param_ _val_param_;) and this will be a valid filter, 

b) make 1 parameter querybean function and return just another builder with float type; will it work with PFloat or I need to write another type?

Ad. 1. I'm not sure if this is possible with Ebean now?

But it seems like a b) scenario from above. I'm returning a PFloat (or new type) and there is method "desc"/"asc" and thats all.

Ad. 2. No clue about this one. 

Using formula (https://ebean.io/docs/mapping/extensions/formula) in bean doesn't help because it needs a parameter.

It seems that here is not to be done and it should be done like this: https://ebean.io/docs/query/select#formula

Nevertheless ORDER BY (point 1) and WHERE (point 2) will be good enought.

With vector stores we need to:

- order by vector discance (point 1),

- limit results amount (already works in Ebean),

- filter by attributes (normal/actual querybeans).

You support on this matter will be highly appreciated.