Selena Deckelmann <selenamarie at gmail.com> writes:
> It would be lovely if there was a way to get a list of potential locks
> required by a script run, without actually taking the locks.

Automatically, not so much.  To get the actual set of locks, it's pretty
much necessary to run the queries, which means that *before* you know
what locks to demand to run your queries, you *first* need to run the
queries to find the list of locks, which is the wrong order....

Fortunately, you may an excellent approximation (which should be good
enough for any but rather perverse situations) by doing a QA run (not in
production!) as follows:

   ---> You specify a blank file for /tmp/locks.sql, making sure
        everything doesn't automagically lock.

   ---> You add the following query into the end of /tmp/ddl-script.sql:

         select pid, mode, granted, locktype, relation into
         public.keep_locks from pg_locks where pid = pg_backend_pid();

That leaves "breadcrumbs" of what tables got locked, which can be
queried later, to come up with a lock list.

oxrsdb=# select nspname, relname, l.* from pg_class c, pg_namespace n, public.keep_locks l where c.oid = relation and n.oid = c.relnamespace;
  nspname   |        relname        |  pid  |        mode         | granted | locktype | relation
------------+-----------------------+-------+---------------------+---------+----------+----------
 public     | keep_locks            | 23459 | AccessExclusiveLock | t       | relation |   163662
 pg_catalog | pg_locks              | 23459 | AccessShareLock     | t       | relation |    10969
 pg_toast   | pg_toast_163662       | 23459 | ShareLock           | t       | relation |   163665
 pg_toast   | pg_toast_163662_index | 23459 | AccessExclusiveLock | t       | relation |   163667
(4 rows)

If there are crucial differences between the data QA and production,
then the list *could* be wrong, due to the DDL behaving differently.
That's an argument for realistic test environments, not for not doing
this.

More interpretation of that may give a more precise query; perhaps all
we need to worry about are the AccessExclusiveLock locks.  Need to think
about that more.
-- 
select 'cbbrowne' || '@' || 'ca.afilias.info';
Christopher Browne
"Bother,"  said Pooh,  "Eeyore, ready  two photon  torpedoes  and lock
phasers on the Heffalump, Piglet, meet me in transporter room three"