Обсуждение: currval = currval+1

On Wed, Jun 1, 2011 at 9:27 AM, salah jubeh <s_jubeh@yahoo.com> wrote:
> I have the following SQL statements
>
> BEGIN;
> -- account_id is a sequence
> INSERT INTO account (name) VALUES ('test customer'||random()::text);
> -- account_id is a foreign key
> INSERT INTO account_detail (account_id,......)  VALUES ((SELECT * from
> currval('account_acccount_id_seq')), ........);
> COMMIT;
>
> ERROR:  insert or update on table "account_detail" violates foreign key
> constraint ...
>
>
>
> I have executed the above without a transaction but in the same session, and
> the issue was that the current value of the account points to the curval + 1
>
> I have solved the above by subtracting 1 in the second insert statement. Why
> I am having this problem, I have used current value many time and this is
> the first time I have this behavior.


works for me -- are you sure you don't have a trigger or something
else going on behind the scenes?

merlin

On Wed, Jun 1, 2011 at 10:22 AM, salah jubeh <s_jubeh@yahoo.com> wrote:
> I have some rules on the table and I have dropped them and everything went
> fine. the rule is as follow
>
> CREATE OR REPLACE RULE status_change_ins AS
>     ON INSERT TO account  DO  INSERT INTO account_status_change_log
> (account_id, account_status_id, status_change_date)
>   VALUES (new.account_id, new.account_status_id, now());
>
> I do not know what is happing here, but this is a strange behavior.

'rules suck' is the problem :(.  uncontrollable re-execution of
volatile functions is just *one* issue with them. good news: 9.1
completely fixes this with view update triggers. In the meantime,
consider dropping the update rule and making a functions which does
the delete and updates the log, or doing it in the client.

merlin

salah jubeh <s_jubeh@yahoo.com> writes:
> I have some rules on the table and I have dropped them and everything went fine.

Rules are macros, and have the usual issues with multiple evaluations of
multiply-referenced arguments.

> CREATE OR REPLACE RULE status_change_ins AS
>     ON INSERT TO account  DO  INSERT INTO account_status_change_log (account_id,
> account_status_id, status_change_date)
>   VALUES (new.account_id, new.account_status_id, now());

You'd be far better off doing that with a trigger.

            regards, tom lane