[SQL] Very slow DELETE on 4000 rows of 55000 row table

I've got a DELETE FROM that seems to run forever, pegging the CPU at 100%. I can't figure out why it's slow. Any clues? stage=# EXPLAIN DELETE FROM EG_INVOICE WHERE PERIOD_ID = 1017506; Index Scan using ix22f7bc70c7de2059 on eg_invoice (cost=0.00..105.39 rows=3955 width=6) Index Cond:

Re: [SQL] Update problem.

Thanks. But to do the UPDATE i have to write each column name (for recrd 4) and with its column name (for record 2) which is quite alot to write :P UPDATE MOD48_00_2007 SET te_cognome= te_cognome, te_paternita= te_paternita WHERE N_GEN= 9 so it will be like that? But i have to set each

Re: [SQL] Serial

am Tue, dem 03.04.2007, um 11:20:16 +0200 mailte Shavonne Marietta Wijesinghe folgendes: The problem is when i INSERT a new record. It takes the value n_gen = 6 but i need it to take the value 5. It keeps a record of the delete records. Is there anyway in PostgreSQL i can change it? or

Re: [SQL] Serial

thanks. I read the page you gave. CREATE SEQUENCE seq_mytable_n_gen; CREATE TABLE mytable ( n_gen int nextval('seq_mytable_n_gen'), mycolumn1 int, mycolumn2 int ); i tried creating it like that. The sequence was created without any error. But for the create table i get ERROR: syntax

Re: [SQL] Serial

Shavonne Marietta Wijesinghe wrote: thanks. I read the page you gave. CREATE SEQUENCE seq_mytable_n_gen; CREATE TABLE mytable ( n_gen int nextval('seq_mytable_n_gen'), mycolumn1 int, mycolumn2 int ); i tried creating it like that. The sequence was created without any error.

Re: [SQL] Serial

am Tue, dem 03.04.2007, um 11:55:10 +0200 mailte Shavonne Marietta Wijesinghe folgendes: thanks. I read the page you gave. Really? CREATE SEQUENCE seq_mytable_n_gen; CREATE TABLE mytable ( n_gen int nextval('seq_mytable_n_gen'), mycolumn1 int, mycolumn2 int ); i tried

Re: [SQL] Serial

I'm mixed up now. I was suppose to something but i did something else. OK so i have my FAMOUS table with the n_gen serial NOT NULL I got lost a bit. When and where do i use the setval() ?? For example i INSERT records via ASP. so i should put the setval() in the INSERT INTO of the ASP page??

Re: [SQL] Serial

am Tue, dem 03.04.2007, um 12:29:37 +0200 mailte Shavonne Marietta Wijesinghe folgendes: I'm mixed up now. I was suppose to something but i did something else. OK so i have my FAMOUS table with the n_gen serial NOT NULL I got lost a bit. When and where do i use the setval() ?? Only to

[SQL] best way: diary functions.

Hi folks I've got 2 tables, availabiliy ~~~ stdate date edate date workdaysinteger commentstext example record 2007-03-01 2007-03-07 5 Please can I have alternate days roster rdate date rdiag varchar(10) example 2007-03-01 B12 2007-03-03

[SQL] LOCK command inside a TRANSACTION

Hi! I need Postgresql somehow does this for me: - if an user query a select on a table, the rows of the table in the result of this select can not be updated or deleted by another user until this one update, delete or discard the changes on those rows. I've found something about the LOCK

[SQL] Using a variable as a view name in a select

I have created a view, called april_may. I need to select this view by combineing to fields in the database to create the view name etc ... Create view as select * from table_X; I need to do something like this ... Select * from (select table.start_month||_||table.end_month);

Re: [SQL] Using a variable as a view name in a select

am Tue, dem 03.04.2007, um 9:04:00 -0400 mailte Wilkinson, Jim folgendes: I have created a view, called april_may. I need to select this view by combineing to fields in the database to create the view name etc ? Please, no answer to an other mail and change the subject to a new subject.

Re: [SQL] Using a variable as a view name in a select

Wilkinson, Jim wrote: I have created a view, called april_may. I need to select this view by combineing to fields in the database to create the view name etc ... Jim Learn to use compose or write and not reply when you want to ask a fresh question. My email rolled this into the LOCK thread

Re: [SQL] LOCK command inside a TRANSACTION

Am Dienstag, 3. April 2007 14:48 schrieb Carlos Santos: - if an user query a select on a table, the rows of the table in the result of this select can not be updated or deleted by another user until this one update, delete or discard the changes on those rows. Sounds like SELECT FOR UPDATE.

Re: [SQL] Serial

On Tue, 2007-04-03 at 04:20, Shavonne Marietta Wijesinghe wrote: Ok so i'm posting alot in the forums. Anyway for a change i have another problem ^___^ I have a table that has a field n_gen serial NOT NULL ermm let me explain. I have 5 records inserted (n_gen = 1, 2, 3, 4, 5) At a

[SQL] A long-running transaction

I have a Java (java 1.1) program that I wrote some years ago, to read records from a text file and insert it into a ostgresql database. One of the assumptions I made was that one file contained one day's data, maybe as many as 1500 records, and I coded it to do the whole lot as one

Re: [SQL] Using a variable as a view name in a select

Jim My initial reaction is what are you trying to achieve? Surely you could have one underlying table with dates in it and SELECT * from mytable WHERE date1='2007/04/01' AND date2='2007/05/01'; but otherwise, like John, I would use an external scripting language to create the table name. Hilary

Re: [SQL] Serial

--- Yes there is, and you generally shouldn't do it. There are issues with race conditions and misreferenced data that can happen when you try to reuse sequence numbers. Sadly, some poorly defined business processes require this. Are you required to have sequential numbers, or is just

Re: [SQL] Update problem.

On Tue, Apr 03, 2007 at 09:13:00AM +0200, Shavonne Marietta Wijesinghe wrote: Thanks. But to do the UPDATE i have to write each column name (for recrd 4) and with its column name (for record 2) which is quite alot to write :P UPDATE MOD48_00_2007 SET te_cognome= te_cognome, te_paternita=

Re: [SQL] Using a variable as a view name in a select

Jim So let's suppose you have a master table of incidents incident_no (serial) incident_date (timestamp) other fields My understanding is that you now want to eg count the incidents starting in a given month and going forwards for 12 months, grouping the results by month. Have I understood the

Re: [SQL] Using a variable as a view name in a select

Almost, in the table there are multiple different incidents. Incident April May June July Aug === Falls1 0 1 0 0 Roof Area 0 1

[SQL] plpgsql function question

Hi, I am having a requirement here. 1) I need to write a plpgsql function where it takes the input parameter of a structure of a table. 2) The table has 15 columns 3) It does lots of validation based on the parameter and finally returns an integer as output parameters Q)

Re: [SQL] plpgsql function question

Karthikeyan Sundaram [EMAIL PROTECTED] schrieb: Hi, I am having a requirement here. 1) I need to write a plpgsql function where it takes the input parameter of a structure of a table. Because? To build this table? You can pass an ascii-text with

Re: [SQL] plpgsql function question

Hi, I guess there is some misunderstanding from my question Let me elaborate more clearly. My Table is Create table a ( i int, j varchar(20), k date); Create or replace function a_func (in p_i int, in p_j varchar, in p_k date) returns int as $$ - do the validation

Re: [SQL] [pgsql-sql] Daily digest v1.2492 (19 messages)

Hi John, It sounds like a disk-bound operation, so cpu is not maxed out. I'm not clear on all the details of your operation but it sounds like you're using Java to do row-by-row based inserts, selects and updates within a transaction, from a file. This can be a very slow process if you have

Re: [SQL] plpgsql function question

am Tue, dem 03.04.2007, um 11:33:39 -0700 mailte Karthikeyan Sundaram folgendes: Hi, I guess there is some misunderstanding from my question Maybe. Let me elaborate more clearly. My Table is Create table a ( i int, j varchar(20), k date); Create or replace

Re: [SQL] A long-running transaction

On Tue, Apr 03, 2007 at 10:16:13PM +0800, John Summerfield wrote: It is hitting the disk pretty hard now on this machine, but the laptop's still going too, and the disk seems to run about half the time, part of a second running, part idle (but the intervals are getting shorter). It struck

Re: [SQL] plpgsql function question

It should work pretty much like you have it. You don't need a type; the table is already a type. Something like: create or replace function a_func (in p_row a) returns int as $$ if p_row.i ... if p_row.j ... $$ If it does not work, show the error and I'll try to dig up an example. John

[SQL] exception handling in postgres plpgsql

Hi, I am having a function like this create or replace function audio_format_func (in p_bitrate audio_format.audio_bitrate%TYPE,in p_sampling_rate audio_format.sampling_rate%type,in p_bit_per_sample audio_format.bit_per_sample%type,in p_audio_codec

Re: [SQL] exception handling in postgres plpgsql

Hi, On Tue, 2007-04-03 at 15:35 -0700, Karthikeyan Sundaram wrote: exception when NO_DATA_FOUND then return 100; end; $$ language 'plpgsql'; When I compile, I am getting an error message ERROR: unrecognized exception condition no_data_found CONTEXT: compile of

Re: [SQL] exception handling in postgres plpgsql

Karthikeyan Sundaram [EMAIL PROTECTED] writes: When I compile, I am getting an error message ERROR: unrecognized exception condition no_data_foundCONTEXT: compile o= f PL/pgSQL function audio_format_func near line 15 =20 How will I handle exceptions in postgres? Reading between the lines I

Re: [SQL] plpgsql function question

am Tue, dem 03.04.2007, um 13:19:26 -0700 mailte Karthikeyan Sundaram folgendes: Thank you very much. It works. I am not doing any insert or update hence I cannot create a trigger. But my another question is How will I pass the values to Foo parameters. I mean I want to pass i