PostgreSQL and JDBC: Is `UPDATE table ... RETURNING ... INTO` supported?

Question

I have a table with [primary key counters] for [per page comments in another table].
These primary keys are per page: for each page, comment IDs start on 1.

I'd like to atomically allocate 10 IDs to write 10 new comments.
— Can I do this with PostgreSQL and JDBC?

(And do you have any links to any example / the relevant JDBC documentation?)

_{I've found only examples about how returning the primary key of a newly inserted row, using some getGeneratedKeys which doesn't seem useful in my case.}

***

I think the SQL UPDATE statement would look something like this:

update PAGES
set NEXT_COMMENT_ID = NEXT_COMMENT_ID + 10
where PAGE_ID = ?                    <-- next-comment-id is *per page*
returning NEXT_COMMENT_ID into ?

So, different threads and servers won't attempt to reuse/overwrite the same IDs (right?).

Why do you need per-page comment IDs at all? Why not use per-comment IDs and foreign key back to the page? — mu is too short, Feb 24 '13 at 19:29
@muistooshort The IDs would then grow rather large. But they're supposed to be short contiguous numbers, so I can use them as indexes in a [bitset](http://www.scala-lang.org/api/current/index.html#scala.collection.BitSet). — KajMagnus, Feb 24 '13 at 19:34
(( I'll try this: http://stackoverflow.com/a/881414/694469 )) — KajMagnus, Feb 24 '13 at 19:45

score 5 · Answer 1 · edited Dec 28 '22 at 18:24

5

This is supported without using the execute() and getResult() methods on the Statement object:

Something like this (barring any error handling):

String sql = "update ... returning ...";
boolean hasResult = statement.execute(sql);
int affectedRows = 0;
ResultSet rs = null;
if (hasResult) {
  rs = statement.getResultSet();
}
int affectedRows = statement.getUpdateCount();

As you know what the statement does, this should be OK. Dealing with an "unknown" SQL statement is a bit more complicated because you need to call getMoreResults() and getUpdateCount() in a loop. See the Javadocs for details.

edited Dec 28 '22 at 18:24

Brian Agnew

268,207
37
334
440

answered Feb 25 '13 at 09:36

Thanks! I was actually about to test something like this — that is, `executed()` instead of `executeUpdate()`. I did it like this first, but thought `executeUpdate()` was the method to call (because of some other answer I found), and that didn't work so I used a stored function instead. But your solution is better — I'd rather avoid stored functions. I'll give this a try later today or so. – KajMagnus Feb 25 '13 at 11:16
should be `rs = statement.getResultSet();` – prayagupa Jun 01 '21 at 20:35

score 0 · Answer 2 · answered Feb 24 '13 at 19:49

0

So you're wanting the following structure?:

x = page primary key y = comments primary key

Page Table

x
-
1
2
3
4 etc

Comments Table

It would make most sense to have a foreign key structure here with an upper limit on the child records.

answered Feb 24 '13 at 19:49

d1ll1nger

1,571
12
16

Yes your *Comments Table* illustration is as intended. — With foreign key structure, you mean the approach that "mu is too short" commented about? That is, *"per-comment IDs and foreign key back to the page"*? – KajMagnus Feb 24 '13 at 19:57
I don't understand what this means: "upper limit on the child records"? – KajMagnus Feb 24 '13 at 19:57
Yes, I mean that structure is the best path to take. You should have a child relationship from the parent but also have a limitation on the child so you can only have 10 rows per parent row. – d1ll1nger Feb 24 '13 at 21:20
But it's a discussion system and there might be 10 000 comments per page (per parent row). So I think I cannot do it in this way, if I want fairly many and small consecutive ids, per page. – KajMagnus Feb 25 '13 at 11:16
If it's a discussion page with no limitations, then that's even more simple. Fetch the parent key aka the page key and add a comment to a child table (which also contains the parent key, aka foreign key). The above methodology will work but the child keys will be in no logical order (if you use a sequence). – d1ll1nger Feb 25 '13 at 14:19

score 0 · Answer 3 · edited May 23 '17 at 11:43

Creating a stored function that does update ... returning ... into works:

create or replace function INC_NEXT_PER_PAGE_REPLY_ID(
  site_id varchar(32), page_id varchar(32), step int) returns int as $$
declare
  next_id int;
begin
  update DW1_PAGES
    set NEXT_REPLY_ID = NEXT_REPLY_ID + step
    where SITE_ID = site_id and PAGE_ID = page_id
    returning NEXT_REPLY_ID into next_id;
  return next_id;
end;
$$ language plpgsql;

And calling it like so:

statement = connection.prepareCall(
    "{? = call INC_NEXT_PER_PAGE_REPLY_ID(?, ?, ?) }")
statement.registerOutParameter(1, java.sql.Types.INTEGER)
bind(values, statement, firstBindPos = 2)  // bind pos no. 1 is the return value
statement.execute()
nextNewReplyIdAfterwards = statement.getInt(1)

Related documentation:

http://jdbc.postgresql.org/documentation/91/callproc.html
class CallableStatement and registerOutParameter()
StackOverflow question: How to return updated rows from function

score 0 · Answer 4 · answered Feb 25 '13 at 14:23

To make the table contain a logical order then you may need to create a composite key and a foreign key within the child table.

sd=# create table x (x int);
CREATE TABLE
sd=# create table y (x int, y int);
CREATE TABLE

sd=# alter table x add primary key (x);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "x_pkey" for table "x"
ALTER TABLE
sd=# alter table y add foreign key (x) references x (x);
ALTER TABLE
sd=# alter table y add primary key (x,y);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "y_pkey" for table "y"
ALTER TABLE

sd=# insert into x values (1);
INSERT 0 1
sd=# insert into x values (2);
INSERT 0 1
sd=# insert into x values (3);
INSERT 0 1
sd=# insert into y values (1,1);
INSERT 0 1
sd=# insert into y values (1,2);
INSERT 0 1
sd=# insert into y values (1,3);
INSERT 0 1
sd=# insert into y values (1,1);
ERROR:  duplicate key value violates unique constraint "y_pkey" 
DETAIL:  Key (x, y)=(1, 1) already exists.

sd=# select * from x;
 x
---
 1
 2
 3
(3 rows)

sd=# select * from y;
 x | y
---+---
 1 | 1
 1 | 2
 1 | 3
(3 rows)

This should get you where you want to be?

Hi Dale! Thanks for your answer! There is already a child page with foreign keys to a parent table, and primary keys, in the way you describe. My quesion is intended to be about how to generate per-page comment IDs in the child page, so the IDs start on 1 (per page), and are contiguous (per page). — I think this answer is not really related to that, but more about SQL and foreign keys in general. Thanks anyway — KajMagnus, Feb 25 '13 at 15:57
(I already have a working solution, using a stored function. Also, "a_horse_with_no_name" suggested a "pure" update (without any stored function), which I might try later — except for that, from my point of view, this question is fairly much solved.) — KajMagnus, Feb 25 '13 at 15:59

score 0 · Answer 5 · answered Aug 29 '14 at 07:29

You're doing an update but the statement generates results, so use executeQuery() instead of executeUpdate(). That's the real difference between the calls: executeQuery() deals with statements that yield a ResultSet; while executeUpdate() returns count of the number of rows affected.

PostgreSQL and JDBC: Is `UPDATE table ... RETURNING ... INTO` supported?

5 Answers5