pá 22. 1. 2021 v 15:10 odesílatel <easter...@verfriemelt.org> napsal:
> > Probably the fix is not hard, but it is almost the same situation as the > > UNION case. The result of your code is not deterministic > > > > If there are more different ti_resource_id then some values can be > randomly > > ignored - when hash agg is used. > > > > The safe fix should be > > > > _resource_id := (SELECT ti_resource_id > > FROM tabk.resource_timeline > > WHERE ti_a2_id = _ab2_id > > AND ti_type = 'task'); > > > > and you get an exception if some values are ignored. Or if you want to > > ignore some values, then you can write > > > > _resource_id := (SELECT MIN(ti_resource_id) -- or MAX > > FROM tabk.resource_timeline > > WHERE ti_a2_id = _ab2_id > > AND ti_type = 'task'); > > > > Using DISTINCT is not a good solution. > > > > in my usecase it was perfectly fine, because there is a constraint > ensuring that here can never be more than on ti_resource_id at any given > time for a given _ab2_id. > also, whenever there would be more data ( for example if the constraint > trigger would have a bug ) you will get an error like this: > > > create table a ( t int ); > insert into a values (1),(2); > > do $$ > declare _t int; > begin > _t := distinct t from a; > end $$; > > Query failed: ERROR: query "SELECT distinct t from a" returned more > than one row > CONTEXT: PL/pgSQL function inline_code_block line 4 at assignment > > no doubt, that this piece of code might not look optimal at first glance, > but i like my code to fail fast. because with the min() approach, you will > not notice, that the constraint trigger is not doing its job, until you get > other strange sideeffects down the road. > ok then you don't need to use group by or DISTINCT just use _t := (SELECT ...); The performance will be same and less obfuscate and you will not use undocumented feature Regards Pavel > richard >
