Re: plpgsql_check_function issue after upgrade
po 5. 12. 2022 v 8:42 odesílatel shashidhar Reddy <
shashidharreddy...@gmail.com> napsal:
> Pavel,
>
> Below is the back trace result
>
> libffi6-dbg libgcc1-dbg libkrb5-dbg libstdc++6-8-dbg libxml2-dbg
> postgresql-13-dbgsym postgresql-13-pldeb
> ugger-dbgsym zlib1g-dbg
>
I am sorry, I don't anything
>
> On Mon, Dec 5, 2022 at 11:28 AM Pavel Stehule
> wrote:
>
>> Hi
>>
>>
>> po 5. 12. 2022 v 6:49 odesílatel shashidhar Reddy <
>> shashidharreddy...@gmail.com> napsal:
>>
>>> Pavel,
>>>
>>> Where can I get list-dbgsym-packages.sh script?
>>>
>>
>>
>> https://manpages.debian.org/testing/debian-goodies/find-dbgsym-packages.1.en.html
>>
>> https://wiki.ubuntu.com/DebuggingProgramCrash
>>
>> please, don't use top-post style in this mailing list
>> https://en.wikipedia.org/wiki/Posting_style
>>
>> Regards
>>
>> Pavel
>>
>>
>>
>>> On Wed, Nov 30, 2022 at 10:04 AM Pavel Stehule
>>> wrote:
>>>
st 30. 11. 2022 v 5:28 odesílatel Pavel Stehule <
pavel.steh...@gmail.com> napsal:
>
>
> st 30. 11. 2022 v 1:38 odesílatel shashidhar Reddy <
> shashidharreddy...@gmail.com> napsal:
>
>> I have tried updating after upgrade but that wasn't working, so I
>> have dropped and recreated the extension.
>> Now it is crashing every time when we call the function.
>>
>
> what is version od plpgsql_check on Postgres 12, what is version of
> plpgsql_check on Postgres 13 (with version of minor release)?
>
> Can you send backtrace?
> https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD
>
Do you have installed some other extensions?
>
>
>
>
>
>>
>> On Tue, 29 Nov, 2022, 9:58 pm Pavel Stehule,
>> wrote:
>>
>>>
>>>
>>> út 29. 11. 2022 v 16:37 odesílatel shashidhar Reddy <
>>> shashidharreddy...@gmail.com> napsal:
>>>
Plogsql check version is 2.2 and one more finding is before calling
the function if we drop and recreate the plpgsql_check extension there
is
no issue, but each time we can't drop and create.
>>>
>>> Maybe you need to run ALTER EXTENSION plpgsql_check UPDATE before
>>> first usage in pg 13
>>>
>>> If the extension works after re-installation, then the problem is
>>> not in an extension.
>>>
>>>
>>>
On Tue, 29 Nov, 2022, 7:19 pm shashidhar Reddy, <
shashidharreddy...@gmail.com> wrote:
> Hello Pavel,
>
> This is the function causing the issue on all servers, and also i
> noticed when I use *plpgsql_check_function *in any function I am
> facing the same issue.
>
>
> On Tue, Nov 29, 2022 at 6:43 PM Pavel Stehule <
> pavel.steh...@gmail.com> wrote:
>
>> Hi
>>
>>
>> út 29. 11. 2022 v 13:49 odesílatel shashidhar Reddy <
>> shashidharreddy...@gmail.com> napsal:
>>
>>> Hello,
>>>
>>> Recently we have upgraded postgres from version 12 to 13 and
>>> upgraded plpgsql_check to the latest version but after upgrade
>>> when calling the below function causing postgres restart .
>>>
>>> CREATE OR REPLACE FUNCTION pro.po_check(
>>> )
>>> RETURNS void
>>> LANGUAGE 'plpgsql'
>>> COST 100
>>> VOLATILE SECURITY DEFINER PARALLEL UNSAFE
>>> AS $BODY$
>>> DECLARE
>>> BEGIN
>>>
>>> PERFORM p.oid, n.nspname, p.proname,
>>> plpgsql_check_function(p.oid)
>>> FROM pg_catalog.pg_namespace n
>>> JOIN pg_catalog.pg_proc p ON pronamespace = n.oid
>>> JOIN pg_catalog.pg_language l ON p.prolang = l.oid
>>> WHERE l.lanname = 'plpgsql' AND p.prorettype <> 2279
>>> and upper(n.nspname) like upper('Pro');
>>>
>>> END;
>>> $BODY$;
>>>
>>> and the error in syslogs shows
>>> kernel: [93631.415790] postgres[86383]: segfault at 80 ip
>>> 7f07f3e3eefd sp 7fffcf1db500 error 4 in
>>> plpgsql_check.so[7f07f3e2e000+34000]
>>>
>>
>> it can be a bug in plpgsql_check. But I am not able to fix it
>> without some information. Can you send the reproducer (minimal
>> example of
>> your code, that reproduce this error)?
>>
>> Regards
>>
>> Pavel
>>
>>>
>>> --
>>> Shashidhar
>>>
>>
>
> --
> Shashidhar
>
>>>
>>> --
>>> Shashidhar
>>>
>>
>
> --
> Shashidhar
>
Re: print in plpython not appearing in logs
Hi Tom:
>> I'm having problems wherein my print() statements inside my plpython stored
>> proc are not appearing in postgresql log. I tried setting the file=sys.stderr
> Hmm. I can tell you that with "logging_collector = on", I would only expect
> the logs to capture stderr output, not stdout. So it makes
> sense to me that plain "print" would disappear into the bit bucket. But if
> you specify stderr output, it ought to work. I don't know
> enough Python to know why it's not working, but it seems to me this is
> primarily a Python question not a Postgres question. Maybe you need
> an explicit fflush-equivalent step? Dunno.
> regards, tom lane
As usual, thank you for pointing out where problem lies.
I made it work by adding flush=True flag to the print() statement.
print("oink oink", file=sys.stderr, flush=True)
I was under the wrong impression that output to stderr are automatically
flushed.
Thank you once again.
Regards,
Ludwig
Re: plpgsql_check_function issue after upgrade
Is there any way to get the older version 1.1 of plpgsql_check to install
it?
On Mon, 5 Dec, 2022, 1:36 pm Pavel Stehule, wrote:
>
>
> po 5. 12. 2022 v 8:42 odesílatel shashidhar Reddy <
> shashidharreddy...@gmail.com> napsal:
>
>> Pavel,
>>
>> Below is the back trace result
>>
>> libffi6-dbg libgcc1-dbg libkrb5-dbg libstdc++6-8-dbg libxml2-dbg
>> postgresql-13-dbgsym postgresql-13-pldeb
>> ugger-dbgsym zlib1g-dbg
>>
>
> I am sorry, I don't anything
>
>
>
>>
>> On Mon, Dec 5, 2022 at 11:28 AM Pavel Stehule
>> wrote:
>>
>>> Hi
>>>
>>>
>>> po 5. 12. 2022 v 6:49 odesílatel shashidhar Reddy <
>>> shashidharreddy...@gmail.com> napsal:
>>>
Pavel,
Where can I get list-dbgsym-packages.sh script?
>>>
>>>
>>> https://manpages.debian.org/testing/debian-goodies/find-dbgsym-packages.1.en.html
>>>
>>> https://wiki.ubuntu.com/DebuggingProgramCrash
>>>
>>> please, don't use top-post style in this mailing list
>>> https://en.wikipedia.org/wiki/Posting_style
>>>
>>> Regards
>>>
>>> Pavel
>>>
>>>
>>>
On Wed, Nov 30, 2022 at 10:04 AM Pavel Stehule
wrote:
>
>
> st 30. 11. 2022 v 5:28 odesílatel Pavel Stehule <
> pavel.steh...@gmail.com> napsal:
>
>>
>>
>> st 30. 11. 2022 v 1:38 odesílatel shashidhar Reddy <
>> shashidharreddy...@gmail.com> napsal:
>>
>>> I have tried updating after upgrade but that wasn't working, so I
>>> have dropped and recreated the extension.
>>> Now it is crashing every time when we call the function.
>>>
>>
>> what is version od plpgsql_check on Postgres 12, what is version of
>> plpgsql_check on Postgres 13 (with version of minor release)?
>>
>> Can you send backtrace?
>> https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD
>>
>
> Do you have installed some other extensions?
>
>
>
>>
>>
>>
>>
>>
>>>
>>> On Tue, 29 Nov, 2022, 9:58 pm Pavel Stehule, <
>>> pavel.steh...@gmail.com> wrote:
>>>
út 29. 11. 2022 v 16:37 odesílatel shashidhar Reddy <
shashidharreddy...@gmail.com> napsal:
> Plogsql check version is 2.2 and one more finding is before
> calling the function if we drop and recreate the plpgsql_check
> extension
> there is no issue, but each time we can't drop and create.
>
Maybe you need to run ALTER EXTENSION plpgsql_check UPDATE before
first usage in pg 13
If the extension works after re-installation, then the problem is
not in an extension.
> On Tue, 29 Nov, 2022, 7:19 pm shashidhar Reddy, <
> shashidharreddy...@gmail.com> wrote:
>
>> Hello Pavel,
>>
>> This is the function causing the issue on all servers, and also i
>> noticed when I use *plpgsql_check_function *in any function I am
>> facing the same issue.
>>
>>
>> On Tue, Nov 29, 2022 at 6:43 PM Pavel Stehule <
>> pavel.steh...@gmail.com> wrote:
>>
>>> Hi
>>>
>>>
>>> út 29. 11. 2022 v 13:49 odesílatel shashidhar Reddy <
>>> shashidharreddy...@gmail.com> napsal:
>>>
Hello,
Recently we have upgraded postgres from version 12 to 13 and
upgraded plpgsql_check to the latest version but
after upgrade when calling the below function causing postgres
restart .
CREATE OR REPLACE FUNCTION pro.po_check(
)
RETURNS void
LANGUAGE 'plpgsql'
COST 100
VOLATILE SECURITY DEFINER PARALLEL UNSAFE
AS $BODY$
DECLARE
BEGIN
PERFORM p.oid, n.nspname, p.proname,
plpgsql_check_function(p.oid)
FROM pg_catalog.pg_namespace n
JOIN pg_catalog.pg_proc p ON pronamespace = n.oid
JOIN pg_catalog.pg_language l ON p.prolang = l.oid
WHERE l.lanname = 'plpgsql' AND p.prorettype <> 2279
and upper(n.nspname) like upper('Pro');
END;
$BODY$;
and the error in syslogs shows
kernel: [93631.415790] postgres[86383]: segfault at 80 ip
7f07f3e3eefd sp 7fffcf1db500 error 4 in
plpgsql_check.so[7f07f3e2e000+34000]
>>>
>>> it can be a bug in plpgsql_check. But I am not able to fix it
>>> without some information. Can you send the reproducer (minimal
>>> example of
>>> your code, that reproduce this error)?
>>>
>>> Regards
>>>
>>> Pavel
>>>
--
Shashidhar
>
Re: plpgsql_check_function issue after upgrade
Am Mon, Dec 05, 2022 at 03:54:28PM +0530 schrieb shashidhar Reddy:
> Is there any way to get the older version 1.1 of plpgsql_check to install
> it?
Is there any way to get you to respect the kind requests to
follow this list's posting customs as expressed by the very
people you want to help you ?
Such as not to top post.
Best regards,
Karsten
> On Mon, 5 Dec, 2022, 1:36 pm Pavel Stehule, wrote:
>
> >
> >
> > po 5. 12. 2022 v 8:42 odesílatel shashidhar Reddy <
> > shashidharreddy...@gmail.com> napsal:
> >
> >> Pavel,
> >>
> >> Below is the back trace result
> >>
> >> libffi6-dbg libgcc1-dbg libkrb5-dbg libstdc++6-8-dbg libxml2-dbg
> >> postgresql-13-dbgsym postgresql-13-pldeb
> >> ugger-dbgsym zlib1g-dbg
> >>
> >
> > I am sorry, I don't anything
> >
> >
> >
> >>
> >> On Mon, Dec 5, 2022 at 11:28 AM Pavel Stehule
> >> wrote:
> >>
> >>> Hi
> >>>
> >>>
> >>> po 5. 12. 2022 v 6:49 odesílatel shashidhar Reddy <
> >>> shashidharreddy...@gmail.com> napsal:
> >>>
> Pavel,
>
> Where can I get list-dbgsym-packages.sh script?
>
> >>>
> >>>
> >>> https://manpages.debian.org/testing/debian-goodies/find-dbgsym-packages.1.en.html
> >>>
> >>> https://wiki.ubuntu.com/DebuggingProgramCrash
> >>>
> >>> please, don't use top-post style in this mailing list
> >>> https://en.wikipedia.org/wiki/Posting_style
> >>>
> >>> Regards
> >>>
> >>> Pavel
> >>>
> >>>
> >>>
> On Wed, Nov 30, 2022 at 10:04 AM Pavel Stehule
> wrote:
>
> >
> >
> > st 30. 11. 2022 v 5:28 odesílatel Pavel Stehule <
> > pavel.steh...@gmail.com> napsal:
> >
> >>
> >>
> >> st 30. 11. 2022 v 1:38 odesílatel shashidhar Reddy <
> >> shashidharreddy...@gmail.com> napsal:
> >>
> >>> I have tried updating after upgrade but that wasn't working, so I
> >>> have dropped and recreated the extension.
> >>> Now it is crashing every time when we call the function.
> >>>
> >>
> >> what is version od plpgsql_check on Postgres 12, what is version of
> >> plpgsql_check on Postgres 13 (with version of minor release)?
> >>
> >> Can you send backtrace?
> >> https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD
> >>
> >
> > Do you have installed some other extensions?
> >
> >
> >
> >>
> >>
> >>
> >>
> >>
> >>>
> >>> On Tue, 29 Nov, 2022, 9:58 pm Pavel Stehule, <
> >>> pavel.steh...@gmail.com> wrote:
> >>>
>
>
> út 29. 11. 2022 v 16:37 odesílatel shashidhar Reddy <
> shashidharreddy...@gmail.com> napsal:
>
> > Plogsql check version is 2.2 and one more finding is before
> > calling the function if we drop and recreate the plpgsql_check
> > extension
> > there is no issue, but each time we can't drop and create.
> >
>
> Maybe you need to run ALTER EXTENSION plpgsql_check UPDATE before
> first usage in pg 13
>
> If the extension works after re-installation, then the problem is
> not in an extension.
>
>
>
> > On Tue, 29 Nov, 2022, 7:19 pm shashidhar Reddy, <
> > shashidharreddy...@gmail.com> wrote:
> >
> >> Hello Pavel,
> >>
> >> This is the function causing the issue on all servers, and also i
> >> noticed when I use *plpgsql_check_function *in any function I am
> >> facing the same issue.
> >>
> >>
> >> On Tue, Nov 29, 2022 at 6:43 PM Pavel Stehule <
> >> pavel.steh...@gmail.com> wrote:
> >>
> >>> Hi
> >>>
> >>>
> >>> út 29. 11. 2022 v 13:49 odesílatel shashidhar Reddy <
> >>> shashidharreddy...@gmail.com> napsal:
> >>>
> Hello,
>
> Recently we have upgraded postgres from version 12 to 13 and
> upgraded plpgsql_check to the latest version but
> after upgrade when calling the below function causing postgres
> restart .
>
> CREATE OR REPLACE FUNCTION pro.po_check(
> )
> RETURNS void
> LANGUAGE 'plpgsql'
> COST 100
> VOLATILE SECURITY DEFINER PARALLEL UNSAFE
> AS $BODY$
> DECLARE
> BEGIN
>
> PERFORM p.oid, n.nspname, p.proname,
> plpgsql_check_function(p.oid)
> FROM pg_catalog.pg_namespace n
> JOIN pg_catalog.pg_proc p ON pronamespace = n.oid
> JOIN pg_catalog.pg_language l ON p.prolang = l.oid
> WHERE l.lanname = 'plpgsql' AND p.prorettype <> 2279
> and upper(n.nspname) like upper('Pro');
>
> END;
> $BODY$;
>
> and the error in syslogs sh
Re: Stored procedure code no longer stored in v14 and v15, changed behaviour
Hello Jeremy, Yes and no For Stored Functions, the “sql_body” can either be a block with BEGIN ATOMIC ... END or a single statement RETURN expression; For a Stored Procedure, the “sql_body” is always the block. Using “language SQL” and an “sql_body” (as per documentation) is certainly easier (no string constant) and more compatible with the SQL standard. But if you do so, you loose the possibility to retrieve your actual code from the database, which, IMO, is very counter intuitive. Lots of people use, how shall we call it, ‘interactive development’ in tools (like the one we create) and instead of running a saved and modified script, you load the objects from the database, you modify it in a database development tool, test, modify, test etc. I understand that dependency tracking is useful, and automatic object modification when doing object renaming is nice, but personally, that would be a corner case compared to day-to-day development of stored code. With regards, Martijn Tonies Upscene Productions https://www.upscene.com Database Workbench - developer tool for Oracle, SQL Server, PostgreSQL, MySQL, InterBase, NexusDB and Firebird. From: Jeremy Smith Sent: Friday, December 2, 2022 3:10 PM To: pgsql-general@lists.postgresql.org Subject: Re: Stored procedure code no longer stored in v14 and v15, changed behaviour From a user point of view, can also be seen as a "regression", when an observable property of the system changes to a new different / incompatible way, to some extent. I'm not saying it is, still it is a change one discovers too late, creates pain to some, and is both worth reporting and discussing. Isn't this only true if you use the new sql_body/BEGIN ATOMIC syntax, though? Can't you keep using the older AS 'definition' syntax and still get the body of the function, unchanged, in the prosrc column? You would, of course, lose the benefit of the dependency tracking. As a user, though, I don't find it surprising that creating a function using new syntax specifically designed to provide dependency tracking would change the way the function is stored internally. To me, the new syntax gives the same benefits and drawbacks to SQL functions that we already have with views, which are also re-written on creation. -Jeremy
Re: Q: error on updating collation version information
Karsten Hilbert wrote: > The database encoding is UTF8. That br_FR@euro.LATIN9 had > _not_ been added manually. It is also not actively used in my > database(s). br_FR@euro.LATIN9 cannot be used actively in an UTF-8 database because it's for a different encoding than the database. It was probably available in the OS at initdb time. Every available locale (see locale -a) gets imported into the template databases, and then into the other databases, since CREATE DATABASE copies a template without filtering out the locales that are incompatible with the target database. There's no need to upgrade anything in the OS to get the ALTER COLLATION... REFRESH error you mention. It's sufficient to have a mix of collations for different encodings and try to refresh collations whose encoding are not compatible with the current database. > What is the canonical advice on the way forward here ? Is > the _suggested_ solution to delete the collation or am I > missing to see the "proper" approach to fixing it ? ISTM that dropping that collation from any non-LATIN9 database is the more sensible action. Maybe it could be discussed as a possible improvement to have ALTER COLLATION... REFRESH ignore the database encoding and still refresh the version number. Best regards, -- Daniel Vérité https://postgresql.verite.pro/ Twitter: @DanielVerite
Re: Stored procedure code no longer stored in v14 and v15, changed behaviour
Also, we've *never* had storage of the original text for views. I'm a little mystified by people claiming they use original text for vetting functions when they clearly have no equivalent ability for views ... or are your applications completely view-free? Well, I would say that's annoying too! If you want an audit comparison point, I'd suggest capturing the result of pg_get_functiondef or one of its sibling functions just after creating your function. "pg_dump -s" is another pretty credible mechanism for capturing schema details. Do note, that pg_get_viewdef returns the SELECT statement of a view, no column name specification, while pg_get_function_def returns a CREATE statement. So the implementation is different too. With regards, Martijn Tonies Upscene Productions https://www.upscene.com Database Workbench - developer tool for Oracle, SQL Server, PostgreSQL, MySQL, InterBase, NexusDB and Firebird.
Re: plpgsql_check_function issue after upgrade
po 5. 12. 2022 v 11:24 odesílatel shashidhar Reddy <
shashidharreddy...@gmail.com> napsal:
> Is there any way to get the older version 1.1 of plpgsql_check to install
> it?
>
you can compile it from source code
https://github.com/okbob/plpgsql_check
there are complete history
Regards
Pavel
>
> On Mon, 5 Dec, 2022, 1:36 pm Pavel Stehule,
> wrote:
>
>>
>>
>> po 5. 12. 2022 v 8:42 odesílatel shashidhar Reddy <
>> shashidharreddy...@gmail.com> napsal:
>>
>>> Pavel,
>>>
>>> Below is the back trace result
>>>
>>> libffi6-dbg libgcc1-dbg libkrb5-dbg libstdc++6-8-dbg libxml2-dbg
>>> postgresql-13-dbgsym postgresql-13-pldeb
>>> ugger-dbgsym zlib1g-dbg
>>>
>>
>> I am sorry, I don't anything
>>
>>
>>
>>>
>>> On Mon, Dec 5, 2022 at 11:28 AM Pavel Stehule
>>> wrote:
>>>
Hi
po 5. 12. 2022 v 6:49 odesílatel shashidhar Reddy <
shashidharreddy...@gmail.com> napsal:
> Pavel,
>
> Where can I get list-dbgsym-packages.sh script?
>
https://manpages.debian.org/testing/debian-goodies/find-dbgsym-packages.1.en.html
https://wiki.ubuntu.com/DebuggingProgramCrash
please, don't use top-post style in this mailing list
https://en.wikipedia.org/wiki/Posting_style
Regards
Pavel
> On Wed, Nov 30, 2022 at 10:04 AM Pavel Stehule <
> pavel.steh...@gmail.com> wrote:
>
>>
>>
>> st 30. 11. 2022 v 5:28 odesílatel Pavel Stehule <
>> pavel.steh...@gmail.com> napsal:
>>
>>>
>>>
>>> st 30. 11. 2022 v 1:38 odesílatel shashidhar Reddy <
>>> shashidharreddy...@gmail.com> napsal:
>>>
I have tried updating after upgrade but that wasn't working, so I
have dropped and recreated the extension.
Now it is crashing every time when we call the function.
>>>
>>> what is version od plpgsql_check on Postgres 12, what is version of
>>> plpgsql_check on Postgres 13 (with version of minor release)?
>>>
>>> Can you send backtrace?
>>> https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD
>>>
>>
>> Do you have installed some other extensions?
>>
>>
>>
>>>
>>>
>>>
>>>
>>>
On Tue, 29 Nov, 2022, 9:58 pm Pavel Stehule, <
pavel.steh...@gmail.com> wrote:
>
>
> út 29. 11. 2022 v 16:37 odesílatel shashidhar Reddy <
> shashidharreddy...@gmail.com> napsal:
>
>> Plogsql check version is 2.2 and one more finding is before
>> calling the function if we drop and recreate the plpgsql_check
>> extension
>> there is no issue, but each time we can't drop and create.
>>
>
> Maybe you need to run ALTER EXTENSION plpgsql_check UPDATE before
> first usage in pg 13
>
> If the extension works after re-installation, then the problem is
> not in an extension.
>
>
>
>> On Tue, 29 Nov, 2022, 7:19 pm shashidhar Reddy, <
>> shashidharreddy...@gmail.com> wrote:
>>
>>> Hello Pavel,
>>>
>>> This is the function causing the issue on all servers, and also
>>> i noticed when I use *plpgsql_check_function *in any function I
>>> am facing the same issue.
>>>
>>>
>>> On Tue, Nov 29, 2022 at 6:43 PM Pavel Stehule <
>>> pavel.steh...@gmail.com> wrote:
>>>
Hi
út 29. 11. 2022 v 13:49 odesílatel shashidhar Reddy <
shashidharreddy...@gmail.com> napsal:
> Hello,
>
> Recently we have upgraded postgres from version 12 to 13 and
> upgraded plpgsql_check to the latest version but
> after upgrade when calling the below function causing postgres
> restart .
>
> CREATE OR REPLACE FUNCTION pro.po_check(
> )
> RETURNS void
> LANGUAGE 'plpgsql'
> COST 100
> VOLATILE SECURITY DEFINER PARALLEL UNSAFE
> AS $BODY$
> DECLARE
> BEGIN
>
> PERFORM p.oid, n.nspname, p.proname,
> plpgsql_check_function(p.oid)
> FROM pg_catalog.pg_namespace n
> JOIN pg_catalog.pg_proc p ON pronamespace = n.oid
> JOIN pg_catalog.pg_language l ON p.prolang = l.oid
> WHERE l.lanname = 'plpgsql' AND p.prorettype <> 2279
> and upper(n.nspname) like upper('Pro');
>
> END;
> $BODY$;
>
> and the error in syslogs shows
> kernel: [93631.415790] postgres[86383]: segfault at 80 ip
> 7f07f3e3eefd sp 7fffcf1db500 error 4 in
> plpgsql_check.so[7f07f3e
Re: plpgsql_check_function issue after upgrade
út 29. 11. 2022 v 17:28 odesílatel Pavel Stehule
napsal:
>
>
> út 29. 11. 2022 v 16:37 odesílatel shashidhar Reddy <
> shashidharreddy...@gmail.com> napsal:
>
>> Plogsql check version is 2.2 and one more finding is before calling the
>> function if we drop and recreate the plpgsql_check extension there is no
>> issue, but each time we can't drop and create.
>>
>
> Maybe you need to run ALTER EXTENSION plpgsql_check UPDATE before first
> usage in pg 13
>
after checking source code, I was partially wrong. plpgsql_check doesn't
support update scripts. So if there are not same versions of plpgsql_check,
you need to reinstall plpgsql_check always
Regards
Pavel
>
> If the extension works after re-installation, then the problem is not in
> an extension.
>
>
>
>> On Tue, 29 Nov, 2022, 7:19 pm shashidhar Reddy, <
>> shashidharreddy...@gmail.com> wrote:
>>
>>> Hello Pavel,
>>>
>>> This is the function causing the issue on all servers, and also i
>>> noticed when I use *plpgsql_check_function *in any function I am facing
>>> the same issue.
>>>
>>>
>>> On Tue, Nov 29, 2022 at 6:43 PM Pavel Stehule
>>> wrote:
>>>
Hi
út 29. 11. 2022 v 13:49 odesílatel shashidhar Reddy <
shashidharreddy...@gmail.com> napsal:
> Hello,
>
> Recently we have upgraded postgres from version 12 to 13 and upgraded
> plpgsql_check
> to the latest version but after upgrade when calling the below function
> causing postgres restart .
>
> CREATE OR REPLACE FUNCTION pro.po_check(
> )
> RETURNS void
> LANGUAGE 'plpgsql'
> COST 100
> VOLATILE SECURITY DEFINER PARALLEL UNSAFE
> AS $BODY$
> DECLARE
> BEGIN
>
> PERFORM p.oid, n.nspname, p.proname, plpgsql_check_function(p.oid)
> FROM pg_catalog.pg_namespace n
> JOIN pg_catalog.pg_proc p ON pronamespace = n.oid
> JOIN pg_catalog.pg_language l ON p.prolang = l.oid
> WHERE l.lanname = 'plpgsql' AND p.prorettype <> 2279
> and upper(n.nspname) like upper('Pro');
>
> END;
> $BODY$;
>
> and the error in syslogs shows
> kernel: [93631.415790] postgres[86383]: segfault at 80 ip
> 7f07f3e3eefd sp 7fffcf1db500 error 4 in
> plpgsql_check.so[7f07f3e2e000+34000]
>
it can be a bug in plpgsql_check. But I am not able to fix it without
some information. Can you send the reproducer (minimal example of your
code, that reproduce this error)?
Regards
Pavel
>
> --
> Shashidhar
>
>>>
>>> --
>>> Shashidhar
>>>
>>
Re: plpgsql_check_function issue after upgrade
Hello Pavel,
I have re installed it after upgrade as update option was not working.
I could see the error in syslogs as error 4 in plpgsql_check.so and the
databases are restarting.
On Mon, 5 Dec, 2022, 5:51 pm Pavel Stehule, wrote:
>
>
> út 29. 11. 2022 v 17:28 odesílatel Pavel Stehule
> napsal:
>
>>
>>
>> út 29. 11. 2022 v 16:37 odesílatel shashidhar Reddy <
>> shashidharreddy...@gmail.com> napsal:
>>
>>> Plogsql check version is 2.2 and one more finding is before calling the
>>> function if we drop and recreate the plpgsql_check extension there is no
>>> issue, but each time we can't drop and create.
>>>
>>
>> Maybe you need to run ALTER EXTENSION plpgsql_check UPDATE before first
>> usage in pg 13
>>
>
> after checking source code, I was partially wrong. plpgsql_check doesn't
> support update scripts. So if there are not same versions of plpgsql_check,
> you need to reinstall plpgsql_check always
>
> Regards
>
> Pavel
>
>
>>
>> If the extension works after re-installation, then the problem is not in
>> an extension.
>>
>>
>>
>>> On Tue, 29 Nov, 2022, 7:19 pm shashidhar Reddy, <
>>> shashidharreddy...@gmail.com> wrote:
>>>
Hello Pavel,
This is the function causing the issue on all servers, and also i
noticed when I use *plpgsql_check_function *in any function I am
facing the same issue.
On Tue, Nov 29, 2022 at 6:43 PM Pavel Stehule
wrote:
> Hi
>
>
> út 29. 11. 2022 v 13:49 odesílatel shashidhar Reddy <
> shashidharreddy...@gmail.com> napsal:
>
>> Hello,
>>
>> Recently we have upgraded postgres from version 12 to 13 and
>> upgraded plpgsql_check to the latest version but after upgrade when
>> calling the below function causing postgres restart .
>>
>> CREATE OR REPLACE FUNCTION pro.po_check(
>> )
>> RETURNS void
>> LANGUAGE 'plpgsql'
>> COST 100
>> VOLATILE SECURITY DEFINER PARALLEL UNSAFE
>> AS $BODY$
>> DECLARE
>> BEGIN
>>
>> PERFORM p.oid, n.nspname, p.proname, plpgsql_check_function(p.oid)
>> FROM pg_catalog.pg_namespace n
>> JOIN pg_catalog.pg_proc p ON pronamespace = n.oid
>> JOIN pg_catalog.pg_language l ON p.prolang = l.oid
>> WHERE l.lanname = 'plpgsql' AND p.prorettype <> 2279
>> and upper(n.nspname) like upper('Pro');
>>
>> END;
>> $BODY$;
>>
>> and the error in syslogs shows
>> kernel: [93631.415790] postgres[86383]: segfault at 80 ip
>> 7f07f3e3eefd sp 7fffcf1db500 error 4 in
>> plpgsql_check.so[7f07f3e2e000+34000]
>>
>
> it can be a bug in plpgsql_check. But I am not able to fix it without
> some information. Can you send the reproducer (minimal example of your
> code, that reproduce this error)?
>
> Regards
>
> Pavel
>
>>
>> --
>> Shashidhar
>>
>
--
Shashidhar
>>>
Re: PANIC: could not flush dirty data: Cannot allocate memory
Some more updates Did this start after upgrading to 22.04? Or after a certain kernel upgrade? For sure it only started with Ubuntu 22.04. We did not had and still not have any issues on servers with Ubuntu 20.04 and 18.04. It also happens with Ubuntu 22.10 (Kernel 5.19.0-23-generic). We now try 6.0 mainline and 5.15. mainline kernel on some servers. I also forgot to mention that /var/lib/postgresql/12 directory is encrypted with fscrypt (ext4 encryption). So we also deactivated the directory encryption on one server to see if it is related to encryption. thanks Klaus
Re: plpgsql_check_function issue after upgrade
Hi
po 5. 12. 2022 v 15:22 odesílatel shashidhar Reddy <
shashidharreddy...@gmail.com> napsal:
> Hello Pavel,
>
> I have re installed it after upgrade as update option was not working.
>
> I could see the error in syslogs as error 4 in plpgsql_check.so and the
> databases are restarting.
>
1. please, don't send top post messages
2. There can be bugs in plpgsql_check, but without more detailed
information I am not able to fix it. Your example is working fine on my
computer.
can you run this function
CREATE OR REPLACE FUNCTION po_check(
)
RETURNS void
LANGUAGE 'plpgsql'
COST 100
VOLATILE SECURITY DEFINER PARALLEL UNSAFE
AS $BODY$
DECLARE r record;
BEGIN
FOR r IN SELECT p.oid, n.nspname, p.proname
FROM pg_catalog.pg_namespace n
JOIN pg_catalog.pg_proc p ON pronamespace = n.oid
JOIN pg_catalog.pg_language l ON p.prolang = l.oid
WHERE l.lanname = 'plpgsql' AND p.prorettype <> 2279
and upper(n.nspname) like upper('Pro');
LOOP
RAISE NOTICE '>>% %<<', r.oid, r.nspname || '.' || r.proname;
PERFORM plpgsql_check_function(r.oid);
RAISE NOTICE 'ok';
END LOOP;
END;
$BODY$;
What is an output?
>
> On Mon, 5 Dec, 2022, 5:51 pm Pavel Stehule,
> wrote:
>
>>
>>
>> út 29. 11. 2022 v 17:28 odesílatel Pavel Stehule
>> napsal:
>>
>>>
>>>
>>> út 29. 11. 2022 v 16:37 odesílatel shashidhar Reddy <
>>> shashidharreddy...@gmail.com> napsal:
>>>
Plogsql check version is 2.2 and one more finding is before calling the
function if we drop and recreate the plpgsql_check extension there is no
issue, but each time we can't drop and create.
>>>
>>> Maybe you need to run ALTER EXTENSION plpgsql_check UPDATE before first
>>> usage in pg 13
>>>
>>
>> after checking source code, I was partially wrong. plpgsql_check doesn't
>> support update scripts. So if there are not same versions of plpgsql_check,
>> you need to reinstall plpgsql_check always
>>
>> Regards
>>
>> Pavel
>>
>>
>>>
>>> If the extension works after re-installation, then the problem is not in
>>> an extension.
>>>
>>>
>>>
On Tue, 29 Nov, 2022, 7:19 pm shashidhar Reddy, <
shashidharreddy...@gmail.com> wrote:
> Hello Pavel,
>
> This is the function causing the issue on all servers, and also i
> noticed when I use *plpgsql_check_function *in any function I am
> facing the same issue.
>
>
> On Tue, Nov 29, 2022 at 6:43 PM Pavel Stehule
> wrote:
>
>> Hi
>>
>>
>> út 29. 11. 2022 v 13:49 odesílatel shashidhar Reddy <
>> shashidharreddy...@gmail.com> napsal:
>>
>>> Hello,
>>>
>>> Recently we have upgraded postgres from version 12 to 13 and
>>> upgraded plpgsql_check to the latest version but after upgrade
>>> when calling the below function causing postgres restart .
>>>
>>> CREATE OR REPLACE FUNCTION pro.po_check(
>>> )
>>> RETURNS void
>>> LANGUAGE 'plpgsql'
>>> COST 100
>>> VOLATILE SECURITY DEFINER PARALLEL UNSAFE
>>> AS $BODY$
>>> DECLARE
>>> BEGIN
>>>
>>> PERFORM p.oid, n.nspname, p.proname,
>>> plpgsql_check_function(p.oid)
>>> FROM pg_catalog.pg_namespace n
>>> JOIN pg_catalog.pg_proc p ON pronamespace = n.oid
>>> JOIN pg_catalog.pg_language l ON p.prolang = l.oid
>>> WHERE l.lanname = 'plpgsql' AND p.prorettype <> 2279
>>> and upper(n.nspname) like upper('Pro');
>>>
>>> END;
>>> $BODY$;
>>>
>>> and the error in syslogs shows
>>> kernel: [93631.415790] postgres[86383]: segfault at 80 ip
>>> 7f07f3e3eefd sp 7fffcf1db500 error 4 in
>>> plpgsql_check.so[7f07f3e2e000+34000]
>>>
>>
>> it can be a bug in plpgsql_check. But I am not able to fix it without
>> some information. Can you send the reproducer (minimal example of your
>> code, that reproduce this error)?
>>
>> Regards
>>
>> Pavel
>>
>>>
>>> --
>>> Shashidhar
>>>
>>
>
> --
> Shashidhar
>
Re: Libpq linked to LibreSSL
On Mon, Dec 5, 2022 at 07:27:11AM +0100, Marco Bambini wrote: > I am using Ubuntu 14.04.5 LTS (64b bit). > If I don't modify anything then my test application runs fine and it load > OpenSSL 1.0.1f 6 Jan 2014 from the default directory. I think what Michael Paquier is saying is that you have to _compile_ Postgres using the LibreSSL includes --- you can't take a binary compliled for OpenSSL and link it against LibreSSL. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Embrace your flaws. They make you human, rather than perfect, which you will never be.
Re: Views "missing" from information_schema.view_table_usage
Oh ok. Not to cause confusion, but after I suggested I would request an update to the docs, I thought maybe it would be better to ask if the VTU's code could be modified to include MVs. So I sent a request to pgsql-hackers (I think that's the list to use for feature requests). But from what you're saying, Tom, that may be a fruitless endeavor. If they reply back "nope", then I'll submit a request to have the documentation updated. Thanks for everyone's contributions! Jon On Fri, Dec 2, 2022 at 5:11 PM Tom Lane wrote: > Erik Wienhold writes: > > Could it be a bug? Materialized views are a Postgres extension[1] (I > always > > thought they are standard.) But I'd expect them to be included when > talking > > about "views". Maybe they are not included because they are considered > being > > closer to physical tables[2] than views. Yet their dependencies would > justify > > inclusion in view_table_usage. > > The reasoning is that the information_schema views are defined by the > SQL standard and therefore should only show content that matches the > standard. Thus, they ignore PG-invented objects like matviews and > sequences. Some other projects adopt more liberal views about > what should be shown in those views, but that one is our policy. > > regards, tom lane >
Solved: Table : Bloat grow high
Hi, minx was freeze or stop because some transactions showed state "idle in transaction" continuously, for it to the frequency of vacuum execution has been increased all tables, and this caused minx in the table to be updated and the queries improved a lot and the transactions with state "idle in transaction" did not continue. Thank you all Best regards De: Alexis Zapata Enviado: domingo, 13 de noviembre de 2022 9:50 a. m. Para: Laurenz Albe ; pgsql-general@lists.postgresql.org Asunto: RE: Table : Bloat grow high Hi Laurenz , I found that xmin does not change when running the vacuum. De: Laurenz Albe Enviado: sábado, 12 de noviembre de 2022 9:05 a. m. Para: Alexis Zapata ; pgsql-general@lists.postgresql.org Asunto: Re: Table : Bloat grow high On Fri, 2022-11-11 at 17:09 +, Alexis Zapata wrote: > In postgresql 13.5 I have a table (size 3.1 GB) and in this table occurs near > to > 200 updates per second, after 2 days the size table is 7 GB and bloat grow to > 45% and > the query operations are degraded. vacuum runs every 5 seconds over this. > but the > bloat growth continues, to solve the > problem quickly, we have made a replica of the table with a trigger, then a > copy > of the data and in a > transaction we rename the table, but it would not be the best solution. > Some suggestion about stop this size increase or parameter to setting up? You'd be most happy with HOT updates. Make sure that there is no index on any of the columns you update, and change the table to have a "fillfactor" less than 100. Then you can get HOT updates which don't require VACUUM for cleaning up. https://www.cybertec-postgresql.com/en/hot-updates-in-postgresql-for-better-performance/ Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: postgres large database backup
> On 01 Dec 2022, at 01:19, Hannes Erven wrote: > > You could also use a filesystem that can do atomic snapshots - like ZFS. > > However, I'm wondering why none of the previous respondents mentioned it? the reason: 'cause most people are "stuck" on public clouds that does not provide that as a "solution" in their cookie cutter (wrongly called "managed") solutions... > Sure, ZFS may have its own performance implications... but my experience is > that it makes "safety snapshots" and "on-demand throw-away testing > environments" very convenient to use. Using that myself, but also as a VM in a ProxMox environmenet, I've found (and restored) with ProxMox Backup Server goot single snapshot backup solution. My cases doesn't need the PITR yet
