Windows Support

[lfittl]

Issue to track support of Windows - currently it is not supported, in part due to build process, in part because the code extraction from Postgres intentionally ignores Windows code.

[Corion]

I did look at making it build on Windows, but besides adding some more -DDEFINEs and some stubs, I haven't progressed far. I think the general approach is to fake as much of the "normal" Pg environments to get the code to compile without any changes to the Pg code at all, but I don't understand why the files are copied during the build process.

Can you maybe outline a bit what the general approach is? I have the rough idea that you implemented a preprocessor that rewrites/copies the C files according to some preset #defines, but a bit more detail would help me get more of the general approach.

[lfittl]

@Corion The general approach taken is that code is extracted using LLVM/clang (plus a Ruby helper script), see https://github.com/lfittl/libpg_query/blob/10-latest/scripts/extract_source.rb

That means the code that gets extracted is partially dependent on the environment where the extraction runs, as well as the defines passed to clang: https://github.com/lfittl/libpg_query/blob/10-latest/scripts/extract_source.rb#L205

My general thoughts on how to make progress on this:

• Remove the explicit ignores of Windows build files in https://github.com/lfittl/libpg_query/blob/10-latest/scripts/extract_source.rb#L97
• Try to build, and compile a list of places where we error out right now
• Manually adjust defines and try to fix things to the point where we know what changes are needed (and ideally things build and don't crash)
• Re-introduce the changes needed to the extraction script, so we can re-run it for new Postgres versions without issue

[Corion]

I've pushed my (really hacky) changes that I needed to make some progress on Windows as https://github.com/Corion/libpg_query/tree/win32-hackery . I think parts of my lack of progress can be attributed to the simply missing build files for Win32, but I'm not sure if the source code really has that many OS dependencies.

I think at least the second point ("list of places where we error out") should be somewhat reduced with the changes in the win32-hackery branch. Please note that I approached the issue strictly with the mindset of "anything that's necessary to get this to compile", not with the mindset of understanding things or finding a nice/maintainable solution. So cherry-picking patches is not really advised except for maybe the parts where a #define was hacked.

[lfittl]

@Corion Nice, thanks for sharing!

Its a priority for me to make progress on this, however there is a few other things on my plate too unfortunately. I'll update here with any progress I make as well to keep you and others in the loop.

[akeeman]

Any progress?

[lfittl]

@akeeman Not yet - thanks for checking!

I'm curious, whats your use case for the library on Windows?

[akeeman]

A few of my colleagues are working on windows, and run some code on their local machine outside of their Docker container on a schedule. All the other code is platform independent, so before it was fine to do so.

We use this library via pglast to extract the tables given a query. During tests it's for dynamically only inititalizing those tables used in the test during integration tests. Besides that it's for automatically locking tables we write.

[amhuhn2]

Hi, @lfittl any progress here?

Here's my use case: I use Windows as my development workstation, although eventually this will be deployed to Heroku, running on some flavor of Linux. But while I am developing the code, I'd like to be able to run it locally to test/develop.

I need to parse a view so that I can walk the parse tree and attempt to figure out which rows are dependent on a row currently being updated.

Thank you for your hard work to maintain such a useful package! I can't wait to start using it.

[lfittl]

@amhuhn2 Nothing to report yet, but thanks for the additional context of your use case - that is helpful to know.

I may be able to take some time to work on this in the upcoming weeks, and will certainly update this issue as I get closer to making this a reality. Thanks for your patience!

[amhuhn2]

Sounds good, @lfittl , thank you.

[runfaj]

@lfittl Any progess on this? In our use case, we use pg-query-parser for our package for building dynamic AST queries in node. pg-query-parser relies on pg-query-native, which in turn, relies on this package.

Because this package doesn't have windows support, some of the build symbols for the other packages fail, thus making the whole project to fail install. All of our team works on macs, where this package works, but in order to allow windows development, this is the only package out of hundreds that ends up preventing it.

[daisuke-yoshimoto]

Any updates on this?

[lfittl]

@daisuke-yoshimoto No updates at this point - Windows support is definitely doable, but I (and the team at pganalyze) have our hands full with other things.

PRs are of course welcome, if you are affected by this and would like to help solve the issue (since Postgres itself builds fine on Windows, we are most likely just missing a few files for portability).

We may have time to work on this with the Postgres 14 version of the parser, planned for later this year.

[daisuke-yoshimoto]

@lfittl

Thank you for your reply. I understood the situation. I will use it via wsl for a while.

[heysinghal]

Hi @lfittl Any Update on this?

[404shades]

Hi @lfittl , Thanks for the amazing library. This library has proved to be ver helpful for us. Just wanted to check if windows support is still in your list. If not can you please suggest libraries which help us do so.
Thanks

[lfittl]

@heysinghal @404shades Its still something I'd like to support eventually, but its not a high priority at this point (and there is no ETA for supporting it).

The main challenge here is related to build tooling - Postgres itself of course supports Windows, but that has a lot of supporting logic we don't have in libpg_query today.

If you are looking for alternate solutions I would just search for other SQL parser projects (and investigate e.g. CockroachDB's SQL parser).

The main motivation for using pg_query is for when you need full Postgres compatibility. For that I'm not aware of any other projects that provide the Postgres parser as a library. Hope that helps!

[karlhorky]

Just chiming in here to say that it seems that this is affecting our bootcamp students who are trying to use the amazing SafeQL SQL linter (libpg_query via libpg-query-node)

• SafeQL installation fails on Windows ts-safeql/safeql#80
• Window support (depends on llbpg_query) launchql/libpg-query-node#22

So the people on Windows currently cannot get linting errors on their SQL queries 😬

Thanks for the project by the way! Really incredible what it enables.

Windows support would enable more people to access this!

[karlhorky]

@lfittl if anyone has time to take a look at this before you get around to it, do you have any idea about the reasons that this is breaking?

Below is the build log copied from ts-safeql/safeql#80 (the lines with gyp, compiling using the Visual Studio 2017 build tools):

$ yarn add --dev @ts-safeql/eslint-plugin libpg-query
yarn add v1.22.19
[1/4] �  Resolving packages...
warning Resolution field "eslint-plugin-jest@27.0.4" is incompatible with requested version "eslint-plugin-jest@^25.3.0"
[2/4] �  Fetching packages...
[3/4] �  Linking dependencies...
warning "@emotion/react > @emotion/babel-plugin@11.10.2" has unmet peer dependency "@babel/core@^7.0.0".
warning "@emotion/react > @emotion/babel-plugin > @babel/plugin-syntax-jsx@7.18.6" has unmet peer dependency "@babel/core@^7.0.0-0".
warning " > @babel/eslint-parser@7.19.1" has unmet peer dependency "@babel/core@>=7.11.0".
warning " > eslint-plugin-flowtype@8.0.3" has unmet peer dependency "@babel/plugin-syntax-flow@^7.14.5".
warning " > eslint-plugin-flowtype@8.0.3" has unmet peer dependency "@babel/plugin-transform-react-jsx@^7.14.9".
[4/4] �  Building fresh packages...
[-/4] ⠁ waiting...
[-/4] ⠁ waiting...
[-/4] ⠂ waiting...
error C:\Users\Victor\projects\debugging-programs\next-js-ecommerce-store\node_modules\libpg-query: Command failed.
Exit code: 1
Command: node-pre-gyp install --fallback-to-build
Arguments:
Directory: C:\Users\Victor\projects\debugging-programs\next-js-ecommerce-store\node_modules\libpg-query
Output:
node-pre-gyp info it worked if it ends with ok
node-pre-gyp info using node-pre-gyp@1.0.10
node-pre-gyp info using node@18.5.0 | win32 | x64
node-pre-gyp info check checked for "C:\Users\Victor\projects\debugging-programs\next-js-ecommerce-store\node_modules\libpg-query\build\Release\queryparser.node" (not found)
node-pre-gyp http GET https://supabase-public-artifacts-bucket.s3.amazonaws.com/libpg-query-node/queryparser-v13.2.5-node-v108-win32-x64.tar.gz
node-pre-gyp ERR! install response status 404 Not Found on https://supabase-public-artifacts-bucket.s3.amazonaws.com/libpg-query-node/queryparser-v13.2.5-node-v108-win32-x64.tar.gz
node-pre-gyp WARN Pre-built binaries not installable for libpg-query@13.2.5 and node@18.5.0 (node-v108 ABI, unknown) (falling back to source compile with node-gyp)
node-pre-gyp WARN Hit error response status 404 Not Found on https://supabase-public-artifacts-bucket.s3.amazonaws.com/libpg-query-node/queryparser-v13.2.5-node-v108-win32-x64.tar.gz
gyp info it worked if it ends with ok
gyp info using node-gyp@8.4.1
gyp info using node@18.5.0 | win32 | x64
gyp info ok
gyp info it worked if it ends with ok
gyp info using node-gyp@8.4.1
gyp info using node@18.5.0 | win32 | x64
gyp info find Python using Python version 3.9.6 found at "C:\Users\Victor\AppData\Local\Programs\Python\Python39\python.exe"
gyp info find VS using VS2017 (15.9.28307.1585) found at:
gyp info find VS "C:\Program Files (x86)\Microsoft Visual Studio\2017\BuildTools"
gyp info find VS run with --verbose for detailed information
gyp info spawn C:\Users\Victor\AppData\Local\Programs\Python\Python39\python.exe
gyp info spawn args [
gyp info spawn args   'C:\\Users\\Victor\\projects\\debugging-programs\\next-js-ecommerce-store\\node_modules\\node-gyp\\gyp\\gyp_main.py',
gyp info spawn args   'binding.gyp',
gyp info spawn args   '-f',
gyp info spawn args   'msvs',
gyp info spawn args   '-I',
gyp info spawn args   'C:\\Users\\Victor\\projects\\debugging-programs\\next-js-ecommerce-store\\node_modules\\libpg-query\\build\\config.gypi',
gyp info spawn args   '-I',
gyp info spawn args   'C:\\Users\\Victor\\projects\\debugging-programs\\next-js-ecommerce-store\\node_modules\\node-gyp\\addon.gypi',
gyp info spawn args   '-I',
gyp info spawn args   'C:\\Users\\Victor\\AppData\\Local\\node-gyp\\Cache\\18.5.0\\include\\node\\common.gypi',
gyp info spawn args   '-Dlibrary=shared_library',
gyp info spawn args   '-Dvisibility=default',
gyp info spawn args   '-Dnode_root_dir=C:\\Users\\Victor\\AppData\\Local\\node-gyp\\Cache\\18.5.0',
gyp info spawn args   '-Dnode_gyp_dir=C:\\Users\\Victor\\projects\\debugging-programs\\next-js-ecommerce-store\\node_modules\\node-gyp',
gyp info spawn args   '-Dnode_lib_file=C:\\\\Users\\\\Victor\\\\AppData\\\\Local\\\\node-gyp\\\\Cache\\\\18.5.0\\\\<(target_arch)\\\\node.lib',
gyp info spawn args   '-Dmodule_root_dir=C:\\Users\\Victor\\projects\\debugging-programs\\next-js-ecommerce-store\\node_modules\\libpg-query',
gyp info spawn args   '-Dnode_engine=v8',
gyp info spawn args   '--depth=.',
gyp info spawn args   '--no-parallel',
gyp info spawn args   '--generator-output',
gyp info spawn args   'C:\\Users\\Victor\\projects\\debugging-programs\\next-js-ecommerce-store\\node_modules\\libpg-query\\build',
gyp info spawn args   '-Goutput_dir=.'
gyp info spawn args ]
gyp info ok
gyp info it worked if it ends with ok
gyp info using node-gyp@8.4.1
gyp info using node@18.5.0 | win32 | x64
gyp info spawn C:\Program Files (x86)\Microsoft Visual Studio\2017\BuildTools\MSBuild\15.0\Bin\MSBuild.exe
gyp info spawn args [
gyp info spawn args   'build/binding.sln',
gyp info spawn args   '/clp:Verbosity=minimal',
gyp info spawn args   '/nologo',
gyp info spawn args   '/p:Configuration=Release;Platform=x64'
gyp info spawn args ]
Building the projects in this solution one at a time. To enable parallel build, please add the "/m" switch.
  nothing.c
  win_delay_load_hook.cc
  nothing.vcxproj -> C:\Users\Victor\projects\debugging-programs\next-js-ecommerce-store\node_modules\libpg-query\build\Release\\nothing.lib
  prebuild_dependencies
  The system cannot find the path specified.
C:\Program Files (x86)\Microsoft Visual Studio\2017\BuildTools\Common7\IDE\VC\VCTargets\Microsoft.CppCommon.targets(209,5): error MSB6006: "cmd.exe" exited with code 1. [C:\Users\Victor\projects\debugging-programs\next-js-ecommerce-store\node_modules\libpg-query\build\queryparser.vcxproj]
gyp ERR! build error
gyp ERR! stack Error: `C:\Program Files (x86)\Microsoft Visual Studio\2017\BuildTools\MSBuild\15.0\Bin\MSBuild.exe` failed with exit code: 1
gyp ERR! stack     at ChildProcess.onExit (C:\Users\Victor\projects\debugging-programs\next-js-ecommerce-store\node_modules\node-gyp\lib\build.js:194:23)
gyp ERR! stack     at ChildProcess.emit (node:events:537:28)
gyp ERR! stack     at ChildProcess._handle.onexit (node:internal/child_process:291:12)
gyp ERR! System Windows_NT 10.0.22000
gyp ERR! command "C:\\Program Files\\nodejs\\node.exe" "C:\\Users\\Victor\\projects\\debugging-programs\\next-js-ecommerce-store\\node_modules\\node-gyp\\bin\\node-gyp.js" "build" "--fallback-to-build"
"--module=C:\\Users\\Victor\\projects\\debugging-programs\\next-js-ecommerce-store\\node_modules\\libpg-query\\build\\Release\\queryparser.node" "--module_name=queryparser" "--module_path=C:\\Users\\Victor\\projects\\debugging-programs\\next-js-ecommerce-store\\node_modules\\libpg-query\\build\\Release" "--napi_version=8" "--node_abi_napi=napi" "--napi_build_version=0" "--node_napi_label=node-v108"
gyp ERR! cwd C:\Users\Victor\projects\debugging-programs\next-js-ecommerce-store\node_modules\libpg-query
gyp ERR! node -v v18.5.0
gyp ERR! node-gyp -v v8.4.1
gyp ERR! not ok
node-pre-gyp ERR! build error
node-pre-gyp ERR! stack Error: Failed to execute 'C:\Program Files\nodejs\node.exe C:\Users\Victor\projects\debugging-programs\next-js-ecommerce-store\node_modules\node-gyp\bin\node-gyp.js build --fallback-to-build --module=C:\Users\Victor\projects\debugging-programs\next-js-ecommerce-store\node_modules\libpg-query\build\Release\queryparser.node --module_name=queryparser --module_path=C:\Users\Victor\projects\debugging-programs\next-js-ecommerce-store\node_modules\libpg-query\build\Release --napi_version=8 --node_abi_napi=napi --napi_build_version=0 --node_napi_label=node-v108' (1)
node-pre-gyp ERR! stack     at ChildProcess.<anonymous> (C:\Users\Victor\projects\debugging-programs\next-js-ecommerce-store\node_modules\@mapbox\node-pre-gyp\lib\util\compile.js:89:23)
node-pre-gyp ERR! stack     at ChildProcess.emit (node:events:537:28)
node-pre-gyp ERR! stack     at maybeClose (node:internal/child_process:1091:16)
node-pre-gyp ERR! stack     at ChildProcess._handle.onexit (node:internal/child_process:302:5)
node-pre-gyp ERR! System Windows_NT 10.0.22000
node-pre-gyp ERR! command "C:\\Program Files\\nodejs\\node.exe" "C:\\Users\\Victor\\projects\\debugging-programs\\next-js-ecommerce-store\\node_modules\\@mapbox\\node-pre-gyp\\bin\\node-pre-gyp" "install" "--fallback-to-build"
node-pre-gyp ERR! cwd C:\Users\Victor\projects\debugging-programs\next-js-ecommerce-store\node_modules\libpg-query
node-pre-gyp ERR! node -v v18.5.0
node-pre-gyp ERR! node-pre-gyp -v v1.0.10
node-pre-gyp ERR! not ok
Failed to execute 'C:\Program Files\nodejs\node.exe C:\Users\Victor\projects\debugging-programs\next-js-ecommerce-store\node_modules\node-gyp\bin\node-gyp.js build --fallback-to-build --module=C:\Users\Victor\projects\debugging-programs\next-js-ecommerce-store\node_modules\libpg-query\build\Release\queryparser.node --module_name=queryparser --module_path=C:\Users\Victor\projects\debugging-programs\nex

[karlhorky]

@lfittl Oh sorry, I see now that you posted back in 2018 a todo list about this - is this still accurate?

• Windows Support #44 (comment)

If it is still accurate, would you mind adding a bit more detail as to how to achieve each step in your list? Then maybe someone like @Eprince-hub may be able to spend some time on this - at least to compile the list of errors that happen.

[lfittl]

@karlhorky Yes, this is still accurate.

What I would recommend is comparing a Postgres source build and a libpg_query build side-by-side, and see what the differences are that need to be addressed. Its likely that the defines that are set are not correct for Windows (Postgres usually autodetects this via the configure script), as well as some files might be missing that need to be present.

Assuming you are working with the latest 13-latest branch here, I would recommend having a Postgres 13 source to test with, and copying files (or source code portions) from there as needed until you get a working build.

This might be easy, or a lot of effort, but unfortunately due to time constraints (and lack of access to a Windows dev machine) I won't have time to help much on this at this point in time.

[karlhorky]

unfortunately due to time constraints (and lack of access to a Windows dev machine) I won't have time to help much on this at this point in time

If there was a bounty on this issue, would this change the situation for you at all? :) We would love to jump in and do this, but are also constrained on time at the moment.

[lfittl]

If there was a bounty on this issue, would this change the situation for you at all? :) We would love to jump in and do this, but are also constrained on time at the moment.

Thanks for the offer, but the problem is our team being time limited / having an engineer with a Windows machine ready to take this on, not that we wouldn't want to pay someone to work on it :)

[DerTimonius]

Is there any way for someone, who has never worked with C (aka me), to help with this? I do have a Windows desktop and would like to help in any way I can!

[karlhorky]

Wonder if an unconventional alternative would be to compile the C code to WebAssembly (Wasm) using something like emscripten, similar to how Neon.tech compiles WolfSSL to Wasm (Cloudflare blog post)

Of course, ideally libpg_query can be set up to be compiled directly to a binary on Windows - better performance, lower overhead. But maybe compiling to Wasm offers a workaround... 🤔 Maybe also useful for other projects / environments which cannot compile to binaries too...

Also, if the compiled Wasm could be published to npm, then it may offer a way to avoid the compilation step every time thelibpg-query package is installed with npm - for example when packages that depend on it are installed such as the ESLint plugin SafeQL - @ts-safeql/eslint-plugin. Avoiding this compilation step would make installation much faster.

cc @Newbie012

[karlhorky]

@RReverser recently ported Sharp to WebAssembly, and used the emnapi library below, which may be an easier path forward with wasm:

• https://github.com/toyobayashi/emnapi

[karlhorky]

@gregnr opened a PR using emnapi + WebAssembly to get libpg_query support on Windows 👀 🔥 (in the libpg-query npm package)

• Adds browser (WASM) support launchql/libpg-query-node#34

@lfittl would this be an acceptable approach for mainline libpg_query on Windows as well? (maybe even just a temporary approach for *some* kind of support, until proper Windows support comes along?)

[karlhorky]

cc @yrashk, in case figuring out compiling libpg_query for Windows without WebAssembly would fit into the open source work that you have been doing in the PostgreSQL space

[lfittl]

Thank you all for your patience on getting this resolved!

After a lot of experimentation over the holidays, I'm happy to say that this is resolved, and the latest libpg_query 16-5.1.0 release now supports Windows. If you're using libpg_query through a binding in another language, I would expect the corresponding maintainers to update soon (and we at pganalyze will be releasing updated Go/Ruby/Rust bindings later this week).

[lelit]

If you're using libpg_query through a binding in another language, I would expect the corresponding maintainers to update soon

Yes, I hope to tackle this in lelit/pglast#7: main problem is to figure out how to call nmake under Windows here... will do some experiments as time permits.