Обсуждение: Error when selecting rows from a temporary table in ODBC

When we execute this query in psql over linux, we don't get any errors

select sum(valor) as totalCR, count(correlativo) as docsCR, caja into tmp_cr
from transaccion as t, codigocaja as c
where c.codigo = t. codigo
and c.tipomov = 'CR'
group by caja;

select sum(valor) as totalDB, count(correlativo) as docsDB, caja into tmp_db
from transaccion t, codigocaja c
where c.codigo = t. codigo
and c.tipomov = 'DB'
group by caja;

select db.caja, totalcr, totaldb, docsdb, docscr
from tmp_db db, tmp_cr cr
where cr.caja  = db.caja;

But when we execute it on PGAdmin (Windows via ODBC) we get the following
error message: ERROR from backend during clear: 'ERROR:  relation_info:
Relation 2300301 not found

Following is the message in psqlodbc.log,

conn=57295424, query='drop table tmp_cr; drop table tmp_db; select
sum(valor) as totalCR, count(correlativo) as docsCR, caja into tmp_cr from
transaccion as t, codigocaja as c where c.codigo = t. codigo and c.tipomov =
'CR' group by caja; select sum(valor) as totalDB, count(correlativo) as
docsDB, caja into tmp_db from transaccion t, codigocaja c where c.codigo =
t. codigo and c.tipomov = 'DB' group by caja; select db.caja, totalcr,
totaldb, docsdb, docscr from tmp_db db, tmp_cr cr where cr.caja = db.caja; '
NOTICE from backend during send_query: 'NOTICE:  mdopen: couldn't open
tmp_db: No such file or directory
'
NOTICE from backend during send_query: 'NOTICE:  mdopen: couldn't open
tmp_cr: No such file or directory
'
NOTICE from backend during send_query: 'NOTICE:  mdopen: couldn't open
tmp_db: No such file or directory
'
NOTICE from backend during send_query: 'NOTICE:  mdopen: couldn't open
tmp_cr: No such file or directory
'
NOTICE from backend during send_query: 'NOTICE:  mdopen: couldn't open
tmp_cr: No such file or directory
'
NOTICE from backend during send_query: 'NOTICE:  mdopen: couldn't open
tmp_cr: No such file or directory
'
NOTICE from backend during clear: 'NOTICE:  mdopen: couldn't open tmp_db: No
such file or directory
'
NOTICE from backend during clear: 'NOTICE:  mdopen: couldn't open tmp_db: No
such file or directory
'
Command response: 'DROP'
Command response: 'SELECT'
Command response: 'SELECT'
ERROR from backend during clear: 'ERROR:  relation_info: Relation 2300301
not found
'
STATEMENT ERROR: func=SC_execute, desc='', errnum=-1, errmsg='Error while
executing the query (non-fatal)'                -----------------------------------------------------------
-                hdbc=57295424, stmt=57310092, result=63909376                manual_result=0, prepare=0, internal=0
           bindings=0, bindings_allocated=0                parameters=0, parameters_allocated=0
statement_type=6,statement='drop table tmp_cr; drop table
 
tmp_db; select sum(valor) as totalCR, count(correlativo) as docsCR, caja
into tmp_cr from transaccion as t, codigocaja as c where c.codigo = t.
codigo and c.tipomov = 'CR' group by caja; select sum(valor) as totalDB,
count(correlativo) as docsDB, caja into tmp_db from transaccion t,
codigocaja c where c.codigo = t. codigo and c.tipomov = 'DB' group by caja;
select db.caja, totalcr, totaldb, docsdb, docscr from tmp_db db, tmp_cr cr
where cr.caja = db.caja; '                stmt_with_params='drop table tmp_cr; drop table tmp_db;
select sum(valor) as totalCR, count(correlativo) as docsCR, caja into tmp_cr
from transaccion as t, codigocaja as c where c.codigo = t. codigo and
c.tipomov = 'CR' group by caja; select sum(valor) as totalDB,
count(correlativo) as docsDB, caja into tmp_db from transaccion t,
codigocaja c where c.codigo = t. codigo and c.tipomov = 'DB' group by caja;
select db.caja, totalcr, totaldb, docsdb, docscr from tmp_db db, tmp_cr cr
where cr.caja = db.caja; '                data_at_exec=-1, current_exec_param=-1, put_data=0
currTuple=-1,current_col=-1, lobj_fd=-1                maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0,
 
scroll_concurrency=1                cursor_name='SQL_CUR036A7B8C'                ----------------QResult
Info -------------------------------                fields=63909344, manual_tuples=0, backend_tuples=0,
tupleField=0, conn=0                fetch_count=0, fcount=0, num_fields=0, cursor='(NULL)'
message='(NULL)',command='DROP', notice='NOTICE:  mdopen:
 
couldn't open tmp_cr: No such file or directory
'                status=6, inTuples=0
CONN ERROR: func=SC_execute, desc='', errnum=110, errmsg='ERROR:
relation_info: Relation 2300301 not found
'           ------------------------------------------------------------           henv=63901552, conn=57295424,
status=1,num_stmts=16           sock=63901504, stmts=63901424, lobj_type=-999           ---------------- Socket Info
-------------------------------          socket=180, reverse=0, errornumber=0, errormsg='(NULL)'
buffer_in=57301892,buffer_out=57305992           buffer_filled_in=3, buffer_filled_out=0, buffer_read_in=2
 


Thank you for your help!

Ligia

Ligia Pimentel wrote:
> 
> When we execute this query in psql over linux, we don't get any errors
> 
> select sum(valor) as totalCR, count(correlativo) as docsCR, caja into tmp_cr
> from transaccion as t, codigocaja as c
> where c.codigo = t. codigo
> and c.tipomov = 'CR'
> group by caja;
> 
> select sum(valor) as totalDB, count(correlativo) as docsDB, caja into tmp_db
> from transaccion t, codigocaja c
> where c.codigo = t. codigo
> and c.tipomov = 'DB'
> group by caja;
> 
> select db.caja, totalcr, totaldb, docsdb, docscr
> from tmp_db db, tmp_cr cr
> where cr.caja  = db.caja;
> 
> But when we execute it on PGAdmin (Windows via ODBC) we get the following
> error message: ERROR from backend during clear: 'ERROR:  relation_info:
> Relation 2300301 not found
> 
> Following is the message in psqlodbc.log,
> 
> conn=57295424, query='drop table tmp_cr; drop table tmp_db; select
> sum(valor) as totalCR, count(correlativo) as docsCR, caja into tmp_cr from
> transaccion as t, codigocaja as c where c.codigo = t. codigo and c.tipomov =
> 'CR' group by caja; select sum(valor) as totalDB, count(correlativo) as
> docsDB, caja into tmp_db from transaccion t, codigocaja c where c.codigo =
> t. codigo and c.tipomov = 'DB' group by caja; select db.caja, totalcr,
> totaldb, docsdb, docscr from tmp_db db, tmp_cr cr where cr.caja = db.caja; '

Seems a chained query is issued.
Queries in a chained query are parse and analyzed all
together before the chained query's execution. Therefore
SELECT couldn't see tables which didn't exist before
the execution.

regards,
Hiroshi Inoue

Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> Ligia Pimentel wrote:
>> conn=57295424, query='drop table tmp_cr; drop table tmp_db; select
>> sum(valor) as totalCR, count(correlativo) as docsCR, caja into tmp_cr from
>> transaccion as t, codigocaja as c where c.codigo = t. codigo and c.tipomov =
>> 'CR' group by caja; select sum(valor) as totalDB, count(correlativo) as
>> docsDB, caja into tmp_db from transaccion t, codigocaja c where c.codigo =
>> t. codigo and c.tipomov = 'DB' group by caja; select db.caja, totalcr,
>> totaldb, docsdb, docscr from tmp_db db, tmp_cr cr where cr.caja = db.caja; '

> Seems a chained query is issued.
> Queries in a chained query are parse and analyzed all
> together before the chained query's execution. Therefore
> SELECT couldn't see tables which didn't exist before
> the execution.

More to the point, the later SELECTs are probably being parsed with
regard to the previous versions of tmp_cr and tmp_db that existed at
the start of the line.

This is fixed in 7.1, but in previous releases you'd best split up
that sequence of operations into multiple query submissions.
        regards, tom lane