[Bug]: Failing to build extension using projects/extension/build.py install

[Abhayraj-pipesort]

What happened?

Followed all steps for installation from source but after installing the build.py file using projects/extension/build.py install command it is failing to create extension properly.
It is expecting a file to be there named as pgai/projects/extension/sql/ai--0.4.2.-dev.sql

pgai extension affected

0.4.2

pgai library affected

No response

PostgreSQL version used

16

What operating system did you use?

Windows 11

What installation method did you use?

Source

What platform did you run on?

Other

Relevant log output and stack trace

No response

How can we reproduce the bug?

Followed official docs and installed

Are you going to work on the bugfix?

None

[JamesGuthrie]

@Abhayraj-pipesort thanks for your interest in pgai. We're aware that it's currently not possible to use it on Windows. My colleague was recently working on fixing this here: #237.

I'm not sure how sonn we'll get that change merged, but you might be able to use some of the pieces of that pull request in the meantime.

[alejandrodnm]

Hey @Abhayraj-pipesort I was working on the Windows PR, I was trying to add the Windows tests as part of the Ci, but failed to do it. The github actions that installed PostgreSQL in windows didn't have some packages I needed to install pgvector, which is a dependency. The one action that's the same that pgvector uses, only supports PG14 and we require 16 or greater.

I had to stop work on this branch. I plan to retake it, just to do some manual tests. I was testing everything via the windows-latest github runner, while trying to implement the Ci.

It would be really helpful if you can test the branch locally.

[k-zshiba]

[Question]
I understand that #237 is not committed because, although the test sets for Windows in it are sufficient, CI cannot be executed.

Is my understanding correct?

[alejandrodnm]

Hey @Abhayraj-pipesort . There are multiple things with native postgres in Windows:

I tried first by adding CI to it, and failed. The github actions I found that set up Postgres in Windows, either didn't have the dev dependencies to build some of the python devs, or in the case of the one used by pgvector, didn't support PG16 which is our baseline.

I tried then to do a manual install on an ec2 Windows machine. That's the latest force-push of the branch. The installation worked, but the postgres functions were failing because the ai module we install was not being loaded properly by plpython3u. What we are planning is pointing Windows users to run the Docker container that comes with pgai installed.

If you want to try out what I did, maybe you can fix it, or maybe it was a problem with my setup:

• Install pgvector
• Checkout the adn/windows branch.
• Set the PG_BIN environment variable. This should be set to the directory that contains pg_config.exe.
• Then cd pgai/projects/extension.
• python build.py install
  • If you have python 3.11 then you're going to have to install a rust compiler, otherwise tiktoken will fail (unless they publish the compiled wheel to pypi)
• Restart Postgres.
• Try to use one of the ai functions.

Things that failed for me at different times:

SRE module mismatch

create or replace function foo() returns pg_catalog.text
as $python$
    from pathlib import Path
    return 'foo'
$python$
language plpython3u;

select foo();

ERROR:  AssertionError: SRE module mismatch
CONTEXT:  Traceback (most recent call last):
  PL/Python function "foo", line 2, in <module>
    from pathlib import Path
  PL/Python function "foo", line 0, in <module>
  PL/Python function "foo", line 13, in <module>
  PL/Python function "foo", line 124, in <module>
  PL/Python function "foo", line 16, in <module>
PL/Python function "foo" 

SQL state: 38000

This error was because my plpython3u interpreter was python 3.11 while the extension was installed with python 3.10. Make sure the pg version from plpython3u matches the one you used to install the extension.

You can check the plpython3u version with:

create or replace function python_version() returns text
 as $python$
     import platform
     return platform.python_version()
 $python$
 language plpython3u;

 select python_version();

Any query using plpython3u causes the pg server to restart

I had to set PYTHONHOME, and PYTHONPATH as system environment variables. I don't know if this is the correct approach. I'd would first try to run a simple python function like foo from the previous point. You can check the postgres logs and see the error message with the values for pythonhome and pythonpath.

** version is not part of ai or something like that

When you run any of the ai. functions, on of the first thing that happens (after some path magic) is that we do something like:

import ai
ai.__version__

If the import ai works but the ai.__version__ fails, then it means you hit the same issue I did. In my case the ai module was not loaded correctly.

You can see it by doing:

create or replace function ai_path () returns pg_catalog.text
as $python$
    import site
    site.addsitedir(r'C:\Program Files\pgai\lib\0.6.0')
    import ai
    return ai.__path__
$python$
language plpython3u;

select ai_path();

If the value returned is _NamespacePath(['C:\Program Files\pgai\lib\0.6.0\ai']) instead of ['C:\Program Files\pgai\lib\0.6.0\ai'], then it means that the __init__.py file C:\Program Files\pgai\lib\0.6.0\ai\__init__.py is not being recognized.

This is were I got stuck.