Troubleshooting
Problem
Counts differs from _v_table compared to rows inserted with cross database select from Stored Procedure.
Symptom
Do not see the tables created using cross database select from _v_table from Stored procedure.
Cause
Wrong results using cross database select from _v_table from Stored procedure.
Diagnosing The Problem
No error received when running cross database select from _v_table from Stored procedure. The output will have the tables from the database connected to.
E.g.
1. Create 2 databases ( DB_A, DB_B)
SYSTEM.ADMIN(ADMIN)=> create database DB_A;
CREATE DATABASE
SYSTEM.ADMIN(ADMIN)=> create database DB_B;
CREATE DATABASE
2. Connect to DB_A database and create two tables ( DB_A_01, DB_A_02)
SYSTEM.ADMIN(ADMIN)=> \c DB_A
You are now connected to database DB_A.
DB_A.ADMIN(ADMIN)=> CREATE TABLE DB_A..table_in_DB_A_01 ( ID_A_01 int ) DISTRIBUTE on (ID_A_01);
CREATE TABLE
DB_A.ADMIN(ADMIN)=> CREATE TABLE DB_A..table_in_DB_A_02 ( ID_A_02 int ) DISTRIBUTE on (ID_A_02);
CREATE TABLE
3. Connect to DB_B and create one table ( DB_B_01):
DB_A.ADMIN(ADMIN)=> \c DB_B
You are now connected to database DB_B.
DB_B.ADMIN(ADMIN)=> CREATE TABLE DB_B..table_in_DB_B_01 ( ID_B_01 int ) DISTRIBUTE on (ID_B_01);
CREATE TABLE
4. Carry out a cross database select connect to DB_B database:
Select tables created in DB_A database when connected to DB_B database using normal select statement and it gives the correct result:
DB_B.ADMIN(ADMIN)=> select TABLENAME from DB_A.._v_table where OBJTYPE in ('TABLE');
TABLENAME
------------------
TABLE_IN_DB_A_01
TABLE_IN_DB_A_02
(2 rows)
5. Now create a stored procedure (eg p_print_tablenames ) in DB_B database:
DB_B.ADMIN(ADMIN)=> CREATE OR REPLACE PROCEDURE DB_B..p_print_tablenames ()
DB_B.ADMIN(ADMIN)-> RETURNS int4
DB_B.ADMIN(ADMIN)-> EXECUTE AS CALLER
DB_B.ADMIN(ADMIN)-> LANGUAGE NZPLSQL AS
DB_B.ADMIN(ADMIN)-> BEGIN_PROC
DB_B.ADMIN(ADMIN)$> DECLARE
DB_B.ADMIN(ADMIN)$> statement_V varchar := '';
DB_B.ADMIN(ADMIN)$> sloupce RECORD;
DB_B.ADMIN(ADMIN)$> BEGIN AUTOCOMMIT ON
DB_B.ADMIN(ADMIN)$> statement_V := 'The List of table names from DB_A is:';
DB_B.ADMIN(ADMIN)$> FOR sloupce IN
DB_B.ADMIN(ADMIN)$> select TABLENAME from DB_A.._v_table where OBJTYPE in ('TABLE')
DB_B.ADMIN(ADMIN)$> LOOP
DB_B.ADMIN(ADMIN)$> statement_V := statement_V || '
DB_B.ADMIN(ADMIN)$> ' || sloupce.TABLENAME;
DB_B.ADMIN(ADMIN)$> END LOOP;
DB_B.ADMIN(ADMIN)$> RAISE NOTICE 'statement_V is: %', statement_V;
DB_B.ADMIN(ADMIN)$> RETURN 0;
DB_B.ADMIN(ADMIN)$> END;
DB_B.ADMIN(ADMIN)$> END_PROC;
CREATE PROCEDURE
6. Reproduce issue by selecting tables created in DB_A database when connected to DB_B database using stored procedure p_print_tablenames () created above. This will not get the tables created in DB_A in the output:
DB_B.ADMIN(ADMIN)=> call DB_B..p_print_tablenames ();
NOTICE: statement_V is: The List of table names from DB_A is:
TABLE_IN_DB_B_01
P_PRINT_TABLENAMES
--------------------
0
(1 row)
=================
7. Review entries from the pg.log:
2016-04-21 02:45:39.971122 EDT [375] DEBUG: QUERY: CREATE OR REPLACE PROCEDURE DB_B..p_print_tablenames ()
RETURNS int4
EXECUTE AS CALLER
LANGUAGE NZPLSQL AS
BEGIN_PROC
DECLARE
statement_V varchar := '';
sloupce RECORD;
BEGIN AUTOCOMMIT ON
statement_V := 'The List of table names from DB_A is:';
FOR sloupce IN
select TABLENAME from DB_A.._v_table where OBJTYPE in ('TABLE')
LOOP
statement_V := statement_V || '
' || sloupce.TABLENAME;
END LOOP;
RAISE NOTICE 'statement_V is: %', statement_V;
RETURN 0;
END;
END_PROC;
2016-04-21 02:45:39.991858 EDT [375] DEBUG: transaction 2522156 started dbos txid 0x1c408
2016-04-21 02:45:39.995611 EDT [375] DEBUG: committed transaction 2522156 (dbos txid 0x1c408)
2016-04-21 02:45:57.427359 EDT [375] DEBUG: QUERY: call DB_B..p_print_tablenames ();
2016-04-21 02:45:57.452775 EDT [375] DEBUG: STORED PROCEDURE EXEC: SELECT TABLENAME from DB_A.._v_table where OBJTYPE in ('TABLE')
2016-04-21 02:45:57.570596 EDT [375] NOTICE: statement_V is: The List of table names from DB_A is:
TABLE_IN_DB_B_01
Resolving The Problem
This is an instance of defect 104294 which is fixed in following Netezza Performance Server (NPS) versions:
7.2.0.8-P1
7.2.1.2-P1
7.2.1.3
Workaround:
Use _v_table_xdb instead of _v_table system view in the select statement from stored procedure.
select TABLENAME from DB_A.._v_table_xdb where OBJTYPE='TABLE' and database='DB_A'
Using the _v_table_xdb system view and a restriction on the database attribute works fine.
E.g:
CREATE OR REPLACE PROCEDURE DB_B..p_print_tablenames ()
RETURNS int4
EXECUTE AS CALLER
LANGUAGE NZPLSQL AS
BEGIN_PROC
DECLARE
statement_V varchar := '';
sloupce RECORD;
BEGIN AUTOCOMMIT ON
statement_V := 'The List of table names from DB_A is:';
FOR sloupce IN
select TABLENAME from DB_A.._v_table_xdb where OBJTYPE='TABLE' and database='DB_A'
LOOP
statement_V := statement_V || '
' || sloupce.TABLENAME;
END LOOP;
RAISE NOTICE 'statement_V is: %', statement_V;
RETURN 0;
END;
END_PROC;
DB_B.ADMIN(ADMIN)=> call DB_B..p_print_tablenames ();
NOTICE: statement_V is: The List of table names from DB_A is:
TABLE_IN_DB_A_01
TABLE_IN_DB_A_02
The above is the expected output using cross database select from Stored Procedure.
Was this topic helpful?
Document Information
Modified date:
17 October 2019
UID
swg21983166