[HACKERS] -X flag in pg_dump
What use is the -X flag to pg_dump. The code say that if one add a setting feature to -X then there should also be a flag --feature. So we have for example: -X disable-triggers and --disable-triggers If all the -X flags come in a long form as well, then what use is the -X at all? Why would one want to use -X disable-triggers instead of --disable-triggers ? -- /Dennis Björklund ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] PostgreSQL Solaris packages now in beta
Robert Lor wrote: Bruce, The binary was compiled in 32bit mode using Sun Studio compiler, and we plan to do 64bit soon. Can you point me to the patch? We can certainly test it! Download CVS HEAD and give it a try. The file that was created for the port is backend/port/tas/solaris_x86_64.s. It is an assembler file. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS]
unsubscribe ---(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] -X flag in pg_dump
Dennis Bjorklund wrote: What use is the -X flag to pg_dump. The code say that if one add a setting feature to -X then there should also be a flag --feature. So we have for example: -X disable-triggers and --disable-triggers If all the -X flags come in a long form as well, then what use is the -X at all? Why would one want to use -X disable-triggers instead of --disable-triggers ? The issue is that some operating systems do not support long options, so -X option is really -X and an argument, while --option is a long option. We have our own getopt.c library so I am unsure we really need have cases that don't support long options anymore. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] debug_query_string and multiple statements
If we want to save the SQL statement for some database objects(table, view, etc.), the backend will see the same problem. Here is an example. create table s(sno int, sname char(10)); select 1; I recall that some DBMS will store the statement for table s like this: create table s(sno int, sname char(10)); We should also treat the comments. Neil Conway [EMAIL PROTECTED] wrote message While reviewing Joachim Wieland's patch to add a pg_cursors system view, I noticed that the patch assumes that debug_query_string contains the portion of the submitted query string that corresponds to the SQL statement we are currently executing. That is incorrect: debug_query_string contains the *entire* verbatim query string sent by the client. So if the client submits the query string SELECT 1; SELECT 2;, debug_query_string will contain exactly that string. (psql actually splits queries like the above into two separate FE/BE messages -- to see what I'm referring to, use libpq directly, or start up a copy of the standalone backend.) This makes debug_query_string the wrong thing to use for the pg_cursors and pg_prepared_statements views, but it affects other parts of the system as well: for example, given PQexec(conn, SELECT 1; SELECT 2/0;) and log_min_error_statement = 'error', the postmaster will log: ERROR: division by zero STATEMENT: SELECT 1; SELECT 2/0; which seems misleading, and is inconsistent with the documentation's description of this configuration parameter. Admittedly this isn't an enormous problem, but I think the current behavior isn't ideal. Unfortunately I don't see an easy way to fix this. It might be possible to extra a semicolon separated list of query strings from the parser or lexer, but that would likely have the effect of munging comments and whitespace from the literal string submitted by the client, which seems the wrong thing to do for logging purposes. An alternative might be to do a preliminary scan to look for semicolon delimited query strings, and then pass each of those strings into the raw_parser() separately, but that seems quite a lot of work (and perhaps a significant runtime cost) to fix what is at worst a minor UI wrinkle. Thoughts? -Neil ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] GRANT/REVOKE: Allow column-level privileges
I think we should pay attention to the sematic of table privs and column privs. Here is some examples. 1. role1 GRANT table priviledge SELECT on table S to role2. role1 REVOKE column priviledge SELECT on column S(SNO) from role2. 2. deal with circles in GRANT graph. kevin brintnall [EMAIL PROTECTED] wrote Fellow Hackers, I've been working on this item for a little while, and I'm starting to see some code come together. I wanted to solicit some feedback before I got too far along to make sure I'm on the right track. Here's a rough overview of what I've done so far: - PARSER: * modified parser to accept SQL column privs syntax * created a PrivAttr Node which holds ( priv, attr[] ) pairs. Currently, it's just a list of strings. For example, when you call... GRANT SELECT, UPDATE (col1, col2) ON table1, table2 to grantee; ... the parser creates a list of Nodes: (select, NIL), (update, (col1, col2)) SYSTEM CATALOG: * add attacl aclinfo[] column to pg_attribute table and Form_pg_attribute. * add OID column to pg_attribute. This permits dependencies to be registered correctly in pg_shdepend. * populated attacl column in existing pg_attribute bootstrap with NULLs * allocated an unused oid for each of the pg_attribute rows that are bootstrapped * created an oid index on pg_attribute * modified ExecuteGrantStmt to handle the PrivAttr structure instead of the list of strings * modified ExecuteGrantStmt to do a nested loop over all (column,relation) pairs in the GRANT and find oids for all of the attributes. PSQL COMMAND LINE: * display column privileges with \d+ table STILL LEFT TO DO: * implement ExecGrant_Attribute() to modify pg_attribute * verify query against column privileges in addition to table privileges * register dependencies * pg_dump column privileges - I'd welcome any feedback on the design changes I've made, or any other potential snags I should watch out for. Thanks. -- kevin brintnall =~ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(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] Cleaning up the INET/CIDR mess
Andrew - Supernews [EMAIL PROTECTED] writes: The spec is quite explicit that inet_pton is not expected to accept the abbreviated forms or any non-decimal values. Hum. That distinctly doesn't match my memory but it seems you're right. The spec mandates inet_ntoa and inet_addr support it but not inet_ntop. Odd. -- greg ---(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] Cleaning up the INET/CIDR mess
On Jan 25, 2006, at 9:29 AM, Bruce Momjian wrote: Tom Lane wrote: Greg Stark [EMAIL PROTECTED] writes: I wonder if this would be an opportunity to fix Postgres's handling of addresses like '10.1'. You've mistaken this for a proposal to change the I/O behavior, which it is specifically not. The standard interpretation of this is the same as '10.0.0.1'. Standard according to whom? Paul Vixie evidently doesn't think that that's a standard abbreviation, else the code we borrowed from libbind would do it already. Agreed. 10.1 as 10.0.0.1 is an old behavior which has been removed from most modern versions of networking tools. Whether PG should support it or not is another question (personally I think that anything other than a dotted quad should fail with an error) but it certainly hasn't been removed from most modern versions of networking tools. gethostbyname() is used by most networking tools, and on most unix OSes it believes 10.1 'resolves to' 10.0.0.1. That includes current versions of linux, OS X, Solaris, Windows XP and I believe the BSDs. So the vast majority of applications on the vast majority of deployed platforms believe that 10.1 is the address 10.0.0.1. (As is often the case binds behaviour is inconsistent and can't really be used as proof of standard behaviour). Cheers, Steve ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Rollback Mountain
Michael Fuhr wrote: Rollback Mountain A raw, powerful story of two young transactions, one serializable and the other read-committed, who meet in the summer of 2005 updating tables in the harsh, high-volume environment of a contemporary online trading system and form an unorthodox yet session-long bond -- by turns ecstatic, bitter, and conflicted. ENCORE ENCORE! :) ---(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] [GENERAL] [PATCH] Better way to check for getaddrinfo
I am not sure what to do on this. Right now we have a one-line test: AC_REPLACE_FUNCS([getaddrinfo]) To test for a macro we are going to need to add include netdb.h, and the LINK test below is overkill. I am thinking we should just hard-code in HAVE_GETADDRINFO for the True64 platform; anything more is going to be just a Tru64 hack anyway. --- R, Rajesh (STSD) wrote: sorry. It is a macro. so, would it be better to check for the macro as suggested by Tom or go with this patch $ diff -r configure.in configure.in.new 918a919 AC_MSG_CHECKING([for getaddrinfo]) 920c921,926 AC_REPLACE_FUNCS([getaddrinfo]) --- AC_TRY_LINK([#include netdb.h #include assert.h], [char (*f)();f=getaddrinfo;], ac_cv_func_getaddrinfo=yes, ac_cv_func_getaddrinfo=no) if test x$ac_cv_func_getaddrinfo = xyes; then AC_DEFINE(HAVE_GETADDRINFO,1,[Define if you have the getaddrinfo function]) fi 923a930 AC_MSG_RESULT([$ac_cv_func_getaddrinfo]) I guess, instead of adding seperate code for macro checking as suggested by Tom, this might serve dual purpose. Thanks, Rajesh R -- This space intentionally left non-blank. -Original Message- From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 24, 2006 2:46 PM To: R, Rajesh (STSD) Cc: Tom Lane; pgsql-hackers@postgresql.org; pgsql-general@postgresql.org Subject: Re: [HACKERS] [GENERAL] [PATCH] Better way to check for getaddrinfo function. On Tue, Jan 24, 2006 at 02:33:13PM +0530, R, Rajesh (STSD) wrote: Its not a macro. I meant that the code generated by AC_REPLACE_FUNCS([getaddrinfo]) by configure.in for configure does not have #include netdb.h. Hence function is not detected(unresolved getaddrinfo). Hence I thought AC_TRY_LINK could give test program instead of AC_REPLACE_FUNCS taking one. But if it isn't a macro, why do you need the header file? In C it's perfectly legal to declare the symbol yourself and try to link and it should work *unless* it's normally a macro. We're still missing some necessary understanding here... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] Backslashes in string literals
On Wed, Jan 25, 2006 at 4:46 pm, in message [EMAIL PROTECTED], Kevin Grittner [EMAIL PROTECTED] wrote: (2) There should probably be some tests added to exercise these options. Attached is a patch to address this one. Note that until psql is fixed, this test will fail. I manually generated a portion of the text to match what I expect to get once psql is fixed, so there could be typos. -Kevin test-string-patch.txt Description: Binary data ---(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] [GENERAL] [PATCH] Better way to check for getaddrinfo
Bruce Momjian pgman@candle.pha.pa.us writes: I am not sure what to do on this. Right now we have a one-line test: AC_REPLACE_FUNCS([getaddrinfo]) To test for a macro we are going to need to add include netdb.h, and the LINK test below is overkill. I am thinking we should just hard-code in HAVE_GETADDRINFO for the True64 platform; anything more is going to be just a Tru64 hack anyway. I still want to understand why any change is needed at all. There must be something very peculiar about getaddrinfo on Tru64 if the original coding doesn't work. Why is it different from every other function we test for? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [GENERAL] [PATCH] Better way to check for getaddrinfo
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: I am not sure what to do on this. Right now we have a one-line test: AC_REPLACE_FUNCS([getaddrinfo]) To test for a macro we are going to need to add include netdb.h, and the LINK test below is overkill. I am thinking we should just hard-code in HAVE_GETADDRINFO for the True64 platform; anything more is going to be just a Tru64 hack anyway. I still want to understand why any change is needed at all. There must be something very peculiar about getaddrinfo on Tru64 if the original coding doesn't work. Why is it different from every other function we test for? I have the answer. Tru64 netdb.h has: #if defined (_SOCKADDR_LEN) || defined (_XOPEN_SOURCE_EXTENDED) #define getaddrinfo ngetaddrinfo #else #define getaddrinfo ogetaddrinfo #endif so it is a macro, and configure produces this line: #undef $ac_func meaning that even if we added #include netdb.h, our configure test still would not work. Perhaps we should just test for ngetaddrinfo on that platform, and define HAVE_GETADDRINFO. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] [GENERAL] [PATCH] Better way to check for getaddrinfo
Bruce Momjian pgman@candle.pha.pa.us writes: I have the answer. Tru64 netdb.h has: #if defined (_SOCKADDR_LEN) || defined (_XOPEN_SOURCE_EXTENDED) #define getaddrinfo ngetaddrinfo #else #define getaddrinfo ogetaddrinfo #endif Seems like the same method we use for testing finite() and other possible-macros would handle this, then. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] -X flag in pg_dump
Dennis Bjorklund wrote: If all the -X flags come in a long form as well, then what use is the -X at all? Why would one want to use -X disable-triggers instead of --disable-triggers ? This was added before we had portable long options. I suppose we could phase it out. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] -X flag in pg_dump
Peter Eisentraut wrote: Dennis Bjorklund wrote: If all the -X flags come in a long form as well, then what use is the -X at all? Why would one want to use -X disable-triggers instead of --disable-triggers ? This was added before we had portable long options. I suppose we could phase it out. Excellent idea. Let's start by deprecating it in a prominent note in pg_dump.c. Maybe Dennis could add that to whatever patch he's preparing. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Cleaning up the INET/CIDR mess
Tom Lane wrote: Without the flag, it's okay for cidr-to-inet to be a binary-compatible (no function) conversion. However, inet-to-cidr has to either zero out bits to the right of the netmask, or error out if any are set. Joachim Wieland posted a patch that makes the coercion function just silently zero out any such bits. That's OK with me, but does anyone want to argue for an error? Zero the bits if it's an explicit cast, raise an error if not. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] -X flag in pg_dump
Peter Eisentraut wrote: Dennis Bjorklund wrote: If all the -X flags come in a long form as well, then what use is the -X at all? Why would one want to use -X disable-triggers instead of --disable-triggers ? This was added before we had portable long options. I suppose we could phase it out. Yes, I think you are right. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Cleaning up the INET/CIDR mess
Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane wrote: Without the flag, it's okay for cidr-to-inet to be a binary-compatible (no function) conversion. However, inet-to-cidr has to either zero out bits to the right of the netmask, or error out if any are set. Joachim Wieland posted a patch that makes the coercion function just silently zero out any such bits. That's OK with me, but does anyone want to argue for an error? Zero the bits if it's an explicit cast, raise an error if not. I know there's precedent for such behavior in the SQL spec, but it always seemed pretty ugly to me :-(. The patch-as-committed just silently zeroes the bits during any inet-cidr cast. I'll change it if there's consensus that that's a bad idea, but I don't really see a reason to. BTW, there is another case I came across that wasn't discussed before: if you do set_masklen() on a cidr value that reduces the netmask length, there are the same options of either zeroing the excess bits or complaining if any aren't zero. I've got that doing the zeroing too. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Cleaning up the INET/CIDR mess
Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane wrote: Without the flag, it's okay for cidr-to-inet to be a binary-compatible (no function) conversion. However, inet-to-cidr has to either zero out bits to the right of the netmask, or error out if any are set. Joachim Wieland posted a patch that makes the coercion function just silently zero out any such bits. That's OK with me, but does anyone want to argue for an error? Zero the bits if it's an explicit cast, raise an error if not. I know there's precedent for such behavior in the SQL spec, but it always seemed pretty ugly to me :-(. The patch-as-committed just silently zeroes the bits during any inet-cidr cast. I'll change it if there's consensus that that's a bad idea, but I don't really see a reason to. I agree. Let's do the zeroing and see if people complain about it. Throwing an error seems extreme. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Adding a --quiet option to initdb
On Thu, Jan 26, 2006 at 11:36:15AM +0100, Peter Eisentraut wrote: James William Pye wrote: Why should initdb give it [processing information] to the user if the user didn't request it in the first place? Because it shows important information that we want the user to see. Plus it can be a fairly long-running process on slower machines, so providing feedback to the user is good. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Adding a --quiet option to initdb
Jim C. Nasby wrote: On Thu, Jan 26, 2006 at 11:36:15AM +0100, Peter Eisentraut wrote: James William Pye wrote: Why should initdb give it [processing information] to the user if the user didn't request it in the first place? Because it shows important information that we want the user to see. Plus it can be a fairly long-running process on slower machines, so providing feedback to the user is good. Moreover, we should not change behaviour just on aesthetic grounds. For example, if initdb were suddenly to become quiet by default, we would need to add some version-specific processing to the buildfarm. As for a --quiet option, I just don't see why it is needed when /dev/null works perfectly well. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Cleaning up the INET/CIDR mess
Bruce Momjian wrote: I agree. Let's do the zeroing and see if people complain about it. I'm complaining. Losing data on a cast is not common behavior. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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
[HACKERS] A note about testing EXEC_BACKEND on recent Linuxen
I just wasted a couple hours trying to determine why an EXEC_BACKEND build would randomly fail on Fedora Core 4. It seems the reason is that by default, recent Linux kernels randomize the stack base address --- not by a lot, but enough to cause child processes to sometimes be unable to attach to the shared memory segment at the same place the postmaster did. You can work around this by doing (as root) echo 0 /proc/sys/kernel/randomize_va_space before starting the postmaster. You'll probably want to set it back to 1 when done experimenting with EXEC_BACKEND, since address randomization is a useful security hack. Just seems like something that should be in our archives ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Suggestions for post-mortem...
Tom Lane wrote: Define die quite nastily ... you haven't really said what went wrong. We lost data from this table, and ended up with transactions rolled back that were in fact committed (ie. data was in DB, program code thought they were rolled back). End result was we deemed the database to be in an unknown, uncorrectable and unstable state. These could all be manifestations of the ReadBuffer bug fixed in 8.0.6. Tickling that bug would result in zeroing out a recently-added table page, which would result in (a) more index entries than table entries, and (b) possible bleating from other processes wondering where their freshly inserted tuples went. This sounds consistent, I'd guess the 80 missing records correspond to 80 most recently updated. Not sure about the missing user -- I'll see what I can find. Thanks for the help -- we now have a probable cause, and a way forward. Alvaro Herrera wrote: Can you confirm how long does the vacuum take to run? Usually very quick, runs every minute -- so the problem occurred between the two vacuums. isolated to this one table, or does it manifest somewhere else? Do you have other errors that may indicate a hardware problem? No. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Proposal: new pg_dump options --copy-delimiter and --copy-null
Folks, This came up at work... I have seed database scripts quasi-generated from pg_dump which include COPY statements, but the data is hard to edit (especially cut paste operations) when the COPY delimiter is some non-visible character like \t. So I thought it would be handy to be able to control the DELIMITER and NULL options in COPY statements that pg_dump uses. Although it would be nice to make CSV and its dependencies one of the options, I'm not sure how pg_dump would handle the end-of-line problem, so I've skipped that part in the patch I've put together. The other option, FORCE QUOTE, doesn't make sense to me as a pg_dump option, but I'm not the arbiter of these things. With the patch, pg_dump would work exactly as usual without options, but it now has two extra options: --copy-delimiter and --copy-null. If set, these will be incorporated in COPY commands as appropriate. --copy-delimiter accepts any single byte other than '\r' or '\n'. --copy-null accepts any input. The patched pg_dump ignores --copy-* options in cases where COPY wouldn't happen anyway. What do you folks think? Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Proposal: new pg_dump options --copy-delimiter and --copy-null
David Fetter [EMAIL PROTECTED] writes: I have seed database scripts quasi-generated from pg_dump which include COPY statements, but the data is hard to edit (especially cut paste operations) when the COPY delimiter is some non-visible character like \t. This seems like an awfully weak use-case for adding to pg_dump's already overly complicated feature set. The difficulty of parsing COPY output is not simplified by making the delimiter variable --- more likely the reverse. Furthermore, it's quite unclear why you'd use pg_dump at all to generate a data file that you intend to feed to some other program. Seems to me that psql -c 'COPY ...' is a more likely front-end for such a process. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal: new pg_dump options --copy-delimiter and --copy-null
On Thu, Jan 26, 2006 at 10:17:05PM -0500, Tom Lane wrote:
David Fetter [EMAIL PROTECTED] writes:
I have seed database scripts quasi-generated from pg_dump which
include COPY statements, but the data is hard to edit (especially
cut paste operations) when the COPY delimiter is some
non-visible character like \t.
This seems like an awfully weak use-case for adding to pg_dump's
already overly complicated feature set.
Those who don't use it will never see it.
The difficulty of parsing COPY output is not simplified by making
the delimiter variable --- more likely the reverse.
It's fairly straight-forward.
Furthermore, it's quite unclear why you'd use pg_dump at all to
generate a data file that you intend to feed to some other program.
In my case, it's about being copy/paste friendly.
Seems to me that psql -c 'COPY ...' is a more likely front-end for
such a process.
Actually, it's not. I'm attaching my preliminary patch, as I see I
haven't explained it well enough.
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 415 235 3778
Remember to vote!
? pg_dump_copy.diff
Index: doc/src/sgml/ref/pg_dump.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/pg_dump.sgml,v
retrieving revision 1.81
diff -c -r1.81 pg_dump.sgml
*** doc/src/sgml/ref/pg_dump.sgml 1 Nov 2005 21:09:50 - 1.81
--- doc/src/sgml/ref/pg_dump.sgml 27 Jan 2006 02:22:41 -
***
*** 163,168
--- 163,188
/varlistentry
varlistentry
+ termoption--copy-delimiter=replaceable
class=parameterdelimiter/replaceable/option/term
+ listitem
+para
+ Use replaceable class=parameterdelimiter/replaceable
+ instead of the default tab character in commandCOPY/command
statements.
+/para
+ /listitem
+ /varlistentry
+
+ varlistentry
+ termoption--copy-null=replaceable
class=parameterstring_for_nulls/replaceable/option/term
+ listitem
+para
+ Use replaceable class=parameterstring_for_nulls/replaceable
instead of the
+ default \N in commandCOPY/command statements.
+/para
+ /listitem
+ /varlistentry
+
+ varlistentry
termoption-d/option/term
termoption--inserts/option/term
listitem
Index: src/bin/pg_dump/pg_dump.c
===
RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.427
diff -c -r1.427 pg_dump.c
*** src/bin/pg_dump/pg_dump.c 21 Jan 2006 02:16:20 - 1.427
--- src/bin/pg_dump/pg_dump.c 27 Jan 2006 02:22:48 -
***
*** 111,116
--- 111,121
/* flag to turn on/off dollar quoting */
static intdisable_dollar_quoting = 0;
+ /* Things used when caller invokes COPY options. */
+ const char *copy_delimiter_default = \t;
+ const char *copy_delimiter = \t;
+ const char *copy_null_default = \\N;
+ const char *copy_null = \\N;
static void help(const char *progname);
static NamespaceInfo *findNamespace(Oid nsoid, Oid objoid);
***
*** 246,251
--- 251,265
{disable-triggers, no_argument, disable_triggers, 1},
{use-set-session-authorization, no_argument,
use_setsessauth, 1},
+ /*
+* The following options don't have an equivalent short option
+* letter, and are not available as -X long-name. Use the
+* long form instead.
+*/
+
+ {copy-delimiter, required_argument, NULL, 2},
+ {copy-null, required_argument, NULL, 3},
+
{NULL, 0, NULL, 0}
};
int optindex;
***
*** 414,419
--- 428,453
break;
/* This covers the long options equivalent to
-X xxx. */
+ case 2:
+ copy_delimiter = strdup(optarg);
+ if (strlen(copy_delimiter) != 1)
+ {
+ fprintf(stderr, _(In %s,
copy-delimiter must be exactly one byte long, not %d\n),
+ progname,
strlen(copy_delimiter));
+ exit(1);
+ }
+ if ( (*copy_delimiter == '\r') ||
(*copy_delimiter == '\n') )
+ {
+ fprintf(stderr, _(In %s,
copy-delimiter may not be \\r or \\n.\n),
+ progname);
+ exit(1);
+ }
+ break;
+
+ case 3:
+
