SQL statement PREPARE does not work in ECPG
Hi,
In the PostgreSQL Documentation, both ECPG PREPARE and SQL statement PREPARE
can be used in ECPG [1].
However, SQL statement PREPARE does not work.
I wrote the source code as follows.
EXEC SQL PREPARE test_prep (int) AS SELECT id from test_table where id = $1;
EXEC SQL EXECUTE test_prep (2);
PostgreSQL 11.2 ECPG produced following code.
{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "prepare \"test_prep\" ( int
) as \" select id from test_table where id = $1 \"", ECPGt_EOIT, ECPGt_EORT);
#line 16 "test_app.pgc"
if (sqlca.sqlcode < 0) error_exit ( );}
#line 16 "test_app.pgc"
{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_execute, "test_prep", ECPGt_EOIT,
ECPGt_EORT);
#line 17 "test_app.pgc"
if (sqlca.sqlcode < 0) error_exit ( );}
#line 17 "test_app.pgc"
There are following problems.
(1)
When I run this program, it failed with "PostgreSQL error : -202[too few
arguments on line 16]".
The reason is ECPGdo has no argument though prepare statement has "$1".
(2)
I want to execute test_prep (2), but ECPGst_execute does not have argument.
Can SQL statement PREPARE be really used in ECPG?
[1] - https://www.postgresql.org/docs/11/ecpg-sql-prepare.html
Regards,
Ryohei Takahashi
RE: SQL statement PREPARE does not work in ECPG
Hi Meskes-san,
Thank you for your replying.
> Please try this instead:
>
> EXEC SQL PREPARE test_prep (int) AS SELECT id from test_table where id
> = $1;
> EXEC SQL EXECUTE test_prep using 2;
>
> This should work.
I tried as follows.
EXEC SQL PREPARE test_prep (int) AS SELECT id from test_table where id = $1;
EXEC SQL EXECUTE test_prep using 2;
{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "prepare \"test_prep\" ( int
) as \" select id from test_table where id = $1 \"", ECPGt_EOIT, ECPGt_EORT);
#line 16 "test_app.pgc"
if (sqlca.sqlcode < 0) error_exit ( );}
#line 16 "test_app.pgc"
{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_execute, "test_prep",
ECPGt_const,"2",(long)1,(long)1,strlen("2"),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_EORT);
#line 17 "test_app.pgc"
if (sqlca.sqlcode < 0) error_exit ( );}
#line 17 "test_app.pgc"
Unfortunately, this does not work.
ECPGst_execute seems good, but prepare statement is the same as my first post.
It fails with "PostgreSQL error : -202[too few arguments on line 16]".
This error is caused by following source code.
/* Check if there are unmatched things left. */
if (next_insert(stmt->command, position, stmt->questionmarks,
std_strings) >= 0)
{
ecpg_raise(stmt->lineno, ECPG_TOO_FEW_ARGUMENTS,
ECPG_SQLSTATE_USING_CLAUSE_DOES_NOT_MATCH_PARAMETERS, NULL);
ecpg_free_params(stmt, false);
return false;
}
if (text[p] == '$' && isdigit((unsigned char) text[p +
1]))
{
/* this can be either a dollar quote or a
variable */
int i;
for (i = p + 1; isdigit((unsigned char)
text[i]); i++)
/* empty loop body */ ;
if (!isalpha((unsigned char) text[i]) &&
isascii((unsigned char) text[i])
&&text[i] != '_')
/* not dollar delimited quote */
return p;
}
I think next_insert() should ignore "$n" in the case of SQL statement PREPARE.
In addition, we should fix following, right?
(1)
As Matsumura-san wrote, ECPG should not produce '"' for SQL statement PREPARE.
(2)
ECPG should produce argument for execute statement such as "EXEC SQL EXECUTE
test_prep (2);"
Regards,
Ryohei Takahashi
RE: SQL statement PREPARE does not work in ECPG
Hi Meskes-san, > Ah right, my bad. The workaround should have been: Thank you. It works. > As for the PREPARE statement itself, could you try the attached small > patch please. It works well for my statement "EXEC SQL PREPARE test_prep (int) AS SELECT id from test_table where id = $1;". However, since data type information is not used, it does not works well for prepare statements which need data type information such as "EXEC SQL PREPARE test_prep (int, int) AS SELECT $1 + $2;". It fails with "PostgreSQL error : -400[operator is not unique: unknown + unknown on line 20]". (Of course, "EXEC SQL PREPARE test_prep AS SELECT $1::int + $2::int;" works well.) > Could you please also verify for me if this works correctly if you use > a variable instead of the const? As in: > EXEC SQL BEGIN DECLARE SECTION; > int i=2; > EXEC SQL END DECLARE SECTION; > ... > EXEC SQL EXECUTE test_prep (:i); It also works. (Actually, I wrote "EXEC SQL EXECUTE test_prep (:i) INTO :ID;".) ECPG produced as follows. ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_execute, "test_prep", ECPGt_int,&(i),(long)1,(long)1,sizeof(int), ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_int,&(ID),(long)1,(long)1,sizeof(int), ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT); Regards, Ryohei Takahashi
RE: SQL statement PREPARE does not work in ECPG
Hi Matsumura-san, Thank you for your explaining. > An important point of the route is that it calls PQprepare() and PQprepare() > needs type-Oid list. (Idea-1) If we fix for Prepare statement with AS clause > and > with parameter list to walk through the route, preprocessor must parse the > parameter list and > preprocessor or ecpglib must make type-Oid list. I think it's difficult. > Especially, I wonder if it can treat user defined type and complex structure > type. I agree. In the case of standard types, ECPG can get oids from pg_type.h. However, in the case of user defined types, ECPG needs to access pg_type table and it is overhead. Therefore, the second idea seems better. By the way, should we support prepare statement like following? (I think yes.) EXEC SQL PREPARE test_prep (int) AS SELECT id from test_table where id = :ID or id =$1; Current ECPG produces following code. ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "prepare \"test_prep\" ( int ) as \" select id from test_table where id = $1 or id = $1 \"", ECPGt_int,&(ID),(long)1,(long)1,sizeof(int), ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_EORT); In this case, both ":ID" and "$1" in the original statement are converted to "$1" and ECPGdo() cannot distinguish them. Therefore, ECPG should produce different code. For example, - ECPG convert ":ID" to "$1" and "$1" in the original statement to "$$1" - next_insert() do not check "$$1" - ECPGdo() reconvert "$$1" to "$1" Regards, Ryohei Takahashi
RE: Too many logs are written on Windows (LOG: could not reserve shared memory region (addr=%p) for child %p:)
Hi, Thank you for replying. > You might be right, but maybe we should first try to understand why > this is happening so frequently. Maybe it's not that normal. I found past threads [1] and [2]. In thread [1], the error is mentioned as an ASLR problem. In thread [2], the patch is provided which retries pgwin32_ReserveSharedMemoryRegion() to resolve the ASLR problem. Therefore, I think our community regard that the error is an ASLR problem and is already resolved by the patch. [1] - https://www.postgresql.org/message-id/flat/CACmJi2JyDLMtxE3i_Krp%3DhrK4KKZ%3DD83s6eMw9q%3DHeM_srQdXg%40mail.gmail.com [2] - https://www.postgresql.org/message-id/flat/CAA4eK1%2BR6hSx6t_yvwtx%2BNRzneVp%2BMRqXAdGJZChcau8Uij-8g%40mail.gmail.com Regards, Ryohei Takahashi
RE: Too many logs are written on Windows (LOG: could not reserve shared memory region (addr=%p) for child %p:)
Hi, I found the reason of the message. My customer uses "F-secure" antivirus software. There are several pages that indicate F-secure causes this message such as [1]. I told my customer to stop F-secure and report to the vendor. Anyway, I think this message is not helpful to administrators and should be lower level such as "DEBUG1". [1] - https://www.postgresql.org/message-id/flat/feb75b80-aa4e-0b45-fbe7-0c2335904fcc%40evolu-s.it Regards, Ryohei Takahashi
RE: Too many logs are written on Windows (LOG: could not reserve shared memory region (addr=%p) for child %p:)
Hi Noah, Magnus and Tsunakawa-san, Thank you for replying. > Can you adopt pgbouncer, to reduce > the frequency of starting new backend processes? That should improve your > performance, too. Actually, before I found that F-secure causes this message, I recommend my customer to use connection pooling to reduce the number of connection times. > Could you collect the information > http://postgr.es/m/20181203053506.gb2860...@rfd.leadboat.com requests? > That may help us understand your system's unusual behavior. (The issue in > that > thread is related but not identical.) Sorry. Since my customer uses PostgreSQL in production environment, I cannot deploy debug modules. > In this particular case, it seems it *was* helpful, no? That's how you found > out the customer used a broken antivirus product, which may certainly also > cause *other* issues. > > Some sort of rate limiting to reduce the volume might help, but the message > itself seems to have clearly been useful. Yes. You are right. The message itself was useful. Your idea to reduce the volume seems good. > We can change pgwin32_ReserveSharedMemoryRegion() to take an argument "int > loglevel". Then the caller first calls it with LOG, and DEBUGx afterwards. > It may also be helpful for the caller to output "LOG: tried %d times to > reserve shared memory region" when the caller ran the function twice or > more before success. That explains the possibly long time or CPU spikes > of connection establishment. It seems good idea for me. Regards, Ryohei Takahashi
RE: Too many logs are written on Windows (LOG: could not reserve shared memory region (addr=%p) for child %p:)
Hi, Thank you for replying. > Like a couple of others on this thread I doubt that lowering the elevel > here would be a good thing, as keeping it noisy has been what allows to > know that something has gone wrong, no? The current behavior is > useful. Currently, I agree with you. I cancel my proposal. Regards, Ryohei Takahashi
Too many logs are written on Windows (LOG: could not reserve shared memory region (addr=%p) for child %p:)
Hi, My customer uses PostgreSQL on Windows and hits the problem that following log is written to the server logs too frequently (250 thousand times per day). "LOG: could not reserve shared memory region (addr=%p) for child %p:" This log is written when pgwin32_ReserveSharedMemoryRegion() in win32_shmem.c fails. If fails, internal_forkexec() in postmaster.c retries up to 100 times. In most of my customer cases, internal_forkexec() finally succeeded to pgwin32_ReserveSharedMemoryRegion() by retrying. According to the comment of internal_forkexec(), pgwin32_ReserveSharedMemoryRegion() sometimes fails if ASLR is active. If so, I think administrators are not interested in this log since it is a normal event. I think the log level should not be "LOG", but should be lower level such as "DEBUG1". Regards, Ryohei Takahashi
RE: SQL statement PREPARE does not work in ECPG
Hi Matsumura-san, Thank you for your explaining. > An important point of the route is that it calls PQprepare() and PQprepare() > needs type-Oid list. (Idea-1) If we fix for Prepare statement with AS clause > and > with parameter list to walk through the route, preprocessor must parse the > parameter list and > preprocessor or ecpglib must make type-Oid list. I think it's difficult. > Especially, I wonder if it can treat user defined type and complex structure > type. I agree. In the case of standard types, ECPG can get oids from pg_type.h. However, in the case of user defined types, ECPG needs to access pg_type table and it is overhead. Therefore, the second idea seems better. By the way, should we support prepare statement like following? (I think yes.) EXEC SQL PREPARE test_prep (int) AS SELECT id from test_table where id = :ID or id =$1; Current ECPG produces following code. ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "prepare \"test_prep\" ( int ) as \" select id from test_table where id = $1 or id = $1 \"", ECPGt_int,&(ID),(long)1,(long)1,sizeof(int), ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_EORT); In this case, both ":ID" and "$1" in the original statement are converted to "$1" and ECPGdo() cannot distinguish them. Therefore, ECPG should produce different code. For example, - ECPG convert ":ID" to "$1" and "$1" in the original statement to "$$1" - next_insert() do not check "$$1" - ECPGdo() reconvert "$$1" to "$1" Regards, Ryohei Takahashi
SQL statement PREPARE does not work in ECPG
Hi,
In the PostgreSQL Documentation, both ECPG PREPARE and SQL statement PREPARE
can be used in ECPG [1].
However, SQL statement PREPARE does not work.
I wrote the source code as follows.
EXEC SQL PREPARE test_prep (int) AS SELECT id from test_table where id = $1;
EXEC SQL EXECUTE test_prep (2);
PostgreSQL 11.2 ECPG produced following code.
{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "prepare \"test_prep\" ( int
) as \" select id from test_table where id = $1 \"", ECPGt_EOIT, ECPGt_EORT);
#line 16 "test_app.pgc"
if (sqlca.sqlcode < 0) error_exit ( );}
#line 16 "test_app.pgc"
{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_execute, "test_prep", ECPGt_EOIT,
ECPGt_EORT);
#line 17 "test_app.pgc"
if (sqlca.sqlcode < 0) error_exit ( );}
#line 17 "test_app.pgc"
There are following problems.
(1)
When I run this program, it failed with "PostgreSQL error : -202[too few
arguments on line 16]".
The reason is ECPGdo has no argument though prepare statement has "$1".
(2)
I want to execute test_prep (2), but ECPGst_execute does not have argument.
Can SQL statement PREPARE be really used in ECPG?
[1] - https://www.postgresql.org/docs/11/ecpg-sql-prepare.html
Regards,
Ryohei Takahashi
RE: SQL statement PREPARE does not work in ECPG
Hi Meskes-san,
Thank you for your replying.
> Please try this instead:
>
> EXEC SQL PREPARE test_prep (int) AS SELECT id from test_table where id
> = $1;
> EXEC SQL EXECUTE test_prep using 2;
>
> This should work.
I tried as follows.
EXEC SQL PREPARE test_prep (int) AS SELECT id from test_table where id = $1;
EXEC SQL EXECUTE test_prep using 2;
{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "prepare \"test_prep\" ( int
) as \" select id from test_table where id = $1 \"", ECPGt_EOIT, ECPGt_EORT);
#line 16 "test_app.pgc"
if (sqlca.sqlcode < 0) error_exit ( );}
#line 16 "test_app.pgc"
{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_execute, "test_prep",
ECPGt_const,"2",(long)1,(long)1,strlen("2"),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_EORT);
#line 17 "test_app.pgc"
if (sqlca.sqlcode < 0) error_exit ( );}
#line 17 "test_app.pgc"
Unfortunately, this does not work.
ECPGst_execute seems good, but prepare statement is the same as my first post.
It fails with "PostgreSQL error : -202[too few arguments on line 16]".
This error is caused by following source code.
/* Check if there are unmatched things left. */
if (next_insert(stmt->command, position, stmt->questionmarks,
std_strings) >= 0)
{
ecpg_raise(stmt->lineno, ECPG_TOO_FEW_ARGUMENTS,
ECPG_SQLSTATE_USING_CLAUSE_DOES_NOT_MATCH_PARAMETERS, NULL);
ecpg_free_params(stmt, false);
return false;
}
if (text[p] == '$' && isdigit((unsigned char) text[p +
1]))
{
/* this can be either a dollar quote or a
variable */
int i;
for (i = p + 1; isdigit((unsigned char)
text[i]); i++)
/* empty loop body */ ;
if (!isalpha((unsigned char) text[i]) &&
isascii((unsigned char) text[i])
&&text[i] != '_')
/* not dollar delimited quote */
return p;
}
I think next_insert() should ignore "$n" in the case of SQL statement PREPARE.
In addition, we should fix following, right?
(1)
As Matsumura-san wrote, ECPG should not produce '"' for SQL statement PREPARE.
(2)
ECPG should produce argument for execute statement such as "EXEC SQL EXECUTE
test_prep (2);"
Regards,
Ryohei Takahashi
RE: SQL statement PREPARE does not work in ECPG
Hi Meskes-san, > Ah right, my bad. The workaround should have been: Thank you. It works. > As for the PREPARE statement itself, could you try the attached small > patch please. It works well for my statement "EXEC SQL PREPARE test_prep (int) AS SELECT id from test_table where id = $1;". However, since data type information is not used, it does not works well for prepare statements which need data type information such as "EXEC SQL PREPARE test_prep (int, int) AS SELECT $1 + $2;". It fails with "PostgreSQL error : -400[operator is not unique: unknown + unknown on line 20]". (Of course, "EXEC SQL PREPARE test_prep AS SELECT $1::int + $2::int;" works well.) > Could you please also verify for me if this works correctly if you use > a variable instead of the const? As in: > EXEC SQL BEGIN DECLARE SECTION; > int i=2; > EXEC SQL END DECLARE SECTION; > ... > EXEC SQL EXECUTE test_prep (:i); It also works. (Actually, I wrote "EXEC SQL EXECUTE test_prep (:i) INTO :ID;".) ECPG produced as follows. ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_execute, "test_prep", ECPGt_int,&(i),(long)1,(long)1,sizeof(int), ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_int,&(ID),(long)1,(long)1,sizeof(int), ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT); Regards, Ryohei Takahashi
Too many logs are written on Windows (LOG: could not reserve shared memory region (addr=%p) for child %p:)
Hi, My customer uses PostgreSQL on Windows and hits the problem that following log is written to the server logs too frequently (250 thousand times per day). "LOG: could not reserve shared memory region (addr=%p) for child %p:" This log is written when pgwin32_ReserveSharedMemoryRegion() in win32_shmem.c fails. If fails, internal_forkexec() in postmaster.c retries up to 100 times. In most of my customer cases, internal_forkexec() finally succeeded to pgwin32_ReserveSharedMemoryRegion() by retrying. According to the comment of internal_forkexec(), pgwin32_ReserveSharedMemoryRegion() sometimes fails if ASLR is active. If so, I think administrators are not interested in this log since it is a normal event. I think the log level should not be "LOG", but should be lower level such as "DEBUG1". Regards, Ryohei Takahashi
RE: Too many logs are written on Windows (LOG: could not reserve shared memory region (addr=%p) for child %p:)
Hi, Thank you for replying. > You might be right, but maybe we should first try to understand why > this is happening so frequently. Maybe it's not that normal. I found past threads [1] and [2]. In thread [1], the error is mentioned as an ASLR problem. In thread [2], the patch is provided which retries pgwin32_ReserveSharedMemoryRegion() to resolve the ASLR problem. Therefore, I think our community regard that the error is an ASLR problem and is already resolved by the patch. [1] - https://www.postgresql.org/message-id/flat/CACmJi2JyDLMtxE3i_Krp%3DhrK4KKZ%3DD83s6eMw9q%3DHeM_srQdXg%40mail.gmail.com [2] - https://www.postgresql.org/message-id/flat/CAA4eK1%2BR6hSx6t_yvwtx%2BNRzneVp%2BMRqXAdGJZChcau8Uij-8g%40mail.gmail.com Regards, Ryohei Takahashi
RE: Too many logs are written on Windows (LOG: could not reserve shared memory region (addr=%p) for child %p:)
Hi, I found the reason of the message. My customer uses "F-secure" antivirus software. There are several pages that indicate F-secure causes this message such as [1]. I told my customer to stop F-secure and report to the vendor. Anyway, I think this message is not helpful to administrators and should be lower level such as "DEBUG1". [1] - https://www.postgresql.org/message-id/flat/feb75b80-aa4e-0b45-fbe7-0c2335904fcc%40evolu-s.it Regards, Ryohei Takahashi
RE: Too many logs are written on Windows (LOG: could not reserve shared memory region (addr=%p) for child %p:)
Hi Noah, Magnus and Tsunakawa-san, Thank you for replying. > Can you adopt pgbouncer, to reduce > the frequency of starting new backend processes? That should improve your > performance, too. Actually, before I found that F-secure causes this message, I recommend my customer to use connection pooling to reduce the number of connection times. > Could you collect the information > http://postgr.es/m/20181203053506.gb2860...@rfd.leadboat.com requests? > That may help us understand your system's unusual behavior. (The issue in > that > thread is related but not identical.) Sorry. Since my customer uses PostgreSQL in production environment, I cannot deploy debug modules. > In this particular case, it seems it *was* helpful, no? That's how you found > out the customer used a broken antivirus product, which may certainly also > cause *other* issues. > > Some sort of rate limiting to reduce the volume might help, but the message > itself seems to have clearly been useful. Yes. You are right. The message itself was useful. Your idea to reduce the volume seems good. > We can change pgwin32_ReserveSharedMemoryRegion() to take an argument "int > loglevel". Then the caller first calls it with LOG, and DEBUGx afterwards. > It may also be helpful for the caller to output "LOG: tried %d times to > reserve shared memory region" when the caller ran the function twice or > more before success. That explains the possibly long time or CPU spikes > of connection establishment. It seems good idea for me. Regards, Ryohei Takahashi
RE: Too many logs are written on Windows (LOG: could not reserve shared memory region (addr=%p) for child %p:)
Hi, Thank you for replying. > Like a couple of others on this thread I doubt that lowering the elevel > here would be a good thing, as keeping it noisy has been what allows to > know that something has gone wrong, no? The current behavior is > useful. Currently, I agree with you. I cancel my proposal. Regards, Ryohei Takahashi
