Обсуждение: Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4
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;
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
Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4
От
"David G. Johnston"
Дата:
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:
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.
Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4
От
"David G. Johnston"
Дата:
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 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.
Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4
От
Greg Sabino Mullane
Дата:
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
);
'DELETE from %I WHERE %I IN (SELECT %I FROM %I WHERE %s LIMIT %s)',
tblname, colname, colname, sel_tblname, wherecon, batchsize
);
Cheers,
Greg
Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4
От
"David G. Johnston"
Дата:
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.