Radicore Forum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » RADICORE » How To » $lock_str FOR UPDATE FOR $tablename with PostgreSQL
$lock_str FOR UPDATE FOR $tablename with PostgreSQL [message #7252] Wed, 06 February 2019 13:55 Go to next message
pdv is currently offline  pdv
Messages: 15
Registered: January 2019
Junior Member
Hi,

I've run into a problem with the $lock_str "FOR UPDATE OF $tablename" when accessing a PostgreSQL database (on macos). The problem is that if an alias is defined in the FROM-clause, then this alias must be used everywhere. The documentation is clear about this:

Quote:
A substitute name for the FROM item containing the alias. An alias is used for brevity or to eliminate ambiguity for self-joins (where the same table is scanned multiple times). When an alias is provided, it completely hides the actual name of the table or function; for example given FROM foo AS f, the remainder of the SELECT must refer to this FROM item as f not foo. If an alias is written, a column alias list can also be written to provide substitute names for one or more columns of the table.

In std.table.class.inc this line (9165)

$array = $DML->getData($this->dbname_server, $this->tablename, $where);

passes the tablename also if an alias has been used, causing a PostgreSQL-error.

To solve this I've replaced this line by this code:

$table_array = extractTableNames($this->sql_from);
if(empty($table_array) || isset($table_array[$this->tablename])) {
$array = $DML->getData($this->dbname_server, $this->tablename, $where);
} else { // the first key is the alias from the FROM clause
$array = $DML->getData($this->dbname_server, array_search($this->tablename, $table_array), $where);
} //if

which works for the tests I've done sofar.

Regards,

Patrick
Re: $lock_str FOR UPDATE FOR $tablename with PostgreSQL [message #7256 is a reply to message #7252] Thu, 07 February 2019 05:06 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2246
Registered: April 2006
Location: Surrey, UK
Senior Member
Instead of just posting your solution can you please supply sample code that produces the error?

Re: $lock_str FOR UPDATE FOR $tablename with PostgreSQL [message #7260 is a reply to message #7256] Thu, 07 February 2019 08:01 Go to previous messageGo to next message
pdv is currently offline  pdv
Messages: 15
Registered: January 2019
Junior Member
Tony,

I have a department(id,...)<-->>employee(id,department_id,...) tables/relationship and when defining the relationship I got this error message (I have made the SQL query more readable):

Fatal Error: PostgreSQL: ERROR: 42P01: relation "dict_column" in FOR UPDATE clause not found in FROM clause LINE 4: ...id, dict_column_jnr.column_seq asc FOR UPDATE OF dict_colum... ^ LOCATION: transformLockingClause, analyze.c:2858 (# 42P01)

SQL query: SELECT column_name AS column_name_jnr
FROM dict_column AS dict_column_jnr
WHERE dict_column_jnr.database_id= 'bxtests'
AND dict_column_jnr.table_id= 'employee'
AND dict_column_jnr.column_id= 'department_id'
ORDER BY dict_column_jnr.database_id, dict_column_jnr.table_id, dict_column_jnr.column_seq asc
FOR UPDATE OF dict_column

Error in line 822 of file '/Library/WebServer/Documents/radicore/includes/dml.pgsql.cl ass.inc'.

Host Info: localhost, Server Version: 10.5
Client Encoding: UTF8, Server Encoding: UTF8
Database: DICT, Schema: "DICT", PUBLIC

PHP_SELF: /radicore/dict/related_column(multi4)a.php
CURRENT DIRECTORY: /Library/WebServer/Documents/radicore/dict
SERVER_ADDR: ::1
SERVER_NAME: localhost
HTTP_HOST: localhost
User Id: MGR
Role Id: GLOBAL
REMOTE_ADDR: ::1
REQUEST_URI: /radicore/dict/related_column(multi4)a.php

To get this to work the last clause should have been: FOR UPDATE OF dict_column_jnr.

Regards,

Patrick
Re: $lock_str FOR UPDATE FOR $tablename with PostgreSQL [message #7263 is a reply to message #7260] Fri, 08 February 2019 04:57 Go to previous message
AJM is currently offline  AJM
Messages: 2246
Registered: April 2006
Location: Surrey, UK
Senior Member
I will try this out, and if it works I will include the change in the next release.

Previous Topic: Radicore tutorial point
Next Topic: Multi servers and PostgreSQL
Goto Forum:
  


Current Time: Sun Jun 13 09:38:16 EDT 2021

Total time taken to generate the page: 0.01594 seconds