Re: [GENERAL] Problems with Greatest

Teddy Schmitz schrieb am 16.02.2017 um 05:38: > As a quick follow up I just did an explain on the query, > > > Aggregate (cost=258007258.87..258007258.88 rows=1 width=8) > -> Nested Loop (cost=0.00..184292254.83 rows=14743000807 width=16) > -> Seq Scan on t1 (cost=0.00..3796.41 rows=263141

Re: [GENERAL] Problems with Greatest

Thanks for the link Pavel, that makes perfect sense now. Teddy From: David G. Johnston Sent: Thursday, February 16, 2017 12:42:36 PM To: Teddy Schmitz Cc: pgsql-general@postgresql.org Subject: [GENERAL] Problems with Greatest On

[GENERAL] Problems with Greatest

On Wednesday, February 15, 2017, Teddy Schmitz > wrote: > > select greatest(max(t1.id), max(t2.id)) from t1, t2; > > I seriously doubt that the greatest function is a larger contributor to run time

Re: [GENERAL] Problems with Greatest

2017-02-16 5:38 GMT+01:00 Teddy Schmitz : > As a quick follow up I just did an explain on the query, > > > Aggregate (cost=258007258.87..258007258.88 rows=1 width=8) > -> Nested Loop (cost=0.00..184292254.83 rows=14743000807 width=16) > -> Seq Scan on t1

Re: [GENERAL] Problems with Greatest

As a quick follow up I just did an explain on the query, Aggregate (cost=258007258.87..258007258.88 rows=1 width=8) -> Nested Loop (cost=0.00..184292254.83 rows=14743000807 width=16) -> Seq Scan on t1 (cost=0.00..3796.41 rows=263141 width=8) -> Materialize (cost=0.00..1088.40 rows=56027

[GENERAL] Problems with Greatest

Hello, I have a query using Greatest that hangs and never returns when called with two tables. Postgres Version: 9.6 Tables t1{ id bigint } t2 { id bigint } they are sharing a sequence the query select greatest(max(t1.id), max(t2.id)) from t1, t2; The purpose was to call setval on

Re: [GENERAL] How to evaluate "explain analyze" correctly after "explain" for the same statement ?

2017-02-16 14:57 GMT+13:00 Patrick B : > > I've got two different scenarios: > > Production database server > PG 9.2 > >- I ran one single time, in a slave server that no queries go to that >server, and it took >10 seconds. > > Test database server > PG 9.2 > >

Re: [GENERAL] How to evaluate "explain analyze" correctly after "explain" for the same statement ?

2017-02-16 14:08 GMT+13:00 Tom Lane : > Patrick B writes: > > For the first time I ran the query, it took >10 seconds. Now it is taking > > less than a second. > > How can I clear for good the cache? So i can have a real idea of how long > > the

Re: [GENERAL] Bad planning data resulting in OOM killing of postgres

Maybe also useful to know: cipafilter=# select reltuples from pg_class where relname = 'log_raw'; reltuples - 5.40531e+08 (1 row) On Wed, Feb 15, 2017 at 7:55 PM, David Hinkle wrote: > Thanks for your help! > > Karsten: The system does fill up swap before

Re: [GENERAL] Bad planning data resulting in OOM killing of postgres

Thanks for your help! Karsten: The system does fill up swap before it blows up. This particular model has 8G of ram and 4G of swap and runs kernel 4.4.0-53-generic #74~14.04.1-Ubuntu. Tom, there are three columns in this table that exhibit the problem, here is the statistics data after an

Re: [GENERAL] How to evaluate "explain analyze" correctly after "explain" for the same statement ?

Patrick B writes: > For the first time I ran the query, it took >10 seconds. Now it is taking > less than a second. > How can I clear for good the cache? So i can have a real idea of how long > the query takes to run? TBH, I think you're probably obsessing over the

Re: [GENERAL] How to evaluate "explain analyze" correctly after "explain" for the same statement ?

2017-02-16 13:25 GMT+13:00 Steve Atkins : > > > On Feb 15, 2017, at 3:58 PM, Patrick B wrote: > > > > Hi all, > > > > I just got a quick question about warm-cache. I'm using PG 9.2. > > > > When I execute this statement soon after I start/restart the

Re: [GENERAL] Can't restart Postgres

On 02/15/2017 03:09 PM, Shawn Thomas wrote: Just wanted to follow up that re-installing Postgres worked (well almost—I did have to reset the permissions and ownership on the key and pem file). Thanks so much for all the help. That's what we are here for :D Sincerely, JD -Shawn --

Re: [GENERAL] How to evaluate "explain analyze" correctly after "explain" for the same statement ?

> On Feb 15, 2017, at 3:58 PM, Patrick B wrote: > > Hi all, > > I just got a quick question about warm-cache. I'm using PG 9.2. > > When I execute this statement soon after I start/restart the database: > > explain select id from test where id = 124; > > The

Re: [GENERAL] Potential Bug: Frequent Unnecessary Degeneration

Hi, On 2017-02-15 20:00:11 -0330, David O'Mahony wrote: > We're running two nodes using with replication enabled. > > pgpool routinely (every day) performs a failover with the following > statements appearing the in log: This list is about bugs in postgresql.org maintained projects, pgpool

[GENERAL] How to evaluate "explain analyze" correctly after "explain" for the same statement ?

Hi all, I just got a quick question about warm-cache. I'm using PG 9.2. When I execute this statement soon after I start/restart the database: explain select id from test where id = 124; The runtime is 40ms. Then, If I execute this statement just after the above one; explain analyze select

[GENERAL] Potential Bug: Frequent Unnecessary Degeneration

Hi All, We're running two nodes using with replication enabled. pgpool routinely (every day) performs a failover with the following statements appearing the in log: 2017-02-15 13:16:01: pid 16190: WARNING: write on backend 1 failed with error :"Success" 2017-02-15 13:16:01: pid 16190: DETAIL:

Re: [GENERAL] Can't restart Postgres

Just wanted to follow up that re-installing Postgres worked (well almost—I did have to reset the permissions and ownership on the key and pem file). Thanks so much for all the help. -Shawn > On Feb 15, 2017, at 9:49 AM, Adrian Klaver wrote: > > On 02/15/2017

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

Scott Marlowe wrote: > Then a ddl gets in line. It has to wait on the vacuum, and the vacuum, > set to run super slow. And everybody waits. On vacuum. Note that this is normally not seen, because autovacuum cancels itself when somebody is blocked behind it -- until the table reaches the

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

On Wed, Feb 15, 2017 at 3:26 PM, Scott Marlowe wrote: > On Wed, Feb 15, 2017 at 10:30 AM, Tim Bellis > wrote: >> I have a postgres 9.3.4 database table which (intermittently but reliably) >> gets into a state where queries get blocked

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

On Wed, Feb 15, 2017 at 10:30 AM, Tim Bellis wrote: > I have a postgres 9.3.4 database table which (intermittently but reliably) > gets into a state where queries get blocked indefinitely (at least for many > hours) behind an automatic vacuum. I was under the

Re: [GENERAL] Problem with PostgreSQL string sorting Hello All,

On Wed, Feb 15, 2017 at 9:01 AM, Hari Sankar A wrote: > ​[...]​ > > I can use lower() and collate 'C' to get this result. But I need a default > collate to support this. > > Is there any collation that support both (Case insensitive and special > character) sorting in

[GENERAL] Problem with PostgreSQL string sorting Hello All,

Hello All, I am a Database administrator and we are using PostgreSQL-9.6.1 version in RHEL-7 linux machine. Current server Encoding setup is UTF8 and LC_COLLATE and LC_CTYPE is en_US.UTF-8. Lets say table person has field name with values likes name -- Abc abc .dcb Dcb $sdf EDF en_US.UTF-8

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

On 02/15/2017 09:30 AM, Tim Bellis wrote: I have a postgres 9.3.4 database table which (intermittently but reliably) gets into a state where queries get blocked indefinitely (at least for many hours) behind an automatic vacuum. I was under the impression that vacuum should never take any

Re: [GENERAL] postgresql how to duplicate rows in result.

Il 14/02/2017 21:51, Merlin Moncure ha scritto: On Tue, Feb 14, 2017 at 12:36 PM, Merlin Moncure wrote: On Tue, Feb 14, 2017 at 12:04 PM, Alessandro Baggi wrote: Hi list, sorry for my english, I will try to example as well. I've a query that

Re: [GENERAL] Can't restart Postgres

Yes, definitely. > On Feb 15, 2017, at 9:49 AM, Adrian Klaver wrote: > > On 02/15/2017 09:45 AM, Shawn Thomas wrote: >> Which would you recommend? Leave the data directory in place and >> re-install PG or copy it to somewhere else, delete it and then >> re-install

Re: [GENERAL] Can't restart Postgres

Yes, sadly it does explain things. Your insight has been super helpful though. -Shawn > On Feb 15, 2017, at 9:38 AM, Adrian Klaver wrote: > > On 02/15/2017 09:28 AM, Shawn Thomas wrote: >> Well that would make more sense of things. I had removed and >> re-installed

Re: [GENERAL] Can't restart Postgres

On 02/15/2017 09:45 AM, Shawn Thomas wrote: Which would you recommend? Leave the data directory in place and re-install PG or copy it to somewhere else, delete it and then re-install PG? I would copy the data directory somewhere else for safe keeping leaving the original in place. Then

Re: [GENERAL] Can't restart Postgres

Which would you recommend? Leave the data directory in place and re-install PG or copy it to somewhere else, delete it and then re-install PG? -Shawn > On Feb 15, 2017, at 9:36 AM, Magnus Hagander wrote: > > On Wed, Feb 15, 2017 at 6:28 PM, Shawn Thomas

Re: [GENERAL] Can't restart Postgres

I was responsible for deleting/re-inatalling the cert. I was attempting to get Shibboleth (a federated authentication service that also uses the cert) running and didn’t realize that PG relied on it. The dumpAll file wasn’t actually deleted but was empty. I’ll look into that mystery once I

Re: [GENERAL] Can't restart Postgres

On 02/15/2017 09:28 AM, Shawn Thomas wrote: Well that would make more sense of things. I had removed and re-installed the postresql-common package: https://packages.debian.org/jessie/postgresql-common Well that is the glue that holds the pgcluster scheme together. Also when I try it I get:

Re: [GENERAL] Can't restart Postgres

On Wed, Feb 15, 2017 at 6:28 PM, Shawn Thomas wrote: > Well that would make more sense of things. I had removed and re-installed > the postresql-common package: > > https://packages.debian.org/jessie/postgresql-common > > and thought that it would leave the main PG

Re: [GENERAL] Can't restart Postgres

Well that would make more sense of things. I had removed and re-installed the postresql-common package: https://packages.debian.org/jessie/postgresql-common and thought that it would leave the main PG package in place. But perhaps I was wrong. I’ll follow Tom’s advice and just re-install

Re: [GENERAL] Can't restart Postgres

On 02/15/2017 09:28 AM, Joshua D. Drake wrote: On 02/15/2017 09:17 AM, Adrian Klaver wrote: On 02/15/2017 09:03 AM, Shawn Thomas wrote: /usr/lib/postgresql/9.4/bin/pg_ctl: No such file or directory That should have been: lsb_release -a No LSB modules are available. Distributor ID: Ubuntu

[GENERAL] Autovacuum stuck for hours, blocking queries

I have a postgres 9.3.4 database table which (intermittently but reliably) gets into a state where queries get blocked indefinitely (at least for many hours) behind an automatic vacuum. I was under the impression that vacuum should never take any blocking locks for any significant period of

Re: [GENERAL] Can't restart Postgres

On 02/15/2017 09:17 AM, Adrian Klaver wrote: On 02/15/2017 09:03 AM, Shawn Thomas wrote: /usr/lib/postgresql/9.4/bin/pg_ctl: No such file or directory That should have been: lsb_release -a No LSB modules are available. Distributor ID: Ubuntu Description:Ubuntu 16.04.2 LTS Release:

Re: [GENERAL] Can't restart Postgres

On 02/15/2017 09:03 AM, Shawn Thomas wrote: /usr/lib/postgresql/9.4/bin/pg_ctl: No such file or directory postgres@pangaea:/usr/lib/postgresql/9.4/bin$ ls -al total 4008 drwxr-xr-x 2 root root4096 Feb 9 16:17 . drwxr-xr-x 3 root root4096 Feb 9 16:17 .. -rwxr-xr-x 1 root root

Re: [GENERAL] Can't restart Postgres

On 02/15/2017 09:03 AM, Shawn Thomas wrote: /usr/lib/postgresql/9.4/bin/pg_ctl: No such file or directory That should have been: lsb_release -a No LSB modules are available. Distributor ID: Ubuntu Description:Ubuntu 16.04.2 LTS Release:16.04 Codename: xenial -- Adrian

Re: [GENERAL] Get tables ending with numbers

Thanks Tom & Charles. -Sathesh From: Charles Clavadetscher Sent: Wednesday, February 15, 2017 12:05 PM To: 'Sathesh S'; 'pgsql-general' Subject: RE: [GENERAL] Get tables ending with

Re: [GENERAL] Can't restart Postgres

On 02/15/2017 09:03 AM, Shawn Thomas wrote: /usr/lib/postgresql/9.4/bin/pg_ctl: No such file or directory postgres@pangaea:/usr/lib/postgresql/9.4/bin$ ls -al total 4008 drwxr-xr-x 2 root root4096 Feb 9 16:17 . drwxr-xr-x 3 root root4096 Feb 9 16:17 .. -rwxr-xr-x 1 root root

Re: [GENERAL] Can't restart Postgres

> On Feb 14, 2017, at 8:47 PM, Shawn Thomas wrote: > > No it doesn’t matter if run with sudo, postgres or even root. Debian > actually wraps the command and executes some some initial scripts with > different privileges but ends up making sure that Postgres ends up

Re: [GENERAL] Can't restart Postgres

Shawn Thomas writes: > I’m thinking that at this point I need to approach this problem as more of a > disaster recovery. There was a full pg_dumpall file that was deleted and > cannot be recovered so I need to recover the data from the >

Re: [GENERAL] Can't restart Postgres

On Wed, Feb 15, 2017 at 6:03 PM, Shawn Thomas wrote: > /usr/lib/postgresql/9.4/bin/pg_ctl: No such file or directory > > postgres@pangaea:/usr/lib/postgresql/9.4/bin$ ls -al > total 4008 > drwxr-xr-x 2 root root4096 Feb 9 16:17 . > drwxr-xr-x 3 root root

Re: [GENERAL] Can't restart Postgres

/usr/lib/postgresql/9.4/bin/pg_ctl: No such file or directory postgres@pangaea:/usr/lib/postgresql/9.4/bin$ ls -al total 4008 drwxr-xr-x 2 root root4096 Feb 9 16:17 . drwxr-xr-x 3 root root4096 Feb 9 16:17 .. -rwxr-xr-x 1 root root 68128 Nov 16 06:53 clusterdb -rwxr-xr-x 1

Re: [GENERAL] Can't restart Postgres

On 02/15/2017 08:35 AM, Shawn Thomas wrote: Yes, that’s the correct sequence of scripts. And no there’s not anything really helpful in the system logs. I’m thinking that at this point I need to approach this problem as more of a disaster recovery. There was a full pg_dumpall file that was

Re: [GENERAL] Can't restart Postgres

On 02/15/2017 08:35 AM, Shawn Thomas wrote: Yes, that’s the correct sequence of scripts. And no there’s not anything really helpful in the system logs. I’m thinking that at this point I need to approach this problem as more of a disaster recovery. There was a full pg_dumpall file that was

Re: [GENERAL] Can't restart Postgres

Yes, that’s the correct sequence of scripts. And no there’s not anything really helpful in the system logs. I’m thinking that at this point I need to approach this problem as more of a disaster recovery. There was a full pg_dumpall file that was deleted and cannot be recovered so I need

Re: [GENERAL] Foreign Data Wrapper for filesystem

El 15/02/17 a las 13:27, John McKown escribió: On Wed, Feb 15, 2017 at 10:20 AM, Tom Lane >wrote: "=?UTF-8?Q?Leonardo_M._Ram=c3=a9?=" > writes: > Hi, I'm looking for a FDW that allows

Re: [GENERAL] Foreign Data Wrapper for filesystem

On Wed, Feb 15, 2017 at 10:20 AM, Tom Lane wrote: > "=?UTF-8?Q?Leonardo_M._Ram=c3=a9?=" writes: > > Hi, I'm looking for a FDW that allows listing a directory as a database > > table allowing me to check if file exists, does anyonke know if such FDW > >

Re: [GENERAL] Foreign Data Wrapper for filesystem

"=?UTF-8?Q?Leonardo_M._Ram=c3=a9?=" writes: > Hi, I'm looking for a FDW that allows listing a directory as a database > table allowing me to check if file exists, does anyonke know if such FDW > exists?. Why not use pg_ls_dir()? An FDW would be mighty awkward to use for

Re: [GENERAL] Using ctid in delete statement

Vick Khera writes: > On Wed, Feb 15, 2017 at 10:32 AM, pinker wrote: >> DELETE FROM table_name WHERE ctid = any ( array ( select tn.ctid from >> table_name tn JOIN items i on tn.itemid=i.itemid WHERE tn.clock < extract ( >> epoch FROM now() - i.history * interval

Re: [GENERAL] Using ctid in delete statement

thank you for the answer -- View this message in context: http://postgresql.nabble.com/Using-ctid-in-delete-statement-tp5944434p591.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] Using ctid in delete statement

On Wed, Feb 15, 2017 at 10:32 AM, pinker wrote: > DELETE FROM table_name WHERE ctid = any ( array ( select tn.ctid from > table_name tn JOIN items i on tn.itemid=i.itemid WHERE tn.clock < extract ( > epoch FROM now() - i.history * interval '10 day')::int + 6 limit 100)); > >

[GENERAL] Foreign Data Wrapper for filesystem

Hi, I'm looking for a FDW that allows listing a directory as a database table allowing me to check if file exists, does anyonke know if such FDW exists?. Regards, -- Leonardo M. Ramé Medical IT - Griensu S.A. Av. Colón 636 - Piso 8 Of. A X5000EPT -- Córdoba Tel.: +54(351)4246924

[GENERAL] Using ctid in delete statement

Hi, is it safe to use ctid in following query? : DELETE FROM table_name WHERE ctid = any ( array ( select tn.ctid from table_name tn JOIN items i on tn.itemid=i.itemid WHERE tn.clock < extract ( epoch FROM now() - i.history * interval '10 day')::int + 6 limit 100)); Could I be sure that ctid

Re: [GENERAL] Missing feature - how to differentiate insert/update in plpgsql function?

On 02/15/2017 06:53 AM, hubert depesz lubaczewski wrote: On Wed, Feb 15, 2017 at 06:44:09AM -0800, Adrian Klaver wrote: On 02/15/2017 06:27 AM, hubert depesz lubaczewski wrote: On Wed, Feb 15, 2017 at 06:24:14AM -0800, Adrian Klaver wrote: On 02/15/2017 06:05 AM, hubert depesz lubaczewski

Re: [GENERAL] Missing feature - how to differentiate insert/update in plpgsql function?

On Wed, Feb 15, 2017 at 02:58:08PM +, Albe Laurenz wrote: > Maybe the following answer can help: > http://stackoverflow.com/a/39204667/6464308 > > I don't really know how stable that (undocumented) behaviour will be, though. Yeah, I'd rather not depend on things like xids for production

Re: [GENERAL] Missing feature - how to differentiate insert/update in plpgsql function?

On Wed, Feb 15, 2017 at 06:44:09AM -0800, Adrian Klaver wrote: > > Well, this wouldn't work for me as pkey will not change. > > Alright you lost me. If the pkey does not change then how do you get new > rows(INSERT)? I think OP is using natural (rather than surrogate) primary keys. So, the PK

Re: [GENERAL] Missing feature - how to differentiate insert/update in plpgsql function?

hubert depesz lubaczewski wrote: > I have a function, in PostgreSQL 9.6, which does: > > INSERT INTO table () values (...) > ON CONFLICT DO UPDATE ...; > > The thing is that the function should return information whether the row > was modified, or created - and currently it seems that this is

Re: [GENERAL] Missing feature - how to differentiate insert/update in plpgsql function?

On Wed, Feb 15, 2017 at 06:44:09AM -0800, Adrian Klaver wrote: > On 02/15/2017 06:27 AM, hubert depesz lubaczewski wrote: > >On Wed, Feb 15, 2017 at 06:24:14AM -0800, Adrian Klaver wrote: > >>On 02/15/2017 06:05 AM, hubert depesz lubaczewski wrote: > >>>Hi, > >>>I have a function, in PostgreSQL

Re: [GENERAL] Missing feature - how to differentiate insert/update in plpgsql function?

On 02/15/2017 06:27 AM, hubert depesz lubaczewski wrote: On Wed, Feb 15, 2017 at 06:24:14AM -0800, Adrian Klaver wrote: On 02/15/2017 06:05 AM, hubert depesz lubaczewski wrote: Hi, I have a function, in PostgreSQL 9.6, which does: INSERT INTO table () values (...) ON CONFLICT DO UPDATE ...;

Re: [GENERAL] Can't restart Postgres

On 02/14/2017 08:47 PM, Shawn Thomas wrote: No it doesn’t matter if run with sudo, postgres or even root. Debian actually wraps the command and executes some some initial scripts with different privileges but ends up making sure that Postgres ends up running under the postgres user. I get the

Re: [GENERAL] Missing feature - how to differentiate insert/update in plpgsql function?

On Wed, Feb 15, 2017 at 06:24:14AM -0800, Adrian Klaver wrote: > On 02/15/2017 06:05 AM, hubert depesz lubaczewski wrote: > >Hi, > >I have a function, in PostgreSQL 9.6, which does: > > > >INSERT INTO table () values (...) > >ON CONFLICT DO UPDATE ...; > > > >The thing is that the function should

Re: [GENERAL] Missing feature - how to differentiate insert/update in plpgsql function?

On 02/15/2017 06:05 AM, hubert depesz lubaczewski wrote: Hi, I have a function, in PostgreSQL 9.6, which does: INSERT INTO table () values (...) ON CONFLICT DO UPDATE ...; The thing is that the function should return information whether the row was modified, or created - and currently it seems

[GENERAL] Missing feature - how to differentiate insert/update in plpgsql function?

Hi, I have a function, in PostgreSQL 9.6, which does: INSERT INTO table () values (...) ON CONFLICT DO UPDATE ...; The thing is that the function should return information whether the row was modified, or created - and currently it seems that this is not available. Or am I missing something?

Re: [GENERAL] Bad planning data resulting in OOM killing of postgres

On Wed, Feb 15, 2017 at 01:04:51PM +0100, Karsten Hilbert wrote: > > Nope, that pops too. The query runs for a long time at a somewhat > > normal rate of ram consumption, using ~1G of RSS then suddenly spikes > > to about 6G, at which point the OOM killer pops it. Box has 8G of ram > > and 4G

Re: [GENERAL] Bad planning data resulting in OOM killing of postgres

On Mon, Feb 13, 2017 at 03:47:08PM -0600, David Hinkle wrote: > Nope, that pops too. The query runs for a long time at a somewhat > normal rate of ram consumption, using ~1G of RSS then suddenly spikes > to about 6G, at which point the OOM killer pops it. Box has 8G of ram > and 4G of swap.