Menu
▾
▴
postgres-xc-developers — Postgres-XC hackers and developers
You can subscribe to this list here.
| 2010 |
Jan
|
Feb
|
Mar
|
Apr
(10) |
May
(17) |
Jun
(3) |
Jul
|
Aug
|
Sep
(8) |
Oct
(18) |
Nov
(51) |
Dec
(74) |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2011 |
Jan
(47) |
Feb
(44) |
Mar
(44) |
Apr
(102) |
May
(35) |
Jun
(25) |
Jul
(56) |
Aug
(69) |
Sep
(32) |
Oct
(37) |
Nov
(31) |
Dec
(16) |
| 2012 |
Jan
(34) |
Feb
(127) |
Mar
(218) |
Apr
(252) |
May
(80) |
Jun
(137) |
Jul
(205) |
Aug
(159) |
Sep
(35) |
Oct
(50) |
Nov
(82) |
Dec
(52) |
| 2013 |
Jan
(107) |
Feb
(159) |
Mar
(118) |
Apr
(163) |
May
(151) |
Jun
(89) |
Jul
(106) |
Aug
(177) |
Sep
(49) |
Oct
(63) |
Nov
(46) |
Dec
(7) |
| 2014 |
Jan
(65) |
Feb
(128) |
Mar
(40) |
Apr
(11) |
May
(4) |
Jun
(8) |
Jul
(16) |
Aug
(11) |
Sep
(4) |
Oct
(1) |
Nov
(5) |
Dec
(16) |
| 2015 |
Jan
(5) |
Feb
|
Mar
(2) |
Apr
(5) |
May
(4) |
Jun
(12) |
Jul
|
Aug
|
Sep
|
Oct
|
Nov
|
Dec
(4) |
| 2019 |
Jan
|
Feb
|
Mar
|
Apr
|
May
|
Jun
|
Jul
(2) |
Aug
|
Sep
|
Oct
|
Nov
|
Dec
|
|
From: Ahsan H. <ahs...@en...> - 2014-02-05 06:59:40
|
Hi Ashutosh, You are going to review the patches from Masataka right? We need to include these changes for 1.2 GA. -- Ahsan On Mon, Feb 3, 2014 at 4:02 PM, Masataka Saito <pg...@gm...> wrote: > Hi, Ashutosh > > I made two patches in different approach. > > The borked plan is made from updating local_plan in the function before > arriving pgxc_locate_grouping_columns. The patch #07 attached backups > local_plan before it is re-written. Is this a right approach? > > As far as I'm concerned using PG_TRY to handle a non-error situation is > dirty. I also wrote patch #06 in another approach to handle this case. It > makes different alias which have the expression to have single > sortgroupref. Do you have any concern about this approach? > > Regards. > > > On 17 January 2014 16:47, Masataka Saito <pg...@gm...> wrote: > >> I would take option #1 if possible, but I think estimating impact of the >> fix and testing requires intimate knowledge of SQL and the optimizer: How >> the planner optimize a plan tree and what SQL could have two entries in a >> target list with the same value, How ressortgroup, resno, plan's target >> list and remote query's target list and base tlist is used by the >> optimizer and executor. >> It is still hard for me. >> >> My concern with 2nd option is how's the performance compared to the >> shipped case and how much case are there that SQLs is not optimized. >> I made some aggregation queries to test how's the query optimized with >> the attached patch, and I found that just returning false in >> pgxc_locate_grouping_columns() is not enough to fix this problem. >> >> For example, the next query generates a borked plan. >> postgres=# CREATE TABLE tbl AS SELECT * FROM (SELECT generate_series(1,3) >> as i) a, (SELECT generate_series(1,3) AS j) b; >> INSERT 0 9 >> postgres=# EXPLAIN VERBOSE SELECT i AS a, i AS b, avg(j) AS c FROM tblGROUP BY >> a ORDER BY b; >> QUERY PLAN >> >> >> --------------------------------------------------------------------------------------------------- >> GroupAggregate (cost=49.83..57.45 rows=10 width=8) >> Output: tbl.i, tbl.i, avg(tbl.i) >> -> Sort (cost=49.83..52.33 rows=1000 width=8) >> Output: tbl.i, tbl.i >> Sort Key: tbl.i >> -> Data Node Scan on tbl "_REMOTE_TABLE_QUERY_" (cost=0.00..0.00 >> rows=1000 width=8) >> Output: tbl.i, tbl.i, (avg(tbl.j)) >> Node/s: datanode1, datanode2 >> Remote query: SELECT i, pg_catalog.int8_avg(avg(j)) FROM >> ONLY public.tbl WHERE true >> (9 rows) >> >> Of course, it fails. >> postgres=# SELECT i AS a, i AS b, avg(j) AS c FROM tbl GROUP BY a ORDER >> BY b; >> ERROR: column "tbl.i" must appear in the GROUP BY clause or be used in >> an aggregate function >> >> In any case, this problem needs more study. >> >> Regards. >> >> >> >> On Wed, Jan 15, 2014 at 7:18 PM, Ashutosh Bapat < >> ash...@en...> wrote: >> >>> >>> >>> >>> On Wed, Jan 15, 2014 at 1:55 PM, Masataka Saito <pg...@gm...>wrote: >>> >>>> Hi Ashutosh, >>>> >>>> Specified groupColIdx doesn't found in tlist. >>>> >>>> It seems that groupColIdx is correct and tlist is wrong. >>>> Because the value of ressortgroupref in the target entry(base_tlist) >>>> they want to locate was zero. >>>> >>>> The mechanism why it comes to be zero is that when >>>> pgxc_rqplan_adjust_tlist updates base_tlist using >>>> pgxc_build_shippable_tlist, a ressortgroupref in a target entry is >>>> reset if a plan's target list has two equal Vars. >>>> >>>> >>> You have spotted the problem correctly. While writing this code, I >>> thought that having two entries in target list with same expression would >>> be problem when it comes to setting references (set_plan_ref), and hence >>> chose to disallow that. The assumption there was, if >>> locate_grouping_columns() doesn't find the column, it would return an >>> invalid value, and thus we can choose not to optimize that case. But >>> locate_grouping_columns() is actually throwing error, which breaks the >>> assumption. >>> >>> So, there are two possibilities >>> 1. bite the bullet and let there be two entries in the targetlist with >>> same expressions but different ressortgroupref and see if that approach has >>> any other problems - we will have to test a bunch of scenarios to make sure >>> that there is no problem there. >>> 2. Modify pgxc_locate_grouping_columns() to return NULL when no >>> expression is found with matching ressortgroupref. But that might need some >>> magic with PG_TRY() and CATCH or code duplication. >>> >>> >>>> It occurs when SELECT target has two aliases to the same column and >>>> each are referred by GROUP BY clause and ORDER BY clause. I will show >>>> a sample query at next. >>>> >>>> postgres-# CREATE TABLE tbl(i INT); >>>> postgres-# SELECT i AS a, i AS b FROM tbl GROUP BY a ORDER BY b; >>>> >>>> It needs more analysis, but I'm thinking how about using >>>> locate_grouping_columns with plan's target list and base_tlist instead >>>> of pgxc_locate_grouping_columns. >>>> >>>> Regards. >>>> >>>> >>>> On Thu, Nov 21, 2013 at 1:40 PM, Ashutosh Bapat < >>>> ash...@en...> wrote: >>>> >>>>> Hi Masataka, >>>>> To start debugging, you might want to fire this query with debugger >>>>> breaking on errstart() or errfinish() or some error reporting hook where >>>>> you can break. The stack stress should give you a lead as to where the >>>>> error is coming from. It's high chance that it's coming from >>>>> pgxc_locate_grouping_columns(). But anyway, start looking around the >>>>> callers of get_sortgroupref_tle() and check their arguments. Best luck. >>>>> >>>>> >>>>> On Thu, Nov 21, 2013 at 6:51 AM, Masataka Saito <pg...@gm...>wrote: >>>>> >>>>>> Thanks for your information. >>>>>> It seems very helpful. >>>>>> >>>>>> cx=# explain verbose SELECT DISTINCT t2.b FROM t1 JOIN t2 ON t1.id = >>>>>> t2.id GROUP BY b; >>>>>> ERROR: XX000: ORDER/GROUP BY expression not found in targetlist >>>>>> LOCATION: get_sortgroupref_tle, tlist.c:251 >>>>>> >>>>>> On Thu, Nov 21, 2013 at 9:53 AM, 鈴木 幸市 <ko...@in...> >>>>>> wrote: >>>>>> > Yes, you can do it with SET command as well just for a target >>>>>> statement. >>>>>> > >>>>>> > Regards; >>>>>> > --- >>>>>> > Koichi Suzuki >>>>>> > >>>>>> > 2013/11/21 7:46、Michael Paquier <mic...@gm...> のメール： >>>>>> > >>>>>> >> On Thu, Nov 21, 2013 at 12:32 AM, Masataka Saito <pg...@gm...> >>>>>> wrote: >>>>>> >>> XC can't build a plan. >>>>>> >>> >>>>>> >>> db=# explain verbose SELECT DISTINCT t2.b FROM t1 JOIN t2 ON >>>>>> t1.id = >>>>>> >>> t2.id GROUP BY b; >>>>>> >>> ERROR: ORDER/GROUP BY expression not found in targetlist >>>>>> >> This is a higher-level bug, planner bug just by looking at this >>>>>> code >>>>>> >> path src/backend/optimizer/util/tlist.c... >>>>>> >> >>>>>> >> Note: Setting up VERBOSITY to verbose in .psqlrc helps grabbing >>>>>> more >>>>>> >> details about the errors that occurred in server like the file >>>>>> name + >>>>>> >> name of this elog/ereport ERROR: >>>>>> >> \set VERBOSITY verbose >>>>>> >> For dev purposes it is a pretty useful default ;) >>>>>> >> >>>>>> >> Regards, >>>>>> >> -- >>>>>> >> Michael >>>>>> >> >>>>>> >> >>>>>> ------------------------------------------------------------------------------ >>>>>> >> Shape the Mobile Experience: Free Subscription >>>>>> >> Software experts and developers: Be at the forefront of tech >>>>>> innovation. >>>>>> >> Intel(R) Software Adrenaline delivers strategic insight and >>>>>> game-changing >>>>>> >> conversations that shape the rapidly evolving mobile landscape. >>>>>> Sign up now. >>>>>> >> >>>>>> http://pubads.g.doubleclick.net/gampad/clk?id=63431311&iu=/4140/ostg.clktrk >>>>>> >> _______________________________________________ >>>>>> >> Postgres-xc-developers mailing list >>>>>> >> Pos...@li... >>>>>> >> >>>>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers >>>>>> >> >>>>>> > >>>>>> >>>>>> >>>>>> ------------------------------------------------------------------------------ >>>>>> Shape the Mobile Experience: Free Subscription >>>>>> Software experts and developers: Be at the forefront of tech >>>>>> innovation. >>>>>> Intel(R) Software Adrenaline delivers strategic insight and >>>>>> game-changing >>>>>> conversations that shape the rapidly evolving mobile landscape. Sign >>>>>> up now. >>>>>> >>>>>> http://pubads.g.doubleclick.net/gampad/clk?id=63431311&iu=/4140/ostg.clktrk >>>>>> _______________________________________________ >>>>>> Postgres-xc-developers mailing list >>>>>> Pos...@li... >>>>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers >>>>>> >>>>> >>>>> >>>>> >>>>> -- >>>>> Best Wishes, >>>>> Ashutosh Bapat >>>>> EnterpriseDB Corporation >>>>> The Postgres Database Company >>>>> >>>> >>>> >>> >>> >>> -- >>> Best Wishes, >>> Ashutosh Bapat >>> EnterpriseDB Corporation >>> The Postgres Database Company >>> >> >> > > > ------------------------------------------------------------------------------ > Managing the Performance of Cloud-Based Applications > Take advantage of what the Cloud has to offer - Avoid Common Pitfalls. > Read the Whitepaper. > > http://pubads.g.doubleclick.net/gampad/clk?id=121051231&iu=/4140/ostg.clktrk > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > -- Ahsan Hadi Snr Director Product Development EnterpriseDB Corporation The Enterprise Postgres Company Phone: +92-51-8358874 Mobile: +92-333-5162114 Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message. |
|
From: Abbas B. <abb...@en...> - 2014-02-05 06:19:05
|
I had to change the insert query a little bit and had to do some dummy
inserts in projects and changes too, but the query worked fine for me. BTW
I tried on current master, I am not sure which version you are using to
test.
Here are the queries I tried.
After creating the three tables, I did
insert into projects values('?', '?', DEFAULT, '?', '?');
insert into changes values('?', '?', '?', '?', DEFAULT, '?', '?',
clock_timestamp(), '?', '?');
INSERT INTO tags (tag_id, tag, project, change_id, note, committer_name,
committer_email, planned_at, planner_name, planner_email)
SELECT tid, tg, proj, chid, n, name, email, at, pname, pemail FROM ( VALUES
('?', '?', '?', '?', '?', '?', '?', clock_timestamp()::timestamptz, '?',
'?')) i(tid, tg, proj, chid, n, name, email, at, pname, pemail) LEFT JOIN
tags ON i.tid = tags.tag_id WHERE tags.tag_id IS NULL;
Best Regards
On Wed, Feb 5, 2014 at 10:47 AM, David E. Wheeler <da...@ju...>wrote:
> On Feb 4, 2014, at 9:24 PM, Abbas Butt <abb...@en...>
> wrote:
>
> > Could you please send the table definition of tag? A CREATE TABLE
> statement would do.
>
> CREATE TABLE tags (
> tag_id TEXT PRIMARY KEY,
> tag TEXT NOT NULL,
> project TEXT NOT NULL REFERENCES projects(project) ON
> UPDATE CASCADE,
> change_id TEXT NOT NULL REFERENCES changes(change_id) ON
> UPDATE CASCADE,
> note TEXT NOT NULL DEFAULT '',
> committed_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
> committer_name TEXT NOT NULL,
> committer_email TEXT NOT NULL,
> planned_at TIMESTAMPTZ NOT NULL,
> planner_name TEXT NOT NULL,
> planner_email TEXT NOT NULL,
> UNIQUE(project, tag)
> ) DISTRIBUTE BY REPLICATION;
>
> Want projects and changes, too?
>
> CREATE TABLE projects (
> project TEXT PRIMARY KEY,
> uri TEXT NULL UNIQUE,
> created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
> creator_name TEXT NOT NULL,
> creator_email TEXT NOT NULL
> ) DISTRIBUTE BY REPLICATION;
>
> CREATE TABLE changes (
> change_id TEXT PRIMARY KEY,
> change TEXT NOT NULL,
> project TEXT NOT NULL REFERENCES projects(project) ON
> UPDATE CASCADE,
> note TEXT NOT NULL DEFAULT '',
> committed_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
> committer_name TEXT NOT NULL,
> committer_email TEXT NOT NULL,
> planned_at TIMESTAMPTZ NOT NULL,
> planner_name TEXT NOT NULL,
> planner_email TEXT NOT NULL
> ) DISTRIBUTE BY REPLICATION;
>
> Best,
>
> David
>
>
>
--
--
*Abbas*
Architect
Ph: 92.334.5100153
Skype ID: gabbasb
www.enterprisedb.co
<http://www.enterprisedb.com/>m<http://www.enterprisedb.com/>
*Follow us on Twitter*
@EnterpriseDB
Visit EnterpriseDB for tutorials, webinars,
whitepapers<http://www.enterprisedb.com/resources-community>and
more<http://www.enterprisedb.com/resources-community>
|
|
From: David E. W. <da...@ju...> - 2014-02-05 05:48:01
|
On Feb 4, 2014, at 9:24 PM, Abbas Butt <abb...@en...> wrote:
> Could you please send the table definition of tag? A CREATE TABLE statement would do.
CREATE TABLE tags (
tag_id TEXT PRIMARY KEY,
tag TEXT NOT NULL,
project TEXT NOT NULL REFERENCES projects(project) ON UPDATE CASCADE,
change_id TEXT NOT NULL REFERENCES changes(change_id) ON UPDATE CASCADE,
note TEXT NOT NULL DEFAULT '',
committed_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
committer_name TEXT NOT NULL,
committer_email TEXT NOT NULL,
planned_at TIMESTAMPTZ NOT NULL,
planner_name TEXT NOT NULL,
planner_email TEXT NOT NULL,
UNIQUE(project, tag)
) DISTRIBUTE BY REPLICATION;
Want projects and changes, too?
CREATE TABLE projects (
project TEXT PRIMARY KEY,
uri TEXT NULL UNIQUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
creator_name TEXT NOT NULL,
creator_email TEXT NOT NULL
) DISTRIBUTE BY REPLICATION;
CREATE TABLE changes (
change_id TEXT PRIMARY KEY,
change TEXT NOT NULL,
project TEXT NOT NULL REFERENCES projects(project) ON UPDATE CASCADE,
note TEXT NOT NULL DEFAULT '',
committed_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
committer_name TEXT NOT NULL,
committer_email TEXT NOT NULL,
planned_at TIMESTAMPTZ NOT NULL,
planner_name TEXT NOT NULL,
planner_email TEXT NOT NULL
) DISTRIBUTE BY REPLICATION;
Best,
David
|
|
From: Abbas B. <abb...@en...> - 2014-02-05 05:24:55
|
Could you please send the table definition of tag? A CREATE TABLE statement would do. On Wed, Feb 5, 2014 at 6:06 AM, David E. Wheeler <da...@ju...>wrote: > Hi there, > > I'd like to release an updated version of Sqitch, but would like to > resolve this issue, first. Anyone had a chance to look into it? > > Thanks, > > David > > On Jan 30, 2014, at 5:35 PM, Koichi Suzuki <koi...@gm...> wrote: > > > Ashutosh, do you have a time to look into it? > > > > 2014-01-31 David E. Wheeler <da...@ju...>: > >> XCers, > >> > >> Sqitch has a query that looks like this: > >> > >> INSERT INTO tags ( > >> tag_id > >> , tag > >> , project > >> , change_id > >> , note > >> , committer_name > >> , committer_email > >> , planned_at > >> , planner_name > >> , planner_email > >> ) > >> SELECT tid, tg, proj, chid, n, name, email, at, pname, > pemail FROM ( VALUES > >> (?, ?, ?, ?, ?, ?, ?, ?::timestamptz, ?, ?) > >> ) i(tid, tg, proj, chid, n, name, email, at, pname, pemail) > >> LEFT JOIN tags ON i.tid = tags.tag_id > >> WHERE tags.tag_id IS NULL > >> > >> Works fine on 8.4-9.3, but XC does not like it: > >> > >> ERROR: unexpected varno 6 in JOIN RTE 5 > >> > >> I have no idea what that means. I tried renaming some of the column > aliases in the VALUES subselect, but it made not difference. Any ideas? > > > > > ------------------------------------------------------------------------------ > Managing the Performance of Cloud-Based Applications > Take advantage of what the Cloud has to offer - Avoid Common Pitfalls. > Read the Whitepaper. > > http://pubads.g.doubleclick.net/gampad/clk?id=121051231&iu=/4140/ostg.clktrk > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > -- -- *Abbas* Architect Ph: 92.334.5100153 Skype ID: gabbasb www.enterprisedb.co <http://www.enterprisedb.com/>m<http://www.enterprisedb.com/> *Follow us on Twitter* @EnterpriseDB Visit EnterpriseDB for tutorials, webinars, whitepapers<http://www.enterprisedb.com/resources-community>and more<http://www.enterprisedb.com/resources-community> |
|
From: Michael P. <mic...@gm...> - 2014-02-05 01:56:18
|
On Wed, Feb 5, 2014 at 7:56 AM, David E. Wheeler <da...@ju...> wrote: > PGXC Hackers, > > What is the simplest way to tell if the server one has connected to is XC? Try to call pgxc_version()? `SHOW gtm_host`? Or is there something else to check, maybe something that doesn't throw an exception? How do you actually do for PG itself? Do you use PG_VERSION_NUM or similar? -- Michael |
|
From: Koichi S. <koi...@gm...> - 2014-02-05 01:36:55
|
Because XC didn't change libpq at all, I cannot find explicit way if you're connecting to PG or XC. Even psql binary from PG works with XC. I'd suggest to test if pgxc_class, pgxc_node and pgxc_nodegroup catalog is available, which are all XC specifyc. To avoid name conflict (you can create table pgxc_class in PG), you can specify pgxc_class oid value, which will be release-specific though. Regards; --- Koichi Suzuki 2014-02-05 David E. Wheeler <da...@ju...>: > On Feb 4, 2014, at 5:27 PM, Koichi Suzuki <koi...@gm...> wrote: > >> You can detect a coordinator in the same way as vanilla PostgreSQL through psql: >> >> SELECT 1; >> >> They work when GTM is up and the coordinator is connected to GTM. >> Anyway, without GTM up and running, the cluster won't work. >> >> Please note that pgxc_ctl monitor command goes directly to datanodes >> because it configures all the components. > > No, what I want to know is if I have connected to XC and not PG. > > D > |
|
From: David E. W. <da...@ju...> - 2014-02-05 01:28:59
|
On Feb 4, 2014, at 5:27 PM, Koichi Suzuki <koi...@gm...> wrote: > You can detect a coordinator in the same way as vanilla PostgreSQL through psql: > > SELECT 1; > > They work when GTM is up and the coordinator is connected to GTM. > Anyway, without GTM up and running, the cluster won't work. > > Please note that pgxc_ctl monitor command goes directly to datanodes > because it configures all the components. No, what I want to know is if I have connected to XC and not PG. D |
|
From: Koichi S. <koi...@gm...> - 2014-02-05 01:27:33
|
If you're detecting a datanode, you can do like this from any available coordinator through psql: EXECUTE DIRECT ON (nodename) 'SELECT 1'; If it is successful, the datanode is connected to the coordinator and GTM is up and running. You can detect a coordinator in the same way as vanilla PostgreSQL through psql: SELECT 1; They work when GTM is up and the coordinator is connected to GTM. Anyway, without GTM up and running, the cluster won't work. Please note that pgxc_ctl monitor command goes directly to datanodes because it configures all the components. Regards; --- Koichi Suzuki 2014-02-05 David E. Wheeler <da...@ju...>: > PGXC Hackers, > > What is the simplest way to tell if the server one has connected to is XC? Try to call pgxc_version()? `SHOW gtm_host`? Or is there something else to check, maybe something that doesn't throw an exception? > > Thanks, > > David > ------------------------------------------------------------------------------ > Managing the Performance of Cloud-Based Applications > Take advantage of what the Cloud has to offer - Avoid Common Pitfalls. > Read the Whitepaper. > http://pubads.g.doubleclick.net/gampad/clk?id=121051231&iu=/4140/ostg.clktrk > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers |
|
From: David E. W. <da...@ju...> - 2014-02-05 01:06:38
|
Hi there, I’d like to release an updated version of Sqitch, but would like to resolve this issue, first. Anyone had a chance to look into it? Thanks, David On Jan 30, 2014, at 5:35 PM, Koichi Suzuki <koi...@gm...> wrote: > Ashutosh, do you have a time to look into it? > > 2014-01-31 David E. Wheeler <da...@ju...>: >> XCers, >> >> Sqitch has a query that looks like this: >> >> INSERT INTO tags ( >> tag_id >> , tag >> , project >> , change_id >> , note >> , committer_name >> , committer_email >> , planned_at >> , planner_name >> , planner_email >> ) >> SELECT tid, tg, proj, chid, n, name, email, at, pname, pemail FROM ( VALUES >> (?, ?, ?, ?, ?, ?, ?, ?::timestamptz, ?, ?) >> ) i(tid, tg, proj, chid, n, name, email, at, pname, pemail) >> LEFT JOIN tags ON i.tid = tags.tag_id >> WHERE tags.tag_id IS NULL >> >> Works fine on 8.4-9.3, but XC does not like it: >> >> ERROR: unexpected varno 6 in JOIN RTE 5 >> >> I have no idea what that means. I tried renaming some of the column aliases in the VALUES subselect, but it made not difference. Any ideas? |
|
From: David E. W. <da...@ju...> - 2014-02-04 22:56:40
|
PGXC Hackers, What is the simplest way to tell if the server one has connected to is XC? Try to call pgxc_version()? `SHOW gtm_host`? Or is there something else to check, maybe something that doesn't throw an exception? Thanks, David |
|
From: Masaki H. <his...@la...> - 2014-02-04 02:21:22
|
Hi, Julian, > > I am thinking 3 choices as below: > > > > 1. Pacemaker and Corosync. > > I have little experience on Linux HA, so one week passed, I even can > > not install them successfully, including Pacemaker/Corosync/crmsh/resouce > agent. > > There are some website mentioned Pacemaker/corosync can help PGXC to > > build a HA infrastructure, but I can not find a comprehensive guide to > > do it. There are much more commponents in PGXC than PG, I think I > > should learn how to build it based on PG first. > > I know separate XC project to provide Pacemaker/Corosync resource > agent for XC. Please let me push them to provide info. We are planning to release resource agent for pacemaker/heartbeat within a few months. Basic idea is to manage pairs of Master-Slave for Datanode, Coordinator and GTM at each server by pacemaker. Hopefully this could be one of the solution to HA feature at XC. > > 2. Zookeeper > > It seems that Zookeeper has the ability to build a HA solution for > > PGXC, which have the similar function with Pacemaker, but I have to > > develop the heartbeat function for Zookeeper to > > start/stop/monitor/failover PGXC. And I do not know if my understand is > right. > > Sorry, I'm not familiar with Zookeeper. > > > 3. PGXC support HA internally. > > Because the table of pgxc_nodes in coordinator already have some > > information about the cluster, it can be enhanced to save the > > Master/Slave relations, it is replicated between all coordinators, > > then it can used as a CRM(Cluster Resource Management, as Pacemaker) > compoment. > > And the coordinator will connect to datanode/gtm/other coordinator in > > its regular work, so the heartbeat function exists natually. Even when > > the database is in the spare time, the coordinator can send a simple > > query as "select 1+1" to datanodes as the heartbeat ticks. > > What need to do is that, the coordinator will start a new process when > > starting, the new process will act as a heartbeat /resouce_agent to > > monitor the cluster status, and restart/failover once one commponent fails. How about monitoring coordinator and GTM? Do you have any idea? > > As my initial understanding, Choice 3 is better than Choice 2 which is > > better than Choice 1. But for the development effort, the order is > > reversed, Choice 1 is easy achieved based on current existing codes. What do we mean by better? My requirement is as follows : Availability : - Shorten Failure detection - Shorten downtime at Failover / Switchover Node management usability : - We can manage Slave node as well as Master node into XC Cluster - Enables node monitoring and management at psql - No need to install / configure external tools : pacemaker / colosync What else? Regards, Hisada > > I am very appreciated that you can share your advice with me. > > Yes, I do agree with this solution. I'd like to have this as a part > of XC release 1.3. > PGXC internal HA should be integrated with other monitoring feature such as > server hardware, power and network. > > It will be exciting to begin this discussion in this mailing list. > > Regards; > --- > Koichi Suzuki > > > > > Thanks > > Julian > > > > > > > > ---------------------------------------------------------------------- > > -------- CenturyLink Cloud: The Leader in Enterprise Cloud Services. > > Learn Why More Businesses Are Choosing CenturyLink Cloud For Critical > > Workloads, Development Environments & Everything In Between. > > Get a Quote or Start a Free Trial Today. > > http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg. > > clktrk _______________________________________________ > > Postgres-xc-developers mailing list > > Pos...@li... > > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > > > ------------------------------------------------------------------------- > ----- > 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.clk > trk > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers |
|
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 |
|
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 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-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: 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: Masataka S. <pg...@gm...> - 2014-02-03 11:03:00
|
Hi, Ashutosh I made two patches in different approach. The borked plan is made from updating local_plan in the function before arriving pgxc_locate_grouping_columns. The patch #07 attached backups local_plan before it is re-written. Is this a right approach? As far as I'm concerned using PG_TRY to handle a non-error situation is dirty. I also wrote patch #06 in another approach to handle this case. It makes different alias which have the expression to have single sortgroupref. Do you have any concern about this approach? Regards. On 17 January 2014 16:47, Masataka Saito <pg...@gm...> wrote: > I would take option #1 if possible, but I think estimating impact of the > fix and testing requires intimate knowledge of SQL and the optimizer: How > the planner optimize a plan tree and what SQL could have two entries in a > target list with the same value, How ressortgroup, resno, plan's target > list and remote query's target list and base tlist is used by the > optimizer and executor. > It is still hard for me. > > My concern with 2nd option is how's the performance compared to the > shipped case and how much case are there that SQLs is not optimized. > I made some aggregation queries to test how's the query optimized with the > attached patch, and I found that just returning false in > pgxc_locate_grouping_columns() is not enough to fix this problem. > > For example, the next query generates a borked plan. > postgres=# CREATE TABLE tbl AS SELECT * FROM (SELECT generate_series(1,3) > as i) a, (SELECT generate_series(1,3) AS j) b; > INSERT 0 9 > postgres=# EXPLAIN VERBOSE SELECT i AS a, i AS b, avg(j) AS c FROM tblGROUP BY > a ORDER BY b; > QUERY PLAN > > > --------------------------------------------------------------------------------------------------- > GroupAggregate (cost=49.83..57.45 rows=10 width=8) > Output: tbl.i, tbl.i, avg(tbl.i) > -> Sort (cost=49.83..52.33 rows=1000 width=8) > Output: tbl.i, tbl.i > Sort Key: tbl.i > -> Data Node Scan on tbl "_REMOTE_TABLE_QUERY_" (cost=0.00..0.00 > rows=1000 width=8) > Output: tbl.i, tbl.i, (avg(tbl.j)) > Node/s: datanode1, datanode2 > Remote query: SELECT i, pg_catalog.int8_avg(avg(j)) FROM > ONLY public.tbl WHERE true > (9 rows) > > Of course, it fails. > postgres=# SELECT i AS a, i AS b, avg(j) AS c FROM tbl GROUP BY a ORDER > BY b; > ERROR: column "tbl.i" must appear in the GROUP BY clause or be used in > an aggregate function > > In any case, this problem needs more study. > > Regards. > > > > On Wed, Jan 15, 2014 at 7:18 PM, Ashutosh Bapat < > ash...@en...> wrote: > >> >> >> >> On Wed, Jan 15, 2014 at 1:55 PM, Masataka Saito <pg...@gm...> wrote: >> >>> Hi Ashutosh, >>> >>> Specified groupColIdx doesn't found in tlist. >>> >>> It seems that groupColIdx is correct and tlist is wrong. >>> Because the value of ressortgroupref in the target entry(base_tlist) >>> they want to locate was zero. >>> >>> The mechanism why it comes to be zero is that when >>> pgxc_rqplan_adjust_tlist updates base_tlist using >>> pgxc_build_shippable_tlist, a ressortgroupref in a target entry is >>> reset if a plan's target list has two equal Vars. >>> >>> >> You have spotted the problem correctly. While writing this code, I >> thought that having two entries in target list with same expression would >> be problem when it comes to setting references (set_plan_ref), and hence >> chose to disallow that. The assumption there was, if >> locate_grouping_columns() doesn't find the column, it would return an >> invalid value, and thus we can choose not to optimize that case. But >> locate_grouping_columns() is actually throwing error, which breaks the >> assumption. >> >> So, there are two possibilities >> 1. bite the bullet and let there be two entries in the targetlist with >> same expressions but different ressortgroupref and see if that approach has >> any other problems - we will have to test a bunch of scenarios to make sure >> that there is no problem there. >> 2. Modify pgxc_locate_grouping_columns() to return NULL when no >> expression is found with matching ressortgroupref. But that might need some >> magic with PG_TRY() and CATCH or code duplication. >> >> >>> It occurs when SELECT target has two aliases to the same column and each >>> are referred by GROUP BY clause and ORDER BY clause. I will show a >>> sample query at next. >>> >>> postgres-# CREATE TABLE tbl(i INT); >>> postgres-# SELECT i AS a, i AS b FROM tbl GROUP BY a ORDER BY b; >>> >>> It needs more analysis, but I'm thinking how about using >>> locate_grouping_columns with plan's target list and base_tlist instead >>> of pgxc_locate_grouping_columns. >>> >>> Regards. >>> >>> >>> On Thu, Nov 21, 2013 at 1:40 PM, Ashutosh Bapat < >>> ash...@en...> wrote: >>> >>>> Hi Masataka, >>>> To start debugging, you might want to fire this query with debugger >>>> breaking on errstart() or errfinish() or some error reporting hook where >>>> you can break. The stack stress should give you a lead as to where the >>>> error is coming from. It's high chance that it's coming from >>>> pgxc_locate_grouping_columns(). But anyway, start looking around the >>>> callers of get_sortgroupref_tle() and check their arguments. Best luck. >>>> >>>> >>>> On Thu, Nov 21, 2013 at 6:51 AM, Masataka Saito <pg...@gm...>wrote: >>>> >>>>> Thanks for your information. >>>>> It seems very helpful. >>>>> >>>>> cx=# explain verbose SELECT DISTINCT t2.b FROM t1 JOIN t2 ON t1.id = >>>>> t2.id GROUP BY b; >>>>> ERROR: XX000: ORDER/GROUP BY expression not found in targetlist >>>>> LOCATION: get_sortgroupref_tle, tlist.c:251 >>>>> >>>>> On Thu, Nov 21, 2013 at 9:53 AM, 鈴木 幸市 <ko...@in...> >>>>> wrote: >>>>> > Yes, you can do it with SET command as well just for a target >>>>> statement. >>>>> > >>>>> > Regards; >>>>> > --- >>>>> > Koichi Suzuki >>>>> > >>>>> > 2013/11/21 7:46、Michael Paquier <mic...@gm...> のメール： >>>>> > >>>>> >> On Thu, Nov 21, 2013 at 12:32 AM, Masataka Saito <pg...@gm...> >>>>> wrote: >>>>> >>> XC can't build a plan. >>>>> >>> >>>>> >>> db=# explain verbose SELECT DISTINCT t2.b FROM t1 JOIN t2 ON t1.id= >>>>> >>> t2.id GROUP BY b; >>>>> >>> ERROR: ORDER/GROUP BY expression not found in targetlist >>>>> >> This is a higher-level bug, planner bug just by looking at this code >>>>> >> path src/backend/optimizer/util/tlist.c... >>>>> >> >>>>> >> Note: Setting up VERBOSITY to verbose in .psqlrc helps grabbing more >>>>> >> details about the errors that occurred in server like the file name >>>>> + >>>>> >> name of this elog/ereport ERROR: >>>>> >> \set VERBOSITY verbose >>>>> >> For dev purposes it is a pretty useful default ;) >>>>> >> >>>>> >> Regards, >>>>> >> -- >>>>> >> Michael >>>>> >> >>>>> >> >>>>> ------------------------------------------------------------------------------ >>>>> >> Shape the Mobile Experience: Free Subscription >>>>> >> Software experts and developers: Be at the forefront of tech >>>>> innovation. >>>>> >> Intel(R) Software Adrenaline delivers strategic insight and >>>>> game-changing >>>>> >> conversations that shape the rapidly evolving mobile landscape. >>>>> Sign up now. >>>>> >> >>>>> http://pubads.g.doubleclick.net/gampad/clk?id=63431311&iu=/4140/ostg.clktrk >>>>> >> _______________________________________________ >>>>> >> Postgres-xc-developers mailing list >>>>> >> Pos...@li... >>>>> >> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers >>>>> >> >>>>> > >>>>> >>>>> >>>>> ------------------------------------------------------------------------------ >>>>> Shape the Mobile Experience: Free Subscription >>>>> Software experts and developers: Be at the forefront of tech >>>>> innovation. >>>>> Intel(R) Software Adrenaline delivers strategic insight and >>>>> game-changing >>>>> conversations that shape the rapidly evolving mobile landscape. Sign >>>>> up now. >>>>> >>>>> http://pubads.g.doubleclick.net/gampad/clk?id=63431311&iu=/4140/ostg.clktrk >>>>> _______________________________________________ >>>>> Postgres-xc-developers mailing list >>>>> Pos...@li... >>>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers >>>>> >>>> >>>> >>>> >>>> -- >>>> Best Wishes, >>>> Ashutosh Bapat >>>> EnterpriseDB Corporation >>>> The Postgres Database Company >>>> >>> >>> >> >> >> -- >> Best Wishes, >> Ashutosh Bapat >> EnterpriseDB Corporation >> The Postgres Database Company >> > > |
|
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: Koichi S. <koi...@gm...> - 2014-02-03 05:17:22
|
Sorry for the late response. pgxc_pool_reload() is needed to synchronize cluster configuration change. Could you try CLEAN CONNECTION as found in http://postgres-xc.sourceforge.net/docs/1_1/sql-cleanconnection.html? BTW, as I wrote in the bug tracking #473, it does not seem to be search_path propagation, but another XC internal. Regards; --- Koichi Suzuki 2014-02-01 David E. Wheeler <da...@ju...>: > XCers, > > In order to work around the search_path bug (https://sourceforge.net/p/postgres-xc/bugs/473/), I added ALTER DATABASE commands to my deployment stuff to set the search path for the database to include all my schemas. However, this change does not affect users currently connected to the database -- including pooled connections to the data nodes. I thought that pgxc_pool_reload() would do the trick. Its docs: > >> pgxc_pool_reload reloads connection data cached in pooler from pgxc_node catalog and reloads all the information info cached in pooler. All the active transactions are aborted and all existing pooler connections are dropped. This results in having all the temporary and prepared objects dropped on remote and local node for session. > > So I called it, it returned true, and then I tried to insert into a table with a column of a type defined in a schema in the search path, but it failed, saying it could not find the type. So I connected to each data node and called pg_terminate_backend() on every connection to the database, then ran my insert statement again -- and it worked. > > So I guess that pgxc_pool_reload() does not disconnect from data nodes and reconnect. It keeps using existing connections. Is that how it should be? The docs say "all existing pooler connections are dropped", but such did not seem to be the case for me. Did I do it right? > > 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: Koichi S. <koi...@gm...> - 2014-02-03 01:33:01
|
Collector function is an intermediate function to collect each datanode's aggregate function result. For example, to calculate avg(), coordinator needs sum() and count() result from each datanode. Implementation example for this will be found in numeric_avg_collect() in backend/utils/adt/numeric.c Regards; --- Koichi Suzuki 2014-02-02 David E. Wheeler <da...@ju...>: > On Feb 1, 2014, at 12:12 AM, Koichi Suzuki <koi...@gm...> wrote: > >> You need to define collection function for an aggregate too. >> Description will be found at >> http://postgres-xc.sourceforge.net/docs/1_1/sql-createaggregate.html > > Hrm. Is there an example somewhere of how, say, a core aggregate function is ported to XC, including a collection function? Because I have no clue how to go about implementing one. > > Thanks, > > David |
|
From: David E. W. <da...@ju...> - 2014-02-01 23:11:47
|
On Feb 1, 2014, at 12:12 AM, Koichi Suzuki <koi...@gm...> wrote: > You need to define collection function for an aggregate too. > Description will be found at > http://postgres-xc.sourceforge.net/docs/1_1/sql-createaggregate.html Hrm. Is there an example somewhere of how, say, a core aggregate function is ported to XC, including a collection function? Because I have no clue how to go about implementing one. Thanks, David |
|
From: David E. W. <da...@ju...> - 2014-02-01 22:59:45
|
On Jan 30, 2014, at 7:41 PM, Ashutosh Bapat <ash...@en...> wrote: > Well, that seems a rather long way off, and RPMs can be renamed at that time, if appropriate. In the meantime, I think using the "xc11" suffix as I proposed is the way to go. Agreed? > > > That looks good. Okay, cool. Mason, do you have the spec file and related code and patches for your RPMs accessible somewhere? I would like to work with the pgrpms folks towards getting an XC community repo up and running. Thanks, David |
|
From: Koichi S. <koi...@gm...> - 2014-02-01 08:12:25
|
You need to define collection function for an aggregate too. Description will be found at http://postgres-xc.sourceforge.net/docs/1_1/sql-createaggregate.html Regards; --- Koichi Suzuki 2014-02-01 David E. Wheeler <da...@ju...>: > PGXC Hackers, > > I built and installed Andrew Dunstan's json_enhancements extension. It's a backport of the 9.3 JSON functions to 9.2. The build worked fine, but when I tried to create the extension I got an error: > > dwheeler=# create extension hstore; > CREATE EXTENSION > dwheeler=# create extension json_enhancements; > ERROR: unsafe use of pseudo-type "internal" > DETAIL: Transition type can not be "internal". > > I poked and prodded the file until I figured out the statement it didn't like. It's this one: > > CREATE AGGREGATE json_agg(anyelement) ( > SFUNC = json_agg_transfn, > FINALFUNC = json_agg_finalfn, > STYPE = internal > ); > > Looking at the code, I see that this is forbidden in XC: > > https://github.com/postgres-xc/postgres-xc/blob/master/src/backend/catalog/pg_aggregate.c#L95 > > Is there a workaround for this? > > 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: David E. W. <da...@ju...> - 2014-02-01 01:24:38
|
XCers, In order to work around the search_path bug (https://sourceforge.net/p/postgres-xc/bugs/473/), I added ALTER DATABASE commands to my deployment stuff to set the search path for the database to include all my schemas. However, this change does not affect users currently connected to the database -- including pooled connections to the data nodes. I thought that pgxc_pool_reload() would do the trick. Its docs: > pgxc_pool_reload reloads connection data cached in pooler from pgxc_node catalog and reloads all the information info cached in pooler. All the active transactions are aborted and all existing pooler connections are dropped. This results in having all the temporary and prepared objects dropped on remote and local node for session. So I called it, it returned true, and then I tried to insert into a table with a column of a type defined in a schema in the search path, but it failed, saying it could not find the type. So I connected to each data node and called pg_terminate_backend() on every connection to the database, then ran my insert statement again -- and it worked. So I guess that pgxc_pool_reload() does not disconnect from data nodes and reconnect. It keeps using existing connections. Is that how it should be? The docs say "all existing pooler connections are dropped", but such did not seem to be the case for me. Did I do it right? Thanks, David |
|
From: David E. W. <da...@ju...> - 2014-02-01 00:02:28
|
PGXC Hackers,
I built and installed Andrew Dunstan’s json_enhancements extension. It’s a backport of the 9.3 JSON functions to 9.2. The build worked fine, but when I tried to create the extension I got an error:
dwheeler=# create extension hstore;
CREATE EXTENSION
dwheeler=# create extension json_enhancements;
ERROR: unsafe use of pseudo-type "internal"
DETAIL: Transition type can not be "internal".
I poked and prodded the file until I figured out the statement it didn’t like. It’s this one:
CREATE AGGREGATE json_agg(anyelement) (
SFUNC = json_agg_transfn,
FINALFUNC = json_agg_finalfn,
STYPE = internal
);
Looking at the code, I see that this is forbidden in XC:
https://github.com/postgres-xc/postgres-xc/blob/master/src/backend/catalog/pg_aggregate.c#L95
Is there a workaround for this?
Thanks,
David
|
261 messages has been excluded from this view by a project administrator.
