current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

When I insert a record I need to return the inserted id by RETURNING. The problem is that the table is partitioned and in partitioned table I can't use RETURNING. I run multiple queries at once so I'm in dire need of RETURNING. Is there a way to accomplish this?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

Assuming a parent's table like this:

CREATE TABLE parent AS (
  id not null default nextval('parent_id_seq'::regclass)
  ... other columns ...
);

Whether you're using a rule or a trigger to divert the INSERTs into the child tables, immediately after the INSERT you may use:

SELECT currval('parent_id_seq'::regclass);

to get the last id inserted by your session, independently of concurrent INSERTs, each session having its own copy of the last sequence value it has obtained.

share|improve this answer
up vote 1 down vote

Without having an overview of how you are currently managing your partitioned tables, it is relatively difficult to assess what you can do to circumvent this limitation. Assuming you are using a trigger paradigm, in order to get RETURNING working as expected, you will have to change the RETURN clauses in your BEFORE INSERT functions from NULL to NEW, as the RETURNING clause will never receive the inserted values from the former case ( since they are never actually inserted into the master table ). Making this change will pass the inserted rows through to the master table as well, allowing the RETURNING clause to receive results.

However, this means a double write occurs, so an AFTER INSERT trigger function will be required to remove the duplicated elements. While this obviously incurs additional overhead and effectively halves efficiency, it will still have less impact than RULE paradigms ( which might take just as much working around anyway ), except of course in bulk insert scenarios. It's a performance trade-off for consistency and maintainability, but as long as that's understood and the benefits of using partitioned tables in the first place are keeping SLA's met, I think it's an acceptable concession.

Here is the full SQL Fiddle to show what I have described in action. Note that the NewID() function at the top is just to show how this would work without having an available sequence and is in no way a suggestion that the function itself is a good idea in any way ( there are plugins for proper global UIDs if you want to go that route ).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.