PREPARE

On Mon, 16 Nov 1998, Taral wrote:

It seems that full support for CORBA and the COS Query Service requires us
to enable the user to parse, prepare, and execute the query in three
separate stages. Are we also planning to support PREPARE? If so, we should
co-ordinate the effort, since the full COSQS support will require pulling
apart pg_parse_and_plan().

Implementing PREPARE would benefit JDBC.

Currently, were implementing it in the driver but having this in the
backend would benefit JDBC a lot in performance.

--
Peter T Mount peter@retep.org.uk
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
Java PDF Generator: http://www.retep.org.uk/pdf

Taral wrote:

It seems that full support for CORBA and the COS Query Service requires us
to enable the user to parse, prepare, and execute the query in three
separate stages. Are we also planning to support PREPARE? If so, we should
co-ordinate the effort, since the full COSQS support will require pulling
apart pg_parse_and_plan().

We should.

Currently we do support PREPARE (kind of) in the SPI interface.

However, it is not strictly necessary (both ODBC and JDBC currently
simulate it on the client side), but it would enable interactive
applications perform much better if we did.

The current FE<->BE protocol is strange mix of CLI and directly
usable psql replacement ;)

BTW, what does CORBA prescribe about transactions (if anything) ?

Is the current transaction model adequate or do we need nested
transactions ?

PS. It would probably be beneficial to look also at Microsofts ADO for
ideas,
afaik this is the DCOM version of what we are trying to do with CORBA.

------------
Hannu

On Mon, Nov 16, 1998 at 02:19:32PM -0600, Taral wrote:

separate stages. Are we also planning to support PREPARE? If so, we should
co-ordinate the effort, since the full COSQS support will require pulling
apart pg_parse_and_plan().

Hopefully. I'm still holding back PREPARE for ecpg until I can think of a
good solution. The best of course would be in the backend. Hmm, how do ODBC
and JDBC solve this?

Michael
--
Dr. Michael Meskes, Manager of the Western Branch Office, Datenrevision GmbH
work: Cuxhavener Str. 36, D-21149 Hamburg
home: Th.-Heuss-Str. 61, D-41812 Erkelenz, Michael.Meskes@usa.net
Go SF49ers! Go Rhein Fire! Use Debian GNU/Linux!

I'm still holding back PREPARE for ecpg until I can think of a
good solution. The best of course would be in the backend.

So what would it take to do this in the backend? I think the places
which would need to be touched fall into areas I either know about or am
starting to look at to implement the CASE clause.

We'd need:
- a "named buffer" (or several) to hold the intermediate input
- a way to pass in parameters or substitution arguments
- a way to decide if previous parser/planner/executor
results can be reused

What else?

- Tom

I'm still holding back PREPARE for ecpg until I can think of a
good solution. The best of course would be in the backend.

So what would it take to do this in the backend? I think the places
which would need to be touched fall into areas I either know about or am
starting to look at to implement the CASE clause.

We'd need:
- a "named buffer" (or several) to hold the intermediate input

portals

- a way to pass in parameters or substitution arguments

SQL functions?

- a way to decide if previous parser/planner/executor
results can be reused

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

[ Cross-post to pgsql-interfaces ]

BTW, what does CORBA prescribe about transactions (if anything) ?

Is the current transaction model adequate or do we need nested
transactions ?

The Query Service is read-only, so does not have locking or transactions...
We will have to implement the Transaction Service...

Current service list for our implementation: (in order of importance)

Naming Service (provided by most 2.2 ORBs)
LifeCycle Service (provided by mico) (dependent on NS)
Query Service
Security Service
ConcurrencyControl Service
Transaction Service (dependent on CCS)
Relationship Service (provided by mico)

(Not sure about the ordering of the last few...)

As you can see, this is a non-trivial list of interfaces :)

Taral

Is the current transaction model adequate or do we need nested
transactions ?

Err... I didn't answer your question, did I? The COS Transaction Service
implements nested transactions.

Taral

Michael Meskes wrote:

On Mon, Nov 16, 1998 at 02:19:32PM -0600, Taral wrote:

separate stages. Are we also planning to support PREPARE? If so, we should
co-ordinate the effort, since the full COSQS support will require pulling
apart pg_parse_and_plan().

Hopefully. I'm still holding back PREPARE for ecpg until I can think of a
good solution. The best of course would be in the backend. Hmm, how do ODBC
and JDBC solve this?

Speaking for ODBC, we keep the PREPARE'd statement in a malloc'ed buffer in the
driver. The fun part is that we must support a set of API calls which request
things like the number of parameters, and result set, column info. We get the
parameter count by simply counting the parameter markers. To get the column
info, we send the statement to the backend, retrieve the column info and discard
any returned rows. Not very elegant nor inefficient. But it works ok.

This functionality should be handled by the backend. May I suggest a protocol
that will allow this typical interchange.

send PREPARE(statement)
receive stmt_handle

send GET_PARAM_COUNT(stmt_handle)
receive param_count
for i = 1 to param_count
send DESCRIBE_PARAMETER(stmt_handle, i); -- include: type, nullability,
scale, & precision
receive parameter description.
end for

send GET_COLUMN_COUNT(stmt_handle);
receive column_count
for i = 1 to column_count
send DESCRIBE_COLUMN(stmt_handle, i); -- included: tablename,
column name, column alias, type, nullability, scale & precision
receive column description.
end for

-- There are other column info attributes worth sending such as: owner,
searchable, signed/unsigned, updateable, case sensitive & autoincrement
-- I will be quite content if we get the main ones specified above.

for n set of parameters
for i = 1 to param_count
send PUT_DATA(stmt_handle, i, param_data[i])
end for
send EXECUTE(stmt_handle)
receive result set
end for

send FREE(stmt_handle)

Is the current transaction model adequate or do we need nested
transactions ?

Err... I didn't answer your question, did I? The COS Transaction Service
implements nested transactions.

Aha... finally found the line I was looking for:

"An implementation of the Transaction Service is not required to support
nested transactions."

Taral

On Tue, 17 Nov 1998, Michael Meskes wrote:

On Mon, Nov 16, 1998 at 02:19:32PM -0600, Taral wrote:

separate stages. Are we also planning to support PREPARE? If so, we should
co-ordinate the effort, since the full COSQS support will require pulling
apart pg_parse_and_plan().

Hopefully. I'm still holding back PREPARE for ecpg until I can think of a
good solution. The best of course would be in the backend. Hmm, how do ODBC
and JDBC solve this?

Background:

JDBC has a class called PrepareStatement. It's created by the
prepareStatement() method in the Connection class. The statement passed to
it has each required parameter represented by a ?

insert into mytable (field1,field2,field3) values (?,?,?);

Now the current postgresql jdbc implementation stores this string, and has
a Vector (Java for a dynamic array) that has each value stored in it as
the client application sets them. When the client calls the
executeUpdate() or executeQuery() methods, we just replace the ?'s with
the values in sequence, and pass the query to the backend as normal.

It's a real botch, but it works.

--
Peter T Mount peter@retep.org.uk
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
Java PDF Generator: http://www.retep.org.uk/pdf

On Tue, Nov 17, 1998 at 01:45:19PM +0000, Thomas G. Lockhart wrote:

So what would it take to do this in the backend? I think the places
which would need to be touched fall into areas I either know about or am
starting to look at to implement the CASE clause.

We'd need:
- a "named buffer" (or several) to hold the intermediate input

I didn't get this one completly. What input do you mean?

- a way to pass in parameters or substitution arguments

Yes. That means changing of declare cursor as well.

- a way to decide if previous parser/planner/executor
results can be reused

Yes.

What else?

Running planner on the statement as it is without the variables to be
substituted. So execution of declare gets faster.

Michael
--
Dr. Michael Meskes, Manager of the Western Branch Office, Datenrevision GmbH
work: Cuxhavener Str. 36, D-21149 Hamburg
home: Th.-Heuss-Str. 61, D-41812 Erkelenz, Michael.Meskes@usa.net
Go SF49ers! Go Rhein Fire! Use Debian GNU/Linux!

- a "named buffer" (or several) to hold the intermediate input

I didn't get this one completly. What input do you mean?

Just the original string/query to be prepared...

- Tom

On Tue, Nov 17, 1998 at 06:40:01PM +0000, Peter T Mount wrote:

it has each required parameter represented by a ?

insert into mytable (field1,field2,field3) values (?,?,?);

Now the current postgresql jdbc implementation stores this string, and has
a Vector (Java for a dynamic array) that has each value stored in it as
the client application sets them. When the client calls the
executeUpdate() or executeQuery() methods, we just replace the ?'s with
the values in sequence, and pass the query to the backend as normal.

That's exactly what I wanted to use for ecpg. But I guess I postpone it just
a little more. :-)

Michael
--
Dr. Michael Meskes, Manager of the Western Branch Office, Datenrevision GmbH
work: Cuxhavener Str. 36, D-21149 Hamburg
home: Th.-Heuss-Str. 61, D-41812 Erkelenz, Michael.Meskes@usa.net
Go SF49ers! Go Rhein Fire! Use Debian GNU/Linux!

On Wed, Nov 18, 1998 at 03:23:30AM +0000, Thomas G. Lockhart wrote:

I didn't get this one completly. What input do you mean?

Just the original string/query to be prepared...

I see. But wouldn't it be more useful to preprocess the query and store the
resulting nodes instead? We don't want to parse the statement everytime a
variable binding comes in.

Michael
--
Dr. Michael Meskes, Manager of the Western Branch Office, Datenrevision GmbH
work: Cuxhavener Str. 36, D-21149 Hamburg
home: Th.-Heuss-Str. 61, D-41812 Erkelenz, Michael.Meskes@usa.net
Go SF49ers! Go Rhein Fire! Use Debian GNU/Linux!

"T" == Taral <taral@cyberjunkie.com> writes:

Is the current transaction model adequate or do we need nested
transactions ?

Err... I didn't answer your question, did I? The COS Transaction Service
implements nested transactions.

T> Aha... finally found the line I was looking for:

T> "An implementation of the Transaction Service is not required to support
T> nested transactions."

To my mind there are _no_ nested transactions in Postgres.

--
Anatoly K. Lasareff Email: tolik@icomm.ru
Senior programmer

Michael Meskes wrote:

On Wed, Nov 18, 1998 at 03:23:30AM +0000, Thomas G. Lockhart wrote:

I didn't get this one completly. What input do you mean?

Just the original string/query to be prepared...

I see. But wouldn't it be more useful to preprocess the query and store the
resulting nodes instead? We don't want to parse the statement everytime a
variable binding comes in.

Right. A real improvement would only be to have the prepared
execution plan in the backend and just giving the parameter
values.

I can think of the following construct:

PREPARE optimizable-statement;

That one will run parser/rewrite/planner, create a new memory
context with a unique identifier and saves the querytree's
and plan's in it. Parameter values are identified by the
usual $n notation. The command returns the identifier.

EXECUTE QUERY identifier [value [, ...]];

then get's back the prepared plan and querytree by the id,
creates an executor context with the given values in the
parameter array and calls ExecutorRun() for them.

The PREPARE needs to analyze the resulting parsetrees to get
the datatypes (and maybe atttypmod's) of the parameters, so
EXECUTE QUERY can convert the values into Datum's using the
types input functions. And the EXECUTE has to be handled
special in tcop (it's something between a regular query and
an utility statement). But it's not too hard to implement.

Finally a

FORGET QUERY identifier;

(don't remember how the others named it) will remove the
prepared plan etc. simply by destroying the memory context
and dropping the identifier from the id->mcontext+prepareinfo
mapping.

This all restricts the usage of PREPARE to optimizable
statements. Is it required to be able to prepare utility
statements (like CREATE TABLE or so) too?

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #

But wouldn't it be more useful to preprocess the query and store the
resulting nodes instead? We don't want to parse the statement
everytime a variable binding comes in.

Sure. Sorry I wasn't being very specific. Also, whoever implements it
gets to do it either way at first :)

btw, I'm buried in trying to get a CASE statement to work, so am not
volunteering for this one...

- Tom

On Thu, Nov 19, 1998 at 03:32:54AM +0000, Thomas G. Lockhart wrote:

Sure. Sorry I wasn't being very specific. Also, whoever implements it
gets to do it either way at first :)

:-)

btw, I'm buried in trying to get a CASE statement to work, so am not
volunteering for this one...

Now I get depressed. :-)

Hopefully someone finds time for this. I don't know the internals enough and
probably will be short on time too as I change jobs yet again.

Michael
--
Dr. Michael Meskes, Manager of the Western Branch Office, Datenrevision GmbH
work: Cuxhavener Str. 36, D-21149 Hamburg
home: Th.-Heuss-Str. 61, D-41812 Erkelenz, Michael.Meskes@usa.net
Go SF49ers! Go Rhein Fire! Use Debian GNU/Linux!