# pg_execute error

- Thread: https://externals.io/message/22575
- Original archive: https://news-web.php.net/php.internals/22575
- Started by Yasuo Ohgaki on 2006-03-25T08:49:18+00:00
- Messages: 16
- Reply by email to internals@lists.php.net
- More for agents: search with https://externals.io/search.md?q={query} · JSON API at https://externals.io/api · documentation at https://externals.io/llms.txt

---

## #22575 — Yasuo Ohgaki — 2006-03-25T08:49:18+00:00

URL: https://externals.io/message/22575#22575

Hi.

Current pgsql.c supports prepared statement, but pg_execute() will raise
 `E_WARNING` if query plan is not prepared yet. This is annoying when
database connection is persistent under web environment.

I would like to do something like

if (!pg_execute($db, 'myquery', array()) {
   pg_prepare($db, 'myquery', 'SQL');
   pg_execute($db, 'myquery', array();
}

WITHOUT raising any error, since @ operator does not work well with
error handler. (i.e. need special handling for pg_execute)

There are 4 options:

1) just ignore errors on pg_execute(). errors can be identified by return status anyway.
2) add pg_is_prepared()
3) add bool parameter to pg_execute()
   e.g. pg_execute(resource connection, string stmtname, array params, bool ignore_error)
4) ignore pg_execute() error if params is null array.

Any comments?

-- 
Yasuo Ohgaki

---

## #22576 — Lukas Smith — 2006-03-25T08:54:10+00:00

URL: https://externals.io/message/22575#22576 · In reply to: #22575

Yasuo Ohgaki wrote:

> 3) add bool parameter to pg_execute()
>    e.g. pg_execute(resource connection, string stmtname, array params, bool ignore_error)

how would you intent to implement this?
AFAIK there is currently no catalog to find out the prepared statements
in the current sessions (I hear 8.2 will change this). So the only way
to find out is to simply "try it" and cause an error in the session
which could trigger all sorts of error handlers on the database side ..
something I would not expect from calling a php function like this.

regards,
Lukas

---

## #22588 — Yasuo Ohgaki — 2006-03-27T02:51:28+00:00

URL: https://externals.io/message/22575#22588 · In reply to: #22576

Lukas Smith wrote:
> Yasuo Ohgaki wrote:
> 
> > […]
> 
> how would you intent to implement this?
> AFAIK there is currently no catalog to find out the prepared statements
> in the current sessions (I hear 8.2 will change this). So the only way
> to find out is to simply "try it" and cause an error in the session
> which could trigger all sorts of error handlers on the database side ..
> something I would not expect from calling a php function like this.

I hear about that.

Current pg_execute raise `E_WARNING` if plan is not prepared.
The error is annoying since programmers has to try and
see if it works.

Anyway, since there are no other comments, I'll get rid of
the error from pg_execute. Since this is the most efficient
way.

-- 
Yasuo Ohgaki

---

## #22591 — Lukas Smith — 2006-03-27T08:01:15+00:00

URL: https://externals.io/message/22575#22591 · In reply to: #22588

Yasuo Ohgaki wrote:
> Lukas Smith wrote:
> > […]
> 
> I hear about that.
> 
> Current pg_execute raise `E_WARNING` if plan is not prepared.
> The error is annoying since programmers has to try and
> see if it works.
> 
> Anyway, since there are no other comments, I'll get rid of
> the error from pg_execute. Since this is the most efficient
> way.

Just to make it clear: calling pg_execute() on a not yet prepared
statement will cause your transaction to be rolled  back on the next
commit. Encouraging the use of pg_execute() to find out of the statement
has been prepared is therefore wrong.

Moreover by your logic we would need to remove E_WARNING's from php
entirely. Finally you can detect if a function call was called with
error suppression in your error handler and that is the appropriate
place to address your issue.

regards,
Lukas

---

## #22594 — Wez Furlong — 2006-03-27T12:40:33+00:00

URL: https://externals.io/message/22575#22594 · In reply to: #22588

This sounds like a really bad, backwards compatibility-breaking idea.

--Wez.


> Anyway, since there are no other comments, I'll get rid of
> the error from pg_execute. Since this is the most efficient
> way.

---

## #22596 — Edin Kadribasic — 2006-03-27T13:26:12+00:00

URL: https://externals.io/message/22575#22596 · In reply to: #22594

Agred, the patch should be reverted.

Edin


Wez Furlong wrote:
> This sounds like a really bad, backwards compatibility-breaking idea.
> 
> --Wez.
> 
> 
> 
> > […]

---

## #22597 — Yasuo Ohgaki — 2006-03-27T16:15:18+00:00

URL: https://externals.io/message/22575#22597 · In reply to: #22591

Lukas Smith wrote:
> Just to make it clear: calling pg_execute() on a not yet prepared
> statement will cause your transaction to be rolled  back on the next
> commit. Encouraging the use of pg_execute() to find out of the statement
> has been prepared is therefore wrong.
> 
> Moreover by your logic we would need to remove E_WARNING's from php
> entirely. Finally you can detect if a function call was called with
> error suppression in your error handler and that is the appropriate
> place to address your issue.

This issue is like `file_exists()` raises E_ERROR, since
PostgreSQL does not provide means to check if plan is
already defined. i.e. design error for the function.

Even if PostgreSQL provide view for already defined plans,
selecting the view to check requires. It requires overheads
which requires network traffic. Thus adding new feature to
check if plan is defined is not good idea.
i.e. DB is usually a bottle neck of web systems.

-- 
Yasuo Ohgaki

---

## #22598 — Lukas Smith — 2006-03-27T16:25:52+00:00

URL: https://externals.io/message/22575#22598 · In reply to: #22597

Yasuo Ohgaki wrote:
> Lukas Smith wrote:
> > […]
> 
> This issue is like `file_exists()` raises E_ERROR, since
> PostgreSQL does not provide means to check if plan is
> already defined. i.e. design error for the function.
> 
> Even if PostgreSQL provide view for already defined plans,
> selecting the view to check requires. It requires overheads
> which requires network traffic. Thus adding new feature to
> check if plan is defined is not good idea.
> i.e. DB is usually a bottle neck of web systems.

So why dont you just do @pg_execute() and in your error handler you can do:

function ErrorHandler($errno, $errstr, $errfile, $errline)
{
 // ignore silenced function calls
 if (!error_reporting()) {
    return;
  }
 ..

If you want a "beautiful" solution you will have to manage your prepared
statements in some persistant layer.

regards,
Lukas

---

## #22599 — Yasuo Ohgaki — 2006-03-27T16:37:37+00:00

URL: https://externals.io/message/22575#22599 · In reply to: #22597

Yasuo Ohgaki wrote:
> Lukas Smith wrote:
> > […]
> 
> This issue is like `file_exists()` raises E_ERROR, since
> PostgreSQL does not provide means to check if plan is
> already defined. i.e. design error for the function.
> 
> Even if PostgreSQL provide view for already defined plans,
> selecting the view to check requires. It requires overheads
> which requires network traffic. Thus adding new feature to
> check if plan is defined is not good idea.
> i.e. DB is usually a bottle neck of web systems.
> 

To make this issue clear, consider following code

for ($i = 0; $i < 10000; $i++) {
  if (!pg_is_prepared('myplan')) { // suppose this function is there
    pg_prepare('myplan', 'sql');
  }
  pg_execute('myplan', $values);
}

w/o the patch, pg_is_prepared() has to be called 10k times which
probably requires additional network traffic.

Best practice tells users should prepare statement before get into
transaction block and check transaction is committed or aborted
anyway. There are other reasons that transaction may fail as you
probably knew.

I disabled all E_WARNING, since all users are encouraged to check
the return status of pg_execute(), but other error may be caught
by pg_execute(). Is this enough?

-- 
Yasuo Ohgaki : yohgaki@ohgaki.net : http://www.ohgaki.net/

---

## #22600 — Yasuo Ohgaki — 2006-03-27T16:46:07+00:00

URL: https://externals.io/message/22575#22600 · In reply to: #22598

Lukas Smith wrote:
> So why dont you just do @pg_execute() and in your error handler you can do:
> 
> function ErrorHandler($errno, $errstr, $errfile, $errline)
> {
>  // ignore silenced function calls
>  if (!error_reporting()) {
>     return;
>   }
>  ..
> 
> If you want a "beautiful" solution you will have to manage your prepared
> statements in some persistant layer.

This is one option.

@ operator is ok, but usually @ operator is not recommenned.
Don't you think so? I try not to use @ as much as possible.

pg_execute() does not have to raise error just like `file_exists()`.
It may be good idea to raise error when connection is bad, etc.

-- 
Yasuo Ohgaki

---

## #22601 — Lukas Smith — 2006-03-27T16:51:03+00:00

URL: https://externals.io/message/22575#22601 · In reply to: #22600

Yasuo Ohgaki wrote:

> @ operator is ok, but usually @ operator is not recommenned.
> Don't you think so? I try not to use @ as much as possible.
> 
> pg_execute() does not have to raise error just like `file_exists()`.
> It may be good idea to raise error when connection is bad, etc.

the problem is a limitation in pgsql and hiding this fact imho causes
more problems than it solves, especially since pgsql will eventually
solve this limitation (i hear 8.2 will solve this).

another solution that does of course add a fixed amount of overhead is
to simply prepare the statement again before you enter the loop. you
will then only have to supress a single call to pg_prepare().

regards,
Lukas

---

## #22602 — Wez Furlong — 2006-03-27T21:11:31+00:00

URL: https://externals.io/message/22575#22602 · In reply to: #22600

Regardless of whether it's a good idea or not, you should not just go
ahead and commit such a big behaviour change to the stable branch
during the release process.

Please revert your commit.

--Wez.


> Lukas Smith wrote:
> > […]
>
> This is one option.
>
> @ operator is ok, but usually @ operator is not recommenned.
> Don't you think so? I try not to use @ as much as possible.
>
> pg_execute() does not have to raise error just like `file_exists()`.
> It may be good idea to raise error when connection is bad, etc.
>
> --
> Yasuo Ohgaki

---

## #22603 — Yasuo Ohgaki — 2006-03-28T00:22:04+00:00

URL: https://externals.io/message/22575#22603 · In reply to: #22601

Lukas Smith wrote:
> Yasuo Ohgaki wrote:
> 
> > […]
> 
> the problem is a limitation in pgsql and hiding this fact imho causes
> more problems than it solves, especially since pgsql will eventually
> solve this limitation (i hear 8.2 will solve this).

I heard postgresql people intends

just execute PQexecute, if it fails, prepare

since this is most natural way to handle persistent database connections.
8.2 backends will have view for currently registered plans. Since backends
have query plans, clients must requests send over net work just to see if
plan is defined or not.

i.e.
if (!pg_execute('plan')) {
  pg_prepare('plan', sql);
  pg_execute('plan');
}

Looking up plan data in backends is just a waste of resources even when 8.2
is available.

> 
> another solution that does of course add a fixed amount of overhead is
> to simply prepare the statement again before you enter the loop. you
> will then only have to supress a single call to pg_prepare().

pg_prepare() also raises `E_WARNING` when plan is already defined, thus
calling pg_prepare() is not an option. PQprepare is not light function also.
It has to initialize SQL parser etc, while PQexecute just looks up plan
names when it fails.

When app uses PHP's persistent connections (or pgpooled connections), programmers
can take advantages of prepared statements performance benefits.
i.e. PostgreSQL have to compile SQL only once for a web server thread /process.

I don't see any down sides removing pg_execute `E_WARNING` for duplicate plans.

I will restore pg_execute E_WARING except already defined plans.
Is this satisfactory?

-- 
Yasuo Ohgaki

---

## #22604 — Yasuo Ohgaki — 2006-03-28T00:25:38+00:00

URL: https://externals.io/message/22575#22604 · In reply to: #22602

Wez Furlong wrote:
> Regardless of whether it's a good idea or not, you should not just go
> ahead and commit such a big behaviour change to the stable branch
> during the release process.
> 
> Please revert your commit.

That's good point.
Any people should not depend on pg_execute's `E_WARNING` error
in transaction, but I'll revert patch against PHP_5_1.

--
Yasuo Ohgaki

> 
> --Wez.
> 
> 
> > […]

---

## #22605 — Lukas Smith — 2006-03-28T09:15:06+00:00

URL: https://externals.io/message/22575#22605 · In reply to: #22604

Yasuo Ohgaki wrote:
> Wez Furlong wrote:
> > […]
> 
> That's good point.
> Any people should not depend on pg_execute's `E_WARNING` error
> in transaction, but I'll revert patch against PHP_5_1.

Sorry but your comparison to `file_exists()` is wrong. pg_execute() 
compares to something like include() if at all. Again I do want 
something like pg_is_prepared() but I rather wait until pgsql 8.2 where 
we get a proper native solution.

For your case you should simply do:

// this will trigger an error if the query plan is already prepared
// but we can ignore the error
@pg_prepare($dbconn, 'myplan', $sql);
for ($i = 0; $i < 10000; $i++) {
   pg_execute($dbconn, 'myplan', $values);
}

along with the necessary changes to your error handler you are all set. 
you only have a single error silenced call.

so this change should be reverted in all branches ..

regards,
Lukas

---

## #22610 — Yasuo Ohgaki — 2006-03-29T01:07:12+00:00

URL: https://externals.io/message/22575#22610 · In reply to: #22605

Lukas Smith wrote:
> Sorry but your comparison to `file_exists()` is wrong. pg_execute()
> compares to something like include() if at all. Again I do want
> something like pg_is_prepared() but I rather wait until pgsql 8.2 where
> we get a proper native solution.
> 
> For your case you should simply do:
> 
> // this will trigger an error if the query plan is already prepared
> // but we can ignore the error
> @pg_prepare($dbconn, 'myplan', $sql);
> for ($i = 0; $i < 10000; $i++) {
>   pg_execute($dbconn, 'myplan', $values);
> }
> 
> along with the necessary changes to your error handler you are all set.
> you only have a single error silenced call.
> 

You are missing points:

1. already defined planned query view requires overheads which
   requires network traffic.
2. @ is better to be avoided as much as possible.
3. All methods except following code requires some overheads.
4. "PQexecute, if it fails PQprepare" is the *intended* usage.
   (i.e. PQexecute should be used as PQisPrepared for usual app)
5. pg_execute's `E_WARNING` error for planned query is too much.
   (I think it's useful for debugging, though)

You may ignore extra pg_prepare or pg_is_prepared call over
postgresql connections, but I do not.

I didn't expect strong opposition removing `E_WARNING` from
pg_execute() since it seems it is the most preffered ways
to resolve issue.

Anyway, I will use @ for pg_execute(), since it's satisfactory
resolution also. `fopen()`'s `E_WARNING` is annoying. I just don't
like to have new function which behave like `fopen()`.
-- 
Yasuo Ohgaki