darold / pgtt Goto Github PK

Tested and validated that this works. What a great extension! In the past I used a snippet of SQL code that I found to install to simulate a GTT.

So this gets me to thinking about ora2pg... Right now ora2pg does not support extraction of GTTs from oracle since PG doesn't support them. But with this extension and if a boolean is turned on in parms to ora2pg, enable_GTT, the GTTs would be downloaded and assuming the target PG database has gtt extension created and loaded, I wonder if we could add the feature to migrate GTTs?

I haven't thought it all through yet, but whatcha think?

when i used make && make install,
then su - postgres, in the psql mode,
execute the command "create extension pgtt;"
the show the error like this :
schema pgtt_schema is not a member of extension "pgtt"

There is 1 issue I found:
with this extension loaded you can no longer drop tables without providing schema names:

create table test1(f1 integer);
drop table test1;
ERROR: table "test1" does not exist

drop table public.test1;
DROP TABLE
Query returned successfully in 317 msec.

SHOW search_path;
"$user", public, pgtt_schema

Hi,

Is the way to automate pgtt loading after session establishing on a server side, without calling LOAD '$libdir/plugins/pgtt' by a client? I know that code of pgtt prevents using session_preload_libraries and shared_preload_libraries.

Hi,
i'm trying to compile the pgtt.c file with visual studio, but ends with unresolved external symbol for the function calls of regcomp/regexec/regfree.

I try to write a patch for these functions using pg_regcomp/pg_regexec/pg_regfree, but the parameters are not matching.

   static int regcomp(regex_t* re, char const  *string, int flags)
  {
     auto len = strlen(string); //right??
         auto collation = LC_COLLATE;//???
      return pg_regcomp(re, string, **len**, flags, **colation**);
  }
 static int regexec(const regex_t* restrict preg, const char* restrict string,
      size_t nmatch, regmatch_t pmatch[restrict], int eflags)
  {
      auto len = strlen(string); // right?
      auto search_start = ??;
          rm_detail_t * details = ????
      return pg_regexec(preg, string, len, **search_start, details ,**nmatch, pmatch, eflags);
  }

Do you have any idea, what values for collation in regcomp and search_start, details in regexec?

Regards,
Lanlan

When PGTT created, it make "template" for table in pgtt_schema.
But owner for pgtt_schema's tables is postgres, regardless which user made create global temporary table DDL.

Thus, if I login as another user (not postgres), I can create table, but can't add column or index for it.

Hello,
The following command has a syntax error:

CREATE /GLOBAL/ TEMPORARY TABLE test_gtt_table (
LIKE other_table LIKE
INCLUDING DEFAULTS
INCLUDING CONSTRAINTS
INCLUDING INDEXES
) ON COMMIT { PRESERVE | DELETE } ROWS;

This command worked when I remove second 'LIKE'.

FYI.

Hi,

pgtt 2.5 fails to build against v14. Log is below. Can you please take a look? v14 is around the corner.

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -O2 -flto=auto -ffat-lto-objects -fexceptions -g -grecord-gcc-switches -pipe -Wall -Werror=format-security -Wp,-D_FORTIFY_SOURCE=2 -Wp,-D_GLIBCXX_ASSERTIONS -specs=/usr/lib/rpm/redhat/redhat-hardened-cc1 -fstack-protector-strong -specs=/usr/lib/rpm/redhat/redhat-annobin-cc1 -m64 -mtune=generic -fasynchronous-unwind-tables -fstack-clash-protection -fcf-protection -fPIC -I/usr/pgsql-14/include -Wno-uninitialized -I. -I./ -I/usr/pgsql-14/include/server -I/usr/pgsql-14/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -c -o pgtt.o pgtt.c
/usr/lib64/ccache/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2 -I/usr/pgsql-14/include -Wno-uninitialized -I. -I./ -I/usr/pgsql-14/include/server -I/usr/pgsql-14/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -flto=thin -emit-llvm -c -o pgtt.bc pgtt.c
pgtt.c: In function '_PG_init':
pgtt.c:304:29: warning: assignment to 'ProcessUtility_hook_type' {aka 'void (*)(PlannedStmt *, const char *, _Bool, ProcessUtilityContext, struct ParamListInfoData *, QueryEnvironment *, struct _DestReceiver *, QueryCompletion )'} from incompatible pointer type 'void ()(PlannedStmt *, const char *, ProcessUtilityContext, struct ParamListInfoData *, QueryEnvironment *, DestReceiver *, QueryCompletion )' {aka 'void ()(PlannedStmt *, const char *, ProcessUtilityContext, struct ParamListInfoData *, QueryEnvironment *, struct _DestReceiver *, QueryCompletion *)'} [-Wincompatible-pointer-types]
304 | ProcessUtility_hook = gtt_ProcessUtility;
| ^
pgtt.c: In function 'gtt_ProcessUtility':
pgtt.c:98:62: error: incompatible type for argument 4 of 'prev_ProcessUtility'
98 | #define GTT_PROCESSUTILITY_ARGS pstmt, queryString, context, params, queryEnv, dest, qc
| ^~~~~~
| |
| ParamListInfo {aka struct ParamListInfoData *}
pgtt.c:373:45: note: in expansion of macro 'GTT_PROCESSUTILITY_ARGS'
373 | prev_ProcessUtility(GTT_PROCESSUTILITY_ARGS);
| ^~~~~~~~~~~~~~~~~~~~~~~
pgtt.c:98:62: note: expected 'ProcessUtilityContext' but argument is of type 'ParamListInfo' {aka 'struct ParamListInfoData *'}
98 | #define GTT_PROCESSUTILITY_ARGS pstmt, queryString, context, params, queryEnv, dest, qc
| ^~~~~~
pgtt.c:373:45: note: in expansion of macro 'GTT_PROCESSUTILITY_ARGS'
373 | prev_ProcessUtility(GTT_PROCESSUTILITY_ARGS);
| ^~~~~~~~~~~~~~~~~~~~~~~
pgtt.c:98:70: warning: passing argument 5 of 'prev_ProcessUtility' from incompatible pointer type [-Wincompatible-pointer-types]
98 | #define GTT_PROCESSUTILITY_ARGS pstmt, queryString, context, params, queryEnv, dest, qc
| ^~~~~~~~
| |
| QueryEnvironment *
pgtt.c:373:45: note: in expansion of macro 'GTT_PROCESSUTILITY_ARGS'
373 | prev_ProcessUtility(GTT_PROCESSUTILITY_ARGS);
| ^~~~~~~~~~~~~~~~~~~~~~~
pgtt.c:98:70: note: expected 'ParamListInfo' {aka 'struct ParamListInfoData *'} but argument is of type 'QueryEnvironment *'
98 | #define GTT_PROCESSUTILITY_ARGS pstmt, queryString, context, params, queryEnv, dest, qc
| ^~~~~~~~
pgtt.c:373:45: note: in expansion of macro 'GTT_PROCESSUTILITY_ARGS'
373 | prev_ProcessUtility(GTT_PROCESSUTILITY_ARGS);
| ^~~~~~~~~~~~~~~~~~~~~~~
pgtt.c:98:80: warning: passing argument 6 of 'prev_ProcessUtility' from incompatible pointer type [-Wincompatible-pointer-types]
98 | #define GTT_PROCESSUTILITY_ARGS pstmt, queryString, context, params, queryEnv, dest, qc
| ^~~~
| |
| DestReceiver * {aka struct _DestReceiver *}
pgtt.c:373:45: note: in expansion of macro 'GTT_PROCESSUTILITY_ARGS'
373 | prev_ProcessUtility(GTT_PROCESSUTILITY_ARGS);
| ^~~~~~~~~~~~~~~~~~~~~~~
pgtt.c:98:80: note: expected 'QueryEnvironment *' but argument is of type 'DestReceiver *' {aka 'struct _DestReceiver *'}
98 | #define GTT_PROCESSUTILITY_ARGS pstmt, queryString, context, params, queryEnv, dest, qc
| ^~~~
pgtt.c:373:45: note: in expansion of macro 'GTT_PROCESSUTILITY_ARGS'
373 | prev_ProcessUtility(GTT_PROCESSUTILITY_ARGS);
| ^~~~~~~~~~~~~~~~~~~~~~~
pgtt.c:98:86: warning: passing argument 7 of 'prev_ProcessUtility' from incompatible pointer type [-Wincompatible-pointer-types]
98 | #define GTT_PROCESSUTILITY_ARGS pstmt, queryString, context, params, queryEnv, dest, qc
| ^~
| |
| QueryCompletion *
pgtt.c:373:45: note: in expansion of macro 'GTT_PROCESSUTILITY_ARGS'
373 | prev_ProcessUtility(GTT_PROCESSUTILITY_ARGS);
| ^~~~~~~~~~~~~~~~~~~~~~~
pgtt.c:98:86: note: expected 'DestReceiver *' {aka 'struct _DestReceiver *'} but argument is of type 'QueryCompletion *'
98 | #define GTT_PROCESSUTILITY_ARGS pstmt, queryString, context, params, queryEnv, dest, qc
| ^~
pgtt.c:373:45: note: in expansion of macro 'GTT_PROCESSUTILITY_ARGS'
373 | prev_ProcessUtility(GTT_PROCESSUTILITY_ARGS);
| ^~~~~~~~~~~~~~~~~~~~~~~
pgtt.c:373:25: error: too few arguments to function 'prev_ProcessUtility'
373 | prev_ProcessUtility(GTT_PROCESSUTILITY_ARGS);
| ^~~~~~~~~~~~~~~~~~~
pgtt.c:98:62: error: incompatible type for argument 4 of 'standard_ProcessUtility'
98 | #define GTT_PROCESSUTILITY_ARGS pstmt, queryString, context, params, queryEnv, dest, qc
| ^~~~~~
| |
| ParamListInfo {aka struct ParamListInfoData *}
pgtt.c:375:49: note: in expansion of macro 'GTT_PROCESSUTILITY_ARGS'
375 | standard_ProcessUtility(GTT_PROCESSUTILITY_ARGS);
| ^~~~~~~~~~~~~~~~~~~~~~~
In file included from pgtt.c:51:
/usr/pgsql-14/include/server/tcop/utility.h:87:95: note: expected 'ProcessUtilityContext' but argument is of type 'ParamListInfo' {aka 'struct ParamListInfoData *'}
87 | ProcessUtilityContext context, ParamListInfo params,
| ~~~~~~~~~~~~~~~~~~~~~~^~~~~~~
pgtt.c:98:70: warning: passing argument 5 of 'standard_ProcessUtility' from incompatible pointer type [-Wincompatible-pointer-types]
98 | #define GTT_PROCESSUTILITY_ARGS pstmt, queryString, context, params, queryEnv, dest, qc
| ^~~~~~~~
| |
| QueryEnvironment *
pgtt.c:375:49: note: in expansion of macro 'GTT_PROCESSUTILITY_ARGS'
375 | standard_ProcessUtility(GTT_PROCESSUTILITY_ARGS);
| ^~~~~~~~~~~~~~~~~~~~~~~
In file included from pgtt.c:51:
/usr/pgsql-14/include/server/tcop/utility.h:87:118: note: expected 'ParamListInfo' {aka 'struct ParamListInfoData *'} but argument is of type 'QueryEnvironment *'
87 | ProcessUtilityContext context, ParamListInfo params,
| ~~~~~~~~~~~~~~^~~~~~
pgtt.c:98:80: warning: passing argument 6 of 'standard_ProcessUtility' from incompatible pointer type [-Wincompatible-pointer-types]
98 | #define GTT_PROCESSUTILITY_ARGS pstmt, queryString, context, params, queryEnv, dest, qc
| ^~~~
| |
| DestReceiver * {aka struct _DestReceiver *}
pgtt.c:375:49: note: in expansion of macro 'GTT_PROCESSUTILITY_ARGS'
375 | standard_ProcessUtility(GTT_PROCESSUTILITY_ARGS);
| ^~~~~~~~~~~~~~~~~~~~~~~
In file included from pgtt.c:51:
/usr/pgsql-14/include/server/tcop/utility.h:88:91: note: expected 'QueryEnvironment *' but argument is of type 'DestReceiver *' {aka 'struct _DestReceiver *'}
88 | QueryEnvironment *queryEnv,
| ~~~~~~~~~~~~~~~~~~^~~~~~~~
pgtt.c:98:86: warning: passing argument 7 of 'standard_ProcessUtility' from incompatible pointer type [-Wincompatible-pointer-types]
98 | #define GTT_PROCESSUTILITY_ARGS pstmt, queryString, context, params, queryEnv, dest, qc
| ^~
| |
| QueryCompletion *
pgtt.c:375:49: note: in expansion of macro 'GTT_PROCESSUTILITY_ARGS'
375 | standard_ProcessUtility(GTT_PROCESSUTILITY_ARGS);
| ^~~~~~~~~~~~~~~~~~~~~~~
In file included from pgtt.c:51:
/usr/pgsql-14/include/server/tcop/utility.h:89:87: note: expected 'DestReceiver *' {aka 'struct _DestReceiver *'} but argument is of type 'QueryCompletion *'
89 | DestReceiver *dest, QueryCompletion *qc);
| ~~~~~~~~~~~~~~^~~~
pgtt.c:375:25: error: too few arguments to function 'standard_ProcessUtility'
375 | standard_ProcessUtility(GTT_PROCESSUTILITY_ARGS);
| ^~~~~~~~~~~~~~~~~~~~~~~
In file included from pgtt.c:51:
/usr/pgsql-14/include/server/tcop/utility.h:85:13: note: declared here
85 | extern void standard_ProcessUtility(PlannedStmt *pstmt, const char *queryString,
| ^~~~~~~~~~~~~~~~~~~~~~~
pgtt.c: In function 'create_temporary_table_internal':
pgtt.c:1569:66: error: incompatible type for argument 4 of 'ProcessUtility'
1569 | NULL, NULL,
| ^~~~
| |
| void *
In file included from pgtt.c:51:
/usr/pgsql-14/include/server/tcop/utility.h:82:74: note: expected 'ProcessUtilityContext' but argument is of type 'void *'
82 | ProcessUtilityContext context, ParamListInfo params,
| ~~~~~~~~~~~~~~~~~~~~~~^~~~~~~
pgtt.c:1570:66: warning: passing argument 6 of 'ProcessUtility' from incompatible pointer type [-Wincompatible-pointer-types]
1570 | None_Receiver,
| ^~~~~~~~~~~~~
| |
| DestReceiver * {aka struct _DestReceiver *}
In file included from pgtt.c:51:
/usr/pgsql-14/include/server/tcop/utility.h:83:70: note: expected 'QueryEnvironment *' but argument is of type 'DestReceiver *' {aka 'struct _DestReceiver *'}
83 | QueryEnvironment *queryEnv,
| ~~~~~~~~~~~~~~~~~~^~~~~~~~
pgtt.c:1566:25: error: too few arguments to function 'ProcessUtility'
1566 | ProcessUtility(stmt,
| ^~~~~~~~~~~~~~
In file included from pgtt.c:51:
/usr/pgsql-14/include/server/tcop/utility.h:80:13: note: declared here
80 | extern void ProcessUtility(PlannedStmt *pstmt, const char queryString,
| ^~~~~~~~~~~~~~
make[1]: *** [: pgtt.o] Error 1
make[1]: *** Waiting for unfinished jobs....
pgtt.c:304:22: warning: incompatible function pointer types assigning to 'ProcessUtility_hook_type' (aka 'void ()(struct PlannedStmt *, const char *, bool, ProcessUtilityContext, struct ParamListInfoData *, struct QueryEnvironment *, struct _DestReceiver *, struct QueryCompletion *)') from 'void (PlannedStmt *, const char *, ProcessUtilityContext, ParamListInfo, QueryEnvironment *, DestReceiver *, QueryCompletion *)' (aka 'void (struct PlannedStmt *, const char *, ProcessUtilityContext, struct ParamListInfoData *, struct QueryEnvironment *, struct _DestReceiver *, struct QueryCompletion *)') [-Wincompatible-function-pointer-types]
ProcessUtility_hook = gtt_ProcessUtility;
^ ~~~~~~~~~~~~~~~~~~
pgtt.c:373:47: error: too few arguments to function call, expected 8, have 7
prev_ProcessUtility(GTT_PROCESSUTILITY_ARGS);
~~~~~~~~~~~~~~~~~~~ ^
pgtt.c:375:51: error: too few arguments to function call, expected 8, have 7
standard_ProcessUtility(GTT_PROCESSUTILITY_ARGS);
~~~~~~~~~~~~~~~~~~~~~~~ ^
/usr/pgsql-14/include/server/tcop/utility.h:85:13: note: 'standard_ProcessUtility' declared here
extern void standard_ProcessUtility(PlannedStmt *pstmt, const char *queryString,
^
pgtt.c:1571:14: error: too few arguments to function call, expected 8, have 7
NULL);
^
/usr/pgsql-14/include/server/tcop/utility.h:80:13: note: 'ProcessUtility' declared here
extern void ProcessUtility(PlannedStmt *pstmt, const char *queryString,
^
1 warning and 3 errors generated.
make[1]: *** [/usr/pgsql-14/lib/pgxs/src/makefiles/../../src/Makefile.global:1070: pgtt.bc] Error 1
make[1]: Leaving directory '/home/devrim/Documents/Devrim/Projects/repo/pgrpms/rpm/redhat/master/non-common/pgtt/main/pgtt-2.5'
error: Bad exit status from /var/tmp/rpm-tmp.RvIuYJ (%build)

version: PG12
configure --prefix=/home/hanson/postgres --enable-debug --enable-cassert --enable-depend CFLAGS=-O0

error :
FailedAssertion

Background: I'm teaching a course and I'm using helper scripts to allow students to write their queries in a file and to execute them in a docker container with image postgres:latest (16.0-bookworm at the time of writing).

I load PGTT via psqlrc.

I noticed two strange things:

• when I execute the following using docker exec <container-name> psql -U postgres -d <db-name> -c "$1", I get an error "ERROR: ON COMMIT can only be used on temporary tables"

  create /*global*/ temporary table glotemp_ex_1 (id varchar) on commit preserve rows;
  insert into glotemp_ex_1 values ('A');
  select * from glotemp_ex_1;

• when I surround the creation with a transaction, it works, but when I open another session, the table does not exist!

  begin; create /*global*/ temporary table glotemp_ex_1 (id varchar) on commit preserve rows; commit;
  insert into glotemp_ex_1 values ('A');
  select * from glotemp_ex_1;

It gets worse:

• when I set AUTOCOMMIT to "off" and do the same one by one, it works
• if I rollback after the creation of the GTT and then try to delete it, I still can't if it's in use, BUT
• if I exit and create a new session after rolling back, the table does not exist.

I'm a bit at a loss...

Hi!
Nice job, but I've got ERROR: duplicate key value violates unique constraint "pg_global_temp_tables_nspname_relname_key"
PostgreSQL 12, Fedora 33, installed from standard packages.

Steps to reproduce:

Connect as user mvno_db2:

mvno_db2=>  LOAD '$libdir/plugins/pgtt';
LOAD
mvno_db2=> create global temporary table t1 ( a integer );
WARNING:  GLOBAL is deprecated in temporary table creation
LINE 1: create global temporary table t1 ( a integer );
               ^
CREATE TABLE

Make second connection as user postgres, connect to the same database and drop table:

=# LOAD '$libdir/plugins/pgtt';
LOAD

mvno_db2=# drop table t1;
DROP TABLE

Switch back to session of mvno_db2 and try recreate GTT:

mvno_db2=> create global temporary table t1 ( a integer );
WARNING:  GLOBAL is deprecated in temporary table creation
LINE 1: create global temporary table t1 ( a integer );
               ^
ERROR:  duplicate key value violates unique constraint "pg_global_temp_tables_nspname_relname_key"
DETAIL:  Key (nspname, relname)=(pgtt_schema, t1) already exists.
CONTEXT:  SQL statement "INSERT INTO pgtt_schema.pg_global_temp_tables VALUES (630344, 'pgtt_schema', 't1', 't', ' a integer ')"

Hi,

When I run regression tests under PG15/14, it failed with following errors:

$ make installcheck
/home/japin/Codes/postgres/build/pg/lib/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --bindir='/home/japin/Codes/postgres/build/pg/bin'    --inputdir=test --dbname=contrib_regression 00_init 01_oncommitdelete 02_oncommitpreserve 03_createontruncate 04_rename 05_useindex 06_createas 07_createlike 08_plplgsql 09_transaction 10_foreignkey 11_after_error 12_droptable
(using postmaster on Unix socket, default port)
============== dropping database "contrib_regression" ==============
DROP DATABASE
============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
============== running regression test queries        ==============
test 00_init                      ... ok           77 ms
test 01_oncommitdelete            ... FAILED (test process exited with exit code 2)      208 ms
test 02_oncommitpreserve          ... FAILED (test process exited with exit code 2)       24 ms
test 03_createontruncate          ... FAILED (test process exited with exit code 2)        6 ms
test 04_rename                    ... FAILED (test process exited with exit code 2)        5 ms
test 05_useindex                  ... FAILED (test process exited with exit code 2)        4 ms
test 06_createas                  ... FAILED (test process exited with exit code 2)        4 ms
test 07_createlike                ... FAILED (test process exited with exit code 2)        4 ms
test 08_plplgsql                  ... FAILED (test process exited with exit code 2)        4 ms
test 09_transaction               ... FAILED (test process exited with exit code 2)        8 ms
test 10_foreignkey                ... FAILED (test process exited with exit code 2)        8 ms
test 11_after_error               ... FAILED (test process exited with exit code 2)        5 ms
test 12_droptable                 ... FAILED (test process exited with exit code 2)        5 ms

========================
 12 of 13 tests failed.
========================

The differences that caused some tests to fail can be viewed in the
file "/home/japin/Codes/pg-extensions/pgtt/regression.diffs".  A copy of the test summary that you see
above is saved in the file "/home/japin/Codes/pg-extensions/pgtt/regression.out".

Here is complete errors.

My Dockerfile:

FROM postgres:15beta1

RUN apt-get -qq update -qq && \
    apt-get -y -qq upgrade && \
    apt-get -y -qq install git && \
    apt-get -y -qq install vim bash-completion wget

RUN apt-get install -y libpq-dev make gcc
RUN apt-get install -y postgresql-server-dev-15 libicu-dev
RUN apt-get install -y libssl-dev
RUN apt-get install -y libcurl4-openssl-dev


RUN cd /usr/src  && \
    git clone https://github.com/darold/pgtt
WORKDIR /usr/src/pgtt
RUN make
RUN make install
RUN export libdir=$(pg_config --pkglibdir) && \
    mkdir $libdir/plugins/ && \
    cd $libdir/plugins/ && \
    ln -s ../pgtt.so

Errors:

...
Step 9/15 : RUN make
 ---> Running in 834b8d46c147
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer -fPIC -I/usr/include/postgresql -Wno-uninitialized -I. -I./ -I/usr/include/postgresql/15/server -I/usr/include/postgresql/internal  -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2   -c -o pgtt.o pgtt.c
pgtt.c: In function ‘gtt_check_command’:
pgtt.c:462:18: error: ‘union ValUnion’ has no member named ‘type’
  462 |       newcon->val.type = T_String;
      |                  ^
pgtt.c:463:19: error: ‘union ValUnion’ has no member named ‘val’; did you mean ‘ival’?
  463 |       newcon->val.val.str = pstrdup(str);
      |                   ^~~
      |                   ival
pgtt.c:700:5: error: unknown type name ‘Value’
  700 |     Value *relationSchemaNameValue = NULL;
      |     ^~~~~
pgtt.c:701:5: error: unknown type name ‘Value’
  701 |     Value *relationNameValue = NULL;
      |     ^~~~~
pgtt.c:738:6: error: unknown type name ‘Value’
  738 |      Value *schemaNameValue = makeString(pgtt_namespace_name);
      |      ^~~~~
...

Thank you!

Hi!

I try compile PGTT for PostgreSQL 14 beta 1, but got this errors:

pgtt.c:287:26: warning: assignment to ‘post_parse_analyze_hook_type’ {aka ‘void (*)(struct ParseState *, struct Query *, struct JumbleState *)’} from incompatible pointer type ‘void (*)(ParseState *, Query *)’ {aka ‘void (*)(struct ParseState *, struct Query *)’} [-Wincompatible-pointer-types]
  post_parse_analyze_hook = gtt_post_parse_analyze;
                          ^
pgtt.c: In function ‘gtt_check_command’:
pgtt.c:461:12: error: ‘CreateTableAsStmt’ {aka ‘struct CreateTableAsStmt’} has no member named ‘relkind’
    if (stmt->relkind != OBJECT_TABLE)
            ^~
pgtt.c:846:14: error: ‘AlterTableStmt’ {aka ‘struct AlterTableStmt’} has no member named ‘relkind’; did you mean ‘relation’?
    if (stmt->relkind != OBJECT_TABLE)
              ^~~~~~~
              relation
pgtt.c: In function ‘gtt_post_parse_analyze’:
pgtt.c:1660:17: error: too few arguments to function ‘prev_post_parse_analyze_hook’
                 prev_post_parse_analyze_hook(pstate, query);
                 ^~~~~~~~~~~~~~~~~~~~~~~~~~~~
make: *** [<builtin>: pgtt.o] Error 1

Before compiling I turn on 14 version in Makefile version checking:

PGVEROK = $(shell $(PG_CONFIG) --version | egrep " (9.[456]|1[01234])" > /dev/null && echo yes || echo no)

Hi,

Is any way to make pgtV for not deleting the view which uses pgt-table?

Hi,

The latest version fails to build against v10 and 9.6 Below is the error. Can you please take a look? Thanks!

Cheers, Devrim

• /usr/bin/make USE_PGXS=1 -j4
  make[1]: Entering directory '/var/lib/pgsql/rpm10/BUILD/pgtt-2.4'
  gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -g -pipe -Wall -Werror=format-security -Wp,-D_FORTIFY_SOURCE=2 -Wp,-D_GLIBCXX_ASSERTIONS -fexceptions -fstack-protector-strong -grecord-gcc-switches -specs=/usr/lib/rpm/redhat/redhat-hardened-cc1 -specs=/usr/lib/rpm/redhat/redhat-annobin-cc1 -m64 -mtune=generic -fasynchronous-unwind-tables -fstack-clash-protection -fcf-protection -fPIC -I/usr/pgsql-10/include -Wno-uninitialized -I. -I./ -I/usr/pgsql-10/include/server -I/usr/pgsql-10/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -c -o pgtt.o pgtt.c
  pgtt.c: In function 'gtt_check_command':
  pgtt.c:901:10: warning: passing argument 4 of 'RangeVarGetRelidExtended' makes integer from pointer without a cast [-Wint-conversion]
  RangeVarCallbackOwnsRelation, NULL);
  ^~~~~~~~~~~~~~~~~~~~~~~~~~~~
  In file included from pgtt.c:24:
  /usr/pgsql-10/include/server/catalog/namespace.h:57:49: note: expected 'bool' {aka 'char'} but argument is of type 'void (*)(const RangeVar *, Oid, Oid, void )' {aka 'void ()(const struct RangeVar *, unsigned int, unsigned int, void *)'}
  LOCKMODE lockmode, bool missing_ok, bool nowait,
  ~~~~~^~~~~~
  pgtt.c:900:12: error: too few arguments to function 'RangeVarGetRelidExtended'
  relid = RangeVarGetRelidExtended(stmt->relation, ShareLock, 0,
  ^~~~~~~~~~~~~~~~~~~~~~~~
  In file included from pgtt.c:24:
  /usr/pgsql-10/include/server/catalog/namespace.h:56:12: note: declared here
  extern Oid RangeVarGetRelidExtended(const RangeVar *relation,
  ^~~~~~~~~~~~~~~~~~~~~~~~
  make[1]: *** [: pgtt.o] Error 1

I notice that the code in the main branch appears to support session_preload_libraries but it is unclear whether or not the the code in the main branch is ready to deploy as the pgtt.control file still shows version 3.2.

Any idea when the release will occur that supports session_preload_libraries?

Hi there. I am on WSL2 Ubuntu 20.04 with PG 12.6. And after installation, I found that the test named '08_plplgsql' failed.

Test output:

The diff result:

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?

My PG version is 12.3, but the version is lower than 9.4 during installation. How can I solve this problem

When I execute
COMMENT ON TABLE G_T_TABLE IS 'Temp table for...';
or
COMMENT ON COLUMN G_T_TABLE.COL1 IS 'Column description';
I'v got the error:
a temporary table has been created and is active, can not add a comment on the GTT table in this session.

On big-endian s390x Debian bookworm+bullseye and Ubuntu noble I'm seeing regression diffs:

14:01:02 **** regression.diffs ****
14:01:02 diff -U3 /<<PKGBUILDDIR>>/test/expected/01_oncommitdelete.out /<<PKGBUILDDIR>>/results/01_oncommitdelete.out
14:01:02 --- /<<PKGBUILDDIR>>/test/expected/01_oncommitdelete.out	2024-05-31 02:58:35.000000000 +0000
14:01:02 +++ /<<PKGBUILDDIR>>/results/01_oncommitdelete.out	2024-06-04 12:01:00.734117902 +0000
14:01:02 @@ -108,7 +108,8 @@
14:01:02  
14:01:02  -- The "template" unlogged table should not exists anymore
14:01:02  SELECT n.nspname, c.relname FROM pg_class c JOIN pg_namespace n ON (c.relnamespace=n.oid) WHERE relname = 't_glob_temptable1';
14:01:02 - nspname | relname 
14:01:02 ----------+---------
14:01:02 -(0 rows)
14:01:02 +  nspname  |      relname      
14:01:02 +-----------+-------------------
14:01:02 + pg_temp_3 | t_glob_temptable1
14:01:02 +(1 row)
14:01:02  
14:01:02 diff -U3 /<<PKGBUILDDIR>>/test/expected/11_after_error.out /<<PKGBUILDDIR>>/results/11_after_error.out
14:01:02 --- /<<PKGBUILDDIR>>/test/expected/11_after_error.out	2024-05-31 02:58:35.000000000 +0000
14:01:02 +++ /<<PKGBUILDDIR>>/results/11_after_error.out	2024-06-04 12:01:02.494027736 +0000
14:01:02 @@ -89,7 +89,8 @@
14:01:02  
14:01:02  -- The "template" unlogged table should not exists anymore
14:01:02  SELECT n.nspname, c.relname FROM pg_class c JOIN pg_namespace n ON (c.relnamespace=n.oid) WHERE relname = 't_glob_temptable1';
14:01:02 - nspname | relname 
14:01:02 ----------+---------
14:01:02 -(0 rows)
14:01:02 +  nspname  |      relname      
14:01:02 +-----------+-------------------
14:01:02 + pg_temp_3 | t_glob_temptable1
14:01:02 +(1 row)
14:01:02  

It seems to be randomly failing independently from the PG version, I've seen failures on PG16 and 15.

Other architectures seem unaffected.

Full build logs: https://pgdgbuild.dus.dg-i.net/job/pgtt-binaries/3/

After the creation of a global temporary table I can select from it and get an empty list. If I repeat that select in a different session it will say the table does not exist. If I try to re-create the table in the second session it succeeds.

As far as I know the main feature difference with the oracle global temporary table is that you only have to create it once and it can be reused in subsequential sessions.

In multi parallel process env, many locks are generated. I start 50 threads simultaneously, one connection per thread, performing DML operations on the same GTT table.
When I start java thread pool process, Some thread will be hang, as the same time , I can not access to GTT table.

Is this extension compatible with pg streaming replication?

After download and untar pgtt-2.6 to a directory, I moved into the untar directory then run make command line, the following error occurred.
`~/pgtt-2.6$ make

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer -fPIC -I/usr/include/postgresql -Wno-uninitialized -I. -I./ -I/usr/include/postgresql/14/server -I/usr/include/postgresql/internal -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2 -c -o pgtt.o pgtt.c
pgtt.c:12:10: fatal error: postgres.h: No such file or directory
12 | #include "postgres.h"
| ^~~~~~~~~~~~
compilation terminated.
make: *** [: pgtt.o] Error 1`

Can anyone help and instruct me how to install pgtt on postgres 14 in Ubuntu environment? Thanks in advance.

Doesn't compile since PG 12.

After some actions (don't know which) I start get errors on regular tables:

mvno_db2=> LOAD '$libdir/plugins/pgtt';
LOAD
mvno_db2=> CREATE TABLE test2(
mvno_db2(>   t1_id integer,
mvno_db2(>   FOREIGN KEY (t1_id)
mvno_db2(>   REFERENCES t1(id)
mvno_db2(>   ) ;
ERROR:  attempt to create referential integrity constraint on global temporary table

It occured on all tables with inlined Foreign Key constraint in the table scripts.
I have no GTT with this name in database.

Hi,

I tried to import mulitple global temporary tables in one sql call but I continuesly got an error.

I found the issue:

In line 667 of the pgtt.c file the end index is pushed so far as the file contains ")".
Therefore the gtt.code attribute is full with not just the columns of the gtt but also includes all other following statements.

I would appreciate it if this could be fixed

Kind regards

Finn

Hello, this use triggers an assertion.

LOAD '$libdir/plugins/pgtt';
CREATE /*GLOBAL*/ TEMPORARY TABLE test_tt (id int, lbl text);
explain verbose SELECT * FROM pgtt_schema.test_tt;  -- or SELECT * FROM pgtt_schema.test_tt;
explain verbose SELECT * FROM pgtt_schema.test_tt;  -- error

TRAP: FailedAssertion("lockmode != NoLock || IsBootstrapProcessingMode() || CheckRelationLockedByMe(r, AccessShareLock, true)", File: "relation.c", Line: 70, PID: 32882)
postgres: postgres postgres [local] EXPLAIN(ExceptionalCondition+0xb9)[0xb0ec6a]
postgres: postgres postgres [local] EXPLAIN(relation_open+0xe3)[0x4a0f34]
postgres: postgres postgres [local] EXPLAIN(table_open+0x1d)[0x568261]
postgres: postgres postgres [local] EXPLAIN[0x912fc7]
postgres: postgres postgres [local] EXPLAIN(QueryRewrite+0xb3)[0x916b8b]
postgres: postgres postgres [local] EXPLAIN(ExplainQuery+0x59b)[0x6928e2]
postgres: postgres postgres [local] EXPLAIN(standard_ProcessUtility+0x845)[0x97f7ce]
/var/lib/pgsql/postgresql_14/lib/plugins/pgtt.so(+0x32b3)[0x7fb7da98a2b3]
postgres: postgres postgres [local] EXPLAIN(ProcessUtility+0xe7)[0x97ef51]
postgres: postgres postgres [local] EXPLAIN[0x97dba3]
postgres: postgres postgres [local] EXPLAIN[0x97d905]
postgres: postgres postgres [local] EXPLAIN(PortalRun+0x244)[0x97d278]
postgres: postgres postgres [local] EXPLAIN[0x976f63]
postgres: postgres postgres [local] EXPLAIN(PostgresMain+0x80b)[0x97b476]
postgres: postgres postgres [local] EXPLAIN[0x8b65eb]
postgres: postgres postgres [local] EXPLAIN[0x8b5f67]
postgres: postgres postgres [local] EXPLAIN[0x8b2434]
postgres: postgres postgres [local] EXPLAIN(PostmasterMain+0x115c)[0x8b1d01]
postgres: postgres postgres [local] EXPLAIN[0x7b2ac8]
/lib64/libc.so.6(__libc_start_main+0xe5)[0x7fb7e63dad85]
postgres: postgres postgres [local] EXPLAIN(_start+0x2e)[0x485b1e]
2024-04-01 21:10:21.580 CST [3796278] LOG:  server process (PID 32882) was terminated by signal 6: Aborted
2024-04-01 21:10:21.580 CST [3796278] DETAIL:  Failed process was running: explain verbose SELECT * FROM pgtt_schema.test_tt;
2024-04-01 21:10:21.580 CST [3796278] LOG:  terminating any other active server processes
2024-04-01 21:10:21.582 CST [32898] FATAL:  the database system is in recovery mode

postgres=# \d+ test_tt
Did not find any relation named "test_tt".
postgres=# LOAD 'pgtt';
LOAD
postgres=# \d+ test_tt
Unlogged table "pgtt_schema.test_tt"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
lbl | text | | | | extended | |

postgres=# INSERT INTO test_tt VALUES (1, 'one'), (2, 'two'), (3, 'three');
ERROR: unrecognized node type: 375
postgres=# select * from test_tt;
ERROR: unrecognized node type: 375
postgres=# select version();
version

PostgreSQL 11.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)

This is the problem I encountered during my test. Could you tell me how to solve it？
It looks like there is a problem with "PGTT provide a query string".

Hi,

2.10 fails to build against PostgreSQL 16, which is due this Thursday. Can you please take a look? Errors are below.

Thanks!

Regards, Devrim

/usr/lib64/ccache/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Xclang -no-opaque-pointers -Wno-unused-command-line-argument -Wno-compound-token-split-by-macro -O2 -I/usr/pgsql-16/include -Wno-uninitialized -I. -I./ -I/usr/pgsql-16/include/server -I/usr/pgsql-16/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -flto=thin -emit-llvm -c -o pgtt.bc pgtt.c
pgtt.c:143:12: error: static declaration of 'get_extension_schema' follows non-static declaration
143 | static Oid get_extension_schema(Oid ext_oid);
| ^~~~~~~~~~~~~~~~~~~~
In file included from pgtt.c:36:
/usr/pgsql-16/include/server/commands/extension.h:50:17: note: previous declaration of 'get_extension_schema' with type 'Oid(Oid)' {aka 'unsigned int(unsigned int)'}
50 | extern Oid get_extension_schema(Oid ext_oid);
| ^~~~~~~~~~~~~~~~~~~~
pgtt.c: In function 'GttHashTableDeleteAll':
pgtt.c:180:21: warning: declaration of 'hentry' shadows a previous local [-Wshadow=compatible-local]
180 | GttHashEnt *hentry;
| ^~~~~~
pgtt.c:1337:17: note: in expansion of macro 'GttHashTableDelete'
1337 | GttHashTableDelete(hentry->name);
| ^~~~~~~~~~~~~~~~~~
pgtt.c:1326:21: note: shadowed declaration is here
1326 | GttHashEnt hentry = NULL;
| ^~~~~~
pgtt.c: In function 'create_temporary_table_internal':
pgtt.c:1571:25: error: too few arguments to function 'DefineIndex'
1571 | DefineIndex(relid, / OID of heap relation /
| ^~~~~~~~~~~
In file included from pgtt.c:35:
/usr/pgsql-16/include/server/commands/defrem.h:27:22: note: declared here
27 | extern ObjectAddress DefineIndex(Oid relationId,
| ^~~~~~~~~~~
make[1]: *** [: pgtt.o] Error 1
make[1]: *** Waiting for unfinished jobs....
pgtt.c:143:12: error: static declaration of 'get_extension_schema' follows non-static declaration
static Oid get_extension_schema(Oid ext_oid);
^
/usr/pgsql-16/include/server/commands/extension.h:50:12: note: previous declaration is here
extern Oid get_extension_schema(Oid ext_oid);
^
pgtt.c:1584:12: error: too few arguments to function call, expected 11, have 10
false); / quiet */
^
/usr/pgsql-16/include/server/commands/defrem.h:27:22: note: 'DefineIndex' declared here
extern ObjectAddress DefineIndex(Oid relationId,
^
pgtt.c:1859:1: error: static declaration of 'get_extension_schema' follows non-static declaration
get_extension_schema(Oid ext_oid)
^
/usr/pgsql-16/include/server/commands/extension.h:50:12: note: previous declaration is here
extern Oid get_extension_schema(Oid ext_oid);
^

When I load PGTT in many sessions (for mass task scheduler), I've got the error:

Background worker "parallel worker" (PID XXX) was terminated by signal 11: Segmentation fault
and server process crushing.

After I disable parallel worker in PostgreSQL config:
max_parallel_workers=0
it works fine.

I used PostgreSQL 14 docker version with PGTT, orafce and pg_curl extensions.
Docker image on dockerhub:
docker pull ukolovda/pg_mvno:14.0

Currently, this extension will not work for postgres in the cloud because of its direct ties to the underlying operating system.

Is there a mini-version of pgtt that can be created for cloud installations like AWS that just comes with SQL stuff to load to get it to work? I'm thinking of another file being added to this project that contains SQL stuff to manually load into a target PG server to get the functionality of pgtt, even if it is limited in some respects.

I'm on a new host and having problems installing pgtt.

wget https://github.com/darold/pgtt/archive/master.zip
unzip master.zip
cd pgtt-master/
export PATH=/usr/pgsql-12/bin/:$PATH
make
/opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2 -I/usr/pgsql-12/include -I. -I./ -I/usr/pgsql-12/include/server -I/usr/pgsql-12/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -flto=thin -emit-llvm -c -o pgtt.bc pgtt.c
make: /opt/rh/llvm-toolset-7/root/usr/bin/clang: Command not found
make: *** [pgtt.bc] Error 127

I installed:
yum install clang.x86_64
yum install clang-devel.x86_64

But that didn't change anything.

Suggestions?