Thread: [HACKERS] tablesample with partitioned tables
Attached patch fixes an oversight that tablesample cannot be used with partitioned tables: create table p (a int) partition by list (a); select * from p tablesample bernoulli (50); ERROR: TABLESAMPLE clause can only be applied to tables and materialized views Thanks, Amit -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Attachment
On Wed, Feb 22, 2017 at 04:51:46PM +0900, Amit Langote wrote: > Attached patch fixes an oversight that tablesample cannot be used with > partitioned tables: > > create table p (a int) partition by list (a); > select * from p tablesample bernoulli (50); > ERROR: TABLESAMPLE clause can only be applied to tables and materialized > views Thanks! Should the error message change somehow to reflect that partitioned tables are included? Is complete transparency of partitioned tables the goal, and reasonable in this context? Also, is there a good reason apart from tuits not to expand TABLESAMPLE to the rest of our SQL-visible relation structures? I'm guessing this could have something to do with the volatility they might have, whether in views that call volatile functions or in foreign tables that might not make the right guarantees... Best, David. -- David Fetter <david(at)fetter(dot)org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On 2017/02/23 0:54, David Fetter wrote: > On Wed, Feb 22, 2017 at 04:51:46PM +0900, Amit Langote wrote: >> Attached patch fixes an oversight that tablesample cannot be used with >> partitioned tables: >> >> create table p (a int) partition by list (a); >> select * from p tablesample bernoulli (50); >> ERROR: TABLESAMPLE clause can only be applied to tables and materialized >> views > > Thanks! > > Should the error message change somehow to reflect that partitioned > tables are included? Is complete transparency of partitioned tables > the goal, and reasonable in this context? We avoid mentioning partitioned tables separately during most of the errors caused by relkind checks. I mentioned recently [1] that in most of these sites such as this one, a table's being partitioned is not significant. > Also, is there a good reason apart from tuits not to expand > TABLESAMPLE to the rest of our SQL-visible relation structures? I'm > guessing this could have something to do with the volatility they > might have, whether in views that call volatile functions or in > foreign tables that might not make the right guarantees... I wouldn't be able to say much about that, but I found an email from the original discussion that occurred around development of this feature that posed the same question. There might be some answers there. [1] https://www.postgresql.org/message-id/854ad246-4dfa-5c68-19ad-867b6800f313%40lab.ntt.co.jp [2] https://www.postgresql.org/message-id/5526D369.1070905%40gmx.net