Re: Instability in TRUNCATE regression test

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Cc: pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: Instability in TRUNCATE regression test
Date: 2006-06-28 17:13:42
Message-ID: 27983.1151514822@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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?

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.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message mark 2006-06-28 17:14:16 Re: Fixed length datatypes. WAS [GENERAL] UUID's as primary keys
Previous Message Jim C. Nasby 2006-06-28 17:10:53 Re: Fixed length datatypes. WAS [GENERAL] UUID's as primary keys