Thread: VACUUM problems

hello, All !

I'm freshman to this list.

Before version 7.1 there was restriction on SQL tuple length 8-32Kb.
Now I'm using 7.1.2 where iz no such limitations. I have created
some documents database. Theare is table with full text of
documents. Some rows are up to 400Kb lenght. The database is
working just fine, but vacuum crashes all the time after procesing
that full text table. When I deleted all the records from that full text
table, vacuum did correctly.
Right now I'm generating the same table but the text is split into
multiple records each ~8K. I think - it will work.

Maybe that's a bug in vacuum or maybe some misconfiguration (I'm
using default config).
If anyone had similar problems, pls share your ideas and
comments about solution.


Mark

"Martins Zarins" <mark@vestnesis.lv> writes:
> Some rows are up to 400Kb lenght. The database is
> working just fine, but vacuum crashes all the time after procesing
> that full text table.

You're going to have to provide more information than that.  What
happens *exactly*?  What shows up in the postmaster's stderr log?
Is there a core file, and if so can you get a backtrace from it?

            regards, tom lane

Hi All, I have two questions

1- I have a query but this is very slow, I need to acelerate the
consult, this result sill be show in the web.

My query is:

select user, descripcion from client where login not in (select
distinct(usr_cliente) from conexion where fecha between '$date_begin' and
'$date_end') order by user"

How can I to acelerate this consult?

2- I made this function:

CREATE FUNCTION list(date,date) RETURNS user
     AS 'select distinct(usr_cliente) from conexion where fecha between
        $date_begin and $date_end' LANGUAGE 'sql';

When I execute a query to this function like:
select select listado('08-10-2001', '08-13-2001') as user;

the result is just 1 row, however If I to execute the query this return
300 rows, why the function only return one row?

Thanks in advanced,

Regards,

Vida Luz Arista a écrit :

> Hi All, I have two questions
>
> 1- I have a query but this is very slow, I need to acelerate the
> consult, this result sill be show in the web.
>
> My query is:
>
> select user, descripcion from client where login not in (select
> distinct(usr_cliente) from conexion where fecha between '$date_begin' and
> '$date_end') order by user"
>
> How can I to acelerate this consult?
>
> 2- I made this function:
>
> CREATE FUNCTION list(date,date) RETURNS user
>      AS 'select distinct(usr_cliente) from conexion where fecha between
>         $date_begin and $date_end' LANGUAGE 'sql';
>
> When I execute a query to this function like:
> select select list('08-10-2001', '08-13-2001') as user;
>
> the result is just 1 row, however If I to execute the query this return
> 300 rows, why the function only return one row?
>

The query's returning the exact opposite of your function, so if you have
301 rows in your table and the function returns you 1, the query'll get 300
:)
select user, descripcion from client where login not in (select
distinct(usr_cliente) from conexion where fecha between '$date_begin' and
'$date_end') order by user"
is equivalent to:
select user, descripcion from client where login not in (select
list('$date_begin', '$date_end'))

PS: a misused "not in" clause can result in very poor performances (it
depends actually of your table contents) : if "everything that is not ..."
is huge, it's quicker to get "everything that is ..." :)

>
> Thanks in advanced,
>
> Regards,
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

On Fri, 24 Aug 2001, Vida Luz Arista wrote:

> Hi All, I have two questions
>
> 1- I have a query but this is very slow, I need to acelerate the
> consult, this result sill be show in the web.
>
> My query is:
>
> select user, descripcion from client where login not in (select
> distinct(usr_cliente) from conexion where fecha between '$date_begin' and
> '$date_end') order by user"

See FAQ entry 4.23.  Short form is convert the query to use EXISTS rather
than IN.


> 2- I made this function:
>
> CREATE FUNCTION list(date,date) RETURNS user
>      AS 'select distinct(usr_cliente) from conexion where fecha between
>         $date_begin and $date_end' LANGUAGE 'sql';
>
> When I execute a query to this function like:
> select select listado('08-10-2001', '08-13-2001') as user;
>
> the result is just 1 row, however If I to execute the query this return
> 300 rows, why the function only return one row?

I think you'd need to say setof user as the return type, although I'm not
100% sure that'll give you back something meaningful (It gives me
something meaningful on my 7.2 devel machine, but I don't have an earlier
system to test on)