0

I have created table with a partition:

CREATE TABLE edw_src.pageviewlog_dev
(
 accessurl character varying(1000),
msisdn character varying(1000),
customerid integer
)
WITH (
  OIDS=FALSE
)
DISTRIBUTED BY (msisdn)
PARTITION BY RANGE(customerid) 
          (
          PARTITION customerid START (0) END (200)
          )

Now I want to change the datasize of accessurl from 1000 to 3000.I am not able to change the datasize,Whenever I am trying I am getting the error.

ERROR: "pageviewlog_dev_1_prt_customerid" is a member of a partitioning configuration
HINT: Perform the operation on the master table.

I am able to change If I change the datatype from pg_attribute.If there any other way to change the datasize of existing column other than pg_attribute

I have found the Solution for the same .Sorry for the replying late .Below is the way to do ,whenever we face this kind of problem in "Post grel and greenplum"

UPDATE pg_attribute SET atttypmod = 300+4
WHERE attrelid = 'edw_src.ivs_hourly_applog_events'::regclass
AND attname = 'adtransactionid';
Harkirat Singh
  • 11
  • 3

1 Answers1

0
  1. Greenplum isn't Postgresql so please don't confuse people by asking a Greenplum question with PostgreSQL in the title.
  2. Don't modify catalog objects like pg_attribute. That will cause lots of problems and isn't supported.

  3. The Admin Guide has the syntax for changing column datatypes and this is all you need to do:

    ALTER TABLE edw_src.pageviewlog_dev 
    ALTER COLUMN accessurl TYPE character varying(3000);

Here is the working example with your table:

    CREATE SCHEMA edw_src;

    CREATE TABLE edw_src.pageviewlog_dev
    (
     accessurl character varying(1000),
    msisdn character varying(1000),
    customerid integer
    )
    WITH (
      OIDS=FALSE
    )
    DISTRIBUTED BY (msisdn)
    PARTITION BY RANGE(customerid) 
              (
              PARTITION customerid START (0) END (200)
              );

Output:

    NOTICE:  CREATE TABLE will create partition "pageviewlog_dev_1_prt_customerid" for table "pageviewlog_dev"
    Query returned successfully with no result in 47 ms.

And now alter the table:

    ALTER TABLE edw_src.pageviewlog_dev
       ALTER COLUMN accessurl TYPE character varying(3000);

Output:

      Query returned successfully with no result in 62 ms.

Proof in psql:

    \d edw_src.pageviewlog_dev
             Table "edw_src.pageviewlog_dev"
       Column   |          Type           | Modifiers 
    ------------+-------------------------+-----------
     accessurl  | character varying(3000) | 
     msisdn     | character varying(1000) | 
     customerid | integer                 | 
    Number of child tables: 1 (Use \d+ to list them.)
    Distributed by: (msisdn)

If you are unable to alter the table it is probably because the catalog is corrupted after you updated pg_attribute directly. You can try dropping the table and recreating it or you can open a support ticket to have them attempt to correct the catalog corruption.

Jon Roberts
  • 2,068
  • 1
  • 9
  • 11
  • Hi jon , As I have clearly explained .This command will not work (ALTER TABLE edw_src.pageviewlog_dev ALTER COLUMN accessurl TYPE character varying(3000);).First create table with partition or with above DDL .Then run this command which you have given me.You will error mess – Harkirat Singh Nov 06 '15 at 06:56
  • Before posting your comment ,first of all create table with partition with above DDL ,Then post your comment....................... – Harkirat Singh Nov 06 '15 at 07:00
  • Thanks joe ,For your reply.I have recreated the table with the different name.But I am with still error and I am not understanding .In your case you are able to alter ,I am not. – Harkirat Singh Nov 08 '15 at 05:04
  • Can you try creating the table with a new name that has accessurl character varying(1000) and then use the alter table command to make it character varying(3000)? You may have a corrupted catalog after you updated pg_attribute directly which is preventing you from altering the original table. – Jon Roberts Nov 09 '15 at 21:50
  • Whenever alter command in "Post grel or Grrenplum "behave like that ,we can do that by using below command .Creating new table is not the solution as we have to stop production. – Harkirat Singh Dec 24 '16 at 10:00
  • UPDATE pg_attribute SET atttypmod = 300+4 WHERE attrelid = 'edw_src.ivs_hourly_applog_events'::regclass AND attname = 'adtransactionid'; – Harkirat Singh Dec 24 '16 at 10:00
  • You shouldn't update a catalog table like pg_attribute directly. There might be a bug with the version of Greenplum you are using. I would try upgrading to see if that resolves the problem. – Jon Roberts Dec 27 '16 at 16:40
  • Actually we are able to do in the new version of Greenplum without intervention of pg_catlog table .But we were not able to do old version ,as a result we came across this solution. – Harkirat Singh Dec 31 '16 at 13:22