gist segmentation fault

[Arup Nanda]

Hello,

We're preparing to upgrade postgresql from 12.8 to 14.2 but still we have some 
bugs and issues. Most of them are our fault, but now we have problem wedon't 
know how to play with it.

-- dmesg message

[Sat Apr  2 08:26:24 2022] postmaster[1939250]: segfault at 7f2998f2c000 ip 
7f4a56cfeaaa sp 7fff5a3e36a8 error 6 in 
libc-2.28.so[7f4a56b9e000+1bc000][Sat Apr  2 08:26:24 2022] Code: 8e c0 01 00 
00 c5 fe 6f 06 c5 fe 6f 4e 20 c5 fe 6f 56 40 c5 fe 6f 5e 60 48 81 c6 80 00 00 
00 48 81 ea 80 00 00 00 c5 fd e7 07 fd e7 4f 20 c5 fd e7 57 40 c5 fd e75f 60 48 
81 c7 80 00 00 00

-- PostgreSQL Log message

2022-04-02 08:26:24 MSK    0LOG:  server process (PID 1939250) was 
terminated by signal 11: Segmentation fault2022-04-02 08:26:24 MSK    
0DETAIL:  Failed process was running: SELECT "SubdivisionId", "UserId", 
"UserLogin"FROM powerbi."GetReportRights1002_VIEW" ;2022-04-02 08:26:24 MSK    
0LOG:  terminating any other active server processes

-- User error message

54000: index row requires 554440232 bytes, maximum size is 8191

-- We have compiled from the source code with modified macros

sed -i.gres "s/#define NAMEDATALEN 64/#define NAMEDATALEN 320/g" 
./src/include/pg_config_manual.h
sed -i.gres "s/#define NAMEDATALEN 64/#define NAMEDATALEN 320/g" 
./src/interfaces/ecpg/include/sqlda-native.h

-- In the attachment files
-- DDL
-- The result of the command execution: gdb /usr/pgsql-14/bin/postgres  --ex 'bt full' --batch

CREATE OR REPLACE VIEW powerbi."GetReportRights1002_VIEW" AS 
 SELECT "FT_GetReportRights"."r_SubdivisionId" AS "SubdivisionId",
"FT_GetReportRights"."r_UserId" AS "UserId",
concat("FT_GetReportRights"."r_UserLogin", '@example.com') AS "UserLogin"
   FROM rights."FT_GetReportRights"(1002) 
"FT_GetReportRights"("r_SubdivisionId", "r_UserId", "r_UserLogin")
UNION ALL
 SELECT DISTINCT '-999'::integer AS "SubdivisionId",
"FT_GetReportRights"."r_UserId" AS "UserId",
concat("FT_GetReportRights"."r_UserLogin", '@example.com') AS "UserLogin"
   FROM rights."FT_GetReportRights"(1002) 
"FT_GetReportRights"("r_SubdivisionId", "r_UserId", "r_UserLogin");



CREATE OR REPLACE FUNCTION rights."FT_GetReportRights"(IN "par_ReportId" 
integer DEFAULT NULL::integer)
  RETURNS TABLE("r_SubdivisionId" integer, "r_UserId" integer, "r_UserLogin" 
character varying) AS
$BODY$
DECLARE
  --"par_ReportId" integer = 1002;

BEGIN


  DROP TABLE IF EXISTS "ListObject" ;
  CREATE temp TABLE IF NOT EXISTS "ListObject"(
"idObject" INTEGER
  );

  WITH RECURSIVE "get_idObjects" AS (
SELECT
 o.id
,o."ParentID"
,o.tag_int as "idObject_Subj"
  FROM
rights."Objects" o
  WHERE
o."idObjectType" = 1
UNION
SELECT
 o_parent.id
,o_parent."ParentID"
,o_slave."idObject_Subj"
  FROM
"get_idObjects" as o_slave
  INNER JOIN rights."Objects" as o_parent
ON o_parent.id = o_slave."ParentID"
AND o_parent."idObjectType" = 2
  )
  INSERT INTO "ListObject"(
  "idObject"
)
SELECT
gio.id
  FROM
"get_idObjects" gio
  WHERE
gio."idObject_Subj" = "par_ReportId"
  ;


  DROP TABLE IF EXISTS "FullRightsOfStructure" ;
  CREATE TEMP TABLE IF NOT EXISTS "FullRightsOfStructure"(
  "SubdivisionId" INTEGER
 ,"UserId" integer
 ,"UserLogin" VARCHAR(20)
   );

  INSERT INTO "FullRightsOfStructure"(
  "SubdivisionId"
 ,"UserId"
 ,"UserLogin"
)
SELECT DISTINCT 
(CASE
  WHEN o."idObjectType" = 3 THEN o.tag_int
  --WHEN o."idObjectType" = 4 THEN ou."SubdivisionId"
  ELSE NULL::INTEGER
 END) as "SubdivisionId"
,ou."id"
,ou."Login"
  FROM
"ListObject" lo
JOIN rights."Rights" r ON r."idObject_Subj" = lo."idObject"
JOIN rights."Users" u ON u.id = r."idRightsUser"
JOIN rights."Objects" o ON o.id = r."idObject"
JOIN dwh."OFAS_Org_User" ou ON ou.id = u."idUser_OFAS"


  union
  select
os.id as "SubdivisionId",
ou.id as "UserId",
ou."Login" as "UserLogin"
  from
dwh."User_See_All" as u_sall
join dwh."OFAS_Org_User" as ou
on u_sall."UserLogin" = ou."Login"
full join dwh."OFAS_Org_Structure" as os
on true
;

  DROP TABLE IF EXISTS "tt_Result";
  CREATE TEMP TABLE "tt_Result"(
  "SubdivisionId" INTEGER
 ,"UserId" integer
 ,"UserLogin" VARCHAR(20)
   );
  insert into "tt_Result" (
  "SubdivisionId"
 ,"UserId"
 ,"UserLogin"
   )
  SELECT
  coalesce(os_in."id",os.id) as "SubdivisionId"
 ,fros."UserId"
 ,fros."UserLogin"
   from
 "FullRightsOfStructure" as fros
   join dwh."OFAS_Org_Structure" as OS on os.id = fros."SubdivisionId"
   left join dwh."OFAS_Org_Structure" as OS_in on os_in."id_hierarchy" <@ 
os."id_hierarchy"

  

Transaction and SQL errors

[Sebastien Flaesch]

Hello!

Is there any plan to have an equivalent of psql's

set ON_ERROR_ROLLBACK on

in the DB engine?

Most other DB engines have this behavior built-in, that makes migration to 
PostgreSQL more difficult when there are complex long transactions in the 
application code.

I do often suggest programmers to trust the DB engine regarding table 
constraints, and let the engine check unique / foreign keys, etc. However, 
these errors cancel the whole transaction and user code can't correct the last 
SQL statement and try a new execution, without losing everything since TX 
started...

Any thoughts?

Any arguments I could give to programmers?   Maybe:
"You better replay all SQL of the whole transaction... (and make them as short 
as possible!)"
?

Seb

Re: Transaction and SQL errors

[Karsten Hilbert]

Am Mon, Apr 04, 2022 at 11:33:14AM + schrieb Sebastien Flaesch:

> Is there any plan to have an equivalent of psql's
>
> set ON_ERROR_ROLLBACK on
>
> in the DB engine?

That is already what happens.

SQL fails, transaction rolls back.

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

Re: Transaction and SQL errors

[David Rowley]

On Mon, 4 Apr 2022 at 23:33, Sebastien Flaesch
 wrote:
> Any arguments I could give to programmers?   Maybe:
> "You better replay all SQL of the whole transaction... (and make them as 
> short as possible!)"
> ?

There are SAVEPOINTs [1].

David

[1] https://www.postgresql.org/docs/current/sql-savepoint.html

Select .... where id not in (....) returns 0 incorrectly

[J. Roeleveld]

Hi all,

While trying to write some queries to clean up records I no longer need, I 
come up with a very strange situation where numbers literally don't add up as 
expected.

If anyone can help me trace where this strange behaviour is coming from and 
how to resolve it, please let me know.
In case this is already resolved in a recent version, I am currently using 
version 11.14.

Please see the following log from psql:

joost=> create temporary table q ( id integer );
CREATE TABLE
joost=> insert into q ( id ) select snapshotlistid from backupitem;
INSERT 0 765
joost=> insert into q ( id ) select snapshotlistid from queue;
INSERT 0 3183

joost=> select count(1) from q;
 count 
---
  3948
(1 row)

joost=> select count(1) from snapshotlist where id in (select id from q);
 count 
---
  1810
(1 row)

joost=> select count(1) from snapshotlist where id not in (select id from q);
 count 
---
 0
(1 row)

joost=> select count(1) from snapshotlist where id not in (select id from 
snapshotlist where id in (select id from q));
  count  
-
 2293923
(1 row)


The tables are defined like: (Note, I did remove some fields from the tables 
which have no impact. Most are foreign keys to further tables or varchar data 
fields)

CREATE TABLE snapshotlist (
  id SERIAL PRIMARY KEY,
  active boolean,
  created TIMESTAMP DEFAULT clock_timestamp(),
  modified TIMESTAMP
);

CREATE TABLE queue (
  id SERIAL PRIMARY KEY,
  queuetask VARCHAR(500) NOT NULL,
  snapshotlistid INTEGER REFERENCES snapshotlist(id) ON DELETE RESTRICT,
  uuid uuid NOT NULL,
  UNIQUE(uuid)
);

CREATE TABLE backupitem (
  id SERIAL PRIMARY KEY,
  snapshotlistid INTEGER REFERENCES snapshotlist(id) ON DELETE RESTRICT NOT 
NULL,
  UNIQUE(snapshotlistid)
);

Re: Transaction and SQL errors

[Sebastien Flaesch]

Hi Karsten,

No...  see the doc: https://www.postgresql.org/docs/14/app-psql.html

ON_ERROR_ROLLBACK

When set to on, if a statement in a transaction block generates an error, the 
error is ignored and the transaction continues.

Seb

Re: Transaction and SQL errors

[Sebastien Flaesch]

Hi David,

I know savepoints are supported by PostgreSQL, but what about existing/legacy 
code that has tons of lines that rely on the behavior of other DB engines?

Would you ask programmers to put a savepoint / release savepoint around each 
SQL statement inside a TX?

Seb

From: David Rowley 
Sent: Monday, April 4, 2022 1:44 PM
To: Sebastien Flaesch 
Cc: pgsql-gene...@postgresql.org 
Subject: Re: Transaction and SQL errors

EXTERNAL: Do not click links or open attachments if you do not recognize the 
sender.

On Mon, 4 Apr 2022 at 23:33, Sebastien Flaesch
 wrote:
> Any arguments I could give to programmers?   Maybe:
> "You better replay all SQL of the whole transaction... (and make them as 
> short as possible!)"
> ?

There are SAVEPOINTs [1].

David

[1] 
https://urldefense.com/v3/__https://www.postgresql.org/docs/current/sql-savepoint.html__;!!I_DbfM1H!SJaCAE7hUHxRte8uMaa9RB8byL3QsmxkBRzk9POp0N8sLvjhpL5AWoiH-7MEuTkZMcrl$

Re: Select .... where id not in (....) returns 0 incorrectly

[Ravi Krishna]

select count(1) from snapshotlist where id not in (select id from q);
count
---
0
(1 row)


Doesn't this usually happen if q.id contains NULL.  That is as per ANSI 
standard.

Re: Select .... where id not in (....) returns 0 incorrectly

[Jeremy Smith]

>
> Doesn't this usually happen if q.id contains NULL.  That is as per ANSI
> standard.
>
>
Yes, there's a good description of this here:
https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_NOT_IN

It would be better to use NOT EXISTS:
select count(*) from snapshotlist where NOT EXISTS (SELECT FROM q WHERE q.id
= snapshotlist.id);


-Jeremy

Re: Select .... where id not in (....) returns 0 incorrectly

[J. Roeleveld]

On Monday, April 4, 2022 2:50:44 PM CEST Jeremy Smith wrote:
> > Doesn't this usually happen if q.id contains NULL.  That is as per ANSI
> > standard.
> 
> Yes, there's a good description of this here:
> https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_NOT_IN
> 
> It would be better to use NOT EXISTS:
> select count(*) from snapshotlist where NOT EXISTS (SELECT FROM q WHERE q.id
> = snapshotlist.id);
> 
> 
> -Jeremy

Thank you Jeremy and Ravi.

This was actually the case, I forgot there is 1 NULL-value in that list.
Personally, I think NULL should be treated as a seperate value and not lead to 
strange behaviour.

I will need to look into my queries and get rid of "NOT IN" constructions when 
the list comes from a different query.

--
Joost

Re: Transaction and SQL errors

[J. Roeleveld]

On Monday, April 4, 2022 1:44:40 PM CEST David Rowley wrote:
> On Mon, 4 Apr 2022 at 23:33, Sebastien Flaesch
> 
>  wrote:
> > Any arguments I could give to programmers?   Maybe:
> > "You better replay all SQL of the whole transaction... (and make them as
> > short as possible!)" ?
> 
> There are SAVEPOINTs [1].
> 
> David
> 
> [1] https://www.postgresql.org/docs/current/sql-savepoint.html

Thank you! :)
I wasn't aware of this yet, but it will make some of my code easier to read .

--
Joost

Re: Transaction and SQL errors

[David G. Johnston]

On Mon, Apr 4, 2022 at 5:41 AM Sebastien Flaesch 
wrote:

>
> I know savepoints are supported by PostgreSQL, but what about
> existing/legacy code that has tons of lines that rely on the behavior of
> other DB engines?
>
> Would you ask programmers to put a savepoint / release savepoint around
> each SQL statement inside a TX?
>
>
That does have the benefit of being a relatively straight-forward solution.

The answer to your original question is no.  To my knowledge there is
presently no one who has broadcast their intent to add this feature, now or
in the foreseeable future.

Personally, I can see where it has significant value for psql in
interactive mode because people make typos.  Application code doesn't.
That removes a whole class of problems where the feature provides benefit.

Whether "everybody is doing it" overcomes "but it's not a good thing to do"
remains to be seen but at least for now we are just saying no to an
automatic rollback setting.

David J.

Re: gist segmentation fault

[Tom Lane]

Arup Nanda  writes:
> We're preparing to upgrade postgresql from 12.8 to 14.2 but still we have 
> some bugs and issues. Most of them are our fault, but now we have problem 
> wedon't know how to play with it.
> [ crash in copy_ltree for an upgraded GIST index ]

I think this is the same problem already reported at [1].
It's an unfortunate error in the GIST ltree opclass.
You could apply the patch [2] locally perhaps, if you
don't want to wait around for 14.3.

regards, tom lane

[1] 
https://www.postgresql.org/message-id/flat/17406-71e02820ae79bb40%40postgresql.org
[2] https://git.postgresql.org/gitweb/?p=postgresql.git;a=patch;h=7d30f59da

Trigger functions and FDW tables

[Michael Nolan]

Background:  About two years ago the membership system I developed for a
client was moved from our in-house postgresql app to civi-CRM, which uses
MySQL.  (I'm semi-retired, the move to civi-CRM is part of a long term
technology change.)

We have a FDW that simulates the old membership table that can be used to
get a record from the civi-CRM servers (in a different data center), it
takes 4-5 seconds for each query, how much of this is network delays and
how much is how long it takes to build a record from the very different
data structures used by civi-CRM is unclear.

There is also a materialized view of the old membership table that is
updated twice a day (6PM and 3AM) from the civi-CRM FDW, it has about 1
million rows and takes anywhere from 30 to 75 minutes to update.

While our membership transactions are now handled in civi-CRM, we still do
event registrations in the Postgresql system.  The problem is if a new
member is created in civi-CRM, it might be up to 15 hours until that member
record is available in the materialized view table.

We have a trigger function that handles updating associated tables when an
event registration transaction is processed (updating the accounting and
event registration systems).  It was checking the materialized view
membership table to verify that the ID was valid (and to look up some data
about the member that might relate to how an event registration is
processed.)  Of course for a brand new member the member record may not be
in the matview yet, this throws an error.

So I tried changing that trigger function to look at the simulated table
that queries the FDW.  The trigger function was hanging the database,
possibly due to the 4-5 second lag time to query the remote MySQL server or
possibly due to lockouts.  I have revered back to checking the materialized
view, but this means that some transactions are not being fully processed.

Is it not recommended to use a FDW table in a trigger function?
--
Mike Nolan
no...@tssi.com

Re: Trigger functions and FDW tables

[Laurenz Albe]

On Mon, 2022-04-04 at 09:18 -0500, Michael Nolan wrote:
> Is it not recommended to use a FDW table in a trigger function?

Yes, I would say that is not recommended.  While a lag of a few seconds,
like you describe, should not be the normal case (you should investigate that),
it is hard to exclude it in the face of network failures (you could end up
waiting for the keepalive timeout of 2 hours).

So the trigger can potentially run a long time, which makes your transaction
long.  A long transaction can block other sessions or autovacuum progress.

The rule of thumb is that you should have nothing inside a database transaction
that *could* take a long time (even if it is normally fast).

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

how to find out field size?

[Shaozhong SHI]

Postgres documentation says:  maximum field size is 1 GB.
PostgreSQL: Documentation: 12: Appendix K. PostgreSQL Limits


So, how to find out the size of column to see how far it from the limit?

Regards,

David

Re: how to find out field size?

[Adrian Klaver]

On 4/4/22 08:28, Shaozhong SHI wrote:

Postgres documentation says:  maximum field size is 1 GB.
PostgreSQL: Documentation: 12: Appendix K. PostgreSQL Limits 



So, how to find out the size of column to see how far it from the limit?


Documentation also shows:

https://www.postgresql.org/docs/current/functions-admin.html

pg_column_size ( "any" ) → integer

Shows the number of bytes used to store any individual data value. If 
applied directly to a table column value, this reflects any compression 
that was done.




Regards,

David




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Trigger functions and FDW tables

[Michael Nolan]

Thanks for confirming my suspicions, I'm working on a plan B to deal with
this as best I can.
--
Mike Nolan

On Mon, Apr 4, 2022 at 9:23 AM Laurenz Albe 
wrote:

> On Mon, 2022-04-04 at 09:18 -0500, Michael Nolan wrote:
> > Is it not recommended to use a FDW table in a trigger function?
>
> Yes, I would say that is not recommended.  While a lag of a few seconds,
> like you describe, should not be the normal case (you should investigate
> that),
> it is hard to exclude it in the face of network failures (you could end up
> waiting for the keepalive timeout of 2 hours).
>
> So the trigger can potentially run a long time, which makes your
> transaction
> long.  A long transaction can block other sessions or autovacuum progress.
>
> The rule of thumb is that you should have nothing inside a database
> transaction
> that *could* take a long time (even if it is normally fast).
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>

Re: Transaction and SQL errors

[Sebastien Flaesch]

David,

Personally, I can see where it has significant value for psql in interactive 
mode because people make typos.  Application code doesn't.  That removes a 
whole class of problems where the feature provides benefit.


Sure, application code must not have typos, but I prefer to let the DB engine 
check for SQL constraints.

Imagine the following case:

BEGIN WORK
... (some other SQL) ...
DELETE FROM items WHERE item_id = 12823  -- Can raise foreign key error
if sql-error then
...

To me it's better than:

BEGIN WORK
...
SELECT ... FROM orders WHERE item_id = 12823
if not-found then -- make sure we get no SQL error than cancels TX!
  DELETE FROM items WHERE item_id = 12823
endif
...

... and not even sure it's valid atomic code depending on isolation level...

A good argument for PostgreSQL's behavior would be that it's better to cancel 
the whole transaction and restart all SQL commands.

However, legacy code is often spaghetti code where one function starts the TX, 
then calls other functions doing SQL ... ( yes, good candidate for savepoints 
usage! )

Anyway, thanks for the info, nothing planed short term, and that's what I was 
asking for.

Cheers!
Seb

Re: gist segmentation fault

[Arup Nanda]

Thanks, Том Lane.

We will wait for the release.



Sent with ProtonMail secure email.
--- Original Message ---
On Monday, April 4th, 2022 at 17:12, Tom Lane  wrote:


> Arup Nanda arup.na...@protonmail.com writes:
>
> > We're preparing to upgrade postgresql from 12.8 to 14.2 but still we have 
> > some bugs and issues. Most of them are our fault, but now we have problem 
> > wedon't know how to play with it.
> > [ crash in copy_ltree for an upgraded GIST index ]
>
>
> I think this is the same problem already reported at [1].
> It's an unfortunate error in the GIST ltree opclass.
> You could apply the patch [2] locally perhaps, if you
> don't want to wait around for 14.3.
>
> regards, tom lane
>
> [1] 
> https://www.postgresql.org/message-id/flat/17406-71e02820ae79bb40%40postgresql.org
> [2] https://git.postgresql.org/gitweb/?p=postgresql.git;a=patch;h=7d30f59da

Re: Select .... where id not in (....) returns 0 incorrectly

[David Rowley]

On Tue, 5 Apr 2022 at 01:21, J. Roeleveld  wrote:
> Personally, I think NULL should be treated as a seperate value and not lead to
> strange behaviour.

I think the rationale behind IN and NOT IN are that c IN(1,2,3) is
equivalent of writing: c = 1 OR c = 2 OR c = 3, whereas NOT IN(1,2,3)
would be the same as c <> 1 AND c <> 2 AND c <> 3.  You can imagine
what would happen in the latter case if you replaced 3 with NULL. "c
<> NULL" is NULL therefore, due to the quals being ANDed, will cause
the WHERE clause not to match anything.

In any case, it's what the SQL standard says, so that's the way we do it.

David

Load Balancer with PostgreSQL

[Lucas]

Hi guys.

I was wondering if you guys could share what are you using for Load Balancing 
with PG?
It has to be smart enough to understand when a server is too busy to get a 
request, so it sends it to the other slaves.
I understand there is PGPool available for this, is there any solution tool you 
guys use? Also, for context, my DB stack is deployed in AWS on EC2 instances.

Thanks!
Lucas

Where is the best place to read for the paper around database?

[BeginnerC]

Hello everyone,
I am a student who are interest in database,can anyone recommend a 
website where can read and download the papers around database?

Thanks in advance!
Best wishes,
Wen Yi.

JSON question

[Dave Ekhaus]

Hi All

I'm experimenting with PostgreSQL's JSON features and have a question.

Say we have the following JSON (which will be stored in a table named 
'resources' - in its 'schema' column ...

{
"name": {
"type": "string",
"label": "Name",
"accessor": "name",
"is_association": false
},
"books": {
"type": "array",
"label": "Books",
"accessor": "books",
"is_association": true
},
"email": {
"type": "string",
"label": "Email",
"accessor": "email",
"is_association": false
},
"date_of_birth": {
"type": "date",
"label": "Birthday",
"accessor": "date_of_birth",
"is_association": false
}
}

[NOTE: the JSON above is the 'schema' attribute of one record in the 
'resources' table. There will be many rows in the 'resources' table]

How can I query the 'resources' table to return the rows in which there's at 
least one JSON object whose 'type' equals 'array' ?

Thanks
Dave

Re: Select .... where id not in (....) returns 0 incorrectly

[J. Roeleveld]

On Monday, April 4, 2022 10:47:51 PM CEST David Rowley wrote:
> On Tue, 5 Apr 2022 at 01:21, J. Roeleveld  wrote:
> > Personally, I think NULL should be treated as a seperate value and not
> > lead to strange behaviour.
> 
> I think the rationale behind IN and NOT IN are that c IN(1,2,3) is
> equivalent of writing: c = 1 OR c = 2 OR c = 3, whereas NOT IN(1,2,3)
> would be the same as c <> 1 AND c <> 2 AND c <> 3.  You can imagine
> what would happen in the latter case if you replaced 3 with NULL. "c
> <> NULL" is NULL therefore, due to the quals being ANDed, will cause
> the WHERE clause not to match anything.
> 
> In any case, it's what the SQL standard says, so that's the way we do it.

I agree with following the standard.

If I would feel really strongly about this (I don't), it would be up to me to 
try and convince others.
And I have got better things to do with my time. :)

--
Joost