Menu
▾
▴
postgres-xc-developers
|
From: Pavan D. <pav...@gm...> - 2013-07-04 08:35:02
|
Hello All,
I tried to use generate_series() function to load a bunch of test data in a
table. It fails with the following error:
psql (PGXC 1.1devel, based on PG 9.2beta2)
Type "help" for help.
postgres=# CREATE TABLE testtbl(a int, b char(10));
CREATE TABLE
postgres=# \d+ testtbl
Table "public.testtbl"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------------+-----------+----------+--------------+-------------
a | integer | | plain | |
b | character(10) | | extended | |
Has OIDs: no
Distribute By: HASH(a)
Location Nodes: ALL DATANODES
postgres=# INSERT INTO testtbl VALUES (generate_series(1,10000), 'foo');
ERROR: set-valued function called in context that cannot accept a set
I thought we had fixed this issue long back, no ? I also tried to search
the list and it seems it used to work at some point. Am I doing something
wrong ? I'm using the master branch from the repository.
Thanks,
Pavan
--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee
|
|
From: 鈴木 幸市 <ko...@in...> - 2013-07-04 08:40:36
|
We've cleaned up some function I/F and it may influenced the case. Any more info? --- Koichi Suzuki On 2013/07/04, at 17:34, Pavan Deolasee <pav...@gm...> wrote: > Hello All, > > I tried to use generate_series() function to load a bunch of test data in a table. It fails with the following error: > > psql (PGXC 1.1devel, based on PG 9.2beta2) > Type "help" for help. > > postgres=# CREATE TABLE testtbl(a int, b char(10)); > CREATE TABLE > postgres=# \d+ testtbl > Table "public.testtbl" > Column | Type | Modifiers | Storage | Stats target | Description > --------+---------------+-----------+----------+--------------+------------- > a | integer | | plain | | > b | character(10) | | extended | | > Has OIDs: no > Distribute By: HASH(a) > Location Nodes: ALL DATANODES > > postgres=# INSERT INTO testtbl VALUES (generate_series(1,10000), 'foo'); > ERROR: set-valued function called in context that cannot accept a set > > I thought we had fixed this issue long back, no ? I also tried to search the list and it seems it used to work at some point. Am I doing something wrong ? I'm using the master branch from the repository. > > Thanks, > Pavan > -- > Pavan Deolasee > http://www.linkedin.com/in/pavandeolasee > ------------------------------------------------------------------------------ > This SF.net email is sponsored by Windows: > > Build for Windows Store. > > http://p.sf.net/sfu/windows-dev2dev_______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers |
|
From: Pavan D. <pav...@gm...> - 2013-07-04 08:43:18
|
On Thu, Jul 4, 2013 at 2:10 PM, 鈴木 幸市 <ko...@in...> wrote:
> We've cleaned up some function I/F and it may influenced the case.
>
> Any more info?
>
It seems we are trying to FQS this query, which is clearly wrong. The
INSERTs must go through the coordinator so that each row can be inserted in
the relevant datanode. If I turn FQS off, then the INSERT works fine.
postgres=# set enable_fast_query_shipping TO on;
SET
postgres=# EXPLAIN INSERT INTO testtbl VALUES (generate_series(1,10000),
'foo');
QUERY PLAN
----------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0)
Node expr: generate_series(1, 10000)
(2 rows)
postgres=# set enable_fast_query_shipping TO off;
SET
postgres=# EXPLAIN INSERT INTO testtbl VALUES (generate_series(1,10000),
'foo');
QUERY PLAN
-----------------------------------------------------
Insert on testtbl (cost=0.00..0.01 rows=1 width=0)
Node/s: d1, d2
Node expr: a
-> Result (cost=0.00..0.01 rows=1 width=0)
(4 rows)
Thanks,
Pavan
--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee
|
|
From: Michael P. <mic...@gm...> - 2013-07-04 09:51:40
|
On Thu, Jul 4, 2013 at 5:34 PM, Pavan Deolasee <pav...@gm...> wrote: > Hello All, > > I tried to use generate_series() function to load a bunch of test data in a > table. It fails with the following error: > > psql (PGXC 1.1devel, based on PG 9.2beta2) > Type "help" for help. > > postgres=# CREATE TABLE testtbl(a int, b char(10)); > CREATE TABLE > postgres=# \d+ testtbl > Table "public.testtbl" > Column | Type | Modifiers | Storage | Stats target | Description > --------+---------------+-----------+----------+--------------+------------- > a | integer | | plain | | > b | character(10) | | extended | | > Has OIDs: no > Distribute By: HASH(a) > Location Nodes: ALL DATANODES > > postgres=# INSERT INTO testtbl VALUES (generate_series(1,10000), 'foo'); > ERROR: set-valued function called in context that cannot accept a set > > I thought we had fixed this issue long back, no ? I also tried to search the > list and it seems it used to work at some point. Am I doing something wrong > ? I'm using the master branch from the repository. generate_series has has never been fixed for hash tables as far as I recall. -- Michael |
|
From: Pavan D. <pav...@gm...> - 2013-07-04 10:08:32
|
On Thu, Jul 4, 2013 at 3:21 PM, Michael Paquier <mic...@gm...>wrote: > > generate_series has has never been fixed for hash tables as far as I > recall. Hmm.. that could well be the case. I am immensely surprised and disappointed if that's true though. IMHO any PostgreSQL user will most likely fire that statement soon after creating a table to populate test data. It does not sound nice if the very first statement errors out. What stops us from supporting that, except of course lack of development time ? Is it a hard problem to crack ? I assume its the former because we just need to put it through the slow path instead of FQS, no ? Thanks, Pavan |
|
From: Nikhil S. <ni...@st...> - 2013-07-04 10:56:08
|
> Hmm.. that could well be the case. I am immensely surprised and > disappointed if that's true though. IMHO any PostgreSQL user will most > likely fire that statement soon after creating a table to populate test > data. It does not sound nice if the very first statement errors out. What > stops us from supporting that, except of course lack of development time ? > Is it a hard problem to crack ? I assume its the former because we just > need to put it through the slow path instead of FQS, no ? > > +1 I would have assumed that INSERTs would have been special cased to precisely allow the coordinator to decide to which datanodes to send the INSERT to. Regards, Nikhils > Thanks, > Pavan > > > ------------------------------------------------------------------------------ > This SF.net email is sponsored by Windows: > > Build for Windows Store. > > http://p.sf.net/sfu/windows-dev2dev > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > -- StormDB - http://www.stormdb.com The Database Cloud |
|
From: Michael P. <mic...@gm...> - 2013-07-04 13:20:45
|
On Thu, Jul 4, 2013 at 7:07 PM, Pavan Deolasee <pav...@gm...> wrote: > > On Thu, Jul 4, 2013 at 3:21 PM, Michael Paquier <mic...@gm...> >> generate_series has never been fixed for hash tables as far as I recall. > Hmm.. that could well be the case. I am immensely surprised and disappointed > if that's true though. IMHO any PostgreSQL user will most likely fire that > statement soon after creating a table to populate test data. It does not > sound nice if the very first statement errors out. +1. > What stops us from supporting that, except of course lack of development time? That and the fact that such exceptions have never been considered a high priority, I suppose. generate_series is the particular case of an immutable function that cannot be FQS'ed for a non-replicated table. I might be wrong, but I think it worked correctly for roundrobin. > Is it a hard problem to crack ? No I don't think so, but I've never taken the time to look at it. > I assume its the former because we just need to put it > through the slow path instead of FQS, no ? Yes, this is the case, it takes, or should take the slow path, and this even if the function is immutable. I recall that the output of EXPLAIN VERBOSE works correctly. -- Michael |
|
From: 鈴木 幸市 <ko...@in...> - 2013-07-05 00:56:17
|
It is now in the bug tracker, ID is 3614635. I'm afraid it's hard to include this improvement in 1.1.0 but will be fixed in the following releases. I understand this has high priority. Regards; --- Koichi Suzuki On 2013/07/04, at 22:20, Michael Paquier <mic...@gm...> wrote: > On Thu, Jul 4, 2013 at 7:07 PM, Pavan Deolasee <pav...@gm...> wrote: >> >> On Thu, Jul 4, 2013 at 3:21 PM, Michael Paquier <mic...@gm...> >>> generate_series has never been fixed for hash tables as far as I recall. >> Hmm.. that could well be the case. I am immensely surprised and disappointed >> if that's true though. IMHO any PostgreSQL user will most likely fire that >> statement soon after creating a table to populate test data. It does not >> sound nice if the very first statement errors out. > +1. > >> What stops us from supporting that, except of course lack of development time? > That and the fact that such exceptions have never been considered a > high priority, I suppose. generate_series is the particular case of an > immutable function that cannot be FQS'ed for a non-replicated table. I > might be wrong, but I think it worked correctly for roundrobin. > >> Is it a hard problem to crack ? > No I don't think so, but I've never taken the time to look at it. > >> I assume its the former because we just need to put it >> through the slow path instead of FQS, no ? > Yes, this is the case, it takes, or should take the slow path, and > this even if the function is immutable. I recall that the output of > EXPLAIN VERBOSE works correctly. > -- > Michael > > ------------------------------------------------------------------------------ > This SF.net email is sponsored by Windows: > > Build for Windows Store. > > http://p.sf.net/sfu/windows-dev2dev > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > |
|
From: Pavan D. <pav...@gm...> - 2013-07-05 04:27:00
|
On Fri, Jul 5, 2013 at 6:26 AM, 鈴木 幸市 <ko...@in...> wrote: > It is now in the bug tracker, ID is 3614635. I'm afraid it's hard to > include this improvement in 1.1.0 but will be fixed in the following > releases. I understand this has high priority. > Thank you for adding it to the tracker. Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee |
|
From: Ashutosh B. <ash...@en...> - 2013-07-05 04:23:09
|
On Thu, Jul 4, 2013 at 6:50 PM, Michael Paquier <mic...@gm...>wrote: > On Thu, Jul 4, 2013 at 7:07 PM, Pavan Deolasee <pav...@gm...> > wrote: > > > > On Thu, Jul 4, 2013 at 3:21 PM, Michael Paquier < > mic...@gm...> > >> generate_series has never been fixed for hash tables as far as I recall. > > Hmm.. that could well be the case. I am immensely surprised and > disappointed > > if that's true though. IMHO any PostgreSQL user will most likely fire > that > > statement soon after creating a table to populate test data. It does not > > sound nice if the very first statement errors out. > +1. > > > What stops us from supporting that, except of course lack of development > time? > That and the fact that such exceptions have never been considered a > high priority, I suppose. generate_series is the particular case of an > immutable function that cannot be FQS'ed for a non-replicated table. I > might be wrong, but I think it worked correctly for roundrobin. > > I don't agree that it's because of immutability. It has more to do with the VALUES_RTE in INSERT statement. Any INSERT statement that is inserting more than one value in the distributed table can not be FQSed. In case there are more than one value being INSERTED through an INSERT statement, there are two RTEs in the query, one for the relation where INSERT is happening, and other for the values. So, I had implemented a simple rule in FQS shippability - if there are more than one RTEs in INSERT statement, do not FQS it. But I am unable to locate this rule now. Looks like while implementing something else, the rule has disappeared. And, since we do not have test for it, we didn't notice it. > > Is it a hard problem to crack ? > No I don't think so, but I've never taken the time to look at it. > > > I assume its the former because we just need to put it > > through the slow path instead of FQS, no ? > Yes, this is the case, it takes, or should take the slow path, and > this even if the function is immutable. I recall that the output of > EXPLAIN VERBOSE works correctly. > -- > Michael > > > ------------------------------------------------------------------------------ > This SF.net email is sponsored by Windows: > > Build for Windows Store. > > http://p.sf.net/sfu/windows-dev2dev > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > -- Best Wishes, Ashutosh Bapat EntepriseDB Corporation The Postgres Database Company |
|
From: Michael P. <mic...@gm...> - 2013-07-05 05:01:37
|
On Fri, Jul 5, 2013 at 1:23 PM, Ashutosh Bapat <ash...@en...> wrote: > > > > On Thu, Jul 4, 2013 at 6:50 PM, Michael Paquier <mic...@gm...> > wrote: >> >> On Thu, Jul 4, 2013 at 7:07 PM, Pavan Deolasee <pav...@gm...> >> wrote: >> > >> > On Thu, Jul 4, 2013 at 3:21 PM, Michael Paquier >> > <mic...@gm...> >> >> generate_series has never been fixed for hash tables as far as I >> >> recall. >> > Hmm.. that could well be the case. I am immensely surprised and >> > disappointed >> > if that's true though. IMHO any PostgreSQL user will most likely fire >> > that >> > statement soon after creating a table to populate test data. It does not >> > sound nice if the very first statement errors out. >> +1. >> >> > What stops us from supporting that, except of course lack of development >> > time? >> That and the fact that such exceptions have never been considered a >> high priority, I suppose. generate_series is the particular case of an >> immutable function that cannot be FQS'ed for a non-replicated table. I >> might be wrong, but I think it worked correctly for roundrobin. >> > > I don't agree that it's because of immutability. It has more to do with the > VALUES_RTE in INSERT statement. Any INSERT statement that is inserting more > than one value in the distributed table can not be FQSed. In case there are > more than one value being INSERTED through an INSERT statement, there are > two RTEs in the query, one for the relation where INSERT is happening, and > other for the values. So, I had implemented a simple rule in FQS > shippability - if there are more than one RTEs in INSERT statement, do not > FQS it. But I am unable to locate this rule now. Looks like while > implementing something else, the rule has disappeared. And, since we do not > have test for it, we didn't notice it. Yeah you are right, sorry. This is not related to the immutability but to the fact that the distribution column is used for such an INSERT. -- Michael |
|
From: Pavan D. <pav...@gm...> - 2013-07-05 04:34:11
|
On Fri, Jul 5, 2013 at 9:53 AM, Ashutosh Bapat < ash...@en...> wrote: > > > > On Thu, Jul 4, 2013 at 6:50 PM, Michael Paquier <mic...@gm... > > wrote: > >> >> >> > I don't agree that it's because of immutability. It has more to do with > the VALUES_RTE in INSERT statement. Any INSERT statement that is inserting > more than one value in the distributed table can not be FQSed. In case > there are more than one value being INSERTED through an INSERT statement, > there are two RTEs in the query, one for the relation where INSERT is > happening, and other for the values. So, I had implemented a simple rule in > FQS shippability - if there are more than one RTEs in INSERT statement, do > not FQS it. But I am unable to locate this rule now. Looks like while > implementing something else, the rule has disappeared. And, since we do not > have test for it, we didn't notice it. > > You mean this code in pgxcship.c ? 905 906 /* PGXCTODO: It should be possible to look at the Query and find out 907 * whether it can be completely evaluated on the Datanode just like SELECT 908 * queries. But we need to be careful while finding out the Datanodes to 909 * execute the query on, esp. for the result relations. If one happens to 910 * remove/change this restriction, make sure you change 911 * pgxc_FQS_get_relation_nodes appropriately. 912 * For now DMLs with single rtable entry are candidates for FQS 913 */ 914 if (query->commandType != CMD_SELECT && list_length(query->rtable) > 1) 915 pgxc_set_shippability_reason(sc_context, SS_UNSUPPORTED_EXPR); BTW, I looked at the regression and I spotted at least one test case that use generate_series() to insert data in a distributed table but still does not fail. Its so because the test case uses a subquery to fire the insert. So a workaround to the failing query is: INSERT INTO testtbl SELECT generate_series(1,10000), 'foo'; Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee |
|
From: Ashutosh B. <ash...@en...> - 2013-07-05 04:56:41
|
On Fri, Jul 5, 2013 at 10:03 AM, Pavan Deolasee <pav...@gm...>wrote: > > > > On Fri, Jul 5, 2013 at 9:53 AM, Ashutosh Bapat < > ash...@en...> wrote: > >> >> >> >> On Thu, Jul 4, 2013 at 6:50 PM, Michael Paquier < >> mic...@gm...> wrote: >> >>> >>> >>> >> I don't agree that it's because of immutability. It has more to do with >> the VALUES_RTE in INSERT statement. Any INSERT statement that is inserting >> more than one value in the distributed table can not be FQSed. In case >> there are more than one value being INSERTED through an INSERT statement, >> there are two RTEs in the query, one for the relation where INSERT is >> happening, and other for the values. So, I had implemented a simple rule in >> FQS shippability - if there are more than one RTEs in INSERT statement, do >> not FQS it. But I am unable to locate this rule now. Looks like while >> implementing something else, the rule has disappeared. And, since we do not >> have test for it, we didn't notice it. >> >> > > > You mean this code in pgxcship.c ? > > 905 > 906 /* PGXCTODO: It should be possible to look at the Query > and find out > 907 * whether it can be completely evaluated on the Datanode > just like SELECT > 908 * queries. But we need to be careful while finding out > the Datanodes to > 909 * execute the query on, esp. for the result relations. > If one happens to > 910 * remove/change this restriction, make sure you change > 911 * pgxc_FQS_get_relation_nodes appropriately. > 912 * For now DMLs with single rtable entry are candidates > for FQS > 913 */ > 914 if (query->commandType != CMD_SELECT && > list_length(query->rtable) > 1) > 915 pgxc_set_shippability_reason(sc_context, > SS_UNSUPPORTED_EXPR); > > Ah, right. I was searching for CMD_INSERT so couldn't locate it. Yes, this is stronger and better condition not to FQS any DML with more than one RTE. I am not sure why it doesn't obey VALUES generate_series()? May it doesn't show up as an RTE. > BTW, I looked at the regression and I spotted at least one test case that > use generate_series() to insert data in a distributed table but still does > not fail. Its so because the test case uses a subquery to fire the insert. > So a workaround to the failing query is: > > INSERT INTO testtbl SELECT generate_series(1,10000), 'foo'; > > Thanks, > Pavan > -- > Pavan Deolasee > http://www.linkedin.com/in/pavandeolasee > -- Best Wishes, Ashutosh Bapat EntepriseDB Corporation The Postgres Database Company |
×
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