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
I have almost completed one web site migration from mysql to pgsql. It
is already running totally pgsql.
But there is one last conversion problem. Most of the queries use
tables populated every 3 hours.
In mysql, for the site to not be unavailable during updating i was
creating temporary tables,
15 matches
Mail list logo
