Re: [GENERAL] Problems with Greatest

[Thomas Kellerer]

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 width=8)
> ->  Materialize  (cost=0.00..1088.40 rows=56027 width=8)
> ->  Seq Scan on t2  (cost=0.00..808.27 rows=56027 width=8)
> 
> 
> It seems it has to do a loop on 14 billion rows?  Can someone explain why 
> this would happen?

You are not joining those two tables. 

"from t1, t2" is a cross join that will calculate the cartesian product of both 
tables



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Problems with Greatest

[Teddy Schmitz]

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 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 than the cross join between t1 and t2.

David J.

[GENERAL] Problems with Greatest

[David G. Johnston]

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 than the cross join between t1 and t2.

David J.

Re: [GENERAL] Problems with Greatest

[Pavel Stehule]

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  (cost=0.00..3796.41 rows=263141 width=8)
> ->  Materialize  (cost=0.00..1088.40 rows=56027 width=8)
> ->  Seq Scan on t2  (cost=0.00..808.27 rows=56027 width=8)
>
>
> It seems it has to do a loop on 14 billion rows?  Can someone explain why
> this would happen?
>

sure - you did Cartesian Product
https://www.tutorialspoint.com/sql/sql-cartesian-joins.htm

for bigger tables it should be pretty slow and it is expected behave

Regards

Pavel

>
> Thanks,
>
>
> Teddy
> --
> *From:* Teddy Schmitz
> *Sent:* Thursday, February 16, 2017 12:32:41 PM
> *To:* pgsql-general@postgresql.org
> *Subject:* 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 the sequence after doing a bulk data load 
> into the database.  But this query never returns.  I have tried it with 
> various combinations,
>
> select greatest(max(t1.id), 6) from t1; -> This returns
>
> select greatest(max(t1.id), 6) from t1, t2; -> This never returns.
>
> The query does work if there is only a few hundred items between the tables 
> but I'm importing about ~300,000 rows between the two tables.  I looked at 
> pg_stat_activity and it says the query is active
>
> I worked around this problem using a union all query but I'm wondering if 
> this is a bug or I am just using greatest wrong.
>
>
>

Re: [GENERAL] Problems with Greatest

[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  (cost=0.00..3796.41 rows=263141 width=8)
->  Materialize  (cost=0.00..1088.40 rows=56027 width=8)
->  Seq Scan on t2  (cost=0.00..808.27 rows=56027 width=8)


It seems it has to do a loop on 14 billion rows?  Can someone explain why this 
would happen?


Thanks,


Teddy


From: Teddy Schmitz
Sent: Thursday, February 16, 2017 12:32:41 PM
To: pgsql-general@postgresql.org
Subject: 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 the sequence after doing a bulk data load 
into the database.  But this query never returns.  I have tried it with various 
combinations,

select greatest(max(t1.id), 6) from t1; -> This returns

select greatest(max(t1.id), 6) from t1, t2; -> This never returns.

The query does work if there is only a few hundred items between the tables but 
I'm importing about ~300,000 rows between the two tables.  I looked at 
pg_stat_activity and it says the query is active

I worked around this problem using a union all query but I'm wondering if this 
is a bug or I am just using greatest wrong.

[GENERAL] Problems with Greatest

[Teddy Schmitz]

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 the sequence after doing a bulk data load 
into the database.  But this query never returns.  I have tried it with various 
combinations,

select greatest(max(t1.id), 6) from t1; -> This returns

select greatest(max(t1.id), 6) from t1, t2; -> This never returns.

The query does work if there is only a few hundred items between the tables but 
I'm importing about ~300,000 rows between the two tables.  I looked at 
pg_stat_activity and it says the query is active

I worked around this problem using a union all query but I'm wondering if this 
is a bug or I am just using greatest wrong.

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

[Patrick B]

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
>
>- This is the server that I'm working on. When I ran the query here
>for the first time, it also took >10 seconds. And it is not a LOCK as no
>one was/is using this database server. (using explain analyze)
>- When I ran the query for the second time (using explain analyze), it
>took 1 second to run.
>- On the third time, it took < than 1 second.
>- This server I can reboot the machine/PG or stop/start Postgres
>Process.
>- I've already done: service postgresql stop;
>sync; echo 3 > /proc/sys/vm/drop_caches; service postgresql start
>
>
> I've made some changes to the query and would like to get its real runtime
> so I can compare and keep working if I need to.
>
> *The question is:*
>
> How can I clear the cache, to get a real good estimation of how much the
> query is taking to run?
>
> P.
>


BTW


  ->  Index Only Scan Backward using ix_geo_time_end_user on geo mg
 (cost=0.00..7369.78 rows=24149 width=8) (actual time=0.020..0.020 rows=0
loops=1)


one of the reasons the query is too expensive... the index has 6GB.

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

[Patrick B]

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 query takes to run?
>
> TBH, I think you're probably obsessing over the wrong thing.  It's
> highly unlikely that cache effects would be sufficient to explain
> a 10-second runtime for a query that otherwise takes less than 1 msec.
> What seems more likely is that the query was waiting on a lock, or
> something else that created a non-cache-related bottleneck.
>
> Also, I think you're coming at things from completely the wrong direction
> if you believe that the worst-case, nothing-in-any-level-of-cache case
> is the "true" runtime.  Most people who are worried about performance
> spend a great deal of effort ensuring that that case doesn't happen to
> them in practice.  As an example, the first few queries in a fresh
> session will almost always run slower than later queries, because it
> takes some time to ramp up the new backend's local catalog caches to have
> all the useful data in them.  But the correct response to that observation
> is to try to make sure your sessions last awhile and execute many queries,
> not to decide that the uncached state is the "true" runtime.  It's only
> representative if you're intentionally shooting yourself in the foot.
>
> regards, tom lane
>


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

   - This is the server that I'm working on. When I ran the query here for
   the first time, it also took >10 seconds. And it is not a LOCK as no one
   was/is using this database server. (using explain analyze)
   - When I ran the query for the second time (using explain analyze), it
   took 1 second to run.
   - On the third time, it took < than 1 second.
   - This server I can reboot the machine/PG or stop/start Postgres Process.
   - I've already done: service postgresql stop;
   sync; echo 3 > /proc/sys/vm/drop_caches; service postgresql start


I've made some changes to the query and would like to get its real runtime
so I can compare and keep working if I need to.

*The question is:*

How can I clear the cache, to get a real good estimation of how much the
query is taking to run?

P.

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

[David Hinkle]

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 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 analyze, and the real data to
> compare it to.
>
> cipafilter=# select attname, n_distinct, most_common_freqs from
> pg_stats where tablename = 'log_raw' and (attname = 'urlid' or attname
> = 'titleid' or attname = 'hostid');
>  attname | n_distinct |
>
>
>
>
>
>
>  most_common_freqs
> -++---
>  urlid   |  38963 |
> {0.0188,0.009,0.0085,0.0079,0.00686667,0.0068,0.00616667,0.0061,0.00606667,0.005,0.0049,0.0048,0.0046,0.00456667,0.0044,0.0044,0.0039,0.0032,0.0026,0.0026,0.00256667,0.00256667,0.00246667,0.0023,0.0023,0.0022,0.0020,0.002,0.002,0.002,0.0019,0.00186667,0.0018,0.0018,0.0018,0.00176667,0.00176667,0.00176667,0.00176667,0.0017,0.0017,0.0017,0.0017,0.0017,0.0017,0.0016,0.00156667,0.0015,0.0015,0.00146667,0.0014,0.0014,0.0014,0.0014,0.0014,0.0014,0.0014,0.00136667,0.0013,0.0013,0.0013,0.0013,0.0013,0.00126667,0.00126667,0.0012,0.0012,0.0012,0.0012,0.0012,0.0012,0.0011,0.0011,0.00106667,0.00106667,0.001,0.001,0.001,0.001,0.00097,0.00097,0.00097,0.00097,0.00097,0.00093,0.00093,0.00093,0.0009,0.0009,0.0009,0.0009,0.0009,0.0009,0.0009,0.0009,0.0009,0.00087,0.00087,0.00087,0.00087}
>  hostid  |   2478 |
> {0.2109,0.0330667,0.0321667,0.0245,0.0223,0.0177667,0.0165,0.0152,0.0151333,0.014,0.0132,0.0115667,0.0099,0.0096,0.0087,0.0085,0.0085,0.00746667,0.0074,0.0065,0.0065,0.0065,0.00646667,0.0051,0.00506667,0.0050,0.00496667,0.0049,0.0049,0.00486667,0.0048,0.0045,0.0044,0.0043,0.00426667,0.0041,0.00396667,0.00386667,0.0038,0.0036,0.0036,0.0036,0.00346667,0.0032,0.0032,0.0032,0.00316667,0.0030,0.0030,0.0029,0.00286667,0.0027,0.0027,0.0027,0.0026,0.0026,0.0026,0.0026,0.0025,0.0024,0.0024,0.0024,0.00236667,0.00226667,0.0022,0.00216667,0.0021,0.0021,0.0021,0.00206667,0.00206667,0.0020,0.002,0.002,0.0019,0.0019,0.0019,0.00186667,0.00186667,0.00186667,0.0018,0.0018,0.0018,0.00176667,0.0017,0.0017,0.0017,0.0017,0.0017,0.0017,0.0016,0.0016,0.0015,0.0015,0.0015,0.0015,0.00146667,0.00146667,0.00146667,0.00146667}
>  titleid |292 | {0.767167}
> (3 rows)
>
> I have to patch the pg_stats table to get postgres to run the
> following queries without crashing:
>
> cipafilter=# UPDATE pg_statistic AS s
> cipafilter-# SET stadistinct = (select reltuples from pg_class
> where relname = 'titles')
> cipafilter-# FROM pg_class c, pg_attribute a where c.oid =
> s.starelid and c.oid = a.attrelid and a.attnum = s.staattnum and
> cipafilter-# relname = 'log_raw' and attname = 'titleid';
> UPDATE 1
> cipafilter=# UPDATE pg_statistic AS s
> cipafilter-# SET stadistinct = (select reltuples from pg_class
> where relname = 'urls')
> cipafilter-# FROM pg_class c, pg_attribute a where c.oid =
> s.starelid and c.oid = a.attrelid and a.attnum = s.staattnum and
> cipafilter-# relname = 'log_raw' and attname = 'urlid';
> UPDATE 1
> cipafilter=# UPDATE pg_statistic AS s
> cipafilter-# SET stadistinct = (select reltuples from pg_class
> where relname = 'hosts')
> cipafilter-# FROM pg_class c, pg_attribute a where c.oid =
> s.starelid and c.oid = a.attrelid and a.attnum = s.staattnum and
> cipafilter-# relname = 'log_raw' and attname = 'hostid';
> UPDATE 1
>
> cipafilter=#  select attname, 

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

[David Hinkle]

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 analyze, and the real data to
compare it to.

cipafilter=# select attname, n_distinct, most_common_freqs from
pg_stats where tablename = 'log_raw' and (attname = 'urlid' or attname
= 'titleid' or attname = 'hostid');
 attname | n_distinct |






 most_common_freqs
-++---
 urlid   |  38963 |
{0.0188,0.009,0.0085,0.0079,0.00686667,0.0068,0.00616667,0.0061,0.00606667,0.005,0.0049,0.0048,0.0046,0.00456667,0.0044,0.0044,0.0039,0.0032,0.0026,0.0026,0.00256667,0.00256667,0.00246667,0.0023,0.0023,0.0022,0.0020,0.002,0.002,0.002,0.0019,0.00186667,0.0018,0.0018,0.0018,0.00176667,0.00176667,0.00176667,0.00176667,0.0017,0.0017,0.0017,0.0017,0.0017,0.0017,0.0016,0.00156667,0.0015,0.0015,0.00146667,0.0014,0.0014,0.0014,0.0014,0.0014,0.0014,0.0014,0.00136667,0.0013,0.0013,0.0013,0.0013,0.0013,0.00126667,0.00126667,0.0012,0.0012,0.0012,0.0012,0.0012,0.0012,0.0011,0.0011,0.00106667,0.00106667,0.001,0.001,0.001,0.001,0.00097,0.00097,0.00097,0.00097,0.00097,0.00093,0.00093,0.00093,0.0009,0.0009,0.0009,0.0009,0.0009,0.0009,0.0009,0.0009,0.0009,0.00087,0.00087,0.00087,0.00087}
 hostid  |   2478 |
{0.2109,0.0330667,0.0321667,0.0245,0.0223,0.0177667,0.0165,0.0152,0.0151333,0.014,0.0132,0.0115667,0.0099,0.0096,0.0087,0.0085,0.0085,0.00746667,0.0074,0.0065,0.0065,0.0065,0.00646667,0.0051,0.00506667,0.0050,0.00496667,0.0049,0.0049,0.00486667,0.0048,0.0045,0.0044,0.0043,0.00426667,0.0041,0.00396667,0.00386667,0.0038,0.0036,0.0036,0.0036,0.00346667,0.0032,0.0032,0.0032,0.00316667,0.0030,0.0030,0.0029,0.00286667,0.0027,0.0027,0.0027,0.0026,0.0026,0.0026,0.0026,0.0025,0.0024,0.0024,0.0024,0.00236667,0.00226667,0.0022,0.00216667,0.0021,0.0021,0.0021,0.00206667,0.00206667,0.0020,0.002,0.002,0.0019,0.0019,0.0019,0.00186667,0.00186667,0.00186667,0.0018,0.0018,0.0018,0.00176667,0.0017,0.0017,0.0017,0.0017,0.0017,0.0017,0.0016,0.0016,0.0015,0.0015,0.0015,0.0015,0.00146667,0.00146667,0.00146667,0.00146667}
 titleid |292 | {0.767167}
(3 rows)

I have to patch the pg_stats table to get postgres to run the
following queries without crashing:

cipafilter=# UPDATE pg_statistic AS s
cipafilter-# SET stadistinct = (select reltuples from pg_class
where relname = 'titles')
cipafilter-# FROM pg_class c, pg_attribute a where c.oid =
s.starelid and c.oid = a.attrelid and a.attnum = s.staattnum and
cipafilter-# relname = 'log_raw' and attname = 'titleid';
UPDATE 1
cipafilter=# UPDATE pg_statistic AS s
cipafilter-# SET stadistinct = (select reltuples from pg_class
where relname = 'urls')
cipafilter-# FROM pg_class c, pg_attribute a where c.oid =
s.starelid and c.oid = a.attrelid and a.attnum = s.staattnum and
cipafilter-# relname = 'log_raw' and attname = 'urlid';
UPDATE 1
cipafilter=# UPDATE pg_statistic AS s
cipafilter-# SET stadistinct = (select reltuples from pg_class
where relname = 'hosts')
cipafilter-# FROM pg_class c, pg_attribute a where c.oid =
s.starelid and c.oid = a.attrelid and a.attnum = s.staattnum and
cipafilter-# relname = 'log_raw' and attname = 'hostid';
UPDATE 1

cipafilter=#  select attname, n_distinct from pg_stats where tablename
= 'log_raw' and (attname = 'urlid' or attname = 'titleid' or attname =
'hostid');
 attname | n_distinct
-+-
 urlid   | 1.51452e+08
 hostid  |  303756
 titleid |  879485

cipafilter=# select titleid, count(titleid) from log_raw group by
titleid order by 

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

[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 query takes to run?

TBH, I think you're probably obsessing over the wrong thing.  It's
highly unlikely that cache effects would be sufficient to explain
a 10-second runtime for a query that otherwise takes less than 1 msec.
What seems more likely is that the query was waiting on a lock, or
something else that created a non-cache-related bottleneck.

Also, I think you're coming at things from completely the wrong direction
if you believe that the worst-case, nothing-in-any-level-of-cache case
is the "true" runtime.  Most people who are worried about performance
spend a great deal of effort ensuring that that case doesn't happen to
them in practice.  As an example, the first few queries in a fresh
session will almost always run slower than later queries, because it
takes some time to ramp up the new backend's local catalog caches to have
all the useful data in them.  But the correct response to that observation
is to try to make sure your sessions last awhile and execute many queries,
not to decide that the uncached state is the "true" runtime.  It's only
representative if you're intentionally shooting yourself in the foot.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Patrick B]

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 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 id from test where id = 124;
> >
> > The runtime is 0.8ms.
>
> This doesn't make seem to make sense.
>
> "explain select ..." doesn't run the query. All it shows is the plan the
> planner chose and some estimates of the "cost" of different steps, with no
> time. Where are you getting 40ms from in this case?
>

>From my DB-VISUALIZER - it shows how long the query took.


>
> "explain analyze select ..." does run the query, along with some -
> potentially non-trivial - instrumentation to measure each step of the plan,
> so you can see whether the planner estimates are reasonable or wildly off.
>
>
> Well.. that is what's happening.

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?

Re: [GENERAL] Can't restart Postgres

[Joshua D. Drake]

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



--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[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 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 id from test where id = 124;
> 
> The runtime is 0.8ms.

This doesn't make seem to make sense.

"explain select ..." doesn't run the query. All it shows is the plan the 
planner chose and some estimates of the "cost" of different steps, with no 
time. Where are you getting 40ms from in this case?

"explain analyze select ..." does run the query, along with some - potentially 
non-trivial - instrumentation to measure each step of the plan, so you can see 
whether the planner estimates are reasonable or wildly off.

Cheers,
  Steve



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Potential Bug: Frequent Unnecessary Degeneration

[Andres Freund]

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
isn't one of those.  Check 
http://www.pgpool.net/mediawiki/index.php/Main_Page#Contacts

- Andres


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Patrick B]

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 id from test where id = 124;


The runtime is 0.8ms.

Probably the statement is cached, right?


So, I do (To clear the cache):


   - service postgresql stop
   - echo 3 > /proc/sys/vm/drop_caches
   - service postgresql start

But, then, executing the statement again, it runs in 0.8 ms.

How can I clean the cache to get the REAL runtime for that statement?
Thanks
Patrick

[GENERAL] Potential Bug: Frequent Unnecessary Degeneration

[David O'Mahony]

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:  while trying to write data from
offset: 0 wlen: 5

The source code (pool_stream.c:pool_flush_it()) seems to consider a write
of 0 bytes an error.  Also the "Success" in the log statement indicates
that errno was not set.

There never appears to be any actual problem with the node.

Should pgpool trigger a failover in this situation?

Thanks,

Dave

Re: [GENERAL] Can't restart Postgres

[Shawn Thomas]

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 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 reinstall Postgres, the install should leave the 
> original directory alone and you will be ready to go. Should there be an oops 
> you will have the copy as backup.
> 
>> 
>> -Shawn
>> 
>>> On Feb 15, 2017, at 9:36 AM, Magnus Hagander >> > wrote:
>>> 
>>> 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 package in place.  But
>>>perhaps I was wrong.  I’ll follow Tom’s advice and just re-install
>>>everything (saving the old data directory) and hope the new
>>>installation can use the old data data directory.
>>> 
>>> 
>>> If you removed it and then installed it, then the removal would remove
>>> all dependent packages and if you then only intalled that one and not
>>> the dependencies that would explain it.
>>> 
>>> If you had run a reinstall on it, then it would've kept them around.
>>> 
>>> 
>>> 
>>>One question about this approach though:  the Debian package
>>>installation automatically initializes the new data directory and
>>>starts PG.  If I shut it down and copy the old data directory into
>>>the newly installed one, will there be an xlog issue?
>>> 
>>> 
>>> You have to copy the xlog along with the database.
>>> 
>>> Or if you leave it in place where it is, the packages won't initialize
>>> a new data directory.
>>> 
>>> --
>>> Magnus Hagander
>>> Me: http://www.hagander.net/
>>> Work: http://www.redpill-linpro.com/
>> 
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Alvaro Herrera]

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
freeze_max_age limit, and then autovacuum is a for-wraparound one that
is no longer terminated, and then everybody has to wait on it.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Scott Marlowe]

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 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 time, and 
>> so would like help resolving the issue.
>>
>> The process blocking the query is:
>> postgres 21985 11304 98 Feb13 ?1-14:20:52 postgres: autovacuum 
>> worker process   
>> which is running the query
>> autovacuum: VACUUM public.
>>
>> The query being blocked is:
>> ALTER TABLE  ALTER COLUMN  DROP DEFAULT
>> (But I have seen this previously with other queries being blocked. I used 
>> the SQL in https://wiki.postgresql.org/wiki/Lock_Monitoring to determine 
>> which queries were blocked)
>
> There are also ways of making the table less likely / not likely /
> will not get vacuum automatically. If you're willing to schedule ddl
> and vacuum on your own you can then mix the two in relative safety.

Followup: 
https://www.postgresql.org/docs/9.3/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Scott Marlowe]

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 impression that vacuum 
> should never take any blocking locks for any significant period of time, and 
> so would like help resolving the issue.
>
> The process blocking the query is:
> postgres 21985 11304 98 Feb13 ?1-14:20:52 postgres: autovacuum worker 
> process   
> which is running the query
> autovacuum: VACUUM public.
>
> The query being blocked is:
> ALTER TABLE  ALTER COLUMN  DROP DEFAULT
> (But I have seen this previously with other queries being blocked. I used the 
> SQL in https://wiki.postgresql.org/wiki/Lock_Monitoring to determine which 
> queries were blocked)

Yup, there's a priority inversion in DDL, DML and maintenance
(vacuum). Vacuum runs slow in the background. Normal
update/delete/insert work fine because of the type of lock vacuum has.

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.

Basically it's bad practice to alter tables that are big and being
worked on, because one way or another you're going to pay a price.

I've used partitions for logging and auditing that autocreate and drop
and vacuum, but they never get ddl done on them when they're getting
updated and vice versa.

There are also ways of making the table less likely / not likely /
will not get vacuum automatically. If you're willing to schedule ddl
and vacuum on your own you can then mix the two in relative safety.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[David G. Johnston]

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 utf8?
>
>
> your help would be really appreciated.
>
>
​Duplicate post on -bugs already answered - it came through the mail system
first even through this is the correct list for the topic.

David J.
​

[GENERAL] Problem with PostgreSQL string sorting Hello All,

[Hari Sankar A]

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 collate sorting this field as:

name
--
abc
Abc
.dcb
Dcb
EDF
$sdf

It ignore's Special characters.

"C" collate sorting this field as:
name
--
$sdf
.dcb
Abc
Dcb
EDF
abc

It does case sensitive sort.

My expected sorting should be: (Case insensitive and special character)

name
--
$sdf
.dcb
Abc
abc
Dcb
EDF

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 utf8?


your help would be really appreciated.


Thanks,
Hari

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

[Adrian Klaver]

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 blocking locks for any significant period of time, and so would like 
help resolving the issue.

The process blocking the query is:
postgres 21985 11304 98 Feb13 ?1-14:20:52 postgres: autovacuum worker process 
  
which is running the query
autovacuum: VACUUM public.

The query being blocked is:
ALTER TABLE  ALTER COLUMN  DROP DEFAULT
(But I have seen this previously with other queries being blocked. I used the 
SQL in https://wiki.postgresql.org/wiki/Lock_Monitoring to determine which 
queries were blocked)



Other ALTER TABLE queries?

If so I believe this might apply:

https://www.postgresql.org/docs/9.5/static/explicit-locking.html

SHARE UPDATE EXCLUSIVE

Conflicts with the SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW 
EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode 
protects a table against concurrent schema changes and VACUUM runs.


Acquired by VACUUM (without FULL), ANALYZE, CREATE INDEX 
CONCURRENTLY, and ALTER TABLE VALIDATE and other ALTER TABLE variants 
(for full details see ALTER TABLE).



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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Alessandro Baggi]

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 joins
multiple tables and return a result like:

id,customers,phone,code,number
1 , ,3,123 , 2
2 , aassdsds,33322,211 , 1
3 , ,21221,221 , 1


I need, where "number" field is > 1, to duplicate the row * N(number field
value) with a result like this:

id,customers,phone,code,number
1 , ,3,123 , 2
1 , ,3,123 , 2
2 , aassdsds,33322,211 , 1
3 , ,21221,221 , 1

How I can accomplish to this problem?


SELECT * FROM foo CROSS JOIN LATERAL (1,number);

:-D


oops -- copy/paste error

SELECT * FROM foo CROSS JOIN LATERAL generate_series(1,number);

merlin
.


Hi Merlin,
I've tried your suggested code and with cross join and generate_series I 
can generate multiple row. There is a way to put as second args a column 
values? I've tried to put "table.number" column values but I got 
"generate_series() does not exists". Inserting a simple int like 5 I get 
5 results for each row.


I've searched on google but can't find a valid example.



Thanks in advance.




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Can't restart Postgres

[Shawn Thomas]

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 PG?
> 
> I would copy the data directory somewhere else for safe keeping leaving the 
> original in place. Then reinstall Postgres, the install should leave the 
> original directory alone and you will be ready to go. Should there be an oops 
> you will have the copy as backup.
> 
>> 
>> -Shawn
>> 
>>> On Feb 15, 2017, at 9:36 AM, Magnus Hagander >> > wrote:
>>> 
>>> 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 package in place.  But
>>>perhaps I was wrong.  I’ll follow Tom’s advice and just re-install
>>>everything (saving the old data directory) and hope the new
>>>installation can use the old data data directory.
>>> 
>>> 
>>> If you removed it and then installed it, then the removal would remove
>>> all dependent packages and if you then only intalled that one and not
>>> the dependencies that would explain it.
>>> 
>>> If you had run a reinstall on it, then it would've kept them around.
>>> 
>>> 
>>> 
>>>One question about this approach though:  the Debian package
>>>installation automatically initializes the new data directory and
>>>starts PG.  If I shut it down and copy the old data directory into
>>>the newly installed one, will there be an xlog issue?
>>> 
>>> 
>>> You have to copy the xlog along with the database.
>>> 
>>> Or if you leave it in place where it is, the packages won't initialize
>>> a new data directory.
>>> 
>>> --
>>> Magnus Hagander
>>> Me: http://www.hagander.net/
>>> Work: http://www.redpill-linpro.com/
>> 
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Can't restart Postgres

[Shawn Thomas]

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 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:
> 
> sudo apt-get remove postgresql-common
> 
> The following packages will be REMOVED:
>  postgresql-9.4 postgresql-9.6 postgresql-common postgresql-contrib-9.4 
> postgresql-contrib-9.6 postgresql-server-dev-9.4 postgresql-server-dev-9.6
> Do you want to continue? [Y/n]
> 
> Which would explain a lot.
> 
>> 
>> 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
>> everything (saving the old data directory) and hope the new installation
>> can use the old data data directory.
>> 
>> One question about this approach though:  the Debian package
>> installation automatically initializes the new data directory and starts
>> PG.  If I shut it down and copy the old data directory into the newly
>> installed one, will there be an xlog issue?
>> 
>> -Shawn
>> 
>>> On Feb 15, 2017, at 9:09 AM, Magnus Hagander >> > wrote:
>>> 
>>> 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 root4096 Feb  9 16:17 ..
>>>  -rwxr-xr-x 1 root root   68128 Nov 16 06:53 clusterdb
>>>  -rwxr-xr-x 1 root root   68192 Nov 16 06:53 createdb
>>>  -rwxr-xr-x 1 root root   63920 Nov 16 06:53 createlang
>>>  -rwxr-xr-x 1 root root   72672 Nov 16 06:53 createuser
>>>  -rwxr-xr-x 1 root root   63936 Nov 16 06:53 dropdb
>>>  -rwxr-xr-x 1 root root   63920 Nov 16 06:53 droplang
>>>  -rwxr-xr-x 1 root root   63904 Nov 16 06:53 dropuser
>>>  -rwxr-xr-x 1 root root   68416 Nov 16 06:53 pg_basebackup
>>>  -rwxr-xr-x 1 root root  351904 Nov 16 06:53 pg_dump
>>>  -rwxr-xr-x 1 root root 2186504 Nov 16 06:53 pg_dumpall
>>>  -rwxr-xr-x 1 root root   30992 Nov 16 06:53 pg_isready
>>>  -rwxr-xr-x 1 root root   47600 Nov 16 06:53 pg_receivexlog
>>>  -rwxr-xr-x 1 root root   51928 Nov 16 06:53 pg_recvlogical
>>>  -rwxr-xr-x 1 root root  154944 Nov 16 06:53 pg_restore
>>>  -rwxr-xr-x 1 root root  515320 Nov 16 06:53 psql
>>>  -rwxr-xr-x 1 root root   68160 Nov 16 06:53 reindexdb
>>>  -rwxr-xr-x 1 root root   72384 Nov 16 06:53 vacuumdb
>>> 
>>>As I mentioned, this Debian package removes pg_ctl from the bin
>>>directory and instead attempts to wrap the pg_ctl functionality in
>>>a perl script so that the PG process is integrated with systemd.
>>>I really wish they hadn’t, and it’s part of the reason I’m where
>>>I’m at.
>>> 
>>> 
>>> pg_ctl is normally present in /usr/lib/postgresql//bin on a
>>> debian system. If that is gone, somebody removed it, or you didn't
>>> install the "postgresql-9.4" package which provides it. On a 9.4 system:
>>> 
>>> $ dpkg -S /usr/lib/postgresql/9.4/bin/pg_ctl
>>> postgresql-9.4: /usr/lib/postgresql/9.4/bin/pg_ctl
>>> 
>>> You could try reinstalling the postgresql-9.4 package and see if it
>>> comes back. The rest of the binaries in that directory seems to be
>>> from postgresql-9.4-client though -- have you actually by mistake
>>> uninstalled the server package completely?
>>> 
>>> As in, that directory is supposed to have the "postgres" binary which
>>> is the database server and it's not there. So there is no wonder it's
>>> not starting...
>>> 
>>> --
>>> Magnus Hagander
>>> Me: http://www.hagander.net/
>>> Work: http://www.redpill-linpro.com/
>> 
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Can't restart Postgres

[Adrian Klaver]

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 reinstall Postgres, the install should leave 
the original directory alone and you will be ready to go. Should there 
be an oops you will have the copy as backup.




-Shawn


On Feb 15, 2017, at 9:36 AM, Magnus Hagander > wrote:

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 package in place.  But
perhaps I was wrong.  I’ll follow Tom’s advice and just re-install
everything (saving the old data directory) and hope the new
installation can use the old data data directory.


If you removed it and then installed it, then the removal would remove
all dependent packages and if you then only intalled that one and not
the dependencies that would explain it.

If you had run a reinstall on it, then it would've kept them around.



One question about this approach though:  the Debian package
installation automatically initializes the new data directory and
starts PG.  If I shut it down and copy the old data directory into
the newly installed one, will there be an xlog issue?


You have to copy the xlog along with the database.

Or if you leave it in place where it is, the packages won't initialize
a new data directory.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/





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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Can't restart Postgres

[Shawn Thomas]

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  > 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 package in place.  But perhaps I 
> was wrong.  I’ll follow Tom’s advice and just re-install everything (saving 
> the old data directory) and hope the new installation can use the old data 
> data directory.
> 
> If you removed it and then installed it, then the removal would remove all 
> dependent packages and if you then only intalled that one and not the 
> dependencies that would explain it.
> 
> If you had run a reinstall on it, then it would've kept them around.
> 
>  
> One question about this approach though:  the Debian package installation 
> automatically initializes the new data directory and starts PG.  If I shut it 
> down and copy the old data directory into the newly installed one, will there 
> be an xlog issue?
> 
> You have to copy the xlog along with the database.
> 
> Or if you leave it in place where it is, the packages won't initialize a new 
> data directory.
> 
> -- 
>  Magnus Hagander
>  Me: http://www.hagander.net/ 
>  Work: http://www.redpill-linpro.com/ 

Re: [GENERAL] Can't restart Postgres

[Shawn Thomas]

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 get the database back in place.

-Shawn

> On Feb 15, 2017, at 9:01 AM, Adrian Klaver  wrote:
> 
> 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 deleted and 
>> cannot be recovered so I need to recover the data from the 
>> /var/lib/postgresql/9.4/main directory.  I believe this is called a file 
>> level recovery.  I assume I need to use a fully functional, same version PG 
>> (on another machine?) to create a full dump of the data directory.  Once I 
>> have this I can re-install Postgres on the initial server and read the 
>> databases back into it.
> 
> I have to believe that if you cannot get the server to start then the data 
> directory is no shape to recover from. And if the data directory is good and 
> it is the program files that are corrupted then it would be a matter of 
> reinstalling Postgres. In either case the most important thing to do would be 
> to make a copy of the data directory before you do anything else.
> 
> What exactly happened that caused the ssl cert and the pg_dumpall file to 
> deleted?
> 
> In other words what else got deleted?
> 
>> 
>> Any advice on how to best go about this?  The official documentation seems a 
>> bit thin:
>> 
>> https://www.postgresql.org/docs/9.4/static/backup-file.html
>> 
>> I’ve only worked with normal (pg_dump, pg_dumpall) backups in the past.
>> 
>> -Shawn
>> 
>>> On Feb 15, 2017, at 6:35 AM, Adrian Klaver  
>>> wrote:
>>> 
>>> 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 same output if run with sudo:
 
 sudo systemctl status postgresql@9.4-main.service
  -l
  Error: could not exec   start -D /var/lib/postgresql/9.4/main -l
 /var/log/postgresql/postgresql-9.4-main.log -s -o  -c
 config_file="/etc/postgresql/9.4/main/postgresql.conf”
 
>>> 
>>> 
>>> So you are talking about:
>>> 
>>> /etc/init.d/postgresql
>>> 
>>> which then calls:
>>> 
>>> /usr/share/postgresql-common/init.d-functions
>>> 
>>> Or is there another setup on your system?
>>> 
>>> Any relevant information in the system logs?
>>> 
 Thanks, though.
 
 -Shawn
>>> 
>>> 
>>> --
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com
>> 
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Can't restart Postgres

[Adrian Klaver]

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:


sudo apt-get remove postgresql-common

The following packages will be REMOVED:
  postgresql-9.4 postgresql-9.6 postgresql-common 
postgresql-contrib-9.4 postgresql-contrib-9.6 postgresql-server-dev-9.4 
postgresql-server-dev-9.6

Do you want to continue? [Y/n]

Which would explain a lot.



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
everything (saving the old data directory) and hope the new installation
can use the old data data directory.

One question about this approach though:  the Debian package
installation automatically initializes the new data directory and starts
PG.  If I shut it down and copy the old data directory into the newly
installed one, will there be an xlog issue?

-Shawn


On Feb 15, 2017, at 9:09 AM, Magnus Hagander > wrote:

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 root4096 Feb  9 16:17 ..
  -rwxr-xr-x 1 root root   68128 Nov 16 06:53 clusterdb
  -rwxr-xr-x 1 root root   68192 Nov 16 06:53 createdb
  -rwxr-xr-x 1 root root   63920 Nov 16 06:53 createlang
  -rwxr-xr-x 1 root root   72672 Nov 16 06:53 createuser
  -rwxr-xr-x 1 root root   63936 Nov 16 06:53 dropdb
  -rwxr-xr-x 1 root root   63920 Nov 16 06:53 droplang
  -rwxr-xr-x 1 root root   63904 Nov 16 06:53 dropuser
  -rwxr-xr-x 1 root root   68416 Nov 16 06:53 pg_basebackup
  -rwxr-xr-x 1 root root  351904 Nov 16 06:53 pg_dump
  -rwxr-xr-x 1 root root 2186504 Nov 16 06:53 pg_dumpall
  -rwxr-xr-x 1 root root   30992 Nov 16 06:53 pg_isready
  -rwxr-xr-x 1 root root   47600 Nov 16 06:53 pg_receivexlog
  -rwxr-xr-x 1 root root   51928 Nov 16 06:53 pg_recvlogical
  -rwxr-xr-x 1 root root  154944 Nov 16 06:53 pg_restore
  -rwxr-xr-x 1 root root  515320 Nov 16 06:53 psql
  -rwxr-xr-x 1 root root   68160 Nov 16 06:53 reindexdb
  -rwxr-xr-x 1 root root   72384 Nov 16 06:53 vacuumdb

As I mentioned, this Debian package removes pg_ctl from the bin
directory and instead attempts to wrap the pg_ctl functionality in
a perl script so that the PG process is integrated with systemd.
I really wish they hadn’t, and it’s part of the reason I’m where
I’m at.


pg_ctl is normally present in /usr/lib/postgresql//bin on a
debian system. If that is gone, somebody removed it, or you didn't
install the "postgresql-9.4" package which provides it. On a 9.4 system:

$ dpkg -S /usr/lib/postgresql/9.4/bin/pg_ctl
postgresql-9.4: /usr/lib/postgresql/9.4/bin/pg_ctl

You could try reinstalling the postgresql-9.4 package and see if it
comes back. The rest of the binaries in that directory seems to be
from postgresql-9.4-client though -- have you actually by mistake
uninstalled the server package completely?

As in, that directory is supposed to have the "postgres" binary which
is the database server and it's not there. So there is no wonder it's
not starting...

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/





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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Can't restart Postgres

[Magnus Hagander]

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 package in place.  But perhaps
> I was wrong.  I’ll follow Tom’s advice and just re-install everything
> (saving the old data directory) and hope the new installation can use the
> old data data directory.
>

If you removed it and then installed it, then the removal would remove all
dependent packages and if you then only intalled that one and not the
dependencies that would explain it.

If you had run a reinstall on it, then it would've kept them around.



> One question about this approach though:  the Debian package installation
> automatically initializes the new data directory and starts PG.  If I shut
> it down and copy the old data directory into the newly installed one, will
> there be an xlog issue?
>

You have to copy the xlog along with the database.

Or if you leave it in place where it is, the packages won't initialize a
new data directory.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Can't restart Postgres

[Shawn Thomas]

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 everything (saving the 
old data directory) and hope the new installation can use the old data data 
directory.

One question about this approach though:  the Debian package installation 
automatically initializes the new data directory and starts PG.  If I shut it 
down and copy the old data directory into the newly installed one, will there 
be an xlog issue?

-Shawn

> On Feb 15, 2017, at 9:09 AM, Magnus Hagander  wrote:
> 
> 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 root4096 Feb  9 16:17 ..
>   -rwxr-xr-x 1 root root   68128 Nov 16 06:53 clusterdb
>   -rwxr-xr-x 1 root root   68192 Nov 16 06:53 createdb
>   -rwxr-xr-x 1 root root   63920 Nov 16 06:53 createlang
>   -rwxr-xr-x 1 root root   72672 Nov 16 06:53 createuser
>   -rwxr-xr-x 1 root root   63936 Nov 16 06:53 dropdb
>   -rwxr-xr-x 1 root root   63920 Nov 16 06:53 droplang
>   -rwxr-xr-x 1 root root   63904 Nov 16 06:53 dropuser
>   -rwxr-xr-x 1 root root   68416 Nov 16 06:53 pg_basebackup
>   -rwxr-xr-x 1 root root  351904 Nov 16 06:53 pg_dump
>   -rwxr-xr-x 1 root root 2186504 Nov 16 06:53 pg_dumpall
>   -rwxr-xr-x 1 root root   30992 Nov 16 06:53 pg_isready
>   -rwxr-xr-x 1 root root   47600 Nov 16 06:53 pg_receivexlog
>   -rwxr-xr-x 1 root root   51928 Nov 16 06:53 pg_recvlogical
>   -rwxr-xr-x 1 root root  154944 Nov 16 06:53 pg_restore
>   -rwxr-xr-x 1 root root  515320 Nov 16 06:53 psql
>   -rwxr-xr-x 1 root root   68160 Nov 16 06:53 reindexdb
>   -rwxr-xr-x 1 root root   72384 Nov 16 06:53 vacuumdb
> 
> As I mentioned, this Debian package removes pg_ctl from the bin directory and 
> instead attempts to wrap the pg_ctl functionality in a perl script so that 
> the PG process is integrated with systemd.  I really wish they hadn’t, and 
> it’s part of the reason I’m where I’m at.
> 
> pg_ctl is normally present in /usr/lib/postgresql//bin on a debian 
> system. If that is gone, somebody removed it, or you didn't install the 
> "postgresql-9.4" package which provides it. On a 9.4 system:
> 
> $ dpkg -S /usr/lib/postgresql/9.4/bin/pg_ctl
> postgresql-9.4: /usr/lib/postgresql/9.4/bin/pg_ctl
> 
> You could try reinstalling the postgresql-9.4 package and see if it comes 
> back. The rest of the binaries in that directory seems to be from 
> postgresql-9.4-client though -- have you actually by mistake uninstalled the 
> server package completely?
> 
> As in, that directory is supposed to have the "postgres" binary which is the 
> database server and it's not there. So there is no wonder it's not 
> starting... 
> 
> -- 
>  Magnus Hagander
>  Me: http://www.hagander.net/ 
>  Work: http://www.redpill-linpro.com/ 

Re: [GENERAL] Can't restart Postgres

[Adrian Klaver]

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
Description:Ubuntu 16.04.2 LTS
Release:16.04
Codename:   xenial



This is starting to sound like someone inadvertently executed an rm
somewhere they shouldn't have (outside of just the original ssl file).


Or a defective package(s) upgrade. Either way crucial parts are missing.



JD





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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Autovacuum stuck for hours, blocking queries

[Tim Bellis]

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 time, and so would like 
help resolving the issue.

The process blocking the query is:
postgres 21985 11304 98 Feb13 ?1-14:20:52 postgres: autovacuum worker 
process   
which is running the query
autovacuum: VACUUM public.

The query being blocked is:
ALTER TABLE  ALTER COLUMN  DROP DEFAULT
(But I have seen this previously with other queries being blocked. I used the 
SQL in https://wiki.postgresql.org/wiki/Lock_Monitoring to determine which 
queries were blocked)

Notes:
  - This database table is used for about 6 million row writes per day, all of 
which are then deleted at the end of the day.
  - We have a reasonable number of systems running identical databases and near 
identical load profiles, and this problem has only been seen on two of those 
systems. It occurs intermittently but reliably (once every few days). It 
persists until postgres is restarted (usually accomplished by a reboot) or the 
autovacuum thread is forcibly killed. Although it often happens again after a 
short period of time when the autovacuum worker process starts.
  - I don't have data on whether this vacuum ever finishes - it certainly takes 
longer than a few hours.
  - Our application kicks off a manual vacuum against this table each night 
which doesn't hit this problem, as far as we're aware.
  - I have sometimes seen this behaviour when there was database corruption. In 
that case I see logs like the following. But it also occurs when there are no 
logs indicating database corruption (which is the purpose of this email)
LOG:  no left sibling (concurrent deletion?) in ""
ERROR:  right sibling's left-link doesn't match: block 41 links to 127 
instead of expected 79 in index ""

Things that I have tried:
  - Printing out backtrace a few times from connecting to that process with gdb 
(see below)
- This indicates that the vacuum process isn't stuck, but I don't know what 
else it says
  - Reading the FAQ and release notes of other 9.3.x versions to see whether 
this looks like a known issue. I couldn't see anything obvious apart from an 
issue about  files (which didn't apply to my system - there was SQL in the 
release note to check).
  - Forcibly killing the autovacuum thread (obviously not a good solution to 
the problem!). This causes the ALTER TABLE query to complete correctly.

Questions:
  - Is this expected? I was under the impression that vacuum should never take 
any blocking locks for any significant period of time.
  - Should I report this as a bug?
  - Should I change some config options to avoid this? (I know turning off 
autovacuum would avoid it, but that's not good practice) I've put the vacuum 
settings for my database below.
  - Should I avoid certain queries which autovacuum is happening?
  - Are there more diags that I should gather to help diagnose this issue?

Thanks!

Tim

--System details--
Postgres 9.3.4 running on Linux x64 VMWare virtual machine. 24 CPUs of 2.7 GHz. 
48GB RAM
It is also running another application (so the database is not the only thing 
on the server).


---Backtrace (from gdb) ---

#0  0x007328c3 in hash_search_with_hash_value ()
#1  0x006369ba in BufTableLookup ()
#2  0x00639192 in ?? ()
#3  0x00639ade in ReadBufferExtended ()
#4  0x004906d9 in _bt_getbuf ()
#5  0x004910e1 in _bt_pagedel ()
#6  0x004924d1 in ?? ()
#7  0x004926da in ?? ()
#8  0x004928fa in btbulkdelete ()
#9  0x0072cdbd in FunctionCall4Coll ()
#10 0x0048cf58 in index_bulk_delete ()
#11 0x0057bb85 in ?? ()
#12 0x0057cfe9 in lazy_vacuum_rel ()
#13 0x0057af56 in ?? ()
#14 0x0057b28c in vacuum ()
#15 0x0060c8fa in ?? ()
#16 0x0060cd96 in ?? ()
#17 0x0060ce66 in StartAutoVacWorker ()
#18 0x00617602 in ?? ()
#19 
#20 0x7f9210c9d393 in __select_nocancel () from /lib64/libc.so.6
#21 0x00618add in PostmasterMain ()
#22 0x005b58d0 in main ()

#0  0x00491107 in _bt_pagedel ()
#1  0x004924d1 in ?? ()
#2  0x004926da in ?? ()
#3  0x004928fa in btbulkdelete ()
...

#0  0x0047a1ef in hash_any ()
#1  0x007336e9 in tag_hash ()
#2  0x0063916c in ?? ()
#3  0x00639ade in ReadBufferExtended ()
#4  0x004906d9 in _bt_getbuf ()
#5  0x004910e1 in _bt_pagedel ()
#6  0x004924d1 in ?? ()
#7  0x004926da in ?? ()
#8  0x004928fa in btbulkdelete ()
...

---Vacuum settings in postgresql.conf---

# - Cost-Based Vacuum Delay -

vacuum_cost_delay = 10  # 0-100 milliseconds
#vacuum_cost_page_hit = 1   # 0-1 credits

Re: [GENERAL] Can't restart Postgres

[Joshua D. Drake]

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:16.04
Codename:   xenial



This is starting to sound like someone inadvertently executed an rm 
somewhere they shouldn't have (outside of just the original ssl file).


JD


--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Can't restart Postgres

[Joshua D. Drake]

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   68128 Nov 16 06:53 clusterdb
  -rwxr-xr-x 1 root root   68192 Nov 16 06:53 createdb
  -rwxr-xr-x 1 root root   63920 Nov 16 06:53 createlang
  -rwxr-xr-x 1 root root   72672 Nov 16 06:53 createuser
  -rwxr-xr-x 1 root root   63936 Nov 16 06:53 dropdb
  -rwxr-xr-x 1 root root   63920 Nov 16 06:53 droplang
  -rwxr-xr-x 1 root root   63904 Nov 16 06:53 dropuser
  -rwxr-xr-x 1 root root   68416 Nov 16 06:53 pg_basebackup
  -rwxr-xr-x 1 root root  351904 Nov 16 06:53 pg_dump
  -rwxr-xr-x 1 root root 2186504 Nov 16 06:53 pg_dumpall
  -rwxr-xr-x 1 root root   30992 Nov 16 06:53 pg_isready
  -rwxr-xr-x 1 root root   47600 Nov 16 06:53 pg_receivexlog
  -rwxr-xr-x 1 root root   51928 Nov 16 06:53 pg_recvlogical
  -rwxr-xr-x 1 root root  154944 Nov 16 06:53 pg_restore
  -rwxr-xr-x 1 root root  515320 Nov 16 06:53 psql
  -rwxr-xr-x 1 root root   68160 Nov 16 06:53 reindexdb
  -rwxr-xr-x 1 root root   72384 Nov 16 06:53 vacuumdb

As I mentioned, this Debian package removes pg_ctl from the bin directory and 
instead attempts to wrap the pg_ctl functionality in a perl script so that the 
PG process is integrated with systemd.  I really wish they hadn’t, and it’s 
part of the reason I’m where I’m at.


Ugh o.k.. I run Ubuntu so I don't have that problem.

The thread is a little confusing about what has been tried and what 
hasn't but:


1. Disable ssl, try and start again
2. Try as postgres:

pg_ctlcluster 9.4 stop; pg_ctlcluster 9.4 start

3. Tom has a great suggestion if you are comfortable with those actions.

4. Is that directory listing the total of what is in 9.4/bin? If so... I 
don't see a postgres binary?


JD



--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Can't restart Postgres

[Adrian Klaver]

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 Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Get tables ending with numbers

[Sathesh S]

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 numbers



Hello Sathesh

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Sathesh S
> Sent: Mittwoch, 15. Februar 2017 07:17
> To: pgsql-general 
> Subject: [GENERAL] Get tables ending with numbers
>
> Hi All,
>
> Im trying to get tables ending with numbers (last 8 characters should be 
> numbers).
>
> For example: I have the tables "test_20160215" and "test_20160131" and 
> "test_1". When i run the below sql im not
> getting any output.
>
> Select relname from pg_class where relname like '%[0-9]'

You should use an operator for regexp:

CREATE TABLE test_20160215 (id integer);
CREATE TABLE

SELECT relname FROM pg_class WHERE relname ~ '[0-9]';
relname
---
[snip]
 test_20160215
[snip]

Or

SELECT relname FROM pg_class WHERE relname ~ 'test_[0-9]+$';
relname
---
 test_20160215
(1 row)

Regards
Charles

> Can someone please give some idea on this.
>
> Thanks,
> Sathesh
>

Re: [GENERAL] Can't restart Postgres

[Adrian Klaver]

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   68128 Nov 16 06:53 clusterdb
  -rwxr-xr-x 1 root root   68192 Nov 16 06:53 createdb
  -rwxr-xr-x 1 root root   63920 Nov 16 06:53 createlang
  -rwxr-xr-x 1 root root   72672 Nov 16 06:53 createuser
  -rwxr-xr-x 1 root root   63936 Nov 16 06:53 dropdb
  -rwxr-xr-x 1 root root   63920 Nov 16 06:53 droplang
  -rwxr-xr-x 1 root root   63904 Nov 16 06:53 dropuser
  -rwxr-xr-x 1 root root   68416 Nov 16 06:53 pg_basebackup
  -rwxr-xr-x 1 root root  351904 Nov 16 06:53 pg_dump
  -rwxr-xr-x 1 root root 2186504 Nov 16 06:53 pg_dumpall
  -rwxr-xr-x 1 root root   30992 Nov 16 06:53 pg_isready
  -rwxr-xr-x 1 root root   47600 Nov 16 06:53 pg_receivexlog
  -rwxr-xr-x 1 root root   51928 Nov 16 06:53 pg_recvlogical
  -rwxr-xr-x 1 root root  154944 Nov 16 06:53 pg_restore
  -rwxr-xr-x 1 root root  515320 Nov 16 06:53 psql
  -rwxr-xr-x 1 root root   68160 Nov 16 06:53 reindexdb
  -rwxr-xr-x 1 root root   72384 Nov 16 06:53 vacuumdb

As I mentioned, this Debian package removes pg_ctl from the bin directory and 
instead attempts to wrap the pg_ctl functionality in a perl script so that the 
PG process is integrated with systemd.  I really wish they hadn’t, and it’s 
part of the reason I’m where I’m at.


Ubuntu uses the same setup and I see it, see below.

The Perl script just redirects commands to the correct version of 
Postgres and uses that versions binaries.



aklaver@arkansas:~$ uname -a
Linux arkansas 4.8.6-x86_64-linode78 #1 SMP Tue Nov 1 14:51:21 EDT 2016 
x86_64 x86_64 x86_64 GNU/Linux

aklaver@arkansas:~$ l /usr/lib/postgresql/9.4/bin/
total 8388
drwxr-xr-x 2 root root4096 Feb  9 07:24 ./
drwxr-xr-x 4 root root4096 Sep 29  2015 ../
-rwxr-xr-x 1 root root   68096 Feb  8 07:04 clusterdb*
-rwxr-xr-x 1 root root   68160 Feb  8 07:04 createdb*
-rwxr-xr-x 1 root root   63888 Feb  8 07:04 createlang*
-rwxr-xr-x 1 root root   72640 Feb  8 07:04 createuser*
-rwxr-xr-x 1 root root   63904 Feb  8 07:04 dropdb*
-rwxr-xr-x 1 root root   63888 Feb  8 07:04 droplang*
-rwxr-xr-x 1 root root   63872 Feb  8 07:04 dropuser*
-rwxr-xr-x 1 root root  114296 Feb  8 07:04 initdb*
-rwxr-xr-x 1 root root   26624 Feb  8 07:04 oid2name*
-rwxr-xr-x 1 root root   18432 Feb  8 07:04 pg_archivecleanup*
-rwxr-xr-x 1 root root   68416 Feb  8 07:04 pg_basebackup*
-rwxr-xr-x 1 root root   64600 Feb  8 07:04 pgbench*
-rwxr-xr-x 1 root root   30792 Feb  8 07:04 pg_config*
-rwxr-xr-x 1 root root   30720 Feb  8 07:04 pg_controldata*
-rwxr-xr-x 1 root root   43320 Feb  8 07:04 pg_ctl*
-rwxr-xr-x 1 root root  355968 Feb  8 07:04 pg_dump*
-rwxr-xr-x 1 root root   89320 Feb  8 07:04 pg_dumpall*
-rwxr-xr-x 1 root root   30960 Feb  8 07:04 pg_isready*
-rwxr-xr-x 1 root root   47568 Feb  8 07:04 pg_receivexlog*
-rwxr-xr-x 1 root root   51928 Feb  8 07:04 pg_recvlogical*
-rwxr-xr-x 1 root root   38920 Feb  8 07:04 pg_resetxlog*
-rwxr-xr-x 1 root root  154912 Feb  8 07:04 pg_restore*
-rwxr-xr-x 1 root root   22536 Feb  8 07:04 pg_standby*
-rwxr-xr-x 1 root root   22648 Feb  8 07:04 pg_test_fsync*
-rwxr-xr-x 1 root root   14416 Feb  8 07:04 pg_test_timing*
-rwxr-xr-x 1 root root  113168 Feb  8 07:04 pg_upgrade*
-rwxr-xr-x 1 root root   51672 Feb  8 07:04 pg_xlogdump*
-rwxr-xr-x 1 root root 5993920 Feb  8 07:04 postgres*
lrwxrwxrwx 1 root root   8 Feb  8 07:04 postmaster -> postgres*
-rwxr-xr-x 1 root root  519384 Feb  8 07:04 psql*
-rwxr-xr-x 1 root root   68128 Feb  8 07:04 reindexdb*
-rwxr-xr-x 1 root root   72352 Feb  8 07:04 vacuumdb*
-rwxr-xr-x 1 root root   22528 Feb  8 07:04 vacuumlo*



-Shawn




On Feb 15, 2017, at 8:49 AM, Joshua D. Drake  wrote:

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 deleted and 
cannot be recovered so I need to recover the data from the 
/var/lib/postgresql/9.4/main directory.  I believe this is called a file level 
recovery.  I assume I need to use a fully functional, same version PG (on 
another machine?) to create a full dump of the data directory.  Once I have 
this I can re-install Postgres on the initial server and read the databases 
back into it.

Any advice on how to best go about this?  The official documentation seems a 
bit thin:

https://www.postgresql.org/docs/9.4/static/backup-file.html

I’ve only worked with normal (pg_dump, pg_dumpall) backups in the past.


Shawn

As the postgres user:

/usr/lib/postgresql/9.4/bin/pg_ctl -D /var/lib/postgresql/9.4/main start

What returns?

Sincerely,

JD


--
Command Prompt, Inc.  

Re: [GENERAL] Can't restart Postgres

[Steve Atkins]

> 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 running 
> under the postgres user.  I get the same output if run with sudo:
> 
> sudo systemctl status postgresql@9.4-main.service -l
>Error: could not exec   start -D /var/lib/postgresql/9.4/main -l 
> /var/log/postgresql/postgresql-9.4-main.log -s -o  -c 
> config_file="/etc/postgresql/9.4/main/postgresql.conf”

There's a suspicious hole between "exec" and "start" where I'd expect to see 
the full path to the pg_ctl binary. As though a variable were unset in a script 
or config file.

Cheers,
  Steve



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Can't restart Postgres

[Tom Lane]

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 
> /var/lib/postgresql/9.4/main directory.  I believe this is called a file 
> level recovery.  I assume I need to use a fully functional, same version PG 
> (on another machine?) to create a full dump of the data directory.  Once I 
> have this I can re-install Postgres on the initial server and read the 
> databases back into it. 

Seems like the hard way.  Rename the data directory out of the way, delete
and reinstall the Postgres packages, and once you've confirmed it works
again, rename the data directory back into place (while the server is
stopped!)

If renaming the old data directory back into place makes it start failing
again, then you've narrowed down the problem to something about the
permissions or contents of the data directory itself or the configuration
files therein.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Can't restart Postgres

[Magnus Hagander]

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 root4096 Feb  9 16:17 ..
>   -rwxr-xr-x 1 root root   68128 Nov 16 06:53 clusterdb
>   -rwxr-xr-x 1 root root   68192 Nov 16 06:53 createdb
>   -rwxr-xr-x 1 root root   63920 Nov 16 06:53 createlang
>   -rwxr-xr-x 1 root root   72672 Nov 16 06:53 createuser
>   -rwxr-xr-x 1 root root   63936 Nov 16 06:53 dropdb
>   -rwxr-xr-x 1 root root   63920 Nov 16 06:53 droplang
>   -rwxr-xr-x 1 root root   63904 Nov 16 06:53 dropuser
>   -rwxr-xr-x 1 root root   68416 Nov 16 06:53 pg_basebackup
>   -rwxr-xr-x 1 root root  351904 Nov 16 06:53 pg_dump
>   -rwxr-xr-x 1 root root 2186504 Nov 16 06:53 pg_dumpall
>   -rwxr-xr-x 1 root root   30992 Nov 16 06:53 pg_isready
>   -rwxr-xr-x 1 root root   47600 Nov 16 06:53 pg_receivexlog
>   -rwxr-xr-x 1 root root   51928 Nov 16 06:53 pg_recvlogical
>   -rwxr-xr-x 1 root root  154944 Nov 16 06:53 pg_restore
>   -rwxr-xr-x 1 root root  515320 Nov 16 06:53 psql
>   -rwxr-xr-x 1 root root   68160 Nov 16 06:53 reindexdb
>   -rwxr-xr-x 1 root root   72384 Nov 16 06:53 vacuumdb
>
> As I mentioned, this Debian package removes pg_ctl from the bin directory
> and instead attempts to wrap the pg_ctl functionality in a perl script so
> that the PG process is integrated with systemd.  I really wish they hadn’t,
> and it’s part of the reason I’m where I’m at.
>

pg_ctl is normally present in /usr/lib/postgresql//bin on a debian
system. If that is gone, somebody removed it, or you didn't install the
"postgresql-9.4" package which provides it. On a 9.4 system:

$ dpkg -S /usr/lib/postgresql/9.4/bin/pg_ctl
postgresql-9.4: /usr/lib/postgresql/9.4/bin/pg_ctl

You could try reinstalling the postgresql-9.4 package and see if it comes
back. The rest of the binaries in that directory seems to be from
postgresql-9.4-client though -- have you actually by mistake uninstalled
the server package completely?

As in, that directory is supposed to have the "postgres" binary which is
the database server and it's not there. So there is no wonder it's not
starting...

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Can't restart Postgres

[Shawn Thomas]

/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 root root   68192 Nov 16 06:53 createdb
  -rwxr-xr-x 1 root root   63920 Nov 16 06:53 createlang
  -rwxr-xr-x 1 root root   72672 Nov 16 06:53 createuser
  -rwxr-xr-x 1 root root   63936 Nov 16 06:53 dropdb
  -rwxr-xr-x 1 root root   63920 Nov 16 06:53 droplang
  -rwxr-xr-x 1 root root   63904 Nov 16 06:53 dropuser
  -rwxr-xr-x 1 root root   68416 Nov 16 06:53 pg_basebackup
  -rwxr-xr-x 1 root root  351904 Nov 16 06:53 pg_dump
  -rwxr-xr-x 1 root root 2186504 Nov 16 06:53 pg_dumpall
  -rwxr-xr-x 1 root root   30992 Nov 16 06:53 pg_isready
  -rwxr-xr-x 1 root root   47600 Nov 16 06:53 pg_receivexlog
  -rwxr-xr-x 1 root root   51928 Nov 16 06:53 pg_recvlogical
  -rwxr-xr-x 1 root root  154944 Nov 16 06:53 pg_restore
  -rwxr-xr-x 1 root root  515320 Nov 16 06:53 psql
  -rwxr-xr-x 1 root root   68160 Nov 16 06:53 reindexdb
  -rwxr-xr-x 1 root root   72384 Nov 16 06:53 vacuumdb

As I mentioned, this Debian package removes pg_ctl from the bin directory and 
instead attempts to wrap the pg_ctl functionality in a perl script so that the 
PG process is integrated with systemd.  I really wish they hadn’t, and it’s 
part of the reason I’m where I’m at.

-Shawn



> On Feb 15, 2017, at 8:49 AM, Joshua D. Drake  wrote:
> 
> 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 deleted and 
>> cannot be recovered so I need to recover the data from the 
>> /var/lib/postgresql/9.4/main directory.  I believe this is called a file 
>> level recovery.  I assume I need to use a fully functional, same version PG 
>> (on another machine?) to create a full dump of the data directory.  Once I 
>> have this I can re-install Postgres on the initial server and read the 
>> databases back into it.
>> 
>> Any advice on how to best go about this?  The official documentation seems a 
>> bit thin:
>> 
>> https://www.postgresql.org/docs/9.4/static/backup-file.html
>> 
>> I’ve only worked with normal (pg_dump, pg_dumpall) backups in the past.
> 
> Shawn
> 
> As the postgres user:
> 
> /usr/lib/postgresql/9.4/bin/pg_ctl -D /var/lib/postgresql/9.4/main start
> 
> What returns?
> 
> Sincerely,
> 
> JD
> 
> 
> -- 
> Command Prompt, Inc.  http://the.postgres.company/
>+1-503-667-4564
> PostgreSQL Centered full stack support, consulting and development.
> Everyone appreciates your honesty, until you are honest with them.
> Unless otherwise stated, opinions are my own.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Can't restart Postgres

[Adrian Klaver]

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 deleted and 
cannot be recovered so I need to recover the data from the 
/var/lib/postgresql/9.4/main directory.  I believe this is called a file level 
recovery.  I assume I need to use a fully functional, same version PG (on 
another machine?) to create a full dump of the data directory.  Once I have 
this I can re-install Postgres on the initial server and read the databases 
back into it.


I have to believe that if you cannot get the server to start then the 
data directory is no shape to recover from. And if the data directory is 
good and it is the program files that are corrupted then it would be a 
matter of reinstalling Postgres. In either case the most important thing 
to do would be to make a copy of the data directory before you do 
anything else.


What exactly happened that caused the ssl cert and the pg_dumpall file 
to deleted?


In other words what else got deleted?



Any advice on how to best go about this?  The official documentation seems a 
bit thin:

https://www.postgresql.org/docs/9.4/static/backup-file.html

I’ve only worked with normal (pg_dump, pg_dumpall) backups in the past.

-Shawn


On Feb 15, 2017, at 6:35 AM, Adrian Klaver  wrote:

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 same output if run with sudo:

sudo systemctl status postgresql@9.4-main.service
 -l
  Error: could not exec   start -D /var/lib/postgresql/9.4/main -l
/var/log/postgresql/postgresql-9.4-main.log -s -o  -c
config_file="/etc/postgresql/9.4/main/postgresql.conf”




So you are talking about:

/etc/init.d/postgresql

which then calls:

/usr/share/postgresql-common/init.d-functions

Or is there another setup on your system?

Any relevant information in the system logs?


Thanks, though.

-Shawn



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





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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Can't restart Postgres

[Joshua D. Drake]

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 deleted and 
cannot be recovered so I need to recover the data from the 
/var/lib/postgresql/9.4/main directory.  I believe this is called a file level 
recovery.  I assume I need to use a fully functional, same version PG (on 
another machine?) to create a full dump of the data directory.  Once I have 
this I can re-install Postgres on the initial server and read the databases 
back into it.

Any advice on how to best go about this?  The official documentation seems a 
bit thin:

https://www.postgresql.org/docs/9.4/static/backup-file.html

I’ve only worked with normal (pg_dump, pg_dumpall) backups in the past.


Shawn

As the postgres user:

/usr/lib/postgresql/9.4/bin/pg_ctl -D /var/lib/postgresql/9.4/main start

What returns?

Sincerely,

JD


--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Can't restart Postgres

[Shawn Thomas]

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 to recover the data from the 
/var/lib/postgresql/9.4/main directory.  I believe this is called a file level 
recovery.  I assume I need to use a fully functional, same version PG (on 
another machine?) to create a full dump of the data directory.  Once I have 
this I can re-install Postgres on the initial server and read the databases 
back into it. 

Any advice on how to best go about this?  The official documentation seems a 
bit thin:

https://www.postgresql.org/docs/9.4/static/backup-file.html

I’ve only worked with normal (pg_dump, pg_dumpall) backups in the past.

-Shawn

> On Feb 15, 2017, at 6:35 AM, Adrian Klaver  wrote:
> 
> 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 same output if run with sudo:
>> 
>> sudo systemctl status postgresql@9.4-main.service
>>  -l
>>   Error: could not exec   start -D /var/lib/postgresql/9.4/main -l
>> /var/log/postgresql/postgresql-9.4-main.log -s -o  -c
>> config_file="/etc/postgresql/9.4/main/postgresql.conf”
>> 
> 
> 
> So you are talking about:
> 
> /etc/init.d/postgresql
> 
> which then calls:
> 
> /usr/share/postgresql-common/init.d-functions
> 
> Or is there another setup on your system?
> 
> Any relevant information in the system logs?
> 
>> Thanks, though.
>> 
>> -Shawn
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Foreign Data Wrapper for filesystem

[Leonardo M . Ramé]

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 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 this
purpose, even if one existed.


​It cannot be used for an arbitrary directory:
" Only files within the database cluster directory and the 
log_directory can be accessed. "​

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


regards, tom lane


​Personally, I don't know what use such a function would be. It would 
be executed on the _SERVER_, not the client. And that is probably why 
the pg_ls_dir() is restricted. If it weren't it could be a security 
(or privacy) violation.​


--
"Irrigation of the land with seawater desalinated by fusion power is 
ancient. It's called 'rain'." -- Michael McClary, in alt.fusion


Maranatha! <><
John McKown


Well, I need to check the existence of a file from a query/procedure. 
After posting I remembered I could do this with plperlu, I'm installing 
it right now.


Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877

Re: [GENERAL] Foreign Data Wrapper for filesystem

[John McKown]

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
> > exists?.
>
> Why not use pg_ls_dir()?  An FDW would be mighty awkward to use for this
> purpose, even if one existed.
>

​It cannot be used for an arbitrary directory:
" Only files within the database cluster directory and the log_directory can
be accessed. "​
​ref: https://www.postgresql.org/docs/current/static/functions-admin.html​


>
> regards, tom lane
>

​Personally, I don't know what use such a function would be. It would be
executed on the _SERVER_, not the client. And that is probably why the
pg_ls_dir() is restricted. If it weren't it could be a security (or
privacy) violation.​

-- 
"Irrigation of the land with seawater desalinated by fusion power is
ancient. It's called 'rain'." -- Michael McClary, in alt.fusion

Maranatha! <><
John McKown

Re: [GENERAL] Foreign Data Wrapper for filesystem

[Tom Lane]

"=?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 this
purpose, even if one existed.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using ctid in delete statement

[Tom Lane]

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 '10 day')::int + 6 limit 100));
>> 
>> Could I be sure that ctid will not change during the execution or will not
>> do any harm to other transactions?

> It will be safe for two reasons: 1) your statement is running in its own
> implicit transaction, and 2) the rows selected from the subquery are
> visible to your transaction and thus will not have been "cleaned up" for
> re-use by any other transaction.

I think it would be a lot safer with the inner SELECT changed to SELECT
FOR UPDATE.  As you say, the ctid seen by a plain SELECT couldn't get
recycled for use by a new tuple while the transaction is still alive,
but as-is there's certainly a hazard that the row is updated by another
transaction.  Then the ctid would point to an already-dead tuple so the
DELETE wouldn't do anything, which is unlikely to be the desired result.
With SELECT FOR UPDATE, you'd have a tuple lock preventing such race
conditions.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using ctid in delete statement

[pinker]

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 changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using ctid in delete statement

[Vick Khera]

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));
>
> Could I be sure that ctid will not change during the execution or will not
> do any harm to other transactions?
>

It will be safe for two reasons: 1) your statement is running in its own
implicit transaction, and 2) the rows selected from the subquery are
visible to your transaction and thus will not have been "cleaned up" for
re-use by any other transaction. So at worst you will try to delete the
same object twice, which in this case is no harm, no foul. That ctid will
not be able to point to some "other" object until your transaction is
completed and the old rows are vacuumed.

[GENERAL] Foreign Data Wrapper for filesystem

[Leonardo M . Ramé]

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 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Using ctid in delete statement

[pinker]

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 will not change during the execution or will not
do any harm to other transactions?


regards



--
View this message in context: 
http://postgresql.nabble.com/Using-ctid-in-delete-statement-tp5944434.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Adrian Klaver]

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 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 that this is not
available. Or am I missing something?


All I can think of is to use:

RETURNING pk

and see if that changed or not.


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)?



For my particular case, I have this table
create table t (
   a_from text,
   a_to text,
   created timestamptz,
   updated timestamptz,
   primary key (a_from, a_to)
);


Well, if I do:

insert into t (a_from, a_+to)

and will use some values that do not exist in table, then insert
happens, but not sure what do you mean about "primary key change" in
this case.

On the other hand, if the from/to already exists in the table, then
update happens (on "updated" column) - and then there is definitely no
pkey change.


Yeah I see(thanks to Karsten also). So:

CREATE TABLE upsert_test (fld_1 varchar,
fld_2 varchar,
PRIMARY KEY (fld_1,-
fld_2));

INSERT INTO upsert_test (fld_1,
fld_2)
VALUES ('test1', 'test3')
ON CONFLICT (fld_1,
fld_2)
DO
UPDATE
SET fld_1 = EXCLUDED.fld_1,
fld_2 = EXCLUDED.fld_2
RETURNING
fld_1,
fld_2;

 fld_1 | fld_2
---+---
 test1 | test3
(1 row)

INSERT 0 1

INSERT INTO upsert_test (fld_1,
fld_2)
VALUES ('test4', 'test5')
ON CONFLICT (fld_1,
fld_2)
DO
UPDATE
SET fld_1 = EXCLUDED.fld_1,
fld_2 = EXCLUDED.fld_2
RETURNING
fld_1,
fld_2;

 fld_1 | fld_2
---+---
 test4 | test5


Can see the differentiation issue now. Can't see a solution right now 
other then the one you already have, a marker field that you can use to 
determine INSERT/UPDATE.





Best regards,

depesz





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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[hubert depesz lubaczewski]

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
(somewhat) code.

Best regards,

depesz



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Karsten Hilbert]

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 already exists or else is created. But the
(then-returned) _value_ of either is the same.

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Albe Laurenz]

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 not
> available. Or am I missing something?

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.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[hubert depesz lubaczewski]

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 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?
> >>
> >>All I can think of is to use:
> >>
> >>RETURNING pk
> >>
> >>and see if that changed or not.
> >
> >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)?
> 
> >
> >For my particular case, I have this table
> >create table t (
> >a_from text,
> >a_to text,
> >created timestamptz,
> >updated timestamptz,
> >primary key (a_from, a_to)
> >);

Well, if I do:

insert into t (a_from, a_to)

and will use some values that do not exist in table, then insert
happens, but not sure what do you mean about "primary key change" in
this case.

On the other hand, if the from/to already exists in the table, then
update happens (on "updated" column) - and then there is definitely no
pkey change.

Best regards,

depesz



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Adrian Klaver]

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

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?


All I can think of is to use:

RETURNING pk

and see if that changed or not.


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)?




For my particular case, I have this table
create table t (
a_from text,
a_to text,
created timestamptz,
updated timestamptz,
primary key (a_from, a_to)
);

where created and updated are set (and kept correct) with triggers. And
in my insert/update, if the row exists, I just set updated to now().


This I understand, though it does not square with the above.



So, for my particular case, I can, and do, compare if created is the
same as updated, and if no - it was update, otherwise - insert.

But it would be really good to get some proper support for
differentiating flow of such queries...

depesz





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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Can't restart Postgres

[Adrian Klaver]

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 same output if run with sudo:

sudo systemctl status postgresql@9.4-main.service
 -l
   Error: could not exec   start -D /var/lib/postgresql/9.4/main -l
/var/log/postgresql/postgresql-9.4-main.log -s -o  -c
config_file="/etc/postgresql/9.4/main/postgresql.conf”




So you are talking about:

/etc/init.d/postgresql

which then calls:

/usr/share/postgresql-common/init.d-functions

Or is there another setup on your system?

Any relevant information in the system logs?


Thanks, though.

-Shawn



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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[hubert depesz lubaczewski]

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 return information whether the row
> >was modified, or created - and currently it seems that this is not
> >available. Or am I missing something?
> 
> All I can think of is to use:
> 
> RETURNING pk
> 
> and see if that changed or not.

Well, this wouldn't work for me as pkey will not change.

For my particular case, I have this table
create table t (
a_from text,
a_to text,
created timestamptz,
updated timestamptz,
primary key (a_from, a_to)
);

where created and updated are set (and kept correct) with triggers. And
in my insert/update, if the row exists, I just set updated to now().

So, for my particular case, I can, and do, compare if created is the
same as updated, and if no - it was update, otherwise - insert.

But it would be really good to get some proper support for
differentiating flow of such queries...

depesz



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Adrian Klaver]

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 that this is not
available. Or am I missing something?


All I can think of is to use:

RETURNING pk

and see if that changed or not.



Best regards,

depesz






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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[hubert depesz lubaczewski]

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?

Best regards,

depesz



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Karsten Hilbert]

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 of swap.
> 
> By any chance:
> 
> - when it happens has the kernel considered using swap ?
> 
> - which kernel are you running ?
> 
> There's been (for some workloads) massive problems with RAM
> exhaustion / swapping / OOM killer going wild with
> 4.7/4.8/some 4.9 kernels.

I guess what I'm trying to say is that it may actually not be
PostgreSQL's fault but rather the kernel invoking the OOM
killer way prematurely.

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Karsten Hilbert]

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.

By any chance:

- when it happens has the kernel considered using swap ?

- which kernel are you running ?

There's been (for some workloads) massive problems with RAM
exhaustion / swapping / OOM killer going wild with
4.7/4.8/some 4.9 kernels.

Just a shot in the dark,
Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general