Re: Help understanding SIReadLock growing without bound on completed transaction

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 >

Re: query, probably needs window functions

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

Re: query, probably needs window functions

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

Re: Query returns no rows in pg_basebackup cluster

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

Re: query, probably needs window functions

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)

query, probably needs window functions

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

Re: How to get the OID of a view

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

Re: How to get the OID of a view

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

Re: How to get the OID of a view

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 >

Re: How to get the OID of a view

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

How to get the OID of a view

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,

Re: pg_dump crashes

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,

Re: pg_dump crashes

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,

Re: pg_dump crashes

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

Re: Query returns no rows in pg_basebackup cluster

"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

Re: Query returns no rows in pg_basebackup cluster

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

Re: pg_dump crashes

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 ''

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

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

Re: btree_gist extension - gbt_cash_union return type

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

Re: pg_dump crashes

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

Re: pg_dump crashes

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

Query to get name a data type of a view

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

Re: pg_dump crashes

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

Re: pg_dump crashes

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 >

Re: pg_dump crashes

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        |

Re: pg_dump crashes

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

Re: pg_dump crashes

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

Re: pg_dump crashes

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

Re: pg_dump crashes

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

Re: pg_dump crashes

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

Re: btree_gist extension - gbt_cash_union return type

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

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

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

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

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

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

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

Re: Query returns no rows in pg_basebackup cluster

"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

Re: pg_dump crashes

> 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. 

Re: Request to help on GIS Query improvement suggestion.

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

Re: pg_dump crashes

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

Re: pg_dump crashes

/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

Re: pg_dump crashes

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]:

Re: pg_dump crashes

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

Re: pg_dump crashes

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

Request to help on Query improvement suggestion.

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: ===

Re: Failed rpm package signature checks with reposync

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:

Re: pg_basebackup + incremental base backups

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

Inaccurate (sometimes wildly so) row estimates for simple join

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

FDW and RLS

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

pg_dump crashes

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

Request to help on GIS Query improvement suggestion.

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: ===

Re: Logical replication troubles

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

btree_gist extension - gbt_cash_union return type

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

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

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

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

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

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

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

Re: Query returns no rows in pg_basebackup cluster

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

Suggestion to improve query performance of data validation in proc.

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..

Re: Query returns no rows in pg_basebackup cluster

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