Thread: COPY does not work with regproc and aclitem
I tried to use COPY command to export and import tables from catalog,
but COPY command has problem with data type regproc. See example
create table test (like pg_aggregate); copy pg_aggregate to '/tmp/pg_agg.out'; copy test from '/tmp/pg_agg.out';
ERROR: more than one function named "pg_catalog.avg"
CONTEXT: COPY test, line 1, column aggfnoid: "pg_catalog.avg"
The problem is that pg_proc table has following unique indexes:
"pg_proc_oid_index" UNIQUE, btree (oid) "pg_proc_proname_args_nsp_index" UNIQUE, btree (proname, proargtypes,
pronamespace)
And regprocin in the backend/utils/adt/regproc.c cannot found unique OID
for proname.
Workaround is use binary mode, but on other side aclitem is not
supported in the binary mode.
postgres=# copy pg_class to '/tmp/pg_class.out' binary; ERROR: no binary output function available for type
aclitem
The solution is that COPY command will be use OID instead procname for
export regproc.
Zdenek
Zdenek Kotala wrote: > I tried to use COPY command to export and import tables from catalog Is it just me or does this seem like a strange thing to want to do? I am trying to think of a good use case, so far without much success. cheers andrew
Zdenek Kotala wrote: > I tried to use COPY command to export and import tables from catalog, > but COPY command has problem with data type regproc. See example > > create table test (like pg_aggregate); > copy pg_aggregate to '/tmp/pg_agg.out'; > copy test from '/tmp/pg_agg.out'; > > ERROR: more than one function named "pg_catalog.avg" > CONTEXT: COPY test, line 1, column aggfnoid: "pg_catalog.avg" Hmm, maybe it should be using regprocedure instead? That one emits type-qualified function names, IIRC. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Andrew Dunstan wrote:
> Zdenek Kotala wrote:
>> I tried to use COPY command to export and import tables from catalog
>
>
> Is it just me or does this seem like a strange thing to want to do? I am
> trying to think of a good use case, so far without much success.
>
I'm playing with catalog upgrade. The very basic idea of my experiment
is export data from catalog and import it back to the new
initialized/fresh catalog.
Zdenek
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Hmm, maybe it should be using regprocedure instead?
Not unless you want to break initdb. The only reason regproc still
exists, really, is to accommodate loading of pg_type during initdb.
Guess what: we can't do type lookup at that point.
regards, tom lane
Tom Lane wrote: > Alvaro Herrera <alvherre@commandprompt.com> writes: > > Hmm, maybe it should be using regprocedure instead? > > Not unless you want to break initdb. The only reason regproc still > exists, really, is to accommodate loading of pg_type during initdb. > Guess what: we can't do type lookup at that point. I was thinking in the copied-out table, which not necessarily has to be pg_aggregate. I just tried, and it works to do this: alvherre=# create table pg_aggregate2 (aggfnoid regprocedure, aggtransfn alvherre(# regprocedure, aggfinalfn regprocedure, aggsortop oid, aggtranstype oid, alvherre(# agginitval text); CREATE TABLE alvherre=# insert into pg_aggregate2 select * from pg_aggregate; INSERT 0 114 alvherre=# create table test (like pg_aggregate2); CREATE TABLE alvherre=# copy pg_aggregate2 to '/tmp/pg_agg.out'; COPY 114 alvherre=# copy test from '/tmp/pg_agg.out'; COPY 114 alvherre=# -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Zdenek Kotala wrote: > Andrew Dunstan wrote: >> Zdenek Kotala wrote: >>> I tried to use COPY command to export and import tables from catalog >> >> >> Is it just me or does this seem like a strange thing to want to do? I >> am trying to think of a good use case, so far without much success. >> > > I'm playing with catalog upgrade. The very basic idea of my experiment > is export data from catalog and import it back to the new > initialized/fresh catalog. > > > Fair enough, but I am somewhat doubtful that COPY is the best way to do this. cheers andrew
Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
>> Hmm, maybe it should be using regprocedure instead?
>
> Not unless you want to break initdb. The only reason regproc still
> exists, really, is to accommodate loading of pg_type during initdb.
> Guess what: we can't do type lookup at that point.
Do you mean something like this:
Datum
regprocout(PG_FUNCTION_ARGS)
{
...
if( donot_resolve_procname == TRUE) { result = (char *) palloc(NAMEDATALEN); snprintf(result, NAMEDATALEN,
"%u",proid); }
...
PG_RETURN_CSTRING(result);
}
donot_resolve_procname will be set when COPY will be performed.
Zdenek
Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
> I'm playing with catalog upgrade. The very basic idea of my experiment
> is export data from catalog and import it back to the new
> initialized/fresh catalog.
That is never going to work, at least not for any interesting catalogs.
A system with a "fresh" (I assume you mean empty) pg_proc, for instance,
is non functional.
A much bigger problem, if you're thinking of this as a component step
of pg_upgrade, is that you can't use anything at the COPY level of
detail because it will fail if the new version wants a different catalog
layout --- for instance, if someone's added a column to the catalog.
The right way to implement pg_upgrade is to transfer the catalog data
at the SQL-command level of abstraction, ie, "pg_dump -s" and reload.
regards, tom lane
Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
> if( donot_resolve_procname == TRUE)
> {
> result = (char *) palloc(NAMEDATALEN);
> snprintf(result, NAMEDATALEN, "%u", proid);
> }
What for? If you want numeric OIDs you can have that today by casting
the column to OID. More to the point, the issue is hardly restricted
to COPY --- you'd get the same thing if you tried to insert data with
INSERT.
regards, tom lane
Tom Lane wrote:
> Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
>> I'm playing with catalog upgrade. The very basic idea of my experiment
>> is export data from catalog and import it back to the new
>> initialized/fresh catalog.
>
> That is never going to work, at least not for any interesting catalogs.
> A system with a "fresh" (I assume you mean empty) pg_proc, for instance,
> is non functional.
No empty, fresh initialized by initdb. I want to copy only "user data"
which is not created during boostrap.
>
> A much bigger problem, if you're thinking of this as a component step
> of pg_upgrade, is that you can't use anything at the COPY level of
> detail because it will fail if the new version wants a different catalog
> layout --- for instance, if someone's added a column to the catalog.
Yes, I know about it. It is not problem, I want to prepare "shadow"
catalog with new structure on old database in separate schema and adjust
data in these tables. After it I want to make final COPY - data will be
copied with correct structure.
> The right way to implement pg_upgrade is to transfer the catalog data
> at the SQL-command level of abstraction, ie, "pg_dump -s" and reload.
I'm not sure if it is important, but I think that preserve OID is
important and SQL level does not allow set OID.
Zdenek
Zdenek Kotala wrote: > Tom Lane wrote: > >> The right way to implement pg_upgrade is to transfer the catalog data >> at the SQL-command level of abstraction, ie, "pg_dump -s" and reload. > > I'm not sure if it is important, but I think that preserve OID is > important and SQL level does not allow set OID. > > Does it matter in any case other than where it refers to an on-disk object? And does that need anything other than a fixup to pg_class::relfilenode? cheers andrew
Andrew Dunstan <andrew@dunslane.net> writes:
> Zdenek Kotala wrote:
>> I'm not sure if it is important, but I think that preserve OID is
>> important and SQL level does not allow set OID.
> Does it matter in any case other than where it refers to an on-disk
> object? And does that need anything other than a fixup to
> pg_class::relfilenode?
The only things pg_upgrade should be trying to preserve OIDs for are
large objects. I don't even see a need to worry about relfilenode:
you've got to link the physical files into the new directory tree
anyway, you can perfectly well link them in under whatever new
relfilenode identity happens to be assigned during the dump-reload step.
This was all worked out years ago.
regards, tom lane
Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
>> Hmm, maybe it should be using regprocedure instead?
>
> Not unless you want to break initdb. The only reason regproc still
> exists, really, is to accommodate loading of pg_type during initdb.
> Guess what: we can't do type lookup at that point.
>
I prepared patch which use oid output function instead regproc output.
This change works only for COPY TO command. SELECT behavior is
untouched. I extended copy regression test as well.
Please, look on it if it is acceptable fix.
With regards Zdenek
Index: src/backend/commands/copy.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/copy.c,v
retrieving revision 1.271
diff -c -r1.271 copy.c
*** src/backend/commands/copy.c 31 Aug 2006 03:17:50 -0000 1.271
--- src/backend/commands/copy.c 24 Oct 2006 12:35:45 -0000
***************
*** 1309,1315 ****
&out_func_oid,
&isvarlena);
else
! getTypeOutputInfo(attr[attnum - 1]->atttypid,
&out_func_oid,
&isvarlena);
fmgr_info(out_func_oid, &cstate->out_functions[attnum - 1]);
--- 1309,1317 ----
&out_func_oid,
&isvarlena);
else
! /* For regproc datatype do not lookup proc name, use OID out function instead.
! It avoids problem with COPY FROM. */
! getTypeOutputInfo(attr[attnum - 1]->atttypid == REGPROCOID? OIDOID : attr[attnum - 1]->atttypid,
&out_func_oid,
&isvarlena);
fmgr_info(out_func_oid, &cstate->out_functions[attnum - 1]);
Index: src/test/regress/input/copy.source
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/input/copy.source,v
retrieving revision 1.14
diff -c -r1.14 copy.source
*** src/test/regress/input/copy.source 2 May 2006 11:28:56 -0000 1.14
--- src/test/regress/input/copy.source 24 Oct 2006 12:35:46 -0000
***************
*** 105,107 ****
--- 105,113 ----
copy copytest3 to stdout csv header;
+ --- test correct handling regproc data type
+ CREATE TEMP TABLE test_regproc (like pg_aggregate);
+ COPY pg_catalog.pg_aggregate TO '@abs_builddir@/results/test_regproc.data';
+ COPY test_regproc FROM '@abs_builddir@/results/test_regproc.data';
+
+ select aggfnoid, cast(aggfnoid as oid) from pg_aggregate where aggfnoid=2147;
Index: src/test/regress/output/copy.source
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/output/copy.source,v
retrieving revision 1.12
diff -c -r1.12 copy.source
*** src/test/regress/output/copy.source 2 May 2006 11:28:56 -0000 1.12
--- src/test/regress/output/copy.source 24 Oct 2006 12:35:46 -0000
***************
*** 70,72 ****
--- 70,82 ----
c1,"col with , comma","col with "" quote"
1,a,1
2,b,2
+ --- test correct handling regproc data type
+ CREATE TEMP TABLE test_regproc (like pg_aggregate);
+ COPY pg_catalog.pg_aggregate TO '@abs_builddir@/results/test_regproc.data';
+ COPY test_regproc FROM '@abs_builddir@/results/test_regproc.data';
+ select aggfnoid, cast(aggfnoid as oid) from pg_aggregate where aggfnoid=2147;
+ aggfnoid | aggfnoid
+ ------------------+----------
+ pg_catalog.count | 2147
+ (1 row)
+
Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
> I prepared patch which use oid output function instead regproc output.
> This change works only for COPY TO command.
This is not a bug and we're not going to fix it, most especially not
like that.
regards, tom lane
Tom Lane napsal(a):
> Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
>> I prepared patch which use oid output function instead regproc output.
>> This change works only for COPY TO command.
>
> This is not a bug and we're not going to fix it, most especially not
> like that.
>
OK, The behavior of regproc type is described in the documentation, but
if we don't fix it, than Some error message like "Regproc data type is
not supported by COPY TO command" could be useful. Because you find that
something is wrong when you want to restore data back and it should be
too late.
Zdenek
Zdenek Kotala wrote: > Tom Lane napsal(a): > >Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes: > >>I prepared patch which use oid output function instead regproc output. > >>This change works only for COPY TO command. > > > >This is not a bug and we're not going to fix it, most especially not > >like that. > > OK, The behavior of regproc type is described in the documentation, but > if we don't fix it, than Some error message like "Regproc data type is > not supported by COPY TO command" could be useful. Because you find that > something is wrong when you want to restore data back and it should be > too late. But it works as "expected". If the approach you suggest would be one we would take, then it should emit the same error on SELECT as well, shouldn't we? I think the problem is that regproc COPY is not useful to you for your particular use case. But there are workarounds, like the one I suggested and you promptly ignored. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera napsal(a):
> Zdenek Kotala wrote:
>> Tom Lane napsal(a):
>>> Zdenek Kotala <Zdenek.Kotala@Sun.COM> writes:
>>>> I prepared patch which use oid output function instead regproc output.
>>>> This change works only for COPY TO command.
>>> This is not a bug and we're not going to fix it, most especially not
>>> like that.
>> OK, The behavior of regproc type is described in the documentation, but
>> if we don't fix it, than Some error message like "Regproc data type is
>> not supported by COPY TO command" could be useful. Because you find that
>> something is wrong when you want to restore data back and it should be
>> too late.
>
> But it works as "expected". If the approach you suggest would be one we
> would take, then it should emit the same error on SELECT as well,
> shouldn't we?
It is right.
> I think the problem is that regproc COPY is not useful to you for your
> particular use case. But there are workarounds, like the one I
> suggested and you promptly ignored.
Yes, I read your suggestion It is useful form me thanks for that. But I
thought how to remove that regproc limitation or how to avoid some
confusing. Current mention about regproc limitation/behavior in the
documentation is really best solution.
By the way, If I read carefully your suggestion, Tom's answer and
documentation, correct solution (theoretical) is replace regproc by
regprocedure datatype in the catalog, but there is problem in the
boostrap phase?
Thanks Zdenek