Insert and limit
<html><DIV>
<DIV>Hi all,</DIV>
<DIV> is possible that the limit statement dosen't run correctly in this case:</DIV>
<DIV> </DIV>
<DIV>Insert into temp</DIV>
<DIV>Select * from tab1</DIV>
<DIV>Where cod = xxxx</DIV>
<DIV>Limit yyy;</DIV>
<DIV> </DIV>
<DIV>Thanks in advance !</DIV><BR clear=all></DIV><br clear=all><hr>Get Your Private, Free E-mail from MSN Hotmail at <a href="http://www.hotmail.com">http://www.hotmail.com</a>.<br></p></html>
From: "Matteo Centenaro" <gente_che_mixa@hotmail.com>
Hi all,
is possible that the limit statement dosen't run correctly in this case:Insert into temp
Select * from tab1
Where cod = xxxx
Limit yyy;
Works here.
richardh=> select * from foo;
a | b
---+----------------------------------
4 | Jack Smith
5 | Andrea Ottwick
(2 rows)
richardh=> insert into foo2 select * from foo limit 1;
INSERT 20788 1
richardh=> select * from foo2;
a | b
---+----------------------------------
4 | Jack Smith
(1 row)
version 7.1.1
- Richard Huxton
From: "Matteo Centenaro" <gente_che_mixa@hotmail.com>
Hi all,
is possible that the limit statement dosen't run correctly in this case:Insert into temp
Select * from tab1
Where cod = xxxx
Limit yyy;
[follow-up message said Matteo was interested in plpgsql]
Still seems OK.
richardh=> create function copy_foo() returns int as '
richardh'> begin
richardh'> insert into foo2 select * from foo limit 1;
richardh'> return 1;
richardh'> end;' language 'plpgsql';
CREATE
richardh=> select copy_foo();
copy_foo
----------
1
(1 row)
richardh=> select * from foo2;
a | b
---+----------------------------------
4 | Jack Smith
(1 row)
You weren't using SELECT INTO were you - there is a different form for
plpgsql.
- Richard Huxton
"Richard Huxton" <dev@archonet.com> writes:
[follow-up message said Matteo was interested in plpgsql]
Still seems OK.
I think a more relevant question is "what version are you running?"
IIRC, LIMIT only works in a top-level SELECT before 7.1.
regards, tom lane
<html><DIV>
<P>I'm running 7.1.1</P>
<P>I create the Pl/Pgsql function using Pgaccess the code is like this:<BR>Insert into tab1 Select * from tab2 where id = xxxx limit var_name;</P></DIV>
<DIV></DIV>
<DIV></DIV>>From: Tom Lane <TGL@SSS.PGH.PA.US>
<DIV></DIV>>To: "Richard Huxton" <DEV@ARCHONET.COM>
<DIV></DIV>>CC: "Matteo Centenaro" <GENTE_CHE_MIXA@HOTMAIL.COM>, pgsql-general@postgresql.org
<DIV></DIV>>Subject: Re: [GENERAL] Insert and limit
<DIV></DIV>>Date: Fri, 15 Jun 2001 10:42:11 -0400
<DIV></DIV>>
<DIV></DIV>>"Richard Huxton" <DEV@ARCHONET.COM>writes:
<DIV></DIV>> > [follow-up message said Matteo was interested in plpgsql]
<DIV></DIV>> > Still seems OK.
<DIV></DIV>>
<DIV></DIV>>I think a more relevant question is "what version are you running?"
<DIV></DIV>>
<DIV></DIV>>IIRC, LIMIT only works in a top-level SELECT before 7.1.
<DIV></DIV>>
<DIV></DIV>> regards, tom lane
<DIV></DIV>>
<DIV></DIV>>---------------------------(end of broadcast)---------------------------
<DIV></DIV>>TIP 3: if posting/reading through Usenet, please send an appropriate
<DIV></DIV>>subscribe-nomail command to majordomo@postgresql.org so that your
<DIV></DIV>>message can get through to the mailing list cleanly
<DIV></DIV><br clear=all><hr>Get Your Private, Free E-mail from MSN Hotmail at <a href="http://www.hotmail.com">http://www.hotmail.com</a>.<br></p></html>
Import Notes
Resolved by subject fallback
Matteo Centenaro wrote:
I'm running 7.1.1
I create the Pl/Pgsql function using Pgaccess the code is like this:
Insert into tab1 Select * from tab2 where id = xxxx limit var_name;
Try something like:
select prolang,prosrc from pg_proc where proname='copy_foo';
from psql - I'd guess there's a glitch in the code pgaccess saved.
- Richard Huxton
I try to execute the select and all seem fine! The limit statement appear in
the right position.The prolang field is set to 18759392.
Have any other idea? Thanks
From: Richard Huxton <dev@archonet.com>
To: Matteo Centenaro <gente_che_mixa@hotmail.com>
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Insert and limit
Date: Sat, 16 Jun 2001 08:12:21 +0100Matteo Centenaro wrote:
I'm running 7.1.1
I create the Pl/Pgsql function using Pgaccess the code is like this:
Insert into tab1 Select * from tab2 where id = xxxx limit var_name;Try something like:
select prolang,prosrc from pg_proc where proname='copy_foo';
from psql - I'd guess there's a glitch in the code pgaccess saved.
- Richard Huxton
_________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
Import Notes
Resolved by subject fallback
Hi!
I seem to be having a problem. I have a database that is around 10 GB
in size. I have it running on a 40 Gb disk. I tried to dump and
restore two of the tables, around 4 GB in size. All went find, the
file approximately 5 GB in size was created. No problem. I wanted to
import the data into another database.
However, when I try to import the database, the disk fills up with
about 20 GB of files in the pg_xlog directory, and the postmaster
crashes out. The only way to get anything to run then is to kill those
files, after which postgres refuses to start. the only way to get it
to work again is to delte the files in /var/lib/pgsql/data and re-run
initdb.
Unfortunately, when I try to restore the backed up data, the same
thing happens.
Can somebody please explain to me why is it that 20 Gb of disk space
is required to restore 5 GB of data? I am guessing that WAL is
creating those files, but with the 64 file limit in the config file,
and each file being 16 MB, that should only ever be up to 1 GB - NOT
20 GB!
What is going on? I have had this same problem with v7.0, and now with
v7.1. Is there an obscure section in the docs that I have missed?
Cheers.
Gordan
"Gordan Bobic" <gordan@freeuk.com> writes:
Can somebody please explain to me why is it that 20 Gb of disk space
is required to restore 5 GB of data?
You may wish to apply the patch at
http://www.ca.postgresql.org/mhonarc/pgsql-patches/2001-06/msg00061.html
What is going on? I have had this same problem with v7.0, and now with
v7.1.
Rather hard to believe, since there was no WAL in 7.0.
regards, tom lane
Can somebody please explain to me why is it that 20 Gb of disk
space
is required to restore 5 GB of data?
You may wish to apply the patch at
http://www.ca.postgresql.org/mhonarc/pgsql-patches/2001-06/msg00061.ht
ml
Ah, excellent. I will try that as soon as my current restore attempt
finishes/breaks.
What is going on? I have had this same problem with v7.0, and now
with
v7.1.
Rather hard to believe, since there was no WAL in 7.0.
Sorry, I meant in upgrading from 7.0 to 7.1.
Thanks.
Gordan