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

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