Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

create index with where clause error out with " no pre-assigned OID" #14465

Closed
kainwen opened this issue Nov 14, 2022 · 1 comment
Closed

create index with where clause error out with " no pre-assigned OID" #14465

kainwen opened this issue Nov 14, 2022 · 1 comment
Labels
type: bug

Comments

@kainwen
Copy link
Contributor

kainwen commented Nov 14, 2022

Bug Report

Greenplum version or build

Step to reproduce the behavior

CREATE FUNCTION unwanted_grant_nofail(int) RETURNS int
        IMMUTABLE LANGUAGE plpgsql AS $$
BEGIN
        PERFORM unwanted_grant();
        RAISE WARNING 'owned';
        RETURN 1;
EXCEPTION WHEN OTHERS THEN
        RETURN 2;
END$$;
CREATE MATERIALIZED VIEW sro_index_mv AS SELECT 1 AS c;
CREATE UNIQUE INDEX ON sro_index_mv (c) WHERE unwanted_grant_nofail(1) > 0;

It will error out:

CREATE UNIQUE INDEX ON sro_index_mv (c) WHERE unwanted_grant_nofail(1) > 0;

ERROR:  no pre-assigned OID for relation "sro_index_mv_c_idx" (oid_dispatch.c:698)  (seg0 127.0.1.1:6002 pid=49878) (oid_dispatch.c:698)

Remove the where clause it can succeed:

gpadmin=# CREATE UNIQUE INDEX ON sro_index_mv (c) ;
CREATE INDEX
kainwen added a commit to kainwen/gpdb that referenced this issue Nov 14, 2022
@kainwen
Return early for some cases in AtEOXact_DispatchOids.
2e74078 
The global var dispatch_oids only is used on QD; the global var
preassigned_oids is only used on QEs.

The function AtEOXact_DispatchOids is called at the end of a
transaction to do some clean-up job:
  * set the above two global vars to NIL
  * reset the OIDs memory context

We can short cut to return if:
  * on QD, find dispatch_oids is NIL
  * on QEs, find preassigned_oids is NIL

Short cut help avoid memory context reset since sometimes we may need
to restore the global vars. A typical case can be found in the issue:
greenplum-db#14465.
kainwen added a commit to kainwen/gpdb that referenced this issue Nov 14, 2022
@kainwen
Backup dispatch_oids before eval_const_expressions.
af54718 
In Greenplum's MPP architecture, for DDLs (like CREATE TABLE, CREATE
INDEX) QD will dispatch the OIDs for the new catalog entries to make
sure global consistency in the cluster.

In some cases, before generate the mail plan (statement) to dispatch,
during const-unfold related stage, eval_const_expressions() will
invoke dispatch to evaluate some expressions (like UDF contains SQL),
this kind of dispatch should not touch dispatch_oids and should not
include dispatch_oids in the object to dispatch.

Another case is on QEs, when eval_const_expressions when
subtransaction aborts, it will lead to clean up preassignoids and
later DDL might fail.

This commit saves dispatch_oids before eval_const_expressions_mutator
and later restores it.

Fix Gihubt Issue: greenplum-db#14465.
kainwen added a commit to kainwen/gpdb that referenced this issue Nov 15, 2022
@kainwen
Refactor SaveOidAssignments and RestoreOidAssignments logic.
b66fc3c 
The two functions SaveOidAssignments and RestoreOidAssignments should
come together, before some procedures that do not want to touch the
global vars (dispatch_oids or preassigned_oids), we need to first save
the oid assignments, and then do the job, finally restore oid
assignments. A typical usage should be as below:
   List *l = SaveOidAssignments();
   do_the_job();
   RestoreOidAssignments(l);

The global var dispatch_oids is only used on QD, and the global var
preassigned_oids is only used on QEs. They are both Lists, in a
specific memorycontext, normally the memorycontext will be reset at
the end of transaction. Greenplum's MPP architecture need to make some
OIDs consistent among coordinator and segments (like table OIDs). The
oid assignments are generated on QD and then dispatched to QEs. A
single SQL might involve sever dispatch events, for example, there are
some functions involving SQLs and these functions are evaluated during
planning stage before we dispatch the final Utility plan. We do not
want to the dispatches during plannign stage to touch oid assignments.

Another subtle case that the pair of functions are useful is that
subtransaction abort will lead to reset  of the oid assignments memory
context. Subtransaction abort might happen for UDF with exception
handling and nothing to do with the main statement needed to
dispatch. That is why we deep copy the content to CurrentMemoryContext
and reset oid assignment context during SaveOidAssignments and bring
everything back during RestoreOidAssignments.

This commit adds the two functions before eval_constant_expressions()
to make sure the procedure does not touch oid assignments.

Fix issue: greenplum-db#14465.
@kainwen kainwen mentioned this issue Nov 15, 2022
Merged
kainwen added a commit that referenced this issue Nov 18, 2022
@kainwen
Refactor SaveOidAssignments and RestoreOidAssignments logic.
c89ae81 
The two functions SaveOidAssignments and RestoreOidAssignments should
come together, before some procedures that do not want to touch the
global vars (dispatch_oids or preassigned_oids), we need to first save
the oid assignments, and then do the job, finally restore oid
assignments. A typical usage should be as below:
   List *l = SaveOidAssignments();
   do_the_job();
   RestoreOidAssignments(l);

The global var dispatch_oids is only used on QD, and the global var
preassigned_oids is only used on QEs. They are both Lists, in a
specific memorycontext, normally the memorycontext will be reset at
the end of transaction. Greenplum's MPP architecture need to make some
OIDs consistent among coordinator and segments (like table OIDs). The
oid assignments are generated on QD and then dispatched to QEs. A
single SQL might involve sever dispatch events, for example, there are
some functions involving SQLs and these functions are evaluated during
planning stage before we dispatch the final Utility plan. We do not
want to the dispatches during plannign stage to touch oid assignments.

Another subtle case that the pair of functions are useful is that
subtransaction abort will lead to reset  of the oid assignments memory
context. Subtransaction abort might happen for UDF with exception
handling and nothing to do with the main statement needed to
dispatch. That is why we deep copy the content to CurrentMemoryContext
and reset oid assignment context during SaveOidAssignments and bring
everything back during RestoreOidAssignments.

This commit adds the two functions before eval_constant_expressions()
to make sure the procedure does not touch oid assignments.

Fix issue: #14465.
kainwen added a commit to greenplum-db/gpdb-postgres-merge that referenced this issue Nov 18, 2022
@kainwen
Refactor SaveOidAssignments and RestoreOidAssignments logic.
91d0a7a 
The two functions SaveOidAssignments and RestoreOidAssignments should
come together, before some procedures that do not want to touch the
global vars (dispatch_oids or preassigned_oids), we need to first save
the oid assignments, and then do the job, finally restore oid
assignments. A typical usage should be as below:
   List *l = SaveOidAssignments();
   do_the_job();
   RestoreOidAssignments(l);

The global var dispatch_oids is only used on QD, and the global var
preassigned_oids is only used on QEs. They are both Lists, in a
specific memorycontext, normally the memorycontext will be reset at
the end of transaction. Greenplum's MPP architecture need to make some
OIDs consistent among coordinator and segments (like table OIDs). The
oid assignments are generated on QD and then dispatched to QEs. A
single SQL might involve sever dispatch events, for example, there are
some functions involving SQLs and these functions are evaluated during
planning stage before we dispatch the final Utility plan. We do not
want to the dispatches during plannign stage to touch oid assignments.

Another subtle case that the pair of functions are useful is that
subtransaction abort will lead to reset  of the oid assignments memory
context. Subtransaction abort might happen for UDF with exception
handling and nothing to do with the main statement needed to
dispatch. That is why we deep copy the content to CurrentMemoryContext
and reset oid assignment context during SaveOidAssignments and bring
everything back during RestoreOidAssignments.

This commit adds the two functions before eval_constant_expressions()
to make sure the procedure does not touch oid assignments.

Fix issue: greenplum-db/gpdb#14465.

(cherry picked from commit c89ae81)
@kainwen kainwen mentioned this issue Nov 22, 2022
Merged
@kainwen
Copy link
Contributor Author

kainwen commented Nov 23, 2022

Fixed by

@kainwen kainwen closed this as completed Nov 23, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
type: bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@kainwen