Обсуждение: Returning generated id after a transaction.

Поиск
Список
Период
Сортировка

Returning generated id after a transaction.

От
Guillaume Henriot
Дата:
Hi,

First of all excuse me if i'm not precise enough, english is not my native language.

I have a small problem while using transactions. I'm doing an INSERT in one table, an UPDATE in another table and then i'm trying to retrieve the id generated by the serial in the first table but the few things I have tryed does not return anything. Here is a sample of my transaction:

BEGIN;
INSERT INTO table1 (id, name) VALUES (DEFAULT, 'name_of_the_entry');
UPDATE table2 SET table1_id = CURRVAL('table1_id_seq') WHERE id = 'some_row_id';
SELECT CURRVAL('parts_id_seq') AS table1_id;
COMMIT;

I have tryed with RETURNING id on the INSERT too and pgadmin tells me each time this kind of message : "query result of 1 line cancelled".

It works well when I do it without the begin and commit, so as a pgsql beginner I'm a bit lost.


Guillaume Henriot

Re: Returning generated id after a transaction.

От
Bartosz Dmytrak
Дата:
Hi,
which postgres version You are using?
do You perform this in function or as anonymous block, or at hoc query?

regards,
Bartek


2012/4/18 Guillaume Henriot <henriotg@gmail.com>
Hi,

First of all excuse me if i'm not precise enough, english is not my native language.

I have a small problem while using transactions. I'm doing an INSERT in one table, an UPDATE in another table and then i'm trying to retrieve the id generated by the serial in the first table but the few things I have tryed does not return anything. Here is a sample of my transaction:

BEGIN;
INSERT INTO table1 (id, name) VALUES (DEFAULT, 'name_of_the_entry');
UPDATE table2 SET table1_id = CURRVAL('table1_id_seq') WHERE id = 'some_row_id';
SELECT CURRVAL('parts_id_seq') AS table1_id;
COMMIT;

I have tryed with RETURNING id on the INSERT too and pgadmin tells me each time this kind of message : "query result of 1 line cancelled".

It works well when I do it without the begin and commit, so as a pgsql beginner I'm a bit lost.


Guillaume Henriot

Re: Returning generated id after a transaction.

От
Guillaume Henriot
Дата:
Hi Bartek,

I'm currently using version 9.1.3 on windows but it will be used on a fedora server afterwards if it changes anything.
I'm not sure I know the difference between anonymous block and ad hoc query, but it's just a block I wrote in an admin page for a php based website. It works well without the BEGIN and COMMIT and gives me back the id but as soon as I add the transaction part I can't retrieve it.

Should I try in a function or stored procedure, I thought i'd start with the simplier version first to debug because it has a lot of parameters.

Thank you for your time,
Guillaume

Le 18 avril 2012 21:45, Bartosz Dmytrak <bdmytrak@gmail.com> a écrit :
Hi,
which postgres version You are using?
do You perform this in function or as anonymous block, or at hoc query?

regards,
Bartek



2012/4/18 Guillaume Henriot <henriotg@gmail.com>
Hi,

First of all excuse me if i'm not precise enough, english is not my native language.

I have a small problem while using transactions. I'm doing an INSERT in one table, an UPDATE in another table and then i'm trying to retrieve the id generated by the serial in the first table but the few things I have tryed does not return anything. Here is a sample of my transaction:

BEGIN;
INSERT INTO table1 (id, name) VALUES (DEFAULT, 'name_of_the_entry');
UPDATE table2 SET table1_id = CURRVAL('table1_id_seq') WHERE id = 'some_row_id';
SELECT CURRVAL('parts_id_seq') AS table1_id;
COMMIT;

I have tryed with RETURNING id on the INSERT too and pgadmin tells me each time this kind of message : "query result of 1 line cancelled".

It works well when I do it without the begin and commit, so as a pgsql beginner I'm a bit lost.


Guillaume Henriot


Re: Returning generated id after a transaction.

От
Bartosz Dmytrak
Дата:
2012/4/19 Guillaume Henriot <henriotg@gmail.com>
Hi Bartek,

I'm currently using version 9.1.3 on windows but it will be used on a fedora server afterwards if it changes anything.
 
platform doesn't matter, I asked about version, because You can use anonymous block what is available since 9.0 AFAIK - http://www.postgresql.org/docs/9.1/static/sql-do.html
 
I'm not sure I know the difference between anonymous block and ad hoc query, but it's just a block I wrote in an admin page for a php based website. It works well without the BEGIN and COMMIT and gives me back the id but as soon as I add the transaction part I can't retrieve it.

Should I try in a function or stored procedure, I thought i'd start with the simplier version first to debug because it has a lot of parameters.

Thank you for your time,
Guillaume


depends on needs You can:
1. use anonymous block like this one (with RETURNING):
DO
$$
DECLARE
"vParentRowId" "tblParent"."RowId"%TYPE;
BEGIN
INSERT INTO "tblParent" ("RowValue")
VALUES ('2012-01-01'::date) --that is because my test table strucutre
RETURNING "tblParent"."RowId" INTO "vParentRowId";

UPDATE "tblChild"
SET "ParentRowId" = "vParentRowId"
WHERE "RowId" = 123; --whatever ID You need;

RAISE NOTICE 'Inserted RowId: %', "vParentRowId";
END;
$$

please notice,  there is not SELECT "vParentRowId", because this block is treated as function BODY, so You would receive "query has no destination for result data" error - 
Trying use RETURN "vParentRowId" You will get error too, because DO returns void.

2. use a stored procedure eg.:

CREATE OR REPLACE FUNCTION "setParent" (
IN "vParentRowValue" "tblParent"."RowValue"%TYPE, 
IN "vChildRowId" "tblChild"."RowId"%TYPE,
OUT "ParentRowId" "tblParent"."RowId"%TYPE
)
RETURNS "tblParent"."RowId"%TYPE
AS
$BODY$
BEGIN
INSERT INTO "tblParent" ("RowValue")
VALUES ('2012-01-01'::date)
RETURNING "tblParent"."RowId" INTO "setParent"."ParentRowId";

UPDATE "tblChild"
SET "ParentRowId" = "setParent"."ParentRowId"
WHERE "RowId" = "vChildRowId";

RETURN;
END;
$BODY$
LANGUAGE plpgsql
SECURITY DEFINER STRICT;

3. use "WITH"
WITH inserted_row AS (
INSERT INTO "tblParent" ("RowValue")
VALUES ('2012-01-01'::date)
RETURNING *
)
UPDATE "tblChild"
SET "ParentRowId" = (SELECT "RowId" FROM inserted_row)
WHERE "RowId" = 123 --whatever You need
RETURNING "ParentRowId"

This one is tricky - You will get "ParentRowId" only when row in tblChild has been found and updated.

personally I prefer no.2 (function). Today I had long discussion with my GUI guy about data logic, application logic and GUI logic. We agreed - data logic should be kept as close to data as possible, business logic should be kept in middleware (beans / servlets, etc) and GUI logic should be kept in GUI. Of course, other people should have different point of view.

Regards,
Bartek

Re: Returning generated id after a transaction.

От
Bartosz Dmytrak
Дата:
Hi,
there is option no. 4 (kind of extended WITH)

WITH 
inserted_row AS (
INSERT INTO "tblParent" ("RowValue")
VALUES ('2012-01-01'::date)
RETURNING *
), 
updated_row AS (UPDATE "tblChild"
SET "ParentRowId" = (SELECT "RowId" FROM inserted_row)
WHERE "RowId" = 123 --whatever You need
)

SELECT "RowId" FROM inserted_row;

I missed it in previous post.
This could be part of function (SELECT statement should be modified) or ad-hoc query.

Regards,
Bartek


Re: Returning generated id after a transaction.

От
Guillaume Henriot
Дата:
Thank you so much for these examples, I tryed the 4th one for it's easiness and it works perfectly ! I'm probably going to replace it with your no.2 example as soon as I have time as I do agree with you about data and application logic.

Thanks again,
Guillaume

Le 21 avril 2012 10:26, Bartosz Dmytrak <bdmytrak@gmail.com> a écrit :
Hi,
there is option no. 4 (kind of extended WITH)

WITH 
inserted_row AS (
INSERT INTO "tblParent" ("RowValue")
VALUES ('2012-01-01'::date)
RETURNING *
), 
updated_row AS (UPDATE "tblChild"
SET "ParentRowId" = (SELECT "RowId" FROM inserted_row)
WHERE "RowId" = 123 --whatever You need
)

SELECT "RowId" FROM inserted_row;

I missed it in previous post.
This could be part of function (SELECT statement should be modified) or ad-hoc query.

Regards,
Bartek



Re: Returning generated id after a transaction.

От
Guillaume Henriot
Дата:
Just one quick question again about my first problem, is that a limitation that code between begin and commit can't send back the id, or was it just a problem about my code ?

Le 23 avril 2012 17:17, Guillaume Henriot <henriotg@gmail.com> a écrit :
Thank you so much for these examples, I tryed the 4th one for it's easiness and it works perfectly ! I'm probably going to replace it with your no.2 example as soon as I have time as I do agree with you about data and application logic.

Thanks again,
Guillaume

Le 21 avril 2012 10:26, Bartosz Dmytrak <bdmytrak@gmail.com> a écrit :

Hi,
there is option no. 4 (kind of extended WITH)

WITH 
inserted_row AS (
INSERT INTO "tblParent" ("RowValue")
VALUES ('2012-01-01'::date)
RETURNING *
), 
updated_row AS (UPDATE "tblChild"
SET "ParentRowId" = (SELECT "RowId" FROM inserted_row)
WHERE "RowId" = 123 --whatever You need
)

SELECT "RowId" FROM inserted_row;

I missed it in previous post.
This could be part of function (SELECT statement should be modified) or ad-hoc query.

Regards,
Bartek




Re: Returning generated id after a transaction.

От
Bartosz Dmytrak
Дата:

2012/4/23 Guillaume Henriot <henriotg@gmail.com>
Just one quick question again about my first problem, is that a limitation that code between begin and commit can't send back the id, or was it just a problem about my code ?


I think there is a bug in Your code:
BEGIN;
    INSERT INTO table1 (id, name) VALUES (DEFAULT, 'name_of_the_entry');
    UPDATE table2 SET table1_id = CURRVAL('table1_id_seq') WHERE id = 'some_row_id';
    SELECT CURRVAL('parts_id_seq') AS table1_id;
COMMIT;

I looks like You are trying to select current value of other sequence. You are trying to use table1_id_seq for update and parts_id_seq for select.

I tried similar code:
BEGIN;
INSERT INTO "tblParent" ("RowValue") VALUES ('2012-01-02'::date);
UPDATE "tblChild"
SET "ParentRowId" = currval('"tblParent_RowId_seq"'::regclass)
WHERE "RowId" = 1923;

SELECT currval('"tblParent_RowId_seq"'::regclass);
COMMIT;

works for me, but select doesn't produce any output in pgAdmin, only a message:
Query result with 1 row discarded. - is that Your exception?
Query returned successfully with no result in 26 ms. 

AFAIK pgAdmin runs all statements in SQL window in one transaction, so there is no need to put everything in BEGIN...COMMIT

I have tried this code line by line i psql and works fine too:
myDatabase=# BEGIN;
BEGIN
myDatabase=# INSERT INTO "tblParent" ("RowValue") VALUES ('2012-01-02'::date);
INSERT 0 1
myDatabase=# UPDATE "tblChild"
SET "ParentRowId" = currval('"tblParent_RowId_seq"'::regclass)
WHERE "RowId" = 1923;
UPDATE 1
myDatabase=# SELECT currval('"tblParent_RowId_seq"'::regclass);
 currval 
---------
     118
(1 row)

myDatabase=# COMMIT;
COMMIT


Regards,
Bartek 

Re: Returning generated id after a transaction.

От
Guillaume Henriot
Дата:
Ah yes, I did not see I left a mistake when I renamed my variables for readability in my example. It should be 'table1_id_seq' instead of 'parts_id_seq' and it gives me the same exception you had. I understand you don't need BEGIN/COMMIT in pgadmin, but I was just trying to test this block as it did not send anything back if I pasted it in my php code too.

Anyway I was just curious, it's not that important, I'll start using your examples and everything should be better :)

Guillaume

Le 24 avril 2012 20:46, Bartosz Dmytrak <bdmytrak@gmail.com> a écrit :

2012/4/23 Guillaume Henriot <henriotg@gmail.com>
Just one quick question again about my first problem, is that a limitation that code between begin and commit can't send back the id, or was it just a problem about my code ?


I think there is a bug in Your code:
BEGIN;
    INSERT INTO table1 (id, name) VALUES (DEFAULT, 'name_of_the_entry');
    UPDATE table2 SET table1_id = CURRVAL('table1_id_seq') WHERE id = 'some_row_id';
    SELECT CURRVAL('parts_id_seq') AS table1_id;
COMMIT;

I looks like You are trying to select current value of other sequence. You are trying to use table1_id_seq for update and parts_id_seq for select.

I tried similar code:
BEGIN;
INSERT INTO "tblParent" ("RowValue") VALUES ('2012-01-02'::date);
UPDATE "tblChild"
SET "ParentRowId" = currval('"tblParent_RowId_seq"'::regclass)
WHERE "RowId" = 1923;

SELECT currval('"tblParent_RowId_seq"'::regclass);
COMMIT;

works for me, but select doesn't produce any output in pgAdmin, only a message:
Query result with 1 row discarded. - is that Your exception?
Query returned successfully with no result in 26 ms. 

AFAIK pgAdmin runs all statements in SQL window in one transaction, so there is no need to put everything in BEGIN...COMMIT

I have tried this code line by line i psql and works fine too:
myDatabase=# BEGIN;
BEGIN
myDatabase=# INSERT INTO "tblParent" ("RowValue") VALUES ('2012-01-02'::date);
INSERT 0 1
myDatabase=# UPDATE "tblChild"
SET "ParentRowId" = currval('"tblParent_RowId_seq"'::regclass)
WHERE "RowId" = 1923;
UPDATE 1
myDatabase=# SELECT currval('"tblParent_RowId_seq"'::regclass);
 currval 
---------
     118
(1 row)

myDatabase=# COMMIT;
COMMIT


Regards,
Bartek 

Re: Returning generated id after a transaction.

От
Bartosz Dmytrak
Дата:
I think this message is related with pgAdmin not with postgres - maybe someone else could explain it.

Regards,
Bartek