1

I have an application that deals with timezones, so naturally I want to be able to view the list of timezones in Postgresql.

I tried to view all available timezones with the following command:

SELECT * FROM pg_timezone_names;

Which prints the following error:

ERROR: timezone directory stack overflow

I even tried limiting the results and received the same error:

SELECT * FROM pg_timezone_names LIMIT 10;

At first I thought it might just be my IDE of choice having issues, but I tried to run the same sql query on the command line and received the same error. I can't, for the life of me, find an answer to this issue. Any information would be helpful. Thanks!

EDIT: I'm using Postgresql 9.3 on Gentoo. Self compiled version of postgresql-server.

Note: I can query the pg_timezone_abbrevs table just fine, just not pg_timezone_names.

DerekE
  • 235
  • 4
  • 15
  • What version of Postgres are you using? What OS? Did you use a binary release, or did you compile it yourself? And if so, did you use the `--with-system-tzdata` flag [described on this page](http://www.postgresql.org/docs/9.3/static/install-procedure.html)? – Matt Johnson-Pint Sep 25 '14 at 23:01
  • @MattJohnson I updated my question to include that information. In portage, Gentoo's package manager, there is no flag for --with-system-tzdata, so I assume it's buit in. I can quest the pg_timezone_abbrevs just fine, just not the names table. – DerekE Sep 25 '14 at 23:17
  • I looked through the ebuild for postgresql-server and it appears --with-system-tzdata is infact built in. http://sources.gentoo.org/cgi-bin/viewvc.cgi/gentoo-x86/dev-db/postgresql-server/postgresql-server-9.3.4.ebuild?view=markup – DerekE Sep 25 '14 at 23:32

3 Answers3

1

According to this Postgres FAQ:

... On platforms that receive regular software updates including new tzdata files, it may be more convenient to rely on the system's copy of the tzdata files. This is possible as a compile-time option. Most Linux distributions choose this approach for their pre-built versions of PostgreSQL. ...

The compile-time option referred to is the --with-system-tzdata, which is described about half-way down this page.

My guess is that this flag is set by the Portage distribution you're using, and that the tzdata is either missing or corrupted on your system. On Gentoo, the tzdata is distributed in the timezone-data package, which you can find here.

Try:

# emerge timezone-data

Then see if that fixes the problem.

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
  • I did already have that package on my system, but I did emerge it again anyway and still receive the same problem. I'm going to try and compile Postgresql and see if maybe that helps. Thanks for the answer anyway, I'm sure this will help someone. – DerekE Sep 26 '14 at 00:03
  • Sorry - that was my best guess. I hope it points you in the right direction. I did see a few examples of `emerge --config timezone-data`, but I'm not sure the difference. – Matt Johnson-Pint Sep 26 '14 at 00:05
  • emerge --config timezone-data actually configures the package after being installed. I tried that as well with no luck. Hopefully I can figure it out.. – DerekE Sep 26 '14 at 01:24
1

This is an answer to my own question.

The following is at least true on the Gentoo distro of linux, but I assume can be present on others.

What causes the error mentioned in my post is a symlink loop by /usr/share/zoneinfo/posix

To solve this error you just have to delete that symlink, as follows:

rm /usr/share/zoneinfo/posix

Pretty easy fix. Be aware that any update to zoneinfo will cause this symlink to be recreated and thus requiring you to remove it again. I haven't had any issues with this link being removed, but I can't say the same for others. If anyone has a reason for NOT deleting this symlink, just post a comment stating so.

DerekE
  • 235
  • 4
  • 15
1

Had the exact same issue on Ubuntu 14.04 LTS with PostgreSQL 9.3.3 however my recursive symlink was with /usr/share/zoneinfo/localtime. Fixed by running:

$ sudo unlink /usr/share/zoneinfo/localtime

Alan Gervais
  • 11
  • 1