RE: [EXTERNAL]Re: pg_dump -Fd -j2 on standby in 9.6.6

[Stefan Petrea]

Hi Andreas,

Thank you for your suggestion about pausing/resuming the standby!

I've tried running on the standby
SELECT pg_xlog_replay_pause()
Then running the dump, and it threw the same error I had seen before.
Seeing that I wasn't able to do it, I just ran 
SELECT pg_xlog_replay_resume()
On the slave.

I've asked in the IRC channel as well and I've been told that in version 10
this would work on standbys. I've looked at the code [3]
and indeed it does not have the check for standby anymore (as it did in the 
previous
gdb code listing in my initial email).

But.. upgrading to 10 would be quite a heavy thing for now, since it would 
involve many changes 
so I don't believe that would be feasible right now.

[3] 
https://github.com/postgres/postgres/blob/REL_10_STABLE/src/bin/pg_dump/pg_dump.c#L1149

Stefan Petrea
System Engineer/DBA, Network Engineering


stefan.pet...@tangoe.com 

     

tangoe.com



This e-mail message, including any attachments, is for the sole use of the 
intended recipient of this message, and may contain information that is 
confidential or legally protected. If you are not the intended recipient or 
have received this message in error, you are not authorized to copy, 
distribute, or otherwise use this message or its attachments. Please notify the 
sender immediately by return e-mail and permanently delete this message and any 
attachments. Tangoe makes no warranty that this e-mail or its attachments are 
error or virus free.

-Original Message-
From: Andreas Kretschmer [mailto:andr...@a-kretschmer.de] 
Sent: Wednesday, March 28, 2018 8:26 AM
To: pgsql-general@lists.postgresql.org; Stefan Petrea 
; pgsql-gene...@postgresql.org
Subject: [EXTERNAL]Re: pg_dump -Fd -j2 on standby in 9.6.6

WARNING: The sender of this email could not be validated and may not match the 
person in the "From" field. This is potentially a spam/phishing email! Contact 
IT if you need assistance.


On 28 March 2018 07:02:30 CEST, Stefan Petrea  wrote:
>Hi,
>
>
>I wonder why synchronized snapshots are not supported on standby 
>servers.
>
>
>
If you want to take backups using -Fd on a standby you should pause the 
reply-process. In this case you don' t need synchronized snapshots.

Regards, Andreas.


--
2ndQuadrant - The PostgreSQL Support Company

Re: [EXTERNAL]Re: pg_dump -Fd -j2 on standby in 9.6.6

[Andreas Kretschmer]

Am 28.03.2018 um 09:46 schrieb Stefan Petrea:

Hi Andreas,

Thank you for your suggestion about pausing/resuming the standby!

I've tried running on the standby
SELECT pg_xlog_replay_pause()
Then running the dump, and it threw the same error I had seen before.


run pg_dump with --no-synchronized-snapshots, it is save in this szenario.

> This e-mail message, including any attachments,

this is a public mailing list ...


Regards, Andreas
--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: Postgresql 10.3 , query never completes if LIMIT clause is specified and paralle processing is on

[Alessandro Aste]

Hello,  any news ?

Thank you,

Alessandro.

On Fri, Mar 23, 2018 at 8:22 PM, Alessandro Aste 
wrote:

> PS , in the meanwhile I discovered a 2nd workaround(beside disabling
> parallel processing) . I added offset  0 to the subquery , and, according
> to the documentation, “OFFSET 0 is the same as omitting the OFFSET clause”
> - https://www.postgresql.org/docs/current/static/queries-limit.html
>
> cmd3dev=# show max_parallel_workers_per_gather ;
>
> *max_parallel_workers_per_gather*
>
> *-*
>
> *8*
>
> (1 row)
>
>
>
> cmd3dev=# \timing
>
> Timing is on.
>
> cmd3dev=#  SELECT * FROM (SELECT seg.circuit_id AS id,
> vendor_gtt_pop.vendor_id, CASE WHEN vendor_gtt_pop.push_to_gaa = 1 THEN 'Y'
> END as gaa, pop.gii_circuitid AS pop_name, cst.label AS pop_status,
> seg.a_company_name AS pop_location, seg.vendor_id AS pop_provider_id,
> seg.vendor_name AS pop_provider, cs.address1 AS pop_street, cs.city AS
> pop_city, cs.postal_code AS pop_postal_code, cs.state AS pop_state,
> csc.code AS pop_country, cs.country_id AS pop_country_id FROM ( SELECT
> c.gii_circuitid, max(so.id) AS service_order_id FROM service_order so
> join circuit c on c.product_id=so.product_id join master_service_order mso
> on mso.id=so.master_service_order_id WHERE NOT (so.ordertype_id = 2 AND
> so.status <> 999) AND NOT (so.ordertype_id = 3 AND so.status <> 999) AND
> c.status >= 20 AND c.status not in (160,999) AND mso.client_id=11615 AND
> c.service_description=28 AND c.status!=160 GROUP BY c.gii_circuitid ) pop
> JOIN service_order so ON so.id = pop.service_order_id left JOIN
> client_site cs on cs.id=so.a_site_id left JOIN country csc on 
> csc.id=cs.country_id
> JOIN circuit c ON so.product_id=c.product_id JOIN circuit_status cst ON
> cst.id=c.status JOIN ( SELECT c.id AS circuit_id, sg.id AS segment_id,
> c.pop_support_vendor_id AS vendor_id, v.name AS vendor_name,
> sg.a_company_name FROM segment sg JOIN circuit_layout cl ON cl.segment_id =
> sg.id AND cl.ordinal = 1 JOIN circuit c ON c.id = cl.circuit_id JOIN
> vendor v ON v.id = c.pop_support_vendor_id ) seg ON seg.circuit_id = c.id
> JOIN vendor_gtt_pop on vendor_gtt_pop.gtt_pop_id = seg.circuit_id offset 0)
> foo where vendor_id = 12346 AND (1=1) ORDER BY pop_name ASC,id desc LIMIT
> 10;
>
>id   | vendor_id | gaa |   pop_name| pop_status | pop_location |
> pop_provider_id | pop_provider | pop_street  | pop_city | pop
>
> _postal_code | pop_state | pop_country | pop_country_id
>
> +---+-+---++
> --+-+--+
> -+--+
>
> -+---+-+
>
> 684807 | 12346 | Y   | GTT/POP/LON1T | Active | LON1T
> |   12288 | Telehouse UK | 14 Coriander Avenue | London   | E14
>
> 2AA |   | GB  |219
>
> (1 row)
>
>
>
> *Time: 2245.073 ms (00:02.245)*
>
>
>
> On Fri, Mar 23, 2018 at 9:31 AM, Alessandro Aste <
> alessandro.a...@gmail.com> wrote:
>
>> Tomas, I'm attaching a 4MB file with the perf report. Let me know if it
>> gets blocked, I'll shrink it to the first 1000 lines.
>>
>> Thank you,
>>
>> Alessandro.
>>
>> On Thu, Mar 22, 2018 at 11:42 PM, Tomas Vondra <
>> tomas.von...@2ndquadrant.com> wrote:
>>
>>> On 03/22/2018 11:29 PM, Alessandro Aste wrote:
>>> > Thanks Tomas. We're currently building postgres from source. In order
>>> to
>>> > enable symbols, you want me to re-configure postres with
>>> --enable-debug
>>> > then run perf?
>>> >
>>>
>>> Yes.
>>>
>>> regards
>>>
>>> --
>>> Tomas Vondra  http://www.2ndQuadrant.com
>>> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>>>
>>
>>
>

Fixed chars

[Enrico Pirozzi]

Hi , I've seen this strange thing.

sitedb=# create table test_tb(codice char(7));
CREATE TABLE
sitedb=# insert into test_tb values('pippo');
INSERT 0 1
sitedb=# select codice || 'a'::char(1),length(codice) from test_tb
 ?column? | length
--+
 pippoa   |  5
(1 row)


On the official documentation

|character(/|n|/)|,|char(/|n|/)| fixed-length, blank padded

https://www.postgresql.org/docs/10/static/datatype-character.html


Can anyone help me?

Enrico

--
Enrico Pirozzi
NBS Group S.r.l.
via Val Tiberina 23/A
63074, San Benedetto del Tronto (AP)
Tel. 0735-7626201

Re: Fixed chars

[Andreas Kretschmer]

Am 28.03.2018 um 11:11 schrieb Enrico Pirozzi:

Hi , I've seen this strange thing.

sitedb=# create table test_tb(codice char(7));
CREATE TABLE
sitedb=# insert into test_tb values('pippo');
INSERT 0 1
sitedb=# select codice || 'a'::char(1),length(codice) from test_tb
 ?column? | length
--+
 pippoa   |  5
(1 row)



where is the problem? length() returns the number of chars in string, 
and the string in codice is 5 chars long.





On the official documentation

|character(/|n|/)|,|char(/|n|/)| fixed-length, blank padded

https://www.postgresql.org/docs/10/static/datatype-character.html


test=*# select length('12345'::char(10)), pg_column_size('12345'::char(10));
 length | pg_column_size
+
  5 | 14
(1 Zeile)



helps that?


Regards, Andreas

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

Re: Fixed chars

[Enrico Pirozzi]

Resolved...my missing


Thanks

Enrico


https://www.postgresql.org/docs/10/static/datatype-character.html

"Values of type|character|are physically padded with spaces to the 
specified width/|n|/, and are stored and displayed that way. However, 
trailing spaces are treated as semantically insignificant and 
disregarded when comparing two values of type|character|. In collations 
where whitespace is significant, this behavior can produce unexpected 
results; for example|SELECT 'a '::CHAR(2) collate "C" < 
E'a\n'::CHAR(2)|returns true, even though|C|locale would consider a 
space to be greater than a newline. Trailing spaces are removed when 
converting a|character|value to one of the other string types. Note that 
trailing spaces/are/semantically significant in|character 
varying|and|text|values, and when using pattern matching, that 
is|LIKE|and regular expressions."



Il 28/03/2018 11:32, Andreas Kretschmer ha scritto:



Am 28.03.2018 um 11:11 schrieb Enrico Pirozzi:

Hi , I've seen this strange thing.

sitedb=# create table test_tb(codice char(7));
CREATE TABLE
sitedb=# insert into test_tb values('pippo');
INSERT 0 1
sitedb=# select codice || 'a'::char(1),length(codice) from test_tb
 ?column? | length
--+
 pippoa   |  5
(1 row)



where is the problem? length() returns the number of chars in string, 
and the string in codice is 5 chars long.





On the official documentation

|character(/|n|/)|,|char(/|n|/)| fixed-length, blank padded

https://www.postgresql.org/docs/10/static/datatype-character.html


test=*# select length('12345'::char(10)), 
pg_column_size('12345'::char(10));

 length | pg_column_size
+
  5 | 14
(1 Zeile)



helps that?


Regards, Andreas



--
Enrico Pirozzi
NBS Group S.r.l.
via Val Tiberina 23/A
63074, San Benedetto del Tronto (AP)
Tel. 0735-7626201

Re: Connection hangs on new created schema

[Juan Manuel Cuello]

On Wed, Mar 28, 2018 at 2:58 AM, Laurenz Albe 
wrote:

> Juan Manuel Cuello wrote:
> > I have a postgresql database with around 4000 schemas. Each schema has
> around
> > the same 65 tables. There are 8 processes connected to the database (it
> is a web app).
> > Each process has only one direct connection to the database (no
> connection pool).
> >
> > Everything works ok until a new schema (with all its tables, indices,
> etc)
> > is added to the database. When this happens, the next query made to the
> database
> > takes too long, sometimes more than a minute o two. Also I see a high DB
> CPU usage
> > during that lapse. Then everything continues working ok as usual and CPU
> drops to normal levels.
> >
> > I'm mostly sure this has to do with the amount of schemas, maybe related
> to relcache,
> > but not sure why creating new schemas causes all this and where to look
> to solve this problem.
>
> If you know the slow query EXPLAIN (ANALYZE, BUFFERS) it and see where the
> time
> is spent.
>


I doesn't seem to depend on the query. Immediately after the new schema is
created, the next query takes a lot of time. It eventually resolves and
next statements are executed ok.

I think it is related to something the db processes are doing when the new
schema is created (maybe reloading relcache?), because the db processes
consumes a lot of CPU during that lapse. After a while, everything goes
back to normality.

Re: Using Lateral

[Johann Spies]

Thanks Paul.

I was hesitant to post my full query.  It is a long and complicated
query.  But here it is now:

WITH p AS (
SELECT
ARRAY [ ARRAY [ 't',
'http://scientific.thomsonreuters.com/schema/wok5.4/public/FullRecord'
] ] AS ns),
uts AS (
SELECT
s.ut
FROM
wos_source.core_2015 s
WHERE
s.id BETWEEN 00
AND 10
 ),
utsb AS (
SELECT
b.ut
FROM
wos_2017_1.belongs2 b,
uts
WHERE
b.ut = uts.ut), q AS (
SELECT
s.ut,
unnest (xpath
('//t:static_data/t:fullrecord_metadata/t:addresses/t:address_name/t:address_spec',
xml,
ns)) AS address_spec
FROM
p,
uts a
LEFT JOIN utsb b ON b.ut = a.ut
LEFT JOIN wos_source.core_2015 s ON s.ut = a.ut
WHERE
b.ut IS NULL), r AS (
SELECT
s.ut,
unnest (xpath
('//t:static_data/t:item/t:reprint_contact/t:address_spec',
xml,
ns)) AS raddress_spec
FROM
p,
wos_2017_1.publication l,
uts a
LEFT JOIN utsb b ON b.ut = a.ut
LEFT JOIN wos_source.core_2015 s ON s.ut = a.ut
WHERE
b.ut IS NULL
AND xpath_exists
('//t:static_data/t:item/t:reprint_contact/t:address_spec', xml, ns)
AND s.ut = l.ut
AND l.pubyear < 1998), qd AS (
SELECT
DISTINCT ut,
CASE
WHEN xpath_exists ('//t:address_spec/t:country/text()',
q.address_spec,
p.ns)
THEN unnest (xpath ('//t:address_spec/t:country/text()',
q.address_spec,
p.ns))::citext
ELSE NULL
END country,
CASE
WHEN xpath_exists ('//t:address_spec/t:city/text()',
q.address_spec,
p.ns)
THEN unnest (xpath ('//t:address_spec/t:city/text()',
q.address_spec,
p.ns))::citext
ELSE NULL
END city,
CASE
WHEN xpath_exists ('//t:organizations/t:organization/text()',
q.address_spec,
ns)
THEN unnest (xpath ('//t:organizations/t:organization/text()',
q.address_spec,
ns))::citext
ELSE NULL
END organisation,
CASE
WHEN xpath_exists
('//t:organizations/t:organization[@pref="Y"]/text()',
q.address_spec,
ns)
THEN unnest (xpath
('//t:organizations/t:organization[@pref="Y"]/text()',
q.address_spec,
ns))::citext
ELSE NULL
END AS prefname,
CASE
WHEN xpath_exists ('//t:suborganizations/t:suborganization/text()',
q.address_spec,
ns)
THEN unnest (xpath ('//t:suborganizations/t:suborganization/text()',
q.address_spec,
ns))::citext
ELSE NULL
END suborgname,
CASE
WHEN xpath_exists ('/t:address_spec/@addr_no',
q.address_spec,
ns)
THEN (xpath ('/t:address_spec/@addr_no',
q.address_spec,
ns))
[ 1 ]::text::INTEGER
ELSE NULL
END addr_no
FROM
p,
q),
rd AS (
SELECT
DISTINCT ut,
CASE
WHEN xpath_exists ('//t:address_spec/t:country/text()',
r.raddress_spec,
p.ns)
THEN unnest (xpath ('//t:address_spec/t:country/text()',
r.raddress_spec,
p.ns))::citext
ELSE NULL
END country,
CASE
WHEN xpath_exists ('//t:address_spec/t:city/text()',
r.raddress_spec,
p.ns)
THEN unnest (xpath ('//t:address_spec/t:city/text()',
r.raddress_spec,
p.ns))::citext
ELSE NULL
END city,
CASE
WHEN xpath_exists ('//t:organizations/t:organization/text()',
r.raddress_spec,
ns)
THEN unnest (xpath ('//t:organizations/t:organization/text()',
r.raddress_spec,
ns))::citext
ELSE NULL
END organisation,
CASE
WHEN xpath_exists
('//t:organizations/t:organization[@pref="Y"]/text()',
r.raddress_spec,
ns)
THEN unnest (xpath
('//t:organizations/t:organization[@pref="Y"]/text()',
r.raddress_spec,
ns))::citext
ELSE NULL
END AS prefname,
CASE
WHEN xpath_exists ('//t:suborganizations/t:suborganization/text()',
r.raddress_spec,
ns)
THEN unnest (xpath ('//t:suborganizations/t:suborganization/text()',
r.raddress_spe

Re: Postgresql 10.3 , query never completes if LIMIT clause is specified and paralle processing is on

[Tomas Vondra]

Hi,

the perf profiles look really weird - clearly, there's a lot of lock
contention, because the top item is this

13.49%13.40%  postmaster   postgres  [.]   LWLockAcquire
 |

 ---LWLockAcquire



That's a sign of lock contention - not sure which one, though. And then
there's heap_hot_search_buffer

12.65% 7.92%  postmaster   postgres [.] heap_hot_search_buffer
 |

 ---heap_hot_search_buffer


So either there's a table with many HOT-updated rows (repeatedly), or we
search the HOT chains very frequently for some reason. Considering it
only affects the non-LIMIT case, I'd guess the latter.

There also seems to be quite a few page faults, for some reason.

Trying to reproduce this without the actual data is rather futile. We
need some sort of reproducer (sample data to test the query on).

regards

On 03/28/2018 10:30 AM, Alessandro Aste wrote:
> Hello,  any news ?
> 
> Thank you,
> 
> Alessandro.
> 
> On Fri, Mar 23, 2018 at 8:22 PM, Alessandro Aste
> mailto:alessandro.a...@gmail.com>> wrote:
> 
> PS , in the meanwhile I discovered a 2nd workaround(beside disabling
> parallel processing) . I added offset  0 to the subquery , and,
> according to the documentation, “OFFSET 0 is the same as omitting
> the OFFSET clause”
> - https://www.postgresql.org/docs/current/static/queries-limit.html
> 
> 
> cmd3dev=# show max_parallel_workers_per_gather ;
> 
> *max_parallel_workers_per_gather*
> 
> *-*
> 
> *8*
> 
> (1 row)
> 
>  
> 
> cmd3dev=# \timing
> 
> Timing is on.
> 
> cmd3dev=#  SELECT * FROM (SELECT seg.circuit_id AS id,
> vendor_gtt_pop.vendor_id, CASE WHEN vendor_gtt_pop.push_to_gaa = 1
> THEN 'Y' END as gaa, pop.gii_circuitid AS pop_name, cst.label AS
> pop_status, seg.a_company_name AS pop_location, seg.vendor_id AS
> pop_provider_id, seg.vendor_name AS pop_provider, cs.address1 AS
> pop_street, cs.city AS pop_city, cs.postal_code AS pop_postal_code,
> cs.state AS pop_state, csc.code AS pop_country, cs.country_id AS
> pop_country_id FROM ( SELECT c.gii_circuitid, max(so.id
> ) AS service_order_id FROM service_order so join
> circuit c on c.product_id=so.product_id join master_service_order
> mso on mso.id =so.master_service_order_id WHERE NOT
> (so.ordertype_id = 2 AND so.status <> 999) AND NOT (so.ordertype_id
> = 3 AND so.status <> 999) AND c.status >= 20 AND c.status not in
> (160,999) AND mso.client_id=11615 AND c.service_description=28 AND
> c.status!=160 GROUP BY c.gii_circuitid ) pop JOIN service_order so
> ON so.id  = pop.service_order_id left JOIN client_site
> cs on cs.id =so.a_site_id left JOIN country csc on
> csc.id =cs.country_id JOIN circuit c ON
> so.product_id=c.product_id JOIN circuit_status cst ON cst.id
> =c.status JOIN ( SELECT c.id  AS
> circuit_id, sg.id  AS segment_id,
> c.pop_support_vendor_id AS vendor_id, v.name  AS
> vendor_name, sg.a_company_name FROM segment sg JOIN circuit_layout
> cl ON cl.segment_id = sg.id  AND cl.ordinal = 1 JOIN
> circuit c ON c.id  = cl.circuit_id JOIN vendor v ON
> v.id  = c.pop_support_vendor_id ) seg ON seg.circuit_id
> = c.id  JOIN vendor_gtt_pop on
> vendor_gtt_pop.gtt_pop_id = seg.circuit_id offset 0) foo where
> vendor_id = 12346 AND (1=1) ORDER BY pop_name ASC,id desc LIMIT 10;
> 
>    id   | vendor_id | gaa |   pop_name    | pop_status |
> pop_location | pop_provider_id | pop_provider | pop_street 
> | pop_city | pop
> 
> _postal_code | pop_state | pop_country | pop_country_id
> 
> 
> +---+-+---++--+-+--+-+--+
> 
> -+---+-+
> 
> 684807 | 12346 | Y   | GTT/POP/LON1T | Active | LON1T   
> |   12288 | Telehouse UK | 14 Coriander Avenue | London   | E14
> 
> 2AA |   | GB  |    219
> 
> (1 row)
> 
>  
> 
> *Time: 2245.073 ms (00:02.245)*
> 
> 
> 
> 
> On Fri, Mar 23, 2018 at 9:31 AM, Alessandro Aste
> mailto:alessandro.a...@gmail.com>> wrote:
> 
> Tomas, I'm attaching a 4MB file with the perf report. Let me
> know if it gets blocked, I'll shrink it to the first 1000 lines. 
> 
> Thank you,
> 
> Alessandro.
> 
> On Thu, Mar 22, 2018 at 11:42 PM, Tomas Vondra
>  > wrote:
> 
> On 03/22/2018 11:29 PM, Alessandro Aste wrote:
> > Thanks Tomas. 

Re: Problem with postgreSQL

[Adrian Klaver]

On 03/28/2018 02:36 AM, Gian mario Caggiu wrote:
Please also reply to list so this can be seen by more eyes.
Ccing list

I've already written to you what versions I downloaded, 10.3 and 9.6.8. 
I clicked in the 'download the installer' section and then I selected 
the version to download.


So this section, correct?:
"
Interactive installer by EnterpriseDB

Download the installer certified by EnterpriseDB for all supported 
PostgreSQL versions.

"


For reference, what version of OS X?

After downloading the installer you ran the GUI installer, correct?

Where there any errors when you ran the installation process?




FYI, there are other choices on this page:

https://www.postgresql.org/download/macosx/

One that may be easier to deal with at this stage is:

Postgres.app

Postgres.app is a simple, native macOS app that runs in the menubar 
without the need of an installer. Open the app, and you have a 
PostgreSQL server ready and awaiting new connections. Close the app, and 
the server shuts down.



http://postgresapp.com/





Il giorno 27 mar 2018, alle ore 20:44, Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> ha scritto:


Which of the downloads did you select from below?:

https://www.postgresql.org/download/macosx/

Without that information it is not possible to provide an answer.





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

Query performance with cluster

[hmidi slim]

Hi,
I'm creating this table :
*create table availability(product_id integer, product_name varchar(255),
during daterange);*
*create index time_idx on availability using gist(during);*
*insert into availability*
*select p.id , concat('prod ', p.id::text),
daterange((now() + concat(r.v, ' days')::interval)::date, (now() +
concat((r.v + 1 + random() * 21)::int, ' days')::interval)::date)*
*from (select * from generate_series(1, 100)) p(id)*
*cross join (select * from generate_series(1, 15)) n(i)*
*join lateral (*
*  select p2.id , n2.i, (random() * 600 - 300)::int AS v*
*  from generate_series(1, 100) p2(id),*
*  generate_series(1, 15) n2(i)*
*) r*
*on r.id  = p.id  and r.i = n.i;*

When I execute this query :
*select * from availability where during @> daterange('2018-03-27',
'2018-03-31');*
I got 2s as query runtime. I used a cluster:
*cluster **availability using time_idx *
The time became between 200ms and 300ms. Based on the doc, *Clustering is a
one-time operation: when the table is subsequently updated, the changes are
not clustered.*SO if a new row was add I have to run the cluster manually.
Is there any proposition when to run the cluster when an update was made,
because this operation took a time to accomplish.Thus the database was
locked and it's not possible for other operation such as read or write to
be executed.Any propositions to use cluster or something else for better
performance?

Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade

[Moreno Andreo]

Il 27/03/2018 22:57, Adrian Klaver ha scritto:

On 03/27/2018 01:46 PM, Ken Beck wrote:

I am working on two systems, one running in Oracle VirtualBox on my
laptop, the other in a DigitalOcean droplet. I know on one of them I
tried to remove the postgres-9.6, and it must have been my laptop, here
is the output from pg_lsclusters:

Ver Cluster Port Status Owner    Data directory Log file
9.3 main    5432 down   postgres /var/lib/postgresql/9.3/main
/var/log/postgresql/postgresql-9.3-main.log


What happens if you do?:

sudo pg_ctlcluster 9.3 main start 
If possible, I'll take a look at 
/var/log/postgresql/postgresql-9.3-main.log.

That may give us a big help to uncover what's goin'on.

Cheers

Moreno.-

Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade

[Adrian Klaver]

On 03/28/2018 09:24 AM, Moreno Andreo wrote:

Il 27/03/2018 22:57, Adrian Klaver ha scritto:

On 03/27/2018 01:46 PM, Ken Beck wrote:

I am working on two systems, one running in Oracle VirtualBox on my
laptop, the other in a DigitalOcean droplet. I know on one of them I
tried to remove the postgres-9.6, and it must have been my laptop, here
is the output from pg_lsclusters:

Ver Cluster Port Status Owner    Data directory Log file
9.3 main    5432 down   postgres /var/lib/postgresql/9.3/main
/var/log/postgresql/postgresql-9.3-main.log


What happens if you do?:

sudo pg_ctlcluster 9.3 main start 
If possible, I'll take a look at 
/var/log/postgresql/postgresql-9.3-main.log.

That may give us a big help to uncover what's goin'on.


See this post:
https://www.postgresql.org/message-id/5bad1f01-d06a-1526-96d2-c9cfd6062a11%40gmail.com



Cheers

Moreno.-






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

pg_ctl promote causes error "could not read block" (PG 9.5.0 and 9.5.4)

[raj 1988]

Hi there,

we are running into this weird issue of table getting on READ ONLY mode
with below error:

ERROR:  could not read block 54 in file "base//215619": read
only 0 of 8192 bytes

We are facing this whenever we promote a streaming standby using pg_ctl
promote command, and this is happing on PG 9.5.0 and 9.5.4 and OEL 6.9

Are we hitting some bug? tried to look around but not able to confirm if we
are hitting a bug or not.  For us this is happening consistently on
different servers whenever we do pg_ctl promote and then it block WRITE on
that table.

As of now we get rid of the error either by doing vacuum full or CTAS, but
i am afraid what we will do in case this happens to our few TB tables.


Thanks a lot in advance

-raj

Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade

[Moreno Andreo]

Il 28/03/2018 18:28, Adrian Klaver ha scritto:

On 03/28/2018 09:24 AM, Moreno Andreo wrote:

Il 27/03/2018 22:57, Adrian Klaver ha scritto:

On 03/27/2018 01:46 PM, Ken Beck wrote:

I am working on two systems, one running in Oracle VirtualBox on my
laptop, the other in a DigitalOcean droplet. I know on one of them I
tried to remove the postgres-9.6, and it must have been my laptop, 
here

is the output from pg_lsclusters:

Ver Cluster Port Status Owner    Data directory Log file
9.3 main    5432 down   postgres /var/lib/postgresql/9.3/main
/var/log/postgresql/postgresql-9.3-main.log


What happens if you do?:

sudo pg_ctlcluster 9.3 main start 
If possible, I'll take a look at 
/var/log/postgresql/postgresql-9.3-main.log.

That may give us a big help to uncover what's goin'on.


See this post:
https://www.postgresql.org/message-id/5bad1f01-d06a-1526-96d2-c9cfd6062a11%40gmail.com 

Yes I saw it, but I can't say if that log was from 9.3 or 9.6 cluster... 
as you can see above he wrote that he had a 9.6, that he tried to 
remove, and a 9.3, which was not starting. So it's not clear to me if 
the log he sent was from one version or the another...






Cheers

Moreno.-

Re: [EXTERNAL]Re: pg_dump -Fd -j2 on standby in 9.6.6

[Ron]

On 03/28/2018 03:05 AM, Andreas Kretschmer wrote:
[snip]

> This e-mail message, including any attachments,

this is a public mailing list ...


The intended recipient is the public mailing list, no?

--
Angular momentum makes the world go 'round.

Re: [EXTERNAL]Re: pg_dump -Fd -j2 on standby in 9.6.6

[David G. Johnston]

On Wed, Mar 28, 2018 at 10:13 AM, Ron  wrote:

> On 03/28/2018 03:05 AM, Andreas Kretschmer wrote:
> [snip]
>
>> > This e-mail message, including any attachments,
>>
>> this is a public mailing list ...
>>
>
> The intended recipient is the public mailing list, no?


​A mailing list doesn't really "receive" email - it is only a distribution
mechanism.  IANAL but basically adding boilerplate like that to mail sent
to a public mailing list is pointless - there are no "intended recipients"
but rather the message is being put into the public domain for anyone to
consume, regardless of whether they are/were a member of the mailing list
to which the email was addressed.

David J.

Re: Using Lateral

[Paul Jungwirth]

On 03/28/2018 05:22 AM, Johann Spies wrote:

Thanks Paul.

I was hesitant to post my full query.  It is a long and complicated
query.


Ha ha, you aren't joking. :-)

With something that long I don't think I'd want to split every xpath 
call into a new join. I guess you could try it and see though.


Stepping back, your original query is prepared for xpath to give 0 
results or 1 result, but not 2+, and the problem is that you're getting 
a 2+ result from the address_spec element. What is the right behavior 
there? To get two rows in the end result? Just use the first/last 
address_spec?


If you want to force 0/1 results, you can keep your structure and do this:

SELECT  DISTINCT ut,
(xpath('//t:address_spec/t:country/text()', q.address_spec, 
p.ns))[1]::citext AS country,
(xpath('//t:address_spec/t:city/text()', q.address_spec, 
p.ns))[1]::citext AS city,
(xpath('//t:organizations/t:organization/text()', 
q.address_spec, p.ns))[1]::citext AS organisation,
 (xpath('//t:organizations/t:organization[@pref="Y"]/text()', 
q.address_spec, p.ns))[1]::citext AS prefname,
 (xpath ('//t:suborganizations/t:suborganization/text()', 
q.address_spec, p.ns))[1]::citext AS suborgname,
 (xpath ('/t:address_spec/@addr_no', q.address_spec, 
p.ns))[1]::text::integer AS addr_no


(Actually I see you are already doing that for addr_no. And an aside: 
did you intend `/t:address_spec` instead of `//t:address_spec` there?)


If you would prefer to get multiple rows back, then you'll probably need 
a subquery to give one row per `xpath('//t:address_spec')`, so that you 
can keep the cities/countries/addr_nos together.


Anyway, I hope that's enough to get you started on the right path! The 
root cause is that UNNEST is giving you two rows where it only makes 
sense to have one, so you need to restrict that or pull it into a 
context where several rows are allowed (like a join).


--
Paul  ~{:-)
p...@illuminatedcomputing.com

Using Lateral

[David G. Johnston]

On Tuesday, March 27, 2018, Johann Spies  wrote:

> In the past I could use this in a query:
>
> SELECT
> DISTINCT ut,
> CASE
> WHEN xpath_exists ('//t:address_spec/t:country/text()',
> q.address_spec,
> p.ns)
> THEN unnest (xpath ('//t:address_spec/t:country/text()',
> q.address_spec,
> p.ns))::citext
> ELSE NULL
> END country,
> [...]
> Just selecting "unnest(...)" gives the wrong result because if the
> xpath does not exist all the other parts of the query (like 'ut' in
> this case) also are empty.
>

You should be able to solve the empty-set-in-target-list problem via a
scalar subquery instead of a case construct.

Select distinct ut, (select unnest(...)) as country from ...

The subselect wil return null if fed zero rows. Though you will still have
to solve an issue if the unrest returns 1+ rows.

In lieu of the inline scalar subquery I would advise writing a function and
just calling it directly in the target-list.  But it should not return
setof, I'd return an array if you need to accept the possibility of 1+
matches, and return an empty array for zero matches.

David J.

Question about buffers_alloc in pg_stat_bgwriter view for monitoring

[Alvar Freude]

Hi all,

Can someone tell me, what the value of buffers_alloc in the pg_stat_bgwriter 
view 
(https://www.postgresql.org/docs/current/static/monitoring-stats.html#PG-STAT-BGWRITER-VIEW)
 is exactly? Is this the amount of shared buffers used by the bgwriter?

I’m working on a new PostgreSQL monitoring framework 
(https://github.com/alvar-freude/Posemo) and now look how to make reasonable 
checks (and as result graphs) for BGWriter activity.

At the moment my plan is to make more then one check out of pg_stat_bgwriter:

 * BGWriterAmount
   A check which returns the amount of written data,
   taken from the following values multiplied by
   current_setting('block_size'):
   buffers_checkpoint, buffers_clean, buffers_backend

 * BGWriterCheckpoints:
   A check which returns the number of checkpoints:
   checkpoints_timed + checkpoints_req
   Or: checkpoints_timed, checkpoints_req

 * BGWriterTime:
   Time taken by the BGWriter
   checkpoint_write_time, checkpoint_sync_time

 * BGWriterAlloc:
   Amount of memory used by BGWriter
   buffers_alloc * current_setting('block_size‘)

 * BGWriterInfo:
   Extra values, which should go to an extra graph, because they
   are much smaller and usually only increase in special situations
   maxwritten_clean, buffers_backend_fsync


Is this a reasonable plan to divide pg_stat_bgwriter into this parts?



Thanks && bye
  Alvar



--
Alvar C.H. Freude | http://alvar.a-blast.org/
https://blog.alvar-freude.de/
https://www.wen-waehlen.de/






signature.asc
Description: Message signed with OpenPGP

Query Crashes PG 10.3 using partitions, works on 9.6

[Cory Tucker]

Hey guys, I am in the middle of testing out a database migration from 9.6
to 10.3.  We have a quasi-multi tenant based application and so are
utilizing native partitions on some relations to help improve some
performance.

I was issuing a query on both databases to cleanup some duplicates in
preparation of applying new indexes.  On the 9.6 database with all the data
in one table, the query runs fine in about 6 min.  On 10.3, with a work_mem
setting of 1GB the query runs for about 7 minutes and then gets terminated
with an out of memory error.  If I bump the work_mem up fairly high (12GB
out of 52GB available) the server actually crashes.

On the both databases, the total dataset size is exactly the same, ~29M
records.  The table looks like this:

  Table "candidate_person"
Column|   Type   | Collation | Nullable | Default
--+--+---+--+-
 created_at   | timestamp with time zone |   | not null | now()
 modified_at  | timestamp with time zone |   | not null | now()
 account_id   | bigint   |   | not null |
 candidate_id | character varying(40)| C | not null |
 first_name   | text |   |  |
 middle_name  | text |   |  |
 last_name| text |   |  |
 spouse   | boolean  |   | not null | false
 suffix   | text |   |  |
 salutation   | text |   |  |
Partition key: LIST (account_id)

With the only difference being on 9.6 there obviously isn't any
partitions.  On 10.3 there are ~250 partition tables.

I have attached the server log that shows the first out of memory and then
the server crash and recovery.


db_crash.log
Description: Binary data

RE: Query Crashes PG 10.3 using partitions, works on 9.6

[Kumar, Virendra]

Would be nice if you can attach explain plan of course, explain analyze is not 
going to work if server is crashing.


Regards,
Virendra

From: Cory Tucker [mailto:cory.tuc...@gmail.com]
Sent: Wednesday, March 28, 2018 5:49 PM
To: pgsql-gene...@postgresql.org
Subject: Query Crashes PG 10.3 using partitions, works on 9.6

Hey guys, I am in the middle of testing out a database migration from 9.6 to 
10.3.  We have a quasi-multi tenant based application and so are utilizing 
native partitions on some relations to help improve some performance.

I was issuing a query on both databases to cleanup some duplicates in 
preparation of applying new indexes.  On the 9.6 database with all the data in 
one table, the query runs fine in about 6 min.  On 10.3, with a work_mem 
setting of 1GB the query runs for about 7 minutes and then gets terminated with 
an out of memory error.  If I bump the work_mem up fairly high (12GB out of 
52GB available) the server actually crashes.

On the both databases, the total dataset size is exactly the same, ~29M 
records.  The table looks like this:

  Table "candidate_person"
Column|   Type   | Collation | Nullable | Default
--+--+---+--+-
 created_at   | timestamp with time zone |   | not null | now()
 modified_at  | timestamp with time zone |   | not null | now()
 account_id   | bigint   |   | not null |
 candidate_id | character varying(40)| C | not null |
 first_name   | text |   |  |
 middle_name  | text |   |  |
 last_name| text |   |  |
 spouse   | boolean  |   | not null | false
 suffix   | text |   |  |
 salutation   | text |   |  |
Partition key: LIST (account_id)

With the only difference being on 9.6 there obviously isn't any partitions.  On 
10.3 there are ~250 partition tables.

I have attached the server log that shows the first out of memory and then the 
server crash and recovery.



This message is intended only for the use of the addressee and may contain
information that is PRIVILEGED AND CONFIDENTIAL.

If you are not the intended recipient, you are hereby notified that any
dissemination of this communication is strictly prohibited. If you have
received this communication in error, please erase all copies of the message
and its attachments and notify the sender immediately. Thank you.

RE: Query Crashes PG 10.3 using partitions, works on 9.6

[Kumar, Virendra]

Hi Cory,

You are running the query (DELETE statement) as if the table is not partitioned 
which is causing the server to crash.
Please run that query for each partitions separately in a loop with dynamic 
query and you should see the improvement. It should be pretty quick.

Regards,
Virendra.

From: Kumar, Virendra
Sent: Wednesday, March 28, 2018 5:57 PM
To: Cory Tucker; pgsql-gene...@postgresql.org
Subject: RE: Query Crashes PG 10.3 using partitions, works on 9.6

Would be nice if you can attach explain plan of course, explain analyze is not 
going to work if server is crashing.


Regards,
Virendra

From: Cory Tucker [mailto:cory.tuc...@gmail.com]
Sent: Wednesday, March 28, 2018 5:49 PM
To: pgsql-gene...@postgresql.org
Subject: Query Crashes PG 10.3 using partitions, works on 9.6

Hey guys, I am in the middle of testing out a database migration from 9.6 to 
10.3.  We have a quasi-multi tenant based application and so are utilizing 
native partitions on some relations to help improve some performance.

I was issuing a query on both databases to cleanup some duplicates in 
preparation of applying new indexes.  On the 9.6 database with all the data in 
one table, the query runs fine in about 6 min.  On 10.3, with a work_mem 
setting of 1GB the query runs for about 7 minutes and then gets terminated with 
an out of memory error.  If I bump the work_mem up fairly high (12GB out of 
52GB available) the server actually crashes.

On the both databases, the total dataset size is exactly the same, ~29M 
records.  The table looks like this:

  Table "candidate_person"
Column|   Type   | Collation | Nullable | Default
--+--+---+--+-
 created_at   | timestamp with time zone |   | not null | now()
 modified_at  | timestamp with time zone |   | not null | now()
 account_id   | bigint   |   | not null |
 candidate_id | character varying(40)| C | not null |
 first_name   | text |   |  |
 middle_name  | text |   |  |
 last_name| text |   |  |
 spouse   | boolean  |   | not null | false
 suffix   | text |   |  |
 salutation   | text |   |  |
Partition key: LIST (account_id)

With the only difference being on 9.6 there obviously isn't any partitions.  On 
10.3 there are ~250 partition tables.

I have attached the server log that shows the first out of memory and then the 
server crash and recovery.



This message is intended only for the use of the addressee and may contain
information that is PRIVILEGED AND CONFIDENTIAL.

If you are not the intended recipient, you are hereby notified that any
dissemination of this communication is strictly prohibited. If you have
received this communication in error, please erase all copies of the message
and its attachments and notify the sender immediately. Thank you.



This message is intended only for the use of the addressee and may contain
information that is PRIVILEGED AND CONFIDENTIAL.

If you are not the intended recipient, you are hereby notified that any
dissemination of this communication is strictly prohibited. If you have
received this communication in error, please erase all copies of the message
and its attachments and notify the sender immediately. Thank you.

Re: Query Crashes PG 10.3 using partitions, works on 9.6

[Tom Lane]

Cory Tucker  writes:
> I was issuing a query on both databases to cleanup some duplicates in
> preparation of applying new indexes.  On the 9.6 database with all the data
> in one table, the query runs fine in about 6 min.  On 10.3, with a work_mem
> setting of 1GB the query runs for about 7 minutes and then gets terminated
> with an out of memory error.

Hm, this seems a bit excessive:

  MessageContext: 1333788672 total in 169 blocks; 2227176 free (9 chunks); 
1331561496 used

and this is really grim:

65678 more child contexts containing 47607478048 total in 2577 blocks; 
12249392 free (446 chunks); 47595228656 used

and this is just silly:

2018-03-28 19:20:33.264 UTC [10580] cory@match ERROR:  out of memory
2018-03-28 19:20:33.264 UTC [10580] cory@match DETAIL:  Failed on request of 
size 1610612736.

Can you extract a self-contained test case that uses unreasonable amounts
of memory?  It seems from this trace that the wheels are coming off in
at least two places, but identifying exactly where is impossible without
more info.

If you can't make a publishable test case, capturing a stack trace from
the point of the OOM error (set the breakpoint at errfinish) would
probably be enough info to figure out what is trying to grab 1.6GB in one
bite.  But it won't help us find out why so many empty ExprContexts are
getting created.

regards, tom lane

Re: Query Crashes PG 10.3 using partitions, works on 9.6

[Cory Tucker]

>
> Can you extract a self-contained test case that uses unreasonable amounts
> of memory?  It seems from this trace that the wheels are coming off in
> at least two places, but identifying exactly where is impossible without
> more info.
>

I will try to make a test case.  The data in this table is PII so I can't
just export it, but I might be able to replicate using fake data.

Will try in the next few days and hopefully post back.

--Cory

Re: Query Crashes PG 10.3 using partitions, works on 9.6

[Cory Tucker]

>
> You are running the query (DELETE statement) as if the table is not
> partitioned which is causing the server to crash.
>
> Please run that query for each partitions separately in a loop with
> dynamic query and you should see the improvement. It should be pretty quick.
>
>

I understand that I can do that and it would be quicker. But the point of
the email was that this thing that works on 9.6, and is valid SQL, causes
the server to crash in a newer version using a valid supported
configuration.

Re: Query Crashes PG 10.3 using partitions, works on 9.6

[Tom Lane]

Cory Tucker  writes:
>> Can you extract a self-contained test case that uses unreasonable amounts
>> of memory?  It seems from this trace that the wheels are coming off in
>> at least two places, but identifying exactly where is impossible without
>> more info.

> I will try to make a test case.  The data in this table is PII so I can't
> just export it, but I might be able to replicate using fake data.

My guess is that the specific data is not critical at all, just the
table properties.  You might need to take care that the same EXPLAIN
plan is selected, which might require fake data that has roughly
similar properties as to number of distinct values and so on.

regards, tom lane

Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade

[Ken Beck]

Thanks for both responses -- the file contents I sent were from file
named postgresql-9.3-main.log.1, and I am assuming this means from the
9.3, not 9.6. Sorry I did not include that earlier. Starting to sousnds
like I need to consult Ubuntu user groups.

Ken Beck
Liberty, Utah, USA

On 03/28/2018 10:46 AM, Moreno Andreo wrote:
> Il 28/03/2018 18:28, Adrian Klaver ha scritto:
>> On 03/28/2018 09:24 AM, Moreno Andreo wrote:
>>> Il 27/03/2018 22:57, Adrian Klaver ha scritto:
 On 03/27/2018 01:46 PM, Ken Beck wrote:
> I am working on two systems, one running in Oracle VirtualBox on my
> laptop, the other in a DigitalOcean droplet. I know on one of them I
> tried to remove the postgres-9.6, and it must have been my laptop,
> here
> is the output from pg_lsclusters:
>
> Ver Cluster Port Status Owner    Data directory Log file
> 9.3 main    5432 down   postgres /var/lib/postgresql/9.3/main
> /var/log/postgresql/postgresql-9.3-main.log

 What happens if you do?:

 sudo pg_ctlcluster 9.3 main start 
>>> If possible, I'll take a look at
>>> /var/log/postgresql/postgresql-9.3-main.log.
>>> That may give us a big help to uncover what's goin'on.
>>
>> See this post:
>> https://www.postgresql.org/message-id/5bad1f01-d06a-1526-96d2-c9cfd6062a11%40gmail.com
>>
> Yes I saw it, but I can't say if that log was from 9.3 or 9.6
> cluster... as you can see above he wrote that he had a 9.6, that he
> tried to remove, and a 9.3, which was not starting. So it's not clear
> to me if the log he sent was from one version or the another...
>
>>
>>>
>>> Cheers
>>>
>>> Moreno.-
>>>
>>>
>>>
>>
>>
>
>
>

Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade

[Adrian Klaver]

On 03/28/2018 03:45 PM, Ken Beck wrote:

Thanks for both responses -- the file contents I sent were from file
named postgresql-9.3-main.log.1, and I am assuming this means from the


Well that is an archived log and would show old information. The current 
log would be postgresql-9.3-main.log.



9.3, not 9.6. Sorry I did not include that earlier. Starting to sousnds
like I need to consult Ubuntu user groups.


Not necessarily as there are many Ubuntu users on this list.

Did you try?:

sudo pg_ctlcluster 9.3 main start



Ken Beck
Liberty, Utah, USA

On 03/28/2018 10:46 AM, Moreno Andreo wrote:

Il 28/03/2018 18:28, Adrian Klaver ha scritto:

On 03/28/2018 09:24 AM, Moreno Andreo wrote:

Il 27/03/2018 22:57, Adrian Klaver ha scritto:

On 03/27/2018 01:46 PM, Ken Beck wrote:

I am working on two systems, one running in Oracle VirtualBox on my
laptop, the other in a DigitalOcean droplet. I know on one of them I
tried to remove the postgres-9.6, and it must have been my laptop,
here
is the output from pg_lsclusters:

Ver Cluster Port Status Owner    Data directory Log file
9.3 main    5432 down   postgres /var/lib/postgresql/9.3/main
/var/log/postgresql/postgresql-9.3-main.log


What happens if you do?:

sudo pg_ctlcluster 9.3 main start

If possible, I'll take a look at
/var/log/postgresql/postgresql-9.3-main.log.
That may give us a big help to uncover what's goin'on.


See this post:
https://www.postgresql.org/message-id/5bad1f01-d06a-1526-96d2-c9cfd6062a11%40gmail.com


Yes I saw it, but I can't say if that log was from 9.3 or 9.6
cluster... as you can see above he wrote that he had a 9.6, that he
tried to remove, and a 9.3, which was not starting. So it's not clear
to me if the log he sent was from one version or the another...





Cheers

Moreno.-

















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

Re: pg_ctl promote causes error "could not read block" (PG 9.5.0 and 9.5.4)

[Michael Paquier]

On Wed, Mar 28, 2018 at 09:36:11AM -0700, raj 1988 wrote:
> Are we hitting some bug? tried to look around but not able to confirm if we
> are hitting a bug or not.  For us this is happening consistently on
> different servers whenever we do pg_ctl promote and then it block WRITE on
> that table.

This has the strong smell of the FSM bug fixed in 9.5.5:
https://www.postgresql.org/docs/devel/static/release-9-5-5.html

So, in order to get things right:
1) Update to the latest version of Postgres 9.5.
2) Make sure that your cluster gets in a clean state.  There are
instructions here:
https://wiki.postgresql.org/wiki/Free_Space_Map_Problems

> As of now we get rid of the error either by doing vacuum full or CTAS, but
> i am afraid what we will do in case this happens to our few TB tables.

This rebuilds the file-space map, which is why it goes away.  You really
want to do the work I am mentioning above to get back to a clean state.
--
Michael


signature.asc
Description: PGP signature

Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade

[Ken Beck]

The current log is 0 length, and indeed empty.

I just tried 'sudo pg_ctlcluster 9.3 main start'  and got the following
error:

Error: Config owner (kcb:1000) and data owner (postgres:116) do not
match, and config owner is not root

Changed to user postgres, tried again and got:

Warning: the cluster will not be running as a systemd service. Consider
using systemctl:
  sudo systemctl start postgresql@9.3-main
Removed stale pid file.

Next tried the suggestion:

sudo systemctl start postgresql@9.3-main, but the system failed to
accept the password for user postgres. That is NOT something I changed
during the Ubuntu upgrade process, so that has been a puzzle as well. My
main login, user kcb is OK, password still works.

So I change back to user kcb and try the suggestion again:

kcb: jobs(0)$ sudo systemctl start postgresql@9.3-main
Job for postgresql@9.3-main.service failed because the control process
exited with error code. See "systemctl status
postgresql@9.3-main.service" and "journalctl -xe" for details.
kcb: jobs(0)$ systemctl status postgresql@9.3-main.service
● postgresql@9.3-main.service - PostgreSQL Cluster 9.3-main
   Loaded: loaded (/lib/systemd/system/postgresql@.service; disabled;
vendor preset: enabled)
   Active: failed (Result: exit-code) since Wed 2018-03-28 19:51:50 MDT;
47s ago
  Process: 2960 ExecStart=postgresql@%i --skip-systemctl-redirect %i
start (code=exited, status=

Mar 28 19:51:50 kcb-VirtualBox systemd[1]: Starting PostgreSQL Cluster
9.3-main...
Mar 28 19:51:50 kcb-VirtualBox postgresql@9.3-main[2960]: Error: Config
owner (kcb:1000) and dat
Mar 28 19:51:50 kcb-VirtualBox systemd[1]: postgresql@9.3-main.service:
Control process exited,
Mar 28 19:51:50 kcb-VirtualBox systemd[1]: Failed to start PostgreSQL
Cluster 9.3-main.
Mar 28 19:51:50 kcb-VirtualBox systemd[1]: postgresql@9.3-main.service:
Unit entered failed stat
Mar 28 19:51:50 kcb-VirtualBox systemd[1]: postgresql@9.3-main.service:
Failed with result 'exit

Is it possible a big problem is that the password for user postgres not
longer works for some reason, not sure why. It appears the password can
not be rest or changed without knowing the original, and what I thought
was the original no longer works.

Output of that is in the attached file;
On 03/28/2018 04:58 PM, Adrian Klaver wrote:
> On 03/28/2018 03:45 PM, Ken Beck wrote:
>> Thanks for both responses -- the file contents I sent were from file
>> named postgresql-9.3-main.log.1, and I am assuming this means from the
>
> Well that is an archived log and would show old information. The
> current log would be postgresql-9.3-main.log.
>
>> 9.3, not 9.6. Sorry I did not include that earlier. Starting to sousnds
>> like I need to consult Ubuntu user groups.
>
> Not necessarily as there are many Ubuntu users on this list.
>
> Did you try?:
>
> sudo pg_ctlcluster 9.3 main start
>
>>
>> Ken Beck
>> Liberty, Utah, USA
>>
>> On 03/28/2018 10:46 AM, Moreno Andreo wrote:
>>> Il 28/03/2018 18:28, Adrian Klaver ha scritto:
 On 03/28/2018 09:24 AM, Moreno Andreo wrote:
> Il 27/03/2018 22:57, Adrian Klaver ha scritto:
>> On 03/27/2018 01:46 PM, Ken Beck wrote:
>>> I am working on two systems, one running in Oracle VirtualBox on my
>>> laptop, the other in a DigitalOcean droplet. I know on one of
>>> them I
>>> tried to remove the postgres-9.6, and it must have been my laptop,
>>> here
>>> is the output from pg_lsclusters:
>>>
>>> Ver Cluster Port Status Owner    Data directory Log file
>>> 9.3 main    5432 down   postgres /var/lib/postgresql/9.3/main
>>> /var/log/postgresql/postgresql-9.3-main.log
>>
>> What happens if you do?:
>>
>> sudo pg_ctlcluster 9.3 main start
> If possible, I'll take a look at
> /var/log/postgresql/postgresql-9.3-main.log.
> That may give us a big help to uncover what's goin'on.

 See this post:
 https://www.postgresql.org/message-id/5bad1f01-d06a-1526-96d2-c9cfd6062a11%40gmail.com


>>> Yes I saw it, but I can't say if that log was from 9.3 or 9.6
>>> cluster... as you can see above he wrote that he had a 9.6, that he
>>> tried to remove, and a 9.3, which was not starting. So it's not clear
>>> to me if the log he sent was from one version or the another...
>>>

>
> Cheers
>
> Moreno.-
>
>
>


>>>
>>>
>>>
>>
>>
>>
>
>


Mar 28 20:05:01 kcb-VirtualBox CRON[3050]: pam_unix(cron:session): session 
opened for user root by (uid=0)
Mar 28 20:05:01 kcb-VirtualBox CRON[3051]: (root) CMD (command -v debian-sa1 > 
/dev/null && debian-sa1 1 1)
Mar 28 20:05:01 kcb-VirtualBox CRON[3050]: pam_unix(cron:session): session 
closed for user root
Mar 28 20:06:31 kcb-VirtualBox sudo[3073]:  kcb : TTY=pts/2 ; PWD=/home/kcb 
; USER=root ; COMMAND=/bin/systemctl start postgresql@9.3-main
Mar 28 20:06:31 kcb-VirtualBox sudo[3073]: pam_unix(sudo:session): session 
opened for user root by (uid=0)
Mar 28 20:06:31 kcb-VirtualBox systemd[1]: Start

Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade

[Rob Sargent]

On 03/28/2018 08:29 PM, Ken Beck wrote:

Is it possible a big problem is that the password for user postgres not
longer works for some reason, not sure why. It appears the password can
not be rest or changed without knowing the original, and what I thought
was the original no longer works.



Maybe the install nulled (reset) the password but you can change it:

sudo su -
passwd postgres

Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade

[David G. Johnston]

On Wed, Mar 28, 2018 at 7:29 PM, Ken Beck  wrote:

> Is it possible a big problem is that the password for user postgres not
> longer works for some reason, not sure why. It appears the password can
> not be rest or changed without knowing the original, and what I thought
> was the original no longer works.
>

​Pretty sure on Ubuntu default the postgres O/S user​ authenticates itself
with PostgreSQL via "peer".  The DB postgres user doesn't have a password.

David J.

Re: Autonomous transaction, background worker

[Thiemo Kellner]

I've experimented with background workers a bit. Yes, normally you'd 
write some C, but you could also install this (mentioned in the blog 
post and the source of pg_background_launch):


Thanks for your answer. I want my logger to be used with the facilities 
there are from PostgreSQL so I'll go the dblink way. The docs write that 
fdw provides a more modern architecture for more ore less the same. 
However, I do not think the execution of arbiträry stuff is possible by fdw.


Kind regards Thiemo

--
SIP/iptel.org: thiemo.kellner
Öffentlicher PGP-Schlüssel:
http://pgp.mit.edu/pks/lookup?op=get&search=0xCA167FB0E717AFFC
<>

Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade

[Adrian Klaver]

On 03/28/2018 07:29 PM, Ken Beck wrote:

Comments in line.


The current log is 0 length, and indeed empty.

I just tried 'sudo pg_ctlcluster 9.3 main start'  and got the following
error:

Error: Config owner (kcb:1000) and data owner (postgres:116) do not
match, and config owner is not root


So why is your config owned by you and the data owned by postgres?

Your configuration files should be under:

/etc/postgresql/9.3/main/

and on my Ubuntu setups anyway is owned by postgres.



Changed to user postgres, tried again and go >
Warning: the cluster will not be running as a systemd service. Consider
using systemctl:
   sudo systemctl start postgresql@9.3-main
Removed stale pid file.

Next tried the suggestion:

sudo systemctl start postgresql@9.3-main, but the system failed to
accept the password for user postgres. That is NOT something I changed


My experience with Ubuntu is that the postgres system user does not have 
a password. So where you using the database postgres user password? For 
more detail see here:


https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-16-04


during the Ubuntu upgrade process, so that has been a puzzle as well. My
main login, user kcb is OK, password still works.

So I change back to user kcb and try the suggestion again:

kcb: jobs(0)$ sudo systemctl start postgresql@9.3-main
Job for postgresql@9.3-main.service failed because the control process
exited with error code. See "systemctl status
postgresql@9.3-main.service" and "journalctl -xe" for details.
kcb: jobs(0)$ systemctl status postgresql@9.3-main.service
● postgresql@9.3-main.service - PostgreSQL Cluster 9.3-main
    Loaded: loaded (/lib/systemd/system/postgresql@.service; disabled;
vendor preset: enabled)
    Active: failed (Result: exit-code) since Wed 2018-03-28 19:51:50 MDT;
47s ago
   Process: 2960 ExecStart=postgresql@%i --skip-systemctl-redirect %i
start (code=exited, status=

Mar 28 19:51:50 kcb-VirtualBox systemd[1]: Starting PostgreSQL Cluster
9.3-main...
Mar 28 19:51:50 kcb-VirtualBox postgresql@9.3-main[2960]: Error: Config
owner (kcb:1000) and dat


Again the error with the config owner being you. Seems it would help to 
track down what copies of the conf files are installed on your machine.




Mar 28 19:51:50 kcb-VirtualBox systemd[1]: postgresql@9.3-main.service:
Control process exited,
Mar 28 19:51:50 kcb-VirtualBox systemd[1]: Failed to start PostgreSQL
Cluster 9.3-main.
Mar 28 19:51:50 kcb-VirtualBox systemd[1]: postgresql@9.3-main.service:
Unit entered failed stat
Mar 28 19:51:50 kcb-VirtualBox systemd[1]: postgresql@9.3-main.service:
Failed with result 'exit

Is it possible a big problem is that the password for user postgres not
longer works for some reason, not sure why. It appears the password can
not be rest or changed without knowing the original, and what I thought
was the original no longer works.


Remember there are two postgres users in play here, the system user and 
the database one. Which one are you referring to?





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

Bad Query Plans on 10.3 vs 9.6

[Cory Tucker]

Hello all.  I'm migrating a database from PG 9.6 to 10.3 and have noticed a
particular query that is performing very badly compared to its 9.6
counterpart.

The plan on 9.6 v 10.3 are effectively identical except in 9.6 the planner
decides to use an index only scan on the primary key and in 10.3 it does a
sequential scan.  The problem is the sequential scan is for a table of 75M
rows and 25 columns so its quiet a lot of pages it has to traverse.

This is the query:

explain verbose
WITH removed AS (
  DELETE FROM match m
  WHERE
NOT EXISTS (
SELECT 1
FROM build.household h  -- This is the table that has 70M rows and
does a full table scan in 10.3
WHERE h.household_id = m.household_id
) OR (
  m.property_id IS NOT NULL AND
  NOT EXISTS (
  SELECT 1
  FROM build.property p
  WHERE p.household_id = m.household_id AND p.property_id =
m.property_id
  )
)
  RETURNING *
)
INSERT INTO orphaned_matches (household_id, account_id, candidate_id,
matched_at, full_name, first_name, last_name, match_reason, property_id,
owner_id)
  SELECT
removed.household_id,
removed.account_id,
removed.candidate_id,
removed.created_at,
removed.full_name,
removed.first_name,
removed.last_name,
removed.match_reason,
removed.property_id,
removed.owner_id
  FROM removed;


What's worse is that in 10.3, the number of rows is actually much smaller
than in 9.6 because I am doing this query on a partitioned table (table
name "match") with a reduced data set.

Query plans for both are attached, plus the query.

thanks
--Cory
 Insert on public.orphaned_matches  (cost=204030825.83..204247350.03 
rows=8660968 width=264)
   CTE removed
 ->  Delete on public.match m  (cost=0.00..204030825.83 rows=8660968 
width=6)
   Output: m.id, m.created_at, m.modified_at, m.household_id, 
m.property_id, m.match_reason, m.full_name, m.first_name, m.middle_name, 
m.last_name, m.account_id, m.candidate_id, m.match_category, m.confidence, 
m.owner_id, m.match_resource
   ->  Seq Scan on public.match m  (cost=0.00..204030825.83 
rows=8660968 width=6)
 Output: m.ctid
 Filter: ((NOT (SubPlan 1)) OR ((m.property_id IS NOT NULL) AND 
(NOT (SubPlan 2
 SubPlan 1
   ->  Index Only Scan using uq_household_id on build.household 
h  (cost=0.57..8.59 rows=1 width=0)
 Index Cond: (h.household_id = (m.household_id)::text)
 SubPlan 2
   ->  Index Scan using property_property_id_idx on 
build.property p  (cost=0.57..8.59 rows=1 width=0)
 Index Cond: (p.property_id = m.property_id)
 Filter: (p.household_id = (m.household_id)::text)
   ->  CTE Scan on removed  (cost=0.00..216524.20 rows=8660968 width=264)
 Output: nextval('orphaned_matches_id_seq'::regclass), now(), 
removed.household_id, removed.account_id, removed.candidate_id, 
removed.created_at, removed.full_name, removed.first_name, removed.last_name, 
removed.match_reason, removed.property_id, removed.owner_id
(16 rows)
 Insert on match.orphaned_matches  (cost=1823761513653.51..1823761525043.91 
rows=455616 width=380)
   CTE removed
 ->  Delete on match m  (cost=0.00..1823761513653.51 rows=455616 width=6)
   Output: m.id, m.created_at, m.modified_at, m.household_id, 
m.account_id, m.candidate_id, m.match_reason, m.property_id, m.full_name, 
m.first_name, m.middle_name, m.last_name, m.match_category, m.confidence, 
m.owner_id, m.match_resource
   ->  Seq Scan on match m  (cost=0.00..1823761513653.51 rows=455616 
width=6)
 Output: m.ctid
 Filter: ((NOT (SubPlan 1)) OR ((m.property_id IS NOT NULL) AND 
(NOT (SubPlan 2
 SubPlan 1
   ->  Seq Scan on build.household h  (cost=0.00..2948996.80 
rows=1 width=0)
 Filter: (h.household_id = (m.household_id)::text)
 SubPlan 2
   ->  Index Scan using uq_idx_property_id on build.property p  
(cost=0.57..2.59 rows=1 width=0)
 Index Cond: (p.property_id = m.property_id)
 Filter: (p.household_id = (m.household_id)::text)
   ->  CTE Scan on removed  (cost=0.00..11390.40 rows=455616 width=380)
 Output: nextval('orphaned_matches_id_seq'::regclass), now(), 
removed.household_id, removed.account_id, removed.candidate_id, 
removed.created_at, removed.full_name, removed.first_name, removed.last_name, 
removed.match_reason, removed.property_id, removed.owner_id
(16 rows)


query.sql
Description: Binary data

Re: Using Lateral

[Johann Spies]

Thanks David and Paul,

You have helped me a lot.

Regards
Johann.

On 28 March 2018 at 20:49, David G. Johnston  wrote:
> On Tuesday, March 27, 2018, Johann Spies  wrote:
>>
>> In the past I could use this in a query:
>>
>> SELECT
>> DISTINCT ut,
>> CASE
>> WHEN xpath_exists ('//t:address_spec/t:country/text()',
>> q.address_spec,
>> p.ns)
>> THEN unnest (xpath ('//t:address_spec/t:country/text()',
>> q.address_spec,
>> p.ns))::citext
>> ELSE NULL
>> END country,
>> [...]
>> Just selecting "unnest(...)" gives the wrong result because if the
>> xpath does not exist all the other parts of the query (like 'ut' in
>> this case) also are empty.
>
>
> You should be able to solve the empty-set-in-target-list problem via a
> scalar subquery instead of a case construct.
>
> Select distinct ut, (select unnest(...)) as country from ...
>
> The subselect wil return null if fed zero rows. Though you will still have
> to solve an issue if the unrest returns 1+ rows.
>
> In lieu of the inline scalar subquery I would advise writing a function and
> just calling it directly in the target-list.  But it should not return
> setof, I'd return an array if you need to accept the possibility of 1+
> matches, and return an empty array for zero matches.
>
> David J.



-- 
Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)

Re: Bad Query Plans on 10.3 vs 9.6

[David Rowley]

On 29 March 2018 at 18:26, Cory Tucker  wrote:
> Hello all.  I'm migrating a database from PG 9.6 to 10.3 and have noticed a
> particular query that is performing very badly compared to its 9.6
> counterpart.
>
> The plan on 9.6 v 10.3 are effectively identical except in 9.6 the planner
> decides to use an index only scan on the primary key and in 10.3 it does a
> sequential scan.  The problem is the sequential scan is for a table of 75M
> rows and 25 columns so its quiet a lot of pages it has to traverse.

How certain are you that all the indexes match on each instance?

It would be useful to see psql's \d output for each table in question.

Another option for you to consider would be to get rid of the OR
clause in the query entirely and have two separate CTEs and INSERT
INTO your orphaned_matches table having SELECTed from both CTEs with a
UNION ALL. A record already deleted won't appear in the 2nd branch of
the UNION ALL result. However, that still might not fix your issue
with the index not being used, but you may find the anti-joined
version of the query is faster anyway.

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services