[GENERAL] Oracle database into PostgreSQL using Ora2PG tool.

[PAWAN SHARMA]

Hi All,

I am migrating Oracle database into PostgreSQL using Ora2PG tool.

So, I am facing one issue with trigger after generating script output of
Oracle database.

*Oracle : *

CREATE OR REPLACE TRIGGER UPDATE_JOB_HISTORY
AFTER UPDATE OF JOB_ID, DEPARTMENT_ID ON EMPLOYEES
FOR EACH ROW
BEGIN
  add_job_history(:old.employee_id, :old.hire_date, sysdate,
  :old.job_id, :old.department_id);
END;
/

*The script generated by Ora2PG tool.*

DROP TRIGGER IF EXISTS update_job_history ON employees CASCADE;
CREATE OR REPLACE FUNCTION trigger_fct_update_job_history() RETURNS trigger
AS $BODY$
BEGIN
  add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMESTAMP,
  OLD.job_id, OLD.department_id);
RETURN NEW;
END
$BODY$
 LANGUAGE 'plpgsql';

CREATE TRIGGER update_job_history
AFTER UPDATE ON employees FOR EACH ROW
EXECUTE PROCEDURE trigger_fct_update_job_history();


when I try to run the above-generated script it will show below error.

ERROR:  syntax error at or near "add_job_history"
LINE 4:   add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMES...
  ^
NOTICE:  relation "employees" does not exist, skipping



Please Suggest or help to resolve it.

-Pawan

Re: [GENERAL] Oracle database into PostgreSQL using Ora2PG tool.

[Pavel Stehule]

Hi

2017-06-02 10:16 GMT+02:00 PAWAN SHARMA :

> Hi All,
>
> I am migrating Oracle database into PostgreSQL using Ora2PG tool.
>
> So, I am facing one issue with trigger after generating script output of
> Oracle database.
>
> *Oracle : *
>
> CREATE OR REPLACE TRIGGER UPDATE_JOB_HISTORY
> AFTER UPDATE OF JOB_ID, DEPARTMENT_ID ON EMPLOYEES
> FOR EACH ROW
> BEGIN
>   add_job_history(:old.employee_id, :old.hire_date, sysdate,
>   :old.job_id, :old.department_id);
> END;
> /
>
> *The script generated by Ora2PG tool.*
>
> DROP TRIGGER IF EXISTS update_job_history ON employees CASCADE;
> CREATE OR REPLACE FUNCTION trigger_fct_update_job_history() RETURNS
> trigger AS $BODY$
> BEGIN
>   add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMESTAMP,
>   OLD.job_id, OLD.department_id);
> RETURN NEW;
> END
> $BODY$
>  LANGUAGE 'plpgsql';
>
> CREATE TRIGGER update_job_history
> AFTER UPDATE ON employees FOR EACH ROW
> EXECUTE PROCEDURE trigger_fct_update_job_history();
>
>
> when I try to run the above-generated script it will show below error.
>
> ERROR:  syntax error at or near "add_job_history"
> LINE 4:   add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMES...
>   ^
> NOTICE:  relation "employees" does not exist, skipping
>
>
maybe you have too old version of ora2pg. Master ora2pg is able to emulate
procedure call by SELECT command.

Regards

Pavel


>
>
> Please Suggest or help to resolve it.
>
> -Pawan
>
>
>
>
>

Re: [GENERAL] Oracle database into PostgreSQL using Ora2PG tool.

[PAWAN SHARMA]

On Fri, Jun 2, 2017 at 2:20 PM, Andrew Sullivan  wrote:

> (Offlist: phone)
>
> It's saying the table isn't there.  Is it?  Is it maybe spelled
> "EMPLOYEES" (all caps) instead?  You need double quotes if so.
>
> A
>
> --
> Andrew Sullivan
> Please excuse my clumbsy thums.
>
> On Jun 2, 2017, at 04:16, PAWAN SHARMA  wrote:
>
> Hi All,
>
> I am migrating Oracle database into PostgreSQL using Ora2PG tool.
>
> So, I am facing one issue with trigger after generating script output of
> Oracle database.
>
> *Oracle : *
>
> CREATE OR REPLACE TRIGGER UPDATE_JOB_HISTORY
> AFTER UPDATE OF JOB_ID, DEPARTMENT_ID ON EMPLOYEES
> FOR EACH ROW
> BEGIN
>   add_job_history(:old.employee_id, :old.hire_date, sysdate,
>   :old.job_id, :old.department_id);
> END;
> /
>
> *The script generated by Ora2PG tool.*
>
> DROP TRIGGER IF EXISTS update_job_history ON employees CASCADE;
> CREATE OR REPLACE FUNCTION trigger_fct_update_job_history() RETURNS
> trigger AS $BODY$
> BEGIN
>   add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMESTAMP,
>   OLD.job_id, OLD.department_id);
> RETURN NEW;
> END
> $BODY$
>  LANGUAGE 'plpgsql';
>
> CREATE TRIGGER update_job_history
> AFTER UPDATE ON employees FOR EACH ROW
> EXECUTE PROCEDURE trigger_fct_update_job_history();
>
>
> when I try to run the above-generated script it will show below error.
>
> ERROR:  syntax error at or near "add_job_history"
> LINE 4:   add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMES...
>   ^
> NOTICE:  relation "employees" does not exist, skipping
>
>
>
> Please Suggest or help to resolve it.
>
> -Pawan
>
>
>
>
>
> Hi Andrew,

employees table is exited.

*Oracle Database*

CREATE TABLE EMPLOYEES
(
 EMPLOYEE_ID NUMBER (6)  NOT NULL ,
 FIRST_NAME VARCHAR2 (20 BYTE) ,
 LAST_NAME VARCHAR2 (25 BYTE)  NOT NULL ,
 EMAIL VARCHAR2 (25 BYTE)  NOT NULL ,
 PHONE_NUMBER VARCHAR2 (20 BYTE) ,
 HIRE_DATE DATE  NOT NULL ,
 JOB_ID VARCHAR2 (10 BYTE)  NOT NULL ,
 SALARY NUMBER (8,2) ,
 COMMISSION_PCT NUMBER (2,2) ,
 MANAGER_ID NUMBER (6) ,
 DEPARTMENT_ID NUMBER (4)
) LOGGING
;

The script generated by the tool.

CREATE TABLE employees (
employee_id integer NOT NULL,
first_name varchar(20),
last_name varchar(25) NOT NULL,
email varchar(25) NOT NULL,
phone_number varchar(20),
hire_date timestamp NOT NULL,
job_id varchar(10) NOT NULL,
salary decimal(8,2),
commission_pct decimal(2,2),
manager_id integer,
department_id smallint
) ;

Re: [GENERAL] Oracle database into PostgreSQL using Ora2PG tool.

[PAWAN SHARMA]

On Fri, Jun 2, 2017 at 2:13 PM, Pavel Stehule 
wrote:

> Hi
>
> 2017-06-02 10:16 GMT+02:00 PAWAN SHARMA :
>
>> Hi All,
>>
>> I am migrating Oracle database into PostgreSQL using Ora2PG tool.
>>
>> So, I am facing one issue with trigger after generating script output of
>> Oracle database.
>>
>> *Oracle : *
>>
>> CREATE OR REPLACE TRIGGER UPDATE_JOB_HISTORY
>> AFTER UPDATE OF JOB_ID, DEPARTMENT_ID ON EMPLOYEES
>> FOR EACH ROW
>> BEGIN
>>   add_job_history(:old.employee_id, :old.hire_date, sysdate,
>>   :old.job_id, :old.department_id);
>> END;
>> /
>>
>> *The script generated by Ora2PG tool.*
>>
>> DROP TRIGGER IF EXISTS update_job_history ON employees CASCADE;
>> CREATE OR REPLACE FUNCTION trigger_fct_update_job_history() RETURNS
>> trigger AS $BODY$
>> BEGIN
>>   add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMESTAMP,
>>   OLD.job_id, OLD.department_id);
>> RETURN NEW;
>> END
>> $BODY$
>>  LANGUAGE 'plpgsql';
>>
>> CREATE TRIGGER update_job_history
>> AFTER UPDATE ON employees FOR EACH ROW
>> EXECUTE PROCEDURE trigger_fct_update_job_history();
>>
>>
>> when I try to run the above-generated script it will show below error.
>>
>> ERROR:  syntax error at or near "add_job_history"
>> LINE 4:   add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMES...
>>   ^
>> NOTICE:  relation "employees" does not exist, skipping
>>
>>
> maybe you have too old version of ora2pg. Master ora2pg is able to emulate
> procedure call by SELECT command.
>
> Regards
>
> Pavel
>
>
>>
>>
>> Please Suggest or help to resolve it.
>>
>> -Pawan
>>
>>
>>
>>
>>
>
> Hi Pavel,

Thanks for the response, I am using.

c:\ora2pg>ora2pg -c ora2pg.conf  -v
Ora2Pg v18.1

Re: [GENERAL] Oracle database into PostgreSQL using Ora2PG tool.

[Pavel Stehule]

2017-06-02 11:15 GMT+02:00 PAWAN SHARMA :

>
> On Fri, Jun 2, 2017 at 2:13 PM, Pavel Stehule 
> wrote:
>
>> Hi
>>
>> 2017-06-02 10:16 GMT+02:00 PAWAN SHARMA :
>>
>>> Hi All,
>>>
>>> I am migrating Oracle database into PostgreSQL using Ora2PG tool.
>>>
>>> So, I am facing one issue with trigger after generating script output of
>>> Oracle database.
>>>
>>> *Oracle : *
>>>
>>> CREATE OR REPLACE TRIGGER UPDATE_JOB_HISTORY
>>> AFTER UPDATE OF JOB_ID, DEPARTMENT_ID ON EMPLOYEES
>>> FOR EACH ROW
>>> BEGIN
>>>   add_job_history(:old.employee_id, :old.hire_date, sysdate,
>>>   :old.job_id, :old.department_id);
>>> END;
>>> /
>>>
>>> *The script generated by Ora2PG tool.*
>>>
>>> DROP TRIGGER IF EXISTS update_job_history ON employees CASCADE;
>>> CREATE OR REPLACE FUNCTION trigger_fct_update_job_history() RETURNS
>>> trigger AS $BODY$
>>> BEGIN
>>>   add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMESTAMP,
>>>   OLD.job_id, OLD.department_id);
>>> RETURN NEW;
>>> END
>>> $BODY$
>>>  LANGUAGE 'plpgsql';
>>>
>>> CREATE TRIGGER update_job_history
>>> AFTER UPDATE ON employees FOR EACH ROW
>>> EXECUTE PROCEDURE trigger_fct_update_job_history();
>>>
>>>
>>> when I try to run the above-generated script it will show below error.
>>>
>>> ERROR:  syntax error at or near "add_job_history"
>>> LINE 4:   add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMES...
>>>   ^
>>> NOTICE:  relation "employees" does not exist, skipping
>>>
>>>
>> maybe you have too old version of ora2pg. Master ora2pg is able to
>> emulate procedure call by SELECT command.
>>
>> Regards
>>
>> Pavel
>>
>>
>>>
>>>
>>> Please Suggest or help to resolve it.
>>>
>>> -Pawan
>>>
>>>
>>>
>>>
>>>
>>
>> Hi Pavel,
>
> Thanks for the response, I am using.
>
> c:\ora2pg>ora2pg -c ora2pg.conf  -v
> Ora2Pg v18.1
>
>
try to use master from github

Regards

Pavel

[GENERAL] PostGIS ST_CreateOverview Function For Non-Public Schema

[Osahon Oduware]

Hi All,

I am trying to utilize the ST_CreateOverview function  as described in the
following link:
https://postgis.net/docs/RT_CreateOverview.html

I need to know how to include the schema name for the raster table in the
function. It appears the default is the public schema.

Please, I need help on this.

[GENERAL] Writing a C function to return the log file name

[Kouber Saparev]

Hello,

I am trying to write a function in C to return the log file name by given
timestamp. I will use that later to make dynamic creation of a foreign
table (file_fdw) to read the csv logs themselves. The thing is I do now
want to hardcode neither the format, nor the directory in my extension.

I already looked into the adminpack extension, but the format is hardcoded
to the default one there, so it does not serve my needs.

Here is what I currently have:
https://gist.github.com/kouber/89b6e5b647452a672a446b12413e20cf

The thing is the function is returning random results, obtained by
pg_strftime().

kouber=# select now()::timestamp, sqlog.log_path(now()::timestamp);
NOTICE:  Log directory = "pg_log"
NOTICE:  Log filename = "postgresql-%F.log"
NOTICE:  Length = "7"
NOTICE:  Filename = "pg_log/postgresql-17422165-04-30.log"
   now |   log_path
+--
2017-06-02 14:17:47.832446 | pg_log/postgresql-17422165-04-30.csv
(1 row)

kouber=# select now()::timestamp, sqlog.log_path(now()::timestamp);
NOTICE:  Log directory = "pg_log"
NOTICE:  Log filename = "postgresql-%F.log"
NOTICE:  Length = "7"
NOTICE:  Filename = "pg_log/postgresql-17422166-02-08.log"
   now |   log_path
+--
2017-06-02 14:18:12.390558 | pg_log/postgresql-17422166-02-08.csv
(1 row)


Any idea what am I doing wrong?

I copied logfile_getname() from syslogger.c, and simply added some debug
messages in there.

Regards,
--
Kouber Saparev

Re: [GENERAL] Oracle database into PostgreSQL using Ora2PG tool.

[Neil Anderson]

On 2 June 2017 at 04:16, PAWAN SHARMA  wrote:
> Hi All,
>
> I am migrating Oracle database into PostgreSQL using Ora2PG tool.
>
> So, I am facing one issue with trigger after generating script output of
> Oracle database.
>
> Oracle :
>
> CREATE OR REPLACE TRIGGER UPDATE_JOB_HISTORY
> AFTER UPDATE OF JOB_ID, DEPARTMENT_ID ON EMPLOYEES
> FOR EACH ROW
> BEGIN
>   add_job_history(:old.employee_id, :old.hire_date, sysdate,
>   :old.job_id, :old.department_id);
> END;
> /
>
> The script generated by Ora2PG tool.
>
> DROP TRIGGER IF EXISTS update_job_history ON employees CASCADE;
> CREATE OR REPLACE FUNCTION trigger_fct_update_job_history() RETURNS trigger
> AS $BODY$
> BEGIN
>   add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMESTAMP,
>   OLD.job_id, OLD.department_id);
> RETURN NEW;
> END
> $BODY$
>  LANGUAGE 'plpgsql';

The examples here
https://www.postgresql.org/docs/9.5/static/sql-createfunction.html
have a semi colon after 'END', is that the syntax error?

> CREATE TRIGGER update_job_history
> AFTER UPDATE ON employees FOR EACH ROW
> EXECUTE PROCEDURE trigger_fct_update_job_history();
>
>
> when I try to run the above-generated script it will show below error.
>
> ERROR:  syntax error at or near "add_job_history"
> LINE 4:   add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMES...
>   ^
> NOTICE:  relation "employees" does not exist, skipping

Since this is a NOTICE maybe it's a red herring? the results of a
CREATE IF NOT EXISTS or similar?


-- 
Neil Anderson
n...@postgrescompare.com
https://www.postgrescompare.com



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle database into PostgreSQL using Ora2PG tool.

[Igor Neyman]

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of PAWAN SHARMA
Sent: Friday, June 02, 2017 4:17 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Oracle database into PostgreSQL using Ora2PG tool.


Attention: This email was sent from someone outside of Perceptron. Always 
exercise caution when opening attachments or clicking links from unknown 
senders or when receiving unexpected emails.

Hi All,

I am migrating Oracle database into PostgreSQL using Ora2PG tool.

So, I am facing one issue with trigger after generating script output of Oracle 
database.

Oracle :

CREATE OR REPLACE TRIGGER UPDATE_JOB_HISTORY
AFTER UPDATE OF JOB_ID, DEPARTMENT_ID ON EMPLOYEES
FOR EACH ROW
BEGIN
  add_job_history(:old.employee_id, :old.hire_date, sysdate,
  :old.job_id, :old.department_id);
END;
/

The script generated by Ora2PG tool.

DROP TRIGGER IF EXISTS update_job_history ON employees CASCADE;
CREATE OR REPLACE FUNCTION trigger_fct_update_job_history() RETURNS trigger AS 
$BODY$
BEGIN
  add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMESTAMP,
  OLD.job_id, OLD.department_id);
RETURN NEW;
END
$BODY$
 LANGUAGE 'plpgsql';

CREATE TRIGGER update_job_history
AFTER UPDATE ON employees FOR EACH ROW
EXECUTE PROCEDURE trigger_fct_update_job_history();


when I try to run the above-generated script it will show below error.

ERROR:  syntax error at or near "add_job_history"
LINE 4:   add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMES...
  ^
NOTICE:  relation "employees" does not exist, skipping



Please Suggest or help to resolve it.

-Pawan


When you call a function inside PlSQL code and don’t care about returned value, 
then you do: PERFORM function_name(…).
Otherwise you do: SELECT function_name(…) INTO your_variable;

So:

DROP TRIGGER IF EXISTS update_job_history ON employees CASCADE;
CREATE OR REPLACE FUNCTION trigger_fct_update_job_history() RETURNS trigger AS 
$BODY$
BEGIN
PERFORM  add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMESTAMP,
  OLD.job_id, OLD.department_id);
RETURN NEW;
END
$BODY$
 LANGUAGE 'plpgsql';

Regards,
Igor Neyman

Re: [GENERAL] Oracle database into PostgreSQL using Ora2PG tool.

[Igor Neyman]

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Igor Neyman
Sent: Friday, June 02, 2017 9:45 AM
To: PAWAN SHARMA ; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Oracle database into PostgreSQL using Ora2PG tool.


Attention: This email was sent from someone outside of Perceptron. Always 
exercise caution when opening attachments or clicking links from unknown 
senders or when receiving unexpected emails.


From: 
pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of PAWAN SHARMA
Sent: Friday, June 02, 2017 4:17 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Oracle database into PostgreSQL using Ora2PG tool.


Attention: This email was sent from someone outside of Perceptron. Always 
exercise caution when opening attachments or clicking links from unknown 
senders or when receiving unexpected emails.

Hi All,

I am migrating Oracle database into PostgreSQL using Ora2PG tool.

So, I am facing one issue with trigger after generating script output of Oracle 
database.

Oracle :

CREATE OR REPLACE TRIGGER UPDATE_JOB_HISTORY
AFTER UPDATE OF JOB_ID, DEPARTMENT_ID ON EMPLOYEES
FOR EACH ROW
BEGIN
  add_job_history(:old.employee_id, :old.hire_date, sysdate,
  :old.job_id, :old.department_id);
END;
/

The script generated by Ora2PG tool.

DROP TRIGGER IF EXISTS update_job_history ON employees CASCADE;
CREATE OR REPLACE FUNCTION trigger_fct_update_job_history() RETURNS trigger AS 
$BODY$
BEGIN
  add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMESTAMP,
  OLD.job_id, OLD.department_id);
RETURN NEW;
END
$BODY$
 LANGUAGE 'plpgsql';

CREATE TRIGGER update_job_history
AFTER UPDATE ON employees FOR EACH ROW
EXECUTE PROCEDURE trigger_fct_update_job_history();


when I try to run the above-generated script it will show below error.

ERROR:  syntax error at or near "add_job_history"
LINE 4:   add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMES...
  ^
NOTICE:  relation "employees" does not exist, skipping



Please Suggest or help to resolve it.

-Pawan


When you call a function inside PlSQL code and don’t care about returned value, 
then you do: PERFORM function_name(…).
Otherwise you do: SELECT function_name(…) INTO your_variable;

So:

DROP TRIGGER IF EXISTS update_job_history ON employees CASCADE;
CREATE OR REPLACE FUNCTION trigger_fct_update_job_history() RETURNS trigger AS 
$BODY$
BEGIN
PERFORM  add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMESTAMP,
  OLD.job_id, OLD.department_id);
RETURN NEW;
END
$BODY$
 LANGUAGE 'plpgsql';

Regards,
Igor Neyman

 P.S. Also you are missing semicolon (END;):

DROP TRIGGER IF EXISTS update_job_history ON employees CASCADE;
CREATE OR REPLACE FUNCTION trigger_fct_update_job_history() RETURNS trigger AS 
$BODY$
BEGIN
PERFORM  add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMESTAMP,
  OLD.job_id, OLD.department_id);
RETURN NEW;
END;
$BODY$
 LANGUAGE 'plpgsql';

Re: [GENERAL] repmgr cascade replication node delay

[Juliano]

Hi David,

Thanks for your email.

This recovery_min_apply_delay sounds interesting!
I wold like to use it between 2 standby nodes (cascade replication using 
repmgr) instead of master/standby, anyone knows if is it possible?

Regards,
Juliano

Sent with [ProtonMail](https://protonmail.com) Secure Email.

 Original Message 
Subject: Re: [GENERAL] repmgr cascade replication node delay
Local Time: June 1, 2017 11:42 PM
UTC Time: June 1, 2017 10:42 PM
From: david.g.johns...@gmail.com
To: Juliano 
pgsql-general@postgresql.org , 
pgsql-general-ow...@postgresql.org 

On Thu, Jun 1, 2017 at 3:30 PM, Juliano  wrote:

The objective of an extra node 24 hours delayed is to recover some error in the 
server.
Is it possible to make this configuration?

A quick search turns up:
recovery_min_apply_delay

https://www.postgresql.org/docs/current/static/standby-settings.html

No personal experience though.

David J.

Re: [GENERAL] Oracle database into PostgreSQL using Ora2PG tool.

[stevenchang1213]

tell me where this function add_job_history() is?Actually, I don't think you 
can count on ora2pg to transform your pl/sql code to plpgsql or other 
(un)trusted procedural language code. It's not that simple!you can type "\df  
add_job_history"  in psql session to check it's existence if it belongs to 
public schema or access it using fully qualified name scheme.

Steven
從我的 Samsung Galaxy 智慧型手機傳送。
 原始訊息 自： PAWAN SHARMA  日期： 2017/6/2  
16:16  (GMT+08:00) 至： pgsql-general@postgresql.org 主旨： [GENERAL] Oracle 
database into PostgreSQL using Ora2PG tool. 
Hi All,
I am migrating Oracle database into PostgreSQL using Ora2PG tool.
So, I am facing one issue with trigger after generating script output of Oracle 
database.
Oracle : 
CREATE OR REPLACE TRIGGER UPDATE_JOB_HISTORY     AFTER UPDATE OF JOB_ID, 
DEPARTMENT_ID ON EMPLOYEES     FOR EACH ROW BEGIN  
add_job_history(:old.employee_id, :old.hire_date, sysdate,                  
:old.job_id, :old.department_id);END; / 
The script generated by Ora2PG tool.
DROP TRIGGER IF EXISTS update_job_history ON employees CASCADE;
CREATE OR REPLACE FUNCTION trigger_fct_update_job_history() RETURNS trigger AS 
$BODY$BEGIN  add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMESTAMP,    
              OLD.job_id, OLD.department_id);RETURN NEW;END$BODY$ LANGUAGE 
'plpgsql';
CREATE TRIGGER update_job_history   AFTER UPDATE ON employees FOR EACH ROW  
EXECUTE PROCEDURE trigger_fct_update_job_history();

when I try to run the above-generated script it will show below error.
ERROR:  syntax error at or near "add_job_history"LINE 4:   
add_job_history(OLD.employee_id, OLD.hire_date, LOCALTIMES...          ^NOTICE: 
 relation "employees" does not exist, skipping


Please Suggest or help to resolve it.
-Pawan


 

Re: [GENERAL] dump to pg

[Neil Anderson]

On 1 June 2017 at 17:37, Nicolas Paris  wrote:
>> If they aren't too big, you might get away by installing the express edition 
>> of the respective DBMS, then import them using the native tools, then export 
>> the data as CSV files.

Good idea. I think SQL Server Express is limited to 10GB on the later
versions. Another tool that can read SQL Server backups is SQL Data
Compare from Redgate, it has a 14 day trial.

http://www.red-gate.com/products/sql-development/sql-data-compare/


Neil Anderson
n...@postgrescompare.com
https://www.postgrescompare.com



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] dump to pg

[Adrian Klaver]

On 06/02/2017 09:31 AM, Neil Anderson wrote:

On 1 June 2017 at 17:37, Nicolas Paris  wrote:

If they aren't too big, you might get away by installing the express edition of 
the respective DBMS, then import them using the native tools, then export the 
data as CSV files.


Good idea. I think SQL Server Express is limited to 10GB on the later
versions. Another tool that can read SQL Server backups is SQL Data
Compare from Redgate, it has a 14 day trial.

http://www.red-gate.com/products/sql-development/sql-data-compare/


Or spin up an AWS SQL Server instance:

https://aws.amazon.com/windows/resources/amis/




Neil Anderson
n...@postgrescompare.com
https://www.postgrescompare.com






--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] dump to pg

[Nicolas Paris]

> Or spin up an AWS SQL Server instance:
> 
> https://aws.amazon.com/windows/resources/amis/
> 

Thanks for the suggestion. Problem is the data is highly sensible and
cannot go on the cloud or non trusted place


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle database into PostgreSQL using Ora2PG tool.

[Neil Anderson]

On 2 June 2017 at 11:57, stevenchang1213  wrote:
>
>
> tell me where this function add_job_history() is?
> Actually, I don't think you can count on ora2pg to transform your pl/sql
> code to plpgsql or other (un)trusted procedural language code. It's not that
> simple!

I wonder, does plpgsql compilation check for existence of the
add_job_history function or is that a runtime check?

> you can type "\df  add_job_history"  in psql session to check it's existence
> if it belongs to public schema or access it using fully qualified name
> scheme.
>
>
> Steven
>
<>
-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Oracle database into PostgreSQL using Ora2PG tool.

[Igor Neyman]

I wonder, does plpgsql compilation check for existence of the add_job_history 
function or is that a runtime check?



At runtime.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] dump to pg

[Neil Anderson]

>
> Thanks for the suggestion. Problem is the data is highly sensible and
> cannot go on the cloud or non trusted place

Sounds like the real question now is not how to import the data, but
how to convert the backups you have to CSV or similar?
Another idea for SQL Server is to use the bcp utility.
https://www.simple-talk.com/sql/database-administration/working-with-the-bcp-command-line-utility/


contact
Description: Binary data

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] index duplicates primary key, but is used more?

[jonathan vanasco]

i'm doing a performance audit and noticed something odd.

we tested a table a while back, by creating lots of indexes that match 
different queries (30+).

for simplicity, here's a two column table:

CREATE TABLE foo (id INT PRIMARY KEY
  value INT NOT NULL DEFAULT 0,
  );

The indexes were generated by a script, so we had things like:

CREATE INDEX idx_test_foo_id_asc ON foo(id ASC);
CREATE INDEX idx_test_foo_id_desc ON foo(id DESC);
CREATE INDEX idx_test_foo_val_asc ON foo(value ASC);
CREATE INDEX idx_test_foo_value_desc ON foo(value DESC);

What I noticed when checking stats earlier, is that although 
`idx_test_foo_id_asc` is the same as the PKEY... it was used about 10x more 
than the pkey.

Does anyone know of this is just random (perhaps due to the name being sorted 
earlier) or there is some other reason that index would be selected ?

my concern in deleting it, is that it might be preferred for queries due to 
hinting from the explicit 'order by'  (even though the contents are the same) 
and I may lose an index being leveraged in that query.

It's on a GIANT table, so it would be hard to recreate. 



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] index duplicates primary key, but is used more?

[Tom Lane]

jonathan vanasco  writes:
> What I noticed when checking stats earlier, is that although 
> `idx_test_foo_id_asc` is the same as the PKEY... it was used about 10x more 
> than the pkey.

> Does anyone know of this is just random (perhaps due to the name being sorted 
> earlier) or there is some other reason that index would be selected ?

It's almost certainly just an artifact.  The planner considers a table's
indexes in OID order.  I don't recall offhand whether it would keep the
first or last of a series of identical-cost plans, but it'd be one or the
other of those behaviors; it would not continue to consider both indexes
once it noticed the plans were the same.

One thing that could favor a newer index is that it probably has somewhat
less bloat in it, resulting in a fractionally smaller cost estimate.  This
doesn't make it better in any absolute sense; reindexing the older index
would reverse that preference, at least for a time.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general