Re: Possible trigger bug? function call argument literalised
Quoting Adrian Klaver : On 1/3/21 1:44 PM, Thiemo Kellner wrote: So is the below still only going to fire on INSERT? If so it will not deal with functions that disappear after the INSERT, which in the end makes it similar to my suggestion:) The point being you are taking a snapshot in time and hoping that holds going forward. Of course when a calculation fails because the function is no longer there or has changed you will know a change has occurred. Is there some process to deal with the preceding? Yes insert only, I reckon there is no way to automatically handle deletes of functions - unless I could install a trigger on the very catalogue table which I will not consider even as last resort. I also discarded the update because I am only interested in the presence check. So, if my dimension table changes some payload attribute values, I do not need to propagate this change anywhere. On the other hand, if someone changes the value of DB_ROUTINE_NAME, I better check. It is a project of my own. There is no process defined. ;-) So, I implemented a non-general solution. create or replace function METHOD_CHECK() returns trigger language plpgsql volatile as $body$ declare V_COUNT smallint; begin select COUNT(*) into V_COUNT from INFORMATION_SCHEMA.ROUTINES where ROUTINE_SCHEMA = TG_TABLE_SCHEMA and ROUTINE_NAME = NEW.DB_ROUTINE_NAME; if V_COUNT != 1 then raise exception using message = 'Routine "' || NEW.DB_ROUTINE_NAME || '" could not be found in schema "' || TG_TABLE_SCHEMA || '!', hint = 'Install the routine beforehand.'; end if; return NEW; -- If NULL was returned, the row would get skipped! end; $body$; -- S/MIME Public Key: https://oc.gelassene-pferde.biz/index.php/s/eJuAUFONag6ofnH Signal (Safer than WhatsApp): +49 1578 7723737 Threema (Safer than WhatsApp): A76MKH3J Handys: +41 78 947 36 21 | +49 1578 772 37 37
How to generate random string for all rows in postgres
I have foo table and would like to set bar column to a random string. I've
got the following query:
update foo
set bar = array_to_string(
array(select string_agg(substring('0123456789bcdfghjkmnpqrstvwxyz',
round(random() * 30)::integer, 1), '')
from generate_series(1, 9)), '');
But it generates the random string once and reuse it for all rows. I asked
people on SO and one of the giants answered (here
):
The problem is that the Postgres optimizer is just too smart and decides
> that it can execute the subquery only once for all rows. Well -- it is
> really missing something obvious -- the random() function makes the
> subquery volatile so this is not appropriate behavior.
Is this (specifically the point about random()) a bug or feature? Thanks.
Re: How to generate random string for all rows in postgres
On Mon, Jan 04, 2021 at 02:14:11PM +0100, Hassan Akefirad wrote:
> I have foo table and would like to set bar column to a random string. I've
> got the following query:
> update foo
> set bar = array_to_string(
> array(select string_agg(substring('0123456789bcdfghjkmnpqrstvwxyz',
> round(random() * 30)::integer, 1), '')
> from generate_series(1, 9)), '');
> But it generates the random string once and reuse it for all rows. I asked
> people on SO and one of the giants answered ([1]here):
Hi,
first of all - there is no need to use array_to_string(array( ... ))
just bar = (select string_agg).
it will not work, for the reasons you said, but it's better not to
overcomplicate stuff.
For your case, I think I'd simply make a function for generating random
strings:
CREATE OR REPLACE FUNCTION random_string( int ) RETURNS TEXT as $$
SELECT string_agg(substring('0123456789bcdfghjkmnpqrstvwxyz',
round(random() * 30)::integer, 1), '') FROM generate_series(1, $1);
$$ language sql;
And then use it like this:
update foo set bar = random_string(9)
I know it's not perfect, but:
1. it works
2. your query becomes easier to read/understand
3. as a side benefit you will get function for other use cases :)
Best regards,
depesz
Re: How to generate random string for all rows in postgres
Got it. Many thanks.
On Mon, Jan 4, 2021 at 2:46 PM hubert depesz lubaczewski
wrote:
> On Mon, Jan 04, 2021 at 02:14:11PM +0100, Hassan Akefirad wrote:
> > I have foo table and would like to set bar column to a random string.
> I've got the following query:
> > update foo
> > set bar = array_to_string(
> > array(select string_agg(substring('0123456789bcdfghjkmnpqrstvwxyz',
> round(random() * 30)::integer, 1), '')
> > from generate_series(1, 9)), '');
> > But it generates the random string once and reuse it for all rows. I
> asked people on SO and one of the giants answered ([1]here):
>
> Hi,
> first of all - there is no need to use array_to_string(array( ... ))
>
> just bar = (select string_agg).
>
> it will not work, for the reasons you said, but it's better not to
> overcomplicate stuff.
>
> For your case, I think I'd simply make a function for generating random
> strings:
>
> CREATE OR REPLACE FUNCTION random_string( int ) RETURNS TEXT as $$
> SELECT string_agg(substring('0123456789bcdfghjkmnpqrstvwxyz',
> round(random() * 30)::integer, 1), '') FROM generate_series(1, $1);
> $$ language sql;
>
> And then use it like this:
>
> update foo set bar = random_string(9)
>
> I know it's not perfect, but:
> 1. it works
> 2. your query becomes easier to read/understand
> 3. as a side benefit you will get function for other use cases :)
>
> Best regards,
>
> depesz
>
>
Re: Trigger with conditional predicates
--
Dirk Mika
Software Developer
mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany
fon +49 2202 2401-1197
dirk.m...@mikatiming.de
www.mikatiming.de
AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika
> > On 1 Jan 2021, at 16:56, Dirk Mika wrote:
> >
> > Hi all and a happy new Year!
> >
> > We have an Oracle schema that is to be converted to PostgreSQL, where
> > conditional predicates are used in some triggers.
> >
> > In particular, columns are populated with values if they are not specified
> > in the update statement which is used.
> > Usually with an expression like this:
> >
> > IF NOT UPDATING('IS_CANCELED')
> > THEN
> > :new.is_canceled := ...;
> > END IF;
> >
> > I have not found anything similar in PostgreSQL. What is the common
> > approach to this problem?
> Can't you use column defaults to handle these cases?
That would work for inserts, but not for updates.
BR
Dirk
Re: Trigger with conditional predicates
>>> In particular, columns are populated with values if they are not specified
>>> in the update statement which is used.
>>> Usually with an expression like this:
>>>
>>> IF NOT UPDATING('IS_CANCELED')
>>> THEN
>>> :new.is_canceled := ...;
>>> END IF;
>>>
>>> I have not found anything similar in PostgreSQL. What is the common
>>> approach to this problem?
>>
>> PostgreSQL doesn't have an exact equivalent. Typically, the OLD and NEW
>> values are compared and then action is taken based on that. For example, in
>> PL/pgSQL:
>>
>> IF NEW.is_canceled IS NOT DISTINCT FROM OLD.is_canceled THEN
>> NEW.is_canceled := etc etc ;
>> ENDIF;
>
> Unfortunately, this doesn't quite fit the logic I need. I don't need to know
> if the value was changed, but if the application that sent the UPDATE
> statement knows the column or not.
> In our case, a number of different applications access the database, which
> may or may not know the column depending on the version.
> And it is also regularly the case that SQL statements are executed directly
> in an SQL client by hand. And in the event that the column was not specified
> in these statements, a trigger is supposed to fill the value.
>
>> There's currently no way to detect if the column was simply not mentioned at
>> all in the UPDATE statement.
>
> Hmm, that's odd.
See thread below:
https://www.postgresql.org/message-id/VisenaEmail.26.7cbf2947c8d23ceb.1769a2755ff%40tc7-visena
I found that thread already, but It doesn't not provide a solution to my
problem.
--
Dirk Mika
Software Developer
mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany
fon +49 2202 2401-1197
dirk.m...@mikatiming.de
www.mikatiming.de
AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika
Re: Trigger with conditional predicates
> On Jan 4, 2021, at 11:06, Dirk Mika wrote: > > See thread below: > > https://www.postgresql.org/message-id/VisenaEmail.26.7cbf2947c8d23ceb.1769a2755ff%40tc7-visena > > I found that thread already, but It doesn't not provide a solution to my > problem. One possibility, which is admittedly very hacky, is: -- Create a new column which is a flag (or bitmap) of other columns that need to be managed in this way, with a default of 0. -- Have two EACH ROW triggers: * The first is ON UPDATE OF the actual column to managed, and sets the appropriate flag or bitmap in the flag column when run. This flags that the application has updated the column. * The second, which runs always, checks that flag, and if it is set, clears it; otherwise, it sets the column to the value desired if the application didn't change it. Of course, the order of execution of these triggers matters; PostgreSQL executes triggers at the same level alphabetically. Now, this is a pretty high-overhead way of handling it, and it is probably better to see if there is an application logic change that can happen here. Best, -- -- Christophe Pettus x...@thebuild.com
Re: Trigger with conditional predicates
> On 4 Jan 2021, at 20:02, Dirk Mika wrote:
>
>>> On 1 Jan 2021, at 16:56, Dirk Mika wrote:
>>>
>>> Hi all and a happy new Year!
>>>
>>> We have an Oracle schema that is to be converted to PostgreSQL, where
>>> conditional predicates are used in some triggers.
>>>
>>> In particular, columns are populated with values if they are not specified
>>> in the update statement which is used.
>>> Usually with an expression like this:
>>>
>>> IF NOT UPDATING('IS_CANCELED')
>>> THEN
>>>:new.is_canceled := ...;
>>> END IF;
>>>
>>> I have not found anything similar in PostgreSQL. What is the common
>>> approach to this problem?
>
>> Can't you use column defaults to handle these cases?
>
> That would work for inserts, but not for updates.
Usually, if you don’t mention a column in an UPDATE, you want the value to
remain as it was, which is precisely what happens by default. That certainly
makes sense to me when you’re dealing with an application that doesn’t know
about the existence of said column; overwriting an existing value that some
other application put there looks like a problem to me. But of course, that
depends a lot on what you’re trying to achieve.
What is your use-case that that is not the desired behaviour? Or are we talking
about a mixed problem here, where this approach works for some fields, but
other fields (such as a status change date) always need to be updated
(regardless of whether a value was specified)?
Regards,
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
