Category: PostgreSQL

ORA-00932: inconsistent datatypes from Heterogeneous Service Database Link

There is an Oracle database link between Oracle and PostgreSQL database through Heterogeneous Service ( HS ).

The sql query is working fine in PostgreSQL database:

testdb=> \d test_tbl
Table "testuser.test_tbl"
Column  | Type                    | Collation | Nullable | Default
--------+-------------------------+-----------+----------+---------
id      | double precision        |           |          |
s10     | character varying(10)   |           |          |
s20     | character varying(20)   |           |          |
s30     | character varying(2048) |           |          |


testdb=> select * from test_tbl;
id  | s10    | s20    | s30
----+--------+--------+---------------
1   | tets1 | test2 | Test Record 1

(1 row)

testdb=>select "id", substr("s10",1,5),
                substr("s20",1,5),substr("s30",1,10) 
         from test_tbl;

id | substr | substr | substr
---+--------+--------+------------
1  | test1  | test2  | Test Recor

(1 row)

While running the same query in Oracle database through Heterogeneous Service ( HS ) database link, get “ORA-00932: inconsistent datatypes” error. Sometimes you might get “ORA-02070: database does not support TO_CHAR in this context”.

SQL> select "id", substr("s10",1,5),substr("s20",1,5),substr("s30",1,10) 
      from "test_tbl"@PGLINK;
select "id", substr("s10",1,5),substr("s20",1,5), substr("s30",1,10) 
from "test_tbl"@PGLINK
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got LONG

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

PostgreSQL: Script to Kill Sessions

This is often used script to kill PostgreSQL session(s).

pg_terminate_backend() AND pg_cancel_backend()

1) kill all connections  of  a specified database name.

-- List all available databases

postgres=>\l

postgres=> SELECT pg_terminate_backend(pg_stat_activity.pid)
           FROM   pg_stat_activity
           WHERE  datname = 'DATABASE NAME'
              AND pid <> pg_backend_pid();

2) kill all connections  of a specified user name.

postgres=> SELECT pg_terminate_backend(pg_stat_activity.pid)
             FROM pg_stat_activity
            WHERE usename = 'testuser'
              AND pid <> pg_backend_pid();

3) kill all sessions of current database.

postgres=> SELECT pg_terminate_backend(pg_stat_activity.pid)
             FROM pg_stat_activity
            WHERE datname = current_database()
              AND pid <> pg_backend_pid();

4) kill a connection  of a specified pid.

postgres=> SELECT pg_terminate_backend(pid);

5) kill a running SQL of a session, but does not kill the session.

postgres=> SELECT pg_cancel_backend( pid );

It is useful when a running SQL is taking long time than expected.

Drop a Role with Dependent Objects in PostgreSQL

Try to drop a role/user in PostgreSQL, and get below errors:

postgres=>drop role testuser_read;
ERROR: role "testuser_read" cannot be dropped because some objects depend on it
DETAIL: privileges for table user1.tab1
privileges for table table user1.tab1
privileges for sequence user1.seq1
...
..
.

SOLUTION

1)Revoke objects privileges from this role/user which is to be dropped:

postgres=> select grantee,table_schema,table_name,privilege_type
            from information_schema.role_table_grants
           where grantee='testuser_read' 
           order by table_name,privilege_type;
grantee    | table_schema | table_name | privilege_type
-----------+--------------+------------+----------------
testuser_read |    user1     | tab1       | SELECT
testuser_read |    user1     | tab2       | SELECT
...
..
.

postgres=> select 'revoke '||privilege_type ||' on '||
           table_schema||'.'||table_name||' from '||grantee||' ;'
from information_schema.role_table_grants
where grantee='testuser_read' ;

-----------------------------------------------------------------------------
 revoke SELECT on user1.tab1 from testuser_read ;
 revoke SELECT on user1.tab2 from testuser_read ;
...
..
.

OR just ...

postgres=>REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA user1 
          FROM testuser_read; 

postgres=>REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA user1
          FROM testuser_read; 

postgres=>alter default privileges in schema testuser revoke select  
          on tables from testuser_read;

postgres=>alter default privileges in schema testuser revoke select ,  
          usage on sequences from testuser_read;

2) Revoke depended roles.

\du to find dependent roles

postgres=>\du
postgres=>revoke other_role from testuser_read;

3) Drop the role.

postgres=> drop role testuser_read;
ERROR:  role "testuser_read" cannot be dropped because some objects depend on it
DETAIL:  privileges for default privileges on new sequences belonging to role testuser in schema testuser
privileges for default privileges on new relations belonging to role tetsuser in schema testuser

postgres=> ALTER DEFAULT PRIVILEGES FOR ROLE testuser IN SCHEMA testuser REVOKE ALL ON TABLES FROM testuser_read;
ALTER DEFAULT PRIVILEGES
postgres=> ALTER DEFAULT PRIVILEGES FOR ROLE testuser IN SCHEMA testuser REVOKE ALL ON SEQUENCES FROM testuser_read;
ALTER DEFAULT PRIVILEGES
postgres=> drop role testuser_read;
DROP ROLE
postgres=>

SEQUENCE in PostgreSQL

1)Create Sequence:

CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name 
[ AS data_type ] 
[ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] 
[ MAXVALUE maxvalue | NO MAXVALUE ] 
[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] 
[ OWNED BY { table_name.column_name | NONE } ]


CREATE SEQUENCE serial START 101;

2) Select the next number from this sequence:

SELECT nextval('serial');

 nextval
---------
     101

SELECT nextval('serial');

 nextval
---------
     102

3)Sequence Manipulation Functions:

-- Return value most recently obtained with nextval 
select currval('serial') ;
-----
102
(1 row)

--Return value most recently obtained with nextval for any sequence
select lastval();
lastval
---------
102
(1 row)

-- Advance sequence and return new value
select nextval('serial') ;
-----
103
(1 row)

-- Set sequence's current value
select setval('serial', 1);
setval
--------
1
(1 row)

--setval
SELECT setval('serial', 42); Next nextval will return 43
SELECT setval('serial', 42, true); Same as above
SELECT setval('serial', 42, false); Next nextval will return 42

4) Sequence naming:

nextval('serial')   operates on sequence serial
nextval('SERIAL')   operates on sequence serial
nextval('"Serial"') operates on sequence Serial

5) Alter sequence:

ALTER SEQUENCE [ IF EXISTS ] name 
[ AS data_type ] 
[ INCREMENT [ BY ] increment ] 
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] 
[ START [ WITH ] start ] 
[ RESTART [ [ WITH ] restart ] ] 
[ CACHE cache ] [ [ NO ] CYCLE ] 
[ OWNED BY { table_name.column_name | NONE } ] 

ALTER SEQUENCE [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_USER 
| SESSION_USER } 
ALTER SEQUENCE [ IF EXISTS ] name RENAME TO new_name 
ALTER SEQUENCE [ IF EXISTS ] name SET SCHEMA new_schema

6) Grant Privileges On Sequence:

GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE sequence_name [, ...] | ALL SEQUENCES IN SCHEMA schema_name [, ...] } 
TO role_specification [, ...] [ WITH GRANT OPTION ]


where role_specification can be: 
[ GROUP ] role_name | PUBLIC | CURRENT_USER | SESSION_USER

7)Drop sequence:

DROP SEQUENCE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

DROP SEQUENCE serial;

8) Views about sequences:

PG_SEQUENCES:

NameTypeReferencesDescription
schemanamenamepg_namespace.nspnameName of schema containing sequence
sequencenamenamepg_class.relnameName of sequence
sequenceownernamepg_authid.rolnameName of sequence’s owner
data_typeregtypepg_type.oidData type of the sequence
start_valuebigintStart value of the sequence
min_valuebigintMinimum value of the sequence
max_valuebigintMaximum value of the sequence
increment_bybigintIncrement value of the sequence
cyclebooleanWhether the sequence cycles
cache_sizebigintCache size of the sequence
last_valuebigintThe last sequence value written to disk. If caching is used, this value can be greater than the last value handed out from the sequence. Null if the sequence has not been read from yet. Also, if the current user does not have USAGE or SELECT privilege on the sequence, the value is null.

PG_SEQUENCE:

pg_sequence Columns

Name Type References Description
seqrelid oid pg_class.oid The OID of the pg_class entry for this sequence
seqtypid oid pg_type.oid Data type of the sequence
seqstart int8 Start value of the sequence
seqincrement int8 Increment value of the sequence
seqmax int8 Maximum value of the sequence
seqmin int8 Minimum value of the sequence
seqcache int8 Cache size of the sequence
seqcycle bool Whether the sequence cycles

Heterogeneous Database Connections – Oracle to PostgreSQL

There are two ways to use Heterogeneous Services,  one is via ODBC which does not require a licence,  or you can use the licensed Gateways.

This article describes how to set up the Oracle configurations using ODBC , and then you can create database link between Oracle database and AWS PostgreSQL RDS.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe