Thread: BUG #8135: current_setting('DateStyle'); does not reflect User setting

The following bug has been logged on the website:

Bug reference:      8135
Logged by:          Frank van den Heuvel
Email address:      frank@heuveltop.nl
PostgreSQL version: 9.1.8
Operating system:   Ubuntu 12.04
Description:        =


Dear developers,

I think I have found a bug. I am building an application that relies on user
setting of TimeZone and DateStyle.

After
 ALTER ROLE frank SET TimeZone =3D 'Europe/Amsterdam';

Where the TimeZone differs from the default timezone.

 SELECT current_setting('TimeZone');

Gives the correct answer.

But this same doesn't work for DateStyle

  ALTER ROLE frank SET DateStyle =3D 'SQL, DMY';

Where the DateStyle differs from the default DateStyle

 SELECT current_setting('DateStyle');

Gives the system/database setting but not the user setting; while the
setting does have its effect on the output of date's and timestamps. This
might also effect other user settings, but I haven't found any yet.

My application needs to know what the user setting is, so this is a
workaround that I use:

WITH user_config AS (
SELECT rolname, split_part(unnest(rolconfig), '=3D', 1) AS key,
split_part(unnest(rolconfig), '=3D', 2) AS value
FROM pg_roles )

SELECT *
FROM user_config
WHERE rolname =3D USER
AND key ILIKE 'DateStyle';


Thank you for reading,

Frank

On 05.05.2013 21:18, frank@heuveltop.nl wrote:
> PostgreSQL version: 9.1.8

Not that it makes any difference for this issue, but you should upgrade
to 9.1.9.

> After
>   ALTER ROLE frank SET TimeZone = 'Europe/Amsterdam';
>
> Where the TimeZone differs from the default timezone.
>
>   SELECT current_setting('TimeZone');
>
> Gives the correct answer.
>
> But this same doesn't work for DateStyle
>
>    ALTER ROLE frank SET DateStyle = 'SQL, DMY';
>
> Where the DateStyle differs from the default DateStyle
>
>   SELECT current_setting('DateStyle');
>
> Gives the system/database setting but not the user setting; while the
> setting does have its effect on the output of date's and timestamps. This
> might also effect other user settings, but I haven't found any yet.

Works for me. Are you sure the value isn't being overridden by a
per-database-and-role setting?  You can use "select source from
pg_settings where name='DateStyle'" to check where the currently
effective value came from.

- Heikki