Re: Migrating an application with Oracle temporary tables

Hi

What I am not clear on is what the rules are as to when a
> function/procedure is effectively recompiled. Is there a danger that.
> assuming the temporary table is created for a session that one session
> might see another session's data due to the procedure having effectively
> compiled the temporary table into its definition?
>

it's not a problem - the plpgsql engine is implemented significantly
differently than PL/SQL

plpgsql code is not compiled - it is just validated in "create" time and it
is stored in original text form. When function is started first time in
session, then source code is reparsed again to syntax tree, and this tree
is evaluated. Inner SQL queries are prepared.

When you create and drop table, then prepared statements are automatically
replaned.

Sometimes can be strange (and difficult) so plpgsql is similar to PL/SQL,
but the implementation is maximally different - so some knowledges and
experience are portable (and lot of related to internals) are not portable.

Regards

Pavel

>
> While this approach does have the disadvantage of requiring the
> application to define the temporary table before using it (which could be
> as simple as using `CREATE TABLE AS SELECT * FROM prototype_table`), it
> seems simpler and potentially more performant than the approach I found
> here:
> https://www.codeproject.com/Articles/1176045/Oracle-style-global-temporary-tables-for-PostgreSQ
>
> It is also in direct opposition to this post I found:
> https://www.cybertec-postgresql.com/en/using-temporary-tables-the-way-they-should-not-be-used/
>
> So far, I have not found a case where, as long as I don’t read or write to
> the permanent table, I get the wrong results from the above approach. It
> allows me to minimize the impact on my application (basically, it means
> that at the start of any transaction that might need a certain temporary
> table, I need to manually create it. The number of places I would need to
> do that is relatively finite, so I’m willing to take that hit, in exchange
> for not having to use dynamic SQL to refer to my temporary tables.
>
>
>
>
>
>