Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4

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

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

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:

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.

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:

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.

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 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

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.

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:

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 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.

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/