[GENERAL] Is there any special way to a trigger send a signal to outer application?
Hi! Windows Server, PGSQL. When a new records arrived to the database, I want process them in the background. I have a processor service. This is periodically (5-10 minutes) checking the message table, and if there is some message, it is process them. This periodic processing is good, but I want to decrease the process time, when records arrived (to increase the performance, and dec. the user's waiting). So I search for a way to do any IPC communication from PGSQL to the processor service. Which way is supports by PGSQL trigger? a.) Starting a new process with any params (this app. can send a WM_* message to my service)? b.) Sending a WM_* message (PostMessage) to my service? c.) Sending a TCP message to my service? Or other? If you have an example about this problem, please send me with the answer! Thanks for your help: dd -- 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 there any special way to a trigger send a signal to outer application?
durumdara wrote: Which way is supports by PGSQL trigger? One way you did NOT mention, but you need to look into: use LISTEN and NOTIFY. Whether or not this is suitable depends on how your application is accessing PostgreSQL, as IIRC some database access APIs (eg: ODBC) do not support LISTEN/NOTIFY. In answer to your questions: a.) Starting a new process with any params (this app. can send a WM_* message to my service)? You could use PL/Python or PL/Perl to invoke the process. Your function must be written extremely carefully to avoid producing a gaping security hole, though, if you have multiple levels of trust accessing your database. b.) Sending a WM_* message (PostMessage) to my service? Maybe you could do it via PL/Python or PL/Perl using a DCOM or some other win32-specific extension module for those languages, too. You could also do that by writing a custom C extension function to PostgreSQL, compiling it, and loading it into the server. A bit of work, though. c.) Sending a TCP message to my service? Same answer as (a) and (b), really - use PL/Python or PL/Perl. -- Craig Ringer -- 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] possible small contribution to the PostgreSQL manual? Example for two-phase commit section.
Aleksey Tsalolikhin wrote: Hi. We're trying to implement two-phase commit and did not find a complete working example in the manual. We found examples of the separate pieces, but not the sequence in which to put them together. [snip] If there is somebody on this list involved with editing the manual, this message is for you. :)Examples make new things clearer, and easier to learn. Just a suggestion. :) You might want to add this as a comment on the interactive version of the online documentation, too, so it's not lost when revising the docs for 8.4 / 8.5 . -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] reducing IO and memory usage: sending the content of a table to multiple files
This is the work-flow I've in mind: 1a) take out *all* data from a table in chunks (M record for each file, one big file?) (\copy??, from inside a scripting language?) 2a) process each file with awk to produce N files very similar each other (substantially turn them into very simple xml) 3a) gzip them 2b) use any scripting language to process and gzip them avoiding a bit of disk IO Does PostgreSQL offer me any contrib, module, technique... to save some IO (and maybe disk space for temporary results?). Are there any memory usage implication if I'm doing a: pg_query(select a,b,c from verylargetable; --no where clause); vs. the \copy equivalent any way to avoid them? thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Posgres Adding braces at beginning and end of text (html) content
Hi All,
I'm fairly new to postgres and I'm having this peculiar problem.
I'm storing raw html in a text field and I want users who know HTML to
update the content in a textarea field.
The problem is postgres is adding braces to the begining and ending of
the content. On creation and every time I update.
This is:
I input:
pxyz p/
.
into the text area field I save and view I see
{pxyz/p
.
}
On a subsequent update I see
{{pxyz/p
}}
On another I see
{{pxyz/p
}}
Not sure what is happening here?
I am using postgres 8.3 on windows
Re: [GENERAL] %r in restore_command?
Hi Duco, sorry that I can't help you. But I want to say, that I have experienced this last week too. In my case there was a power outage before and after restart this happened, so ... ... but would be nice to have a comment from an expert. Regards, Bernhard -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Help with C-Function on Postgre
Hi to all, I'm new on this list and also on using postgre. I wanted to write some C-Function but, also if I read all the documentation about it on the Manuals directory on the postgre web page, i still have some problems. I already made some simple works just to try how was working and all went fine. Now I need to make some basic operations inside the database, for example create new tables, temp tables, delete them insert some new information inside existent tables, and something like this. The problem is that i didn't find nothing about it. I found only some information about this things on the doxygen section of the postgre website but is really difficult for me (http://doxygen.postgresql.org/) [I went in the directory include/server and I took a look around there but without understand a lot]. I hope you can help me! Thank you in advance, Angelo. _ Chiama gratis dal tuo PC! Parla su Messenger http://clk.atdmt.com/GBL/go/140630369/direct/01/
[GENERAL] Posgres Adding braces at beginning and end of text (html) content
Hi All,
I'm fairly new to postgres and I'm having this peculiar problem.
I'm storing raw html in a text field and I want users who know HTML to
update the content in a textarea field.
The problem is postgres is adding braces to the begining and ending of
the content. On creation and every time I update.
This is:
I input:
pxyz p/
.
into the text area field I save and view I see
{pxyz/p
.
}
On a subsequent update I see
{{pxyz/p
}}
On another I see
{{pxyz/p
}}
Not sure what is happening here?
I am using postgres 8.3 on windows
--
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] Posgres Adding braces at beginning and end of text (html) content
On Thursday 2. April 2009, linnewbie wrote: Hi All, I'm fairly new to postgres and I'm having this peculiar problem. I'm storing raw html in a text field and I want users who know HTML to update the content in a textarea field. The problem is postgres is adding braces to the begining and ending of the content. On creation and every time I update. I can't reproduce your problem in 8.3.5: pgslekt= create table test (i integer, t text); CREATE TABLE pgslekt= insert into test values (1, 'pHei hei/p'); INSERT 0 1 pgslekt= select * from test; i | t ---+ 1 | pHei hei/p (1 row) Perhaps it's a middleware problem? -- Leif Biberg Kristensen | Registered Linux User #338009 Me And My Database: http://solumslekt.org/blog/ -- 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] %r in restore_command?
Duco Fijma wrote: Please allow me to rephrase a question I asked on this list some time ago. Could somebody shine some light on what exactly influences the value of the %r parameter in the restore_command (as used in recovery.conf)? I'm using this in a hot-standby-configuration in combination with pg_standby and _sometimes_ my archive on shipped transaction logs grow really huge. The value of %r then never changes any more in subsequent calls of the restore_command, causing pg_standby to not delete any WAL segment anymore. AFAIR %r is supposed to mean the earliest segment that can safely be removed(*). If there's a lot of backlog then perhaps the recovery process has stopped replaying WAL segments for some reason. Is there anything unusual in the slave logs? (*) I *think* the technical definition is segment previous to the one on which the last restartpoint was set, or something similar. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 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] Posgres Adding braces at beginning and end of text (html) content
(CC'ed to the list)
On Thursday 2. April 2009, linnewbie wrote:
I am using tcl ( ncgi and tclobdc ) so it is more like the excerpts
below:
ie I input:
h1Hello World /h1
pxyz p/
into the text area field, save:
set page_content [ ncgi::value textarea_field_name]
database connect dbh $datasource $dbuser $dbpassword
set sql INSERT INTO profile (page_content) \
VALUES('$page_content')
dbh $sql
view:
set sql SELECT page_content FROM profile \
WHERE page_id = $page
set page_content [lindex [ dbh $sql ] 0]
::ncgi::header text/html
puts textarea id='page_content' name='page_content' $page_content
/ textarea
in browser I see:
{h1Hello World /h1
pxyz p/
.
}
On a subsequent update I see
{{
h1Hello World /h1
pxyz p/
.
}}
On another I see
{{{
h1Hello World /h1
pxyz p/
.
}}}
This is definitely not a postgresql problem. I'm storing tons of HTML
code, mostly via PHP scripts, and have had only minor issues with it,
eg. HTML entities like amp; being rendered as naked ampersands on
retrieval. That's a nuisance when you try to keep the W3C validator
happy, but there are ways around it.
You should probably present your problem to the Tcl community, and see
if they can come up with a reason for this oddity.
--
Leif Biberg Kristensen | Registered Linux User #338009
Me And My Database: http://solumslekt.org/blog/
--
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] Posgres Adding braces at beginning and end of text (html) content
PostgreSQL does not add braces to text. It sounds like a problem with the code
you have that inserts and retreives data out of PostgreSQL
Let's try a test case:
BEGIN;
CREATE TEMP TABLE test_table (
foo text
);
INSERT INTO test_table (foo) VALUES('htmlfoo/html');
SELECT foo FROM test_table;
ROLLBACK;
The result of the select statement should look like:
foo
--
htmlfoo/html
(1 row)
i.e., no added braces.
John L. Cheng
From: linnewbie linnew...@gmail.com
To: pgsql-general@postgresql.org
Sent: Thursday, April 2, 2009 5:48:40 AM
Subject: [GENERAL] Posgres Adding braces at beginning and end of text (html)
content
Hi All,
I'm fairly new to postgres and I'm having this peculiar problem.
I'm storing raw html in a text field and I want users who know HTML to
update the content in a textarea field.
The problem is postgres is adding braces to the begining and ending of
the content. On creation and every time I update.
This is:
I input:
pxyz p/
..
into the text area field I save and view I see
{pxyz/p
..
}
On a subsequent update I see
{{pxyz/p
.
}}
On another I see
{{pxyz/p
.
}}
Not sure what is happening here?
I am using postgres 8.3 on windows
--
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] Posgres Adding braces at beginning and end of text (html) content
On Apr 2, 8:59 am, l...@solumslekt.org (Leif B. Kristensen) wrote:
On Thursday 2. April 2009, linnewbie wrote:
Hi All,
I'm fairly new to postgres and I'm having this peculiar problem.
I'm storing raw html in a text field and I want users who know HTML to
update the content in a textarea field.
The problem is postgres is adding braces to the begining and ending of
the content. On creation and every time I update.
I can't reproduce your problem in 8.3.5:
pgslekt= create table test (i integer, t text);
CREATE TABLE
pgslekt= insert into test values (1, 'pHei hei/p');
INSERT 0 1
pgslekt= select * from test;
i | t
---+
1 | pHei hei/p
(1 row)
Perhaps it's a middleware problem?
--
Leif Biberg Kristensen | Registered Linux User #338009
Me And My Database:http://solumslekt.org/blog/
--
Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
To make changes to your
subscription:http://www.postgresql.org/mailpref/pgsql-general
I am using tcl ( ncgi and tclobdc ) so it is more like the excerpts
below:
ie I input:
h1Hello World /h1
pxyz p/
.
into the text area field, save:
set page_content [ ncgi::value textarea_field_name]
database connect dbh $datasource $dbuser $dbpassword
set sql INSERT INTO profile (page_content) \
VALUES('$page_content')
dbh $sql
..
..
..
view:
set sql SELECT page_content FROM profile \
WHERE page_id = $page
set page_content [lindex [ dbh $sql ] 0]
::ncgi::header text/html
...
...
...
puts textarea id='page_content' name='page_content' $page_content
/
textarea
.
in browser I see:
{h1Hello World /h1
pxyz p/
.
}
On a subsequent update I see
{{
h1Hello World /h1
pxyz p/
.
}}
On another I see
{{{
h1Hello World /h1
pxyz p/
.
--
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] Posgres Adding braces at beginning and end of text (html) content
In response to linnewbie :
I am using tcl ( ncgi and tclobdc ) so it is more like the excerpts
below:
ie I input:
h1Hello World /h1
pxyz p/
.
into the text area field, save:
set page_content [ ncgi::value textarea_field_name]
database connect dbh $datasource $dbuser $dbpassword
set sql INSERT INTO profile (page_content) \
VALUES('$page_content')
That is a security hole for sql-injection.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
--
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] Posgres Adding braces at beginning and end of text (html) content
hi,
i'm not a tcl user but it looks like an array representation. try to
remove braces [] from page_content.
regards.
thomas
linnewbie schrieb:
into the text area field, save:
set page_content [ ncgi::value textarea_field_name]
database connect dbh $datasource $dbuser $dbpassword
set sql INSERT INTO profile (page_content) \
VALUES('$page_content')
dbh $sql
--
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] possible small contribution to the PostgreSQL manual? Example for two-phase commit section.
On Thu, Apr 2, 2009 at 1:23 AM, Craig Ringer cr...@postnewspapers.com.au wrote: You might want to add this as a comment on the interactive version of the online documentation, too, so it's not lost when revising the docs for 8.4 / 8.5 . Done, sir. Thanks! Aleksey -- Aleksey Tsalolikhin UNIX System Administrator I get stuff done! http://www.verticalsysadmin.com/ LinkedIn - http://www.linkedin.com/in/atsaloli -- 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 to find the query completeion time?
Hi, Is there a way in Postgres to find when a particular query will finish? For example, for a query like this SELECT * FROM TABLE1 Can we find out from any of the catalog tables(or any other way) when this query is likely to complete? Thanks -- 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] How to find the query completeion time?
On Thursday 2. April 2009, SHARMILA JOTHIRAJAH wrote: Hi, Is there a way in Postgres to find when a particular query will finish? For example, for a query like this SELECT * FROM TABLE1 Can we find out from any of the catalog tables(or any other way) when this query is likely to complete? Thanks How about EXPLAIN ANALYZE SELECT * FROM TABLE1 ? or just set \timing in the psql and run the query. -- Leif Biberg Kristensen | Registered Linux User #338009 Me And My Database: http://solumslekt.org/blog/ -- 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] Posgres Adding braces at beginning and end of text (html) content
On Apr 2, 10:01 am, andreas.kretsch...@schollglas.com (A.
Kretschmer) wrote:
In response to linnewbie :
I am using tcl ( ncgi and tclobdc ) so it is more like the excerpts
below:
ie I input:
h1Hello World /h1
pxyz p/
.
into the text area field, save:
set page_content [ ncgi::value textarea_field_name]
database connect dbh $datasource $dbuser $dbpassword
set sql INSERT INTO profile (page_content) \
VALUES('$page_content')
That is a security hole for sql-injection.
This database user only has select,insert,update privileges on this
table and these are internal users (administrators) so I'm not sure
how much trouble they can make.
Is there another way to have users update content that is really
really complex html, nested ul with spans with spacial classes
etc?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] cast needed - but where and why?
Hello list,
I am getting the following error after upgrading from 7.4.6 to 8.3.6
and can't figure out what is wrong. Any help would be greatly appreciated.
from our program:
sqlcode=-400 errmsg='column event_ref_log_no is of type integer but
expression is of type text' in line 4138.
from pg_log:
2009-04-02 10:45:10 EDT:srm2api:ERROR: column event_ref_log_no is of type
integer but expression is of type text at character 146
2009-04-02 10:45:10 EDT:srm2api:HINT: You will need to rewrite or cast the
expression.
2009-04-02 10:45:10 EDT:srm2api:STATEMENT: insert into t_unit_event_log (
event_log_no , unit_serial_no , event_type
, event_category , event_mesg , event_severity , event_status ,
event_ref_log_no , event_logged_by , event_date ,
alarm , last_updated_by , last_updated_date ) values ( nextval (
'seq_event_log_no' ) , $1 , $2 , $3 , $4 , $
5 , $6 , case when $7 0 then $8 else null end , current_user , now ()
, $9 , current_user , now () )
from our program:
exec sql begin declare section;
int h_cnt= 0;
int h_event_ref_log_no = NULL;
...
// insert into uel
exec sql insert into t_unit_event_log
(event_log_no,
unit_serial_no,
event_type,
event_category,
event_mesg,
event_severity,
event_status,
event_ref_log_no,
event_logged_by,
event_date,
alarm,
last_updated_by,
last_updated_date)
values (nextval('seq_event_log_no'),
:h_serial_no,
:h_type,
:h_category,
:h_mesg,
:h_sev,
:h_status,
casewhen :h_event_ref_log_no 0
then :h_event_ref_log_no
else null end,
current_user,
now(),
:h_alarm,
current_user,
now());
Thanks,
Steve
--
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] How to find the query completeion time?
--- On Thu, 4/2/09, Leif B. Kristensen l...@solumslekt.org wrote: From: Leif B. Kristensen l...@solumslekt.org Subject: Re: [GENERAL] How to find the query completeion time? To: pgsql-general@postgresql.org Date: Thursday, April 2, 2009, 10:53 AM On Thursday 2. April 2009, SHARMILA JOTHIRAJAH wrote: Hi, Is there a way in Postgres to find when a particular query will finish? For example, for a query like this SELECT * FROM TABLE1 Can we find out from any of the catalog tables(or any other way) when this query is likely to complete? Thanks How about EXPLAIN ANALYZE SELECT * FROM TABLE1 ? or just set \timing in the psql and run the query. This will basically execute the query and return the time taken. Is there a way to know when an already-started query will end? In ORACLE I can get that information from V$SESSION_LONGOPS view which will give the approx TIME_REMAINING to complete running queries. Is there a similar way in postgres? -- Leif Biberg Kristensen | Registered Linux User #338009 Me And My Database: http://solumslekt.org/blog/ -- 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] cast needed - but where and why?
Steve Clark scl...@netwolves.com writes: I am getting the following error after upgrading from 7.4.6 to 8.3.6 and can't figure out what is wrong. Any help would be greatly appreciated. 2009-04-02 10:45:10 EDT:srm2api:ERROR: column event_ref_log_no is of type integer but expression is of type text at character 146 I don't know ecpg very well, but if it doesn't provide any information about parameter datatypes then the backend would resolve this: case when $7 0 then $8 else null end as producing a result of type text. 7.4 would have allowed that to be cast to int silently, but 8.3 won't (and the runtime cast involved would've been expensive anyway). I suggest sticking a cast directly on the ambiguous parameter, ie casewhen :h_event_ref_log_no 0 then :h_event_ref_log_no :: integer else null end, (You needn't cast the null, since the type attached to the other case arm is a sufficient cue.) 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] Posgres Adding braces at beginning and end of text (html) content
On Apr 2, 11:06 am, linnewbie linnew...@gmail.com wrote:
On Apr 2, 10:01 am, andreas.kretsch...@schollglas.com (A.
Kretschmer) wrote:
In response to linnewbie :
I am using tcl ( ncgi and tclobdc ) so it is more like the excerpts
below:
ie I input:
h1Hello World /h1
pxyz p/
.
into the text area field, save:
set page_content [ ncgi::value textarea_field_name]
database connect dbh $datasource $dbuser $dbpassword
set sql INSERT INTO profile (page_content) \
VALUES('$page_content')
That is a security hole for sql-injection.
This database user only has select,insert,update privileges on this
table and these are internal users (administrators) so I'm not sure
how much trouble they can make.
Is there another way to have users update content that is really
really complex html, nested ul with spans with spacial classes
etc?
This is a tcl thing though.
--
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] Help with C-Function on Postgre
On Thu, Apr 02, 2009 at 12:52:40PM +0200, Angelo Nicolosi wrote: I wanted to write some C-Function Where is this code going to live? if it's going to be inside PG as a function you can call from SQL you want something called SPI: http://www.postgresql.org/docs/current/static/spi.html If it's going to be outside PG and connect to the database to do its work you want to use the client libraries: http://www.postgresql.org/docs/current/static/libpq.html Hope that helps! -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] string_to_array with empty input
On Apr 1, 2009, at 12:19 PM, Robert Haas wrote:
my @ints = map { $_ || 0 } split ',', $string;
This ensures that I get the proper number of records in the example
of something like '1,2,,4'.
I can't see that there's any way to do this in SQL regardless of how
we define this operation.
It's easy enough to write a function to do it:
CREATE OR REPLACE FUNCTION trim_blanks (anyarray) RETURNS anyarray AS $$
SELECT ARRAY(
SELECT CASE WHEN $1[i] IS NULL OR $1[i] = '' THEN '0' ELSE
$1[i] END
FROM generate_series(1, array_upper($1, 1)) s(i)
ORDER BY i
);
$$ LANGUAGE SQL IMMUTABLE;
Best,
David
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] string_to_array with empty input
On Apr 1, 2009, at 2:22 PM, Tom Lane wrote:
Another way to state the point is that we can offer people a choice of
two limitations: string_to_array doesn't work for zero-length lists,
or string_to_array doesn't work for empty strings (except most of the
time, it does). The former is sounding less likely to bite people
unexpectedly.
Right, very well put.
Or we could stick to the current behavior and say use COALESCE() to
resolve the ambiguity, if you need to.
Steve has a point that leaving it as-is leaves it as impossible to
tell the difference between string_to_array(NULL, ',') and
string_to_array('', ','). The former properly handles an unknown
value, while the latter, where '' is a known value, seems weird to be
returning NULL.
Best,
David
--
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] reducing IO and memory usage: sending the content of a table to multiple files
On Thu, Apr 02, 2009 at 11:20:02AM +0200, Ivan Sergio Borgonovo wrote: This is the work-flow I've in mind: 1a) take out *all* data from a table in chunks (M record for each file, one big file?) (\copy??, from inside a scripting language?) What about using cursors here? 2a) process each file with awk to produce N files very similar each other (substantially turn them into very simple xml) 3a) gzip them GZIP uses significant CPU time; there are various lighter weight schemes available that may be better depending on where this data is going. 2b) use any scripting language to process and gzip them avoiding a bit of disk IO What disk IO are you trying to save and why? Does PostgreSQL offer me any contrib, module, technique... to save some IO (and maybe disk space for temporary results?). Are there any memory usage implication if I'm doing a: pg_query(select a,b,c from verylargetable; --no where clause); vs. the \copy equivalent any way to avoid them? As far as I understand it will get all the data from the database into memory first and then your code gets a chance. For large datasets this obviously doesn't work well. CURSORs are you friend here. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] string_to_array with empty input
Leif B. Kristensen wrote:
On Thursday 2. April 2009, Steve Crawford wrote:
Currently string_to_array(null, ',') yields a null result -
indistinguishable from string_to_array('',','). Wrapping in coalesce
does not help distinguish true null input from empty-string input. I'm
not sure at the moment what other cases exist where non-null input
generates null output.
Somehow this reminds me of the old division by zero problem.
IMO, the proper way to handle this kind of anomaly would be to test if
the length of the string is non-zero before submitting it to the
string_to_array() function.
Quite the opposite. Where division by zero is simply illegal as is, say,
string_to_array(1234, ','), string_to_array('', ',') is legal.
Unfortunately it is legal, and legal and legal with numerous
reasonable interpretations of which legal is most appropriate/consistent.
I would argue against a change to have string_to_array('',',') throw an
error.
Cheers,
Steve
--
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] cast needed - but where and why?
Tom Lane wrote: Steve Clark scl...@netwolves.com writes: I am getting the following error after upgrading from 7.4.6 to 8.3.6 and can't figure out what is wrong. Any help would be greatly appreciated. 2009-04-02 10:45:10 EDT:srm2api:ERROR: column event_ref_log_no is of type integer but expression is of type text at character 146 I don't know ecpg very well, but if it doesn't provide any information about parameter datatypes then the backend would resolve this: case when $7 0 then $8 else null end as producing a result of type text. 7.4 would have allowed that to be cast to int silently, but 8.3 won't (and the runtime cast involved would've been expensive anyway). I suggest sticking a cast directly on the ambiguous parameter, ie casewhen :h_event_ref_log_no 0 then :h_event_ref_log_no :: integer else null end, (You needn't cast the null, since the type attached to the other case arm is a sufficient cue.) regards, tom lane Thanks Tom, that fixed the problem. I wasn't thinking about what the back end was seeing, only that it was defined in my pgc program as an int. Regards, Steve -- 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] How to find the query completeion time?
In ORACLE I can get that information from V$SESSION_LONGOPS view which will give the approx TIME_REMAINING to complete running queries. Is there a similar way in postgres? As far as I know, PostgreSQL has no such facility, and the database server has no idea how long a given query will take to run. I'm not really sure how Oracle does that, given that the performance of a long-running query will vary during its execution. Other queries will begin and end, altering resource availability. Also, if your query runs in several large parts, it can be hard to estimate how quickly parts you haven't started executing yet will run. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] string_to_array with empty input
On Thu, Apr 02, 2009 at 09:29:04AM -0700, Steve Crawford wrote:
Leif B. Kristensen wrote:
Somehow this reminds me of the old division by zero problem.
IMO, the proper way to handle this kind of anomaly would be to test if
the length of the string is non-zero before submitting it to the
string_to_array() function.
Quite the opposite. Where division by zero is simply illegal
This is just a matter of definitions; divide by zero is fine in lots of
languages and no exception will be raised. The fact that you're saying
it's simply illegal means that you've internalised the definition to
such an extent that any alternative appears simply illegal.
It seems reasonable to assume that if, to pick an arbitrary choice,
string_to_array returned a zero element set people would say it was
simply illegal for it to do anything else. There are choices for
either and a choice needs to be made or the situation should somehow be
made impossible.
I would argue against a change to have string_to_array('',',') throw an
error.
I'd agree, throwing an exception here doesn't seem useful.
--
Sam http://samason.me.uk/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] string_to_array with empty input
On Thu, Apr 2, 2009 at 12:17 PM, David E. Wheeler da...@kineticode.com wrote:
On Apr 1, 2009, at 2:22 PM, Tom Lane wrote:
Another way to state the point is that we can offer people a choice of
two limitations: string_to_array doesn't work for zero-length lists,
or string_to_array doesn't work for empty strings (except most of the
time, it does). The former is sounding less likely to bite people
unexpectedly.
Right, very well put.
Or we could stick to the current behavior and say use COALESCE() to
resolve the ambiguity, if you need to.
Steve has a point that leaving it as-is leaves it as impossible to tell the
difference between string_to_array(NULL, ',') and string_to_array('', ',').
The former properly handles an unknown value, while the latter, where '' is
a known value, seems weird to be returning NULL.
*shrug* CASE WHEN blah IS NOT NULL THEN string_to_array(blah, ',') END
More and more I'm leaning toward leaving this alone. No matter how
you define it, the behavior can be changed to whichever alternative
you prefer with a 1-line case statement.
...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: [HACKERS] [GENERAL] string_to_array with empty input
On Thu, Apr 2, 2009 at 12:10 PM, David E. Wheeler da...@kineticode.com wrote:
On Apr 1, 2009, at 12:19 PM, Robert Haas wrote:
my @ints = map { $_ || 0 } split ',', $string;
This ensures that I get the proper number of records in the example of
something like '1,2,,4'.
I can't see that there's any way to do this in SQL regardless of how
we define this operation.
It's easy enough to write a function to do it:
CREATE OR REPLACE FUNCTION trim_blanks (anyarray) RETURNS anyarray AS $$
SELECT ARRAY(
SELECT CASE WHEN $1[i] IS NULL OR $1[i] = '' THEN '0' ELSE $1[i] END
FROM generate_series(1, array_upper($1, 1)) s(i)
ORDER BY i
);
$$ LANGUAGE SQL IMMUTABLE;
Ah! Thanks for the tip.
...Robert
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [GENERAL] Re: [GENERAL] ERROR: XX001: could not read block 2354 of relation…
On Thu, Apr 2, 2009 at 11:34 AM, Patrick Desjardins mrdesjard...@gmail.com wrote: Humm, they want to close the AntiVirus for 1 night not anymore. Do you think that if we take out the database directory of the scan that it will solve the problem or it really need to have no antivirus on the server? They really want to keep it... Yes, telling it to ignore SHOULD make everything ok. As previously mentioned, 1: some anti-virus products do not behave properly when told to ignore things. 2: database servers should be isolated in such a way that active virus scanning should be unnecessary. 3: your IT department needs to practice good change approval. This means they need to test this on a non-production server first to make sure their idea is a good one. Obviously this was not done before applying anti-virus software so they have one strike against them already. If they won't turn it off and test it on a test server first, you should ask your manager to hire a professional to replace whoever didn't test this first. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] string_to_array with empty input
David E. Wheeler da...@kineticode.com writes:
Or we could stick to the current behavior and say use COALESCE() to
resolve the ambiguity, if you need to.
Steve has a point that leaving it as-is leaves it as impossible to
tell the difference between string_to_array(NULL, ',') and
string_to_array('', ','). The former properly handles an unknown
value, while the latter, where '' is a known value, seems weird to be
returning NULL.
Yeah, COALESCE is an abuse of a convenient notation, which will fall
over if you also want NULL to yield NULL. A correct fix
outside-the-function would look more like
case when str = '' then '{}'::text[] else string_to_array(str, ',') end
which should correctly yield NULL for NULL input and an empty array
for empty input. Similarly, if someone wanted to force the
single-empty-string result, they should do
case when str = '' then '{}'::text[] else string_to_array(str, ',') end
which also still yields NULL if str is NULL.
Right at the moment, if we stick with the historical definition
of the function, *both* camps have to write out their choice of
the above. Seems like this is the worst of all possible worlds.
We should probably pick one or the other.
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: [HACKERS] [GENERAL] string_to_array with empty input
On Thu, Apr 2, 2009 at 2:04 PM, Tom Lane t...@sss.pgh.pa.us wrote: Right at the moment, if we stick with the historical definition of the function, *both* camps have to write out their choice of the above. Seems like this is the worst of all possible worlds. We should probably pick one or the other. ISTM there are three camps. ...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: [HACKERS] [GENERAL] string_to_array with empty input
Robert Haas robertmh...@gmail.com writes: On Thu, Apr 2, 2009 at 2:04 PM, Tom Lane t...@sss.pgh.pa.us wrote: Right at the moment, if we stick with the historical definition of the function, *both* camps have to write out their choice of the above. Seems like this is the worst of all possible worlds. We should probably pick one or the other. ISTM there are three camps. If there's a camp that actually *wants* a NULL result for this case, I missed the reasoning. AFAICS we can either say that every application is going to have to put in a CASE wrapper around this function, or say that we'll make it do the right thing for some of them and the rest have to put the same wrapper around it. 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: [HACKERS] [GENERAL] string_to_array with empty input
On Thu, Apr 02, 2009 at 02:04:41PM -0400, Tom Lane wrote:
A correct fix
outside-the-function would look more like
case when str = '' then '{}'::text[] else string_to_array(str, ',') end
which should correctly yield NULL for NULL input and an empty array
for empty input. Similarly, if someone wanted to force the
single-empty-string result, they should do
case when str = '' then '{}'::text[] else string_to_array(str, ',') end
which also still yields NULL if str is NULL.
Right at the moment, if we stick with the historical definition
of the function, *both* camps have to write out their choice of
the above. Seems like this is the worst of all possible worlds.
We should probably pick one or the other.
Yes, I'd be tempted to pick one and go with it. It's seems a completely
arbitrary choice one way or the other but the current behaviour is
certainly wrong.
I'd go with returning a zero element array because it would do
the right thing more often when paired with array_to_string.
I've also been through the first few pages of a Google search for
array_to_string and it seems to do the right thing for the majority
of the cases.
--
Sam http://samason.me.uk/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] string_to_array with empty input
On Thu, Apr 2, 2009 at 2:18 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Apr 2, 2009 at 2:04 PM, Tom Lane t...@sss.pgh.pa.us wrote: Right at the moment, if we stick with the historical definition of the function, *both* camps have to write out their choice of the above. Seems like this is the worst of all possible worlds. We should probably pick one or the other. ISTM there are three camps. If there's a camp that actually *wants* a NULL result for this case, I missed the reasoning. AFAICS we can either say that every application is going to have to put in a CASE wrapper around this function, or say that we'll make it do the right thing for some of them and the rest have to put the same wrapper around it. So that we don't break existing apps because of an issue that is trivial to work around. ...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: [HACKERS] [GENERAL] string_to_array with empty input
Robert Haas robertmh...@gmail.com writes: On Thu, Apr 2, 2009 at 2:18 PM, Tom Lane t...@sss.pgh.pa.us wrote: If there's a camp that actually *wants* a NULL result for this case, I missed the reasoning. So that we don't break existing apps because of an issue that is trivial to work around. We would only be breaking them if a NULL result were actually the correct behavior for the application's requirements, which seems a bit unlikely. 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] Postgresql installation with ssh connection.
dear Sirs, is there on the web a simple guide (for idiots) to install postgresql on CentOS 5.2 using only a ssh connection? (no web browser, no graphical capability). My first problem is to download using ftp instead a web browser. The default installation (#yum install opstresql)suggest the version 8.1 but I would like to install the latest 8.3, so I suppose that I have to change some file containing yum directive per postgresql. Actually postgresql is no installed, so I don't have to unistall and/or backup. Thank you for your suggestions. D. Formenton -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] string_to_array with empty input
On Apr 2, 2009, at 11:24 AM, Sam Mason wrote:
Yes, I'd be tempted to pick one and go with it. It's seems a
completely
arbitrary choice one way or the other but the current behaviour is
certainly wrong.
I'd go with returning a zero element array because it would do
the right thing more often when paired with array_to_string.
I've also been through the first few pages of a Google search for
array_to_string and it seems to do the right thing for the
majority
of the cases.
Forgive me if I'm missing something, but it seems to me that
array_to_string() works either way, no?
try=# select '' || array_to_string('{}'::text[], ',') || ''; ?column?
--
(1 row)
Time: 72.129 ms
try=# select '' || array_to_string('{}'::text[], ',') || '';
?column?
--
(1 row)
Best,
David
--
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] Postgresql installation with ssh connection.
2009/4/3 dfx d...@dfx.it: dear Sirs, The default installation (#yum install opstresql)suggest the version 8.1 but I would like to install the latest 8.3, so I suppose that I have to change some file containing yum directive per postgresql. 30 seconds on the postgres website ... ran into this ;} http://yum.pgsqlrpms.org/ Cheers, Andrej -- 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] Postgresql installation with ssh connection.
dfx wrote: dear Sirs, is there on the web a simple guide (for idiots) to install postgresql on CentOS 5.2 using only a ssh connection? (no web browser, no graphical capability). My first problem is to download using ftp instead a web browser. Try something like yum install lynx first - lynx is a text-based browser. The default installation (#yum install opstresql)suggest the version 8.1 but I would like to install the latest 8.3, so I suppose that I have to change some file containing yum directive per postgresql. Notes regarding RPMs here (there must be a more up-to-date one, this is the first I found). http://archives.postgresql.org/pgsql-announce/2008-06/msg00012.php You'll probably want to add a new repository to yum (or something like that - Debian my myself I'm afraid). -- 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: [GENERAL] reducing IO and memory usage: sending the content of a table to multiple files
On Thu, 2 Apr 2009 17:27:55 +0100
Sam Mason s...@samason.me.uk wrote:
On Thu, Apr 02, 2009 at 11:20:02AM +0200, Ivan Sergio Borgonovo
wrote:
This is the work-flow I've in mind:
1a) take out *all* data from a table in chunks (M record for each
file, one big file?) (\copy??, from inside a scripting language?)
What about using cursors here?
The only way I've seen using cursors with php involve defining a
function... It looks a bit messy for a 10 lines script having to
define a function just as a shell for a sql statement.
I'd even write it in python if the project didn't start to look as a
small Frankenstein... and sooner or later I bet I'll have to include
some php files to recycle some function.
I didn't find any elegant example of cursor use in PHP... OK PHP is
not the most elegant language around... but still any good exapmle
someone could point me at?
If you could point me to some clean way to use cursors in php I'd
happy to learn.
I was thinking about using another embedded language that better
suits data processing (an unsafe version?) so I could directly
output to files from within a postgresql function...
2a) process each file with awk to produce N files very similar
each other (substantially turn them into very simple xml)
3a) gzip them
GZIP uses significant CPU time; there are various lighter weight
schemes available that may be better depending on where this data
is going.
That's a requirement.
2b) use any scripting language to process and gzip them avoiding
a bit of disk IO
What disk IO are you trying to save and why?
Because this is going to be the largest write operation the all
system will have to handle during the day.
I'm not interested in fast complicated queries, planning,
transactions, caching... I just need to get a whole table pass it
through a filter and output several filtered versions of the same
table.
So I think the largest cost of the operation will be IO.
\copy should be optimised for raw data output, but maybe all its
advantages get lost once I've to use pipes and adding complexity to
filtering.
Does PostgreSQL offer me any contrib, module, technique... to
save some IO (and maybe disk space for temporary results?).
Are there any memory usage implication if I'm doing a:
pg_query(select a,b,c from verylargetable; --no where clause);
vs.
the \copy equivalent
any way to avoid them?
As far as I understand it will get all the data from the database
into memory first and then your code gets a chance. For large
datasets this obviously doesn't work well. CURSORs are you friend
here.
I was reading about all the php documents and trying to understand
how buffers and memory usage works, so I gave a look to MySQL
documents too...
MySQL has mysql_unbuffered_query.
So I was wondering how memory is managed on the server and on
clients.
What's going to happen when I do a
$result=pg_query(select * from t1;);
while($row=pg_fetch_array($result)) {
}
vs.
using cursors...
vs.
asynchronous query (they just look as non stopping queries with no
relationship with memory usage)
Where are the buffers etc...
thanks
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] string_to_array with empty input
On Thu, Apr 2, 2009 at 2:50 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Apr 2, 2009 at 2:18 PM, Tom Lane t...@sss.pgh.pa.us wrote: If there's a camp that actually *wants* a NULL result for this case, I missed the reasoning. So that we don't break existing apps because of an issue that is trivial to work around. We would only be breaking them if a NULL result were actually the correct behavior for the application's requirements, which seems a bit unlikely. But that's completely untrue. If the most useful behavior is either ARRAY[''] or ARRAY[], then there are presumably lots and lots of people out there who have apps that do COALESCE(string_to_array(...), something). Whichever way you change string_to_array() will break all of the people doing this who wanted the opposite behavior for no good reason. ...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] Postgresql installation with ssh connection.
On Thu, Apr 2, 2009 at 12:51 PM, dfx d...@dfx.it wrote: dear Sirs, is there on the web a simple guide (for idiots) to install postgresql on CentOS 5.2 using only a ssh connection? (no web browser, no graphical capability). My first problem is to download using ftp instead a web browser. The default installation (#yum install opstresql)suggest the version 8.1 but I would like to install the latest 8.3, so I suppose that I have to change some file containing yum directive per postgresql. Actually postgresql is no installed, so I don't have to unistall and/or backup. If you want to use the pgsql version that's included with RHEL 5.2 you can just use yum: yum list | grep -i postgres to see a list of packages. sudo yum install postgresql* to install everything. If you want to run the latest and greatest, then you can dl the files via wget and / or lynx. Using a web browser, and wandering about ftp://ftp.postgresql.org you'll find this directory: ftp://ftp.postgresql.org/pub/binary/v8.3.7/linux/rpms/redhat/rhel-5-x86_64/ right click on the packages and copy the link into your buffer, then in your ssh terminal, type in: wget ftp://ftp.postgresql.org/pub/binary/v8.3.7/linux/rpms/redhat/rhel-5-x86_64/ftp://ftp.postgresql.org/pub/binary/v8.3.7/linux/rpms/redhat/rhel-5-x86_64/postgresql-server-8.3.7-1PGDG.rhel5.x86_64.rpm and wait for it to finish. If it gets stopped halfway through, use wget -c ftp:// (rest of url here) to start where you left off. Or just use ftp. Or lynx. Then when you've got them all in a directory ready to install, do: sudo rpm --install *.rpm in that directory. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Thank you
Thank you very much to all people!!! I reached the goal using lynx to dowload the rpms and then the An Almost Idiots's Guide To PostgreSQL YUM from Postgres OnLine Journal Domenico -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Hello, i want to subscribe...
Hello, i want to subscribe to this lists
[GENERAL] slow select in big table
Hello i have big table 80mln records, ~6GB data, 2columns (int, int) if query select count(col1) from tab where col2=1234; return low records (1-10) time is good 30-40ms but when records is 1000 time is 12s How to increse performace ? my postgresql.conf shared_buffers = 810MB temp_buffers = 128MB work_mem = 512MB maintenance_work_mem = 256MB max_stack_depth = 7MB effective_cache_size = 800MB db 8.3.7 server, atlon dual-core 2,0Ghz, 2GB RAM, SATA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [GENERAL] Re: [GENERAL] ERROR: XX001: could not read block 2354 of relation…
Humm, they want to close the AntiVirus for 1 night not anymore. Do you think that if we take out the database directory of the scan that it will solve the problem or it really need to have no antivirus on the server? They really want to keep it... On Wed, Apr 1, 2009 at 1:06 PM, Scott Marlowe scott.marl...@gmail.comwrote: On Wed, Apr 1, 2009 at 10:32 AM, Patrick Desjardins mrdesjard...@gmail.com wrote: I have not reindexes. I will try to use the Reindex command (http://www.postgresql.org/docs/8.3/interactive/sql-reindex.html). If I get error I will try to drop them. If it doesn't solve I will pg_dump. I still need to wait the IT to remove the Anti-virus. Will give you more news later. You may be tilting at windmills until they do. I'd go stand behind somebody in IT until they came and fixed it. Seriously.
[GENERAL] indirect membership in group roles
Suppose I have some group roles, say student and employee, to
which I want to grant another group role, user. I then want to give
privileges to an updateable view my_preferences to user so that
they'll be given to any login role that's a member of student or
employee.
Seems sensible, right? That way some login role could even be both
student and employee and I need not add user to that because
it's taken care of automatically, nor do I have to worry about whether
to take away user if the login role later ceases to be student but
remains employee, and again later when the login role ceases to be
employee.
For some reason, which I couldn't see spelled out very well in the
docs for GRANT ROLE and SET ROLE, indirect membership in the group
user doesn't give one its privileges unless you SET ROLE user
first, even if all roles involved have INHERIT set. The difference is
seen in pg_has_role('user','member') vs pg_has_role('user','usage').
I don't understand the rationale for this limitation. It seems to
make inheritance much less useful, because then (very frequently used)
SELECT, UPDATE, etc. statements have the extra (programming and
execution) overhead of at least one SET ROLE statement, and worse,
probably have to SELECT pg_has_role() first or be ready to do some
error handling. All because the membership is indirect. Could
someone explain the reasoning to me?
Thanks,
Kev
--
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] indirect membership in group roles
Kev kevinjamesfi...@gmail.com writes: For some reason, which I couldn't see spelled out very well in the docs for GRANT ROLE and SET ROLE, indirect membership in the group user doesn't give one its privileges unless you SET ROLE user first, even if all roles involved have INHERIT set. Really? Works for me: regression=# create group student inherit; CREATE ROLE regression=# create group employee inherit; CREATE ROLE regression=# create group user; CREATE ROLE regression=# grant user to student; GRANT ROLE regression=# grant user to employee; GRANT ROLE regression=# create user joe inherit; CREATE ROLE regression=# grant student to joe; GRANT ROLE regression=# create table mytable (f1 int); CREATE TABLE regression=# grant select on mytable to user; GRANT regression=# \c - joe psql (8.4devel) You are now connected to database regression as user joe. regression= select * from mytable; f1 (0 rows) I suspect you forgot to attach the inherit property to the intermediate-level group. 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: [HACKERS] [GENERAL] string_to_array with empty input
On Thu, Apr 02, 2009 at 12:06:01PM -0700, David E. Wheeler wrote: On Apr 2, 2009, at 11:24 AM, Sam Mason wrote: Yes, I'd be tempted to pick one and go with it. It's seems a completely arbitrary choice one way or the other but the current behaviour is certainly wrong. I'd go with returning a zero element array because it would do the right thing more often when paired with array_to_string. I've also been through the first few pages of a Google search for array_to_string and it seems to do the right thing for the majority of the cases. Forgive me if I'm missing something, but it seems to me that array_to_string() works either way, no? Sorry, I meant to type string_to_array but typed array_to_string instead---after doing exactly the same thing when searching for stuff in Google! I think I should be using copy/paste more! -- Sam http://samason.me.uk/ -- 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] reducing IO and memory usage: sending the content of a table to multiple files
On Thu, Apr 02, 2009 at 09:48:33PM +0200, Ivan Sergio Borgonovo wrote:
I didn't find any elegant example of cursor use in PHP... OK PHP is
not the most elegant language around... but still any good exapmle
someone could point me at?
I don't program PHP; but my guess would be something like:
pg_query(BEGIN;);
pg_query(DECLARE cur CURSOR FOR SELECT * FROM t1;);
while (pg_num_rows($result = pg_query(FETCH 1000 FROM cur;)) 0) {
while($row = pg_fetch_array($result)) {
}
}
pg_query(COMMIT;);
You can obviously increase the FETCH upwards and if you're feeling
fancy you could even run the FETCH async from the code that processes
the results. Maybe something like:
pg_query($conn, BEGIN;);
pg_query($conn, DECLARE cur CURSOR FOR SELECT * FROM t1;);
pg_send_query($conn, FETCH 1000 FROM cur;);
while(1) {
$result = pg_get_result($conn);
pg_send_query($conn, FETCH 1000 FROM cur;);
if (pg_num_rows($result) == 0)
break;
while($row = pg_fetch_array($conn, $result)) {
}
if (pg_get_result($conn)) {
// badness, only expecting a single result
}
}
Note, I've never tried to do PG database stuff from PHP, let alone stuff
like this so it may be all wrong! AFAICT, there's no need to bother
with pg_connection_busy because the call to pg_get_result will block
until the results come back from the database.
So I think the largest cost of the operation will be IO.
\copy should be optimised for raw data output, but maybe all its
advantages get lost once I've to use pipes and adding complexity to
filtering.
Streaming IO is pretty fast, I think you'll be hard pushed to keep up
with it from PHP and you'll end up CPU bound in no time. Be interesting
to find out though.
I was reading about all the php documents and trying to understand
how buffers and memory usage works, so I gave a look to MySQL
documents too...
Not sure about PG, but the C api pretty much always buffers everything
in memory first. There was mention of getting control of this, but I've
got no idea where it got.
--
Sam http://samason.me.uk/
--
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 select in big table
On Fri, Apr 3, 2009 at 2:18 AM, rafalak rafa...@gmail.com wrote: Hello i have big table 80mln records, ~6GB data, 2columns (int, int) if query select count(col1) from tab where col2=1234; return low records (1-10) time is good 30-40ms but when records is 1000 time is 12s How to increse performace ? my postgresql.conf shared_buffers = 810MB temp_buffers = 128MB work_mem = 512MB maintenance_work_mem = 256MB max_stack_depth = 7MB effective_cache_size = 800MB db 8.3.7 server, atlon dual-core 2,0Ghz, 2GB RAM, SATA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Is the table has indexes? Decreasing the work_mem also increase performance. Monitor these changes by explain the query plan. Regards, Abbas.
[GENERAL] Re: [GENERAL] Re: [GENERAL] R e: [GENERAL] ERROR: XX001: could not read block 2354 of relation...
Patrick Desjardins wrote: Humm, they want to close the AntiVirus for 1 night not anymore. Do you think that if we take out the database directory of the scan that it will solve the problem or it really need to have no antivirus on the server? They really want to keep it... It depends on the AV product. If you exclude all PostgreSQL processes from monitoring (the postmaster, postgres.exe, etc) and you exclude the postgresql data directory from scans and realtime protection, then some AV programs may successfully avoid interfering with Pg. It depends on if the AV software is half-decently written. All you can really do is test it and see. Hope you didn't need that data ... Personally, I don't think there's any place for AV software on a database server. It should not be necessary and it's a needless performance/reliability hit. -- Craig Ringer -- 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 select in big table
On Thu, Apr 2, 2009 at 2:48 PM, rafalak rafa...@gmail.com wrote: Hello i have big table 80mln records, ~6GB data, 2columns (int, int) if query select count(col1) from tab where col2=1234; return low records (1-10) time is good 30-40ms but when records is 1000 time is 12s How to increse performace ? my postgresql.conf shared_buffers = 810MB temp_buffers = 128MB work_mem = 512MB maintenance_work_mem = 256MB max_stack_depth = 7MB effective_cache_size = 800MB Try lowering random_page_cost close to the setting of seq_page_cost (i.e. just over 1 on a default seq_page_cost) and see if that helps. -- 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] reducing IO and memory usage: sending the content of a table to multiple files
On Thu, Apr 2, 2009 at 7:05 PM, Sam Mason s...@samason.me.uk wrote:
On Thu, Apr 02, 2009 at 09:48:33PM +0200, Ivan Sergio Borgonovo wrote:
I didn't find any elegant example of cursor use in PHP... OK PHP is
not the most elegant language around... but still any good exapmle
someone could point me at?
I don't program PHP; but my guess would be something like:
pg_query(BEGIN;);
pg_query(DECLARE cur CURSOR FOR SELECT * FROM t1;);
while (pg_num_rows($result = pg_query(FETCH 1000 FROM cur;)) 0) {
while($row = pg_fetch_array($result)) {
}
}
pg_query(COMMIT;);
I've done something similar and it worked just fine.
--
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] Hello, i want to subscribe...
On Fri, Apr 3, 2009 at 2:35 AM, Ricardo Fuentes ricardo...@gmail.comwrote: Hello, i want to subscribe to this lists Hi Ricardo, Here is the link to subscribe. http://www.postgresql.org/community/lists/ Regards, Itishree
[GENERAL] How to capture an interactive psql session in a log file?
What I'm trying to do doesn't seem like it should be that difficult or unusual, but I can't seem to find the right combination of commands to make it happen. I want to have a log file that captures everything from an interactive psql session. Running 8.3.7 with bash shell on Linux. If I use \o file or \o |tee file, it doesn't capture my entered commands, or any error text. I can use some fancy bash to capture stderr in the file too, and then error text goes there, but no matter what, neither the commands I enter nor the psql prompt will be captured in the log file. This is even when using -a, or \set ECHO all. It's as if my entered commands neither go to stdout or stderr. Has anyone solved this issue before? Thanks in advance Gordon -- View this message in context: http://www.nabble.com/How-to-capture-an-interactive-psql-session-in-a-log-file--tp22862412p22862412.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] How to capture an interactive psql session in a log file?
On Thu, Apr 02, 2009 at 10:55:10PM -0700, Gordon Shannon wrote: Has anyone solved this issue before? have you seen program script? usage: just run script you will get shell. now run your command you want to capture everything from - it will work as usual. after you finish - exit the script-shell, and check the generated typescript file Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
