Re: [HACKERS] COPY does not work with regproc and aclitem
Tom Lane napsal(a): Zdenek Kotala [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] COPY does not work with regproc and aclitem
Zdenek Kotala wrote: Tom Lane napsal(a): Zdenek Kotala [EMAIL PROTECTED] 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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] COPY does not work with regproc and aclitem
Alvaro Herrera napsal(a): Zdenek Kotala wrote: Tom Lane napsal(a): Zdenek Kotala [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] COPY does not work with regproc and aclitem
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] 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 - 1.271 --- src/backend/commands/copy.c 24 Oct 2006 12:35:45 - *** *** 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 - 1.14 --- src/test/regress/input/copy.source 24 Oct 2006 12:35:46 - *** *** 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 - 1.12 --- src/test/regress/output/copy.source 24 Oct 2006 12:35:46 - *** *** 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) + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] COPY does not work with regproc and aclitem
Zdenek Kotala [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] COPY does not work with regproc and aclitem
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] COPY does not work with regproc and aclitem
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 Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] COPY does not work with regproc and aclitem
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] COPY does not work with regproc and aclitem
Alvaro Herrera [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] COPY does not work with regproc and aclitem
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] 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 Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] COPY does not work with regproc and aclitem
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] COPY does not work with regproc and aclitem
Tom Lane wrote:
Alvaro Herrera [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] COPY does not work with regproc and aclitem
Zdenek Kotala [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] COPY does not work with regproc and aclitem
Zdenek Kotala [EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] COPY does not work with regproc and aclitem
Tom Lane wrote: Zdenek Kotala [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] COPY does not work with regproc and aclitem
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] COPY does not work with regproc and aclitem
Andrew Dunstan [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
