Thread: need urgent help

need urgent help

From

Jie Liang

Date:

17 June 2001, 14:08:15

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

Re: [ADMIN] need urgent help

From

Tom Lane

Date:

17 June 2001, 15:35:47

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

Re: Re: [ADMIN] need urgent help

From

Jie Liang

Date:

17 June 2001, 16:00:45

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)
>

Re: Re: [ADMIN] need urgent help

From

Tom Lane

Date:

17 June 2001, 16:35:28

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

Re: Re: [ADMIN] need urgent help

From

Jie Liang

Date:

17 June 2001, 18:13:53

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)
>

Re: Re: [ADMIN] need urgent help

From

Tom Lane

Date:

17 June 2001, 18:20:27

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

Re: Re: [ADMIN] need urgent help

From

Jie Liang

Date:

17 June 2001, 19:07:10

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
>

log file

From

Jie Liang

Date:

20 June 2001, 00:44:30

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

pg_xlog

From

Jie Liang

Date:

20 June 2001, 01:06:02

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

pg_xlogs

From

Jie Liang

Date:

20 June 2001, 01:42:37

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

Re: log file

From

"Richard Huxton"

Date:

20 June 2001, 06:03:17

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

Re: log file

From

"Josh Berkus"

Date:

20 June 2001, 11:15:27

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