can you make a setup for pg12 on windows systems?

[gdonufrio]

Please , can you make a setup for pg12 on windows systems?

[rjuju]

Hi,

Unfortunately this is out of my knowledge. I don't have a windows system and didn't use one in more than a decade, so I'm not in a position to do it.

If you have a windows system, you could maybe try https://wiki.postgresql.org/wiki/Building_and_Installing_PostgreSQL_Extension_Modules or https://www.2ndquadrant.com/en/blog/compiling-postgresql-extensions-visual-studio-windows/.

Note that I'll be happy to fix any problem this may undercover with the source code (it will likely have issue with missing PGDLLEXPORT, maybe other things).

[suprimex]

Hi, playing with compilation on windows as advised here: https://wiki.postgresql.org/wiki/Building_and_Installing_PostgreSQL_Extension_Modules
I got just one error:

Severity Code Description Project File Line Suppression State
Error LNK2001 unresolved external symbol autovacuum_max_workers pg_qualstats pg_qualstats.obj 1

Otherwise, the compilation goes fine.
Also, I suppose need to be added PGDLLEXPORT as advised above (see link)

[rjuju]

@suprimex thanks a lot for looking at it!

Unfortunately the problem with autovacuum_max_workers has to be fixed in postgres, not pg_qualstats. Looking at previous changes in postgres to add PGDLLIMPORT, such annotation aren't backported so it means that pg_qualstats wouldn't be compilable on windows before postgres 15 at best. I'll try to have the PGDLLIMPORT added in postgres 15 and also see if I can use an alternative method to retrieve the value so you can compile pg_qualstats on windows with existing postgres versions. It would be a bit less efficient but it's only needed once during server startup so it shouldn't be a problem.

[rjuju]

@suprimex I just pushed a fix_windows branch (https://github.com/powa-team/pg_qualstats/tree/fix_windows) that should fix at least this issue. Is that enough to allow compilation on Windows?

[suprimex]

Thanks, @rjuju now it is compiled without errors.
But do not forget about PGDLLEXPORT as advised above link, otherwise, at runtime, we getting an error:
Could not find function "pg_qualstats_2_0" in file ... pg_qualstats.ddl

[suprimex]

p.s. (quote) You should also add a prototype marked PGDLLEXPORT for the function so that MSVC knows to export its symbol:

PGDLLEXPORT Datum <your_function_name>(PG_FUNCTION_ARGS); /* << like this*/
PG_FUNCTION_INFO_V1(your_function_name);

[rjuju]

Thanks for checking the patch. For the PGDLLEXPORT, I thought that PG_FUNCTION_INFO_V1 would take care of that. But looking twice it's actually doing half the work unfortunately, as seen in https://github.com/postgres/postgres/blob/master/src/include/fmgr.h#L417:

 *	On Windows, the function and info function must be exported.  Our normal
 *	build processes take care of that via .DEF files or --export-all-symbols.
 *	Module authors using a different build process might need to manually
 *	declare the function PGDLLEXPORT.  We do that automatically here for the
 *	info function, since authors shouldn't need to be explicitly aware of it.
*/
#define PG_FUNCTION_INFO_V1(funcname) \
extern Datum funcname(PG_FUNCTION_ARGS); \
extern PGDLLEXPORT const Pg_finfo_record * CppConcat(pg_finfo_,funcname)(void); \
const Pg_finfo_record * \
[...]

So I'm assuming that I have to do it for all the functions declared as PG_FUNCTION_INFO_V1()? Do you know if other things need it too (like maybe the GUC variables)?

[suprimex]

yes, I just added:
PGDLLEXPORT Datum pg_qualstats_reset (PG_FUNCTION_ARGS);
PGDLLEXPORT Datum pg_qualstats (PG_FUNCTION_ARGS);
PGDLLEXPORT Datum pg_qualstats_2_0 (PG_FUNCTION_ARGS);
PGDLLEXPORT Datum pg_qualstats_names (PG_FUNCTION_ARGS);
PGDLLEXPORT Datum pg_qualstats_names_2_0 (PG_FUNCTION_ARGS);
PGDLLEXPORT Datum pg_qualstats_example_query (PG_FUNCTION_ARGS);
PGDLLEXPORT Datum pg_qualstats_example_queries (PG_FUNCTION_ARGS);

But now, I assume, there is something wrong with the mechanism which detecting how the library was loaded.
Any call to the library gives me a message "ERROR: pg_qualstats must be loaded via shared_preload_libraries"
SHOW shared_preload_libraries; gives me "pg_qualstats, pg_stat_statements"

[rjuju]

Any call to the library gives me a message "ERROR: pg_qualstats must be loaded via shared_preload_libraries"
SHOW shared_preload_libraries; gives me "pg_qualstats, pg_stat_statements"

Building on windows shouldn'thave any impact on this I think. The error message is not really accurate anymore though, as pg_qualstats can also be used in a "stand alone" mode. Did you restart postgres after compiling and changing the shared_preload_libraries? Is it working it you do something like:

SET pg_qualstats.sample_rate = 1;
SET pg_qualstats.track_pg_catalog = 1;
SELECT count(*) from pg_class where relname = 'pg_qualstats';
SELECT * from pg_qualstats();

[suprimex]

Sure ;-) restarted.

SET pg_qualstats.sample_rate = 1;
Query OK, 0 rows affected
SET pg_qualstats.track_pg_catalog = 1;
Query OK, 0 rows affected
SELECT count(*) from pg_class where relname = 'pg_qualstats';
1
SELECT * from pg_qualstats();
ERROR: pg_qualstats must be loaded via shared_preload_libraries

from log:

2021-07-27 13:06:05.556 EEST [25760] ERROR: 55000: pg_qualstats must be loaded via shared_preload_libraries
2021-07-27 13:06:05.556 EEST [25760] LOCATION: pg_qualstats_common, C:\Users<my_user_name>\source\repos\pg_qualstats\pg_qualstats.c:1988
2021-07-27 13:06:05.556 EEST [25760] STATEMENT: SELECT * from pg_qualstats();

[rjuju]

I see. It seems that your first guess was correct: compilation works fine but somehow the loading code isn't executed. The _PG_init (and _PG_fini) functions are explicitly called after dynamically loading the shared library, so I'm assuming that a PGDLLEXPORT is also required there.

I pushed another commit to add all those missing annotations (331ee73). Does it solve all the remaining problems?

[rjuju]

Thanks to @suprimex help we could fix all issues with Windows environment.

I just merged the commits on the master branch, which should now compile cleanly and work as intended on Windows!

[rjuju]

Hi @gdonufrio

I spent some time setting up a build environment on Windows. Does this archivework for you?
pg_qualstats-2.0.3-pg12-x64.zip

It should be extracted in the root folder of your postgres 12 installation.

[gdonufrio]

Thank you very much , That is amazing Julien . I will try tomorrow when I will return from Hollidays Gabriele Il giorno dom 15 ago 2021 alle 13:47 Julien Rouhaud < ***@***.***> ha scritto:

…

Hi @gdonufrio <https://github.com/gdonufrio> I spent some time setting up a build environment on Windows. Does this archivework for you? pg_qualstats-2.0.3-pg12-x64.zip <https://github.com/powa-team/pg_qualstats/files/6988037/pg_qualstats-2.0.3-pg12-x64.zip> It should be extracted in the root folder of your postgres 12 installation. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#40 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AICK33F73BY7GLSP4WKTG3TT46SLRANCNFSM47N5QYCA> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&utm_campaign=notification-email> .

[rjuju]

Great, thanks a lot! I also have an installer if you prefer, but github doesn't let me add it here. I temporarily added it to the 2.0.3 release at https://github.com/powa-team/pg_qualstats/releases/download/2.0.3/pg_qualstats-2.0.3-pg12-x64.exe

Let me know one you downloaded it so I can remove it from the release, as I'm not entirely sure that it's working as expected yet.

[gdonufrio]

I owe you one ! it works perfectly and this is the utility I always dreamt for postgres Gabriele Il giorno mar 17 ago 2021 alle ore 15:18 Julien Rouhaud < ***@***.***> ha scritto:

…

Great, thanks a lot! I also have an installer if you prefer, but github doesn't let me add it here. I temporarily added it to the 2.0.3 release at https://github.com/powa-team/pg_qualstats/releases/download/2.0.3/pg_qualstats-2.0.3-pg12-x64.exe Let me know one you downloaded it so I can remove it from the release, as I'm not entirely sure that it's working as expected yet. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#40 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AICK33GI2OB63MGNMAULCMDT5JOSVANCNFSM47N5QYCA> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&utm_campaign=notification-email> .

[rjuju]

That's an excellent news! Thanks a lot for testing @gdonufrio!

I will upload the zip archives and installers for all pg versions then, and will keep doing so for all upcoming releases for all the extensions I maintain!

[cowwoc]

@rjuju Where can I find a Windows installer for 2.0.4?

[rjuju]

@cowwoc sorry, I want to automate this but never found the time to work on that. I just attached the zip and installers for the 2.0.4.