Skip site navigation (1) Skip section navigation (2)

Re: Instability in TRUNCATE regression test

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: Instability in TRUNCATE regression test
Date: 2006-06-28 17:50:22
Message-ID: 20060628175022.GA30996@surnet.cl (view raw or flat)
Thread:
Lists: pgsql-hackers
Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > Tom Lane wrote:
> >> 1. Find a way to make the processing order consistent (eg by driving it
> >> off OID ordering).  Doesn't seem easy, but maybe I'm missing an idea.
> 
> > Hmm, what about
> 
> > 1. get the complete list of tables to truncate, AccessShareLock'ed, get
> > their names
> > 2. release locks
> > 3. sort the list lexicographically (or by Oid, whatever)
> > 4. acquire the stronger locks, in list order, taking care of not
> > aborting if a table is no longer there
> > 5. truncate
> 
> Releasing locks is no good ... what if someone adds/drops FK constraints
> while you've not got any lock?

Recheck after acquiring the stronger locks, unlock and drop from list.

> One thing I was toying with was to add an index to pg_constraint on,
> say, (confrelid, conrelid), and to replace the existing seqscans for FK
> constraints with scans using this index.  The second-column ordering
> would guarantee everybody visits the entries in the same order.  Not
> sure about overall performance implications ... in a small database,
> several indexscans might take more time than one seqscan.

I think there is more than one place that would benefit from such an
index.  Probably turn into a syscache as well?

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

In response to

Responses

pgsql-hackers by date

Next:From: Alvaro HerreraDate: 2006-06-28 17:53:16
Subject: Re: Fixed length datatypes. WAS [GENERAL] UUID's as
Previous:From: Andrew DunstanDate: 2006-06-28 17:49:55
Subject: Re: Fixed length datatypes. WAS [GENERAL] UUID's as

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group