Re: [SQL] Very slow DELETE on 4000 rows of 55000 row table
Bryce Nesbitt <[EMAIL PROTECTED]> writes: > 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? Unindexed foreign key constraints pointing to this table, perhaps? EXPLAIN ANALYZE would give a great deal more clue than plain EXPLAIN. 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
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 column name = to the column name. And i have to do it for echt field? isn't there any other way.. "I hate writting :P" And Andrew can explain a bit the setval() Thanks Shavonne Wijesinghe From: "Andrew Sullivan" <[EMAIL PROTECTED]> To: Sent: Monday, April 02, 2007 5:17 PM Subject: Re: [SQL] Update problem. On Mon, Apr 02, 2007 at 04:52:46PM +0200, Shavonne Marietta Wijesinghe wrote: At a surtain point i need to replace a record with another For example i have inserted 4 records. (1, 2 , 3 , 4) I need to replace all the values from the record 4 to the record 2 but keeping the n_gen serial key. And then delete the record 4. So that the next record i insert will take the n_gen 4 If I understand you correclty, you can do an UPDATE to record 4 to record 2, then do a setval() on the sequence. The setval() is tricky, though, because you have the problem that other connections could be using it. I'd lock the table in question while you did all this. A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Serial
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 surtain point i DELETE the record 2 and UPDATE the record 5 with the n_gen 2. So now i have 4 records (n_gen = 1, 2 (EX 5), 3, 4) Upto this point i'm ok. 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 another progressive that i can use for n_gen ?? Thanks alot. Shavonne Wijesinghe
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 another progressive that i > can use for n_gen ?? Yes, setval(). http://www.postgresql.org/docs/8.1/interactive/functions-sequence.html Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
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 error at or near "nextval" at character 38
What should i do?
Shavonne Wijesinghe
- Original Message -
From: "A. Kretschmer" <[EMAIL PROTECTED]>
To:
Sent: Tuesday, April 03, 2007 11:32 AM
Subject: 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 another progressive
that i
can use for n_gen ??
Yes, setval().
http://www.postgresql.org/docs/8.1/interactive/functions-sequence.html
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
---(end of broadcast)---
TIP 6: explain analyze is your friend
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. But for the create table i get
>
> ERROR: syntax error at or near "nextval" at character 38
>
> What should i do?
Add the missing "default":
CREATE TABLE mytable
(
n_gen int DEFAULT nextval('seq_mytable_n_gen'),
mycolumn1 int,
mycolumn2 int
);
--
Milen A. Radev
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
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 creating it like that. The sequence was created without any error.
> But for the create table i get
>
> ERROR: syntax error at or near "nextval" at character 38
>
> What should i do?
Your question was, how to set the sequence to a new value, and my answer
was: use setval().
Now you tried to create a new table. You have a simple syntax error:
,[ example ]
| test=# create sequence testseq;
| CREATE SEQUENCE
| test=*# create table testtab (id int default nextval('testseq'));
| CREATE TABLE
`
You forgot the word 'default'.
>
>
> Shavonne Wijesinghe
>
> - Original Message -
> From: "A. Kretschmer" <[EMAIL PROTECTED]>
Please, no silly text above with fullquote below, i read from top to
bottom...
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---(end of broadcast)---
TIP 6: explain analyze is your friend
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?? And even when i do a SELECT i should use the setval()?? SELECT setval(N_GEN) FROM MyTable ORDER BY N_GEN::INT DESC ?? Shavonne Wijesinghe ---(end of broadcast)--- TIP 6: explain analyze is your friend
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 manipulate the current value of this sequence. > > For example i INSERT records via ASP. so i should put the setval() in the > INSERT INTO of the ASP page?? No. You can define your table like: create table foo (id serial, ...) This creates implicit a own sequence and set the default for id to nextval(). > And even when i do a SELECT i should use the setval()?? For a select? No. 12:39 < akretschmer> ??sequence 12:39 < rtfm_please> For information about sequence 12:39 < rtfm_please> see http://techdocs.postgresql.org/college/001_sequences/index.php 12:39 < rtfm_please> or http://www.postgresql.org/docs/current/interactive/sql-createsequence.html 12:39 < rtfm_please> or http://www.postgresql.org/docs/current/static/functions-sequence.html Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[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 B11 2006-03-05 B12 2007-03-07 B13 What would be the best way to create a view to list every date within a range giving either rostered, available but not rostered, and not available? I've read through the docs and created a function (below) to return every date within a range, but I can't get my head round converting that to a query. Although I'm doing this as a learning exercise, it will be used in a web site I'm developing so comments on speed and efficiency would also be welcome. Gary create or replace function date_range(fdate date,tdate date) returns setof date AS $PROC$ DECLARE wdate date; BEGIN return next fdate; wdate:=fdate+1; while wdate <= tdate LOOP return next wdate; wdate:=wdate+1; end LOOP; return; END; $PROC$ LANGUAGE plpgsql; -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(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
[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 command inside a TRANSACTION but I didn't see how I could do that yet. Does anybody have any ideas? Carlos Henrique Iazzetti Santos Compels Informática Santa Rita do Sapucaí - MG Brazil www.compels.net __ Fale com seus amigos de graça com o novo Yahoo! Messenger http://br.messenger.yahoo.com/
[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); == Start_month = april End_month = May What I what to pass to the select is the combination of the 2 fields as the view name. Any ideas ?
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. Your mail sorted in the wrong thread. > Select * from (select table.start_month||_||table.end_month); > > == > > Start_month = april > > End_month = May > > > > What I what to pass to the select is the combination of the 2 fields as the > view name. Perhaps with a function that receive start and end and returns the result as a table (set-returning function) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
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 making it incredibly difficult to find. Create view as select * from table_X; I need to do something like this ... Select * from (select table.start_month||_||table.end_month); == Start_month = april End_month = May What I what to pass to the select is the combination of the 2 fields as the view name. Any ideas ? The simple ways I can think of are external to postgresql, and depend on your host environment. For example, on Linux (or OS X) I might do something like this: psql -c "select * from (select table.$(date +%B -d 'last month')_table.$(date +%B))" Note, the above para is really a single line, there is a space between "last" and "month." on DOS it might be a little trickier, but probably VB Script can do it. In Java, it is somewhat similar but different. If this doesn't answer, give better info and maybe someone else can help you properly. ---(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: [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. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
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 surtain point i DELETE the record 2 and UPDATE the record 5 with > the n_gen 2. So now i have 4 records (n_gen = 1, 2 (EX 5), 3, 4) Upto > this point i'm ok. > > 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 another progressive > that i can use for n_gen ?? 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 something that feels good / looks good? ---(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
[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 transaction so either a single file was loaded in its entirity, or none of its data was. I lost the Java code, but revived the idea and I've collected about two years' data using (Linux) shell scripts, and loading the data using psql. Then, I found the Java code on a disused hard disk:-) I made the necessary changes for it to build in java 1.5, and used psql to extract data from my new database in the correct format for the old program. This time, I have a little more data than I ever loaded at once before: [EMAIL PROTECTED]:~$ wc -l testdata 6242217 testdata [EMAIL PROTECTED]:~$ \ls -hl testdata -rw-r--r-- 1 summer summer 285M 2007-03-28 22:32 testdata [EMAIL PROTECTED]:~$ Now, it wouldn't surprise me if postgresql used lots of memory - but how much could it possibly need? My laptop, where I first tried this, has 1.25 Gbytes, so I could allow it some. It wouldn't surprise me a lot if it used lots of memory and caused all sorts of problems, but no, it's chugging away, still using no more RAM than it could have had on my old Pentium 133 all those years ago. In the process of checking it out, I've set it running on a machine with a AMD Sempron(tm) 2400+ running Kubuntu 6.10 (kernel is 2.6.17-6-server-xen0) and 512 Mbytes of RAM. This is the java program:-) summer pts/6:0.0 Thu205days 1:07 1:07 /usr/bin/gij-4.1 -cp /usr/s It's been running five days so far, and I can see where it's up to by attaching strace. It's reading 2k of the input file every few seconds. Okay, clearly something's wrong, and I don't think it's all my crddu code. No probs swapping: [EMAIL PROTECTED]:~$ free total used free sharedbuffers cached Mem:460800 456472 4328 0860 262164 -/+ buffers/cache: 193448 267352 Swap: 14618722841461588 [EMAIL PROTECTED]:~$ 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 me as fairly curious that neither postgresql nor the application was hogging the CPU. Perhaps the laptop is more interesting: look at the size of the buffer pool: [EMAIL PROTECTED]:~> free total used free sharedbuffers cached Mem: 12955281268548 26980 0 3976 392388 -/+ buffers/cache: 872184 423344 Swap: 1941496 326561908840 [EMAIL PROTECTED]:~> Again, no problem with over-use of RAM, and I'm logged on using KDE too and that's running fine. It's been running a little longer here: summer pts/2328Mar07 5days 25:12 25:11 java -cp /home/summer/Classes/:/usr/share/p This is Sun's Java 1.5 on OpenSUSE 10.2. This is what suggested I should write: [EMAIL PROTECTED]:~> procinfo Linux 2.6.18.8-0.1-default ([EMAIL PROTECTED]) (gcc 4.1.2 20061115) #1 1CPU [Echidna.] Memory: TotalUsedFree Shared Buffers Mem: 1295528 1271720 23808 03716 Swap: 1941496 32656 1908840 Bootup: Tue Mar 27 18:50:19 2007Load average: 2.21 2.65 2.69 2/243 19305 user : 1d 3:17:04.03 16.0% page in : 131097310 disk 1: 3079516r20087664w nice : 0:05:39.64 0.1% page out: 197016649 system: 2d 20:38:37.13 40.1% page act: 87906251 IOwait: 2d 0:46:37.33 28.5% page dea: 16218135 hw irq: 0:44:46.71 0.4% page flt: 306255213 sw irq: 0:50:04.69 0.5% swap in : 4026 idle : 1d 0:36:29.73 14.4% swap out: 9552 uptime: 7d 2:59:20.97 context : 702502442 irq 0: 153880209 timer irq 7: 0 parport0 irq 1: 69402 i8042 irq 8: 2 rtc irq 2: 0 cascade [4] irq 9: 1696942 acpi irq 3: 4 irq 10: 1 irq 4: 4 irq 11: 71842329 ehci_hcd:usb1, uhci_ irq 5: 28545863 Intel 82801DB-ICH4irq 12:467432 i8042 irq 6: 1 irq 14: 25021586 ide0 [EMAIL PROTECTED]:~> Look at that line beginning "System:" two days 20 hours in the Linux kernel. It's my guess that the Linux kernel is spending a great deal of time manipulating that buffer pool. This shows postgresql taking 60% CPU: [EMAIL PROTECTED]:~> ps xaru USER PID %CPU %MEMVSZ RSS TTY STAT START TIME COMMAND summer 20237 0.2 1.2 263716 15988 pts/23 Rl+ Mar28 25:11 java -cp /home/summer/Class postgres 19321 0.1 0.3 19844 3984 ?D21:50 0:00 postgres: sum
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 At 14:04 03/04/2007, 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 Create view as select * from table_X; I need to do something like this Select * from (select table.start_month||_||table.end_month); == Start_month = april End_month = May What I what to pass to the select is the combination of the 2 fields as the view name. Any ideas ? Hilary Forbes DMR Limited (UK registration 01134804) A DMR Information and Technology Group company ( www.dmr.co.uk) Direct tel 01689 889950 Fax 01689 860330 DMR is a UK registered trade mark of DMR Limited **
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 something that > feels good / looks good? The following link develops the use of a gapless sequence: http://www.varlena.com/GeneralBits/130.php Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
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= te_paternita > WHERE N_GEN= 9 Shouldn't need to. UPDATE [table] SET somecolumn = newval WHERE othercolumn = criterionval just changes the value of "somecolumn" and leaves everything else alone. You of course have to name the columns you're trying to change or use as selection criteria. > And Andrew can explain a bit the setval() The setval() function sets the current value of a sequence. The problem that you have is that there's no way to LOCK a sequence, so you might find that you can't do it effectively. LOCKing the calling table, if it's the only thing that calls this sequence, might help. But you could easy run into a race condition where someone inserts and gets the nextval() of 5, then you update your rows valued 4 to 2 and set the value via setval() to 4. Next, 4 gets inserted, but the _next_ operation that comes along will try to insert 5, and get an error. (Sorry if this isn't clear. You should read the manual carefully about what sequences are and are not intended to do. If I've understood your intention correctly, you're trying to get a sequence to give you a gapless range in the table. Sequences are designed with that requirement explicitly excluded, and you might be better to try another method.) A -- Andrew Sullivan | [EMAIL PROTECTED] Everything that happens in the world happens at some place. --Jane Jacobs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
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 problem?
If so here goes:
Set up a table hftest
incident serial
incdate timestamp
SELECT * from hftest;
incident | incdate
--+-
1000 | 2006-05-03 00:00:00
1001 | 2006-04-03 00:00:00
1002 | 2006-04-01 00:00:00
1003 | 2006-12-08 00:00:00
1004 | 2007-02-28 00:00:00
1005 | 2007-08-03 00:00:00
Now:
SELECT max(to_char(incdate,'Mon')) ,count(incident) from hftest WHERE
date_trunc('month',incdate) >='2006/04/01' AND
date_trunc('month',incdate)<=date_trunc('month',date '2006/04/01' +
interval '12 months') GROUP BY date_trunc('month',incdate) ORDER BY
date_trunc('month',incdate);
max | count
-+---
Apr | 2
May | 1
Dec | 1
Feb | 1
which is almost what you want. To get the missing months with
zeroes, I think you probably need a table of months and to use a left
outer join but you may have found a better way by now!
Now I have NO idea on the efficiency of this as I rather suspect
all those date_trunc functions may have an adverse effect!
Best regards
Hilary
At 16:44 03/04/2007, you wrote:
Hi
Hilary,
I am trying to produce reports where the user can select a different
fiscal year starting month. From this I would select the correct
table view to produce the reports in the correct month order by
column
Select * from table_view;
Incident
April May
June July
Aug
.
===
Falls
1
0
1
0 0
.
.
.
.
Can you think of another way to do this ?
From: Hilary Forbes
[
mailto:[EMAIL PROTECTED]]
Sent: April 3, 2007 10:14 AM
To: Wilkinson, Jim; pgsql-sql@postgresql.org
Subject: 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
At 14:04 03/04/2007, 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
Create view as select * from table_X;
I need to do something like this
Select * from (select table.start_month||_||table.end_month);
==
Start_month = april
End_month = May
What I what to pass to the select is the combination of the 2 fields as
the view name.
Any ideas ?
Hilary Forbes
DMR Limited (UK registration 01134804)
A DMR Information and Technology Group company (
www.dmr.co.uk)
Direct tel 01689 889950 Fax 01689 860330
DMR is a UK registered trade mark of DMR Limited
**
Hilary Forbes
DMR Limited (UK registration 01134804)
A DMR Information and Technology Group company
(
www.dmr.co.uk)
Direct tel 01689 889950 Fax 01689 860330
DMR is a UK registered trade mark of DMR Limited
**
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 0 0 2
Complaints.. 1 2 3 2 2
Etc ...
What I need to do is to be able to change the column heading to have a
different start and finish month
Etc ...
Incident Feb Mar Apr May June
==
Falls1 0 1 0
0
Roof Area 0 1 0 0 2
Complaints.. 1 2 3 2 2
The only way I can think of is to create 12 differents views with the
months in order and then concatenating the start_month and end_month
fields in the database to create the view name. Then do a select with
the created view name.
Select * from May_June;
.
.
.
From: Hilary Forbes [mailto:[EMAIL PROTECTED]
Sent: April 3, 2007 12:45 PM
To: Wilkinson, Jim
Cc: pgsql-sql@postgresql.org
Subject: 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 problem?
If so here goes:
Set up a table hftest
incident serial
incdate timestamp
SELECT * from hftest;
incident | incdate
--+-
1000 | 2006-05-03 00:00:00
1001 | 2006-04-03 00:00:00
1002 | 2006-04-01 00:00:00
1003 | 2006-12-08 00:00:00
1004 | 2007-02-28 00:00:00
1005 | 2007-08-03 00:00:00
Now:
SELECT max(to_char(incdate,'Mon')) ,count(incident) from hftest WHERE
date_trunc('month',incdate) >='2006/04/01' AND
date_trunc('month',incdate)<=date_trunc('month',date '2006/04/01' +
interval '12 months') GROUP BY date_trunc('month',incdate) ORDER BY
date_trunc('month',incdate);
max | count
-+---
Apr | 2
May | 1
Dec | 1
Feb | 1
which is almost what you want. To get the missing months with zeroes,
I think you probably need a table of months and to use a left outer join
but you may have found a better way by now!
Now I have NO idea on the efficiency of this as I rather suspect all
those date_trunc functions may have an adverse effect!
Best regards
Hilary
At 16:44 03/04/2007, you wrote:
Hi Hilary,
I am trying to produce reports where the user can select a different
fiscal year starting month. From this I would select the correct table
view to produce the reports in the correct month order by column
Select * from table_view;
Incident April May June July Aug
===
Falls 1 0 1 0 0
.
.
.
.
Can you think of another way to do this ?
From: Hilary Forbes [ mailto:[EMAIL PROTECTED]
]
Sent: April 3, 2007 10:14 AM
To: Wilkinson, Jim; pgsql-sql@postgresql.org
Subject: 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
At 14:04 03/04/2007, 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 ...
Create view as select * from table_X;
I need to do something like this ...
Select * from (select table.start_month||_||table.end_month);
==
Start_month = april
End_month = May
What I what to pass to the select is the combination of the 2 fields as
the view name.
Any ideas ?
Hilary Forbes
DMR Limited (UK registration 01134804)
A DMR Information and Technology Group company ( www.dmr.co.uk
[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) How will I passe the table structure as as parameter 2) Do I need to create a type? Please help me. Regards skarthi _ Take a break and play crossword puzzles - FREE! http://games.msn.com/en/flexicon/default.htm?icid=flexicon_ wlmemailtaglinemarch07
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 the table-definition and EXECUTE this string. > 2) The table has 15 columns Okay. And the problem is? > 3) It does lots of validation based on the parameter and finally > returns an integer as output parameters Okay. create function ... returns int as $$ ... return 1; end; $$ language plpgsql; > >Q) How will I passe the table structure as as parameter As i said, for instance as simple text and EXECUTE this. >2) Do I need to create a type? No. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(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: [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
return 1;
$$
language 'plpgsql';
This works fine
What I want is something like this
create or replace functinon a_func (in a%rowtype) returns int as
$$
do the validation
$$
language 'plpgsql';
execute a_func(1, 'good','04/02/2007');
> Date: Tue, 3 Apr 2007 20:18:43 +0200> From: [EMAIL PROTECTED]> To:
> pgsql-sql@postgresql.org> Subject: 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 the> table-definition
> and EXECUTE this string.> > > > 2) The table has 15 columns> > Okay. And the
> problem is?> > > > 3) It does lots of validation based on the parameter and
> finally> > returns an integer as output parameters> > Okay. create function
> ... returns int as $$ ... return 1; end; $$> language plpgsql;> > > > > > Q)
> How will I passe the table structure as as parameter> > As i said, for
> instance as simple text and EXECUTE this.> > > > 2) Do I need to create a
> type?> > No.> > > Andreas> -- > Really, I'm not out to destroy Microsoft.
> That will just be a completely> unintentional side effect. (Linus Torvalds)>
> "If I was god, I would recompile penguin with --enable-fly." (unknow)>
> Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°> >
> ---(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
_
i'm making a difference. Make every IM count for the cause of your choice. Join
Now.
http://clk.atdmt.com/MSN/go/msnnkwme008001msn/direct/01/?href=http://im.live.com/messenger/im/home/?source=wlmailtagline
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
many rows. The OS stats you describe fits that theory (but not
conclusively).
If you are using (psuedo-)code such as:
Open file {
Read line {
select from Pg: "select from [other_table] where val =
[line[colN]]"
exec to Pg: "insert into [table] (col1, col2, ...) values
(line[col1], line[col2]..."
}
}
You can radically speed up such a system by using the "copy"
(http://www.postgresql.org/docs/8.2/interactive/sql-copy.html) command
to load all the data at once from the file into Pg and then do
post-processing with Java/SQL to get all the fields looking right.
Doing a bulk update with a join across several tables is so much faster
than looping through them with a wrapper in Java (or other lang) you
won't believe it.
I hope this helps and is on-topic for you.
Steve
At 09:38 AM 4/3/2007, [EMAIL PROTECTED] wrote:
Date: Tue, 03 Apr 2007 22:16:13 +0800
From: John Summerfield <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Subject: A long-running transaction
Message-ID: <[EMAIL PROTECTED]>
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 transaction so either a single file was loaded in its entirity,
or none of its data was.
I lost the Java code, but revived the idea and I've collected about
two
years' data using (Linux) shell scripts, and loading the data using
psql.
Then, I found the Java code on a disused hard disk:-)
I made the necessary changes for it to build in java 1.5, and used
psql
to extract data from my new database in the correct format for the old
program. This time, I have a little more data than I ever loaded at
once
before:
[EMAIL PROTECTED]:~$ wc -l testdata
6242217 testdata
[EMAIL PROTECTED]:~$ \ls -hl testdata
-rw-r--r-- 1 summer summer 285M 2007-03-28 22:32 testdata
[EMAIL PROTECTED]:~$
Now, it wouldn't surprise me if postgresql used lots of memory - but
how
much could it possibly need? My laptop, where I first tried this, has
1.25 Gbytes, so I could allow it some.
It wouldn't surprise me a lot if it used lots of memory and caused all
sorts of problems, but no, it's chugging away, still using no more RAM
than it could have had on my old Pentium 133 all those years ago.
In the process of checking it out, I've set it running on a machine
with
a AMD Sempron(tm) 2400+ running Kubuntu 6.10 (kernel is
2.6.17-6-server-xen0) and 512 Mbytes of RAM.
This is the java program:-)
summer pts/6:0.0 Thu205days 1:07 1:07
/usr/bin/gij-4.1 -cp /usr/s
It's been running five days so far, and I can see where it's up to by
attaching strace. It's reading 2k of the input file every few seconds.
Okay, clearly something's wrong, and I don't think it's all my crddu
code.
No probs swapping:
[EMAIL PROTECTED]:~$ free
total used free sharedbuffers
cached
Mem:460800 456472 4328 0860
262164
-/+ buffers/cache: 193448 267352
Swap: 14618722841461588
[EMAIL PROTECTED]:~$
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 me as fairly curious that neither postgresql nor the
application was hogging the CPU.
Perhaps the laptop is more interesting: look at the size of the buffer
pool:
[EMAIL PROTECTED]:~> free
total used free sharedbuffers
cached
Mem: 12955281268548 26980 0 3976
392388
-/+ buffers/cache: 872184 423344
Swap: 1941496 326561908840
[EMAIL PROTECTED]:~>
Again, no problem with over-use of RAM, and I'm logged on using KDE
too
and that's running fine.
It's been running a little longer here:
summer pts/2328Mar07 5days 25:12 25:11 java -cp
/home/summer/Classes/:/usr/share/p
This is Sun's Java 1.5 on OpenSUSE 10.2.
This is what suggested I should write:
[EMAIL PROTECTED]:~> procinfo
Linux 2.6.18.8-0.1-default ([EMAIL PROTECTED]) (gcc 4.1.2 20061115) #1
1CPU [Echidna.]
Memory: TotalUsedFree Shared Buffers
Mem: 1295528 1271720 23808 03716
Swap: 1941496 32656 1908840
Bootup: Tue Mar 27 18:50:19 2007Load average: 2.21 2.65 2.69 2/243
19305
user : 1d 3:17:04.03 16.0% page in : 131097310 disk 1:
3079516r20087664w
nice : 0:05:39.64 0.1% page out:
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 function a_func (in p_i int, in p_j varchar, in p_k date) > returns int as > $$ > - do the validation > return 1; > $$ > language 'plpgsql'; > > This works fine > > What I want is something like this > create or replace functinon a_func (in a%rowtype) returns int as > $$ > do the validation > $$ > language 'plpgsql'; What's the reason? For an INSERT or UPDATE - check? You can use a TRIGGER and check the NEW-structure. Or create a new type, based on the table-structure. Than you can create your function. test=# create type a as ( i int, j varchar(20), k date); CREATE TYPE test=*# create function my_a (IN foo a) returns int as $$begin return 1; end; $$ language plpgsql; CREATE FUNCTION test=*# > > Date: Tue, 3 Apr 2007 20:18:43 +0200 > > From: [EMAIL PROTECTED] > > To: pgsql-sql@postgresql.org > > Subject: Re: [SQL] plpgsql function question Please, no silly text above and fullquote below, i'm reading from top to bottom... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
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 me as fairly curious that neither postgresql nor the > application was hogging the CPU. Why? Nothing about this seems likely CPU bound. It's probably I/O. I note is number: > IOwait: 2d 0:46:37.33 28.5% page dea: 16218135 which is pretty awful. Also > For each record, I update a non-key field in another table; the source > data for that other table is less than a megabyte. this is a real issue. Basically, you're constrained at the rotation speed of your disk, because for each record, you have to first find then update one row somewhere else. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
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
On Apr 3, 2007, at 2:33 PM, Karthikeyan Sundaram wrote:
What I want is something like this
create or replace functinon a_func (in a%rowtype) returns int as
$$
do the validation
$$
language 'plpgsql';
execute a_func(1, 'good','04/02/2007');
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(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
[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 audio_format.audio_codec%type,in p_mimetype audio_format.mimetype%type, in p_mono_stero audio_format.number_of_channel%type) returns int as$$DECLARE p_audio_id audio_format.audio_id%type;begin select audio_id into a from audio_format where audio_bitrate = p_bitrate and sampling_rate = p_sampling_rate and mimetype = p_mimetype and number_of_channel = p_mono_stero and audio_code = p_audio_codec; return 1; 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 PL/pgSQL function "audio_format_func" near line 15 How will I handle exceptions in postgres? Please advise. Regards skarthi _ i'm making a difference. Make every IM count for the cause of your choice. Join Now. http://clk.atdmt.com/MSN/go/msnnkwme008001msn/direct/01/?href=http://im.live.com/messenger/im/home/?source=wlmailtagline
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 PL/pgSQL function "audio_format_func" near line > 15 The constant is no_data. See http://www.postgresql.org/docs/8.2/static/errcodes-appendix.html Joe ---(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: [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_found"CONTEXT: 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 gather that you are reading 8.2 documentation and trying to apply the info to some previous version that doesn't have SELECT INTO STRICT (which you failed to use anyway...) You probably want to test the magic FOUND variable instead --- see the plpgsql docs. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
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 = 1 > j = 'God' > K = now() > > which all the three will be used to check in the function > should I say > > select a_func(1,'good',now()); Because your function expects one parameter of your new type, you have to CAST your data into this type: test=# select * from my_a((1, 'foo', current_date)::a); my_a -- 1 (1 row) [ dumb fullquote deleted ] Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(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
[SQL] Moving a simple function to pl/pgsql (Novice question)
Forgive me in advance for this terribly novice question... I have the following function which was written in MS SQL Servers trigger/rule language... CREATE TRIGGER [Sync_Deals] ON [dbo].[Deals] FOR INSERT, UPDATE AS begin declare @Found int declare @deal varchar(10) select @deal = deal_address from inserted Select @Found = count(*) from dealbook.dbo.deals where deal_address = @deal if @Found > 0 delete from dealbook.dbo.deals where deal_address = @deal insert into dealbook.dbo.deals select * from inserted end The purpose being when a row in a table in one database is updated, it will copy (or replicate I guess) the record into a different table into another database in the same server. (deleting said record first if it already exists) What is the best way to do this within Postgres? I assume a trigger is the way to go here as well, but having not written a trigger in PG I'm a little stuck as to where to start. My client goes live with their new system on Tuesday and this function forms part of some custom additions they wrote on their previous SQL server database. I'd thus like to make sure I know what I'm doing to finalise the conversion before the weekend. It seems to me I need to create a function to do the copy, and then create a trigger to call the function - but I'm not entirely sure - you can probably tell I haven't done anything with triggers, functions or rules yet. :-) (BTW: No I am not trying to get someone to do my work for me :-P - I have about 30ish triggers of various nature to convert, I just need somewhere to start so I can figure out how it's done then I can do the rest myself) TIA, P. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
