trigger - dynamic WHERE clause

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

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

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

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

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

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

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

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