Horton

Thread

  1. 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-16T10:50:14Z

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

  2. 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-16T14:55:25Z

    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

  3. 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-16T15:58:28Z

    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

  4. 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-22T15:54:56Z

    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
>

  5. 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-22T16:43:07Z

    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.

  6. 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-23T08:57:07Z

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

  7. 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-23T14:24:47Z

    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.

  8. 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-23T14:54:36Z

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

  9. 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-01-23T15:02:56Z

    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

  10. 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-23T15:03:54Z

    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.

  11. 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-03T05:32:18Z

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

  12. 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-03T05:37:25Z

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

  13. 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-03T05:50:56Z

    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.

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

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2025-02-03T11:10:51Z

    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/