Re: [HACKERS] stored procedures

2013-04-16 Thread aasat
Is stored procedures planned in future? I think is a most missing future today in Postgres. Using a dblink to emulate commit in transaction is very complicated -- View this message in context: http://postgresql.1045698.n5.nabble.com/stored-procedures-tp4331060p5752274.html Sent from the

Re: [HACKERS] stored procedures

2013-04-16 Thread Pavel Stehule
Hello 2013/4/16 aasat satri...@veranet.pl Is stored procedures planned in future? I think is a most missing future today in Postgres. It is in ToDo, but nobody working on this feature in this moment, probably. Using a dblink to emulate commit in transaction is very complicated

Re: [HACKERS] stored procedures

2011-09-23 Thread Merlin Moncure
On Thu, Sep 1, 2011 at 12:18 PM, Josh Berkus j...@agliodbs.com wrote: On 8/31/11 12:15 PM, Merlin Moncure wrote: An out of process, autonomous transaction type implementation should probably not sit under stored procedures for a number of reasons -- mainly that it's going to expose too many

Re: [HACKERS] stored procedures

2011-09-01 Thread Josh Berkus
On 8/31/11 12:15 PM, Merlin Moncure wrote: An out of process, autonomous transaction type implementation should probably not sit under stored procedures for a number of reasons -- mainly that it's going to expose too many implementation details to the user. For example, does a SP heavy app

Re: [HACKERS] stored procedures

2011-09-01 Thread Christopher Browne
On Thu, Sep 1, 2011 at 1:18 PM, Josh Berkus j...@agliodbs.com wrote: On 8/31/11 12:15 PM, Merlin Moncure wrote: An out of process, autonomous transaction type implementation should probably not sit under stored procedures for a number of reasons -- mainly that it's going to expose too many

Re: [HACKERS] stored procedures

2011-08-31 Thread Thom Brown
On 9 May 2011 20:52, Merlin Moncure mmonc...@gmail.com wrote: On Mon, May 9, 2011 at 1:41 PM, Bruce Momjian br...@momjian.us wrote: Josh Berkus wrote: Peter, I would like to collect some specs on this feature.  So does anyone have links to documentation of existing implementations, or

Re: [HACKERS] stored procedures

2011-08-31 Thread Merlin Moncure
On Wed, Aug 31, 2011 at 9:00 AM, Thom Brown t...@linux.com wrote: On 9 May 2011 20:52, Merlin Moncure mmonc...@gmail.com wrote: On Mon, May 9, 2011 at 1:41 PM, Bruce Momjian br...@momjian.us wrote: Josh Berkus wrote: Peter, I would like to collect some specs on this feature.  So does anyone

Re: [HACKERS] stored procedures - use cases?

2011-05-12 Thread Markus Wanner
Hi, On 05/10/2011 02:55 PM, Robert Haas wrote: On Mon, May 9, 2011 at 11:58 PM, Pavel Stehule pavel.steh...@gmail.com wrote: no - you are little bit confused :). CALL and function execution shares nothing. There is significant differences between function and procedure. Function is called

Re: [HACKERS] stored procedures - use cases?

2011-05-10 Thread Robert Haas
On Mon, May 9, 2011 at 11:58 PM, Pavel Stehule pavel.steh...@gmail.com wrote: no - you are little bit confused :). CALL and function execution shares nothing. There is significant differences between function and procedure. Function is called only from executor - from some plan, and you have

Re: [HACKERS] stored procedures - use cases?

2011-05-10 Thread Pavel Stehule
2011/5/10 Robert Haas robertmh...@gmail.com: On Mon, May 9, 2011 at 11:58 PM, Pavel Stehule pavel.steh...@gmail.com wrote: no - you are little bit confused :). CALL and function execution shares nothing. There is significant differences between function and procedure. Function is called only

Re: [HACKERS] stored procedures

2011-05-09 Thread Bruce Momjian
Josh Berkus wrote: Peter, I would like to collect some specs on this feature. So does anyone have links to documentation of existing implementations, or their own spec writeup? A lot of people appear to have a very clear idea of this concept in their own head, so let's start

Re: [HACKERS] stored procedures

2011-05-09 Thread Merlin Moncure
On Mon, May 9, 2011 at 1:41 PM, Bruce Momjian br...@momjian.us wrote: Josh Berkus wrote: Peter, I would like to collect some specs on this feature.  So does anyone have links to documentation of existing implementations, or their own spec writeup?  A lot of people appear to have a very

Re: [HACKERS] stored procedures - use cases?

2011-05-09 Thread Bruce Momjian
Kevin Grittner wrote: Peter Eisentraut pete...@gmx.net wrote: what would be the actual use cases of any of these features? Let's collect some, so we can think of ways to make them work. The two things which leap to mind for me are: (1) All the \d commands in psql should be

Re: [HACKERS] stored procedures - use cases?

2011-05-09 Thread Bruce Momjian
Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: On m??n, 2011-04-25 at 14:35 -0500, Kevin Grittner wrote: (1) All the \d commands in psql should be implemented in SPs so that they are available from any client, through calling one SP equivalent to one \d command. You don't

Re: [HACKERS] stored procedures - use cases?

2011-05-09 Thread Andrew Dunstan
On 05/09/2011 08:20 PM, Bruce Momjian wrote: Tom Lane wrote: Peter Eisentrautpete...@gmx.net writes: On mån, 2011-04-25 at 14:35 -0500, Kevin Grittner wrote: (1) All the \d commands in psql should be implemented in SPs so that they are available from any client, through calling one SP

Re: [HACKERS] stored procedures - use cases?

2011-05-09 Thread Christopher Browne
On Mon, May 9, 2011 at 9:21 PM, Andrew Dunstan and...@dunslane.net wrote: On 05/09/2011 08:20 PM, Bruce Momjian wrote: Tom Lane wrote: Peter Eisentrautpete...@gmx.net  writes: On mån, 2011-04-25 at 14:35 -0500, Kevin Grittner wrote: (1)  All the \d commands in psql should be implemented

Re: [HACKERS] stored procedures - use cases?

2011-05-09 Thread Bruce Momjian
Christopher Browne wrote: Multiple resultsets in one call would be a good thing, though, no? cheers I *thought* the purpose of having stored procedures was to allow a substrate supporting running multiple transactions, so it could do things like: - Managing vacuums - Managing

Re: [HACKERS] stored procedures - use cases?

2011-05-09 Thread Pavel Stehule
2011/5/10 Bruce Momjian br...@momjian.us: Christopher Browne wrote: Multiple resultsets in one call would be a good thing, though, no? cheers I *thought* the purpose of having stored procedures was to allow a substrate supporting running multiple transactions, so it could do things

Re: [HACKERS] stored procedures - use cases?

2011-04-29 Thread Jim Nasby
On Apr 26, 2011, at 6:08 PM, Kevin Grittner wrote: Josh Berkus j...@agliodbs.com wrote: -- doing a backfill operation for 10GB of computed data, taking 8 hours, where I don't want to hold a transaction open for 8 hours since this is a high-volume OLTP database. Been there, done that.

Re: [HACKERS] stored procedures

2011-04-29 Thread Merlin Moncure
On Fri, Apr 22, 2011 at 1:28 PM, Peter Eisentraut pete...@gmx.net wrote: On fre, 2011-04-22 at 08:37 -0500, Merlin Moncure wrote: It wouldn't bother me in the lest that if in plpgsql procedures if you had to set up and tear down a transaction on every line. It would probably be more

Re: [HACKERS] stored procedures - use cases?

2011-04-27 Thread Merlin Moncure
On Tue, Apr 26, 2011 at 10:12 PM, Greg Stark gsst...@mit.edu wrote: On Tue, Apr 26, 2011 at 11:55 PM, Josh Berkus j...@agliodbs.com wrote: Here's where I wanted autonomous transactions just last week, and didn't have them so I had to use a python script outside the database: -- doing a CREATE

Re: [HACKERS] stored procedures - use cases?

2011-04-27 Thread Josh Berkus
These don't seem like compelling use cases at all to me. You said you had to fall back to using a python script outside the database, but what disadvantage does that have? Why is moving your application logic into the database an improvement? Since both were part of a code rollout, it

Re: [HACKERS] stored procedures - use cases?

2011-04-27 Thread Greg Stark
On Wed, Apr 27, 2011 at 6:48 PM, Josh Berkus j...@agliodbs.com wrote: If you pursue your argument a little further, Greg, why do we have functions at all?  We could do it all in the application. Autonomous transactions have value on their own. But it's not so that you can run create index

Re: [HACKERS] stored procedures - use cases?

2011-04-27 Thread Josh Berkus
Greg, Because we want to be able to manipulate data in queries in data-type-specific ways. For example we want to do aggregations on the result of a function or index scans across a user data type, etc. I don't see how this is different from wanting to capture error output, which would face

Re: [HACKERS] stored procedures - use cases?

2011-04-27 Thread David E. Wheeler
On Apr 27, 2011, at 3:28 PM, Josh Berkus wrote: Actually, you can already sort of do that using XSLT. So I don't necessary think that's a prohibitive idea, depending on implementation. After all, many of the new non-relational databases implement exactly this. The proposed JSON data type

Re: [HACKERS] stored procedures - use cases?

2011-04-26 Thread Peter Eisentraut
On mån, 2011-04-25 at 14:35 -0500, Kevin Grittner wrote: (1) All the \d commands in psql should be implemented in SPs so that they are available from any client, through calling one SP equivalent to one \d command. You don't need stored procedures with special transaction behavior for this.

Re: [HACKERS] stored procedures - use cases?

2011-04-26 Thread Tom Lane
Peter Eisentraut pete...@gmx.net writes: On mån, 2011-04-25 at 14:35 -0500, Kevin Grittner wrote: (1) All the \d commands in psql should be implemented in SPs so that they are available from any client, through calling one SP equivalent to one \d command. You don't need stored procedures

Re: [HACKERS] stored procedures - use cases?

2011-04-26 Thread Kevin Grittner
Peter Eisentraut pete...@gmx.net wrote: On mån, 2011-04-25 at 14:35 -0500, Kevin Grittner wrote: (1) All the \d commands in psql should be implemented in SPs so that they are available from any client, through calling one SP equivalent to one \d command. You don't need stored procedures

Re: [HACKERS] stored procedures - use cases?

2011-04-26 Thread Josh Berkus
Another point, as there appear to be diverging camps about supertransactional stored procedures vs. autonomous transactions, what would be the actual use cases of any of these features? Let's collect some, so we can think of ways to make them work. Here's where I wanted autonomous

Re: [HACKERS] stored procedures - use cases?

2011-04-26 Thread Kevin Grittner
Tom Lane t...@sss.pgh.pa.us wrote: No, but what you *would* need is the ability to return multiple result sets from one call. At least. Even then, you could not exactly duplicate the current output of \d; but you could duplicate the functionality. I would think that psql could

Re: [HACKERS] stored procedures - use cases?

2011-04-26 Thread Kevin Grittner
Josh Berkus j...@agliodbs.com wrote: -- doing a backfill operation for 10GB of computed data, taking 8 hours, where I don't want to hold a transaction open for 8 hours since this is a high-volume OLTP database. Been there, done that. Definitely not a rare use case. -Kevin -- Sent via

Re: [HACKERS] stored procedures - use cases?

2011-04-26 Thread Greg Stark
On Tue, Apr 26, 2011 at 11:55 PM, Josh Berkus j...@agliodbs.com wrote: Here's where I wanted autonomous transactions just last week, and didn't have them so I had to use a python script outside the database: -- doing a CREATE INDEX CONCURRENTLY for 150 partitions on a partitioned table. --

Re: [HACKERS] stored procedures - use cases?

2011-04-26 Thread Daniel Farina
On Mon, Apr 25, 2011 at 12:07 PM, Peter Eisentraut pete...@gmx.net wrote: Another point, as there appear to be diverging camps about supertransactional stored procedures vs. autonomous transactions, what would be the actual use cases of any of these features?  Let's collect some, so we can

Re: [HACKERS] stored procedures

2011-04-25 Thread Merlin Moncure
On Fri, Apr 22, 2011 at 11:46 PM, David Christensen da...@endpoint.com wrote: On Apr 22, 2011, at 3:50 PM, Tom Lane wrote: Merlin Moncure mmonc...@gmail.com writes: On Fri, Apr 22, 2011 at 1:28 PM, Peter Eisentraut pete...@gmx.net wrote: It would probably be more reasonable and feasible to

Re: [HACKERS] stored procedures

2011-04-25 Thread Kevin Grittner
Merlin Moncure mmonc...@gmail.com wrote: Procedures unlike functions however can no longer rely that catalogs remain static visibility wise through execution for functions. If you start from the perspective that stored procedures are in many respects more like psql scripts than functions,

Re: [HACKERS] stored procedures

2011-04-25 Thread Merlin Moncure
On Mon, Apr 25, 2011 at 9:18 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Merlin Moncure mmonc...@gmail.com wrote: Procedures unlike functions however can no longer rely that catalogs remain static visibility wise through execution for functions. If you start from the perspective

Re: [HACKERS] stored procedures

2011-04-25 Thread Peter Eisentraut
On tor, 2011-04-21 at 18:24 +0300, Peter Eisentraut wrote: So the topic of real stored procedures came up again. Meaning a function-like object that executes outside of a regular transaction, with the ability to start and stop SQL transactions itself. I would like to add a note about the SQL

Re: [HACKERS] stored procedures

2011-04-25 Thread Andrew Dunstan
On 04/25/2011 02:18 PM, Peter Eisentraut wrote: On tor, 2011-04-21 at 18:24 +0300, Peter Eisentraut wrote: So the topic of real stored procedures came up again. Meaning a function-like object that executes outside of a regular transaction, with the ability to start and stop SQL transactions

Re: [HACKERS] stored procedures

2011-04-25 Thread Merlin Moncure
On Mon, Apr 25, 2011 at 1:18 PM, Peter Eisentraut pete...@gmx.net wrote: On tor, 2011-04-21 at 18:24 +0300, Peter Eisentraut wrote: So the topic of real stored procedures came up again.  Meaning a function-like object that executes outside of a regular transaction, with the ability to start

Re: [HACKERS] stored procedures

2011-04-25 Thread Peter Eisentraut
On mån, 2011-04-25 at 13:34 -0500, Merlin Moncure wrote: hm. does the sql standard prohibit the use of extra transactional features? It doesn't prohibit anything. It just kindly requests that standard syntax has standard behavior. are you sure it's not implied that any sql (including START

Re: [HACKERS] stored procedures - use cases?

2011-04-25 Thread Peter Eisentraut
On tor, 2011-04-21 at 18:24 +0300, Peter Eisentraut wrote: So the topic of real stored procedures came up again. Meaning a function-like object that executes outside of a regular transaction, with the ability to start and stop SQL transactions itself. I would like to collect some specs on

Re: [HACKERS] stored procedures - use cases?

2011-04-25 Thread Kevin Grittner
Peter Eisentraut pete...@gmx.net wrote: what would be the actual use cases of any of these features? Let's collect some, so we can think of ways to make them work. The two things which leap to mind for me are: (1) All the \d commands in psql should be implemented in SPs so that they are

Re: [HACKERS] stored procedures - use cases?

2011-04-25 Thread Todd A. Cook
Peter Eisentraut wrote: Another point, as there appear to be diverging camps about supertransactional stored procedures vs. autonomous transactions, what would be the actual use cases of any of these features? Looping over hundreds of identical schema executing DDL statements on each. We

Re: [HACKERS] stored procedures - use cases?

2011-04-25 Thread Merlin Moncure
On Mon, Apr 25, 2011 at 2:07 PM, Peter Eisentraut pete...@gmx.net wrote: On tor, 2011-04-21 at 18:24 +0300, Peter Eisentraut wrote: So the topic of real stored procedures came up again.  Meaning a function-like object that executes outside of a regular transaction, with the ability to start

Re: [HACKERS] stored procedures - use cases?

2011-04-25 Thread Darren Duncan
Peter Eisentraut wrote: Another point, as there appear to be diverging camps about supertransactional stored procedures vs. autonomous transactions, what would be the actual use cases of any of these features? Let's collect some, so we can think of ways to make them work. An analogy I like to

Re: [HACKERS] stored procedures

2011-04-24 Thread Susanne Ebrecht
On 21.04.2011 17:24, Peter Eisentraut wrote: I would like to collect some specs on this feature. So does anyone have links to documentation of existing implementations, or their own spec writeup? A lot of people appear to have a very clear idea of this concept in their own head, so let's start

Re: [HACKERS] stored procedures

2011-04-22 Thread Merlin Moncure
On Thu, Apr 21, 2011 at 8:34 PM, Robert Haas robertmh...@gmail.com wrote: On Apr 21, 2011, at 3:51 PM, Merlin Moncure mmonc...@gmail.com wrote: If you do it that (base it on AT) way, then you can't: 1) call any utility command (vacuum, etc) 2) run for an arbitrary amount of time 3) discard

Re: [HACKERS] stored procedures

2011-04-22 Thread Merlin Moncure
On Thu, Apr 21, 2011 at 5:07 PM, Tom Lane t...@sss.pgh.pa.us wrote: Merlin Moncure mmonc...@gmail.com writes: On Thu, Apr 21, 2011 at 1:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: 3. What sort of primitive operations do you expect the SP to be able to execute outside a transaction?  The plpgsql

Re: [HACKERS] stored procedures

2011-04-22 Thread Tom Lane
Merlin Moncure mmonc...@gmail.com writes: It wouldn't bother me in the lest that if in plpgsql procedures if you had to set up and tear down a transaction on every line. It would once you noticed the performance impact ... regards, tom lane -- Sent via pgsql-hackers

Re: [HACKERS] stored procedures

2011-04-22 Thread Merlin Moncure
On Fri, Apr 22, 2011 at 9:29 AM, Tom Lane t...@sss.pgh.pa.us wrote: Merlin Moncure mmonc...@gmail.com writes: It wouldn't bother me in the lest that if in plpgsql procedures if you had to set up and tear down a transaction on every line. It would once you noticed the performance impact ...

Re: [HACKERS] stored procedures

2011-04-22 Thread Kevin Grittner
Merlin Moncure mmonc...@gmail.com wrote: Tom Lane t...@sss.pgh.pa.us wrote: You can't have arithmetic, comparisons, or much of anything outside a transaction with plpgsql. That model just plain doesn't work for this purpose, I think. You really want a control language that's independent

Re: [HACKERS] stored procedures

2011-04-22 Thread Tom Lane
Kevin Grittner kevin.gritt...@wicourts.gov writes: Merlin Moncure mmonc...@gmail.com wrote: wouldn't it be better if the current crop of language handlers could run procedures without major changes? C functions with SPI? However it's internally implemented, the more userland mindspace

Re: [HACKERS] stored procedures

2011-04-22 Thread Merlin Moncure
On Fri, Apr 22, 2011 at 10:10 AM, Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: Merlin Moncure mmonc...@gmail.com wrote: wouldn't it be better if the current crop of language handlers could run procedures without major changes?  C functions with SPI?

Re: [HACKERS] stored procedures

2011-04-22 Thread Robert Haas
On Apr 22, 2011, at 11:10 AM, Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: Merlin Moncure mmonc...@gmail.com wrote: wouldn't it be better if the current crop of language handlers could run procedures without major changes? C functions with SPI?

Re: [HACKERS] stored procedures

2011-04-22 Thread Kevin Grittner
Robert Haas robertmh...@gmail.com wrote: On Apr 22, 2011, at 11:10 AM, Tom Lane t...@sss.pgh.pa.us wrote: I'd like a pony, too. No ponies for me; make mine an Arabian stallion. Let's be perfectly clear about this: there is no part of plpgsql that can run outside a transaction today, and

Re: [HACKERS] stored procedures

2011-04-22 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Apr 22, 2011, at 11:10 AM, Tom Lane t...@sss.pgh.pa.us wrote: I'd like a pony, too. Let's be perfectly clear about this: there is no part of plpgsql that can run outside a transaction today, and probably no part of the other PLs either, and

Re: [HACKERS] stored procedures

2011-04-22 Thread Joshua Berkus
Tom, I'd like a pony, too. Let's be perfectly clear about this: there is no part of plpgsql that can run outside a transaction today, and probably no part of the other PLs either, and changing that without major changes is wishful thinking of the first order. I always thought that it

Re: [HACKERS] stored procedures

2011-04-22 Thread Merlin Moncure
On Fri, Apr 22, 2011 at 11:06 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Apr 22, 2011, at 11:10 AM, Tom Lane t...@sss.pgh.pa.us wrote: I'd like a pony, too.  Let's be perfectly clear about this: there is no part of plpgsql that can run outside a

Re: [HACKERS] stored procedures

2011-04-22 Thread Andrew Dunstan
On 04/22/2011 12:06 PM, Tom Lane wrote: You could possibly lobotomize plpgsql down to a small number of datatypes and operators that are known not to ever do anything more interesting than palloc() and elog(), but IMO the usefulness would be low and the fragility high. It'd be better to give

Re: [HACKERS] stored procedures

2011-04-22 Thread Peter Eisentraut
On fre, 2011-04-22 at 08:37 -0500, Merlin Moncure wrote: It wouldn't bother me in the lest that if in plpgsql procedures if you had to set up and tear down a transaction on every line. It would probably be more reasonable and feasible to have a setup where you can end a transaction in plpgsql

Re: [HACKERS] stored procedures

2011-04-22 Thread Merlin Moncure
On Fri, Apr 22, 2011 at 1:28 PM, Peter Eisentraut pete...@gmx.net wrote: On fre, 2011-04-22 at 08:37 -0500, Merlin Moncure wrote: It wouldn't bother me in the lest that if in plpgsql procedures if you had to set up and tear down a transaction on every line. It would probably be more

Re: [HACKERS] stored procedures

2011-04-22 Thread Tom Lane
Merlin Moncure mmonc...@gmail.com writes: On Fri, Apr 22, 2011 at 1:28 PM, Peter Eisentraut pete...@gmx.net wrote: It would probably be more reasonable and feasible to have a setup where you can end a transaction in plpgsql but a new one would start right away. ya, that's an idea. Yeah,

Re: [HACKERS] stored procedures

2011-04-22 Thread Merlin Moncure
On Fri, Apr 22, 2011 at 3:50 PM, Tom Lane t...@sss.pgh.pa.us wrote: Merlin Moncure mmonc...@gmail.com writes: On Fri, Apr 22, 2011 at 1:28 PM, Peter Eisentraut pete...@gmx.net wrote: It would probably be more reasonable and feasible to have a setup where you can end a transaction in plpgsql

Re: [HACKERS] stored procedures

2011-04-22 Thread Kevin Grittner
Merlin Moncure mmonc...@gmail.com wrote: hm, another neat thing about this is that it skirts the unfortunate confusion between sql 'begin' and plpgsql 'begin'... I hadn't thought about that. There is the SQL-standard START TRANSACTION synonym, so there is a way to deal with it -- but since

Re: [HACKERS] stored procedures

2011-04-22 Thread David Christensen
On Apr 22, 2011, at 3:50 PM, Tom Lane wrote: Merlin Moncure mmonc...@gmail.com writes: On Fri, Apr 22, 2011 at 1:28 PM, Peter Eisentraut pete...@gmx.net wrote: It would probably be more reasonable and feasible to have a setup where you can end a transaction in plpgsql but a new one would

[HACKERS] stored procedures

2011-04-21 Thread Peter Eisentraut
So the topic of real stored procedures came up again. Meaning a function-like object that executes outside of a regular transaction, with the ability to start and stop SQL transactions itself. I would like to collect some specs on this feature. So does anyone have links to documentation of

Re: [HACKERS] stored procedures

2011-04-21 Thread Pavel Stehule
Hi Peter 2011/4/21 Peter Eisentraut pete...@gmx.net: So the topic of real stored procedures came up again.  Meaning a function-like object that executes outside of a regular transaction, with the ability to start and stop SQL transactions itself. I would like to collect some specs on this

Re: [HACKERS] stored procedures

2011-04-21 Thread Robert Haas
On Thu, Apr 21, 2011 at 11:24 AM, Peter Eisentraut pete...@gmx.net wrote: So the topic of real stored procedures came up again.  Meaning a function-like object that executes outside of a regular transaction, with the ability to start and stop SQL transactions itself. I would like to collect

Re: [HACKERS] stored procedures

2011-04-21 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: EDB has an implementation of this in Advanced Server. A stored procedure can issue a COMMIT, which commits the current transaction and begins a new one. This might or might not be what people are imagining for this feature. If we end up doing

Re: [HACKERS] stored procedures

2011-04-21 Thread Robert Haas
On Thu, Apr 21, 2011 at 12:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: EDB has an implementation of this in Advanced Server.  A stored procedure can issue a COMMIT, which commits the current transaction and begins a new one.  This might or might not be

Re: [HACKERS] stored procedures

2011-04-21 Thread Josh Berkus
Peter, I would like to collect some specs on this feature. So does anyone have links to documentation of existing implementations, or their own spec writeup? A lot of people appear to have a very clear idea of this concept in their own head, so let's start collecting those. Delta between

Re: [HACKERS] stored procedures

2011-04-21 Thread Pavel Stehule
Hello 2011/4/21 Josh Berkus j...@agliodbs.com: Peter, I would like to collect some specs on this feature.  So does anyone have links to documentation of existing implementations, or their own spec writeup?  A lot of people appear to have a very clear idea of this concept in their own head,

Re: [HACKERS] stored procedures

2011-04-21 Thread Kevin Grittner
I'm pretty close to agreement with Josh, I think. Josh Berkus j...@agliodbs.com wrote: Delta between SPs and Functions for PostgreSQL: * SPs are executed using CALL or EXECUTE, and not SELECT. Agreed, although some products will search for a matching procedure name if the start of a

Re: [HACKERS] stored procedures

2011-04-21 Thread Tom Lane
Kevin Grittner kevin.gritt...@wicourts.gov writes: Josh Berkus j...@agliodbs.com wrote: ** question: if an SP is called by another SP, what is its transaction context? Entering or leaving an SP should not start or end a transaction. That all sounds mighty hand-wavy and at serious risk of

Re: [HACKERS] stored procedures

2011-04-21 Thread Merlin Moncure
On Thu, Apr 21, 2011 at 1:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: 3. What sort of primitive operations do you expect the SP to be able to execute outside a transaction?  The plpgsql model where all the primitive operations are really SQL ain't gonna work. I'm less sure what to do about #3.  

Re: [HACKERS] stored procedures

2011-04-21 Thread Pavel Stehule
2011/4/21 Tom Lane t...@sss.pgh.pa.us: Kevin Grittner kevin.gritt...@wicourts.gov writes: Josh Berkus j...@agliodbs.com wrote: ** question: if an SP is called by another SP, what is its transaction context? Entering or leaving an SP should not start or end a transaction. That all sounds

Re: [HACKERS] stored procedures

2011-04-21 Thread Robert Haas
On Thu, Apr 21, 2011 at 2:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: Josh Berkus j...@agliodbs.com wrote: ** question: if an SP is called by another SP, what is its transaction context? Entering or leaving an SP should not start or end a

Re: [HACKERS] stored procedures

2011-04-21 Thread Merlin Moncure
On Thu, Apr 21, 2011 at 2:37 PM, Robert Haas robertmh...@gmail.com wrote: On Thu, Apr 21, 2011 at 2:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: Josh Berkus j...@agliodbs.com wrote: ** question: if an SP is called by another SP, what is its

Re: [HACKERS] stored procedures

2011-04-21 Thread Christopher Browne
On Thu, Apr 21, 2011 at 3:51 PM, Merlin Moncure mmonc...@gmail.com wrote: On Thu, Apr 21, 2011 at 2:37 PM, Robert Haas robertmh...@gmail.com wrote: On Thu, Apr 21, 2011 at 2:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: Josh Berkus

Re: [HACKERS] stored procedures

2011-04-21 Thread Darren Duncan
Peter Eisentraut wrote: So the topic of real stored procedures came up again. Meaning a function-like object that executes outside of a regular transaction, with the ability to start and stop SQL transactions itself. I would like to collect some specs on this feature. So does anyone have

Re: [HACKERS] stored procedures

2011-04-21 Thread Tom Lane
Merlin Moncure mmonc...@gmail.com writes: On Thu, Apr 21, 2011 at 1:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: 3. What sort of primitive operations do you expect the SP to be able to execute outside a transaction?  The plpgsql model where all the primitive operations are really SQL ain't gonna

Re: [HACKERS] stored procedures

2011-04-21 Thread Tatsuo Ishii
Robert Haas robertmh...@gmail.com writes: EDB has an implementation of this in Advanced Server. A stored procedure can issue a COMMIT, which commits the current transaction and begins a new one. This might or might not be what people are imagining for this feature. If we end up doing

Re: [HACKERS] stored procedures

2011-04-21 Thread Josh Berkus
On 4/21/11 3:07 PM, Tom Lane wrote: Maybe we should think about the SP controlling a second backend (or even multiple backends?) that's executing the transactional operations. dblink on steroids, as it were. This is how people are doing this now (using dblink I mean). -- Josh Berkus

Re: [HACKERS] stored procedures

2011-04-21 Thread Tom Lane
Josh Berkus j...@agliodbs.com writes: On 4/21/11 3:07 PM, Tom Lane wrote: Maybe we should think about the SP controlling a second backend (or even multiple backends?) that's executing the transactional operations. dblink on steroids, as it were. This is how people are doing this now (using

Re: [HACKERS] stored procedures

2011-04-21 Thread Robert Haas
On Apr 21, 2011, at 3:51 PM, Merlin Moncure mmonc...@gmail.com wrote: If you do it that (base it on AT) way, then you can't: 1) call any utility command (vacuum, etc) 2) run for an arbitrary amount of time 3) discard any locks (except advisory) 4) deal with serialization isolation/mvcc

Re: [HACKERS] stored procedures

2011-04-21 Thread Pavel Stehule
What about cancelling? Cancel the current running query, or the whole procedure (I'm assuming the latter?  How would that work? Good question.  If you're imagining that the SP could decide to cancel a database request partway through, it seems even further afield from what could reasonably

[HACKERS] stored procedures to webservices

2007-12-15 Thread ivo nascimento
Hi everybody, I' m work on a software to create automatic webservices for stored procedure in any language. It's almost like the explain above: have one table pg_plwebservice Have one sp hello, develope in any languages like sql, plpgsql(trusted or untrusted)like for example. The DBA

Re: [HACKERS] stored procedures to webservices

2007-12-15 Thread Josh Berkus
Hey, Ivo, I' m work on a software to create automatic webservices for stored procedure in any language. Seems like the new XML and XLST support should fit in here somewhere. -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of

Re: [HACKERS] stored procedures to webservices

2007-12-15 Thread ivo nascimento
Hello Josh, the XML and XLST are data presentation only? the idea is provide some like one Http request where I can post data for a Stored procedure and receive one Http response using WSDL description and SOAP transport to implement the web service. Where can I find more info about this new

[HACKERS] Stored procedures - Oracle vs postgresql

2004-07-26 Thread Suresh Tri
Hi all, I am currently trying to estimate the effort required to implement Oracle type stored procedure in PostgreSQL. As I understood Oracle supports both functions and procedures, but postgres only functions. ALso there are no OUT parameter in postgres. I got some info from

Re: [HACKERS] Stored procedures - Oracle vs postgresql

2004-07-26 Thread Andreas Pflug
Suresh Tri wrote: Hi all, I am currently trying to estimate the effort required to implement Oracle type stored procedure in PostgreSQL. As I understood Oracle supports both functions and procedures, but postgres only functions. ALso there are no OUT parameter in postgres. I got some info from