Re: [GENERAL] Server process crash - Segmentation fault

[Leif Jensen]

   Hello Tom, Adrian

   Thank you for your help and the patch. Things works nicely for me now :-).

 Leif


- Original Message -
> Adrian Klaver  writes:
> > On 05/08/2014 07:19 AM, Tom Lane wrote:
> >> 9.3 patch is here:
> >> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=fc58c39d468587467c7c55b349c28167794eadaf
> 
> > Alright, so I obviously linked to the wrong patch because I pointed
> > at
> > HEAD and not REL9_3_STABLE.
> 
> I think those patches are the same, actually, but I was trying to be
> careful.
> 
> > What I am trying to figure out is what is
> > the distinction between commit and commitdiff?
> 
> The "commitdiff" link shows you the actual diffs in the patch, the
> other
> one doesn't.
> 
> In practice, Leif's going to want to hit the "patch" link anyway to
> get a
> clean downloadable patch; so likely what we should have pointed him at
> is
> http://git.postgresql.org/gitweb/?p=postgresql.git;a=patch;h=fc58c39d468587467c7c55b349c28167794eadaf
> I'm just in the habit of looking at the "commitdiff" versions of the
> web
> pages as being the best readability/information tradeoff for casual
> examination of a patch.
> 
> 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] How to fix lost synchronization with server

[Andrus]

Where to get this fix in binary form for Windows 32-bit ?

Here, but you will need to wait until 9.3.5 is out:
http://www.postgresql.org/download/windows/.


When 9.3.5 or Windows x32 nightly build or test build  will be out ?

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] SSL Compression - doesn't work?

[Krystian Bigaj]

I've build OpenSSL with zlib (perl Configure VC-WIN32 no-asm zlib-dynamic
--prefix=...) and now compression works correctly.

After executing:
SELECT lpad('', 1024*1024, 'A')
there is only about 13kB of TCP transfer instead of ~1MB.

Thanks again!

However I'm still curious why windows build doesn't have this enabled by
default. Is it a potential compatibility issues, or just an oversight?

I would like to see a note in docs about that issue. I've read that docs
about sslcompression before, also I knew that PG on Windows is build with
zlib support (for pg_dump/pg_restore), and because of this I wrongly
assumed that OpenSSL is build with zlib too. Additionally that
"Encryption=SSL encrypted" and "SSL Compression=yes" in pgAdmin made me
think that it's a bug in PG.

Best regards,
Krystian Bigaj


On 8 May 2014 17:28, Terence Ferraro  wrote:

> You mentioned you are using the Windows version; unless something has
> changed recently in their build process, the included openssl library is
> not linked against zlib and therefore compression is not possible unless
> you recompile the Windows version yourself.
>
>
> *Terence J. Ferraro*
>
> On Thu, May 8, 2014 at 9:36 AM, Adrian Klaver 
> wrote:
>
>> On 05/08/2014 01:22 AM, Krystian Bigaj wrote:
>>
>>> Hi,
>>>
>>> I'm wondering how, and if SSL compression works correctly.
>>>
>>> Here is how I tested it:
>>> - PostgreSQL 9.3.4 x86 on Windows 7 x64
>>> - .crt/.key files by openssl, and placed in database cluster folder
>>> - postgres.exe ran with: --ssl="on" --ssl_cert_file="test.crt"
>>> --ssl_key_file="test.key"
>>> - connection made by pgadmin with SSL=prefer, SSL Compression=True
>>> - when connected I see in properties: Encryptions=SSL encrypted, SSL
>>> Compression=yes
>>> - I've dumped TCP transfer and I can tell that data is encrypted
>>>
>>> Now when I run query like:
>>> SELECT lpad('', 1024*1024, 'A')
>>>
>>> then I see that there is a TCP transfer of 1,01MB (so 1MB of string
>>> data, and some pg header/data).
>>>
>>> If I turn off SSL Compression data transfer between postgres and pgadmin
>>> is still 1,01MB (but in properties I see SSL Compression=no)
>>>
>>> It looks like SSL compression doesn't work, or am I missing something?
>>>
>>
>> http://www.postgresql.org/docs/9.3/static/libpq-connect.html
>>
>> sslcompression
>>
>> If set to 1 (default), data sent over SSL connections will be
>> compressed (this requires OpenSSL version 0.9.8 or later). If set to 0,
>> compression will be disabled (this requires OpenSSL 1.0.0 or later). This
>> parameter is ignored if a connection without SSL is made, or if the version
>> of OpenSSL used does not support it.
>>
>> So what version of OpenSSL are you using?
>>
>>
>>> Best regards,
>>> Krystian Bigaj
>>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.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] Crosstab function

[Hengky Liwandouw]

Thanks Sim, a very usefull information.

 

 

From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Sim Zacks
Sent: Wednesday, May 07, 2014 7:33 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Crosstab function

 

What I have done in the past to build a generic reporting application is to
have the function write the results you want in a table and return the
tablename and then have the client code call select * from that table. 

My standard report tablename is tblreport || userid;
It gets dropped at the beginning of the function, so it is basically a temp
table that doesn't interfere with any other users.

Example:
execute 'drop table if exists reports.tblreport' || v_userid ;
execute 'drop sequence if exists reports.tblreport' || v_userid ||
'_id_seq; create sequence reports.tblreport' || v_userid || '_id_seq';
v_sql=' create table reports.tblreport' || v_userid || ' as ';

Sim



 

Re: [GENERAL] How to fix lost synchronization with server

[Michael Paquier]

On Fri, May 9, 2014 at 2:37 AM, Andrus  wrote:
> Where to get this fix in binary form for Windows 32-bit ?
Here, but you will need to wait until 9.3.5 is out:
http://www.postgresql.org/download/windows/.
If you are in a hurry, you can still compile manually pg_dump and
deploy it where you need it...

> Where to get its compiled version or how to compile it in Windows ?
There are many ways to do that, the most popular methods involving
MinGW or msvc. More information is available on the docs as well:
http://www.postgresql.org/docs/9.3/static/install-windows.html

And here are some more resources.
https://wiki.postgresql.org/wiki/Running_%26_Installing_PostgreSQL_On_Native_Windows
https://wiki.postgresql.org/wiki/Building_With_MinGW

Regards,
-- 
Michael


-- 
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] Pgpool starting problem

[Tatsuo Ishii]

> Hi all,
> 
> If I turn use_watchdog = off in the pgpool.conf file, then in spite of the
> system being configured as master-slave, a socket file is created in /tmp,
> and everything starts normally with no errors. If, however, I set
> use_watchdog = on, pgpool will abort on startup with the following errors:
> 
> 2014-05-08 14:40:22 ERROR: pid 32893: watchdog: wd_init failed
> 2014-05-08 14:40:22 ERROR: pid 32893: wd_main error
> 2014-05-08 14:40:22 ERROR: pid 32893: unlink(/tmp/.s.PGSQL.9898) failed: No
> such file or directory
> 
> It seems pretty consistent doing this, and I can flip use_watchdog on and
> off and the same behavior repeating. Any ideas?

You didn't give any pgpool version, configuration file info, I cannot
guess what's going on. There's a detailed installation document here:

http://www.pgpool.net/pgpool-web/contrib_docs/watchdog_master_slave_3.3/en.html

Despite you follow the instruction you still have problems, you should ask 
pgpool ML.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp


-- 
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] Receiving many more rows than expected

[Adrian Klaver]

On 05/08/2014 03:11 PM, Vincent de Phily wrote:

On Thursday 08 May 2014 06:30:39 Adrian Klaver wrote:

On 05/08/2014 04:09 AM, Vincent de Phily wrote:

Hello,






DO_LOOP is tested in a few places where we can make a clean exit. A cronjob
will restart the process if it is not or badly running.


  curs.execute(query)
  results = curs.fetchall()
  rlen = len(results)

  if rlen > 0:
  LOG.debug("Fetched %d rows", rlen)


 # What do you see in LOG for rlen values?


The histogram shows a large amount of small values, progressively becoming
rarer for bigger values, up to value 5000 which is very frequent again
(depending on the day, between 0.5 and 5% of queries return the maximum number
of rows), then a handfull freak values of ~9k, 11k, 15k, 33k, 46k, 107k, etc
which cause my problems.


So just to be clear this the value for rlen and that value and log 
message only appear in the loop above?


As Sim suggested, it might be worth it to add a unique id to the above 
message to prove or not that the values are coming from where you think 
they are.





  if rlen == 0:
  # [...] wait for notification...
  continue

  # [...] Enqueue batch and let other threads process it.
  # [...] Those threads will eventually delete the processed rows
  from
  #   the foo table.

The problem is that sometimes (once every few days at about 2-300K queries
per day) I get many more rows than the max 5000 I asked for (I've seen up
to 25k). And I'm getting timeouts and other problems as a result.



And there is no instance of the UPDATE query that is unconstrained and a 
code path to that query?


Or as Sim suggested another copy of this code without the LIMIT?

Also what happens in the rlen == 0 case?


Any idea ? Thanks.





--
Adrian Klaver
adrian.kla...@aklaver.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] Receiving many more rows than expected

[Vincent de Phily]

On Thursday 08 May 2014 06:30:39 Adrian Klaver wrote:
> On 05/08/2014 04:09 AM, Vincent de Phily wrote:
> > Hello,
> > 
> > I'm processing records in batches using PG 9.1.12, python 2.7, and psycopg
> 
> > 2.5.2 :
> Comments in the code below:
> > def enqueue_loop(q):
> >  curs = DB_HANDLER.cursor()
> >  query = """UPDATE foo SET processing = 't' WHERE id IN
> >  
> > (SELECT id FROM foo WHERE processing = 'f' ORDER BY id ASC
> > LIMIT
> > 
> >  5000 FOR UPDATE)  RETURNING *"""
> 
> # Where is this query actually run?

Sorry, I edited my code too much, it's actually executed at the first line of 
the loop, I added it below.

> >  while DO_LOOP: #the whole program eventually stops if this is false
> 
> # What cause DO_LOOP to go false?

Either when receiving a signal from the OS (registered with 
"signal.signal(signal.SIGINT, stop_main)") or when the topmost try-catch-
reinitialize-retry loop has caught a quick sucession of exceptions.

DO_LOOP is tested in a few places where we can make a clean exit. A cronjob 
will restart the process if it is not or badly running.

> >  curs.execute(query)
> >  results = curs.fetchall()
> >  rlen = len(results)
> >  
> >  if rlen > 0:
> >  LOG.debug("Fetched %d rows", rlen)
> 
> # What do you see in LOG for rlen values?

The histogram shows a large amount of small values, progressively becoming 
rarer for bigger values, up to value 5000 which is very frequent again 
(depending on the day, between 0.5 and 5% of queries return the maximum number 
of rows), then a handfull freak values of ~9k, 11k, 15k, 33k, 46k, 107k, etc 
which cause my problems.

> >  if rlen == 0:
> >  # [...] wait for notification...
> >  continue
> >  
> >  # [...] Enqueue batch and let other threads process it.
> >  # [...] Those threads will eventually delete the processed rows
> >  from
> >  #   the foo table.
> > 
> > The problem is that sometimes (once every few days at about 2-300K queries
> > per day) I get many more rows than the max 5000 I asked for (I've seen up
> > to 25k). And I'm getting timeouts and other problems as a result.
> > 
> > The id column is your typical primary key integer with a unique index.
> > I've
> > checked the problematic cases and there are no id gaps or duplicate rows.
> > There are multiple threads in the program, but only the main thread is
> > running enqueue_loop(). I'm not sure if this is a server or a driver
> > issue.
> > 
> > 
> > Any idea ? Thanks.

-- 
Vincent de Phily


-- 
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] Oracle to PostgreSQL replication

[Anand Kumar, Karthik]

We use symmetricDS for this. Works pretty well.

http://www.symmetricds.org/


From: Serge Fonville mailto:serge.fonvi...@gmail.com>>
Date: Wednesday, May 7, 2014 at 2:49 AM
To: Geoff Montee mailto:geoff.mon...@gmail.com>>
Cc: Sameer Kumar mailto:sameer.ku...@ashnik.com>>, 
PostgreSQL General Discussion Forum 
mailto:pgsql-general@postgresql.org>>
Subject: Re: [GENERAL] Oracle to PostgreSQL replication

Hi,

I need to setup a replication process for continuously replicating changes 
happening in an Oracle Database to a PostgreSQL database.

My Oracle Database is version 11.2 and setup as a cluster with RAC
My Postgres database version is 9.2

Oracle Database is running in Solaris and PostgreSQL is running on RHEL.

Is there any commercial or open source tool available to achieve this?

How about EnterpriseDB XDB 
replication?

Kind regards/met vriendelijke groet,

Serge Fonville

http://www.sergefonville.nl


2014-05-07 11:44 GMT+02:00 Geoff Montee 
mailto:geoff.mon...@gmail.com>>:

On Wed, May 7, 2014 at 12:31 AM, Sameer Kumar 
mailto:sameer.ku...@ashnik.com>> wrote:
Hi,


I need to setup a replication process for continuously replicating changes 
happening in an Oracle Database to a PostgreSQL database.


My Oracle Database is version 11.2 and setup as a cluster with RAC
My Postgres database version is 9.2

Oracle Database is running in Solaris and PostgreSQL is running on RHEL.

Is there any commercial or open source tool available to achieve this?



Continuent's Tungsten Replicator apparently offers Oracle to MySQL replication. 
There's a wiki page that suggests PostgreSQL support was in development at one 
time. I'm not sure how far they got, or if they are still working on it.

http://www.continuent.com/solutions/replication

https://wiki.postgresql.org/wiki/Tungsten

Geoff Montee

Re: [GENERAL] Ubuntu Packages / Config Files

[Stephan Fabel]

Stuart,

thanks for your reply.

On 05/08/2014 12:47 AM, Stuart Bishop wrote:
> recovery.conf goes into $DATADIR, which is
> /var/lib/postgresql/9.1/main in your case. I rationalize this by
> considering it database state, rather than configuration, since
> commands like 'pg_ctl promote' or using a trigger file will mess with
> it.

Fair enough; let's change the file's extension and avoid confusion
then... ;-)

>> The problem is that recovery.conf gets ignored in this case. I can add
>> another symlink pointing to it into the data directory, for example, or
>> copy the file there, then it works, but honestly this has cost me a LOT
>> of time figuring out.
> 
> Having the two, with the master in /etc, will confuse you. And quite
> possibly PostgreSQL if it needs to promote the database.

OK, I will leave only one copy of recovery.conf in $DATADIR to avoid
problems.

Thanks,
Stephan


-- 
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] Pgpool starting problem

[David G Johnston]

Jay at Verizon wrote
> Hi all,
> 
> If I turn use_watchdog = off in the pgpool.conf file, then in spite of the
> system being configured as master-slave, a socket file is created in /tmp,
> and everything starts normally with no errors. If, however, I set
> use_watchdog = on, pgpool will abort on startup with the following errors:
> 
> 2014-05-08 14:40:22 ERROR: pid 32893: watchdog: wd_init failed
> 2014-05-08 14:40:22 ERROR: pid 32893: wd_main error
> 2014-05-08 14:40:22 ERROR: pid 32893: unlink(/tmp/.s.PGSQL.9898) failed:
> No such file or directory
> 
> It seems pretty consistent doing this, and I can flip use_watchdog on and
> off and the same behavior repeating. Any ideas?
> --
> Jay

I'm doubting you are providing enough information for someone to actually
provide useful help.

Beyond that this is not the correct list for this question.  pgPoolII is a
standalone product - i.e., not part of PostgreSQL core which is what this
list to dedicated to.  The proper links, as well as documentation, can be
found at:

http://www.pgpool.net/mediawiki/index.php/Main_Page

I suggest posting the actual configuration file along with detailing exactly
how (and on what platform) you are running the various applications.

It is possible that O/S user permissions prevent pgPool from seeing or
interacting with the socket file.

David J.





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Pgpool-starting-problem-tp5803276p5803279.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] Pgpool starting problem

[Jay at Verizon]

Hi all,

If I turn use_watchdog = off in the pgpool.conf file, then in spite of the
system being configured as master-slave, a socket file is created in /tmp,
and everything starts normally with no errors. If, however, I set
use_watchdog = on, pgpool will abort on startup with the following errors:

2014-05-08 14:40:22 ERROR: pid 32893: watchdog: wd_init failed
2014-05-08 14:40:22 ERROR: pid 32893: wd_main error
2014-05-08 14:40:22 ERROR: pid 32893: unlink(/tmp/.s.PGSQL.9898) failed: No
such file or directory

It seems pretty consistent doing this, and I can flip use_watchdog on and
off and the same behavior repeating. Any ideas?
--
Jay



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Pgpool-starting-problem-tp5803276.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] SSL Compression - doesn't work?

[Krystian Bigaj]

Thanks! That would make sense why it doesn't work on Windows.

Do you know why PG build for Windows ships zlib support enabled for PG
(mostly pg_dump/pg_restore) but disabled for OpenSSL?

Best regards,
Krystian Bigaj


On 8 May 2014 17:28, Terence Ferraro  wrote:

> You mentioned you are using the Windows version; unless something has
> changed recently in their build process, the included openssl library is
> not linked against zlib and therefore compression is not possible unless
> you recompile the Windows version yourself.
>
>
> *Terence J. Ferraro*
>
> On Thu, May 8, 2014 at 9:36 AM, Adrian Klaver 
> wrote:
>
>> On 05/08/2014 01:22 AM, Krystian Bigaj wrote:
>>
>>> Hi,
>>>
>>> I'm wondering how, and if SSL compression works correctly.
>>>
>>> Here is how I tested it:
>>> - PostgreSQL 9.3.4 x86 on Windows 7 x64
>>> - .crt/.key files by openssl, and placed in database cluster folder
>>> - postgres.exe ran with: --ssl="on" --ssl_cert_file="test.crt"
>>> --ssl_key_file="test.key"
>>> - connection made by pgadmin with SSL=prefer, SSL Compression=True
>>> - when connected I see in properties: Encryptions=SSL encrypted, SSL
>>> Compression=yes
>>> - I've dumped TCP transfer and I can tell that data is encrypted
>>>
>>> Now when I run query like:
>>> SELECT lpad('', 1024*1024, 'A')
>>>
>>> then I see that there is a TCP transfer of 1,01MB (so 1MB of string
>>> data, and some pg header/data).
>>>
>>> If I turn off SSL Compression data transfer between postgres and pgadmin
>>> is still 1,01MB (but in properties I see SSL Compression=no)
>>>
>>> It looks like SSL compression doesn't work, or am I missing something?
>>>
>>
>> http://www.postgresql.org/docs/9.3/static/libpq-connect.html
>>
>> sslcompression
>>
>> If set to 1 (default), data sent over SSL connections will be
>> compressed (this requires OpenSSL version 0.9.8 or later). If set to 0,
>> compression will be disabled (this requires OpenSSL 1.0.0 or later). This
>> parameter is ignored if a connection without SSL is made, or if the version
>> of OpenSSL used does not support it.
>>
>> So what version of OpenSSL are you using?
>>
>>
>>> Best regards,
>>> Krystian Bigaj
>>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>

Re: [GENERAL] How to fix lost synchronization with server

[Andrus]

But I wouldn't
call that a reliable fix.  You'd be better off applying the patch.


Where to get this fix in binary form for Windows 32-bit ?

pg_dump.exe uses files below [1]. Is it sufficient to replace libpq.dll file 
?

Where to get its compiled version or how to compile it in Windows ?

[1]
libeay32.dll
libiconv.dll
libintl.dll
libpq.dll
msvcr100.dll
pg_dump.exe
ssleay32.dll
zlib1.dll 




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

Re: [GENERAL] How to fix lost synchronization with server

[Tom Lane]

"Andrus"  writes:
>> I looked back at the previous thread you mentioned (bug #7914) and was
>> reminded that we never did understand what was going on in that report.
>> I'm not sure if you are seeing the same thing though.  That user reported
>> that he was able to see pg_dump's memory consumption bloating well beyond
>> what it ought to be (I suppose he was watching the process in whatever
>> Windows' equivalent of ps or top is).  Do you see that?

> This occurs in customer computer which I din't observe.

> I added --inserts  parameter to pg_dump and ssl=false in postgresql.conf 
> file.

> After that backup works OK.

Now that we've identified the actual problem [1], I'm guessing the reason
why --inserts appears to make it go away is that then pg_dump uses a
SELECT which requires more server-side effort than COPY.  So that slows
down the server just a bit, and if the phase of the moon is favorable the
timing no longer results in this buffer-bloat behavior.  But I wouldn't
call that a reliable fix.  You'd be better off applying the patch.

regards, tom lane

[1] 
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=2f557167b19af79ffecb8faedf8b7bce4d48f3e1


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

Re: [GENERAL] How to fix lost synchronization with server

[Andrus]

I looked back at the previous thread you mentioned (bug #7914) and was
reminded that we never did understand what was going on in that report.
I'm not sure if you are seeing the same thing though.  That user reported
that he was able to see pg_dump's memory consumption bloating well beyond
what it ought to be (I suppose he was watching the process in whatever
Windows' equivalent of ps or top is).  Do you see that?


This occurs in customer computer which I din't observe.

I added --inserts  parameter to pg_dump and ssl=false in postgresql.conf 
file.


After that backup works OK.
Is it OK to use  --inserts parameter ?

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] Oracle to PostgreSQL replication

[tuanhoanganh]

You can test with SymmetricDS (www.*symmetricds*.org)


On Thu, May 8, 2014 at 10:35 PM, tuanhoanganh  wrote:

> You can test with SymmetricDS (www.*symmetricds*.org)
>
>
> On Thu, May 8, 2014 at 12:53 PM, Sameer Kumar wrote:
>
>>
>> Thanks alot everyone!
>>
>> I guess I will be exploring more on oracle foreign data wrapper.
>>
>> Has anyone tried using oracle_fdw with Oracle RAC? I am wondering how
>> would it handle failovers.
>>
>>
>

Re: [GENERAL] SSL Compression - doesn't work?

[Terence Ferraro]

You mentioned you are using the Windows version; unless something has
changed recently in their build process, the included openssl library is
not linked against zlib and therefore compression is not possible unless
you recompile the Windows version yourself.


*Terence J. Ferraro*

On Thu, May 8, 2014 at 9:36 AM, Adrian Klaver wrote:

> On 05/08/2014 01:22 AM, Krystian Bigaj wrote:
>
>> Hi,
>>
>> I'm wondering how, and if SSL compression works correctly.
>>
>> Here is how I tested it:
>> - PostgreSQL 9.3.4 x86 on Windows 7 x64
>> - .crt/.key files by openssl, and placed in database cluster folder
>> - postgres.exe ran with: --ssl="on" --ssl_cert_file="test.crt"
>> --ssl_key_file="test.key"
>> - connection made by pgadmin with SSL=prefer, SSL Compression=True
>> - when connected I see in properties: Encryptions=SSL encrypted, SSL
>> Compression=yes
>> - I've dumped TCP transfer and I can tell that data is encrypted
>>
>> Now when I run query like:
>> SELECT lpad('', 1024*1024, 'A')
>>
>> then I see that there is a TCP transfer of 1,01MB (so 1MB of string
>> data, and some pg header/data).
>>
>> If I turn off SSL Compression data transfer between postgres and pgadmin
>> is still 1,01MB (but in properties I see SSL Compression=no)
>>
>> It looks like SSL compression doesn't work, or am I missing something?
>>
>
> http://www.postgresql.org/docs/9.3/static/libpq-connect.html
>
> sslcompression
>
> If set to 1 (default), data sent over SSL connections will be
> compressed (this requires OpenSSL version 0.9.8 or later). If set to 0,
> compression will be disabled (this requires OpenSSL 1.0.0 or later). This
> parameter is ignored if a connection without SSL is made, or if the version
> of OpenSSL used does not support it.
>
> So what version of OpenSSL are you using?
>
>
>> Best regards,
>> Krystian Bigaj
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.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] Server process crash - Segmentation fault

[Adrian Klaver]

On 05/08/2014 07:43 AM, Tom Lane wrote:

Adrian Klaver  writes:

On 05/08/2014 07:19 AM, Tom Lane wrote:

9.3 patch is here:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=fc58c39d468587467c7c55b349c28167794eadaf



Alright, so I obviously linked to the wrong patch because I pointed at
HEAD and not REL9_3_STABLE.


I think those patches are the same, actually, but I was trying to be
careful.


What I am trying to figure out is what is
the distinction between commit and commitdiff?


The "commitdiff" link shows you the actual diffs in the patch, the other
one doesn't.


Got it, saves opening the diff for each file.



In practice, Leif's going to want to hit the "patch" link anyway to get a
clean downloadable patch; so likely what we should have pointed him at is
http://git.postgresql.org/gitweb/?p=postgresql.git;a=patch;h=fc58c39d468587467c7c55b349c28167794eadaf
I'm just in the habit of looking at the "commitdiff" versions of the web
pages as being the best readability/information tradeoff for casual
examination of a patch.


Thanks for the explanation.



regards, tom lane





--
Adrian Klaver
adrian.kla...@aklaver.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] Server process crash - Segmentation fault

[Tom Lane]

Adrian Klaver  writes:
> On 05/08/2014 07:19 AM, Tom Lane wrote:
>> 9.3 patch is here:
>> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=fc58c39d468587467c7c55b349c28167794eadaf

> Alright, so I obviously linked to the wrong patch because I pointed at 
> HEAD and not REL9_3_STABLE.

I think those patches are the same, actually, but I was trying to be
careful.

> What I am trying to figure out is what is 
> the distinction between commit and commitdiff?

The "commitdiff" link shows you the actual diffs in the patch, the other
one doesn't.

In practice, Leif's going to want to hit the "patch" link anyway to get a
clean downloadable patch; so likely what we should have pointed him at is
http://git.postgresql.org/gitweb/?p=postgresql.git;a=patch;h=fc58c39d468587467c7c55b349c28167794eadaf
I'm just in the habit of looking at the "commitdiff" versions of the web
pages as being the best readability/information tradeoff for casual
examination of a patch.

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] Server process crash - Segmentation fault

[Adrian Klaver]

On 05/08/2014 07:19 AM, Tom Lane wrote:

Leif Jensen  writes:

I already compiled postgreSQL myself and now using 9.3.4, so I would very 
much like a patch. Where can I find that ?


9.3 patch is here:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=fc58c39d468587467c7c55b349c28167794eadaf


Alright, so I obviously linked to the wrong patch because I pointed at 
HEAD and not REL9_3_STABLE. What I am trying to figure out is what is 
the distinction between commit and commitdiff?




regards, tom lane





--
Adrian Klaver
adrian.kla...@aklaver.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] Server process crash - Segmentation fault

[Adrian Klaver]

On 05/08/2014 06:59 AM, Leif Jensen wrote:

Hi Tom,

I already compiled postgreSQL myself and now using 9.3.4, so I would very 
much like a patch. Where can I find that ?


http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=04e5025be8bbe572e12b19c4ba9e2a8360b8ffe5



  Leif


--
Adrian Klaver
adrian.kla...@aklaver.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] Server process crash - Segmentation fault

[Tom Lane]

Leif Jensen  writes:
>I already compiled postgreSQL myself and now using 9.3.4, so I would very 
> much like a patch. Where can I find that ?

9.3 patch is here:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=fc58c39d468587467c7c55b349c28167794eadaf

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] Server process crash - Segmentation fault

[Leif Jensen]

   Hi Tom,

   I already compiled postgreSQL myself and now using 9.3.4, so I would very 
much like a patch. Where can I find that ?

 Leif


- Original Message -
> Leif Jensen  writes:
> >Could it be related to the OFFSET part of the statement ? I have
> >another query on the same table without OFFSET, which seems to
> >work fine.
> 
> Yeah, the specific code path here involves executing a stable (or
> possibly
> immutable) SQL function in a LIMIT or OFFSET clause. I was able to
> reproduce the crash like so:
> 
> create function foo(int) returns int as 'select $1 limit 1'
> language sql stable;
> 
> begin;
> 
> declare c cursor for select * from int8_tbl limit foo(3);
> 
> select * from c;
> 
> move backward all in c;
> 
> select * from c;
> 
> commit;
> 
> You might be able to dodge the problem if you can make the SQL
> function
> inline-able (the LIMIT 1 in my example is just to prevent that from
> happening). A less appealing alternative is to mark the function
> VOLATILE, which I think would also prevent this crash, but might have
> negative performance consequences.
> 
> If you don't mind building your own PG then you could grab the actual
> fix
> from our git repo; I should have something committed before long.
> 
> 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] SSL Compression - doesn't work?

[Adrian Klaver]

On 05/08/2014 01:22 AM, Krystian Bigaj wrote:

Hi,

I'm wondering how, and if SSL compression works correctly.

Here is how I tested it:
- PostgreSQL 9.3.4 x86 on Windows 7 x64
- .crt/.key files by openssl, and placed in database cluster folder
- postgres.exe ran with: --ssl="on" --ssl_cert_file="test.crt"
--ssl_key_file="test.key"
- connection made by pgadmin with SSL=prefer, SSL Compression=True
- when connected I see in properties: Encryptions=SSL encrypted, SSL
Compression=yes
- I've dumped TCP transfer and I can tell that data is encrypted

Now when I run query like:
SELECT lpad('', 1024*1024, 'A')

then I see that there is a TCP transfer of 1,01MB (so 1MB of string
data, and some pg header/data).

If I turn off SSL Compression data transfer between postgres and pgadmin
is still 1,01MB (but in properties I see SSL Compression=no)

It looks like SSL compression doesn't work, or am I missing something?


http://www.postgresql.org/docs/9.3/static/libpq-connect.html

sslcompression

If set to 1 (default), data sent over SSL connections will be 
compressed (this requires OpenSSL version 0.9.8 or later). If set to 0, 
compression will be disabled (this requires OpenSSL 1.0.0 or later). 
This parameter is ignored if a connection without SSL is made, or if the 
version of OpenSSL used does not support it.


So what version of OpenSSL are you using?



Best regards,
Krystian Bigaj



--
Adrian Klaver
adrian.kla...@aklaver.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] Receiving many more rows than expected

[Adrian Klaver]

On 05/08/2014 04:09 AM, Vincent de Phily wrote:

Hello,

I'm processing records in batches using PG 9.1.12, python 2.7, and psycopg
2.5.2 :


Comments in the code below:



def enqueue_loop(q):
 curs = DB_HANDLER.cursor()
 query = """UPDATE foo SET processing = 't' WHERE id IN
(SELECT id FROM foo WHERE processing = 'f' ORDER BY id ASC LIMIT
 5000 FOR UPDATE)  RETURNING *"""


   # Where is this query actually run?


 while DO_LOOP: #the whole program eventually stops if this is false


   # What cause DO_LOOP to go false?


 results = curs.fetchall()
 rlen = len(results)
 if rlen > 0:
 LOG.debug("Fetched %d rows", rlen)


   # What do you see in LOG for rlen values?


 if rlen == 0:
 # [...] wait for notification...
 continue
 # [...] Enqueue batch and let other threads process it.
 # [...] Those threads will eventually delete the processed rows from
 #   the foo table.


The problem is that sometimes (once every few days at about 2-300K queries per
day) I get many more rows than the max 5000 I asked for (I've seen up to 25k).
And I'm getting timeouts and other problems as a result.

The id column is your typical primary key integer with a unique index. I've
checked the problematic cases and there are no id gaps or duplicate rows.
There are multiple threads in the program, but only the main thread is running
enqueue_loop(). I'm not sure if this is a server or a driver issue.


Any idea ? Thanks.





--
Adrian Klaver
adrian.kla...@aklaver.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] Analyze against a table with geometry columns runs out of memory

[Roxanne Reid-Bennett]

On 5/8/2014 7:07 AM, Paul Ramsey wrote:
Roxanne, you seem to have isolated the problem to a particular 
geometry column, which speaks to this being a PostGIS problem.

ok - wrong list - sorry!
Since the analyze code was re-written in 2.1, and your issue is coming 
up in a 2.0>2.1 upgrade, that further points to the issue potentially 
being a PostGIS problem. Unless the same data works in a PgSQL 
9.2/PostGIS 2.1 combo, it seems clear that PgSQL 9.3 is not the 
culprit here. (Though I would love to be reassured that 9.2/2.1 combo 
also does not work, since that eliminates a bad interaction between 
9.3/2.1 as the issue.)
At some point in order to debug I’ll probably need a copy of the data, 
or access to a system that has the data and a dev environment. Please 
do file a ticket at http://trac.osgeo.org/postgis on this issue.


Thanks Paul,

I will see if we can trim the specific scenario down a little more for 
you and then file (data volume at least).


Roxanne

Re: [GENERAL] Receiving many more rows than expected

[Sim Zacks]

  
  
On 05/08/2014 02:09 PM, Vincent de
  Phily wrote:


  The problem is that sometimes (once every few days at about 2-300K queries per 
day) I get many more rows than the max 5000 I asked for (I've seen up to 25k). 
And I'm getting timeouts and other problems as a result.


I would bet you have another copy of the code running without the
limit. Maybe on a cron job.
To prove this, modify the log statement slightly and see if this
statement is really returning more then 5000 rows.
LOG.debug("1 - Fetched %d rows", rlen) or similar.



  

Re: [GENERAL] Trouble finding libpq on Centos 6.5

[Asif Naeem]

On Thu, May 8, 2014 at 5:02 PM, Aqz  wrote:

> I have devel package installed.
>
> Centos postgresql package adds file to /etc/ld.so.conf.d with path to
> pgsql libraries directory (/usr/pgsql-9.3/lib/) so I don't think that is
> the problem.
> As you can see in my first message ldconfig -p lists libpq library among
> others..
> Also :
> $ LD_LIBRARY_PATH="/usr/pgsql-9.3/lib" ld -lpq
>  ld: cannot find -lpq
>

I am not sure if Linux linker (ld) pick run time linker/loader paths by
default ( AFAIR Solaris compiler/linker can use library search path from
LD_LIBRARY_PATH etc ). I would suggest to use -L or add directory
/usr/pgsql-9.3/lib to linker (ld) default library search path. you can
check current search path via "ld --verbose | grep SEARCH_DIR".


> 2014-05-08 13:14 GMT+02:00 Asif Naeem :
>
>> Hi,
>>
>> Yes. It seems that /usr/pgsql-9.3/lib is not in linker default library
>> search path. You may either require to provide it explicitly via -L option
>> while linking or add /usr/pgsql-9.3/lib to the default library search path.
>>
>> As suggested by Alberto, for development work related devel package is
>> required.
>>
>> Regards,
>> Muhammad Asif Naeem
>>
>> On Thu, May 8, 2014 at 1:06 PM, Alberto Cabello Sánchez 
>>  wrote:
>>
>>> On Thu, 8 May 2014 08:54:44 +0200
>>> Aqz  wrote:
>>>
>>> > Hi.
>>> >
>>> > I'm not sure if I should write here, or on a Centos mailing list...
>>> >
>>> > I have fresh, vanilla Centos 6.5 with postgresql yum repository added.
>>> > I've successfully installed postgresql93 and postgresql93-libs
>>> packages,
>>> > but still :
>>> >
>>> > $ ld -lpq
>>> > ld: cannot find -lpq
>>> >
>>> > $ ldconfig -p
>>> > libpq.so.5 (libc6,x86-64) => /usr/pgsql-9.3/lib/libpq.so.5
>>> > libpq.so (libc6,x86-64) => /usr/pgsql-9.3/lib/libpq.so
>>> >
>>> >
>>> > What did I do wrong?
>>>
>>> Hi, Wojtek
>>>
>>> Maybe you need to install postgresql93-devel package?
>>>
>>>
>>> --
>>> Alberto Cabello Sánchez 
>>>
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>>
>>
>>
>
>
> --
> Wojtek Dziubiński
> www.aquaz.eu
>

Re: [GENERAL] Trouble finding libpq on Centos 6.5

[Alberto Cabello Sánchez]

On Thu, 8 May 2014 14:02:01 +0200
Aqz  wrote:

> I have devel package installed.
> 
> Centos postgresql package adds file to /etc/ld.so.conf.d with path to pgsql
> libraries directory (/usr/pgsql-9.3/lib/) so I don't think that is the
> problem.

So, it seems "ldconfig" searches in ld.so.conf.d paths, but "ld" doesn't...
which doesn't make sense to me.


-- 
Alberto Cabello Sánchez 


-- 
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] Trouble finding libpq on Centos 6.5

[Aqz]

I have devel package installed.

Centos postgresql package adds file to /etc/ld.so.conf.d with path to pgsql
libraries directory (/usr/pgsql-9.3/lib/) so I don't think that is the
problem.
As you can see in my first message ldconfig -p lists libpq library among
others..
Also :
$ LD_LIBRARY_PATH="/usr/pgsql-9.3/lib" ld -lpq
ld: cannot find -lpq


2014-05-08 13:14 GMT+02:00 Asif Naeem :

> Hi,
>
> Yes. It seems that /usr/pgsql-9.3/lib is not in linker default library
> search path. You may either require to provide it explicitly via -L option
> while linking or add /usr/pgsql-9.3/lib to the default library search path.
>
> As suggested by Alberto, for development work related devel package is
> required.
>
> Regards,
> Muhammad Asif Naeem
>
> On Thu, May 8, 2014 at 1:06 PM, Alberto Cabello Sánchez 
>  wrote:
>
>> On Thu, 8 May 2014 08:54:44 +0200
>> Aqz  wrote:
>>
>> > Hi.
>> >
>> > I'm not sure if I should write here, or on a Centos mailing list...
>> >
>> > I have fresh, vanilla Centos 6.5 with postgresql yum repository added.
>> > I've successfully installed postgresql93 and postgresql93-libs packages,
>> > but still :
>> >
>> > $ ld -lpq
>> > ld: cannot find -lpq
>> >
>> > $ ldconfig -p
>> > libpq.so.5 (libc6,x86-64) => /usr/pgsql-9.3/lib/libpq.so.5
>> > libpq.so (libc6,x86-64) => /usr/pgsql-9.3/lib/libpq.so
>> >
>> >
>> > What did I do wrong?
>>
>> Hi, Wojtek
>>
>> Maybe you need to install postgresql93-devel package?
>>
>>
>> --
>> Alberto Cabello Sánchez 
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>


-- 
Wojtek Dziubiński
www.aquaz.eu

Re: [GENERAL] Trouble finding libpq on Centos 6.5

[Steve Clark]

On 05/08/2014 02:54 AM, Aqz wrote:

Hi.

I'm not sure if I should write here, or on a Centos mailing list...

I have fresh, vanilla Centos 6.5 with postgresql yum repository added.
I've successfully installed postgresql93 and postgresql93-libs packages, but 
still :

$ ld -lpq
ld: cannot find -lpq

$ ldconfig -p
libpq.so.5 (libc6,x86-64) => /usr/pgsql-9.3/lib/libpq.so.5
libpq.so (libc6,x86-64) => /usr/pgsql-9.3/lib/libpq.so


What did I do wrong?

--
Wojtek


They put the postgres stuff in versioned directories.
there is a program you use to find out where stuff is.
do a man on pg_config

$ pg_config --bindir
/usr/pgsql-9.3/bin

$ pg_config

BINDIR = /usr/pgsql-9.3/bin
DOCDIR = /usr/share/doc/pgsql
HTMLDIR = /usr/share/doc/pgsql
INCLUDEDIR = /usr/pgsql-9.3/include
PKGINCLUDEDIR = /usr/pgsql-9.3/include
INCLUDEDIR-SERVER = /usr/pgsql-9.3/include/server
LIBDIR = /usr/pgsql-9.3/lib
PKGLIBDIR = /usr/pgsql-9.3/lib
LOCALEDIR = /usr/pgsql-9.3/share/locale
MANDIR = /usr/pgsql-9.3/share/man
SHAREDIR = /usr/pgsql-9.3/share
SYSCONFDIR = /etc/sysconfig/pgsql
PGXS = /usr/pgsql-9.3/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--disable-rpath' '--prefix=/usr/pgsql-9.3' 
'--includedir=/usr/pgsql-9.3/include' '--mandir=/usr/pgsql-9.3/share/man' 
'--datadir=/usr/pgsql-9.3/share' '--with-perl' '--with-python' '--with-tcl' 
'--with-tclconfig=/usr/lib64' '--with-openssl' '--with-pam' '--with-krb5' 
'--with-gssapi' '--with-includes=/usr/include' '--with-libraries=/usr/lib64' 
'--enable-nls' '--with-ossp-uuid' '--with-libxml' '--with-libxslt' 
'--with-ldap' '--with-system-tzdata=/usr/share/zoneinfo' 
'--sysconfdir=/etc/sysconfig/pgsql' '--docdir=/usr/share/doc' 'CFLAGS=-O2 -g 
-pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector 
--param=ssp-buffer-size=4 -m64 -mtune=generic -I/usr/include/et' 'CPPFLAGS= 
-I/usr/include/et'
CC = gcc
CPPFLAGS = -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include
CFLAGS = -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions 
-fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic 
-I/usr/include/et -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
-Wformat-security -fno-strict-aliasing -fwrapv
CFLAGS_SL = -fpic
LDFLAGS = -L../../../src/common -L/usr/lib64 -Wl,--as-needed
LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgport -lpgcommon -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz 
-lreadline -lcrypt -ldl -lm
VERSION = PostgreSQL 9.3.4



--
Stephen Clark
*NetWolves Managed Services, LLC.*
Director of Technology
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.cl...@netwolves.com
http://www.netwolves.com

Re: [GENERAL] Trouble finding libpq on Centos 6.5

[Asif Naeem]

Hi,

Yes. It seems that /usr/pgsql-9.3/lib is not in linker default library
search path. You may either require to provide it explicitly via -L option
while linking or add /usr/pgsql-9.3/lib to the default library search path.

As suggested by Alberto, for development work related devel package is
required.

Regards,
Muhammad Asif Naeem

On Thu, May 8, 2014 at 1:06 PM, Alberto Cabello Sánchez 
 wrote:

> On Thu, 8 May 2014 08:54:44 +0200
> Aqz  wrote:
>
> > Hi.
> >
> > I'm not sure if I should write here, or on a Centos mailing list...
> >
> > I have fresh, vanilla Centos 6.5 with postgresql yum repository added.
> > I've successfully installed postgresql93 and postgresql93-libs packages,
> > but still :
> >
> > $ ld -lpq
> > ld: cannot find -lpq
> >
> > $ ldconfig -p
> > libpq.so.5 (libc6,x86-64) => /usr/pgsql-9.3/lib/libpq.so.5
> > libpq.so (libc6,x86-64) => /usr/pgsql-9.3/lib/libpq.so
> >
> >
> > What did I do wrong?
>
> Hi, Wojtek
>
> Maybe you need to install postgresql93-devel package?
>
>
> --
> Alberto Cabello Sánchez 
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

[GENERAL] Receiving many more rows than expected

[Vincent de Phily]

Hello,

I'm processing records in batches using PG 9.1.12, python 2.7, and psycopg 
2.5.2 :

def enqueue_loop(q):
curs = DB_HANDLER.cursor()
query = """UPDATE foo SET processing = 't' WHERE id IN  


   (SELECT id FROM foo WHERE processing = 'f' ORDER BY id ASC LIMIT
5000 FOR UPDATE)  RETURNING *"""
while DO_LOOP: #the whole program eventually stops if this is false
results = curs.fetchall()
rlen = len(results)
if rlen > 0:
LOG.debug("Fetched %d rows", rlen)
if rlen == 0:
# [...] wait for notification...
continue
# [...] Enqueue batch and let other threads process it.
# [...] Those threads will eventually delete the processed rows from
#   the foo table.


The problem is that sometimes (once every few days at about 2-300K queries per 
day) I get many more rows than the max 5000 I asked for (I've seen up to 25k). 
And I'm getting timeouts and other problems as a result.

The id column is your typical primary key integer with a unique index. I've 
checked the problematic cases and there are no id gaps or duplicate rows. 
There are multiple threads in the program, but only the main thread is running 
enqueue_loop(). I'm not sure if this is a server or a driver issue.


Any idea ? Thanks.


-- 
Vincent de Phily



-- 
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] Analyze against a table with geometry columns runs out of memory

[Paul Ramsey]

Roxanne, you seem to have isolated the problem to a particular geometry column, 
which speaks to this being a PostGIS problem. Since the analyze code was 
re-written in 2.1, and your issue is coming up in a 2.0>2.1 upgrade, that 
further points to the issue potentially being a PostGIS problem. Unless the 
same data works in a PgSQL 9.2/PostGIS 2.1 combo, it seems clear that PgSQL 9.3 
is not the culprit here. (Though I would love to be reassured that 9.2/2.1 
combo also does not work, since that eliminates a bad interaction between 
9.3/2.1 as the issue.)

At some point in order to debug I’ll probably need a copy of the data, or 
access to a system that has the data and a dev environment. Please do file a 
ticket at http://trac.osgeo.org/postgis on this issue.

P

-- 
Paul Ramsey
http://cleverelephant.ca
http://postgis.net

On May 7, 2014 at 11:15:10 PM, Roxanne Reid-Bennett (r...@tara-lu.com) wrote:

Hello,  

We are working out the upgrade of our servers from Postgres 9.1 and  
Postgis 2.0 to Postgres 9.3 and Postgis 2.1  
After building the base stack, The System Admin restored the database  
from a backup. [I'll ask for more details if you need them]  

I have 3 tables with geometry columns in them that when they are  
autovacuumed, vacuumed, or analyzed run the system out of memory. I  
have isolated that the problem for one of the tables is related to a  
geometry column. I have tables in the system that are much larger on  
disk with geometry columns in them that vacuum analyze just fine, so it  
isn't just that they have geometry columns. Two of the tables are  
related to each other, the other is a load of Government supplied data  
and completely separate in detail and concept for data.  

Using the smallest table... we looked at maintenance_work_mem and tried  
several runs with varying values [16MB, 64MB, 256MB, and 500MB]. Larger  
maintenance_work_mem allows the process to run longer before it starts  
gobbling up swap, but the process still spends most of it's time in  
"uninterruptible sleep (usually IO)" state and just eats up the swap  
until all of the memory is gone.  

Smallest table definition, config and log file entries, etc follow  
below. If I have failed to provide necessary or desired information,  
just ask.  

We have noted that the memory management was changed going into 9.3 -  
but we haven't been able to find anything that would indicate any known  
issues ... This problem caused us to take a hard look at the stack  
again, and we will be building a new stack anyway because we need a  
newer GEOS - but we are seriously considering dropping Postgres back to  
9.2.  

I am out of ideas on what else to try after maintenance_work_mem ...  
Does anybody have any suggestions/questions/observations for me?  

Thank you.  

Roxanne  
--  

VirutualBox: 4.1.24 Intel Xeon 2.13 GHz (8) 48 Gb RAM  
Virtual Box instance: 64 Bit 4 Processors Base Memory: 12Gb  

running Ubuntu 12.04.1 LTS  
Linux 3.2.0-23-generic #36-Ubuntu SMP Tue Apr 10 20:39:51 UTC 2012  
x86_64 x86_64 x86_64 GNU/Linux  

Postgres: PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc  
(Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit  
PostGis: POSTGIS="2.1.2 r12389" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel.  
4.8.0, 6 March 2012" GDAL="GDAL 1.9.2, released 2012/10/08"  
LIBXML="2.7.8" LIBJSON="UNKNOWN" TOPOLOGY RASTER  

Postgres.conf entries (of probable interest - if I didn't list it, it's  
probably defaulted):  

max_connections = 100  
shared_buffers = 4089196kB  
work_mem = 128MB  
maintenance_work_mem = 64MB  
checkpoint_segments = 64  
checkpoint_timeout = 30min  
checkpoint_completion_target = 0.75  
effective_cache_size = 4089196kB  
default_statistics_target = 200  
autovacuum_max_workers = 1 [this is normally set to 3]  

Analyzing the original table "activity" failed. Using a copy of the  
original table with no indexes, no foreign keys, no constraints also  
failed. However, dropping one of the two geometry columns (region) out  
of the copy allowed it to succeed. Taking a copy of just "region" which  
contains (Multi)Polygons and the primary key via "CREATE TABLE ... as  
(Select...)", from the original table "activity" to create  
temp.region... analyze runs out of memory. The following were run  
against temp.region.  

smallest/shortest table definition from \d:  

Table "temp.region"  
Column | Type | Modifiers  
-+-+---  
activity_id | integer |  
region | geometry(Geometry,4326) |  

  
HQ4_Staging=# analyze verbose temp.region;  
INFO: 0: analyzing "temp.region"  
LOCATION: do_analyze_rel, analyze.c:335  
INFO: 0: "region": scanned 1022 of 1022 pages, containing 52990  
live rows and 0 dead rows; 52990 rows in sample, 52990 estimated total rows  
LOCATION: acquire_sample_rows, analyze.c:1299  
The connection to the server was lost. Attempting reset: Failed.  
  

Duration of the above was approximately 1.25 hrs.  

The Log files 

Re: [GENERAL] Ubuntu Packages / Config Files

[Stuart Bishop]

On 2 May 2014 01:40, Stephan Fabel  wrote:
> All,
>
> apologies if this has been addressed somewhere already. I don't have a
> lot of experience in PostgreSQL; this is my first setup where I'm trying
> to scale and provide some of the more advanced features (like WAL
> shipping, master-slave sync, integrating pgbouncer, etc.), and I'm
> looking for help regarding the configuration files.
>
> I'm using Ubuntu 12.04 for these deployments at the moment. The Ubuntu
> packages don't put the configuration files with the cluster data (by
> default under /var/lib/postgresql/9.1/main under 12.04), but in
> /etc/postgresql/9.1/main) and they start postgres with the -c option
> pointing there.
>
> Whenever I try to add a slave, first I stop the postgresql service, move
> the above data directory to something like
> /var/lib/postgresql/9.1/main.orig, create a new 'main' directory with
> identical permissions/ownerships, and start pg_basebackup pointing
> there. It will not copy the server.crt and server.key symlinks (by
> default pointing to the "snakeoil" cert/key) so I re-create those. I
> then put the appropriate recovery.conf into /etc/postgresql/9.1/main,
> given that that's the configuration directory where everything is. I set
> "wal_level = hot_standby" and "hot_standby = on" in postgresql.conf.
> After  I then start the postgresql service again.

recovery.conf goes into $DATADIR, which is
/var/lib/postgresql/9.1/main in your case. I rationalize this by
considering it database state, rather than configuration, since
commands like 'pg_ctl promote' or using a trigger file will mess with
it.


> The problem is that recovery.conf gets ignored in this case. I can add
> another symlink pointing to it into the data directory, for example, or
> copy the file there, then it works, but honestly this has cost me a LOT
> of time figuring out.

Having the two, with the master in /etc, will confuse you. And quite
possibly PostgreSQL if it needs to promote the database.

> So, a couple of questions:
>
> 1) am I even going about this the right way under an Ubuntu system?

Yes.

> 2) do the packages available at apt.postgresql.org behave differently?

They are more up to date. I think the manual symlink of the SSL files
in $DATADIR is no longer needed, instead pulling them from /etc.

> 3) do later versions of postgresql behave differently?

8.4->9.3 are all pretty much the same.

> Eventually, I'd like to use configuration management tools like puppet
> to deploy something like that, but I suppose that's a topic for another day.

Yeah... integration with configuration management is going to be
interesting when PG allows you to modify config from the SQL command
line...

-- 
Stuart Bishop 
http://www.stuartbishop.net/


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

[GENERAL] SSL Compression - doesn't work?

[Krystian Bigaj]

Hi,

I'm wondering how, and if SSL compression works correctly.

Here is how I tested it:
- PostgreSQL 9.3.4 x86 on Windows 7 x64
- .crt/.key files by openssl, and placed in database cluster folder
- postgres.exe ran with: --ssl="on" --ssl_cert_file="test.crt"
--ssl_key_file="test.key"
- connection made by pgadmin with SSL=prefer, SSL Compression=True
- when connected I see in properties: Encryptions=SSL encrypted, SSL
Compression=yes
- I've dumped TCP transfer and I can tell that data is encrypted

Now when I run query like:
SELECT lpad('', 1024*1024, 'A')

then I see that there is a TCP transfer of 1,01MB (so 1MB of string data,
and some pg header/data).

If I turn off SSL Compression data transfer between postgres and pgadmin is
still 1,01MB (but in properties I see SSL Compression=no)

It looks like SSL compression doesn't work, or am I missing something?

Best regards,
Krystian Bigaj

Re: [GENERAL] Trouble finding libpq on Centos 6.5

[Alberto Cabello Sánchez]

On Thu, 8 May 2014 08:54:44 +0200
Aqz  wrote:

> Hi.
> 
> I'm not sure if I should write here, or on a Centos mailing list...
> 
> I have fresh, vanilla Centos 6.5 with postgresql yum repository added.
> I've successfully installed postgresql93 and postgresql93-libs packages,
> but still :
> 
> $ ld -lpq
> ld: cannot find -lpq
> 
> $ ldconfig -p
> libpq.so.5 (libc6,x86-64) => /usr/pgsql-9.3/lib/libpq.so.5
> libpq.so (libc6,x86-64) => /usr/pgsql-9.3/lib/libpq.so
> 
> 
> What did I do wrong?

Hi, Wojtek

Maybe you need to install postgresql93-devel package?


-- 
Alberto Cabello Sánchez 


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