test:
create type h3 as (id int,name char(10));
CREATE or replace FUNCTION proc17()
RETURNS SETOF h3 AS $$
DECLARE
v_rec h3;
BEGIN
create temp table abc(id int,name varchar) on commit drop;
insert into abc select 1,'lw';
insert into abc select 2,'lw2';
for v_rec in
select * from abc loop
On 02/06/2014 12:09 AM, mephysto wrote:
Hi Adrian,
it is not an artifact. This log comes from a multiplayer game, and this
is an specific test to replicate the error. Practically, there are two
users that execute the same operation, so you can see the simultaneous
selects.
My opinion was every s
I don't need local sorting, I only had to retrieve some objects from db
belongs to user.
A this point is it better unlogged tables or postgres object arrays?
Il 06/feb/2014 09:35 "alexandros_e [via PostgreSQL]" <
ml-node+s1045698n5790806...@n5.nabble.com> ha scritto:
> @mephysto I think you are t
@mephysto I think you are trying to solve the wrong type of problem. Creation
of tables (temporary or not) are not supposed to run concurrently. So, this
is not an issue of PostgreSQL but design. There are two ways to solve the
problem.
a) You could use the sessionID (provided The Glassfish server
Hi Adrian,
it is not an artifact. This log comes from a multiplayer game, and this is
an specific test to replicate the error. Practically, there are two users
that execute the same operation, so you can see the simultaneous selects.
My opinion was every session was isolated from others and tempor
On 02/05/2014 12:19 PM, Mephysto wrote:
I posted my last message via Nabble, so I think that the log is not
shown in email.
I try to repost my log via email:
DEBUG: building index "pg_toast_148085_index" on table "pg_toast_148085"
CONTEXT: SQL statement "CREATE LOCAL TEMPORARY TABLE deck_ty
On 05 Feb 2014, at 21:19, Mephysto wrote:
> I posted my last message via Nabble, so I think that the log is not shown in
> email.
>
>
> I try to repost my log via email:
>
> DEBUG: building index "pg_toast_148085_index" on table "pg_toast_148085"
> CONTEXT: SQL statement "CREATE LOCAL TEMP
I posted my last message via Nabble, so I think that the log is not shown
in email.
I try to repost my log via email:
DEBUG: building index "pg_toast_148085_index" on table "pg_toast_148085"
CONTEXT: SQL statement "CREATE LOCAL TEMPORARY TABLE deck_types
ON COMMIT DROP
On 02/05/2014 07:36 AM, mephysto wrote:
Ehm no,
at a few line befor end you can read this:
ERROR: relation "deck_types" already exists
I should have been clearer. There is no error log posted in your
previous message.
So, the error persists.
:(
--
View this message in context:
htt
Ehm no,
at a few line befor end you can read this:
ERROR: relation "deck_types" already exists
So, the error persists.
:(
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Temporary-table-already-exists-tp5789852p5790688.html
Sent from the PostgreSQL - general mailin
On 02/05/2014 07:19 AM, mephysto wrote:
Hello newly,
this is my error log:
Thanks in advance.
Seems problem is solved:)
Meph
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www
Hello newly,
this is my error log:
Thanks in advance.
Meph
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Temporary-table-already-exists-tp5789852p5790682.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general maili
mephysto wrote:
> Is it possible that it is read-uncommitted transaction isolation level?
No; there is no such thing in PostgreSQL.
The lowest isolation level is READ COMMITTED.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your s
On 01/31/2014 06:49 AM, mephysto wrote:
Dmitriy Igrishin wrote
2014-01-31 Albe Laurenz <
laurenz.albe@.gv
You could try to set log_statement to "all" and see what SQL actually
gets sent to the database.
You could also include "EXECUTE 'DROP TABLE deck_types';" in your
function.
I wou
Dmitriy Igrishin wrote
> 2014-01-31 Albe Laurenz <
> laurenz.albe@.gv
> >:
>
>> mephysto wrote:
>> > Hi Albe, this is code of my stored function:
>> > CREATE OR REPLACE FUNCTION :FUNCTION_SCHEMA.get_deck_types
>> [...]
>> > BEGIN
>> [...]
>> > CREATE LOCAL TEMPORARY T
2014-01-31 Albe Laurenz :
> mephysto wrote:
> > Hi Albe, this is code of my stored function:
> > CREATE OR REPLACE FUNCTION :FUNCTION_SCHEMA.get_deck_types
> [...]
> > BEGIN
> [...]
> > CREATE LOCAL TEMPORARY TABLE deck_types
> > ON COMMIT DROP
> >
Thank you Felix,
but I would to create temporary table from stored procedure, non from
application code.
Thanks again.
Meph
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Temporary-table-already-exists-tp5789852p5789877.html
Sent from the PostgreSQL - general mailing
Id + "( ... ) on commit preserve rows");
etc.
Then you have to add the tmpTableId to every statement in your code but it should work fine.
Gesendet: Freitag, 31. Januar 2014 um 12:04 Uhr
Von: mephysto
An: pgsql-general@postgresql.org
Betreff: Re: [GENERAL] Temporary table already
mephysto wrote:
> Hi Albe, this is code of my stored function:
> CREATE OR REPLACE FUNCTION :FUNCTION_SCHEMA.get_deck_types
[...]
> BEGIN
[...]
> CREATE LOCAL TEMPORARY TABLE deck_types
> ON COMMIT DROP
> AS
> SELECT
> stored_fu
Hi Albe,this is code of my stored function:ConnectionPool reuse connections,
of course, but how you can see from my code, the temporary table deck_types
are already defined with ON COMMIT DROP clause, so I think that my work is
not in transaction. Am I true?If so, how can I put my code in
transacti
mephysto wrote:
> in my database I'm using several stored_functions that take advantage of
> temporary table. The application that is connected to Postgres is a Java Web
> Application in a Glassfish Application Server: it is connected by a JDBC
> Connection Pool provided by Glassfish with this sett
Hi there,
in my database I'm using several stored_functions that take advantage of
temporary table. The application that is connected to Postgres is a Java Web
Application in a Glassfish Application Server: it is connected by a JDBC
Connection Pool provided by Glassfish with this settings:
Resourc
Basically, I take the same query as above and replace all occurences of
tables logs and tags with temp_logs and temp_tags, created as follow:
CREATE TEMPORARY TABLE temp_logs ON COMMIT DROP AS
SELECT * FROM logs WHERE condition ORDER BY date DESC LIMIT max_size;
CREATE TEMPORARY TABLE temp_tags
On Tue, Nov 9, 2010 at 4:47 AM, Matthieu Huin wrote:
> Hello Merlin,
>
> So far the improvement in responsiveness has been very noticeable, even
> without indexing the temporary tables. Of course, this is just trading
> accuracy for speed as I simply narrow arbitrarily the search space ...
>
> The
Hello Merlin,
So far the improvement in responsiveness has been very noticeable, even
without indexing the temporary tables. Of course, this is just trading
accuracy for speed as I simply narrow arbitrarily the search space ...
The schema I am working on is close to the one I am referencing i
On Mon, Nov 8, 2010 at 12:15 PM, Matthieu Huin wrote:
> Greetings all,
>
> I am trying to optimize SELECT queries on a large table (10M rows and more)
> by using temporary tables that are subsets of my main table, thus narrowing
> the search space to a more manageable size.
> Is it possible to tra
Greetings all,
I am trying to optimize SELECT queries on a large table (10M rows and
more) by using temporary tables that are subsets of my main table, thus
narrowing the search space to a more manageable size.
Is it possible to transfer indices (or at least use the information from
existing i
A crude way would be to put it in a block and ignore the exception
BEGIN
-- create temp table
EXCEPTION
WHEN DUPLICATE_TABLE THEN
-- Table already exists,
NULL;
END;
thx
- Amol
Szymon Guz wrote:
I want to create a temp table in a trigger... but this mu
I want to create a temp table in a trigger... but this must be created max
once for each transaction. I'd like to check in the trigger if the table
exists.
How can I check if a table is a temporary table?
regards
Szymon Guz
sanjeev kumar wrote:
Hi,
I am using EnterpriseDB(8.1) here is my individual procedure code
[snip]
Now my question is from DB side there's no error, But from UI (java) side
while calling the procedure
they are getting the null refcursor and as well as "op_errormessage" out
parameter getting th
Hi,
I am using EnterpriseDB(8.1) here is my individual procedure code written as
like:
Create or replace Procedure sp_leaveSummary(
op_viewSummary OUT sys_refcursor,
op_errormessage OUT varchar
On 13/04/2007 21:55, Merlin Moncure wrote:
in addition to the 'create table' stmt, all queries that touch the
table must also be dynamic. postgresql 8.3 will have improved plan
invalidation which will (aiui) remove this requirement.
Thanks for that - just tried it and it worked.
Ray.
-
OK, suppose in his function :
- if it does not exist, he creates the temp table, with ON COMMIT DELETE
ROWS
- if it does exists, he truncates it just to be sure
So the next execution of the function will find the temp table, it will
have the same OID, all is well.
On 4/13/07, Raymond O'Donnell <[EMAIL PROTECTED]> wrote:
Hello again all,
I'm using a temporary table in a pl/PgSQL function, and I've read the
bit in the FAQ about using EXECUTE to force the table-creation query to
be re-evaluated each time the function is called.
However, it doesn't seem to w
Hello again all,
I'm using a temporary table in a pl/PgSQL function, and I've read the
bit in the FAQ about using EXECUTE to force the table-creation query to
be re-evaluated each time the function is called.
However, it doesn't seem to work for me. The first time I call the
function, all is
In article <[EMAIL PROTECTED]>,
James Croft <[EMAIL PROTECTED]> wrote:
[given a bunch of temporary tables called session_data]
% How can I determine if one of the above relations is a temporary
% table in the current session (one of them, the first in ns 2200, is a
% normal permanent table)?
On 1/25/06, Tom Lane <[EMAIL PROTECTED]> wrote:
> Jaime Casanova <[EMAIL PROTECTED]> writes:
> > On 1/25/06, James Croft <[EMAIL PROTECTED]> wrote:
> >> How can I determine if one of the above relations is a temporary
> >> table in the current session (one of them, the first in ns 2200, is a
> >> n
Jaime Casanova <[EMAIL PROTECTED]> writes:
> On 1/25/06, James Croft <[EMAIL PROTECTED]> wrote:
>> How can I determine if one of the above relations is a temporary
>> table in the current session (one of them, the first in ns 2200, is a
>> normal permanent table)?
> SELECT n.nspname as "Schema", c
On 1/25/06, James Croft <[EMAIL PROTECTED]> wrote:
>
> On 25 Jan 2006, at 14:17, Jaime Casanova wrote:
>
> >> How can I determine what temporary tables exist in my session,
> >> bearing in mind that other sessions contain temp tables using the
> >> same names?
> >>
> >
> > just the ones you have cr
On 25 Jan 2006, at 14:17, Jaime Casanova wrote:
How can I determine what temporary tables exist in my session,
bearing in mind that other sessions contain temp tables using the
same names?
just the ones you have created in your session, temporary tables in
other sessions are invisible to you
> How can I determine what temporary tables exist in my session,
> bearing in mind that other sessions contain temp tables using the
> same names?
>
just the ones you have created in your session, temporary tables in
other sessions are invisible to you...
--
regards,
Jaime Casanova
(DBA: DataBase
Hi all,
I've had a look at through the list archives but haven't found an
answer to this one. Any suggestions appreciated (aside from ones
suggesting that I should not need to do this ;-)...
- A normal table foo is created in a database.
- Clients connect to the database, some create a tem
IIRC, any things where you're doing creates/drops
in plpgsql pretty much mean you have to generate
the statements as strings and use EXECUTE which
will prevent the plans from being cached.
On Fri, 28 Sep 2001, Dinesh Parikh wrote:
> Dear all, I have a strange problem.Document says that one can
Dear all,
I have a strange problem.Document says that one can
create a temporary table in a session. I had created a temporary table in
languge plpgsql.(Actually in a procedure). After completing my task I droped a
table within a seesion(In same Function). Now problem arises. When I again
c
[ Charset ISO-8859-1 unsupported, converting... ]
> CREATE TEMPORARY TABLE table (...
> Where is the table created? On HD (what dir), in memory,...
On disk, deleted when session exits.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610
CREATE TEMPORARY TABLE table (...
Where is the table created? On HD (what dir), in memory,...
Thanks,
Mihai Gheorghiu
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Chris Jones <[EMAIL PROTECTED]> writes:
> Now I have a table called pg_temp.1548.0, and I can't delete it:
> fastfacts=> drop table "pg_temp.1548.0";
> ERROR: class "pg_temp.1548.0" is a system catalog
> The same thing happens, whether I'm connected as a mortal user or as
> the PG super-user.
I
47 matches
Mail list logo
