[BUGS] BUG #3881: lo_open leaks memory

[Michael Akinde]

The following bug has been logged online:

Bug reference:  3881
Logged by:  Michael Akinde
Email address:  [EMAIL PROTECTED]
PostgreSQL version: 8.2.5
Operating system:   Linux Debian Etch
Description:lo_open leaks memory
Details: 

We are using large objects to store gridded data, and wish to provide
function to permit the extraction of a single point from the data grids. One
issue we have run into, unfortunately, is that lo_open seems to leak memory
somewhat terribly.

Simplified test case:

create or replace function f() RETURNS setof bytea as
$body$
declare
   r oid;
   fd int;
   ret bytea;
begin
   for r in select gridoid FROM gridvalue LIMIT 15
   LOOP
   fd := lo_open( r, 262144 ); -- 262144 = "INV_READ"
   --PERFORM lo_lseek( fd, 120, 0 ); -- 0 = "SEEK_SET"
   --ret := loread( fd, 4 );
   PERFORM lo_close( fd );
   --RETURN NEXT ret;
   END LOOP;
end;
$body$
language plpgsql;

SELECT * FROM f();

(Note that several lines are commented out - behavior is essentially the
same with or without).

We find that the above function (on a 8.2.5 setup) will rapidly max out 1GB
of shared memory (it seems to goblle up 10-20kb for each lo_open), and
performs very poorly. Obviously, this only gets worse with increasing
queries (many of our queries will be retrieving points from over 10 million
grids, so simply increasing memory is unfortunately not a viable solution).

We are aware that this is a "known" bug (inasmuch as the comments on the
backend source seems to explicitly state that the lo_* functions are known
to leak memory).

Questions:
- Any likelihood that this may be fixed in the near future (or ever)? 

- Might there be a simple workaround for this problem?

We have looked at the backend code ourselves, but I suspect that it would
probably be easier for us to use toasted binary objects (esentially
developing our own specialized lo_* system of functions) than to try and
patch this on our own. Or might that run into similar (or different)
problems?

Regards,

Michael Akinde
Database Architect, met.no

---(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

[BUGS] Bug in all version with encryption

[Suresh Gupta VG]

Dear Team,

I had recently upgraded my pgsql 7.4.2 to pgsql 8.2.5 on Solaris 9 Sparc
machine. All the installation went normally as per install documents of
Postgresql. In between the installation, I got the following error. But
at the end I could create database and can use the DB as normal.
---
ERROR: incompatible library "/usr/lib/pgsql_clcrypt.so": missing magic
block
HINT: Extension libraries are required to use the PG_MODULE_MAGIC macro.
STATEMENT: CREATE FUNCTION blowfish_encrypt(text, text) RETURNS text
AS '/usr/lib/pgsql_clcrypt.so', 'blowfish_encrypt'
LANGUAGE c;
psql:pgsql7.4_dump_10012008:877: ERROR: incompatible library
"/usr/lib/pgsql_clcrypt.so": missing magic block
HINT: Extension libraries are required to use the PG_MODULE_MAGIC macro.
ERROR: incompatible library "/usr/lib/pgsql_clcrypt.so": missing magic
block
HINT: Extension libraries are required to use the PG_MODULE_MAGIC macro.
STATEMENT: CREATE FUNCTION blowfish_decrypt(text, text) RETURNS text
AS '/usr/lib/pgsql_clcrypt.so', 'blowfish_decrypt'
LANGUAGE c;
psql:pgsql7.4_dump_10012008:887: ERROR: incompatible library
"/usr/lib/pgsql_clcrypt.so": missing magic block
HINT: Extension libraries are required to use the PG_MODULE_MAGIC macro.
---

This problem is coming with all the versions of PGSQL. We had a good
experience with similar error when we were upgraded with 7.4.2 long
back. Can you pls advice what to do to rectify this error in Postgresql
8.2.5.

--Suresh Gupta

 

 

Regards,

G. V. Suresh Gupta


-

Innovative Technology Solutions(ITS), Zensar Technologies 

Zensar Knowledge Park, Plot#5, MIDC IT Tower, 

Kharadi, Off Nagar Road, Pune - 411014

Landline :  +91-20-66453471   | +91-9890898688 

Email :   [EMAIL PROTECTED]| website:  www.zensar.com
 

 

 

 

 

 

 



DISCLAIMER:
This email may contain confidential or privileged information for the intended 
recipient(s) and the views expressed in the same are not necessarily the views 
of Zensar Technologies Ltd. If you are not the intended recipient or have 
received this e-mail by error, its use is strictly prohibited, please delete 
the e-mail and notify the sender. Zensar Technologies Ltd. does not accept any 
liability for virus infected mails. 
<>

Re: [BUGS] Bug in all version with encryption

[John R Pierce]

Suresh Gupta VG wrote:


I had recently upgraded my pgsql 7.4.2 to pgsql 8.2.5 on Solaris 9 
Sparc machine. All the installation went normally as per install 
documents of Postgresql. In between the installation, I got the 
following error. But at the end I could create database and can use 
the DB as normal.

---
ERROR: incompatible library "/usr/lib/pgsql_clcrypt.so": missing magic 
block

HINT: Extension libraries are required to use the PG_MODULE_MAGIC macro.
STATEMENT: CREATE FUNCTION blowfish_encrypt(text, text) RETURNS text
AS '/usr/lib/pgsql_clcrypt.so', 'blowfish_encrypt'
LANGUAGE c;
...
---

This problem is coming with all the versions of PGSQL. We had a good 
experience with similar error when we were upgraded with 7.4.2 long 
back. Can you pls advice what to do to rectify this error in 
Postgresql 8.2.5.




this does not look like a postgres bug to me.


Offhand, it looks like your database loads custom functions from the 
shared object library /usr/lib/pgsql_clcrypt.so which was probably built 
for your older 7.4...  you'd either need to rebuild that library to be 
8.2 compatible, or remove the CREATE FUNCTION statements that reference 
it (and modify any SQL code you have which calls these 
blowfish_encrypt() etc functions...)




---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [BUGS] postgresql in FreeBSD jails: proposal

[Marc G. Fournier]

[EMAIL PROTECTED] (Mischa Sandberg) writes:

>Unfortunately, with multiple jails running PG servers and (due to app
>limitations) all servers having same PGPORT, you get the situation that
>when jail#2 (,jail#3,...) server comes up, it:
>- detects that there is a shm seg with ipc key 5432001
>- checks whether the associated postmaster process exists (with kill -0)
>- overwrites the segment created and being used by jail #1

Easiest fix: change the UID of the user running the postmaster (ie. pgsql) so 
that each runs as a distinct UID (instead of distinct PGPORT) ... been doing 
this since moving to FreeBSD 6.x ... no patches required ...
-- 

Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email . [EMAIL PROTECTED]  MSN . [EMAIL PROTECTED]
Yahoo . yscrappy   Skype: hub.orgICQ . 7615664

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [BUGS] Bug in all version with encryption

[Suresh Gupta VG]

Thanks for your reply.

 

As per your suggestion, if we remove "CREATE FUNCTION" statement, we
cannot use that function.

Actually, we need this feature.

 

Can you pls suggest some url or docs to help us in developing the
c-language code to rectify this as we had done earlier for pgsql7.4.2.

 

And pls provide us with new macros introduced in 8.2.5 later to 7.4.2,
so that it can help us in our coding very mush

Thanks in advice.

 

 

Regards,

G. V. Suresh Gupta


-

Innovative Technology Solutions(ITS), Zensar Technologies 

Zensar Knowledge Park, Plot#5, MIDC IT Tower, 

Kharadi, Off Nagar Road, Pune - 411014

Landline :  +91-20-66453471   | +91-9890898688 

Email :   [EMAIL PROTECTED]| website:  www.zensar.com

 

 

 

 

 

 

-Original Message-
From: John R Pierce [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 17, 2008 2:31 PM
To: Suresh Gupta VG
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] Bug in all version with encryption

 

Suresh Gupta VG wrote:

> 

> I had recently upgraded my pgsql 7.4.2 to pgsql 8.2.5 on Solaris 9 

> Sparc machine. All the installation went normally as per install 

> documents of Postgresql. In between the installation, I got the 

> following error. But at the end I could create database and can use 

> the DB as normal.

> ---

> ERROR: incompatible library "/usr/lib/pgsql_clcrypt.so": missing magic


> block

> HINT: Extension libraries are required to use the PG_MODULE_MAGIC
macro.

> STATEMENT: CREATE FUNCTION blowfish_encrypt(text, text) RETURNS text

> AS '/usr/lib/pgsql_clcrypt.so', 'blowfish_encrypt'

> LANGUAGE c;

> ...

> ---

> 

> This problem is coming with all the versions of PGSQL. We had a good 

> experience with similar error when we were upgraded with 7.4.2 long 

> back. Can you pls advice what to do to rectify this error in 

> Postgresql 8.2.5.

> 

 

this does not look like a postgres bug to me.

 

 

Offhand, it looks like your database loads custom functions from the 

shared object library /usr/lib/pgsql_clcrypt.so which was probably built


for your older 7.4...  you'd either need to rebuild that library to be 

8.2 compatible, or remove the CREATE FUNCTION statements that reference 

it (and modify any SQL code you have which calls these 

blowfish_encrypt() etc functions...)

 

 



DISCLAIMER:
This email may contain confidential or privileged information for the intended 
recipient(s) and the views expressed in the same are not necessarily the views 
of Zensar Technologies Ltd. If you are not the intended recipient or have 
received this e-mail by error, its use is strictly prohibited, please delete 
the e-mail and notify the sender. Zensar Technologies Ltd. does not accept any 
liability for virus infected mails. 

Re: [BUGS] Bug in all version with encryption

[Alvaro Herrera]

Suresh Gupta VG wrote:

> As per your suggestion, if we remove "CREATE FUNCTION" statement, we
> cannot use that function.

Add the PG_MODULE_MAGIC stuff to your code and recompile it.  This is
mentioned in the documentation.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---(end of broadcast)---
TIP 6: explain analyze is your friend

[BUGS] BUG #3882: unexpected PARAM_SUBLINK ID

[Jan Mate]

The following bug has been logged online:

Bug reference:  3882
Logged by:  Jan Mate
Email address:  [EMAIL PROTECTED]
PostgreSQL version: 8.2.6
Operating system:   Linux and Mac OS X
Description:unexpected PARAM_SUBLINK ID
Details: 

I am trying to create a row versioning table using view and rules.

The problem occur when I try to insert a new row to view using:

INSERT INTO "table" (number, level) VALUES(1, 1);

I get the following error:
ERROR:  unexpected PARAM_SUBLINK ID: 3

BUT, the same INSERT RULE (see the dump below) works fine when I delete the
"limited" column from the table (and view) and modify the rules on the
view:

Dump of my DB is:

--
-- PostgreSQL database dump
--

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;

DROP RULE "_INSERT" ON public."table";
DROP INDEX public."r_table#_id#key";
ALTER TABLE ONLY public."r_table" DROP CONSTRAINT "r_table#pkey";
DROP SEQUENCE public."r_table#__id_entry#seq";
DROP VIEW public."table";
DROP TABLE public."r_table";
DROP SEQUENCE public."r_table#_id#seq";

--
-- Name: r_table#_id#seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE "r_table#_id#seq"
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1
CYCLE;


SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: r_table; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE "r_table" (
_id bigint DEFAULT nextval('"r_table#_id#seq"'::regclass) NOT NULL,
__version bigint NOT NULL,
__latest boolean DEFAULT false NOT NULL,
__op_type text NOT NULL,
__timestamp timestamp without time zone NOT NULL,
__id_entry bigint NOT NULL,
number bigint NOT NULL,
limited timestamp without time zone,
"level" bigint DEFAULT 1 NOT NULL
);


--
-- Name: table; Type: VIEW; Schema: public; Owner: -
--
-- Select only the latest version of rows if they are not DELETED

CREATE VIEW "table" AS
SELECT "r_table"._id, "r_table".number, "r_table".limited,
"r_table"."level" FROM "r_table" WHERE "r_table".__id_entry,
"r_table".__version) IN (SELECT "r_table".__id_entry,
max("r_table".__version) AS __version FROM "r_table" GROUP BY
"r_table".__id_entry)) AND ("r_table".__op_type <> 'DELETE'::text)) AND
("r_table".__latest = true));


--
-- Name: r_table#__id_entry#seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE "r_table#__id_entry#seq"
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;


--
-- Name: r_table#pkey; Type: CONSTRAINT; Schema: public; Owner: -;
Tablespace: 
--

ALTER TABLE ONLY "r_table"
ADD CONSTRAINT "r_table#pkey" PRIMARY KEY (_id);


--
-- Name: r_table#_id#key; Type: INDEX; Schema: public; Owner: -; Tablespace:

--

CREATE UNIQUE INDEX "r_table#_id#key" ON "r_table" USING btree (_id);


--
-- Name: _INSERT; Type: RULE; Schema: public; Owner: -
--
-- Insert the new row only if there is not duplicate row in view

CREATE RULE "_INSERT" AS ON INSERT TO "table" DO INSTEAD INSERT INTO
"r_table" (__version, __latest, __op_type, __timestamp, __id_entry, number,
limited, "level") VALUES (0, true, 'INSERT'::text, now(),
nextval('"r_table#__id_entry#seq"'::regclass), (SELECT new.number WHERE (NOT
((new.number, new.limited, new."level") IN (SELECT "table".number,
"table".limited, "table"."level" FROM "table", new.limited,
new."level");

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[BUGS] BUG #3883: Autovacuum deadlock with truncate?

[Steven Flatt]

The following bug has been logged online:

Bug reference:  3883
Logged by:  Steven Flatt
Email address:  [EMAIL PROTECTED]
PostgreSQL version: 8.2.4
Operating system:   FreeBSD 6.1
Description:Autovacuum deadlock with truncate?
Details: 

This isn't a postgres deadlock per se, but the end result is that two
postgres backends are stuck, each waiting on a PGSemaphoreLock that the
other presumably has.  The processes have been stuck for hours.

First process is the postgres autovacuum.
Second process is a PLpgSQL function which is intended to "clean tables". 
It goes through a list of tables and truncates them if they have any data. 
I realize that it generally doesn't make a great deal of sense to truncate
data so soon after being modified that the data hasn't even been
vacuumed/analyzed, but this happened in a test environment and we'd like to
understand the root cause.

pg_locks info and gdb backtraces follow:

---

pid 35775 (autovacuum)

=# select pid,relation,mode,granted from pg_locks where pid = 35775;
  pid  | relation |   mode   | granted
---+--+--+-
 35775 |16783 | ShareUpdateExclusiveLock | t
 35775 |16788 | RowExclusiveLock | t
 35775 |16790 | RowExclusiveLock | t
 35775 |16791 | RowExclusiveLock | t
 35775 |  | ExclusiveLock| t
(5 rows)

(gdb) bt
#0  0x6854a5b7 in semop () from /lib/libc.so.6
#1  0x081c7b4f in PGSemaphoreLock (sema=0x86adf888, interruptOK=1 '\001')
at pg_sema.c:411
#2  0x081f50f1 in ProcWaitForSignal () at proc.c:1075
#3  0x081e78e3 in LockBufferForCleanup (buffer=14408) at bufmgr.c:1926
#4  0x081541c2 in lazy_vacuum_heap (onerel=0x86a8c08,
vacrelstats=0x8668170)
at vacuumlazy.c:552
#5  0x08153fa0 in lazy_scan_heap (onerel=0x86a8c08, vacrelstats=0x8668170,
Irel=0x8668158, nindexes=3) at vacuumlazy.c:482
#6  0x08153722 in lazy_vacuum_rel (onerel=0x86a8c08, vacstmt=0x86c6be0)
at vacuumlazy.c:164
#7  0x0814f623 in vacuum_rel (relid=16783, vacstmt=0x86c6be0,
expected_relkind=114 'r') at vacuum.c:1098
#8  0x0814eb10 in vacuum (vacstmt=0x86c6be0, relids=0x86c6d18) at
vacuum.c:397
#9  0x081c9d57 in autovacuum_do_vac_analyze (relid=16783, dovacuum=1
'\001',
doanalyze=1 '\001', freeze_min_age=1) at autovacuum.c:912
#10 0x081c97e4 in do_autovacuum (dbentry=0x840dc08) at autovacuum.c:659
#11 0x081c92c1 in AutoVacMain (argc=0, argv=0x0) at autovacuum.c:433
#12 0x081c8f3a in autovac_start () at autovacuum.c:178
#13 0x081cf01a in ServerLoop () at postmaster.c:1249
#14 0x081ce916 in PostmasterMain (argc=3, argv=0x9fbfed40) at
postmaster.c:963
#15 0x0817fef0 in main (argc=3, argv=0x9fbfed40) at main.c:188

---

pid 6869 (function to clean tables)

=# select pid,relation,mode,granted from pg_locks where pid = 6869 and not
granted;
 pid  | relation |mode | granted
--+--+-+-
 6869 |16783 | AccessExclusiveLock | f

(gdb) bt
#0  0x6854a5b7 in semop () from /lib/libc.so.6
#1  0x081c7b4f in PGSemaphoreLock (sema=0x86ae0890, interruptOK=1 '\001')
at pg_sema.c:411
#2  0x081f4e29 in ProcSleep (locallock=0x8f5e160,
lockMethodTable=0x8332324)
at proc.c:829
#3  0x081f2660 in WaitOnLock (locallock=0x8f5e160, owner=0x9965db8)
at lock.c:1140
#4  0x081f2120 in LockAcquire (locktag=0x9fbfdc70, lockmode=8,
sessionLock=0 '\0', dontWait=0 '\0') at lock.c:792
#5  0x081f0eb4 in LockRelationOid (relid=16783, lockmode=8) at lmgr.c:81
#6  0x08091b6e in relation_open (relationId=16783, lockmode=8) at
heapam.c:694
#7  0x08091db2 in relation_openrv (relation=0xbdf9110, lockmode=8)
at heapam.c:821
#8  0x08091ef4 in heap_openrv (relation=0xbdf9110, lockmode=8) at
heapam.c:891
#9  0x08135610 in ExecuteTruncate (stmt=0xbdf9160) at tablecmds.c:549
#10 0x08202da1 in ProcessUtility (parsetree=0xbdf9160, params=0x0,
dest=0x835b4c0, completionTag=0x0) at utility.c:626
#11 0x08175868 in _SPI_execute_plan (plan=0x9fbfde80, Values=0x0,
Nulls=0x0,
snapshot=0x0, crosscheck_snapshot=0x0, read_only=0 '\0', tcount=0)
at spi.c:1496
#12 0x0817374d in SPI_execute (
src=0x8a1db18 "TRUNCATE foo", read_only=0 '\0',
tcount=0) at spi.c:316
#13 0x87d23df1 in exec_stmt_dynexecute (estate=0x9fbfe270, stmt=0x84e01b0)
at pl_exec.c:2561
#14 0x87d21ba2 in exec_stmt (estate=0x9fbfe270, stmt=0x84e01b0)
at pl_exec.c:1212
#15 0x87d2196d in exec_stmts (estate=0x9fbfe270, stmts=0x84e0038)
at pl_exec.c:1123
#16 0x87d2450d in exec_stmt_dynfors (estate=0x9fbfe270, stmt=0x84dfde0)
at pl_exec.c:2793
#17 0x87d21bbb in exec_stmt (estate=0x9fbfe270, stmt=0x84dfde0)
at pl_exec.c:1216
#18 0x87d2196d in exec_stmts (estate=0x9fbfe270, stmts=0x84dffb0)
at pl_exec.c:1123
#19 0x87d226e6 in exec_stmt_fors (estate=0x9fbfe270, stmt=0x84df9e0)
at pl_exec.c:1694
#20 0x87d21b0c i

Re: [BUGS] BUG #3883: Autovacuum deadlock with truncate?

[Alvaro Herrera]

Steven Flatt escribió:

> This isn't a postgres deadlock per se, but the end result is that two
> postgres backends are stuck, each waiting on a PGSemaphoreLock that the
> other presumably has.  The processes have been stuck for hours.

Can you reproduce this problem at will?

Are the processes still running (sleeping, rather) so that we can try to
examine them more deeply?

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [BUGS] BUG #3881: lo_open leaks memory

[Tom Lane]

"Michael Akinde" <[EMAIL PROTECTED]> writes:
> Description:lo_open leaks memory

Hmm, I cannot replicate any memory leak with your example.
I'm testing 8.2.6 (well, really 8.2 branch tip) not 8.2.5, but I
don't recall that there were any recent fixes in this area.
Perhaps there is some contributing factor you didn't mention?

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [BUGS] Bug in all version with encryption

[John R Pierce]

Suresh Gupta VG wrote:


Thanks for your reply.

As per your suggestion, if we remove “CREATE FUNCTION” statement, we 
cannot use that function.


Actually, we need this feature.

Can you pls suggest some url or docs to help us in developing the 
c-language code to rectify this as we had done earlier for pgsql7.4.2.




http://www.postgresql.org/docs/current/static/xfunc-c.html

again, this isn't a postgres bug. you're posting this on the wrong list.


---(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: [BUGS] BUG #3883: Autovacuum deadlock with truncate?

[Steven Flatt]

On 1/17/08, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> > This isn't a postgres deadlock per se, but the end result is that two
> > postgres backends are stuck, each waiting on a PGSemaphoreLock that the
> > other presumably has.  The processes have been stuck for hours.
>
> Can you reproduce this problem at will?

Well we have a bunch of test runs (at least a few dozen) that do a
similar thing, but only this one seems to be stuck.  Still, I imagine
I can reproduce by doing enough updates/deletes to interest the
autovacuumer immediately followed by executing the fn_clean_tables()
function -- however not sure of the precise timing to get into this
state.

> Are the processes still running (sleeping, rather) so that we can try to
> examine them more deeply?

Yes, the processes are still "sleeping" in the same state.  Just did
another bt a few minutes ago and they're the exact same.  Don't
imagine the processes are going to be moving much...

Steve

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [BUGS] BUG #3882: unexpected PARAM_SUBLINK ID

[Tom Lane]

"Jan Mate" <[EMAIL PROTECTED]> writes:
> I get the following error:
> ERROR:  unexpected PARAM_SUBLINK ID: 3

This is an interesting test case.  The problem is coming from the part
of the rule that has

(new.number, new.limited, new."level") IN (SELECT ...)

The parser transforms this to a SubLink with a "testexpr" that
looks like

V1 = P1 AND V2 = P2 AND V3 = P3

where the V's are Vars representing the new.* fields and the P's
are PARAM_SUBLINK Params representing the output columns of the
sub-SELECT.

The planner's subselect.c assumes that the testexpr will still
look like that, at least to the extent of referencing the same
Params in the same order, by the time it sees the SubLink.
This assumption was always a bit shaky, as noted in the code,
but I had not seen a case that breaks it.  What happens is that
for an ON INSERT rule with INSERT / VALUES, the rewriter substitutes
the VALUES-list items for the new.* Vars, which in this case
yields

1 = P1 AND null = P2 AND 1 = P3

and then const-simplification reasons that timestamp equality is a
strict operator and therefore cannot succeed on constant-null input,
so the result of eval_const_expressions is just

1 = P1 AND 1 = P3

causing subselect.c to choke because there's no Param corresponding
to the second subselect output column.

I thought for a bit about a band-aid fix involving doing sublink
expansion before const-simplification, but really the right answer
is to get rid of the shaky assumption.  Instead of relying on
one-for-one matching of Param uses, subselect.c should scan the
subselect's output targetlist for itself to determine the number
and types of the output columns.  This involves duplicating some
code from the parser's transformSubLink, but only about a dozen
lines worth.  (It was trying to avoid duplicating that logic that
led me down the garden path to this error :-()  The substitution
for the PARAM_SUBLINK Params still needs to happen, but that
should be driven off list_nth() selection from the result of this
scan, instead of assuming that the Params appear in any particular
ordering.

Will fix.

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

[BUGS] Minor mathematical error in documentation

[Russell Smith]

Hi,

I've had this minor bugbear with this part of the docs for a while and 
am finally reporting it.


http://www.postgresql.org/docs/8.3/static/sql-expressions.html (And all 
back branch documentation)


SELECT ... WHERE x <> 0 AND y/x > 1.5;

But this is safe:

SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;

A CASE construct used in this fashion will defeat optimization attempts, 
so it should only be done when necessary. (In this particular example, 
it would be best to sidestep the problem by writing y > 1.5*x instead.)



In-equality transformations do not guarantee that y > 1.5x == y/x > 
1.5.  This is only true for x>0, y < 1.5*x for x<0.  I have not posted a 
patch as I'm not sure what is the best way to change the example.


Regards

Russell Smith



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [BUGS] BUG #3883: Autovacuum deadlock with truncate?

[Steven Flatt]

On 1/17/08, Tom Lane <[EMAIL PROTECTED]> wrote:
> No, that's not what the backtraces say.  The autovac process is trying
> to get super-exclusive lock on a buffer (apparently in relation 16783
> --- what is that?).  There's no evidence in the stack trace that the
> TRUNCATE process has any conflicting buffer lock.

Relation 16783 is a regular table, nothing special about it, except
perhaps that it's inherited?  (It's a partition.)  It's got an integer
primary key column whose default value is the nextval of a sequence,
another integer column, two varchar columns, and five timestamptz
columns.  It's got three indexes and a check constraint.

Data would have been inserted into this table, then many rows updated
as part of a test.  Immediately following the test, the data would
have been truncated by the fn_clean_tables() function.  Apparently the
autovacuumer wanted to work on the table at the same time.

> What I think might be happening is a three-way deadlock involving these
> two and a third process that has the desired buffer lock.  Have you got
> anything else that seems to be stuck?

Don't see a third process that is stuck...

=# select * from pg_stat_activity where current_query not like '%IDLE%';
 datid | datname | procpid | usesysid | usename |  current_query
  | waiting | query_start  | backend_sta
rt | client_addr | client_port
---+-+-+--+-+---
--+-+--+
---+-+-
 16384 | datname |   35775 |   10 | pgsql   | VACUUM ANALYZE
public.foo | f   |  | 2008-01-17
01:31:54.932049-05 | |
 16384 | datname |6869 |   10 | pgsql   | SELECT
fn_clean_tables()| t   | 2008-01-17 01:31:51.68996-05
| 2008-01-16 22:34:40.914391-05 | 0.0.0.0 |   51451
(2 rows)

(I've obfuscated some of the names.)

Steve

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [BUGS] BUG #3883: Autovacuum deadlock with truncate?

[Tom Lane]

"Steven Flatt" <[EMAIL PROTECTED]> writes:
> This isn't a postgres deadlock per se, but the end result is that two
> postgres backends are stuck, each waiting on a PGSemaphoreLock that the
> other presumably has.  The processes have been stuck for hours.

No, that's not what the backtraces say.  The autovac process is trying
to get super-exclusive lock on a buffer (apparently in relation 16783
--- what is that?).  There's no evidence in the stack trace that the
TRUNCATE process has any conflicting buffer lock.

What I think might be happening is a three-way deadlock involving these
two and a third process that has the desired buffer lock.  Have you got
anything else that seems to be stuck?

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [BUGS] Minor mathematical error in documentation

[Peter Eisentraut]

Russell Smith wrote:
> SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;
>
> A CASE construct used in this fashion will defeat optimization attempts,
> so it should only be done when necessary. (In this particular example,
> it would be best to sidestep the problem by writing y > 1.5*x instead.)
>
>
> In-equality transformations do not guarantee that y > 1.5x == y/x >
> 1.5.  This is only true for x>0

So the proper expression would be

SELECT ... WHERE CASE WHEN x >= 0 THEN y > 1.5*x ELSE y < 1.5*x END;

or

SELECT ... WHERE (x >= 0 AND y > 1.5*x) OR y < 1.5*x;

which obviously isn't simpler.  So I suggest that we just delete the 
parenthetical note.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [BUGS] BUG #3882: unexpected PARAM_SUBLINK ID

[Tom Lane]

"Jan Mate" <[EMAIL PROTECTED]> writes:
> Description:unexpected PARAM_SUBLINK ID

If convenient, please try the 8.2 patch seen here:
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/plan/subselect.c

regards, tom lane

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [BUGS] Minor mathematical error in documentation

[Tom Lane]

Russell Smith <[EMAIL PROTECTED]> writes:
> In-equality transformations do not guarantee that y > 1.5x == y/x > 
> 1.5.  This is only true for x>0, y < 1.5*x for x<0.  I have not posted a 
> patch as I'm not sure what is the best way to change the example.

Seems a bit nit-picky, but we could change the example to

SELECT ... WHERE x > 0 AND y/x > 1.5;
becomes
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;

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 #3883: Autovacuum deadlock with truncate?

[Tom Lane]

"Steven Flatt" <[EMAIL PROTECTED]> writes:
> On 1/17/08, Tom Lane <[EMAIL PROTECTED]> wrote:
>> What I think might be happening is a three-way deadlock involving these
>> two and a third process that has the desired buffer lock.  Have you got
>> anything else that seems to be stuck?

> Don't see a third process that is stuck...

Hmm.  The only other theory I can think of is that some process forgot
about a buffer pin it was holding, or the wakeup signal to release the
autovac process somehow got lost; either of which would be significant
bugs.

Do you still have the hung processes available?  It would be really
useful to take a look at the buffer header that the autovac process's
LockBufferForCleanup() is working on.  (In gdb, "f 3" then "p *bufHdr")

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster