how to add a new column in pg_proc table

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[@]dcc.uchile.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@yahoo.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@sss.pgh.pa.us> wrote:

noman naeem <doublevision_17@yahoo.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@yahoo.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@sss.pgh.pa.us> wrote:

noman naeem <doublevision_17@yahoo.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