Re: Help understanding SIReadLock growing without bound on completed transaction

[Thomas Munro]

On Fri, May 22, 2020 at 7:48 AM Mike Klaas  wrote:
> It's my understanding that these locks should be cleared when there are no 
> conflicting transactions.  These locks had existed for > 1 week and we have 
> no transactions that last more than a few seconds (the oldest transaction in 
> pg_stat_activity is always < 1minute old).
> Why would a transaction that is finished continue accumulating locks over 
> time?

Predicate locks are released by ClearOldPredicateLocks(), which
releases SERIALIZABLEXACTs once they are no longer interesting.  It
has a  conservative idea of what is no longer interesting: it waits
until the lowest xmin across active serializable snapshots is >= the
transaction's finishedBefore xid, which was the system's next xid (an
xid that hasn't been used yet*) at the time the SERIALIZABLEXACT
committed.  One implication of this scheme is that SERIALIZABLEXACTs
are cleaned up in commit order.  If you somehow got into a state where
a few of them were being kept around for a long time, but others
committed later were being cleaned up (which I suppose must be the
case or your system would be complaining about running out of
SERIALIZABLEXACTs), that might imply that there is a rare leak
somewhere in this scheme.  In the past I have wondered if there might
be a problem with wraparound in the xid tracking for finished
transactions, but I haven't worked out the details (transaction ID
wraparound is both figuratively and literally the Ground Hog Day of
PostgreSQL bug surfaces).

*Interestingly, it takes an unlocked view of that value, but that
doesn't seem relevant here; it could see a value that's too low, not
too high.

Re: query, probably needs window functions

[David G. Johnston]

On Fri, May 22, 2020 at 12:38 PM Michael Lewis  wrote:

> I believe something like this is what you want. You might be able to do it
> without a sub-query by comparing the current name value to the lag value
> and null it out if it's the same.
>

This.  I misread the question.  You might also consider just outputting one
row per person and output the related phone numbers using
string_agg(phone.number, E'\n')

David J.

Re: query, probably needs window functions

[Michael Lewis]

I believe something like this is what you want. You might be able to do it
without a sub-query by comparing the current name value to the lag value
and null it out if it's the same.

select
case when row_number = 1 then id end AS id,
case when row_number = 1 then name end as name,
phone.number
from(
select person.id, person.name, phone.number, row_number() partition
by( phone.person_id order by phone.number ) as row_number
from person
join phone on person.id = phone.person_id
) AS sub
order by name, row_number;

Re: Query returns no rows in pg_basebackup cluster

[Andrus]

Hi!


ERROR:  item order invariant violated for index "desktop_baas_liigid_idx"
DETAIL:  Lower index tid=(3,15) (points to index tid=(16,4098)) higher index tid=(3,16) (points to index tid=(17,4098)) page 
lsn=292/630C0CE8.

SQL state: XX002

Uh huh ... and I'll bet the same test on the source server is just fine?
I don't find it surprising in the least that different platforms have
different ideas on fine points like how to sort a leading underscore.
Those things just aren't that well standardized.


This column is not used for locale specific data.

Running 

alter table desktop alter  baas type char(8) collate ucs_basic 

fixes the issue. 
Is this fix reasonable ?

What other issues may occur ?

Can base backup created in windows using pg_basecakup used in Linux without 
such fix?

Andrus.

Re: query, probably needs window functions

[David G. Johnston]

On Friday, May 22, 2020, Scott Ribe  wrote:

> given, let's say:
>
> create table person (id int not null, name varchar);
> create table phone (id int not null, person_id int not null, number
> varchar);
>
> select person.*, phone.number from person join phone on (person.id =
> phone.person_id) order by...
>
> How would you get results where only the first row for a person was filled
> in, with rest of that person's phones showing blanks for those columns? I'm
> guessing that window functions provide this capability, but I don't know
> how.
>
>
If a left join doesn’t give you the answer you want you should probably
provide exact input and output data that you are working with/toward.

David J.

query, probably needs window functions

[Scott Ribe]

given, let's say: 

create table person (id int not null, name varchar);
create table phone (id int not null, person_id int not null, number varchar);

select person.*, phone.number from person join phone on (person.id = 
phone.person_id) order by...

How would you get results where only the first row for a person was filled in, 
with rest of that person's phones showing blanks for those columns? I'm 
guessing that window functions provide this capability, but I don't know how.

--
Scott Ribe
scott_r...@elevated-dev.com
https://www.linkedin.com/in/scottribe/

Re: How to get the OID of a view

[David G. Johnston]

On Fri, May 22, 2020 at 9:15 AM stan  wrote:

> I am trying to write a query to return the names, and data types of all the
> columns in a view. It has been pointed out to me that the best approach
> would be using pg_catalog. OK, so I found pg_view, which I can get the
> names
> of a the views from and pg_attribute which can give me the column names,
> but it looks like i need to join this on OID, and pg_table does not have
> that data.
>
>
The table you want is pg_class:

https://www.postgresql.org/docs/12/catalog-pg-class.html

If the system views listed in [1] don't provide you what you need you need
to fall-back to the system tables listed in [2].

[1] https://www.postgresql.org/docs/12/views-overview.html
[2] https://www.postgresql.org/docs/12/catalogs-overview.html

David J.

Re: How to get the OID of a view

[Adrian Klaver]

On 5/22/20 9:15 AM, stan wrote:

I am trying to write a query to return the names, and data types of all the
columns in a view. It has been pointed out to me that the best approach
would be using pg_catalog. OK, so I found pg_view, which I can get the names
of a the views from and pg_attribute which can give me the column names,
but it looks like i need to join this on OID, and pg_table does not have
that data.




I'm guessing you mean pg_tables.

In any case, go straight to the source pg_class:

\dv
  List of relations
 Schema |   Name   | Type |  Owner
+--+--+--
 public | tag_litem| view | postgres
 public | tag_short_status | view | postgres

select oid, relname, relkind from pg_class where relname = 'tag_litem';
  oid  |  relname  | relkind
---+---+-
 60558 | tag_litem | v

Where relkind = 'v' means view:

https://www.postgresql.org/docs/12/catalog-pg-class.html

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

Re: How to get the OID of a view

[Charles Clavadetscher]

Hello
--

> On 22.05.2020, at 18:15, stan  wrote:
> 
> I am trying to write a query to return the names, and data types of all the
> columns in a view. It has been pointed out to me that the best approach
> would be using pg_catalog. OK, so I found pg_view, which I can get the names
> of a the views from and pg_attribute which can give me the column names,
> but it looks like i need to join this on OID, and pg_table does not have
> that data.
> 
> 
> -- 
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
>-- Benjamin Franklin
> 
> 

You find the oid of the views in pg_catalog.pg_class (relkind 'v').

Regards
Charles

Re: How to get the OID of a view

[Tom Lane]

stan  writes:
> I am trying to write a query to return the names, and data types of all the
> columns in a view. It has been pointed out to me that the best approach
> would be using pg_catalog. OK, so I found pg_view, which I can get the names
> of a the views from and pg_attribute which can give me the column names,
> but it looks like i need to join this on OID, and pg_table does not have
> that data.

Yeah, pg_tables is just a user-friendly view, it's not really that
useful for programmatic work.  I'd look directly at pg_class and
pg_attribute for this problem.

A good way to learn what to do is to see what psql does for its
various \d commands --- if you start it with the -E option you'll
see the underlying SQL it issues.  It'll likely be more complicated
than you want, but you can strip away what's not useful for you.

regards, tom lane

How to get the OID of a view

[stan]

I am trying to write a query to return the names, and data types of all the
columns in a view. It has been pointed out to me that the best approach
would be using pg_catalog. OK, so I found pg_view, which I can get the names
of a the views from and pg_attribute which can give me the column names,
but it looks like i need to join this on OID, and pg_table does not have
that data.


-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Re: pg_dump crashes

[Adrian Klaver]

On 5/22/20 8:17 AM, Nico De Ranter wrote:



On Fri, May 22, 2020 at 5:14 PM Adrian Klaver > wrote:


On 5/22/20 8:05 AM, Nico De Ranter wrote:
 >

 >
 >     Assuming the above matches:
 >
 >     COPY public.file (fileid, fileindex, jobid, pathid, filenameid,
 >     deltaseq, markid, lstat, md5)
 >
 >     the '' would be for the md5 field. I'm
going to say
 >     that is important.
 >
 >
 > But that would be content of the database only. The should matter
for
 > the application but not for a dump of the database, right?

Also what does:

\d public.file

show?

In particular are there any triggers on the table?


bacula=# \d public.file
                                  Table "public.file"
    Column   |   Type   | Collation | Nullable |               Default
+--+---+--+--
  fileid     | bigint   |           | not null | 
nextval('file_fileid_seq'::regclass)

  fileindex  | integer  |           | not null | 0
  jobid      | integer  |           | not null |
  pathid     | integer  |           | not null |
  filenameid | integer  |           | not null |
  deltaseq   | smallint |           | not null | 0
  markid     | integer  |           | not null | 0
  lstat      | text     |           | not null |
  md5        | text     |           | not null |
Indexes:
     "file_pkey" PRIMARY KEY, btree (fileid)
     "file_jobid_idx" btree (jobid)
     "file_jpfid_idx" btree (jobid, pathid, filenameid)



Following up on the max(bigint), I tried

    SELECT md5 FROM public.file where fileid >2087994666;

and got

    ERROR:  compressed data is corrupted

So it does look like those entries are killing it.  Now for the 
million-dollar question: how do I get them out?


This is a Bacula table and I'm guessing you are using:

https://www.bacula.org/9.6.x-manuals/en/main/Configuring_Director.html#11585

signature=MD5
An MD5 signature will be computed for all files saved. Adding this 
option generates about 5% extra overhead for each file saved. In 
addition to the additional CPU time, the MD5 signature adds 16 more 
bytes per file to your catalog. We strongly recommend that this option 
or the SHA1 option be specified as a default for all files.


Maybe you can ask the Bacula folks how that is actually calculated and 
test that on row, to see if you can overwrite the bad data.




Nico

--





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

Re: pg_dump crashes

[Adrian Klaver]

On 5/22/20 8:17 AM, Nico De Ranter wrote:



On Fri, May 22, 2020 at 5:14 PM Adrian Klaver > wrote:


On 5/22/20 8:05 AM, Nico De Ranter wrote:
 >

 >
 >     Assuming the above matches:
 >
 >     COPY public.file (fileid, fileindex, jobid, pathid, filenameid,
 >     deltaseq, markid, lstat, md5)
 >
 >     the '' would be for the md5 field. I'm
going to say
 >     that is important.
 >
 >
 > But that would be content of the database only. The should matter
for
 > the application but not for a dump of the database, right?

Also what does:

\d public.file

show?

In particular are there any triggers on the table?


bacula=# \d public.file
                                  Table "public.file"
    Column   |   Type   | Collation | Nullable |               Default
+--+---+--+--
  fileid     | bigint   |           | not null | 
nextval('file_fileid_seq'::regclass)

  fileindex  | integer  |           | not null | 0
  jobid      | integer  |           | not null |
  pathid     | integer  |           | not null |
  filenameid | integer  |           | not null |
  deltaseq   | smallint |           | not null | 0
  markid     | integer  |           | not null | 0
  lstat      | text     |           | not null |
  md5        | text     |           | not null |
Indexes:
     "file_pkey" PRIMARY KEY, btree (fileid)
     "file_jobid_idx" btree (jobid)
     "file_jpfid_idx" btree (jobid, pathid, filenameid)



Following up on the max(bigint), I tried

    SELECT md5 FROM public.file where fileid >2087994666;

and got

    ERROR:  compressed data is corrupted

So it does look like those entries are killing it.  Now for the 
million-dollar question: how do I get them out?


Do you have recent previous backup?



Nico

--

Nico De Ranter

Operations Engineer





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

Re: pg_dump crashes

[Adrian Klaver]

On 5/22/20 8:13 AM, Nico De Ranter wrote:








bacula=# SELECT md5 FROM public.file where fileid = 4557430888798830399;
  md5
-
(0 rows)
So that fileid is bogus too (max(bigint) I assume)


No:

select 4557430888798830399::bigint;
int8
-
 4557430888798830399
(1 row)

It means it cannot find that fileid. I putting that down to file corruption.




--

Nico De Ranter



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

Re: Query returns no rows in pg_basebackup cluster

[Tom Lane]

"Andrus"  writes:
>> Possibly you could try running contrib/amcheck on the index in question
>> and see if it reports any issues.

> I tried and it reports error

> ERROR:  item order invariant violated for index "desktop_baas_liigid_idx"
> DETAIL:  Lower index tid=(3,15) (points to index tid=(16,4098)) higher index 
> tid=(3,16) (points to index tid=(17,4098)) page 
> lsn=292/630C0CE8.
> SQL state: XX002

Uh huh ... and I'll bet the same test on the source server is just fine?

I don't find it surprising in the least that different platforms have
different ideas on fine points like how to sort a leading underscore.
Those things just aren't that well standardized.

regards, tom lane

Re: Query returns no rows in pg_basebackup cluster

[Andrus]

Hi!


The sorting rules for this locale must be the same in both platforms.
Only locale names are different.

I think they are less alike than you hoped, because if they were alike,
you wouldn't be seeing this problem.
Possibly you could try running contrib/amcheck on the index in question
and see if it reports any issues.


I tried and it reports error

ERROR:  item order invariant violated for index "desktop_baas_liigid_idx"
DETAIL:  Lower index tid=(3,15) (points to index tid=(16,4098)) higher index tid=(3,16) (points to index tid=(17,4098)) page 
lsn=292/630C0CE8.

SQL state: XX002

Andrus.

Re: pg_dump crashes

[Nico De Ranter]

On Fri, May 22, 2020 at 5:14 PM Adrian Klaver 
wrote:

> On 5/22/20 8:05 AM, Nico De Ranter wrote:
> >
>
> >
> > Assuming the above matches:
> >
> > COPY public.file (fileid, fileindex, jobid, pathid, filenameid,
> > deltaseq, markid, lstat, md5)
> >
> > the '' would be for the md5 field. I'm going to
> say
> > that is important.
> >
> >
> > But that would be content of the database only. The should matter for
> > the application but not for a dump of the database, right?
>
> Also what does:
>
> \d public.file
>
> show?
>
> In particular are there any triggers on the table?
>

bacula=# \d public.file
 Table "public.file"
   Column   |   Type   | Collation | Nullable |   Default

+--+---+--+--
 fileid | bigint   |   | not null |
nextval('file_fileid_seq'::regclass)
 fileindex  | integer  |   | not null | 0
 jobid  | integer  |   | not null |
 pathid | integer  |   | not null |
 filenameid | integer  |   | not null |
 deltaseq   | smallint |   | not null | 0
 markid | integer  |   | not null | 0
 lstat  | text |   | not null |
 md5| text |   | not null |
Indexes:
"file_pkey" PRIMARY KEY, btree (fileid)
"file_jobid_idx" btree (jobid)
"file_jpfid_idx" btree (jobid, pathid, filenameid)



Following up on the max(bigint), I tried

   SELECT md5 FROM public.file where fileid >2087994666;

and got

   ERROR:  compressed data is corrupted

So it does look like those entries are killing it.  Now for the
million-dollar question: how do I get them out?

Nico

-- 

Nico De Ranter

Operations Engineer

T. +32 16 38 72 10







eSATURNUS
Philipssite 5, D, box 28
3001 Leuven – Belgium

T. +32 16 40 12 82
F. +32 16 40 84 77
www.esaturnus.com



*For Service & Support :*

Support Line Belgium: +32 2 2009897

Support Line International: +44 12 56 68 38 78

Or via email : medical.services...@sony.com

Re: Query to get name a data type of a view

[David G. Johnston]

On Friday, May 22, 2020, stan  wrote:

> When I run the following query,
>
>
> SELECT column_name,data_type
> FROM information_schema.columns
> WHERE table_name = 'mfg_part_view';
>
>
> I get the following result:
>
>  column_name  | data_type
>  --+---
>   mfg  | USER-DEFINED
>   mfg_part_no  | character varying
>   unit | USER-DEFINED
>
>
> I need to return the name, and data type of each column for the specified
> view.
>

Use the pg_catalog schema “tables” directly instead of the SQL standard
information_schema view.  The later doesn’t provide detail of
PostgreSQL-specific features by definition.

David J.

Re: btree_gist extension - gbt_cash_union return type

[Tim Kane]

Thank you Tom, I appreciate the thorough explanation.

Good to confirm that it’s of no consequence.

Tim

On Fri, 22 May 2020 at 15:44, Tom Lane  wrote:

> Tim Kane  writes:
> > I've noticed a discrepancy in the return type for the gbt_cash_union
> > function...
> > On fresh instances of postgres 9.6.11, where the btree_gist extension is
> > newly created (version 1.2) yields a gbt_cash_union function with a
> return
> > type of gbtreekey16
>
> ... which is correct.
>
> > While instances that have been upgraded from 9.6.2 to 9.6.11, where the
> > btree_gist was originally installed as 1.0 and then upgraded from 1.0 to
> > 1.2 - that same function has a return type of gbtreekey8
>
> Hm.  I think this is an oversight in commit 749a787c5; we were focused
> on fixing the functions' argument types and forgot that there were any
> return-type changes.
>
> However, I'm not too fussed about it.  Nothing checks those signatures
> at run-time, so it's basically cosmetic.  The reason for the pushups
> in 749a787c5 was to ensure that we could name the functions in ALTER
> FUNCTION; but that just depends on the argument types, so it's not
> a reason to worry either.
>
> > Is it safe/recommended to modify this function to return gbtreekey16?
>
> I wouldn't sweat about it.  If you did want to fix it, it'd have to be
> a manual UPDATE on pg_proc, there not being any ALTER FUNCTION way
> to do it.  On the whole, the risk of fat-fingering the update and
> thereby hosing your database seems to outweigh any benefit.
>
> > Perhaps safer still to drop the extension and recreate it?
>
> That would force dropping the indexes that depend on it, so
> it seems like a big overreaction.
>
> regards, tom lane
>

Re: pg_dump crashes

[Adrian Klaver]

On 5/22/20 8:05 AM, Nico De Ranter wrote:






Assuming the above matches:

COPY public.file (fileid, fileindex, jobid, pathid, filenameid,
deltaseq, markid, lstat, md5)

the '' would be for the md5 field. I'm going to say
that is important.


But that would be content of the database only. The should matter for 
the application but not for a dump of the database, right?


Also what does:

\d public.file

show?

In particular are there any triggers on the table?




--

Nico De Ranter








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

Re: pg_dump crashes

[Nico De Ranter]

On Fri, May 22, 2020 at 5:09 PM Adrian Klaver 
wrote:

> On 5/22/20 8:05 AM, Nico De Ranter wrote:
> >
> > On Fri, May 22, 2020 at 5:02 PM Adrian Klaver  > > wrote:
> >
> > On 5/22/20 7:55 AM, Nico De Ranter wrote:
> >  > Correct.
> >  >
> >  > If I run 'pg_dumpall --cluster 11/main --file=dump.sql'   the end
> > of the
> >  > file looks like:
> >  >
> >  > ## cut here
> >  > 4557430888798830399 1061109567 1061109567 1061109567 1061109567
> > 16191 \N
> >  > \N ??
> >  > 4557430888798830399 1061109567 1061109567 1061109567 1061109567
> > 16191 \N
> >  > \N ??
> >  > \.
> >  >
> >  > ## cut here
> >  >
> >  > If I run 'pg_dump --table=public.file --cluster 11/main
> >  > --file=dump-2.sql bacula'  those lines are actually followed by
> > about
> >  > 850 or so lines that look ok.  I'm assuming the difference is due
> to
> >  > buffering.
> >  > However the fact that I do see a number of regular lines
> > following this
> >  > may suggest it's just garbage in the table but not really causing
> > the
> >  > issue afterall.
> >
> > Assuming the above matches:
> >
> > COPY public.file (fileid, fileindex, jobid, pathid, filenameid,
> > deltaseq, markid, lstat, md5)
> >
> > the '' would be for the md5 field. I'm going to
> say
> > that is important.
> >
> >
> > But that would be content of the database only. The should matter for
> > the application but not for a dump of the database, right?
>
> I would think that depends on what '???' is really representing.
>
> In the database try:
>
> SELECT md5 FROM public.file where fileid = 4557430888798830399;
>
>

bacula=# SELECT md5 FROM public.file where fileid = 4557430888798830399;
 md5
-
(0 rows)

So that fileid is bogus too (max(bigint) I assume)

-- 

Nico De Ranter

Operations Engineer

T. +32 16 38 72 10







eSATURNUS
Philipssite 5, D, box 28
3001 Leuven – Belgium

T. +32 16 40 12 82
F. +32 16 40 84 77
www.esaturnus.com



*For Service & Support :*

Support Line Belgium: +32 2 2009897

Support Line International: +44 12 56 68 38 78

Or via email : medical.services...@sony.com

Query to get name a data type of a view

[stan]

When I run the following query,


SELECT column_name,data_type
FROM information_schema.columns
WHERE table_name = 'mfg_part_view';


I get the following result:

 column_name  | data_type 
 --+---
  mfg  | USER-DEFINED
  mfg_part_no  | character varying
  unit | USER-DEFINED
  descrip  | character varying
  mfg_part_key | integer
(5 rows)

The SQL that creates this view is as follows:

CREATE VIEW mfg_part_view as
select
mfg.name as mfg ,
mfg_part.mfg_part_no ,
costing_unit.unit ,
mfg_part.descrip ,
mfg_part.mfg_part_key
from mfg_part
right join costing_unit on
mfg_part.unit_key = costing_unit.costing_unit_key
inner join mfg on
mfg.mfg_key = mfg_part.mfg_key
WHERE mfg_part is NOT NULL
ORDER BY
mfg.name ,
mfg_part.mfg_part_no ;

Clearly that is not what I expected :-)

I need to return the name, and data type of each column for the specified
view. 

How can I do this?



-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Re: pg_dump crashes

[Nico De Ranter]

On Fri, May 22, 2020 at 5:07 PM Adrian Klaver 
wrote:

> On 5/22/20 7:48 AM, Nico De Ranter wrote:
> > The original server was running 9.5.14
> > The system I am currently testing on is  11.8
> >
> > 2 fields are marked as 'extended'.   However if I understand correctly
> > the table isn't actually toasted:
> >
> >
> >oid  |table_schema|   table_name| total_bytes  |
> >total|   index|   toast|   table
> >
> ---++-+--++++
> >   19601 | public | file| 147648061440 |
> > 138 GB | 57 GB  | 8192 bytes | 80 GB
>
> What query are you using to get above?
>

SELECT oid, table_schema, table_name,
total_bytes,
pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS
table_bytes FROM (
  SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
  , c.reltuples AS row_estimate
  , pg_total_relation_size(c.oid) AS total_bytes
  , pg_indexes_size(c.oid) AS index_bytes
  , pg_total_relation_size(reltoastrelid) AS toast_bytes
  FROM pg_class c
  LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE relkind = 'r'
  ) a) a order by total_bytes desc;

 Stolen from stackoverflow :-)

Nico

-- 

Nico De Ranter

Operations Engineer

T. +32 16 38 72 10







eSATURNUS
Philipssite 5, D, box 28
3001 Leuven – Belgium

T. +32 16 40 12 82
F. +32 16 40 84 77
www.esaturnus.com



*For Service & Support :*

Support Line Belgium: +32 2 2009897

Support Line International: +44 12 56 68 38 78

Or via email : medical.services...@sony.com

Re: pg_dump crashes

[Adrian Klaver]

On 5/22/20 8:05 AM, Nico De Ranter wrote:


On Fri, May 22, 2020 at 5:02 PM Adrian Klaver > wrote:


On 5/22/20 7:55 AM, Nico De Ranter wrote:
 > Correct.
 >
 > If I run 'pg_dumpall --cluster 11/main --file=dump.sql'   the end
of the
 > file looks like:
 >
 > ## cut here
 > 4557430888798830399 1061109567 1061109567 1061109567 1061109567
16191 \N
 > \N ??
 > 4557430888798830399 1061109567 1061109567 1061109567 1061109567
16191 \N
 > \N ??
 > \.
 >
 > ## cut here
 >
 > If I run 'pg_dump --table=public.file --cluster 11/main
 > --file=dump-2.sql bacula'  those lines are actually followed by
about
 > 850 or so lines that look ok.  I'm assuming the difference is due to
 > buffering.
 > However the fact that I do see a number of regular lines
following this
 > may suggest it's just garbage in the table but not really causing
the
 > issue afterall.

Assuming the above matches:

COPY public.file (fileid, fileindex, jobid, pathid, filenameid,
deltaseq, markid, lstat, md5)

the '' would be for the md5 field. I'm going to say
that is important.


But that would be content of the database only. The should matter for 
the application but not for a dump of the database, right?


I would think that depends on what '???' is really representing.

In the database try:

SELECT md5 FROM public.file where fileid = 4557430888798830399;


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

Re: pg_dump crashes

[Adrian Klaver]

On 5/22/20 7:48 AM, Nico De Ranter wrote:

The original server was running 9.5.14
The system I am currently testing on is  11.8

2 fields are marked as 'extended'.   However if I understand correctly 
the table isn't actually toasted:



   oid  |    table_schema    |       table_name        | total_bytes  | 
   total    |   index    |   toast    |   table

---++-+--++++
  19601 | public             | file                    | 147648061440 | 
138 GB     | 57 GB      | 8192 bytes | 80 GB


What query are you using to get above?




On Fri, May 22, 2020 at 3:58 PM Andreas Kretschmer 
mailto:andr...@a-kretschmer.de>> wrote:




Am 22.05.20 um 14:37 schrieb Nico De Ranter:
 > Postgres version: 9.5

which minor-version?

Can you check if the table has TOAST-Tables? Can you try to select all
columns but not TOASTed columns?
Maybe there is data-corruption only in toast-tables.


Regards, Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.

www.2ndQuadrant.com 





--

Nico De Ranter

Operations Engineer

T. +32 16 38 72 10







eSATURNUS
Philipssite 5, D, box 28
3001 Leuven – Belgium



T. +32 16 40 12 82
F. +32 16 40 84 77
www.esaturnus.com 

** 

*For Service & Support :*

Support Line Belgium: +32 2 2009897

Support Line International: +44 12 56 68 38 78

Or via email : medical.services...@sony.com 







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

Re: pg_dump crashes

[Nico De Ranter]

On Fri, May 22, 2020 at 5:02 PM Adrian Klaver 
wrote:

> On 5/22/20 7:55 AM, Nico De Ranter wrote:
> > Correct.
> >
> > If I run 'pg_dumpall --cluster 11/main --file=dump.sql'   the end of the
> > file looks like:
> >
> > ## cut here
> > 4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N
> > \N ??
> > 4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N
> > \N ??
> > \.
> >
> > ## cut here
> >
> > If I run 'pg_dump --table=public.file --cluster 11/main
> > --file=dump-2.sql bacula'  those lines are actually followed by about
> > 850 or so lines that look ok.  I'm assuming the difference is due to
> > buffering.
> > However the fact that I do see a number of regular lines following this
> > may suggest it's just garbage in the table but not really causing the
> > issue afterall.
>
> Assuming the above matches:
>
> COPY public.file (fileid, fileindex, jobid, pathid, filenameid,
> deltaseq, markid, lstat, md5)
>
> the '' would be for the md5 field. I'm going to say
> that is important.
>

But that would be content of the database only. The should matter for the
application but not for a dump of the database, right?


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

-- 

Nico De Ranter

Operations Engineer

T. +32 16 38 72 10







eSATURNUS
Philipssite 5, D, box 28
3001 Leuven – Belgium

T. +32 16 40 12 82
F. +32 16 40 84 77
www.esaturnus.com



*For Service & Support :*

Support Line Belgium: +32 2 2009897

Support Line International: +44 12 56 68 38 78

Or via email : medical.services...@sony.com

Re: pg_dump crashes

[Adrian Klaver]

On 5/22/20 7:55 AM, Nico De Ranter wrote:

Correct.

If I run 'pg_dumpall --cluster 11/main --file=dump.sql'   the end of the 
file looks like:


## cut here
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??

\.

## cut here

If I run 'pg_dump --table=public.file --cluster 11/main 
--file=dump-2.sql bacula'  those lines are actually followed by about 
850 or so lines that look ok.  I'm assuming the difference is due to 
buffering.
However the fact that I do see a number of regular lines following this 
may suggest it's just garbage in the table but not really causing the 
issue afterall.


Assuming the above matches:

COPY public.file (fileid, fileindex, jobid, pathid, filenameid, 
deltaseq, markid, lstat, md5)


the '' would be for the md5 field. I'm going to say 
that is important.




Nico










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

Re: pg_dump crashes

[Nico De Ranter]

Correct.

If I run 'pg_dumpall --cluster 11/main --file=dump.sql'   the end of the
file looks like:

## cut here
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N \N
??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N \N
??
\.

## cut here

If I run 'pg_dump --table=public.file --cluster 11/main --file=dump-2.sql
bacula'  those lines are actually followed by about 850 or so lines that
look ok.  I'm assuming the difference is due to buffering.
However the fact that I do see a number of regular lines following this may
suggest it's just garbage in the table but not really causing the issue
afterall.

Nico



On Fri, May 22, 2020 at 4:47 PM Adrian Klaver 
wrote:

> On 5/22/20 6:40 AM, Nico De Ranter wrote:
> > I was just trying that.  It's always the same (huge) table that crashes
> > the pg_dump.   Running a dump excluding that one table goes fine,
> > running a dump of only that one table crashes.
> > In the system logs I always see a segfault
> >
> > May 22 15:22:14 core4 kernel: [337837.874618] postgres[1311]: segfault
> > at 7f778008ed0d ip 55f197ccc008 sp 7ffdd1fc15a8 error 4 in
> > postgres[55f1977c+727000]
> >
> > It doesn't seem to be an Out-of-memory thing (at least not on the OS
> level).
> > The database is currently installed on a dedicated server with 32GB
> > RAM.   I tried tweaking some of the memory parameters for postgres, but
> > the crash always happens at the exact same spot (if I run pg_dump for
> > that one table with and without memory tweaks the resulting files are
> > identical).
> >
> > One thing I just noticed looking at the dump file: at around the end of
> > the file I see this:
>
> So the below is the output from?:
>
> pg_dumpall --cluster 11/main --file=dump.sql
>
> >
> > 2087983804 516130 37989 2218636 3079067 0 0 P4B BcISC IGk L BOT BOP A jC
> > BAA I BeMj/b BceUl6 BehUAn 0Ms A C I4p9CBfUiSeAPU4eDuipKQ
> > *4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191
> > \N \N ??
> > 4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N
> > \N ??
> > 4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N
> > \N ??
> > 4557430888798830399 1061109567 1061109567 1145127487 1413694803 21071 \N
> > \N ??
> > 4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N
> > \N ??
> > 4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N
> > \N ??
> > 4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N
> > \N ??
> > 4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N
> > \N ??
> > 4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N
> > \N ??
> > 4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N
> > \N ??
> > 4557430888798830399 1061109567 1061109567 1145127487 1413694803 21071 \N
> > \N ??
> > 4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N
> > \N ??
> > 4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N
> > \N ??
> > 4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N
> > \N ??
> > 4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N
> > \N ??
> > 4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N
> > \N ??
> > 4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N
> > \N ??
> > 6071772946555290175 1056985679 1061109567 1061109567 1061109567 16191 \N
> > \N ??
> > 4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N
> > \N ??
> > 4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N
> > \N ??
> > 4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N
> > \N ??
> > 4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N
> > \N ??
> > 4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N
> > \N ??
> > 4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N
> > \N ??
> > 4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N
> > \N ??
> > 4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N
> > \N ??
> > 

Re: pg_dump crashes

[Nico De Ranter]

The original server was running 9.5.14
The system I am currently testing on is  11.8

2 fields are marked as 'extended'.   However if I understand correctly the
table isn't actually toasted:


  oid  |table_schema|   table_name| total_bytes  |
total|   index|   toast|   table
---++-+--++++
 19601 | public | file| 147648061440 | 138
GB | 57 GB  | 8192 bytes | 80 GB


On Fri, May 22, 2020 at 3:58 PM Andreas Kretschmer 
wrote:

>
>
> Am 22.05.20 um 14:37 schrieb Nico De Ranter:
> > Postgres version: 9.5
>
> which minor-version?
>
> Can you check if the table has TOAST-Tables? Can you try to select all
> columns but not TOASTed columns?
> Maybe there is data-corruption only in toast-tables.
>
>
> Regards, Andreas
>
> --
> 2ndQuadrant - The PostgreSQL Support Company.
> www.2ndQuadrant.com
>
>
>
>

-- 

Nico De Ranter

Operations Engineer

T. +32 16 38 72 10







eSATURNUS
Philipssite 5, D, box 28
3001 Leuven – Belgium

T. +32 16 40 12 82
F. +32 16 40 84 77
www.esaturnus.com



*For Service & Support :*

Support Line Belgium: +32 2 2009897

Support Line International: +44 12 56 68 38 78

Or via email : medical.services...@sony.com

Re: pg_dump crashes

[Adrian Klaver]

On 5/22/20 6:40 AM, Nico De Ranter wrote:
I was just trying that.  It's always the same (huge) table that crashes 
the pg_dump.   Running a dump excluding that one table goes fine, 
running a dump of only that one table crashes.

In the system logs I always see a segfault

May 22 15:22:14 core4 kernel: [337837.874618] postgres[1311]: segfault 
at 7f778008ed0d ip 55f197ccc008 sp 7ffdd1fc15a8 error 4 in 
postgres[55f1977c+727000]


It doesn't seem to be an Out-of-memory thing (at least not on the OS level).
The database is currently installed on a dedicated server with 32GB 
RAM.   I tried tweaking some of the memory parameters for postgres, but 
the crash always happens at the exact same spot (if I run pg_dump for 
that one table with and without memory tweaks the resulting files are 
identical).


One thing I just noticed looking at the dump file: at around the end of 
the file I see this:


So the below is the output from?:

pg_dumpall --cluster 11/main --file=dump.sql



2087983804 516130 37989 2218636 3079067 0 0 P4B BcISC IGk L BOT BOP A jC 
BAA I BeMj/b BceUl6 BehUAn 0Ms A C I4p9CBfUiSeAPU4eDuipKQ
*4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 
\N \N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1145127487 1413694803 21071 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1145127487 1413694803 21071 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
6071772946555290175 1056985679 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??*
2087983833 554418 37989 5405605 14507502 0 0 P4B Bb8c/ IGk L BOS BOP A 
Lfh BAA Bg BeMj+2 Bd1LVN BehUAl rlx ABA TOR


It looks suspicious however there are about 837 more lines before the 
output stops.


Nico

On Fri, May 22, 2020 at 3:27 PM Adrian Klaver > wrote:


On 5/22/20 5:37 AM, Nico De Ranter wrote:
 > Hi all,
 >
 > Postgres version: 9.5
 > OS: Ubuntu 18.04.4
 >
 > I have a 144GB Bacula database that crashes the postgres daemon
when I
 > try to do a pg_dump.
 > At some point the server ran out of diskspace for the database
storage.
 > I expanded the lvm and rebooted the server. It seemed to work fine,
 > however when I try to dump the bacula database the postgres
daemon dies
 > after about 37GB.
 >
 > I tried copying 

Re: btree_gist extension - gbt_cash_union return type

[Tom Lane]

Tim Kane  writes:
> I've noticed a discrepancy in the return type for the gbt_cash_union
> function...
> On fresh instances of postgres 9.6.11, where the btree_gist extension is
> newly created (version 1.2) yields a gbt_cash_union function with a return
> type of gbtreekey16

... which is correct.

> While instances that have been upgraded from 9.6.2 to 9.6.11, where the
> btree_gist was originally installed as 1.0 and then upgraded from 1.0 to
> 1.2 - that same function has a return type of gbtreekey8

Hm.  I think this is an oversight in commit 749a787c5; we were focused
on fixing the functions' argument types and forgot that there were any
return-type changes.

However, I'm not too fussed about it.  Nothing checks those signatures
at run-time, so it's basically cosmetic.  The reason for the pushups
in 749a787c5 was to ensure that we could name the functions in ALTER
FUNCTION; but that just depends on the argument types, so it's not
a reason to worry either.

> Is it safe/recommended to modify this function to return gbtreekey16?

I wouldn't sweat about it.  If you did want to fix it, it'd have to be
a manual UPDATE on pg_proc, there not being any ALTER FUNCTION way
to do it.  On the whole, the risk of fat-fingering the update and
thereby hosing your database seems to outweigh any benefit.

> Perhaps safer still to drop the extension and recreate it?

That would force dropping the indexes that depend on it, so
it seems like a big overreaction.

regards, tom lane

Re: Inaccurate (sometimes wildly so) row estimates for simple join

[Greg Nolle]

On 22 May 2020 at 15:30:42, Michael Lewis (mle...@entrata.com) wrote:

On Fri, May 22, 2020 at 7:27 AM Greg Nolle 
wrote:

> The crux seems to be that test_b does not have an even distribution for
> a_id values: it only has records for two of the values in the referenced
> table. This is how our real dataset is too and isn’t something we can
> really change.
>

How does it perform with extended statistics created for these two columns?

https://www.postgresql.org/docs/current/sql-createstatistics.html

I forgot to mention that I had already tried that actually, and it sadly
made no difference. I tried both dependencies and ndistinct modes and the
estimates remained the same. Having read through the docs I think that’s to
be expected since dependencies is designed to help when you have multiple
predicates and ndistinct when you’re doing grouping. Neither of which is
the case with my query.

For good measure I just tried mcv mode on PG12 with the test script I
posted and that also made no difference.

Re: Inaccurate (sometimes wildly so) row estimates for simple join

[Michael Lewis]

On Fri, May 22, 2020 at 7:27 AM Greg Nolle 
wrote:

> The crux seems to be that test_b does not have an even distribution for
> a_id values: it only has records for two of the values in the referenced
> table. This is how our real dataset is too and isn’t something we can
> really change.
>

How does it perform with extended statistics created for these two columns?

https://www.postgresql.org/docs/current/sql-createstatistics.html

Re: Suggestion to improve query performance of data validation in proc.

[Michael Lewis]

On Fri, May 22, 2020 at 2:09 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Friday, May 22, 2020, postgann2020 s  wrote:
>
>
>>
>> We are looking for a better query than "*SELECT 1  FROM
>> schema.table_name WHERE column1=structure_id1*" this query for data
>> validation.
>>
>
 If many rows match potentially, then wrapping the query with select
exists(old_query) would allow the execution to bail asap.

Re: Query returns no rows in pg_basebackup cluster

[Tom Lane]

"Andrus"  writes:
>> No, what it sounds like is the OP tried to physically replicate a
>> database on another platform with completely different sorting rules.

> The sorting rules for this locale must be the same in both platforms.
> Only locale names are different.

I think they are less alike than you hoped, because if they were alike,
you wouldn't be seeing this problem.

Possibly you could try running contrib/amcheck on the index in question
and see if it reports any issues.

regards, tom lane

Re: pg_dump crashes

[Ron]

> pg_dump: The command was: COPY public.file (fileid, fileindex, jobid, 
pathid, filenameid, deltaseq, markid, lstat, md5) TO stdout;


What happens when you run that COPY ... TO stdout; command (but redirecting 
it to /dev/null)?


On 5/22/20 8:40 AM, Nico De Ranter wrote:
I was just trying that.  It's always the same (huge) table that crashes 
the pg_dump.   Running a dump excluding that one table goes fine, running 
a dump of only that one table crashes.

In the system logs I always see a segfault

May 22 15:22:14 core4 kernel: [337837.874618] postgres[1311]: segfault at 
7f778008ed0d ip 55f197ccc008 sp 7ffdd1fc15a8 error 4 in 
postgres[55f1977c+727000]


It doesn't seem to be an Out-of-memory thing (at least not on the OS level).
The database is currently installed on a dedicated server with 32GB RAM.  
 I tried tweaking some of the memory parameters for postgres, but the 
crash always happens at the exact same spot (if I run pg_dump for that one 
table with and without memory tweaks the resulting files are identical).


One thing I just noticed looking at the dump file: at around the end of 
the file I see this:


2087983804 516130 37989 2218636 3079067 0 0 P4B BcISC IGk L BOT BOP A jC 
BAA I BeMj/b BceUl6 BehUAn 0Ms A C I4p9CBfUiSeAPU4eDuipKQ
*4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1145127487 1413694803 21071 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1145127487 1413694803 21071 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
6071772946555290175 1056985679 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??
4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N 
\N ??*
2087983833 554418 37989 5405605 14507502 0 0 P4B Bb8c/ IGk L BOS BOP A Lfh 
BAA Bg BeMj+2 Bd1LVN BehUAl rlx ABA TOR


It looks suspicious however there are about 837 more lines before the 
output stops.


Nico

On Fri, May 22, 2020 at 3:27 PM Adrian Klaver > wrote:


On 5/22/20 5:37 AM, Nico De Ranter wrote:
> Hi all,
>
> Postgres version: 9.5
> OS: Ubuntu 18.04.4
>
> I have a 144GB Bacula database that crashes the postgres daemon when I
> try to do a pg_dump.
> At some point the server ran out of diskspace for the database storage.
> I expanded the lvm and rebooted the server. It seemed to work fine,
> 

Re: Request to help on GIS Query improvement suggestion.

[Michael Lewis]

Your indexes and operators are not compatible. You have added a btree index
on md5 function result and are not using md5 in your query, and also using
LIKE operator not one of the supported ones. I believe it might use a btree
operator (plain value, not md5 result) if you are always searching for
"string starts with  but I don't know what it ends with" but you can't
possibly use a btree index where you are putting a wild card at the front.

https://www.postgresql.org/docs/9.5/indexes-types.html

a gist index operators supported-
https://www.postgresql.org/docs/9.5/gist-builtin-opclasses.html

Here's a whole page on full text search, it would be worth a read-
https://www.postgresql.org/docs/9.5/textsearch-tables.html#TEXTSEARCH-TABLES-INDEX

Re: pg_dump crashes

[Andreas Kretschmer]

Am 22.05.20 um 14:37 schrieb Nico De Ranter:

Postgres version: 9.5


which minor-version?

Can you check if the table has TOAST-Tables? Can you try to select all 
columns but not TOASTed columns?

Maybe there is data-corruption only in toast-tables.


Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: pg_dump crashes

[Nico De Ranter]

/usr/lib/postgresql/11/bin/pg_upgrade
   --old-datadir /data/postgresql/9.5/main/
   --new-datadir /var/lib/postgresql/11/main/
   -b /usr/lib/postgresql/9.5/bin
   -B /usr/lib/postgresql/11/bin
   -o ' -c config_file=/etc/postgresql/9.5/main/postgresql.conf'
   -O ' -c config_file=/etc/postgresql/11/main/postgresql.conf'

On Fri, May 22, 2020 at 3:35 PM Adrian Klaver 
wrote:

> On 5/22/20 5:37 AM, Nico De Ranter wrote:
> > Hi all,
> >
> > Postgres version: 9.5
> > OS: Ubuntu 18.04.4
> >
> > I have a 144GB Bacula database that crashes the postgres daemon when I
> > try to do a pg_dump.
> > At some point the server ran out of diskspace for the database storage.
> > I expanded the lvm and rebooted the server. It seemed to work fine,
> > however when I try to dump the bacula database the postgres daemon dies
> > after about 37GB.
> >
> > I tried copying the database to another machine and upgrading postgres
> > to 11 using pg_upgrade.  The upgrade seems to work but I still get
> > exactly the same problem when trying to dump the database.
>
> What was the full command you used to do the pg_upgrade?
>
> >
> > postgres@core4:~$ pg_dumpall --cluster 11/main --file=dump.sql
> > pg_dump: Dumping the contents of table "file" failed: PQgetCopyData()
> > failed.
> > pg_dump: Error message from server: server closed the connection
> > unexpectedly
> > This probably means the server terminated abnormally
> > before or while processing the request.
> > pg_dump: The command was: COPY public.file (fileid, fileindex, jobid,
> > pathid, filenameid, deltaseq, markid, lstat, md5) TO stdout;
> > pg_dumpall: pg_dump failed on database "bacula", exiting
> >
> > In the logs I see:
> >
> > 2020-05-22 14:23:30.649 CEST [12768] LOG:  server process (PID 534) was
> > terminated by signal 11: Segmentation fault
> > 2020-05-22 14:23:30.649 CEST [12768] DETAIL:  Failed process was
> > running: COPY public.file (fileid, fileindex, jobid, pathid, filenameid,
> > deltaseq, markid, lstat, md5) TO stdout;
> > 2020-05-22 14:23:30.651 CEST [12768] LOG:  terminating any other active
> > server processes
> > 2020-05-22 14:23:30.651 CEST [482] WARNING:  terminating connection
> > because of crash of another server process
> > 2020-05-22 14:23:30.651 CEST [482] DETAIL:  The postmaster has commanded
> > this server process to roll back the current transaction and exit,
> > because another server process exited abnormally and possibly corrupted
> > shared memory.
> > 2020-05-22 14:23:30.651 CEST [482] HINT:  In a moment you should be able
> > to reconnect to the database and repeat your command.
> > 2020-05-22 14:23:30.652 CEST [12768] LOG:  all server processes
> > terminated; reinitializing
> > 2020-05-22 14:23:30.671 CEST [578] LOG:  database system was
> > interrupted; last known up at 2020-05-22 14:15:19 CEST
> > 2020-05-22 14:23:30.809 CEST [578] LOG:  database system was not
> > properly shut down; automatic recovery in progress
> > 2020-05-22 14:23:30.819 CEST [578] LOG:  redo starts at 197/D605EA18
> > 2020-05-22 14:23:30.819 CEST [578] LOG:  invalid record length at
> > 197/D605EA50: wanted 24, got 0
> > 2020-05-22 14:23:30.819 CEST [578] LOG:  redo done at 197/D605EA18
> > 2020-05-22 14:23:30.876 CEST [12768] LOG:  database system is ready to
> > accept connections
> > 2020-05-22 14:29:07.511 CEST [12768] LOG:  received fast shutdown request
> >
> >
> > Any ideas how to fix or debug this?
> >
> > Nico
> >
> > --
> >
> > Nico De Ranter
> >
> > Operations Engineer
> >
> > T. +32 16 38 72 10
> >
> >
> > 
> >
> > 
> >
> >
> > eSATURNUS
> > Philipssite 5, D, box 28
> > 3001 Leuven – Belgium
> >
> >
> >
> > T. +32 16 40 12 82
> > F. +32 16 40 84 77
> > www.esaturnus.com 
> >
> > ** 
> >
> > *For Service & Support :*
> >
> > Support Line Belgium: +32 2 2009897
> >
> > Support Line International: +44 12 56 68 38 78
> >
> > Or via email : medical.services...@sony.com
> > 
> >
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>


-- 

Nico De Ranter

Operations Engineer

T. +32 16 38 72 10







eSATURNUS
Philipssite 5, D, box 28
3001 Leuven – Belgium

T. +32 16 40 12 82
F. +32 16 40 84 77
www.esaturnus.com



*For Service & Support :*

Support Line Belgium: +32 2 2009897

Support Line International: +44 12 56 68 38 78

Or via email : medical.services...@sony.com

Re: pg_dump crashes

[Nico De Ranter]

I was just trying that.  It's always the same (huge) table that crashes the
pg_dump.   Running a dump excluding that one table goes fine, running a
dump of only that one table crashes.
In the system logs I always see a segfault

May 22 15:22:14 core4 kernel: [337837.874618] postgres[1311]: segfault at
7f778008ed0d ip 55f197ccc008 sp 7ffdd1fc15a8 error 4 in
postgres[55f1977c+727000]

It doesn't seem to be an Out-of-memory thing (at least not on the OS level).
The database is currently installed on a dedicated server with 32GB RAM.
 I tried tweaking some of the memory parameters for postgres, but the crash
always happens at the exact same spot (if I run pg_dump for that one table
with and without memory tweaks the resulting files are identical).

One thing I just noticed looking at the dump file: at around the end of the
file I see this:

2087983804 516130 37989 2218636 3079067 0 0 P4B BcISC IGk L BOT BOP A jC
BAA I BeMj/b BceUl6 BehUAn 0Ms A C I4p9CBfUiSeAPU4eDuipKQ



























*4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N
\N ??4557430888798830399 1061109567 1061109567
1061109567 1061109567 16191 \N \N
??4557430888798830399 1061109567 1061109567
1061109567 1061109567 16191 \N \N
??4557430888798830399 1061109567 1061109567
1145127487 1413694803 21071 \N \N
??4557430888798830399 1061109567 1061109567
1061109567 1061109567 16191 \N \N
??4557430888798830399 1061109567 1061109567
1061109567 1061109567 16191 \N \N
??4557430888798830399 1061109567 1061109567
1061109567 1061109567 16191 \N \N
??4557430888798830399 1061109567 1061109567
1061109567 1061109567 16191 \N \N
??4557430888798830399 1061109567 1061109567
1061109567 1061109567 16191 \N \N
??4557430888798830399 1061109567 1061109567
1061109567 1061109567 16191 \N \N
??4557430888798830399 1061109567 1061109567
1145127487 1413694803 21071 \N \N
??4557430888798830399 1061109567 1061109567
1061109567 1061109567 16191 \N \N
??4557430888798830399 1061109567 1061109567
1061109567 1061109567 16191 \N \N
??4557430888798830399 1061109567 1061109567
1061109567 1061109567 16191 \N \N
??4557430888798830399 1061109567 1061109567
1061109567 1061109567 16191 \N \N
??4557430888798830399 1061109567 1061109567
1061109567 1061109567 16191 \N \N
??4557430888798830399 1061109567 1061109567
1061109567 1061109567 16191 \N \N
??6071772946555290175 1056985679 1061109567
1061109567 1061109567 16191 \N \N
??4557430888798830399 1061109567 1061109567
1061109567 1061109567 16191 \N \N
??4557430888798830399 1061109567 1061109567
1061109567 1061109567 16191 \N \N
??4557430888798830399 1061109567 1061109567
1061109567 1061109567 16191 \N \N
??4557430888798830399 1061109567 1061109567
1061109567 1061109567 16191 \N \N
??4557430888798830399 1061109567 1061109567
1061109567 1061109567 16191 \N \N
??4557430888798830399 1061109567 1061109567
1061109567 1061109567 16191 \N \N
??4557430888798830399 1061109567 1061109567
1061109567 1061109567 16191 \N \N
??4557430888798830399 1061109567 1061109567
1061109567 1061109567 16191 \N \N
??4557430888798830399 1061109567 1061109567
1061109567 1061109567 16191 \N \N
??4557430888798830399 1061109567 1061109567
1061109567 1061109567 16191 \N \N ??*
2087983833 554418 37989 5405605 14507502 0 0 P4B Bb8c/ IGk L BOS BOP A Lfh
BAA Bg BeMj+2 Bd1LVN BehUAl rlx ABA TOR

It looks suspicious however there are about 837 more lines before the
output stops.

Nico

On Fri, May 22, 2020 at 3:27 PM Adrian Klaver 
wrote:

> On 5/22/20 5:37 AM, Nico De Ranter wrote:
> > Hi all,
> >
> > Postgres version: 9.5
> > OS: Ubuntu 18.04.4
> >
> > I have a 144GB Bacula database that crashes the postgres daemon when I
> > try to do a pg_dump.
> > At some point the server ran out of diskspace for the database storage.
> > I expanded the lvm and rebooted the server. It seemed to work fine,
> > however when I try to dump the bacula database the postgres daemon dies
> > after about 37GB.
> >
> > I tried copying the database to another machine and upgrading postgres
> > to 11 using pg_upgrade.  The upgrade seems to work but I still get
> > exactly the same problem when trying to dump the database.
> >
> > postgres@core4:~$ pg_dumpall --cluster 11/main --file=dump.sql
> > 

Re: pg_dump crashes

[Adrian Klaver]

On 5/22/20 5:37 AM, Nico De Ranter wrote:

Hi all,

Postgres version: 9.5
OS: Ubuntu 18.04.4

I have a 144GB Bacula database that crashes the postgres daemon when I 
try to do a pg_dump.
At some point the server ran out of diskspace for the database storage.  
I expanded the lvm and rebooted the server. It seemed to work fine, 
however when I try to dump the bacula database the postgres daemon dies 
after about 37GB.


I tried copying the database to another machine and upgrading postgres 
to 11 using pg_upgrade.  The upgrade seems to work but I still get 
exactly the same problem when trying to dump the database.


What was the full command you used to do the pg_upgrade?



postgres@core4:~$ pg_dumpall --cluster 11/main --file=dump.sql
pg_dump: Dumping the contents of table "file" failed: PQgetCopyData() 
failed.
pg_dump: Error message from server: server closed the connection 
unexpectedly

This probably means the server terminated abnormally
before or while processing the request.
pg_dump: The command was: COPY public.file (fileid, fileindex, jobid, 
pathid, filenameid, deltaseq, markid, lstat, md5) TO stdout;

pg_dumpall: pg_dump failed on database "bacula", exiting

In the logs I see:

2020-05-22 14:23:30.649 CEST [12768] LOG:  server process (PID 534) was 
terminated by signal 11: Segmentation fault
2020-05-22 14:23:30.649 CEST [12768] DETAIL:  Failed process was 
running: COPY public.file (fileid, fileindex, jobid, pathid, filenameid, 
deltaseq, markid, lstat, md5) TO stdout;
2020-05-22 14:23:30.651 CEST [12768] LOG:  terminating any other active 
server processes
2020-05-22 14:23:30.651 CEST [482] WARNING:  terminating connection 
because of crash of another server process
2020-05-22 14:23:30.651 CEST [482] DETAIL:  The postmaster has commanded 
this server process to roll back the current transaction and exit, 
because another server process exited abnormally and possibly corrupted 
shared memory.
2020-05-22 14:23:30.651 CEST [482] HINT:  In a moment you should be able 
to reconnect to the database and repeat your command.
2020-05-22 14:23:30.652 CEST [12768] LOG:  all server processes 
terminated; reinitializing
2020-05-22 14:23:30.671 CEST [578] LOG:  database system was 
interrupted; last known up at 2020-05-22 14:15:19 CEST
2020-05-22 14:23:30.809 CEST [578] LOG:  database system was not 
properly shut down; automatic recovery in progress

2020-05-22 14:23:30.819 CEST [578] LOG:  redo starts at 197/D605EA18
2020-05-22 14:23:30.819 CEST [578] LOG:  invalid record length at 
197/D605EA50: wanted 24, got 0

2020-05-22 14:23:30.819 CEST [578] LOG:  redo done at 197/D605EA18
2020-05-22 14:23:30.876 CEST [12768] LOG:  database system is ready to 
accept connections

2020-05-22 14:29:07.511 CEST [12768] LOG:  received fast shutdown request


Any ideas how to fix or debug this?

Nico

--

Nico De Ranter

Operations Engineer

T. +32 16 38 72 10







eSATURNUS
Philipssite 5, D, box 28
3001 Leuven – Belgium



T. +32 16 40 12 82
F. +32 16 40 84 77
www.esaturnus.com 

** 

*For Service & Support :*

Support Line Belgium: +32 2 2009897

Support Line International: +44 12 56 68 38 78

Or via email : medical.services...@sony.com 







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

Re: pg_dump crashes

[Adrian Klaver]

On 5/22/20 5:37 AM, Nico De Ranter wrote:

Hi all,

Postgres version: 9.5
OS: Ubuntu 18.04.4

I have a 144GB Bacula database that crashes the postgres daemon when I 
try to do a pg_dump.
At some point the server ran out of diskspace for the database storage.  
I expanded the lvm and rebooted the server. It seemed to work fine, 
however when I try to dump the bacula database the postgres daemon dies 
after about 37GB.


I tried copying the database to another machine and upgrading postgres 
to 11 using pg_upgrade.  The upgrade seems to work but I still get 
exactly the same problem when trying to dump the database.


postgres@core4:~$ pg_dumpall --cluster 11/main --file=dump.sql
pg_dump: Dumping the contents of table "file" failed: PQgetCopyData() 
failed.
pg_dump: Error message from server: server closed the connection 
unexpectedly

This probably means the server terminated abnormally
before or while processing the request.
pg_dump: The command was: COPY public.file (fileid, fileindex, jobid, 
pathid, filenameid, deltaseq, markid, lstat, md5) TO stdout;

pg_dumpall: pg_dump failed on database "bacula", exiting


What happens if you try to dump just this table?

Something along lines of:

pg_dump -t file -d some_db -U some_user

Have you looked at the system logs to see if it is the OS killing the 
process?





In the logs I see:

2020-05-22 14:23:30.649 CEST [12768] LOG:  server process (PID 534) was 
terminated by signal 11: Segmentation fault
2020-05-22 14:23:30.649 CEST [12768] DETAIL:  Failed process was 
running: COPY public.file (fileid, fileindex, jobid, pathid, filenameid, 
deltaseq, markid, lstat, md5) TO stdout;
2020-05-22 14:23:30.651 CEST [12768] LOG:  terminating any other active 
server processes
2020-05-22 14:23:30.651 CEST [482] WARNING:  terminating connection 
because of crash of another server process
2020-05-22 14:23:30.651 CEST [482] DETAIL:  The postmaster has commanded 
this server process to roll back the current transaction and exit, 
because another server process exited abnormally and possibly corrupted 
shared memory.
2020-05-22 14:23:30.651 CEST [482] HINT:  In a moment you should be able 
to reconnect to the database and repeat your command.
2020-05-22 14:23:30.652 CEST [12768] LOG:  all server processes 
terminated; reinitializing
2020-05-22 14:23:30.671 CEST [578] LOG:  database system was 
interrupted; last known up at 2020-05-22 14:15:19 CEST
2020-05-22 14:23:30.809 CEST [578] LOG:  database system was not 
properly shut down; automatic recovery in progress

2020-05-22 14:23:30.819 CEST [578] LOG:  redo starts at 197/D605EA18
2020-05-22 14:23:30.819 CEST [578] LOG:  invalid record length at 
197/D605EA50: wanted 24, got 0

2020-05-22 14:23:30.819 CEST [578] LOG:  redo done at 197/D605EA18
2020-05-22 14:23:30.876 CEST [12768] LOG:  database system is ready to 
accept connections

2020-05-22 14:29:07.511 CEST [12768] LOG:  received fast shutdown request


Any ideas how to fix or debug this?

Nico

--

Nico De Ranter

Operations Engineer

T. +32 16 38 72 10







eSATURNUS
Philipssite 5, D, box 28
3001 Leuven – Belgium



T. +32 16 40 12 82
F. +32 16 40 84 77
www.esaturnus.com 

** 

*For Service & Support :*

Support Line Belgium: +32 2 2009897

Support Line International: +44 12 56 68 38 78

Or via email : medical.services...@sony.com 







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

Request to help on Query improvement suggestion.

[devchef2020 d]

Hi Team,

Thanks for your support.

Could someone please suggest on the below query.

One of the query which was created on GIS data is taking a long time and
even it is not taking the index as well. I have included all the required
details for reference.

Database Stack:
===
PostgreSQL : 9.5.15
Postgis: 2.2.7

Table Structure:
===

ALTER TABLE SCHEMA.TABLE_NAME ADD COLUMN parental_path text;

Created Indexes on column parental_path:
=

CREATE INDEX cable_pair_parental_path_idx
  ON SCHEMA.TABLE_NAME
  USING btree
  (md5(parental_path) COLLATE pg_catalog."default");

CREATE INDEX cable_pair_parental_path_idx_fulltext
  ON SCHEMA.TABLE_NAME
  USING gist
  (parental_path COLLATE pg_catalog."default");

Sample data in "parental_path" column:
==

'route--2309421/2951584/3373649/2511322/1915187/2696397/2623291/2420708/2144348/2294454,circuit--88458/88460,sheath--8874'

Actual Query:
=

SELECT seq_no + 1 FROM SCHEMA.TABLE_NAME WHERE (parental_path LIKE
'%,sheath--' || cable_seq_id || ',%' OR parental_path LIKE 'sheath--' ||
cable_seq_id || ',%' OR parental_path LIKE '%,sheath--' || cable_seq_id OR
parental_path = 'sheath--' || cable_seq_id) ORDER BY seq_no DESC LIMIT 1;

Explain Plan:
=

Limit  (cost=108111.60..108111.61 rows=1 width=4) (actual
time=4597.605..4597.605 rows=0 loops=1)
 Output: ((seq_no + 1)), seq_no
 Buffers: shared hit=2967 read=69606 dirtied=1
 ->  Sort  (cost=108111.60..108113.09 rows=595 width=4) (actual
time=4597.603..4597.603 rows=0 loops=1)
   Output: ((seq_no + 1)), seq_no
   Sort Key: TABLE_NAME.seq_no DESC
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=2967 read=69606 dirtied=1
   ->  *Seq Scan on SCHEMA.TABLE_NAME  (cost=0.00..108108.63 rows=595
width=4) (actual time=4597.595..4597.595 rows=0 loops=1)*
 Output: (seq_no + 1), seq_no
 Filter: ((TABLE_NAME.parental_path ~~
'%,sheath--64690,%'::text) OR (TABLE_NAME.parental_path ~~
'sheath--64690,%'::text) OR (TABLE_NAME.parental_path ~~
'%,sheath--64690'::text) OR (TABLE_NAME.parental_path =
'sheath--64690'::text))
 Rows Removed by Filter: 1930188
 Buffers: shared hit=2967 read=69606 dirtied=1

Please share your suggestion.

Thanks & Regards,
Devchef.

Re: Failed rpm package signature checks with reposync

[mkruk]

The problem appeared today:

reposync -g -l -n --repoid=pgdg10 --download_path=repos
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirror.as29550.net
 * extras: centos.serverspace.co.uk
 * updates: mirror.cwcs.co.uk
warning:
/repo/centos7/repos/pgdg10/bgw_replstatus10-1.0.1-1.rhel7.x86_64.rpm: Header
V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY   
   
]  0.0 B/s |0 B  --:--:-- ETA Public key for
bgw_replstatus10-1.0.1-1.rhel7.x86_64.rpm is not installed
(1/139): bgw_replstatus10-1.0.1-1.rhel7.x86_64.rpm
...
(139/139): wal2json10-2.2-1.rhel7.x86_64.rpm


|  22 kB  00:00:00
Removing amcheck_next10-1.5-1.rhel7.x86_64.rpm, due to missing GPG key.
...

###

rpm -qa|grep pgdg
pgdg-redhat-repo-42.0-11.noarch




--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: pg_basebackup + incremental base backups

[Christopher Pereira]

On 21-May-20 08:43, Stephen Frost wrote:

* Christopher Pereira (krip...@imatronix.cl) wrote:

[...]

Is there some way to rebuild the standby cluster by doing a differential
backup of the primary cluster directly?

We've contemplated adding support for something like this to pgbackrest,
since all the pieces are there, but there hasn't been a lot of demand
for it and it kind of goes against the idea of having a proper backup
solution, really..  It'd also create quite a bit of load on the primary
to checksum all the files to do the comparison against what's on the
replica that you're trying to update, so not something you'd probably
want to do a lot more than necessary.


We have backups of the whole server and only need a efficient way to 
rebuild the hot-standby cluster when pg_rewind is not able to do so.


I agree with your concerns about the increased load on the primary 
server, but this rebuilding process would only be done in case of 
emergency or during low load hours.


pg_basebackup works fine but does not support differential/incremental 
backups which is a blocker.


Do you know any alternative software that is able to rebuild the standby 
PG data dir using rsync or similar while the primary is still online?


It seems a simple pg_start_backup + rsync + pg_stop_backup (maybe 
combined with a LVM snapshot) would do, but we would prefer to use some 
existing tool.


We just tried barman, but it also seems to require a restore from the 
backup before being able to start the standby server (?), and we are 
afraid this would require double storage, IO and time for rebuilding the 
standby cluster.


Thanks.

Inaccurate (sometimes wildly so) row estimates for simple join

[Greg Nolle]

Hi,

I’m working on diagnosing an issue with a complex query on a large PG11
database we have. The planner is choosing a plan that takes 60+ seconds but
if we force it to use a particular index then it takes only a couple of
seconds. I’ve narrowed down what I think is the cause to a very simple join
for which PG is underestimating the rows by a factor of 20. It then chooses
to do a nested loop which is actually much slower than it thinks it would
be based on the estimate.

I’ve managed to reproduce this underestimation issue using a simple data
set that is very similar in distribution to what we have in our real
database:

CREATE TABLE test_a (
a_id VARCHAR(255) PRIMARY KEY,
group_val VARCHAR(255) NOT NULL
);
INSERT INTO test_a (a_id, group_val) SELECT 'a_id'||s, 'group'||s FROM
generate_series(1, 100) s;
INSERT INTO test_a (a_id, group_val) VALUES ('a_id101', 'group1');

CREATE TABLE test_b (
b_id VARCHAR(255) PRIMARY KEY,
a_id VARCHAR(255) NOT NULL,
CONSTRAINT test_bk_fk01 FOREIGN KEY (a_id) REFERENCES test_a (a_id)
);
INSERT INTO test_b (b_id, a_id) SELECT 'b_id'||s, 'a_id1' FROM
generate_series(1, 1500) s;
INSERT INTO test_b (b_id, a_id) SELECT 'b_id'||s, 'a_id2' FROM
generate_series(1501, 2000) s;

ANALYZE test_a;
ANALYZE test_b;

EXPLAIN ANALYZE SELECT * FROM test_b b JOIN test_a a ON a.a_id = b.a_id
WHERE a.group_val = 'group1';

This gives an estimated 40 rows for the join when in fact there are 1500
rows. The crux seems to be that test_b does not have an even distribution
for a_id values: it only has records for two of the values in the
referenced table. This is how our real dataset is too and isn’t something
we can really change.

I’ve read through the row estimation information here:
https://www.postgresql.org/docs/11/row-estimation-examples.html and also
read through the source code for the eqjoinsel_inner function. I can see
how it’s calculating the estimate but I have no clue how it can be made
accurate for this (seemingly) simple scenario.

Any hints or tips would be greatly appreciated.

FDW and RLS

[Ted Toth]

Will RLS be applied to data being retrieved via a FDW?

Ted

pg_dump crashes

[Nico De Ranter]

Hi all,

Postgres version: 9.5
OS: Ubuntu 18.04.4

I have a 144GB Bacula database that crashes the postgres daemon when I try
to do a pg_dump.
At some point the server ran out of diskspace for the database storage.  I
expanded the lvm and rebooted the server. It seemed to work fine, however
when I try to dump the bacula database the postgres daemon dies after about
37GB.

I tried copying the database to another machine and upgrading postgres to
11 using pg_upgrade.  The upgrade seems to work but I still get exactly the
same problem when trying to dump the database.

postgres@core4:~$ pg_dumpall --cluster 11/main --file=dump.sql
pg_dump: Dumping the contents of table "file" failed: PQgetCopyData()
failed.
pg_dump: Error message from server: server closed the connection
unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
pg_dump: The command was: COPY public.file (fileid, fileindex, jobid,
pathid, filenameid, deltaseq, markid, lstat, md5) TO stdout;
pg_dumpall: pg_dump failed on database "bacula", exiting

In the logs I see:

2020-05-22 14:23:30.649 CEST [12768] LOG:  server process (PID 534) was
terminated by signal 11: Segmentation fault
2020-05-22 14:23:30.649 CEST [12768] DETAIL:  Failed process was running:
COPY public.file (fileid, fileindex, jobid, pathid, filenameid, deltaseq,
markid, lstat, md5) TO stdout;
2020-05-22 14:23:30.651 CEST [12768] LOG:  terminating any other active
server processes
2020-05-22 14:23:30.651 CEST [482] WARNING:  terminating connection because
of crash of another server process
2020-05-22 14:23:30.651 CEST [482] DETAIL:  The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2020-05-22 14:23:30.651 CEST [482] HINT:  In a moment you should be able to
reconnect to the database and repeat your command.
2020-05-22 14:23:30.652 CEST [12768] LOG:  all server processes terminated;
reinitializing
2020-05-22 14:23:30.671 CEST [578] LOG:  database system was interrupted;
last known up at 2020-05-22 14:15:19 CEST
2020-05-22 14:23:30.809 CEST [578] LOG:  database system was not properly
shut down; automatic recovery in progress
2020-05-22 14:23:30.819 CEST [578] LOG:  redo starts at 197/D605EA18
2020-05-22 14:23:30.819 CEST [578] LOG:  invalid record length at
197/D605EA50: wanted 24, got 0
2020-05-22 14:23:30.819 CEST [578] LOG:  redo done at 197/D605EA18
2020-05-22 14:23:30.876 CEST [12768] LOG:  database system is ready to
accept connections
2020-05-22 14:29:07.511 CEST [12768] LOG:  received fast shutdown request


Any ideas how to fix or debug this?

Nico

-- 

Nico De Ranter

Operations Engineer

T. +32 16 38 72 10







eSATURNUS
Philipssite 5, D, box 28
3001 Leuven – Belgium

T. +32 16 40 12 82
F. +32 16 40 84 77
www.esaturnus.com



*For Service & Support :*

Support Line Belgium: +32 2 2009897

Support Line International: +44 12 56 68 38 78

Or via email : medical.services...@sony.com

Request to help on GIS Query improvement suggestion.

[postggen2020 s]

Hi Team,

Thanks for your support.

Could someone please suggest on the below query.

One of the query which was created on GIS data is taking a long time and
even it is not taking the index as well. I have included all the required
details for reference.

Database Stack:
===
PostgreSQL : 9.5.15
Postgis: 2.2.7

Table Structure:
===

ALTER TABLE SCHEMA.TABLE_NAME ADD COLUMN parental_path text;

Created Indexes on column parental_path:
=

CREATE INDEX cable_pair_parental_path_idx
  ON SCHEMA.TABLE_NAME
  USING btree
  (md5(parental_path) COLLATE pg_catalog."default");

CREATE INDEX cable_pair_parental_path_idx_fulltext
  ON SCHEMA.TABLE_NAME
  USING gist
  (parental_path COLLATE pg_catalog."default");

Sample data in "parental_path" column:
==

'route--2309421/2951584/3373649/2511322/1915187/2696397/2623291/2420708/2144348/2294454,circuit--88458/88460,sheath--8874'

Actual Query:
=

SELECT seq_no + 1 FROM SCHEMA.TABLE_NAME WHERE (parental_path LIKE
'%,sheath--' || cable_seq_id || ',%' OR parental_path LIKE 'sheath--' ||
cable_seq_id || ',%' OR parental_path LIKE '%,sheath--' || cable_seq_id OR
parental_path = 'sheath--' || cable_seq_id) ORDER BY seq_no DESC LIMIT 1;

Explain Plan:
=

Limit  (cost=108111.60..108111.61 rows=1 width=4) (actual
time=4597.605..4597.605 rows=0 loops=1)
 Output: ((seq_no + 1)), seq_no
 Buffers: shared hit=2967 read=69606 dirtied=1
 ->  Sort  (cost=108111.60..108113.09 rows=595 width=4) (actual
time=4597.603..4597.603 rows=0 loops=1)
   Output: ((seq_no + 1)), seq_no
   Sort Key: TABLE_NAME.seq_no DESC
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=2967 read=69606 dirtied=1
   ->  *Seq Scan on SCHEMA.TABLE_NAME  (cost=0.00..108108.63 rows=595
width=4) (actual time=4597.595..4597.595 rows=0 loops=1)*
 Output: (seq_no + 1), seq_no
 Filter: ((TABLE_NAME.parental_path ~~
'%,sheath--64690,%'::text) OR (TABLE_NAME.parental_path ~~
'sheath--64690,%'::text) OR (TABLE_NAME.parental_path ~~
'%,sheath--64690'::text) OR (TABLE_NAME.parental_path =
'sheath--64690'::text))
 Rows Removed by Filter: 1930188
 Buffers: shared hit=2967 read=69606 dirtied=1

Please share your suggestion if I have to change or add new objects to the
table etc..


Thanks & Regards,
PostgAnn.

Re: Logical replication troubles

[Peter Eisentraut]

On 2020-05-20 17:16, Anders Bøgh Bruun wrote:
[67] LOG:  logical replication apply worker for subscription 
"widgets_sub" has started
[67] DEBUG:  connecting to publisher using connection string 
"dbname=testdb host=master port=5432 user=repuser password=abc123"
[67] ERROR:  could not receive data from WAL stream: ERROR:  publication 
"widgets_pub" does not exist
   CONTEXT:  slot "my_slot", output plugin "pgoutput", in the change 
callback, associated LSN 0/1674958
[1] DEBUG:  unregistering background worker "logical replication worker 
for subscription 16396"
[1] LOG:  background worker "logical replication worker" (PID 67) exited 
with exit code 1


I can verify that the publication called widgets_pub does exist, and I 
am not seeing any errors on the sending side.


The SQL-file named "works" just has the creation of the replication slot 
moved down to after I insert some data into the table I want to 
replicate. And that works as expected.


You need to create the publication before the replication slot.  The 
walsender's view of the world moves along with the WAL it is 
decoding/sending.  So when the subscription worker connects, it 
initially sees a state as of the creation of the replication slot, when 
the publication did not exist yet.


--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

btree_gist extension - gbt_cash_union return type

[Tim Kane]

I've noticed a discrepancy in the return type for the gbt_cash_union
function...

On fresh instances of postgres 9.6.11, where the btree_gist extension is
newly created (version 1.2) yields a gbt_cash_union function with a return
type of gbtreekey16

While instances that have been upgraded from 9.6.2 to 9.6.11, where the
btree_gist was originally installed as 1.0 and then upgraded from 1.0 to
1.2 - that same function has a return type of gbtreekey8

I expect something is missing from the extension upgrade script to modify
the return type.

Is it safe/recommended to modify this function to return gbtreekey16?
Perhaps safer still to drop the extension and recreate it?

Thanks in advance. If this should be considered a bug, and there is a
better place to report such - please advise.

Tim

Re: Suggestion to improve query performance of data validation in proc.

[David G. Johnston]

On Friday, May 22, 2020, postgann2020 s  wrote:


>
> We are looking for a better query than "*SELECT 1  FROM schema.table_name
> WHERE column1=structure_id1*" this query for data validation.
>

 There is no more simple a query that involve records on a single,table.

Please suggest is there any other ways to validate this kind of queries
> which will improve the overall performance.
>

Abandon procedural logic and embrace the declarative set oriented nature of
SQL.

David J.

Re: Suggestion to improve query performance of data validation in proc.

[postgann2020 s]

Hi David,

Thanks for your feedback.

We are using the below kind of validation throughout the proc in multiple
locations and for validation we are using the below statements.

--check Data available or not for structure_id1
   IF EXISTS(SELECT 1  FROM schema.table_name WHERE
column1=structure_id1)  THEN
 is_exists1 :=true;
END IF;

We are looking for a better query than "*SELECT 1  FROM schema.table_name
WHERE column1=structure_id1*" this query for data validation.

Please suggest is there any other ways to validate this kind of queries
which will improve the overall performance.

Regards,
Postgann.

On Fri, May 22, 2020 at 12:36 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> You should read through the and the contained linked FAQ - note especially
> the concept and recommendation for “cross-posting”.
>
> https://wiki.postgresql.org/wiki/Mailing_Lists#Email_etiquette_mechanics
>
> On Thursday, May 21, 2020, postgann2020 s  wrote:
>
>>
>> We have multiple long procs that are having 100s of data validations and
>> currently we have written as below.
>>
>> ***
>>
>> if (SELECT 1  FROM SCHEMA.TABLE WHERE column=data AND column=data) then
>> statements
>> etc..
>>
>> ***
>>
>> Are there any other ways to validate the data, which will help us to
>> improve the performance of the query?
>>
>
> I have no idea what your are trying to get at here.  You should try
> providing SQL that actually runs.  Though at first glance it seems quite
> probable your are doing useless work anyway.
>
> David J.
>

Re: Suggestion to improve query performance of data validation in proc.

[David G. Johnston]

You should read through the and the contained linked FAQ - note especially
the concept and recommendation for “cross-posting”.

https://wiki.postgresql.org/wiki/Mailing_Lists#Email_etiquette_mechanics

On Thursday, May 21, 2020, postgann2020 s  wrote:

>
> We have multiple long procs that are having 100s of data validations and
> currently we have written as below.
>
> ***
>
> if (SELECT 1  FROM SCHEMA.TABLE WHERE column=data AND column=data) then
> statements
> etc..
>
> ***
>
> Are there any other ways to validate the data, which will help us to
> improve the performance of the query?
>

I have no idea what your are trying to get at here.  You should try
providing SQL that actually runs.  Though at first glance it seems quite
probable your are doing useless work anyway.

David J.

Re: Query returns no rows in pg_basebackup cluster

[Andrus]

Hi!


Database in Windows is in read-only (recovery) mode so it cannot changed.

Then you might as well just rm -rf it (or whatever the equivalent Windows
incantation is).  On Windows, that database is broken and useless.


Backup in created in Windows from Linux server using pg_receivewal and 
pg_basebackup .
Can this backup used for PITR in Linux ?

Andrus.

Suggestion to improve query performance of data validation in proc.

[postgann2020 s]

Hi Team,
Thanks for your support.

Could you please suggest on below query.

We have multiple long procs that are having 100s of data validations and
currently we have written as below.

***

if (SELECT 1  FROM SCHEMA.TABLE WHERE column=data AND column=data) then
statements
etc..

***

Are there any other ways to validate the data, which will help us to
improve the performance of the query?.

Thanks for your support.

Regards,
PostgAnn.

Re: Query returns no rows in pg_basebackup cluster

[Andrus]

Hi!


Main server is in Linux and backup server is in windows.

This is not a supported setup if you want to run a physical backup.
Your backup and your primary need to be the same - software and hardware.  
Consider anything that is working to be a false
negative – assume >something will break or simply give incorrect results.


This base backup should used for recovery. Taking new base backup in Linux does 
not allow to recover to earlier date.
Both servers have Intel 64 bit CPUs.
I understand that only issue is the index structure and that REINDEX will fix 
this.
What other issues may occur ?

Will pg_dump/pg_restore in Windows server fix all issues.

Andrus.