Re: [GENERAL] Error: absolute path not allowed

[Raghavendra]

On Tue, Jan 8, 2013 at 8:39 PM, Wolf Schwurack  wrote:

>  I am getting a repeating error and not sure what why. 
>
> ** **
>
> 2013-01-08 06:12:08 MSTERROR:  absolute path not allowed
>
> 2013-01-08 06:12:08 MSTSTATEMENT:  SELECT pg_read_file($1, 0, 1048576)
>
> 2013-01-08 06:42:10 MSTERROR:  absolute path not allowed
>
> 2013-01-08 06:42:10 MSTSTATEMENT:  SELECT pg_read_file($1, 0, 1048576)
>
> 2013-01-08 07:12:01 MSTERROR:  absolute path not allowed
>
> 2013-01-08 07:12:01 MSTSTATEMENT:  SELECT pg_read_file($1, 0, 1048576)
>
> 2013-01-08 07:42:02 MSTERROR:  absolute path not allowed
>
> 2013-01-08 07:42:02 MSTSTATEMENT:  SELECT pg_read_file($1, 0, 1048576)
>
> **
>

pg_read_file() search's from $PGDATA location.

Can we see how pg_read_file() has been called ?

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

Re: [GENERAL] Error: absolute path not allowed

[Scott Marlowe]

On Tue, Jan 8, 2013 at 8:09 AM, Wolf Schwurack  wrote:
> I am getting a repeating error and not sure what why.
>
>
>
> 2013-01-08 06:12:08 MSTERROR:  absolute path not allowed
>
> 2013-01-08 06:12:08 MSTSTATEMENT:  SELECT pg_read_file($1, 0, 1048576)
>
> 2013-01-08 06:42:10 MSTERROR:  absolute path not allowed
>
> 2013-01-08 06:42:10 MSTSTATEMENT:  SELECT pg_read_file($1, 0, 1048576)
>
> 2013-01-08 07:12:01 MSTERROR:  absolute path not allowed
>
> 2013-01-08 07:12:01 MSTSTATEMENT:  SELECT pg_read_file($1, 0, 1048576)
>
> 2013-01-08 07:42:02 MSTERROR:  absolute path not allowed
>
> 2013-01-08 07:42:02 MSTSTATEMENT:  SELECT pg_read_file($1, 0, 1048576)
>
>
>
> Anyone know why I’m getting this?

Just a guess but it looks like $1 has an absolute path (i.e. it starts with / )


-- 
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 store clickmap points?

[aasat]

Hi, 

I want to store clickmap points (X, Y and hits value) for website

I currently have table like this

CREATE TABLE clickmap (
  page_id integer,
  date date,
  x smallint,
  y smallint,
  hits integer
)

But this generated about 1M rows per day.

Can Postgres have better method to store this data? I also have the
possibility to update hits value for point









--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/How-to-store-clickmap-points-tp5739121.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

[GENERAL] Error: absolute path not allowed

[Wolf Schwurack]

I am getting a repeating error and not sure what why.

2013-01-08 06:12:08 MSTERROR:  absolute path not allowed
2013-01-08 06:12:08 MSTSTATEMENT:  SELECT pg_read_file($1, 0, 1048576)
2013-01-08 06:42:10 MSTERROR:  absolute path not allowed
2013-01-08 06:42:10 MSTSTATEMENT:  SELECT pg_read_file($1, 0, 1048576)
2013-01-08 07:12:01 MSTERROR:  absolute path not allowed
2013-01-08 07:12:01 MSTSTATEMENT:  SELECT pg_read_file($1, 0, 1048576)
2013-01-08 07:42:02 MSTERROR:  absolute path not allowed
2013-01-08 07:42:02 MSTSTATEMENT:  SELECT pg_read_file($1, 0, 1048576)

Anyone know why I'm getting this?



  0___  Wolfgang Schwurack
 c/  /'_DBA/SA
(*)  \(*)   University of Utah/UEN
Tel: (801) 587-9444
email: w...@uen.org

[GENERAL] Getting PLPython to work with PostgreSQL 9.2

[ledocf]

Hi, 

I have recently installed PostgreSQL 9.2 on my Windows 7 64bit computer. In
addition I have installed Python 3.3. I am new to PostgreSQL, and wish to
play with its support for Python.

I tried to run the command:

CREATE EXTENSION plpython3u

Only to receive the error:

Could not load library "C:/Program Files/PostgreSQL/9.2/lib/plpython3.dll"

Looking at plpython3.dll in Dependency Walker yielded the following:

 

This raises two questions:

a) LIBINTL-8.DLL and POSTGRES>EXE actually exist on my system. This appears
to suggest some sort of environment problem. 

b) PYTHON32.DLL doesn't exist on my machine (I only have python3.dll). This
would suggest that perhaps the (latest) version of Python I have is not
comptaible

Can anyone shed some light on the problems I am having? I cannot see what
environment variables need to be set (the docs I have read specify nothing).
In addition, no requirements for the supported versions of Python are
defined.

HELP!!



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Getting-PLPython-to-work-with-PostgreSQL-9-2-tp5739185.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] query by partial timestamp

[Kirk Wythers]

On Jan 8, 2013, at 6:48 PM, Tom Lane  wrote:

> The OP didn't
> suggest how many years his data covers, but it's quite possible that
> pulling a full year's worth of data will read enough of the table that
> there's no point in worrying about whether an index could be used
> anyway.

There are only a few years worth of data, 2008 - 2012. However, the data 
consists of 15 min measurements and when renormalized (un-pivoted) is several 
hundred million records. It is conceivable that someone will want to query by 
month, or even hour of the day. 

[GENERAL] Installation

[James]

Hello, I was just wondering if it is possible to set the postgresql 
default folder to a data drive? I tried to change the default from my 
system drive because it was taking up far too much space, but it was 
having none of it and continued to install on my system drive. Regards, 
James.



--
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] currval of sequence xxx_seq is not yet defined in this session?

[kenyon]

Thanks for your reply!

yeath,I quite accept your opinion,once i guess the drive adds savepoint
between the two SQL，but not sure

i want to get the last insert id to ensure the INSERT correctly ,so i called
currval().

as the error occurs seldom,i find it's difficult to trace the problem or the
logic is doing exactly

later i modified the code looks like:
>select nexval('t_wedding_wedding_id_seq') as Wedding_id;
>insert into t_wedding(wedding_id...) select Wedding_id...;

and in the later pressure tesing never saw the error again.




-
God believes postgres is Good
--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/currval-of-sequence-xxx-seq-is-not-yet-defined-in-this-session-tp5738893p5739294.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] Discerning when functions had execute revoked from public

[Tom Lane]

Jerry Sievers  writes:
> If I understand correctly how it works, public execute is granted in
> the default case of no rows returned as seen in the first case AND
> when we get a row with  grantee=0 and privilege='execute'.

> select (aclexplode(proacl)).* from pg_proc where proname = 'foo';

I don't think this will work reliably, because you'll get zero rows out
in two cases: when proacl is null (which implies the default of public
execute) and when proacl is a non-null empty array (which implies that
no privileges are granted to anybody).  The latter case is probably not
very common in practice, but if you do run into it you don't want to
confuse it with the default.

Here's a more extensively worked-out example:

postgres=# create user alice;
CREATE ROLE
postgres=# create user bob;
CREATE ROLE
postgres=# select oid, rolname from pg_authid;
  oid  | rolname  
---+--
10 | postgres
 95618 | alice
 95619 | bob
(3 rows)

postgres=# \c - alice
You are now connected to database "postgres" as user "alice".
postgres=> create function foo() returns int as $$select 1$$ language sql;
CREATE FUNCTION
postgres=> select proacl, proacl is null from pg_proc where proname = 'foo';
 proacl | ?column? 
+--
| t
(1 row)

postgres=> grant execute on function foo() to bob;
GRANT
postgres=> select proacl, proacl is null from pg_proc where proname = 'foo';
proacl| ?column? 
--+--
 {=X/alice,alice=X/alice,bob=X/alice} | f
(1 row)

postgres=> select (aclexplode(proacl)).* from pg_proc where proname = 'foo';
 grantor | grantee | privilege_type | is_grantable 
-+-++--
   95618 |   0 | EXECUTE| f
   95618 |   95618 | EXECUTE| f
   95618 |   95619 | EXECUTE| f
(3 rows)

It's worth explaining that what happened here was that GRANT
instantiated the default permissions for the function (namely, "all
rights for owner, plus execute rights for public") and then added the
requested privileges for bob.

postgres=> revoke all on function foo() from public;
REVOKE
postgres=> select proacl, proacl is null from pg_proc where proname = 'foo';
   proacl| ?column? 
-+--
 {alice=X/alice,bob=X/alice} | f
(1 row)

postgres=> select (aclexplode(proacl)).* from pg_proc where proname = 'foo';
 grantor | grantee | privilege_type | is_grantable 
-+-++--
   95618 |   95618 | EXECUTE| f
   95618 |   95619 | EXECUTE| f
(2 rows)

postgres=> revoke all on function foo() from bob;
REVOKE
postgres=> revoke all on function foo() from alice;
REVOKE
postgres=> select proacl, proacl is null from pg_proc where proname = 'foo';
 proacl | ?column? 
+--
 {} | f
(1 row)

postgres=> select (aclexplode(proacl)).* from pg_proc where proname = 'foo';
 grantor | grantee | privilege_type | is_grantable 
-+-++--
(0 rows)

At this point it would be wrong to conclude that EXECUTE privileges are
available to PUBLIC, or indeed to anybody except a superuser.

postgres=> select foo();
ERROR:  permission denied for function foo

However, this representation is still not telling the whole truth,
because the owner always has full grant options; alice can't revoke her
own grant options.  (They're gone according to the ACL representation,
but Postgres will behave as though she still has them.)  So this is not
a dead-end state --- alice can still re-grant permissions if she
chooses.

postgres=> grant execute on function foo() to public;
GRANT
postgres=> select foo();
 foo 
-
   1
(1 row)

postgres=> select proacl, proacl is null from pg_proc where proname = 'foo';
   proacl   | ?column? 
+--
 {=X/alice} | f
(1 row)

postgres=> select (aclexplode(proacl)).* from pg_proc where proname = 'foo';
 grantor | grantee | privilege_type | is_grantable 
-+-++--
   95618 |   0 | EXECUTE| f
(1 row)

At this point alice is getting her permissions to call her own function
via PUBLIC, not directly.

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] Discerning when functions had execute revoked from public

[Jerry Sievers]

Todd, there is no auditing that will answer the question *when* (in
terms of when change took place), strictly speaking.

But anyway, have a look at the functions acl* and inparticular
aclexplode as seen below.

If I understand correctly how it works, public execute is granted in
the default case of no rows returned as seen in the first case AND
when we get a row with  grantee=0 and privilege='execute'.


sj$ psql -ef s
Pager usage is off.
set datestyle to iso,ymd;
SET
set client_min_messages to warning;
SET
begin;
BEGIN
create function foo() returns int as $$select 1$$ language sql;
CREATE FUNCTION
select (aclexplode(proacl)).* from pg_proc where proname = 'foo';
 grantor | grantee | privilege_type | is_grantable 
-+-++--
(0 rows)

revoke execute on function foo() from public;
REVOKE
select (aclexplode(proacl)).* from pg_proc where proname = 'foo';
 grantor | grantee | privilege_type | is_grantable 
-+-++--
   16385 |   16385 | EXECUTE| f
(1 row)

grant execute on function foo() to public;
GRANT
select (aclexplode(proacl)).* from pg_proc where proname = 'foo';
 grantor | grantee | privilege_type | is_grantable 
-+-++--
   16385 |   16385 | EXECUTE| f
   16385 |   0 | EXECUTE| f
(2 rows)

It may be the case that other acl* functions can answer this question
even more easily and/or infvormation_schema views will give useful
output as well.

HTH



Todd Kover  writes:

> I am trying to write something that will enumerate grants/revokes on
> functions to make sure they are adjusted properly after said function is
> drop/recreated, should that happen.  This will also be used to validate
> that permissions are what they should be.
>
> According to:
>
> http://www.postgresql.org/docs/9.2/static/sql-createfunction.html
>
>  } Another point to keep in mind is that by default, execute privilege
>  } is granted to PUBLIC for newly created functions (see GRANT for
>  } more information). Frequently you will wish to restrict use of a
>  } security definer function to only some users. To do that, you must
>  } revoke the default PUBLIC privileges and then grant execute privilege
>  } selectively. To avoid having a window where the new function is
>  } accessible to all, create it and set the privileges within a single
>  } transaction.
>
> This revocation from public happens in our environment.  Trouble is, I
> can not find where an indiciation that execute has been revoked from
> public in pg_catalog.pg_proc (or any other table for that matter).  Is
> there a way to find this somewhere in the catalog?
>
> Apologies if this should be obvious.  I'm sure I will find it as soon as
> I hit send.  :-)
>
> thanks,
> -Todd
>
>
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800


-- 
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] query by partial timestamp

[Tom Lane]

Gavan Schneider  writes:
> From my perspective there are at least three ways to attack 
> this problem:

> (I have not tested these, so apologies for the stupid syntax errors.)

> 1.  SELECT ... WHERE 2011 = extract(YEAR FROM col_of_type_timestamp);

> 2.  SELECT ... WHERE
>  '2011-01-01'::TIMESTAMP <= col_of_type_timestamp
>  ANDcol_of_type_timestamp <= 
> '2011-12-31'::TIMESTAMP;

> 3.  SELECT ... WHERE
>  (col_of_type_timestamp, col_of_type_timestamp) OVERLAPS
>  (DATE '2011-01-01', DATE '2012-01-01');

> Is this the full list?

Another possibility is date_trunc, viz

4. SELECT ... WHERE date_trunc(col_of_type_timestamp, 'year') = '2011-01-01'

You could also use BETWEEN, but that's just syntactic sugar for method 2.

Note that in both methods 2 and 3 it's easy to get the edge cases wrong;
in particular I think your version of method 2 gives the wrong answer
for later-than-midnight times on 2011-12-31, while #3 might (not sure)
give the wrong answer for exactly midnight on 2012-01-01.  These things
are fixable of course with a bit of care.  Personally I'd go with

col >= '2011-01-01' AND col < '2012-01-01'

> So... generalizing the original question: which approach would 
> yield the best performance and/or compliance with SQL standards?

> I note Steve Crawford has (strongly) hinted that direct date 
> comparison is more likely to use an index (when available) so I 
> suspect this is the way to go, but would an index based on 
> extract(YEAR...) negate this difference?

Method 3 is not indexable at all and is unlikely to become so --- the
SQL standard's definition of OVERLAPS is squirrely enough that people
haven't bothered to think about optimizing it.  Method 2 works well with
a plain btree index on the timestamp column.  You can get method 1 to be
indexed if you create a functional index on "extract(year from col)";
but since the index would have pretty much no other use than answering
this exact type of query, that's not a very attractive alternative.
Method 4 is like method 1 --- you'd need a specialized index.

Note that in any case an index is not going to be helpful if the query
would need to fetch more than a few percent of the table.  The OP didn't
suggest how many years his data covers, but it's quite possible that
pulling a full year's worth of data will read enough of the table that
there's no point in worrying about whether an index could be used
anyway.

Another thing to think about is whether you'll have related sorts of
queries that aren't about full years --- maybe sometimes you need a
month's worth of data, for example.  The BETWEEN-style query and a btree
index will adapt easily to non-year intervals, while the EXTRACT
approach will not, and date_trunc is rather limited as well.

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] query by partial timestamp

[Gavan Schneider]

On Tuesday, January 8, 2013 at 09:26, Raymond O'Donnell wrote:


On 08/01/2013 22:19, Kirk Wythers wrote:

I have a column of type TIMESTAMP, I'd like to query all records from
2011. If it were text I could use a partial such as:

WHERE text ~ '2011'

There must be a simple way to pull the year part out of a timestamp
format. Thanks in advance.


You want the extract() function.

From my perspective there are at least three ways to attack 
this problem:


(I have not tested these, so apologies for the stupid syntax errors.)

1.  SELECT ... WHERE 2011 = extract(YEAR FROM col_of_type_timestamp);

2.  SELECT ... WHERE
'2011-01-01'::TIMESTAMP <= col_of_type_timestamp
ANDcol_of_type_timestamp <= 
'2011-12-31'::TIMESTAMP;

3.  SELECT ... WHERE
(col_of_type_timestamp, col_of_type_timestamp) OVERLAPS
(DATE '2011-01-01', DATE '2012-01-01');

Is this the full list?

So... generalizing the original question: which approach would 
yield the best performance and/or compliance with SQL standards?


I note Steve Crawford has (strongly) hinted that direct date 
comparison is more likely to use an index (when available) so I 
suspect this is the way to go, but would an index based on 
extract(YEAR...) negate this difference?


Regards
Gavan Schneider



--
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] Discerning when functions had execute revoked from public

[Tom Lane]

Todd Kover  writes:
> This revocation from public happens in our environment.  Trouble is, I
> can not find where an indiciation that execute has been revoked from
> public in pg_catalog.pg_proc (or any other table for that matter).  Is
> there a way to find this somewhere in the catalog?

pg_proc.proacl is what to look at.  If that is NULL then it means the
function has default privileges (viz, public execute).  If it's not NULL
then the function has whatever privileges it says.

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] query by partial timestamp

[Steve Crawford]

On 01/08/2013 02:19 PM, Kirk Wythers wrote:

I have a column of type TIMESTAMP, I'd like to query all records from 2011. If 
it were text I could use a partial such as:

WHERE
text ~ '2011'

There must be a simple way to pull the year part out of a timestamp format. 
Thanks in advance.





As others pointed out, you can do this with "extract(...).

BUT, if your timestamp column is indexed (and if the index will 
constrain your records to a sufficiently small subset of the table that 
use of indexes is warranted) you may be better off using date 
comparisons. I doubt the planner will use the indexes otherwise.


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] query by partial timestamp

[Rob Sargent]

On 01/08/2013 03:39 PM, Raymond O'Donnell wrote:

On 08/01/2013 22:26, Raymond O'Donnell wrote:

On 08/01/2013 22:19, Kirk Wythers wrote:

I have a column of type TIMESTAMP, I'd like to query all records from
2011. If it were text I could use a partial such as:

WHERE text ~ '2011'

There must be a simple way to pull the year part out of a timestamp
format. Thanks in advance.


You want the extract() function.


Sorry, meant to include the reference:

http://www.postgresql.org/docs/9.2/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

Ray.


Ray, I thought you were simply encouraging the OP to learn to fish :)


--
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] query by partial timestamp

[Raymond O'Donnell]

On 08/01/2013 22:26, Raymond O'Donnell wrote:
> On 08/01/2013 22:19, Kirk Wythers wrote:
>> I have a column of type TIMESTAMP, I'd like to query all records from
>> 2011. If it were text I could use a partial such as:
>>
>> WHERE text ~ '2011'
>>
>> There must be a simple way to pull the year part out of a timestamp
>> format. Thanks in advance.
> 
> You want the extract() function.

Sorry, meant to include the reference:

http://www.postgresql.org/docs/9.2/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

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] query by partial timestamp

[Raymond O'Donnell]

On 08/01/2013 22:19, Kirk Wythers wrote:
> I have a column of type TIMESTAMP, I'd like to query all records from
> 2011. If it were text I could use a partial such as:
> 
> WHERE text ~ '2011'
> 
> There must be a simple way to pull the year part out of a timestamp
> format. Thanks in advance.

You want the extract() function.

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

[GENERAL] query by partial timestamp

[Kirk Wythers]

I have a column of type TIMESTAMP, I'd like to query all records from 2011. If 
it were text I could use a partial such as:

WHERE
text ~ '2011'

There must be a simple way to pull the year part out of a timestamp format. 
Thanks in advance.




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

[GENERAL] Discerning when functions had execute revoked from public

[Todd Kover]

I am trying to write something that will enumerate grants/revokes on
functions to make sure they are adjusted properly after said function is
drop/recreated, should that happen.  This will also be used to validate
that permissions are what they should be.

According to:

http://www.postgresql.org/docs/9.2/static/sql-createfunction.html

 } Another point to keep in mind is that by default, execute privilege
 } is granted to PUBLIC for newly created functions (see GRANT for
 } more information). Frequently you will wish to restrict use of a
 } security definer function to only some users. To do that, you must
 } revoke the default PUBLIC privileges and then grant execute privilege
 } selectively. To avoid having a window where the new function is
 } accessible to all, create it and set the privileges within a single
 } transaction.

This revocation from public happens in our environment.  Trouble is, I
can not find where an indiciation that execute has been revoked from
public in pg_catalog.pg_proc (or any other table for that matter).  Is
there a way to find this somewhere in the catalog?

Apologies if this should be obvious.  I'm sure I will find it as soon as
I hit send.  :-)

thanks,
-Todd


-- 
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] pgxs "missing magic block ... PG_MODULE_MAGIC" with "MODULE_big"

[David Fuhry]

Thank you Tom, you are correct. Adding the line:

OBJS = test_cfunc.o

to the Makefile resolved the problem.

-Dave


On Tue, Jan 8, 2013 at 12:55 PM, Tom Lane  wrote:

> David Fuhry  writes:
> > Hi, I'm trying to add a C-Language function but getting the following
> error:
> > $ psql -f test_cfunc.sql
> > psql:test_cfunc.sql:3: ERROR:  incompatible library
> > "/usr/local/pgsql/lib/test_cfunc.so": missing magic block
> > HINT:  Extension libraries are required to use the PG_MODULE_MAGIC macro.
>
> > I am calling PG_MODULE_MAGIC in my c file. Minimal Makefile, .c, and .sql
> > files are attached. In the Makefile, if I change "MODULE_big" to
> "MODULES",
> > it works. However, I want to use "MODULE_big" because I need to later
> link
> > to a shared library using SHLIB_LINK, and "MODULES" doesn't support
> > SHLIB_LINK as per
> > http://archives.postgresql.org/pgsql-hackers/2012-03/msg00537.php
>
> I think you're missing the OBJS line, with the result that the .so ends
> up empty.
>
> regards, tom lane
>

Re: [GENERAL] [Solved] Corrupt indexes on slave when using pg_bulkload on master

[Tom Lane]

James Cowell  writes:
> I enabled archive mode (which I didn't care about before as the database only 
> holds 36 hours of data) and the indexes seem to replicate over fine.  I 
> suppose the problem here is lack of documentation, but at least the code is 
> well commented :)

> It looks like pg_bulkload works just fine with replication so long as it's 
> set up right.

Hm.  I had thought we had interlocks in there to prevent turning on
replication unless the WAL level was sufficiently high.  It sounds like
you managed to dodge that sanity check.  Could you be more specific about
what your replication configuration looks like?

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] pgxs "missing magic block ... PG_MODULE_MAGIC" with "MODULE_big"

[Tom Lane]

David Fuhry  writes:
> Hi, I'm trying to add a C-Language function but getting the following error:
> $ psql -f test_cfunc.sql
> psql:test_cfunc.sql:3: ERROR:  incompatible library
> "/usr/local/pgsql/lib/test_cfunc.so": missing magic block
> HINT:  Extension libraries are required to use the PG_MODULE_MAGIC macro.

> I am calling PG_MODULE_MAGIC in my c file. Minimal Makefile, .c, and .sql
> files are attached. In the Makefile, if I change "MODULE_big" to "MODULES",
> it works. However, I want to use "MODULE_big" because I need to later link
> to a shared library using SHLIB_LINK, and "MODULES" doesn't support
> SHLIB_LINK as per
> http://archives.postgresql.org/pgsql-hackers/2012-03/msg00537.php

I think you're missing the OBJS line, with the result that the .so ends
up empty.

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] pgxs "missing magic block ... PG_MODULE_MAGIC" with "MODULE_big"

[David Fuhry]

Forgot to mention, I am on 9.2.1. "SELECT version();" reports:

PostgreSQL 9.2.1 on x86_64-unknown-linux-gnu, compiled by gcc (Debian
4.4.5-8) 4.4.5, 64-bit

-Dave



On Tue, Jan 8, 2013 at 12:30 PM, David Fuhry  wrote:

> Hi, I'm trying to add a C-Language function but getting the following
> error:
>
> $ psql -f test_cfunc.sql
> psql:test_cfunc.sql:3: ERROR:  incompatible library
> "/usr/local/pgsql/lib/test_cfunc.so": missing magic block
> HINT:  Extension libraries are required to use the PG_MODULE_MAGIC macro.
>
> I am calling PG_MODULE_MAGIC in my c file. Minimal Makefile, .c, and .sql
> files are attached. In the Makefile, if I change "MODULE_big" to "MODULES",
> it works. However, I want to use "MODULE_big" because I need to later link
> to a shared library using SHLIB_LINK, and "MODULES" doesn't support
> SHLIB_LINK as per
> http://archives.postgresql.org/pgsql-hackers/2012-03/msg00537.php
>
> Any suggestions on how I can get PG_MODULE_MAGIC to work with "MODULE_big"
> (or link a shared library when using "MODULES")?
>
> Thanks,
>
> Dave
>
>
>

[GENERAL] pgxs "missing magic block ... PG_MODULE_MAGIC" with "MODULE_big"

[David Fuhry]

Hi, I'm trying to add a C-Language function but getting the following error:

$ psql -f test_cfunc.sql
psql:test_cfunc.sql:3: ERROR:  incompatible library
"/usr/local/pgsql/lib/test_cfunc.so": missing magic block
HINT:  Extension libraries are required to use the PG_MODULE_MAGIC macro.

I am calling PG_MODULE_MAGIC in my c file. Minimal Makefile, .c, and .sql
files are attached. In the Makefile, if I change "MODULE_big" to "MODULES",
it works. However, I want to use "MODULE_big" because I need to later link
to a shared library using SHLIB_LINK, and "MODULES" doesn't support
SHLIB_LINK as per
http://archives.postgresql.org/pgsql-hackers/2012-03/msg00537.php

Any suggestions on how I can get PG_MODULE_MAGIC to work with "MODULE_big"
(or link a shared library when using "MODULES")?

Thanks,

Dave


Makefile
Description: Binary data
#include 
#include 
#include "postgres.h"
#include "fmgr.h"

PG_MODULE_MAGIC;

extern Datum test_cfunc(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(test_cfunc);
Datum test_cfunc(PG_FUNCTION_ARGS)
{
  PG_RETURN_NULL();
}


test_cfunc.sql
Description: Binary data

-- 
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] [postgis-users] Query with LIMIT but as random result set?

[Stefan Keller]

Hi,

Thanks a lot to Adrian, Nicolas and Brooks.
"... ORDER BY random() LIMIT 10;" works ok.

But with the following option it gets more tricky assume:
> And as an option the (limited) resultset should be spatially
> distributed (not clustered).

I'm thinking about some radial spatial distribution function.

Yours, Stefan


2013/1/8 Brooks Kehler :
> this should work -
>
> order by random() limit 10;
>
>
>
> On Tue, Jan 8, 2013 at 10:20 AM, Stefan Keller  wrote:
>>
>> Hi
>>
>> I have a query like this
>>
>>   SELECT ST_AsText(way) geom, name AS label
>>   FROM osm_point
>>   LIMIT 10;
>>
>> When I repeatedly do this, the result set will be always the same.
>> I have observed this only empirically and I know that the ordering of
>> the result set is undefined without ORDER BY.
>> There are two indexes involved, one geospatial for way and one for name.
>>
>> My question is: Does someone have an idea on how to randomize the
>> result set on every consecutive query?
>> And as an option the (limited) resultset should be spatially
>> distributed (not clustered).
>>
>> Yours, Stefan
>> ___
>> postgis-users mailing list
>> postgis-us...@lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
>
> ___
> postgis-users mailing list
> postgis-us...@lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>


-- 
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] Picking the first of an order in an aggregate query

[Vincent Veyron]

Le lundi 31 décembre 2012 à 20:55 -0500, Robert James a écrit :
> On 12/31/12, François Beausoleil  wrote:
> >
> > Le 2012-12-31 à 15:38, Robert James a écrit :
> >
> >> DISTINCT is a very simple solution!
> >> But I have one problem: In addition to the FIRST fields, I also do
> >> want some aggregate functions.  More accurately, it would be:
> >>
> >> SELECT grouping_field, FIRST(field_a), FIRST(field_b), SUM(field_x),
> >> MAX(field_y)
> >> ...
> >>
> >> How should I do that? Should I do two queries with a join on the
> >> grouping field? Or is there a more direct way?
> >
> > WINDOW functions can help you:
> >
> > SELECT
> > grouping_field
> >   , first_value(field_a) OVER (ORDER BY ...)
> >   , first_value(field_b) OVER (ORDER BY ...)
> >   , sum(field_x) OVER ()
> >   , max(field_y) OVER ()
> > FROM ...
> >
> > The empty OVER clauses will make the sum / max work over the full result
> > set, and not a subset. I really recommend reading the window functions
> > section on the site.
> >
> 
> I see.  Will the optimizer know enough to not repeat the work for each
> first_value I do? Or am I better off using a JOIN of some sort?
> 
> 

You probably can check with explain analyze; if not, a CTE (common table
expression) might help; something like :

with t1 as (
select grouping_field, sum(field_x) as sum_x, max(field_y) as max_y
FROM ... 
group by grouping_field
)
SELECT grouping_field, 
first_value(field_a) OVER (Partition by grouping_field ORDER BY ...),
first_value(field_b) OVER (Partition by grouping_field ORDER BY ...),
t1.sum_x,
t1.max_y
FROM ... INNER JOIN t1 using (grouping_field)

'Partition by grouping_field' may or may not be necessary in your case,
depending on what you want; see :

http://www.postgresql.org/docs/current/static/tutorial-window.html

-- 
Vincent Veyron
http://marica.fr
Logiciel pour département juridique



-- 
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] [Solved] Corrupt indexes on slave when using pg_bulkload on master

[James Cowell]

I seem to have solved my problem, out of frustration I downloaded the source to 
see what I could work out (although it's a good while since I did C) and I 
found these comments in nbtsort-9.1.c:
 
 * Formerly the index pages being built were kept in shared buffers, but
 * that is of no value (since other backends have no interest in them yet)
 * and it created locking problems for CHECKPOINT, because the upper-level
 * pages were held exclusive-locked for long periods.  Now we just build
 * the pages in local memory and smgrwrite or smgrextend them as we finish
 * them.  They will need to be re-read into shared buffers on first use after
 * the build finishes.
 *
 * Since the index will never be used unless it is completely built,
 * from a crash-recovery point of view there is no need to WAL-log the
 * steps of the build. After completing the index build, we can just sync
 * the whole file to disk using smgrimmedsync() before exiting this module.
 * This can be seen to be sufficient for crash recovery by considering that
 * it's effectively equivalent to what would happen if a CHECKPOINT occurred
 * just after the index build. However, it is clearly not sufficient if the
 * DBA is using the WAL log for PITR or replication purposes, since another
 * machine would not be able to reconstruct the index from WAL.  Therefore,
 * we log the completed index pages to WAL if and only if WAL archiving is
 * active.
 
I enabled archive mode (which I didn't care about before as the database only 
holds 36 hours of data) and the indexes seem to replicate over fine.  I suppose 
the problem here is lack of documentation, but at least the code is well 
commented :)

It looks like pg_bulkload works just fine with replication so long as it's set 
up right.
 
Cheers,
 
James
  


 From: James Cowell 
To: James Cowell ; "pgsql-general@postgresql.org" 
 
Cc: Jeff Janes  
Sent: Thursday, 3 January 2013, 16:12
Subject: Re: [GENERAL] Corrupt indexes on slave when using pg_bulkload on master
  

Hi Jeff (and group)
 
Was the reproduction information sufficient?
 
Do I need to submit this officially as a bug or something?
 
At the moment I'm considering rebuilding my cluster with 9.0 to see if that 
works and if not then reverting back to 9.1 but loading each DB seperately.  I 
would really like to understand why a load of 10 sequential rows with 
pg_bulkload produces a corrupt index on node 2 though, it just doesn't make 
sense to me.
 
Thanks and Happy New Year!
 
James
 


 From: James Cowell 
To: "pgsql-general@postgresql.org"  
Cc: Jeff Janes  
Sent: Wednesday, 19 December 2012, 13:11
Subject: Re: [GENERAL] Corrupt indexes on slave when using pg_bulkload on master
  

Actually, scratch that.  The difference in behaviour seems to be on the 
optimiser which now table scans the 10 row table (which I guess it should 
always have done really) rather than use the index as it was in 9.1.6.
 
The same index corruption occurs, so the same reproduction case stands, it just 
needs a "set enable_seqscan=false" prior to running the selects on the slave.
 
Cheers,
 
James
 


 From: James Cowell 
To: Jeff Janes  
Cc: "pgsql-general@postgresql.org"  
Sent: Monday, 17 December 2012, 20:42
Subject: Re: [GENERAL] Corrupt indexes on slave when using pg_bulkload on master
  

I saw that 9.1.7 was out and it had another bugfix in for WAL playback.
 
I installed it and ran my reproduction case and it no longer corrupts the index.

I reindexed the database I load into and did a full data load and the indexes 
still corrupt on the slave.

It does not appear to be related to constraint violation as one table is new 
rows only.

I will try and put together a new reproduction case this week.

Cheers,

James




 From: James Cowell 
To: Jeff Janes  
Cc: "pgsql-general@postgresql.org"  
Sent: Thursday, 13 December 2012, 12:26
Subject: Re: [GENERAL] Corrupt indexes on slave when using pg_bulkload on master
  

Hi Jeff,
 
Thanks again for your reply.
 
>If there are no constraint violations, do you still see the problem?
 
Yes, I've stripped it down to an empty table with a 10 row load and the pk 
index on the secondary node still corrupts.
 
> Were there any older version on which it worked? 
 
I'm afraid I started on 9.1.5, I upgraded to 9.1.6 when I had the initial 
problem due to the bugfix in the changelog to do with corrupt indexes on the 
secondary node but it hasn't resolved the issue.
 
> Can you post a minimal schema and control file to reproduce the problem?
 
I've attached a text file with details for table, load config file etc, is that 
everything you would need?
 
Cheers,
 
James
 


 From: Jeff Janes 
To: James Cowell  
Cc: "pgsql-general@postgresql.org"  
Sent: Monday, 10 December 2012, 16:53
Subject: Re: [GENERAL] Corrupt indexes on slave when using pg_bulkload on master
  
On Wed,

Re: [GENERAL] Using composite types within PLPGSQL Function

[Leif Biberg Kristensen]

 Tirsdag 8. januar 2013 16.10.03 skrev Graeme Hinchliffe :
> My example code is :
> 
> CREATE TYPE testtype AS (
> a INTEGER,
> b INTEGER
> );
> 
> CREATE OR REPLACE FUNCTION test() RETURNS INTEGER AS $$
> DECLARE
>   x testtype;
> BEGIN
> (x).a:=1;
> RETURN 1;
> END
> $$ LANGUAGE plpgsql;
> 
> This throws up syntax errors for the (x).a:=1; line.. I have also tried
> SELECT INTO (x).a 1;
> 
> Any help much appreciated, version of PostgreSQL is 8.4 under Debian.

It should work with

x.a := 1;

without the parentheses. See http://solumslekt.org/blog/?p=91 for an example 
of composite types and functions.

regards, Leif


-- 
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] Query with LIMIT but as random result set?

[Adrian Klaver]

On 01/08/2013 07:20 AM, Stefan Keller wrote:

Hi

I have a query like this

   SELECT ST_AsText(way) geom, name AS label
   FROM osm_point
   LIMIT 10;

When I repeatedly do this, the result set will be always the same.
I have observed this only empirically and I know that the ordering of
the result set is undefined without ORDER BY.
There are two indexes involved, one geospatial for way and one for name.

My question is: Does someone have an idea on how to randomize the
result set on every consecutive query?
And as an option the (limited) resultset should be spatially
distributed (not clustered).



SELECT ST_AsText(way) geom, name AS label
FROM osm_point ORDER BY random()
LIMIT 10;



Yours, Stefan





--
Adrian Klaver
adrian.kla...@gmail.com


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

[GENERAL] Query with LIMIT but as random result set?

[Stefan Keller]

Hi

I have a query like this

  SELECT ST_AsText(way) geom, name AS label
  FROM osm_point
  LIMIT 10;

When I repeatedly do this, the result set will be always the same.
I have observed this only empirically and I know that the ordering of
the result set is undefined without ORDER BY.
There are two indexes involved, one geospatial for way and one for name.

My question is: Does someone have an idea on how to randomize the
result set on every consecutive query?
And as an option the (limited) resultset should be spatially
distributed (not clustered).

Yours, Stefan


-- 
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] Database Design: Maintain Audit Trail of Changes

[Wolfgang Keller]

>For several reasons (including operational and legal) once data are
> entered in a table they cannot be changed or deleted without an audit
> trail of the change, when it occurred, who made the change, and the
> reason for it.

Besides the need for storing additional information that the user who
modifies the data may be required to enter manually, couldn't the
function of simply tracking what was inserted, updated and deleted be
implemented by an "eternal" transaction log that never gets purged, but
regularly archived in partitions, PGP-signed (with a qualified key) if
required to prove authenticity?

After all, PostgreSQL maintains this automatically anyway, so why not
use it?

TIA,

Sincerely,

Wolfgang


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

[GENERAL] Using composite types within PLPGSQL Function

[Graeme Hinchliffe]

Hi,
I am trying to use a composite type within a function, I have 
tried SELECT INTO and direct assignment to set the value of a single element 
within the composite variable with no joy.  I hope this is simply a case of my 
not knowing the correct syntax?

My example code is :

CREATE TYPE testtype AS (
a INTEGER,
b INTEGER
);

CREATE OR REPLACE FUNCTION test() RETURNS INTEGER AS $$
DECLARE
  x testtype;
BEGIN
(x).a:=1;
RETURN 1;
END
$$ LANGUAGE plpgsql;

This throws up syntax errors for the (x).a:=1; line.. I have also tried SELECT 
INTO (x).a 1;

Any help much appreciated, version of PostgreSQL is 8.4 under Debian.

Thanks

Graeme
--
Graeme Hinchliffe
Senior Systems Designer, Zen Internet
T: 0845 058 9000
F: 0845 058 9005
W: zen.co.uk

BT have increased their monthly phone line rental to ?15.45 per month. Our 
remains at ?11.22. Switch now : 
http://www.zen.co.uk/home-office/voice/phone-services.aspx

This message is private and confidential. If you have received this message in 
error, please notify us and remove it from your system.

Zen Internet Limited may monitor email traffic data to manage billing, to 
handle customer enquiries and for the prevention and detection of fraud. We may 
also monitor the content of emails sent to and/or from Zen Internet Limited for 
the purposes of security, staff training and to monitor quality of service.

Zen Internet Limited is registered in England and Wales, Sandbrook Park, 
Sandbrook Way, Rochdale, OL11 1RY Company No. 03101568 VAT Reg No. 686 0495 01

[GENERAL] Questions about 9.2 unique constraints

[a...@hsk.hk]

Hi,

In PostgreSQL 9.0.x we must define a constraint as DEFERRABLE on the "create 
table", we cannot define DEFERRABLE on "create table as select", how is this 
restriction in 9.2 now?

Also, in 9.2 can deferrable uniqueness be mixed with Foreign keys?

Thanks



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

[GENERAL] When to run Vacuum in postgres 9.0.4

[Anjali Arora]

Hi all,

I have a large dataset and I have crawled it two and three times; hence 
reltuples has increased. So I need some way to find out vacuum is needed on the 
system. 

Please help me in finding out some parameter or statistics which will tell me 
vacuum is required on the database.

Regards,
Anjali

Re: [GENERAL] PostgreSQL 9.2 and PGBOUNCER

[a...@hsk.hk]

On 8 Jan 2013, at 5:04 PM, Birta Levente wrote:

> The pooler is between client and postgresql server. So, if you want to 
> connect through the pooler you need to connect on 6543.
> 
> But I think you really need to read some documentation about pgbouncer.

Thanks, I can connect to any testing DB via pgbouncer now.

Regards

Re: [GENERAL] Database Design: Maintain Audit Trail of Changes

[Stevo Slavić]

In Java world, for this purpose I tend to use JPA/Hibernate with Envers
http://www.jboss.org/envers - db vendor agnostic solution.

Kind regards,
Stevo Slavic.


On Tue, Jan 8, 2013 at 6:32 AM, Craig Ringer  wrote:

> On 4/01/2013 12:09 AM, Adrian Klaver wrote:
> > On 01/03/2013 07:38 AM, Rich Shepard wrote:
> >
> >>
> >>The middleware of the application needs to check this table when data
> >> are
> >> to be viewed in the UI and present only the current row contents. A
> >> separate
> >> view would display a history of changes for that row.
> >>
> >>All thoughts, suggestions, and recommendations based on your
> >> expertise and
> >> experience will be most welcome.
> >
> > As a matter of course I include fields to record the timestamp and
> > user for insert of records and last update of record on my tables.
> >
> > For a relatively simple solution see this blog post I put up this summer:
> >
> > http://aklaver.org/wordpress/2012/06/23/postgres-and-hstore/
> >
> > I have since expanded that to include updates by using TG_OP to
> > determine the operation being done on the table.
> >
> > There is also pg_audit
> > :
> > https://github.com/jcasanov/pg_audit
>
> Additionally, this is an audit trigger I was using internally and
> generalized:
>
> http://wiki.postgresql.org/wiki/Audit_trigger_91plus
>
> --
> Craig Ringer http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>
>
>
> --
> 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] lc_time not working? Solved.

[J. Hondius]

Thanks very much Tom and Adrian.
Solved. I will RTFM better next time.

Thanks again, Joek

Adrian Klaver schreef:

On 01/07/2013 07:56 AM, Tom Lane wrote:

Adrian Klaver  writes:

On 01/07/2013 06:45 AM, J. Hondius wrote:

I'm not getting my PostgreSQL 9.2 to return the correct lc_time



openwave001=# select set_config('lc_time', 'nl_NL', true);



When you use true it only applies for that transaction.


But he has the same setting in postgresql.conf anyway.  I think the real
mistake is here:


openwave001=# select to_char(now(),'dd month day ');


To get localized month/day names you need to add the TM prefix to the
field, ie this should be

select to_char(now(),'dd tmmonth tmday ');


Aargh, missed that.



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] PostgreSQL 9.2 and PGBOUNCER

[Birta Levente]

!! Please do not top post !!

On 08/01/2013 10:21, a...@hsk.hk wrote:

Hi,

Thanks for your reply.

Below is the pgbouncer.ini
===
[databases]
;  use db:postgres for connection testing
postgres = port=5432 dbname=postgres


As I said before, with this you can only connect database postgres.

Maybe you want:
* = port=5432
With these you can connect all databases in your cluster.



[pgbouncer]
listen_port = 6543
listen_addr = 127.0.0.1

watch this: maybe you want to connect from the outside: listen_addr = *


admin_users = postgres
auth_type = md5
auth_file = users.txt
I don't know if on ubuntu need or not specify the full path to the 
users.txt.



server_reset_query = DISCARD ALL;
ignore_startup_parameters = application_name
logfile = pgbouncer.log
pidfile = pgbouncer.pid
pool_mode = session
default_pool_size = 20
log_pooler_errors = 0
===

If I want to test the connection from my application to DB postgres via 
pgbouncer, which port should I use, post 6543 or port 5432?


The pooler is between client and postgresql server. So, if you want to 
connect through the pooler you need to connect on 6543.


But I think you really need to read some documentation about pgbouncer.





Thanks




On 8 Jan 2013, at 3:13 PM, Birta Levente wrote:


On 08/01/2013 08:40, a...@hsk.hk wrote:

Hi,


My PostgreSQL is 9.2.1 in Ubuntu 12.04, I need to set up a connection
pool by using pgbouncer.


I used "apt-get install pgbouncer", after configuring it, I can now
connect to pgbouncer and can use all pgbouncer SHOW commands,  however:

Q1) Is  version "1.4.2/bouncer" the right one for PostgreSQL 9.2?
$ psql -U postgres -p 6543 pgbouncer
psql.bin (9.2.1, server 1.4.2/bouncer)
WARNING: psql.bin version 9.2, server version 1.4.
Some psql features might not work.
Type "help" for help.
No entry for terminal type "xterm-color";
using dumb terminal settings.



I think the best is the latest: v1.5.4




Q2) if I try the general psql commands, I got errors
for example:
$ psql -U postgres -p 6543 pgbouncer
pgbouncer=# \l
ERROR:  invalid command 'SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
pg_catalog.array_to_string(d.datacl, '\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;', use SHOW HELP;


If you connect to the pgbouncer, which is special, not really a database, you 
only can show pgbouncer stats and change pgbouncer settings

# SHOW help;
show you available commands.




Q3) I can connect to pgbouncer by using "psql -U postgres -p 6543
pgbouncer", however if I try to use "-d postgres", I got error:
$ psql -U postgres -p 6543 pgbouncer -d postgres
psql.bin: warning: extra command-line argument "pgbouncer" ignored
psql.bin: ERROR:  no working server connection


You need to show us pgbouncer.ini.
And maybe read this before
http://get.enterprisedb.com/docs/Tutorial_All_PPSS_pgBouncer.pdf




Q4) Which port should I use in my application in order to connect to
PostgreSQL via pgbouncer, port 6543 or port 5432?
the port value in pgbounce.ini:
postgres = port=5432 dbname=postgres
listen_port = 6543
the port value in postgresql.conf:
port=5432


With this you make connection pooling only for the database named postgres. 
This is what you really want?

Read this minihowto too:
http://filip.rembialkowski.net/pgbouncer-mini-howto-benchmark/





Please help!
Thanks







--
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] PostgreSQL 9.2 and PGBOUNCER

[a...@hsk.hk]

Hi,

Thanks for your reply.

Below is the pgbouncer.ini
===
[databases]
;  use db:postgres for connection testing
postgres = port=5432 dbname=postgres

[pgbouncer]
listen_port = 6543
listen_addr = 127.0.0.1
admin_users = postgres
auth_type = md5
auth_file = users.txt
server_reset_query = DISCARD ALL;
ignore_startup_parameters = application_name
logfile = pgbouncer.log
pidfile = pgbouncer.pid
pool_mode = session
default_pool_size = 20
log_pooler_errors = 0
===

If I want to test the connection from my application to DB postgres via 
pgbouncer, which port should I use, post 6543 or port 5432?

Thanks




On 8 Jan 2013, at 3:13 PM, Birta Levente wrote:

> On 08/01/2013 08:40, a...@hsk.hk wrote:
>> Hi,
>> 
>> 
>> My PostgreSQL is 9.2.1 in Ubuntu 12.04, I need to set up a connection
>> pool by using pgbouncer.
>> 
>> 
>> I used "apt-get install pgbouncer", after configuring it, I can now
>> connect to pgbouncer and can use all pgbouncer SHOW commands,  however:
>> 
>> Q1) Is  version "1.4.2/bouncer" the right one for PostgreSQL 9.2?
>> $ psql -U postgres -p 6543 pgbouncer
>> psql.bin (9.2.1, server 1.4.2/bouncer)
>> WARNING: psql.bin version 9.2, server version 1.4.
>> Some psql features might not work.
>> Type "help" for help.
>> No entry for terminal type "xterm-color";
>> using dumb terminal settings.
>> 
> 
> I think the best is the latest: v1.5.4
> 
> 
>> 
>> Q2) if I try the general psql commands, I got errors
>> for example:
>> $ psql -U postgres -p 6543 pgbouncer
>> pgbouncer=# \l
>> ERROR:  invalid command 'SELECT d.datname as "Name",
>> pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
>> pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
>> pg_catalog.array_to_string(d.datacl, '\n') AS "Access privileges"
>> FROM pg_catalog.pg_database d
>> ORDER BY 1;', use SHOW HELP;
> 
> If you connect to the pgbouncer, which is special, not really a database, you 
> only can show pgbouncer stats and change pgbouncer settings
> 
> # SHOW help;
> show you available commands.
> 
>> 
>> 
>> Q3) I can connect to pgbouncer by using "psql -U postgres -p 6543
>> pgbouncer", however if I try to use "-d postgres", I got error:
>> $ psql -U postgres -p 6543 pgbouncer -d postgres
>> psql.bin: warning: extra command-line argument "pgbouncer" ignored
>> psql.bin: ERROR:  no working server connection
> 
> You need to show us pgbouncer.ini.
> And maybe read this before
> http://get.enterprisedb.com/docs/Tutorial_All_PPSS_pgBouncer.pdf
> 
> 
>> 
>> Q4) Which port should I use in my application in order to connect to
>> PostgreSQL via pgbouncer, port 6543 or port 5432?
>> the port value in pgbounce.ini:
>> postgres = port=5432 dbname=postgres
>> listen_port = 6543
>> the port value in postgresql.conf:
>> port=5432
> 
> With this you make connection pooling only for the database named postgres. 
> This is what you really want?
> 
> Read this minihowto too:
> http://filip.rembialkowski.net/pgbouncer-mini-howto-benchmark/
> 
>> 
>> 
>> 
>> Please help!
>> Thanks
>> 
>> 
>> 
> 
> 
> 
> -- 
> 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