pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4
14+ messages / 6 participants
[nested] [flat]
* Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4
@ 2025-01-16 10:50 Rajni Bobal <rajnibobal@gmail.com>
2025-01-16 14:55 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Vik Fearing <vik@postgresfriends.org>
0 siblings, 1 reply; 14+ messages in thread
From: Rajni Bobal @ 2025-01-16 10:50 UTC (permalink / raw)
To: rajnibobal@gmail.com; pgsql-bugs@lists.postgresql.org
Hello Sir/Team,
I have created a new bug report, #18776, with the updated problem
statement. Kindly disregard bug #18774. Apologies for any inconvenience
caused.
regards,
RAJNI BOBAL
On Thu, Jan 16, 2025 at 12:59 PM PG Bug reporting form <
noreply@postgresql.org> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 18774
> Logged by: Rajni Bobal
> Email address: rajnibobal@gmail.com
> PostgreSQL version: Unsupported/Unknown
> Operating system: Ubuntu 22
> Description:
>
> The output or behavior of the query used in the
> function(delete_from_table1)
> below is not producing the expected results, while
> function(delete_from_table2) with its query produces the expected result
> when used. ( Means all the data is deleted by the function
> delete_from_table1, while data (based on wherecon condition) is deleted by
> the function delete_from_table2.)
>
> CREATE OR REPLACE FUNCTION delete_from_table1(tblname text, sel_tblname
> text, wherecon text, colname text, batchsize int)
> RETURNS void AS
> $$
> BEGIN
> -- Construct dynamic SQL for DELETE
> EXECUTE 'DELETE FROM '
> || quote_ident(tblname)
> || ' WHERE '
> || quote_ident(colname)
> || ' IN (SELECT '
> || quote_ident(colname)
> || ' FROM '
> || quote_ident(sel_tblname)
> || ' WHERE '
> || wherecon
> || ' LIMIT '
> || batchsize || ')';
> END;
> $$ LANGUAGE plpgsql;
>
>
> CREATE OR REPLACE FUNCTION delete_from_table2(tblname text, sel_tblname
> text, wherecon text, colname text, batchsize int)
> RETURNS void AS
> $$
> BEGIN
> EXECUTE 'DELETE from ' || quote_ident(tblname) ||
> ' where $1 in (select $1 from ' || quote_ident(sel_tblname) ||
> ' where ' || wherecon || ' limit $2 ) '
> USING colname, batchsize;
> END;
> $$ LANGUAGE plpgsql;
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4
2025-01-16 10:50 Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
@ 2025-01-16 14:55 ` Vik Fearing <vik@postgresfriends.org>
2025-01-16 15:58 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 14+ messages in thread
From: Vik Fearing @ 2025-01-16 14:55 UTC (permalink / raw)
To: Rajni Bobal <rajnibobal@gmail.com>; pgsql-bugs@lists.postgresql.org
On 16/01/2025 11:50, Rajni Bobal wrote:
> Hello Sir/Team,
>
> I have created a new bug report, #18776, with the updated problem
> statement. Kindly disregard bug #18774. Apologies for any
> inconvenience caused.
>
#18776 appears to have been discarded as a duplicate. In any case, no
one is going to fix a bug in 9.4 which has been EOL since 2020. I
suggest you upgrade to a supported version and re-report the bug if it
still exists.
--
Vik Fearing
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4
2025-01-16 10:50 Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
2025-01-16 14:55 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Vik Fearing <vik@postgresfriends.org>
@ 2025-01-16 15:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-01-22 15:54 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Tom Lane @ 2025-01-16 15:58 UTC (permalink / raw)
To: Vik Fearing <vik@postgresfriends.org>; +Cc: Rajni Bobal <rajnibobal@gmail.com>; pgsql-bugs@lists.postgresql.org
Vik Fearing <vik@postgresfriends.org> writes:
> #18776 appears to have been discarded as a duplicate. In any case, no
> one is going to fix a bug in 9.4 which has been EOL since 2020. I
> suggest you upgrade to a supported version and re-report the bug if it
> still exists.
If you do reproduce your problem on a still-in-support branch,
please re-file with a *self-contained* test case. It's impossible
for someone to reverse-engineer your problem from just the two
function definitions, with no table declarations, sample data,
calling sequence, or expected vs. actual results.
https://wiki.postgresql.org/wiki/Guide_to_reporting_problems
If it's difficult to cram all the details into our bug-reporting
form, personally I'd ignore the form and just send mail to
pgsql-bugs@lists.postgresql.org
regards, tom lane
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4
2025-01-16 10:50 Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
2025-01-16 14:55 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Vik Fearing <vik@postgresfriends.org>
2025-01-16 15:58 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Tom Lane <tgl@sss.pgh.pa.us>
@ 2025-01-22 15:54 ` Rajni Bobal <rajnibobal@gmail.com>
2025-01-22 16:43 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 David G. Johnston <david.g.johnston@gmail.com>
2025-01-23 15:02 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Greg Sabino Mullane <htamfids@gmail.com>
0 siblings, 2 replies; 14+ messages in thread
From: Rajni Bobal @ 2025-01-22 15:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Vik Fearing <vik@postgresfriends.org>; pgsql-bugs@lists.postgresql.org
Dear Team,
Thanks for your kind response.
KIndly find the attached scripts for creating the same issue on postgresql
16.3
Attachments :
1. creating of table (scrCreate.sql)
2. insert statements scripts (insert_statements_new.sql)
*Correct output function : *
CREATE OR REPLACE FUNCTION delete_from_table1(tblname text, sel_tblname
text, wherecon text, colname text, batchsize int)
RETURNS void AS
$$
BEGIN
-- Construct dynamic SQL for DELETE
EXECUTE 'DELETE FROM '
|| quote_ident(tblname)
|| ' WHERE '
|| quote_ident(colname)
|| ' IN (SELECT '
|| quote_ident(colname)
|| ' FROM '
|| quote_ident(sel_tblname)
|| ' WHERE '
|| wherecon
|| ' LIMIT '
|| batchsize || ')';
END;
$$ LANGUAGE plpgsql;
SELECT delete_from_table1('commandlog','commandlog','cmdid','cmdtime <
extract(epoch FROM (current_date - 15))*1000',500);
*Error output function : *
CREATE OR REPLACE FUNCTION delete_from_table2(tblname text, sel_tblname
text, wherecon text, colname text, batchsize int)
RETURNS void AS
$$
BEGIN
EXECUTE 'DELETE from ' || quote_ident(tblname) ||
' where $1 in (select $1 from ' || quote_ident(sel_tblname) ||
' where ' || wherecon || ' limit $2 ) '
USING colname, batchsize;
END;
$$ LANGUAGE plpgsql;
i am running the function as
SELECT delete_from_table2('commandlog','commandlog','cmdid','cmdtime <
extract(epoch FROM (current_date - 15))*1000',500);
On Thu, Jan 16, 2025 at 9:28 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Vik Fearing <vik@postgresfriends.org> writes:
> > #18776 appears to have been discarded as a duplicate. In any case, no
> > one is going to fix a bug in 9.4 which has been EOL since 2020. I
> > suggest you upgrade to a supported version and re-report the bug if it
> > still exists.
>
> If you do reproduce your problem on a still-in-support branch,
> please re-file with a *self-contained* test case. It's impossible
> for someone to reverse-engineer your problem from just the two
> function definitions, with no table declarations, sample data,
> calling sequence, or expected vs. actual results.
>
> https://wiki.postgresql.org/wiki/Guide_to_reporting_problems
>
> If it's difficult to cram all the details into our bug-reporting
> form, personally I'd ignore the form and just send mail to
> pgsql-bugs@lists.postgresql.org
>
> regards, tom lane
>
Attachments:
scrCreate.sql (application/sql, 686B) download
insert_statements_new.sql (application/sql, 29.0K) download
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4
2025-01-16 10:50 Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
2025-01-16 14:55 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Vik Fearing <vik@postgresfriends.org>
2025-01-16 15:58 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Tom Lane <tgl@sss.pgh.pa.us>
2025-01-22 15:54 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
@ 2025-01-22 16:43 ` David G. Johnston <david.g.johnston@gmail.com>
2025-01-23 08:57 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
1 sibling, 1 reply; 14+ messages in thread
From: David G. Johnston @ 2025-01-22 16:43 UTC (permalink / raw)
To: Rajni Bobal <rajnibobal@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Vik Fearing <vik@postgresfriends.org>; pgsql-bugs@lists.postgresql.org
You seem to have reversed which query you deem correct...
On Wed, Jan 22, 2025 at 8:59 AM Rajni Bobal <rajnibobal@gmail.com> wrote:
>
> || quote_ident(colname)
> || ' IN (SELECT '
> || quote_ident(colname)
> || ' FROM '
> || quote_ident(sel_tblname)
>
This deletes rows from the table depending upon finding the same value in
colname in the subquery.
> ' where $1 in (select $1 from ' || quote_ident(sel_tblname) ||
>
This deletes rows from the table depending only upon finding at least one
row in the subquery - the limit is basically pointless. Since all rows
return the value provided in $1, and $1 = $1.
So these indeed produce different results, as they should. The "error" one
is behaving exactly as it should. If it doesn't do what you want - which
is likely as it is a very unusual query - don't use it.
David J.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4
2025-01-16 10:50 Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
2025-01-16 14:55 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Vik Fearing <vik@postgresfriends.org>
2025-01-16 15:58 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Tom Lane <tgl@sss.pgh.pa.us>
2025-01-22 15:54 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
2025-01-22 16:43 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 David G. Johnston <david.g.johnston@gmail.com>
@ 2025-01-23 08:57 ` Rajni Bobal <rajnibobal@gmail.com>
2025-01-23 14:24 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Rajni Bobal @ 2025-01-23 08:57 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Vik Fearing <vik@postgresfriends.org>; pgsql-bugs@lists.postgresql.org
Hello SIr,
Please clarify about why $1 is not been replaced by 'colname' in
delete_from_table2
function.
regards,
On Wed, Jan 22, 2025 at 10:13 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
> You seem to have reversed which query you deem correct...
>
> On Wed, Jan 22, 2025 at 8:59 AM Rajni Bobal <rajnibobal@gmail.com> wrote:
>
>>
>> || quote_ident(colname)
>> || ' IN (SELECT '
>> || quote_ident(colname)
>> || ' FROM '
>> || quote_ident(sel_tblname)
>>
>
> This deletes rows from the table depending upon finding the same value in
> colname in the subquery.
>
>
>> ' where $1 in (select $1 from ' || quote_ident(sel_tblname) ||
>>
>
> This deletes rows from the table depending only upon finding at least one
> row in the subquery - the limit is basically pointless. Since all rows
> return the value provided in $1, and $1 = $1.
>
> So these indeed produce different results, as they should. The "error"
> one is behaving exactly as it should. If it doesn't do what you want -
> which is likely as it is a very unusual query - don't use it.
>
> David J.
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4
2025-01-16 10:50 Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
2025-01-16 14:55 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Vik Fearing <vik@postgresfriends.org>
2025-01-16 15:58 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Tom Lane <tgl@sss.pgh.pa.us>
2025-01-22 15:54 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
2025-01-22 16:43 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 David G. Johnston <david.g.johnston@gmail.com>
2025-01-23 08:57 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
@ 2025-01-23 14:24 ` David G. Johnston <david.g.johnston@gmail.com>
2025-01-23 14:54 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
2025-02-03 05:37 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
0 siblings, 2 replies; 14+ messages in thread
From: David G. Johnston @ 2025-01-23 14:24 UTC (permalink / raw)
To: Rajni Bobal <rajnibobal@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Vik Fearing <vik@postgresfriends.org>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Thursday, January 23, 2025, Rajni Bobal <rajnibobal@gmail.com> wrote:
> Hello SIr,
> Please clarify about why $1 is not been replaced by 'colname' in delete_from_table2
> function.
>
It has been replace with the string literal value ‘colname’, not a column
reference identifier “colname”. You cannot use positional parameters to
supply identifiers. Structure must be known at parse time.
David J.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4
2025-01-16 10:50 Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
2025-01-16 14:55 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Vik Fearing <vik@postgresfriends.org>
2025-01-16 15:58 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Tom Lane <tgl@sss.pgh.pa.us>
2025-01-22 15:54 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
2025-01-22 16:43 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 David G. Johnston <david.g.johnston@gmail.com>
2025-01-23 08:57 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
2025-01-23 14:24 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 David G. Johnston <david.g.johnston@gmail.com>
@ 2025-01-23 14:54 ` Rajni Bobal <rajnibobal@gmail.com>
2025-01-23 15:03 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 14+ messages in thread
From: Rajni Bobal @ 2025-01-23 14:54 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Vik Fearing <vik@postgresfriends.org>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Hello Sir,
Colname is variable here, it value should be replaced in param string ($1).
Thanks and regards.
On Thu, 23 Jan 2025 at 7:54 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Thursday, January 23, 2025, Rajni Bobal <rajnibobal@gmail.com> wrote:
>
>> Hello SIr,
>> Please clarify about why $1 is not been replaced by 'colname' in delete_from_table2
>> function.
>>
>
> It has been replace with the string literal value ‘colname’, not a column
> reference identifier “colname”. You cannot use positional parameters to
> supply identifiers. Structure must be known at parse time.
>
> David J.
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4
2025-01-16 10:50 Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
2025-01-16 14:55 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Vik Fearing <vik@postgresfriends.org>
2025-01-16 15:58 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Tom Lane <tgl@sss.pgh.pa.us>
2025-01-22 15:54 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
2025-01-22 16:43 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 David G. Johnston <david.g.johnston@gmail.com>
2025-01-23 08:57 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
2025-01-23 14:24 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 David G. Johnston <david.g.johnston@gmail.com>
2025-01-23 14:54 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
@ 2025-01-23 15:03 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: David G. Johnston @ 2025-01-23 15:03 UTC (permalink / raw)
To: Rajni Bobal <rajnibobal@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Vik Fearing <vik@postgresfriends.org>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Thu, Jan 23, 2025 at 7:54 AM Rajni Bobal <rajnibobal@gmail.com> wrote:
> Colname is variable here, it value should be replaced in param string
> ($1).
>
Regardless of the specific characters it is replaced with the important
point is that your first query introduces an identifier and the second a
string literal. This is why they behave differently.
There is no bug here - the queries are doing what they are supposed to.
The use of string literals makes the second filter always evaluate to true
so long as at least one row is returned and the value of $1 is not null (in
this case your where clause is "cmdid' IN ('cmdid', 'cmdid', ...)". Thus
it deletes all rows in the table.
David J.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4
2025-01-16 10:50 Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
2025-01-16 14:55 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Vik Fearing <vik@postgresfriends.org>
2025-01-16 15:58 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Tom Lane <tgl@sss.pgh.pa.us>
2025-01-22 15:54 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
2025-01-22 16:43 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 David G. Johnston <david.g.johnston@gmail.com>
2025-01-23 08:57 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
2025-01-23 14:24 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 David G. Johnston <david.g.johnston@gmail.com>
@ 2025-02-03 05:37 ` Rajni Bobal <rajnibobal@gmail.com>
2025-02-03 05:50 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 14+ messages in thread
From: Rajni Bobal @ 2025-02-03 05:37 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
Hello Sir,
Thanks for the reply,
as per the documentation, following query execute successfully :
EXECUTE 'SELECT count(*) FROM '
|| quote_ident(tabname)
|| ' WHERE inserted_by = $1 AND inserted <= $2'
INTO c
USING checked_user, checked_date;
HERE, $1 and $2 are strings and its values are substituted in the
query. Please clarify, may be i am missing something.
Regards,
Rajni Bobal
On Thu, Jan 23, 2025 at 7:54 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Thursday, January 23, 2025, Rajni Bobal <rajnibobal@gmail.com> wrote:
>
>> Hello SIr,
>> Please clarify about why $1 is not been replaced by 'colname' in delete_from_table2
>> function.
>>
>
> It has been replace with the string literal value ‘colname’, not a column
> reference identifier “colname”. You cannot use positional parameters to
> supply identifiers. Structure must be known at parse time.
>
> David J.
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4
2025-01-16 10:50 Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
2025-01-16 14:55 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Vik Fearing <vik@postgresfriends.org>
2025-01-16 15:58 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Tom Lane <tgl@sss.pgh.pa.us>
2025-01-22 15:54 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
2025-01-22 16:43 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 David G. Johnston <david.g.johnston@gmail.com>
2025-01-23 08:57 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
2025-01-23 14:24 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 David G. Johnston <david.g.johnston@gmail.com>
2025-02-03 05:37 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
@ 2025-02-03 05:50 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: David G. Johnston @ 2025-02-03 05:50 UTC (permalink / raw)
To: Rajni Bobal <rajnibobal@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Sunday, February 2, 2025, Rajni Bobal <rajnibobal@gmail.com> wrote:
>
>
> as per the documentation, following query execute successfully :
>
> EXECUTE 'SELECT count(*) FROM '
> || quote_ident(tabname)
> || ' WHERE inserted_by = $1 AND inserted <= $2'
> INTO c
> USING checked_user, checked_date;
>
> HERE, $1 and $2 are strings and its values are substituted in the query. Please clarify, may be i am missing something.
>
>
Ask yourself why “quote_ident(tabname)” is used to insert the table name
into the SQL string via concatenation instead of also using a number
placeholder.
David J.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4
2025-01-16 10:50 Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
2025-01-16 14:55 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Vik Fearing <vik@postgresfriends.org>
2025-01-16 15:58 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Tom Lane <tgl@sss.pgh.pa.us>
2025-01-22 15:54 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
@ 2025-01-23 15:02 ` Greg Sabino Mullane <htamfids@gmail.com>
2025-02-03 05:32 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
1 sibling, 1 reply; 14+ messages in thread
From: Greg Sabino Mullane @ 2025-01-23 15:02 UTC (permalink / raw)
To: Rajni Bobal <rajnibobal@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Vik Fearing <vik@postgresfriends.org>; pgsql-bugs@lists.postgresql.org, "David G. Johnston" <david.g.johnston@gmail.com>
On Wed, Jan 22, 2025 at 10:59 AM Rajni Bobal <rajnibobal@gmail.com> wrote:
> SELECT delete_from_table1('commandlog','commandlog','cmdid','cmdtime <
> extract(epoch FROM (current_date - 15))*1000',500);
>
(putting aside many other issues for now)
It would help to see the exact output you are getting. The query as written
above cannot work with the functions you gave, which have the column name
as the FOURTH parameter, and the where clause as the THIRD.
EXECUTE 'DELETE from ' || quote_ident(tblname) ||
> ' where $1 in (select $1 from ' || quote_ident(sel_tblname) ||
> ' where ' || wherecon || ' limit $2 ) '
> USING colname, batchsize;
>
Use FORMAT instead:
EXECUTE FORMAT(
'DELETE from %I WHERE %I IN (SELECT %I FROM %I WHERE %s LIMIT %s)',
tblname, colname, colname, sel_tblname, wherecon, batchsize
);
Cheers,
Greg
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4
2025-01-16 10:50 Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
2025-01-16 14:55 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Vik Fearing <vik@postgresfriends.org>
2025-01-16 15:58 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Tom Lane <tgl@sss.pgh.pa.us>
2025-01-22 15:54 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
2025-01-23 15:02 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Greg Sabino Mullane <htamfids@gmail.com>
@ 2025-02-03 05:32 ` Rajni Bobal <rajnibobal@gmail.com>
2025-02-03 11:10 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Álvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 14+ messages in thread
From: Rajni Bobal @ 2025-02-03 05:32 UTC (permalink / raw)
To: Greg Sabino Mullane <htamfids@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
Hello Sir,
Please clarify the use of %I will not be substituted by the string literal
'colname' rather its value will be substituted dynamically.
regards,
Rajni bobal
On Thu, Jan 23, 2025 at 8:33 PM Greg Sabino Mullane <htamfids@gmail.com>
wrote:
> On Wed, Jan 22, 2025 at 10:59 AM Rajni Bobal <rajnibobal@gmail.com> wrote:
>
>> SELECT delete_from_table1('commandlog','commandlog','cmdid','cmdtime <
>> extract(epoch FROM (current_date - 15))*1000',500);
>>
>
> (putting aside many other issues for now)
>
> It would help to see the exact output you are getting. The query as
> written above cannot work with the functions you gave, which have the
> column name as the FOURTH parameter, and the where clause as the THIRD.
>
>
> EXECUTE 'DELETE from ' || quote_ident(tblname) ||
>> ' where $1 in (select $1 from ' || quote_ident(sel_tblname) ||
>> ' where ' || wherecon || ' limit $2 ) '
>> USING colname, batchsize;
>>
>
> Use FORMAT instead:
>
> EXECUTE FORMAT(
> 'DELETE from %I WHERE %I IN (SELECT %I FROM %I WHERE %s LIMIT %s)',
> tblname, colname, colname, sel_tblname, wherecon, batchsize
> );
>
> Cheers,
> Greg
>
> --
> Crunchy Data - https://www.crunchydata.com
> Enterprise Postgres Software Products & Tech Support
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4
2025-01-16 10:50 Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
2025-01-16 14:55 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Vik Fearing <vik@postgresfriends.org>
2025-01-16 15:58 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Tom Lane <tgl@sss.pgh.pa.us>
2025-01-22 15:54 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
2025-01-23 15:02 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Greg Sabino Mullane <htamfids@gmail.com>
2025-02-03 05:32 ` Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
@ 2025-02-03 11:10 ` Álvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 0 replies; 14+ messages in thread
From: Álvaro Herrera @ 2025-02-03 11:10 UTC (permalink / raw)
To: Rajni Bobal <rajnibobal@gmail.com>; +Cc: Greg Sabino Mullane <htamfids@gmail.com>; pgsql-bugs@lists.postgresql.org
On 2025-Feb-03, Rajni Bobal wrote:
> Please clarify the use of %I will not be substituted by the string literal
> 'colname' rather its value will be substituted dynamically.
Yes, it is expanded dynamically to the _value_ of the variable
colname, not to the literal 'colname'. If you wanted to expand a
string literal there, you would use something like
SELECT format('some text .. %I ... more', 'colname');
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2025-02-03 11:10 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-01-16 10:50 Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 Rajni Bobal <rajnibobal@gmail.com>
2025-01-16 14:55 ` Vik Fearing <vik@postgresfriends.org>
2025-01-16 15:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-01-22 15:54 ` Rajni Bobal <rajnibobal@gmail.com>
2025-01-22 16:43 ` David G. Johnston <david.g.johnston@gmail.com>
2025-01-23 08:57 ` Rajni Bobal <rajnibobal@gmail.com>
2025-01-23 14:24 ` David G. Johnston <david.g.johnston@gmail.com>
2025-01-23 14:54 ` Rajni Bobal <rajnibobal@gmail.com>
2025-01-23 15:03 ` David G. Johnston <david.g.johnston@gmail.com>
2025-02-03 05:37 ` Rajni Bobal <rajnibobal@gmail.com>
2025-02-03 05:50 ` David G. Johnston <david.g.johnston@gmail.com>
2025-01-23 15:02 ` Greg Sabino Mullane <htamfids@gmail.com>
2025-02-03 05:32 ` Rajni Bobal <rajnibobal@gmail.com>
2025-02-03 11:10 ` Álvaro Herrera <alvherre@alvh.no-ip.org>
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