Thread: trigger - dynamic WHERE clause

trigger - dynamic WHERE clause

From
Tarlika Elisabeth Schmitz
Date:
EXECUTE 'SELECT 1 FROM ' || TG_TABLE_NAME || ' WHERE ' || whereclause
|| ' FOR UPDATE;';

I am generating the whereclause dynamically as the number of columns
queried varies.

Am I right in assuming that I cannot use EXECUTE ... USING in this
scenario?

--

Best Regards,
Tarlika Elisabeth Schmitz

Re: trigger - dynamic WHERE clause

From
Pavel Stehule
Date:
Hello

2011/5/22 Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>:
> EXECUTE 'SELECT 1 FROM ' || TG_TABLE_NAME || ' WHERE ' || whereclause
> || ' FOR UPDATE;';
>
> I am generating the whereclause dynamically as the number of columns
> queried varies.
>
> Am I right in assuming that I cannot use EXECUTE ... USING in this
> scenario?
>

why not? You can use it - just USING has a fixed numbers of
parameters, so you should to use a arrays.

Regards

Pavel Stehule

> --
>
> Best Regards,
> Tarlika Elisabeth Schmitz
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: trigger - dynamic WHERE clause

From
Tarlika Elisabeth Schmitz
Date:
On Sun, 22 May 2011 20:39:01 +0200
Pavel Stehule <pavel.stehule@gmail.com> wrote:

>Hello
>
>2011/5/22 Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>:
>> EXECUTE 'SELECT 1 FROM ' || TG_TABLE_NAME || ' WHERE ' || whereclause
>> || ' FOR UPDATE;';
>>
>> I am generating the whereclause dynamically as the number of columns
>> queried varies.
>>
>> Am I right in assuming that I cannot use EXECUTE ... USING in this
>> scenario?
>>
>
>why not? You can use it - just USING has a fixed numbers of
>parameters, so you should to use a arrays.

Thank you for your responses, Pavel, and for your excellent blog pages.

Sorry, I am struggling with this a bit:

Currently, I am producing the whereclause on a subset of columns:

SELECT  array_to_string (array(
  SELECT  newrecord.key || ' = ' ||  quote_literal(newrecord.value)
  FROM (SELECT (each(hstore(NEW))).*) AS newrecord
WHERE newrecord.key LIKE 'id%' ), ' AND ')
INTO whereclause;

That gives me, for example:
SELECT 1 FROM test WHERE id1 = '26' AND id2 = 'name2' FOR UPDATE;

In an attempt to use EXECUTE '...' USING, I tried to execute
SELECT 1 FROM test WHERE id1 = $1 AND id2 = $2 FOR UPDATE;

I produced an array of corresponding values:
SELECT  array(
  SELECT  newrecord.value
    FROM (SELECT (each(hstore(NEW))).*) AS newrecord
    WHERE newrecord.key LIKE 'id%'
)  INTO av; -- text array

EXECUTE '...' USING av

==> ERROR: operator does not exist: integer = text[]



--

Best Regards,
Tarlika Elisabeth Schmitz

Re: trigger - dynamic WHERE clause

From
Tarlika Elisabeth Schmitz
Date:
Hello Pavel,
Thanks for taking the time to reply.

On Fri, 27 May 2011 09:12:20 +0200
Pavel Stehule <pavel.stehule@gmail.com> wrote:

>Hello
>
>2011/5/26 Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>:
>> On Sun, 22 May 2011 20:39:01 +0200
>> Pavel Stehule <pavel.stehule@gmail.com> wrote:
>>
>>>2011/5/22 Tarlika Elisabeth Schmitz
>>><postgresql3@numerixtechnology.de>:
>>>> EXECUTE 'SELECT 1 FROM ' || TG_TABLE_NAME || ' WHERE ' ||
>>>> whereclause || ' FOR UPDATE;';
>>>>
>>>> I am generating the whereclause dynamically as the number of
>>>> columns queried varies.
>>>>
>>>> Am I right in assuming that I cannot use EXECUTE ... USING in this
>>>> scenario?
>>>>
>>>
>>>why not? You can use it - just USING has a fixed numbers of
>>>parameters, so you should to use a arrays.
>>
>> Currently, I am producing the whereclause on a subset of columns:
>>
>> SELECT  array_to_string (array(
>>  SELECT  newrecord.key || ' = ' ||  quote_literal(newrecord.value)
>>  FROM (SELECT (each(hstore(NEW))).*) AS newrecord
>> WHERE newrecord.key LIKE 'id%' ), ' AND ')
>> INTO whereclause;
>>
>> That gives me, for example:
>> SELECT 1 FROM test WHERE id1 = '26' AND id2 = 'name2' FOR UPDATE;
>>
>> In an attempt to use EXECUTE '...' USING, I tried to execute
>> SELECT 1 FROM test WHERE id1 = $1 AND id2 = $2 FOR UPDATE;
>>
>> I produced an array of corresponding values:
>> [...]
>>
>> EXECUTE '...' USING av
>>
>> ==> ERROR: operator does not exist: integer = text[]
>>
>
>I am not sure, if I understand well to your goals.

I am trying to write a generic INSERT trigger, which checks whether the
NEW record already exists. In the simplified example above, columns
called "id*" are PK columns and they might be of different type.

>The showed problem is in wrong using a array
>[...]
>Clause USING doesn't do a array unpacking
>
>you should to generate little bit different dynamic statement
>EXECUTE 'SELECT .. WHERE a = $1[1] AND b = $1[2]' USING ARRAY[...]

I changed that but this wasn't my only problem; typecasting was the
second issue. Column "id1" is INT4 and the value obtained from NEW via
each(hstore(NEW))) converted to TEXT.

I can fix this by explicit typecasting:
'... WHERE id1 = $1[1]::int4 ...'



But there's a few things I'd be interested to understand:

1) My original version quoted all values regardless of type. I presume
this worked with integers because there's some implicit typecasting
going on?

2) I took from your blog entry
(http://okbob.blogspot.com/2008/06/execute-using-feature-in-postgresql-84.html)
that it is good practice to use EXECUTE USING.
Well, there's no danger of SQL injection as this particular DB runs on
an internal network. However, I am wondering whether EXECUTE USING has
a performance advantage?


--

Best Regards,
Tarlika Elisabeth Schmitz

Re: trigger - dynamic WHERE clause

From
Pavel Stehule
Date:
Hello

>>[...]
>>Clause USING doesn't do a array unpacking
>>
>>you should to generate little bit different dynamic statement
>>EXECUTE 'SELECT .. WHERE a = $1[1] AND b = $1[2]' USING ARRAY[...]
>
> I changed that but this wasn't my only problem; typecasting was the
> second issue. Column "id1" is INT4 and the value obtained from NEW via
> each(hstore(NEW))) converted to TEXT.
>
> I can fix this by explicit typecasting:
> '... WHERE id1 = $1[1]::int4 ...'
>
>
>
> But there's a few things I'd be interested to understand:
>
> 1) My original version quoted all values regardless of type. I presume
> this worked with integers because there's some implicit typecasting
> going on?
>

It is working usually - sometimes explicit number can help with
searching a related functions. You can have a problem when function or
operator is overwritten. You should to test it.

> 2) I took from your blog entry
> (http://okbob.blogspot.com/2008/06/execute-using-feature-in-postgresql-84.html)
> that it is good practice to use EXECUTE USING.
> Well, there's no danger of SQL injection as this particular DB runs on
> an internal network. However, I am wondering whether EXECUTE USING has
> a performance advantage?
>

You newer know where or who is attacker :)

The performance is very similar now - the most slow part is generating
of execution plan - not IO operations.

Regards

Pavel Stehule

>
> --
>
> Best Regards,
> Tarlika Elisabeth Schmitz
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: trigger - dynamic WHERE clause

From
Tarlika Elisabeth Schmitz
Date:
On Mon, 30 May 2011 11:02:34 +0200
Pavel Stehule <pavel.stehule@gmail.com> wrote:

>> 2) I took from your blog entry
>> (http://okbob.blogspot.com/2008/06/execute-using-feature-in-postgresql-84.html)
>> that it is good practice to use EXECUTE USING.
>> Well, there's no danger of SQL injection as this particular DB runs
>> on an internal network. However, I am wondering whether EXECUTE
>> USING has a performance advantage?
>>
>
>You newer know where or who is attacker :)
>The performance is very similar now - the most slow part is generating
>of execution plan - not IO operations.

I have converted my generic trigger to use EXECUTE ... USING.

I need to convert all NEW values to a text array, retaining their
ordinal position.
avals(hstore(NEW)) doesn't seem to do that:

NEW: (5,name5,1000,,,2)
avals(hstore(NEW)):  {5,name5,2,1000,NULL,NULL}

The best I can come up with is a JOIN with information_schema.columns.

--

Best Regards,
Tarlika Elisabeth Schmitz

Re: trigger - dynamic WHERE clause

From
Pavel Stehule
Date:
2011/5/31 Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>:
> On Mon, 30 May 2011 11:02:34 +0200
> Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
>>> 2) I took from your blog entry
>>> (http://okbob.blogspot.com/2008/06/execute-using-feature-in-postgresql-84.html)
>>> that it is good practice to use EXECUTE USING.
>>> Well, there's no danger of SQL injection as this particular DB runs
>>> on an internal network. However, I am wondering whether EXECUTE
>>> USING has a performance advantage?
>>>
>>
>>You newer know where or who is attacker :)
>>The performance is very similar now - the most slow part is generating
>>of execution plan - not IO operations.
>
> I have converted my generic trigger to use EXECUTE ... USING.
>
> I need to convert all NEW values to a text array, retaining their
> ordinal position.
> avals(hstore(NEW)) doesn't seem to do that:
>
> NEW: (5,name5,1000,,,2)
> avals(hstore(NEW)):  {5,name5,2,1000,NULL,NULL}
>
> The best I can come up with is a JOIN with information_schema.columns.

jup

it should be relative expensive (slow). If you need a generic triggers
use different PL instead. I can not to know what requests you have to
solve. But try to look on PLPerl or PLPython. Generic triggers can be
developed there with less work.

Regards

Pavel

>
> --
>
> Best Regards,
> Tarlika Elisabeth Schmitz
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: trigger - dynamic WHERE clause

From
Tarlika Elisabeth Schmitz
Date:
On Tue, 31 May 2011 06:09:18 +0200
Pavel Stehule <pavel.stehule@gmail.com> wrote:

>2011/5/31 Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>:
>> On Mon, 30 May 2011 11:02:34 +0200
>> Pavel Stehule <pavel.stehule@gmail.com> wrote:
>>
>>>> 2) I took from your blog entry
>>>> (http://okbob.blogspot.com/2008/06/execute-using-feature-in-postgresql-84.html)
>>>> that it is good practice to use EXECUTE USING.
>>>> Well, there's no danger of SQL injection as this particular DB runs
>>>> on an internal network. However, I am wondering whether EXECUTE
>>>> USING has a performance advantage?
>>>>
>>>
>>>You newer know where or who is attacker :)
>>>The performance is very similar now - the most slow part is
>>>generating of execution plan - not IO operations.
>>
>> I have converted my generic trigger to use EXECUTE ... USING.
>>
>> I need to convert all NEW values to a text array, retaining their
>> ordinal position.
>> avals(hstore(NEW)) doesn't seem to do that:
>>
>> NEW: (5,name5,1000,,,2)
>> avals(hstore(NEW)):  {5,name5,2,1000,NULL,NULL}
>>
>> The best I can come up with is a JOIN with
>> information_schema.columns.
>
>jup
>
>it should be relative expensive (slow).

O dear - I "only" have to import 1 Mio records. :(

>I can not to know what requests you have to solve.

It's actually quite simple: I wrote one generic insert trigger, which
- checks whether record already exists
- skips insert if it exists
- updates record instead

SELECT 1 FROM <table> WHERE pk-columns = NEW-pk-columns FOR UPDATE
IF exists
    UPDATE <table> WHERE ...
    return NULL
ELSE
    return NEW


To assemble the where-clause, I either need a naming convention for the
PK columns or obtain them via the information_schema.

My current understanding is that if I want to address the NEW fields by
number rather than by name, I need to convert NEW to array. Obviously,
the array elements need to be in a predictable position then.

>If you need a generic triggers use different PL instead.
>But try to look on PLPerl or PLPython. Generic triggers can be
>developed there with less work.

quicker to write or quicker to execute?


Another thought I had, regardless of PL: I think I should use a cursor
for the UPDATE rather than where-clause. Would that be more efficient?



--

Best Regards,
Tarlika Elisabeth Schmitz

Re: trigger - dynamic WHERE clause

From
Pavel Stehule
Date:
2011/5/31 Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>:
> On Tue, 31 May 2011 06:09:18 +0200
> Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
>>2011/5/31 Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>:
>>> On Mon, 30 May 2011 11:02:34 +0200
>>> Pavel Stehule <pavel.stehule@gmail.com> wrote:
>>>
>>>>> 2) I took from your blog entry
>>>>> (http://okbob.blogspot.com/2008/06/execute-using-feature-in-postgresql-84.html)
>>>>> that it is good practice to use EXECUTE USING.
>>>>> Well, there's no danger of SQL injection as this particular DB runs
>>>>> on an internal network. However, I am wondering whether EXECUTE
>>>>> USING has a performance advantage?
>>>>>
>>>>
>>>>You newer know where or who is attacker :)
>>>>The performance is very similar now - the most slow part is
>>>>generating of execution plan - not IO operations.
>>>
>>> I have converted my generic trigger to use EXECUTE ... USING.
>>>
>>> I need to convert all NEW values to a text array, retaining their
>>> ordinal position.
>>> avals(hstore(NEW)) doesn't seem to do that:
>>>
>>> NEW: (5,name5,1000,,,2)
>>> avals(hstore(NEW)):  {5,name5,2,1000,NULL,NULL}
>>>
>>> The best I can come up with is a JOIN with
>>> information_schema.columns.
>>
>>jup
>>
>>it should be relative expensive (slow).
>
> O dear - I "only" have to import 1 Mio records. :(
>
>>I can not to know what requests you have to solve.
>
> It's actually quite simple: I wrote one generic insert trigger, which
> - checks whether record already exists
> - skips insert if it exists
> - updates record instead
>
> SELECT 1 FROM <table> WHERE pk-columns = NEW-pk-columns FOR UPDATE
> IF exists
>    UPDATE <table> WHERE ...
>    return NULL
> ELSE
>    return NEW
>
>
> To assemble the where-clause, I either need a naming convention for the
> PK columns or obtain them via the information_schema.
>
> My current understanding is that if I want to address the NEW fields by
> number rather than by name, I need to convert NEW to array. Obviously,
> the array elements need to be in a predictable position then.
>
>>If you need a generic triggers use different PL instead.
>>But try to look on PLPerl or PLPython. Generic triggers can be
>>developed there with less work.
>
> quicker to write or quicker to execute?
>

maybe both - when you know Perl or Python

>
> Another thought I had, regardless of PL: I think I should use a cursor
> for the UPDATE rather than where-clause. Would that be more efficient?
>

little bit maybe 20% faster

Pavel

>
>
> --
>
> Best Regards,
> Tarlika Elisabeth Schmitz
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>