One Star

How to use 1 PostgreSQL sequence for multiple tables?

Hi!
I'm trying to reuse a generated id of a "SELECT nextval('my_sequence_name')" for multiple tables. I use a CSV file as a starting point which links into a tMap component. The tMap component has two outputs to tPostgresqlOutput components.
Each iteration of the CSV file should end up in a one data record in each case. Both PostgreSQL tables have an id column which is to be filled from the same sequence. So the values of the id columns should be the same for each iteration.
I was able to add the "SELECT nextval('my_sequence_name')" query as an additional column for each tPostgresqlOutput but this results of course in different values for the id columns. I also tried to link a tPostgresqlRow component to a tFileInputCSV with a connection type of "Iterate" but I then see no possibility to "copy" the generated sequence id to both tables.
Am I missing something or is there no way in talend to do that (what I doubt...)?
Tags (1)
4 REPLIES
Employee

Re: How to use 1 PostgreSQL sequence for multiple tables?

This topic makes me think a lot to 463 where fanf wanted to use the "last insert id" as a column of the flow schema. The "less bad" solution we (he, in fact) found was to use a tPerlRow. Currently, I don't see a better solution for your problem. The only "clean" solution would be to have a dedicated component which would perform a SQL query and add fetched columns to current flow schema.
Another solution could be to use :
- a tFileRowCount (on your CSV file) to know how many lines you'll have
- then run
- a tFor (from 1 to number of line in CSV file) on a tPostgresqlInput (doing the nextval action) to a tContextLoad (with a context var called "lastVal")
- then run
- CSV input/tMap/tPostgresqlOutput*2. In the tMap, use a Var $_context{lastVal}-- and use it in your 2 outputs
I'll try to give you screenshots tomorrow.
One Star

Re: How to use 1 PostgreSQL sequence for multiple tables?

This topic makes me think a lot to 463 where fanf wanted to use the "last insert id" as a column of the flow schema. The "less bad" solution we (he, in fact) found was to use a tPerlRow. Currently, I don't see a better solution for your problem. The only "clean" solution would be to have a dedicated component which would perform a SQL query and add fetched columns to current flow schema.
Another solution could be to use :
- a tFileRowCount (on your CSV file) to know how many lines you'll have
- then run
- a tFor (from 1 to number of line in CSV file) on a tPostgresqlInput (doing the nextval action) to a tContextLoad (with a context var called "lastVal")
- then run
- CSV input/tMap/tPostgresqlOutput*2. In the tMap, use a Var $_context{lastVal}-- and use it in your 2 outputs
I'll try to give you screenshots tomorrow.

First of all: Thanks for your quick reply!
fanf's solution sounds quite dirty to me and I actually do not want to bother with auto generated perl code.
Your mentioned dedicated component would be great to have. :-)
The solution with tFileRowCount and tFor is cleaner than fanf's one but is also quite a hack. If something goes wrong, I'm not able to rollback completely, i.e. the generated sequence ids are gone, right?
I'm just wondering how other TOS users circumvent this. This reuse of generated sequence ids is quite essential for me...
Employee

Re: How to use 1 PostgreSQL sequence for multiple tables?

I strongly suggest you to create a feature request in the bugtracker, we'll certainly provide a clean solution (maybe not in 2.2.x, it's a bit too late
One Star

Re: How to use 1 PostgreSQL sequence for multiple tables?

Thanks again for this great and quick support! I just added a feature request: 1875