Re: plpgsql_check_function issue after upgrade
st 30. 11. 2022 v 5:28 odesílatel Pavel Stehule
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
> 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
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
>
> 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
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
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.
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
>>> 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: delete statement returning too many results
On Mon, Nov 28, 2022 at 9:18 AM Ron wrote: > On 11/28/22 07:29, Arlo Louis O'Keeffe wrote: > > Hello everyone, > > > > I am seeing weird behaviour of a delete statement that is returning more > results than I am expecting. > > > > This is the query: > > > > DELETE FROM queue > > WHERE > > id IN ( > > SELECT id > > FROM queue > > ORDER BY id > > LIMIT 1 > > FOR UPDATE > > SKIP LOCKED > > ) > > RETURNING *; > > > > My understanding is that the limit in the sub-select should prevent this > query from ever > > returning more than one result. Sadly I am seeing cases where there is > more than one result. > > > > This repository has a Java setup that pretty reliably reproduces my > issue: > > https://github.com/ArloL/postgres-query-error-demo > > > > I checked the docs for select and delete and couldn’t find any hint for > cases > > where the behaviour of limit might be surprising. > > > > Am I missing something? > If I reduce your delete statement to: DELETE FROM queue WHERE ID IN (123); And there are 2 rows with ID 123... Should it not delete both rows? and if I wanted a queue like behavior in that situation, I would use a cursor for update. Then inside that cursor, use DELETE WHERE CURRENT OF? > More than one row will be deleted if there in more than one record in > "queue" for the specific value of "id" (i.e "id" is not unique). > > -- > Angular momentum makes the world go 'round. > > >
PostgreSQL extension for storing Graph data (Apache AGE)
Hello PostgreSQL users, https://github.com/apache/age Apache AGE is an Apache 2-licensed open source PostgreSQL extension for storing Graph data. The current version of Apache AGE is to enable PostgreSQL users to use Neo4j's openCypher-based graph queries in unison with existing relational tables Any idea or suggestion to make AGE better would be much appreciated. Thank you, AK
PostgreSQL extension for processing Graph queries (Apache AGE)
Hello PostgreSQL users, https://github.com/apache/age Apache AGE is an Apache 2-licensed open source PostgreSQL extension for storing Graph data. The current version of Apache AGE is to enable PostgreSQL users to use Neo4j's openCypher-based graph queries in unison with existing relational tables Any idea or suggestion to make AGE better would be much appreciated. Thank you, AK
Re: delete statement returning too many results
Harmen writes: > On Mon, Nov 28, 2022 at 12:11:53PM -0500, Tom Lane wrote: >> So basically it's unsafe to run the sub-select more than once, >> but the query as written leaves it up to the planner whether >> to do that. I'd suggest rephrasing as [...] > I'm not the original poster, but I do use similar constructions for simple > postgres queues. I've been trying for a while, but I don't understand where > the > extra rows come from, or what's "silent" about SKIP LOCKED. Sorry, I should not have blamed SKIP LOCKED in particular; this construction will misbehave with or without that. The issue is with using SELECT FOR UPDATE inside a DELETE or UPDATE that then modifies the row that the subquery returned. The next execution of the subquery will, or should, return a different row: either some not-deleted row, or the modified row. So in this context, the result of the subquery is volatile. The point of putting it in a MATERIALIZED CTE is to lock the result down regardless of that. > Because we get different results depending on the plan postgres picks, I can > see two options: either the query is broken, or postgres is broken. You can argue that the planner should treat volatile subqueries differently than it does today. But the only reasonable way of tightening the semantics would be to force re-execution of such a subquery every time, even when it's not visibly dependent on the outer query. That would be pretty bad for performance, and I doubt it would make the OP happy in this example, because what it would mean is that his query "fails" every time not just sometimes. (Because of that, I don't have too much trouble concluding that the query is broken, whether or not you feel that postgres is also broken.) The bigger picture here is that we long ago decided that the planner should not inquire too closely into the volatility of subqueries, primarily because there are use-cases where people intentionally rely on them not to be re-executed. As an example, these queries give different results: regression=# select random() from generate_series(1,3); random - 0.7637195395988317 0.09569374432524946 0.490132093120365 (3 rows) regression=# select (select random()) from generate_series(1,3); random 0.9730230633436501 0.9730230633436501 0.9730230633436501 (3 rows) In the second case, the sub-select is deemed to be independent of the outer query and executed only once. You can argue that if that's what you want you should be forced to put the sub-select in a materialized CTE to make that plain. But we felt that that would make many more people unhappy than happy, so we haven't done it. Maybe the question could be revisited once all PG versions lacking the MATERIALIZED syntax are long dead. regards, tom lane
Re: Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all
On Tue, Nov 29, 2022 at 12:01:01PM -0500, Tom Lane wrote: > Yeah, I don't see that "cluster" adds anything. It's sometimes worth > saying "database superuser" to ensure that you don't confuse people > who might think of some external-to-Postgres meaning of "superuser", > but otherwise plain "superuser" is fine. And we've settled on > "bootstrap superuser" as the best term for the role with OID 10. > So the present set of glossary entries looks fine to me. Thanks, agreed. I just wanted to suggest a clarification in case it was useful. -- 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: Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all
"David G. Johnston" writes: > On Tue, Nov 29, 2022 at 9:31 AM Bruce Momjian wrote: >> I know I am replying late here, but isn't it the database _cluster_ >> superuser? > The "cluster" being implied doesn't seem like a big deal. The shorter term > is nice. It doesn't seem worth changing all the many, many, places in the > documentation where just "database superuser' is used - and so we've > updated the glossary to be just that. Yeah, I don't see that "cluster" adds anything. It's sometimes worth saying "database superuser" to ensure that you don't confuse people who might think of some external-to-Postgres meaning of "superuser", but otherwise plain "superuser" is fine. And we've settled on "bootstrap superuser" as the best term for the role with OID 10. So the present set of glossary entries looks fine to me. regards, tom lane
Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all
On Tue, Nov 29, 2022 at 9:31 AM Bruce Momjian wrote: > > I know I am replying late here, but isn't it the database _cluster_ > superuser? > > The "cluster" being implied doesn't seem like a big deal. The shorter term is nice. It doesn't seem worth changing all the many, many, places in the documentation where just "database superuser' is used - and so we've updated the glossary to be just that. David J.
Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all
On Wed, Oct 26, 2022 at 09:57:50PM -0700, Adrian Klaver wrote: > On 10/26/22 18:33, Bryn Llewellyn wrote: > > The descriptive designation "the role that owns the SQL part of the > > implementation of PostgreSQL" is too much of a mouthful for daily use. > > And anyway, this notion captures only part of the story that makes > > "postgres" uniquely what it is—at least on Ubuntu. > > > > MORE... > > > > Here's what my empirical observations told me: > > > > It's easy to characterize this role by describing the way that you get > > it and the conspicuous unique emergent properties that it has. > > > > * You specify its name using the flag that's shown thus in response to > > "initdb —help" > > > > -U, --username=NAME database superuser name > > How much time would it have taken to go to the docs?: > > https://www.postgresql.org/docs/current/app-initdb.html I know I am replying late here, but isn't it the database _cluster_ superuser? -- 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: plpgsql_check_function issue after upgrade
ú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
>> 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: delete statement returning too many results
On Mon, Nov 28, 2022 at 12:11:53PM -0500, Tom Lane wrote: > So basically it's unsafe to run the sub-select more than once, > but the query as written leaves it up to the planner whether > to do that. I'd suggest rephrasing as > > WITH target_rows AS MATERIALIZED ( > SELECT id > FROM queue > ORDER BY id > LIMIT 1 > FOR UPDATE > SKIP LOCKED > ) > DELETE FROM queue > WHERE id IN (SELECT * FROM target_rows) > RETURNING *; Thanks for the explanation and suggested fix, Tom. I'm not the original poster, but I do use similar constructions for simple postgres queues. I've been trying for a while, but I don't understand where the extra rows come from, or what's "silent" about SKIP LOCKED. Because we get different results depending on the plan postgres picks, I can see two options: either the query is broken, or postgres is broken. Assuming it's the former, would there be a way to make it clearer that the "obvious" (to me) way to use SKIP LOCKED is wrong? Thanks! Harmen
Re: plpgsql_check_function issue after upgrade
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.
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
>
#PGSQL Phriday #003
Hello everyone! I am hosting this month's #PGSQL Phriday. This monthly event inspires the community to write about a specific topic. I have chosen the topic "What is the PostgreSQL Community to you?" You can find all the rules and details in the blog post below. I hope you will consider joining in on the topic and writing your own post! I look forward to reading it! https://sqlasylum.wordpress.com/2022/11/29/pgsql-phriday-003-what-is-the-community-to-you/ Thank you Pat Wright
Re: plpgsql_check_function issue after upgrade
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
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
>
plpgsql_check_function issue after upgrade
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]
--
Shashidhar
Re: PANIC: could not flush dirty data: Cannot allocate memory
Hello all! Thanks for the many hints to look for. We did some tuning and further debugging and here are the outcomes, answering all questions in a single email. In the meantime, you could experiment with setting checkpoint_flush_after to 0 We did this: # SHOW checkpoint_flush_after; checkpoint_flush_after 0 (1 row) But we STILL have PANICs. I tried to understand the code but failed. I guess that there are some code paths which call pg_flush_data() without checking this settings, or the check does not work. 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. I would believe that the kernel would raise a bunch of printks if it hit ENOMEM in the commonly used paths, so you would see something in dmesg or wherever you collect your kernel log if it happened where it was expected. There is nothing in the kernel logs (dmesg) Do you use cgroups or such to limit memory usage of postgres? No Any uncommon options on the filesystem or the mount point? No. Also no Antivirus: /dev/xvda2 / ext4 noatime,nodiratime,errors=remount-ro 0 1 or LABEL=cloudimg-rootfs /ext4 discard,errors=remount-ro 0 1 does this happen on all the hosts, or is it limited to one host or one technology? It happens on XEN VMs, KVM VMs and VMware VMs. On Intel and AMD plattforms. Another interesting thing would be to know the mount and file system options for the FS that triggers the failures. E.g. # tune2fs -l /dev/sda1 tune2fs 1.46.5 (30-Dec-2021) Filesystem volume name: cloudimg-rootfs Last mounted on: / Filesystem UUID: 0522e6b3-8d40-4754-a87e-5678a6921e37 Filesystem magic number: 0xEF53 Filesystem revision #:1 (dynamic) Filesystem features: has_journal ext_attr resize_inode dir_index filetype needs_recovery extent 64bit flex_bg encrypt sparse_super large_file huge_file dir_nlink extra_isize metadata_csum Filesystem flags: signed_directory_hash Default mount options:user_xattr acl Filesystem state: clean Errors behavior: Continue Filesystem OS type: Linux Inode count: 12902400 Block count: 26185979 Reserved block count: 0 Overhead clusters:35096 Free blocks: 18451033 Free inodes: 12789946 First block: 0 Block size: 4096 Fragment size:4096 Group descriptor size:64 Reserved GDT blocks: 243 Blocks per group: 32768 Fragments per group: 32768 Inodes per group: 16128 Inode blocks per group: 1008 Flex block group size:16 Filesystem created: Wed Apr 20 18:31:24 2022 Last mount time: Thu Nov 10 09:49:34 2022 Last write time: Thu Nov 10 09:49:34 2022 Mount count: 7 Maximum mount count: -1 Last checked: Wed Apr 20 18:31:24 2022 Check interval: 0 () Lifetime writes: 252 GB Reserved blocks uid: 0 (user root) Reserved blocks gid: 0 (group root) First inode: 11 Inode size: 256 Required extra isize: 32 Desired extra isize: 32 Journal inode:8 First orphan inode: 42571 Default directory hash: half_md4 Directory Hash Seed: c5ef129b-fbee-4f35-8f28-ad7cc93c1c43 Journal backup: inode blocks Checksum type:crc32c Checksum: 0xb74ebbc3 Thanks Klaus
