What could cause a temp table to disappear?

François Beausoleil <francois@xxxxxxxxxxx> · Wed, 27 Feb 2013 16:19:18 -0500

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.

As I said, dev works, not prod.

Dev version is:
 PostgreSQL 9.1.3 on x86_64-apple-darwin10.8.0, compiled by i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5664), 64-bit

Production version is:
 PostgreSQL 9.1.8 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit

Any hints?
François

NOTE: Log cut at 120 characters wide for easier viewing.

[INFO ] binding-data-persister:14072 - (0.001154s) BEGIN
[INFO ] binding-data-persister:14072 - (0.014419s) CREATE TEMPORARY TABLE markets_import( LIKE markets INCLUDING ALL ) ON COMMIT DROP
[INFO ] binding-data-persister:14072 - (0.006675s) INSERT INTO "markets_import" ("market_id", "name", "geo_re", "short_name", "latitude", "longitude", "la
[INFO ] binding-data-persister:14072 - (0.004407s) CREATE TEMPORARY TABLE channels_import( LIKE channels INCLUDING ALL ) ON COMMIT DROP
[INFO ] binding-data-persister:14072 - (0.020669s) INSERT INTO "channels_import" ("channel_id", "name", "language", "cable") VALUES ('3056df00-90b7-012f-6
[INFO ] binding-data-persister:14072 - (0.002994s) CREATE TEMPORARY TABLE channel_market_memberships_import( LIKE channel_market_memberships INCLUDING ALL
[INFO ] binding-data-persister:14072 - (0.006635s) INSERT INTO "channel_market_memberships_import" ("market_id", "channel_id") VALUES ('2ec7d8f0-e5f6-012e
[INFO ] binding-data-persister:14072 - (0.009247s) CREATE TEMPORARY TABLE shows_import( LIKE shows INCLUDING ALL ) ON COMMIT DROP
[INFO ] binding-data-persister:14072 - (0.155186s) INSERT INTO "shows_import" ("show_id", "name", "hashtag", "number_of_keywords", "number_of_hashtags", "
[INFO ] binding-data-persister:14072 - (0.013038s) INSERT INTO "shows_import" ("show_id", "name", "hashtag", "number_of_keywords", "number_of_hashtags", "
[INFO ] binding-data-persister:14072 - (0.005852s) CREATE TEMPORARY TABLE episodes_import( LIKE episodes INCLUDING ALL ) ON COMMIT DROP
[INFO ] binding-data-persister:14072 - (0.053025s) INSERT INTO "episodes_import" ("episode_id", "show_id", "name") VALUES ('a2dde110-d643-012e-eba1-40400f
...
[INFO ] binding-data-persister:14072 - (0.049428s) INSERT INTO "episodes_import" ("episode_id", "show_id", "name") VALUES ('68318970-52d4-0130-b15d-7a163e
[INFO ] binding-data-persister:14072 - (0.005152s) CREATE TEMPORARY TABLE producers_import( LIKE producers INCLUDING ALL ) ON COMMIT DROP
[INFO ] binding-data-persister:14072 - (0.009256s) INSERT INTO "producers_import" ("producer_id", "name") VALUES ('e91dc8f0-d385-012e-eb99-40400fe46aa7',
[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
[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

The server's log dont' have anything outstanding in there:

2013-02-27 20:14:24.567 UTC - svanalytics@svanalytics_production 1939 (42P01) 2013-02-27 20:09:25 UTC - ERROR:  relation "airings_import" does not exist at character 51
2013-02-27 20:14:24.567 UTC - svanalytics@svanalytics_production 1939 (42P01) 2013-02-27 20:09:25 UTC - STATEMENT:      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)

<<attachment: smime.p7s>>