Thread: Insert and limit
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; Thanks in advance !Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
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
<div><p>I'm running 7.1.1<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;</div><div></div><div></div>>From: Tom Lane <div></div>>To: "RichardHuxton" <div></div>>CC: "Matteo Centenaro" , 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"writes: <div></div>> > [follow-up message said Matteo was interested in plpgsql] <div></div>> > Stillseems 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 getthrough to the mailing list cleanly <div></div><br clear="all" /><hr />Get Your Private, Free E-mail from MSN Hotmailat <a href="http://www.hotmail.com">http://www.hotmail.com</a>.<br />
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 +0100 > >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 _________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
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