BUG #14408: Schema not found error when 2 or more indices declared on temporary table
The following bug has been logged on the website:
Bug reference: 14408
Logged by: Victor Colborn
Email address: vcolborn@gmail.com
PostgreSQL version: 9.6.1
Operating system: (Red Hat 4.4.7-16), 64-bit
Description:
I'm finding that this rather simple postgresql 9.6.1 function
CREATE OR REPLACE FUNCTION trying_to_index_me()
RETURNS VOID AS
$BODY$
BEGIN
CREATE Temporary TABLE temp_data_to_index (
id INTEGER NOT NULL,
this_id UUID NOT NULL,
that_id smallint NOT NULL,
CONSTRAINT idx_temp_data_to_index_unique
UNIQUE (id,this_id,that_id)
);
CREATE INDEX idx_temp_data_to_index_thisthat ON
temp_data_to_index(this_id,that_id);
DROP TABLE temp_data_to_index;
END;
$BODY$ LANGUAGE plpgsql VOLATILE COST 100;
--SELECT trying_to_index_me();
is resulting in a schema "" does not exist error. The exact error is:
ERROR: schema "" does not exist
CONTEXT: SQL statement "CREATE INDEX idx_temp_data_to_index_thisthat ON
temp_data_to_index(this_id,that_id)"
PL/pgSQL function trying_to_index_me() line 10 at SQL statement
********** Error **********
ERROR: schema "" does not exist
SQL state: 3F000
Context: SQL statement "CREATE INDEX idx_temp_data_to_index_thisthat ON
temp_data_to_index(this_id,that_id)"
PL/pgSQL function trying_to_index_me() line 10 at SQL statement
and occurs reliably on the second and subsequent executions. Cut/Pasting the
above SQL chunk reproduces the error...for me. Quite interested if that's
not the case elsewhere. I have the following clues:
It will error consistently once its in.
It always indicates the 'CREATE INDEX' line as the culprit of error.
Declaring the pg_temp temporary schema as '... ON pg_temp.temp_data_to_index
...' has no effect.
I find that if I drop the function and recreate it, I'll get one execution
and then the errored state will occur again.
Inserting records also had no effect on the error state.
I find that removing either idx_temp_data_to_index_thisthat or
idx_temp_data_to_index_unique resolves the issue.
It indicates the name of the not found schema to be "" or "0MA{Start of
Text}" or some similar variant with non-visible character values.
Truly appreciate your thoughts.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
vcolborn@gmail.com writes:
I'm finding that this rather simple postgresql 9.6.1 function
...
is resulting in a schema "" does not exist error. The exact error is:
Hm. The described symptoms sound quite a bit like an uninitialized-memory
problem. However, I can't reproduce the problem here (using up-to-date
RHEL6), nor does valgrind complain about this example for me.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
I'm able to reproduce on 9.5.3 as well.
Could it possibly related to a configuration setting (or some other common
attribute) I can review/alter?
Very much appreciate your time Tom.
On Tue, Nov 1, 2016 at 3:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
vcolborn@gmail.com writes:
I'm finding that this rather simple postgresql 9.6.1 function
...
is resulting in a schema "" does not exist error. The exact error is:Hm. The described symptoms sound quite a bit like an uninitialized-memory
problem. However, I can't reproduce the problem here (using up-to-date
RHEL6), nor does valgrind complain about this example for me.regards, tom lane
Victor Colborn <vcolborn@gmail.com> writes:
I'm able to reproduce on 9.5.3 as well.
Oh, interesting.
Could it possibly related to a configuration setting (or some other common
attribute) I can review/alter?
Can't think what ... but what non-default settings are you using?
Is this a custom build of Postgres, or just an RPM (whose?)
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Tue, Nov 1, 2016 at 12:26 PM, <vcolborn@gmail.com> wrote:
The following bug has been logged on the website:
Bug reference: 14408
Logged by: Victor Colborn
Email address: vcolborn@gmail.com
PostgreSQL version: 9.6.1
Operating system: (Red Hat 4.4.7-16), 64-bit
Description:I'm finding that this rather simple postgresql 9.6.1 function
[...]is resulting in a schema "" does not exist error. The exact error is:
[...]
and occurs reliably on the second and subsequent executions. Cut/Pasting
the
above SQL chunk reproduces the error...for me. Quite interested if that's
not the case elsewhere. I have the following clues:[...]
It indicates the name of the not found schema to be "" or "0MA{Start of
Text} " or some similar variant with non-visible character values.
My gut reaction here is to type, by hand, the CREATE FUNCTION code into a
newly created text file and send it through psql. Your comment about
copy/paste and "non-visible characters" leads me to suspect that whatever
source you are copying from has been "poisoned" by a control character
that you can't see and that is causing a problem.
David J.
Good suggestion David. Tried that to no resolve. I am getting a successful first execution, but something is improper on subsequent executions.
Show quoted text
On Nov 1, 2016, at 5:00 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tue, Nov 1, 2016 at 12:26 PM, <vcolborn@gmail.com> wrote:
The following bug has been logged on the website:
Bug reference: 14408
Logged by: Victor Colborn
Email address: vcolborn@gmail.com
PostgreSQL version: 9.6.1
Operating system: (Red Hat 4.4.7-16), 64-bit
Description:I'm finding that this rather simple postgresql 9.6.1 function
[...]is resulting in a schema "" does not exist error. The exact error is:
[...]
and occurs reliably on the second and subsequent executions. Cut/Pasting the
above SQL chunk reproduces the error...for me. Quite interested if that's
not the case elsewhere. I have the following clues:[...]
It indicates the name of the not found schema to be "" or "0MA{Start of
Text} " or some similar variant with non-visible character values.My gut reaction here is to type, by hand, the CREATE FUNCTION code into a newly created text file and send it through psql. Your comment about copy/paste and "non-visible characters" leads me to suspect that whatever source you are copying from has been "poisoned" by a control character that you can't see and that is causing a problem.
David J.
Both were executables downloaded from the PostgreSQL website and while I would suspect the configuration is fairly default, I'm not the only hands at work. The 9.5.3 install has been there a while. The 9.6.1 install is newer, just a month or two.
I suspect my environment, configuration, or something specific to "my box" since it isn't trivial to reproduce.
On Nov 1, 2016, at 4:51 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Victor Colborn <vcolborn@gmail.com> writes:
I'm able to reproduce on 9.5.3 as well.
Oh, interesting.
Could it possibly related to a configuration setting (or some other common
attribute) I can review/alter?Can't think what ... but what non-default settings are you using?
Is this a custom build of Postgres, or just an RPM (whose?)
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs