CODEWITHSUNDEEP
amazon-redshifttemp-tables
SimonB
SimonB

Reputation: 1055

Redshift Temp Table Identity column

My stored procedure includes the following code:

CREATE TEMPORARY TABLE #lala
(
     idx int IDENTITY(1,1), 
     tablename nvarchar(128)
);
  
INSERT INTO #lala(tablename)
    SELECT LEFT(tablename, LEN(tablename) - 3)
    FROM SVV_EXTERNAL_TABLES 
    WHERE schemaname = 'spectrum' 
      AND tablename LIKE '%_v2';

I'm then calling it like this:

BEGIN;
CALL myschema.make_union_views('spectrum_views','spectrum','mycursor');
FETCH ALL FROM mycursor;
COMMIT;

At first it was running succesfully.

Then it began falling over, and debugging, I listed the contents of '#lala

enter image description here

I am confused as to how this has come about - that the [idx] column is not sequential ?

Hope that someone can shed some light on what might be happening?

Upvotes: 0

Views: 1012

Answers (1)

Bill Weiner
Bill Weiner

Reputation: 11032

This is by design. Redshift is a cluster and as such communications between parts of the cluster are expensive. Redshift ensures the uniqueness of identity columns but NOT sequentiality. Per the CREATE TABLE documentation (https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html):

When you load the table using an INSERT INTO [tablename] SELECT * FROM or COPY statement, the data is loaded in parallel and distributed to the node slices. To be sure that the identity values are unique, Amazon Redshift skips a number of values when creating the identity values.

Upvotes: 1

Related Questions