Re: Is data import from Oracle table to Postgres table in Ora2pg consecutive or random?
I have not tried this in a while but I think a SELECT with a "hint" will return rows in the order of the index in the hint. This does NOT work for distributed queries. > On 09/12/2023 9:10 AM EDT Daniel Gustafsson wrote: > > > > On 12 Sep 2023, at 14:26, Matthias Apitz wrote: > > > > El día martes, septiembre 12, 2023 a las 02:19:19 +0200, Peter J. Holzer > > escribió: > > > >>> - Or can we add additional parameters to the ora2pg.conf file to control > >>> this > >>> process and ensure that the data is imported sequentially, following the > >>> primary key from smallest to largest? > > > > AFAIK, a simple SELECT in PostgreSQL without an ORDER BY will return the > > rows > > in random order. > > It will return the rows in some order, without guarantees about randomness or > asc/desc ordering. Any query which relies on ordering, whichever is wanted, > should include an ORDER BY clause. > > -- > Daniel Gustafsson
Re: Using "exit" to bring "goto" functionality.
I am a newbe at my current job. They have a separate log table for every table because they want all the columns. I have been looking for a way to go to a common log table without requiring major changes to the application. With your help, I have a proof of principle that I can demonstrate now. > On 07/10/2023 2:58 PM EDT Christophe Pettus wrote: > > > > On Jul 10, 2023, at 11:54, Bryn Llewellyn wrote: > > > > What is the rationale for supporting what seems to be on its face this > > strange functionality? > > It allows you to EXIT or CONTINUE a loop thaIt is not the innermost one, by > naming the label of an outer loop. > > One can debate endlessly whether it's good or bad to include that > functionality, but it definitely has use cases. I would assume that at this > point, it's not going anywhere. > > The relevant documentation is here: > > > https://www.postgresql.org/docs/15/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS
Re: Trigger Function question
I saw your message about a "few" columns and missed the new.* notation. Is there a way to get new.* into a jsonb column? > On 07/10/2023 2:38 PM EDT Christophe Pettus wrote: > > > > On Jul 10, 2023, at 11:37, DAVID ROTH wrote: > > > > Thanks for the example. I have a test trigger now that does that but my > > application needs all of the columns. > > I'm not quite sure I understanding. Logging NEW.* and OLD.* *does* get all > the columns, without having to specific query to find out which columns the > table that cause the trigger to fire has.
Re: Trigger Function question
I was hoping that NEW could be treated as a record or as an arrayy similar to pg_argv. > On 07/10/2023 2:31 PM EDT Christophe Pettus wrote: > > > > On Jul 10, 2023, at 11:29, DAVID ROTH wrote: > > > > I want to use a single trigger function to log multiple tables and the > > tables have different columns. I can get the names of the columns from the > > catalog. But I have not been able to figure out how to get NEW.x when x is > > not known until run time. > > Unless you only want to log a subset of rows from each table, it's not > required that you get the specific columns. Here's an example of how to do a > generic auditing trigger: > > https://wiki.postgresql.org/wiki/Audit_trigger > > If it's supported on your platform, you might also look at the pg_audit > extension.
Re: Trigger Function question
Thanks for the example. I have a test trigger now that does that but my application needs all of the columns. > On 07/10/2023 2:31 PM EDT Christophe Pettus wrote: > > > > On Jul 10, 2023, at 11:29, DAVID ROTH wrote: > > > > I want to use a single trigger function to log multiple tables and the > > tables have different columns. I can get the names of the columns from the > > catalog. But I have not been able to figure out how to get NEW.x when x is > > not known until run time. > > Unless you only want to log a subset of rows from each table, it's not > required that you get the specific columns. Here's an example of how to do a > generic auditing trigger: > > https://wiki.postgresql.org/wiki/Audit_trigger > > If it's supported on your platform, you might also look at the pg_audit > extension.
Re: Trigger Function question
I want to use a single trigger function to log multiple tables and the tables have different columns. I can get the names of the columns from the catalog. But I have not been able to figure out how to get NEW.x when x is not known until run time. > On 07/10/2023 2:23 PM EDT Christophe Pettus wrote: > > > > On Jul 10, 2023, at 11:20, DAVID ROTH wrote: > > > > In a trigger function, is there a way to get a list of all of the columns > > in the triggering table? > > You can get the table that the trigger fired on with TG_TABLE_SCHEMA and > TG_TABLE_NAME, and then query the system catalogs to get a list of columns. > > But can you tell us a bit more about your use-case? You may be able to write > the trigger in such a way that it doesn't need to change behavior based on > the columns.
Trigger Function question
In a trigger function, is there a way to get a list of all of the columns in the triggering table? I want to be able to use a single trigger function with multiple tables. Thanks
Trigger questions
1) Can I create a trigger on a view? 2) Do triggers cascade? Say I have an insert trigger on a table. And, I have an insert trigger on a view that references this table If I do an insert on the view, will both triggers fire?
Schema/user/role
Is there any good reference to explain the best usage of each of these structures. I am coming from Oracle. What is the best analog to Oracle's "user". Thanks Dave Roth > On 03/20/2023 10:15 AM Ron Johnson wrote: > > > Real-time CDC is the difficult part. ora2pg (using views) can do a > static migration. No coding (unless you consider clever use of bash to > modify config files to be coding). I used it to migrate a 7TB db to > Postgresql. > > https://ora2pg.darold.net/ > > On Mon, Mar 20, 2023 at 8:58 AM Inzamam Shafiq > mailto:inzamam.sha...@hotmail.com > wrote: > > > > Hi, > > > > Hope everyone is fine. > > > > Can someone help or guide regarding Open Source tools for Oracle to > > PostgreSQL migration with real time CDC. along with this is there any > > possibility to change the structure of the database? Let me explain a > > little more, > > > > We have an Oracle DB which is around 1TB and we want to migrate to > > PostgreSQL that have a new table structure, so we want to perform data > > transformation and real time CDC from Oracle to PostgreSQL. Do we have any > > good open source tool to achieve this with No Coding involved.?? > > > > Thanks. > > > > Regards, > > > > Inzamam Shafiq > > Sr. DBA > > > > > > >
Schemas and Search Path
Is there any practical limit on the number of schemas in a database? Will the number of schemas in a user's search path impact performance? Thanks Dave Roth > On 03/20/2023 10:15 AM Ron Johnson wrote: > > > Real-time CDC is the difficult part. ora2pg (using views) can do a > static migration. No coding (unless you consider clever use of bash to > modify config files to be coding). I used it to migrate a 7TB db to > Postgresql. > > https://ora2pg.darold.net/ > > On Mon, Mar 20, 2023 at 8:58 AM Inzamam Shafiq > mailto:inzamam.sha...@hotmail.com > wrote: > > > > Hi, > > > > Hope everyone is fine. > > > > Can someone help or guide regarding Open Source tools for Oracle to > > PostgreSQL migration with real time CDC. along with this is there any > > possibility to change the structure of the database? Let me explain a > > little more, > > > > We have an Oracle DB which is around 1TB and we want to migrate to > > PostgreSQL that have a new table structure, so we want to perform data > > transformation and real time CDC from Oracle to PostgreSQL. Do we have any > > good open source tool to achieve this with No Coding involved.?? > > > > Thanks. > > > > Regards, > > > > Inzamam Shafiq > > Sr. DBA > > > > > > >
Re: Stored procedure code no longer stored in v14 and v15, changed behaviour
Is there a way to reverse engineer the original code (or its equivalent) from what is saved in the database? > On 12/02/2022 8:48 AM Dominique Devienne wrote: > > > On Thu, Dec 1, 2022 at 8:51 PM Tom Lane wrote: > > Do you really fail to see the contradictions in this? You want the > > database to preserve the original DDL, but you also want it to update > > in response to subsequent alterations. You can't have both those > > Hi. I probably didn't express myself correctly. I don't think there's > a contradiction. > > I originally wrote: > "maintaining the original, at least until a re-write is necessary on renames". > > But that I meant that the SQL would be preserved as-is, *initially*. > But that if/when a rename affecting that SQL happens, then it's fair > game to re-write it. > Because then the diff between my in-memory code-generated DDL, and the > server-side > DDL is no longer a false positive, as it is now from the "pre-emptive" > re-write. > > What is creating me pain, is the fact the re-write of the SQL is > *eager* instead of *lazy*. > I.e. I'm paying for the rewrite, even when it's not strictly necessary > (from my POV at least). > > I hope that makes more sense. Thanks, --DD
Re: Oracle to Postgress Migration
Looks good. Thanks > On 07/14/2022 3:10 PM Bruce Momjian wrote: > > > On Thu, Jul 14, 2022 at 03:06:58PM -0400, DAVID ROTH wrote: > > Has anything been published on Oracle to Postgress migration. > > > > I am finding plenty of information about schema migration but, > > I think this is the easy part. > > I have a a tremendous amount of SQL and PL/SQL code that needs to be > > translated. I know Oracle "packages" will not translate. DECODE is not > > standard and will need to be rewritten as CASE. I have seen > > feature/function translation matrices to/from Oracle and other database but > > I can't seem to find one for Postgress. > > Please point me in the right direction. > > I would start here: > > https://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion > > -- > Bruce Momjian https://momjian.us > EDB https://enterprisedb.com > > Indecision is a decision. Inaction is an action. Mark Batterson
Oracle to Postgress Migration
Has anything been published on Oracle to Postgress migration. I am finding plenty of information about schema migration but, I think this is the easy part. I have a a tremendous amount of SQL and PL/SQL code that needs to be translated. I know Oracle "packages" will not translate. DECODE is not standard and will need to be rewritten as CASE. I have seen feature/function translation matrices to/from Oracle and other database but I can't seem to find one for Postgress. Please point me in the right direction. Dave
Multiple Indexes
I understand the planner can use multiple indexes to get the best plan. Can someone point me a paper that explains how this works. Thanks
Re: General Performance Question
Agreed. The code I am migrating uses the EXECUTE immediate form for almost everything. The path of least resistance for me is to just copy the code to Postgres and change "EXECUTE IMMEDIATE" to "EXECUTE". I am asking about performance differences to see if it is worth converting code that does not have any dynamic elements. I am also hoping to get a better understanding of the way the Postgres optimizer works. Any links you could suggest for this would be appreciated. > On 11/18/2021 9:27 AM Thomas Kellerer wrote: > > > DAVID ROTH schrieb am 18.11.2021 um 15:15: > > I am working on a large Oracle to Postgres migration. > > The existing code frequently constructs a string and then uses Oracle's > > "EXECUTE IMMEDIATE" to run it. > > "EXECUTE" has the same functionality in Postgres. > > > > For example: > > CREATE or REPLACE FUNCTION djr_foo_fnc (p_emp_no IN number) > > RETURN VARCHAR2 > > AS > > v_sql VARCHAR2(1000); > > v_name VARCHAR2(30); > > BEGIN > > v_sql := 'SELECT name FROM employees'; > > v_sql := v_sql ||' WHERE employee_number = '||p_emp_no; > > EXECUTE IMMEDIATE v_sql INTO v_name; > > RETURN v_name; > > END; > > / > > > > CREATE or REPLACE FUNCTION djr_foo_fnc (p_emp_no IN number) > > RETURN VARCHAR2 > > AS > > v_name VARCHAR2(30); > > BEGIN > > SELECT name INTO v_name FROM employees > > WHERE employee_number = p_emp_no; > > RETURN v_name; > > END; > > / > > > > These are oversimplified samples of some very complex queries I need to > > migrate. > > > > How does the Postgres optimizer handle these 2 formats? > > Which format is likely to perform better? > > The query does not use any dynamic parts, so EXECUTE is not needed to begin > with. > (not even in the Oracle implementation) > > For functions that just encapsulate a SQL query, a "language sql" function > might be better: > > CREATE OR REPLACE FUNCTION djr_foo_fnc (p_emp_no integer) > RETURNS text > AS > $$ > SELECT name > FROM employees > WHERE employee_number = p_emp_no; > $$ > language sql > rows 1; > > > They are optimized together with the calling function which can be an > advantage > but doesn't have to be in all cases. > > If the function is called frequently avoiding the overhead of PL/pgSQL can > make > a difference though.
General Performance Question
I am working on a large Oracle to Postgres migration. The existing code frequently constructs a string and then uses Oracle's "EXECUTE IMMEDIATE" to run it. "EXECUTE" has the same functionality in Postgres. For example: CREATE or REPLACE FUNCTION djr_foo_fnc (p_emp_no IN number) RETURN VARCHAR2 AS v_sql VARCHAR2(1000); v_name VARCHAR2(30); BEGIN v_sql :='SELECT name FROM employees'; v_sql := v_sql ||' WHERE employee_number = '||p_emp_no; EXECUTE IMMEDIATE v_sql INTO v_name; RETURN v_name; END; / CREATE or REPLACE FUNCTION djr_foo_fnc (p_emp_no IN number) RETURN VARCHAR2 AS v_name VARCHAR2(30); BEGIN SELECT name INTO v_name FROM employees WHERE employee_number = p_emp_no; RETURN v_name; END; / These are oversimplified samples of some very complex queries I need to migrate. How does the Postgres optimizer handle these 2 formats? Which format is likely to perform better? Thanks Dave
Re: Postgres Equivalent of Oracle Package
I can see how schemas can be used to keep related tables and other object together. I may be missing something but I don't see how they can be used to emulate the "once-per-session" behavior of Oracle packages. Have I missed something? > On 11/16/2021 12:27 PM Pavel Stehule wrote: > > > Hi > > út 16. 11. 2021 v 18:23 odesílatel DAVID ROTH mailto:adapt...@comcast.net > napsal: > > > > One of the nice things about Oracle packages is that the code is > loaded and global values are set and stored only once per session. This is > very useful for values that are used repeatedly. > > > > What is the best way of emulating this behavior in Postgresql? > > > > > > you can use a schemas - you can see it in Orafce > > https://github.com/orafce/orafce > > Regards > > Pavel > >
Postgres Equivalent of Oracle Package
One of the nice things about Oracle packages is that the code is loaded and global values are set and stored only once per session. This is very useful for values that are used repeatedly. What is the best way of emulating this behavior in Postgresql?
Re: The tragedy of SQL
There was also QUEL. The original language for Ingress out of UCB. > On 09/14/2021 9:51 AM David Goodenough > wrote: > > > On Tuesday, 14 September 2021 14:06:13 BST Merlin Moncure wrote: > > On Tue, Sep 14, 2021 at 12:32 AM Guyren Howe wrote: > > > If I had $5 million to invest in a startup, I would hire as many of > the > > > core Postgres devs as I could to make a new database with all the > > > sophistication of Postgres but based on Datalog (or something > similar). > > > (Or maybe add Datalog to Postgres). If that could get traction, it > would > > > lead in a decade to a revolution in productivity in our industry. > > I've long thought that there is more algebraic type syntax sitting > > underneath SQL yearning to get out. If you wanted to try something > > like that today, a language pre-compiler or translator which converted > > the code to SQL is likely the only realistic approach if you wanted to > > get traction. History is not very kind to these approaches though and > > SQL is evolving and has huge investments behind it...much more than 5 > > million bucks. > > > > ORMs a function of poor development culture and vendor advocacy, not > > the fault of SQL. If developers don't understand or are unwilling to > > use joins in language A, they won't in language B either. > > > > merlin > Back in the day, within IBM there were two separate relational databases. > System-R (which came from San Hose) and PRTV (the Peterlee Relational Test > vehicle). As I understand it SQL came from System-R and the optimizer > (amongst other things) came from PRTV. > > PRTV > (https://en.wikipedia.org/wiki/IBM_Peterlee_Relational_Test_Vehicle_(PRTV)) > did not use SQL, and was never a released product, except with a graphical > add-on which was sold to two UK local authorities for urban planning. > > So there are (and always have been) different ways to send requests to a > relational DB, it is just that SQL won the day. >
