pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #5274: [PL/PgSQL] EXECUTE ... USING variable expansion
7+ messages / 4 participants
[nested] [flat]
* BUG #5274: [PL/PgSQL] EXECUTE ... USING variable expansion
@ 2010-01-13 19:10 Vincenzo Romano <vincenzo.romano@notorand.it>
2010-01-14 08:54 ` Re: BUG #5274: [PL/PgSQL] EXECUTE ... USING variable expansion Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Vincenzo Romano @ 2010-01-13 19:10 UTC (permalink / raw)
To: pgsql-bugs
The following bug has been logged online:
Bug reference: 5274
Logged by: Vincenzo Romano
Email address: vincenzo.romano@notorand.it
PostgreSQL version: 8.4.2
Operating system: Linux
Description: [PL/PgSQL] EXECUTE ... USING variable expansion
Details:
My system says:
~ lsb_release -a
LSB Version:
:core-3.1-amd64:core-3.1-noarch:core-3.2-amd64:core-3.2-noarch:desktop-3.1-a
md64:desktop-3.1-noarch:desktop-3.2-amd64:desktop-3.2-noarch
Distributor ID: Fedora
Description: Fedora release 12 (Constantine)
Release: 12
Codename: Constantine
If you try the following:
CREATE TABLE test ( i INT );
CREATE OR REPLACE FUNCTION func()
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
e TEXT;
t TEXT;
i INT;
BEGIN
i := 42;
t := 'answer';
EXECUTE 'SELECT $1' INTO e USING t;
RAISE INFO '%',e;
EXECUTE 'ALTER TABLE test ALTER COLUMN i SET DEFAULT $1' USING i;
END;
$function$;
SELECT func();
The first EXECUTE...USING replaces the variable $1 with the value of the
variable "t". The first output line reads:
INFO: answer
The second EXECUTE...USING doesn't do the replacement and triggers an
error:
ERROR: there is no parameter $1
CONTEXT: SQL statement "ALTER TABLE test ALTER COLUMN i SET DEFAULT $1"
PL/pgSQL function "func" line 10 at EXECUTE statement
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #5274: [PL/PgSQL] EXECUTE ... USING variable expansion
2010-01-13 19:10 BUG #5274: [PL/PgSQL] EXECUTE ... USING variable expansion Vincenzo Romano <vincenzo.romano@notorand.it>
@ 2010-01-14 08:54 ` Pavel Stehule <pavel.stehule@gmail.com>
2010-01-14 14:26 ` Re: BUG #5274: [PL/PgSQL] EXECUTE ... USING variable expansion Vincenzo Romano <vincenzo.romano@notorand.it>
0 siblings, 1 reply; 7+ messages in thread
From: Pavel Stehule @ 2010-01-14 08:54 UTC (permalink / raw)
To: Vincenzo Romano <vincenzo.romano@notorand.it>; +Cc: pgsql-bugs
Hello
it is not bug.
DDL statements like CREATE TABLE, ALTER TABLE are doesn't support
parametrisation - you cannot use a placeholder for parameter
everywhere. So you cannot to write PQexecParams(..."ALTER TABLE test
ALTER COLUMN $1 ...", ...), so it cannot be supported by EXECUTE
USING. Parameters are available only for DML statements - for
statements with execution plan. You can store a plan and you can call
stored plan with different parameters - it means - parameter cannot be
a SQL identifier - like column or table name, because this changes a
plan.
so
you can do
EXECUTE 'SELECT * FROM tab WHERE col = $1' USING var1
but you cannot do:
EXECUTE 'SELECT * FROM $1 WHERE col = 10' USING var1, because SELECT
FROM tab1 or SELECT FROM tab2 needs different execution plans. You
cannot do too:
EXECUTE 'CREATE TABLE $1' USING var1, bacause CREATE TABLE is DDL
statement without plan, and without possibility to use a parameters.
You have to do:
EXECUTE 'SELECT * FROM ' || var1::regclass || ' WHERE col=10' --
var1::regclass is minimum protection against SQL injection
EXECUTE 'CREATE TABLE '|| quote_ident(var1) || '(....
Regards
Pavel Stehule
2010/1/13 Vincenzo Romano <vincenzo.romano@notorand.it>:
>
> The following bug has been logged online:
>
> Bug reference: 5274
> Logged by: Vincenzo Romano
> Email address: vincenzo.romano@notorand.it
> PostgreSQL version: 8.4.2
> Operating system: Linux
> Description: [PL/PgSQL] EXECUTE ... USING variable expansion
> Details:
>
> My system says:
> ~ lsb_release -a
> LSB Version:
> :core-3.1-amd64:core-3.1-noarch:core-3.2-amd64:core-3.2-noarch:desktop-3.1-a
> md64:desktop-3.1-noarch:desktop-3.2-amd64:desktop-3.2-noarch
> Distributor ID: Fedora
> Description: Fedora release 12 (Constantine)
> Release: 12
> Codename: Constantine
>
> If you try the following:
>
> CREATE TABLE test ( i INT );
>
> CREATE OR REPLACE FUNCTION func()
> RETURNS void
> LANGUAGE plpgsql
> AS $function$
> DECLARE
> e TEXT;
> t TEXT;
> i INT;
> BEGIN
> i := 42;
> t := 'answer';
> EXECUTE 'SELECT $1' INTO e USING t;
> RAISE INFO '%',e;
> EXECUTE 'ALTER TABLE test ALTER COLUMN i SET DEFAULT $1' USING i;
> END;
> $function$;
>
> SELECT func();
>
> The first EXECUTE...USING replaces the variable $1 with the value of the
> variable "t". The first output line reads:
>
> INFO: answer
>
> The second EXECUTE...USING doesn't do the replacement and triggers an
> error:
>
> ERROR: there is no parameter $1
> CONTEXT: SQL statement "ALTER TABLE test ALTER COLUMN i SET DEFAULT $1"
> PL/pgSQL function "func" line 10 at EXECUTE statement
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #5274: [PL/PgSQL] EXECUTE ... USING variable expansion
2010-01-13 19:10 BUG #5274: [PL/PgSQL] EXECUTE ... USING variable expansion Vincenzo Romano <vincenzo.romano@notorand.it>
2010-01-14 08:54 ` Re: BUG #5274: [PL/PgSQL] EXECUTE ... USING variable expansion Pavel Stehule <pavel.stehule@gmail.com>
@ 2010-01-14 14:26 ` Vincenzo Romano <vincenzo.romano@notorand.it>
2010-01-14 14:36 ` Re: BUG #5274: [PL/PgSQL] EXECUTE ... USING variable expansion Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Vincenzo Romano @ 2010-01-14 14:26 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-bugs
2010/1/14 Pavel Stehule <pavel.stehule@gmail.com>:
> Hello
>
> it is not bug.
>
> DDL statements like CREATE TABLE, ALTER TABLE are doesn't support
> parametrisation - you cannot use a placeholder for parameter
> everywhere. So you cannot to write PQexecParams(..."ALTER TABLE test
> ALTER COLUMN $1 ...", ...), so it cannot be supported by EXECUTE
> USING. Parameters are available only for DML statements - for
> statements with execution plan. You can store a plan and you can call
> stored plan with different parameters - it means - parameter cannot be
> a SQL identifier - like column or table name, because this changes a
> plan.
>
> so
>
> you can do
>
> EXECUTE 'SELECT * FROM tab WHERE col = $1' USING var1
>
> but you cannot do:
>
> EXECUTE 'SELECT * FROM $1 WHERE col = 10' USING var1, because SELECT
> FROM tab1 or SELECT FROM tab2 needs different execution plans. You
> cannot do too:
>
> EXECUTE 'CREATE TABLE $1' USING var1, bacause CREATE TABLE is DDL
> statement without plan, and without possibility to use a parameters.
>
> You have to do:
>
> EXECUTE 'SELECT * FROM ' || var1::regclass || ' WHERE col=10' --
> var1::regclass is minimum protection against SQL injection
> EXECUTE 'CREATE TABLE '|| quote_ident(var1) || '(....
>
> Regards
> Pavel Stehule
Documentation (v8.*) clearly states that you cannot use the
placeholders for table and column names.
What I'm reporting here is that even:
EXECUTE 'ALTER TABLE test ALTER COLUMN i SET DEFAULT $1' USING 42;
is complaining with:
ERROR: there is no parameter $1
CONTEXT: SQL statement "ALTER TABLE test ALTER COLUMN i SET DEFAULT $1"
while:
EXECUTE 'SELECT $1' USING 42;
works.
There's no variable, just a constant value. The usefulness of the
EXECUTE USING is thus dramatically reduced.
Also because the documentation reports something really meaningful and
reasonable:
The command string can use parameter values, which are referenced in
the command as $1, $2,
etc. These symbols refer to values supplied in the USING clause. This
method is often preferable to
inserting data values into the command string as text: it avoids
run-time overhead of converting the
values to text and back, and it is much less prone to SQL-injection
attacks since there is no need for
quoting or escaping.
(http://www.postgresql.org/files/documentation/pdf/8.4/postgresql-8.4.2-A4.pdf
page 800
or http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
"38.5.4. Executing Dynamic Commands")
--
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #5274: [PL/PgSQL] EXECUTE ... USING variable expansion
2010-01-13 19:10 BUG #5274: [PL/PgSQL] EXECUTE ... USING variable expansion Vincenzo Romano <vincenzo.romano@notorand.it>
2010-01-14 08:54 ` Re: BUG #5274: [PL/PgSQL] EXECUTE ... USING variable expansion Pavel Stehule <pavel.stehule@gmail.com>
2010-01-14 14:26 ` Re: BUG #5274: [PL/PgSQL] EXECUTE ... USING variable expansion Vincenzo Romano <vincenzo.romano@notorand.it>
@ 2010-01-14 14:36 ` Pavel Stehule <pavel.stehule@gmail.com>
2010-01-14 14:59 ` Re: BUG #5274: [PL/PgSQL] EXECUTE ... USING variable expansion Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Pavel Stehule @ 2010-01-14 14:36 UTC (permalink / raw)
To: Vincenzo Romano <vincenzo.romano@notorand.it>; +Cc: pgsql-bugs
2010/1/14 Vincenzo Romano <vincenzo.romano@notorand.it>:
> 2010/1/14 Pavel Stehule <pavel.stehule@gmail.com>:
>> Hello
>>
>> it is not bug.
>>
>> DDL statements like CREATE TABLE, ALTER TABLE are doesn't support
>> parametrisation - you cannot use a placeholder for parameter
>> everywhere. So you cannot to write PQexecParams(..."ALTER TABLE test
>> ALTER COLUMN $1 ...", ...), so it cannot be supported by EXECUTE
>> USING. Parameters are available only for DML statements - for
>> statements with execution plan. You can store a plan and you can call
>> stored plan with different parameters - it means - parameter cannot be
>> a SQL identifier - like column or table name, because this changes a
>> plan.
>>
>> so
>>
>> you can do
>>
>> EXECUTE 'SELECT * FROM tab WHERE col = $1' USING var1
>>
>> but you cannot do:
>>
>> EXECUTE 'SELECT * FROM $1 WHERE col = 10' USING var1, because SELECT
>> FROM tab1 or SELECT FROM tab2 needs different execution plans. You
>> cannot do too:
>>
>> EXECUTE 'CREATE TABLE $1' USING var1, bacause CREATE TABLE is DDL
>> statement without plan, and without possibility to use a parameters.
>>
>> You have to do:
>>
>> EXECUTE 'SELECT * FROM ' || var1::regclass || ' WHERE col=10' --
>> var1::regclass is minimum protection against SQL injection
>> EXECUTE 'CREATE TABLE '|| quote_ident(var1) || '(....
>>
>> Regards
>> Pavel Stehule
>
> Documentation (v8.*) clearly states that you cannot use the
> placeholders for table and column names.
> What I'm reporting here is that even:
>
> EXECUTE 'ALTER TABLE test ALTER COLUMN i SET DEFAULT $1' USING 42;
>
> is complaining with:
>
> ERROR: there is no parameter $1
> CONTEXT: SQL statement "ALTER TABLE test ALTER COLUMN i SET DEFAULT $1"
>
> while:
>
> EXECUTE 'SELECT $1' USING 42;
>
> works.
> There's no variable, just a constant value. The usefulness of the
Hmm this needs be better documented. This is DDL case, so you cannot
use USING ever.
> EXECUTE USING is thus dramatically reduced.
it's depend - sure, you cannot use USING for DDL statements, but DDL
statements are usually small and not complicated. USING is for large
DML statements, there is primary goal. Simply, this is for statements
with executing plan - ALTER hasn't plan, so you cannot use USING.
> Also because the documentation reports something really meaningful and
> reasonable:
>
> The command string can use parameter values, which are referenced in
> the command as $1, $2,
> etc. These symbols refer to values supplied in the USING clause. This
> method is often preferable to
> inserting data values into the command string as text: it avoids
> run-time overhead of converting the
> values to text and back, and it is much less prone to SQL-injection
> attacks since there is no need for
> quoting or escaping.
you can use quote_literal function for protection against SQL injection.
>
> (http://www.postgresql.org/files/documentation/pdf/8.4/postgresql-8.4.2-A4.pdf
> page 800
> or http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
> "38.5.4. Executing Dynamic Commands")
>
> --
> Vincenzo Romano
> NotOrAnd Information Technologies
> NON QVIETIS MARIBVS NAVTA PERITVS
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #5274: [PL/PgSQL] EXECUTE ... USING variable expansion
2010-01-13 19:10 BUG #5274: [PL/PgSQL] EXECUTE ... USING variable expansion Vincenzo Romano <vincenzo.romano@notorand.it>
2010-01-14 08:54 ` Re: BUG #5274: [PL/PgSQL] EXECUTE ... USING variable expansion Pavel Stehule <pavel.stehule@gmail.com>
2010-01-14 14:26 ` Re: BUG #5274: [PL/PgSQL] EXECUTE ... USING variable expansion Vincenzo Romano <vincenzo.romano@notorand.it>
2010-01-14 14:36 ` Re: BUG #5274: [PL/PgSQL] EXECUTE ... USING variable expansion Pavel Stehule <pavel.stehule@gmail.com>
@ 2010-01-14 14:59 ` Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Pavel Stehule @ 2010-01-14 14:59 UTC (permalink / raw)
To: Vincenzo Romano <vincenzo.romano@notorand.it>; +Cc: pgsql-bugs
> Pavel,
> if it was a simple plain dynami statement, I could create it by string
> concatenation, literal_ and ident_ quoting and so on.
the situation isn't too simply like you see it. PostgreSQL supports
composite and nested types. For this types you cannot use quote*
functions. Next problem is NULL values. I did this work four years ago
and http://archives.postgresql.org/pgsql-patches/2006-08/msg00271.php
it was rejected - from good reasons. You can see more messages from
this time in archive. In plpgsql we don't would to supply SQL parser -
so when you would to identify pleaseholders, then your implementation
will be slow or buggy.
Regards
Pavel Stehule
> But this is not my case.
> I'm exploring an alternative approach to the table partitioning
> (http://notsononsense.blogspot.com/2009/12/postgresql-different-approach-to-table.html).
> And being able to handle dynamic DML and DDL is a key point, also for
> the traditional one.
> In practice, the whole EXECUTE...USING command is generated
> dynamically, so not even the names or the values passed after the
> USING lexem are known at that time.
>
> --
> Vincenzo Romano
> NotOrAnd Information Technologies
> cel. +39 339 8083886 | gtalk. vincenzo.romano@notorand.it
> fix. +39 0823 454163 | skype. notorand.it
> fax. +39 02 700506964 | msn. notorand.it
> NON QVIETIS MARIBVS NAVTA PERITVS
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* PGRES_EMPTY_QUERY from Postgre 14.3
@ 2023-08-21 14:59 Sanjay Patil <sanjaypatilwork@gmail.com>
2023-08-21 15:52 ` Re: PGRES_EMPTY_QUERY from Postgre 14.3 David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Sanjay Patil @ 2023-08-21 14:59 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
Hi Team
Currently, we are facing PGRES_EMPTY_QUERY return from Postgres DB on 14.3
version.
Its not every case, Whenever load increase to DB. Could you please suggest
us.
Thanks & Regards,
Sanjay Patil
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PGRES_EMPTY_QUERY from Postgre 14.3
2023-08-21 14:59 PGRES_EMPTY_QUERY from Postgre 14.3 Sanjay Patil <sanjaypatilwork@gmail.com>
@ 2023-08-21 15:52 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: David G. Johnston @ 2023-08-21 15:52 UTC (permalink / raw)
To: Sanjay Patil <sanjaypatilwork@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
On Mon, Aug 21, 2023 at 8:18 AM Sanjay Patil <sanjaypatilwork@gmail.com>
wrote:
> Hi Team
>
> Currently, we are facing PGRES_EMPTY_QUERY return from Postgres DB on 14.3
> version.
>
> Its not every case, Whenever load increase to DB. Could you please suggest
> us.
>
>
I get that a volume-based error is harder to reproduce but at least try and
provide some details like schema, query, and environment, when asking for
others to debug your system.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2023-08-21 15:52 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-01-13 19:10 BUG #5274: [PL/PgSQL] EXECUTE ... USING variable expansion Vincenzo Romano <vincenzo.romano@notorand.it>
2010-01-14 08:54 ` Pavel Stehule <pavel.stehule@gmail.com>
2010-01-14 14:26 ` Vincenzo Romano <vincenzo.romano@notorand.it>
2010-01-14 14:36 ` Pavel Stehule <pavel.stehule@gmail.com>
2010-01-14 14:59 ` Pavel Stehule <pavel.stehule@gmail.com>
2023-08-21 14:59 PGRES_EMPTY_QUERY from Postgre 14.3 Sanjay Patil <sanjaypatilwork@gmail.com>
2023-08-21 15:52 ` Re: PGRES_EMPTY_QUERY from Postgre 14.3 David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox