Re: [Postgres-xc-bugs] Corrupt Objects

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

Thanks everybody.   You should cleanup an object in pgxc_class as well like
delete from pgxc_class where pcrelid = MY_OID;

Good luck;
---
Koichi Suzuki

2013/11/16 West, William <ww...@uc...>:
> All,
>
> I was able to remove the invalid object based on some instructions that were
> for PostgreSQL from Oscar Raig Colon posted on stackoverflow.com. I got rid
> of the object so now maintenance routines like vacuum can be performed
> against the entire database. Here is the sequence of queries that seemed to
> partially work:
>
> First locate the objects OID:
>
> select c.oid from pg_class c join pg_namespace n on n.oid=c.relnamespace
> where
> c.relname = MY_TABLE and n.nspname= MY_SCHEMA;
>
> Then delete these records from the metadata tables:
>
> delete from pg_class where oid = MY_OID;
> delete from pg_depend where objid = MY_OID;
> delete from pg_constraint where conrelid = MY_OID;
>
> This deleted the table but I cannot re-create the table under the old name
> probably because it is still being referenced in some other metadata table I
> haven’t found yet. I hope this helps anyone trying to solve this problem.
>
> Regards,
>
> Bill West
>
> From: <West>, William West <ww...@uc...>
> Date: Thursday, November 14, 2013 at 3:58 PM
> To: Mason Sharp <ms...@tr...>
>
> Cc: Koichi Suzuki <koi...@gm...>,
> "pos...@li..."
> <pos...@li...>
> Subject: Re: [Postgres-xc-bugs] Corrupt Objects
>
> Mason/Koichi,
>
> I applied the patch and restarted the cluster. During startup I received the
> following message on datanode startups:
>
> LOG:  Falling back to local Xid. Was = 0, now is = 21590. AutovacuumLauncher
> = 1
> LOG:  Will fall back to local snapshot for XID = 21590, source = 0, gxmin =
> 0, autovac launch = 1, autovac = 0
>
> The log file is not reporting any new info that I can tell:
>
> LOG:  database system was shut down at 2013-11-14 15:39:04 PST
> LOG:  database system is ready to accept connections
> LOG:  autovacuum launcher started
> LOG:  failed to find proc 0x2ba62235adc0 in ProcArray
> LOG:  failed to find proc 0x2ba62235adc0 in ProcArray
> LOG:  failed to find proc 0x2ba62235adc0 in ProcArray
> ERROR:  XX000: could not open relation with OID 24594
> LOCATION:  relation_open, heapam.c:923
> STATEMENT:  truncate table gene.mutect;
>
> I’m not sure if any of this is meaningful but since I did not get a
> definitive answer on whether there is a mechanism in place to remove invalid
> objects (in this case the primary key), I am going to assume that there is
> not. Let me know if this is not true or an oversimplification for
> Postgres-XC.
>
> Thanks,
>
> bw
> From: Mason Sharp <ms...@tr...>
> Date: Thursday, November 14, 2013 at 12:26 PM
> To: William West <ww...@uc...>
> Cc: Koichi Suzuki <koi...@gm...>,
> "pos...@li..."
> <pos...@li...>
> Subject: Re: [Postgres-xc-bugs] Corrupt Objects
>
> Hi Bill,
>
> On Thu, Nov 14, 2013 at 1:09 PM, West, William <ww...@uc...> wrote:
>>
>> Koichi,
>>
>> The verbose logging provided the following information:
>>
>> LOG:  database system was shut down at 2013-11-14 09:46:37 PST
>> LOG:  database system is ready to accept connections
>> LOG:  autovacuum launcher started
>> LOG:  failed to find proc 0x2b960f53cdc0 in ProcArray
>> LOG:  failed to find proc 0x2b960f53cdc0 in ProcArray
>> LOG:  failed to find proc 0x2b960f53cdc0 in ProcArray
>> ERROR:  syntax error at or near "(" at character 24
>> LOG:  failed to find proc 0x2b960f53cdc0 in ProcArray
>> ERROR:  XX000: could not open relation with OID 24594
>> LOCATION:  relation_open, heapam.c:923
>> STATEMENT:  truncate table gene.mutect;
>> LOG:  failed to find proc 0x2b960f53cdc0 in ProcArray
>> LOG:  failed to find proc 0x2b960f53cdc0 in ProcArray
>> LOG:  failed to find proc 0x2b960f53cdc0 in ProcArray
>> LOG:  failed to find proc 0x2b960f53cdc0 in ProcArray
>> LOG:  failed to find proc 0x2b960f53cdc0 in ProcArray
>> LOG:  failed to find proc 0x2b960f53cdc0 in ProcArray
>>
>
> The procarray errors suggest that there may be an issue communicating with
> GTM.
>
> I fear that we currently allow falling back to local XIDs and snapshots,
> which should be prohibited.  Weird things can happen to your data at that
> point...
>
> Feel free to try out the attached development patch to help uncover such
> issues.
>
>
>> To the second question the answer is yes the problem exists when I restart
>> the cluster. I have even re-initialized the database several times and still
>> encounter this problem. Oddly enough if I create a table named gene.mutect1
>> and poly a primary key to it and populate it with data, I can access t with
>> no problem. However since I can remove the invalid objects it make the
>> entire database unusable.
>>
>> Thanks,
>>
>> Bill West
>>
>> From: Koichi Suzuki <koi...@gm...>
>> Date: Wednesday, November 13, 2013 at 7:24 PM
>> To: William West <ww...@uc...>
>> Cc: Michael Paquier <mic...@gm...>,
>> "pos...@li..."
>> <pos...@li...>
>> Subject: Re: [Postgres-xc-bugs] Corrupt Objects
>>
>> First of all, to analyze what is going on, it will be helpful if you set
>> log_error_verbosity GUC in postgresql.conf to verbose. The message is
>> printed in number of internal modules so this setting will help.   You can
>> do this from psql session but you may have to be a superuser.
>>
>> Second, do you have the same error when you restart the whole XC cluster?
>>
>> Regards;
>>
>> ---
>> Koichi Suzuki
>>
>>
>> 2013/11/14 West, William <ww...@uc...>
>>>
>>> Sorry forget the version in my last response:
>>>
>>> psql (PGXC 1.1, based on PG 9.2.4)
>>>
>>>
>>> Regards,
>>>
>>> bw
>>>
>>> On 11/13/13, 3:39 PM, "West, William" <ww...@uc...> wrote:
>>>
>>> >I am deploying a database to a production server that was developed
>>> >without a problem on my dev platform. There are a couple of schemas and
>>> > 4
>>> >tables. I can create and populate the tables but once I apply a primary
>>> >key to one of the tables, it is no longer available for any DML
>>> > operation.
>>> >Apparently it cannot find the primary key wherever it is looking for it.
>>> >If I recreate the table under a new name and populate and apply the
>>> >primary key it works fine but now I am stuck with two useless objects
>>> >which will interfere with maintenance routines down the line.
>>> >
>>> >My production environment is fairly unique in that I am deploying this
>>> >instance on a new supercomputer architecture and this could very
>>> > possible
>>> >be the source of this instability (since this all works on my dev
>>> > cluster
>>> >which is just a couple of linux VM nodes). However, I don¹t know where
>>> > to
>>> >look at the metadata or logs to find out what is wrong.
>>> >
>>> >I have reinitialized and rebuilt the database a number of time but this
>>> >won¹t be a real option when the project goes live (I hope) since it
>>> > takes
>>> >quite a bit of time even when doing a restore. Is there any manual way
>>> > to
>>> >remove these objects (limited to the table and primary key)from metadata
>>> >tables and therefore eliminate them from the instance?
>>> >
>>> >Thanks,
>>> >
>>> >bw
>>> >
>>> >On 11/13/13, 3:27 PM, "Michael Paquier" <mic...@gm...>
>>> > wrote:
>>> >
>>> >>On Thu, Nov 14, 2013 at 5:34 AM, West, William <ww...@uc...> wrote:
>>> >>> drop table gene.mutect cascade;
>>> >>> ERROR: cache lookup failed for relation 24594
>>> >>> It doesn¹t seemed based on what I read that there is a mechanism to
>>> >>>drop
>>> >>> corrupted objects. I hope this isn¹t the case since being unable to
>>> >>>remove
>>> >>> invalid objects would make this product unsuitable for production
>>> >>> work.
>>> >>This is clearly a bug. This error appears because even if this session
>>> >>has taken a lock on this relation that should guarantee its presence,
>>> >>well it is not here, hence the error.
>>> >>
>>> >>Telling first what is the version used, and then giving an explicit
>>> >>test case would help the dev team to solve this issue.
>>> >>Regards,
>>> >>--
>>> >>Michael
>>> >
>>>
>>>
>>> ------------------------------------------------------------------------------
>>> DreamFactory - Open Source REST & JSON Services for HTML5 & Native Apps
>>> OAuth, Users, Roles, SQL, NoSQL, BLOB Storage and External API Access
>>> Free app hosting. Or install the open source package on any LAMP server.
>>> Sign up and see examples for AngularJS, jQuery, Sencha Touch and Native!
>>>
>>> http://pubads.g.doubleclick.net/gampad/clk?id=63469471&iu=/4140/ostg.clktrk
>>> _______________________________________________
>>> Postgres-xc-bugs mailing list
>>> Pos...@li...
>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-bugs
>>
>>
>>
>>
>> ------------------------------------------------------------------------------
>> DreamFactory - Open Source REST & JSON Services for HTML5 & Native Apps
>> OAuth, Users, Roles, SQL, NoSQL, BLOB Storage and External API Access
>> Free app hosting. Or install the open source package on any LAMP server.
>> Sign up and see examples for AngularJS, jQuery, Sencha Touch and Native!
>>
>> http://pubads.g.doubleclick.net/gampad/clk?id=63469471&iu=/4140/ostg.clktrk
>> _______________________________________________
>> Postgres-xc-bugs mailing list
>> Pos...@li...
>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-bugs
>>
>
>
>
> --
> Mason Sharp
>
> TransLattice - http://www.translattice.com
> Distributed and Clustered Database Solutions
>
>