Обсуждение: need urgent help
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
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
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)
>
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
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)
>
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
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 >
Tom, We turn on the log file by: In file 'postgresql.conf' syslog = 2 # range 0-2 syslog_facility = 'LOCAL0' syslog_ident = 'postgres' 1. It seems that postmaster writes stuff undef /db/pgsql/pg_xlog (they are not text files). 2. the log file I expected (I want log include SQL statement and ERROR) Just have NOTICE and DEBUG info. How to choose these options?? 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
Tom, Since our disk is filled full so fast, Does logs under pg_xlog can be deleted and how to turn off it?? 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
Tom, There are a lot of pg_sorttemp???.???, which full our disk so fast, how to enforce them in a certain size to prevent disk full. 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
From: "Jie Liang" <jliang@ipinc.com> > Tom, > > We turn on the log file by: > In file 'postgresql.conf' > syslog = 2 # range 0-2 > syslog_facility = 'LOCAL0' > syslog_ident = 'postgres' > > 1. It seems that postmaster writes stuff undef /db/pgsql/pg_xlog > (they are not text files). pg_xlog stuff is WAL related (Ch 9 Administrators guide) > 2. the log file I expected (I want log include SQL statement and ERROR) > Just have NOTICE and DEBUG info. I *think* you want DEBUG_PRINT_QUERY and its friends. See Ch 3.4.2 of the Administrators Guide. HTH - Richard Huxton
Richard, Jiang,
> I *think* you want DEBUG_PRINT_QUERY and its friends. See Ch 3.4.2 of
> the
> Administrators Guide.
Actually, Jiang need to re-start the postmaster with the command-line
options for logging set. Annoyingly, the default init.d script for
Postgres in most RPMs starts Postgres without any logging.
Unfortunately, I'm at a remote terminal right now, so I can't post an
example.
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco