[HACKERS] Typo in pg_dump documentation and new suggestion for Release Notes
In the online documentation for 8.2, pg_dump page, --table=table section, in the second Note: paragraph, rather than the old locution of -n sch -t tab probably should be rather than the old location of -n sch -t tab Also maybe this section on pg_dump in the release notes page for 8.2 should be changed from pg_dump now supports multiple -n (schema) and -t (table) options, and adds -N and -T options to exclude objects. Also, the arguments of these switches can now be wild-card expressions rather than single object names, for example -t 'foo*', and a schema can be part of a -t or -T switch, for example -t schema1.table1. to the following: pg_dump now supports multiple -n (schema) and -t (table) options, and adds -N and -T options to exclude objects. Also, the arguments of these switches can now be wild-card expressions rather than single object names, for example -t 'foo*', and a schema can be part of a -t or -T switch, for example -t schema1.table1. The pre-8.2 syntax for dumping an individual table is no longer supported. You must write something like -t sch.tab to select a table in a particular schema, rather than the old location of -n sch -t tab. -- Open WebMail Project (http://openwebmail.org) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Different length lines in COPY CSV
I too have to deal with this issue daily since I deal with Windows daily. It sounds like the source of the csv files were originally Excel files. I have never been able to figure out how Excel determines to quit putting null values in empty columns after X rows and resume again later on. If the file has less than 65000 rows I would suggest using OpenOffice 2.0 instead of Excel. OpenOffice does not stop filling the empty columns and with 2.0 it now supports the same maximum number of rows that Excel does. I use Perl constantly to reformat files and import them as a csv using the COPY command. I think the original poster would prefer a php solution though... While it is not a problem for me I do have other less technical users who don't know perl and this makes postgres much more difficult for them to use. Most of them come from a M$ Access background which can handle importing of Excel files directly thus don't have to deal with this issue. A file conversion utility would be very helpful for supporting Postgres with Windows especially if it could handle Excel files in their native format. Mike On Mon, Dec 12, 2005 at 07:58:52PM +0100, Martijn van Oosterhout wrote: On Mon, Dec 12, 2005 at 10:15:03AM -0500, Pollard, Mike wrote: Tom Lane wrote: What's been suggested in the past is some sort of standalone file-format-conversion utility, which could deal with this sort of stuff without having to also deal with all the backend-internal considerations that COPY must handle. So (at least in theory) it'd be simpler and more maintainable. That still seems like a good idea to me --- in fact, given my druthers I would rather have seen CSV support done in such an external program. Why not add hooks into COPY to call the user's massage functions? That way you don't have to read and write the data, then read it again to load it into the database. Well, it does make you wonder about supporting something like (perl style): \copy foo FROM 'myfilter dodgy-data.csv |' or maybe \copy foo from pipe 'myfilter dodgy-data.csv' or possibly \pipe foo from dodgy-data.csv using autodetecting-format-filter.pl Which would cause psql to fork/exec the filter and pass the output data to the server. We could then provide all sorts of parsers for format-of-the-week. This would probably include the converse: \pipe foo to table.xls using make-excel-spreadsheet.pl 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. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] PgInstaller error on upgrade
I am running XP Pro with SP2. Postgres 8.1 installed with MSI but later on I did upgrade the ODBC driver. Shortly after selecting upgrade.bat I received an error: This error may indicate a problem with the installation package. Number 2803 After clicking OK the installer continued anyway and seems to have worked. I executed the command using the runas and did remember to extract the files before proceeding. Mike ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Did this issue ever get resolved?
http://archives.postgresql.org/pgsql-hackers/2005-03/msg00260.php I don't see any other discussions about it in the archives. I too get these from time to time on my W2K box. Mostly the permission denied entries. If 1663/17269/1677179: Permission denied refers to a file under the data directory (I have found a folder 17269 that contains similiar names to 1677179) then it would seem postgres is trying to write data to a file that does not exist. Mike ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Dump from cygwin directly to windows
Ok, I didn't think you could do a restore as a non-superuser. I had executed the command on the remote(cygwin) machine in this case. I didn't specify a user to have it run as on the windows side (thought it would default to postgres). On the cygwin side I did execute it under a non-superuser domain account. When I first saw all the \N errors I thought it was a unix to windows end of line character conversion error. Thanks for your help. Mike On Sat, 2004-12-18 at 11:00, Tom Lane wrote: Mike G [EMAIL PROTECTED] writes: It looks like it started off as a permissions problem. I added the users to the database before trying again and this time it worked fine. I have attached the log from the original attempt if you wish to have a look. As best I can tell, you ran the restore script as a non-superuser, which probably wasn't a good thing to do. 8.0 pg_dump generates scripts that should cope with this situation a bit more reasonably, but it would still end up as a do-over in most cases because none of the object ownerships would come out right. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Dump from cygwin directly to windows
Hello, In testing RC1 today I attempted to use pg_dump -h cygwin_box mydb | psql -h RC1_box mydb It started off ok but at somepoint while transferring data over in a table it went astray. I started getting rows and rows of syntax errors most of them saying invalid command \N. All of the functions, views, and table definitions transferred successfully but none of the data in the tables did. I swear I did this in an earlier beta version and it worked. I don't see any option in pg_dump to specify options for the Copy commands issued. Using the pg_dump as insert statements option seems to be working fine. Not sure if this would be considered a bug or new feature. Mike ---(end of broadcast)--- TIP 3: 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] FW: postgres shmat request fails on win98 (cygwin)
Hello, I will bring it up with the postgresql hackers. PS - Sorry for the new posting. I read these via digest. Mike * From: Jason Tishler jason at tishler dot net * To: cygwin at cygwin dot com * Date: Sun, 8 Aug 2004 11:07:56 -0400 * Subject: Re: Initdb FATAL error shmat - Win98 Cygwin 1.5.10-3 - PostgreSQL 7.4.3 * References: [EMAIL PROTECTED] Corinna, I apologize for the delay, but I was on vacation... On Tue, Aug 03, 2004 at 03:30:58PM +0200, Corinna Vinschen wrote: I tried it and it actually only happens on 9x. I found that postgres tries to shmat to an address which I have no idea about where it comes from. The problem with that address is, that it's neither a multiple of SHMLBA, nor does postgres call shmat with the SHM_RND flag. For some reason the address is ok on NT. So, Jason, do you have an idea why that happens? No. Two questions come to mind: - How does postgres evaluate that address I don't know. and why does it only fail on 9x? Ditto. - Why does postgres use a fixed address at all, instead of using NULL to let the system decide which address to use? Ditto. I do not have access to Windows 9x and (unfortunately) have not used PostgreSQL seriously for 3 years now, so I'm not particularly motivated to work on this problem. Sorry, Jason -- PGP/GPG Key: http://www.tishler.net/jason/pubkey.asc or key servers Fingerprint: 7A73 1405 7F2B E669 C19D 8784 1AFD E4CC ECF4 8EF6 -- Unsubscribe info: http://cygwin.com/ml/#unsubscribe-simple Problem reports: http://cygwin.com/problems.html Documentation: http://cygwin.com/docs.html FAQ: http://cygwin.com/faq/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] FW: postgres shmat request fails on win98 (cygwin)
According to the docs, even the 8.0 developer docs (http://developer.postgresql.org/docs/postgres/install-win32.html), Tip: If you are using Windows 98 or newer you can build and use all of PostgreSQL the Unix way if you install the Cygwin toolkit first. In that case see Chapter 14. I don't see any mention of XP, whether the Pro or Home version, will be supported in version 8 at the moment either. Mike On Sun, 2004-08-08 at 13:28, Doug McNaught wrote: Mike G [EMAIL PROTECTED] writes: Hello, I will bring it up with the postgresql hackers. PS - Sorry for the new posting. I read these via digest. Neither the Cygwin nor the (beta) Windows-native version of Postgresql is supported in any way on Windows 9x/ME AFIAK. Anyone trying to run it there is strictly on their own. -Doug ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] upper() / lower() regression test case needed?
Hello, Going through the regress test sql files I noticed that when testing string functions there was no upper / lower case tests. I see upper / lower being used in the select_having and select_implicit files in the GROUP BY and ORDER BY clauses. Good enough or should I submit patch for adding these test cases to strings.sql? Mike ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] I need Help
Yes it is possible for both to be on the same pc. Please send mail to the general or novice list if you need more help. On Mon, 2004-05-03 at 11:05, olivia jurado wrote: Hi. I'm from Panama. I don't speak english very well but I'm learning english. I Need help. I installed postgresql 7.4 in my computer, I'm using redhat 9.0 . I installed pgadmin III but I can't to conecct to the server. The port 5432 is not open. I have one computer. If possible to use server and client in this same machine. please help me. thanks _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 3: 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] Duplicate variable declared?
Hello, In file postgresql-7.4.2/src/backend/utils/fmgrtab.c line 336 and line 337 are both equal to extern Datum array_push (PG_FUNCTION_ARGS); I have created a simple perl script that examines files line by line and if 2 in a row match prints a result. Sometimes it catches bad cvs checkins or copy/paste mistakes. I will continue to scan the output. Hope this helps. Mike On Tue, 2004-04-20 at 22:51, Matthew T. O'Connor wrote: Tom Lane wrote: Matthew T. O'Connor [EMAIL PROTECTED] writes: This is a good question, and I would like some opinions from some other people more informed than I. You *can not* vacuum other sessions' temp tables; you don't have access to the data. (You have no way to get at pages that are modified in someone else's local buffer manager.) You could vacuum your own temp tables, if you had any, but I would hardly expect autovacuum to have any. Ok, so I will change pg_autovacuum to explicitly ignore temp tables. Just to be sure, I can do this by avoiding anything found in the pg_temp schemea, or is there a better way? Is it possible that a user could or would put a non-temp table the pg_temp schemea? In reference to Chris' followup question, you *should* be vacuuming system catalogs, and you *should not* be vacuuming TOAST tables. VACUUM on a regular table automatically hits the associated TOAST table. Ok, this is as I thought, pg_autovacuum is already doing the right thing here. (see the post I just sent a few minutes ago for more details). Thanks Tom, Matthew ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] signature.asc Description: This is a digitally signed message part
[HACKERS] Returning number of rows - Copy In function
Hello,
I was looking at putting the code for this in copy.c
CopyReadLineFunction.
If I do a printf at point A it compiles, installs, runs, doesn't display
any data after running initdb and returns a result when a COPY FROM is
executed in psql.
If I do a printf at point B it compiles, installs, and then crashes when
executing initdb.
loading pg_description ... initdb: child process exited with exit code
139
initdb: failed
Thoughts on why A is ok but B is not?
I don't really want to send any results to the screen if it is called
when first creating a database. I have looked for something like
CMD_SELECT to identify when Initdb is executed.
Is their an existing variable I can check that I have not found?
Index: copy.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/commands/copy.c,v
retrieving revision 1.218
diff -b -B -r1.218 copy.c
107c108
---
int co_rows_inserted = 0;
1448a1451,1452
/*my code*/
co_rows_inserted = 0;
1782a1787
/*this is being called during database creation!!*/
1786a1792,1818
co_rows_inserted = copy_lineno;
char *buf = ;
gcvt(co_rows_inserted, 10, buf);
printf(%i %s\n,co_rows_inserted, buf);/*ok here point A*/
if (co_rows_inserted 0)
{
printf(%i %s\n,co_rows_inserted, buf);/*not ok here point B*/
/*
StringInfoData msgbuf;
pq_beginmessage(msgbuf, ' ');
pq_sendstring(msgbuf, gettext(buf) );
pq_endmessage(msgbuf);
*/
}
else if (co_rows_inserted == 0)
{
ereport(WARNING,(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
errmsg (File used to import data is empty.),
errhint(Double check file name used)));
}else if (co_rows_inserted 0)
{
ereport(ERROR,
(errcode(ERRCODE_BAD_COPY_FILE_FORMAT), errmsg(Rows copied were less than zero. Copy function has been broken),
errhint(File a bug please)));
}
signature.asc
Description: This is a digitally signed message part
Re: [HACKERS] COPY formatting
I deal with this daily in a cygwin environment. I wrote a simple c++ program where I hardcoded the input file name/location and output file name/location. I strip the quotation marks out where they are used for identifying text fields and change the comma's used as CSV's to pipes. I use a combination of bash scripting to execute the c++ program and then Perl to execute a stored procedure. I am new to Perl so I have not yet considered migrating it all into Perl. The dos2unix tools in cygwin always messed up the first character of the first line. I thought the real issue with the copy function and CSVs was that it did not like the use of quotations around the fields to identify text fields. For a true Windows port handling MS Excel files in their native format would be a goal I would hope. If your api could handle that then I would agree with your method. On Wed, 2004-03-17 at 22:14, Joshua D. Drake wrote: That is why I suggested providing a pre-written/pre-compiled/installed function for CSV (call it CSV?). Advanced users could still write their own as people can write many other things if they know their ways. As someone who just went through a whole truckload of crap getting delimited files parsed from MSSQL to PostgreSQL. I believe yes this would be great thing. We ended up using plPython with the CSV module. Sincerely, Joshua Drake ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings signature.asc Description: This is a digitally signed message part
[HACKERS] Confusion over Copy.c/Count rows from file patch
Hi,
I am trying to revise my patch for counting the number of rows inserted
when using a COPY statement.
I set it up a printf to see the results in the log file.
When I execute a COPY the first time it reports 0 rows inserted(but
actually inserts all rows). If I execute again it reports the number of
rows + 1. Execute a third time and the number of rows are reported +
1(same result as 2nd run). All three tests were done against the same
file.
If there is a simple explanation to why ProcessUtility is not called the
first time COPY is executed and it is from then on it could help me a
lot.
Index: copy.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/commands/copy.c,v
retrieving revision 1.218
diff -u -r1.218 copy.c
--- copy.c 10 Feb 2004 01:55:24 - 1.218
+++ copy.c 16 Mar 2004 04:25:46 -
@@ -104,8 +104,7 @@
static const char *copy_relname; /* table name for error messages */
static int copy_lineno; /* line number for error messages */
static const char *copy_attname; /* current att for error messages */
-
-
+int co_rows_inserted;
/*
* These static variables are used to avoid incurring overhead for each
* attribute processed. attribute_buf is reused on each CopyReadAttribute
@@ -1271,7 +1270,8 @@
attr = tupDesc-attrs;
num_phys_attrs = tupDesc-natts;
attr_count = length(attnumlist);
- num_defaults = 0;
+ num_defaults = 0;
+
/*
* We need a ResultRelInfo so we can use the regular executor's
@@ -1734,6 +1734,7 @@
ExecCloseIndices(resultRelInfo);
FreeExecutorState(estate);
+/*looks like we return success here*/
}
@@ -1779,12 +1780,27 @@
* of a frontend multibyte character couldn't look like ASCII characters.
*/
for (;;)
- {
+ {
+
c = CopyGetChar();
if (c == EOF)
{
result = true;
- break;
+ co_rows_inserted = copy_lineno;
+/*Now report number of rows imported to user*/
+
+if (co_rows_inserted == 0)
+ {
+ ereport(WARNING,(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
+errmsg (File used to import data is empty.),
+errhint(Double check file name used)));
+ }else if (co_rows_inserted 0)
+{
+ereport(ERROR,
+ (errcode(ERRCODE_BAD_COPY_FILE_FORMAT), errmsg(Rows copied were less than zero. Copy function has been broken),
+errhint(File a bug please)));
+}
+ break;
}
if (c == '\r')
{
Index: utility.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/tcop/utility.c,v
retrieving revision 1.210
diff -u -r1.210 utility.c
--- utility.c 10 Feb 2004 01:55:26 - 1.210
+++ utility.c 16 Mar 2004 04:28:07 -
@@ -60,7 +60,7 @@
/*
* Error-checking support for DROP commands
*/
-
+extern int co_rows_inserted;
struct msgstrings
{
char kind;
@@ -1283,8 +1283,9 @@
tag = COMMENT;
break;
- case T_CopyStmt:
- tag = COPY;
+ case T_CopyStmt:
+tag = COPY;
+printf(COPY %i ROWS FROM FLAT FILE\n,co_rows_inserted );
break;
case T_RenameStmt:
signature.asc
Description: This is a digitally signed message part
