Re: [HACKERS] DROP SCHEMA xxx CASCADE: ERROR: could not open relation with OID yyy
On Thu, Feb 10, 2011 at 12:03:49AM -0500, Tom Lane wrote: > strk writes: > > I've finally completed the debugging phase and have > > a minimal self-contained testcase showing the problem. > > It has to do with INITIALLY DEFERRED constraints. > > I looked into this and find that the issue is you're trying to drop a > table that has unfired AFTER TRIGGER events pending. When they finally > fire, they can't find the table anymore. > > I'm inclined to think that we should disallow that; or even more to the > point, that it'd be a good thing to apply CheckTableNotInUse() when > about to drop a table. If we disallow such cases for ALTER TABLE, then > a fortiori we should do so for DROP TABLE. Makes sense to me disallowing drop. An intuitive error message is all I was looking for. --strk; () Free GIS & Flash consultant/developer /\ http://strk.keybit.net/services.html -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DROP SCHEMA xxx CASCADE: ERROR: could not open relation with OID yyy
On Mon, Feb 07, 2011 at 02:31:49PM +0100, Dimitri Fontaine wrote:
> strk writes:
> > Do you have an idea on how to further debug this ?
>
> That usually goes with providing a self-contained test case⦠that is a
> minimum script that creates the function(s) and calls them.
I've finally completed the debugging phase and have
a minimal self-contained testcase showing the problem.
It has to do with INITIALLY DEFERRED constraints.
The testcase is attached.
--strk;
() Free GIS & Flash consultant/developer
/\ http://strk.keybit.net/services.html
-- {
CREATE OR REPLACE FUNCTION makeTopo()
RETURNS void AS $$
BEGIN
CREATE SCHEMA topo;
CREATE TABLE topo.edge (
id int4 PRIMARY KEY,
ref int4,
FOREIGN KEY(ref) REFERENCES topo.edge(id)
DEFERRABLE INITIALLY DEFERRED);
-- NOTE: returning here "fixes" the bug
--RETURN;
INSERT INTO topo.edge VALUES (1,1);
END
$$ language 'plpgsql' VOLATILE STRICT;
-- }
DROP SCHEMA IF EXISTS topo CASCADE;
BEGIN;
SELECT makeTopo();
SELECT 'topo.edge'::regclass::oid;
-- NOTE: this fixes it!
-- SET CONSTRAINTS ALL IMMEDIATE;
-- ERROR: could not open relation with OID XXX
-- NOTE: X is 'topo.edge'::regclass::oid
DROP TABLE topo.edge CASCADE;
COMMIT;
-- NOTE: doing it here is fine
--DROP TABLE topo.edge CASCADE;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DROP SCHEMA xxx CASCADE: ERROR: could not open relation with OID yyy
I've uploaded also the script output ( CASCADE traces ) : http://strk.keybit.net/tmp/could_not_open_relation.sql http://strk.keybit.net/tmp/could_not_open_relation.log And realized that the relation oid is the one first requested for deletion. Ie: DROP TABLE XXX CASCADE; .. ERROR: could not open relation with OID XXX:regclass::oid I've found two ways to avoid the error: 1. Perform the DROP TABLE outside the transaction triggering its creation 2. Avoiding population of the table being dropped (the AddEdge call) Note that the 'edge_data' table has a foreign key to itself, and the constraint is initially deferred (may have a role here, I guess ?) "next_left_edge_exists" FOREIGN KEY (abs_next_left_edge) REFERENCES.edge_data(edge_id) DEFERRABLE INITIALLY DEFERRED, "next_right_edge_exists" FOREIGN KEY (abs_next_right_edge) REFERENCES edge_data(edge_id) DEFERRABLE INITIALLY DEFERRED --strk; () Free GIS & Flash consultant/developer /\ http://strk.keybit.net/services.html -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DROP SCHEMA xxx CASCADE: ERROR: could not open relation with OID yyy
I've handled to produce a small testcase: http://strk.keybit.net/tmp/could_not_open_relation.sql It still requires postgis (svn), but if anyone has that it might help. Will try to go on with the reduction. --strk; On Mon, Feb 07, 2011 at 12:38:08PM +0100, strk wrote: > Hi all, > I'm trying to debug an ugly error triggered from a "DROP SCHEMA xxx CASCADE" > call inside a function. > > The call is the last step of the stored pl/pgsql procedure. > > I've verified that removing the "DROP SCHEMA" command from _inside_ > the function body and performing it _outside_ it (right after return) > everything works fine. > > Note that the schema that the function is trying to drop was created > by a function called by the function attempting to drop it. > Both function (the one which creates the schema and the one which > attempts to drop it) are defined as VOLATILE. > > Also, I can see traces of the DROP SCHEMA CASCADE being executed, till > the ERROR comes out (lots of traces for cascading objects). > > This is : > PostgreSQL 8.4.3 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real > (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit > > Do you have an idea on how to further debug this ? > TIA. > > --strk; > > () Free GIS & Flash consultant/developer > /\ http://strk.keybit.net/services.html -- () Free GIS & Flash consultant/developer /\ http://strk.keybit.net/services.html -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] DROP SCHEMA xxx CASCADE: ERROR: could not open relation with OID yyy
Hi all, I'm trying to debug an ugly error triggered from a "DROP SCHEMA xxx CASCADE" call inside a function. The call is the last step of the stored pl/pgsql procedure. I've verified that removing the "DROP SCHEMA" command from _inside_ the function body and performing it _outside_ it (right after return) everything works fine. Note that the schema that the function is trying to drop was created by a function called by the function attempting to drop it. Both function (the one which creates the schema and the one which attempts to drop it) are defined as VOLATILE. Also, I can see traces of the DROP SCHEMA CASCADE being executed, till the ERROR comes out (lots of traces for cascading objects). This is : PostgreSQL 8.4.3 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit Do you have an idea on how to further debug this ? TIA. --strk; () Free GIS & Flash consultant/developer /\ http://strk.keybit.net/services.html -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dyamic updates of NEW with pl/pgsql
On Fri, Mar 12, 2010 at 10:47:45AM -0800, David Fetter wrote: > On Fri, Mar 12, 2010 at 07:35:41PM +0100, Pavel Stehule wrote: > > 2010/3/12 David Fetter : > > > > > > This is, by the way, an excellent argument for including hstore in > > > core in 9.1. :) > > > > I like it - but it looking little bit strange - I thinking we need > > only one function (maybe with some special support from pl executor) > > > > begin > > update_field(NEW, 'field', value); > > > > This doesn't seem like a terribly useful addition, it being specific > to PL/pgsql. Then there's the quoting issue, which the above doesn't > quite address. Putting hstore in would let all the other PLs use it, > to the extent that they need such a thing. :) Plus pure SQL use ! I was considering using hstore for a table value too for a form of "historic table". Just to say I'd also be happy with it being core in pgsql :) --strk; () Free GIS & Flash consultant/developer /\ http://strk.keybit.net/services.html -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dyamic updates of NEW with pl/pgsql
On Wed, Mar 10, 2010 at 07:50:16AM -0500, Andrew Dunstan wrote: > Using an hstore in 9.0 it's not too bad, Does it still have a limit of 65535 bytes per field ? --strk; () Free GIS & Flash consultant/developer /\ http://strk.keybit.net/services.html -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dyamic updates of NEW with pl/pgsql
On Tue, Mar 09, 2010 at 06:59:31PM +0100, Pavel Stehule wrote: > 2010/3/9 strk : > > How can a pl/pgsql trigger change the > > values of dynamic fields in NEW record ? > > > > By "dynamic" I mean that the field name > > is a variable in the trigger context. > > > > I've been told it's easy to do with pl/perl but > > I'd like to delive a pl/pgsql solution to have > > less dependencies. > > It isn't possible yet Any workaround you may suggest ? --strk; () Free GIS & Flash consultant/developer /\ http://strk.keybit.net/services.html -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Dyamic updates of NEW with pl/pgsql
How can a pl/pgsql trigger change the values of dynamic fields in NEW record ? By "dynamic" I mean that the field name is a variable in the trigger context. I've been told it's easy to do with pl/perl but I'd like to delive a pl/pgsql solution to have less dependencies. Thanks in advance. --strk; () Free GIS & Flash consultant/developer /\ http://strk.keybit.net/services.html -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Typo in online docs
On this page: http://www.postgresql.org/docs/8.4/interactive/explicit-locking.html This chapter: 13.3.4. Advisory Locks This example: SELECT pg_advisory_lock(id) FROM foo WHERE id = 12345; -- ok SELECT pg_advisory_lock(id) FROM foo WHERE id > 12345 LIMIT 100; -- danger! SELECT pg_advisory_lock(q.id) FROM ( SELECT id FROM foo WHERE id > 12345 LIMIT 100; ) q; -- ok Last statement seems bogus to me ... --strk; () Free GIS & Flash consultant/developer /\ http://foo.keybit.net/~strk/services.html -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] FreeBSD broke with autoconf-based build
On Tue, Nov 01, 2005 at 09:10:24AM -0500, Tom Lane wrote: > [EMAIL PROTECTED] writes: > > I'm having troubles building postgis HEAD on freebsd > > using the new autoconf-based scripts. > > FreeBSD which, exactly? It makes a difference, because AFAICS from > configure newer versions of FreeBSD use ELF, and the link switches > are different then. Thank you, it's ELF_SYSTEM undefined. We just copied Makefile.shlib, not Makefile.global. We'll have to add a check for ELF_SYSTEM in our own configure script. Wouldn't this be a problem for pgxs-compiled modules as well ? --strk; ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] FreeBSD broke with autoconf-based build
I'm having troubles building postgis HEAD on freebsd using the new autoconf-based scripts. The Makefile.shlib file copied by pgsql sources adds a -Bforcearchive flag to LINK.shared with arch is freebsd, but the flag seems to be unsupported (this is from 7.2.1 to 8.0.0) Weird enough PostgreSQL build works fine (no -Bforcearchive flag used) If I remove the -Bforcearchive flag from Makefile.shlib everything seems to work fine. I suppose (but didn't test) that this would also fail with pgxs. Are there freebsd users around to test this ? --strk; ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] IMMUTABLE bug ?
I cant get the IMMUTABLE modifier meaning. The 'testme' IMMUTABLE function is invoked 3 times in the following query: # select testme(1), testme(1), testme(1); NOTICE: called NOTICE: called NOTICE: called testme | testme | testme ++ ret| ret| ret (1 row) Why is so ? shouldn't the IMMUTABLE keywork make it a single call ? >From the 8.0 manual : IMMUTABLE indicates that the function always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its argument list. If this option is given, any call of the function with all-constant arguments can be immediately replaced with the function value. The function definition: CREATE OR REPLACE FUNCTION testme(integer) RETURNS text AS ' BEGIN RAISE NOTICE ''called''; return ''ret''::text; END ' LANGUAGE 'plpgsql' IMMUTABLE STRICT; PostgreSQL version 8.0.0 --strk; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] GiST memory usage
Missing info for the report: win32 postgresql 8.0.3 maintenance_work_mem 512 btree index on oid works fine --strk; On Sat, Aug 13, 2005 at 01:12:30PM +0200, [EMAIL PROTECTED] wrote: > Hi all. > I've been reported memory limit hits > during a postgis index construction > (GiST index). > > The indexed tuples are about 425 millions. > The index key is a box2d object (4*sizeof(float)). > > The machine has 4 Gb of RAM, and operations are > peaked at ~2.4 Gb of usage. > > Is there a way to detect where is memory hold ? > > Is there an index-specific memory context for use > to early release memory blocks allocated during > operations (didn't see any use of memory contexts > in the contrib/rtree_gist) ? > > TIA > > --strk; ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] GiST memory usage
Hi all. I've been reported memory limit hits during a postgis index construction (GiST index). The indexed tuples are about 425 millions. The index key is a box2d object (4*sizeof(float)). The machine has 4 Gb of RAM, and operations are peaked at ~2.4 Gb of usage. Is there a way to detect where is memory hold ? Is there an index-specific memory context for use to early release memory blocks allocated during operations (didn't see any use of memory contexts in the contrib/rtree_gist) ? TIA --strk; ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] CVS pg_config --includedir-server broken
On Tue, Jun 28, 2005 at 08:12:16PM -0400, Bruce Momjian wrote: > strk wrote: > > The valure returned from pg_config --includedir-server > > is broken as of CVS. > > > > It points to unexistent directory: > > /home/extra/pgroot-cvs/include/server > > > > Correct value would be: > > /home/extra/pgroot-cvs/include/postgresql/server > > Well, on my system on CVS is right: A make clean did the trick, sorry for bothering. --strk; ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] CVS pg_config --includedir-server broken
The valure returned from pg_config --includedir-server is broken as of CVS. It points to unexistent directory: /home/extra/pgroot-cvs/include/server Correct value would be: /home/extra/pgroot-cvs/include/postgresql/server --strk; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] accessing postgres conf from stored procedure
On Mon, Jun 27, 2005 at 08:55:50AM -0400, Dave Cramer wrote: > you can use show xxx to show configuration values > > http://www.postgresql.org/docs/7.4/interactive/sql-show.html No direct interface for shared libs ? --strk; ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] accessing postgres conf from stored procedure
Is it possible to access postgres configuration from a C stored procedure ? I need to leverage memory usage and I'd use postgres configuration rather then a compile-time define. Is there such a configuration, if access is possible ? --strk; ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] help with corrupted database
On Wed, Mar 23, 2005 at 02:49:53PM +, Richard Huxton wrote: > strk wrote: > >On Wed, Mar 23, 2005 at 01:48:11PM +, Richard Huxton wrote: > > > >>strk wrote: > >> > >>>Hello. > >>>A memory fault in a trigger left my database > >>>in a corrupted state: > >>> > >> > >>> - I can't create temporary tables anymore > >>> (restart/vacuum full don't help) > >>> ERROR: cache lookup failed for relation 1250714 > >> > >>*What* is giving this error? Something seems to be holding onto a > >>reference to (at a guess) your temporary table. Can you identify what? > > > > > >Whatever is called from create temp table .. > > > >strk=# create temp table test1(a int); > >ERROR: cache lookup failed for relation 1250714 > >strk=# create temp table test2(a int); > >ERROR: cache lookup failed for relation 1250714 > > Have you disconnected/reconnected since deleting the temp table? Yes. And vacuum full'd. And pg_ctl stop/start. --strk; ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] help with corrupted database
On Wed, Mar 23, 2005 at 01:48:11PM +, Richard Huxton wrote: > strk wrote: > >Hello. > >A memory fault in a trigger left my database > >in a corrupted state: > > > > > - I can't create temporary tables anymore > > (restart/vacuum full don't help) > > ERROR: cache lookup failed for relation 1250714 > > *What* is giving this error? Something seems to be holding onto a > reference to (at a guess) your temporary table. Can you identify what? Whatever is called from create temp table .. strk=# create temp table test1(a int); ERROR: cache lookup failed for relation 1250714 strk=# create temp table test2(a int); ERROR: cache lookup failed for relation 1250714 PostgreSQL 8.0.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.4 (Debian) --strk; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] help with corrupted database
Hello. A memory fault in a trigger left my database in a corrupted state: - A temporary table listed in pg_class was not accessible with a select - I could not DROP it - I deleted the record from pg_class - I can't create temporary tables anymore (restart/vacuum full don't help) ERROR: cache lookup failed for relation 1250714 Any hint? --strk; ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] caches lifetime with SQL vs PL/PGSQL procs
It is embarassing for me, but I could not reproduce the bug. :( Maybe I just ended up with a corrupted database (or I was just too tired). Behaviour seems to be the same for both SQL and pl/pgsql functions on a new database (and I got rid of the old one). Sorry. --strk; On Thu, Mar 17, 2005 at 06:46:04PM -0500, Tom Lane wrote: > [EMAIL PROTECTED] writes: > > I've tried with 7.4.3 - *good* results with both SQL and PL/PGSQL > > (actually even less that best 8.0.1: 12Mb) > > > I think this makes it a bug... > > You haven't actually provided a test case that would let someone else > reproduce the problem ... > > regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] caches lifetime with SQL vs PL/PGSQL procs
On postgresql-8.0.0 I've faced a *really* weird behavior. A simple query (single table - simple function call - no index), makes postgres process grow about as much as the memory size required to keep ALL rows in memory. The invoked procedure call doesn't leak. It's IMMUTABLE. Calls other procedures (not leaking). Now. One of the other procedures it calls is an 'SQL' one. Replacing it with a correponding 'PL/PGSQL' implementation drastically reduces memory occupation: SQL: 220Mb PL/PGSQL: 13Mb The function body is *really* simple: -- SQL CREATE OR REPLACE FUNCTION get_proj4_from_srid(integer) RETURNS text AS 'SELECT proj4text::text FROM spatial_ref_sys WHERE srid= $1' LANGUAGE 'sql' IMMUTABLE STRICT; -- PL/PGSQL CREATE OR REPLACE FUNCTION get_proj4_from_srid(integer) RETURNS text AS ' BEGIN RETURN proj4text::text FROM spatial_ref_sys WHERE srid= $1; END ' LANGUAGE 'plpgsql' IMMUTABLE STRICT; Is this expected ? --strk; ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] caches lifetime with SQL vs PL/PGSQL procs
I've tried with 7.4.3 - *good* results with both SQL and PL/PGSQL (actually even less that best 8.0.1: 12Mb) I think this makes it a bug... --strk; On Wed, Mar 16, 2005 at 01:58:44PM +0100, [EMAIL PROTECTED] wrote: > I've tested with 8.0.1 and get same results. > > --strk; > > On Wed, Mar 16, 2005 at 01:04:03PM +0100, [EMAIL PROTECTED] wrote: > > On postgresql-8.0.0 I've faced a *really* weird behavior. > > > > A simple query (single table - simple function call - no index), > > makes postgres process grow about as much as the memory size required > > to keep ALL rows in memory. > > > > The invoked procedure call doesn't leak. > > It's IMMUTABLE. > > Calls other procedures (not leaking). > > > > Now. > > One of the other procedures it calls is an 'SQL' one. > > Replacing it with a correponding 'PL/PGSQL' implementation > > drastically reduces memory occupation: > > > > SQL: 220Mb > > PL/PGSQL: 13Mb > > > > The function body is *really* simple: > > > > -- SQL > > CREATE OR REPLACE FUNCTION get_proj4_from_srid(integer) RETURNS text AS > > 'SELECT proj4text::text FROM spatial_ref_sys WHERE srid= $1' > > LANGUAGE 'sql' IMMUTABLE STRICT; > > > > -- PL/PGSQL > > CREATE OR REPLACE FUNCTION get_proj4_from_srid(integer) RETURNS text AS > > ' BEGIN > > RETURN proj4text::text FROM spatial_ref_sys WHERE srid= $1; > > END > > ' LANGUAGE 'plpgsql' IMMUTABLE STRICT; > > > > > > Is this expected ? > > > > --strk; > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] caches lifetime with SQL vs PL/PGSQL procs
I've tested with 8.0.1 and get same results. --strk; On Wed, Mar 16, 2005 at 01:04:03PM +0100, [EMAIL PROTECTED] wrote: > On postgresql-8.0.0 I've faced a *really* weird behavior. > > A simple query (single table - simple function call - no index), > makes postgres process grow about as much as the memory size required > to keep ALL rows in memory. > > The invoked procedure call doesn't leak. > It's IMMUTABLE. > Calls other procedures (not leaking). > > Now. > One of the other procedures it calls is an 'SQL' one. > Replacing it with a correponding 'PL/PGSQL' implementation > drastically reduces memory occupation: > > SQL: 220Mb > PL/PGSQL: 13Mb > > The function body is *really* simple: > > -- SQL > CREATE OR REPLACE FUNCTION get_proj4_from_srid(integer) RETURNS text AS > 'SELECT proj4text::text FROM spatial_ref_sys WHERE srid= $1' > LANGUAGE 'sql' IMMUTABLE STRICT; > > -- PL/PGSQL > CREATE OR REPLACE FUNCTION get_proj4_from_srid(integer) RETURNS text AS > ' BEGIN > RETURN proj4text::text FROM spatial_ref_sys WHERE srid= $1; > END > ' LANGUAGE 'plpgsql' IMMUTABLE STRICT; > > > Is this expected ? > > --strk; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] STABLE functions
Hello all, I saw that STABLE-defined functions
don't get replaced by their output, shoudn't they ?
Following shows that VOLATILE and STABLE functions outputs
won't get into the Filter, while IMMUTABLE will.
Documentation says that STABLE is the modifier to use for functions
which don't change output within a single query, isn't the shown one
a "single" query ?
Thanks in advance
--strk;
-- VOLATILE
update pg_proc set provolatile = 'v' where proname = 'find_srid';
UPDATE 1
explain analyze select * from input.geobit_5 where the_geom &&
setsrid('BOX3D(.7e+06 4.6e+06, .8e+06 4.83e+06)'::box3d,
find_srid('input','geobit_5','the_geom'));
QUERY PLAN
--
Seq Scan on geobit_5 (cost=0.00..1708.79 rows=1 width=379) (actual
time=586.979..1099.565 rows=255 loops=1)
Filter: (the_geom &&
setsrid('01030001000500C05C2541308C5141C05C2541CC6C5241006A2841CC6C5241006A2841308C5141C05C2541308C5141'::geometry,
find_srid('input'::character varying, 'geobit_5'::character varying,
'the_geom'::character varying)))
Total runtime: 1099.989 ms
(3 rows)
-- STABLE
update pg_proc set provolatile = 's' where proname = 'find_srid';
UPDATE 1
explain analyze select * from input.geobit_5 where the_geom &&
setsrid('BOX3D(.7e+06 4.6e+06, .8e+06 4.83e+06)'::box3d,
find_srid('input','geobit_5','the_geom'));
QUERY PLAN
--
Index Scan using geobit_5_gist on geobit_5 (cost=0.00..6.02 rows=1 width=379)
(actual time=2.084..42.157 rows=255 loops=1)
Index Cond: (the_geom &&
setsrid('01030001000500C05C2541308C5141C05C2541CC6C5241006A2841CC6C5241006A2841308C5141C05C2541308C5141'::geometry,
find_srid('input'::character varying, 'geobit_5'::character varying,
'the_geom'::character varying)))
Total runtime: 42.835 ms
(3 rows)
-- IMMUTABLE
update pg_proc set provolatile = 'i' where proname = 'find_srid';
UPDATE 1
explain analyze select * from input.geobit_5 where the_geom &&
setsrid('BOX3D(.7e+06 4.6e+06, .8e+06 4.83e+06)'::box3d,
find_srid('input','geobit_5','the_geom'));
QUERY PLAN
Index Scan using geobit_5_gist on geobit_5 (cost=0.00..1095.52 rows=298
width=379) (actual time=0.127..18.010 rows=255 loops=1)
Index Cond: (the_geom &&
'010320787F01000500C05C2541308C5141C05C2541CC6C5241006A2841CC6C5241006A2841308C5141C05C2541308C5141'::geometry)
Total runtime: 18.276 ms
(3 rows)
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[HACKERS] typeoid by name for PG72
Hello, I'm trying to make an array constructor function work for PGSQL from 72 to 80. My current problem is that in PG73 the ArrayType structure wants an elementtype but FmgrInfo does not contain the Oid of the given argument (only of the function). Is there an "easy" way to either: - obtain OID of a given argument - obtain OID of a given type by its name By "easy" I mean not involving a query to the backend. --strk; For standing up against patentability of software, Thank You, Poland! Read the intervention:http://kwiki.ffii.org/ConsPolon041221En Send your thanks: thankyoupoland.info Read/do more: http://www.noepatents.org/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] fmgr_oldstyle calls from 8.0.0RC2 trigger
Sorry, my fault, just mistyped the function name in PG_FUNCTION_INFO_V1() macro. --strk; On Wed, Dec 29, 2004 at 11:08:28AM +0100, strk wrote: > I'm trying to implement a trigger within 8.0.0RC2. > I get a segfault when the trigger is fired, valgrind > reports an illegal read being the line: > > TriggerData *trigdata = (TriggerData *) fcinfo->context; > > Valgrind reports: > > ==941== Use of uninitialised value of size 4 > ==941==at 0x3CD9C197: cache_bbox (lwgeom_triggers.c:10) > ==941==by 0x825C274: fmgr_oldstyle (fmgr.c:637) > ==941==by 0x81258C8: ExecCallTriggerFunc (trigger.c:1149) > ==941==by 0x812646F: ExecBRUpdateTriggers (trigger.c:1525) > > Isn't fmgr_oldstyle the Version 0 function caller ? > Shouldn't triggers be defined as Version 1 ? > > TIA > > --strk; > > For standing up against patentability of software, > > Thank You, Poland! > > Read the intervention:http://kwiki.ffii.org/ConsPolon041221En > Send your thanks: thankyoupoland.info > Read/do more: http://www.noepatents.org/ > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings -- For standing up against patentability of software, Thank You, Poland! Read the intervention:http://kwiki.ffii.org/ConsPolon041221En Send your thanks: thankyoupoland.info Read/do more: http://www.noepatents.org/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] fmgr_oldstyle calls from 8.0.0RC2 trigger
I'm trying to implement a trigger within 8.0.0RC2. I get a segfault when the trigger is fired, valgrind reports an illegal read being the line: TriggerData *trigdata = (TriggerData *) fcinfo->context; Valgrind reports: ==941== Use of uninitialised value of size 4 ==941==at 0x3CD9C197: cache_bbox (lwgeom_triggers.c:10) ==941==by 0x825C274: fmgr_oldstyle (fmgr.c:637) ==941==by 0x81258C8: ExecCallTriggerFunc (trigger.c:1149) ==941==by 0x812646F: ExecBRUpdateTriggers (trigger.c:1525) Isn't fmgr_oldstyle the Version 0 function caller ? Shouldn't triggers be defined as Version 1 ? TIA --strk; For standing up against patentability of software, Thank You, Poland! Read the intervention:http://kwiki.ffii.org/ConsPolon041221En Send your thanks: thankyoupoland.info Read/do more: http://www.noepatents.org/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] join selectivity
On Thu, Dec 23, 2004 at 10:01:33AM -0500, Tom Lane wrote: > [EMAIL PROTECTED] writes: > > Doing some tests I've found out that the returned value from the > > JOINSEL is applied to REL1.rows X REL2.rows, but REL1 and REL2 > > are not 'base' table, rather relations with a number of > > rows once again estimated by other selectivity functions. > > Right. This amounts to assuming that the join conditions and the > restriction conditions are independent, which of course is bogus, > but we really don't have enough information to do better. > > regards, tom lane Doesn't JOINSEL have access to RESTRICTSEL output for REL1 and REL2 ? --strk; ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] join selectivity
On Thu, Dec 23, 2004 at 10:13:03AM -0500, Tom Lane wrote: > [EMAIL PROTECTED] writes: > > On Thu, Dec 23, 2004 at 10:01:33AM -0500, Tom Lane wrote: > >> Right. This amounts to assuming that the join conditions and the > >> restriction conditions are independent, which of course is bogus, > >> but we really don't have enough information to do better. > > > Doesn't JOINSEL have access to RESTRICTSEL output for REL1 and REL2 ? > > You could probably compare the fields of the RelOptInfo structures, > but what are you going to do with it? AFAICS you *should not* make > the join selectivity depend on that. So it should NOT depend on full number of rows either, is this right ? --strk; > > regards, tom lane > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] join selectivity
On Thu, Dec 16, 2004 at 01:56:29PM -0500, Tom Lane wrote:
> "Mark Cave-Ayland" <[EMAIL PROTECTED]> writes:
> > ... But in the case of
> > , if we're estimating the number of rows to return then
> > that becomes harder
>
> I didn't say it was easy ;-). The existing selectivity functions can't
> do better than a rough guess in such cases, and I don't expect you can
> either.
Tom, correct me if I'm wrong.
Doing some tests I've found out that the returned value from the
JOINSEL is applied to REL1.rows X REL2.rows, but REL1 and REL2
are not 'base' table, rather relations with a number of
rows once again estimated by other selectivity functions.
For example, if JOINSEL always returns 1.0, you get a different
'estimated' number of rows for a Nested Loop depending on the
presence of a condition filtering one of the tables.
Example:
test1 has 34 rows
test2 has 32 rows
a full join makes the estimate=1088 rows ( 34*32 )
a join with a filter on test2 makes estimate=34 ( 34*1 ? )
strk=# explain analyze select * from test1, test2 where test1.geom &&
test2.geom;
NOTICE: LWGEOM_gist_joinsel called (returning 1.00)
QUERY PLAN
--
Nested Loop (cost=3.37..32.17 rows=1088 width=36) (actual time=0.193..70.691
rows=983 loops=1)
Join Filter: ("inner".geom && "outer".geom)
-> Seq Scan on test2 (cost=0.00..4.32 rows=32 width=4) (actual
time=0.074..0.267 rows=32 loops=1)
-> Materialize (cost=3.37..3.71 rows=34 width=32) (actual
time=0.002..0.026 rows=34 loops=32)
-> Seq Scan on test1 (cost=0.00..3.34 rows=34 width=32) (actual
time=0.042..0.159 rows=34 loops=1)
Total runtime: 71.426 ms
(6 rows)
trk=# explain analyze select * from test1, test2 where test1.geom && test2.geom
and test2.id = 1;
NOTICE: LWGEOM_gist_joinsel called (returning 1.00)
QUERY PLAN
Nested Loop (cost=0.00..8.17 rows=34 width=44) (actual time=0.179..2.704
rows=17 loops=1)
Join Filter: ("inner".geom && "outer".geom)
-> Seq Scan on test2 (cost=0.00..4.40 rows=1 width=8) (actual
time=0.078..0.208 rows=1 loops=1)
Filter: (id = 1)
-> Seq Scan on test1 (cost=0.00..3.34 rows=34 width=36) (actual
time=0.041..0.181 rows=34 loops=1)
Total runtime: 2.819 ms
(6 rows)
Now, is the number 1 what has been estimated by
the RESTRICT selectivity estimator for
SERIAL = ?
If it is, does our JOINSEL function have access to this
information ?
TIA
--strk;
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] join selectivity
On Thu, Dec 16, 2004 at 03:12:21PM -0500, Greg Stark wrote: > > "Mark Cave-Ayland" <[EMAIL PROTECTED]> writes: > > > Well at the moment PostGIS has a RESTRICT function that takes an expression > > of the formwhere column is a column consisting of > > geometries and constant is a bounding box. This is based upon histogram > > statistics and works well. > > Are these functions that would be useful for GiST indexes in general? They provide selectivity for an 'overlap' operator. GiST is not involved in any way. Basically it provides statistical gathering for box types columns and it's analysys in estimating the number of boxes that would overlap a constant box. > What's involved in pulling them into a system? I mean, for example, a database > using RTREE (or GiST I guess) boxes and the @ operator. It uses BOX2D as a key, maybe if you provide a cast from your type to BOX2D it could work... I'd like to hear about attempt at this. > I didn't realize anyone really had any idea where to start with gathering > statistics or writing selectivity functions for geometric types. It's great > news to hear there's actually work in this area. Statistics in postgis have been available for a long time: 2002-10-12 00:52 dblasby * postgis_estimate.c: New file with original estimation methods. --strk; > > -- > greg > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] [postgis-devel] RE: join selectivity
On Mon, Dec 13, 2004 at 03:04:01PM -, Mark Cave-Ayland wrote: > Hi strk, > > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > > Sent: 13 December 2004 14:05 > > To: Mark Cave-Ayland > > Cc: [EMAIL PROTECTED] > > Subject: Re: [postgis-devel] RE: join selectivity > > > > > > On Mon, Dec 13, 2004 at 12:16:15PM -, Mark Cave-Ayland wrote: > > > Hi strk, > > > > > > (cut) > > > > > > > > Taking a look at join selectivity... > > > > > For a query like this: > > > > > > > > > > SELECT id FROM table1, table2 > > > > > WHERE table1.geom && table2.geom; > > > > > > > > > > RESTRICT selectivity is invoked twice and > > > > > JOIN selectivity is invoked once. > > > > > The RESTRICT code is not able to find a costant part > > > > > and thus returns the default value (0.05), > > > > > JOIN selectivity so far returns an hard-wired 0.1. > > > > > > > > > > Questions: > > > > > (1) What should RESTRICT selectivity do in this case ?! > > > > > > > Maybe that's how the planner decide what to do: > > > > 1) sequencially scan table1 and use index for each row > > > > (RESTRICT) > > > > 2) sequencially scan table2 and use index for each row > > > > (RESTRICT) > > > > 3) ... some other magic I'm missing .. (JOIN) > > > > > > Indeed, you could be on the right lines here in thinking > > the planner > > > considers some form of individual scan on each first before > > finalising > > > on a plan type (although unless the tables are small I would have > > > thought this would not have been an option). Does this > > change if you > > > do a SET ENABLE_SEQSCAN = 'f' before the query? > > > > Bingo. > > Both ENABLE_SEQSCAN = 'f' or unavailability of an index make > > the selectivity estimator calls go away. The join selectivity > > is called nonetheless (also in absence of indexes). > > Right. So what you're saying is that if there is *no* GiST index on *one* of > the geom columns, or sequential scans are disabled, then the calls to > RESTRICT go away? Index on a single table makes 2 calls to RESTRICT: strk=# select * from test1, test2 where test1.geom && test2.geom; NOTICE: LWGEOM_gist_joinsel called (returning 0.05) NOTICE: LWGEOM_gist_sel called NOTICE: no constant arguments - returning default selectivity NOTICE: LWGEOM_gist_sel called NOTICE: no constant arguments - returning default selectivity Index on a both table makes 4 calls to RESTRICT: strk=# select * from test1, test2 where test1.geom && test2.geom; NOTICE: LWGEOM_gist_joinsel called (returning 0.05) NOTICE: LWGEOM_gist_sel called NOTICE: no constant arguments - returning default selectivity NOTICE: LWGEOM_gist_sel called NOTICE: no constant arguments - returning default selectivity NOTICE: LWGEOM_gist_sel called NOTICE: no constant arguments - returning default selectivity NOTICE: LWGEOM_gist_sel called NOTICE: no constant arguments - returning default selectivity No index makes no calls to RESTRICT: strk=# select * from test1, test2 where test1.geom && test2.geom; NOTICE: LWGEOM_gist_joinsel called (returning 0.05) > > > > It just seems strange for aclause to > > > call a function involving a constant. Again, I'd probably ask on > > > pgsql-hackers just to clarify - I think Tom Lane was > > involved with the > > > planner, so will be able to answer this one fairly quickly. > > CCd to pgsql-hackers for clarification. BTW which version are you developing > against - 7.4 or 8.0? 8.0.0RC1 --strk; > > > Kind regards, > > Mark. > > > WebBased Ltd > South West Technology Centre > Tamar Science Park > Plymouth > PL6 8BT > > T: +44 (0)1752 791021 > F: +44 (0)1752 791023 > W: http://www.webbased.co.uk > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] join selectivity
On Mon, Dec 13, 2004 at 10:16:09AM -, Mark Cave-Ayland wrote: > > > -Original Message- > > From: strk [mailto:[EMAIL PROTECTED] > > Sent: 10 December 2004 15:35 > > To: Mark Cave-Ayland > > Cc: [EMAIL PROTECTED] > > Subject: join selectivity > > > > > > Taking a look at join selectivity... > > For a query like this: > > > > SELECT id FROM table1, table2 > > WHERE table1.geom && table2.geom; > > > > RESTRICT selectivity is invoked twice and > > JOIN selectivity is invoked once. > > The RESTRICT code is not able to find a costant part > > and thus returns the default value (0.05), > > JOIN selectivity so far returns an hard-wired 0.1. > > > > Questions: > > (1) What should RESTRICT selectivity do in this case ?! > > (2) Is JOIN selectivity a fraction of table2 X table1 > > records ? > > > Hi strk, > > Really??! I can't see why the RESTRICT selectivity should be called - the > only thing I can think of is that it's being called as some part of cast or > query rewriting. Maybe that's how the planner decide what to do: 1) sequencially scan table1 and use index for each row (RESTRICT) 2) sequencially scan table2 and use index for each row (RESTRICT) 3) ... some other magic I'm missing .. (JOIN) > > Hmmm good question - the wording in the documentation is "The idea behind a > join selectivity estimator is to guess what fraction of the rows in a pair > of tables will satisfy a WHERE-clause condition of the form" which is > slightly ambiguous - I would ask on pgsql-hackers now that the mailing lists > are working normally again. I've tested this. It is a fraction of table2.rows X table1.rows. 0.1 is probably a big number for that... --strk; > > > Kind regards, > > Mark. > > > WebBased Ltd > South West Technology Centre > Tamar Science Park > Plymouth > PL6 8BT > > T: +44 (0)1752 791021 > F: +44 (0)1752 791023 > W: http://www.webbased.co.uk > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] 800RC1 valgrind-detected bug ?
Hi all. I'm getting error reports from valgrind while debugging postgis. It seems that the error only shows up when I build a GiST index AND vacuum analyze. If I drop the index the error goes away. If I create the index the error still doesn't show. If I vacuum analyze, the error is back, but not always. The query does not involve indexed operators. Any hint about what can couse this ? - versions: valgrind-2.1.2.CVS psql 8.0.0rc1 ==15489== Syscall param write(buf) contains uninitialised or unaddressable byte( s) ==15489==at 0x3C1AF9B8: write (in /lib/libc-2.3.2.so) ==15489==by 0x3C14F67D: (within /lib/libc-2.3.2.so) ==15489==by 0x3C14F615: _IO_do_write (in /lib/libc-2.3.2.so) ==15489==by 0x3C150138: _IO_file_overflow (in /lib/libc-2.3.2.so) ==15489==by 0x3C150D1F: _IO_file_xsputn (in /lib/libc-2.3.2.so) ==15489==by 0x3C145F8E: _IO_fwrite (in /lib/libc-2.3.2.so) ==15489==by 0x82062CE: write_relcache_init_file (in /pgroot-800/bin/postgres ) ==15489==by 0x82130AC: InitPostgres (in /pgroot-800/bin/postgres) ==15489==by 0x819B16A: PostgresMain (in /pgroot-800/bin/postgres) ==15489==by 0x813B2E2: main (in /pgroot-800/bin/postgres) ==15489== Address 0x3C0220F8 is not stack'd, malloc'd or (recently) free'd ==15489== ==15489== Syscall param write(buf) contains uninitialised or unaddressable byte( s) ==15489==at 0x3C1AF9B8: write (in /lib/libc-2.3.2.so) ==15489==by 0x3C14F67D: (within /lib/libc-2.3.2.so) ==15489==by 0x3C14F615: _IO_do_write (in /lib/libc-2.3.2.so) ==15489==by 0x3C14EF67: _IO_file_close_it (in /lib/libc-2.3.2.so) ==15489==by 0x3C144CE9: _IO_fclose (in /lib/libc-2.3.2.so) ==15489==by 0x8189F4E: FreeDesc (in /pgroot-800/bin/postgres) ==15489==by 0x820677E: write_relcache_init_file (in /pgroot-800/bin/postgres ) ==15489==by 0x82130AC: InitPostgres (in /pgroot-800/bin/postgres) ==15489==by 0x819B16A: PostgresMain (in /pgroot-800/bin/postgres) ==15489==by 0x813B2E2: main (in /pgroot-800/bin/postgres) ==15489== Address 0x3C022200 is not stack'd, malloc'd or (recently) free'd TIA --strk; ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] join selectivity
On Mon, Dec 13, 2004 at 12:16:05PM -0500, Tom Lane wrote: > "Mark Cave-Ayland" <[EMAIL PROTECTED]> writes: > > For a query like this: > > > > SELECT id FROM table1, table2 > > WHERE table1.geom && table2.geom; > > > > RESTRICT selectivity is invoked twice and > > JOIN selectivity is invoked once. > > Hm, are you testing in a context where both tables have indexes that are > relevant to the && operator? Single index relevant to the && operator makes 2 calls to RESTRICT. Double index (one for each table) makes 4 calls to RESTRICT. In both cases JOIN is called once. --strk; > The estimated join result size is computed from the join selectivity > estimate for the && operator. I was about to say that restriction > selectivity wouldn't be used at all, but on second thought I believe > that it would be invoked while considering nestloop with inner indexscan > plans. That is, we'd consider > > NestLoop > Seq Scan on table2 > Indexscan on table1 > IndexCond: table1.geom && outer.geom > > and to determine the estimated cost of each indexscan, we would invoke > restriction selectivity for &&, with varRelid referencing table1. > Given this call you are supposed to treat table2.geom as a constant of > uncertain value, so the thing is semantically sensible as a restriction > clause for table1 (whether you can produce a really good estimate is > another question :-(). > > Similarly, we'd consider the reverse plan with table1 as outer, and > that would give rise to another restriction selectivity check with > varRelid = table2. > > >> (2) Is JOIN selectivity a fraction of table2 X table1 > >> records ? > > Yes. Similarly restriction selectivity is a fraction of records in the > table under consideration. > > regards, tom lane > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] pg_restore segfault with pg-CVS
On Tue, Sep 14, 2004 at 10:55:46AM -0400, Tom Lane wrote: > strk <[EMAIL PROTECTED]> writes: > > Trying to make minimal example I found out it's pg_dump too. > > Example: > > $ pg_dump template1 > > ... > > Segmentation Fault > > You've got a broken build then, or a platform-specific problem. > Check shared library linkage, maybe? > > regards, tom lane Yup. Was mismatching binary/library. Thanks to all. --strk; ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] pg_restore segfault with pg-CVS
On Tue, Sep 14, 2004 at 10:45:16PM +1000, Philip Warner wrote: > At 06:55 PM 14/09/2004, strk wrote: > >Current pg_restore segfaults. > > You might need to give a little more information; does it only segfault for > a specific DB? Does it segfault for a trivial empty DB? Can you construct a > minimal example? Trying to make minimal example I found out it's pg_dump too. Example: $ pg_dump template1 ... Segmentation Fault --strk; > > > > Philip Warner| __---_ > Albatross Consulting Pty. Ltd. |/ - \ > (A.B.N. 75 008 659 498) | /(@) __---_ > Tel: (+61) 0500 83 82 81 | _ \ > Fax: (+61) 03 5330 3172 | ___ | > Http://www.rhyme.com.au |/ \| > |---- > PGP key available upon request, | / > and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] gist cost estimator failing
This might be gist-related or not. The cost estimator is wrongly estimating an higher cost on Index Scan then it estimates on Seq Scan (while reality shows the difference). I've found out that the gistcostestimate function is just a stub calling the genericcostestimate, can this be the problem ? Can you point me to where to inspect further ? Seq Scan on plmshp02_1 (cost=0.00..19.14 rows=26 width=81) (actual time=0.54..61.68 rows=75 loops=1) Total runtime: 61.83 msec Index Scan using plmshp02_1_gist on plmshp02_1 (cost=0.00..81.53 rows=26 width=81) (actual time=0.39..19.22 rows=75 loop=1) Total runtime: 19.37 msecs --strk; ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] pg_restore segfault with pg-CVS
Current pg_restore segfaults. Manually fault seems to happen at (*AH->ClosePtr) (AH) in pg_backup_archiver.c/CloseArchive(Archive *) --strk; ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] returning modified input from C functions
On Mon, Aug 23, 2004 at 03:20:09PM -0400, Tom Lane wrote: > strk <[EMAIL PROTECTED]> writes: > > Is returning a pointer to the input safe in every case ? > > You mean for returning *unmodified* input, don't you? > > Yes, that's safe. Scribbling on input values is definitely never safe. > > regards, tom lane I meant scribbling actually. Would PG_DETOAST_DATUM_COPY() make it safe then ? --strk; ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] returning modified input from C functions
Is returning a pointer to the input safe in every case ? --strk; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Dump Version
On Fri, Aug 13, 2004 at 11:12:50PM +1000, Philip Warner wrote: > At 10:30 PM 13/08/2004, strk wrote: > >>From PG73: > >552; 2663984 CAST bytea (public.wkb) > >>From PG74: > >547; 1915318 CAST CAST (public.wkb AS bytea) > > > >Since these two dumps have same file format version I asked.. > > The file format (unfortunately for you) does not define the format of the > contents of the TOC entries. Ok. This is what I wanted to know. Thank you. --strk; ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Dump Version
Philip, I'm trying to make the process you describe automatic. A script reads objects definitoin from an .sql file and automatically deletes linkes from 'listfile' corresponding to objects already found in the given .sql file. As for my problem (detecting CASTS) I had to support lines in the form: >From PG73: 552; 2663984 CAST bytea (public.wkb) >From PG74: 547; 1915318 CAST CAST (public.wkb AS bytea) Since these two dumps have same file format version I asked.. --strk; On Fri, Aug 13, 2004 at 10:05:09PM +1000, Philip Warner wrote: > At 08:53 PM 13/08/2004, strk wrote: > >Commenting out lines from the dump corresponding to objects that > >will be defined by another script. > > I may have misunderstood, but try: > > pg_restore -l dumpfile > listfile > > then delete lines from listfile that you do not want, and do: > > pg_restore -L listfile dumpfile > > and it will only restore the items corresponding to lines in listfile. > > > > > > > Philip Warner| __---_ > Albatross Consulting Pty. Ltd. |/ - \ > (A.B.N. 75 008 659 498) | /(@) __---_ > Tel: (+61) 0500 83 82 81 | _ \ > Fax: (+61) 03 5330 3172 | ___ | > Http://www.rhyme.com.au |/ \| > |---- > PGP key available upon request, | / > and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Dump Version
On Fri, Aug 13, 2004 at 08:42:44PM +1000, Philip Warner wrote: > At 08:09 PM 13/08/2004, strk wrote: > >What does 'Dump Version' refer to then ? Are these ToC output > >changes encoded somehow ? > > The file format version of pg_dump; the actual contents depend on pg_dump > version, and the source database since pg_dump asks the source (as much as > possible) to present it's data in a textual format. > > What are you trying to do? Commenting out lines from the dump corresponding to objects that will be defined by another script. I also had problem with OPERATOR as only the name is listed (I parsed the ascii version as a workaround). I can parse the ascii version for CASTS as well... but I if everything was possible just using the ToC that would be faster. --strk; > > > > Philip Warner| __---_ > Albatross Consulting Pty. Ltd. |/ - \ > (A.B.N. 75 008 659 498) | /(@) __---_ > Tel: (+61) 0500 83 82 81 | _ \ > Fax: (+61) 03 5330 3172 | ___ | > Http://www.rhyme.com.au |/ \| > |---- > PGP key available upon request, | / > and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Dump Version
Hello, dump problems again. I'm working on a dump ToC parser for spatial database restore (postgis). I've noticed that pg_dump-7.3 and pg_dump-7.4 list CAST entries in different ways. Still 'Dump Version' is the same (1.7-0). What does 'Dump Version' refer to then ? Are these ToC output changes encoded somehow ? TIA --strk; ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_dump and sequences (bug ?)
With help from Christopher I've made some other tests. Neither 7.4 nor 7.5/8.0 pg_dump are able to detect the error. Here is a summary: The produced dump creates a SEQUENCE SET call with no corresponding SEQUENCE or TABLE SCHEMA creating the sequence. No Error or warning is issued at dump time, nor it is at vacuum time. A currupted catalog set is probably be the reason. Shouldn't pg_dump warn about that ? Is there a catalog corruption detection app ? TIA --strk; On Sun, Aug 08, 2004 at 01:40:40PM +0200, strk wrote: > On Sun, Aug 08, 2004 at 12:50:43PM +0800, Christopher Kings-Lynne wrote: > > Also, given this and your previous operator commutator problem, I > > strongly suspect that someone has taken an axe to the system catalogs on > > your installation and they are very screwy. > > System catalogs screws are possible. > That someone is probably me, but I don't remember pg_dump > giving me any warning about this. Also I think I've run vacuum > on the db before dumping. > > As before I'm not on the source computer so I can't send the > pg_dump -s, but if you want, I can send you the pg_restore -l. > > --strk; > > > > > > Chris > > > > > > strk wrote: > > > > >Using pg_dump from postgresql 7.3.4 I've obtained > > >a dump file containing a SEQUENCE SET with no > > >corresponding SEQUENCE. I've seen that this is usually > > >due to the presence of a table with a 'serial' field, > > >but since in this case there is no such table I wonder > > >if this is a bug in pg_dump. > > > > > >The only reason I can imagine for this is pg_dump taking > > >any sequence whose name ends in _seq as being associated > > >to a table, no matter if that table exists and has a 'serial' > > >field. Is this possible ? Shouldn't this kind of dependency > > >be coded somehow ? > > > > > >TIA > > > > > >--strk; > > > > > >---(end of broadcast)--- > > >TIP 8: explain analyze is your friend > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_dump and sequences (bug ?)
On Sun, Aug 08, 2004 at 12:50:43PM +0800, Christopher Kings-Lynne wrote: > Also, given this and your previous operator commutator problem, I > strongly suspect that someone has taken an axe to the system catalogs on > your installation and they are very screwy. System catalogs screws are possible. That someone is probably me, but I don't remember pg_dump giving me any warning about this. Also I think I've run vacuum on the db before dumping. As before I'm not on the source computer so I can't send the pg_dump -s, but if you want, I can send you the pg_restore -l. --strk; > > Chris > > > strk wrote: > > >Using pg_dump from postgresql 7.3.4 I've obtained > >a dump file containing a SEQUENCE SET with no > >corresponding SEQUENCE. I've seen that this is usually > >due to the presence of a table with a 'serial' field, > >but since in this case there is no such table I wonder > >if this is a bug in pg_dump. > > > >The only reason I can imagine for this is pg_dump taking > >any sequence whose name ends in _seq as being associated > >to a table, no matter if that table exists and has a 'serial' > >field. Is this possible ? Shouldn't this kind of dependency > >be coded somehow ? > > > >TIA > > > >--strk; > > > >---(end of broadcast)--- > >TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] pg_dump and sequences (bug ?)
Using pg_dump from postgresql 7.3.4 I've obtained a dump file containing a SEQUENCE SET with no corresponding SEQUENCE. I've seen that this is usually due to the presence of a table with a 'serial' field, but since in this case there is no such table I wonder if this is a bug in pg_dump. The only reason I can imagine for this is pg_dump taking any sequence whose name ends in _seq as being associated to a table, no matter if that table exists and has a 'serial' field. Is this possible ? Shouldn't this kind of dependency be coded somehow ? TIA --strk; ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] operator commutator
On Tue, Aug 03, 2004 at 05:17:27PM +0800, Christopher Kings-Lynne wrote: > >-- > >-- TOC entry 537 (OID 2663955) > >-- Name: ~=; Type: OPERATOR; Schema: public; Owner: strk > >-- Data Pos: 0 > >-- > > > >CREATE OPERATOR ~= ( > >PROCEDURE = geometry_same, > >LEFTARG = geometry, > >RIGHTARG = geometry, > >COMMUTATOR = 2663954, > >RESTRICT = eqsel, > >JOIN = eqjoinsel > >); > > How about: > > \x > SELECT oid, * FROM pg_operator WHERE oprname='~=' OR oid=2663954; Unfortunately I'm not on the machine from which the dump was issued, so I can't check. I'll inspect when possible, so I'll also tell you which pg_dump version was it. --strk; > > Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] operator commutator
On Tue, Aug 03, 2004 at 04:49:48PM +0800, Christopher Kings-Lynne wrote: > >Operator commutator is itself, and when reading the ascii version > >of the dump (produced with -Fc) I see that this has been changed > >with what was probably it's oid instead. > > Can you paste that ascii from the dump? -- -- TOC entry 537 (OID 2663955) -- Name: ~=; Type: OPERATOR; Schema: public; Owner: strk -- Data Pos: 0 -- CREATE OPERATOR ~= ( PROCEDURE = geometry_same, LEFTARG = geometry, RIGHTARG = geometry, COMMUTATOR = 2663954, RESTRICT = eqsel, JOIN = eqjoinsel ); > > >Is this a bug in pg_dump ? > >How do I tell which pg_dump version produced the dump ? > > I'm not sure it is possible. If it is, it will be at the very top of > the ascii version. No luck. Would be helpful IMO. > > Chris --strk; ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] operator commutator
Hi all, when trying to restore a dump I get the following error: pg_restore: [archiver (db)] could not execute query: ERROR: argument of commutator must be a name Operator commutator is itself, and when reading the ascii version of the dump (produced with -Fc) I see that this has been changed with what was probably it's oid instead. Is this a bug in pg_dump ? How do I tell which pg_dump version produced the dump ? Is self commutation valid ? TIA --strk; ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] index leaks ?
Hello, I'm watching a strange beahviour by postgres, I wonder if it's a memory leak: Creating an index and destroying it makes the postgres process grow in size. This is what I do: create table test (a int); create index test_idx on test (a); drop index test_idx; create index test_idx on test (a); drop index test_idx; create index test_idx on test (a); drop index test_idx; ... The size growth is reported by 'top' in the fields SIZE, RSS and SHARE. The database is a newly created one after first initdb as of CVS 20040311. Can it be a memory leak in postgres code ? TIA --strk; ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] cvs: mislinked plpgsql.so ?
Working with current CVS: $ createlang plpgsql createlang: language installation failed: ERROR: could not load library "/pgroot-cvs/lib/postgresql/plpgsql.so": /pgroot-cvs/lib/postgresql/plpgsql.so: undefined symbol: downcase_truncate_identifier --strk; ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] BYTE_ORDER for contribs
Is there a quick way to use the BYTE_ORDER define as set by pgsql ? I can't find an "entry point" include for it. It's needed for postgis (problems with Solaris BYTE_ORDER). --strk; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PostGIS Integration
tgl wrote: > Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > >> Those two cases are not hard, because in those scenarios the parser > >> knows it is expecting a type specification. The real problem is this > >> syntax for typed literals: > >> typename 'string' > > > Just disallow that particular case for custom types :P > > Well, maybe we could --- comments? Tom Lockhart went to some lengths to > support that, but now that he's gafiated we could perhaps rethink it. > AFAICS the SQL spec only requires this syntax for certain built-in types. > Tom wanted to generalize that to all datatypes that Postgres supports, > and that seems like a reasonable goal ... but if it prevents getting to > other reasonable goals then we ought to think twice. If it's not for SQL conformance I don't think we really need to generalize that. As far as there are other means to gain the same result... 'string'::type(parameter) can be the "general" postgres version. while varchar(2) 'string' can be the standard SQL version (not general). --strk; > > > Will this work: 'string'::typename > > Yes, since the :: cues the parser to expect a typename next. > > regards, tom lane > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] sticky sequence
I cannot get rid of a sequence: gis=# drop sequence geopol.geology_gid_seq; ERROR: cache lookup of relation 8511697 failed Yes, geology_gid_seq have been created as a consequence of a geology.gid attribute of type serial. And.. yes, I've removed the relation with a delete on pg_class. Where is this sequence-to-relation association stored so that i can clean it up ? NOTE: vacuum full does not solve this. TIA -strk; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] ERROR: Index pg_toast_8443892_index is not a btree
I agree about keeping it simple for the users. Anyway if that shows up a bad problems with either the implementation or the operating system of the users it would be nice to know how to inspect it further. In my case this could also help debugging a postgres extension (postgis) which is involved in text->internal conversion and is showing heap corruption problems. The question now is: what does that message mean ? Did a routine try to create an index and left its work before finishing it ? --strk; JanWieck wrote: > Christopher Kings-Lynne wrote: > > >> I couldn't agree more. Look at this very instance. He now found the > >> right reindex command and the corrupted file is gone. We don't have the > >> slightest clue what happened to that file. Was it truncated? Did some > >> other process scribble around in the shared memory? How do you tell now? > > > > The end user just could not care less. They want their machine running > > again as soon as is humanly possible without going through a back and > > forth process of subscribing to some lists they don't care about, etc. > > I know, that's (unfortunately) true. Although it's not very farsighted > because better bug reports usually lead to better software in the next > release. > > > Jan > > -- > #==# > # It's easier to get forgiveness for being wrong than for being right. # > # Let's break this rule - forgive me. # > #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ERROR: Index pg_toast_8443892_index is not a btree
tgl wrote: > strk <[EMAIL PROTECTED]> writes: > > gis=# reindex table pg_toast_8443892; -- this was an assuption I made > > ERROR: Relation "pg_toast_8443892" does not exist > > If it's 7.3 or later you need to say > > reindex table pg_toast.pg_toast_8443892; > > regards, tom lane It worked! Thank you very much. --strk; ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] ERROR: Index pg_toast_8443892_index is not a btree
JanWieck wrote: > strk wrote: > > > I get the following error when vacuuming a db or inserting > > a big value in a column of a toastable datatype (GEOMETRY). > > > > ERROR: Index pg_toast_8443892_index is not a btree > > > > My last action has been killing a psql that was getting > > mad about receiving too much input and beeping as hell > > (readline issue ?). > > You must have killed a lot more than your psql frontend to get that as a > result. really... I hit ^C at the psql terminal and kill -9 The only other reason I can thing about is the data type text input function screwing pg internal pointers... > > > > > After that, I put the insert query I was trying to feed to > > psql in a file and sourced it... Bump! that error appeared. > > > > psql:B:477: ERROR: Index pg_toast_8443892_index is not a btree > > > > Line 477 is EOF... > > > > Vacuum does not solve this (as you can see in the first error message) > > > > Do you have any hint about how to fix this ? > > Try "reindex table ". If you really only lost that btree > index, that should do. gis=# reindex table test; -- this is the table I was trying to insert into WARNING: table "test" wasn't reindexed REINDEX gis=# reindex table pg_toast_8443892; -- this was an assuption I made ERROR: Relation "pg_toast_8443892" does not exist gis=# vacuum; ERROR: Index pg_toast_8443892_index is not a btree gis=# Where could this pg_toast_8443892_index reference be found ? --strk; ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] ERROR: Index pg_toast_8443892_index is not a btree
I get the following error when vacuuming a db or inserting a big value in a column of a toastable datatype (GEOMETRY). ERROR: Index pg_toast_8443892_index is not a btree My last action has been killing a psql that was getting mad about receiving too much input and beeping as hell (readline issue ?). After that, I put the insert query I was trying to feed to psql in a file and sourced it... Bump! that error appeared. psql:B:477: ERROR: Index pg_toast_8443892_index is not a btree Line 477 is EOF... Vacuum does not solve this (as you can see in the first error message) Do you have any hint about how to fix this ? TIA --strk; ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] PG7.4 ordering operator
tgl wrote: > strk <[EMAIL PROTECTED]> writes: > > Testing postgis support in PG7.4 (2003-11-11) > > I've encountered to this problem: > > ERROR: could not identify an ordering operator for type geometry > > Previous PG versions does not show this problem. > > Any hint on what might be missing ? > > A default btree operator class for type geometry. PG 7.4 no longer > uses assumptions about operator names to determine sorting/grouping > behavior. If you have some operators that provide a scalar sort > ordering on your datatype, then make a btree opclass to show that. > See > http://www.postgresql.org/docs/7.4/static/xindex.html#XINDEX-OPCLASS-DEPENDENCIES > > regards, tom lane Thanks for the answer, I've one more question: I've provided a default btree operator class but I'm often going out of memory when using DISTINCT or UNION clauses. How can I reduce memory usage in these cases ? Since passed argument are TOASTED, but I use only a small initial portion if them to make the computation, can I avoid DETOASTING them and still reach that initial part ? The information I need is stored at offset 40 from detoasted data and is 6doubles long. I cannot find TOAST documentation. thanks. --strk; ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] with(isstrict) vs ISSTRICT
Does with(isStrict) still work ? If not when did postgres drop its support ? TIA --strk; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] initdb segfaults - latest cvs
andrew wrote: > Ok, seriously weird. This is apparently from the pg_type relation and > looks just fine on my installation. > > Have you tried "make distclean; cvs update; configure; make check" ? Tried now: 4 of 93 tests failed. .. initdb does not fault though ;) It seems that the build system is missing something (make distclean made it work) Thanks for you support --strk; ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] initdb segfaults - latest cvs
Running initdb: creating template1 database in /pgroot-cvs/data/base/1 ... child process was terminated by signal 11 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] PG7.4 ordering operator
Testing postgis support in PG7.4 (2003-11-11) I've encountered to this problem: ERROR: could not identify an ordering operator for type geometry HINT: Use an explicit ordering operator or modify the query. Whenever I issue one of these commands: gis=# select the_geom from table1 UNION select the_geom from table2; gis=# select DISTINCT the_geom from table1; gis=# select the_geom from table1 ORDER BY the_geom; Operators '<', '>', '=' are available: oprname | leftoperand | rightoperand -+-+-- << | geometry| geometry &< | geometry| geometry && | geometry| geometry &> | geometry| geometry >> | geometry| geometry ~= | geometry| geometry @ | geometry| geometry ~ | geometry| geometry = | geometry| geometry < | geometry| geometry > | geometry| geometry (11 rows) Previous PG versions does not show this problem. Any hint on what might be missing ? --strk; ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] pgsql CVS build failure on Debian GNU/Linux 3.0
I can't build postgresql from CVS. Any help ? This is the message I get: bison -y -d preproc.y preproc.y:6275: fatal error: maximum table size (32767) exceeded Bison version: bison (GNU Bison) 1.35 TIA .strk; -- pallamondo.net <-- take a look ! ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] DETOASTing in custom memory context
tgl wrote: > strk <[EMAIL PROTECTED]> writes: > >> From whitin an aggregate sfunc I did: > > oldcontext = MemoryContextSwitchTo(fcinfo->flinfo->fn_mcxt); > > geom = (GEOMETRY *)PG_DETOAST_DATUM(datum); > > MemoryContextSwitchTo(oldcontext); > > > And later in aggregate's finalfunc: > > pfree(geom); > > > Result: > > segfault! > > > What's wrong with it ? > > Perhaps you wanted PG_DETOAST_DATUM_COPY(). Or possibly use > PG_FREE_IF_COPY() rather than an unconditional pfree, though > that would depend on just what your usage pattern is. Sure, how did I miss that ! PG_FREE_IF_COPY is unapplicable here since pfree() call is in a different function that the one DETOASTING it (finalfunc and sfunc respectively of an aggregate), but PG_DETOAST_DATUM_COPY() did at least force a copy and thus the context-switch... thanks, --strk; ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] DETOASTing in custom memory context
>From whitin an aggregate sfunc I did: oldcontext = MemoryContextSwitchTo(fcinfo->flinfo->fn_mcxt); geom = (GEOMETRY *)PG_DETOAST_DATUM(datum); MemoryContextSwitchTo(oldcontext); And later in aggregate's finalfunc: pfree(geom); Result: segfault! What's wrong with it ? NOTE that if I MemoryContextAllocate in fcinfo->flinfo->fn_mcxt and memcopy DETOASTED geom, everything works (ar at least it seems to) --strk; strk wrote: > Tom, thanks again for the quick answer and > sorry for the lame question about memor allocation. > > I hope this is acceptable: > Is there a way to make PG_DETOAST_DATUM and friends allocate > memory in a custom memory context ? > > Right now I'm DETOASTing, memcopying in a custom context > and pfreeing the DETOASTed datum, I'd like to avoid one > copy. > > TIA. > --strk; > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] DETOASTing in custom memory context
Tom, thanks again for the quick answer and sorry for the lame question about memor allocation. I hope this is acceptable: Is there a way to make PG_DETOAST_DATUM and friends allocate memory in a custom memory context ? Right now I'm DETOASTing, memcopying in a custom context and pfreeing the DETOASTed datum, I'd like to avoid one copy. TIA. --strk; ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Aggregate detoasted arguments lifetime
Dear pg-hackers, Making an aggregate I want to stuff all input values (detoasted) in an array and process them all togheter with finalfunc. This is because in order to process them a conversion is involved and I'm trying to reduce the number of conversions to the lowest possible. My question is: if I write in the "state" array pointers to DETOASTED input args, will I find them intact at finalfunc time ? TIA --strk; ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] finding memory leaks in extensions
Hello, I'm trying to detect memory leaks in an external postgres extensions. Since SIZE of postgres process keeps growing between function calls, does this mean there are memory leaks in the extenal packages or it might be the postgres itself keeping allocated memory as a buffer for later use ? To note also that the same identical calls keeps making SIZE grow ... PS: I'm working with postgres-7.3.2 TIA --strk; ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] SPI portals and memory usage
Do I save memory using a cursor from a SPI-connected procedure ? I'm working on a table containing geometric objects and I'd like to keep a geometry in memory just for the time it is needed, and one at time, otherwise my machine is in permanent swapping. --strk; ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
