Steve Crawford 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 "parcours"? If so,
"_parc
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 exis
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 g
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."
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
sco
>>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
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
> l
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 c
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 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 t
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 c
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: -> Heade
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."
- Thom
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.
I'm
On Mon, 22 Jun 2009 11:40:08 +0200
Martijn van Oosterhout 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. Swapp
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 wri
On Mon, 22 Jun 2009 07:26:56 +0800
Craig Ringer 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 interpret it as a c
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 t
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 be
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 val
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, EXEC
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:
> Havi
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 t
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 tho
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 s
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 postgresql version? Why you need return the temporary table type? Why create a temporary table if you use a function return type "setof"?
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:2
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 in
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 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
netw
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 broadcast)-
> 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
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 recommend
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 WHE
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
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
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 t
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 a
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 qu
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?
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
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 on
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
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 Admin,
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
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 S
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
> 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
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
> 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
