RE: primary key and unique index

[HORDER Phil]

Some databases will create a unique index for you when you create a primary key.
Oracle will create one, but only if you haven’t already done that.

Postgres will ALWAYS create a unique index based on the primary key – so you 
should never do that as well, or the db will have to maintain two identical 
indexes.

(When we migrated our db design from Oracle we ended up with lots of duplicate 
indexes).

SQL> Select * from pg_indexes order by schemaname, tablename;

Phil Horder
Database Mechanic

Thales
Land and Air Systems
Horizon House, Throop Road, Templecombe, Somerset, BA8 0DH, UK
www.thalesgroup.com/uk

Tel: +44 (0) 1963 37 2041
Email: phil.hor...@uk.thalesgroup.com

Please consider the environment before printing a hard copy of this email.

The information contained in this e-mail is confidential. It is intended only 
for the stated addressee(s) and access to it by any other person is 
unauthorised. If you are not an addressee, you must not disclose, copy, 
circulate or in any other way use or rely on the information contained in this 
e-mail. Such unauthorised use may be unlawful. If you have received this e-mail 
in error, please inform us immediately on +44 (0)1963 370511 and delete it and 
all copies from your system.

Thales UK Limited. A company registered in England and Wales. Registered 
Office: 350 Longwater Avenue, Green Park, Reading, RG2 6GF. Registered Number: 
868273

From: Thomas Poty [mailto:thomas.p...@gmail.com]
Sent: 23 March 2018 07:56
To: pgsql-general@lists.postgresql.org
Subject: primary key and unique index

Hi all,
I am migrating fromMySQL to Postgresql 9.6.
In MySQL a  "show create table" gives me :
 ...
  PRIMARY KEY (`ID`,`CountryCode`,`LanguageCode`),
  UNIQUE KEY `unique_my table_4` (`ID`,`CountryCode`,`LanguageCode`),
...
So, In PostgreSQL, does it make sense to create a primary key AND a unique 
index based on the same columns?
Is PostgreSQL smart enough to use the unique index created for the primary key.
I know PostgreSQL can be based on a unique index to create a primary key but I 
also know it is possible to create several indexes on the same columns with the 
same order.
Thanks
Thomas

Re: primary key and unique index

[Thomas Poty]

Many thanks Phil for complementary information .

Le sam. 24 mars 2018 à 09:53, HORDER Phil 
a écrit :

> Some databases will create a unique index for you when you create a
> primary key.
>
> Oracle will create one, but only if you haven’t already done that.
>
>
>
> Postgres will ALWAYS create a unique index based on the primary key – so
> you should never do that as well, or the db will have to maintain two
> identical indexes.
>
>
>
> (When we migrated our db design from Oracle we ended up with lots of
> duplicate indexes).
>
>
>
> SQL> Select * from pg_indexes order by schemaname, tablename;
>
>
>
> Phil Horder
>
> Database Mechanic
>
>
>
> Thales
>
> Land and Air Systems
>
> Horizon House, Throop Road, Templecombe, Somerset, BA8 0DH, UK
>
> www.thalesgroup.com/uk
>
>
>
> Tel: +44 (0) 1963 37 2041
>
> Email: phil.hor...@uk.thalesgroup.com
>
>
>
> *Please consider the environment before printing a hard copy of this
> email.*
>
>
>
> The information contained in this e-mail is confidential. It is intended
> only for the stated addressee(s) and access to it by any other person is
> unauthorised. If you are not an addressee, you must not disclose, copy,
> circulate or in any other way use or rely on the information contained in
> this e-mail. Such unauthorised use may be unlawful. If you have received
> this e-mail in error, please inform us immediately on +44 (0)1963 370511
> and delete it and all copies from your system.
>
>
>
> *Thales UK Limited. A company registered in England and Wales. Registered
> Office: 350 Longwater Avenue, Green Park, Reading, RG2 6GF. Registered
> Number: 868273*
>
>
>
> *From:* Thomas Poty [mailto:thomas.p...@gmail.com]
> *Sent:* 23 March 2018 07:56
> *To:* pgsql-general@lists.postgresql.org
> *Subject:* primary key and unique index
>
>
>
> Hi all,
>
> I am migrating fromMySQL to Postgresql 9.6.
>
> In MySQL a  "show create table" gives me :
>  ...
>   PRIMARY KEY (`ID`,`CountryCode`,`LanguageCode`),
>   UNIQUE KEY `unique_my table_4` (`ID`,`CountryCode`,`LanguageCode`),
> ...
>
> So, In PostgreSQL, does it make sense to create a primary key AND a unique
> index based on the same columns?
>
> Is PostgreSQL smart enough to use the unique index created for the primary
> key.
>
> I know PostgreSQL can be based on a unique index to create a primary key
> but I also know it is possible to create several indexes on the same
> columns with the same order.
>
> Thanks
>
> Thomas
>

Re: Foreign Key locking / deadlock issue.... v2

[rob stone]

Hello Phil,

On Fri, 2018-03-23 at 15:42 +, HORDER Phil wrote:
> Rob, thanks for looking.
>  
> The "pause" is only to not-do-the-commit yet, so that the child
> process can then try and access the record - I've not left anything
> out.
> This code is my own demo, not a cut from our production code.
>  
> Did you run this as the 'postgres' superuser?  That would bypass the
> RLS, and probably avoid the problem.

No. Never use super user to test scripts.

>  
> I checked by creating a new user, and ran my code in that:
>  
> Sql> create user test password 'password';
>  
> After running my test script, psql \dp shows:
>  
> Schema |   Name| Type  |Access privileges   
> | Column privileges | Policies
> +---+---+--
> ---+---+--
> public | eln   | table |
> |   |
> public | pl| table |
> |   | security_policy:+
> |   |   |  
>   |   |   (u): true +
> |   |   |
> |   |   (c): true
>  
> (plus some other stuff for postGIS)
>  
> Here’s my code again:
>  
> drop table if exists ELN;
> drop table if exists PL;
>  
> Create table PL
> (pl_id integer,
> m_number text
> );
>  
> alter table PL ENABLE row level security;
> alter table PL FORCE row level security;
>  
> drop policy if exists security_policy on PL ;
> CREATE POLICY security_policy on PL FOR ALL TO PUBLIC USING (true)
> WITH CHECK (true);
>  
> Alter table PL add constraint PL_PK primary key (pl_id);
> Insert into PL values (1, null);
> Insert into PL values (2, null);
> Insert into PL values (3, null);
>  
> Create table ELN
> (event_id integer,
> pl_id integer
> );
>  
>  
> Alter table ELN add constraint ELN_PK primary key (event_id);
> Alter table ELN add constraint ELN_PL_FK foreign key (pl_id)
> references PL (pl_id);
>  
> Insert into ELN values (301, null);
> Insert into ELN values (302, null);
> Insert into ELN values (303, null);
>  
> commit;
>  
> -- process 1:
> start transaction;
> update pl set m_number = '234' where pl_id = 2;   
> update pl set m_number = '345' where pl_id = 3;  
>  
> -- wait here, and run process 2

This was never explained. You are running "process 2" in an entirely
different session

If that is true, why don't you commit the updates to table pl and
release the locks?
The fact that eln.pl_id is a foreign key is irrelevant. If you tried to
alter eln.pl_id to a value that doesn't exist in pl.pl_id will cause an
exception to occur due to the constraint.

Cheers,
Rob

> commit;
>  
>  
> -- process 2:
> start transaction;
> update eln set pl_id = 3 where event_id = 303; 
> update eln set pl_id = 2 where event_id = 302;
> -- Gets blocked by process 1
> commit;
>  
>  
> Phil Horder
> Database Mechanic
>  
> -Original Message-
> From: rob stone [mailto:floripa...@gmail.com] 
> Sent: 23 March 2018 11:43
> To: HORDER Phil; pgsql-general
> Subject: Re: Foreign Key locking / deadlock issue v2
>  
> Hello Phil,
>  
> I've run your sample script on 9.6.5 and 10.3.
> The only thing that I added was a commit; after the initial inserts
> just to ensure the rows were saved.
> No errors were reported for either version.
>  
> The output of \dp after running was:-
>  
> Access privileges  Schema | Name |
> Type  | Access privileges | Column privileges
> | Policies
> +--+---+---+---+-
> --
> ---
> public | eln  | table |   |   |
>  public | pl   | table |   |   |
> security_policy:+
> |  |   |   |   |  
> (u):
> true
>  
>  
> --> including the FOR ALL in the create policy statement as well as
> WITH CHECK(true).
>  
>Access privileges  Schema | Name |
> Type  | Access privileges | Column privileges
> | Policies
> +--+---+---+---+-
> --
> ---
> public | eln  | table |   |   |
>  public | pl   | table |   |   |
> security_policy:+
> |  |   |   |   |  
> (u):
> true +
> |  |   |   |   |  
> (c):
> true
>  
>  
> The only mystery is what happens here:-
>  
> 
>  
> -- …. Pause while other processing happens …..
> (commit;)
>  
> -- Child table processing – occurs often & quickly. Starts after
> parent update.
>  
> <\snip>
>  
>  
> I'd like to know more about RLS and trying to de-bug your script.
>  
> On a production a

Re: case and accent insensitive

[Andreas Kretschmer]

Am 23.03.2018 um 23:04 schrieb MOISES ESPINOSA:
I don't know how i could reproduced case insensitive and accent 
insensitive.


Maybe you can use lower() for case insensitive or citext for the same 
(https://www.postgresql.org/docs/10/static/citext.html) and the 
unaccent-extension
for the accent insensitive 
(https://www.postgresql.org/docs/10/static/unaccent.html)


*untested*


Regards, Andreas

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

RE: Foreign Key locking / deadlock issue.... v2

[HORDER Phil]

> This was never explained. You are running "process 2" in an entirely 
> different session

Yes, two processes are required to get a deadlock.

> If that is true, why don't you commit the updates to table pl and release the 
> locks?

It's a long story... but I can't change it at the moment, the PL update is a 
single long-running batch process, which shouldn't get in the way of the ELN 
process.

> The fact that eln.pl_id is a foreign key is irrelevant. If you tried to alter 
> eln.pl_id to a value that doesn't exist in pl.pl_id will cause an exception 
> to occur due to the constraint.

I don't see how this is irrelevant, it's the FK check against the PL table that 
is causing the lock contention.
We're not getting FK violations - the data is being constructed correctly.

The issue I have is that the FK check is being blocked by an apparently 
upgraded lock on the PL table, caused by the RLS policy.

I'm running Postgres 9.6.1 (I think)


Phil Horder
Database Mechanic


-Original Message-
From: rob stone [mailto:floripa...@gmail.com] 
Sent: 24 March 2018 11:30
To: HORDER Phil; pgsql-general
Subject: Re: Foreign Key locking / deadlock issue v2

Hello Phil,

On Fri, 2018-03-23 at 15:42 +, HORDER Phil wrote:
> Rob, thanks for looking.
>  
> The "pause" is only to not-do-the-commit yet, so that the child 
> process can then try and access the record - I've not left anything 
> out.
> This code is my own demo, not a cut from our production code.
>  
> Did you run this as the 'postgres' superuser?  That would bypass the 
> RLS, and probably avoid the problem.

No. Never use super user to test scripts.

>  
> I checked by creating a new user, and ran my code in that:
>  
> Sql> create user test password 'password';
>  
> After running my test script, psql \dp shows:
>  
> Schema |   Name| Type  |Access privileges   
> | Column privileges | Policies
> +---+---+--
> ---+---+--
> public | eln   | table |
> |   |
> public | pl| table |
> |   | security_policy:+
> |   |   |  
>   |   |   (u): true +
> |   |   |
> |   |   (c): true
>  
> (plus some other stuff for postGIS)
>  
> Here’s my code again:
>  
> drop table if exists ELN;
> drop table if exists PL;
>  
> Create table PL
> (pl_id integer,
> m_number text
> );
>  
> alter table PL ENABLE row level security; alter table PL FORCE row 
> level security;
>  
> drop policy if exists security_policy on PL ; CREATE POLICY 
> security_policy on PL FOR ALL TO PUBLIC USING (true) WITH CHECK 
> (true);
>  
> Alter table PL add constraint PL_PK primary key (pl_id); Insert into 
> PL values (1, null); Insert into PL values (2, null); Insert into PL 
> values (3, null);
>  
> Create table ELN
> (event_id integer,
> pl_id integer
> );
>  
>  
> Alter table ELN add constraint ELN_PK primary key (event_id); Alter 
> table ELN add constraint ELN_PL_FK foreign key (pl_id) references PL 
> (pl_id);
>  
> Insert into ELN values (301, null);
> Insert into ELN values (302, null);
> Insert into ELN values (303, null);
>  
> commit;
>  
> -- process 1:
> start transaction;
> update pl set m_number = '234' where pl_id = 2;   
> update pl set m_number = '345' where pl_id = 3;  
>  
> -- wait here, and run process 2

This was never explained. You are running "process 2" in an entirely different 
session

If that is true, why don't you commit the updates to table pl and release the 
locks?
The fact that eln.pl_id is a foreign key is irrelevant. If you tried to alter 
eln.pl_id to a value that doesn't exist in pl.pl_id will cause an exception to 
occur due to the constraint.

Cheers,
Rob

> commit;
>  
>  
> -- process 2:
> start transaction;
> update eln set pl_id = 3 where event_id = 303; 
> update eln set pl_id = 2 where event_id = 302;
> -- Gets blocked by process 1
> commit;
>  
>  
> Phil Horder
> Database Mechanic
>  
> -Original Message-
> From: rob stone [mailto:floripa...@gmail.com]
> Sent: 23 March 2018 11:43
> To: HORDER Phil; pgsql-general
> Subject: Re: Foreign Key locking / deadlock issue v2
>  
> Hello Phil,
>  
> I've run your sample script on 9.6.5 and 10.3.
> The only thing that I added was a commit; after the initial inserts 
> just to ensure the rows were saved.
> No errors were reported for either version.
>  
> The output of \dp after running was:-
>  
> Access privileges  Schema | Name | 
> Type  | Access privileges | Column privileges
> | Policies
> +--+---+---+---+-
> --
> ---
> public | eln  | t

Re: Foreign Key locking / deadlock issue.... v2

[Adrian Klaver]

On 03/24/2018 11:03 AM, HORDER Phil wrote:


I'm running Postgres 9.6.1 (I think)


To find out for sure do:

psql> select version();





Phil Horder
Database Mechanic



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

Re: Troubleshooting a segfault and instance crash

[Blair Boadway]

Following up on this thread, we removed pgaudit from the system to eliminate on 
variable (removed from postgres.conf including shared_preload_libraries) but 
after a couple of weeks of success we hit the segfault again.  Again it 
happened while running some DDL (object grants).  This time we were configured 
to harvest a core file, which gave us a small bit of info:

gdb -q -c core /usr/pgsql-9.6/bin/postgres
Reading symbols from /usr/pgsql-9.6/bin/postgres...(no debugging symbols 
found)...done.

Core was generated by `postgres: batch_user_account'.
Program terminated with signal 11, Segmentation fault.
#0  0x00386712868a in __strcmp_sse42 () from /lib64/libc.so.6
Missing separate debuginfos, use: debuginfo-install 
postgresql96-server-9.6.5-1PGDG.rhel6.x86_64

That wasn’t really enough information to tell me what the problem.  Did not 
have success with installing debuginfo:

Could not find debuginfo for main pkg: 
postgresql96-server-9.6.5-1PGDG.rhel6.x86_64

Not sure how useful that would be to dig further on.  So it doesn’t seem 
pgaudit is the culprit but not sure what to make of the strcmp error.

-Blair


From: Jan Bilek 
Date: Thursday, March 8, 2018 at 2:56 PM
To: "pavel.steh...@gmail.com" , Blair Boadway 

Cc: "pgsql-gene...@postgresql.org" 
Subject: RE: Troubleshooting a segfault and instance crash

Hi Blair, Pavel,

we are using procedure described in https://access.redhat.com/solutions/4896  
to automate crash detail collection for our production systems on RHEL 7.

Perhaps something like this can help on your side.

Kind Regards,
Jan

On 2018-03-09 04:35:05+10:00 Pavel Stehule wrote:


2018-03-08 19:16 GMT+01:00 Blair Boadway 
mailto:bboad...@abebooks.com>>:
Hi Pavel,

I don’t have a core yet, the only way I have now is to intentionally crash the 
prod system a couple of times.  Haven’t resorted to that yet.
hard to help without backtrace - and then you need core dump


Interesting you mentioned pgaudit—it is installed on this system because that 
is a our standard installation but on this particular system we haven’t yet 
needed audits so the audit role is ‘empty’.  (And on a different system with 
same installation and heavy of audit we’ve seen no segfaults)
other extensions are simply or without relation to DDL or well known. So 
pgaudit is best candidate - but the error can be anywhere

Regards

Pavel
On this system

pgaudit.role = 'auditor'
pgaudit.log_parameter = off
pgaudit.log_catalog = off
pgaudit.log_statement_once = on
pgaudit.log_level = log

select * from information_schema.role_table_grants where grantee = 'auditor';
(0 rows)

thanks, Blair

From: Pavel Stehule mailto:pavel.steh...@gmail.com>>
Date: Thursday, March 8, 2018 at 9:49 AM
To: Blair Boadway mailto:bboad...@abebooks.com>>
Cc: "pgsql-gene...@postgresql.org" 
mailto:pgsql-gene...@postgresql.org>>
Subject: Re: Troubleshooting a segfault and instance crash
Hi

2018-03-08 18:40 GMT+01:00 Blair Boadway 
mailto:bboad...@abebooks.com>>:
Hello,

We’re seeing an occasional segfault on a particular database

Mar  7 14:46:35 pgprod2 kernel:postgres[29351]: segfault at 0 ip 
00302f32868a sp 7ffcf1547498 error 4 in 
libc-2.12.so[302f20+18a000]
Mar  7 14:46:35 pgprod2 POSTGRES[21262]: [5] user=,db=,app=client= LOG:  server 
process (PID 29351) was terminated by signal 11: Segmentation fault

It crashes the database, though it starts again on its own without any apparent 
issues.  This has happened 3 times in 2 months and each time the segfault error 
and memory address is the same. We’ve only seen it on one database, though 
we’ve seen it on both hosts of primary/standby setup—we switched over primary 
to other host and got a segfault there, which seems to eliminate a hardware 
issue.  Oddly the database has no issues for normal DML workloads (it is a 
moderately busy prod oltp system) but the segfault has happened very shortly 
after DML changes are made.  Most recently it happened while running a series 
of grants for new db users we were deploying (ie. running a sql script from 
psql on the primary host)

grant usage on schema app to app_user1;
grant usage on schema app to app_user2;
...

Our set up is
RHEL 6.9  - 2.6.32-696.16.1.el6.x86_64
PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 
(Red Hat 4.4.7-18), 64-bit
Extensions - 
pg_cron,repmgr_funcs,pgaudit,pg_stat_statements,pg_hint_plan,pglogical

So far can’t reproduce on a test system, have just added some OS config to 
collect core from the OS but haven’t collected a core yet.  There isn’t any 
particular config change or extension that we can link to the problem, this is 
a system that has run for months without problems since last config changes.  
Appreciate any ideas.
can you get core dump? It can be pgaudit bug maybe? It is complex extension.
Regards
Pavel

Regards,
Blair

Re: Troubleshooting a segfault and instance crash

[Peter Geoghegan]

On Thu, Mar 8, 2018 at 9:40 AM, Blair Boadway  wrote:
> Mar  7 14:46:35 pgprod2 kernel:postgres[29351]: segfault at 0 ip
> 00302f32868a sp 7ffcf1547498 error 4 in
> libc-2.12.so[302f20+18a000]
>
> Mar  7 14:46:35 pgprod2 POSTGRES[21262]: [5] user=,db=,app=client= LOG:
> server process (PID 29351) was terminated by signal 11: Segmentation fault

> It crashes the database, though it starts again on its own without any
> apparent issues.  This has happened 3 times in 2 months and each time the
> segfault error and memory address is the same.

We had a recent report of a segfault on a Redhat compatible system,
that seemed like it might originate from within its glibc [1].
Although all the versions there didn't match what you have, it's worth
considering as a possibility.

Maybe you can't install debuginfo packages because you don't yet have
the necessary debuginfo repos set up. Just a guess. That is sometimes
a required extra step.

[1] https://postgr.es/m/7369.1520528...@sss.pgh.pa.us
--
Peter Geoghegan

Re: Troubleshooting a segfault and instance crash

[Blair Boadway]

Thanks for the tip.  We are using RHEL 6.9 and definitely up to date on glibc 
(2.12-1.209.el6_9.2).  We also have the same versions on a very similar system 
with no segfault.

My colleague got a better backtrace that shows another extension

Core was generated by `postgres: batch_user_account''.
Program terminated with signal 11, Segmentation fault.
#0 0x00386712868a in __strcmp_sse42 () from /lib64/libc.so.6
Missing separate debuginfos, use: debuginfo-install 
postgresql96-server-9.6.5-1PGDG.rhel6.x86_64
(gdb) bt
#0 0x00386712868a in __strcmp_sse42 () from /lib64/libc.so.6
#1 0x7fa3f0c7074c in get_query_string (pstate=, 
query=, jumblequery=) at 
pg_hint_plan.c:1882
#2 0x7fa3f0c70a5d in pg_hint_plan_post_parse_analyze (pstate=0x25324b8, 
query=0x25325e8) at pg_hint_plan.c:2875
#3 0x005203bc in parse_analyze ()
#4 0x006df933 in pg_analyze_and_rewrite ()
#5 0x007c6f6b in ?? ()
#6 0x007c6ff0 in CachedPlanGetTargetList ()
#7 0x006e173a in PostgresMain ()
#8 0x006812f5 in PostmasterMain ()
#9 0x00609278 in main ().


We aren’t sure if this indicates that pg_hint_plan is causing the segfault or 
if it happened to be doing something when the segfault occurred.  We aren’t 
actually using pg_hint_plan hints in this system so we’re not sure how all this 
relates to segfault when another process does a ‘grant usage on schema abc to 
user xyz;’ unrelated to the account segfaulting.

Short of better ideas, we will pull the pg_hint_plan extension and see if that 
removes the problem.

-Blair





From: Peter Geoghegan 
Date: Saturday, March 24, 2018 at 4:18 PM
To: Blair Boadway 
Cc: "pgsql-gene...@postgresql.org" 
Subject: Re: Troubleshooting a segfault and instance crash

On Thu, Mar 8, 2018 at 9:40 AM, Blair Boadway 
mailto:bboad...@abebooks.com>> wrote:
Mar  7 14:46:35 pgprod2 kernel:postgres[29351]: segfault at 0 ip
00302f32868a sp 7ffcf1547498 error 4 in
libc-2.12.so[302f20+18a000]

Mar  7 14:46:35 pgprod2 POSTGRES[21262]: [5] user=,db=,app=client= LOG:
server process (PID 29351) was terminated by signal 11: Segmentation fault

It crashes the database, though it starts again on its own without any
apparent issues.  This has happened 3 times in 2 months and each time the
segfault error and memory address is the same.

We had a recent report of a segfault on a Redhat compatible system,
that seemed like it might originate from within its glibc [1].
Although all the versions there didn't match what you have, it's worth
considering as a possibility.

Maybe you can't install debuginfo packages because you don't yet have
the necessary debuginfo repos set up. Just a guess. That is sometimes
a required extra step.

[1] https://postgr.es/m/7369.1520528...@sss.pgh.pa.us
--
Peter Geoghegan

RE: Troubleshooting a segfault and instance crash

[Jan Bilek]

Hi Blair,

In regards of that debug package i found it here: 
http://cbs.centos.org/koji/buildinfo?buildID=20425 , see 
http://cbs.centos.org/kojifiles/packages/rh-postgresql96-postgresql/9.6.5/1.el7/x86_64/rh-postgresql96-postgresql-debuginfo-9.6.5-1.el7.x86_64.rpm

However I have very little experience with it to provide more instructions.

There is also an option to get your own debugging symbols from building 
postgresql server with those and stripping them. I've found pretty good example 
here: 
http://marcioandreyoliveira.blogspot.com.au/2008/03/how-to-debug-striped-programs-with-gdb.html

Finally, looking into that Seg. Fault, strcmp is pretty common-nasty error. 
Most probably buffer overflow, where RHEL is more sensitive OS on this then the 
others. This is where postgresql pays a toll for being written in C and not 
e.g. C++. It can be practically anywhere.

Anyway, by being able to link those debugging symbols to your core dump, we 
should immediately see where it is and you'll do a great help to the community. 
I'm sure that then Pavel will be able to issue a fix in a matter of minutes ;)

Kind Regards,
Jan

--
Jan Bilek

CTO, EFTLab

M: +61 (0) 498 103 179
E:  jan.bi...@eftlab.com.au
A: 109 Brighton Road, Sandgate, QLD 4017

IMPORTANT NOTICE
This message contains confidential information and is intended only for the 
addressee(s). E-mail transmission cannot be guaranteed to be secure or 
error-free as information could be intercepted, corrupted, lost, destroyed, 
arrive late or incomplete, or contain viruses. EFTlab Pty Ltd cannot accept 
liability for any errors or omissions in the contents of this message, which 
may arise as a result of e-mail transmission. Please note that EFTlab Pty Ltd 
may monitor, analyse and archive email traffic, data and the content of email 
for the purposes of security, legal compliance and staff training. If you have 
received this email in error please notify us at 
supp...@eftlab.com.au.


On 2018-03-25 08:44:21+10:00 Blair Boadway wrote:
Following up on this thread, we removed pgaudit from the system to eliminate on 
variable (removed from postgres.conf including shared_preload_libraries) but 
after a couple of weeks of success we hit the segfault again.  Again it 
happened while running some DDL (object grants).  This time we were configured 
to harvest a core file, which gave us a small bit of info:

gdb -q -c core /usr/pgsql-9.6/bin/postgres
Reading symbols from /usr/pgsql-9.6/bin/postgres...(no debugging symbols 
found)...done.

Core was generated by `postgres: batch_user_account'.
Program terminated with signal 11, Segmentation fault.
#0  0x00386712868a in __strcmp_sse42 () from /lib64/libc.so.6
Missing separate debuginfos, use: debuginfo-install 
postgresql96-server-9.6.5-1PGDG.rhel6.x86_64

That wasn’t really enough information to tell me what the problem.  Did not 
have success with installing debuginfo:

Could not find debuginfo for main pkg: 
postgresql96-server-9.6.5-1PGDG.rhel6.x86_64

Not sure how useful that would be to dig further on.  So it doesn’t seem 
pgaudit is the culprit but not sure what to make of the strcmp error.

-Blair


From: Jan Bilek 
Date: Thursday, March 8, 2018 at 2:56 PM
To: "pavel.steh...@gmail.com" , Blair Boadway 

Cc: "pgsql-gene...@postgresql.org" 
Subject: RE: Troubleshooting a segfault and instance crash

Hi Blair, Pavel,

we are using procedure described in https://access.redhat.com/solutions/4896  
to automate crash detail collection for our production systems on RHEL 7.

Perhaps something like this can help on your side.

Kind Regards,
Jan

On 2018-03-09 04:35:05+10:00 Pavel Stehule wrote:


2018-03-08 19:16 GMT+01:00 Blair Boadway 
mailto:bboad...@abebooks.com>>:
Hi Pavel,

I don’t have a core yet, the only way I have now is to intentionally crash the 
prod system a couple of times.  Haven’t resorted to that yet.
hard to help without backtrace - and then you need core dump


Interesting you mentioned pgaudit—it is installed on this system because that 
is a our standard installation but on this particular system we haven’t yet 
needed audits so the audit role is ‘empty’.  (And on a different system with 
same installation and heavy of audit we’ve seen no segfaults)
other extensions are simply or without relation to DDL or well known. So 
pgaudit is best candidate - but the error can be anywhere

Regards

Pavel
On this system

pgaudit.role = 'auditor'
pgaudit.log_parameter = off
pgaudit.log_catalog = off
pgaudit.log_statement_once = on
pgaudit.log_level = log

select * from information_schema.role_table_grants where grantee = 'auditor';
(0 rows)

thanks, Blair

From: Pavel Stehule mailto:pavel.steh...@gmail.com>>
Date: Thursday, March 8, 2018 at 9:49 AM
To: Blair Boadway mailto:bboad...@abebooks.com>>
Cc: "pgsql-gene...@postgresql.org" 
mailto:pgsql-gene...@postgresql.org>>
Subject: Re: Troubleshooting a segfault a

Re: Troubleshooting a segfault and instance crash

[Pavel Stehule]

2018-03-25 0:41 GMT+01:00 Blair Boadway :

> Thanks for the tip.  We are using RHEL 6.9 and definitely up to date on
> glibc (2.12-1.209.el6_9.2).  We also have the same versions on a very
> similar system with no segfault.
>
>
>
> My colleague got a better backtrace that shows another extension
>
>
>
> Core was generated by `postgres: batch_user_account''.
>
> Program terminated with signal 11, Segmentation fault.
>
> #0 0x00386712868a in __strcmp_sse42 () from /lib64/libc.so.6
>
> Missing separate debuginfos, use: debuginfo-install
> postgresql96-server-9.6.5-1PGDG.rhel6.x86_64
>
> (gdb) bt
>
> #0 0x00386712868a in __strcmp_sse42 () from /lib64/libc.so.6
>
> #1 0x7fa3f0c7074c in get_query_string (pstate=,
> query=, jumblequery=) at
> pg_hint_plan.c:1882
>
> #2 0x7fa3f0c70a5d in pg_hint_plan_post_parse_analyze
> (pstate=0x25324b8, query=0x25325e8) at pg_hint_plan.c:2875
>
> #3 0x005203bc in parse_analyze ()
>
> #4 0x006df933 in pg_analyze_and_rewrite ()
>
> #5 0x007c6f6b in ?? ()
>
> #6 0x007c6ff0 in CachedPlanGetTargetList ()
>
> #7 0x006e173a in PostgresMain ()
>
> #8 0x006812f5 in PostmasterMain ()
>
> #9 0x00609278 in main ().
>
>
>
>
>
> We aren’t sure if this indicates that pg_hint_plan is causing the segfault
> or if it happened to be doing something when the segfault occurred.  We
> aren’t actually using pg_hint_plan hints in this system so we’re not sure
> how all this relates to segfault when another process does a ‘grant usage
> on schema abc to user xyz;’ unrelated to the account segfaulting.
>

although you don't use pg_hint_plan explicitly, pg_hint_plan is active - it
is active via planner callbacks


>
>
> Short of better ideas, we will pull the pg_hint_plan extension and see if
> that removes the problem.
>

please, try to report this back trace to pg_hint_plan authors.

Regards

Pavel


>
>
> -Blair
>
>
>
>
>
>
>
>
>
>
>
> *From: *Peter Geoghegan 
> *Date: *Saturday, March 24, 2018 at 4:18 PM
> *To: *Blair Boadway 
> *Cc: *"pgsql-gene...@postgresql.org" 
> *Subject: *Re: Troubleshooting a segfault and instance crash
>
>
>
> On Thu, Mar 8, 2018 at 9:40 AM, Blair Boadway 
> wrote:
>
> Mar  7 14:46:35 pgprod2 kernel:postgres[29351]: segfault at 0 ip
>
> 00302f32868a sp 7ffcf1547498 error 4 in
>
> libc-2.12.so[302f20+18a000]
>
>
>
> Mar  7 14:46:35 pgprod2 POSTGRES[21262]: [5] user=,db=,app=client= LOG:
>
> server process (PID 29351) was terminated by signal 11: Segmentation fault
>
>
>
> It crashes the database, though it starts again on its own without any
>
> apparent issues.  This has happened 3 times in 2 months and each time the
>
> segfault error and memory address is the same.
>
>
>
> We had a recent report of a segfault on a Redhat compatible system,
>
> that seemed like it might originate from within its glibc [1].
>
> Although all the versions there didn't match what you have, it's worth
>
> considering as a possibility.
>
>
>
> Maybe you can't install debuginfo packages because you don't yet have
>
> the necessary debuginfo repos set up. Just a guess. That is sometimes
>
> a required extra step.
>
>
>
> [1] https://postgr.es/m/7369.1520528...@sss.pgh.pa.us
>
> --
>
> Peter Geoghegan
>
>
>