[GENERAL] Session variables (how do I pass session data to a function)

[Philip W. Dalrymple]

I have a question on how to do something with Postgres that I think
of by the name of "Session Variables".

What I want to do is build a table that will be filled in by a trigger, 
it will contains three fields in addition to the fields in the table
that causes the trigger that will contain the time, user and the "proxy
user" for the change (this is an audit table) I have the following example
that handles the first two extra fields correctly:




CREATE TABLE partypersons (
partyid TEXT,
firstname TEXT,
middlename TEXT,
lastname TEXT,
personaltitle TEXT,
suffex TEXT,
employmentstatus TEXT,
residencestatus TEXT,
maritalstatus TEXT,
PRIMARY KEY (partyid)
)
;

CREATE TABLE partypersons_audit (
audit_when TIMESTAMP,
audit_who TEXT,
partyid TEXT,
firstname TEXT,
middlename TEXT,
lastname TEXT,
personaltitle TEXT,
suffex TEXT,
employmentstatus TEXT,
residencestatus TEXT,
maritalstatus TEXT,
PRIMARY KEY (partyid,audit_when)
)
;


ALTER TABLE partypersons ADD
FOREIGN KEY ( partyid ) REFERENCES partys  ( partyid ) 
;

-- AUDIT FOR PARTYPERSON

CREATE OR REPLACE FUNCTION fauditpartypersons() RETURNS TRIGGER AS 
$fauditpartypersons$
DECLARE
BEGIN
IF (TG_OP = 'UPDATE') THEN
IF (OLD.firstname = NEW.firstname) THEN
NEW.firstname := NULL;
END IF; 
IF (OLD.middlename = NEW.middlename) THEN
NEW.middlename := NULL;
END IF; 
IF (OLD.lastname = NEW.lastname) THEN
NEW.lastname := NULL;
END IF; 
IF (OLD.personaltitle = NEW.personaltitle) THEN
NEW.personaltitle := NULL;
END IF; 
IF (OLD.suffex = NEW.suffex) THEN
NEW.suffex := NULL;
END IF; 
IF (OLD.employmentstatus = NEW.employmentstatus) THEN
NEW.employmentstatus := NULL;
END IF; 
IF (OLD.residencestatus = NEW.residencestatus) THEN
NEW.residencestatus := NULL;
END IF; 
IF (OLD.maritalstatus = NEW.maritalstatus) THEN
NEW.maritalstatus := NULL;
END IF; 
END IF;
INSERT INTO partypersons_audit SELECT  now(), user, NEW.*;
RETURN NULL;

END;
$fauditpartypersons$ LANGUAGE plpgsql;


CREATE TRIGGER auditpartyperson AFTER UPDATE OR INSERT
ON partypersons FOR ROW
EXECUTE PROCEDURE fauditpartypersons ()
;




As you can see the primary key and the time and user are always set
while the other fields are NULL unless an update (or insert) changes
them.

What I want to do is to add to this another field that will be NULL
UNLESS the session sets a value to the "user" for that the middle-wear 
system is acting for.

What this will be used for will be a web system that will access the 
DB as the user apache but I want to do two things:

1) log to the audit table all changes even if they are done outside of
the web interface (this is done by the above system)

2) in the same log allow the system (apache) to also say who it is "acting
for"


Thanks.

-- 
This email, and any files transmitted with it, is confidential 
and intended solely for the use of the individual or entity to 
whom they are addressed.  If you have received this email in error, 
please advise [EMAIL PROTECTED] .

New MDT Software Headquarters (As of July 1, 2008):
3480 Preston Ridge Road
Suite 450
Alpharetta, GA 30005


Philip W. Dalrymple III <[EMAIL PROTECTED]>
MDT Software - The Change Management Company
+1 678 297 1001
Fax +1 678 297 1003


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Fwd: Copying Blobs between two tables using Insert stmt

[Csaba Nagy]

On Fri, 2008-10-17 at 13:40 -0700, John Skillings wrote:
> Hello Csaba,
> 
> Back in the month of April, I noticed that you posted a similar
> request on copying blobs between two tables, having separate OID.
> 
> Can you let me know your final solution please.

The final version I'm using is this one:

CREATE OR REPLACE FUNCTION copy_blob(p_blobId oid) 
RETURNS oid 
AS '
DECLARE 
v_NewOID OID;
v_LODesc INTEGER;
BEGIN
SELECT lo_create(0) INTO v_NewOID;

SELECT lo_open(v_NewOID, -1) INTO v_LODesc;
  
PERFORM lowrite(v_LODesc, data) FROM pg_largeobject WHERE loid =
p_blobId ORDER BY pageno;
  
PERFORM lo_close(v_LODesc);

RETURN v_NewOID;
END;
' LANGUAGE 'plpgsql';

The only drawback is that you must enable reading of pg_largeobject for
the application user, as by default only the postgres super user can
access it. You would do that with something like (as postgres super
user):

GRANT SELECT ON pg_largeobject TO my_app_user;

Cheers,
Csaba.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] IS NULL seems to return false, even when parameter is NULL

[Richard Huxton]

Andrus wrote:
>> I have a strange problem with the following condition in a SELECT:
>> AND (("TableData" = "inDate") OR ("inDate" IS NULL))
>>
>> it works perfectly when the input date in the function (inDate) matchs
>> a date in the table, but it does not work when the parameter inDate is
>> NULL.
>> I recall the function with pgadmin writing NULL as paramater.
>> It seems that ("inDate" IS NULL) never returns true, even when the
>> parameter is null...
>> What's wrong?
> 
> Order is wrong, change it to
>> AND (("inDate" IS NULL) OR ("TableData" = "inDate") )
> 
> PostgreSQL OR is not commutative if left operand evaluates to NULL.

Seems to work here:

select (true or null) as a, (null or true) as b, (null or false) as c,
(false or null) as d;
 a | b | c | d
---+---+---+---
 t | t |   |
(1 row)


-- 
  Richard Huxton
  Archonet Ltd

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: re[GENERAL] moving a portion of text

[Sam Mason]

On Mon, Oct 20, 2008 at 04:21:31AM -0700, pere roca wrote:
>   I have a column with full of data like ATB-OO  NCK-TT  how can I
> easily remove the "-" ?

If you just want to remove all the dashes then I'd use a regex;
something like:

  UPDATE table SET data = regexp_replace(data, '-', '', 'g');

This says, modify the table replacing every instance (the 'g' option) of
a dash with an empty string in the "data" column.

> it seems that the "-" is allways the fourth letter.

another option in that case would be to use a couple of calls to
substring() instead of the regex call:

  substring(data FROM 1 FOR 3) || substring(data FROM 4);

The manual page[1] contains more complete descriptions of the functions
that operate on text.  If you haven't come across regexs before then I'd
recommend playing around with them, they're very useful and appear in
lots of places--most text editors will allow you to search and replace
using them, doing much more interesting things that I did above.


  Sam

 [1] http://www.postgresql.org/docs/current/static/functions-string.html

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] IS NULL seems to return false, even when parameter is NULL

[Andrus]

Richard,


Seems to work here:

select (true or null) as a, (null or true) as b, (null or false) as c,
(false or null) as d;
a | b | c | d
---+---+---+---
t | t |   |
(1 row)


I'm sorry for the wrong information.

Andrus.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] IS NULL seems to return false, even when parameter is NULL

[Tom Lane]

"Andrus" <[EMAIL PROTECTED]> writes:
> PostgreSQL OR is not commutative if left operand evaluates to NULL.

This is nonsense.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Slow delete with with cascading foreign keys

[Matthias Karlsson]

Hi,

I have a rather complex set of relations, connected with cascading
foreign keys on delete. I'm experiencing very slow performance when
deleting *the* lead node, which everything eventually depends on. The
number of records ultimately to be deleted aren't that many (perhaps
2000-3000) but there are maybe 30 relations involved. I understand
that this is most likely due to missing indices, but I have been
unable to figure out how to get PostgreSQL to tell me where the
performance bottleneck is.

Say my simple query looks like "delete from foo where id = 1". An
"explain" on this won't yield any information for example.

Is there any way to get PostgreSQL to do an analys of a delete
statement like the way I need?

// Matthias

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

re[GENERAL] moving a portion of text

[pere roca]

  hi,
  I have a column with full of data like ATB-OO  NCK-TT  how can I
easily remove the "-" ? it seems that the "-" is allways the fourth letter.

 thanks,
 Pere
-- 
View this message in context: 
http://www.nabble.com/removing-a-portion-of-text-tp20067248p20067248.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Slow delete with with cascading foreign keys

[Tom Lane]

"Matthias Karlsson" <[EMAIL PROTECTED]> writes:
> I have a rather complex set of relations, connected with cascading
> foreign keys on delete. I'm experiencing very slow performance when
> deleting *the* lead node, which everything eventually depends on. The
> number of records ultimately to be deleted aren't that many (perhaps
> 2000-3000) but there are maybe 30 relations involved. I understand
> that this is most likely due to missing indices, but I have been
> unable to figure out how to get PostgreSQL to tell me where the
> performance bottleneck is.

If it's a reasonably modern PG version, EXPLAIN ANALYZE will break out
the time spent in each on-delete trigger, which should be enough to
answer the question.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] index scan leads to result that is different from sec scan after upgrading to 8.3.4

[Sergey Konoplev]

Hi all,

Well, we have migrated our server from 8.3.3 to 8.3.4. The server is
based on Red Hat and an instans it deals with insalled on RAMFS.

db_online=> select version();
version

PostgreSQL 8.3.4 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC)
4.1.2 20071124 (Red Hat 4.1.2-42)
(1 row)

There is a table:
Table "public.person_online"
Column  |   Type   |
Modifiers
+--+-
 obj_id | bigint   | not null
 obj_status_did | smallint |
...
 po_since   | timestamp with time zone | not null default now()
 po_geo_point   | point| not null
Indexes:
"pk_person_online" PRIMARY KEY, btree (obj_id)
"i_person_online__geo_point" gist (box(po_geo_point,
po_geo_point)) WHERE obj_status_did = 1
"i_person_online__since" btree (po_since)
Triggers:
t_person_online_since_bu BEFORE UPDATE ON person_online FOR EACH
ROW EXECUTE PROCEDURE t_person_online_since()

Pay attention to i_person_online__geo_point index.

After migration we did initdb, installed btree_gist contrib (it was
instaled on 8.3.3 too) and created the table and the index. Later we
noticed strange behaviour of our application and that is what we have
managed to find:

db_online=> select obj_status_did, count(1) from person_online
where obj_status_did = 1 group by obj_status_did;
 obj_status_did | count
+---
  1 |   711
(1 row)

db_online=> select obj_status_did, count(1) from person_online
group by obj_status_did;
 obj_status_did | count
+---
  5 | 2
  1 |  1930
(2 rows)

explain showed that first query do index scan and second one sec scan.


db_online=>


We did reindex i_person_online__geo_point and situation seemed being solved:

db_online=> select obj_status_did, count(1) from person_online
where obj_status_did = 1 group by obj_status_did;
 obj_status_did | count
+---
  1 |  2071
(1 row)
db_online=> select obj_status_did, count(1) from person_online
group by obj_status_did;
 obj_status_did | count
+---
  5 | 2
  1 |  2071
(2 rows)

But after our application had done some inserts/updates/deletes the
queries showed different count of rows with obj_status_did = 1 again.
We tried to drop-create the index but it did not help either.

Then we created a twin-table and a twin-index and did a lot of inserts
and random deletes using different connections. Everything was
alright. We renamed original table to person_online_tmp and the
twin-table to person_online. Few minutes later we saw wrong behaviour
again.

Will somebody explain what has happened and how to solve the problem please?

--
Regards,
Sergey Konoplev
--
PostgreSQL articles in english & russian
http://gray-hemp.blogspot.com/search/label/postgresql/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] index scan leads to result that is different from sec scan after upgrading to 8.3.4

[Tom Lane]

"Sergey Konoplev" <[EMAIL PROTECTED]> writes:
> Will somebody explain what has happened and how to solve the problem please?

Apparently you've found a bug in either btree_gist or the core GIST
code.  Can you put together a self-contained test case?

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] index scan leads to result that is different from sec scan after upgrading to 8.3.4

[Sergey Konoplev]

Sorry, I forget to paste explains

> explain showed that first query do index scan and second one sec scan.
>

db_online=> select obj_status_did, count(1) from person_online
where obj_status_did = 1 group by obj_status_did;
QUERY PLAN
-
 GroupAggregate  (cost=0.00..36.37 rows=1 width=2) (actual
time=1.169..1.170 rows=1 loops=1)
   ->  Index Scan using i_person_online_test__geo_point on
person_online_test  (cost=0.00..30.07 rows=1258 width=2) (actual
time=0.016..0.928 rows=258 loops=1)
 Total runtime: 1.268 ms
(3 rows)

db_online=> select obj_status_did, count(1) from person_online
group by obj_status_did;
   QUERY PLAN
-
 HashAggregate  (cost=302.87..302.88 rows=1 width=2) (actual
time=0.668..0.669 rows=1 loops=1)
   ->  Seq Scan on person_online_test  (cost=0.00..296.58 rows=1258
width=2) (actual time=0.009..0.372 rows=258 loops=1)
 Total runtime: 0.754 ms
(3 rows)


-- 
Regards,
Sergey Konoplev
--
PostgreSQL articles in english & russian
http://gray-hemp.blogspot.com/search/label/postgresql/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] index scan leads to result that is different from sec scan after upgrading to 8.3.4

[Tom Lane]

"Sergey Konoplev" <[EMAIL PROTECTED]> writes:
> db_online=> select obj_status_did, count(1) from person_online
> where obj_status_did = 1 group by obj_status_did;
> QUERY PLAN
> -
>  GroupAggregate  (cost=0.00..36.37 rows=1 width=2) (actual
> time=1.169..1.170 rows=1 loops=1)
>->  Index Scan using i_person_online_test__geo_point on
> person_online_test  (cost=0.00..30.07 rows=1258 width=2) (actual
> time=0.016..0.928 rows=258 loops=1)
>  Total runtime: 1.268 ms
> (3 rows)

Hmm.  So the problem seems to be statable as "a full-index scan on a
GIST index might fail to return all the rows, if the index has been
modified since creation".  Teodor, can you think of anything you
changed recently in that area?

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Session variables (how do I pass session data to a function)

[Harald Armin Massa]

Philip,

"session variables" is the perfect name for this kind of use; and
googling it up some times ago lead me to

a) a temp table implementation
b) some shared memory implementation

of these I can present you with a), written to store an integer
user-ID; you can adjust it accordingly:

CREATE OR REPLACE FUNCTION set_user(myid_user integer)
  RETURNS integer AS
$BODY$
BEGIN
perform relname from pg_class
where relname = 'icke_tmp'
  and case when has_schema_privilege(relnamespace, 'USAGE')
then pg_table_is_visible(oid) else false end;
if not found then
create temporary table icke_tmp (
id_user integer
);
else
   delete from icke_tmp;
end if;

insert into icke_tmp values (myid_user);
  RETURN 0;
  END;
 $BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;


CREATE OR REPLACE FUNCTION get_user()
  RETURNS integer AS
$BODY$
declare
ergebnis int4;
BEGIN
perform relname from pg_class
where relname = 'icke_tmp'
  and case when has_schema_privilege(relnamespace, 'USAGE')
then pg_table_is_visible(oid) else false end;
  if not found then
return 0;
  else
select id_user from icke_tmp into ergebnis;
  end if;
  if not found then
ergebnis:=0;
  end if;
  RETURN ergebnis;
  END;
 $BODY$
  LANGUAGE 'plpgsql' STABLE
  COST 100;

You can adjust that "0" to NULL or whatever should be the default for
your application. In mine I default to 0; with 0 being something like
"testuser".

best wishes,

Harald
-- 
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
EuroPython 2009 will take place in Birmingham - Stay tuned!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] index scan leads to result that is different from sec scan after upgrading to 8.3.4

[Sergey Konoplev]

> "Sergey Konoplev" <[EMAIL PROTECTED]> writes:
>> Will somebody explain what has happened and how to solve the problem please?
>
> Apparently you've found a bug in either btree_gist or the core GIST
> code.  Can you put together a self-contained test case?
>

Ok, I will try.

One more thing I want to mention is that we have fixed (temporary) the
problem by recreating the index without "where" clause so it is not
used by the queries now.

-- 
Regards,
Sergey Konoplev
--
PostgreSQL articles in english & russian
http://gray-hemp.blogspot.com/search/label/postgresql/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: re[GENERAL] moving a portion of text

[Lennin Caro]

--- On Mon, 10/20/08, pere roca <[EMAIL PROTECTED]> wrote:

> From: pere roca <[EMAIL PROTECTED]>
> Subject: re[GENERAL] moving a portion of text
> To: pgsql-general@postgresql.org
> Date: Monday, October 20, 2008, 11:21 AM
> hi,
>   I have a column with full of data like ATB-OO  NCK-TT
>  how can I
> easily remove the "-" ? it seems that the
> "-" is allways the fourth letter.
> 
>  thanks,
>  Pere
> -- 
> View this message in context:
> http://www.nabble.com/removing-a-portion-of-text-tp20067248p20067248.html
> Sent from the PostgreSQL - general mailing list archive at
> Nabble.com.
> 
> 
with fixed length

select substring(tex1,1,3) ||  substring(tex1,5) from t1

with variable length

select substring(tex1,1,strpos(tex1,'-'::varchar)-1) ||  
substring(tex1,strpos(tex1,'-'::varchar)+1) from t1

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [ODBC] Error in Adding All Table

[Andrei Kovalevski]

Hello,

What PostgreSQL server and ODBC driver version do you use?

salman Sheikh wrote:


Hi freinds,
i wanted to add my all tables once in MFC application,normally we add it
one by one.
If i add all table by pressing control and click on all table ,i can
add them,but by debugging it shows me always errors.

 
ERROR: column reference "ctid" is ambiguous;

Error while executing the query

i need also suggestion,if it is better to add all table once or one by
one though dialog Box. I am using Visual C++ 2005.

thanks
Sheikh



Pt! Schon vom neuen WEB.DE MultiMessenger geh&oum l;rt?
Der kann`s mit allen: *http://www.produkte.web.de/messenger/?did=3123*  




--
Andrei Kovalevski
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/

[GENERAL] Error in Adding All Table

[salman Sheikh]

Hi freinds, 
i wanted to add my all tables once in MFC application,normally we add it 
one by one. 
If i add all table by pressing control and click on all table ,i can 
add them,but by debugging it shows me always errors.

 
ERROR: column reference "ctid" is ambiguous; 
Error while executing the query 


i need also suggestion,if it is better to add all table once or one by 
one though dialog Box. I am using Visual C++ 2005.


thanks 
Sheikh 



Pt! Schon vom neuen WEB.DE MultiMessenger gehört? 
Der kann`s mit allen: *http://www.produkte.web.de/messenger/?did=3123* 
[http://www.produkte.web.de/messenger/?did=3123]

Re: [GENERAL] IS NULL seems to return false, even when parameter is NULL

[Andrus]

I have a strange problem with the following condition in a SELECT:
AND (("TableData" = "inDate") OR ("inDate" IS NULL))

it works perfectly when the input date in the function (inDate) matchs
a date in the table, but it does not work when the parameter inDate is
NULL.
I recall the function with pgadmin writing NULL as paramater.
It seems that ("inDate" IS NULL) never returns true, even when the
parameter is null...
What's wrong?


Order is wrong, change it to 


AND (("inDate" IS NULL) OR ("TableData" = "inDate") )


PostgreSQL OR is not commutative if left operand evaluates to NULL.

Andrus.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Drupal and PostgreSQL - performance issues?

[Michelle Konzack]

Am 2008-10-16 23:17:35, schrieb Mikkel Høgh:
> P.S.: Why are e-mails from this list not sent with a Reply-To: header  
> of the lists e-mail-address?

Because if I hit  I want to send a private message  and  if  I
hit  it goes to  the  list  andthe  all
people get bulk-mail from me?

If you have problems with  Reply-To:  get  a  REAL  MUA,  which  support


Thanks, Greetings and nice Day/Evening
Michelle Konzack
Systemadministrator
24V Electronic Engineer
Tamay Dogan Network
Debian GNU/Linux Consultant


-- 
Linux-User #280138 with the Linux Counter, http://counter.li.org/
# Debian GNU/Linux Consultant #
Michelle Konzack   Apt. 917  ICQ #328449886
+49/177/935194750, rue de Soultz MSN LinuxMichi
+33/6/61925193 67100 Strasbourg/France   IRC #Debian (irc.icq.com)


signature.pgp
Description: Digital signature

Re: [GENERAL] Annoying Reply-To

[Michelle Konzack]

Hi Martinn, here the great Dictator Michelle!

Am 2008-10-17 10:24:44, schrieb Martin Gainty:
> 
> free unfettered and open discussion without interference from ANY entity is a 
> requirement of a democracy
> the REAL question is ..is this a democracy???

Shut-Up or I will install you Micr0$of SQL Server...  LOL  ;-)

Thanks, Greetings and nice Day/Evening
Michelle Konzack
Systemadministrator
24V Electronic Engineer
Tamay Dogan Network
Debian GNU/Linux Consultant


-- 
Linux-User #280138 with the Linux Counter, http://counter.li.org/
# Debian GNU/Linux Consultant #
Michelle Konzack   Apt. 917  ICQ #328449886
+49/177/935194750, rue de Soultz MSN LinuxMichi
+33/6/61925193 67100 Strasbourg/France   IRC #Debian (irc.icq.com)


signature.pgp
Description: Digital signature

Re: [GENERAL] Annoying Reply-To

[Michelle Konzack]

Am 2008-10-17 08:12:00, schrieb Scott Marlowe:
> I prefer the list the way it is.  And so do a very large, very silent
> majority of users.

 I agree with you.

I am on Mailinglist since I use the Internet (1995) and  there  are  not
very much mailinglists which manipulate the "Reply-To:" Header...

So, I prefer, HOW this list is.

Of course, I reply with  or .

Thanks, Greetings and nice Day/Evening
Michelle Konzack
Systemadministrator
24V Electronic Engineer
Tamay Dogan Network
Debian GNU/Linux Consultant


-- 
Linux-User #280138 with the Linux Counter, http://counter.li.org/
# Debian GNU/Linux Consultant #
Michelle Konzack   Apt. 917  ICQ #328449886
+49/177/935194750, rue de Soultz MSN LinuxMichi
+33/6/61925193 67100 Strasbourg/France   IRC #Debian (irc.icq.com)


signature.pgp
Description: Digital signature

Re: [GENERAL] Drupal and PostgreSQL - performance issues?

[Michelle Konzack]

Am 2008-10-17 12:13:00, schrieb Mikkel Høgh:
> Besides, the if the Reply-To thing is so dangerous, why do most other  
> mailing lists do it?

Curently I am on 117 Mailinglists and ONLY 2 Winsuck lists do this crap.
So, from what are you talking about?

Thanks, Greetings and nice Day/Evening
Michelle Konzack
Systemadministrator
24V Electronic Engineer
Tamay Dogan Network
Debian GNU/Linux Consultant


-- 
Linux-User #280138 with the Linux Counter, http://counter.li.org/
# Debian GNU/Linux Consultant #
Michelle Konzack   Apt. 917  ICQ #328449886
+49/177/935194750, rue de Soultz MSN LinuxMichi
+33/6/61925193 67100 Strasbourg/France   IRC #Debian (irc.icq.com)


signature.pgp
Description: Digital signature

Re: [GENERAL] Annoying Reply-To

[Michelle Konzack]

Am 2008-10-17 08:42:46, schrieb Andrew Sullivan:
> My suggestion would be to use a mail user agent that knows how to read
> the list headers, which were standardized many years ago.  Then you
> "reply to list".  Mutt has done this for at least a few years now.  I
> don't know about other MUAs.

N.C.  ;-)

Thanks, Greetings and nice Day/Evening
Michelle Konzack
Systemadministrator
24V Electronic Engineer
Tamay Dogan Network
Debian GNU/Linux Consultant


-- 
Linux-User #280138 with the Linux Counter, http://counter.li.org/
# Debian GNU/Linux Consultant #
Michelle Konzack   Apt. 917  ICQ #328449886
+49/177/935194750, rue de Soultz MSN LinuxMichi
+33/6/61925193 67100 Strasbourg/France   IRC #Debian (irc.icq.com)


signature.pgp
Description: Digital signature

[GENERAL] Hot Standby utility and administrator functions

[Simon Riggs]

I'm looking to implement the following functions for Hot Standby, to
allow those with administrative tools or management applications to have
more control during recovery. Please let me know if other functions are
required.

What else do we need?

* pg_is_in_recovery()
returns bool (true if in recovery, false if not)

* pg_last_recovered_xact_xid()
Will throw an ERROR if *not* executed in recovery mode.
returns bigint 

* pg_last_completed_xact_xid()
Will throw an ERROR *if* executed in recovery mode.
returns bigint 

(together allows easy arithmetic on xid difference between master and
slave).

* pg_last_recovered_xact_timestamp()
returns timestamp with timezone
(allows easy arithmetic with now() to allow derivation of replication
delay etc)

* pg_freeze_recovery() - freezes recovery after the current record has
been applied. The server is still up and queries can happen, but no WAL
replay will occur. This is a temporary state change and we keep no
record of this, other than making a server log entry. If the server is
shutdown or crashes, it will unfreeze itself automatically. Has no
effect on master.
Will throw an ERROR if not executed in recovery mode.
Superusers only.
returns text (XLogRecPtr of freeze point)

* pg_unfreeze_recovery() - unfreezes recovery. Recovery will begin again
at exactly the point recovery was frozen at.
Will throw an ERROR is not executed in recovery mode.
Superusers only.
returns bool (true if unfroze, false if was not frozen when called)

* pg_end_recovery() - 
Will force recovery to end at current location. Recovery mode cannot be
easily re-entered, so there is no "restart" function.
Will throw an ERROR is not executed in recovery mode.
Superusers only.
returns text (XLogRecPtr of freeze point)

* pg_start_backup()/pg_stop_backup() could work during recovery, but the
backup history file would need to be manually inserted into the archive
once complete. Is that acceptable? (Note that we don't know where the
archive is or how to access that; the information is all in
recovery_command. We cannot assume that archive_command points to same
archive. So making it happen automatically is too much work for this
release, if ever.) If that seems useful, we could do this by avoiding
any operation that changes WAL stream during recovery: no checkpoints,
log switches etc.. 
pg_start_backup() would return XLogRecPtr of last restartpoint.
pg_stop_backup() would return last known xlrec recovered (we won't keep
track of this record by record).

* pg_reload_conf() will not force re-read of recovery.conf since that
may require extra work and doesn't seem that important, if we have the
manual override mentioned above.

All desirable? All possible? Any others?

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] index scan leads to result that is different from sec scan after upgrading to 8.3.4

[Teodor Sigaev]

Hmm.  So the problem seems to be statable as "a full-index scan on a
GIST index might fail to return all the rows, if the index has been
modified since creation".  Teodor, can you think of anything you
changed recently in that area?


Only fixing possible duplicates during index scan. Will see.

--
Teodor Sigaev   E-mail: [EMAIL PROTECTED]
   WWW: http://www.sigaev.ru/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Annoying Reply-To

[Martin Gainty]

since you are not an advocate of democracy I bid you adieu
Martin 
__ 
Disclaimer and confidentiality note 
Everything in this e-mail and any attachments relates to the official business 
of Sender. This transmission is of a confidential nature and Sender does not 
endorse distribution to any party other than intended recipient. Sender does 
not necessarily endorse content contained within this transmission. 


> Date: Sat, 18 Oct 2008 03:50:07 +0200
> From: [EMAIL PROTECTED]
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Annoying Reply-To
> 
> Hi Martinn, here the great Dictator Michelle!
> 
> Am 2008-10-17 10:24:44, schrieb Martin Gainty:
> > 
> > free unfettered and open discussion without interference from ANY entity is 
> > a requirement of a democracy
> > the REAL question is ..is this a democracy???
> 
> Shut-Up or I will install you Micr0$of SQL Server...  LOL  ;-)
> 
> Thanks, Greetings and nice Day/Evening
> Michelle Konzack
> Systemadministrator
> 24V Electronic Engineer
> Tamay Dogan Network
> Debian GNU/Linux Consultant
> 
> 
> -- 
> Linux-User #280138 with the Linux Counter, http://counter.li.org/
> # Debian GNU/Linux Consultant #
> Michelle Konzack   Apt. 917  ICQ #328449886
> +49/177/935194750, rue de Soultz MSN LinuxMichi
> +33/6/61925193 67100 Strasbourg/France   IRC #Debian (irc.icq.com)

_
Want to read Hotmail messages in Outlook? The Wordsmiths show you how.
http://windowslive.com/connect/post/wedowindowslive.spaces.live.com-Blog-cns!20EE04FBC541789!167.entry?ocid=TXT_TAGLM_WL_hotmail_092008

[GENERAL] pg_dump is ignoring my pgpass file

[Luca Ferrari]

Hi all,
I'm running 8.2.9, and I've got a curious problem on a database of my cluster. 
I've got my pgpass file:

[EMAIL PROTECTED]:~$ cat ~/.pgpass
192.168.1.2:5432:raydb:ray:xxx
192.168.1.2:5432:hrpmdb:hrpm:x
192.168.1.2:5432:vatcontrollerdb:vatcontroller:xx

and if I connect from the command line to any database I'm not prompted for a 
password. But if I try to execute the following:

pg_dump --create --column-inserts -v -f raydb_ott_20_08.sql  -U ray -h 
sedeldap  raydb

I'm prompted for a password immediatly. But if I execute the same command with 
another database (and another user) I'm not prompted for a password at all. 
I've checked and the ray user is also owner of the raydb.any idea on what 
I'm missing?

Thanks,
Luca



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] index scan leads to result that is different from sec scan after upgrading to 8.3.4

[Teodor Sigaev]

Hmm.  So the problem seems to be statable as "a full-index scan on a
GIST index might fail to return all the rows, if the index has been
modified since creation".  Teodor, can you think of anything you
changed recently in that area?


I still can't reproduce the bug, but found useless recheck condition with bitmap 
check:


drop table if exists qq;
select 1 as st , 1::int4 as t into qq from generate_series(1,1) as t;
create index qqidx on qq using gist (st) where t =1;
INSERT INTO qq (SELECT (4 * random())::int4, (4 * random())::int4 from 
generate_series(1,1));


# explain select t, count(1) from qq where t =1 group by t;
 QUERY PLAN
-
 GroupAggregate  (cost=19.62..633.49 rows=1 width=2)
   ->  Bitmap Heap Scan on qq  (cost=19.62..630.28 rows=640 width=2)
 Recheck Cond: (t = 1)
 ->  Bitmap Index Scan on qqidx  (cost=0.00..19.46 rows=640 width=0)

--
Teodor Sigaev   E-mail: [EMAIL PROTECTED]
   WWW: http://www.sigaev.ru/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Using a variable as tablename ins plpgsql?

[Glyn Astill]

Hi people,

Hopefully this is a quickie, I want to pass in a table name to a plpgsql 
function and then use that table name in my queries.

Is EXECUTE the only way to do this?

Ta
Glyn







Send instant messages to your online friends http://uk.messenger.yahoo.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using a variable as tablename ins plpgsql?

[Pavel Stehule]

Hello

2008/10/20 Glyn Astill <[EMAIL PROTECTED]>:
> Hi people,
>
> Hopefully this is a quickie, I want to pass in a table name to a plpgsql 
> function and then use that table name in my queries.
>
> Is EXECUTE the only way to do this?
>
> Ta
> Glyn
>

yes,

regards
Pavel Stehule

>
>
>
>
>
>
> Send instant messages to your online friends http://uk.messenger.yahoo.com
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using a variable as tablename ins plpgsql?

[Fernando Moreno]

2008/10/20 Glyn Astill <[EMAIL PROTECTED]>

> Hi people,
>
> Hopefully this is a quickie, I want to pass in a table name to a plpgsql
> function and then use that table name in my queries.
>
> Is EXECUTE the only way to do this?
>

As far as I know, yes. That's the only way to create queries using dynamic
table and column names.

[GENERAL] Booth Volunteers for LISA 08 (San Diego)

[Joshua Drake]

Hello,

PostgreSQL is going to have a booth at LISA:

http://www.usenix.org/event/lisa08/
http://www.usenix.org/event/lisa08/exhibition.html

Currently we are confirmed for Joshua Drake and Richard Broeserma.
However due to an excessive travel schedule by me, it would be great to
find a few folks to replace me at this event. At a minimum we need to
add at least one more person, preferably two.

Any volunteers?

Joshua D. Drake



-- 
The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Booth Volunteers for LISA 08 (San Diego)

[Martin Gainty]

let us know when they come to btown or chicago

Thanks Joshua
Martin 
__ 
Disclaimer and confidentiality note 
Everything in this e-mail and any attachments relates to the official business 
of Sender. This transmission is of a confidential nature and Sender does not 
endorse distribution to any party other than intended recipient. Sender does 
not necessarily endorse content contained within this transmission. 


> Date: Mon, 20 Oct 2008 10:06:10 -0700
> From: [EMAIL PROTECTED]
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Booth Volunteers for LISA 08 (San Diego)
> 
> Hello,
> 
> PostgreSQL is going to have a booth at LISA:
> 
> http://www.usenix.org/event/lisa08/
> http://www.usenix.org/event/lisa08/exhibition.html
> 
> Currently we are confirmed for Joshua Drake and Richard Broeserma.
> However due to an excessive travel schedule by me, it would be great to
> find a few folks to replace me at this event. At a minimum we need to
> add at least one more person, preferably two.
> 
> Any volunteers?
> 
> Joshua D. Drake
> 
> 
> 
> -- 
> The PostgreSQL Company since 1997: http://www.commandprompt.com/ 
> PostgreSQL Community Conference: http://www.postgresqlconference.org/
> United States PostgreSQL Association: http://www.postgresql.us/
> 
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

_
You live life beyond your PC. So now Windows goes beyond your PC.
http://clk.atdmt.com/MRT/go/115298556/direct/01/

Re: [GENERAL] index scan leads to result that is different from sec scan after upgrading to 8.3.4

[Tom Lane]

Teodor Sigaev <[EMAIL PROTECTED]> writes:
> I still can't reproduce the bug, but found useless recheck condition with 
> bitmap 
> check:

> select 1 as st , 1::int4 as t into qq from generate_series(1,1) as t;
> create index qqidx on qq using gist (st) where t =1;
> INSERT INTO qq (SELECT (4 * random())::int4, (4 * random())::int4 from 
> generate_series(1,1));

> # explain select t, count(1) from qq where t =1 group by t;
>   QUERY PLAN
> -
>   GroupAggregate  (cost=19.62..633.49 rows=1 width=2)
> ->  Bitmap Heap Scan on qq  (cost=19.62..630.28 rows=640 width=2)
>   Recheck Cond: (t = 1)
>   ->  Bitmap Index Scan on qqidx  (cost=0.00..19.46 rows=640 width=0)

No, it's not useless.  If the bitmap goes lossy, we have to recheck the
index predicate too, because we'll be looking at all rows on the
indicated page(s) and there's no guarantee that they all match the
predicate.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Slow delete with with cascading foreign keys

[Matthias Karlsson]

Tom Lane skrev:

"Matthias Karlsson" <[EMAIL PROTECTED]> writes:

I have a rather complex set of relations, connected with cascading
foreign keys on delete. I'm experiencing very slow performance when
deleting *the* lead node, which everything eventually depends on. The
number of records ultimately to be deleted aren't that many (perhaps
2000-3000) but there are maybe 30 relations involved. I understand
that this is most likely due to missing indices, but I have been
unable to figure out how to get PostgreSQL to tell me where the
performance bottleneck is.


If it's a reasonably modern PG version, EXPLAIN ANALYZE will break out
the time spent in each on-delete trigger, which should be enough to
answer the question.

regards, tom lane


Thanks, that gave me something to work with. I targeted the triggers that had the most "uses", but it did not seem to 
help that much. I managed to reduce execution time with maybe 10-15%, but I'll try to apply indices more aggressively to 
see if it helps.


// Matthias

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [HACKERS] Hot Standby utility and administrator functions

[Robert Haas]

> * pg_last_recovered_xact_xid()
> Will throw an ERROR if *not* executed in recovery mode.
> returns bigint
>
> * pg_last_completed_xact_xid()
> Will throw an ERROR *if* executed in recovery mode.
> returns bigint

Should these return xid?

...Robert

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [HACKERS] Hot Standby utility and administrator functions

[Simon Riggs]

On Mon, 2008-10-20 at 16:22 -0400, Robert Haas wrote:
> > * pg_last_recovered_xact_xid()
> > Will throw an ERROR if *not* executed in recovery mode.
> > returns bigint
> >
> > * pg_last_completed_xact_xid()
> > Will throw an ERROR *if* executed in recovery mode.
> > returns bigint
> 
> Should these return xid?

Perhaps, but they match txid_current() which returns bigint.
http://developer.postgresql.org/pgdocs/postgres/functions-info.html

Thanks for checking.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [HACKERS] Hot Standby utility and administrator functions

[Alvaro Herrera]

Simon Riggs escribió:
> 
> On Mon, 2008-10-20 at 16:22 -0400, Robert Haas wrote:
> > > * pg_last_recovered_xact_xid()
> > > Will throw an ERROR if *not* executed in recovery mode.
> > > returns bigint
> > >
> > > * pg_last_completed_xact_xid()
> > > Will throw an ERROR *if* executed in recovery mode.
> > > returns bigint
> > 
> > Should these return xid?
> 
> Perhaps, but they match txid_current() which returns bigint.
> http://developer.postgresql.org/pgdocs/postgres/functions-info.html

That's been "extended with an epoch counter" per the docs; I don't think
that's appropriate for the new functions, is it?

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Debian no longer dumps cores?

[Alvaro Herrera]

Hi,

My Debian system (now running Linux 2.6.26) is no longer dumping core
files, and I can't figure out why :-(

Of course, I've set ulimit -c to unlimited, and I'm running the
postmaster directly in the same shell (no pg_ctl or init scripts), but
it's still not working.  I'm not sure where else to look; Google
searches return tons of junk but nothing useful.

The kernel was compiled by me to add oprofile support, though I used
Debian's .config to generate the base config, and changed nothing else
(no time to fiddle).

If anybody has any idea of what's going on I'd appreciate it.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [HACKERS] Hot Standby utility and administrator functions

[Simon Riggs]

On Mon, 2008-10-20 at 17:44 -0300, Alvaro Herrera wrote:
> Simon Riggs escribió:
> > 
> > On Mon, 2008-10-20 at 16:22 -0400, Robert Haas wrote:
> > > > * pg_last_recovered_xact_xid()
> > > > Will throw an ERROR if *not* executed in recovery mode.
> > > > returns bigint
> > > >
> > > > * pg_last_completed_xact_xid()
> > > > Will throw an ERROR *if* executed in recovery mode.
> > > > returns bigint
> > > 
> > > Should these return xid?
> > 
> > Perhaps, but they match txid_current() which returns bigint.
> > http://developer.postgresql.org/pgdocs/postgres/functions-info.html
> 
> That's been "extended with an epoch counter" per the docs; I don't think
> that's appropriate for the new functions, is it?

I assumed it was, so you can subtract them easily. 

It can be done either way, I guess. Happy to provide what people need. I
just dreamed up a few that sounded useful.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How best to implement a multi-table constraint?

[Karl Nack]

Hello all,

I'm a bit of a newb designing a database to hold landcover information for
properties in a city. Here's some simple sample data:

property:
property_name*, property_area
-
sample house, 2500


property_landcover:
property_name*, landcover_name*, landcover_area
---
sample house, building, 1000
sample house, grass, 1000
sample house, concrete, 500


Now, I need to check that the sum of landcover_area for a property matches
the property_area.

It seems like I have three obvious options:

1. A constraint trigger that sums up landcover area and compares it to the
property area.

Downside: The trigger will run for every row that's updated in these two
tables, although it only needs to run once for each property.


2. A statement-level trigger that does the same thing as #1.

Downside: Since I don't have access to the updated rows, I'll have to
check the entire property table against the entire property_landcover
table. It seems like this could get expensive if either of these tables
gets very large.


3. Use a 3rd table to hold the total landcover area for each property. Use
row-level triggers to keep this 3rd table updated. Use a statement-level
trigger (or table constraint) to ensure the total landcover area matches
the property area.

Downside: Although I avoid redundant checks, my understanding is that
UPDATE is a fairly expensive operation, so it might not actually perform
any better.


Although my tables are small right now, they may potentially have to hold
an entire city's worth of properties, so I'm interested in finding a
solution that scales.

Can anyone offer some feedback or suggestions on which of these options to
use? Or perhaps even another solution that hasn't occurred to me?

Thanks!

-Karl

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [HACKERS] Debian no longer dumps cores?

[Greg Smith]

On Mon, 20 Oct 2008, Alvaro Herrera wrote:


My Debian system (now running Linux 2.6.26) is no longer dumping core
files, and I can't figure out why :-(


My guess is that you're being nailed by one of the changes related to 
implementing the improved capabilities interface made in 2.6.25 or 2.6.26. 
I know that broke some libpcap versions for example: 
http://lkml.org/lkml/2008/4/22/18


I haven't upgraded a Debian system to a kernel that new yet myself to know 
specifically what's wrong, the blog posting at 
http://linux-man-pages.blogspot.com/2008/05/capabilities-have-fully-arrived-finally.html 
is a good starter point to dig for more info.  If I had to make a guess 
I'd suspect whatever is dumping core doesn't have the CAP_SYS_RAWIO 
capability needed to access /proc/kcore and you need to tweak a file 
related to that area rather than the ulimit stuff.


Also worth mentioning is that you may need to adjust 
/proc/sys/fs/suid_dumpable (see 
http://manpages.courier-mta.org/htmlman5/proc.5.html ) and that there have 
been distributions that just broke even the CAP_SYS_RAWIO interface at the 
kernel level; see http://vir.homelinux.org/blog/archives/30-Lost-time.html 
for an example.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Can PL/PGSQL function return multiple SETOFs

[cyw]

Is something like this possible?
CREATE testsetof( IN toad_id integer)   RETURNS SETOF road_table, SETOF int4 
AS $BODY$


If yes, is this the way to do 'RETURN NEXT'?
   RETURN NEXT road_table_row, an_integer;

Thanks
CYW 




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [HACKERS] Hot Standby utility and administrator functions

[Tom Lane]

Simon Riggs <[EMAIL PROTECTED]> writes:
> On Mon, 2008-10-20 at 17:44 -0300, Alvaro Herrera wrote:
>> That's been "extended with an epoch counter" per the docs; I don't think
>> that's appropriate for the new functions, is it?

> I assumed it was, so you can subtract them easily. 

> It can be done either way, I guess. Happy to provide what people need. I
> just dreamed up a few that sounded useful.

I don't think you should be inventing new functions without clear
use-cases in mind.  Depending on what the use is, I could see either the
xid or the txid definition as being *required*.

In any case, do not use the wrong return type for the definition you're
implementing.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Slow delete with with cascading foreign keys

[Tom Lane]

Matthias Karlsson <[EMAIL PROTECTED]> writes:
> Tom Lane skrev:
>> If it's a reasonably modern PG version, EXPLAIN ANALYZE will break out
>> the time spent in each on-delete trigger, which should be enough to
>> answer the question.

> Thanks, that gave me something to work with. I targeted the triggers that had 
> the most "uses", but it did not seem to 
> help that much. I managed to reduce execution time with maybe 10-15%, but 
> I'll try to apply indices more aggressively to 
> see if it helps.

Hm, you still didn't mention which PG version you're using --- but if
it's pre-8.3, keep in mind that you need to start a fresh session to
get the plans for FK triggers to change.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_dump is ignoring my pgpass file

[Tom Lane]

Luca Ferrari <[EMAIL PROTECTED]> writes:
> I've got my pgpass file:

> [EMAIL PROTECTED]:~$ cat ~/.pgpass
> 192.168.1.2:5432:raydb:ray:xxx
> 192.168.1.2:5432:hrpmdb:hrpm:x
> 192.168.1.2:5432:vatcontrollerdb:vatcontroller:xx

> ... But if I try to execute the following:

> pg_dump --create --column-inserts -v -f raydb_ott_20_08.sql  -U ray -h 
> sedeldap  raydb

AFAICT the matching of .pgpass entries to a connection attempt is
strictly textual.  "sedeldap" != "192.168.1.2" therefore none of
these entries apply.  The question is not so much why ray isn't
getting let in, as why anyone else is ...

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Session variables (how do I pass session data to a function)

[Guy Rouillier]

Philip W. Dalrymple wrote:


What I want to do is to add to this another field that will be NULL
UNLESS the session sets a value to the "user" for that the middle-wear 
system is acting for.


This can be done via a setting.  See 
http://www.postgresql.org/docs/8.2/static/functions-admin.html#FUNCTIONS-ADMIN-SET-TABLE


If you'd like to read a fuller discussion of this very topic, search for 
a thread titled "can I define own variables" in the archives.  I had to 
do the same thing back then.


--
Guy Rouillier

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Can PL/PGSQL function return multiple SETOFs

[Pavel Stehule]

Hello

plpgsql should return only one set. You should to returns set of
cursors - that is real multisets.
http://www.postgresql.org/docs/8.3/interactive/plpgsql-cursors.html

regards
Pavel Stehule

2008/10/21  <[EMAIL PROTECTED]>:
> Is something like this possible?
> CREATE testsetof( IN toad_id integer)   RETURNS SETOF road_table, SETOF int4
> AS $BODY$
>
> If yes, is this the way to do 'RETURN NEXT'?
>   RETURN NEXT road_table_row, an_integer;
>
> Thanks
> CYW
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [HACKERS] Debian no longer dumps cores?

[tomas]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Mon, Oct 20, 2008 at 05:49:04PM -0300, Alvaro Herrera wrote:
> Hi,
> 
> My Debian system (now running Linux 2.6.26) is no longer dumping core
> files, and I can't figure out why :-(

FWIW, same happens here, out-of-the-box 2.6.26-1 vanilla Debian. Booting
with 2.6.24-1 "fixes" this. Didn't try 2.6.25-1 yet.

Regards
- -- tomás
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFI/XFUBcgs9XrR2kYRAmATAJ96SU3oNvNWeJw0VOB7RMBcL66npQCfeZ1Q
YlMSHwJ5c/XxgH3sFpDuA94=
=a1Vq
-END PGP SIGNATURE-

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general