CREATE TABLE () INHERITS not supported #5956
Assignees
Labels
area/ysql
Yugabyte SQL (YSQL)
kind/enhancement
This is an enhancement of an existing feature
priority/medium
Medium priority issue
Comments
|
Hi, Any updates about INHERITS support? |
|
Hello same question, Any updates about INHERITS support ? |
|
I'm an official Odoo partner who'd also be interested in that feature 😃 |
yugabyte-ci
added
kind/bug
yugabyte-ci
added
kind/enhancement
|
Hi, any news ? We are needing it |
|
same here :) |
|
Hello, any news about fixing this ? |
devansh-ism
pushed a commit
that referenced
this issue
May 6, 2024
Summary:
This diff adds the support for `pg_stat_statements` with an extra column of `yb_latency_histogram` on top of version 1.10. Add a new version `1.10-yb-1.0` with upgrade path from `1.6-yb-1.0` to `1.10-yb-1.0`.
`pg_stat_statements` retains the statistics data by saving the data in a file on the disk (at `pg_stat/pg_stat_statements.stat`) during PostgreSQL shutdown and reads the file back at the startup to repopulate the entries. Using this mechanism allows PostgreSQL server to retain the data across graceful restart of the server. This diff supports this behaviour for PG 15.2. Retention of data and repopulating of shared memory during major version upgrade (from PG 11.2 to PG 15.2) will be addressed in separate diff.
pgssEntry struct:
```
typedef struct pgssEntry
{
pgssHashKey key; /* hash key of entry - MUST BE FIRST */
Counters counters; /* the statistics for this query */
Size query_offset; /* query text offset in external file */
int query_len; /* # of valid bytes in query string, or -1 */
int encoding; /* query text encoding */
slock_t mutex; /* protects the counters only */
size_t yb_slow_executions; /* # of executions >= yb_hdr_max_value * yb_hdr_latency_res_ms */
hdr_histogram yb_hdr_histogram; /* flexible array member at end - MUST BE LAST */
} pgssEntry;
```
Noticeable changes from PG upstream
- Addition of planning statistics: 17e0328
This commit makes pg_stat_statements support new GUC `pg_stat_statements.track_planning`. If this option is enabled, pg_stat_statements tracks the planning statistics of the statements, e.g., the number of times the statement was planned, the total time spent planning the statement, etc.
Due to this all the in memory entry counters will have aggregated values in two valued array naming planning and execution.
```
typedef enum pgssStoreKind {
PGSS_INVALID = -1,
/*
* PGSS_PLAN and PGSS_EXEC must be respectively 0 and 1 as they're used to
* reference the underlying values in the arrays in the Counters struct,
* and this order is required in pg_stat_statements_internal().
*/
PGSS_PLAN = 0,
PGSS_EXEC,
PGSS_NUMKIND /* Must be last value of this enum */
} pgssStoreKind;
```
```
typedef struct Counters
{
int64 calls[PGSS_NUMKIND]; /* # of times planned/executed */
double total_time[PGSS_NUMKIND]; /* total planning/execution time,
* in msec */
double min_time[PGSS_NUMKIND]; /* minimum planning/execution time in
* msec */
double max_time[PGSS_NUMKIND]; /* maximum planning/execution time in
* msec */
double mean_time[PGSS_NUMKIND]; /* mean planning/execution time in
* msec */
double sum_var_time[PGSS_NUMKIND]; /* sum of variances in
```
- Track identical top vs nested queries independently: 6b4d23f
This change basically added a boolean value named `toplevel` to `pgsshashkey` to track statistics of same statements at top level and nested within a function. This changes structure of `pgsshashkey` which acts as key to `pgss_entry` in key-value hash map.
```
typedef struct pgssHashKey
{
Oid userid; /* user OID */
Oid dbid; /* database OID */
uint64 queryid; /* query identifier */
bool toplevel; /* query executed at top level */
} pgssHashKey;
```
- Display WAL usage statistics: 69bfaf2
- Add JIT counters to pg_stat_statements: 57d6aea
- Use has_privs_for_roles for predefined role checks: 6198420
- Add some tests for older versions still usable: 2b0da03
- Resolve a YB_TODO in in `pg_stat_statements.c` which re-enables redacted password support
- PG commit 4f0b0966c866ae9f0e15d7cc73ccf7ce4e1af84b (titled, Make use of in-core query id added by commit 5fd9dfa) displays the query identifier if has been computed, either by enabling compute_query_id or using a third-party module which is enabled by default as `pg_stat_statements` extension is preloaded and created during initdb. Changed some tests expected `.out` files that were flaming on this build.
```
/*
* YB_TODO(lnguyen@yugabyte)
* Postgres 13 drop this case. Need to verify if that's what we want.
*
* Use the redacted query for checking purposes.
redacted_query = pnstrdup(query, query_len);
redacted_query = RedactPasswordIfExists(redacted_query);
redacted_query_len = strlen(redacted_query);
queryId = pgss_hash_string(redacted_query, redacted_query_len);
*/
/*
```
Uncommented the above code and removed this line in this diff as pgss_hash_string function is removed: 4f0b096.
```
queryId = pgss_hash_string(redacted_query, redacted_query_len);
```
In total there are 82 commits between PG 11.2 and PG 15.2
```
git log --format='%h %s' REL_11_2..REL_15_2 -- contrib/pg_stat_statements | wc -l
82
```
YB changes:
- `yb_latency_histogram` support was added as part of this diff: https://phorge.dev.yugabyte.com/D23163 . Changes as part of this are not mentioned here.
- Create new files `yb_pg_pg_stat_statements.sql `and `yb_pg_pg_stat_statements.out`. pg_stat_statements.sql and pg_stat_statements.out files are copy of PG upstream while **yb_pg_** files have changes for YB compatibility. Changes include
- Remove MERGE queries from test as currently not supported. (PG commit that added MERGE queries test: 72abf03)
- Remove Inherits as not supported. GH issue: #5956
- `DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv ORDER BY a`; Add ORDER BY to get correct cursor output.
- Remove queries including selecting PG WAL related fields from pg_stat_statements. (PG commit that added WAL related queries 57d6aea)
- As part of PG 15.2 merge `oldextversions.sql` and `oldextversions.out` files were added. But we do not add them in `yb_schedule` of TestPgRegressPgStatStatements#schedule test because these add tests for older versions which is not applicable for yb.
- Also create files `yb_pg_stat_statement.sql` and `yg_pg_stat_statements.out` to separate out YB specific tests including redacted password support added as part of https://phorge.dev.yugabyte.com/D14961. Added in `yb_schedule` also.
- Add YB_PG_STAT_STATEMENTS_COLS_V1_10 constant with value 44. This is one more than PG_STAT_STATEMENTS_COLS_V1_10 (43) to add `yb_latency_histogram` column.
- Add api version `YB_PGSS_V1_10` to pgssVersion enum corresponding to `1.10-yb-1.0`.
- Fix 4/5 TestHdr tests with correction of query to fetch min_exec_time and max_exec_time instead of min_time and max_time from pg_stat_statements.
- Test which is still failing is `TestHdr#testStatementHist` which is trying to fetch data from ysql web-port (13000) and getting connection error. This is cause of failure in other tests not related to pg_stat_statements in pg15 branch.
- Enable pg_stat_statements extension creation in `initdb.c` via uncommenting the `enable_pg_stat_statements()`call. This was commented during PG15.2 merge.
Makefile and extension upgrade sql file changes:
- Only export `pg_stat_statements--1.10-yb-1.0.sql` , `pg_stat_statements--1.6-yb-1.0--1.10-yb-1.0.sql` and `pg_stat_statements--1.6--1.6-yb-1.0.sql` via Makefile DATA. This will add support only for YB version whether in case of new installation (which would be to 1.10-yb-1.0) and upgrades.
- pg_stat_statements--1.6-yb-1.0--1.10-yb-1.0.sql have contents of:
- pg_stat_statements--1.6- - 1.7.sql
- pg_stat_statements--1.7 - - 1.8.sql
- pg_stat_statements--1.8 - - 1.9.sql
- pg_stat_statements--1.9 - - 1.10.sql
- pg_stat_statements--1.10 - - 1.10-yb.sql
- pg_stat_statements--1.10-yb-1.0.sql have contents of:
- pg_stat_statements--1.4.sql
- pg_stat_statements--1.4-1.5.sql
- pg_stat_statements--1.5-1.6.sql
- pg_stat_statements--1.6 - 1.6-yb-1.0.sql
- pg_stat_statements--1.6-yb-1.0--1.10-yb-1.0.sql
These files are added by manually copying them in single file with in between comments to distinguish from which file the statements are copied. Also in between echo statements are also removed having only single echo statement at top of these two files.
pg_stat_statements--1.6-yb-1.0--1.10-yb-1.0.sql:
```
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
\echo Use "ALTER EXTENSION pg_stat_statements UPDATE TO '1.10-yb-1.0'" to load this file. \quit
/* Sourced from contrib/pg_stat_statements/pg_stat_statements--1.6--1.7.sql */
/* First we have to remove them from the extension */
ALTER EXTENSION pg_stat_statements DROP FUNCTION pg_stat_statements_reset();
/* Then we can drop them */
DROP FUNCTION pg_stat_statements_reset();
/* Now redefine */
CREATE FUNCTION pg_stat_statements_reset(IN userid Oid DEFAULT 0,
IN dbid Oid DEFAULT 0,
IN queryid bigint DEFAULT 0
)
RETURNS void
AS 'MODULE_PATHNAME', 'pg_stat_statements_reset_1_7'
LANGUAGE C STRICT PARALLEL SAFE;
-- Don't want this to be available to non-superusers.
REVOKE ALL ON FUNCTION pg_stat_statements_reset(Oid, Oid, bigint) FROM PUBLIC;
/* Sourced from contrib/pg_stat_statements/pg_stat_statements--1.7--1.8.sql */
"""
code
"""
```
Test Plan:
Jenkins: rebase: pg15
./yb_build.sh --java-test org.yb.pgsql.TestPgRegressPgStatStatements -n 50
./yb_build.sh --java-test org.yb.pgsql.TestHdr#testHdrRange -n 50
./yb_build.sh --java-test org.yb.pgsql.TestHdr#testMinMaxSleeps -n 50
./yb_build.sh --java-test org.yb.pgsql.TestHdr#testMinMaxSleepsBF8 -n 50
./yb_build.sh --java-test org.yb.pgsql.TestHdr#testPgRegressPercentile -n 50
Reviewers: aagrawal, jason, skumar, amartsinchyk
Reviewed By: jason, amartsinchyk
Subscribers: amartsinchyk, yql, smishra
Differential Revision: https://phorge.dev.yugabyte.com/D33574
|
Hello @devansh-ism, thanks for the update about INHERITS support, I don't know well about the process of release, when a commit is pushed, and how to check about your commit integration... |
|
Hi @agaldemas , I think you misunderstood the context in which I mentioned this issue. I mentioned the GH in commit summary because I commented some query in PG upstream ported regression test that uses inherits. We did this change to make test YugaByteDB compatible. Inherits is still not supported in YugabyteDB. I'm sorry for misunderstanding caused. |
That's why I ask for some details about your comment ;O)... |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Jira Link: DB-1496
Hi!
I am trying to use Odoo with YugabyteDB, I thought it would be interesting to reflect in this issue the related problems with this application.
Related
Regards! 🍀
The text was updated successfully, but these errors were encountered: