RE: Troubleshooting a segfault and instance crash

[Jan Bilek]

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 
>:
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 >
Date: Thursday, March 8, 2018 at 9:49 AM
To: Blair Boadway >
Cc: "pgsql-gene...@postgresql.org" 
>
Subject: Re: Troubleshooting a segfault and instance crash
Hi

2018-03-08 18:40 GMT+01:00 Blair Boadway 
>:
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: JDBC connectivity issue

[Dave Cramer]

You should be using the latest version of the driver. What version are you
using ?

Even though you have a 9.4 database the latest version is the correct
version to use.

Dave Cramer

da...@postgresintl.com
www.postgresintl.com

On 8 March 2018 at 22:14, David G. Johnston 
wrote:

> On Thu, Mar 8, 2018 at 2:30 PM, chris  wrote:
>
>> Given that the syntax looks correct for the url, how would we go about
>> debugging that it's not seeing the comma?
>
>
> ​First thing I'd do is ensure the version of the driver I'm using supports
> the feature I'm trying to use.
>
> David J.
>
>

Re: JDBC connectivity issue

[David G. Johnston]

On Thu, Mar 8, 2018 at 2:30 PM, chris  wrote:

> Given that the syntax looks correct for the url, how would we go about
> debugging that it's not seeing the comma?


​First thing I'd do is ensure the version of the driver I'm using supports
the feature I'm trying to use.

David J.

Re: JDBC connectivity issue

[Adrian Klaver]

On 03/08/2018 01:30 PM, chris wrote:
Given that the syntax looks correct for the url, how would we go about 
debugging that it's not seeing the comma?


From previous post:

Can you provide more information about the JDBC version you are using 
and the app code you are using?





On 03/08/2018 02:27 PM, Adrian Klaver wrote:

On 03/08/2018 01:12 PM, chris wrote:

Hi,

I have a JDBC temping to connect to 2 postgres nodes in a hot standby 
configuration. Running postgres 9.4 on centos6.


What we are trying to accomplish is in an event of a failover, to 
first try to connect to the master. If that fails to then reach out 
to the standby.


I looked online and found the suggested way to do this, but it's not 
working.


This is the JDBC line we have:

/jdbc.url=jdbc:postgresql://10.16.10.12:5432,10.16.10.13:5432/app_db/

Here is the error we are getting:/
/

/2018-03-07 13:54:36, 994 ERROR:com.zaxxer.hikari.util.PropertyElf - 
failed to set properly port number on target class 
org.postgresql.ds.PGSimpleDataSource 
java.langNumberFormatException:for input string: 
"5432,10.16.10.13:5432"/


Lets try that again:

To me it looks like whatever code you are using is trying to use 
"5432,10.16.10.13:5432" as the port number for 10.16.10.12. In other 
words it is not seeing the ',' as a separator for the two IP's'.


Time to clean the glasses:)



It looks like the first IP address has disappeared or is not set right./
/

Thanks in advance for the help.


//











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

Re: Prefixing schema name

[Tiffany Thang]

Thanks David and Achilleas. This is no longer an issue. The table
previously went to the public schema. I'm not sure what I did. Anyway,
without a prefix, it defaults to the current schema.

On Thu, Mar 8, 2018 at 2:02 AM, Achilleas Mantzios <
ach...@matrix.gatewaynet.com> wrote:

> On 08/03/2018 01:13, David G. Johnston wrote:
>
> On Wed, Mar 7, 2018 at 4:05 PM, Tiffany Thang 
> wrote:
>
>> ​
>> The search_path configuration works only for queries.
>>
>
> ​Um
>
> ​https://www.postgresql.org/docs/10/static/sql-createschema.html
>
> "​A CREATE command specifying an unqualified object name creates the
> object in the current schema (the one at the front of the search path,
> which can be determined with the function current_schema)"
>
>
> In pg_dump relies heavily on search_path for all CREATE statements.
>
>
>> For example:
>> Is there a way to run the create/insert statements below without
>> prefixing the schema name, user1?​
>>
>
>> As the user, user1:
>> Create table user1.table1 (id int);
>> Insert into user1.table1 values (1);
>>
>
> ​Just omitting "user1" and seeing what happens would be informative.  You
> should find it does exactly what you think - namely because the default
> search_path will cause "user1" to appear first.
>
> Insert is more similar to Select than it is to Create - the object being
> inserted into must already exist
>
> David J.
> ​​
>
>
> --
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
>
>

Re: What is the meaning of pg_restore output?

[Tom Lane]

Adrian Klaver  writes:
> When I restore using 10.2 I see:
> pg_restore: creating ACL "public.TABLE wl_week"
> Do you see something similar?

> My suspicion is that this might have something to do with the commits below:

Yeah, this evidently changed in commits 3eb9a5e7c et al, which made ACL
restore go through restore_toc_entry().  I think I figured that the
"creating ACL" message that restore_toc_entry() would emit made the
dedicated "setting owner and privileges" message redundant.  It had also
been misleading for quite some time, maybe forever: restore of that TOC
entry would set the object's privileges all right, but if it ever had
anything to do with setting the object's ownership, it was a really long
time ago.

regards, tom lane

Re: JDBC connectivity issue

[chris]

Given that the syntax looks correct for the url, how would we go about 
debugging that it's not seeing the comma?



On 03/08/2018 02:27 PM, Adrian Klaver wrote:

On 03/08/2018 01:12 PM, chris wrote:

Hi,

I have a JDBC temping to connect to 2 postgres nodes in a hot standby 
configuration. Running postgres 9.4 on centos6.


What we are trying to accomplish is in an event of a failover, to 
first try to connect to the master. If that fails to then reach out 
to the standby.


I looked online and found the suggested way to do this, but it's not 
working.


This is the JDBC line we have:

/jdbc.url=jdbc:postgresql://10.16.10.12:5432,10.16.10.13:5432/app_db/

Here is the error we are getting:/
/

/2018-03-07 13:54:36, 994 ERROR:com.zaxxer.hikari.util.PropertyElf - 
failed to set properly port number on target class 
org.postgresql.ds.PGSimpleDataSource 
java.langNumberFormatException:for input string: 
"5432,10.16.10.13:5432"/


Lets try that again:

To me it looks like whatever code you are using is trying to use 
"5432,10.16.10.13:5432" as the port number for 10.16.10.12. In other 
words it is not seeing the ',' as a separator for the two IP's'.


Time to clean the glasses:)



It looks like the first IP address has disappeared or is not set right./
/

Thanks in advance for the help.


//

Re: JDBC connectivity issue

[Adrian Klaver]

On 03/08/2018 01:12 PM, chris wrote:

Hi,

I have a JDBC temping to connect to 2 postgres nodes in a hot standby 
configuration. Running postgres 9.4 on centos6.


What we are trying to accomplish is in an event of a failover, to first 
try to connect to the master. If that fails to then reach out to the 
standby.


I looked online and found the suggested way to do this, but it's not 
working.


This is the JDBC line we have:

/jdbc.url=jdbc:postgresql://10.16.10.12:5432,10.16.10.13:5432/app_db/

Here is the error we are getting:/
/

/2018-03-07 13:54:36, 994 ERROR:com.zaxxer.hikari.util.PropertyElf - 
failed to set properly port number on target class 
org.postgresql.ds.PGSimpleDataSource java.langNumberFormatException:for 
input string: "5432,10.16.10.13:5432"/


Lets try that again:

To me it looks like whatever code you are using is trying to use 
"5432,10.16.10.13:5432" as the port number for 10.16.10.12. In other 
words it is not seeing the ',' as a separator for the two IP's'.


Time to clean the glasses:)



It looks like the first IP address has disappeared or is not set right./
/

Thanks in advance for the help.


//





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

Re: JDBC connectivity issue

[Tim Cross]

My interpretation of that error is slightly different. I think what it is
saying is that for the first IP address, it thinks the port number is the
full string "5432,10.16.10.13:5432" and fails when it tries to convert that
into a port number. Are you positive the URL format you are trying to use
is supported by the JDBC driver your using?

On 9 March 2018 at 08:12, chris  wrote:

> Hi,
>
> I have a JDBC temping to connect to 2 postgres nodes in a hot standby
> configuration. Running postgres 9.4 on centos6.
>
> What we are trying to accomplish is in an event of a failover, to first
> try to connect to the master. If that fails to then reach out to the
> standby.
>
> I looked online and found the suggested way to do this, but it's not
> working.
>
> This is the JDBC line we have:
>
> *jdbc.url=jdbc:postgresql://10.16.10.12:5432
> ,10.16.10.13:5432/app_db
> *
>
> Here is the error we are getting:
>
> *2018-03-07 13:54:36, 994 ERROR:com.zaxxer.hikari.util.PropertyElf -
> failed to set properly port number on target class
> org.postgresql.ds.PGSimpleDataSource java.langNumberFormatException:for
> input string: "5432,10.16.10.13:5432 "*
>
> It looks like the first IP address has disappeared or is not set right.
>
> Thanks in advance for the help.
>
>
>
>


-- 
regards,

Tim

--
Tim Cross

Re: JDBC connectivity issue

[Adrian Klaver]

On 03/08/2018 01:12 PM, chris wrote:

Hi,

I have a JDBC temping to connect to 2 postgres nodes in a hot standby 
configuration. Running postgres 9.4 on centos6.


What we are trying to accomplish is in an event of a failover, to first 
try to connect to the master. If that fails to then reach out to the 
standby.


I looked online and found the suggested way to do this, but it's not 
working.


This is the JDBC line we have:

/jdbc.url=jdbc:postgresql://10.16.10.12:5432,10.16.10.13:5432/app_db/


That seems to be correct:

https://jdbc.postgresql.org/documentation/94/connect.html
"Connection Fail-over

To support simple connection fail-over it is possible to define multiple 
endpoints (host and port pairs) in the connection url separated by 
commas. The driver will try to once connect to each of them in order 
until the connection succeeds. If none succeed, a normal connection 
exception is thrown.


The syntax for the connection url is:

jdbc:postgresql://host1:port1,host2:port2/database"



Here is the error we are getting:/
/

/2018-03-07 13:54:36, 994 ERROR:com.zaxxer.hikari.util.PropertyElf - 
failed to set properly port number on target class 
org.postgresql.ds.PGSimpleDataSource java.langNumberFormatException:for 
input string: "5432,10.16.10.13:5432"/


It looks like the first IP address has disappeared or is not set right./


To me it looks whatever code you are using is trying to us 
"5432,10.16.10.13:5432" as the port number for 10.16.10.12. In other 
words it is not seeing the ',' as a separator for the two IP's'.


Can you provide more information about the JDBC version you are using 
and the app code you are using?



/

Thanks in advance for the help.


//





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

JDBC connectivity issue

[chris]

Hi,

I have a JDBC temping to connect to 2 postgres nodes in a hot standby 
configuration. Running postgres 9.4 on centos6.


What we are trying to accomplish is in an event of a failover, to first 
try to connect to the master. If that fails to then reach out to the 
standby.


I looked online and found the suggested way to do this, but it's not 
working.


This is the JDBC line we have:

/jdbc.url=jdbc:postgresql://10.16.10.12:5432,10.16.10.13:5432/app_db/

Here is the error we are getting:/
/

/2018-03-07 13:54:36, 994 ERROR:com.zaxxer.hikari.util.PropertyElf - 
failed to set properly port number on target class 
org.postgresql.ds.PGSimpleDataSource java.langNumberFormatException:for 
input string: "5432,10.16.10.13:5432"/


It looks like the first IP address has disappeared or is not set right./
/

Thanks in advance for the help.


//

Re: What is the meaning of pg_restore output?

[Adrian Klaver]

On 03/08/2018 09:06 AM, Alexandru Lazarev wrote:

client_min_messages GUC didn't change

Commands are:
pg_dump --host 127.0.0.1 --port 5432 --username postgres --format custom 
--blobs --file /tmp/postgresql.backup -Z9 my_db


pg_restore -h 127.0.0.1 -p 5432 -U postgres -Fc -v -j$(nproc) -d my_db 
/tmp/postgresql.backup 2>&1 | tee /tmp/restore.txt





When I restore using 10.2 I see:

pg_restore: creating ACL "public.TABLE wl_week"

Do you see something similar?

My suspicion is that this might have something to do with the commits below:

https://git.postgresql.org/gitweb/?p=postgresql.git=search=HEAD=commit=+ACL

Someone with more knowledge of this will need to confirm or deny.


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

Re: circular wait not triggering deadlock ?

[Justin Pryzby]

On Thu, Mar 08, 2018 at 03:05:36PM -0500, Tom Lane wrote:
> Justin Pryzby  writes:
> > On Thu, Mar 08, 2018 at 01:57:06PM -0500, Tom Lane wrote:
> >> PID 20488 is evidently waiting for PID 6471 to finish its transaction.
> >> What's that one doing?
> 
> > Um, I thought I had kept track of all two pids but looks not..
> > query| SELECT pg_export_snapshot();
> 
> pg_export_snapshot doesn't block for other transactions, though.
> Further down, you have output that confirms that:
> 
> >   6471 | idle in transaction | psql  | SELECT pg_export_snapshot();
> 
> That SELECT is the most recently completed command, not the current one.
> So now the question is what the connected application is waiting for
> before committing.

I believe it does an idle loop around sleep(1), until all the pg_dump
--snapshot children are done.

Justin

Re: circular wait not triggering deadlock ?

[Tom Lane]

Justin Pryzby  writes:
> On Thu, Mar 08, 2018 at 01:57:06PM -0500, Tom Lane wrote:
>> PID 20488 is evidently waiting for PID 6471 to finish its transaction.
>> What's that one doing?

> Um, I thought I had kept track of all two pids but looks not..
> query| SELECT pg_export_snapshot();

pg_export_snapshot doesn't block for other transactions, though.
Further down, you have output that confirms that:

>   6471 | idle in transaction | psql  | SELECT pg_export_snapshot();

That SELECT is the most recently completed command, not the current one.
So now the question is what the connected application is waiting for
before committing.

regards, tom lane

Re: circular wait not triggering deadlock ?

[Justin Pryzby]

On Thu, Mar 08, 2018 at 01:57:06PM -0500, Tom Lane wrote:
> Justin Pryzby  writes:
> > Running pg10.2, I have a handful of maintenance jobs run in the middle of 
> > the
> > night, which appear to have gotten stuck waiting on each other..
> 
> > ts=# SELECT granted, pid, mode, virtualtransaction, virtualxid FROM 
> > pg_locks WHERE virtualxid='22/4317099' ORDER BY 1,2,3;
> >  granted |  pid  | mode  | virtualtransaction | virtualxid 
> > -+---+---++
> >  f   | 20488 | ShareLock | 19/28401734| 22/4317099
> >  t   |  6471 | ExclusiveLock | 22/4317099 | 22/4317099
> 
> PID 20488 is evidently waiting for PID 6471 to finish its transaction.
> What's that one doing?

Um, I thought I had kept track of all two pids but looks not..

query| SELECT pg_export_snapshot();

So that explains that.
I already killed one proc, but..

ts=# SELECT granted, relation::regclass, pid, mode, virtualtransaction, 
virtualxid FROM pg_locks WHERE pid=17248 ORDER BY 1;
 granted |relation |  pid  |  mode   | 
virtualtransaction | virtualxid
-+-+---+-++
 f   | eric_enodeb_cell_metrics| 17248 | AccessShareLock | 
27/1755026 |

ts=# SELECT granted, relation::regclass, pid, mode, virtualtransaction, 
virtualxid FROM pg_locks WHERE relation='eric_enodeb_cell_metrics'::regclass 
ORDER BY 1; --pid=17248  virtualxid='22/4317099' ORDER BY 1,2,3;
 granted | relation |  pid  |   mode   | 
virtualtransaction | virtualxid
-+--+---+--++
 f   | eric_enodeb_cell_metrics | 22961 | AccessShareLock  | 
31/1337307 |
 f   | eric_enodeb_cell_metrics | 17248 | AccessShareLock  | 
27/1755026 |
 f   | eric_enodeb_cell_metrics | 28357 | AccessShareLock  | 
28/1118276 |
 f   | eric_enodeb_cell_metrics | 21846 | AccessExclusiveLock  | 
29/830016  |
 f   | eric_enodeb_cell_metrics | 16592 | AccessExclusiveLock  | 
18/38156962|
 f   | eric_enodeb_cell_metrics | 18941 | AccessShareLock  | 
32/838769  |
 t   | eric_enodeb_cell_metrics |   530 | ShareUpdateExclusiveLock | 
16/38159763|
(7 rows)

ts=# SELECT pid, state, left(query,199) FROM pg_stat_activity WHERE pid 
IN(530,17248);
   530 | active | ALTER TABLE eric_enodeb_cell_metrics* ALTER start_time SET 
STATISTICS 400
 17248 | active | SELECT c.tableoid, c.oid, c.relname, (SELECT 
pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM 
pg_catalog.unnest(coalesce(c.relacl,pg_catalog.acldefault(
CASE WHEN c.relkind = 'S' 

ts=# SELECT granted, pid, mode, virtualtransaction, virtualxid FROM pg_locks 
WHERE relation='eric_enodeb_cell_20180304'::regclass ;
 granted |  pid  |   mode   | virtualtransaction | virtualxid
-+---+--++
 t   | 20488 | ShareUpdateExclusiveLock | 19/28401734|
 f   |   530 | ShareUpdateExclusiveLock | 16/38159763|

ts=# SELECT pid, state, left(query,199) FROM pg_stat_activity WHERE pid=20488;
  pid  | state  |   
left
---++---
 20488 | active | CREATE INDEX CONCURRENTLY index_1113966210 ON 
eric_enodeb_cell_20180304 USING btree (site_id) WITH (fillfactor='100') 
TABLESPACE oldindex

ts=# SELECT granted, pid, relation::regclass, locktype, mode  virtualxid, 
virtualxid, virtualtransaction FROM pg_locks WHERE pid=20488 ORDER BY 1;
 granted |  pid  | relation  |  locktype  |virtualxid   
 | virtualxid  | virtualtransaction 
-+---+---++--+-+
 f   | 20488 |   | virtualxid | ShareLock   
 | 22/4317099  | 19/28401734

ts=# SELECT granted, pid, relation::regclass, locktype, mode  virtualxid, 
virtualtransaction FROM pg_locks WHERE virtualxid='22/4317099' ORDER BY 1;
 granted |  pid  | relation |  locktype  |  virtualxid   | virtualtransaction 
-+---+--++---+
 f   | 20488 |  | virtualxid | ShareLock | 19/28401734
 t   |  6471 |  | virtualxid | ExclusiveLock | 22/4317099
(2 rows)

So...I gather ALTER SET STATS is waiting on pg_dump which is waiting on CREATE
INDEX which is waiting on SELECT pg_export_snapshot(), which I 

Re: Crash and core on 10.1 and 10.2

[Kelly Burkhart]

We're on centos 7.0, glibc-2.17-55.  Current centos is
7.4, glibc-2.17-196.  We have some hosts on the newer centos, I'll ask our
sysadmin about upgrading.

Do you know of glibc issues that would be of relevance?

Our main production database has been running the same centos and pg 9.4.4
without any issue for a very long time.  (Although we use no json or gin
stuff there).

-K

On Thu, Mar 8, 2018 at 11:00 AM, Tom Lane  wrote:

> Kelly Burkhart  writes:
> > Hello, I've had two core dumps in the last couple of weeks.  The most
> > recent, yesterday was on version 10.2:
>
> > (gdb) bt
> > #0  0x7f317a043886 in get_next_seq () from /lib64/libc.so.6
> > #1  0x7f317a044acc in strcoll_l () from /lib64/libc.so.6
> > #2  0x007ced5f in varstrfastcmp_locale ()
> > #3  0x0081b6fb in qsort_ssup ()
> > #4  0x0081d8e1 in tuplesort_performsort ()
>
> Hm.  If you'd just showed this one, my thoughts might bend towards a bug
> in our sort abbreviation logic, which is relatively new ...
>
> > (gdb) bt
> > #0  0x7f6e1f09d8ea in get_next_seq () from /lib64/libc.so.6
> > #1  0x7f6e1f09eacc in strcoll_l () from /lib64/libc.so.6
> > #2  0x007cf70b in varstr_cmp ()
> > #3  0x0075f25b in compareJsonbContainers ()
> > #4  0x0075d8f2 in jsonb_eq ()
>
> ... but this stack trace is not going anywhere near that code.  The
> common factor is just strcoll_l(), raising the possibility that you're
> dealing with a glibc bug, or perhaps corrupted locale data on your
> machine.  Are you up-to-date on glibc patches?
>
> regards, tom lane
>

Re: circular wait not triggering deadlock ?

[Tom Lane]

Justin Pryzby  writes:
> Running pg10.2, I have a handful of maintenance jobs run in the middle of the
> night, which appear to have gotten stuck waiting on each other..

> ts=# SELECT granted, pid, mode, virtualtransaction, virtualxid FROM pg_locks 
> WHERE virtualxid='22/4317099' ORDER BY 1,2,3;
>  granted |  pid  | mode  | virtualtransaction | virtualxid 
> -+---+---++
>  f   | 20488 | ShareLock | 19/28401734| 22/4317099
>  t   |  6471 | ExclusiveLock | 22/4317099 | 22/4317099

PID 20488 is evidently waiting for PID 6471 to finish its transaction.
What's that one doing?

> Is it a bug that this isn't caught by a deadlock detector and cancelled?

We did find a bug recently with concurrent CREATE INDEX CONCURRENTLY's
all waiting for each other to commit.

regards, tom lane

Re: Troubleshooting a segfault and instance crash

[Pavel Stehule]

2018-03-08 19:16 GMT+01:00 Blair Boadway :

> 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 
> *Date: *Thursday, March 8, 2018 at 9:49 AM
> *To: *Blair Boadway 
> *Cc: *"pgsql-gene...@postgresql.org" 
> *Subject: *Re: Troubleshooting a segfault and instance crash
>
>
>
> Hi
>
>
>
> 2018-03-08 18:40 GMT+01:00 Blair Boadway :
>
> 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: Help troubleshooting SubtransControlLock problems

[Scott Frazer]

On Wed, Mar 7, 2018 at 3:50 PM, Alvaro Herrera 
wrote:
>
> Scott Frazer wrote:
>
> > It's only happening on the read replicas, though. I've just set my
master
> > to handle all the traffic, but that's not really sustainable
>
> I failed to notice at start of thread that these were replicas.  I
> suppose then you would have to be thinking about the range of XIDs in
> the primary server.  Maybe you left some old transaction open, or an
> uncommitted prepared transaction there?
>
> If not, I suppose you're up for some "perf" profiling ...

I think you got it. I found a 2-day old transaction on the master server
that never closed. I had to pg_terminate_backend on it and the problem
seems to have cleared up.

Re: Troubleshooting a segfault and instance crash

[Blair Boadway]

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.

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)

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 
Date: Thursday, March 8, 2018 at 9:49 AM
To: Blair Boadway 
Cc: "pgsql-gene...@postgresql.org" 
Subject: Re: Troubleshooting a segfault and instance crash

Hi

2018-03-08 18:40 GMT+01:00 Blair Boadway 
>:
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

[Pavel Stehule]

Hi

2018-03-08 18:40 GMT+01:00 Blair Boadway :

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

Troubleshooting a segfault and instance crash

[Blair Boadway]

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.

Regards,
Blair

Re: What is the meaning of pg_restore output?

[Alexandru Lazarev]

 client_min_messages GUC didn't change

Commands are:
pg_dump --host 127.0.0.1 --port 5432 --username postgres --format custom
--blobs --file /tmp/postgresql.backup -Z9 my_db

pg_restore  -h 127.0.0.1 -p 5432 -U postgres -Fc -v -j$(nproc) -d my_db
/tmp/postgresql.backup 2>&1 | tee /tmp/restore.txt



Virus-free.
www.avast.com

<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Thu, Mar 8, 2018 at 5:30 PM, Adrian Klaver 
wrote:

> On 03/08/2018 01:49 AM, Alexandru Lazarev wrote:
>
>> Hi PG community,
>> I have questions about pg_restore output.
>>
>> At some point of time in pg_restore output was such phrase "*/pg_restore:
>> setting owner and privileges for /*", then it disappeared,
>> it seems, when upgraded from PG 9.6.2 to 9.6.6
>> Other phrases "pg_restore: processing /*item */3615 DEFAULT id
>> pg_restore: creating DEFAULT "public.id "
>> are observed if pg_restore is executed with flag -j N (where N > 1)
>> and aren't present when -j 1 (or without -j)
>>
>> So main questions are:
>> 1. What is the meaning of this phrases?
>> 2. Are they documented somewhere?
>> 3. Is it possible that output change from version to version?
>>
>
> I do not see any change in the release notes:
> https://www.postgresql.org/docs/9.6/static/release.html
>
> I would look at what at client_min_messages (enum) in:
> https://www.postgresql.org/docs/9.6/static/runtime-config-logging.html
>
> It would also helpful to see the complete pg_dump and pg_restore commands
> you are working with.
>
>
>> Why I am asking?
>> I saw a script which does pg_restore and grep some of this phrases as
>> SUCCESS indicator that pg_restore passed OK (+ something like grep -iv
>> "error").
>> script doesn't use pg_restore exit code because, as I understood from the
>> authors, in the past were situations that pg_restore returned 0 code but
>> didn't restore of db.
>>
>> Any answers, hints are welcome :).
>> Thanks
>>
>> > =link_campaign=sig-email_content=webmail_term=icon>
>>  Virus-free. www.avast.com > il?utm_medium=email_source=link_campaign=sig-email&
>> utm_content=webmail_term=link>
>>
>> <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Crash and core on 10.1 and 10.2

[Tom Lane]

Kelly Burkhart  writes:
> Hello, I've had two core dumps in the last couple of weeks.  The most
> recent, yesterday was on version 10.2:

> (gdb) bt
> #0  0x7f317a043886 in get_next_seq () from /lib64/libc.so.6
> #1  0x7f317a044acc in strcoll_l () from /lib64/libc.so.6
> #2  0x007ced5f in varstrfastcmp_locale ()
> #3  0x0081b6fb in qsort_ssup ()
> #4  0x0081d8e1 in tuplesort_performsort ()

Hm.  If you'd just showed this one, my thoughts might bend towards a bug
in our sort abbreviation logic, which is relatively new ...

> (gdb) bt
> #0  0x7f6e1f09d8ea in get_next_seq () from /lib64/libc.so.6
> #1  0x7f6e1f09eacc in strcoll_l () from /lib64/libc.so.6
> #2  0x007cf70b in varstr_cmp ()
> #3  0x0075f25b in compareJsonbContainers ()
> #4  0x0075d8f2 in jsonb_eq ()

... but this stack trace is not going anywhere near that code.  The
common factor is just strcoll_l(), raising the possibility that you're
dealing with a glibc bug, or perhaps corrupted locale data on your
machine.  Are you up-to-date on glibc patches?

regards, tom lane

Crash and core on 10.1 and 10.2

[Kelly Burkhart]

Hello, I've had two core dumps in the last couple of weeks.  The most
recent, yesterday was on version 10.2:

(gdb) bt
#0  0x7f317a043886 in get_next_seq () from /lib64/libc.so.6
#1  0x7f317a044acc in strcoll_l () from /lib64/libc.so.6
#2  0x007ced5f in varstrfastcmp_locale ()
#3  0x0081b6fb in qsort_ssup ()
#4  0x0081d8e1 in tuplesort_performsort ()
#5  0x005eaf00 in finalize_aggregates ()
#6  0x005ebe42 in ExecAgg ()
#7  0x005e30e8 in ExecProcNodeInstr ()
#8  0x005ded12 in standard_ExecutorRun ()
#9  0x00589380 in ExplainOnePlan ()
#10 0x00589667 in ExplainOneQuery ()
#11 0x00589b74 in ExplainQuery ()
#12 0x0070036b in standard_ProcessUtility ()
#13 0x006fd9f7 in PortalRunUtility ()
#14 0x006fe702 in FillPortalStore ()
#15 0x006ff110 in PortalRun ()
#16 0x006fb163 in exec_simple_query ()
#17 0x006fc41c in PostgresMain ()
#18 0x00475c7d in ServerLoop ()
#19 0x00697449 in PostmasterMain ()
#20 0x00476691 in main ()

The earlier was last week on 10.1:

(gdb) bt
#0  0x7f6e1f09d8ea in get_next_seq () from /lib64/libc.so.6
#1  0x7f6e1f09eacc in strcoll_l () from /lib64/libc.so.6
#2  0x007cf70b in varstr_cmp ()
#3  0x0075f25b in compareJsonbContainers ()
#4  0x0075d8f2 in jsonb_eq ()
#5  0x005db2bc in ExecInterpExpr ()
#6  0x005e3b09 in ExecScan ()
#7  0x005de352 in standard_ExecutorRun ()
#8  0x005e262a in ParallelQueryMain ()
#9  0x004db981 in ParallelWorkerMain ()
#10 0x006895ff in StartBackgroundWorker ()
#11 0x0069470d in maybe_start_bgworkers ()
#12 0x00695245 in sigusr1_handler ()
#13 
#14 0x7f6e1f0f9783 in __select_nocancel () from /lib64/libc.so.6
#15 0x00475327 in ServerLoop ()
#16 0x00696409 in PostmasterMain ()
#17 0x00476671 in main ()


These are running on a centos 7 host, dell r640.  My modifications to the
default postgresql.conf are:

max_connections = 1000
shared_buffers = 12GB
work_mem = 1GB
maintenance_work_mem = 1GB
constraint_exclusion = partition
track_activities = on
track_counts = on
track_functions = all
track_io_timing = on
autovacuum = on
datestyle = 'iso, ymd'
log_destination = 'csvlog'
logging_collector = on
log_directory = '/opt/pgdata/dblog'
log_filename = 'postgresql-%w.log'
log_file_mode = 0644
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
log_min_duration_statement = 0
log_checkpoints = on
log_connections = on
log_disconnections = on
log_duration = on
log_hostname = off
log_line_prefix = '#%r:%p:%m:%i# '
log_lock_Waits = on
log_statement = 'none'
log_temp_files = 0
update_process_title = on
temp_tablespaces = 'tb_temp'
idle_in_transaction_session_timeout = 30 # in milliseconds, 0 is
disabled


The database is quite busy at the time of the crash with 99% of statements
being very simple.  Each time the crash happened during an ad-hoc query of
a table with a jsonb field and a gin index on that field.  The most recent
query:

server process (PID 318386) was terminated by signal 11: Segmentation fault
 Failed process was running: explain analyze select count(distinct
other_keys->>'shard_dt') from krb_dataset_shard where dataset_id=22 and
other_keys->>'symbol' = 'AAPL';

The table looks like this:

   Table "tb_us.krb_dataset_shard"
   Column   |  Type  |   Modifiers

++
 id | bigint | not null default
nextval('krb_dataset_shard_id_seq'::regclass)
 dataset_id | bigint | not null
 other_keys | jsonb  | not null default '{}'::jsonb
Indexes:
"krb_dataset_shard_pkey" PRIMARY KEY, btree (id), tablespace
"tb_idx_tablespace"
"krb_dataset_shard_ak1" btree (dataset_id), tablespace
"tb_idx_tablespace"
"krb_dataset_shard_ak2" gin (other_keys), tablespace "tb_idx_tablespace"
Foreign-key constraints:
"dataset_id_fk" FOREIGN KEY (dataset_id) REFERENCES dataset(id)


I do not have the query from the previous crash.

Please let me know if there is any other information that I can provide.

Thanks,

-Kelly

Re: Trigger to create string to inverse SQL statement

[Francisco Olarte]

Jacek:

On Thu, Mar 8, 2018 at 1:53 PM, Łukasz Jarych  wrote:
> maybe somebody wrote SP to invert update statement?

Maybe, but if you are going to ask for it you better define the
problem a little.

Francisco Olarte.

Re: Trigger to create string to inverse SQL statement

[Francisco Olarte]

Hello:

On Thu, Mar 8, 2018 at 1:51 PM, Łukasz Jarych  wrote:
> what do you mean use external program to build inverted queries. Do you have 
> any examples?

Please, do not top quote, or the thread will get difficult to follow fast.

That being said. Capturing a DML in a trigger is relatively easy. On
recent postgres I've seen just using json to capture the whole new and
old rows, which looks promissing.

But then, building a 'rollback' DML is difficult to do in sql or
pl-pgsql, you would normally want a more normal programming language.
i.e., I would normally turn to perl for this, having used it since the
mid 90s.

Then, you have pl-perl, but this is difficult to debug/manage. So what
I would normally do is to just capture the changes in a trigger and
then have a program which queries the log table, builds the anti-query
and executes it ( no point in keeping it, since once you execute it
there is nothing to undo ). The beauty of this is you can take a
sample from your log table and easily test the program just
implementing a debug flag which prints the queries instead of
executing ( and does not touch the log table, which I assume a real
undoer will need to fro record-keepint ).

This is assuming the log is used as an "undo log", which is what I
would assume from the very scarce information I have. And this kind of
programs normally are seldom used, so postponing the query building to
a later time and logging minimal info fast is normally better ( is
like backups, you normally make backups faster, then optimize what you
can of restores, as ideally they would never be used, or transactions,
you normally optimize for commits first, then rollbacks ).

Francisco Olarte.

Re: What is the meaning of pg_restore output?

[Adrian Klaver]

On 03/08/2018 01:49 AM, Alexandru Lazarev wrote:

Hi PG community,
I have questions about pg_restore output.

At some point of time in pg_restore output was such phrase 
"*/pg_restore: setting owner and privileges for /*", then 
it disappeared, it seems, when upgraded from PG 9.6.2 to 9.6.6

Other phrases "pg_restore: processing /*item */3615 DEFAULT id
pg_restore: creating DEFAULT "public.id "
are observed if pg_restore is executed with flag -j N (where N > 1)
and aren't present when -j 1 (or without -j)

So main questions are:
1. What is the meaning of this phrases?
2. Are they documented somewhere?
3. Is it possible that output change from version to version?


I do not see any change in the release notes:
https://www.postgresql.org/docs/9.6/static/release.html

I would look at what at client_min_messages (enum) in:
https://www.postgresql.org/docs/9.6/static/runtime-config-logging.html

It would also helpful to see the complete pg_dump and pg_restore 
commands you are working with.




Why I am asking?
I saw a script which does pg_restore and grep some of this phrases as 
SUCCESS indicator that pg_restore passed OK (+ something like grep -iv 
"error").
script doesn't use pg_restore exit code because, as I understood from 
the authors, in the past were situations that pg_restore returned 0 code 
but didn't restore of db.


Any answers, hints are welcome :).
Thanks

 
	Virus-free. www.avast.com 
 



<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>



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

Re: Command to execute SQL string

[David G. Johnston]

On Thursday, March 8, 2018, Francisco Olarte  wrote:
>
> On Thu, Mar 8, 2018 at 1:26 PM, Łukasz Jarych  wrote:
> > i have a table when i have string in each row,
> > for example "DELETE FROM t_test WHERE ID = 3".
> >
> > I want to execute for each row this command.
> > It is possible?
>
> I supose you mean execute the command contained in each row. Anyway,
> it is easy with a procedure, look for example at
>
> https://www.postgresql.org/docs/10/static/plpgsql-statements.html#PLPGSQL-
> STATEMENTS-EXECUTING-DYN
>
>
See also v10 psql \gexec

David J.

Re: Trigger to create string to inverse SQL statement

[Łukasz Jarych]

maybe somebody wrote SP to invert update statement?

Best,
Jacek

2018-03-08 13:51 GMT+01:00 Łukasz Jarych :

> thank you,
>
> what do you mean use external program to build inverted queries. Do you
> have any examples?
>
> Best,
> Jacek
>
> 2018-03-08 13:44 GMT+01:00 Francisco Olarte :
>
>> Hello:
>>
>> On Thu, Mar 8, 2018 at 1:32 PM, Łukasz Jarych  wrote:
>>
>>>
>>>
>>> i have a trigger which is added log history:
>>>
>>>
>>>
>>>
>> ​I'll trust it is, but I cannot really see any thing in there, it may be
>> better if in the future you used something like psql and pasted the data in
>> a text format, which people with no-so-good eyesight like me can zoom in.​
>>
>>
>>
>>> It is possible to create additional column here with string with
>>> inversed SQL statement?
>>>
>>> So in this case: "Delete FROM t_trig WHERE ID=1".
>>>
>>> And what i want is to have possibility to loop through table and execute
>>> inversed sql statement for each row.
>>>
>>
>> ​It should be. For inserts it is not that difficult, for updates YMMV, it
>> is difficult to ​reverse a command exactly, but you get new and old values
>> so you could write it. For deletes just reinsert. But I doubt there is a
>> function which does it for you, you'll have to code something, and I
>> suspect it may be easier to just capture op, old and new in your triggers
>> and use an external program to build the inverted queries.
>>
>> ​Francisco Olarte.​
>>
>>
>

Re: Trigger to create string to inverse SQL statement

[Łukasz Jarych]

thank you,

what do you mean use external program to build inverted queries. Do you
have any examples?

Best,
Jacek

2018-03-08 13:44 GMT+01:00 Francisco Olarte :

> Hello:
>
> On Thu, Mar 8, 2018 at 1:32 PM, Łukasz Jarych  wrote:
>
>>
>>
>> i have a trigger which is added log history:
>>
>>
>>
>>
> ​I'll trust it is, but I cannot really see any thing in there, it may be
> better if in the future you used something like psql and pasted the data in
> a text format, which people with no-so-good eyesight like me can zoom in.​
>
>
>
>> It is possible to create additional column here with string with inversed
>> SQL statement?
>>
>> So in this case: "Delete FROM t_trig WHERE ID=1".
>>
>> And what i want is to have possibility to loop through table and execute
>> inversed sql statement for each row.
>>
>
> ​It should be. For inserts it is not that difficult, for updates YMMV, it
> is difficult to ​reverse a command exactly, but you get new and old values
> so you could write it. For deletes just reinsert. But I doubt there is a
> function which does it for you, you'll have to code something, and I
> suspect it may be easier to just capture op, old and new in your triggers
> and use an external program to build the inverted queries.
>
> ​Francisco Olarte.​
>
>

Re: Command to execute SQL string

[Łukasz Jarych]

Hello Francisco,

thank you !

Jacek is the same as Łukasz. Is my second name and my artist's nickname ;-)

love your answer,
thank you!

Best,
Jacek

2018-03-08 13:40 GMT+01:00 Francisco Olarte :

> Hello:
>
>
>
> On Thu, Mar 8, 2018 at 1:26 PM, Łukasz Jarych  wrote:
> > i have a table when i have string in each row,
> > for example "DELETE FROM t_test WHERE ID = 3".
> >
> > I want to execute for each row this command.
> > It is possible?
>
> I supose you mean execute the command contained in each row. Anyway,
> it is easy with a procedure, look for example at
>
> https://www.postgresql.org/docs/10/static/plpgsql-statements.html#PLPGSQL-
> STATEMENTS-EXECUTING-DYN
>
> just do a loop on your command table, follow the examples and you
> should be done.
>
> Francisco Olarte.
>
> PS:
> > Jacek
> Is this the same as Łukasz or Jarych ?
>
> F.O.
>

Re: Trigger to create string to inverse SQL statement

[Francisco Olarte]

Hello:

On Thu, Mar 8, 2018 at 1:32 PM, Łukasz Jarych  wrote:

>
>
> i have a trigger which is added log history:
>
>
>
>
​I'll trust it is, but I cannot really see any thing in there, it may be
better if in the future you used something like psql and pasted the data in
a text format, which people with no-so-good eyesight like me can zoom in.​



> It is possible to create additional column here with string with inversed
> SQL statement?
>
> So in this case: "Delete FROM t_trig WHERE ID=1".
>
> And what i want is to have possibility to loop through table and execute
> inversed sql statement for each row.
>

​It should be. For inserts it is not that difficult, for updates YMMV, it
is difficult to ​reverse a command exactly, but you get new and old values
so you could write it. For deletes just reinsert. But I doubt there is a
function which does it for you, you'll have to code something, and I
suspect it may be easier to just capture op, old and new in your triggers
and use an external program to build the inverted queries.

​Francisco Olarte.​

Re: Command to execute SQL string

[Francisco Olarte]

Hello:



On Thu, Mar 8, 2018 at 1:26 PM, Łukasz Jarych  wrote:
> i have a table when i have string in each row,
> for example "DELETE FROM t_test WHERE ID = 3".
>
> I want to execute for each row this command.
> It is possible?

I supose you mean execute the command contained in each row. Anyway,
it is easy with a procedure, look for example at

https://www.postgresql.org/docs/10/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

just do a loop on your command table, follow the examples and you
should be done.

Francisco Olarte.

PS:
> Jacek
Is this the same as Łukasz or Jarych ?

F.O.

Trigger to create string to inverse SQL statement

[Łukasz Jarych]

Hello,

i have a trigger which is added log history:



It is possible to create additional column here with string with inversed
SQL statement?

So in this case: "Delete FROM t_trig WHERE ID=1".

And what i want is to have possibility to loop through table and execute
inversed sql statement for each row.

Please help,
Jacek Antek

Command to execute SQL string

[Łukasz Jarych]

Hello,

i have a table when i have string in each row,
for example "DELETE FROM t_test WHERE ID = 3".

I want to execute for each row this command.
It is possible?

Best wishes,
Jacek

RE: Resync second slave to new master

[Dylan Luong]

Thanks! That fixed the issue.

-Original Message-
From: Michael Paquier [mailto:mich...@paquier.xyz] 
Sent: Thursday, 8 March 2018 6:41 PM
To: Yavuz Selim Sertoğlu 
Cc: Dylan Luong ; pgsql-generallists.postgresql.org 

Subject: Re: Resync second slave to new master

On Thu, Mar 08, 2018 at 10:48:29AM +0300, Yavuz Selim Sertoğlu wrote:
> If not set, could you add recovery.conf file
> recovery_target_timeline='latest'
> parameter?
> https://www.postgresql.org/docs/devel/static/recovery-target-settings.html

Yes, that's visibly the issue here.
--
Michael

What is the meaning of pg_restore output?

[Alexandru Lazarev]

Hi PG community,
I have questions about pg_restore output.

At some point of time in pg_restore output was such phrase "*pg_restore:
setting owner and privileges for *", then it disappeared, it
seems, when upgraded from PG 9.6.2 to 9.6.6
Other phrases "pg_restore: processing *item *3615 DEFAULT id
pg_restore: creating DEFAULT "public.id"
are observed if pg_restore is executed with flag -j N (where N > 1)
and aren't present when -j 1 (or without -j)

So main questions are:
1. What is the meaning of this phrases?
2. Are they documented somewhere?
3. Is it possible that output change from version to version?

Why I am asking?
I saw a script which does pg_restore and grep some of this phrases as
SUCCESS indicator that pg_restore passed OK (+ something like grep -iv
"error").
script doesn't use pg_restore exit code because, as I understood from the
authors, in the past were situations that pg_restore returned 0 code but
didn't restore of db.

Any answers, hints are welcome :).
Thanks


Virus-free.
www.avast.com

<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

Re: Resync second slave to new master

[Michael Paquier]

On Thu, Mar 08, 2018 at 10:48:29AM +0300, Yavuz Selim Sertoğlu wrote:
> If not set, could you add recovery.conf file
> recovery_target_timeline='latest'
> parameter?
> https://www.postgresql.org/docs/devel/static/recovery-target-settings.html

Yes, that's visibly the issue here.
--
Michael


signature.asc
Description: PGP signature