[GENERAL] How to implement the skip errors for copy from ?

[xbzhang]

I want to?implement the skip errors for copy from,lik as :create table A (c int 
primary key);copy A from stdin;112\.
copy will failed:ERROR:  duplicate key violates primary key constraint "CC_PKEY"

CONTEXT:  COPY CC, line 2: "1"
I want skip the error, and continue to copy the reset of tuple. The resultwill 
be that there are two rows in table A: 1 and 2.
how to?implement that ?




张晓博?? 研发二部
北京人大金仓信息技术股份有限公司
地址：北京市海淀区上地西路八号院上地科技大厦4号楼501
邮编：100085
电话：(010) 5885 1118 - 8450
手机：15311394463
邮箱：xbzh...@kingbase.com.cn

Re: Fwd: [GENERAL] libpq: indefinite block on poll during network problems

[Dmitry Samonenko]

Guys, first of all: thank you for you help and cooperation. I have received
several mails suggesting tweaks for tcp_keepalive and usage of postgresql
server functions/features (cancel, statement timeout), but as I said - it
won't help.

I have reproduced the problem scenario. Logs are attached. I walk you
through.

== Setup ==
Client and server applications are placed on separate hosts. Client =
192.168.15.4, Server = 192.168.15.7. Both are in local net. Both are
synchronized using 3rd party NTP server. Lets look in strace_export.txt -
top 8 lines = socket setup. Keepalive option is set. Client's OS keepalive
parameters:

[root@krr2srv1wsn1 dtp_generator]# sysctl -a | grep keepalive
net.ipv4.tcp_keepalive_intvl = 5
net.ipv4.tcp_keepalive_probes = 3
net.ipv4.tcp_keepalive_time = 10

This means that after 10 seconds of idle connection first TCP Keep-Alive
probe is sent. If 3 probes with 5 second interval fail - connection should
be considered dead.

Server configuration is in postgresql.conf.

== Part 1. TCP Keep Alive ==
At 11:25:35.847138 connection to the server is made and the first query is
sent. Got response fast at 11:25:35.858582. No other queries were made for
the next minute to catch keep alive packets. Wireshark 1.8.2 marks 13 - 36
frames as Keep-Alive, so we can see that it's configured right and
definitely works.

== Part 2. The Problem ==
At 11:26:40.933017 queries generation is started on client side. Client is
configured to perform 1 request per second. After some arbitrary time next
command is executed on server node:
[root@cluster1]# date && iptables -A OUTPUT -p tcp --sport 5432 -j DROP &&
iptables -A INPUT -p tcp --dport 5432 -j DROP

11:26:47 is outputed to console. As you can see in client trace file, this
time corresponds to frame 55 - the last query is made. strace shows send &&
poll syscalls. And... that's it. The client got blocked on poll.

== Part 3. The aftermath ==
The Client was blocked ~2 minutes. I killed application with SIGTERM, which
you can see in strace. At the time application was still waiting on libpq's
poll. The Pcap file show no trace of keep-alive packets after server was
isolated with iptable's rules. As I said earlier: TCP Keep-Alive is done on
idle connection only. When TCP retransmission kicks-in - TCP Keep-Alive is
not performed.


Let me repeat myself again: the problem is NOT with the server. The problem
is with libpq's PGgetResult which ultimately leads to very optimistic poll
routine.

Thank you.

With regards, Dmitry Samonenko.
11:25:35.843597 socket(PF_INET, SOCK_STREAM, IPPROTO_IP) = 12
11:25:35.843629 setsockopt(12, SOL_SOCKET, SO_KEEPALIVE, [1], 4) = 0
11:25:35.843656 setsockopt(12, SOL_TCP, TCP_NODELAY, [1], 4) = 0
11:25:35.843691 fcntl64(12, F_SETFL, O_RDONLY|O_NONBLOCK) = 0
11:25:35.843713 connect(12, {sa_family=AF_INET, sin_port=htons(5432), 
sin_addr=inet_addr("192.168.15.7")}, 16) = -1 EINPROGRESS (Operation now in 
progress)
11:25:35.843901 poll([{fd=12, events=POLLOUT|POLLERR, revents=POLLOUT}], 1, 
2000) = 1
11:25:35.846760 getsockopt(12, SOL_SOCKET, SO_ERROR, [0], [4]) = 0
11:25:35.846821 getsockname(12, {sa_family=AF_INET, sin_port=htons(50796), 
sin_addr=inet_addr("192.168.15.4")}, [16]) = 0
11:25:35.847138 send(12, "\0\0\0\10\4\322\26/", 8, 0) = 8
11:25:35.847285 poll([{fd=12, events=POLLIN|POLLERR, revents=POLLIN}], 1, 2000) 
= 1
11:25:35.848669 recv(12, "N", 1, 0) = 1
11:25:35.848720 poll([{fd=12, events=POLLOUT|POLLERR, revents=POLLOUT}], 1, 
2000) = 1
11:25:35.848799 send(12, "\0\0\0$\0\3\0\0user\0teligent\0database\0mnp\0\0", 
36, 0) = 36
11:25:35.848982 poll([{fd=12, events=POLLIN|POLLERR, revents=POLLIN}], 1, 2000) 
= 1
11:25:35.851547 recv(12, 
"R\0\0\0\10\0\0\0\0S\0\0\0\26application_name\0\0S\0\0\0\31client_encoding\0UTF8\0S\0\0\0\27DateStyle\0ISO,
 
MDY\0S\0\0\0\31integer_datetimes\0on\0S\0\0\0\33IntervalStyle\0postgres\0S\0\0\0\24is_superuser\0on\0S\0\0\0\31server_encoding\0UTF8\0S\0\0\0\31server_version\0009.2.4\0S\0\0\0#session_authorization\0teligent\0S\0\0\0#standard_conforming_strings\0on\0S\0\0\0\22TimeZone\0W-SU\0K\0\0\0\f\0\0w\30i\222\303\265Z\0\0\0\5I",
 16384, 0) = 319
11:25:35.851989 send(12, "Q\0\0\0;select route, edr_tag from 
p90_get_route(\'9282487100\')\0", 60, 0) = 60
11:25:35.852145 poll([{fd=12, events=POLLIN|POLLERR, revents=POLLIN}], 1, -1) = 
1
11:25:35.858582 recv(12, 
"T\0\0\0008\0\2route\0\0\0\0\0\0\0\0\0\4\23\377\377\377\377\377\377\0\0edr_tag\0\0\0\0\0\0\0\0\0\4\23\377\377\377\377\377\377\0\0D\0\0\0\27\0\2\0\0\0\5D2502\0\0\0\4P-N0C\0\0\0\rSELECT
 1\0Z\0\0\0\5I", 16384, 0) = 101
11:26:40.933017 send(12, "Q\0\0\0;select route, edr_tag from 
p90_get_route(\'9282487100\')\0", 60, 0) = 60
11:26:40.934888 poll([{fd=12, events=POLLIN|POLLERR, revents=POLLIN}], 1, -1) = 
1
11:26:40.936551 recv(12, 
"T\0\0\0008\0\2route\0\0\0\0\0\0\0\0\0\4\23\377\377\377\377\377\377\0\0edr_tag\0\0\0\0\0\0\0\0\0\4\23\377\377\377\377\377\377\0\0D\0\0\0\27\0\2\0\0\0\5D2502\0\0\0\4P-N0C\0\0\0\rSELECT
 1\0Z\0\0\0\5I", 16384, 0

Re: Fwd: [GENERAL] libpq: indefinite block on poll during network problems

[Martijn van Oosterhout]

On Thu, May 29, 2014 at 12:27:50PM +0400, Dmitry Samonenko wrote:
> Guys, first of all: thank you for you help and cooperation. I have received
> several mails suggesting tweaks for tcp_keepalive and usage of postgresql
> server functions/features (cancel, statement timeout), but as I said - it
> won't help.
> 
> I have reproduced the problem scenario. Logs are attached. I walk you
> through.
> 
> == Setup ==
> Client and server applications are placed on separate hosts. Client =
> 192.168.15.4, Server = 192.168.15.7. Both are in local net. Both are
> synchronized using 3rd party NTP server. Lets look in strace_export.txt -
> top 8 lines = socket setup. Keepalive option is set. Client's OS keepalive
> parameters:
> 
> [root@krr2srv1wsn1 dtp_generator]# sysctl -a | grep keepalive
> net.ipv4.tcp_keepalive_intvl = 5
> net.ipv4.tcp_keepalive_probes = 3
> net.ipv4.tcp_keepalive_time = 10
> 
> This means that after 10 seconds of idle connection first TCP Keep-Alive
> probe is sent. If 3 probes with 5 second interval fail - connection should
> be considered dead.

Something very important to note: those settings do nothing unless the
SO_KEEPALIVE option is turned on for the socket.  AFAICT libpq does not
enable this option, hence they (probably) have no effect.

(Discovered after finding processes staying alive for several months
because the firewall had lost it's state table at some point).

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
   -- Arthur Schopenhauer


signature.asc
Description: Digital signature

Re: Fwd: [GENERAL] libpq: indefinite block on poll during network problems

[Dmitry Samonenko]

Please, look inside attached strace_export.txt. Second line.

With regard, Dmitry Samonenko.

Re: Fwd: [GENERAL] libpq: indefinite block on poll during network problems

[Tom Lane]

Martijn van Oosterhout  writes:
> Something very important to note: those settings do nothing unless the
> SO_KEEPALIVE option is turned on for the socket.  AFAICT libpq does not
> enable this option, hence they (probably) have no effect.

AFAICS, it does so by default since 9.0.

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 implement the skip errors for copy from ?

[Vik Fearing]

On 05/29/2014 09:25 AM, xbzhang wrote:
> I want to implement the skip errors for copy from,lik as :
> create table A (c int primary key);
> copy A from stdin;
> 1
> 1
> 2
> \.
>
> copy will failed:
> ERROR: duplicate key violates primary key constraint "CC_PKEY"
> CONTEXT: COPY CC, line 2: "1"
>
> I want skip the error, and continue to copy the reset of tuple. The result
> will be that there are two rows in table A: 1 and 2.
>
> how to implement that ?

Have a look at pgloader. http://pgloader.tapoueh.org/

-- 
Vik

Re: [GENERAL] How to implement the skip errors for copy from ?

[Adrian Klaver]

On 05/29/2014 12:25 AM, xbzhang wrote:
> I want to implement the skip errors for copy from,lik as :
> create table A (c int primary key);
> copy A from stdin;
> 1
> 1
> 2
> \.
> 
> copy will failed:
> ERROR: duplicate key violates primary key constraint "CC_PKEY"
> CONTEXT: COPY CC, line 2: "1"
> 
> I want skip the error, and continue to copy the reset of tuple. The result
> will be that there are two rows in table A: 1 and 2.
> 
> how to implement that ?

Use an external program like:

pgloader

http://pgloader.tapoueh.org/

or

pg_bulkload

http://pgbulkload.projects.pgfoundry.org/pg_bulkload.html

> 邮箱：xbzh...@kingbase.com.cn 
> 


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

[GENERAL] relocation R_X86_64_32 against `.rodata' can not be used when making a shared object

[Mimiko]

Hello.

I want to compile postgresql with this:

./configure --prefix=/opt/postgresql --enable-nls --disable-rpath 
--enable-profiling --enable-depend --with-tcl --with-segsize=4 
--with-perl --with-python --with-gssapi --with-krb5 --with-pam 
--with-ldap --with-ossp-uuid --with-libxml --with-libxslt --with-openssl 
--with-zlib --with-gnu-ld 
--with-includes="/opt/openssl/include:/opt/libxml2/include:/opt/libxslt/include:/opt/zlib/include:/opt/xz/include" 
LDFLAGS="-Wl,-R,ORIGIN/../lib/../lib/../lib../lib:ORIGIN/../lib/x86_64-linux-gnu 
-L/opt/openssl/lib -L/opt/libxml2/lib -L/opt/libxslt/lib -L/opt/zlib/lib 
-L/opt/xz/lib" LIBS="-llzma -lrt" CPPFLAGS="-I/opt/libxml2/include"


But `make` fails with this error:
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
-Wformat-security -fno-strict-aliasing -fwrapv 
-fexcess-precision=standard -pg -DLINUX_PROFILE -pthread  -D_REENTRANT 
-D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -fpic -shared 
-Wl,-soname,libpq.so.5 -Wl,--version-script=exports.list -o libpq.so.5.6 
fe-auth.o fe-connect.o fe-exec.o fe-misc.o fe-print.o fe-lobj.o 
fe-protocol2.o fe-protocol3.o pqexpbuffer.o fe-secure.o libpq-events.o 
chklocale.o inet_net_ntop.o noblock.o pgstrcasecmp.o pqsignal.o thread.o 
strlcpy.o getpeereid.o ip.o md5.o encnames.o wchar.o -L../../../src/port 
-L../../../src/common 
-Wl,-R,ORIGIN/../lib/../lib/../lib../lib:ORIGIN/../lib/x86_64-linux-gnu 
-L/opt/openssl/lib -L/opt/libxml2/lib -L/opt/libxslt/lib -L/opt/zlib/lib 
-L/opt/xz/lib -L/usr/lib  -Wl,--as-needed  -lssl -lcrypto -lgssapi_krb5 
-lcrypt -lldap_r  -lpthread
/usr/bin/ld: /opt/openssl/lib/libssl.a(t1_meth.o): relocation 
R_X86_64_32 against `.rodata' can not be used when making a shared 
object; recompile with -fPIC

/opt/openssl/lib/libssl.a: could not read symbols: Bad value
collect2: error: ld returned 1 exit status
make[3]: *** [libpq.so.5.6] Error 1
make[3]: Leaving directory 
`/home/mimiko/src/postgresql/src/interfaces/libpq'

make[2]: *** [all-libpq-recurse] Error 2
make[2]: Leaving directory `/home/mimiko/src/postgresql/src/interfaces'
make[1]: *** [all-interfaces-recurse] Error 2
make[1]: Leaving directory `/home/mimiko/src/postgresql/src'
make: *** [world-src-recurse] Error 2

Want to mention that openssl, zlib, libxml2, libxslt and xz are compiled 
in /opt as static only.


How can I resolve this error, or this is a wrong way?

--
Mimiko desu.


--
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] relocation R_X86_64_32 against `.rodata' can not be used when making a shared object

[Tom Lane]

Mimiko  writes:
> But `make` fails with this error:
> /usr/bin/ld: /opt/openssl/lib/libssl.a(t1_meth.o): relocation 
> R_X86_64_32 against `.rodata' can not be used when making a shared 
> object; recompile with -fPIC

> Want to mention that openssl, zlib, libxml2, libxslt and xz are compiled 
> in /opt as static only.

You cannot use such libraries with Postgres.  They need to be linked with
dynamic shared objects, which means they either need to be DSOs themselves
or at least compiled in a way that would allow them to become part of a
DSO.  As the message says, that means you need to have used -fPIC when
building them (though -fpic might work as well depending on platform).

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

[GENERAL] Postgresql service stopped automatically after restart

[Kalai R]

Hello,

I am developing application using postgresql 9.3 and VS2005 as FrontEnd.
I am using silent installation method to install postgresql from .Net
application.
Postgresql installed and connected with application is fine

*The problem is* , when we on the system after shut down, postgresql
service doesn't start. Can't start manually also. At this situation,

-> sometime files are deleted in bin folder, if I paste the deleted files
in that folder, service start

->sometime files are not deleted,could not start the service

->some time if we change the postgresql service to local system account,
service start

->some time if we change the postgresql service to local system account,
could not start the service


Mostly this problem occurs in the System that have Windows XP or if any *Virus
Scanner installed*

How to solve this Problem?

Thanks & Regards
kalai

Re: [GENERAL] Postgresql service stopped automatically after restart

[Adrian Klaver]

On 05/29/2014 07:34 AM, Kalai R wrote:

Hello,

I am developing application using postgresql 9.3 and VS2005 as FrontEnd.
I am using silent installation method to install postgresql from .Net
application.
Postgresql installed and connected with application is fine

*The problem is* , when we on the system after shut down, postgresql
service doesn't start. Can't start manually also. At this situation,

-> sometime files are deleted in bin folder, if I paste the deleted
files in that folder, service start

->sometime files are not deleted,could not start the service

->some time if we change the postgresql service to local system account,
service start

->some time if we change the postgresql service to local system account,
could not start the service


Mostly this problem occurs in the System that have Windows XP or if any
*Virus Scanner installed*

How to solve this Problem?


Start by looking in the Postgres logs. The last time you had this issue 
it was a mis-configured pg_hba.conf file:


http://www.postgresql.org/message-id/aanlktikkmzzxcfirfmhexu-abe+ohquwh2tcdqjzi...@mail.gmail.com

As to deleted files, I would start by disabling the AV software checks 
for the Postgres directories.




Thanks & Regards
kalai



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

[GENERAL] Postgresql 9.2.4 - timezone error

[Bhushan Pathak]

Hello,

I have postgresql 9.2.4 running on a CentOS 5 server. A JBOSS AS instance
also runs on the same server, which connects to postgresql server [using
defined connection pools].

The issue I'm facing is that the JBOSS server is throwing out the error -
org.postgresql.util.PSQLException: FATAL: invalid value for parameter
"TimeZone": "America/New_York"

When I looked up on the internet, most common answer was to execute the
following query from CLI which would expose the real problem -

myDB=# SELECT * FROM pg_timezone_names WHERE name = 'America/New_York';
ERROR:  could not stat "/usr/share/zoneinfo/America/New_York": Too many
levels of symbolic links

What does this really mean? Why is postgresql complaining for symbolic
links? Any ideas what has gone wrong? Any thing that needs checking?

Thanks

Bhushan Pathak

Re: [GENERAL] Postgresql service stopped automatically after restart

[Kalai R]

I have checked no problem in pg_hba.conf file.

Also I have disabled AV software checks for the Postgres Directories.

But Problem remains


On Thu, May 29, 2014 at 8:15 PM, Adrian Klaver 
wrote:

> On 05/29/2014 07:34 AM, Kalai R wrote:
>
>> Hello,
>>
>> I am developing application using postgresql 9.3 and VS2005 as FrontEnd.
>> I am using silent installation method to install postgresql from .Net
>> application.
>> Postgresql installed and connected with application is fine
>>
>> *The problem is* , when we on the system after shut down, postgresql
>>
>> service doesn't start. Can't start manually also. At this situation,
>>
>> -> sometime files are deleted in bin folder, if I paste the deleted
>> files in that folder, service start
>>
>> ->sometime files are not deleted,could not start the service
>>
>> ->some time if we change the postgresql service to local system account,
>> service start
>>
>> ->some time if we change the postgresql service to local system account,
>> could not start the service
>>
>>
>> Mostly this problem occurs in the System that have Windows XP or if any
>> *Virus Scanner installed*
>>
>>
>> How to solve this Problem?
>>
>
> Start by looking in the Postgres logs. The last time you had this issue it
> was a mis-configured pg_hba.conf file:
>
> http://www.postgresql.org/message-id/AANLkTikKMzZxcfirFMHEXu-ABE+
> ohquwh2tcdqjzi...@mail.gmail.com
>
> As to deleted files, I would start by disabling the AV software checks for
> the Postgres directories.
>
>
>> Thanks & Regards
>> kalai
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Code for user-defined type

[Paul Jones]

On Wed, May 28, 2014 at 10:51:43AM +0200, Pavel Stehule wrote:
> 
> Hello
> 
> 
> 2014-05-27 20:30 GMT+02:00 Paul Jones :
> 
> > I have written a user-defined type that allows direct import and printing
> > of
> > DB2 timestamps.It does correctly import and export DB2 timestamps,
> > butI'm wondering ifsomeone could tell me if I made anymistakes in
> > the C code, particularly w.r.t. memory leaks or non-portableconstructs.
> >
> >
> > I'm doing this on 9.3.4.
> >
> > Thanks,
> 
> There is one issue DirectFunctionCall takes a parameters converted to Datum
> and returns Datum
> 
> You should to use a macros XGetDatum and DatumGetX
> 
> In this case
> 
>         newDate = DatumGetTimestamp(DirectFunctionCall2(to_timestamp,
>  CStringGetDatum(date_txt),
> CStringGetDatum(cstring_to_text(nls_date_format;
> 
> PG_RETURN_TIMESTAMP(newDate);
> 
> 
> 
> There is inconsistency in types - Timestamp and Timestamptz -

Thanks, Pavel!

I used the proper XGetDatum and DatumGetX and was able to get it to work
properly.  However, I since discovered that I probably should not use
"cstring_to_text" because of the palloc's it does.  The problem comes
when doing "\copy table from file".  After about 1000 rows, the backend
dies with SEGV, I think because of too many pallocs being created in
the copy transaction.

I rewrote it so that the format string is turned into a text at .so load time,
and then converted the input string into a local text.

PJ


-- 
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] Code for user-defined type

[Pavel Stehule]

2014-05-29 18:04 GMT+02:00 Paul Jones :

> On Wed, May 28, 2014 at 10:51:43AM +0200, Pavel Stehule wrote:
> >
> > Hello
> >
> >
> > 2014-05-27 20:30 GMT+02:00 Paul Jones :
> >
> > > I have written a user-defined type that allows direct import and
> printing
> > > of
> > > DB2 timestamps.It does correctly import and export DB2 timestamps,
> > > butI'm wondering ifsomeone could tell me if I made anymistakes in
> > > the C code, particularly w.r.t. memory leaks or non-portableconstructs.
> > >
> > >
> > > I'm doing this on 9.3.4.
> > >
> > > Thanks,
> >
> > There is one issue DirectFunctionCall takes a parameters converted to
> Datum
> > and returns Datum
> >
> > You should to use a macros XGetDatum and DatumGetX
> >
> > In this case
> >
> > newDate = DatumGetTimestamp(DirectFunctionCall2(to_timestamp,
> >  CStringGetDatum(date_txt),
> > CStringGetDatum(cstring_to_text(nls_date_format;
> >
> > PG_RETURN_TIMESTAMP(newDate);
> >
> >
> >
> > There is inconsistency in types - Timestamp and Timestamptz -
>
> Thanks, Pavel!
>
> I used the proper XGetDatum and DatumGetX and was able to get it to work
> properly.  However, I since discovered that I probably should not use
> "cstring_to_text" because of the palloc's it does.  The problem comes
> when doing "\copy table from file".  After about 1000 rows, the backend
> dies with SEGV, I think because of too many pallocs being created in
> the copy transaction.
>
> I rewrote it so that the format string is turned into a text at .so load
> time,
> and then converted the input string into a local text.
>

too many pallocs should not fail on SEGV (I am thinking, but can be
fallible).

For extension development is good idea use postgres backend compiled with
--enable-cassert option.

It can do a extra tests of memery usage, and can show some other information

Regards

Pavel


>
> PJ
>

Re: [GENERAL] relocation R_X86_64_32 against `.rodata' can not be used when making a shared object

[Mimiko]

On 29.05.2014 17:10, Tom Lane wrote:

As the message says, that means you need to have used -fPIC when
building them (though -fpic might work as well depending on platform).


Thank you for answering.

Searching on net I couldn't understand what to compile with -fPIC. So
those libraries must be compiled with -fPIC. For libxml2 and libxslt I
use --with-pic. Is this sufficient? zlib and openssl does not have such
option. Then which of CFLAGS or CPPFLAGS should I set with -fPIC for them?

You are saying that postgresql will always generate shared libraries? If
I use -fPIC to compile other libraries, will postresql's compile will
include those libraries in its lib directory? ie, making postgresql
self-contained with this libraries?

--
Mimiko desu.




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

Re: [GENERAL] Postgresql 9.2.4 - timezone error

[Tom Lane]

Bhushan Pathak  writes:
> myDB=# SELECT * FROM pg_timezone_names WHERE name = 'America/New_York';
> ERROR:  could not stat "/usr/share/zoneinfo/America/New_York": Too many
> levels of symbolic links

Oh?  There's something really broken about your tzdata installation,
then.  On any Red Hat-derived system, /usr/share/zoneinfo/America/New_York
ought to be a plain file and none of the directories above it should
be symlinks either.

> What does this really mean?

It probably means there's a circular loop of symlinks at one of the steps
along that file path, for instance /usr/share/zoneinfo might be a symlink
pointing to itself.  This would break many things not only Postgres.

You need to get rid of whatever's nonstandard there and reinstall the
regular tzdata package from Red Hat/CentOS.

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] Code for user-defined type

[Tom Lane]

Paul Jones  writes:
> I used the proper XGetDatum and DatumGetX and was able to get it to work
> properly.  However, I since discovered that I probably should not use
> "cstring_to_text" because of the palloc's it does.  The problem comes
> when doing "\copy table from file".  After about 1000 rows, the backend
> dies with SEGV, I think because of too many pallocs being created in
> the copy transaction.

That probably means you're stomping on memory that doesn't belong to you.

pallocs per se should not be a problem for COPY --- it does a context
reset per row.  And even if it didn't, you'd not likely be running out
of memory after a mere thousand rows.  However, a buffer-overrun type
of coding error would be probabilistic as to when it became obvious
via a core dump; some of the time you'd be stomping on memory that
was unused anyway.

regards, tom lane


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

Re: [GENERAL] Postgresql 9.2.4 - timezone error

[Jerry Sievers]

Bhushan Pathak  writes:

> Hello,
>
> I have postgresql 9.2.4 running on a CentOS 5 server. A JBOSS AS instance 
> also runs on the same server, which connects to postgresql server [using 
> defined connection
> pools].
>
> The issue I'm facing is that the JBOSS server is throwing out the error -
> org.postgresql.util.PSQLException: FATAL: invalid value for parameter 
> "TimeZone": "America/New_York"
>
> When I looked up on the internet, most common answer was to execute the 
> following query from CLI which would expose the real problem -
>
> myDB=# SELECT * FROM pg_timezone_names WHERE name = 'America/New_York';
> ERROR:  could not stat "/usr/share/zoneinfo/America/New_York": Too many 
> levels of symbolic links
>
> What does this really mean? Why is postgresql complaining for symbolic links? 
> Any ideas what has gone wrong? Any thing that needs checking?

Something is fishy in your filesystem such as a link loop...  see this
trivial example;

sj$ pwd
/tmp
sj$ ln -s foo bar
sj$ ln -s bar foo
sj$ cat foo
cat: foo: Too many levels of symbolic links
sj$ ls -l foo bar
lrwxrwxrwx 1 yomama yomama 3 May 29 11:38 bar -> foo
lrwxrwxrwx 1 yomama yomama 3 May 29 11:38 foo -> bar

>
> Thanks
>
> Bhushan Pathak
>

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


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

[GENERAL] Merge a sharded master into a single read-only slave

[Sébastien Lorion]

I have a master database sharded by user_id, with globally unique IDs for
everything, except shared configuration data stored in global tables
(resources strings, system parameters, etc).

What would be the best (ie both fast and reliable, simple to maintain as a
bonus) to merge all shards into a single read-only slave that will then be
replicated and used for read queries ? I took a look at Londiste and
repmgr, and can see some ways to accomplish that, but would appreciate the
advice of people here.

Thank you,

Sébastien

Re: [GENERAL] Backups over slave instead master?

[Andres Freund]

Hi,

On 2014-05-16 12:49:25 +0530, chiru r wrote:
> Yes, It is possible to execute backups on the slave server instead of
> master.
> 
> Below are the steps we run for one of our past customer every day to
> refresh his Dev/test environments using slave backups.
> 
> *On Slave:*
> 
> 1. Pause the replication
> 
> postgres=# select pg_xlog_replay_pause();
> 
>  pg_xlog_replay_pause
> 
> --
> 
> (1 row)
> 
> 2. Make sure wheather Replication paused or not.
> 
> postgres =# select pg_is_xlog_replay_paused();
> 
>  pg_is_xlog_replay_paused
> 
> --
> 
> * t*
> 
> (1 row)
> 
> 3. Copy the data directory using any one rsync,tar,scp or cp..etc
> 
> 4. Resume the replication to continue the replication process.
> 
> postgres=# select pg_xlog_replay_resume();
> 
>  pg_xlog_replay_resume
> 
> ---
> 
> (1 row)
> 
> 5. Verify the weather replication is resumed or not.
> 
> postgres=# select pg_is_xlog_replay_paused();
> 
>  pg_is_xlog_replay_paused
> 
> --
> 
>  *f*

This procedure is absolutely broken:
a) There'll be further writes even if you stop replay. Both the
   background writer and the checkpointer are active. The latter will
   only create restartpoints, but that's still problematic.
b) Because of the nonexistance of a backup label a backup that's been
   created won't necessarily start up from the right point.

>From 9.2. you can simply use pg_basebackup from standby servers
though. That does all the necessary things internally.

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


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

Re: [GENERAL] Code for user-defined type

[Paul Jones]

- Original Message -
> From: Tom Lane 
> To: Paul Jones 
> Cc: "pavel.steh...@gmail.com" ; 
> "pgsql-general@postgresql.org" 
> Sent: Thursday, May 29, 2014 11:32 AM
> Subject: Re: [GENERAL] Code for user-defined type
> 
> Paul Jones  writes:
> 
>>  I used the proper XGetDatum and DatumGetX and was able to get it to work
>>  properly.  However, I since discovered that I probably should not use
>>  "cstring_to_text" because of the palloc's it does.  The 
> problem comes
>>  when doing "\copy table from file".  After about 1000 rows, 
> the backend
>>  dies with SEGV, I think because of too many pallocs being created in
>>  the copy transaction.
> 
> That probably means you're stomping on memory that doesn't belong to 
> you.
> 
> pallocs per se should not be a problem for COPY --- it does a context
> reset per row.  And even if it didn't, you'd not likely be running out
> of memory after a mere thousand rows.  However, a buffer-overrun type
> of coding error would be probabilistic as to when it became obvious
> via a core dump; some of the time you'd be stomping on memory that
> was unused anyway.
> 
>             regards, tom lane

Ok, I am going to recompile with --enable-cassert and give
cstring_to_text/text_to_cstring another try to see if I can track down
what's going wrong.  I'm letting internal routines do all the work
so it's probably something bad I'm passing to them.

PJ

>


-- 
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] Backups over slave instead master?

[hubert depesz lubaczewski]

OmniPITR (https://github.com/omniti-labs/omnipitr) has working backups off
slave. Working as in - we were using in it production since 8.3 at least.

depesz


On Thu, May 29, 2014 at 8:02 PM, Andres Freund 
wrote:

> Hi,
>
> On 2014-05-16 12:49:25 +0530, chiru r wrote:
> > Yes, It is possible to execute backups on the slave server instead of
> > master.
> >
> > Below are the steps we run for one of our past customer every day to
> > refresh his Dev/test environments using slave backups.
> >
> > *On Slave:*
> >
> > 1. Pause the replication
> >
> > postgres=# select pg_xlog_replay_pause();
> >
> >  pg_xlog_replay_pause
> >
> > --
> >
> > (1 row)
> >
> > 2. Make sure wheather Replication paused or not.
> >
> > postgres =# select pg_is_xlog_replay_paused();
> >
> >  pg_is_xlog_replay_paused
> >
> > --
> >
> > * t*
> >
> > (1 row)
> >
> > 3. Copy the data directory using any one rsync,tar,scp or cp..etc
> >
> > 4. Resume the replication to continue the replication process.
> >
> > postgres=# select pg_xlog_replay_resume();
> >
> >  pg_xlog_replay_resume
> >
> > ---
> >
> > (1 row)
> >
> > 5. Verify the weather replication is resumed or not.
> >
> > postgres=# select pg_is_xlog_replay_paused();
> >
> >  pg_is_xlog_replay_paused
> >
> > --
> >
> >  *f*
>
> This procedure is absolutely broken:
> a) There'll be further writes even if you stop replay. Both the
>background writer and the checkpointer are active. The latter will
>only create restartpoints, but that's still problematic.
> b) Because of the nonexistance of a backup label a backup that's been
>created won't necessarily start up from the right point.
>
> From 9.2. you can simply use pg_basebackup from standby servers
> though. That does all the necessary things internally.
>
> Greetings,
>
> Andres Freund
>
> --
>  Andres Freund http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Is it typo in pg_stat_replication column name in PG 9.4 ?

[Bruce Momjian]

On Sun, May 18, 2014 at 02:10:53PM +0530, Raghavendra wrote:
> Hi,
> 
> PostgreSQL 9.4 document for pg_stat_replication view mentions column name as
> "backend_xid", whereas when a view described it shows column name as
> "backend_xmin".
> http://www.postgresql.org/docs/devel/static/monitoring-stats.html#
> PG-STAT-REPLICATION-VIEW
> 
> postgres=# select version();
> -[ RECORD 1 ]
> 
>  version | PostgreSQL 9.4beta1 on x86_64-unknown-linux-gnu, compiled by gcc
> (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit
> 
> postgres=# \d pg_stat_replication
>           View "pg_catalog.pg_stat_replication"
>       Column      |           Type           | Modifiers
> --+--+---
>  pid              | integer                  |
>  usesysid         | oid                      |
>  usename          | name                     |
>  application_name | text                     |
>  client_addr      | inet                     |
>  client_hostname  | text                     |
>  client_port      | integer                  |
>  backend_start    | timestamp with time zone |
>  backend_xmin     | xid                      |
>  state            | text                     |
>  sent_location    | pg_lsn                   |
>  write_location   | pg_lsn                   |
>  flush_location   | pg_lsn                   |
>  replay_location  | pg_lsn                   |
>  sync_priority    | integer                  |
>  sync_state       | text                     |
> 
> Is it a typo in pg_stat_replication view column ?

This has been fixed by this commit:

commit eb56812144bb310f7cb2f63e53d6d58f30df95b5
Author: Fujii Masao 
Date:   Mon May 19 17:03:30 2014 +0900

Fix incorrect column name in pg_stat_replication document.

Fabrízio de Royes Mello

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

  + Everyone has their own god. +


-- 
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] log_collector & sysout on windows

[Marc Brazeau]

I think I figured this out after some playing around.

Looks like postgres only redirects stderr to its log files.  Consistent
with what documentation says, and my findings playing around.

In my situation, I was looking for sysout of my python archiver to go to
postgres log files.

I added sys.stdout = sys.stderr at the start of entry python script, and
all was well after that.


On Wed, May 28, 2014 at 1:30 PM, Marc Brazeau wrote:

> So I'm sure I'm doing something wrong, or this is maybe a novice question,
>
> On My Mac, I set log_collector=on, archive_command='echo hello %p %f'
> And awesome, my postgres log files get the output from my archive command.
>
> On Windows, this does not work (Using postgres 9.3..4)
>
> Only way to get output from my archive_command, is with the -l option on
> pg_ctl start.  But this has issues:
> - Not sure it will work with Services (thoughI haven't tried).
> - I don't get any of the log_rotation
> - And I lose context (from having sysouts along side other Postgres
> message).
>
> Surely there is a simple answer here?
>

Re: [GENERAL] Postgresql 9.2.4 - timezone error

[rob stone]

On Thu, 2014-05-29 at 20:22 +0530, Bhushan Pathak wrote:
> Hello,
> 
> 
> I have postgresql 9.2.4 running on a CentOS 5 server. A JBOSS AS
> instance also runs on the same server, which connects to postgresql
> server [using defined connection pools].
> 
> 
> The issue I'm facing is that the JBOSS server is throwing out the
> error -
> org.postgresql.util.PSQLException: FATAL: invalid value for parameter
> "TimeZone": "America/New_York"
> 
> 
> 
> When I looked up on the internet, most common answer was to execute
> the following query from CLI which would expose the real problem -
> 
> 
> myDB=# SELECT * FROM pg_timezone_names WHERE name =
> 'America/New_York';
> ERROR:  could not stat "/usr/share/zoneinfo/America/New_York": Too
> many levels of symbolic links
> 
> 
> What does this really mean? Why is postgresql complaining for symbolic
> links? Any ideas what has gone wrong? Any thing that needs checking?
> 
> 
> Thanks
> 
> 
> Bhushan Pathak


We are still running 9.2.4.

This query works fine here:-

"SELECT * FROM pg_timezone_names WHERE name = '$tz_name'::text"

HTH
Robert



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

[GENERAL] Re: PostgreSQL 9.3 XML parser seems not to recognize the DOCTYPE element in XML files

[Bob Moyers]

I have this table:
CREATE TABLE REPORT_STYLE (
REPORT_STYLE_NAME VARCHAR(75) NOT NULL,
REPORT_STYLE_VERSION NUMERIC(8,0) NOT NULL,
UPDATE_TS TIMESTAMP NOT NULL,
UPDATE_USER_NAME VARCHAR(30) NOT NULL,
STYLE_DESCR VARCHAR(200),
JASPER_STYLE XML
);
ALTER TABLE REPORT_STYLE ADD CONSTRAINT PK_RPTSTY PRIMARY KEY 
(REPORT_STYLE_NAME);


And this document (notice the 
http://jasperreports.sourceforge.net/dtds/jaspertemplate.dtd";>


   
   
   
   
 




 
   
   
   
   
   
   
   
   
   
   
   
   
   
   
 




 
   




When I try this update:
UPDATE REPORT_STYLE SET JASPER_STYLE = XMLPARSE(DOCUMENT ?) WHERE 
(REPORT_STYLE_NAME = ?)

I get:
org.postgresql.util.PSQLException: ERROR: invalid XML content
Detail: line 2: StartTag: invalid element name
http://jas
^


I AM using 'XMLPARSE(DOCUMENT'.   Why the error?

Using PG 9.3 with this JDBC driver:
10:37:51,822 INFO  [org.jboss.as.connector.deployers.jdbc] (MSC service thread 
1-11) JBAS010404: Deploying non-JDBC-compliant driver class 
org.postgresql.Driver (version 9.2)

Re: [GENERAL] Postgresql service stopped automatically after restart

[Adrian Klaver]

On 05/29/2014 08:01 AM, Kalai R wrote:

I have checked no problem in pg_hba.conf file.


Have you checked the Postgres or Windows system logs?



Also I have disabled AV software checks for the Postgres Directories.

But Problem remains


There actually seem to be several problems across multiple platforms if 
I am following correctly. An attempt at a solution is going to need a 
great deal more information then what we have been given so far. My 
suggestion would be to stick to one issue at a time and work it to 
completion, say the case where the files are not deleted but the service 
does not start.


For starters, information that would be helpful:

1) OS version

2) What your .NET application is actually doing when installing Postgres.

3) Error messages that the user sees.

4) Error messages from the Postgres/OS logs

5) I assume this system was working in the past, so an indication of 
when it quit working and any changes that occurred over that time period.











--
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] Postgresql service stopped automatically after restart

[David G Johnston]

Kalai R wrote
> I have checked no problem in pg_hba.conf file.
> 
> Also I have disabled AV software checks for the Postgres Directories.
> 
> But Problem remains

Disabling AV won't magically make the system start working if it's already
broken.  You disable it to avoid breaking the system in the first place.

David J.



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Postgresql-service-stopped-automatically-after-restart-tp5805424p5805513.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] Postgresql 9.2.4 - timezone error

[Bhushan Pathak]

There was a redirect loop created somehow -
[root ~]# ls -l /etc/localtime
lrwxrwxrwx 1 root root 36 May 12 01:13 /etc/localtime ->
/usr/share/zoneinfo/America/New_York
[root ~]# ll /usr/share/zoneinfo/America/New_York
lrwxrwxrwx 1 root root 14 Feb 21 00:08 /usr/share/zoneinfo/America/New_York
-> /etc/localtime

I deleted both the links, updated the TZ rpm package & restarted the
server. That solved the issue. Thanks for your help.

Bhushan


On Fri, May 30, 2014 at 1:06 AM, rob stone  wrote:

>
>
>
> On Thu, 2014-05-29 at 20:22 +0530, Bhushan Pathak wrote:
> > Hello,
> >
> >
> > I have postgresql 9.2.4 running on a CentOS 5 server. A JBOSS AS
> > instance also runs on the same server, which connects to postgresql
> > server [using defined connection pools].
> >
> >
> > The issue I'm facing is that the JBOSS server is throwing out the
> > error -
> > org.postgresql.util.PSQLException: FATAL: invalid value for parameter
> > "TimeZone": "America/New_York"
> >
> >
> >
> > When I looked up on the internet, most common answer was to execute
> > the following query from CLI which would expose the real problem -
> >
> >
> > myDB=# SELECT * FROM pg_timezone_names WHERE name =
> > 'America/New_York';
> > ERROR:  could not stat "/usr/share/zoneinfo/America/New_York": Too
> > many levels of symbolic links
> >
> >
> > What does this really mean? Why is postgresql complaining for symbolic
> > links? Any ideas what has gone wrong? Any thing that needs checking?
> >
> >
> > Thanks
> >
> >
> > Bhushan Pathak
>
>
> We are still running 9.2.4.
>
> This query works fine here:-
>
> "SELECT * FROM pg_timezone_names WHERE name = '$tz_name'::text"
>
> HTH
> Robert
>
>