[drupal-devel] [bug] PG_QUERY ERROR - LOCK TABLES - IMPLEMENTATION FAULT.

[Cvbge]

Issue status update for 
http://drupal.org/node/22911
Post a follow up: 
http://drupal.org/project/comments/add/22911

 Project:      Drupal
 Version:      cvs
 Component:    database system
 Category:     bug reports
 Priority:     critical
 Assigned to:  Anonymous
 Reported by:  kinai
 Updated by:   Cvbge
 Status:       patch (ready to be committed)
 Attachment:   http://drupal.org/files/issues/drupal-head-lock_0.diff (2.82 KB)

Table names are now escaped




Cvbge



Previous comments:
------------------------------------------------------------------------

Sun, 15 May 2005 14:34:48 +0000 : kinai

I get this error :


Warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax
error on or near "cache" at character 13 in
/var/www/drupal/includes/database.pgsql.inc on line 45


Fatal error: ERROR: syntax error on or near "cache" at character 13
query: LOCK TABLES cache WRITE in
/var/www/drupal/includes/database.pgsql.inc on line 62


Someone could solve this problem ?


Thanks
kinaï




------------------------------------------------------------------------

Sun, 15 May 2005 15:19:26 +0000 : kinai

The SQL Command : LOCK TABLES like used in bootstrap.inc is only
specified for MySQL, not for PostgreSQL.
The UNLOCK TABLES SQL command is not implemented in PostgreSQL. 


Someone could solve this problem ?


Thanks,
Kinaï




------------------------------------------------------------------------

Sat, 28 May 2005 23:34:03 +0000 : bd_csmc

this issue still exists, i've verified it on a fresh cvs install w/
pgsql 8




------------------------------------------------------------------------

Sat, 28 May 2005 23:39:52 +0000 : bd_csmc

i fixed the problem in my installation, by changing the syntax to:


LOCK TABLE {cache}


it's table, not tables. and i dropped the 'write' bit.


furthermore, pgsql does not support the UNLOCK command, so i had to
comment the unlock tables command. unless there are more lock/unlock
commands elsewhere in the code, this works fine for me.


according to pgsql docs, UNLOCK is automatically performed at
transaction end, which is why there is no such command.




------------------------------------------------------------------------

Fri, 19 Aug 2005 11:29:25 +0000 : Cvbge

Attachment: http://drupal.org/files/issues/locks.diff (2.91 KB)

The patch adds two functions to lock and unlock tables.




------------------------------------------------------------------------

Tue, 23 Aug 2005 12:57:43 +0000 : Cvbge

Attachment: http://drupal.org/files/issues/locks_0.diff (2.8 KB)

Updated, without unnecessary changes.




------------------------------------------------------------------------

Tue, 23 Aug 2005 13:07:18 +0000 : chx

Cvbge says he tested pgsql, I applied to a mysql one, saved system
settings and looks OK.




------------------------------------------------------------------------

Tue, 23 Aug 2005 14:31:53 +0000 : Cvbge

Attachment: http://drupal.org/files/issues/locks_1.diff (2.81 KB)

Worked, yet not worked ...
This version does not use 
<?php
 db_query("{$table}" ) 
?>

 syntax. I promise I won't do that again!




------------------------------------------------------------------------

Thu, 25 Aug 2005 21:10:07 +0000 : Dries

MySQL is happy with the singular 'LOCK TABLE' (rather than 'LOCK
TABLES') which should work with both MySQL and PostgreSQL.  Committed
that changes to CVS.




------------------------------------------------------------------------

Thu, 25 Aug 2005 21:43:09 +0000 : Cvbge

Please rollback your patch and either
a) use syntax LOCK TABLE name (dropping S and WRITE, if you say that
mysql support "LOCK")
b) apply my patch.


Postgres does not support WRITE part (as well as S part).
Also locking won't work without starting transaction.


You can do LOCK TABLE name only, but this lock is too excessive and
won't allow even SELECTs on locked table.




------------------------------------------------------------------------

Fri, 26 Aug 2005 10:15:15 +0000 : Cvbge

Attachment: http://drupal.org/files/issues/drupal-head-lock.diff (2.82 KB)

Attached updated patch for current head.




------------------------------------------------------------------------

Sat, 27 Aug 2005 17:14:55 +0000 : Thomas Ilsche

+1
tested the patch on pgsql, runs as expected.