Phalcon\Mvc\Model::Update()/Save() fails on compound Primary Keys - PostgreSQL

[vitaly-sobolev]

*** ISSUE DESC ***

1. Phalcon\Mvc\Model::save() to create a new record fails with ”%column_name is required” message
   when there is PRIMARY KEY constraint on two fields (one field is of SERIAL data type) and
   when saving the object with the value that has been already existed in the table (this field is of integer data type, not serial).
   See the detailed desc below...

2. Phalcon\Mvc\Model::Update()/Save() fail with Unique violation: "...duplicate key value violates unique constraint..."
   when there are not primary key constraints.
   See the detailed desc below...

*** ENVIRONMENT ***
phalcon Version 0.6.0
pdo_pgsql
PDO Driver for PostgreSQL enabled
PostgreSQL(libpq) Version 9.0.4
Module version 1.0.2
Revision $Id: pdo_pgsql.c 306939 2011-01-01 02:19:59Z felipe $

*** SCRIPTS, OBJECTS ***

CREATE TABLE "public"."test_table" (
 "first_id" SERIAL, 
 "second_id" INTEGER NOT NULL, 
 "text_field" VARCHAR(20), 
 CONSTRAINT "test_table_idx" PRIMARY KEY("first_id", "second_id")
) WITHOUT OIDS;

class Test extends Phalcon\Mvc\Model
{
 public $first_id;
 public $second_id;
 public $text_field;
public function getSource()
{
  return "test_table"; 
}

public function initialize()
{
 $this->skipAttributes(array('first_id')); // this doesn’t help me
 $this->skipAttributesOnUpdate(array('first_id')); // this doesn’t help me
 }
}

*** DETAILED DESCRIPTIONS ***
#1 ISSUE DETAILED DESC:

The following code works fine:

 $test = new Test();
 $test->second_id = 555;
 $test->text_field = 'new 555 1';
 $test->save();

But when you try to create and save the object with value of “second_id” field that has been already existed in the table…

 $test = new Test();
 $test->second_id = 555; /* there have been already the record with second_id = 555  */
 $test->text_field = 'new 555 2';
 $test->save()

…this code fails with “first_id is required” error message.

Although at the same time the following query executes without any errors:
INSERT INTO test_table (second_id, text_field, first_id) VALUES (555, 'new 555 2', default).
#2 ISSUE DETAILED DESC:

After replacing PK constraint with the Unique key constraint:
CONSTRAINT "test_table_idx" UNIQUE("first_id", "second_id")
Update()/Save() does not work.

How to represent: firstly add a record into the table_test. And then try to update it:

$test = Test::FindFirst('first_id = 1 and second_id = 555'); /*first_id abd second_id should be equal some real data from the test_table*/
 $test->text_field = 'updated';
 $test->update();

This code raise: "SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "test_table_idx" DETAIL: Key (first_id, second_id)=(2, 555) already exists."

Note 1: $test->save() raises the same error.

Note 2: By the way: beforeCreate event of the Test model is raised in this case. Is beforeCreate supposed to be raised on UPDATE?

Note 3: with the Primary Key constraint
CONSTRAINT "test_table_idx" PRIMARY KEY("first_id", "second_id")
the above code works fine.

[phalcon]

Hi, thanks for the extensive description of your problem, I'm going to investigate your issues

[phalcon]

Hi, I uploaded some improvements to fix this issues, can you please test with 0.7.0?

[phalcon]

Please re-open this issue if you're having still this problem with 0.7.0, thanks

[vitaly-sobolev]

Hi, thanks a lot for the quick fix!
But unfortunately I cannot test it with 0.7 on Windows.

A dummy question: how to get 0.7. from the repository (to compile for FreeBSD OS)?
“git clone git://github.com/phalcon/cphalcon.git” command takes 6.1. obviously.

Or when will be 0.7(not-beta) released?

[phalcon]

Hi, 0.7.0 was released yesterday, you can compile it from master:

git clone git://github.com/phalcon/cphalcon.git
cd cphalcon/build
sudo ./install

or download a DLL from the download page: http://phalconphp.com/download

[vitaly-sobolev]

Thank you, guys!
I've tested the issue with 0.7.0.
The #1 one is fixed, everything works fine.
The #2 one is still present. Although for my project it is enough to proceed developing. But may be it will be critical for somebody.
Once again here is desc of the #2:

* SHORT DESC _: Phalcon\Mvc\Model::Update()/Save() fail with Unique violation: "...duplicate key value violates unique constraint..."
when there are not primary key constraints, but at the same time there is Unique key constraint.
*_* ENVIRONMENT ***
phalcon Version 0.7.0
pdo_pgsql
PostgreSQL(libpq) Version 9.0.4
Module version 1.0.2
PostgreSQL(libpq) PostgreSQL 9.0.4 on amd64-portbld-freebsd8.2, compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD], 64-bit

*** SCRIPTS, OBJECTS ***

CREATE TABLE "public"."test_table" (
  "first_id" SERIAL, 
  "second_id" INTEGER NOT NULL, 
  "text_field" VARCHAR(20), 
  CONSTRAINT "test_table_idx" UNIQUE("first_id", "second_id")
) WITHOUT OIDS;

class Test extends Phalcon\Mvc\Model
{
 public $first_id;
 public $second_id;
 public $text_field;
public function getSource()
{
  return "test_table"; 
}

public function initialize()
{
 $this->skipAttributes(array('first_id')); // this doesn’t help me
 $this->skipAttributesOnUpdate(array('first_id')); // this doesn’t help me
 }
}

*** DETAILED DESC ***
How to represent: firstly add a record into the table_test. And then try to update it:

/* Add a record into the just created table. */
$test = new Test();
$test->second_id = 555;
$test->text_field = 'new 555 1';
$test->save();          
/* Update this record. First_id and second_id should be equal some real data from the test_table */
$test = Test::FindFirst('first_id = 1 and second_id = 555');
$test->text_field = 'updated';
$test->update();

This code raise: "SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "test_table_idx" DETAIL: Key (first_id, second_id)=(2, 555) already exists."

And again, thanks a lot for fixing #1! Nice job!

[phalcon]

Hello, the ORM doesn't recognize unique keys as primary keys, only those that are explicitly marked as primary keys in the table. You could manually set the model's meta-data to force that those columns are treated as primary keys. ;)

http://docs.phalconphp.com/en/latest/reference/models.html#manual-meta-data

[vitaly-sobolev]

Ok, thanks for explanation. Got it. :)