Re: how to add a new column in pg_proc table

Hello Every one,

I am a student and working on my final year project,
I chose postgres as my development database,because of
its flexable architecture and extensibility.

I need to add a column of text type in pg_proc table
which is the part of the system catalog.I need to have
it so that I can proceed with my project.

I already know about the pg_proc.c and the structure
which contains all the relevent table columns in this
file.I also know about the script that generates the
.bki file with the file format as well.But ....

I keep on getting errors.It be very helpful if some
one can guide me regarding the basic steps for adding
a column in the pg_proc table......it would be indeed
of a great a help.

Regards,
Nauman

__________________________________
Do you Yahoo!?
The all-new My Yahoo! - Get yours free!
http://my.yahoo.com

On Tue, Jan 25, 2005 at 07:44:18AM -0800, noman naeem wrote:

Hi,

> I need to add a column of text type in pg_proc table
> which is the part of the system catalog.I need to have
> it so that I can proceed with my project.
>
[snip]
>
> I keep on getting errors.It be very helpful if some
> one can guide me regarding the basic steps for adding
> a column in the pg_proc table......

What did you do exactly? What errors you get?

--
Alvaro Herrera (<alvherre[(at)]dcc(dot)uchile(dot)cl>)
"I can't go to a restaurant and order food because I keep looking at the
fonts on the menu. Five minutes later I realize that it's also talking
about food" (Donald Knuth)

noman naeem <doublevision_17(at)yahoo(dot)com> writes:
> I keep on getting errors.It be very helpful if some
> one can guide me regarding the basic steps for adding
> a column in the pg_proc table...

The odds are that you didn't correctly update either pg_proc.h itself,
the preset pg_attribute rows for it in pg_attribute.h (both versions!),
or you just forgot to adjust the number-of-attributes for pg_proc in
pg_class.h.

In pg_proc.h, remember that you have to fix the struct declaration,
the #defines for attribute numbers, plus adjust all the built-in entries
(that last is a *real* PITA...)

Not to mention adjust code that uses or updates pg_proc, particularly
catalog/pg_proc.c.

One other nonobvious gotcha is that varlength fields must be physically
positioned after all fixed-width fields, because the C code tends to
assume that it can get at fixed-width fields by struct overlay.

I'd suggest looking at the last patch that added a field to pg_proc for
guidelines and to make sure you did not miss anything:

2004-01-06 18:55 tgl

* doc/src/sgml/catalogs.sgml, src/backend/bootstrap/bootstrap.c,
src/backend/catalog/pg_aggregate.c, src/backend/catalog/pg_proc.c,
src/backend/commands/functioncmds.c, src/backend/nodes/copyfuncs.c,
src/backend/nodes/equalfuncs.c, src/backend/parser/gram.y,
src/backend/utils/adt/sets.c, src/backend/utils/fmgr/fmgr.c,
src/include/catalog/catversion.h,
src/include/catalog/pg_attribute.h, src/include/catalog/pg_class.h,
src/include/catalog/pg_proc.h, src/include/nodes/nodes.h,
src/include/nodes/parsenodes.h, src/pl/plperl/plperl.c,
src/pl/plpgsql/src/pl_comp.c, src/pl/plpython/plpython.c,
src/pl/tcl/pltcl.c: Apply the core parts of Dennis Bjorklund's
patch to allow function parameters to be declared with names.
pg_proc has a column to store names, and CREATE FUNCTION can insert
data into it, but that's all as yet. I need to do more work on the
pg_dump and plpgsql portions of the patch before committing those,
but I thought I'd get the bulky changes in before the tree drifts
under me. initdb forced due to pg_proc change.

regards, tom lane

Tom,
Thanks for the reply I rechanged the
pg_proc.h,pg_attribute.h and pg_class.h but still
facing errors,rather this time there were two
different errors.

They came at the time of frmgrtab.h file creation,they
are

fmgrtab.c:25: error: syntax error before '-' token
fmgrtab.c:2168: error: syntax error before '}' token

there are loads and loads of such errors.

what do u say....

Could you tell me from where I can have the last patch
that added a field to pg_proc for
guidelines of which you suggested.

awaiting for reply.....

Thanks,
Nauman

--- Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> noman naeem <doublevision_17(at)yahoo(dot)com> writes:
> > I keep on getting errors.It be very helpful if
> some
> > one can guide me regarding the basic steps for
> adding
> > a column in the pg_proc table...
>
> The odds are that you didn't correctly update either
> pg_proc.h itself,
> the preset pg_attribute rows for it in
> pg_attribute.h (both versions!),
> or you just forgot to adjust the
> number-of-attributes for pg_proc in
> pg_class.h.
>
> In pg_proc.h, remember that you have to fix the
> struct declaration,
> the #defines for attribute numbers, plus adjust all
> the built-in entries
> (that last is a *real* PITA...)
>
> Not to mention adjust code that uses or updates
> pg_proc, particularly
> catalog/pg_proc.c.
>
> One other nonobvious gotcha is that varlength fields
> must be physically
> positioned after all fixed-width fields, because the
> C code tends to
> assume that it can get at fixed-width fields by
> struct overlay.
>
> I'd suggest looking at the last patch that added a
> field to pg_proc for
> guidelines and to make sure you did not miss
> anything:
>
> 2004-01-06 18:55 tgl
>
> * doc/src/sgml/catalogs.sgml,
> src/backend/bootstrap/bootstrap.c,
> src/backend/catalog/pg_aggregate.c,
> src/backend/catalog/pg_proc.c,
> src/backend/commands/functioncmds.c,
> src/backend/nodes/copyfuncs.c,
> src/backend/nodes/equalfuncs.c,
> src/backend/parser/gram.y,
> src/backend/utils/adt/sets.c,
> src/backend/utils/fmgr/fmgr.c,
> src/include/catalog/catversion.h,
> src/include/catalog/pg_attribute.h,
> src/include/catalog/pg_class.h,
> src/include/catalog/pg_proc.h,
> src/include/nodes/nodes.h,
> src/include/nodes/parsenodes.h,
> src/pl/plperl/plperl.c,
> src/pl/plpgsql/src/pl_comp.c,
> src/pl/plpython/plpython.c,
> src/pl/tcl/pltcl.c: Apply the core parts of Dennis
> Bjorklund's
> patch to allow function parameters to be declared
> with names.
> pg_proc has a column to store names, and CREATE
> FUNCTION can insert
> data into it, but that's all as yet. I need to do
> more work on the
> pg_dump and plpgsql portions of the patch before
> committing those,
> but I thought I'd get the bulky changes in before
> the tree drifts
> under me. initdb forced due to pg_proc change.
>
> regards, tom lane
>

__________________________________
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail

noman naeem <doublevision_17(at)yahoo(dot)com> writes:
> They came at the time of frmgrtab.h file creation,they
> are

> fmgrtab.c:25: error: syntax error before '-' token
> fmgrtab.c:2168: error: syntax error before '}' token

> there are loads and loads of such errors.

I suppose you forgot to update the Gen_fmgrtab.sh script
to account for new column numbering in pg_proc.

> Could you tell me from where I can have the last patch

See our CVS server --- a checkout and then "cvs diff" around
the time point I identified for you should do the trick.

regards, tom lane

Hello Tom,

Now I have been able to generate valid bki file and
have been able to avoid all the errors thanks to
you,but still have not been able to add that
column.Now at initdb the database fails to initialize
itself.And the error it gives is.

duplicate key violates unique constraint
"pg_attribute_relid_attnum_index"

I am very sure it is due to pg_attribute.h file in
which I have inserted the entry for "protempsrc"
column.

The main thing is I am unable to understand this
insert statement structure.Please guide,I am in an
urgency.

It would be great if you can describe what it does and
means.

DATA(insert ( 1255 protempsrc 26 -1 -1 -1 0 -1 -1 f x
i f f f t 0));

--- Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> noman naeem <doublevision_17(at)yahoo(dot)com> writes:
> > They came at the time of frmgrtab.h file
> creation,they
> > are
>
> > fmgrtab.c:25: error: syntax error before '-' token
> > fmgrtab.c:2168: error: syntax error before '}'
> token
>
> > there are loads and loads of such errors.
>
> I suppose you forgot to update the Gen_fmgrtab.sh
> script
> to account for new column numbering in pg_proc.
>
> > Could you tell me from where I can have the last
> patch
>
> See our CVS server --- a checkout and then "cvs
> diff" around
> the time point I identified for you should do the
> trick.
>
> regards, tom lane
>

__________________________________
Do you Yahoo!?
All your favorites on one personal page Try My Yahoo!
http://my.yahoo.com