Re: script to drop and create all indexes in a database with parameter concurrently

not true. you can use something like --- select 'drop index
'||schemaname||'.'||indexname from pg_indexes;

It will return all <schema.index>

Payal Singh,
OmniTi Computer Consulting Inc.
Junior Database Architect,
Phone: 240.646.0770 x 253

On Wed, Dec 18, 2013 at 3:02 PM, Campbell, Lance <lance(at)illinois(dot)edu> wrote:

> Comment:
> Why don't you simply use a bash script that:
> 1. gets list of all indexes in a file with prefix 'DROP
> INDEX...<index_name'
> 2. Since now each line is a command, input this file to psql.
>
> I create all of my objects inside "schemas". As a result that won't quite
> work because you need to set the search_path prior to doing the drop and
> create. The script works for both those people that just use the public
> "schema" and those that use many "schema". By "schema" I am referring to
> the name space that the database objects are built in.
>
> Thanks,
>
> Lance Campbell
> Software Architect
> Web Services at Public Affairs
> 217-333-0382
>
>
>
> -----Original Message-----
> From: Payal Singh [mailto:payal(at)omniti(dot)com]
> Sent: Wednesday, December 18, 2013 9:04 AM
> To: Doom.zhou
> Cc: Campbell, Lance; pgsql-admin(at)postgresql(dot)org
> Subject: Re: [ADMIN] script to drop and create all indexes in a database
> with parameter concurrently
>
> Why don't you simply use a bash script that:
> 1. gets list of all indexes in a file with prefix 'DROP
> INDEX...<index_name'
> 2. Since now each line is a command, input this file to psql.
>
> On Wed, Dec 18, 2013 at 03:43:01PM +0800, Doom.zhou wrote:
> > oh my fault !!it's horrible
> > *ERROR: deadlock detected*
> > *DETAIL: Process 10504 waits for ShareUpdateExclusiveLock on relation
> > 23298 of database 16407; blocked by process 10502.*
> > * Process 10502 waits for ShareLock on virtual transaction 2/6981;
> > blocked by process 10504.*
> > * Process 10504: CREATE INDEX CONCURRENTLY t_ems_log_opt_time_idx
> ON
> > t_ems_log USING btree (opt_time);*
> > * Process 10502: CREATE INDEX CONCURRENTLY t_ems_log_create_by_idx
> > ON t_ems_log USING btree (create_by);*
> > *HINT: See server log for query details.*
> > *STATEMENT: CREATE INDEX CONCURRENTLY t_ems_log_opt_time_idx ON
> > t_ems_log USING btree (opt_time);*
> >
> > *parallel feature script *
> >
> >
> > >>
> > >>
> > >>
> > >>
> > >>
> > > *#set variablesexport
> > > PATH=$HOME/bin:$PATH#main
> > >
> dbhost=192.168.0.214database=haierdbport=5432schema=publicdbschema=~/tbctemp/dbschema.txtfiltered=~/tbctemp/dbschema_filtered.txtsql=~/tbctemp/rebuild_indexes.sql
> rm "$dbschema"; rm "$filtered"; rm "$sql" pg_dump -U postgres -s -h
> "$dbhost"
> > > -p $dbport -n $schema "$database" > "$dbschema" grep -e CREATE\
> > > INDEX -e SET\ search_path "$dbschema" | sed 's/CREATE\ INDEX/CREATE\
> > > INDEX\ CONCURRENTLY/g' > "$filtered"#parallel
> tmp_fifofile="/tmp/$.fifo"mkfifo
> > > $tmp_fifofile exec 6<>$tmp_fifofile rm
> $tmp_fifofilethread=2for
> > > ((i=0;i<$thread;i++));do echodone >&6 # while read pdoread -u6 #if [[
> > > "$p" == SET* ]]; then # echo $p >> "$sql" #else { name=$(cut -d\
> > > -f4 <<<"${p}") drop="DROP INDEX $name;" echo $drop >> "$sql"
> echo
> > > $p >> "$sql" psql -U postgres -h "$dbhost" -p $dbport -d
> "$database"
> > > -c "$drop" psql -U postgres -h "$dbhost" -p $dbport -d
> "$database"
> > > -c "$p" #fi echo >&6 } &done < "$filtered"waitexec 6>&-#psql -U
> > > postgres -h "$dbhost" -p $dbport -d "$database" -f "$sql" #rm
> > > "$dbschema"#rm "$filtered"#rm "$sql" #end*
> >
> >
> >
> >
> > On Wed, Dec 18, 2013 at 2:19 PM, Doom.zhou <zzepaigh(at)gmail(dot)com> wrote:
> >
> > >
> > >
> > >
> > > On Wed, Dec 18, 2013 at 4:24 AM, Campbell, Lance <lance(at)illinois(dot)edu
> >wrote:
> > >
> > >> Below is a Linux script I wrote that will drop and create all of
> > >> the indexes for your database with the “concurrently” parameter.
> > >>
> > >>
> > >>
> > >> #!/bin/sh
> > >>
> > >>
> > >>
> > >> dbhost=localhost
> > >>
> > >> database=somedatabasename
> > >>
> > >> dbschema=/tempfile/dbschema.txt
> > >>
> > >> filtered=/tempfile/dbschema_filtered.txt
> > >>
> > >> sql=/tempfile/rebuild_indexes.sql
> > >>
> > >>
> > >>
> > >> rm "$dbschema"
> > >>
> > >> rm "$filtered"
> > >>
> > >> rm "$sql"
> > >>
> > >>
> > >>
> > >> pg_dump -s -h "$dbhost" "$database" > "$dbschema"
> > >>
> > >>
> > >>
> > > Great script !
> > > i think you can add parallel perform each couple (drop & create
> > > index). ^^
> > >
>