Add Pg Search Extension in WarpSQL

[singhalkarun]

Halting this for a while as we weren't able to crack this. Will be talking to Philippe (from ParadeDB) for solutions/recommendations.

[KDwevedi]

any updates here @singhalkarun ? Should we try another extension for BM25? https://blog.pgvecto.rs/pgbestmatchrs-elevate-your-postgresql-text-queries-with-bm25

[singhalkarun]

Hi @KDwevedi yet to talk to Philippe. Can you directly use paradedb image for testing BM25 for now until we crack this? I don't want the dev to be blocked due to this https://github.com/paradedb/paradedb

Let me know if their are blockers in using paradedb image and will figure out ways to unblock those.

docker run --name paradedb paradedb/paradedb

[KDwevedi]

@ChakshuGautam RFC in Confluence

[singhalkarun]

Conversation with Philippe from ParadeDb is started on Slack. @Srijan-SS02 to post further updates here.

[Srijan-SS02]

Comments from Phillippe:

• the pg_search GEM is not maintained by us. It's a different project, you shouldn't use that.
• You use cargo build instead of cargo pgrx package, which is what you should be using to create the installs. Please follow the README.md instructions

[singhalkarun]

Current Dockerfile

FROM rust:latest AS pg_search_builder
ARG PG_VERSION=14

RUN apt-get update && apt-get install -y wget gnupg2 lsb-release

RUN sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

RUN wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -

RUN apt-get update && apt-get install -y \
    git \
    postgresql-${PG_VERSION} \
    postgresql-server-dev-${PG_VERSION}     \
    libclang-dev

RUN git clone --branch main https://github.com/paradedb/paradedb.git /paradedb
WORKDIR /paradedb/pg_search

RUN cargo install --locked cargo-pgrx --version 0.11.3

RUN PG_MAJOR_VERSION=$(echo ${PG_VERSION} | cut -d '.' -f 1) && \
    PG_CONFIG_PATH=$(which pg_config) && \
    export PATH=$PATH:/usr/lib/postgresql/${PG_VERSION}/bin && \
    cargo pgrx init --pg${PG_MAJOR_VERSION} ${PG_CONFIG_PATH}

RUN PG_MAJOR_VERSION=$(echo ${PG_VERSION} | cut -d '.' -f 1) && \
    cargo build --release --no-default-features --features "pg${PG_MAJOR_VERSION}"

FROM samagragovernance/postgres:1.0.1-pg15

RUN apk update && apk add ruby && gem install pg_search

COPY --from=pg_search_builder /paradedb/target/release/libpg_search.so /usr/local/lib/postgresql/
COPY --from=pg_search_builder /paradedb/pg_search/pg_search.control /usr/local/share/postgresql/extension/
COPY --from=pg_search_builder /paradedb/pg_search/sql/*.sql /usr/local/share/postgresql/extension/

RUN echo "shared_preload_libraries = 'timescaledb,pg_search'" >> /usr/local/share/postgresql/postgresql.conf.sample

[Srijan-SS02]

Phillipe:

Hey! We don’t have any expertise with Alpine Linux at the company. Best would be for you to experiment and we can help you async. Once you get it working, it would be great if you could contribute the instructions to the README. If you need a call for this, we will have to charge you for our support tier as we do not offer non-Ubuntu support as part of our community support