Selena Deckelmann <selenamarie at gmail.com> writes:
> On Thu, Aug 19, 2010 at 11:21 AM, Jan Wieck <JanWieck at yahoo.com> wrote:
>> On 8/18/2010 9:40 PM, Christopher Browne wrote:
>>> Where Jan's leaning comes in the comment here:
>>> ------
>>> To fix this, slonik needs to issue LOCK TABLE statements right after "begin;".
>>> That way, it will wait until all conflicting transactions have committed before
>>> generating its own snapshot.
>>>
>>> We need new syntax to add this information to the EXECUTE SCRIPT command and it
>>> will be the responsibility of the user to provide the list of tables to lock.
>>
>> An alternative to this would be to have EXECUTE SCRIPT parse the DDL
>> script a little deeper, executing all LOCK TABLE statements at the
>> beginning, before creating the SYNC that contains all DML before the DDL.
>>
>> This way we would not need to change the EXECUTE SCRIPT syntax at
>> all.

True enough, and it can always be handled (from a DBA's perspective) as
a single statement:

LOCK [optional TABLE] [optional ONLY] t1,t2,t3,t4,...t99;

The "simplest" rule would be that if the DDL begins with
"[Ll][Oo][Cc][Kk]", then the first statement gets pulled off and
executed first.

It's not *much* of an extension of that (e.g. - more complicated, but
not hideously so) to notice all leading LOCK statements.  (e.g. - as
soon as we find a statement that isn't a LOCK, we're done).

It doesn't require a new parser, as the one already implemented in
src/parsestatements already does the heavy lifting.  We just need to
match individual statements against LOCK.

> That was the suggestion I was going to make!
>
> Its sort of the inverse of how the executor treats EXPLAIN.. maybe? :)
>
> -selena

To Selena's comments, this isn't a panacea; unlike EXPLAIN, this doesn't
involve any deep parsing of the statements.  The parsing that we do (see
http://git.postgresql.org/gitweb?p=slony1-engine.git;a=tree;f=src/parsestatements)
is merely to split apart the SQL statements.  

The parser doesn't know anything deeper than:
 - comments
 - the several quoting styles ('', "", $dollar$ dollar quoting $dollar$)
 - we use ";" to terminate statements

It doesn't go into the PG backend to parse semantics, so we can't
automatically infer what tables need to be locked.  That's because
statements may include:

  a) Stored procedures, which make such analysis look like the Halting
     Problem, but, to *really* kill the idea,

  b) There may be statements that may reference data not available to
     the parser.  (For a truly mean case, someone might call a stored
     function that does an RPC call to get a list of tables to process
     *from a remote server*.  A parser can't do anything about that!)

At any rate, I don't think it's too scary to extend the DDL code to
partition the DDL into two sets of statements:
  - Prefix section, consisting  of LOCK statements
  - The rest of the DDL

Actually, that suggests to me a *bit* more sophisticated semantics...

EXECUTE SCRIPT works two ways:

  1.  If the DDL begins with LOCK statements, then EXECUTE SCRIPT begins
  by locking those tables specified in the LOCK statements.

  2.  If the DDL begins without any LOCK statements, then EXECUTE SCRIPT
  assumes that *ALL* tables should be locked.

Thus:

 - If you're a smart DBA, who knows what tables need to be locked, you
   can specify them, as in case #1, leaving other tables unlocked, so
   concurrent updates can continue unabated.

 - If you didn't know what to lock, and didn't bother lock anything,
   Slony-I protects the data from error by automatically demanding locks
   on all the tables.

I'd be uncomfortable with the default being to "lock nothing," which
*could* be an option #3.
-- 
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"