Re: [GENERAL] currval and DISCARD ALL

On 25-07-2013 05:32, suresh.balasubra wrote:
> Disclaimer: I am no hacker, just a PostGreSQL user, trying to provide a user
> scenario where DISCARD SEQUENCES functionality is required.
>
> We have designed a developed a small Application Development platform for
> which the backend is PostGreSQL.
>
> There is a DBLayer which is responsible in generating SQL statements for all
> the INSERT, UPDATE, DELETE operations. Data can be pushed into multiple
> tables using this layer. What we provide to this layer is just a DataSet
> (.NET). DataTables in the DataSet will be named after their respective
> tables. We also use DataColumn extended properties to push in additional
> logic. All these happen with in a transaction and also in one shot, just one
> hit to the DB by appending SQL statements in proper order.
>
> There is an interesting feature that we have built into this DBlayer which
> is auto linking. All tables in our system will have a serial field 'id'.
> Suppose there is a master table (let us call it 'voucher') and a detail
> table ('voucher_lines'), and we are using the layer to push one record to
> the master table and 50 records to the detail table. 'voucher_lines' table
> will have a integer column 'voucher_id'. '*_id' fields are automatically
> populated with 'currval('*_id_seq'). All this works like a charm.
>
> Now, imagine we want to push data into another table (say 'invoice') which
> also has a field 'voucher_id'. This is a different activity not connected
> with the above mentioned transaction. In this scenario this field will get
> updated as currval('voucher_id_seq') returns a value. But we do not want
> that to be updated. What we want is to resolve '*_id' fields into values
> only within a transaction. After the transaction we want to get away with
> the session. If we could have cleared the session someway (DISCARD ALL does
> it, but not the currval sequence info) after the first transaction it would
> have worked for us.
>

I already sent a patch to implement DISCARD SEQUENCES [1] that I expect
to be part of 9.4 version.

Regards,

[1] https://commitfest.postgresql.org/action/patch_view?id=1171

--
Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento