Install pgtt on windows server?

Question

Install pgtt on windows server?

rgonzalezVeolia opened this issue 10 months ago · 79 comments

Hello, I have tried to install pgtt extension in postgres 13 on windows server, but I only receive errors when trying to run make... will anyone have any documents to install on windows?

Answer 1 · 2024-03-05T04:45:18.000Z

I don't have enough knowledge and especially the desire to spend time on a port for Windows. If anyone can support this porting to Windows OS, a patch would be welcome. Thanks.

Answer 2 · 2024-04-16T02:32:43.000Z

I compiled the latest version of pgtt for pg13 windows on windows. Can you try the attached archive? I also have an installer but unfortunately github doesn't allow linking it here.

pgtt-3.2.0-pg13-x64.zip

Answer 3 · 2024-04-16T12:52:53.000Z

I compiled the latest version of pgtt for pg13 windows on windows. Can you try the attached archive? I also have an installer but unfortunately github doesn't allow linking it here.

pgtt-3.2.0-pg13-x64.zip

Thanks a lot!!! maybe can you send to me? rodrigo-andres.gonzalez@veolia.com

Answer 4 · 2024-04-16T13:54:49.000Z

I tried but my email provider also blocks .exe attachments. I uploaded it at https://transfert.free.fr/9dePGmw, the link should stay valid for the next 7 days.

Answer 5 · 2024-04-16T13:59:37.000Z

I tried but my email provider also blocks .exe attachments. I uploaded it at https://transfert.free.fr/9dePGmw, the link should stay valid for the next 7 days.

Thanks!!! Work fine on release 13... one question more, i tried to use in release 12, but this show me an incompatible version error. If possible use in release 12, what i must change?

Answer 6 · 2024-04-16T14:20:30.000Z

Thank for the confirmation, that's a very good news!

one question more, i tried to use in release 12, but this show me an incompatible version error. If possible use in release 12, what i must change?

That's expected. While pgtt source code with compatible with pg12 up to the current development version of postgres (pg17), the compiled version is only compatible with a single major version. That's why the installer hints you to install the extension in the postgres 13 directory (at least if it finds the correct registry keys).

I had to patch some stuff to be able to compile it and generate the installer, so I wanted to make sure that it was actually working before spending too much effort with this approach. Now that I know it works I will work with @darold to make a few adjustments, and also fix the windows compile system. Once done I will be able to generate an installer for all supported version and will give them to @darold so he can upload them on the release page.

Answer 7 · 2024-04-16T14:25:19.000Z

thanks for your help!!! i'll be waiting!!!

Answer 8 · 2024-04-21T02:42:15.000Z

I finally had time to work on this. I just sent a PR to ease the windows installer creation (the script also needed some changes) and using those I could generate installers for all supported postgre smajor versions (12 to 16). I uploaded them at https://transfert.free.fr/D8M2FtD

If you can test the pg12 version that would confirm that it's all good and that we can publish them on the release apge.

Answer 9 · 2024-04-22T14:02:14.000Z

Thanks!!!
pgtt installed OK. But, the tables exist while the session exist. This tables must be registered under pgtt_schema?

Answer 10 · 2024-04-22T14:26:12.000Z

example of one temp table:
LOAD 'pgtt';

CREATE /GLOBAL/ TEMPORARY TABLE ht_attributetypemodel (
oid varchar(32) NOT NULL
) ON COMMIT DELETE ROWS ;
ALTER TABLE ht_attributetypemodel ALTER COLUMN oid SET NOT NULL;

Under Linux work fine, and the table exist in schema pgtt_schema. In windows this is not working.

Answer 11 · 2024-04-23T02:30:41.000Z

I realized that the new version is not the "real" 3.2.0" but the latest commit, which includes some other changes related to the LOAD 'pgtt' thing.

Was the installer for version 13 actually working? If yes I can try to generate the installer for version 12 with the "real" pgtt version 3.2.0 and see if that fixes the problem. In that case we will be able to look at this issue before releasing a new pgtt version.

Answer 12 · 2024-04-23T13:17:25.000Z

I realized that the new version is not the "real" 3.2.0" but the latest commit, which includes some other changes related to the LOAD 'pgtt' thing.

Was the installer for version 13 actually working? If yes I can try to generate the installer for version 12 with the "real" pgtt version 3.2.0 and see if that fixes the problem. In that case we will be able to look at this issue before releasing a new pgtt version.

Not really. The installer work, i can create the extension and load, but the temporary tables is not working, i mean, this exist only in the session, then this tables desapear. This tables, must stay in the pgtt_schema, and is not stayed there.

Answer 13 · 2024-04-23T13:48:35.000Z

rgonzalezVeolia commented 8 months ago

Answer 14 · 2024-04-23T14:11:44.000Z

so the same behaviour with both the first installer and the new one?

just to be sure, did you check with CREATE GLOBAL TEMPORARY TABLE rather that with GLOBAL between comments?

Answer 15 · 2024-04-23T14:15:45.000Z

so the same behaviour with both the first installer and the new one?
Yes, same behavior.
just to be sure, did you check with CREATE GLOBAL TEMPORARY TABLE rather that with GLOBAL between comments?
Yes, same behavior, and show me a warning of obsolete.

Answer 16 · 2024-04-23T14:16:35.000Z

rgonzalezVeolia commented 8 months ago

Answer 17 · 2024-05-24T15:27:56.000Z

Hi, I need the latest version of the extension for Windows and compatible with PostgreSQL 16, is there any update here?

Answer 18 · 2024-05-24T15:45:56.000Z

Hi @dariocorti, have you tested latest development code? If so, do you have the same issue above?

Answer 19 · 2024-05-25T06:11:43.000Z

Hi @dariocorti, have you tested latest development code? If so, do you have the same issue above?

Hi @darold, I tried to compile the last version with Microsoft Visual studio to generate del .dll file, but when I execute the LOAD command in Postgres I recevie the errore that it's not compatible with 16 version. Is there a package already compiled as .dll that working in Windows?

Answer 20 · 2024-05-25T06:26:59.000Z

How about if you enable the extension by setting session_preload_libraries='pgtt'?

Answer 21 · 2024-05-25T06:37:52.000Z

You can find an installer and a zip file containing all the required files to install the latest commit of pgtt on pg16: https://transfert.free.fr/AOao3oi

As this is the latest commit, it includes the recent changes to allow session_preload_libraries='pgtt' rather than an explicit LOAD command.

If you can confirm that this is fully functionnal is would be a very good news as we will be able to provide those for all pgtt versions on all pg versions.

Answer 22 · 2024-05-26T08:20:36.000Z

@rgonzalezVeolia I have been unable to reproduce the issue on linux. Just to be sure, did you refresh the pgad;im object tree after execute the CREATE /* GLOBAL */ TEMPORARY TABLE command?

Or maybe the problem you hit was actually something like what I describe in #48 ? That would explain why it seems to fail but would actually be just a corner case bug.

Answer 23 · 2024-05-27T07:39:06.000Z

You can find an installer and a zip file containing all the required files to install the latest commit of pgtt on pg16: https://transfert.free.fr/AOao3oi

As this is the latest commit, it includes the recent changes to allow session_preload_libraries='pgtt' rather than an explicit LOAD command.

If you can confirm that this is fully functionnal is would be a very good news as we will be able to provide those for all pgtt versions on all pg versions.

Hi @rjuju, I confirm that works!! Thanks!

Answer 24 · 2024-05-27T13:03:22.000Z

@rgonzalezVeolia I have been unable to reproduce the issue on linux. Just to be sure, did you refresh the pgad;im object tree after execute the CREATE /* GLOBAL */ TEMPORARY TABLE command?

Or maybe the problem you hit was actually something like what I describe in #48 ? That would explain why it seems to fail but would actually be just a corner case bug.

Yes, refresh after execution ...

Answer 25 · 2024-05-27T13:43:15.000Z

Thanks for the confirmation. I really don't know what could be the problem in your case.

Answer 26 · 2024-05-27T13:50:03.000Z

rgonzalezVeolia commented 7 months ago

Answer 27 · 2024-05-27T13:51:25.000Z

You can see that i create temporary table. But, is not in the list from pgtt_schema.

Answer 28 · 2024-05-27T13:52:55.000Z

Can you do the same but in a psql console, just to remove the doubt on use through pgadmin?

Answer 29 · 2024-05-27T13:56:24.000Z

rgonzalezVeolia commented 7 months ago

Answer 30 · 2024-05-27T14:00:21.000Z

In your second connection you have to execute LOAD 'pgtt'; before doing the SELECT.

Answer 31 · 2024-05-27T14:00:43.000Z

You need to do the same LOAD 'pgtt'; in the new connection, and issue another query first to avoid the bug that was fiexed yesterday, so something like

LOAD 'pgtt';
SELECT 1;
SELECT * FROM hr_attributetypemodel;

Answer 32 · 2024-05-27T14:02:49.000Z

rgonzalezVeolia commented 7 months ago

Answer 33 · 2024-05-27T14:06:09.000Z

that's really strange. Can you show the result of SHOW search_path;? It should at least confirm that the extension seems to be setup as intended.

Answer 34 · 2024-05-27T14:07:12.000Z

rgonzalezVeolia commented 7 months ago

Answer 35 · 2024-05-27T14:07:58.000Z

ah, then it's NOT working at all.

Answer 36 · 2024-05-27T14:08:53.000Z

rgonzalezVeolia commented 7 months ago

Answer 37 · 2024-05-27T14:09:20.000Z

do you have other extension installed? what does SHOW shared_preload_libraries; output?

Answer 38 · 2024-05-27T14:10:01.000Z

rgonzalezVeolia commented 7 months ago

Answer 39 · 2024-05-27T14:11:39.000Z

How about SHOW pgtt.enabled; ?

Answer 40 · 2024-05-27T14:13:21.000Z

not recognized.

Answer 41 · 2024-05-27T14:15:09.000Z

ok, so for some reason LOAD 'pgtt'; doesn't seem to have any effect. I really don't know how it could happen as the LOAD should fail if there's no module associated. Can you check that trying to load an unexisting module fails? Like LOAD 'pgtt2';

Answer 42 · 2024-05-27T14:16:30.000Z

rgonzalezVeolia commented 7 months ago

Answer 43 · 2024-05-27T14:18:35.000Z

how can i deinstall pgtt?, to try install again, y then do i will test.

Answer 44 · 2024-05-27T14:20:53.000Z

How about the output if you do the following:

SET pggtt.enabled TO on;
LOAD 'pgtt';
SELECT * FROM pgtt_schema.pg_global_temp_tables;
SELECT * FROM hr_attributetypemodel;

Answer 45 · 2024-05-27T14:21:08.000Z

you should remove the pgtt.dll that's in one of the sub directories of that installation (in C:\Program Files or similar typically)

Answer 46 · 2024-05-27T14:22:10.000Z

rgonzalezVeolia commented 7 months ago

Answer 47 · 2024-05-27T14:23:09.000Z

it can't works, the table have not been created.

Answer 48 · 2024-05-27T14:25:58.000Z

Let's restart from the table creation

SET pggtt.enabled TO on;
LOAD 'pgtt';
CREATE GLOBAL TEMPORARY TABLE hr_attributetypemodel (id int);
SELECT * FROM pgtt_schema.pg_global_temp_tables;
\c - -
SET pggtt.enabled TO on;
LOAD 'pgtt';
SELECT * FROM pgtt_schema.pg_global_temp_tables;
SELECT * FROM hr_attributetypemodel;

Answer 49 · 2024-05-27T14:28:54.000Z

rgonzalezVeolia commented 7 months ago

Answer 50 · 2024-05-27T14:33:37.000Z

can you execute exactly the script I have post please just to be sure that it doesn't work in this simple use. Just copy paste the code in psql.

Answer 51 · 2024-05-27T14:35:26.000Z

rgonzalezVeolia commented 7 months ago

Answer 52 · 2024-05-27T14:36:02.000Z

SET pggtt.enabled TO on; (this line is ok?)

Answer 53 · 2024-05-27T14:37:34.000Z

Ya, it don't works at all. Can you set session_preload_libraries='pgtt' in your postgresql.conf then restart PG.
Once it is done execute again the script.

Answer 54 · 2024-05-27T14:41:09.000Z

And if needed here's an installer for the latest commit (f84fa01) for pg13 https://transfert.free.fr/c3PHn7Z

Answer 55 · 2024-05-27T14:42:04.000Z

rgonzalezVeolia commented 7 months ago

Answer 56 · 2024-05-27T14:44:35.000Z

rgonzalezVeolia commented 7 months ago

Answer 57 · 2024-05-27T14:49:15.000Z

It behave just like if the extension is not loaded. I guess that if you execute the following

SET pggtt.enabled TO on;
LOAD 'pgtt';
SELECT 1;
SHOW search_path;
CREATE GLOBAL TEMPORARY TABLE hr_attributetypemodel (id int);
\d hr_attributetypemodel

it will not change the search_path and create a real temporary table that of course disappears when we reconnect.

Answer 58 · 2024-05-27T14:50:09.000Z

if you can remove the extension and use latest installer send by rjuju and give it a try.

Answer 59 · 2024-05-27T14:51:22.000Z

SET pggtt.enabled TO on;
LOAD 'pgtt';
SELECT 1;
SHOW search_path;
CREATE GLOBAL TEMPORARY TABLE hr_attributetypemodel (id int);
\d hr_attributetypemodel

Answer 60 · 2024-05-27T14:56:38.000Z

I'm dry of solution here, next test could be to use PG16 like @dariocorti and see if you still have the issue.

Answer 61 · 2024-05-27T14:57:53.000Z

There is nothing in the log file that could be of any interest to find why the extension not loaded?

Answer 62 · 2024-05-27T15:02:57.000Z

SET pggtt.enabled TO on;
LOAD 'pgtt';
SELECT 1;
SHOW search_path;
CREATE GLOBAL TEMPORARY TABLE hr_attributetypemodel (id int);
\d hr_attributetypemodel

Answer 63 · 2024-05-27T15:03:25.000Z

if you can remove the extension and use latest installer send by rjuju and give it a try.

did it. Same problem.

Answer 64 · 2024-05-27T15:08:22.000Z

Well give a try with PG16 that's all I can say now.

Answer 65 · 2024-05-27T15:11:18.000Z

OK, Im installing on my machine, not same server with problem. I'll let you know how it work.

Answer 66 · 2024-05-27T16:05:39.000Z

rgonzalezVeolia commented 7 months ago

Answer 67 · 2024-05-27T16:06:02.000Z

Version 16.

Answer 68 · 2024-05-27T16:14:40.000Z

So this is a problem with PG13, thanks for the test.

Answer 69 · 2024-05-27T16:29:19.000Z

would you try repair to PG13?

Answer 70 · 2024-05-27T18:18:30.000Z

I will let @rjuju answer to this question but why are you using pgtt? If this is for a migration for Oracle I recommend you to use PG16. Why do you need PG13?

Answer 71 · 2024-05-27T18:19:42.000Z

PG13 is a requirement from custom. :|

Answer 72 · 2024-05-27T18:22:18.000Z

And why using Global Temporary Table? Also note that pgtt works perfectly on PG13 under Linux, does Windows is also a customer requirement?

Answer 73 · 2024-05-27T18:23:00.000Z

yes, is requirement also.

Answer 74 · 2024-05-28T02:40:50.000Z

I tried locally on a pg16 server I could manage to create, using the last zip file I shared above:

postgres=# select version();
                          version
------------------------------------------------------------
 PostgreSQL 16.1, compiled by Visual C++ build 1937, 64-bit
(1 row)

postgres=# show search_path;
   search_path
-----------------
 "$user", public
(1 row)

postgres=# load 'pgtt';
LOAD
postgres=# show search_path;
    search_path
--------------------
 public,pgtt_schema
(1 row)


postgres=# create global temporary table t_tbl(id integer);
WARNING:  GLOBAL is deprecated in temporary table creation
LINE 1: create global temporary table t_tbl(id integer);
               ^
CREATE TABLE
postgres=# \c
You are now connected to database "postgres" as user "postgres".
postgres=# load 'pgtt';
LOAD

postgres=# select * from t_tbl;
 id
----
(0 rows)

It works as expected. I really have no explanation why it doesn't work for you.

Answer 75 · 2024-05-28T04:02:43.000Z

sorry I missed that you confirmed that it's working for you for pg16.

I try to do the same on a local pg13 and I confirm that I have the same behaviour as you: nothing works. I added an explicit WARNING message at the beginning of the LOAD command and it's also not emitted, so it's clear that pg13 on windows for some reason is broken. since it works just fine on Linux, your best hope is to contact edb packagers and ask them what needs to be done to make it work, as it's the only moving piece here.

Answer 76 · 2024-05-31T02:31:11.000Z

I'm closing this issue, port to Windows is considered as done. Thanks again to @rjuju

Answer 77 · 2024-05-31T06:57:02.000Z

@rgonzalezVeolia good news, I finally found why the extension wasn't working on versions lower than 16. This is now fixed and I uploaded freshly built installers. You can download them from the version 4 release page at https://github.com/darold/pgtt/releases/tag/v4.0.

A confirmation that this is also working for you would be great.

Answer 78 · 2024-05-31T13:36:24.000Z

Great!!!

Work!!!

Answer 79 · 2024-05-31T14:18:07.000Z

Excellent news, thanks a lot for checking!

I can now finally close the issue as resolved :)