Hi,
To enhance the performance of the queries, I use temp tables in my website
reports.
It seems that a temp table has not been dropped automatically in a certain
session, and now the report is giving an error since the temp table already
exists ?!
When I type the following, the database
On 05/23/2013 12:24 AM, Pascal Tufenkji wrote:
Hi,
To enhance the performance of the queries, I use temp tables in my
website reports.
It seems that a temp table has not been dropped automatically in a
certain session, and now the report is giving an error since the temp
table already
Steve Crawford scrawf...@pinpointresearch.com writes:
On 05/23/2013 12:24 AM, Pascal Tufenkji wrote:
When I type the following, the database gives me an error:
dragon=# CREATE TEMP TABLE _parcours (id int);
ERROR: type _parcours already exists
Perhaps you have a type or temp table named
we're having a similar situation, where FunctionA calls FunctionB inside a
cursor. FunctionB DROPs Temp table, then creates temp table. FunctionA runs
through the cursor fine but breaks after the last loop, unable to DROP
temporary table because it is being used by active queries in this
session.
Since we upgraded from postgres 8.0 to 8.4, every script where we have queries
that use temp tables now has to have an explicit drop of the temp table at the
end of the script, or it will blow up the next time it runs, saying it cannot
create the temp table because it already exists (these are
On 04/06/2011 08:12 AM, Davenport, Julie wrote:
Since we upgraded from postgres 8.0 to 8.4, every script where we have
queries that use temp tables now has to have an explicit drop of the
temp table at the end of the script, or it will blow up the next time it
runs, saying it cannot create the
On Wed, Apr 06, 2011 at 09:12:55AM -0500, Davenport, Julie wrote:
postgres 8.4 database). When we get the error, if we try to drop
the table at the command line, it says the table does not exist, yet
we cannot rerun the script unless we stop and restart the database.
What if you stop your
Since we upgraded from postgres 8.0 to 8.4, every script where we have queries
that use temp tables now has to have an explicit drop of the temp table at the
end of the script, or it will blow up the next time it runs, saying it cannot
create the temp table because it already exists (these are
On Wed, Apr 06, 2011 at 10:47:55AM -0500, Davenport, Julie wrote:
We've never explicitly closed the connection, it just seemed to close
automatically when the coldfusion script ended. Not sure how I would even do
that from a script since this is run automatically, not from the command
On Wed, Apr 06, 2011 at 09:12:55AM -0500, Davenport, Julie wrote:
postgres 8.4 database). When we get the error, if we try to drop
the table at the command line, it says the table does not exist, yet
we cannot rerun the script unless we stop and restart the database.
What if you stop your
On Apr 6, 2011, at 9:47 AM, Davenport, Julie wrote:
We’ve never explicitly closed the connection, it just seemed to close
automatically when the coldfusion script ended.
My guess is you've also upgraded coldfusion, or changed its config, and now
it's caching connections.
--
Scott Ribe
Do temp tables need to be explicitly dropped, or do the go away when the
process that created them leaves?
--
Until later, Geoffrey
I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them.
-
In response to Geoffrey :
Do temp tables need to be explicitly dropped, or do the go away when the
process that created them leaves?
The latter one.
But explicitely delete them isn't an error.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header)
I was wondering if I was going to use any of the replication
available (I'm thinking especially to pgpool but I'm also interested
in any other tool as slony, pgcluster...), should I take into
account any side effect on temp tables?
I'm currently using php and pg_connect for a web application.
On Mon, 22 Jun 2009 07:26:56 +0800
Craig Ringer cr...@postnewspapers.com.au wrote:
http://www.postgresql.org/docs/8.3/interactive/sql-createtable.html
If specified, the table is created as a temporary table.
Temporary tables are automatically dropped at the end of a
session
I'd
On Mon, Jun 22, 2009 at 09:53:59AM +0200, Ivan Sergio Borgonovo wrote:
The OS knows much less about what anonymous memory (memory not
backed by a file) means to a program and can't be as clever with
it. Swapping tends to be _much_ more CPU expensive than writing
But issuing a write to
On Mon, 22 Jun 2009 11:40:08 +0200
Martijn van Oosterhout klep...@svana.org wrote:
On Mon, Jun 22, 2009 at 09:53:59AM +0200, Ivan Sergio Borgonovo
wrote:
The OS knows much less about what anonymous memory (memory not
backed by a file) means to a program and can't be as clever
with it.
Postgres v8.2
I'm having a problem with using temporary tables and sequences in a function.
It seems that the temp tables are not being removed once the function has
completed. I'm also running into OID conflicts when I run the function in
another session. I've had to do things like this to
am Wed, dem 05.09.2007, um 6:58:30 -0700 mailte Rob folgendes:
What is the proper why to deal with temp tables and sequences? Why aren't they
being dropped after the function ends? Why do I get OID errors if I delete the
temp table/sequence at the end of the function and then try to rerun the
On Thu, Feb 08, 2007 at 12:29:37PM +0500, Shoaib Mir wrote:
Something like this will help you:
execute immediate 'create temporary table test (a number) on commit drop';
PL/pgSQL doesn't recognize immediate and number isn't a PostgreSQL
type so the above yields a syntax error. Also, EXECUTE
Agreed :) I guess missed out some details from there as I just thought he
needed to drop a temp table inside a function like this:
CREATE OR REPLACE function tempfunc (int) returns int
AS
$$
begin
execute 'create temporary table test (a numeric) on commit drop';
execute 'INSERT INTO test
Having developed a complex query, I want to wrap it up as a function
so that it can take a parameter and return a set of rows. This query
is currently written as multiple sql statements that create a few
interstitial temp tables that are then joined. If I put this into a
function definition, do
On Wed, Feb 07, 2007 at 20:40:09 -0800,
jws [EMAIL PROTECTED] wrote:
Having developed a complex query, I want to wrap it up as a function
so that it can take a parameter and return a set of rows. This query
is currently written as multiple sql statements that create a few
interstitial temp
Something like this will help you:
execute immediate 'create temporary table test (a number) on commit drop';
--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
On 2/8/07, Bruno Wolff III [EMAIL PROTECTED] wrote:
On Wed, Feb 07, 2007 at 20:40:09 -0800,
jws [EMAIL PROTECTED] wrote:
Having
Anton Melser wrote:
Hi,
I am trying to move up in the world with my sql and need to do the
following...
I have a subscribers table and I need to export to csv (semi-colon
separated) certain fields - that is fine, but I also need to export a
multi-select field from another table as one string
Hi,
I am trying to move up in the world with my sql and need to do the following...
I have a subscribers table and I need to export to csv (semi-colon
separated) certain fields - that is fine, but I also need to export a
multi-select field from another table as one string (0 to n values
separated
2006/5/25, Brandon E Hofmann [EMAIL PROTECTED]:
(..)I tried defining composite types, but get a runtime error that it isn'tavailable. That is you postgres-- William Leite AraújoEspecialista em Geoprocessamento- UFMG
Bacharel em Ciêncida da Computação - UFMGMSN:[EMAIL PROTECTED]ICQ:222159351GTalk:
2006/5/25, Brandon E Hofmann [EMAIL PROTECTED]:
(..)I tried defining composite types, but get a runtime error that it isn'tavailable. That is you postgresql version? Why you need return the temporary table type? Why create a temporary table if you use a function return type setof?
-- William
When using a temp table in plpgsql functions that has columns comprised
from many tables populated by joins, how do you specify a temp table return
type? I get an error when I specify returns setof temp_table. Also when I
specify a permanent table as the return type, I get extra blank columns
just to add on that, there is only one user for the db. so both
application accesses use the same db username and password. the web app
automatically logs into the db in with the one username and password
for both remote and local access.
---(end of
hi all,
i am working on a java based web application. this application connects
to a postgres DB.
now within some pages i am using temp tables to perform some
calculation. when i access these pages locally it works fine. however
when i access the web app remotely (as in over the web or on the
On Thu, Apr 20, 2006 at 09:21:27PM -0700, [EMAIL PROTECTED] wrote:
just to add on that, there is only one user for the db. so both
application accesses use the same db username and password. the web app
automatically logs into the db in with the one username and password
for both remote and
Hi,
one of our programs went haywire and created around 200,000 temp
tables. In the end, I restarted the db, but the temporary tables are
still around
the query
SELECT n.nspname, c.relname, c.relkind, c.relpages, c.reltuples FROM
pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
Hari Bhaskaran [EMAIL PROTECTED] writes:
one of our programs went haywire and created around 200,000 temp
tables. In the end, I restarted the db, but the temporary tables are
still around
What did you do, the old kill -9 some random process approach to
database management? The recommended
What did you do, the old kill -9 some random process approach to
database management? The recommended ways of cancelling a session
wouldn't have caused this.
I never said I kill -9 . I do pg_ctl stop
BTW, drop cascade on the namespace seems to be working.
create temp queries failed with an
I am TOP POSTING intentionally --
Thanks Mike... Very informative -- I never realized that child (INHERITED)
tables do NOT inherit the indexes from their parent... that might be part of
the solution I duplicated the parents index on the child table -- the
function still takes 4672 ms to
Performing an update to an inherited table system from inside of a stored
procedure (PLPGSQL) seems to be unusually sluggish... Does anyone have a
faster solution ? I am updating 50 records and it takes approximately 4.375
seconds + or -
The inherited table has an ON INSERT DO INSTEAD and
On Tue, Jul 12, 2005 at 10:52:24AM -0700, Greg Patnude wrote:
Performing an update to an inherited table system from inside of a stored
procedure (PLPGSQL) seems to be unusually sluggish...
Is the update slower when done inside a function than when doing
it directly (e.g., from psql)? That
Hi,
I've seen the session variable question pop up a fair bit on this list.
The temporary table solution seems good but I've got a question before
using it...
- My app creates a temp table for session vars
- UPDATE, INSERT and DELETE triggers on tables use this data
My question is:
If I run a
James Croft wrote:
Hi,
I've seen the session variable question pop up a fair bit on this list.
The temporary table solution seems good but I've got a question before
using it...
Another option is to use one of the procedural languages that provide
global variable storage. The attached examples
Hi
Thank You very much.
As I mentioned I need temp tables for storing sesssion variables.
I plan to write functions to return suitable column value and I need
them to be availabele during whole session. That makes deleteing on
commit not the best solution. For example I want to keep emp_id in one
I am assuming you need session varables for a web based app right?
For a standard client/server app created in something like VB or Delphi all you
really need is a single
connection(because most db apps are single threaded), and a temp table will
stay around until that
connection is closed, and
Dennis Sacks wrote:
The disadvantage is, you'll have to have some process for deleting
old data from the table, as it will stay around and it will bite you
when you get the same pg_backend_pid() again down the road.
Rather than use pg_backend_id(), why not just assign session IDs from a
sequence?
Hi
I used to work with Oracle and now tryin' PostgreSQL I'm a bit
confused.
I found that creating temp table in one session does not make it
available for other sessions for the same user? Is this intended??
I was tryin to use because of lack of session and package variables in
PGSQL (thats what
Hi
I used to work with Oracle and now tryin' PostgreSQL I'm a bit
confused.
I found that creating temp table in one session does not make it
available for other sessions for the same user? Is this intended??
Yes, it's natural behave of temp. tables in PostgreSQL. The life cycle of
temp
NO-fisher-SPAM_PLEASE wrote:
Hi
I used to work with Oracle and now tryin' PostgreSQL I'm a bit
confused.
I found that creating temp table in one session does not make it
available for other sessions for the same user? Is this intended??
PostgreSQL does not support global temporary tables. This
This is not entirely correct. We use temp tables all the time in PLpgsql
functions and never have to use
execute. We have found that you have to use EXECUTE only in certain
circumstances.
stored procedures that use temporary tables are more
painful to write - you need to use EXECUTE for any
On Thu, Apr 28, 2005 at 01:14:58PM -0500, Tony Caduto wrote:
This is not entirely correct. We use temp tables all the time in PLpgsql
functions and never have to use
execute. We have found that you have to use EXECUTE only in certain
circumstances.
we use this in all our functions that
Tony Caduto wrote:
This is not entirely correct. We use temp tables all the time in
PLpgsql functions and never have to use
execute. We have found that you have to use EXECUTE only in certain
circumstances.
we use this in all our functions that use temp tables, and we use PG
Lightning
We only do the connection refesh in the Lightning Admin Query editorfor
testing our SQL that uses temp tables.
refreshing the connection eliminates the OID does not exist problems.
We put everything into stored procs and use them from Delphi applications and
still never use execute in
our procs.
A message like this?
lost synchronization with server, resetting connection
Yeah, like that.
I get pissed way too often when people miss simplest things and bug me with
stupid questions, and now it's me.
Sorry guys.
---(end of broadcast)---
51 matches
Mail list logo
