Re: Query on exception handling in PL/pgSQL
От | Johan Wehtje |
---|---|
Тема | Re: Query on exception handling in PL/pgSQL |
Дата | |
Msg-id | 41A8EEF2.6090607@tpgi.com.au обсуждение исходный текст |
Ответ на | Re: Query on exception handling in PL/pgSQL (Victor Ciurus <vikcious@gmail.com>) |
Список | pgsql-general |
I would second this motion as it relates to bulk inserts, I also love PG, but it's bulk insert/copy from file routines are definitely one area where it cannot claim to be "most advanced". My own wish list includes : *Copy to a new table - so that a table in the db is created - default is for all fields to be a relaxed varchar, but there should be an option of using a list of field names paired with data types in the copy command. * specify a start row , a la MS SQL - so a file that might have 2 rows of header,column information - or in the case of many Terminal captures an arbitrary number of blank lines.. *support for using the first rows values as field names when copying to a new table - or using them to map columns in the file to the destination table. *Specify row delimiter - so a row delimiter can be other than a new line character - or can be a character combination. This would help when dumping multi line text field values from one DB and loading them in another without having to use the backslash escaped newline method. *Commit after - specify number of rows to read before committing - and behavior on error, ideally if I have 100,000 rows in a bulk copy and there was one error in line 45,000 the server would write a file with the same name as the input file + an extension like err, with just that row omitted . Also it would load 100 rows at a time - perform any logging. Johan Wehtje Victor Ciurus wrote: >And how does exactly PG8.0 help us with this? > >Tom, coould you be so kind and tell me/us when will PG incorporate >such a hugely claimed feature like "copy table_x from 'file_y.txt' >(using delimiters) IGNORE ON DUPLICATES"? > >I do LOVE PG and I'm very pleased with its overall features level >still the lack of "ingore" on unique key constraint insert (especially >for large bulk inserts!) makes me feel quite helpless! > >Or, is there another way, like in Oracle's exception handling, to >hanle this thing (COPY/INSERTs with Ignore?) I can't emagine how such >a 'simple' (no offense!) db like MySQL can hanlde INSERT IGONERS (not >to mention the 'so serious' ORACLE, INFORMIX , MSSQL and prolly DB2) >and PG not beeing able to do this! > >Please enlighten me! I saw a huge deabte over this feature, dated >2001-2002, ended up on a 'promise' that such feature will be made >available staring with PG 7.1 or 7.2 (can't remeber exactly!) still no >sign of improvement on it! > >Best regards, >Victor > > >On Fri, 26 Nov 2004 14:06:52 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > >>diya das <diyadas@yahoo.com> writes: >> >> >> >> >>>My pgsql function >>>does an insert to a table and I have opened a >>>transaction block before calling my function from the >>>client. When the insert operation fails due to unique >>>key violation the whole transaction aborts. Is there a >>>way I can handle this error in my pgsql funtion rather >>>that aborting and not executing the rest of the >>>operations? >>> >>> >>Not before PG 8.0. In earlier versions you might be able to work around >>the problem by testing for existence of the target key before you >>insert; though this has obvious race-condition problems if you expect >>multiple clients to be doing it simultaneously. >> >> regards, tom lane >> >>---------------------------(end of broadcast)--------------------------- >>TIP 7: don't forget to increase your free space map settings >> >> >> > >---------------------------(end of broadcast)--------------------------- >TIP 7: don't forget to increase your free space map settings > >. > > >
В списке pgsql-general по дате отправления: