pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedneed urgent help
14+ messages / 9 participants
[nested] [flat]
* need urgent help
@ 2001-06-17 18:08 Jie Liang <jliang@ipinc.com>
0 siblings, 1 reply; 14+ messages in thread
From: Jie Liang @ 2001-06-17 18:08 UTC (permalink / raw)
To: pgsql-sql; +Cc: pgsql-admin@postgresql.org
when I reload my database from pg_dumpall:
COPY "urlinfo" FROM stdin;
ERROR: copy: line 11419, MemoryContextAlloc: invalid request size
4294967271
PQendcopy: resetting connection
this table is very big.
how could I resolve this??
thanks.
Jie LIANG
St. Bernard Software
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
jliang@ipinc.com
www.stbernard.com
www.ipinc.com
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: need urgent help
@ 2001-06-17 19:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jie Liang <jliang@ipinc.com>
0 siblings, 1 reply; 14+ messages in thread
From: Tom Lane @ 2001-06-17 19:12 UTC (permalink / raw)
To: Jie Liang <jliang@ipinc.com>; +Cc: pgsql-sql; pgsql-admin@postgresql.org
Jie Liang <jliang@ipinc.com> writes:
> when I reload my database from pg_dumpall:
> COPY "urlinfo" FROM stdin;
> ERROR: copy: line 11419, MemoryContextAlloc: invalid request size
> 4294967271
> PQendcopy: resetting connection
Postgres version? Table schema? Input data?
regards, tom lane
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [SQL] Re: need urgent help
@ 2001-06-17 20:00 Jie Liang <jliang@ipinc.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 14+ messages in thread
From: Jie Liang @ 2001-06-17 20:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql; pgsql-admin@postgresql.org
Tom,
We are fixing it now.
postgres-7.1.2
urldb=# \d urlinfo
Table "urlinfo"
Attribute | Type | Modifier
-----------+--------------------------+----------------------------------
url | text | not null
id | integer | not null
ratedby | character varying(32) |
ratedon | timestamp with time zone | default "timestamp"('now'::text)
comments | text |
list | smallint |
pidwsr | integer |
Index: urlinfo_pkey
the original schema includes another constraint which using a user defined
function to check whether url is validate or not.
we fixed input data at line 11419.
thanks.
Jie LIANG
St. Bernard Software
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
jliang@ipinc.com
www.stbernard.com
www.ipinc.com
On Sun, 17 Jun 2001, Tom Lane wrote:
> Jie Liang <jliang@ipinc.com> writes:
> > when I reload my database from pg_dumpall:
> > COPY "urlinfo" FROM stdin;
> > ERROR: copy: line 11419, MemoryContextAlloc: invalid request size
> > 4294967271
> > PQendcopy: resetting connection
>
> Postgres version? Table schema? Input data?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [SQL] Re: need urgent help
@ 2001-06-17 20:02 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jie Liang <jliang@ipinc.com>
0 siblings, 1 reply; 14+ messages in thread
From: Tom Lane @ 2001-06-17 20:02 UTC (permalink / raw)
To: Jie Liang <jliang@ipinc.com>; +Cc: pgsql-sql; pgsql-admin@postgresql.org
Jie Liang <jliang@ipinc.com> writes:
> the original schema includes another constraint which using a user defined
> function to check whether url is validate or not.
So are you saying it was just a bug in this user-defined function?
Or is there something we need to investigate?
regards, tom lane
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [SQL] Re: need urgent help
@ 2001-06-17 22:13 Jie Liang <jliang@ipinc.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 14+ messages in thread
From: Jie Liang @ 2001-06-17 22:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql; pgsql-admin@postgresql.org
Tom,
Might be.
It seems that something wrong in our input data at centain line.
I don't know how it happened.
Because I have to turn our database on now, so I might check it out later.
The whole thing is :
we pg_dumpall(v7.0) from our database.
then we reload with
gunzip -c dump.gz|psql templete1 after we upgraded to postgresql-7.1.2
just that table failed, however some use defined function failed
when we use them, so we re_create every function(plpgsql,plperl)
with a script. then seems ok now.
HOWEVER, we seems to have a new problem:
I feel it slower than before, I might missed something
urldb=# \d urlinfo
Table "urlinfo"
Attribute | Type | Modifier
-----------+--------------------------+----------------------------------
url | text | not null
id | integer | not null
ratedby | character varying(32) |
ratedon | timestamp with time zone | default "timestamp"('now'::text)
comments | text |
list | smallint |
pidwsr | integer |
Indices: urlinfo_pkey,
urlinfo_ukey
urldb=# select count(*) from urlinfo;
count
---------
1298542
I vacuumed urlinfo already.
urlinfo_pkey is primary key (id), urlinfo_ukey is unique key (url)
when I:
SELECT id FROM urlinfo WHERE url='http://*.yahoo.com';;
It seems taking me longer than before.
Jie LIANG
St. Bernard Software
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
jliang@ipinc.com
www.stbernard.com
www.ipinc.com
On Sun, 17 Jun 2001, Tom Lane wrote:
> Jie Liang <jliang@ipinc.com> writes:
> > the original schema includes another constraint which using a user defined
> > function to check whether url is validate or not.
>
> So are you saying it was just a bug in this user-defined function?
> Or is there something we need to investigate?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [SQL] Re: need urgent help
@ 2001-06-17 22:20 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jie Liang <jliang@ipinc.com>
0 siblings, 1 reply; 14+ messages in thread
From: Tom Lane @ 2001-06-17 22:20 UTC (permalink / raw)
To: Jie Liang <jliang@ipinc.com>; +Cc: pgsql-sql; pgsql-admin@postgresql.org
Jie Liang <jliang@ipinc.com> writes:
> I vacuumed urlinfo already.
VACUUM ANALYZE, or just VACUUM?
> urlinfo_pkey is primary key (id), urlinfo_ukey is unique key (url)
> when I:
> SELECT id FROM urlinfo WHERE url='http://*.yahoo.com';;
> It seems taking me longer than before.
What does EXPLAIN say about it?
regards, tom lane
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [SQL] Re: need urgent help
@ 2001-06-17 22:39 Jie Liang <jliang@ipinc.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 14+ messages in thread
From: Jie Liang @ 2001-06-17 22:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql; pgsql-admin@postgresql.org
Tom,
It comes back very quick now, there might some backend haven't come back
before.
thanks a lot.
Jie LIANG
St. Bernard Software
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
jliang@ipinc.com
www.stbernard.com
www.ipinc.com
On Sun, 17 Jun 2001, Tom Lane wrote:
> Jie Liang <jliang@ipinc.com> writes:
> > I vacuumed urlinfo already.
>
> VACUUM ANALYZE, or just VACUUM?
>
> > urlinfo_pkey is primary key (id), urlinfo_ukey is unique key (url)
> > when I:
> > SELECT id FROM urlinfo WHERE url='http://*.yahoo.com';;
> > It seems taking me longer than before.
>
> What does EXPLAIN say about it?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* NEED URGENT HELP....
@ 2006-12-21 07:41 Sandip G <sandip@singapore.com>
0 siblings, 2 replies; 14+ messages in thread
From: Sandip G @ 2006-12-21 07:41 UTC (permalink / raw)
To: pgsql-bugs@postgresql.org; +Cc: pgsql-sql; pgsql-general@postgresql.org
I am using PostgreSql 8.1 with pgAdmin III. OS is XP. this is my
function:
CREATE OR REPLACE FUNCTION sp_get_phase(character varying, character
varying, character varying)
RETURNS ret_dv_sp_get_phase AS
$BODY$
SELECT BOOK_NO, USER_ID, COMPANY_ID, PHASE, UPDATE_USER_ID,
UPDATE_DATE,
AddInfo1, AddInfo2
FROM T_PHASE
WHERE (USER_ID = $1) AND (COMPANY_ID = $2) AND BOOK_NO IN ($3)
$BODY$
LANGUAGE 'sql' VOLATILE;
When I run
select * from sp_get_phase ('sandip', 'oms', '4') returns 1
record.....this works fine....
select * from sp_get_phase ('sandip', 'oms', '1') returns 1
record.....this also works fine... BUT
select * from sp_get_phase ('sandip', 'oms', '1,4') this return a
Blank record.
I tried to execute the SQL statement from the function
SELECT BOOK_NO, USER_ID, COMPANY_ID, PHASE, UPDATE_USER_ID,
UPDATE_DATE,
AddInfo1, AddInfo2
FROM T_PHASE
WHERE (USER_ID = 'testUser') AND (COMPANY_ID = 'testCompany') AND
BOOK_NO IN (1,4)
----- This Works fine... returns 2 records. What may be the problem?
Thanks in advance.
Regards,
Sandip.
--
___________________________________________________
Search for products and services at:
http://search.mail.com
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [GENERAL] NEED URGENT HELP....
@ 2006-12-25 17:32 Harpreet Dhaliwal <harpreet.dhaliwal01@gmail.com>
parent: Sandip G <sandip@singapore.com>
1 sibling, 0 replies; 14+ messages in thread
From: Harpreet Dhaliwal @ 2006-12-25 17:32 UTC (permalink / raw)
To: Sandip G <sandip@singapore.com>; +Cc: pgsql-bugs@postgresql.org; pgsql-sql; pgsql-general@postgresql.org
select * from sp_get_phase ('sandip', 'oms', '1,4') this return a Blank
record
it would match ur query against '1,4' for the corressponding field in the
table.
do u really have one such value for that field in your table, i mean '1,4'
??
it won't search for 1 and 4 separately if that is what you want your query
to work.
~Harpreet
On 12/21/06, Sandip G <sandip@singapore.com> wrote:
>
> I am using PostgreSql 8.1 with pgAdmin III. OS is XP.
>
> this is my function:
>
> CREATE OR REPLACE FUNCTION sp_get_phase(character varying, character
> varying, character varying)
> RETURNS ret_dv_sp_get_phase AS
> $BODY$
> SELECT BOOK_NO, USER_ID, COMPANY_ID, PHASE, UPDATE_USER_ID,
> UPDATE_DATE,
> AddInfo1, AddInfo2
> FROM T_PHASE
> WHERE (USER_ID = $1) AND (COMPANY_ID = $2) AND BOOK_NO IN ($3)
> $BODY$
> LANGUAGE 'sql' VOLATILE;
>
>
> When I run
> select * from sp_get_phase ('sandip', 'oms', '4') returns 1
> record.....this works fine....
>
> select * from sp_get_phase ('sandip', 'oms', '1') returns 1
> record.....this also works fine... BUT
>
> select * from sp_get_phase ('sandip', 'oms', '1,4') this return a Blank
> record.
>
> I tried to execute the SQL statement from the function
>
> SELECT BOOK_NO, USER_ID, COMPANY_ID, PHASE, UPDATE_USER_ID,
> UPDATE_DATE,
> AddInfo1, AddInfo2
> FROM T_PHASE
> WHERE (USER_ID = 'testUser') AND (COMPANY_ID = 'testCompany')
> AND BOOK_NO IN (1,4)
>
> ----- This Works fine... returns 2 records. What may be the problem?
>
> Thanks in advance.
> Regards,
> Sandip.
>
>
>
> -- <http://a8-asy.a8ww.net/a8-ads/adftrclick?redirectid=en-mail_a_01;
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: NEED URGENT HELP....
@ 2006-12-25 23:45 Guy Rouillier <guyr-ml1@burntmail.com>
parent: Sandip G <sandip@singapore.com>
1 sibling, 1 reply; 14+ messages in thread
From: Guy Rouillier @ 2006-12-25 23:45 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Sandip G wrote:
> WHERE (USER_ID = $1) AND (COMPANY_ID = $2) AND BOOK_NO IN ($3)
> select * from sp_get_phase ('sandip', 'oms', '1,4') this return a
> Blank record.
$3 is a parameter marker for a single value. You cannot supply a
comma-separated list of values and expect it to operate on them. As
Harpreet points out, it is interpreting your comma-separated list as a
single value. If you know you will always have (e.g.) two values you
want to pass, you could code your function with "in ($3, $4)", but if
you want a generalized, variable-length list of values in your IN
clause, you'd need to use dynamic SQL.
--
Guy Rouillier
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: NEED URGENT HELP....
@ 2006-12-25 23:52 Henrique P Machado <zehrique@gmail.com>
parent: Guy Rouillier <guyr-ml1@burntmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Henrique P Machado @ 2006-12-25 23:52 UTC (permalink / raw)
To: Guy Rouillier <guyr-ml1@burntmail.com>; +Cc: pgsql-general@postgresql.org
WHERE (USER_ID = $1) AND (COMPANY_ID = $2) AND BOOK_NO IN ($3)
Could'nt he use an array in this 3rd parameter?
--
ZehRique
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: NEED URGENT HELP....
@ 2006-12-26 11:53 Martijn van Oosterhout <kleptog@svana.org>
parent: Henrique P Machado <zehrique@gmail.com>
0 siblings, 2 replies; 14+ messages in thread
From: Martijn van Oosterhout @ 2006-12-26 11:53 UTC (permalink / raw)
To: Henrique P Machado <zehrique@gmail.com>; +Cc: Guy Rouillier <guyr-ml1@burntmail.com>; pgsql-general@postgresql.org
On Mon, Dec 25, 2006 at 08:52:52PM -0300, Henrique P Machado wrote:
> WHERE (USER_ID = $1) AND (COMPANY_ID = $2) AND BOOK_NO IN ($3)
>
> Could'nt he use an array in this 3rd parameter?
I think so, if it's written:
AND BOOK_NO = ANY($3)
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: NEED URGENT HELP....
@ 2006-12-26 15:09 Gurjeet Singh <singh.gurjeet@gmail.com>
parent: Martijn van Oosterhout <kleptog@svana.org>
1 sibling, 0 replies; 14+ messages in thread
From: Gurjeet Singh @ 2006-12-26 15:09 UTC (permalink / raw)
To: Martijn van Oosterhout <kleptog@svana.org>; Henrique P Machado <zehrique@gmail.com>; Guy Rouillier <guyr-ml1@burntmail.com>; pgsql-general@postgresql.org, "Sandip G" <sandip@singapore.com>
It works Martijn...
but with a few changes will be required in your function Sandip; you will
have to pass an ARRAY constructor and return a SETOF record. Here's a
sample:
postgres=> create table tab ( a int, b int );
CREATE TABLE
postgres=> insert into tab values ( 1, 9 );
INSERT 0 1
postgres=> insert into tab values (2,8);
INSERT 0 1
postgres=> insert into tab values (3,7);
INSERT 0 1
postgres=> insert into tab values (4,6);
INSERT 0 1
postgres=> insert into tab values (5,5);
INSERT 0 1
postgres=> create or replace function fun ( character varying [] ) returns
setof
tab as
postgres-> $$
postgres$> select * from tab where a = any ($1)
postgres$> $$ language 'sql' volatile;
CREATE FUNCTION
postgres=> select fun('{1}');
fun
-------
(1,9)
(1 row)
postgres=> select fun('{2,3}');
fun
-------
(2,8)
(3,7)
(2 rows)
postgres=>
Hope it helps....
On 12/26/06, Martijn van Oosterhout <kleptog@svana.org> wrote:
>
> On Mon, Dec 25, 2006 at 08:52:52PM -0300, Henrique P Machado wrote:
> > WHERE (USER_ID = $1) AND (COMPANY_ID = $2) AND BOOK_NO IN ($3)
> >
> > Could'nt he use an array in this 3rd parameter?
>
> I think so, if it's written:
>
> AND BOOK_NO = ANY($3)
>
> Have a nice day,
> --
> Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> > From each according to his ability. To each according to his ability to
> litigate.
>
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.1 (GNU/Linux)
>
> iD8DBQFFkQ1LIB7bNG8LQkwRApbCAJsH26IcDusO5Vi5kNC1UQ185usbnACeOxdC
> xQo+z5Z7+Xofks/h3MmeF7w=
> =Rq6g
> -----END PGP SIGNATURE-----
>
>
>
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: NEED URGENT HELP....
@ 2006-12-27 00:28 Iannsp <iannsp@gmail.com>
parent: Martijn van Oosterhout <kleptog@svana.org>
1 sibling, 0 replies; 14+ messages in thread
From: Iannsp @ 2006-12-27 00:28 UTC (permalink / raw)
To: Henrique P Machado <zehrique@gmail.com>; Guy Rouillier <guyr-ml1@burntmail.com>; pgsql-general@postgresql.org
Martijn van Oosterhout escreveu:
> On Mon, Dec 25, 2006 at 08:52:52PM -0300, Henrique P Machado wrote:
>
>> WHERE (USER_ID = $1) AND (COMPANY_ID = $2) AND BOOK_NO IN ($3)
>>
>> Could'nt he use an array in this 3rd parameter?
>>
>
> I think so, if it's written:
>
> AND BOOK_NO = ANY($3)
>
> Have a nice day,
>
I believe not because the aray have one another sintax, different of on
simple data, but if you create one stored procedure you will be
transform the data...
If you sayd array to the data like '1,2,3' is another case, and I think
you dont have problemns with this.
ok.
--
Ivo Nascimento
Iann tech - Desenvolvendo soluções com performance e segurança
http://www.ianntech.com.br
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2006-12-27 00:28 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-17 18:08 need urgent help Jie Liang <jliang@ipinc.com>
2001-06-17 19:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-06-17 20:00 ` Jie Liang <jliang@ipinc.com>
2001-06-17 20:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-06-17 22:13 ` Jie Liang <jliang@ipinc.com>
2001-06-17 22:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-06-17 22:39 ` Jie Liang <jliang@ipinc.com>
2006-12-21 07:41 NEED URGENT HELP.... Sandip G <sandip@singapore.com>
2006-12-25 17:32 ` Re: [GENERAL] NEED URGENT HELP.... Harpreet Dhaliwal <harpreet.dhaliwal01@gmail.com>
2006-12-25 23:45 ` Re: NEED URGENT HELP.... Guy Rouillier <guyr-ml1@burntmail.com>
2006-12-25 23:52 ` Re: NEED URGENT HELP.... Henrique P Machado <zehrique@gmail.com>
2006-12-26 11:53 ` Re: NEED URGENT HELP.... Martijn van Oosterhout <kleptog@svana.org>
2006-12-26 15:09 ` Re: NEED URGENT HELP.... Gurjeet Singh <singh.gurjeet@gmail.com>
2006-12-27 00:28 ` Re: NEED URGENT HELP.... Iannsp <iannsp@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox