Re: What could cause a temp table to disappear?

On 02/27/2013 01:19 PM, François Beausoleil wrote:
> Hi all,
>
> I open a transaction, create a few temporary tables, import data in them, then insert into the final tables from the temporary tables and commit. In dev, it works, but not in prod. I'm trying to track down the cause. An outline of the generated SQL is (actual log at bottom):
>
> BEGIN;
> CREATE TEMPORARY TABLE shows_import( LIKE shows INCLUDING ALL ) ON COMMIT DROP;
> INSERT INTO shows_import VALUES (...);
>
> CREATE TEMPORARY TABLE airings_import( LIKE airings INCLUDING ALL ) ON COMMIT DROP;
> INSERT INTO airings_import VALUES (...); -- 2500 rows
> INSERT INTO airings_import VALUES (...); -- another 2500 rows
>
> INSERT INTO shows SELECT * FROM shows_import;
> INSERT INTO airings SELECT * FROM airings_import WHERE NOT EXISTS( SELECT * FROM airings WHERE ... );
> COMMIT;
>
> The error happens on the next to last step, with the following PostgreSQL error:
>
> ERROR: relation "airings_import" does not exist
> LINE 3: FROM airings_import
> ^: INSERT INTO airings
> SELECT *
> FROM airings_import
> WHERE NOT EXISTS(
> SELECT *
> FROM airings
> WHERE airings_import.show_id = airings.show_id
> AND airings_import.channel_id = airings.channel_id
> AND airings_import.start_at = airings.start_at)
>
> It surely is a case of me not the missing comma... I'm really flabbergasted by this.
>

>
> Any hints?

See in line notes below.
Also what client/library are you using to connect with?

> François
>
>
> NOTE: Log cut at 120 characters wide for easier viewing.
>
> [INFO ] binding-data-persister:14072 - (0.006859s) CREATE TEMPORARY TABLE airings_import( LIKE airings INCLUDING ALL ) ON COMMIT DROP
> [INFO ] binding-data-persister:14072 - (0.103249s) INSERT INTO "airings_import" ("airing_id", "channel_id", "show_id", "start_at", "end_at") VALUES ('61ec
> ...
> [INFO ] binding-data-persister:14072 - (0.089145s) INSERT INTO "airings_import" ("airing_id", "channel_id", "show_id", "start_at", "end_at") VALUES ('03ea

So what is happening below and does that happen on dev?

> [INFO ] binding-data-persister:14072 - (0.011618s) TRUNCATE TABLE "markets"
> [INFO ] binding-data-persister:14072 - (0.006574s) INSERT INTO "markets" SELECT * FROM "markets_import" RETURNING "market_id"
> [INFO ] binding-data-persister:14072 - (0.004189s) TRUNCATE TABLE "channels"
> [INFO ] binding-data-persister:14072 - (0.008122s) INSERT INTO "channels" SELECT * FROM "channels_import" RETURNING "channel_id"
> [INFO ] binding-data-persister:14072 - (0.003201s) TRUNCATE TABLE "channel_market_memberships"
> [INFO ] binding-data-persister:14072 - (0.006054s) INSERT INTO "channel_market_memberships" SELECT * FROM "channel_market_memberships_import" RETURNING "m
> [INFO ] binding-data-persister:14072 - (0.011205s) TRUNCATE TABLE "shows"
> [INFO ] binding-data-persister:14072 - (0.063107s) INSERT INTO "shows" SELECT * FROM "shows_import" RETURNING "show_id"
> [INFO ] binding-data-persister:14072 - (0.008198s) TRUNCATE TABLE "episodes"
> [INFO ] binding-data-persister:14072 - (0.738297s) INSERT INTO "episodes" SELECT * FROM "episodes_import" RETURNING "episode_id"
> [INFO ] binding-data-persister:14072 - (0.004846s) TRUNCATE TABLE "producers"
> [INFO ] binding-data-persister:14072 - (0.005278s) INSERT INTO "producers" SELECT * FROM "producers_import" RETURNING "producer_id"
> [ERROR] binding-data-persister:14072 - PG::Error: ERROR: relation "airings_import" does not exist
> [INFO ] binding-data-persister:14072 - (1.583302s) ROLLBACK
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com