correcting tablespaces inside data folder
Hello. Yes I'm one of those guys who only recently realized the mess of having tablespaces inside the data directory... now I want to use pg_upgrade and it will make things even worse... Does anyone have a best approach to this problem? Fastest/safest? pg 9.3.x on win Thanks, Duarte
Re: correcting tablespaces inside data folder
Magnus, You mean changing the symlinks inside pg_tblspc? On Fri, Jul 6, 2018 at 5:49 PM Magnus Hagander wrote: > On Fri, Jul 6, 2018 at 6:42 PM, Duarte Carreira > wrote: > >> Hello. >> >> Yes I'm one of those guys who only recently realized the mess of having >> tablespaces inside the data directory... now I want to use pg_upgrade and >> it will make things even worse... >> >> Does anyone have a best approach to this problem? Fastest/safest? >> >> pg 9.3.x on win >> >> > If you can afford to shut the server down, the easiest is to shut it down, > move the tablespaces (with mv, and as long as you stay within the partition > it should be almost instant), update the symlinks to point to the new > location, and start it up again. > > -- > Magnus Hagander > Me: https://www.hagander.net/ <http://www.hagander.net/> > Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/> >
Re: correcting tablespaces inside data folder
On Fri, Jul 6, 2018 at 6:15 PM Magnus Hagander wrote: > (please don't top-post. It makes it hard to follow discussions) > > On Fri, Jul 6, 2018 at 7:05 PM, Duarte Carreira > wrote: > >> Magnus, >> You mean changing the symlinks inside pg_tblspc? >> >> > Yes. As long as the server is shut down, you can modify those symlinks. > Ok, I'll try on my laptop and see how it goes. Thanks. > > > >> >> On Fri, Jul 6, 2018 at 5:49 PM Magnus Hagander >> wrote: >> >>> On Fri, Jul 6, 2018 at 6:42 PM, Duarte Carreira >>> wrote: >>> >>>> Hello. >>>> >>>> Yes I'm one of those guys who only recently realized the mess of having >>>> tablespaces inside the data directory... now I want to use pg_upgrade and >>>> it will make things even worse... >>>> >>>> Does anyone have a best approach to this problem? Fastest/safest? >>>> >>>> pg 9.3.x on win >>>> >>>> >>> If you can afford to shut the server down, the easiest is to shut it >>> down, move the tablespaces (with mv, and as long as you stay within the >>> partition it should be almost instant), update the symlinks to point to the >>> new location, and start it up again. >>> >>> -- >>> Magnus Hagander >>> Me: https://www.hagander.net/ <http://www.hagander.net/> >>> Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/> >>> >> > > > -- > Magnus Hagander > Me: https://www.hagander.net/ <http://www.hagander.net/> > Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/> >
Re: correcting tablespaces inside data folder
Hi. On Fri, Jul 6, 2018 at 6:30 PM Duarte Carreira wrote: > > > On Fri, Jul 6, 2018 at 6:15 PM Magnus Hagander > wrote: > >> (please don't top-post. It makes it hard to follow discussions) >> >> On Fri, Jul 6, 2018 at 7:05 PM, Duarte Carreira >> wrote: >> >>> Magnus, >>> You mean changing the symlinks inside pg_tblspc? >>> >>> >> Yes. As long as the server is shut down, you can modify those symlinks. >> > > Ok, I'll try on my laptop and see how it goes. > Thanks. > Update: everything seems to be working fine. On windows you can't change the symlinks (called junctions on windows). You have to delete them with rd symlimk_id, and then recreate them pointing to the new location with: mklink /J symlink_id path_to_new_location Have to be specially carefull to recreate the symlinks correctly! Thanks again. Duarte > > >> >> >> >>> >>> On Fri, Jul 6, 2018 at 5:49 PM Magnus Hagander >>> wrote: >>> >>>> On Fri, Jul 6, 2018 at 6:42 PM, Duarte Carreira >>>> wrote: >>>> >>>>> Hello. >>>>> >>>>> Yes I'm one of those guys who only recently realized the mess of >>>>> having tablespaces inside the data directory... now I want to use >>>>> pg_upgrade and it will make things even worse... >>>>> >>>>> Does anyone have a best approach to this problem? Fastest/safest? >>>>> >>>>> pg 9.3.x on win >>>>> >>>>> >>>> If you can afford to shut the server down, the easiest is to shut it >>>> down, move the tablespaces (with mv, and as long as you stay within the >>>> partition it should be almost instant), update the symlinks to point to the >>>> new location, and start it up again. >>>> >>>> -- >>>> Magnus Hagander >>>> Me: https://www.hagander.net/ <http://www.hagander.net/> >>>> Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/> >>>> >>> >> >> >> -- >> Magnus Hagander >> Me: https://www.hagander.net/ <http://www.hagander.net/> >> Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/> >> >
duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5
store: creating COMMENT "EXTENSION "tablefunc""
pg_restore: creating TYPE "public.addbandarg"
pg_restore: creating COMMENT "public.TYPE "addbandarg""
pg_restore: creating TYPE "public.agg_count"
pg_restore: creating SHELL TYPE "public.raster"
pg_restore: creating FUNCTION "public.raster_in("cstring")"
pg_restore: creating FUNCTION "public.raster_out("public"."raster")"
pg_restore: creating TYPE "public.raster"
pg_restore: creating COMMENT "public.TYPE "raster""
pg_restore: creating TYPE "public.agg_samealignment"
pg_restore: creating SHELL TYPE "public.box2d"
pg_restore: creating FUNCTION "public.box2d_in("cstring")"
pg_restore: creating FUNCTION "public.box2d_out("public"."box2d")"
pg_restore: creating TYPE "public.box2d"
pg_restore: creating COMMENT "public.TYPE "box2d""
pg_restore: creating SHELL TYPE "public.box2df"
pg_restore: creating FUNCTION "public.box2df_in("cstring")"
pg_restore: creating FUNCTION "public.box2df_out("public"."box2df")"
pg_restore: creating TYPE "public.box2df"
pg_restore: creating SHELL TYPE "public.box3d"
pg_restore: creating FUNCTION "public.box3d_in("cstring")"
pg_restore: creating FUNCTION "public.box3d_out("public"."box3d")"
pg_restore: creating TYPE "public.box3d"
pg_restore: creating COMMENT "public.TYPE "box3d""
pg_restore: creating SHELL TYPE "public.geography"
pg_restore: creating FUNCTION "public.geography_analyze("internal")"
pg_restore: creating FUNCTION "public.geography_in("cstring", "oid", integer)"
pg_restore: creating FUNCTION "public.geography_out("public"."geography")"
pg_restore: creating FUNCTION "public.geography_recv("internal", "oid",
integer)"
pg_restore: creating FUNCTION "public.geography_send("public"."geography")"
pg_restore: creating FUNCTION "public.geography_typmod_in("cstring"[])"
pg_restore: creating FUNCTION "public.geography_typmod_out(integer)"
pg_restore: creating TYPE "public.geography"
pg_restore: creating COMMENT "public.TYPE "geography""
pg_restore: creating SHELL TYPE "public.geometry"
pg_restore: creating FUNCTION "public.geometry_analyze("internal")"
pg_restore: creating FUNCTION "public.geometry_in("cstring")"
pg_restore: creating FUNCTION "public.geometry_out("public"."geometry")"
pg_restore: creating FUNCTION "public.geometry_recv("internal")"
pg_restore: creating FUNCTION "public.geometry_send("public"."geometry")"
pg_restore: creating FUNCTION "public.geometry_typmod_in("cstring"[])"
pg_restore: creating FUNCTION "public.geometry_typmod_out(integer)"
pg_restore: creating TYPE "public.geometry"
pg_restore: creating COMMENT "public.TYPE "geometry""
pg_restore: creating TYPE "public.geometry_dump"
pg_restore: creating COMMENT "public.TYPE "geometry_dump""
pg_restore: creating TYPE "public.geomval"
pg_restore: creating COMMENT "public.TYPE "geomval""
pg_restore: creating SHELL TYPE "public.gidx"
pg_restore: creating FUNCTION "public.gidx_in("cstring")"
pg_restore: creating FUNCTION "public.gidx_out("public"."gidx")"
pg_restore: creating TYPE "public.gidx"
pg_restore: creating SHELL TYPE "public.pgis_abs"
pg_restore: creating FUNCTION "public.pgis_abs_in("cstring")"
pg_restore: creating FUNCTION "public.pgis_abs_out("public"."pgis_abs")"
pg_restore: creating TYPE "public.pgis_abs"
pg_restore: creating TYPE "public.rastbandarg"
pg_restore: creating COMMENT "public.TYPE "rastbandarg""
pg_restore: creating TYPE "public.reclassarg"
pg_restore: creating COMMENT "public.TYPE "reclassarg""
pg_restore: creating SHELL TYPE "public.spheroid"
pg_restore: creating FUNCTION "public.spheroid_in("cstring")"
pg_restore: creating FUNCTION "public.spheroid_out("public"."spheroid")"
pg_restore: creating TYPE "public.spheroid"
pg_restore: creating TYPE "public.summarystats"
pg_restore: creating COMMENT "public.TYPE "summarystats""
pg_restore: creating TYPE "public.tablefunc_crosstab_2"
pg_restore: creating TYPE "public.tablefunc_crosstab_3"
pg_restore: creating TYPE "public.tablefunc_crosstab_4"
pg_restore: creating TYPE "public.unionarg"
pg_restore: creating COMMENT "public.TYPE "unionarg""
pg_restore: creating TYPE "public.valid_detail"
pg_restore: creating SHELL TYPE "sde.se_coord"
pg_restore: creating FUNCTION "sde.st_coord_in("cstring")"
pg_restore: creating FUNCTION "sde.st_coord_out("sde"."se_coord")"
pg_restore: creating FUNCTION "sde.st_coord_recv("internal")"
pg_restore: creating FUNCTION "sde.st_coord_send("sde"."se_coord")"
pg_restore: creating TYPE "sde.se_coord"
pg_restore: creating SHELL TYPE "sde.se_extent"
pg_restore: creating FUNCTION "sde.st_extent_in("cstring")"
pg_restore: creating FUNCTION "sde.st_extent_out("sde"."se_extent")"
pg_restore: creating FUNCTION "sde.st_extent_recv("internal")"
pg_restore: creating FUNCTION "sde.st_extent_send("sde"."se_extent")"
pg_restore: creating TYPE "sde.se_extent"
pg_restore: creating FUNCTION "sde.st_envelope_in("cstring")"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 4712; 1255 1141726 FUNCTION
st_envelope_in("cstring") sde
pg_restore: [archiver (db)] could not execute query: ERROR: duplicate key
value violates unique constraint "pg_type_oid_index"
DETAIL: Key (oid)=(1142573) already exists.
Command was: CREATE FUNCTION "sde"."st_envelope_in"("cstring") RETURNS
"sde"."st_envelope"
LANGUAGE "c" IMMUTABLE STRICT
AS 'st_g...
command: "D:\Program Files\PostgreSQL\9.5\bin/pg_restore" --port 50432
--username ^"postgres^" --exit-on-error --verbose --dbname ^"dbname^=postgis^"
"pg_upgrade_dump_16393.custom" >> "pg_upgrade_dump_16393.log" 2>&1
Duarte Carreira
Diretor | Dep. Informa??o Geogr?fica e Cartografia
www.edia.pt<http://www.edia.pt>
www.alqueva.com.pt<http://www.alqueva.com.pt>
Tel. +351 284315100
[http://www.edia.pt/edia/images/rodape.jpg]<http://www.edia.pt>
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<https://aka.ms/ghei36> From: Tom Lane 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 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&a=commitdiff&h=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: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5
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<https://aka.ms/ghei36> 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<mailto: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&a=commitdiff&h=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: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5
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
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
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 -
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
RE: duplicate OID issue when using pg_upgrade to move from 9.3 to 9.5
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
Re: Query on postgres_fdw extension
Hello everyone. I got here after encountering the same difficulty, although on a much more mundane scenario. I'm used to fdw on a read-only basis. I was just inserting a new record on a foreign table and got blocked... and after much searching got here. Not to rant or anything, but I am completely surprised by this limitation. As far as I can see it is impossible to use fdw to insert records on 99% of tables, since all have some kind of primary sequential key. I'm just a user so cannot really understand the intricacies involved in this process. Tried to find past messages and up to 2013 without understanding the real problem. The simplest workaround seems to be to quit using auto-numbering mechanisms and implement numbering trigger functions, which is really just going back to the 90s... Another option would be a local function that would get the remote default and use it in a local insert trigger. The complexity is just orders of magnitude higher. We are talking about auto-numbering keys... I don't know... realistically what do you guys see as a best/simple approach? Having 2 tables seems to me the easiest, less complex solution, but it's hard on maintenance... And don't take this the wrong way, but is it really that hard to have a compromise: if there's a serial on the remote, then the user could change the local definition so to just send the "DEFAULT" keyword to the remote and let it figure it out? At least the user would have a chance of setting the preferred behavior without much fuss, on a per-table basis. And still use the basic functionality of serial/identity columns. PostgreSQL has such complex stuff that this seems odd to be left out. Well I hope I didn't cross over as negative or anything. I do love pgsql and always promote it as the best thing under the sun. Best regards, Duarte Laurenz Albe escreveu no dia quinta, 20/01/2022 à(s) 15:36: > On Fri, 2021-05-14 at 10:53 -0400, Tom Lane wrote: > > Swathi P writes: > > > Hence we decided to have the coordinator nodes as stateless and hence > > > declared the column with no serial/sequence. Let me know if this makes > > > sense. > > > > Attaching serial-sequence defaults on both sides would certainly not > > work very well, because the sequences wouldn't stay in sync. > > > > Unfortunately, postgres_fdw just doesn't have a good way right now > > to make use of dynamically-generated defaults at the remote server. > > If you leave out a column in your INSERT, it's going to compute > > and send the locally-defined default (which is just null in this > > case), so the remote's default expression is never used. > > > > I remember that we spent a great deal of effort in postgres_fdw's > > early days, trying to find a way that we could use the remote's > > defaults in cases like this. But everything we tried ended up > > causing horrible semantic inconsistencies, so we ended up with > > the always-use-the-local-default approach. There was some feeling > > that maybe this could be revisited later, but no one's done so. > > > > One conceivable workaround is to do your insertions through a > > foreign table that doesn't even have the serial column, so that > > the INSERT command received by the remote server lacks that > > column and the default gets applied. Probably too messy though. > > One possibility might be to define a trigger on the remote table > that fetches the next sequence value if you try to insert NULL. > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com > > > > > >
Re: Query on postgres_fdw extension
Thanks for your help!
I'm not going forward with the id generating scheme... I prefer to let the
bd do that work on its own. Sharding is way over my head.
For now I just created the 2 tables, one for inserting (without the id
column), another for everything else. It's awkward and prone to human error
but as long as nothing changes and no one deletes it thinking it's
garbage...
Thanks.
Vijaykumar Jain escreveu no dia quinta,
20/01/2022 à(s) 17:39:
> On Thu, 20 Jan 2022 at 21:29, Duarte Carreira
> wrote:
>
>> Hello everyone.
>>
>> I don't know... realistically what do you guys see as a best/simple
>> approach?
>>
>
> We implemented a custom sharding (directory sharding with lookup tables)
> layer of 10 shards, but it was write local, read global.
> the api was responsible for all rebalancing incase of hotspots.
> other api sharding examples ...
> Database Sharding: Solving Performance in a Multi-Tenant Restaurant Data
> Analytics System (gotenzo.com)
> <https://blog.gotenzo.com/tech/database-sharding-solving-performance-in-a-multi-tenant-restaurant-data-analytics-system>
>
>
> <https://blog.gotenzo.com/tech/database-sharding-solving-performance-in-a-multi-tenant-restaurant-data-analytics-system>
> although
> it worked really well, when you are maintaining it on your own, it gets
> really painful, much beyond id generation globally.
>
> i will not go into the details, but in short, sharded setup is not the
> same as local setup. there would be many more things that would not work as
> expected
> which would otherwise work really well on a standalone setup.
>
> writes over shard may work, but you realize it is over the network, so you
> can lock you table for a much longer duration and cause a much more serious
> outage,
> if you really wanted to have distributed writes with unique keys, you can
> go with uuid i think or have your own seq generator globally (see below).
>
>
> *Move ID generation out of the database to an ID generation service
> outside of the database… As soon as a piece of work enters their system, an
> ID gets assigned to it… and that ID generated in a way that is known to be
> globally unique within their system*
>
> A Better ID Generator For PostgreSQL | robconery
> <https://rob.conery.io/2014/05/29/a-better-id-generator-for-postgresql/>
> Index of /shard_manager/shard_manager-0.0.1/ (pgxn.org)
> <https://api.pgxn.org/src/shard_manager/shard_manager-0.0.1/> (pretty
> old but if you can use your coordinator server as a id_generator(), then
> you can generate ids which are globally unique)
> Sharding & IDs at Instagram. With more than 25 photos and 90 likes… | by
> Instagram Engineering | Instagram Engineering (instagram-engineering.com)
> <https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c>
>
> imho, do not try sharding manually, unless you have enough dbas to
> maintain the shards, try using citus, it would make a lot of the manual
> stuff easier.
>
> also, the below work arounds are bad, incase you just want to rush through
>
> postgres=# \c localdb
> You are now connected to database "localdb" as user "postgres".
> localdb=#
> localdb=# \dt
> Did not find any relations.
> localdb=# \det
> List of foreign tables
> Schema | Table |Server
> +---+---
> public | t | remote_server
> (1 row)
>
> localdb=# \det+ t
> List of foreign tables
> Schema | Table |Server | FDW options |
> Description
>
> +---+---++-
> public | t | remote_server | (schema_name 'public', table_name 't') |
> (1 row)
>
> localdb=# \det t
> List of foreign tables
> Schema | Table |Server
> +---+---
> public | t | remote_server
> (1 row)
>
> localdb=# create or replace function getnext() returns int as $_$ select
> id FROM dblink ('dbname = remotedb', $$ select nextval('t_id_seq') $$ )
> as newtable(id int); $_$ language sql;
> CREATE FUNCTION
> localdb=# \c remotedb
> You are now connected to database "remotedb" as user "postgres".
> remotedb=# \dt t
> List of relations
> Schema | Name | Type | Owner
> +--+---+--
> public | t| table | postgres
> (1 row)
>
> remotedb=# \ds t_id_seq
> List of relations
> Schema | Name | Type | Owner
> +--+--+--
> public | t_id_seq | sequence | postgres
> (1 row)
>
> remotedb=# \c localdb
> You are now connected to database "localdb" as user "postgres".
> localdb=# insert into t values (getnext(), 100);
> INSERT 0 1
> localdb=# insert into t values (getnext(), 100);
> INSERT 0 1
> localdb=# select * from t;
> id | col1
> +--
> 11 |4
> 12 |5
> 13 | 100
> 14 | 100
> (4 rows)
>
> just my opinion, ignore it not useful.
>
>
>
Re: Query on postgres_fdw extension
Hmmm... I don't think a view or trigger are necessary.
If we just create the 2 foreign tables, one complete and one without id,
you can simply insert into the table without id and it will work fine.
To select and show data, you use the "complete" table that has the id
column.
No need for trigger and view. If I understood correctly.
I have this 2 table setup working.
It's a workaround that quickly escalates out of hand though... with little
added value.
Thanks.
Laurenz Albe escreveu no dia sexta, 21/01/2022
à(s) 13:59:
> On Thu, 2022-01-20 at 15:59 +, Duarte Carreira wrote:
> > I got here after encountering the same difficulty, although on a much
> more mundane scenario.
> >
> > I'm used to fdw on a read-only basis. I was just inserting a new record
> on a foreign table
> > and got blocked... and after much searching got here.
> >
> > As far as I can see it is impossible to use fdw to insert records on 99%
> of tables,
> > since all have some kind of primary sequential key.
>
> Yes, this is tricky. You could use something like this:
>
> CREATE TABLE local (
>id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
>data text
> );
>
> CREATE FOREIGN TABLE remote (id bigint NOT NULL, data text)
>SERVER whatever OPTIONS (table_name 'local');
>
> CREATE FOREIGN TABLE remote_noid (data text)
>SERVER whatever OPTIONS (table_name 'local');
>
> CREATE VIEW v_remote AS SELECT * FROM remote;
>
> CREATE FUNCTION ins_trig() RETURNS trigger LANGUAGE plpgsql AS
> $$BEGIN
>INSERT INTO remote_noid (data) VALUES (NEW.data);
>RETURN NEW;
> END;$$;
>
> CREATE TRIGGER ins_trig INSTEAD OF INSERT ON v_remote
> FOR EACH ROW EXECUTE FUNCTION ins_trig();
>
> INSERT INTO v_remote (data) VALUES ('something');
>
> SELECT * FROM v_remote;
>
> id │ data
> ╪═══
> 1 │ something
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
