Menu
▾
▴
postgres-xc-developers
|
From: David E. W. <da...@ju...> - 2014-01-29 01:13:02
|
XCers,
I just tried to use pgTAP on XC and got this error:
dwheeler=# create extension pgtap;
ERROR: In XC, SQL functions cannot contain utility statements
CONTEXT: SQL function "_cleanup"
This is _cleanup():
CREATE OR REPLACE FUNCTION _cleanup()
RETURNS boolean AS $$
DROP TABLE __tresults__;
DROP SEQUENCE __tresults___numb_seq;
DROP TABLE __tcache__;
DROP SEQUENCE __tcache___id_seq;
SELECT TRUE;
$$ LANGUAGE sql;
These are temporary objects created by an EXECUTE statement in another function. Are they disallowed?
Google just turned up the XC source:
https://github.com/postgres-xc/postgres-xc/blob/master/src/backend/catalog/pg_proc.c#L897
Maybe those errors should be re-worded to indicate *what* utility statements are disallowed. Happy to contribute a patch if someone can list them.
Otherwise, I would be interested to figure out how I could work around this issue on XC.
Thanks,
David
|
|
From: Koichi S. <koi...@gm...> - 2014-01-29 04:00:25
|
Yes, utility statements are not allowed in functions and you cannot use temporary object if more than one node are involved in a transaction. The former may be fixed. Michael, do you have any mention to this restriction that utility statements are not allowed in functions? The background of the latter is the implicit 2PC. You can set enforce_two_phase_commit to OFF to use temporary object but this may come up with data inconsistency among the nodes. I believe we can allow temporary objects in such a transaction because temporary objects don't have to survive beyond the session. As long as only implicit 2PCs are involved, we may be able to allow temporary objects and can extend current XC core. Regards; --- Koichi Suzuki 2014-01-29 David E. Wheeler <da...@ju...>: > XCers, > > I just tried to use pgTAP on XC and got this error: > > dwheeler=# create extension pgtap; > ERROR: In XC, SQL functions cannot contain utility statements > CONTEXT: SQL function "_cleanup" > > This is _cleanup(): > > CREATE OR REPLACE FUNCTION _cleanup() > RETURNS boolean AS $$ > DROP TABLE __tresults__; > DROP SEQUENCE __tresults___numb_seq; > DROP TABLE __tcache__; > DROP SEQUENCE __tcache___id_seq; > SELECT TRUE; > $$ LANGUAGE sql; > > These are temporary objects created by an EXECUTE statement in another function. Are they disallowed? > > Google just turned up the XC source: > > https://github.com/postgres-xc/postgres-xc/blob/master/src/backend/catalog/pg_proc.c#L897 > > Maybe those errors should be re-worded to indicate *what* utility statements are disallowed. Happy to contribute a patch if someone can list them. > > Otherwise, I would be interested to figure out how I could work around this issue on XC. > > Thanks, > > David > > > ------------------------------------------------------------------------------ > WatchGuard Dimension instantly turns raw network data into actionable > security intelligence. It gives you real-time visual feedback on key > security issues and trends. Skip the complicated setup - simply import > a virtual appliance and go from zero to informed in seconds. > http://pubads.g.doubleclick.net/gampad/clk?id=123612991&iu=/4140/ostg.clktrk > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers |
|
From: Michael P. <mic...@gm...> - 2014-01-29 05:41:51
|
On Wed, Jan 29, 2014 at 1:00 PM, Koichi Suzuki <koi...@gm...> wrote: > Yes, utility statements are not allowed in functions and you cannot > use temporary object if more than one node are involved in a > transaction. > > The former may be fixed. Michael, do you have any mention to this > restriction that utility statements are not allowed in functions? If I recall correctly, this limitation is caused by ruleutils.c for query rebuilding. Regards, -- Michael |
|
From: David E. W. <da...@ju...> - 2014-01-29 04:03:32
|
On Jan 28, 2014, at 8:00 PM, Koichi Suzuki <koi...@gm...> wrote: > Yes, utility statements are not allowed in functions and you cannot > use temporary object if more than one node are involved in a > transaction. So: 1. What is a utility statement? 2. How does one limit the nodes on which something executes? > The background of the latter is the implicit 2PC. You can set > enforce_two_phase_commit to OFF to use temporary object but this may > come up with data inconsistency among the nodes. I believe we can > allow temporary objects in such a transaction because temporary > objects don't have to survive beyond the session. As long as only > implicit 2PCs are involved, we may be able to allow temporary objects > and can extend current XC core. Yeah, I only need a temporary table on the node on which stuff is executing, if that makes sense. Basically, for pgTAP tests, a transaction is started, temporary tables are created to store the test state, then the tests run, the state is shown to the user, and then the temporary tables are dropped (or allowed to disappear on disconnect). Thanks, David |
|
From: Michael P. <mic...@gm...> - 2014-01-29 05:42:32
|
On Wed, Jan 29, 2014 at 1:03 PM, David E. Wheeler <da...@ju...> wrote: > On Jan 28, 2014, at 8:00 PM, Koichi Suzuki <koi...@gm...> wrote: > >> Yes, utility statements are not allowed in functions and you cannot >> use temporary object if more than one node are involved in a >> transaction. > > So: > > 1. What is a utility statement? Stuff that goes through utility.c. -- Michael |
|
From: David E. W. <da...@ju...> - 2014-01-29 16:56:41
|
On Jan 28, 2014, at 9:42 PM, Michael Paquier <mic...@gm...> wrote: > Stuff that goes through utility.c. Okay, so then is there some way the error message can be rephrased so one can tell what sorts of commands those are? Thanks, David |
|
From: Koichi S. <koi...@gm...> - 2014-01-30 01:08:04
|
Thanks Michael for the prompt reply. Year, utility command should be those handled by utility.c, almost all the DDL statements. As to temporary objects in implicit 2PC, this can be a candidate for 1.3. Regards; --- Koichi Suzuki 2014-01-30 David E. Wheeler <da...@ju...>: > On Jan 28, 2014, at 9:42 PM, Michael Paquier <mic...@gm...> wrote: > >> Stuff that goes through utility.c. > > Okay, so then is there some way the error message can be rephrased so one can tell what sorts of commands those are? > > Thanks, > > David > |
|
From: David E. W. <da...@ju...> - 2014-01-30 01:13:35
|
On Jan 29, 2014, at 5:07 PM, Koichi Suzuki <koi...@gm...> wrote: > Year, utility command should be those handled by utility.c, almost all > the DDL statements. > > As to temporary objects in implicit 2PC, this can be a candidate for 1.3. So is there currently no way to create a temporary table from a function? What about inside a PL/pgSQL EXECUTE statement? Thanks, David |
|
From: Koichi S. <koi...@gm...> - 2014-02-03 05:21:53
|
As long as the restriction comes from utilities.c, I think PL/pgSQL will share the same restriction. Utilities.c should provide query rebuilding as we have for usual DML feature. I'm afraid it is not quite simple. Reagards; --- Koichi Suzuki 2014-01-30 David E. Wheeler <da...@ju...>: > On Jan 29, 2014, at 5:07 PM, Koichi Suzuki <koi...@gm...> wrote: > >> Year, utility command should be those handled by utility.c, almost all >> the DDL statements. >> >> As to temporary objects in implicit 2PC, this can be a candidate for 1.3. > > So is there currently no way to create a temporary table from a function? What about inside a PL/pgSQL EXECUTE statement? > > Thanks, > > David > |
|
From: Michael P. <mic...@gm...> - 2014-02-03 11:38:14
|
On Mon, Feb 3, 2014 at 2:21 PM, Koichi Suzuki <koi...@gm...> wrote: > As long as the restriction comes from utilities.c, I think PL/pgSQL > will share the same restriction. Utilities.c should provide query > rebuilding as we have for usual DML feature. I'm afraid it is not > quite simple. This might be deadly facilitated in 9.4 with the event trigger facility based on json objects that Alvaro is currently working on in this CF. -- Michael |
|
From: Koichi S. <koi...@gm...> - 2014-02-03 14:20:30
|
Good news. Then this restriction can be resolved in XC-1.3. Thanks. --- Koichi Suzuki 2014-02-03 Michael Paquier <mic...@gm...>: > On Mon, Feb 3, 2014 at 2:21 PM, Koichi Suzuki <koi...@gm...> wrote: >> As long as the restriction comes from utilities.c, I think PL/pgSQL >> will share the same restriction. Utilities.c should provide query >> rebuilding as we have for usual DML feature. I'm afraid it is not >> quite simple. > This might be deadly facilitated in 9.4 with the event trigger > facility based on json objects that Alvaro is currently working on in > this CF. > -- > Michael |
|
From: David E. W. <DA...@JU...> - 2014-02-04 00:49:02
|
On Feb 3, 2014, at 6:20 AM, Koichi Suzuki <koi...@gm...> wrote:
> Good news. Then this restriction can be resolved in XC-1.3.
Meanwhile, it looks like I can work around it by sticking this in the functions that create or drop temporary objects in EXECUTE statements:
SET LOCAL enforce_two_phase_commit = off;
Now, if only SAVEPOINTS were supported (we use a lot of exception-handling in functions), we would be set! Is SAVEPOINT support on the road map? I only see it mentioned as unsupported.
https://sourceforge.net/apps/mediawiki/postgres-xc/index.php?title=Roadmap
Thanks,
David
|
|
From: Koichi S. <koi...@gm...> - 2014-02-04 01:06:50
|
When you turn off enforce_two_phase_commit to off, you should be careful that if a transactions is involved with one node (coordinator and/or datanode), commit is not associated with prepare transaction and you may need manual database recovery if one of the commits fails. We're using this in the regression test mainly to maintain temporary object usage. We can distinguish such implicit 2PC from explicit ones so we may be able to enable TEMP objects in such case in 1.3 or later. Regards; --- Koichi Suzuki 2014-02-04 David E. Wheeler <DA...@ju...>: > On Feb 3, 2014, at 6:20 AM, Koichi Suzuki <koi...@gm...> wrote: > >> Good news. Then this restriction can be resolved in XC-1.3. > > Meanwhile, it looks like I can work around it by sticking this in the functions that create or drop temporary objects in EXECUTE statements: > > SET LOCAL enforce_two_phase_commit = off; > > Now, if only SAVEPOINTS were supported (we use a lot of exception-handling in functions), we would be set! Is SAVEPOINT support on the road map? I only see it mentioned as unsupported. > > https://sourceforge.net/apps/mediawiki/postgres-xc/index.php?title=Roadmap > > Thanks, > > David > |
|
From: David E. W. <da...@ju...> - 2014-02-04 01:15:35
|
On Feb 3, 2014, at 5:06 PM, Koichi Suzuki <koi...@gm...> wrote: > When you turn off enforce_two_phase_commit to off, you should be > careful that if a transactions is involved with one node (coordinator > and/or datanode), commit is not associated with prepare transaction > and you may need manual database recovery if one of the commits fails. > > We're using this in the regression test mainly to maintain temporary > object usage. We can distinguish such implicit 2PC from explicit > ones so we may be able to enable TEMP objects in such case in 1.3 or > later. Yeah, I just won’t ever be able to run pgTAP until proper temporary table and SAVEPOINT support is in. I can live with this, as I can always run my tests against Postgres, even if we deploy to XC. Should get to loading more data into my test cluster this week or next! Thanks, David |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X