Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit57d11ef

Browse files
committed
Check relkind before using TABLESAMPLE in postgres_fdw
Check the remote relkind before trying to use TABLESAMPLE to acquiresample from the remote relation. Even if the remote server version hasTABLESAMPLE support, the foreign table may point to incompatible relkind(e.g. a view or a sequence).If the relkind does not support TABLESAMPLE, error out if TABLESAMPLEwas requested specifically (as system/bernoulli), or fallback to randomjust like we do for old server versions.We currently end up disabling sampling for such relkind values anyway,due to reltuples being -1 or 1, but that seems rather accidental, andmight get broken by improving reltuples estimates, etc. So better tomake the check explicit.Reported-by: Tom LaneDiscussion:https://postgr.es/m/951485.1672461744%40sss.pgh.pa.us
1 parentd913928 commit57d11ef

File tree

3 files changed

+47
-22
lines changed

3 files changed

+47
-22
lines changed

‎contrib/postgres_fdw/deparse.c

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -2368,22 +2368,23 @@ deparseAnalyzeSizeSql(StringInfo buf, Relation rel)
23682368
}
23692369

23702370
/*
2371-
* Construct SELECT statement to acquire the number of rows of a relation.
2371+
* Construct SELECT statement to acquire the number of rows and the relkind of
2372+
* a relation.
23722373
*
23732374
* Note: we just return the remote server's reltuples value, which might
23742375
* be off a good deal, but it doesn't seem worth working harder. See
23752376
* comments in postgresAcquireSampleRowsFunc.
23762377
*/
23772378
void
2378-
deparseAnalyzeTuplesSql(StringInfobuf,Relationrel)
2379+
deparseAnalyzeInfoSql(StringInfobuf,Relationrel)
23792380
{
23802381
StringInfoDatarelname;
23812382

23822383
/* We'll need the remote relation name as a literal. */
23832384
initStringInfo(&relname);
23842385
deparseRelation(&relname,rel);
23852386

2386-
appendStringInfoString(buf,"SELECT reltuples FROM pg_catalog.pg_class WHERE oid = ");
2387+
appendStringInfoString(buf,"SELECT reltuples, relkind FROM pg_catalog.pg_class WHERE oid = ");
23872388
deparseStringLiteral(buf,relname.data);
23882389
appendStringInfoString(buf,"::pg_catalog.regclass");
23892390
}

‎contrib/postgres_fdw/postgres_fdw.c

Lines changed: 42 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -4974,18 +4974,25 @@ postgresAnalyzeForeignTable(Relation relation,
49744974
}
49754975

49764976
/*
4977-
*postgresCountTuplesForForeignTable
4977+
*postgresGetAnalyzeInfoForForeignTable
49784978
*Count tuples in foreign table (just get pg_class.reltuples).
4979+
*
4980+
* can_tablesample determines if the remote relation supports acquiring the
4981+
* sample using TABLESAMPLE.
49794982
*/
49804983
staticdouble
4981-
postgresCountTuplesForForeignTable(Relationrelation)
4984+
postgresGetAnalyzeInfoForForeignTable(Relationrelation,bool*can_tablesample)
49824985
{
49834986
ForeignTable*table;
49844987
UserMapping*user;
49854988
PGconn*conn;
49864989
StringInfoDatasql;
49874990
PGresult*volatileres=NULL;
49884991
volatiledoublereltuples=-1;
4992+
volatilecharrelkind=0;
4993+
4994+
/* assume the remote relation does not support TABLESAMPLE */
4995+
*can_tablesample= false;
49894996

49904997
/*
49914998
* Get the connection to use. We do the remote access as the table's
@@ -4999,7 +5006,7 @@ postgresCountTuplesForForeignTable(Relation relation)
49995006
* Construct command to get page count for relation.
50005007
*/
50015008
initStringInfo(&sql);
5002-
deparseAnalyzeTuplesSql(&sql,relation);
5009+
deparseAnalyzeInfoSql(&sql,relation);
50035010

50045011
/* In what follows, do not risk leaking any PGresults. */
50055012
PG_TRY();
@@ -5008,9 +5015,10 @@ postgresCountTuplesForForeignTable(Relation relation)
50085015
if (PQresultStatus(res)!=PGRES_TUPLES_OK)
50095016
pgfdw_report_error(ERROR,res,conn, false,sql.data);
50105017

5011-
if (PQntuples(res)!=1||PQnfields(res)!=1)
5018+
if (PQntuples(res)!=1||PQnfields(res)!=2)
50125019
elog(ERROR,"unexpected result from deparseAnalyzeTuplesSql query");
50135020
reltuples=strtod(PQgetvalue(res,0,0),NULL);
5021+
relkind=*(PQgetvalue(res,0,1));
50145022
}
50155023
PG_FINALLY();
50165024
{
@@ -5021,6 +5029,11 @@ postgresCountTuplesForForeignTable(Relation relation)
50215029

50225030
ReleaseConnection(conn);
50235031

5032+
/* TABLESAMPLE is supported only for regular tables and matviews */
5033+
*can_tablesample= (relkind==RELKIND_RELATION||
5034+
relkind==RELKIND_MATVIEW||
5035+
relkind==RELKIND_PARTITIONED_TABLE);
5036+
50245037
returnreltuples;
50255038
}
50265039

@@ -5147,27 +5160,25 @@ postgresAcquireSampleRowsFunc(Relation relation, int elevel,
51475160
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
51485161
errmsg("remote server does not support TABLESAMPLE feature")));
51495162

5150-
/*
5151-
* For "auto" method, pick the one we believe is best. For servers with
5152-
* TABLESAMPLE support we pick BERNOULLI, for old servers we fall-back to
5153-
* random() to at least reduce network transfer.
5154-
*/
5155-
if (method==ANALYZE_SAMPLE_AUTO)
5156-
{
5157-
if (server_version_num<95000)
5158-
method=ANALYZE_SAMPLE_RANDOM;
5159-
else
5160-
method=ANALYZE_SAMPLE_BERNOULLI;
5161-
}
5162-
51635163
/*
51645164
* If we've decided to do remote sampling, calculate the sampling rate. We
51655165
* need to get the number of tuples from the remote server, but skip that
51665166
* network round-trip if not needed.
51675167
*/
51685168
if (method!=ANALYZE_SAMPLE_OFF)
51695169
{
5170-
reltuples=postgresCountTuplesForForeignTable(relation);
5170+
boolcan_tablesample;
5171+
5172+
reltuples=postgresGetAnalyzeInfoForForeignTable(relation,
5173+
&can_tablesample);
5174+
5175+
/*
5176+
* Make sure we're not choosing TABLESAMPLE when the remote relation does
5177+
* not support that. But only do this for "auto" - if the user explicitly
5178+
* requested BERNOULLI/SYSTEM, it's better to fail.
5179+
*/
5180+
if (!can_tablesample&& (method==ANALYZE_SAMPLE_AUTO))
5181+
method=ANALYZE_SAMPLE_RANDOM;
51715182

51725183
/*
51735184
* Remote's reltuples could be 0 or -1 if the table has never been
@@ -5212,6 +5223,19 @@ postgresAcquireSampleRowsFunc(Relation relation, int elevel,
52125223
}
52135224
}
52145225

5226+
/*
5227+
* For "auto" method, pick the one we believe is best. For servers with
5228+
* TABLESAMPLE support we pick BERNOULLI, for old servers we fall-back to
5229+
* random() to at least reduce network transfer.
5230+
*/
5231+
if (method==ANALYZE_SAMPLE_AUTO)
5232+
{
5233+
if (server_version_num<95000)
5234+
method=ANALYZE_SAMPLE_RANDOM;
5235+
else
5236+
method=ANALYZE_SAMPLE_BERNOULLI;
5237+
}
5238+
52155239
/*
52165240
* Construct cursor that retrieves whole rows from remote.
52175241
*/

‎contrib/postgres_fdw/postgres_fdw.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -223,7 +223,7 @@ extern void deparseDirectDeleteSql(StringInfo buf, PlannerInfo *root,
223223
List*returningList,
224224
List**retrieved_attrs);
225225
externvoiddeparseAnalyzeSizeSql(StringInfobuf,Relationrel);
226-
externvoiddeparseAnalyzeTuplesSql(StringInfobuf,Relationrel);
226+
externvoiddeparseAnalyzeInfoSql(StringInfobuf,Relationrel);
227227
externvoiddeparseAnalyzeSql(StringInfobuf,Relationrel,
228228
PgFdwSamplingMethodsample_method,
229229
doublesample_frac,

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp