2005/8/30, Jim C. Nasby <[EMAIL PROTECTED]>:
>
> FWIW, that where clause might be more efficient as
> WHERE pontos_0 > pontos_7. Some databases would be able to use indexes
> to answer that (not sure if PostgreSQL could), plus it removes an
> operator. It also seems to be cleaner code to me. :)
>
On Tue, Aug 30, 2005 at 08:13:15AM -0300, Clodoaldo Pinto wrote:
> 2005/8/29, Michael Fuhr <[EMAIL PROTECTED]>:
> >
> > In general, writers shouldn't block readers. Have you examined
> > pg_locks? Do you know exactly what the blocked queries are, or can
> > you find out from pg_stat_activity (st
30 Aug 2005 10:35:31 -0400, Greg Stark <[EMAIL PROTECTED]>:
>
> Well a regular vacuum will mark the free space for reuse. If you insert or
> update any records the new ones will go into those spots. Make sure you set
> the fsm_* parameters high enough to cover all the updates and inserts for the
>
2005/8/30, Alvaro Herrera <[EMAIL PROTECTED]>:
> On Tue, Aug 30, 2005 at 10:18:20AM -0300, Clodoaldo Pinto wrote:
> > A vacuum full or a cluster is totally out of reach since each take
> > about one hour.
>
> Even if you cluster/vacuum only the just-loaded table?
>
No, that would be much faster
Clodoaldo Pinto <[EMAIL PROTECTED]> writes:
> I'm already doing a vacuum (not full) once a day.
>
> A vacuum full or a cluster is totally out of reach since each take
> about one hour. The biggest table is 170 million rows long.
Well a regular vacuum will mark the free space for reuse. If you i
On Tue, Aug 30, 2005 at 10:18:20AM -0300, Clodoaldo Pinto wrote:
> 30 Aug 2005 09:10:51 -0400, Greg Stark <[EMAIL PROTECTED]>:
> > Also, if consider doing a "vacuum full" or "cluster" after the batch job to
> > clear up the free space (not in a large transaction). That will still take a
> > table
30 Aug 2005 09:10:51 -0400, Greg Stark <[EMAIL PROTECTED]>:
>
> I think truncate takes a table lock.
> Just change it to "delete from times_producao".
Thanks, i will try it.
>
> Also, if consider doing a "vacuum full" or "cluster" after the batch job to
> clear up the free space (not in a large
Clodoaldo Pinto <[EMAIL PROTECTED]> writes:
> 2005/8/29, Tom Lane <[EMAIL PROTECTED]>:
> >
> > What is the function doing to the table, exactly? DDL changes generally
> > take exclusive locks ...
>
> This is the transaction:
>
> begin;
> select update_last_date();
> truncate times_producao;
I
2005/8/30, Michael Fuhr <[EMAIL PROTECTED]>:
>
> TRUNCATE acquires an AccessExclusiveLock, which conflicts with all
> other lock types. Locks are held until the transaction completes,
> so once this lock is acquired no other transactions will be able
> to access the table until this transaction c
On Tue, Aug 30, 2005 at 08:39:52AM -0300, Clodoaldo Pinto wrote:
>
> begin;
> select update_last_date();
> truncate times_producao;
TRUNCATE acquires an AccessExclusiveLock, which conflicts with all
other lock types. Locks are held until the transaction completes,
so once this lock is acquired n
2005/8/29, Tom Lane <[EMAIL PROTECTED]>:
>
> What is the function doing to the table, exactly? DDL changes generally
> take exclusive locks ...
This is the transaction:
begin;
select update_last_date();
truncate times_producao;
select kstime(), insert_times_producao(), kstime();
select kstime()
2005/8/29, Michael Fuhr <[EMAIL PROTECTED]>:
>
> In general, writers shouldn't block readers. Have you examined
> pg_locks? Do you know exactly what the blocked queries are, or can
> you find out from pg_stat_activity (stats_command_string must be
> enabled)? Are you doing any explicit locking
On Mon, Aug 29, 2005 at 09:41:21PM -0300, Clodoaldo Pinto wrote:
>
> 26873 ? S 0:18 postgres: user database 127.0.0.1(38946) SELECT waiting
> 23973 ? S 0:22 postgres: user database 127.0.0.1(43396) SELECT waiting
> 31212 ? S 0:02 postgres: user database 127.0.0.1(60619) SELECT waiting
> 21601 ? S 0
Clodoaldo Pinto <[EMAIL PROTECTED]> writes:
> I've been trying for 3 days to figure out what is happening to no
> avail. What am i missing about transactions and MVCC? What could make
> a plpgsql update function lock a table?
What is the function doing to the table, exactly? DDL changes generally
14 matches
Mail list logo
