[GENERAL] Deploying PostgreSQL on virtualized hardware

In a couple months, I'm going to be considering how best to deploy an application I have that uses PostgreSQL as its back-end. It also makes heavy use of Perl under mod_perl and UMN MapServer with a sprinkling of PostGIS. I've recently become intrigued by the idea of virtualized servers [0],

Re: [GENERAL] copy with escape

blackwater dev wrote: I have data that I'm running through pg_escape_sting in php and then adding to stdin for a copy command. The problem is "O'reilly" is being changed to "O''Reilly" in the string and then in the db. I saw with the copy command I can specify the escape but it isn't working

Re: [GENERAL] copy with escape

"blackwater dev" <[EMAIL PROTECTED]> writes: > I have data that I'm running through pg_escape_sting in php and then adding > to stdin for a copy command. The problem is "O'reilly" is being changed to > "O''Reilly" in the string and then in the db. pg_escape_string is designed to produce a string

[GENERAL] copy with escape

I have data that I'm running through pg_escape_sting in php and then adding to stdin for a copy command. The problem is "O'reilly" is being changed to "O''Reilly" in the string and then in the db. I saw with the copy command I can specify the escape but it isn't working for me. Should this comma

Re: [GENERAL] Windows 8.3 installer leaves a debug flag on?

"Dan Armbrust" <[EMAIL PROTECTED]> writes: > To follow up on my old thread - > I tested another install of Postgres 8.3.0.1 - and on windows, the > postgresql.conf file has this in it: > shared_preload_libraries = '$libdir/plugins/plugin_debugger.dll' > # > (change requires restart)

[GENERAL] how to auto GRANT custom ACL on a new table?

Hello, I have asked the question on novice ML but it seems there is not much traffic there. I hope nobody minds I'm re-asking here, on a much more active list. The problem is as follows: There are 2 role groups in my cluster: "editors" and "viewers". In each group there are several users. In

Re: [GENERAL] "RETURNS SETOF" function question

On Sun, Feb 24, 2008 at 7:08 PM, Erik Jones <[EMAIL PROTECTED]> wrote: > > There was an article that covered this in the Postgres Online Journal > ( > http://www.postgresonline.com/journal/index.php?/categories/6-pl-programming). > Basically, do this: > > CREATE OR REPLACE FUNCTION foo(text, text

Re: [GENERAL] Use index for upper(customername) like 'ABC%'

> CREATE INDEX ie_cust_upper_name ON customer ( UPPER( customername ) ) > WHERE ( UPPER(customername) LIKE 'ABC%' ); ABC is different at every time. This is first letters of customer name entered by user. So I cannot add ABC to index. Andrus. ---(end of broadcast)-

[GENERAL] disabling triggers, constaints and so on

We are still in a pickle with trying to resolve our trigger issues without affecting slony triggers. The point is, we need to be able to disable triggers, check constraints, and foreign-key constraints without affecting slony triggers in certain situations. This is all running on 7.4.19, thu

[GENERAL] Windows 8.3 installer leaves a debug flag on?

To follow up on my old thread - I tested another install of Postgres 8.3.0.1 - and on windows, the postgresql.conf file has this in it: shared_preload_libraries = '$libdir/plugins/plugin_debugger.dll' # (change requires restart) I got the impression that that debugger wasn't inte

[GENERAL] give a hand to a user having trouble with MySQL?

Came across a blog from Ovid, one of the Perl gurus at BBC. http://use.perl.org/~Ovid/journal/35752?from=rss Quote "They're not kidding, either. Subquery optimization in MySQL is terribly broken, as we've discove

Re: [GENERAL] text and bytea

"hernan gonzalez" <[EMAIL PROTECTED]> writes: > test=# create view vchartest as > select encode(convert_to(c,'LATIN9'),'escape') as c1 from chartest; Hmm. This isn't a very sensible combination that you've written here, but I see the point: encode(..., 'escape') is broken in that it fails to con

Re: [GENERAL] text and bytea

"hernan gonzalez" <[EMAIL PROTECTED]> writes: > The objetionable ones IMHO are decode()/encode(), which can > consume/produce a "non-utf8 string" (I mean, not the backend encoding) Huh? Those deal with bytea too --- in fact, they've got nothing at all to do with multibyte character representation

Re: [GENERAL] autovacuum not freeing up unused space on 8.3.0

Stuart Brooks wrote: > ERROR: canceling autovacuum task > CONTEXT: automatic vacuum of table "metadb.test.transactions" Are these happening regularly? They indicate that something is happening on the table that collides with what autovacuum needs to do, and autovacuum defers its task. For thi

Re: [GENERAL] APEX / HTML DB for PostgreSQL

Il giorno 24/feb/08, alle ore 04:53, justin tocci ha scritto: In its most basic form a great tool would just start as a pl/pgsql or pl/perl function that could be used to call a table and have it output a batch of records to an editable html form or a colored pdf for nice reports. There wo

Re: [GENERAL] text and bytea

Another example (Psotgresql 8.3.0, UTF-8 server/client encoding) test=# create table chartest ( c text); test=# insert into chartest (c) values ('¡Hasta mañana!'); test=# create view vchartest as select encode(convert_to(c,'LATIN9'),'escape') as c1 from chartest; test=# select c,octet_length(c

Re: [GENERAL] text and bytea

> IMHO, the semantics of encode() and decode() are correct (the bridge > between bytea and text ... in the backend encoding; they should be the > only bridge), convert() is also ok (deals with bytes), but > convert_to() and convert_from() are dubious if not broken: they imply > texts in arbitrary e

Re: [GENERAL] process pool

"Luca Ferrari" <[EMAIL PROTECTED]> writes: > Hi, > sorry for this question, but as far as I know postgresql does not use a > process pool, rather a new process is created for any connection on demand. > If this is true, what is the reason for this? Generally to add code the question is not wh

[GENERAL] Cascading Trigger - changing row on delete does not delete row

All, I have 2 tables which both have triggers on them. When I delete a row on table A, a cascading trigger ends up modifying rows in table B. The modified rows in table B trigger an update on rows in table A which happens to be the same row that I am trying to delete. I don't get any error

[GENERAL] process pool

Hi, sorry for this question, but as far as I know postgresql does not use a process pool, rather a new process is created for any connection on demand. If this is true, what is the reason for this? Thanks, Luca ---(end of broadcast)--- TIP 3: Hav

Re: [GENERAL] HOW does 8.3 CREATE REPLACe .. FUNCTION ..COST work

"Obe, Regina" <[EMAIL PROTECTED]> writes: > It would be really nice if this worked with OR as well. Is it just much > harder to deal with the > OR case in the planner or was there some other reason why the OR case > was left out? Nobody's really made a case why we should have the planner expend

Re: [GENERAL] HOW does 8.3 CREATE REPLACe .. FUNCTION ..COST work

> > --Test 2: This works as I would expect - shows that none of the > > functions are run presumably its going straight for 5 > 2 > > --becuase it recognizes its the cheapest route > > TRUNCATE TABLE log_call; > > SELECT foo.value > > FROM (SELECT (fn_pg_costlyfunction() > 2 OR fn_pg_cheapfunctio

Re: [GENERAL] HOW does 8.3 CREATE REPLACe .. FUNCTION ..COST work

"Obe, Regina" <[EMAIL PROTECTED]> writes: > --Test 1: This shows that fn_pg_costlyfunction() is the only function > that is run - > -- unexpected to me shouldn't no function be evaluated or the cheap one? > --What's the difference between Test 1 and Test 2 that makes Test 2 do > the RIGHT thing? >

Re: [GENERAL] Planner: rows=1 after "similar to" where condition.

Resent due to bounce... orange.nl #5.0.0 X-SMTP-Server; host sss.pgh.pa.us[66.207.139.130] said: 550 >-Original Message- >From: Tom Lane [mailto:[EMAIL PROTECTED] >Sent: Monday, 25 February 2008 16:34 >To: Joris Dobbelsteen >Cc: Gregory Stark; Scott Marlowe; pgsql-general@postgresql.org

Re: [GENERAL] Planner: rows=1 after "similar to" where condition.

>-Original Message- >From: Tom Lane [mailto:[EMAIL PROTECTED] >Sent: Monday, 25 February 2008 16:34 >To: Joris Dobbelsteen >Cc: Gregory Stark; Scott Marlowe; pgsql-general@postgresql.org >Subject: Re: [GENERAL] Planner: rows=1 after "similar to" >where condition. > >"Joris Dobbelsteen" <

Re: [GENERAL] Planner: rows=1 after "similar to" where condition.

"Joris Dobbelsteen" <[EMAIL PROTECTED]> writes: > "Bitmap Heap Scan on log_syslog syslog (cost=11168.32..16988.84 rows=1 > width=221) (actual time=11145.729..30067.606 rows=212 loops=1)" > " Recheck Cond: (((program)::text = 'amavis'::text) AND > ((facility)::text = 'mail'::text))" > " Filter: (

Re: [GENERAL] text and bytea

"hernan gonzalez" <[EMAIL PROTECTED]> writes: > IMHO, the semantics of encode() and decode() are correct (the bridge > between bytea and text ... in the backend encoding; they should be the > only bridge), convert() is also ok (deals with bytes), but > convert_to() and convert_from() are dubious i

[GENERAL] HOW does 8.3 CREATE REPLACe .. FUNCTION ..COST work

I think I am missing something about how the new CREATE OR REPLACE FUNCTION ...COST works or I am missing some setting in postgresql conf. I was hoping I could use it to control the function that is used in cases where only one needs to be evaluated. Regardless of what I do it seems to always ev

Re: [GENERAL] text and bytea

> Umm, I think all you showed was that the to_ascii() function was > broken. Postgres knows exactly what encoding the string is in, the > backend encoding: in your case UTF-8. That would be fine, if it were true; then, one could assume that every postgresql function that returns a text gets ALW

Re: [GENERAL] Planner: rows=1 after "similar to" where condition.

>-Original Message- >From: Gregory Stark [mailto:[EMAIL PROTECTED] >Sent: Monday, 25 February 2008 12:31 >To: Joris Dobbelsteen >Cc: Scott Marlowe; pgsql-general@postgresql.org >Subject: Re: Planner: rows=1 after "similar to" where condition. > >"Joris Dobbelsteen" <[EMAIL PROTECTED]> writ

Re: [GENERAL] Planner: rows=1 after "similar to" where condition.

"Joris Dobbelsteen" <[EMAIL PROTECTED]> writes: > Should be: > PostGreSQL 8.2.5 on x86_64-pc-linux-gnu (GCC 4.1.2.20061115) (Debian > 4.1.1-21). In this case that may matter. One of the changes in the 8.2.6 update was: . Improve planner's handling of LIKE/regex estimation in non-C locales (Tom)

Re: [GENERAL] Planner: rows=1 after "similar to" where condition.

>-Original Message- >From: Scott Marlowe [mailto:[EMAIL PROTECTED] >Sent: Monday, 25 February 2008 7:14 >To: Joris Dobbelsteen >Cc: pgsql-general@postgresql.org >Subject: Re: [GENERAL] Planner: rows=1 after "similar to" >where condition. > >On Sun, Feb 24, 2008 at 4:35 PM, Joris Dobbelste

Re: [GENERAL] request help forming query

yes, this could get called on quite large tables (maybe not billions ...). The second solution looks useful - I'll try it on some test data. thanks both of you. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster