Postgresql 9.2.4 - timezone error

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

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

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

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

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

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

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

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

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 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.consulting@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

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

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

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

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 <floriparob@gmail.com> wrote: