Re: [GENERAL] COPY question

[Craig Ringer]

On 10/22/2010 05:16 AM, Szymon Guz wrote:

Hi,
I still get the same error while using COPY FROM 'file'. I have to pass
the full directory for this to work, example:


Sounds like you want to be using psql's \copy, not the server side COPY. 
\copy is aware of your current working directory and doesn't require the 
server to have permission to access the file - or even be on the same 
machine.


--
Craig Ringer

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

Re: [GENERAL] compile error in libpq program

[John R Pierce]

On 10/21/10 5:24 PM, zab08 wrote:

I use this command to compile the libpq.c.
*cc -I /opt/PostgreSQL/8.4/include -o libpq libpq.c -L 
/opt/PostgreSQL/8.4/lib -lpq*

*
*
but I get these error:
/usr/bin/ld: warning: libssl.so.4, needed by 
/opt/PostgreSQL/8.4/lib/libpq.so, not found (try using -rpath or 
-rpath-link)
/usr/bin/ld: warning: libcrypto.so.4, needed by 
/opt/PostgreSQL/8.4/lib/libpq.so, not found (try using -rpath or 
-rpath-link)





if you don't want SSL support, you need to run ./configure --without-openssl
if you DO want SSL support, you'll need those libraries in a standard 
place, or specify --with-openssl=/path/to/ssl


you should use make and the Makefile generated by ./configure to get all 
the settings correct I believe you can use


make libpq.so

to just build the runtime rather than the full system.



--
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] Generate a dynamic sequence within a query

[Daniel Verite]

David Kerr wrote:

> Well, an upgrade's not on tap for a few months. Until then i'll need to
> figure out somethnig else.

This may help:

http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-sum-in-on
e-query/

or http://preview.tinyurl.com/mc4q6p

Best regards,
-- 
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

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

[GENERAL] compile error in libpq program

[zab08]

I use this command to compile the libpq.c.
 cc -I /opt/PostgreSQL/8.4/include -o libpq libpq.c -L /opt/PostgreSQL/8.4/lib 
-lpq


but I get these error:
/usr/bin/ld: warning: libssl.so.4, needed by /opt/PostgreSQL/8.4/lib/libpq.so, 
not found (try using -rpath or -rpath-link)
/usr/bin/ld: warning: libcrypto.so.4, needed by 
/opt/PostgreSQL/8.4/lib/libpq.so, not found (try using -rpath or -rpath-link)
/opt/PostgreSQL/8.4/lib/libpq.so: undefined reference to `TLSv1_method'
/opt/PostgreSQL/8.4/lib/libpq.so: undefined reference to `SSL_set_ex_data'
/opt/PostgreSQL/8.4/lib/libpq.so: undefined reference to `SSL_connect'
/opt/PostgreSQL/8.4/lib/libpq.so: undefined reference to 
`X509_STORE_load_locations'
/opt/PostgreSQL/8.4/lib/libpq.so: undefined reference to `BIO_free'
/opt/PostgreSQL/8.4/lib/libpq.so: undefined reference to `BIO_ctrl'
/opt/PostgreSQL/8.4/lib/libpq.so: undefined reference to 
`CRYPTO_set_locking_callback'
/opt/PostgreSQL/8.4/lib/libpq.so: undefined reference to `X509_NAME_oneline'
/opt/PostgreSQL/8.4/lib/libpq.so: undefined reference to `SSL_CTX_set_verify'
/opt/PostgreSQL/8.4/lib/libpq.so: undefined reference to 
`PEM_read_bio_PrivateKey'
/opt/PostgreSQL/8.4/lib/libpq.so: undefined reference to `CRYPTO_num_locks'
/opt/PostgreSQL/8.4/lib/libpq.so: undefined reference to `ENGINE_finish'
/opt/PostgreSQL/8.4/lib/libpq.so: undefined reference to 
`SSL_CTX_load_verify_locations'
/opt/PostgreSQL/8.4/lib/libpq.so: undefined reference to `ERR_get_error'
/opt/PostgreSQL/8.4/lib/libpq.so: undefined reference to 
`ERR_reason_error_string'
/opt/PostgreSQL/8.4/lib/libpq.so: undefined reference to `SSL_library_init'
/opt/PostgreSQL/8.4/lib/libpq.so: undefined reference to 
`CRYPTO_set_id_callback'
/opt/PostgreSQL/8.4/lib/libpq.so: undefined reference to `SSL_get_ex_data'
/opt/PostgreSQL/8.4/lib/libpq.so: undefined reference to `SSL_new'
/opt/PostgreSQL/8.4/lib/libpq.so: undefined reference to `SSL_write'
/opt/PostgreSQL/8.4/lib/libpq.so: undefined reference to `OPENSSL_config'
/opt/PostgreSQL/8.4/lib/libpq.so: undefined reference to 
`SSL_CTX_get_cert_store'
/opt/PostgreSQL/8.4/lib/libpq.so: undefined reference to 
`X509_check_private_key'
/opt/PostgreSQL/8.4/lib/libpq.so: undefined reference to `X509_free'
/opt/PostgreSQL/8.4/lib/libpq.so: undefined reference to `SSL_shutdown'
/opt/PostgreSQL/8.4/lib/libpq.so: undefined reference to `BIO_new_file'
/opt/PostgreSQL/8.4/lib/libpq.so: undefined reference to `ENGINE_free'
/opt/PostgreSQL/8.4/lib/libpq.so: undefined reference to `SSL_get_error'
/opt/PostgreSQL/8.4/lib/libpq.so: undefined reference to `ENGINE_by_id'
/opt/PostgreSQL/8.4/lib/libpq.so: undefined reference to `ENGINE_init'
collect2: ld 返回 1


I use postgresql 8.4  and use ssl to connect the server.

Re: [GENERAL] Generate a dynamic sequence within a query

[David Kerr]

On Wed, Oct 20, 2010 at 09:35:11PM -0700, Darren Duncan wrote:
- Josh Kupershmidt wrote:
- >On Wed, Oct 20, 2010 at 6:22 PM, David Kerr  wrote:
- >>I know I've seen posts on how to do this, but i can't seem to find them.
- >>
- >>I've got a data set
- >>
- >>A, B
- >>A, C
- >>A, D
- >>[...]
- >>
- >>and so on
- >>
- >>and i'd like to be able to wite a query that would result in
- >>
- >>1,A,B
- >>2,A,C
- >>3,A,D
- >>[...]
- >>
- >>PG version is 8.3.
- >
- >If you can upgrade to 8.4, you could use the row_number() window
- >function which is perfectly suited to this task, should be as simple
- >as:
- >
- >SELECT row_number() OVER (), * FROM tablename;
- 
- Yes indeed.  For a simple increment by one sequence, functions like rank() 
- ... see 
- http://www.postgresql.org/docs/9.0/interactive/functions-window.html ... 
- are exactly what you want. -- Darren Duncan


Well, an upgrade's not on tap for a few months. Until then i'll need to
figure out somethnig else.

thanks all.

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] Gripe: bytea_output default => data corruption

[ljb]

br...@momjian.us wrote:
>...
> Yes, we mentioned that setting in the release notes too:
>...
> While the "Incompatibilities" section mentions only the first paragraph,
> this remention lower down has even more details.  Not sure what else you
> wanted us to do.

Here's how I would have written that first paragraph. My addition starts
at "Libpq's PQunescapeBytea() function..."

|  * Allow bytea values to be written in hex notation (Peter Eisentraut)
|
|   The server parameter bytea_output controls whether hex or
| traditional format is used for bytea output. Libpq's PQescapeByteaConn()
| function automatically uses the hex format when connected to PostgreSQL
| 9.0 or newer servers. Libpq's PQunescapeBytea() function from version 9.0
| and newer will properly decode both hex and traditional format. However,
| in versions of Libpq older than 9.0, the PQunescapeByte() function can only
| decode traditional format, and will corrupt bytea data received in hex
| format without reporting an error. To avoid loss of data, you must either
| upgrade all clients to 9.0.x, or set the server's bytea_output parameter
| to 'escape'.

Again: My complaint is that pre-9.0 libpq-based clients mis-decode the new
default hex format bytea data without reporting an error, and this danger is
insufficiently documented in the release notes.

Speaking of documentation, go read the 9.0.x reference manual sections for
Libpq's PQescapeByteaConn() and PQunescapeBytea(). These descriptions of
escaping and unescaping are incorrect for 9.0, which can add to any confusion.

-- 
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] Updates, deletes and inserts are very slow. What can I do make them bearable?

[Scott Marlowe]

On Thu, Oct 21, 2010 at 4:31 PM, Tim Uckun  wrote:
> On Fri, Oct 22, 2010 at 4:33 AM, Brian Hirt  wrote:
>>>
>>> There are only two tables in the query.
>>>
>>
>> Tim,
>>
>> No, your query is written incorrectly.  I don't understand why you come on 
>> to this list all hostile and confrontational.  Regardless, people still try 
>> to help you and then you still ignore the advice of people that are giving 
>> you the solutions to your problems.
>
>
> I don't think I was hostile. I think people were hostile to me as a
> matter of fact.
>
> I asked a question and the first reply was really snarky and unhelpful.

Agreed.  But when Tom pointed out the problem in your query you were
quite sure you couldn't be wrong.  When I asked you to run explain to
see what kind of row estimate you got, I got no answer.  This was a
query problem not a hardware tuning problem.

The best step for getting a good answer is forming a good question.

So, has changing the query made it faster?  Did explain show what I
expect, a huge cartesian product?  Just guessing.

-- 
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] Updates, deletes and inserts are very slow. What can I do make them bearable?

[Tim Uckun]

>>> True.  His only real snark was in reponse to the "let me google that
> for you" link.  OTOH, he's arguing with Tom Lane about whether his SQL
> is well formed.  There's arguing on the internet is stupid, then
> there's arguing with Tom Lane about SQL is stupid.

I wasn't arguing with Tom Lane about the SQL. I was using standard SQL
the kind used by all other databases. Tom correctly pointed out that
postgres does not deal with that particular SQL statement the way
other databases do and wrote it the way postgres wants it.


As far as I know update table set x=y from table inner join other
table is a pretty standard way of doing things.  Tom pointed out that
in postgres you have to leave out the second mention of the table as
postgres takes that as a self join.

>
> Have to admit when I saw that I said to myself OP needs someone to tell
> him "whoa, big fella".  I've been in similar situations where I was
> "sure" of one thing and the problem must be elsewhere, when of course I
> was wrong about the one thing...
>
>

I have been wrong lots of times.  It's not a big deal. In this case I
was expecting postgres to act one way because most of my experience is
with other databases and that's the way other databases handle things.
 I would think that's a common occurrence here.

-- 
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] Updates, deletes and inserts are very slow. What can I do make them bearable?

[Tim Uckun]

On Fri, Oct 22, 2010 at 4:33 AM, Brian Hirt  wrote:
>>
>> There are only two tables in the query.
>>
>
> Tim,
>
> No, your query is written incorrectly.  I don't understand why you come on to 
> this list all hostile and confrontational.  Regardless, people still try to 
> help you and then you still ignore the advice of people that are giving you 
> the solutions to your problems.


I don't think I was hostile. I think people were hostile to me as a
matter of fact.

I asked a question and the first reply was really snarky and unhelpful.

-- 
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] Gripe: bytea_output default => data corruption

[Bruce Momjian]

Rajesh Kumar Mallah wrote:
> Dear Griper!,
> 
> fortunately someone showed a easy 'fix'
> 
> ALTER DATABASE foo SET bytea_output='escape' ;

Yes, we mentioned that setting in the release notes too:

E.2.3.5. Data Types

*

  Allow bytea values to be written in hex notation (Peter
Eisentraut)

  The server parameter bytea_output controls whether hex or
traditional format is used for bytea output. Libpq's PQescapeByteaConn()
function automatically uses the hex format when connected to PostgreSQL
9.0 or newer servers.

  The new hex format will be directly compatible with more
applications that use binary data, allowing them to store and retrieve
it without extra conversion. It is also significantly faster to read and
write than the traditional format. 

While the "Incompatibilities" section mentions only the first paragraph,
this remention lower down has even more details.  Not sure what else you
wanted us to do.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
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] COPY question

[Raymond O'Donnell]

On 21/10/2010 22:16, Szymon Guz wrote:

Hi,
I still get the same error while using COPY FROM 'file'. I have to pass
the full directory for this to work, example:

COPY first (a,b,c) FROM '1st_file.csv' with csv header delimiter ',';
ERROR:  could not open file "1st_file.csv" for reading: No such file or
directory

>

The same when I use: '.1st_file.csv' or './1st_file.csv'.

No errors when I give the full path.


Yes, that's documented behaviour - you need to give the full path.



Giving the full path is stupid, how can I use just the file name?


Why is it stupid?

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] COPY question

[Szymon Guz]

On 21 October 2010 23:28, Raymond O'Donnell  wrote:

> On 21/10/2010 22:16, Szymon Guz wrote:
>
>> Hi,
>> I still get the same error while using COPY FROM 'file'. I have to pass
>> the full directory for this to work, example:
>>
>> COPY first (a,b,c) FROM '1st_file.csv' with csv header delimiter ',';
>> ERROR:  could not open file "1st_file.csv" for reading: No such file or
>> directory
>>
> >
>
>> The same when I use: '.1st_file.csv' or './1st_file.csv'.
>>
>> No errors when I give the full path.
>>
>
> Yes, that's documented behaviour - you need to give the full path.
>
>
I've noticed that already. Btw, I could use the file name, but it won't be
searched in my current directory.
"The path will be interpreted relative to the working directory of the
server process (normally the cluster's data directory), not the client's
working directory."


>
>
>  Giving the full path is stupid, how can I use just the file name?
>>
>
> Why is it stupid?
>

Because I can't just move my scripts/queries to some other directory, where
I have files with the same names, but different content (just for tests).

Thanks for the answer.

regards
Szymon

[GENERAL] COPY question

[Szymon Guz]

Hi,
I still get the same error while using COPY FROM 'file'. I have to pass the
full directory for this to work, example:

COPY first (a,b,c) FROM '1st_file.csv' with csv header delimiter ',';
ERROR:  could not open file "1st_file.csv" for reading: No such file or
directory

The same when I use: '.1st_file.csv' or './1st_file.csv'.

No errors when I give the full path.

Giving the full path is stupid, how can I use just the file name?

[PostgreSQL 9.1alpha1 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(Ubuntu/Linaro 4.4.4-14ubuntu5) 4.4.5, 64-bit]


regards
Szymon

Re: [GENERAL] Advice needed on application/database authentication/authorization/auditing model

[Dmitriy Igrishin]

Hey Tony,

2010/10/21 Tony Cebzanov 

> I have a web application with a Postgres backend.  In my initial
> prototype, I decided not to have a Postgres database user created for
> each application user, opting instead to use my own users table.
>
IMO, you are trying to reinvent the wheel. Although, you may do it just for
fun. :-)

Authentication of application users is done via PAM, so no password is
> necessary in my users table -- I just let PAM do its thing, and if the
> user is authenticated, I check for a record in my application's users
> table to see if they're authorized to use the app, along with what
> privileges they have, e.g.:
>
> CREATE TYPE USER_ROLE AS ENUM ('User', 'Auditor', 'Administrator');
>
> CREATE TABLE users (
>id SERIAL PRIMARY KEY,
>username TEXT UNIQUE NOT NULL,
>displayname TEXT NOT NULL,
>role USER_ROLE NOT NULL DEFAULT 'User'
> );
>
Why not just create "groups" via CREATE ROLE User ... and grants this
roles to the "users" (created via CREATE USER or CREATE ROLE ... LOGIN)  ?


> Now that this is moving beyond a prototype stage, I need to tighten up
> the authentication/authorization/access control model.  In particular, I
> need to add some basic audit trail functionality.  I found a couple of
> projects that help make auditing easy (tablelog and EMaj being the most
> promising) but they both rely on the database users mapping 1:1 to
> application users, which is currently not the case -- right now I've
> only got one database user that's used for all of the pooled
> connections, so the audit logs are showing that user instead of my
> application user.
>
Consider to use connection pool for "anonymous" users, i.e. users, which
are not logged in and persistent connections for logged in users.
To implement audit consider to use triggers. See, for example,
http://www.postgresql.org/docs/9.0/static/plpgsql-trigger.html#PLPGSQL-TRIGGER-AUDIT-EXAMPLE


> So, I'm wondering what others have done in similar situations.  It seems
> to me like the database connection pooling means I probably need the
> connections to be made with a privileged "database superuser" account
> that has permission to "SET ROLE" to each and every application user,
> then "RESET ROLE" when it's done.  That's a bit of a pain, but doable
>
> Then there's the issue of application roles vs. database roles.  I
> wanted to have three roles in this application:  regular users, auditors
> (who can do everything regular users can, plus access audit tables to
> view audit log tables and potentially restore data from them) and
> administrators (who can do everything in the application, but shouldn't
> be Postgres superusers.)  Unfortunately, I can't figure out a clever way
> to do this mapping, especially because Postgres doesn't allow users to
> refer to system tables like pg_authid to do an explicit mapping of app
> roles to database roles.


> So, does anyone have any suggestions here?  I feel like there's got to
> be a way to do this, but I can't find anything relevant in the list
> archives.
>
> Thanks.
> -Tony
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
// Dmitriy.

Re: [GENERAL] Cannot Start Postgres After System Boot

[Tom Lane]

Rich Shepard  writes:
>Since I cannot start the postmaster I cannot run pg_dumpall.

As far as I can tell you *are* starting the postmaster, and it is
responding when you query it via TCP (eg, with "psql -h localhost").
What is not working is connections via the Unix socket.  I still
suspect that the problem there is that the postmaster is creating
the socket file somewhere other than /tmp, but your client library
thinks /tmp is where to look.

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] Cannot Start Postgres After System Boot

[Scott Marlowe]

On Thu, Oct 21, 2010 at 12:38 PM, Rich Shepard  wrote:
> On Thu, 21 Oct 2010, Reid Thompson wrote:
>
>> what does
>> $ netstat -an |grep 5432
>> return?
>>
>> something is running on tcp port 5432
>
>  Doesn't show that.
>
> [rshep...@salmo ~]$ netstat -an |grep 5432
> tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN

That's exactly what it shows.

-- 
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] Cannot Start Postgres After System Boot [SOLVED]

[Rich Shepard]

On Thu, 21 Oct 2010, Adrian Klaver wrote:


The missing piece of information seems to be the system board failure. My
guess is that caused corruption.  See if you can connect by doing:

psql -h localhost -d aesi


Adrian,

[rshep...@salmo ~]$ psql -h localhost -d aesi
psql: could not connect to server: Connection refused
Is the server running on host "localhost" and accepting
TCP/IP connections on port 5432?

  Let's try something different:

[rshep...@salmo ~]$ su - postgres
Password: 
postg...@salmo:~$ postgres -D /var/lib/pgsql/data &

[1] 17910
postg...@salmo:~$ FATAL:  bogus data in lock file "postmaster.pid": ""

  So, I rm postmaster.pid and run again as user postgres and ... it works!

  Now that it's working again, can I assume the problem is with the
rc.postgresql init script not running as root rather than as user postgres?
If that's the case, I need to learn how to effectively su to user postgres
during the boot process so postgresql starts as it should.

  Suggestions, anyone?

  And thanks to all of you for helping me climb out of the hole in which I
was stuck.

  Now I need to re-read how to properly and cleanly upgrade postgres and
move from 8.3.3 to 8.4.5. (I've just posted a question on the CMS MadeSimple
forum asking if there's an issue with 9.0. If not, that's to what I'll
upgrade.)

Much grasses,

Rich


--
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] Cannot Start Postgres After System Boot

[Andrej]

On 22 October 2010 07:45, Rich Shepard  wrote:
>  When I run 'ps ax | grep post' I found a few postgres processes. I tried
> '/etc/rc.d/rc.postgresql stop' but that had no effect. I killed the lowest
> numbered process and that removed them all. However, I still cannot start a
> new postgresql process.
I just stumbled upon your post from two years ago; has
your setup changed since then?


Cheers,
Andrej

-- 
Please don't top post, and don't use HTML e-Mail :}  Make your quotes concise.

http://www.georgedillon.com/web/html_email_is_evil.shtml

-- 
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] Cannot Start Postgres After System Boot

[Reid Thompson]

On Thu, 2010-10-21 at 11:45 -0700, Rich Shepard wrote:
> On Thu, 21 Oct 2010, Scott Marlowe wrote:
> 
> > So a postgres IS running on your machine.  I put it to you it's not
> > running where you think it is.
> 
>When I run 'ps ax | grep post' I found a few postgres processes. I tried
> '/etc/rc.d/rc.postgresql stop' but that had no effect. I killed the lowest
> numbered process and that removed them all. However, I still cannot start a
> new postgresql process.
> 
> Rich
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



What does
$ su - postgres
$ pg_ctl -D /var/lib/pgsql/data
$ ps -ef|grep post

return

Re: [GENERAL] Cannot Start Postgres After System Boot

[Reid Thompson]

On Thu, 2010-10-21 at 11:38 -0700, Rich Shepard wrote:
> On Thu, 21 Oct 2010, Reid Thompson wrote:
> 
> > what does
> > $ netstat -an |grep 5432
> > return?
> >
> > something is running on tcp port 5432
> 
>Doesn't show that.
> 
> [rshep...@salmo ~]$ netstat -an |grep 5432
> tcp0  0 127.0.0.1:5432  0.0.0.0:*   LISTEN

The above line means that something is listening on TCP port 5432.
You do NOT have a listener on unix socket port 5432.
EX:  my box has both

$ netstat -an|grep 5432
tcp0  0 0.0.0.0:54320.0.0.0:*   LISTEN 
unix  2  [ ACC ] STREAM LISTENING 413260   
/var/run/postgresql/.s.PGSQL.5432

If I telnet to 
$ telnet localhost 5432

and run
$ netstat -an|grep 5432
tcp0  0 0.0.0.0:54320.0.0.0:*   LISTEN 
tcp0  0 127.0.0.1:56771 127.0.0.1:5432  ESTABLISHED
tcp0  0 127.0.0.1:5432  127.0.0.1:56771 ESTABLISHED
unix  2  [ ACC ] STREAM LISTENING 413260   
/var/run/postgresql/.s.PGSQL.5432
rthom...@raker>~ 

the established connection is shown
and lsof shows
$ lsof -i TCP:5432
COMMAND   PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
telnet  22648 rthompso3u  IPv4 445992  0t0  TCP 
raker.ateb.com:56771->raker.ateb.com:postgresql (ESTABLISHED)
rthom...@raker>~  
$ 

Re: [GENERAL] Question on Explain : Index Scan

[DM]

*Why is the difference in query plan, and the total runtime. Both tables
have the same  btree index*


test=# explain analyze select * from test_seqindex1 where sid='AA023';
 QUERY
PLAN
-
 Index Scan using test_seqindex1_pkey on test_seqindex1  (cost=0.00..8.27
rows=1 width=28) (actual time=0.017..0.018 rows=1 loops=1)
   Index Cond: ((sid)::text = 'AA023'::text)
 Total runtime: 0.035 ms
(3 rows)

test=# explain analyze select * from test_seqindex2 where sid='AA023';
QUERY
PLAN
--
 Bitmap Heap Scan on test_seqindex2  (cost=4.95..275.53 rows=73 width=30)
(actual time=57.833..71.577 rows=2 loops=1)
   Recheck Cond: ((sid)::text = 'AA023'::text)
   ->  Bitmap Index Scan on idx_test_seqindex2_sid  (cost=0.00..4.93 rows=73
width=0) (actual time=34.374..34.374 rows=2 loops=1)
 Index Cond: ((sid)::text = 'AA023'::text)
 Total runtime: 71.599 ms
(5 rows)


test=# \d test_seqindex1
   Table "public.test_seqindex1"
 Column | Type  | Modifiers
+---+---
 sid| character varying(13) | not null
 name   | character varying(80) |
Indexes:
"test_seqindex1_pkey" PRIMARY KEY, btree (sid)

test=# \d test_seqindex2
   Table "public.test_seqindex2"
 Column | Type  | Modifiers
+---+---
 eid| integer   | not null
 sid| character varying(13) |
 ename  | character varying(80) |
Indexes:
"test_seqindex2_pkey" PRIMARY KEY, btree (eid)
"idx_test_seqindex2_sid" btree (sid)


===


On Thu, Oct 21, 2010 at 11:09 AM, DM  wrote:

> perfecto, thank you for the explanation.
>
> - Deepak
>
>
> On Thu, Oct 21, 2010 at 3:20 AM, Mathieu De Zutter 
> wrote:
>
>> On Thu, Oct 21, 2010 at 3:47 AM, DM  wrote:
>> > I was hoping the optimizer would do a join using index scan.
>> >
>> > Could some one please explain me why its not doing an index scan rather
>> than
>> > sequential scan .
>>
>>
>> A index scan would be probably slower here because you're asking for a
>> lot of rows. A lot of rows means a lot of I/O, and an index scan is
>> more I/O intensive (since it has to read the index too). If you limit
>> the result (by being more selective in your where clause, just like
>> you do in the first two queries), postgres will most likely switch to
>> index scan.
>>
>> You can see for yourself if index-scan would be faster in your case by
>> running the following command before "explain (analyze)":
>>
>> set enable_seqscan = off;
>>
>> BTW, try to use explain analyze instead of explain, that way you'll
>> see the actual timings too instead of just the planner estimates.
>>
>> Kind regards,
>> Mathieu
>>
>
>

Re: [GENERAL] Cannot Start Postgres After System Boot

[Rich Shepard]

On Thu, 21 Oct 2010, Scott Marlowe wrote:


So a postgres IS running on your machine.  I put it to you it's not
running where you think it is.


  When I run 'ps ax | grep post' I found a few postgres processes. I tried
'/etc/rc.d/rc.postgresql stop' but that had no effect. I killed the lowest
numbered process and that removed them all. However, I still cannot start a
new postgresql process.

Rich

--
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] Cannot Start Postgres After System Boot

[Rich Shepard]

On Thu, 21 Oct 2010, Reid Thompson wrote:


what does
$ netstat -an |grep 5432
return?

something is running on tcp port 5432


  Doesn't show that.

[rshep...@salmo ~]$ netstat -an |grep 5432
tcp0  0 127.0.0.1:5432  0.0.0.0:*   LISTEN

Rich

--
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] Cannot Start Postgres After System Boot

[Rich Shepard]

On Thu, 21 Oct 2010, Scott Marlowe wrote:


WHOA, never delete those files unless you're sure you've killed off
postgres first.  Then and only then you can delete them and safely
restart.  If you ever manage to bring up two postmasters on the same store
you've just destroyed your database.


Scott,

  Postgres has not been running. That's the problem I've been trying to
solve. The only reason I've manually killed the socket and its lock is when
the system shut down uncleanly and postgres would not start while they were
present.

Thanks,

Rich


--
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] Cannot Start Postgres After System Boot

[Rich Shepard]

On Thu, 21 Oct 2010, Tom Lane wrote:


Actually, I was saying that the script should *not* concern itself with
the pidfile at all.


Tom,

  I understood what you wrote.


Hmm, maybe the postmaster thinks it should be putting the socket file
someplace other than /tmp. Have you got a nondefault setting of
unix_socket_directory in postgresq.conf?


  No. It's been commented out forever, so it should be the default.


Also, if you're using the distro's build of postgresql not your own, it's
possible that the compiled-in default for unix_socket_directory isn't /tmp
--- though the copy of libpq you're using seems to think it is /tmp.


  The currently installed 8.3.3 has been running for some time now. I've not
made any changes since last Friday (the last day I used one of the
databases), and the system board failed Sunday afternoon, just after an OS
upgrade.


Maybe your libpq came from someplace different than the postmaster
executable?


  I've no idea how that could have happened.

  Since I cannot start the postmaster I cannot run pg_dumpall. What's the
pragmatic way for me to once again get postgres running (and, presumably,
able to cleanly stop and restart when necessary)?

Many thanks,

Rich



--
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] Cannot Start Postgres After System Boot

[Adrian Klaver]

On 10/21/2010 11:21 AM, Rich Shepard wrote:

On Thu, 21 Oct 2010, Scott Marlowe wrote:


WHOA, never delete those files unless you're sure you've killed off
postgres first. Then and only then you can delete them and safely
restart. If you ever manage to bring up two postmasters on the same store
you've just destroyed your database.


Scott,

Postgres has not been running. That's the problem I've been trying to
solve. The only reason I've manually killed the socket and its lock is when
the system shut down uncleanly and postgres would not start while they were
present.

Thanks,

Rich




But it is running:

rshep...@salmo ~]$ psql -h localhost -l
 List of databases
   Name|   Owner| Encoding ---++--
 aesi  | sql-ledger | LATIN1
 cms   | rshepard   | UTF8
 postgres  | postgres   | UTF8
 refdb | postgres   | UTF8
 scirefs   | rshepard   | LATIN1
 template0 | postgres   | UTF8
 template1 | postgres   | UTF8


The missing piece of information seems to be the system board failure. 
My guess is that caused corruption.  See if you can connect by doing:


psql -h localhost -d aesi

--
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

Re: [GENERAL] Question on Explain : Index Scan

[DM]

perfecto, thank you for the explanation.

- Deepak

On Thu, Oct 21, 2010 at 3:20 AM, Mathieu De Zutter wrote:

> On Thu, Oct 21, 2010 at 3:47 AM, DM  wrote:
> > I was hoping the optimizer would do a join using index scan.
> >
> > Could some one please explain me why its not doing an index scan rather
> than
> > sequential scan .
>
>
> A index scan would be probably slower here because you're asking for a
> lot of rows. A lot of rows means a lot of I/O, and an index scan is
> more I/O intensive (since it has to read the index too). If you limit
> the result (by being more selective in your where clause, just like
> you do in the first two queries), postgres will most likely switch to
> index scan.
>
> You can see for yourself if index-scan would be faster in your case by
> running the following command before "explain (analyze)":
>
> set enable_seqscan = off;
>
> BTW, try to use explain analyze instead of explain, that way you'll
> see the actual timings too instead of just the planner estimates.
>
> Kind regards,
> Mathieu
>

Re: [GENERAL] Cannot Start Postgres After System Boot

[Scott Marlowe]

On Thu, Oct 21, 2010 at 11:35 AM, Rich Shepard  wrote:
> On Thu, 21 Oct 2010, Scott Marlowe wrote:
>
>> So, what do
>>
>> telnet localhost 5432
>
> Scott,
>
>  That port's clear:
>
> [rshep...@salmo ~]$ telnet localhost 5432
> Trying 127.0.0.1...
> Connected to localhost.
> Escape character is '^]'.

So something IS attached and is answering the phone.

>> AND
>> psql -h localhost -l
>
>  Huh!
>
> [rshep...@salmo ~]$ psql -h localhost -l
>         List of databases

So a postgres IS running on your machine.  I put it to you it's not
running where you think it is.

-- 
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] Cannot Start Postgres After System Boot

[Reid Thompson]

On Thu, 2010-10-21 at 10:35 -0700, Rich Shepard wrote:
> On Thu, 21 Oct 2010, Scott Marlowe wrote:
> 
> > So, what do
> >
> > telnet localhost 5432
> 
> Scott,
> 
>That port's clear:
> 
> [rshep...@salmo ~]$ telnet localhost 5432
> Trying 127.0.0.1...
> Connected to localhost.
> Escape character is '^]'.
> 
> > AND
> > psql -h localhost -l
> 
>Huh!
> 
> [rshep...@salmo ~]$ psql -h localhost -l
>   List of databases
> Name|   Owner| Encoding 
> ---++--
>   aesi  | sql-ledger | LATIN1
>   cms   | rshepard   | UTF8
>   postgres  | postgres   | UTF8
>   refdb | postgres   | UTF8
>   scirefs   | rshepard   | LATIN1
>   template0 | postgres   | UTF8
>   template1 | postgres   | UTF8
> (7 rows)
> 
>So, why can't I connect to a database by entering, for example, 'psql
> aesi'?
> 
> Thanks,
> 
> Rich

what does
$ netstat -an |grep 5432
return?

something is running on tcp port 5432

Re: [GENERAL] Cannot Start Postgres After System Boot

[Scott Marlowe]

On Thu, Oct 21, 2010 at 11:36 AM, Lennin Caro  wrote:
>
> Try to delete the files like this
>
> .s.PGSQL.5432
> .s.PGSQL.5432.lock
> 8.x-main.pid
>
> and restart postmaster

WHOA, never delete those files unless you're sure you've killed off
postgres first.  Then and only then you can delete them and safely
restart.  If you ever manage to bring up two postmasters on the same
store you've just destroyed your database.

--
To understand recursion, one must first understand recursion.

-- 
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] Composite Index question

[DM]

Thank you for all your suggestions and answers.

- Deepak

On Wed, Oct 20, 2010 at 7:41 PM, mark  wrote:

>
> On 10/20/2010 05:43 PM, DM wrote:
> > Composite Index question:
> >
> > I have composite index on 3 columns on a table, by mistake the composite
> > index was created twice on the table.
> >
> > Will there any performance issues on this table because of the 2 same
> > composite indexes?
> >
> > Thanks
> > Deepak
>
> Are the indices of the same type (e.g. both BTree) but with different index
> names?
>
> Is the second composite index the columns in same order as the first ? if
> not in the same column order you might be seeing some benefit for some
> queries but this is dependent on the queries filter clauses.  If so you
> might consider augmenting one or both of the indices to better suit your
> queries.
>
> From my experience, it appears to degrade performance because two indices
> have to be maintained. (not to mention also appears to be a waste of disk
> space) I am hopeful someone will correct me if I am wrong.
>
> Ours were from people explicitly creating indexes on columns that had
> indices implicitly created on them when the table was created.  Cleanup was
> pretty easy and painless on our production systems.
>
> It's pretty easy to check for exact duplicates all over a given database as
> well as how often each index is being used.
> Check some of the queries here:
> http://www.xzilla.net/blog/2008/Jul/Index-pruning-techniques.html
>
> ..:Mark
>
>
> --
> 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] Custom cache implemented in a postgresql C function

[Gabi Julien]

Thanks to all of you. This was very good feedback. I'll use the one cache per 
process suggestion of Tom Lane. This will be the easiest to implement.

On Thursday 21 October 2010 11:14:40 A.M. wrote:
> 
> On Oct 20, 2010, at 7:44 PM, Gabi Julien wrote:
> 
> > Hi,
> > 
> > Here is my problem: I have a postgresql C function that looks like this:
> > 
> > Datum filter(PG_FUNCTION_ARGS);
> > 
> > It takes identifiers and queries a bunch of tables and ends up returning 
> > true or false. So far nothing difficult except that we want better 
> > performance. The function was already optimized to the best of my abilities 
> > and changing the structure of the database would not help. However, having 
> > a cache would be the perfect solution. I could implement this cache outside 
> > of postgresql if need be but nothing could beat implementing this directly 
> > in a postgresql C function.
> > 
> > So this is what I want, a custom cache built into a postgresql C function. 
> > Since postgresql uses different processes, it would be best to use the 
> > shared memory. Can this be done safely? At its core, the cache could be 
> > considered as simple as a map protected by a mutex. With postgresql, I 
> > first need to initialized some shared memory. This is explained at the end 
> > of this link:
> > 
> > http://www.postgresql.org/docs/8.2/static/xfunc-c.html
> > 
> > However, it sounds like I need to reserve the shared memory in advance 
> > using:
> > 
> > void RequestAddinShmemSpace(int size)
> > 
> > In my case, I do not know how big my cache will be. I would preferably 
> > allocate the memory dynamically. Is this possible? In any case, am I trying 
> > to reinvent the wheel here? Is there already a shared map or a shared hash 
> > structure available in postgresql?
> > 
> > If shared memory turns out too difficult to use, I could create separate 
> > caches for each postgresql processes. This would be a waste of space but it 
> > might be better then nothing. In this case, do I need to make my code 
> > thread safe? In other words, is postgresql using more then one thread per 
> > processes?
> 
> Apart from the other suggestions made, another option could be to use your 
> own shared memory which you allocate and manage yourself (without postgresql 
> managing it). You could implement a simple least-recently-used cache to purge 
> old entries as the cache grows.
> 
> Cheers,
> M



-- 
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] Cannot Start Postgres After System Boot

[Tom Lane]

Rich Shepard  writes:
> On Wed, 20 Oct 2010, Tom Lane wrote:
>> In particular, I wonder whether the script's refusal to start if the
>> pidfile already exists accounts for your report that it fails to
>> auto-restart after a reboot.

>This clears up my uncertainty. The pidfile should not exist after a clean
> shutdown, so it should be removed after a crash, too.

Actually, I was saying that the script should *not* concern itself with
the pidfile at all.  Having a script that automatically removes the
pidfile is a big foot-gun: if you ever run it at any time other than
system boot, you'll destroy a critical interlock against starting two
postmasters in the same data directory.  The postmaster is perfectly
capable of getting rid of a stale pidfile by itself, and is far less
likely to do the wrong thing than a scripted removal is.

>Yet, when I try to access one of my databases I cannot:

> [rshep...@salmo ~]$ psql aesi
> psql: could not connect to server: No such file or directory
>  Is the server running locally and accepting
>  connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

>There was no postgres running before I ran /etc/rc.d/rc.postgresql start.
> There is also no socket on /tmp.

Hmm, maybe the postmaster thinks it should be putting the socket file
someplace other than /tmp.  Have you got a nondefault setting of
unix_socket_directory in postgresq.conf?  Also, if you're using the
distro's build of postgresql not your own, it's possible that the
compiled-in default for unix_socket_directory isn't /tmp --- though
the copy of libpq you're using seems to think it is /tmp.  Maybe your
libpq came from someplace different than the postmaster executable?

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] Cannot Start Postgres After System Boot

[Rich Shepard]

On Thu, 21 Oct 2010, Adrian Klaver wrote:


What does your postgresql.conf file show for ? :
listen_addresses =


Adrian,

#listen_addresses = 'localhost' # what IP address(es) to listen on;

  This hasn't changed.

Thanks,

Rich

--
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] Cannot Start Postgres After System Boot

[Adrian Klaver]

On 10/21/2010 10:41 AM, Rich Shepard wrote:

On Thu, 21 Oct 2010, Reid Thompson wrote:


what does
$ netstat -an|grep 5432
return?


Reid,

[rshep...@salmo ~]$ netstat -an|grep 5432
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN unix 3 [ ] STREAM CONNECTED 785432


what does
$ ps -ef|grep post
return?
The above indicates that the tcp ipv4 socket is already bound by some
process


[rshep...@salmo ~]$ ps -ef|grep post
postgres 3753 1 0 Oct20 ? 00:00:00 postgres -D /var/lib/pgsql/data
postgres 3755 3753 0 Oct20 ? 00:00:00 postgres: writer process postgres
3756 3753 0 Oct20 ? 00:00:00 postgres: wal writer process postgres 3757
3753 0 Oct20 ? 00:00:00 postgres: autovacuum launcher process postgres
3758 3753 0 Oct20 ? 00:00:00 postgres: stats collector process root 4285
1 0 Oct19 ? 00:00:01 /usr/libexec/postfix/master
postfix 4287 4285 0 Oct19 ? 00:00:00 qmgr -l -t fifo -u
postfix 10143 4285 0 02:15 ? 00:00:00 anvil -l -t unix -u
postfix 16244 4285 0 10:01 ? 00:00:00 smtpd -n smtp -t inet -u -o stress
postfix 16245 4285 0 10:01 ? 00:00:00 trivial-rewrite -n rewrite -t unix -u
postfix 16246 4285 0 10:01 ? 00:00:00 smtpd -n smtp -t inet -u -o stress
postfix 16305 4285 0 10:06 ? 00:00:00 smtpd -n smtp -t inet -u -o stress
postfix 16426 4285 0 10:15 ? 00:00:00 smtpd -n smtp -t inet -u -o stress
postfix 16625 4285 0 10:31 ? 00:00:00 pickup -l -t fifo -u
postfix 16743 4285 0 10:38 ? 00:00:00 cleanup -z -t unix -u
postfix 16744 4285 0 10:38 ? 00:00:00 local -t unix

Yet I cannot connect to a database either from the command line or, in the
case of SQL-Ledger, from firefox:

Error!

could not connect to server: No such file or directory Is the server
running
locally and accepting connections on Unix domain socket
"/tmp/.s.PGSQL.5432"?

Thanks,

Rich



What does your postgresql.conf file show for ? :

listen_addresses =

--
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

Re: [GENERAL] Cannot Start Postgres After System Boot

[Rich Shepard]

On Thu, 21 Oct 2010, Lennin Caro wrote:


Try to delete the files like this



.s.PGSQL.5432
.s.PGSQL.5432.lock
8.x-main.pid



and restart postmaster


Lennin,

  The sockets are not to be found.

Rich


--
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] Cannot Start Postgres After System Boot

[Rich Shepard]

On Thu, 21 Oct 2010, Reid Thompson wrote:


what does
$ netstat -an|grep 5432
return?


Reid,

[rshep...@salmo ~]$ netstat -an|grep 5432
tcp0  0 127.0.0.1:5432  0.0.0.0:*   LISTEN 
unix  3  [ ] STREAM CONNECTED 785432



what does
$ ps -ef|grep post
return?
The above indicates that the tcp ipv4 socket is already bound by some process


[rshep...@salmo ~]$ ps -ef|grep post
postgres  3753 1  0 Oct20 ?00:00:00 postgres -D /var/lib/pgsql/data
postgres  3755  3753  0 Oct20 ?00:00:00 postgres: writer process 
postgres  3756  3753  0 Oct20 ?00:00:00 postgres: wal writer process 
postgres  3757  3753  0 Oct20 ?00:00:00 postgres: autovacuum launcher process 
postgres  3758  3753  0 Oct20 ?00:00:00 postgres: stats collector process 
root  4285 1  0 Oct19 ?00:00:01 /usr/libexec/postfix/master

postfix   4287  4285  0 Oct19 ?00:00:00 qmgr -l -t fifo -u
postfix  10143  4285  0 02:15 ?00:00:00 anvil -l -t unix -u
postfix  16244  4285  0 10:01 ?00:00:00 smtpd -n smtp -t inet -u -o stress 
postfix  16245  4285  0 10:01 ?00:00:00 trivial-rewrite -n rewrite -t unix -u
postfix  16246  4285  0 10:01 ?00:00:00 smtpd -n smtp -t inet -u -o stress 
postfix  16305  4285  0 10:06 ?00:00:00 smtpd -n smtp -t inet -u -o stress 
postfix  16426  4285  0 10:15 ?00:00:00 smtpd -n smtp -t inet -u -o stress 
postfix  16625  4285  0 10:31 ?00:00:00 pickup -l -t fifo -u

postfix  16743  4285  0 10:38 ?00:00:00 cleanup -z -t unix -u
postfix  16744  4285  0 10:38 ?00:00:00 local -t unix

  Yet I cannot connect to a database either from the command line or, in the
case of SQL-Ledger, from firefox:

Error!

could not connect to server: No such file or directory Is the server running
locally and accepting connections on Unix domain socket
"/tmp/.s.PGSQL.5432"?

Thanks,

Rich

--
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] Cannot Start Postgres After System Boot

[Lennin Caro]

--- On Thu, 10/21/10, Reid Thompson  wrote:

From: Reid Thompson 
Subject: Re: [GENERAL] Cannot Start Postgres After System Boot
To: "Rich Shepard" 
Cc: pgsql-general@postgresql.org
Date: Thursday, October 21, 2010, 4:28 AM

On 10/20/2010 6:53 PM, Rich Shepard wrote:
>   For reasons I do not understand, the Slackware start-up file for postgres
> (/etc/rc.d/rc.postgresql) fails to work properly after I reboot the system.
> (Reboots normally occur only after a kernel upgrade or with a hardware
> failure that crashes the system.)
>
>   Trying to restart the system manually (su postgres -c 'postgres -D
> /var/lib/pgsql/data &') regardless of the presence of /tmp/.s.PGSQL.5432
> and /var/lib/pgsql/postmaster.pid. Here's what I see:
>
> [rshep...@salmo ~]$ su postgres -c 'postgres -D /var/lib/pgsql/data &'
> Password: [rshep...@salmo ~]$ LOG:  could not bind IPv4 socket: Address 
> already in use
> HINT:  Is another postmaster already running on port 5432? If not, wait a
> few seconds and retry.
> WARNING:  could not create listen socket for "localhost"
> FATAL:  could not create any TCP/IP sockets
>
>   If someone would be kind enough to point out what I'm doing incorrectly
> (e.g., removing /tmp/.s.PGSQL.5432 and postmaster.pid when the startup
> process complains they're not right) I'll save this information for the next
> time. I can also provide the 'start' section of the Slackware init file so I
> could learn why it's not working properly.
>
> TIA,
>
> Rich
>
what does
$ netstat -an|grep 5432
return?

what does
$ ps -ef|grep post
return?

The above indicates that the tcp ipv4 socket is already bound by some process

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

Try to delete the files like this

.s.PGSQL.5432
.s.PGSQL.5432.lock
8.x-main.pid

and restart postmaster




  

Re: [GENERAL] Cannot Start Postgres After System Boot

[Rich Shepard]

On Thu, 21 Oct 2010, Scott Marlowe wrote:


So, what do

telnet localhost 5432


Scott,

  That port's clear:

[rshep...@salmo ~]$ telnet localhost 5432
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.


AND
psql -h localhost -l


  Huh!

[rshep...@salmo ~]$ psql -h localhost -l
 List of databases
   Name|   Owner| Encoding 
---++--

 aesi  | sql-ledger | LATIN1
 cms   | rshepard   | UTF8
 postgres  | postgres   | UTF8
 refdb | postgres   | UTF8
 scirefs   | rshepard   | LATIN1
 template0 | postgres   | UTF8
 template1 | postgres   | UTF8
(7 rows)

  So, why can't I connect to a database by entering, for example, 'psql
aesi'?

Thanks,

Rich

--
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] Cannot Start Postgres After System Boot

[Scott Marlowe]

On Thu, Oct 21, 2010 at 11:27 AM, Rich Shepard  wrote:
>  Yet, when I try to access one of my databases I cannot:
>
> [rshep...@salmo ~]$ psql aesi
> psql: could not connect to server: No such file or directory
>        Is the server running locally and accepting
>        connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

So, what do

telnet localhost 5432
AND
psql -h localhost -l

do?

-- 
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] Updates, deletes and inserts are very slow. What can I do make them bearable?

[Scott Marlowe]

On Thu, Oct 21, 2010 at 11:18 AM, Rob Sargent  wrote:
>
>
> On 10/21/2010 10:45 AM, Scott Marlowe wrote:
>> On Thu, Oct 21, 2010 at 10:37 AM, Rob Sargent  wrote:
>>>
>>>
>>> On 10/21/2010 10:27 AM, Scott Marlowe wrote:
 On Thu, Oct 21, 2010 at 9:33 AM, Brian Hirt  wrote:
>>
>> There are only two tables in the query.
>>
>
> Tim,
>
> No, your query is written incorrectly.  I don't understand why you come 
> on to this list all hostile and confrontational.  Regardless, people 
> still try to help you and then you still ignore the advice of people that 
> are giving you the solutions to your problems.

 Maybe he's used to paid commercial support where people are often
 quite rude and hostile to the support staff to try and "motivate" them
 or something?  I've seen it before for sure.

 Again, OP, what does EXPLAIN say about this query?

>>>
>>> Maybe I should re-read, but I didn't feel any confrontation.
>>> Frustration for sure.  OP has clearly tried pretty hard, on some tricky
>>> bits too, but I'm betting all for naught if (as seems likely) it's just
>>> mistaken sql.  "update from" is NOT straight forward.
>>
>> True.  His only real snark was in reponse to the "let me google that
>> for you" link.  OTOH, he's arguing with Tom Lane about whether his SQL
>> is well formed.  There's arguing on the internet is stupid, then
>> there's arguing with Tom Lane about SQL is stupid.
>
> Have to admit when I saw that I said to myself OP needs someone to tell
> him "whoa, big fella".  I've been in similar situations where I was
> "sure" of one thing and the problem must be elsewhere, when of course I
> was wrong about the one thing...

Agreed, don't start tuning your server until you're done tuning your queries.

-- 
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] Cannot Start Postgres After System Boot

[Rich Shepard]

On Wed, 20 Oct 2010, Tom Lane wrote:


Personally, I'd drop all the machinations with checking the pidfile or
removing old socket files.


Tom,

  I didn't write the script; whoever maintains the Slackware package for
PostgreSQL did. Regardless, I'll make the changes you suggest.


In particular, I wonder whether the script's refusal to start if the
pidfile already exists accounts for your report that it fails to
auto-restart after a reboot.


  This clears up my uncertainty. The pidfile should not exist after a clean
shutdown, so it should be removed after a crash, too.


could be reduced to just:

else
su postgres -c 'postgres -D /var/lib/pgsql/data &'
exit 0
fi

I'd also strongly recommend making that be "su - postgres -c ..."
rather than the way it is now; it's failing to ensure that the
postmaster is started with the postgres account's login settings.


  Done. I wondered about the 'su postgres' because when I run that on the
command line I'm asked for the postgres password. I suppose that since
root's running the init file it's not asked.


I'm not sure about your comment that manual start attempts fail with
LOG:  could not bind IPv4 socket: Address already in use
It's pretty hard to believe that that could occur on a freshly
booted system unless the TCP port was in fact already in use ---
ie, either there *is* a running postmaster, or something else is
using port 5432.


  I'm not seeing this now, but running the revised script (as root) still
produces this:

Starting PostgreSQL
3753
3755
3756
3757
3758
16481
PostgreSQL daemon already running
Warning: Missing pid file /var/lib/pgsql/data/postmaster.pid

  Yet, when I try to access one of my databases I cannot:

[rshep...@salmo ~]$ psql aesi
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

  There was no postgres running before I ran /etc/rc.d/rc.postgresql start.
There is also no socket on /tmp.

  I'd greatly appreciate learning why the startup script is not working so I
can be confident that either the rc.postgresql file or my command line
invocation will consistenly work properly to start the server. I will
provide whatever system information is needed to help diagnose and fix this
problem.

Many thanks,

Rich


Many thanks,

Rich

--
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] Updates, deletes and inserts are very slow. What can I do make them bearable?

[Rob Sargent]

On 10/21/2010 10:45 AM, Scott Marlowe wrote:
> On Thu, Oct 21, 2010 at 10:37 AM, Rob Sargent  wrote:
>>
>>
>> On 10/21/2010 10:27 AM, Scott Marlowe wrote:
>>> On Thu, Oct 21, 2010 at 9:33 AM, Brian Hirt  wrote:
>
> There are only two tables in the query.
>

 Tim,

 No, your query is written incorrectly.  I don't understand why you come on 
 to this list all hostile and confrontational.  Regardless, people still 
 try to help you and then you still ignore the advice of people that are 
 giving you the solutions to your problems.
>>>
>>> Maybe he's used to paid commercial support where people are often
>>> quite rude and hostile to the support staff to try and "motivate" them
>>> or something?  I've seen it before for sure.
>>>
>>> Again, OP, what does EXPLAIN say about this query?
>>>
>>
>> Maybe I should re-read, but I didn't feel any confrontation.
>> Frustration for sure.  OP has clearly tried pretty hard, on some tricky
>> bits too, but I'm betting all for naught if (as seems likely) it's just
>> mistaken sql.  "update from" is NOT straight forward.
> 
> True.  His only real snark was in reponse to the "let me google that
> for you" link.  OTOH, he's arguing with Tom Lane about whether his SQL
> is well formed.  There's arguing on the internet is stupid, then
> there's arguing with Tom Lane about SQL is stupid.

Have to admit when I saw that I said to myself OP needs someone to tell
him "whoa, big fella".  I've been in similar situations where I was
"sure" of one thing and the problem must be elsewhere, when of course I
was wrong about the one thing...


-- 
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] Updates, deletes and inserts are very slow. What can I do make them bearable?

[Roberto Scattini]

On Thu, Oct 21, 2010 at 1:37 PM, Rob Sargent  wrote:

>
>
> On 10/21/2010 10:27 AM, Scott Marlowe wrote:
> > On Thu, Oct 21, 2010 at 9:33 AM, Brian Hirt  wrote:
> >>>
> >>> There are only two tables in the query.
> >>>
> >>
> >> Tim,
> >>
> >> No, your query is written incorrectly.  I don't understand why you come
> on to this list all hostile and confrontational.  Regardless, people still
> try to help you and then you still ignore the advice of people that are
> giving you the solutions to your problems.
> >
> > Maybe he's used to paid commercial support where people are often
> > quite rude and hostile to the support staff to try and "motivate" them
> > or something?  I've seen it before for sure.
> >
> > Again, OP, what does EXPLAIN say about this query?
> >
>
> Maybe I should re-read, but I didn't feel any confrontation.
> Frustration for sure.  OP has clearly tried pretty hard, on some tricky
> bits too, but I'm betting all for naught if (as seems likely) it's just
> mistaken sql.  "update from" is NOT straight forward.
>
>
i had the same feeling than brian and scott, but i am one of the
"questioners" not the "answerers" on the list, so i didnt said anything...
but the "i-am-touching-many-parameters-and-doesnt-work" and then complain is
not one of the best strategies...
the SQL sentence is far more complicated than it pretended to be...

-- 
Roberto Scattini
 ___ _
 ))_) __ )L __
((__)(('(( ((_)

Re: [GENERAL] Updates, deletes and inserts are very slow. What can I do make them bearable?

[Scott Marlowe]

On Thu, Oct 21, 2010 at 10:37 AM, Rob Sargent  wrote:
>
>
> On 10/21/2010 10:27 AM, Scott Marlowe wrote:
>> On Thu, Oct 21, 2010 at 9:33 AM, Brian Hirt  wrote:

 There are only two tables in the query.

>>>
>>> Tim,
>>>
>>> No, your query is written incorrectly.  I don't understand why you come on 
>>> to this list all hostile and confrontational.  Regardless, people still try 
>>> to help you and then you still ignore the advice of people that are giving 
>>> you the solutions to your problems.
>>
>> Maybe he's used to paid commercial support where people are often
>> quite rude and hostile to the support staff to try and "motivate" them
>> or something?  I've seen it before for sure.
>>
>> Again, OP, what does EXPLAIN say about this query?
>>
>
> Maybe I should re-read, but I didn't feel any confrontation.
> Frustration for sure.  OP has clearly tried pretty hard, on some tricky
> bits too, but I'm betting all for naught if (as seems likely) it's just
> mistaken sql.  "update from" is NOT straight forward.

True.  His only real snark was in reponse to the "let me google that
for you" link.  OTOH, he's arguing with Tom Lane about whether his SQL
is well formed.  There's arguing on the internet is stupid, then
there's arguing with Tom Lane about SQL is stupid.

-- 
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] Updates, deletes and inserts are very slow. What can I do make them bearable?

[Rob Sargent]

On 10/21/2010 10:27 AM, Scott Marlowe wrote:
> On Thu, Oct 21, 2010 at 9:33 AM, Brian Hirt  wrote:
>>>
>>> There are only two tables in the query.
>>>
>>
>> Tim,
>>
>> No, your query is written incorrectly.  I don't understand why you come on 
>> to this list all hostile and confrontational.  Regardless, people still try 
>> to help you and then you still ignore the advice of people that are giving 
>> you the solutions to your problems.
> 
> Maybe he's used to paid commercial support where people are often
> quite rude and hostile to the support staff to try and "motivate" them
> or something?  I've seen it before for sure.
> 
> Again, OP, what does EXPLAIN say about this query?
> 

Maybe I should re-read, but I didn't feel any confrontation.
Frustration for sure.  OP has clearly tried pretty hard, on some tricky
bits too, but I'm betting all for naught if (as seems likely) it's just
mistaken sql.  "update from" is NOT straight forward.

-- 
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] Updates, deletes and inserts are very slow. What can I do make them bearable?

[Scott Marlowe]

On Thu, Oct 21, 2010 at 9:33 AM, Brian Hirt  wrote:
>>
>> There are only two tables in the query.
>>
>
> Tim,
>
> No, your query is written incorrectly.  I don't understand why you come on to 
> this list all hostile and confrontational.  Regardless, people still try to 
> help you and then you still ignore the advice of people that are giving you 
> the solutions to your problems.

Maybe he's used to paid commercial support where people are often
quite rude and hostile to the support staff to try and "motivate" them
or something?  I've seen it before for sure.

Again, OP, what does EXPLAIN say about this query?

-- 
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] postgres services on amazon

[Greg Smith]

Aljoša Mohorović wrote:

i know, i am using amazon services and have no issues with running
postgres on ec2 but i'm more concern with the fact that it seems that
nobody is planning to provide professional services comparable with
the current mysql based services from amazon


Just because none are there currently doesn't mean nobody is planning to 
offer those services.  PostgreSQL 9.0 adds some features that make it 
particular well suited for cloud-style scaling deployments now, and it's 
a safe bet that somebody is planning product offerings in that area now 
that it's feasible to do so. 


--
Greg Smith, 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services and Support  www.2ndQuadrant.us



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

[GENERAL] Advice needed on application/database authentication/authorization/auditing model

[Tony Cebzanov]

I have a web application with a Postgres backend.  In my initial
prototype, I decided not to have a Postgres database user created for
each application user, opting instead to use my own users table.
Authentication of application users is done via PAM, so no password is
necessary in my users table -- I just let PAM do its thing, and if the
user is authenticated, I check for a record in my application's users
table to see if they're authorized to use the app, along with what
privileges they have, e.g.:

CREATE TYPE USER_ROLE AS ENUM ('User', 'Auditor', 'Administrator');

CREATE TABLE users (
id SERIAL PRIMARY KEY,
username TEXT UNIQUE NOT NULL,
displayname TEXT NOT NULL,
role USER_ROLE NOT NULL DEFAULT 'User'
);

Now that this is moving beyond a prototype stage, I need to tighten up
the authentication/authorization/access control model.  In particular, I
need to add some basic audit trail functionality.  I found a couple of
projects that help make auditing easy (tablelog and EMaj being the most
promising) but they both rely on the database users mapping 1:1 to
application users, which is currently not the case -- right now I've
only got one database user that's used for all of the pooled
connections, so the audit logs are showing that user instead of my
application user.

So, I'm wondering what others have done in similar situations.  It seems
to me like the database connection pooling means I probably need the
connections to be made with a privileged "database superuser" account
that has permission to "SET ROLE" to each and every application user,
then "RESET ROLE" when it's done.  That's a bit of a pain, but doable

Then there's the issue of application roles vs. database roles.  I
wanted to have three roles in this application:  regular users, auditors
(who can do everything regular users can, plus access audit tables to
view audit log tables and potentially restore data from them) and
administrators (who can do everything in the application, but shouldn't
be Postgres superusers.)  Unfortunately, I can't figure out a clever way
to do this mapping, especially because Postgres doesn't allow users to
refer to system tables like pg_authid to do an explicit mapping of app
roles to database roles.

So, does anyone have any suggestions here?  I feel like there's got to
be a way to do this, but I can't find anything relevant in the list
archives.

Thanks.
-Tony

-- 
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] Updates, deletes and inserts are very slow. What can I do make them bearable?

[Brian Hirt]

> 
> There are only two tables in the query.
> 

Tim,

No, your query is written incorrectly.  I don't understand why you come on to 
this list all hostile and confrontational.  Regardless, people still try to 
help you and then you still ignore the advice of people that are giving you the 
solutions to your problems.

--brian


-- 
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] full vacuum cancelation

[Vick Khera]

On Thu, Oct 21, 2010 at 9:15 AM, Viktor Bojović
 wrote:
> my full vacuuming of one table runs still after four days. Is it safe to
> cancel it by pressing Ctrl+c?

Is it really running (as in progressing with its work) or is it
blocking waiting for a lock?  Check the pg_stat_activity view to see
what it is doing.  How big is this table?

-- 
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] Custom cache implemented in a postgresql C function

[A.M.]

On Oct 20, 2010, at 7:44 PM, Gabi Julien wrote:

> Hi,
> 
> Here is my problem: I have a postgresql C function that looks like this:
> 
> Datum filter(PG_FUNCTION_ARGS);
> 
> It takes identifiers and queries a bunch of tables and ends up returning true 
> or false. So far nothing difficult except that we want better performance. 
> The function was already optimized to the best of my abilities and changing 
> the structure of the database would not help. However, having a cache would 
> be the perfect solution. I could implement this cache outside of postgresql 
> if need be but nothing could beat implementing this directly in a postgresql 
> C function.
> 
> So this is what I want, a custom cache built into a postgresql C function. 
> Since postgresql uses different processes, it would be best to use the shared 
> memory. Can this be done safely? At its core, the cache could be considered 
> as simple as a map protected by a mutex. With postgresql, I first need to 
> initialized some shared memory. This is explained at the end of this link:
> 
> http://www.postgresql.org/docs/8.2/static/xfunc-c.html
> 
> However, it sounds like I need to reserve the shared memory in advance using:
> 
> void RequestAddinShmemSpace(int size)
> 
> In my case, I do not know how big my cache will be. I would preferably 
> allocate the memory dynamically. Is this possible? In any case, am I trying 
> to reinvent the wheel here? Is there already a shared map or a shared hash 
> structure available in postgresql?
> 
> If shared memory turns out too difficult to use, I could create separate 
> caches for each postgresql processes. This would be a waste of space but it 
> might be better then nothing. In this case, do I need to make my code thread 
> safe? In other words, is postgresql using more then one thread per processes?

Apart from the other suggestions made, another option could be to use your own 
shared memory which you allocate and manage yourself (without postgresql 
managing it). You could implement a simple least-recently-used cache to purge 
old entries as the cache grows.

Cheers,
M
-- 
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] Updates, deletes and inserts are very slow. What can I do make them bearable?

[Richard Broersma]

On Wed, Oct 20, 2010 at 7:24 PM, Tim Uckun  wrote:

> update cu
> set screenshot_file_name = tu.screenshot_file_name,
>    screenshot_content_type  = tu.screenshot_content_type,
>    screenshot_file_size = tu.screenshot_file_size,
>    screenshot_status  = tu.screenshot_status
>
> from  cu
> inner join tu on tu.cu_id = cu.id

> I am having similar problems with deletes and inserts. Trying to
> delete even a few thousand records takes forever.  The selects seem to
> be just fine.

> Where is the FAST button for postgres updates? What parameter do I
> have to set in order to update 6000 records in under an hour?

Is this a pass-through query or is it an ordinary query in Access?
If it is an an ordinary query, I'd expect that to be one cause since
MS-Access will re-write this query so that it updates a single tuple
at a time.  So your single update statement becomes 6000 single tuple
update statements.  This is part of MS-Access's optimistic locking
mechanism.

Also, this might be an ODBC issue (I have the sample problem on one of
my laptop that is memory constrained but I haven't taken the time to
identify the actual problem).  What happens when you issue this query
directly from PSQL, does the query run much faster.


-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

-- 
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] Generate a dynamic sequence within a query

[David Kerr]

On Wed, Oct 20, 2010 at 10:32:15PM -0400, Josh Kupershmidt wrote:
- On Wed, Oct 20, 2010 at 6:22 PM, David Kerr  wrote:
- > I know I've seen posts on how to do this, but i can't seem to find them.
- >
- > I've got a data set
- >
- > A, B
- > A, C
- > A, D
- > [...]
- >
- > and so on
- >
- > and i'd like to be able to wite a query that would result in
- >
- > 1,A,B
- > 2,A,C
- > 3,A,D
- > [...]
- >
- > PG version is 8.3.
- 
- If you can upgrade to 8.4, you could use the row_number() window
- function which is perfectly suited to this task, should be as simple
- as:
- 
- SELECT row_number() OVER (), * FROM tablename;

Ah, no chance of that for a while. figures all the  fun stuff is always a 
version
away =)

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] full vacuum cancelation

[Tom Lane]

=?UTF-8?Q?Viktor_Bojovi=C4=87?=  writes:
> my full vacuuming of one table runs still after four days. Is it safe to
> cancel it by pressing Ctrl+c?

I assume this is a pre-9.0 Postgres version?

Depending on how far along the vacuum actually is, there's a possibility
that trying to cancel it will cause a PANIC, but that will not damage
your data --- the worst consequence would be killing any other sessions
that are running concurrently.

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] Custom cache implemented in a postgresql C function

[Tom Lane]

Alban Hertroys  writes:
> That sounds like your function would classify as a STABLE function
> within Postgres, did you define it as such? Postgres will cache the
> results of STABLE (and IMMUTABLE) functions all by itself, in which
> case you may not need your custom cache.

Uh, no it won't.  It will pre-evaluate immutable functions that are
called with constant arguments, which is not the same thing at all.

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] postgres services on amazon

[Aljoša Mohorović]

On Thu, Oct 21, 2010 at 4:20 PM, Jos  wrote:
> I am not aware of anybody offering a postgresql server.  But it is
> very easy to build your own one.  The advantage is that you can
> control your own backup schemes.

i know, i am using amazon services and have no issues with running
postgres on ec2 but i'm more concern with the fact that it seems that
nobody is planning to provide professional services comparable with
the current mysql based services from amazon.
ibm and oracle also have services based on aws, although not actually
comparable with amazon rds.

Aljosa

-- 
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] Old values in statement triggers?

[Brian Hirt]

Thanks Josh,

On Oct 21, 2010, at 7:49 AM, Josh Kupershmidt wrote:

> 2010/10/21 Grzegorz Jaśkiewicz :
>> OLD.column_name
>> NEW.column_name ?
> 
> I believe OP is asking specifically about statement-level triggers. As

Yup.

> the docs 
> 
> say:
> | Statement-level triggers do not currently have any way to
> | examine the individual row(s) modified by the statement.
> 

I don't know how I didn't see that in the docs when I was looking.  It must 
have been sleepy morning eyes or lack of coffee.

> What I've done is to have a row-level trigger that populates a
> temporary table with "interesting" changes that need further
> processing, and then a statement-level trigger which does bulk-updates
> based on what's in that temporary table. This comes in quite handy
> when bulk-loading data, e.g. with COPY.
> 

I'll look at doing something like you describe, although I wonder if the 
overhead of doing a row trigger and then a mass update at the end with a 
statement trigger will really be worth it for what I'm doing.   I might just 
end up doing only a row trigger.

--brian
-- 
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] postgres services on amazon

[Jos]

On Tue, Oct 19, 2010 at 3:35 PM, Aljosa Mohorovic
 wrote:
> i was wondering if anybody is providing postgres services comparable
> to http://aws.amazon.com/rds/ ?
>
> Aljosa Mohorovic
>
I am not aware of anybody offering a postgresql server.  But it is
very easy to build your own one.  The advantage is that you can
control your own backup schemes.

--Jos Verbeeck

-- 
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] 9.0 SSL renegotiation failure restoring data

[Dave Page]

On Thu, Oct 21, 2010 at 2:40 PM, Bruce Momjian  wrote:
> Andrus wrote:
>> > Uh, we don't ship SSL in the server.
>> > We ship code that _uses_ ssl, so I
>> > would look at your operating system to see what version of SSL you have,
>> > and perhaps update that.
>>
>> I installed postgres 9.0 RC in Windows from official link and looked into
>> bin:
>>
>>  Directory of C:\Program Files\PostgreSQL\9.0\bin
>>
>> 15.11.2009  16:37           200_704 ssleay32.dll
>> 15.11.2009  16:37         1_017_344 libeay32.dll
>>
>> How to update them so that they match with linux server ?
>
> Oh, interesting.  So we install SSL with our Win32 install, and I assume
> that is a new/correct version of ssl.
>
> Dave, this person says they are getting disconnected regularly and we
> thought it was ssl renegotiation, but not I am unclear what is causing
> the disconnection.

It's possible - it depends on what version of OpenSSL was available at
the time we started testing 9.0.

Dharmendra, can you please check and upgrade the build servers if necessary?

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

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL 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] a query on stored procedures/functions in pgsql

[Adrian Klaver]

On Wednesday 20 October 2010 9:48:39 pm Neil D'Souza wrote:
> > You have same plpgsql identifiers as sql identifiers, and because
> > plpgsql identifiers has higher priority, your query is broken. For
> > simple functions like this don't use a plpgsql language - use sql
> > language instead.
>
> Thank you for the quick reply. The example I constructed was
> specifically for this post. I modified the function as below and it
> works fine now. It would be great if the point you mentioned was a
> note in the PGSQL Documentation (or did I miss it). In case I didnt
> miss it, Is there anyone I have to write to, to help get this note in?
>

For the record it is in the docs twice:

http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT
"Caution

PL/pgSQL will substitute for any identifier matching one of the function's 
declared variables; it is not bright enough to know whether that's what you 
meant! Thus, it is a bad idea to use a variable name that is the same as any 
table, column, or function name that you need to reference in commands within 
the function. For more discussion see Section 38.10.1. "


http://www.postgresql.org/docs/8.4/interactive/plpgsql-implementation.html#PLPGSQL-VAR-SUBST

"The substitution mechanism will replace any token that matches a known 
variable's name. This poses various traps for the unwary. For example, it is a 
bad idea to use a variable name that is the same as any table or column name 
that you need to reference in queries within the function, because what you 
think is a table or column name will still get replaced. In the above example, 
suppose that logtable has column names logtxt and logtime, and we try to write 
the INSERT as...

"



-- 
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

Re: [GENERAL] Old values in statement triggers?

[Josh Kupershmidt]

2010/10/21 Grzegorz Jaśkiewicz :
> OLD.column_name
> NEW.column_name ?

I believe OP is asking specifically about statement-level triggers. As
the docs 
say:

| Statement-level triggers do not currently have any way to
| examine the individual row(s) modified by the statement.

What I've done is to have a row-level trigger that populates a
temporary table with "interesting" changes that need further
processing, and then a statement-level trigger which does bulk-updates
based on what's in that temporary table. This comes in quite handy
when bulk-loading data, e.g. with COPY.

Josh

-- 
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] 9.0 SSL renegotiation failure restoring data

[Bruce Momjian]

Andrus wrote:
> > Uh, we don't ship SSL in the server.
> > We ship code that _uses_ ssl, so I
> > would look at your operating system to see what version of SSL you have,
> > and perhaps update that.
> 
> I installed postgres 9.0 RC in Windows from official link and looked into 
> bin:
> 
>  Directory of C:\Program Files\PostgreSQL\9.0\bin
> 
> 15.11.2009  16:37   200_704 ssleay32.dll
> 15.11.2009  16:37 1_017_344 libeay32.dll
> 
> How to update them so that they match with linux server ?

Oh, interesting.  So we install SSL with our Win32 install, and I assume
that is a new/correct version of ssl.

Dave, this person says they are getting disconnected regularly and we
thought it was ssl renegotiation, but not I am unclear what is causing
the disconnection.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
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] Old values in statement triggers?

[Grzegorz Jaśkiewicz]

OLD.column_name
NEW.column_name ?

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

[GENERAL] Old values in statement triggers?

[Brian Hirt]

Is it possible to reference the old values in a statement trigger using 
plpgsql?   I'm looking for something similar to NEW and OLD that row triggers 
have, but I don' see anything @ 
http://www.postgresql.org/docs/current/static/plpgsql-trigger.html which 
doesn't really offer any examples for statement triggers.

If what I'm looking for doesn't exist, are there any common workarounds that 
people use to find the set of updated/inserted/deleted rows?

Thanks in advance,

Brian

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

[GENERAL] full vacuum cancelation

[Viktor Bojović]

Hi,
my full vacuuming of one table runs still after four days. Is it safe to
cancel it by pressing Ctrl+c?

-- 
---
Viktor Bojović
---
Wherever I go, Murphy goes with me

[GENERAL] GridSQL

[zab08]

thanks for share paper of GridSQL or others!

Re: [GENERAL] Generate a dynamic sequence within a query

[Thomas Kellerer]

Alban Hertroys, 21.10.2010 13:43:

I'm currently using WebFOCUS at work and they have a LAST operator,
referring to the value a column had in the last returned row. That's
pretty good for stuff like this, so I wonder if it wouldn't be
beneficial to have something like that in Postgres?


Already there since 8.4 ;)
Look into the windowing functions (in Oracle they are called analytical 
functions)

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

Thomas


--
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] Generate a dynamic sequence within a query

[Alban Hertroys]

On 21 Oct 2010, at 24:28, Raymond O'Donnell wrote:

>> and i'd like to be able to wite a query that would result in
>> 
>> 1,A,B
>> 2,A,C
>> 3,A,D
>> [...]
>> 
>> PG version is 8.3.
>> 
>> Any ideas?
> 
> You probably want generate_series():
> 
>   http://www.postgresql.org/docs/8.3/static/functions-srf.html


I'm currently using WebFOCUS at work and they have a LAST operator, referring 
to the value a column had in the last returned row. That's pretty good for 
stuff like this, so I wonder if it wouldn't be beneficial to have something 
like that in Postgres?

SQL isn't FOCUS, but in SQL it would work something like this:

SELECT COALESCE(LAST foo +1, 1) AS foo, bar FROM table;
foo | bar
+-
  1 | Apple
  2 | Banana
  3 | Orange
  4 | Lemon

Or for fun,
SELECT COALESCE(LAST foo *2, 1) AS foo, bar || COALESCE(LAST bar, '') AS bar 
FROM table;
foo | bar
+
  1 | Apple
  2 | BananaApple
  4 | OrangeBananaApple
  8 | LemonOrangeBananaApple

Of course being able to use LAST requires that there's still a copy of the last 
returned row lingering in a buffer somewhere. If we have that, great! If we 
don't, well, it depends on how much the devs desire such a feature :)

Alban Hertroys

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


!DSPAM:737,4cc0277010283330040792!



-- 
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] 9.0 SSL renegotiation failure restoring data

[Andrus]

Uh, we don't ship SSL in the server.
We ship code that _uses_ ssl, so I
would look at your operating system to see what version of SSL you have,
and perhaps update that.


I installed postgres 9.0 RC in Windows from official link and looked into 
bin:


Directory of C:\Program Files\PostgreSQL\9.0\bin

15.11.2009  16:37   200_704 ssleay32.dll
15.11.2009  16:37 1_017_344 libeay32.dll

How to update them so that they match with linux server ?

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] Custom cache implemented in a postgresql C function

[Alban Hertroys]

On 21 Oct 2010, at 1:44, Gabi Julien wrote:

> Hi,
> 
> Here is my problem: I have a postgresql C function that looks like this:
> 
> Datum filter(PG_FUNCTION_ARGS);
> 
> It takes identifiers and queries a bunch of tables and ends up returning true 
> or false. So far nothing difficult except that we want better performance. 
> The function was already optimized to the best of my abilities and changing 
> the structure of the database would not help. However, having a 

That sounds like your function would classify as a STABLE function within 
Postgres, did you define it as such? Postgres will cache the results of STABLE 
(and IMMUTABLE) functions all by itself, in which case you may not need your 
custom cache. The default is to classify a function as VOLATILE, meaning the 
results aren't suitable for caching.

Another possible solution is to store the results of your function (or of the 
queries it performs) in a separate table[1] that would function as a cache of 
sorts. The benefit is that the table gets managed by Postgres, so you won't 
have to worry about stuff like spilling to disk if the cache grows too large to 
fit in (available) memory.

[1] A TEMP TABLE wouldn't work, as it isn't visible to other sessions, although 
you could create one per session of course.

Of course, with a custom cache you have more control over how it behaves, so 
that may still be your best solution.

Alban Hertroys

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


!DSPAM:737,4cc01f6410281645420170!



-- 
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] Question on Explain : Index Scan

[Mathieu De Zutter]

On Thu, Oct 21, 2010 at 3:47 AM, DM  wrote:
> I was hoping the optimizer would do a join using index scan.
>
> Could some one please explain me why its not doing an index scan rather than
> sequential scan .


A index scan would be probably slower here because you're asking for a
lot of rows. A lot of rows means a lot of I/O, and an index scan is
more I/O intensive (since it has to read the index too). If you limit
the result (by being more selective in your where clause, just like
you do in the first two queries), postgres will most likely switch to
index scan.

You can see for yourself if index-scan would be faster in your case by
running the following command before "explain (analyze)":

set enable_seqscan = off;

BTW, try to use explain analyze instead of explain, that way you'll
see the actual timings too instead of just the planner estimates.

Kind regards,
Mathieu

-- 
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] Updates, deletes and inserts are very slow. What can I do make them bearable?

[Scott Marlowe]

On Wed, Oct 20, 2010 at 11:05 PM, Tim Uckun  wrote:
>>
>> No, it isn't.  This is a three-way join between consolidated_urls, cu,
>> and tu --- the fact that cu is the same underlying table as
>
> cu is an alias for consolidated_urls. tu is an alias for trending_urls.
>
> There are only two tables in the query.

What does

explain update (your query continues) say about it?  I'm betting you
get a nice big row count in there somewhere.

-- 
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] Upgrade from 8.3.3

[Richard Huxton]

On 21/10/10 00:04, Rich Shepard wrote:

While I'm re-learning how to properly start postgres after a reboot,
I'd like recommendations on upgrading my current 8.3.3 to a newer
version. Since I'm the only one currently using the system (but I'll
be using CMS Made Simple for my revised web site and that works only
with the 8.x releases), I wonder if I should upgrade to 8.4.5 or 9.0.
I suspect the former, but I'd appreciate thoughts from more
knowledgeable folks here.


I'd be surprised if there was anything in CMS Made Simple that would 
work with 8.4 and not 9.0. Have they said it doesn't or is it just not 
tested against it?


--
  Richard Huxton
  Archonet Ltd

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