Horton

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add some sanity checks in executor for query ID reporting

  2. Fix psql's \sf and \ef for new-style SQL functions.

  1. INFORMATION_SCHEMA.routines column routine_definition does not show the source

    Erki Eessaar <erki.eessaar@taltech.ee> — 2021-11-03T14:42:16Z

    Hello

PostgreSQL 14 added the feature: "Allow SQL-language functions<https://www.postgresql.org/docs/14/sql-createfunction.html> and procedures<https://www.postgresql.org/docs/14/sql-createprocedure.html> to use SQL-standard function bodies."

Unfortunately the bodies of such routines are not visible in the column routine_definition of the view INFORMATION_SCHEMA.routines. In case of these routines the field contains the empty string instead of the routine body.


Please observe the difference:

START TRANSACTION;

CREATE OR REPLACE FUNCTION f_fahrenheit_celsius_old(numeric) RETURNS numeric AS $$
SELECT round((($1 - 32.0) * 5.0 / 9.0),3); $$ LANGUAGE sql
IMMUTABLE STRICT LEAKPROOF;

CREATE OR REPLACE FUNCTION f_fahrenheit_celsius_new(numeric) RETURNS numeric
LANGUAGE sql IMMUTABLE STRICT LEAKPROOF
RETURN round((($1 - 32.0) * 5.0 / 9.0),3);

SELECT
routine_schema,
routine_name,
routine_definition
FROM INFORMATION_SCHEMA.routines
WHERE routine_name IN ('f_fahrenheit_celsius_old','f_fahrenheit_celsius_new');

ROLLBACK;

Best regards
Erki Eessaar

  2. Re: INFORMATION_SCHEMA.routines column routine_definition does not show the source

    David G. Johnston <david.g.johnston@gmail.com> — 2021-11-03T15:21:34Z

    On Wed, Nov 3, 2021 at 7:49 AM Erki Eessaar <erki.eessaar@taltech.ee> wrote:

> Unfortunately the bodies of such routines are not visible in the column
> routine_definition of the view INFORMATION_SCHEMA.routines. In case of
> these routines the field contains the empty string instead of the routine
> body.
>

Thanks for the report!

The information schema query consults pg_proc.prosrc directly instead of
calling pg_get_functiondef(...) (which didn't exist when the original query
was written, and hasn't been wrong until now).

David J.

  3. Re: INFORMATION_SCHEMA.routines column routine_definition does not show the source

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-11-03T16:46:38Z

    "David G. Johnston" <david.g.johnston@gmail.com> writes:
> The information schema query consults pg_proc.prosrc directly instead of
> calling pg_get_functiondef(...) (which didn't exist when the original query
> was written, and hasn't been wrong until now).

pg_get_functiondef would produce more than we want, but it looks like
pg_get_function_sqlbody() would do.

BTW, while researching this I noted the header comment for
pg_get_functiondef:

 * Note: if you change the output format of this function, be careful not
 * to break psql's rules (in \ef and \sf) for identifying the start of the
 * function body.  To wit: the function body starts on a line that begins
 * with "AS ", and no preceding line will look like that.

Needless to say, the SQL-function-body patch has ignored this advice
totally.  At the very least this comment needs to be adjusted, but
I wonder if it's not telling us that \ef and/or \sf are broken.

			regards, tom lane

  4. Re: INFORMATION_SCHEMA.routines column routine_definition does not show the source

    David G. Johnston <david.g.johnston@gmail.com> — 2022-12-02T00:00:05Z

    Decided to ping this as I don't recall or see it getting patched and the
recent discussion regarding the prosrc field.

On Wed, Nov 3, 2021 at 9:46 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > The information schema query consults pg_proc.prosrc directly instead of
> > calling pg_get_functiondef(...) (which didn't exist when the original
> query
> > was written, and hasn't been wrong until now).
>
> pg_get_functiondef would produce more than we want, but it looks like
> pg_get_function_sqlbody() would do.
>
> BTW, while researching this I noted the header comment for
> pg_get_functiondef:
>
>  * Note: if you change the output format of this function, be careful not
>  * to break psql's rules (in \ef and \sf) for identifying the start of the
>  * function body.  To wit: the function body starts on a line that begins
>  * with "AS ", and no preceding line will look like that.
>
> Needless to say, the SQL-function-body patch has ignored this advice
> totally.  At the very least this comment needs to be adjusted, but
> I wonder if it's not telling us that \ef and/or \sf are broken.
>
>

  5. Re: INFORMATION_SCHEMA.routines column routine_definition does not show the source

    Tom Lane <tgl@sss.pgh.pa.us> — 2022-12-02T01:28:39Z

    "David G. Johnston" <david.g.johnston@gmail.com> writes:
> Decided to ping this as I don't recall or see it getting patched and the
> recent discussion regarding the prosrc field.

>> * Note: if you change the output format of this function, be careful not
>> * to break psql's rules (in \ef and \sf) for identifying the start of the
>> * function body.  To wit: the function body starts on a line that begins
>> * with "AS ", and no preceding line will look like that.
>> 
>> Needless to say, the SQL-function-body patch has ignored this advice
>> totally.  At the very least this comment needs to be adjusted, but
>> I wonder if it's not telling us that \ef and/or \sf are broken.

Indeed, if you experiment with "\sf+" or "\ef" with a line number,
those features fail miserably for new-style SQL functions.

We could partially fix that by teaching psql to also recognize
"BEGIN" as the start of the function body.  I say "partially"
because the whole point of that line-numbers feature is so that
you can figure out which line an error report is complaining about.
However, SQL functions don't report in terms of line numbers,
and never have, so maybe that's moot.  It's fortunate that they
don't, because the reconstructed function text is likely to have
different line breaks than the original.

			regards, tom lane

  6. Re: INFORMATION_SCHEMA.routines column routine_definition does not show the source

    Tom Lane <tgl@sss.pgh.pa.us> — 2022-12-02T02:41:30Z

    I wrote:
> We could partially fix that by teaching psql to also recognize
> "BEGIN" as the start of the function body.

Looks like we need "RETURN " too, so more or less as attached.

(I didn't bother with a test case yet.  \sf wouldn't be too
hard to exercise, but I wonder how we could test \ef portably.)

			regards, tom lane

  7. INFORMATION_SCHEMA.routines column routine_definition does not show the source

    David G. Johnston <david.g.johnston@gmail.com> — 2024-09-18T05:42:53Z

    Ping again as a Reddit poster just pointed this out.

On Thursday, December 1, 2022, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> I wrote:
> > We could partially fix that by teaching psql to also recognize
> > "BEGIN" as the start of the function body.
>
> Looks like we need "RETURN " too, so more or less as attached.
>
> (I didn't bother with a test case yet.  \sf wouldn't be too
> hard to exercise, but I wonder how we could test \ef portably.)
>
>
>
Not sure I bring much to patch review here; but is the plan to commit this
first then update information_schema or do both in one go?

David J.

  8. Re: INFORMATION_SCHEMA.routines column routine_definition does not show the source

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-09-18T05:51:23Z

    "David G. Johnston" <david.g.johnston@gmail.com> writes:
> Ping again as a Reddit poster just pointed this out.

Huh?  The patch you're replying to was committed two years ago.

			regards, tom lane

  9. Re: INFORMATION_SCHEMA.routines column routine_definition does not show the source

    David G. Johnston <david.g.johnston@gmail.com> — 2024-09-18T06:05:04Z

    On Tuesday, September 17, 2024, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > Ping again as a Reddit poster just pointed this out.
>
> Huh?  The patch you're replying to was committed two years ago.
>
>
The patch I’m replying to doesn’t fix the bug reported on this thread
though…which still exists.

https://github.com/postgres/postgres/blob/24f5205948093a96edf8213294b3d585ac3fe1fb/src/backend/catalog/information_schema.sql#L1593

             CASE WHEN pg_has_role(p.proowner, 'USAGE') THEN p.prosrc ELSE
null END
             AS character_data) AS routine_definition,

David J.