Re: [GENERAL] Text search parser's treatment of URLs and emails

[Thom Brown]

On 8 September 2010 21:48, Thom Brown  wrote:
> Hi,
>
> I noticed that if I run this:
>
> SELECT alias, description, token FROM
> ts_debug('http://www.postgresql.org:2345/directory/page.html?version=9.1&build=alpha1#summary');
>
> I get:
>
>  alias   |  description  |                              token
> --+---+-
>  protocol | Protocol head | http://
>  url      | URL           |
> www.postgresql.org:2345/directory/page.html?version=9.1&build=alpha1#summary
>  host     | Host          | www.postgresql.org:2345
>  url_path | URL path      |
> /directory/page.html?version=9.1&build=alpha1#summary
> (4 rows)
>
>
> It could be me being picky, but I don't regard parameters or page
> fragments as part of the URL path.  Ideally, I'd sort of expect:
>
>    alias     |  description  |                              token
> --+---+-
>  protocol     | Protocol head | http://
>  url          | URL           |
> www.postgresql.org:2345/directory/page.html?version=9.1&build=alpha1#summary
>  host         | Host          | www.postgresql.org
>  port         | Port          | 2345
>  url_path     | URL path      | /directory/page.html
>  query_string | Query string  | version=9.1&build=alpha1
>  fragment     | Page fragment | summary
> (7 rows)
>
> ... of course that's if there was support for query strings and page
> fragments, which there isn't.  But if changes were made to support my
> definition of a URL path, they'd have to be considered breaking
> changes.
>
> But my main gripe is with the name "url_path".
>
> Also:
>
> SELECT alias, description, token FROM ts_debug('myname+prior...@gmail.com');
>
> Yields:
>
>   alias   |   description   |       token
> ---+-+
>  asciiword | Word, all ASCII | myname
>  blank     | Space symbols   | +
>  email     | Email address   | prior...@gmail.com
> (3 rows)
>
> The entire string I entered is a valid email address, and isn't
> totally uncommon.  Shouldn't that take such email address styles be
> taken into account?  The example above incorrectly identifies the
> email address since the real destination address would most likely be
> myn...@gmail.com.

No opinions?

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

-- 
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 9 Mac OS X one-click install - PL/perl broken

[Dave Page]

On Tue, Sep 28, 2010 at 7:46 PM, Scott Ribe  wrote:
> On Sep 28, 2010, at 11:50 AM, Dave Page wrote:
>
>> You're welcome. I guess it is running the 64bit image - is your
>> machine Leopard Server?
>
> That's irrelevant. The 32-bit vs 64-bit default is for the kernel and 
> extensions, not for applications. On 64-bit hardware, apps can be run as 
> 64-bit, and will be if there's a 64-bit executable, regardless of which mode 
> the kernel is booted into.

Thanks for the clarification. I thought it was only Snow Leopard/Snow
Leopard Server and Leopard Server that could run the 64 bit images.


-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

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

[GENERAL] receive and transmit streaming replication at same time

[post]

Hello List,

a lot of recherche and no answer so far. So my question to you:

Is it possible to set up ONE PostgreSQL 9 to serve as a streaming
replication provider (Master) and receiver (client) at the same time?

Can i configure both to
* accept 'insert into ...' & stream that changes out (usual master)
AND
* catch streaming from another streaming ?

My application:
* Two servers acquire measurements at the same time into one table.
* No risk of conflicts!
* No need of standby skills (for now).


Thanks,
Bastian



-- 
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] receive and transmit streaming replication at same time

[Grzegorz Jaśkiewicz]

no you can't
but you have have multiple clusters running at the same time on the
same box. Just set them up on different ports, and in different
directories.

-- 
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] receive and transmit streaming replication at same time

[Thom Brown]

On 29 September 2010 10:22,   wrote:
> Hello List,
>
> a lot of recherche and no answer so far. So my question to you:
>
> Is it possible to set up ONE PostgreSQL 9 to serve as a streaming
> replication provider (Master) and receiver (client) at the same time?
>
> Can i configure both to
> * accept 'insert into ...' & stream that changes out (usual master)
> AND
> * catch streaming from another streaming ?
>
> My application:
> * Two servers acquire measurements at the same time into one table.
> * No risk of conflicts!
> * No need of standby skills (for now).

It sounds like you're after a multi-master setup, but that's not
possible (at least not at the moment).  Unless you're talking about
chainable slaves where slaves can stream data to other slaves, which I
think it also not currently possible.

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

-- 
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 for a CEP app

[Leonardo Francalanci]

Hi,


I need to generate aggregates of data coming from a stream.

I could easily doing it inserting data coming from the stream into a table,
and then query it using something like:

select  from atable group by 

The problem with this approach is that I would have to wait for the whole
stream to be finished before making the above query; since we're talking
about 20M+ rows, it would take some time for the query to finish.

What if I do something like:

select  from my_fifo_function([...]) 
group by 

where my_fifo_function reads data from the stream and returns "rows" as
soon as they are available on the stream? This way I would get the reply
as soon as the stream has finished (assuming postgresql can keep up
with that). In other words, the query would be made even before the
stream has "started", and would last at least as long as the stream.

(of course, I don't need data from the stream to be saved in any way,
that's why I don't need to store it in any table).

Does postgresql read data from a function returning a SETOF row by row?
Or it waits the whole function to be finished (caching the whole resultset)
before starting to use the returned data? If it reads row by row I think it
could work...


Would that make sense? 




-- 
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 for a CEP app

[Raymond O'Donnell]

On 29/09/2010 10:52, Leonardo Francalanci wrote:

Hi,


I need to generate aggregates of data coming from a stream.

I could easily doing it inserting data coming from the stream into a table,
and then query it using something like:

select  from atable group by

The problem with this approach is that I would have to wait for the whole
stream to be finished before making the above query; since we're talking
about 20M+ rows, it would take some time for the query to finish.

What if I do something like:

select  from my_fifo_function([...])
group by

where my_fifo_function reads data from the stream and returns "rows" as
soon as they are available on the stream? This way I would get the reply
as soon as the stream has finished (assuming postgresql can keep up
with that). In other words, the query would be made even before the
stream has "started", and would last at least as long as the stream.

(of course, I don't need data from the stream to be saved in any way,
that's why I don't need to store it in any table).

Does postgresql read data from a function returning a SETOF row by row?
Or it waits the whole function to be finished (caching the whole resultset)
before starting to use the returned data? If it reads row by row I think it
could work...


In pl/pgsql at any rate, functions which return a set of rows build up 
the entire result set in memory and then return the set in one go:


http://www.postgresql.org/docs/9.0/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

...see the note at the end of this section.

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

--
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] FTS GIN Index Question

[Oleg Bartunov]

Christian,

On Wed, 29 Sep 2010, Christian Ramseyer wrote:


Hi List

I have a largish partitioned table, it has ~60 million records in each of 12 
partitions. It appears that a Full Text Index could speed up some user 
queries a lot.


A quick test with an additional tsvector column revealed that this would take 
up around 35 GB of space for this column and then maybe 5 more for the gin 
index on it. As this is a lot of space (~ 480 GB), I'm a bit tempted to use a 
gin index without the separate tsvector column. However, the doc says that 
this will be slower.


do you have problem with disk space ? Searching index is usually very fast
operation, only small part of index readed.  Did you checked time to read
index ?



Does anyone have an idea of how much slower we're talking here? The index 
defintion would be a concatenation of two setweights(), i.e.:


... using gin(
 (setweight(to_tsvector('config',coalesce(col1,'')), 'A') ||
  setweight(to_tsvector('config',coalesce(col2,'')), 'B')))

Also, general recommendations regarding full text search configurations of 
that size are very welcome.


Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

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

[GENERAL] Optimizing postgresql.conf for dedicated windows server 2003 x64 standard edition

[Andrus]

I installed PostgreSql 9.0 Win 64 standard distro.

Stack builder offered in end of installation does not contain 
postgresql.conf optimization wizard.


How to optimize postgresql.conf  for 64 bit dedicated postgresql windows 
2003 server or is default .conf file best for this?


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] Postgresql for a CEP app

[Leonardo Francalanci]

> In pl/pgsql at any rate, functions which return  a set of rows build up 
> the entire result set in memory and then return the  set in one go:


Ok, then pl/pgsql and pl/python (which can't return SETOF) are ruled out.
(Thank you for pointing that out).
But pl/perl seems to do the trick:

"PL/Perl functions can also return sets of either scalar or composite types.
Usually you'll want to return rows one at a time, both to speed up startup
time and to keep from queueing up the entire result set in memory" 


Any other issues anyone can think about?




-- 
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] Optimizing postgresql.conf for dedicated windows server 2003 x64 standard edition

[Thom Brown]

2010/9/29 Andrus :
> I installed PostgreSql 9.0 Win 64 standard distro.
>
> Stack builder offered in end of installation does not contain
> postgresql.conf optimization wizard.
>
> How to optimize postgresql.conf  for 64 bit dedicated postgresql windows
> 2003 server or is default .conf file best for this?

It depends on how write-heavy the database will be, how much memory
you have to spare, and what sort of data you keep in it.  I'm
absolutely certain you will need to alter the config from the
defaults.  It's set up to be very conservative by default.

But take a look at http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

-- 
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] Optimizing postgresql.conf for dedicated windows server 2003 x64 standard edition

[Dave Page]

2010/9/29 Andrus :
> I installed PostgreSql 9.0 Win 64 standard distro.
>
> Stack builder offered in end of installation does not contain
> postgresql.conf optimization wizard.
>

We updated it for Win64, but it looks like the stackbuilder catalog
wasn't updated to allow it to be downloaded on that platform. I've
fixed that - it should be available within an hour or so.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] Optimizing postgresql.conf for dedicated windows server 2003 x64 standard edition

[Andrus]

We updated it for Win64, but it looks like the stackbuilder catalog
wasn't updated to allow it to be downloaded on that platform. I've
fixed that - it should be available within an hour or so.


I just installed 9.0 x64

Is it sufficient to run Program files / PostgreSql 9.0 / Application Stack 
Builder  from this installation to get it ?
Will it set optimal configuration so that manual editing of postgresql.conf 
is not required ?


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] Optimizing postgresql.conf for dedicated windows server 2003 x64 standard edition

[Dave Page]

On Wed, Sep 29, 2010 at 11:37 AM, Andrus  wrote:
>> We updated it for Win64, but it looks like the stackbuilder catalog
>> wasn't updated to allow it to be downloaded on that platform. I've
>> fixed that - it should be available within an hour or so.
>
> I just installed 9.0 x64
>
> Is it sufficient to run Program files / PostgreSql 9.0 / Application Stack
> Builder  from this installation to get it ?

Yes, in an hour or so.

> Will it set optimal configuration so that manual editing of postgresql.conf
> is not required ?

It will generate a starter configuration, that will be based on your
selected workload type and machine resources. That may be fine for
your needs, or you may need to tune it to more precisely meet the
needs of your actual workload. It doesn't claim to eliminate the need
for any future manual tuning.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

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

[GENERAL] optimizing a query with sub select

[Georgi Ivanov]

Hi,
I have this query
 SELECT
  *
FROM
  v_material
WHERE
show_in_recent AND section_id IN (
  SELECT
s.id
  FROM
section AS s, section AS s2
  WHERE
s2.id = 842
AND
s.breadcrumb <@ s2.breadcrumb
)
ORDER BY
  published_on DESC
LIMIT 3;

This query is slow (2 sec)

if i just remove the sub-select in IN clause, and replace it with the  value
of the sub-select ,the query is very fast.
The sub-select itself is very fast.
Is there a way to optimize it ?

Here are the 2 execution plans
Slow :

QUERY
PLAN

--
 Limit  (cost=82.13..92.65 rows=3 width=1109) (actual
time=2626.954..2626.954 rows=0 loops=1)
   ->  Nested Loop Left Join  (cost=82.13..633409.54 rows=180475 width=1109)
(actual time=2626.952..2626.952 rows=0 loops=1)
 ->  Nested Loop Semi Join  (cost=82.13..568691.75 rows=180475
width=1105) (actual time=2626.951..2626.951 rows=0 loops=1)
   Join Filter: (s.id = s.id)
   ->  Nested Loop  (cost=0.00..564548.94 rows=180475
width=1109) (actual time=0.096..2536.080 rows=179994 loops=1)
 ->  Nested Loop  (cost=0.00..511665.26 rows=180475
width=1106) (actual time=0.092..2236.543 rows=179994 loops=1)
   ->  Nested Loop Left Join  (cost=0.00..460819.28
rows=180475 width=1001) (actual time=0.084..1789.881 rows=179994 loops=1)
 ->  Nested Loop  (cost=0.00..403069.44
rows=180475 width=997) (actual time=0.067..1442.077 rows=179994 loops=1)
   ->  Nested Loop
(cost=0.00..352510.00 rows=180475 width=989) (actual time=0.061..1161.405
rows=179994 loops=1)
 ->  Nested Loop Left Join
(cost=0.00..301950.56 rows=180475 width=975) (actual time=0.056..873.697
rows=179994 loops=1)
   ->  Nested Loop
(cost=0.00..237987.06 rows=180475 width=971) (actual time=0.046..707.396
rows=179994 loops=1)
 ->  Index Scan
Backward using material_published_on on material m  (cost=0.00..187427.62
rows=180475 width=956) (actual time=0.037..392.091 rows=179994 loops=1)
   Filter:
(active AND show_in_recent)
 ->  Index Scan
using staff_pkey on staff s  (cost=0.00..0.27 rows=1 width=19) (actual
time=0.001..0.001 rows=1 loops=179994)
   Index Cond: (
s.id = m.created_by)
   ->  Index Scan using
rating_pkey on rating r  (cost=0.00..0.34 rows=1 width=8) (actual
time=0.000..0.000 rows=0 loops=179994)
 Index Cond:
(m.rating_id = r.id)
 ->  Index Scan using
lookup_material_type_pkey on lookup_material_type mt  (cost=0.00..0.27
rows=1 width=18) (actual time=0.001..0.001 rows=1 loops=179994)
   Index Cond: (mt.id =
m.type_id)
   ->  Index Scan using
lookup_material_importance_pkey on lookup_material_importance lmi
(cost=0.00..0.27 rows=1 width=12) (actual time=0.001..0.001 rows=1
loops=179994)
 Index Cond: (lmi.id =
m.importance_id)
 ->  Index Scan using material_images_pkey
on material_images mi  (cost=0.00..0.31 rows=1 width=8) (actual
time=0.001..0.001 rows=0 loops=179994)
   Index Cond: (m.id = mi.material_id)
   Filter: (mi."position" = 1)
   ->  Index Scan using section_pkey on section s
(cost=0.00..0.27 rows=1 width=105) (actual time=0.001..0.002 rows=1
loops=179994)
 Index Cond: (s.id = m.section_id)
 ->  Index Scan using lookup_section_type_pkey on
lookup_section_type lst  (cost=0.00..0.28 rows=1 width=11) (actual
time=0.001..0.001 rows=1 loops=179994)
   Index Cond: (lst.id = s.type_id)
   ->  Materialize  (cost=82.13..82.14 rows=1 width=4) (actual
time=0.000..0.000 rows=1 loops=179994)
 ->  Nested Loop  (cost=0.00..82.13 rows=1 width=4)
(actual time=0.535..0.730 rows=1 loops=1)
   Join Filter: (s.breadcrumb <@ s2.breadcrumb)
   ->  Index Scan using section_pkey on section s2
(cost=0.00..8.27 rows=1 width=34) (actual time=0.005..0.006 rows=1 loops=1)
 Index Cond: (id = 842)
   ->  Seq Scan on section s  (cost=0

Re: [GENERAL] optimizing a query with sub select

[Grzegorz Jaśkiewicz]

Use JOIN sherlock.

-- 
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] receive and transmit streaming replication at same time

[Vick Khera]

On Wed, Sep 29, 2010 at 5:22 AM,   wrote:
> Is it possible to set up ONE PostgreSQL 9 to serve as a streaming
> replication provider (Master) and receiver (client) at the same time?
>

You can implement such a replication using Slony-1.

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

[GENERAL] disable trigger all

[Grzegorz Jaśkiewicz]

I got bitten Today by 'alter table disable trigger all' vs 'trigger user'.
Basically , assuming that psql doesn't show me that FKs are disabled
some code was using 'trigger all' instead of 'user'.

Is that a bug of psql, or a feature ? As far as I can see
pg_catalog.pg_constraint doesn't contain information about whether FK
is disabled as a result of trigger disabling query or not. So it is
hard to asses the damage as well.



-- 
GJ

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

Re: Fwd: [GENERAL] Restore problem from 8.4 backup to 9.0

[Tom Lane]

Adam Wizon  writes:
> Thanks for the fast reply.  I must have still been connected to the older 
> database somehow.  I cleaned up my installation and restored the database.  
> No error messages this time.  I need to change the pg_hba.conf file.  I read 
> the documentation and its supposed to be in the data directory (which is 
> locked), but it doesn't seem to be there.  Is there an easy way to create the 
> file in the data directory (without overriding access privileges) at this 
> point?

That's where it would normally be, but try "show hba_file;" if you think
the packager of your distribution put it somewhere else.

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

Fwd: [GENERAL] Restore problem from 8.4 backup to 9.0

[Adam Wizon]

Where would I type 'show hba_file'?  I'm on Mac.  Also I downloaded the 
distribution from postgresql.org.  If I run an initdb and I already restored a 
database, will it put the pg_hba.conf file in the 'data' folder?  

> Subject: Re: Fwd: [GENERAL] Restore problem from 8.4 backup to 9.0
> 
> Adam Wizon  writes:
>> Thanks for the fast reply.  I must have still been connected to the older 
>> database somehow.  I cleaned up my installation and restored the database.  
>> No error messages this time.  I need to change the pg_hba.conf file.  I read 
>> the documentation and its supposed to be in the data directory (which is 
>> locked), but it doesn't seem to be there.  Is there an easy way to create 
>> the file in the data directory (without overriding access privileges) at 
>> this point?
> 
> That's where it would normally be, but try "show hba_file;" if you think
> the packager of your distribution put it somewhere else.
> 
>   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] disable trigger all

[Tom Lane]

=?UTF-8?Q?Grzegorz_Ja=C5=9Bkiewicz?=  writes:
> I got bitten Today by 'alter table disable trigger all' vs 'trigger user'.
> Basically , assuming that psql doesn't show me that FKs are disabled
> some code was using 'trigger all' instead of 'user'.

I think that is today's lesson in why not to work as a superuser except
when you absolutely have to.

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

Fwd: [GENERAL] Restore problem from 8.4 backup to 9.0

[Adam Wizon]

If I run the admin tool from my postgres account, that works fine since 
postgres is the owner.  

> Cc: pgsql-general@postgresql.org
> Subject: Re: Fwd: [GENERAL] Restore problem from 8.4 backup to 9.0
> 
>> Thanks for the fast reply.  I must have still been connected to the older 
>> database somehow.  I cleaned up my installation and restored the database.  
>> No error messages this time.  I need to change the pg_hba.conf file.  I read 
>> the documentation and its supposed to be in the data directory (which is 
>> locked), but it doesn't seem to be there.  Is there an easy way to create 
>> the file in the data directory (without overriding access privileges) at 
>> this point?
> 
> That's where it would normally be, but try "show hba_file;" if you think
> the packager of your distribution put it somewhere else.
> 
>   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] disable trigger all

[Grzegorz Jaśkiewicz]

2010/9/29 Tom Lane :
> =?UTF-8?Q?Grzegorz_Ja=C5=9Bkiewicz?=  writes:
>> I got bitten Today by 'alter table disable trigger all' vs 'trigger user'.
>> Basically , assuming that psql doesn't show me that FKs are disabled
>> some code was using 'trigger all' instead of 'user'.
>
> I think that is today's lesson in why not to work as a superuser except
> when you absolutely have to.


Thanks :)
Somehow I knew, you're not going to say. 'Ya, this is a bug, that info
should be displayed to the user'.

-- 
GJ

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

[GENERAL] Basic question on PGSQL and Transactions

[Henri De Feraudy]

Hello, 
  I've been writing an application that allows me to edit simple maps. It 
involves Postgis but here I think I have run into 
a basic Postgres consideration.
I did a bit of editing of a map which involved creating a polygon with a 
selection, inserting into a database and reloading
the "layer" where this geometric information lies.
There I was editing quite happily for about an hour and a half. I left my 
application and came back to it in the evening:
all my work was lost.
Could this be due to the fact I had not committed my insertions to the database?
I mean, if you are inserting into a table from PLPGSQL do you have to do an 
explicit commit at some stage?
I'm really quite a newbie as you can see.

Re: [GENERAL] Basic question on PGSQL and Transactions

[Andy Colson]

On 9/29/2010 11:31 AM, Henri De Feraudy wrote:

Hello,
I've been writing an application that allows me to edit simple maps. It
involves Postgis but here I think I have run into
a basic Postgres consideration.
I did a bit of editing of a map which involved creating a polygon with a
selection, inserting into a database and reloading
the "layer" where this geometric information lies.
There I was editing quite happily for about an hour and a half. I left
my application and came back to it in the evening:
all my work was lost.
Could this be due to the fact I had not committed my insertions to the
database?
I mean, if you are inserting into a table from PLPGSQL do you have to do
an explicit commit at some stage?
I'm really quite a newbie as you can see.



Yes, you really must commit.

Now, sometimes, some languages/tools will "help" you and autocommit.  It 
depends on what language/tools/etc you are using.


This is a wookie!  If that does not make sense, you must commit!
-south park, slightly paraphrased

-Andy

--
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] Basic question on PGSQL and Transactions

[Merlin Moncure]

On Wed, Sep 29, 2010 at 12:38 PM, Andy Colson  wrote:
> On 9/29/2010 11:31 AM, Henri De Feraudy wrote:
>>
>> Hello,
>> I've been writing an application that allows me to edit simple maps. It
>> involves Postgis but here I think I have run into
>> a basic Postgres consideration.
>> I did a bit of editing of a map which involved creating a polygon with a
>> selection, inserting into a database and reloading
>> the "layer" where this geometric information lies.
>> There I was editing quite happily for about an hour and a half. I left
>> my application and came back to it in the evening:
>> all my work was lost.
>> Could this be due to the fact I had not committed my insertions to the
>> database?
>> I mean, if you are inserting into a table from PLPGSQL do you have to do
>> an explicit commit at some stage?
>> I'm really quite a newbie as you can see.
>>
>
> Yes, you really must commit.
>
> Now, sometimes, some languages/tools will "help" you and autocommit.  It
> depends on what language/tools/etc you are using.

yes...one more note about this:  It is absolutely never (IMNSHO) a
good idea to intentionally hold transactions open while waiting on
user input except for testing purposes.

merlin

-- 
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] Basic question on PGSQL and Transactions

[Alban Hertroys]

On 29 Sep 2010, at 18:31, Henri De Feraudy wrote:

> I mean, if you are inserting into a table from PLPGSQL do you have to do an 
> explicit commit at some stage?

I suppose you meant psql here, as otherwise the above statement wouldn't make 
sense ;)

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4ca371b7678305060513005!



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

[GENERAL] Prepared statements and unknown types

[Thom Brown]

Could someone explain why the following doesn't work?

test=# PREPARE meow(unknown) AS
test-# SELECT $1 as meow;
ERROR:  could not determine data type of parameter $1

The problem is that using PDO in PHP, prepared statements aren't
possible if values are used instead of columns in the select list.

This appears to be allowed for MySQL and SQL Server.

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

-- 
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] FTS GIN Index Question

[Christian Ramseyer]

On 09/29/2010 12:10 PM, Oleg Bartunov wrote:

Christian,

On Wed, 29 Sep 2010, Christian Ramseyer wrote:


Hi List

I have a largish partitioned table, it has ~60 million records in each
of 12 partitions. It appears that a Full Text Index could speed up
some user queries a lot.

A quick test with an additional tsvector column revealed that this
would take up around 35 GB of space for this column and then maybe 5
more for the gin index on it. As this is a lot of space (~ 480 GB),
I'm a bit tempted to use a gin index without the separate tsvector
column. However, the doc says that this will be slower.


do you have problem with disk space ? Searching index is usually very fast
operation, only small part of index readed. Did you checked time to read
index ?



Hi Oleg, thanks for your reply,

Well I could get the disk space, but it's on a corporate SAN so it's a 
bit tedious. I didn't compare directly so far since even creating both 
index versions for only a single partition would bring me close to the 
space limit and also take maybe 20 hours or so. So my idea was to ask 
here first if I'm looking at a "a few percent" or a "orders of 
magnitude" difference. But of course, if there isn't enough feeback for 
an informed decision I'll either try both or just go with the 
space-intensive variant to be on the safe side, user experience is 
certainly more important than disk usage.


Christian




--
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] Prepared statements and unknown types

[Peter Bex]

On Wed, Sep 29, 2010 at 07:08:22PM +0100, Thom Brown wrote:
> Could someone explain why the following doesn't work?
> 
> test=# PREPARE meow(unknown) AS
> test-# SELECT $1 as meow;
> ERROR:  could not determine data type of parameter $1
> 
> The problem is that using PDO in PHP, prepared statements aren't
> possible if values are used instead of columns in the select list.

The type is always string for data that's sent; it's converted to
an appropriate type when the destination of the parameter is determined.
If you know the type, you can do

PREPARE meow(text) AS
SELECT $1 as meow;

or

PREPARE meow(unknown) AS
SELECT $1::text as meow;

You can also send a parameter as a specific type using the C interface,
but that requires support from the language/library you're using.

> This appears to be allowed for MySQL and SQL Server.

I don't know how they handle that.  Perhaps they try to read your mind.
Perhaps PHP adds some kind of type conversion for types it knows for
those two interfaces.

Cheers,
Peter
-- 
http://sjamaan.ath.cx
--
"The process of preparing programs for a digital computer
 is especially attractive, not only because it can be economically
 and scientifically rewarding, but also because it can be an aesthetic
 experience much like composing poetry or music."
-- Donald Knuth

-- 
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] Prepared statements and unknown types

[Thom Brown]

On 29 September 2010 19:15, Peter Bex  wrote:
> On Wed, Sep 29, 2010 at 07:08:22PM +0100, Thom Brown wrote:
>> Could someone explain why the following doesn't work?
>>
>> test=# PREPARE meow(unknown) AS
>> test-# SELECT $1 as meow;
>> ERROR:  could not determine data type of parameter $1
>>
>> The problem is that using PDO in PHP, prepared statements aren't
>> possible if values are used instead of columns in the select list.
>
> The type is always string for data that's sent; it's converted to
> an appropriate type when the destination of the parameter is determined.
> If you know the type, you can do
>
> PREPARE meow(text) AS
> SELECT $1 as meow;
>
> or
>
> PREPARE meow(unknown) AS
> SELECT $1::text as meow;
>
> You can also send a parameter as a specific type using the C interface,
> but that requires support from the language/library you're using.
>
>> This appears to be allowed for MySQL and SQL Server.
>
> I don't know how they handle that.  Perhaps they try to read your mind.
> Perhaps PHP adds some kind of type conversion for types it knows for
> those two interfaces.

Okay, I understand what's happening.  But does the planner need to
understand the type of literals in the select list if it's not used
anywhere else?

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

-- 
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] psql copy command - 1 char limitation on delimiter

[Steve Crawford]

On 09/25/2010 07:03 AM, Tom Lane wrote:

rey  writes:
   

Why limit this to a single character?
 

Performance.  Believe it or not, breaking fields at the delimiter is
a significant factor in COPY speed.

regards, tom lane

   
I agree that that multi-character (or even regex) delimiters would be 
useful. Would it be reasonable for the copy process to differentiate 
between single character delimiters which could be processed in 
"high-speed" mode and multi-character or regex delimiters which would be 
available as needed albeit at the expense of a performance hit?


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] Prepared statements and unknown types

[Peter Bex]

On Wed, Sep 29, 2010 at 07:33:53PM +0100, Thom Brown wrote:
> Okay, I understand what's happening.  But does the planner need to
> understand the type of literals in the select list if it's not used
> anywhere else?

Fields sent back to the client also carry their type with them.
There's no "unknown" type (and it wouldn't be very useful in any
case, because how would you go about displaying an unknown type?)

Cheers,
Peter
-- 
http://sjamaan.ath.cx
--
"The process of preparing programs for a digital computer
 is especially attractive, not only because it can be economically
 and scientifically rewarding, but also because it can be an aesthetic
 experience much like composing poetry or music."
-- Donald Knuth

-- 
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] Prepared statements and unknown types

[Thom Brown]

On 29 September 2010 19:41, Peter Bex  wrote:
> On Wed, Sep 29, 2010 at 07:33:53PM +0100, Thom Brown wrote:
>> Okay, I understand what's happening.  But does the planner need to
>> understand the type of literals in the select list if it's not used
>> anywhere else?
>
> Fields sent back to the client also carry their type with them.
> There's no "unknown" type (and it wouldn't be very useful in any
> case, because how would you go about displaying an unknown type?)

There is an unknown type...

test=# create table stuff (id serial);
NOTICE:  CREATE TABLE will create implicit sequence "stuff_id_seq" for
serial column "stuff.id"
CREATE TABLE

test=# insert into stuff select c from generate_series(1,100) x(c);
INSERT 0 100

test=# PREPARE meow(unknown) AS
SELECT * from stuff where id < $1;
PREPARE
test=# execute meow(3);
 id

  1
  2
(2 rows)

test=# PREPARE bark(unknown) AS
SELECT * from stuff where id < $1;
PREPARE
test=# execute bark('3'::unknown);
 id

  1
  2
(2 rows)

The docs specifically make mention of it in the case of PREPARE.
-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

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

[GENERAL] Are there any commands to see the created index info?

[sunpeng]

Are there any commands to see the created index info?
For example, if I have a table A (has 100,000 tuples )with index A_INDEX,
how to see the A_INDEX info, such as the deep of B+ tree?
peng

Re: [GENERAL] Prepared statements and unknown types

[Tom Lane]

Peter Bex  writes:
> On Wed, Sep 29, 2010 at 07:33:53PM +0100, Thom Brown wrote:
>> Okay, I understand what's happening.  But does the planner need to
>> understand the type of literals in the select list if it's not used
>> anywhere else?

> Fields sent back to the client also carry their type with them.
> There's no "unknown" type (and it wouldn't be very useful in any
> case, because how would you go about displaying an unknown type?)

Well, actually there *is* an "unknown" type (OID 705), which is what
will be reported if there's a literal of unresolved type in the SELECT
list.  That's how come you can do

regression=# select 'foo' as meow;
 meow 
--
 foo
(1 row)

However, the issue here is not the output but the input: PREPARE is
complaining that the *input* parameter $1 has no determinate type.
If PREPARE doesn't know it, then the client isn't going to know it
either, and so it would be hard for the client to know what to send
to execute the statement.

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] Prepared statements and unknown types

[Thom Brown]

On 29 September 2010 20:02, Tom Lane  wrote:
> Peter Bex  writes:
>> On Wed, Sep 29, 2010 at 07:33:53PM +0100, Thom Brown wrote:
>>> Okay, I understand what's happening.  But does the planner need to
>>> understand the type of literals in the select list if it's not used
>>> anywhere else?
>
>> Fields sent back to the client also carry their type with them.
>> There's no "unknown" type (and it wouldn't be very useful in any
>> case, because how would you go about displaying an unknown type?)
>
> Well, actually there *is* an "unknown" type (OID 705), which is what
> will be reported if there's a literal of unresolved type in the SELECT
> list.  That's how come you can do
>
> regression=# select 'foo' as meow;
>  meow
> --
>  foo
> (1 row)
>
> However, the issue here is not the output but the input: PREPARE is
> complaining that the *input* parameter $1 has no determinate type.
> If PREPARE doesn't know it, then the client isn't going to know it
> either, and so it would be hard for the client to know what to send
> to execute the statement.

We'll have to think of ways to work round this then as it's for a
database class in a common library we're building.

Thanks Tom

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

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

[GENERAL] Get next OID

[Dianne Yumul]

Hello everyone,

We have PostgreSQL 8.1.21 on CentOS 5.5 and some of our older programs still 
use OIDs to identify a row.  I'm planning on adding a serial column to the 
tables and modifying the code to use the serial column instead.  But I am 
curious to find out how close we are before the OID wraps around.  Is there a 
way to check for this?  I tried select max(oid) from table on the tables and 
the highest I got was around 8 million.  I searched the docs, faq and mailing 
list archives but haven't found any info on getting the next OID.

Thank you very much.

Dianne



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

[GENERAL] Centralized User Management Tool?

[David Kerr]

Howdy,

Does anyone know of any tools or methods to handle centralized user management 
within postgres?

I've got about 20 DB servers (and growing) each requiring a different number 
and level of user access 
(think dev, qa, staging, production, etc.)

Corporate security guidelines state that all users need to have their own 
login, and since we
work with sensitive data, that extends down into the developers space. 

I know i can authenticate using PAM on linux, however that still requires a 
user to get created in
the DB. (I'm looking for a tool that helps manage that user creation portion)

Thanks

Dave

-- 
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] Centralized User Management Tool?

[Joshua D. Drake]

On Wed, 2010-09-29 at 14:21 -0700, David Kerr wrote:
> Howdy,
> 
> Does anyone know of any tools or methods to handle centralized user 
> management within postgres?
> 
> I've got about 20 DB servers (and growing) each requiring a different number 
> and level of user access 
> (think dev, qa, staging, production, etc.)
> 
> Corporate security guidelines state that all users need to have their own 
> login, and since we
> work with sensitive data, that extends down into the developers space. 
> 
> I know i can authenticate using PAM on linux, however that still requires a 
> user to get created in
> the DB. (I'm looking for a tool that helps manage that user creation portion)

ldap


> 
> Thanks
> 
> Dave
> 

-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt


-- 
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] Centralized User Management Tool?

[David Kerr]

On Wed, Sep 29, 2010 at 02:23:14PM -0700, Joshua D. Drake wrote:
- > Howdy,
- > 
- > Does anyone know of any tools or methods to handle centralized user 
management within postgres?
- > 
- > I've got about 20 DB servers (and growing) each requiring a different 
number and level of user access 
- > (think dev, qa, staging, production, etc.)
- > 
- > Corporate security guidelines state that all users need to have their own 
login, and since we
- > work with sensitive data, that extends down into the developers space. 
- > 
- > I know i can authenticate using PAM on linux, however that still requires a 
user to get created in
- > the DB. (I'm looking for a tool that helps manage that user creation 
portion)
- 
- ldap

oh, great! i thought that fell under the "authenticate" but not "authorize" 
umbrella.

thanks!

Dave

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

[GENERAL] Missing path in pg_config

[Turner, John J]

Hello,

I've installed the PostgreSQL 9.0 binary package for Windows XP 32-bit
and I'm trying to install the temporal extension module available in
pg_Foundry.  One problem I can see that I'm running into is that the
path defined for PGXS in pg_config does not exist:

PGXS = c:/program files/PostgreSQL/9.0/lib/pgxs/src/makefiles/pgxs.mk

I have a path up to the ".../lib" but nothing after that.  Do I need to
re-install something or run a script to flesh things out?
It seems when I follow the instructions for the temporal module install
(as found here
 ), it bombs out when it tries to find this path while
running make install

Any help greatly appreciated.

Thanks,
John

Re: [GENERAL] Missing path in pg_config

[Dave Page]

On Wed, Sep 29, 2010 at 7:23 PM, Turner, John J
 wrote:
> Hello,
>
> I’ve installed the PostgreSQL 9.0 binary package for Windows XP 32-bit and
> I’m trying to install the temporal extension module available in
> pg_Foundry.  One problem I can see that I’m running into is that the path
> defined for PGXS in pg_config does not exist:
>
> PGXS = c:/program files/PostgreSQL/9.0/lib/pgxs/src/makefiles/pgxs.mk
>
> I have a path up to the “…/lib” but nothing after that.  Do I need to
> re-install something or run a script to flesh things out?
>
> It seems when I follow the instructions for the temporal module install (as
> found here), it bombs out when it tries to find this path while running make
> install

PGXS doesn't work with the VC++ build we use for Windows. I wonder if
that output from pg_config should be #ifdef'd out until such time as
we can figure out a way to make it work - if that's even possible.



-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

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

[GENERAL] Somewhat odd messages being logged on replicated server

[Karl Denninger]

 I am playing with the replication on 9.0 and running into the following.

I have a primary that is running at a colo, and is replicated down to a
secondary here using SLONY.  This is working normally.

I decided to set up a replication of the SLONY secondary onto my
"sandbox" machine to see what I think of it as a solution to potentially
replace SLONY.  All appears to be working fine, EXCEPT that I'm getting
the following in the log.

Sep 29 19:58:54 dbms2 postgres[8564]: [2-2] STATEMENT:  update post set
views = (select views from post where number='116763' and toppost='1') +
1 where number='116763' and toppost='1'
Sep 29 20:01:11 dbms2 postgres[8581]: [2-1] ERROR:  cannot execute
UPDATE in a read-only transaction
Sep 29 20:01:11 dbms2 postgres[8581]: [2-2] STATEMENT:  update post set
views = (select views from post where number='2040327' and toppost='1')
+ 1 where number='2040327' and toppost='1'
Sep 29 20:02:02 dbms2 postgres[8582]: [2-1] ERROR:  cannot execute
UPDATE in a read-only transaction
Sep 29 20:02:02 dbms2 postgres[8582]: [2-2] STATEMENT:  update post set
views = (select views from post where number='140406' and toppost='1') +
1 where number='140406' and toppost='1'
Sep 29 20:04:58 dbms2 postgres[8586]: [2-1] ERROR:  cannot execute
UPDATE in a read-only transaction

When I go look at the value of "views" on both the master and replicated
slave, they have the same value. so I'm not sure why the error is
showing up.

There are no clients attempting to connect to the replicated server at
all at this point (I will enable that later once I'm satisfied that it
is working in the general sense), so this has to be coming from the
replication system itself.

I presume that since the values are identical on both machines this can
be safely ignored, but I'm curious why it's happening

-- 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] Somewhat odd messages being logged on replicated server

[Jeff Davis]

On Wed, 2010-09-29 at 20:04 -0500, Karl Denninger wrote:
> Sep 29 19:58:54 dbms2 postgres[8564]: [2-2] STATEMENT:  update post set
> views = (select views from post where number='116763' and toppost='1') +
> 1 where number='116763' and toppost='1'
> Sep 29 20:01:11 dbms2 postgres[8581]: [2-1] ERROR:  cannot execute
> UPDATE in a read-only transaction
> Sep 29 20:01:11 dbms2 postgres[8581]: [2-2] STATEMENT:  update post set
> views = (select views from post where number='2040327' and toppost='1')
> + 1 where number='2040327' and toppost='1'
> Sep 29 20:02:02 dbms2 postgres[8582]: [2-1] ERROR:  cannot execute
> UPDATE in a read-only transaction
> Sep 29 20:02:02 dbms2 postgres[8582]: [2-2] STATEMENT:  update post set
> views = (select views from post where number='140406' and toppost='1') +
> 1 where number='140406' and toppost='1'
> Sep 29 20:04:58 dbms2 postgres[8586]: [2-1] ERROR:  cannot execute
> UPDATE in a read-only transaction
> 
> When I go look at the value of "views" on both the master and replicated
> slave, they have the same value. so I'm not sure why the error is
> showing up.
> 
> There are no clients attempting to connect to the replicated server at
> all at this point (I will enable that later once I'm satisfied that it
> is working in the general sense), so this has to be coming from the
> replication system itself.

It looks very much like clients are connecting and issuing UPDATEs (or
trying to). Can you re-examine the situation? Perhaps try changing
pg_hba.conf to be sure nobody is connecting. 9.0-style replication
doesn't issue queries by itself.

Also, the UPDATEs look unsafe by themselves (replication or not). If two
such updates are executed simultaneously, "views" might not be updated
twice. Instead, try:

update post set views = views + 1
  where number='140406' and toppost='1'

Regards,
Jeff Davis


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

[GENERAL] build of 9.0 did not make an "etc" directory

[Rob Sargent]

The log from make shows

echo "#define PGBINDIR \"/opt/PostgreSQL/9.0/bin\"" >pg_config_paths.h
echo "#define PGSHAREDIR \"/opt/PostgreSQL/9.0/share/postgresql\""
>>pg_config_paths.h
echo "#define SYSCONFDIR \"/opt/PostgreSQL/9.0/etc/postgresql\""
>>pg_config_paths.h
echo "#define INCLUDEDIR \"/opt/PostgreSQL/9.0/include\""
>>pg_config_paths.h
echo "#define PKGINCLUDEDIR
\"/opt/PostgreSQL/9.0/include/postgresql\"" >>pg_config_paths.h
echo "#define INCLUDEDIRSERVER
\"/opt/PostgreSQL/9.0/include/postgresql/server\"" >>pg_config_paths.h
echo "#define LIBDIR \"/opt/PostgreSQL/9.0/lib\"" >>pg_config_paths.h
echo "#define PKGLIBDIR \"/opt/PostgreSQL/9.0/lib/postgresql\""
>>pg_config_paths.h
echo "#define LOCALEDIR \"/opt/PostgreSQL/9.0/share/locale\""
>>pg_config_paths.h
echo "#define DOCDIR \"/opt/PostgreSQL/9.0/share/doc//postgresql\""
>>pg_config_paths.h
echo "#define HTMLDIR \"/opt/PostgreSQL/9.0/share/doc//postgresql\""
>>pg_config_paths.h
echo "#define MANDIR \"/opt/PostgreSQL/9.0/share/man\""
>>pg_config_paths.h

but I do not see it in /opt/PostgreSQL/9.0.

Further, pc_config also believes it exists (or should):

am-rjs:bin$ pwd
/opt/PostgreSQL/9.0/bin
am-rjs:bin$ ./pg_config --sysconfdir
/opt/PostgreSQL/9.0/etc/postgresql

I do have the bin, include, lib and share directories.

I built with the following:

am-rjs:bin$ ./pg_config --configure
'--prefix=/opt/PostgreSQL/9.0' '--with-ossp-uuid' '--with-libxml'
'--with-libxslt' '--with-openssl' '--with-perl' '--with-python'

Best to just restart?

Re: [GENERAL] Get next OID

[Tom Lane]

Dianne Yumul  writes:
> We have PostgreSQL 8.1.21 on CentOS 5.5 and some of our older programs still 
> use OIDs to identify a row.  I'm planning on adding a serial column to the 
> tables and modifying the code to use the serial column instead.  But I am 
> curious to find out how close we are before the OID wraps around.  Is there a 
> way to check for this?  I tried select max(oid) from table on the tables and 
> the highest I got was around 8 million.  I searched the docs, faq and mailing 
> list archives but haven't found any info on getting the next OID.

pg_controldata would tell you approximately where the OID counter 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] Somewhat odd messages being logged on replicated server

[Karl Denninger]

On 9/29/2010 8:55 PM, Jeff Davis wrote:
> On Wed, 2010-09-29 at 20:04 -0500, Karl Denninger wrote:
>> Sep 29 19:58:54 dbms2 postgres[8564]: [2-2] STATEMENT:  update post set
>> views = (select views from post where number='116763' and toppost='1') +
>> 1 where number='116763' and toppost='1'
>> Sep 29 20:01:11 dbms2 postgres[8581]: [2-1] ERROR:  cannot execute
>> UPDATE in a read-only transaction
>> Sep 29 20:01:11 dbms2 postgres[8581]: [2-2] STATEMENT:  update post set
>> views = (select views from post where number='2040327' and toppost='1')
>> + 1 where number='2040327' and toppost='1'
>> Sep 29 20:02:02 dbms2 postgres[8582]: [2-1] ERROR:  cannot execute
>> UPDATE in a read-only transaction
>> Sep 29 20:02:02 dbms2 postgres[8582]: [2-2] STATEMENT:  update post set
>> views = (select views from post where number='140406' and toppost='1') +
>> 1 where number='140406' and toppost='1'
>> Sep 29 20:04:58 dbms2 postgres[8586]: [2-1] ERROR:  cannot execute
>> UPDATE in a read-only transaction
>>
>> When I go look at the value of "views" on both the master and replicated
>> slave, they have the same value. so I'm not sure why the error is
>> showing up.
>>
>> There are no clients attempting to connect to the replicated server at
>> all at this point (I will enable that later once I'm satisfied that it
>> is working in the general sense), so this has to be coming from the
>> replication system itself.
> It looks very much like clients are connecting and issuing UPDATEs (or
> trying to). Can you re-examine the situation? Perhaps try changing
> pg_hba.conf to be sure nobody is connecting. 9.0-style replication
> doesn't issue queries by itself.
I'm VERY sure nobody is connecting - the machine in question is behind a
firewall!  In addition here's the netstat on it:

$ netstat -a -n|more
Active Internet connections (including servers)
Proto Recv-Q Send-Q  Local Address  Foreign Address   (state)
tcp4   0  0 192.168.1.202.22   192.168.1.40.51232
ESTABLISHED
tcp4   0  0 192.168.1.202.61119192.168.1.201.5432
ESTABLISHED
tcp4   0  0 *.5432 *.*LISTEN
tcp6   0  0 *.5432 *.*LISTEN
tcp4   0  0 *.514  *.*LISTEN
tcp4   0  0 127.0.0.1.25   *.*LISTEN
tcp4   0  0 *.22   *.*LISTEN
tcp6   0  0 *.22   *.*LISTEN
udp6   0  0 ::1.22165  ::1.22165 
udp4   0  0 127.0.0.1.123  *.*   
udp6   0  0 ::1.123*.*   
udp6   0  0 fe80:3::1.123  *.*   
udp4   0  0 192.168.1.202.123  *.*   
udp6   0  0 *.123  *.*   
udp4   0  0 *.123  *.*   
udp4   0  0 *.514  *.*   
udp6   0  0 *.514  *.*   

The only connection to a Postgresql server is to the master I am pulling
the replication from (201)  No other connections - the listener is
there, but nobody's connected to it.

> Also, the UPDATEs look unsafe by themselves (replication or not). If two
> such updates are executed simultaneously, "views" might not be updated
> twice. Instead, try:
>
> update post set views = views + 1
>   where number='140406' and toppost='1'
>
> Regards,
>   Jeff Davis
That's actually ok - the update itself is a legitimate statement on the
master, posted to that table on a reply, and is part of a transaction.

I thought logshipping like this over TCP would present the actual WAL
changes (e.g. page changes) and was quite surprised when I saw what look
very much like actual statements in the logfile.  A process status also
shows no postgres processes.

All of the real application connections are on a machine 1200 miles away
and behind pgpool, so if there WAS a connection it would be persistent. 
It's not there (never mind that the clients don't have an entry in
pg_hba that would work, nor could they cross the firewall since there is
no port forwarding in the firewall to allow it.)

I'll investigate this further.

-- Karl

-- 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] build of 9.0 did not make an "etc" directory

[Tom Lane]

Rob Sargent  writes:
> echo "#define SYSCONFDIR \"/opt/PostgreSQL/9.0/etc/postgresql\""

SYSCONFDIR is a place where you might choose to put some handmade
configuration files, but the standard installation doesn't put anything
there.  So offhand I don't see a reason why it should create the
directory.

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] Somewhat odd messages being logged on replicated server

[Jeff Davis]

On Wed, 2010-09-29 at 21:57 -0500, Karl Denninger wrote:
> That's actually ok - the update itself is a legitimate statement on the
> master, posted to that table on a reply, and is part of a transaction.

Even if it's part of a transaction, you still have a race condition,
unless you're using a serializable transcation isolation level.

> I thought logshipping like this over TCP would present the actual WAL
> changes (e.g. page changes) and was quite surprised when I saw what look
> very much like actual statements in the logfile.  A process status also
> shows no postgres processes.

9.0-style replication only ships WAL. Those UPDATE statements are _not_
coming from the 9.0-style log shipping replication.

> All of the real application connections are on a machine 1200 miles away
> and behind pgpool, so if there WAS a connection it would be persistent. 
> It's not there (never mind that the clients don't have an entry in
> pg_hba that would work, nor could they cross the firewall since there is
> no port forwarding in the firewall to allow it.)
> 

Aha, you're using pgpool. The statements are probably coming from there
-- it's got a lot of options and it may be misconfigured.

Turn on some extended logging information that includes the client IP
address. That will probably give you some better insight.

Regards,
Jeff Davis



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