Обсуждение: What could cause a temp table to disappear?

Поиск
Список
Период
Сортировка

What could cause a temp table to disappear?

От
François Beausoleil
Дата:
Hi all,

I open a transaction, create a few temporary tables, import data in them, then insert into the final tables from the
temporarytables and commit. In dev, it works, but not in prod. I'm trying to track down the cause. An outline of the
generatedSQL 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
WHERENOT 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
(AppleInc. build 5664), 64-bit 

Production version is:PostgreSQL 9.1.8 on x86_64-iso-8859-1-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 )
ONCOMMIT 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_membershipsINCLUDING 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
COMMITDROP 
[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 )
ONCOMMIT 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:
INSERTINTO 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) 


Re: What could cause a temp table to disappear?

От
Adrian Klaver
Дата:
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
temporarytables and commit. In dev, it works, but not in prod. I'm trying to track down the cause. An outline of the
generatedSQL 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.klaver@gmail.com

Re: What could cause a temp table to disappear?

От
François Beausoleil
Дата:

Le 2013-02-27 à 16:45, Adrian Klaver a écrit :

On 02/27/2013 01:19 PM, François Beausoleil wrote:


Any hints?

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

I use Ruby & Sequel. Sequel is a very thin library over straight SQL.

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

This is the production log, and it's the exact same in dev. The data I'm putting in the tables is authoritative, except for airings, which may or may not already exist in the database. In dev, the INSERT INTO airings SELECT * FROM airings_import WHERE NOT EXISTS(...) statement runs to completion. That's why I'm puzzled.

Bye,
François
Вложения

Re: What could cause a temp table to disappear?

От
Adrian Klaver
Дата:
On 02/27/2013 01:48 PM, François Beausoleil wrote:
>
> Le 2013-02-27 à 16:45, Adrian Klaver a écrit :
>
>> On 02/27/2013 01:19 PM, François Beausoleil wrote:
>>
>>>
>>> Any hints?
>>
>> See in line notes below.
>> Also what client/library are you using to connect with?
>
> I use Ruby & Sequel. Sequel is a very thin library over straight SQL.
>

>
> This is the production log, and it's the exact same in dev. The data I'm
> putting in the tables is authoritative, except for airings, which may or
> may not already exist in the database. In dev, the INSERT INTO airings
> SELECT * FROM airings_import WHERE NOT EXISTS(...) statement runs to
> completion. That's why I'm puzzled.

Everything else is the same? It is acting like something is closing a
session on you. Could there be a connection pooler in the mix?

>
> Bye,
> François


--
Adrian Klaver
adrian.klaver@gmail.com

Re: What could cause a temp table to disappear?

От
Tom Lane
Дата:
Adrian Klaver <adrian.klaver@gmail.com> writes:
> On 02/27/2013 01:48 PM, François Beausoleil wrote:
>> Le 2013-02-27 à 16:45, Adrian Klaver a écrit :
>>> Also what client/library are you using to connect with?

>> I use Ruby & Sequel. Sequel is a very thin library over straight SQL.

> Everything else is the same? It is acting like something is closing a
> session on you. Could there be a connection pooler in the mix?

Or just closing a transaction --- note the ON COMMIT DROP.  I'm thinking
something in the client-side stack is doing something you don't expect.
Suggest turning on server-side logging (log_statement = all, maybe
log_connections too) rather than trusting this client-side log to be
telling the whole truth.

            regards, tom lane

Re: What could cause a temp table to disappear?

От
François Beausoleil
Дата:
Le 2013-02-27 à 16:59, Tom Lane a écrit :

> Adrian Klaver <adrian.klaver@gmail.com> writes:
>> On 02/27/2013 01:48 PM, François Beausoleil wrote:
>>> Le 2013-02-27 à 16:45, Adrian Klaver a écrit :
>>>> Also what client/library are you using to connect with?
>
>>> I use Ruby & Sequel. Sequel is a very thin library over straight SQL.
>
>> Everything else is the same? It is acting like something is closing a
>> session on you. Could there be a connection pooler in the mix?
>
> Or just closing a transaction --- note the ON COMMIT DROP.  I'm thinking
> something in the client-side stack is doing something you don't expect.
> Suggest turning on server-side logging (log_statement = all, maybe
> log_connections too) rather than trusting this client-side log to be
> telling the whole truth.

Ok, as you suspected, there are multiple connections and the wrong connection was used to execute the wrong queries. I
haven'tfully found the root cause, but I know where to look. 

Thanks for your help!
François