Re: [GENERAL] temp tables ORACLE/PGSQL

[Dennis Sacks]

NO-fisher-SPAM_PLEASE wrote:
Hi
I used to work with Oracle and now tryin' PostgreSQL I'm a bit
confused.
I found that creating temp table in one session does not  make it
available for other sessions for the same user? Is this intended??
 

PostgreSQL does not support global temporary tables. This is one of the 
most painful features missing as far as porting from Oracle goes from my 
standpoint.

Yes, you need to create the temporary table at the beginning of each 
session. Also, stored procedures that use temporary tables are more 
painful to write - you need to use EXECUTE for any SQL that references a 
temporary table - read the Porting From Oracle section of the PostgreSQL 
manual. I'd recommend rereading it several times.

The other option with temporary tables is to emulate a global temporary 
table using a normal table and adding a column like this:

session_id INTEGER DEFAULT pg_backend_pid() NOT NULL
and then modifying your select/update/delete statements to include 
where session_id = pg_backend_pid() so that you only deal with the 
data from your current session.

The pg_backend_pid() guaranteed to be unique while connected. You'll 
just want to make sure you have a process for deleting rows from the 
table so if you get a pg_backend_pid() again you won't have problems.

This has the advantage of not having to create a temporary table at the 
beginning of every session, plus your stored procedures don't need to 
use EXECUTE. The disadvantage is, you'll have to have some process for 
deleting old data from the table, as it will stay around and it will 
bite you when you get the same pg_backend_pid() again down the road.

Dennis
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] temp tables ORACLE/PGSQL

[Dennis Sacks]

Tony Caduto wrote:
This is not entirely correct.   We use temp tables all the time in 
PLpgsql functions and never have to use
execute.  We have found that you have to use EXECUTE only in certain 
circumstances.

we use this in all our functions that use temp tables, and we use PG 
Lightning Admin, which refreshes the
connection each time you run a query which gets around a lot of issues 
with temp tables.

If you refresh the connection each time you run a query,  maybe you 
don't need to use EXECUTE with temporary tables in stored procedures, 
but who does that in a production database application? Most people want 
to re-use connections for performance reasons.

Dennis
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

[GENERAL] lots of puzzling log messages

[Dennis Sacks]

I am Seeing twelve of these messages every five to ten seconds in the 
Postgresql serverlog when my java application is running:

2005-04-19 16:43:03 LOG:  0: duration: 0.246 ms
LOCATION:  exec_simple_query, postgres.c:960
2005-04-19 16:43:03 LOG:  0: statement: rollback; begin;
LOCATION:  pg_parse_query, postgres.c:464
postgresql.conf has:
log_connections = true
log_duration = true
log_statement = true
log_timestamp = true
Running  postgres 7.4.2.
The Java application uses postgresql-7.4.1.jar. It is a custom developed 
application.

I am wondering what these log messages mean. Any ideas?
Dennis
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq