Forums/Workbench for Netezza/Workbench Community Help

Answered

Temporary Tables disappears

alexander Chervinsky
asked this on April 02, 2013 10:51 AM

Hi we met at NZUniverse Boston. I am from CIDC/Everest Poker.

We love your product but have a major issue. Any temp table we create gets lost in few minutes. This is not an issue in winsql

So: Conenct to netezza database. creat a temp table:
eg:

create temp table t__1 as
select ac.id, substring(p.code,1,2) property_id,
row_number() over (partition by ac.id order by p.code) row_seq
from eva_affiliate_property ap, eva_affiliate_contact ac , eva_user u, eva_property p
where ac.affiliate_id = ap.affiliate_id and ac.user_id = u.id and ap.property_id = p.id
and ap.status = 'approved';

wait 5-10 minutes DO NOT DISCONNECT...
running select * from t__1 will say table not found.

 

Comments latest first

User photo
mark mayo

I agree managing connections is difficult.

Maybe a Admin, or Developer option?

Thanks for your help again!

April 15, 2013 08:03 AM
User photo
Oleg Popivnenko
Aginity LLC

That is a correct assumption, the application edition does not make any difference.

Thanks for sharing that with us, we will consider adding an option to keep a connection for a query tab open in the future release (even though it is not a good thing given the limits on the number of concurrent connection in MPP system.)

MPP != MS Sql Server :)

April 15, 2013 08:00 AM
User photo
mark mayo

Thanks for your help!

BTW. We're using Aginity for Greenplum didn't think it made a difference Netezza or Greenplum.

In the meantime we'll have to use R3.
It's unfortunate because we really like the new features:

- Implemented database grouping in the object tree;
- Implemented support for SQL parameters in unattended (command line) mode;
- Implemented load testing functionality (Tools / Load Testing menu); 

 

Here's our R3 info for reference:

Workbench: Aginity Workbench for EMC Greenplum 3.0.724.6101 (beta) (3/6/2012)
OS Version: Microsoft Windows NT 6.1.7601 Service Pack 1 (64-bit)
DBMS Version: PostgreSQL 8.2.15 (Greenplum Database 4.2.4.0 build 1) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Jan 29 2013 14:27:25
Driver Used: Greenplum Native

 

April 15, 2013 07:57 AM
User photo
Oleg Popivnenko
Aginity LLC

The option turns the connection pooling off completely. What this means in practice is that the connection is dropped physiclly after the SQL is executed and then a completely new connection is created for the next batch. In other words, this is an opposite to what you'd like to have :)

The functionality has not changed for ages so i was kinda surprised you observed any difference in behavior of v4 vs v3. Surprise...

April 15, 2013 07:44 AM
User photo
mark mayo

I see that managing connections could be a problem.  

It's interesting that in Aginity R3 temp tables it were possible, and  R4 changed.

Is it possible that we can get a toggle in the next release/upgrade to disable connection pooling?

I tried the option in Tools -> General -> Disable connection pooling and it didn't make a difference. 

April 15, 2013 07:42 AM
User photo
Oleg Popivnenko
Aginity LLC

Workbench is not keeping a connection to the database open. Netezza, as well as all MPP databases, has a limited number of server connections available. Keeping a connection for each query open would exhaust the available connections pretty quickly.

There's a connection pooling mechanism that keeps just some number of connections open on the client. Therefore, sometimes  you can see the temp tables on the consecutive exec, sometimes you cannot. That is the bahavior by design.

April 13, 2013 06:25 AM
User photo
mark mayo

@Alex if you rollback to the previous Aginity version you'll be able to use temp tables.

I was able to rollback to the previous Aginity  version.

The first time I didn't delete the registry key.

After rolling back to R3 I was able to use TEMP tables again.

April 12, 2013 11:19 AM
User photo
mark mayo

This only seems to be a recent problem.   Prior to upgrading to the latest Aginity 4..x.x I was able to create temp tables and it would be persisted for the duration of the session.

Now I'm also having the same issue of disappearing temp tables.  

I  tried to rollback my versions, but it still won't work.

Is there a way we can rollback this feature?

April 12, 2013 10:59 AM
User photo
Alex Eikleberry
I am having the same problem with temp tables disappearing. I tried using SQL dump runner but it is always freezing up on me. Also Abort never works. I am accustomed to using Microsoft query analyzer or management studio and I am very disappointed with Aginity(we just purchased Neteeza)
April 02, 2013 10:51 AM
User photo
Oleg Popivnenko
Aginity LLC
Workbench does not keep a connection open and, therefore, temporary tables are not preserved between query executions.
Please use SQL Dump Runner menu which keeps the connection open.
April 02, 2013 10:51 AM