Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Erwin nails it, but let me add that the extended query protocol allows you to use more flavors of prepared statements. Besides avoiding re-parsing and re-planning, one big advantage of prepared statements is to send parameter values separately, which avoids escaping and parsing overhead, not to mention the opportunity for SQL injections and bugs if you don't use an API that handles parameters in a manner you can't forget to escape them.</p> <p><a href="http://www.postgresql.org/docs/9.1/static/protocol-flow.html" rel="nofollow">http://www.postgresql.org/docs/9.1/static/protocol-flow.html</a></p> <blockquote> <p>Query planning for named prepared-statement objects occurs when the Parse message is processed. If a query will be repeatedly executed with different parameters, it might be beneficial to send a single Parse message containing a parameterized query, followed by multiple Bind and Execute messages. This will avoid replanning the query on each execution.</p> <p>The unnamed prepared statement is likewise planned during Parse processing if the Parse message defines no parameters. But if there are parameters, query planning occurs every time Bind parameters are supplied. This allows the planner to make use of the actual values of the parameters provided by each Bind message, rather than use generic estimates.</p> </blockquote> <p>So, if your DB interface supports it, you can use unnamed prepared statements. It's a bit of a middle ground between a query and a usual prepared statement.</p> <p>If you use PHP with PDO, please note that PDO's prepared statement implementation is rather useless for postgres, since it uses named prepared statements, but re-prepares every time you call prepare(), no plan caching takes place. So you get the worst of both : many roundtrips and plan without parameters. I've seen it be 1000x slower than pg_query() and pg_query_params() on specific queries where the postgres optimizer really needs to know the parameters to produce the optimal plan. pg_query uses raw queries, pg_query_params uses unnamed prepared statements. Usually one is faster than the other, that depends on the size of parameter data.</p>
    singulars
    1. This table or related slice is empty.
    1. POVastly different query run time in application
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USpeufeu
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThank you for the input, I will look into those. You've addressed one of my concerns about undoing prepared statements, which is sql injection troubles and the regulations that I'm actually bound by as a result ... I'm still in a development/research phase so we have some luxury with time/trial/error I mostly want to understand the impact of these decisions now. Thanks again!!
      singulars
      1. PO
      1. USStAlphonzo
    2. COThe #1 reason to use prepared statements is to amortize parsing/planning overhead when you will execute the same statement many times. However, in postgres, clever use of VALUES() allows multi-updates and many other ways to process multiple rows using a single query, with a great performance boost, too. These days in the PHP community, Prepared Statements are really fashionable, mostly for the wrong reasons (ie, to avoid SQL injections). Preparing a statement and not reusing it is slower, makes more roundtrips to the database, etc.
      singulars
      1. PO
      1. USpeufeu
    3. COPersonally I only use APIs which handle parameters automatically, so there is no way to inadvertently introduce a SQL injection. Python's DBAPI is an excellent example, and with psycopg2 it also handles type conversion (eg, postgres DATETIME <=> python datetime) for all types, including arrays. PHP has pg_query_params() which is nice, although it doesn't convert types. I don't know which language you use, though. In any case, if you don't reuse statements, use raw queries or the unnamed prepared statement, not named prepare + execute + deallocate (don't forget to deallocate !)
      singulars
      1. PO
      1. USpeufeu
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload