Обсуждение: Postgresql 9.2.4 - timezone error
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
On Wed, May 28, 2014 at 10:51:43AM +0200, Pavel Stehule wrote: > > Hello > > > 2014-05-27 20:30 GMT+02:00 Paul Jones <pbj@cmicdo.com>: > > > 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
2014-05-29 18:04 GMT+02:00 Paul Jones <pbj@cmicdo.com>:
On Wed, May 28, 2014 at 10:51:43AM +0200, Pavel Stehule wrote:
>
> Hello
>
>
> 2014-05-27 20:30 GMT+02:00 Paul Jones <pbj@cmicdo.com>:
>
> > 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 DatumThanks, Pavel!
> 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 -
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
Bhushan Pathak <bhushan.pathak02@gmail.com> 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
Paul Jones <pbj@cmicdo.com> 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
Bhushan Pathak <bhushan.pathak02@gmail.com> 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 connectsto 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 thereal 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 thingthat 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.consulting@comcast.net p: 312.241.7800
----- Original Message ----- > From: Tom Lane <tgl@sss.pgh.pa.us> > To: Paul Jones <pbj@cmicdo.com> > Cc: "pavel.stehule@gmail.com" <pavel.stehule@gmail.com>; "pgsql-general@postgresql.org" <pgsql-general@postgresql.org> > Sent: Thursday, May 29, 2014 11:32 AM > Subject: Re: [GENERAL] Code for user-defined type > > Paul Jones <pbj@cmicdo.com> 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 >
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
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
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 <floriparob@gmail.com> wrote:
We are still running 9.2.4.
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
This query works fine here:-
"SELECT * FROM pg_timezone_names WHERE name = '$tz_name'::text"
HTH
Robert