Re: RES: Postgresql Crasching

[Adrian Klaver]

On 1/25/19 10:50 AM, Márcio Antônio Sepp wrote:


On 1/25/19 10:21 AM, Márcio Antônio Sepp wrote:


Hi all,


We are experiencing the follow error in last few weeks:

Jan 25 16:03:51 FreeBSD11 kernel: pid 78639 (postgres), uid 770: exited

on

signal 11 (core dumped)
Jan 25 16:03:51 FreeBSD11 postgres[73403]: [7-1] 2019-01-25 16:03:51.135

-02

[73403] WARNING:  terminating connection because of crash of another

server

process
Jan 25 16:03:51 FreeBSD11 postgres[73403]: [7-2] 2019-01-25 16:03:51.135

-02

[73403] DETAIL:  The postmaster has commanded this server process to roll
back the current transacti
on and exit, because another server process exited abnormally and

possibly

corrupted shared memory.
Jan 25 16:03:51 FreeBSD11 postgres[73403]: [7-3] 2019-01-25 16:03:51.135

-02

[73403] HINT:  In a moment you should be able to reconnect to the

database

and repeat your command.


What is the logs just before the above?



Jan 25 16:01:55 FreeBSD11 postgres[71605]: [6-2] 2019-01-25 16:01:55.733 -02 
[71605] DETAIL:  Key (tdoemp, codemp, codfil, origem, sercai, numdoc, tdopes, 
codpes, datpar)=(1, 59
84305, 1, 55, 1, 160, 1, 5984305000107, 201901) is not present in table 
"e026pag_p2019".
Jan 25 16:01:55 FreeBSD11 postgres[71605]: [6-3] 2019-01-25 16:01:55.733 -02 
[71605] STATEMENT:  COMMIT
Jan 25 16:02:35 FreeBSD11 postgres[73403]: [6-1] 2019-01-25 16:02:35.184 -02 [73403] ERROR:  
insert or update on table "e026par_p2019" violates foreign key constraint 
"e026par_p
2019_tdoemp_fkey"
Jan 25 16:02:35 FreeBSD11 postgres[73403]: [6-2] 2019-01-25 16:02:35.184 -02 
[73403] DETAIL:  Key (tdoemp, codemp, codfil, origem, sercai, numdoc, tdopes, 
codpes, datpar)=(1, 59
84305, 1, 55, 1, 160, 1, 5984305000107, 201901) is not present in table 
"e026pag_p2019".
Jan 25 16:02:35 FreeBSD11 postgres[73403]: [6-3] 2019-01-25 16:02:35.184 -02 
[73403] STATEMENT:  COMMIT
Jan 25 16:03:51 FreeBSD11 kernel: pid 78639 (postgres), uid 770: exited on 
signal 11 (core dumped)



Hmm, a 1:16 between the query failing and the crash. Hard to say if 
there is a cause and effect. I would follow Tom Lane's advice and see if 
you can get a stack trace.



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

RE: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

[Duarte Carreira]

Well, happy to report pg_upgrade worked, and have 9.5 cluster up and running.

Will be testing during weekend but everything looks ok. Just had to tweak a few 
settings that changed from 9.3 to 9.5 (logs and checkpoint_segments).

Life's good.

Thanks again, and just have to say it - open source rocks!

Duarte

-Mensagem original-
De: Duarte Carreira 
Enviada: 25 de janeiro de 2019 18:24
Para: Tom Lane 
Cc: pgsql-gene...@postgresql.org
Assunto: RE: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

Tom, can't thank you enough.

Now, the 2nd query and results are like so:
select pg_describe_object(classid,objid,objsubid), deptype from pg_depend where 
refobjid = 'sde.st_envelope'::regtype; "function st_envelope_in(cstring)";"n"
"function st_envelope_out(st_envelope)";"n"
"type st_envelope[]";"i"
"function st_envelope_union(st_envelope,st_envelope)";"n"
"function st_envelope_union(st_envelope,st_envelope)";"n"
"function st_envelope_union(st_envelope,st_envelope)";"n"
"function st_envelope_intersects(st_envelope,st_envelope)";"n"
"function st_envelope_intersects(st_envelope,st_envelope)";"n"
"function st_envelope_intersects(st_envelope,st_envelope)";"n"
"function st_envelope_equal(st_envelope,st_envelope)";"n"
"function st_envelope_equal(st_envelope,st_envelope)";"n"
"function st_geo_gist_equal(st_envelope,st_envelope,internal)";"n"
"function st_geo_gist_equal(st_envelope,st_envelope,internal)";"n"

I'll try pg_upgrade again and let you know how it goes. (fingers crossed)

Thanks,
Duarte

-Mensagem original-
De: Tom Lane 
Enviada: 25 de janeiro de 2019 18:19
Para: Duarte Carreira 
Cc: pgsql-gene...@postgresql.org
Assunto: Re: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

Duarte Carreira  writes:
> So I proceeded with the 2 queries:

> select pg_describe_object(refclassid,refobjid,refobjsubid), deptype 
> from pg_depend where objid = 'sde.st_envelope'::regtype; "schema sde";"n"
> "function st_envelope_in(cstring)";"n"
> "function st_envelope_out(st_envelope)";"n"

OK, that looks about like what I'd expect.

> select pg_describe_object(classid,objid,objsubid), deptype from 
> pg_depend where refobjid = 'sde.st_envelope'::regtype; "type 
> st_envelope[]";"i"
> "function st_envelope_union(st_envelope,st_envelope)";"n"
> "function st_envelope_union(st_envelope,st_envelope)";"n"
> "function st_envelope_union(st_envelope,st_envelope)";"n"
> "function st_envelope_intersects(st_envelope,st_envelope)";"n"
> "function st_envelope_intersects(st_envelope,st_envelope)";"n"
> "function st_envelope_intersects(st_envelope,st_envelope)";"n"
> "function st_envelope_equal(st_envelope,st_envelope)";"n"
> "function st_envelope_equal(st_envelope,st_envelope)";"n"
> "function st_geo_gist_equal(st_envelope,st_envelope,internal)";"n"
> "function st_geo_gist_equal(st_envelope,st_envelope,internal)";"n"

Ah-hah --- st_envelope_in and st_envelope_out are not mentioned here?
That explains your problem.  You'd need to add those two rows to pg_depend, 
which could go something like

insert into pg_depend (classid, objid, objsubid,
   refclassid, refobjid, refobjsubid, deptype) values (
  'pg_proc'::regclass,
  'sde.st_envelope_in(cstring)'::regprocedure,
  0,
  'pg_type'::regclass,
  'sde.st_envelope'::regtype,
  0,
  'n');

insert into pg_depend (classid, objid, objsubid,
   refclassid, refobjid, refobjsubid, deptype) values (
  'pg_proc'::regclass,
  'sde.st_envelope_out(sde.st_envelope)'::regprocedure,
  0,
  'pg_type'::regclass,
  'sde.st_envelope'::regtype,
  0,
  'n');

I suppose the evidence about what happened to those rows is long gone, so 
there's not much point in doing anything but patching things up to the point 
where you can run pg_upgrade.

regards, tom lane

RES: Postgresql Crasching

[Márcio Antônio Sepp]

> 
> On 1/25/19 10:21 AM, Márcio Antônio Sepp wrote:
> >
> > Hi all,
> >
> >
> > We are experiencing the follow error in last few weeks:
> >
> > Jan 25 16:03:51 FreeBSD11 kernel: pid 78639 (postgres), uid 770: exited
> on
> > signal 11 (core dumped)
> > Jan 25 16:03:51 FreeBSD11 postgres[73403]: [7-1] 2019-01-25 16:03:51.135
> -02
> > [73403] WARNING:  terminating connection because of crash of another
> server
> > process
> > Jan 25 16:03:51 FreeBSD11 postgres[73403]: [7-2] 2019-01-25 16:03:51.135
> -02
> > [73403] DETAIL:  The postmaster has commanded this server process to roll
> > back the current transacti
> > on and exit, because another server process exited abnormally and
> possibly
> > corrupted shared memory.
> > Jan 25 16:03:51 FreeBSD11 postgres[73403]: [7-3] 2019-01-25 16:03:51.135
> -02
> > [73403] HINT:  In a moment you should be able to reconnect to the
> database
> > and repeat your command.
> 
> What is the logs just before the above?


Jan 25 16:01:55 FreeBSD11 postgres[71605]: [6-2] 2019-01-25 16:01:55.733 -02 
[71605] DETAIL:  Key (tdoemp, codemp, codfil, origem, sercai, numdoc, tdopes, 
codpes, datpar)=(1, 59
84305, 1, 55, 1, 160, 1, 5984305000107, 201901) is not present in table 
"e026pag_p2019".
Jan 25 16:01:55 FreeBSD11 postgres[71605]: [6-3] 2019-01-25 16:01:55.733 -02 
[71605] STATEMENT:  COMMIT
Jan 25 16:02:35 FreeBSD11 postgres[73403]: [6-1] 2019-01-25 16:02:35.184 -02 
[73403] ERROR:  insert or update on table "e026par_p2019" violates foreign key 
constraint "e026par_p
2019_tdoemp_fkey"
Jan 25 16:02:35 FreeBSD11 postgres[73403]: [6-2] 2019-01-25 16:02:35.184 -02 
[73403] DETAIL:  Key (tdoemp, codemp, codfil, origem, sercai, numdoc, tdopes, 
codpes, datpar)=(1, 59
84305, 1, 55, 1, 160, 1, 5984305000107, 201901) is not present in table 
"e026pag_p2019".
Jan 25 16:02:35 FreeBSD11 postgres[73403]: [6-3] 2019-01-25 16:02:35.184 -02 
[73403] STATEMENT:  COMMIT
Jan 25 16:03:51 FreeBSD11 kernel: pid 78639 (postgres), uid 770: exited on 
signal 11 (core dumped)


> >
> > This is our test machine and some days ago we try to increase memory and
> > vcpu, but the problem persist.
> >
> >
> > Some information:
> > root@FreeBSD11:~ # uname -a
> > FreeBSD FreeBSD11.2 11.2-RELEASE-p4 FreeBSD 11.2-RELEASE-p4 #0: Thu Sep
> 27
> > 08:16:24 UTC 2018
> > r...@amd64-builder.daemonology.net:/usr/obj/usr/src/sys/GENERIC  amd64
> >
> >
> > root@FreeBSD11:~ # swapinfo -h
> > Device  1K-blocks UsedAvail Capacity
> > /dev/da0s1b   4194268   0B 4.0G 0%
> >
> >
> > root@FreeBSD11:~ # df -h
> > Filesystem SizeUsed   Avail Capacity  Mounted on
> > /dev/da0s1a141G 13G117G10%/
> > devfs  1.0K1.0K  0B   100%/dev
> >
> >
> > # select version();
> > PostgreSQL 11.0 on amd64-portbld-freebsd11.2, compiled by FreeBSD clang
> > version 6.0.0 (tags/RELEASE_600/final 326565) (based on LLVM 6.0.0), 64-
> bit
> >

Re: Postgresql Crasching

[Tom Lane]

=?iso-8859-1?Q?M=E1rcio_Ant=F4nio_Sepp?=  writes:
> We are experiencing the follow error in last few weeks:
> Jan 25 16:03:51 FreeBSD11 kernel: pid 78639 (postgres), uid 770: exited on
> signal 11 (core dumped)

> # select version();
> PostgreSQL 11.0 on amd64-portbld-freebsd11.2, compiled by FreeBSD clang
> version 6.0.0 (tags/RELEASE_600/final 326565) (based on LLVM 6.0.0), 64-bit

First thing to try is updating to 11.1, just to see if problem is already
fixed.  If not, find out what query is crashing (see postmaster log),
and if you can get a stack trace out of one of the core files, that'd
be pretty helpful too.

https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend

We might need to ask for a self-contained test case, if the stack
trace doesn't make it clear what's failing.

regards, tom lane

Re: Postgresql Crasching

[Adrian Klaver]

On 1/25/19 10:21 AM, Márcio Antônio Sepp wrote:


Hi all,


We are experiencing the follow error in last few weeks:

Jan 25 16:03:51 FreeBSD11 kernel: pid 78639 (postgres), uid 770: exited on
signal 11 (core dumped)
Jan 25 16:03:51 FreeBSD11 postgres[73403]: [7-1] 2019-01-25 16:03:51.135 -02
[73403] WARNING:  terminating connection because of crash of another server
process
Jan 25 16:03:51 FreeBSD11 postgres[73403]: [7-2] 2019-01-25 16:03:51.135 -02
[73403] DETAIL:  The postmaster has commanded this server process to roll
back the current transacti
on and exit, because another server process exited abnormally and possibly
corrupted shared memory.
Jan 25 16:03:51 FreeBSD11 postgres[73403]: [7-3] 2019-01-25 16:03:51.135 -02
[73403] HINT:  In a moment you should be able to reconnect to the database
and repeat your command.


What is the logs just before the above?




This is our test machine and some days ago we try to increase memory and
vcpu, but the problem persist.


Some information:
root@FreeBSD11:~ # uname -a
FreeBSD FreeBSD11.2 11.2-RELEASE-p4 FreeBSD 11.2-RELEASE-p4 #0: Thu Sep 27
08:16:24 UTC 2018
r...@amd64-builder.daemonology.net:/usr/obj/usr/src/sys/GENERIC  amd64


root@FreeBSD11:~ # swapinfo -h
Device  1K-blocks UsedAvail Capacity
/dev/da0s1b   4194268   0B 4.0G 0%


root@FreeBSD11:~ # df -h
Filesystem SizeUsed   Avail Capacity  Mounted on
/dev/da0s1a141G 13G117G10%/
devfs  1.0K1.0K  0B   100%/dev


# select version();
PostgreSQL 11.0 on amd64-portbld-freebsd11.2, compiled by FreeBSD clang
version 6.0.0 (tags/RELEASE_600/final 326565) (based on LLVM 6.0.0), 64-bit


Any suggestion?

Tks a lot


--
Att
Márcio A. Sepp






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

RE: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

[Duarte Carreira]

Tom, can't thank you enough.

Now, the 2nd query and results are like so:
select pg_describe_object(classid,objid,objsubid), deptype from pg_depend where 
refobjid = 'sde.st_envelope'::regtype;
"function st_envelope_in(cstring)";"n"
"function st_envelope_out(st_envelope)";"n"
"type st_envelope[]";"i"
"function st_envelope_union(st_envelope,st_envelope)";"n"
"function st_envelope_union(st_envelope,st_envelope)";"n"
"function st_envelope_union(st_envelope,st_envelope)";"n"
"function st_envelope_intersects(st_envelope,st_envelope)";"n"
"function st_envelope_intersects(st_envelope,st_envelope)";"n"
"function st_envelope_intersects(st_envelope,st_envelope)";"n"
"function st_envelope_equal(st_envelope,st_envelope)";"n"
"function st_envelope_equal(st_envelope,st_envelope)";"n"
"function st_geo_gist_equal(st_envelope,st_envelope,internal)";"n"
"function st_geo_gist_equal(st_envelope,st_envelope,internal)";"n"

I'll try pg_upgrade again and let you know how it goes. (fingers crossed)

Thanks,
Duarte

-Mensagem original-
De: Tom Lane  
Enviada: 25 de janeiro de 2019 18:19
Para: Duarte Carreira 
Cc: pgsql-gene...@postgresql.org
Assunto: Re: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

Duarte Carreira  writes:
> So I proceeded with the 2 queries:

> select pg_describe_object(refclassid,refobjid,refobjsubid), deptype 
> from pg_depend where objid = 'sde.st_envelope'::regtype; "schema sde";"n"
> "function st_envelope_in(cstring)";"n"
> "function st_envelope_out(st_envelope)";"n"

OK, that looks about like what I'd expect.

> select pg_describe_object(classid,objid,objsubid), deptype from 
> pg_depend where refobjid = 'sde.st_envelope'::regtype; "type 
> st_envelope[]";"i"
> "function st_envelope_union(st_envelope,st_envelope)";"n"
> "function st_envelope_union(st_envelope,st_envelope)";"n"
> "function st_envelope_union(st_envelope,st_envelope)";"n"
> "function st_envelope_intersects(st_envelope,st_envelope)";"n"
> "function st_envelope_intersects(st_envelope,st_envelope)";"n"
> "function st_envelope_intersects(st_envelope,st_envelope)";"n"
> "function st_envelope_equal(st_envelope,st_envelope)";"n"
> "function st_envelope_equal(st_envelope,st_envelope)";"n"
> "function st_geo_gist_equal(st_envelope,st_envelope,internal)";"n"
> "function st_geo_gist_equal(st_envelope,st_envelope,internal)";"n"

Ah-hah --- st_envelope_in and st_envelope_out are not mentioned here?
That explains your problem.  You'd need to add those two rows to pg_depend, 
which could go something like

insert into pg_depend (classid, objid, objsubid,
   refclassid, refobjid, refobjsubid, deptype) values (
  'pg_proc'::regclass,
  'sde.st_envelope_in(cstring)'::regprocedure,
  0,
  'pg_type'::regclass,
  'sde.st_envelope'::regtype,
  0,
  'n');

insert into pg_depend (classid, objid, objsubid,
   refclassid, refobjid, refobjsubid, deptype) values (
  'pg_proc'::regclass,
  'sde.st_envelope_out(sde.st_envelope)'::regprocedure,
  0,
  'pg_type'::regclass,
  'sde.st_envelope'::regtype,
  0,
  'n');

I suppose the evidence about what happened to those rows is long gone, so 
there's not much point in doing anything but patching things up to the point 
where you can run pg_upgrade.

regards, tom lane

Postgresql Crasching

[Márcio Antônio Sepp]

Hi all,


We are experiencing the follow error in last few weeks:

Jan 25 16:03:51 FreeBSD11 kernel: pid 78639 (postgres), uid 770: exited on
signal 11 (core dumped)
Jan 25 16:03:51 FreeBSD11 postgres[73403]: [7-1] 2019-01-25 16:03:51.135 -02
[73403] WARNING:  terminating connection because of crash of another server
process
Jan 25 16:03:51 FreeBSD11 postgres[73403]: [7-2] 2019-01-25 16:03:51.135 -02
[73403] DETAIL:  The postmaster has commanded this server process to roll
back the current transacti
on and exit, because another server process exited abnormally and possibly
corrupted shared memory.
Jan 25 16:03:51 FreeBSD11 postgres[73403]: [7-3] 2019-01-25 16:03:51.135 -02
[73403] HINT:  In a moment you should be able to reconnect to the database
and repeat your command.


This is our test machine and some days ago we try to increase memory and
vcpu, but the problem persist.


Some information:
root@FreeBSD11:~ # uname -a
FreeBSD FreeBSD11.2 11.2-RELEASE-p4 FreeBSD 11.2-RELEASE-p4 #0: Thu Sep 27
08:16:24 UTC 2018
r...@amd64-builder.daemonology.net:/usr/obj/usr/src/sys/GENERIC  amd64


root@FreeBSD11:~ # swapinfo -h
Device  1K-blocks UsedAvail Capacity
/dev/da0s1b   4194268   0B 4.0G 0%


root@FreeBSD11:~ # df -h
Filesystem SizeUsed   Avail Capacity  Mounted on
/dev/da0s1a141G 13G117G10%/
devfs  1.0K1.0K  0B   100%/dev


# select version();
PostgreSQL 11.0 on amd64-portbld-freebsd11.2, compiled by FreeBSD clang
version 6.0.0 (tags/RELEASE_600/final 326565) (based on LLVM 6.0.0), 64-bit


Any suggestion? 

Tks a lot


--
Att
Márcio A. Sepp

Re: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

[Tom Lane]

Duarte Carreira  writes:
> So I proceeded with the 2 queries:

> select pg_describe_object(refclassid,refobjid,refobjsubid), deptype from 
> pg_depend where objid = 'sde.st_envelope'::regtype;
> "schema sde";"n"
> "function st_envelope_in(cstring)";"n"
> "function st_envelope_out(st_envelope)";"n"

OK, that looks about like what I'd expect.

> select pg_describe_object(classid,objid,objsubid), deptype from pg_depend 
> where refobjid = 'sde.st_envelope'::regtype;
> "type st_envelope[]";"i"
> "function st_envelope_union(st_envelope,st_envelope)";"n"
> "function st_envelope_union(st_envelope,st_envelope)";"n"
> "function st_envelope_union(st_envelope,st_envelope)";"n"
> "function st_envelope_intersects(st_envelope,st_envelope)";"n"
> "function st_envelope_intersects(st_envelope,st_envelope)";"n"
> "function st_envelope_intersects(st_envelope,st_envelope)";"n"
> "function st_envelope_equal(st_envelope,st_envelope)";"n"
> "function st_envelope_equal(st_envelope,st_envelope)";"n"
> "function st_geo_gist_equal(st_envelope,st_envelope,internal)";"n"
> "function st_geo_gist_equal(st_envelope,st_envelope,internal)";"n"

Ah-hah --- st_envelope_in and st_envelope_out are not mentioned here?
That explains your problem.  You'd need to add those two rows to pg_depend,
which could go something like

insert into pg_depend (classid, objid, objsubid,
   refclassid, refobjid, refobjsubid, deptype)
values (
  'pg_proc'::regclass,
  'sde.st_envelope_in(cstring)'::regprocedure,
  0,
  'pg_type'::regclass,
  'sde.st_envelope'::regtype,
  0,
  'n');

insert into pg_depend (classid, objid, objsubid,
   refclassid, refobjid, refobjsubid, deptype)
values (
  'pg_proc'::regclass,
  'sde.st_envelope_out(sde.st_envelope)'::regprocedure,
  0,
  'pg_type'::regclass,
  'sde.st_envelope'::regtype,
  0,
  'n');

I suppose the evidence about what happened to those rows is long gone,
so there's not much point in doing anything but patching things up to
the point where you can run pg_upgrade.

regards, tom lane

RE: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

[Duarte Carreira]

Just a footnote. This is not a postgis object, it's an esri object. Belongs to 
the sde schema that is installed in every database where esri software is 
installed in, namely arcgis server.

-Mensagem original-
De: Duarte Carreira 
Enviada: 25 de janeiro de 2019 18:03
Para: 'Tom Lane' 
Cc: pgsql-gene...@postgresql.org
Assunto: RE: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

First, I'd like to apologize for posting the dump file to the whole list... I 
wish I could delete it, at least the attachment...

I ran the explain plan, and got a seq scan, so no faulty index:
"Sort  (cost=5073.45..5195.00 rows=48622 width=17)"
"  Sort Key: classid, objid"
"  ->  Seq Scan on pg_depend  (cost=0.00..1288.39 rows=48622 width=17)"
"Filter: ((deptype <> 'p'::"char") AND (deptype <> 'e'::"char"))"

So I proceeded with the 2 queries:

select pg_describe_object(refclassid,refobjid,refobjsubid), deptype from 
pg_depend where objid = 'sde.st_envelope'::regtype; "schema sde";"n"
"function st_envelope_in(cstring)";"n"
"function st_envelope_out(st_envelope)";"n"

select pg_describe_object(classid,objid,objsubid), deptype from pg_depend where 
refobjid = 'sde.st_envelope'::regtype; "type st_envelope[]";"i"
"function st_envelope_union(st_envelope,st_envelope)";"n"
"function st_envelope_union(st_envelope,st_envelope)";"n"
"function st_envelope_union(st_envelope,st_envelope)";"n"
"function st_envelope_intersects(st_envelope,st_envelope)";"n"
"function st_envelope_intersects(st_envelope,st_envelope)";"n"
"function st_envelope_intersects(st_envelope,st_envelope)";"n"
"function st_envelope_equal(st_envelope,st_envelope)";"n"
"function st_envelope_equal(st_envelope,st_envelope)";"n"
"function st_geo_gist_equal(st_envelope,st_envelope,internal)";"n"
"function st_geo_gist_equal(st_envelope,st_envelope,internal)";"n"

But I couldn't figure out what this means...

Duarte

-Mensagem original-
De: Tom Lane 
Enviada: 25 de janeiro de 2019 16:04
Para: Duarte Carreira 
Cc: pgsql-gene...@postgresql.org
Assunto: Re: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

Duarte Carreira  writes:
> Sure, I'll be sending you the dump shortly off-list.

Hmph.  So the problem seems to be that pg_dump isn't emitting a "shell type" 
command for sde.st_envelope.  The first mention of that type is in the "CREATE 
FUNCTION sde.st_envelope_in..." command, and that won't have done anything to 
force the correct type OID to be assigned, and indeed what you get happens to 
conflict with some other type.

Whatever the problem is only affects that one type --- if you grep the pg_dump 
output for CREATE TYPE, you see

CREATE TYPE sde.se_coord;
CREATE TYPE sde.se_coord (
CREATE TYPE sde.se_extent;
CREATE TYPE sde.se_extent (
CREATE TYPE sde.st_envelope (
CREATE TYPE sde.st_geometry;
CREATE TYPE sde.st_geometry (
CREATE TYPE sde.st_pixeldata;
CREATE TYPE sde.st_pixeldata (
CREATE TYPE sde.st_raster;
CREATE TYPE sde.st_raster (
CREATE TYPE sde.st_state_data_type;
CREATE TYPE sde.st_state_data_type (

The lines with just a type name and no parameter list are the shell type 
creation commands, and there's one for each base type ...
except st_envelope.

I'm not very sure what's going on here, but the logic that pg_dump uses to 
decide whether to print a shell type assumes that there will be circular 
dependencies between the base type and its I/O functions.
I wonder whether pg_depend has gotten damaged in your source database.

One thing you could check to start with is to manually try pg_dump's 
dependency-fetch query in the problematic database:

explain SELECT classid, objid, refclassid, refobjid, deptype FROM pg_depend 
WHERE deptype != 'p' AND deptype != 'e' ORDER BY 1,2;

If you get an indexscan plan, then it's possible that the issue is a corrupted 
index, and "REINDEX pg_depend" would fix it.

But I suspect that it's going to be a seqscan-and-sort, which would mean that 
the indexes couldn't be at fault and there actually is missing data in 
pg_depend.  (The 9.3 release series had some messy data-loss problems, so this 
conclusion isn't as astonishing as one could wish.)

The next thing to look at would be the dependencies associated with the 
st_envelope type.  Try queries like this:

regression=# select pg_describe_object(refclassid,refobjid,refobjsubid), 
deptype from pg_depend where objid = 'public.widget'::regtype;
 pg_describe_object  | deptype 
-+-
 schema public   | n
 function widget_in(cstring) | n
 function widget_out(widget) | n
(3 rows)

regression=# select pg_describe_object(classid,objid,objsubid), deptype from 
pg_depend where refobjid = 'public.widget'::regtype;
 pg_describe_object  | deptype 
-+-
 function widget_in(cstring) | n
 function widget_out(widget) | n
 type widget[]   | i
 ... more stuff ...

I don't have postgis installed 

RE: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

[Duarte Carreira]

First, I'd like to apologize for posting the dump file to the whole list... I 
wish I could delete it, at least the attachment...

I ran the explain plan, and got a seq scan, so no faulty index:
"Sort  (cost=5073.45..5195.00 rows=48622 width=17)"
"  Sort Key: classid, objid"
"  ->  Seq Scan on pg_depend  (cost=0.00..1288.39 rows=48622 width=17)"
"Filter: ((deptype <> 'p'::"char") AND (deptype <> 'e'::"char"))"

So I proceeded with the 2 queries:

select pg_describe_object(refclassid,refobjid,refobjsubid), deptype from 
pg_depend where objid = 'sde.st_envelope'::regtype;
"schema sde";"n"
"function st_envelope_in(cstring)";"n"
"function st_envelope_out(st_envelope)";"n"

select pg_describe_object(classid,objid,objsubid), deptype from pg_depend where 
refobjid = 'sde.st_envelope'::regtype;
"type st_envelope[]";"i"
"function st_envelope_union(st_envelope,st_envelope)";"n"
"function st_envelope_union(st_envelope,st_envelope)";"n"
"function st_envelope_union(st_envelope,st_envelope)";"n"
"function st_envelope_intersects(st_envelope,st_envelope)";"n"
"function st_envelope_intersects(st_envelope,st_envelope)";"n"
"function st_envelope_intersects(st_envelope,st_envelope)";"n"
"function st_envelope_equal(st_envelope,st_envelope)";"n"
"function st_envelope_equal(st_envelope,st_envelope)";"n"
"function st_geo_gist_equal(st_envelope,st_envelope,internal)";"n"
"function st_geo_gist_equal(st_envelope,st_envelope,internal)";"n"

But I couldn't figure out what this means...

Duarte

-Mensagem original-
De: Tom Lane  
Enviada: 25 de janeiro de 2019 16:04
Para: Duarte Carreira 
Cc: pgsql-gene...@postgresql.org
Assunto: Re: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

Duarte Carreira  writes:
> Sure, I'll be sending you the dump shortly off-list.

Hmph.  So the problem seems to be that pg_dump isn't emitting a "shell type" 
command for sde.st_envelope.  The first mention of that type is in the "CREATE 
FUNCTION sde.st_envelope_in..." command, and that won't have done anything to 
force the correct type OID to be assigned, and indeed what you get happens to 
conflict with some other type.

Whatever the problem is only affects that one type --- if you grep the pg_dump 
output for CREATE TYPE, you see

CREATE TYPE sde.se_coord;
CREATE TYPE sde.se_coord (
CREATE TYPE sde.se_extent;
CREATE TYPE sde.se_extent (
CREATE TYPE sde.st_envelope (
CREATE TYPE sde.st_geometry;
CREATE TYPE sde.st_geometry (
CREATE TYPE sde.st_pixeldata;
CREATE TYPE sde.st_pixeldata (
CREATE TYPE sde.st_raster;
CREATE TYPE sde.st_raster (
CREATE TYPE sde.st_state_data_type;
CREATE TYPE sde.st_state_data_type (

The lines with just a type name and no parameter list are the shell type 
creation commands, and there's one for each base type ...
except st_envelope.

I'm not very sure what's going on here, but the logic that pg_dump uses to 
decide whether to print a shell type assumes that there will be circular 
dependencies between the base type and its I/O functions.
I wonder whether pg_depend has gotten damaged in your source database.

One thing you could check to start with is to manually try pg_dump's 
dependency-fetch query in the problematic database:

explain SELECT classid, objid, refclassid, refobjid, deptype FROM pg_depend 
WHERE deptype != 'p' AND deptype != 'e' ORDER BY 1,2;

If you get an indexscan plan, then it's possible that the issue is a corrupted 
index, and "REINDEX pg_depend" would fix it.

But I suspect that it's going to be a seqscan-and-sort, which would mean that 
the indexes couldn't be at fault and there actually is missing data in 
pg_depend.  (The 9.3 release series had some messy data-loss problems, so this 
conclusion isn't as astonishing as one could wish.)

The next thing to look at would be the dependencies associated with the 
st_envelope type.  Try queries like this:

regression=# select pg_describe_object(refclassid,refobjid,refobjsubid), 
deptype from pg_depend where objid = 'public.widget'::regtype;
 pg_describe_object  | deptype 
-+-
 schema public   | n
 function widget_in(cstring) | n
 function widget_out(widget) | n
(3 rows)

regression=# select pg_describe_object(classid,objid,objsubid), deptype from 
pg_depend where refobjid = 'public.widget'::regtype;
 pg_describe_object  | deptype 
-+-
 function widget_in(cstring) | n
 function widget_out(widget) | n
 type widget[]   | i
 ... more stuff ...

I don't have postgis installed here, so this example is looking at a type 
"public.widget", but of course what you want to look at is sde.st_envelope.  If 
you don't see links to st_envelope_in and st_envelope_out in both queries, then 
we've found the problem.

Fixing it is a bit trickier, but in principle you could manually insert the 
missing row(s) once you know what they need to be.

regards, tom lane

Re: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

[Tom Lane]

Duarte Carreira  writes:
> Sure, I'll be sending you the dump shortly off-list.

Hmph.  So the problem seems to be that pg_dump isn't emitting a "shell
type" command for sde.st_envelope.  The first mention of that type is
in the "CREATE FUNCTION sde.st_envelope_in..." command, and that won't
have done anything to force the correct type OID to be assigned, and
indeed what you get happens to conflict with some other type.

Whatever the problem is only affects that one type --- if you grep
the pg_dump output for CREATE TYPE, you see

CREATE TYPE sde.se_coord;
CREATE TYPE sde.se_coord (
CREATE TYPE sde.se_extent;
CREATE TYPE sde.se_extent (
CREATE TYPE sde.st_envelope (
CREATE TYPE sde.st_geometry;
CREATE TYPE sde.st_geometry (
CREATE TYPE sde.st_pixeldata;
CREATE TYPE sde.st_pixeldata (
CREATE TYPE sde.st_raster;
CREATE TYPE sde.st_raster (
CREATE TYPE sde.st_state_data_type;
CREATE TYPE sde.st_state_data_type (

The lines with just a type name and no parameter list are the shell
type creation commands, and there's one for each base type ...
except st_envelope.

I'm not very sure what's going on here, but the logic that pg_dump
uses to decide whether to print a shell type assumes that there will
be circular dependencies between the base type and its I/O functions.
I wonder whether pg_depend has gotten damaged in your source database.

One thing you could check to start with is to manually try pg_dump's
dependency-fetch query in the problematic database:

explain SELECT classid, objid, refclassid, refobjid, deptype
FROM pg_depend WHERE deptype != 'p' AND deptype != 'e' ORDER BY 1,2;

If you get an indexscan plan, then it's possible that the issue
is a corrupted index, and "REINDEX pg_depend" would fix it.

But I suspect that it's going to be a seqscan-and-sort, which would mean
that the indexes couldn't be at fault and there actually is missing data
in pg_depend.  (The 9.3 release series had some messy data-loss problems,
so this conclusion isn't as astonishing as one could wish.)

The next thing to look at would be the dependencies associated with
the st_envelope type.  Try queries like this:

regression=# select pg_describe_object(refclassid,refobjid,refobjsubid), 
deptype from pg_depend where objid = 'public.widget'::regtype;
 pg_describe_object  | deptype 
-+-
 schema public   | n
 function widget_in(cstring) | n
 function widget_out(widget) | n
(3 rows)

regression=# select pg_describe_object(classid,objid,objsubid), deptype from 
pg_depend where refobjid = 'public.widget'::regtype;
 pg_describe_object  | deptype 
-+-
 function widget_in(cstring) | n
 function widget_out(widget) | n
 type widget[]   | i
 ... more stuff ...

I don't have postgis installed here, so this example is looking at
a type "public.widget", but of course what you want to look at is
sde.st_envelope.  If you don't see links to st_envelope_in and
st_envelope_out in both queries, then we've found the problem.

Fixing it is a bit trickier, but in principle you could manually
insert the missing row(s) once you know what they need to be.

regards, tom lane

RE: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

[Duarte Carreira]

Sure, I'll be sending you the dump shortly off-list.

Duarte

-Mensagem original-
De: Tom Lane  
Enviada: 25 de janeiro de 2019 14:28
Para: Duarte Carreira 
Cc: pgsql-gene...@postgresql.org
Assunto: Re: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

Duarte Carreira  writes:
> Tom, just did a pg_dump -s from 9.3 and there are no warnings in the output 
> file. It goes smoothly and fast (few seconds). Database is around 50GB, 
> schema 9MB. Has postgis and a few more extensions - ogr_fdw (from postigs), 
> file_fdw, postgres_fdw, table_func.

Hmph.  Would you be willing to send me a schema-only dump (off-list!) of the 
problematic database?  The best format would be an "-Fc -s"
pg_dump output, because that would show what pg_dump thinks the dependencies 
are.  Be sure you make it with the newer pg_dump.

regards, tom lane

Re: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

[Tom Lane]

Duarte Carreira  writes:
> Tom, just did a pg_dump -s from 9.3 and there are no warnings in the output 
> file. It goes smoothly and fast (few seconds). Database is around 50GB, 
> schema 9MB. Has postgis and a few more extensions - ogr_fdw (from postigs), 
> file_fdw, postgres_fdw, table_func.

Hmph.  Would you be willing to send me a schema-only dump (off-list!)
of the problematic database?  The best format would be an "-Fc -s"
pg_dump output, because that would show what pg_dump thinks the
dependencies are.  Be sure you make it with the newer pg_dump.

regards, tom lane

RE: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

[Duarte Carreira]

Tom, just did a pg_dump -s from 9.3 and there are no warnings in the output 
file. It goes smoothly and fast (few seconds). Database is around 50GB, schema 
9MB. Has postgis and a few more extensions - ogr_fdw (from postigs), file_fdw, 
postgres_fdw, table_func.
I also did a pg_dumpall -s and also no warnings.

I do a pg_dump full backup every week with no incidents.

Versions are:

Windows x64
9.3.22
9.5.15

Thanks for taking a look.
Duarte

De: Duarte Carreira
Enviada: 24 de janeiro de 2019 21:52
Para: Tom Lane 
Cc: pgsql-gene...@postgresql.org
Assunto: Re: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

Hi Tom.
It's 9.5 latest.
I'll try pgdump tomorrow.
Obter o Outlook para Android


From: Tom Lane mailto:t...@sss.pgh.pa.us>>
Sent: Thursday, January 24, 2019 9:02:25 PM
To: Duarte Carreira
Cc: pgsql-gene...@postgresql.org
Subject: Re: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5

Duarte Carreira mailto:dcarre...@edia.pt>> writes:
> I've trying to upgrade a 9.3 instance to 9.5 using pg_upgrade and facing this 
> issue...

9.5.what?

Perusing the commit logs, I note that 9.5.3 included a fix for a
pg_upgrade issue that could possibly lead to this symptom, see
https://git.postgresql.org/gitweb/?p=postgresql.git=commitdiff=196870f2f

If you are on 9.5.recent, it'd be worth looking closer, because this
is certainly pretty odd.  One wouldn't expect a CREATE FUNCTION to
result in assignment of a type OID, at least not in pg_dump/pg_upgrade
scripts --- they should always put out a shell CREATE TYPE first.

By any chance, if you attempt a "pg_dump -s" from the problematic database,
does it emit any warnings (about dependency loops, perhaps)?

regards, tom lane

RE: Full text search with more than one word

[Ritanjali Majihee]

Hi Magnus ,

 

Example- Search keyword is ("Marketing Coordinator" OR "Marketing Specialist" 
OR "Marketing Associate" ) AND "Creative Suite".

 

Thank you for your information , phraseto_tsquery() is supporting two word 
search or phase search where as it is not supporting logical operator search 
and to_tsquery() is supporting logical operator search but not supporting two 
word search or phase search. If we need both functionally  in full text search 
is there any other way we can proceed .

 

Can you please help us solve this issue. 

 

Thanks,

Ritanjali

 

 

From: mag...@hagander.net [mailto:mag...@hagander.net] 
Sent: Friday, January 18, 2019 8:45 PM
To: Ritanjali Majihee
Cc: pgsql-general General; Ambiger, Mahantesh; mahantesh.ambi...@yahoo.com
Subject: Re: Full text search with more than one word

 

(please don't top-post on the postgresql mailinglists)

 

On Fri, Jan 18, 2019 at 3:57 PM Ritanjali Majihee  
wrote:

Hi Magnus,

 

exact  Search keyword is ("Marketing Coordinator" OR "Marketing Specialist" OR 
"Marketing Associate" ) AND "Creative Suite".

 

Where condition like below syntax we are using in Postgresql function

 

SELECT * from tablename where html_tokens  @@ to_tsquery( ' || 
quote_literal(ikeyword) || ')

 

>From search keyword we are getting result but not as expected .It is not 
>searching together "Marketing Coordinator" string, It is giving  result like 
>"Marketing and Coordinator".

Can you please help us solve this issue. 

 

 

You should look into phraseto_tsquery() for searching for phrases, and 
websearchto_tsquery() for the kind of complete search with or and and in it 
that you are looking for. to_tsquery doesn't do any parsing like that.

 

//Magnus

 

Re: Full text search with more than one word

[Magnus Hagander]

Again, please do not top-post on the postgresql mailinglists, it's
considered rude.


On Fri, Jan 25, 2019 at 12:56 PM Ritanjali Majihee <
rmaj...@247headhunting.com> wrote:

> Hi Magnus ,
>
>
>
> Example- Search keyword is ("Marketing Coordinator" OR "Marketing
> Specialist" OR "Marketing Associate" ) AND "Creative Suite".
>
>
>
> Thank you for your information , phraseto_tsquery() is supporting two word
> search or phase search where as it is not supporting logical operator
> search and to_tsquery() is supporting logical operator search but not
> supporting two word search or phase search. If we need both functionally
>  in full text search is there any other way we can proceed .
>
>
>
> Can you please help us solve this issue.
>


Yes, as I said in my original email, check out the websearchto_tsquery
function.

//Magnus



>
> *From:* mag...@hagander.net [mailto:mag...@hagander.net]
> *Sent:* Friday, January 18, 2019 8:45 PM
> *To:* Ritanjali Majihee
> *Cc:* pgsql-general General; Ambiger, Mahantesh;
> mahantesh.ambi...@yahoo.com
> *Subject:* Re: Full text search with more than one word
>
>
>
> (please don't top-post on the postgresql mailinglists)
>
>
>
> On Fri, Jan 18, 2019 at 3:57 PM Ritanjali Majihee <
> rmaj...@247headhunting.com> wrote:
>
> Hi Magnus,
>
>
>
> exact  Search keyword is ("Marketing Coordinator" OR "Marketing
> Specialist" OR "Marketing Associate" ) AND "Creative Suite".
>
>
>
> Where condition like below syntax we are using in Postgresql function
>
>
>
> SELECT * from tablename where html_tokens  @@ to_tsquery( ' ||
> quote_literal(ikeyword) || ')
>
>
>
> From search keyword we are getting result but not as expected .It is not
> searching together "Marketing Coordinator" string, It is giving  result
> like "Marketing and Coordinator".
>
> Can you please help us solve this issue.
>
>
>
>
>
> You should look into phraseto_tsquery() for searching for phrases, and
> websearchto_tsquery() for the kind of complete search with or and and in it
> that you are looking for. to_tsquery doesn't do any parsing like that.
>
>
>
> //Magnus
>
>
>


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

Re: Sv: Re: Geographical multi-master replication

[Andrew Smith]

On Fri., 25 Jan. 2019, 8:40 pm Andreas Kretschmer 
>
> Am 25.01.19 um 10:10 schrieb Andreas Joseph Krogh:
> > To my surprise I'm unable to find downloadable BDR3. I thought it was
> > an open-source extention to vanilla-pg-11, isn't that the case anymore?
>
> yeah, you have to sign a support contract. It works as a extension to
> vanilla-pg-11, but it's not open source.
>

Also, no Windows support, which makes it a non starter for our
organisation.

>

Re: Sv: Re: Geographical multi-master replication

[Andreas Kretschmer]

Am 25.01.19 um 10:10 schrieb Andreas Joseph Krogh:
To my surprise I'm unable to find downloadable BDR3. I thought it was 
an open-source extention to vanilla-pg-11, isn't that the case anymore?


yeah, you have to sign a support contract. It works as a extension to 
vanilla-pg-11, but it's not open source.



Regards, Andreas

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

Sv: Re: Geographical multi-master replication

[Andreas Joseph Krogh]

På fredag 25. januar 2019 kl. 06:45:43, skrev Andreas Kretschmer <
andr...@a-kretschmer.de >: 

 Am 25.01.19 um 06:10 schrieb Jeremy Finzel:
 >
 >     The problem is that the version for BDR 1.0.7, which has an
 >     implementation for postgres 9.4, will be on end of live at the end
 >     of this year. Unfortunately the paid solution is out of our
 >     budget, so we currently have two options: find an alternative or
 >     remove the multi-region implementation. We are currently looking
 >     for alternatives.
 >
 >
 > You are missing all of the alternatives here.  Why don't you consider
 > upgrading from postgres 9.4 and with it to a supported version of
 > BDR?  There is nothing better you can do to keep your infrastructure
 > up to date, performant, secure, and actually meet your multi-master
 > needs than to upgrade to a newer version of postgres which does have
 > BDR support.
 >
 > Even "stock" postgres 9.4 is set for end of life soon. Upgrade!

 ACK!

 Sure, you have to pay for a support contract, and this isn't for free,
 but you will get a first-class support for BDR. If you really needs a
 worldwide distributed multi-master solution you should be able to buy that.


 Regards, Andreas   To my surprise I'm unable to find downloadable BDR3. I 
thought it was an open-source extention to vanilla-pg-11, isn't that the case 
anymore?   -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 
963 andr...@visena.com  www.visena.com