[BUGS] BUG #3423: cache on temporary tables in plpgsql
The following bug has been logged online: Bug reference: 3423 Logged by: vituko Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4 Operating system: debian sarge Description:cache on temporary tables in plpgsql Details: "relation with OID # does not exist" errors when accessing temporary tables in PL/PgSQL functions related : bug : 2005-12/msg00207.php : possible solution : faq : 4.19 but : I'm working on 7.4 and execute command cannot return any value (execute into is not implemented). So I cannot see any alternative. Any suggestions? I have a loop "for record in select...", for each record, I get a table name and then I do an 'execute query', creating a temporary table and from this temporary table I need a field value. What can I do? Thank you very much. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #3423: cache on temporary tables in plpgsql
Hello It's not bug. Solution is simple. You cannot drop temp table ever. Temp table is automaticly dropped when session is aborted. http://www.pgsql.cz/index.php/Automatic_execution_plan_caching_in_PL/pgSQL In PostgreSQL 8.3 this problem is solved. Regards Pavel Stehule 2007/6/30, vituko <[EMAIL PROTECTED]>: The following bug has been logged online: Bug reference: 3423 Logged by: vituko Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4 Operating system: debian sarge Description:cache on temporary tables in plpgsql Details: "relation with OID # does not exist" errors when accessing temporary tables in PL/PgSQL functions related : bug : 2005-12/msg00207.php : possible solution : faq : 4.19 but : I'm working on 7.4 and execute command cannot return any value (execute into is not implemented). So I cannot see any alternative. Any suggestions? I have a loop "for record in select...", for each record, I get a table name and then I do an 'execute query', creating a temporary table and from this temporary table I need a field value. What can I do? Thank you very much. ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] BUG #3422: Segmentation violation in PL/pgSQL
The following bug has been logged online:
Bug reference: 3422
Logged by: Holger Schurig
Email address: [EMAIL PROTECTED]
PostgreSQL version: PostgreSQL 8.3d
Operating system: Linux
Description:Segmentation violation in PL/pgSQL
Details:
PostgreSQL CVS from today
Skytools (http://pgfoundry.org/scm/?group_id=1000206) from today
After "make" && "make install" of PostgreSQL, I set PGDATA, run "initdb"
without any parameters. Then, in skytools, I do "configure". Because I'm
just interested in PyQ, I change the SUBDIRS variable in
skytools/sql/Makefile to only contain "pgq pgq_ext txid". After this, I run
"make" && "make install".
Now I run "postgres -d5" (standard postgresql.conf file from initdb). In
skytools/sql/pgdb I run "make test". Now I can observe a segmentation
violation.
The last lines of the "-d5" output are:
DEBUG: plan:
DETAIL: {PLANNEDSTMT :commandType 1 :canSetTag true :planTree {MERGEJOIN
:startup_cost
124.66 :total_cost 204.66 :plan_rows 25 :plan_width 8 :targetlist
({TARGETENTRY :expr {VAR :varno 65001 :varattno 1 :vartype 20
:vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 1} :resno 1 :resname id1
:ressortgroupref 1 :resorigtbl 0 :resorigcol 0 :resjunk false})
:qual
({NULLTEST :arg {VAR :varno 65000 :varattno 1 :vartype 20 :vartypmod
-1
:varlevelsup 0 :varnoold 2 :varoattno 1} :nulltesttype 0}) :lefttree
{SORT
:startup_cost 62.33 :total_cost 64.83 :plan_rows 1000 :plan_width 8
:targetlist ({TARGETENTRY :expr {VAR :varno 65001 :varattno 1
:vartype 20
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1} :resno 1
:resname <>
:ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false})
:qual <>
:lefttree {FUNCTIONSCAN :startup_cost 0.00 :total_cost 12.50
:plan_rows 1000
:plan_width 8 :targetlist ({TARGETENTRY :expr {VAR :varno 1
:varattno 1
:vartype 20 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1}
:resno 1
:resname <> :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk
false})
:qual <> :lefttree <> :righttree <> :initPlan <> :extParam (b)
:allParam (b)
:scanrelid 1 :funcexpr {FUNCEXPR :funcid 41492 :funcresulttype 20
:funcretset
true :funcformat 0 :args ({PARAM :paramkind 0 :paramid 1 :paramtype
41484
:paramtypmod -1})} :funccolnames ("id1") :funccoltypes <>
:funccoltypmods <>}
:righttree <> :initPlan <> :extParam (b) :allParam (b) :numCols 1
:sortColIdx
1 :sortOperators 413 :nullsFirst true} :righttree {SORT
:startup_cost 62.33
:total_cost 64.83 :plan_rows 1000 :plan_width 8 :targetlist
({TARGETENTRY
:expr {VAR :varno 65001 :varattno 1 :vartype 20 :vartypmod -1
:varlevelsup 0
:varnoold 2 :varoattno 1} :resno 1 :resname <> :ressortgroupref 0
:resorigtbl
0 :resorigcol 0 :resjunk false}) :qual <> :lefttree {FUNCTIONSCAN
:startup_cost 0.00 :total_cost 12.50 :plan_rows 1000 :plan_width 8
:targetlist
({TARGETENTRY :expr {VAR :varno 2 :varattno 1 :vartype 20 :vartypmod
-1
:varlevelsup 0 :varnoold 2 :varoattno 1} :resno 1 :resname <>
:ressortgroupref
0 :resorigtbl 0 :resorigcol 0 :resjunk false}) :qual <> :lefttree
<>
:righttree <> :initPlan <> :extParam (b) :allParam (b) :scanrelid 2
:funcexpr
{FUNCEXPR :funcid 41492 :funcresulttype 20 :funcretset true
:funcformat 0
:args ({PARAM :paramkind 0 :paramid 2 :paramtype 41484 :paramtypmod
-1})}
:funccolnames ("id2") :funccoltypes <> :funccoltypmods <>}
:righttree <>
:initPlan <> :extParam (b) :allParam (b) :numCols 1 :sortColIdx 1
:sortOperators 413 :nullsFirst true} :initPlan <> :extParam (b)
:allParam (b)
:jointype 1 :joinqual <> :mergeclauses ({OPEXPR :opno 410 :opfuncid
467
:opresulttype 16 :opretset false :args ({VAR :varno 65001 :varattno
1 :vartype
20 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1} {VAR
:varno 65000
:varattno 1 :vartype 20 :vartypmod -1 :varlevelsup 0 :varnoold 2
:varoattno
1})}) :mergeFamilies 1976 :mergeStrategies 5 :mergeNullsFirst 1}
:rtable ({RTE
:alias {ALIAS :aliasname id1 :colnames <>} :eref {ALIAS :aliasname
id1
:colnames ("id1")} :rtekind 4 :funcexpr <> :funccoltypes <>
:funccoltypmods <>
:inh false :inFromCl true :requiredPerms 2 :checkAsUser 0} {RTE
:alias {ALIAS
:aliasname id2 :colnames <>} :eref {ALIAS :aliasname id2 :colnames
("id2")}
:rtekind 4 :funcexpr <> :funccoltypes <> :funccoltypmods <> :inh
false
:inFromCl true :requiredPerms 2 :checkAsUser 0} {RTE :alias <> :eref
{ALIAS
:aliasname unnamed_join :colnames ("id1" "id2")} :rtekind 2
:jointype 1
:joinaliasvars <> :inh false :inFromCl true :requiredPerms 2
:checkAsUser 0})
:resultRelations <> :utilityStmt <> :intoClause <> :subplans <>
:rewindPlanIDs
(b) :returningLists <> :rowMarks <> :nParamExec 0}
CONTEXT: SQL s
Fwd: [BUGS] BUG #3421: Failed to create temporary batch file
-- Forwarded message -- From: Fadi Thabtah <[EMAIL PROTECTED]> Date: Sun, 1 Jul 2007 12:56:52 +0100 Subject: RE: [BUGS] BUG #3421: Failed to create temporary batch file To: Jaime Casanova <[EMAIL PROTECTED]> yes I do. Fadi From: Jaime Casanova [mailto:[EMAIL PROTECTED] Sent: Sun 7/1/2007 5:40 AM To: Fadi Thabtah Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #3421: Failed to create temporary batch file On 6/30/07, Fadi Thabtah <[EMAIL PROTECTED]> wrote: The following bug has been logged online: Bug reference: 3421 Logged by: Fadi Thabtah Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2 Operating system: Windows Description:Failed to create temporary batch file Details: I was unable to install Postgres because of the following message "Failed to create temporary batch file", which interrupts the installation. do you have write permissions on the directory you are installing at? -- regards, Jaime Casanova "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning." Richard Cook This transmission is confidential and may be legally privileged. If you receive it in error, please notify us immediately by e-mail and remove it from your system. If the content of this e-mail does not relate to the business of the University of Huddersfield, then we do not endorse it and will accept no liability. -- Atentamente, Jaime Casanova "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning." Richard Cook ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #3422: Segmentation violation in PL/pgSQL
"Holger Schurig" <[EMAIL PROTECTED]> writes: > Description:Segmentation violation in PL/pgSQL Can you provide a gdb stack trace for this? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #3418: Memory leak with sql EXCEPTION?
Viatcheslav Kalinin <[EMAIL PROTECTED]> writes:
> I've made two cases actually, simple one that I already mentioned about
> in my previous mails and more complex one that I tried to make somewhat
> close to our work case where I first encountered the problem in question.
As I said before, the only memory growth I see in the first one is the
list of subcommitted child XIDs, which we really can't get rid of.
The second one is having problems because of AFTER triggers fired for
the foreign key constraints. That we can improve, as per the attached
patch.
regards, tom lane
Index: trigger.c
===
RCS file: /cvsroot/pgsql/src/backend/commands/trigger.c,v
retrieving revision 1.210.2.1
diff -c -r1.210.2.1 trigger.c
*** trigger.c 25 Jan 2007 04:17:56 - 1.210.2.1
--- trigger.c 1 Jul 2007 17:26:52 -
***
*** 2766,2771
--- 2766,2789
afterTriggers->state_stack[my_level] = NULL;
Assert(afterTriggers->query_depth ==
afterTriggers->depth_stack[my_level]);
+ /*
+* It's entirely possible that the subxact created an event_cxt
but
+* there is not anything left in it (because all the triggers
were
+* fired at end-of-statement). If so, we should release the
context
+* to prevent memory leakage in a long sequence of
subtransactions.
+* We can detect whether there's anything of use in the context
by
+* seeing if anything was added to the global events list since
+* subxact start. (This test doesn't catch every case where the
+* context is deletable; for instance maybe the only additions
were
+* from a sub-sub-xact. But it handles the common case.)
+*/
+ if (afterTriggers->cxt_stack[my_level] &&
+ afterTriggers->events.tail ==
afterTriggers->events_stack[my_level].tail)
+ {
+ MemoryContextDelete(afterTriggers->cxt_stack[my_level]);
+ /* avoid double delete if abort later */
+ afterTriggers->cxt_stack[my_level] = NULL;
+ }
}
else
{
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
