X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 86B4D9F9FE3 for ; Mon, 26 Jun 2006 12:15:17 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 09717-03 for ; Mon, 26 Jun 2006 12:15:11 -0300 (ADT) X-Greylist: delayed 01:19:35.09656 by SQLgrey- Received: from ar-sd.net (mail.ar-sd.net [81.196.35.136]) by postgresql.org (Postfix) with ESMTP id 133579F9AD3 for ; Mon, 26 Jun 2006 12:15:10 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by ar-sd.net (Postfix) with ESMTP id 43E782FA31; Mon, 26 Jun 2006 18:10:22 +0300 (EEST) Received: from ar-sd.net ([127.0.0.1]) by localhost (ar-sd.net [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 21328-01; Mon, 26 Jun 2006 18:10:19 +0300 (EEST) Received: from forge (forge.mpsro.dom [192.168.0.11]) by ar-sd.net (Postfix) with SMTP id 7735A2F7BC; Mon, 26 Jun 2006 18:10:19 +0300 (EEST) Message-ID: <003801c69933$58bad960$0b00a8c0@mpsro.dom> From: "Andy" To: "Tom Lane" Cc: References: <014601c69928$3dd35740$0b00a8c0@mpsro.dom> <29135.1151333441@sss.pgh.pa.us> Subject: Re: Problem Setting DateStyle Date: Mon, 26 Jun 2006 18:15:20 +0300 MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_0035_01C6994C.7C583E80" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2869 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869 X-Virus-Scanned: by amavisd-new at ar-sd.net X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/229 X-Sequence-Number: 22191 This is a multi-part message in MIME format. ------=_NextPart_000_0035_01C6994C.7C583E80 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit Steps: 1. show datestyle DateStyle ISO, DMY 2. set datestyle to 'SQL, DMY'; show datestyle; DateStyle SQL, DMY >>>>>>>>>> this should be the good result. Now the tries: Edit the postgresql.conf. Set datestyle = 'SQL, DMY' parameter. Restart the DB server. show datestyle DateStyle ISO, DMY ... there is no change. the other try: alter database test2 set datestyle to 'SQL, DMY'; SQL executed. show datestyle DateStyle ISO, DMY the same... Am I missing something??? If I set set datestyle to 'SQL, DMY' in a connection session then it works well for that session. I need this type of format: auftragsdatum 27/06/2005 27/06/2005 27/06/2005 27/06/2005 27/06/2005 27/06/2005 21/11/2005 09/12/2005 07/07/2005 29/06/2005 I don't know what am I missing in the configuration. I attach the postgres.conf. Best regards, Andy. ----- Original Message ----- From: "Tom Lane" To: "Andy" Cc: Sent: Monday, June 26, 2006 5:50 PM Subject: Re: [ADMIN] Problem Setting DateStyle > "Andy" writes: >> I tried to modify it in postgres.conf >>> datestyle = 'European' or >> datestyle = 'SQL, DMY' >> and I tried to make >> alter database test5 SET DateStyle='SQL, DMY'; > >> .... none of these worked. > > They work for me. Perhaps you should show us exactly what you did and > what happened. > > regards, tom lane > > ------=_NextPart_000_0035_01C6994C.7C583E80 Content-Type: application/octet-stream; name="postgresql.conf" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="postgresql.conf" # -----------------------------=0A= # PostgreSQL configuration file=0A= # -----------------------------=0A= #=0A= # This file consists of lines of the form:=0A= #=0A= # name =3D value=0A= #=0A= # (The '=3D' is optional.) White space may be used. Comments are = introduced=0A= # with '#' anywhere on a line. The complete list of option names and=0A= # allowed values can be found in the PostgreSQL documentation. The=0A= # commented-out settings shown in this file represent the default values.=0A= #=0A= # Please note that re-commenting a setting is NOT sufficient to revert it=0A= # to the default value, unless you restart the postmaster.=0A= #=0A= # Any option can also be given as a command line switch to the=0A= # postmaster, e.g. 'postmaster -c log_connections=3Don'. Some options=0A= # can be changed at run-time with the 'SET' SQL command.=0A= #=0A= # This file is read on postmaster startup and when the postmaster=0A= # receives a SIGHUP. If you edit the file on a running system, you have =0A= # to SIGHUP the postmaster for the changes to take effect, or use =0A= # "pg_ctl reload". Some settings, such as listen_address, require=0A= # a postmaster shutdown and restart to take effect.=0A= =0A= =0A= #------------------------------------------------------------------------= ---=0A= # FILE LOCATIONS=0A= #------------------------------------------------------------------------= ---=0A= =0A= # The default values of these variables are driven from the -D command = line=0A= # switch or PGDATA environment variable, represented here as ConfigDir.=0A= # data_directory =3D 'ConfigDir' # use data in another directory=0A= # hba_file =3D 'ConfigDir/pg_hba.conf' # the host-based authentication = file=0A= # ident_file =3D 'ConfigDir/pg_ident.conf' # the IDENT configuration = file=0A= =0A= # If external_pid_file is not explicitly set, no extra pid file is = written.=0A= # external_pid_file =3D '(none)' # write an extra pid file=0A= =0A= =0A= #------------------------------------------------------------------------= ---=0A= # CONNECTIONS AND AUTHENTICATION=0A= #------------------------------------------------------------------------= ---=0A= =0A= # - Connection Settings -=0A= =0A= #listen_addresses =3D 'localhost' # what IP interface(s) to listen on; =0A= # defaults to localhost, '*' =3D any=0A= listen_addresses =3D 'localhost, 192.168.0.10' =0A= =0A= #port =3D 5432=0A= max_connections =3D 50=0A= # note: increasing max_connections costs about 500 bytes of shared=0A= # memory per connection slot, in addition to costs from shared_buffers=0A= # and max_locks_per_transaction.=0A= #superuser_reserved_connections =3D 2=0A= #unix_socket_directory =3D ''=0A= #unix_socket_group =3D ''=0A= #unix_socket_permissions =3D 0777 # octal=0A= #rendezvous_name =3D '' # defaults to the computer name=0A= =0A= # - Security & Authentication -=0A= =0A= #authentication_timeout =3D 60 # 1-600, in seconds=0A= #ssl =3D false=0A= #password_encryption =3D true=0A= #krb_server_keyfile =3D ''=0A= #db_user_namespace =3D false=0A= =0A= =0A= #------------------------------------------------------------------------= ---=0A= # RESOURCE USAGE (except WAL)=0A= #------------------------------------------------------------------------= ---=0A= =0A= # - Memory -=0A= =0A= #shared_buffers =3D 1000 # min 16, at least max_connections*2, 8KB each=0A= #work_mem =3D 1024 # min 64, size in KB=0A= #maintenance_work_mem =3D 16384 # min 1024, size in KB=0A= #max_stack_depth =3D 2048 # min 100, size in KB=0A= =0A= #2005.05.11 - andy=0A= #shared_buffers =3D 28160 # 220MB =3D 220 x 1024 x 1024 / (8 x 1024) = (8KB)=0A= #shared_buffers =3D 5000 # 220MB =3D 220 x 1024 x 1024 / (8 x 1024) = (8KB)=0A= shared_buffers =3D 5120 # 40MB =3D 40 x 1024 x 1024 / (8 x 1024) (8KB)=0A= #work_mem =3D 10240 # 10MB =3D 10 x 1024 KB=0A= work_mem =3D 2048 # 2MB =3D 2 x 1024 KB=0A= maintenance_work_mem =3D 51200 # 50MB =3D 50 x 1024 KB=0A= =0A= =0A= =0A= =0A= # - Free Space Map -=0A= =0A= #max_fsm_pages =3D 20000 # min max_fsm_relations*16, 6 bytes each=0A= #max_fsm_relations =3D 1000 # min 100, ~50 bytes each=0A= =0A= # - Kernel Resource Usage -=0A= =0A= #max_files_per_process =3D 1000 # min 25=0A= #preload_libraries =3D ''=0A= =0A= # - Cost-Based Vacuum Delay -=0A= =0A= #vacuum_cost_delay =3D 0 # 0-1000 milliseconds=0A= #vacuum_cost_page_hit =3D 1 # 0-10000 credits=0A= #vacuum_cost_page_miss =3D 10 # 0-10000 credits=0A= #vacuum_cost_page_dirty =3D 20 # 0-10000 credits=0A= #vacuum_cost_limit =3D 200 # 0-10000 credits=0A= =0A= # - Background writer -=0A= =0A= #bgwriter_delay =3D 200 # 10-10000 milliseconds between rounds=0A= #bgwriter_percent =3D 1 # 0-100% of dirty buffers in each round=0A= #bgwriter_maxpages =3D 100 # 0-1000 buffers max per round=0A= =0A= =0A= #------------------------------------------------------------------------= ---=0A= # WRITE AHEAD LOG=0A= #------------------------------------------------------------------------= ---=0A= =0A= # - Settings -=0A= =0A= #fsync =3D true # turns forced synchronization on or off=0A= #wal_sync_method =3D fsync # the default varies across platforms:=0A= # fsync, fdatasync, open_sync, or open_datasync=0A= #wal_buffers =3D 8 # min 4, 8KB each=0A= #commit_delay =3D 0 # range 0-100000, in microseconds=0A= #commit_siblings =3D 5 # range 1-1000=0A= =0A= # - Checkpoints -=0A= =0A= checkpoint_segments =3D 5 # in logfile segments, min 1, 16MB each=0A= #checkpoint_timeout =3D 300 # range 30-3600, in seconds=0A= #checkpoint_warning =3D 30 # 0 is off, in seconds=0A= =0A= # - Archiving -=0A= =0A= #archive_command =3D '' # command to use to archive a logfile segment=0A= =0A= =0A= #------------------------------------------------------------------------= ---=0A= # QUERY TUNING=0A= #------------------------------------------------------------------------= ---=0A= =0A= # - Planner Method Configuration -=0A= =0A= #enable_hashagg =3D true=0A= #enable_hashjoin =3D true=0A= #enable_indexscan =3D true=0A= #enable_mergejoin =3D true=0A= #enable_nestloop =3D true=0A= #enable_seqscan =3D true=0A= #enable_sort =3D true=0A= #enable_tidscan =3D true=0A= =0A= # - Planner Cost Constants -=0A= =0A= #effective_cache_size =3D 1000 # typically 8KB each=0A= random_page_cost =3D 3 # units are one sequential page fetch cost=0A= #cpu_tuple_cost =3D 0.01 # (same)=0A= #cpu_index_tuple_cost =3D 0.001 # (same)=0A= #cpu_operator_cost =3D 0.0025 # (same)=0A= =0A= #effective_cache_size =3D 6400 # 50MB =3D 50 x 1024 / 8 #2005.05.11 - = andy=0A= #effective_cache_size =3D 12800 # 100MB =3D 100 x 1024 / 8 #2005.05.11 - = andy=0A= effective_cache_size =3D 32000 # 250MB =3D 250 x 1024 / 8 #2005.05.11 - = andy=0A= #effective_cache_size =3D 57600 # 450MB =3D 450 x 1024 / 8 #2005.05.11 - = andy=0A= #effective_cache_size =3D 157600 # 450MB =3D 450 x 1024 / 8 #2005.05.11 = - andy=0A= =0A= =0A= # - Genetic Query Optimizer -=0A= =0A= #geqo =3D true=0A= #geqo_threshold =3D 12=0A= #geqo_effort =3D 5 # range 1-10=0A= #geqo_pool_size =3D 0 # selects default based on effort=0A= #geqo_generations =3D 0 # selects default based on effort=0A= #geqo_selection_bias =3D 2.0 # range 1.5-2.0=0A= =0A= # - Other Planner Options -=0A= =0A= #default_statistics_target =3D 10 # range 1-1000=0A= #from_collapse_limit =3D 8=0A= #join_collapse_limit =3D 8 # 1 disables collapsing of explicit JOINs=0A= =0A= =0A= #------------------------------------------------------------------------= ---=0A= # ERROR REPORTING AND LOGGING=0A= #------------------------------------------------------------------------= ---=0A= =0A= # - Where to Log -=0A= =0A= #log_destination =3D 'stderr' # Valid values are combinations of stderr,=0A= # syslog and eventlog, depending on=0A= # platform.=0A= =0A= # This is relevant when logging to stderr:=0A= #redirect_stderr =3D false # Enable capturing of stderr into log = files.=0A= # These are only relevant if redirect_stderr is true:=0A= #log_directory =3D 'pg_log' # Directory where log files are written.=0A= # May be specified absolute or relative to = PGDATA=0A= #log_filename =3D 'postgresql-%Y-%m-%d_%H%M%S.log' # Log file name = pattern.=0A= # May include strftime() escapes=0A= #log_truncate_on_rotation =3D false # If true, any existing log file of = the =0A= # same name as the new log file will be = truncated=0A= # rather than appended to. But such = truncation=0A= # only occurs on time-driven rotation,=0A= # not on restarts or size-driven rotation.=0A= # Default is false, meaning append to = existing =0A= # files in all cases.=0A= #log_rotation_age =3D 1440 # Automatic rotation of logfiles will = happen after=0A= # so many minutes. 0 to disable.=0A= #log_rotation_size =3D 10240 # Automatic rotation of logfiles will = happen after=0A= # so many kilobytes of log output. 0 to = disable.=0A= =0A= # These are relevant when logging to syslog:=0A= #syslog_facility =3D 'LOCAL0'=0A= #syslog_ident =3D 'postgres'=0A= =0A= =0A= # - When to Log -=0A= =0A= client_min_messages =3D notice # Values, in order of decreasing detail:=0A= # debug5, debug4, debug3, debug2, debug1,=0A= # log, notice, warning, error=0A= =0A= log_min_messages =3D notice # Values, in order of decreasing detail:=0A= # debug5, debug4, debug3, debug2, debug1,=0A= # info, notice, warning, error, log, fatal,=0A= # panic=0A= =0A= #log_error_verbosity =3D default # terse, default, or verbose messages=0A= =0A= #log_min_error_statement =3D panic # Values in order of increasing = severity:=0A= # debug5, debug4, debug3, debug2, debug1,=0A= # info, notice, warning, error, panic(off)=0A= =0A= #log_min_duration_statement =3D -1 # -1 is disabled, in milliseconds.=0A= =0A= #silent_mode =3D false # DO NOT USE without syslog or redirect_stderr=0A= =0A= # - What to Log -=0A= =0A= #debug_print_parse =3D false=0A= #debug_print_rewritten =3D false=0A= #debug_print_plan =3D false=0A= #debug_pretty_print =3D false=0A= log_connections =3D true=0A= #log_disconnections =3D false=0A= log_duration =3D true=0A= log_line_prefix =3D '%t %p - %l ' # e.g. '<%u%%%d> ' =0A= # %u=3Duser name %d=3Ddatabase name=0A= # %r=3Dremote host and port=0A= # %p=3DPID %t=3Dtimestamp %i=3Dcommand tag=0A= # %c=3Dsession id %l=3Dsession line number=0A= # %s=3Dsession start timestamp %x=3Dtransaction id=0A= # %q=3Dstop here in non-session processes=0A= # %%=3D'%'=0A= log_statement =3D 'all' # none, mod, ddl, all=0A= #log_hostname =3D true ## andy - don't use IT!=0A= =0A= =0A= #------------------------------------------------------------------------= ---=0A= # RUNTIME STATISTICS=0A= #------------------------------------------------------------------------= ---=0A= =0A= # - Statistics Monitoring -=0A= =0A= #log_parser_stats =3D false=0A= #log_planner_stats =3D false=0A= #log_executor_stats =3D false=0A= #log_statement_stats =3D false=0A= =0A= # - Query/Index Statistics Collector -=0A= =0A= #stats_start_collector =3D true=0A= #stats_command_string =3D false=0A= #stats_block_level =3D false=0A= #stats_row_level =3D false=0A= #stats_reset_on_server_start =3D true=0A= =0A= =0A= #------------------------------------------------------------------------= ---=0A= # CLIENT CONNECTION DEFAULTS=0A= #------------------------------------------------------------------------= ---=0A= =0A= # - Statement Behavior -=0A= =0A= #search_path =3D '$user,public' # schema names=0A= #default_tablespace =3D '' # a tablespace name, or '' for default=0A= #check_function_bodies =3D true=0A= #default_transaction_isolation =3D 'read committed'=0A= #default_transaction_read_only =3D false=0A= #statement_timeout =3D 0 # 0 is disabled, in milliseconds=0A= =0A= # - Locale and Formatting -=0A= =0A= #datestyle =3D 'iso, mdy'=0A= datestyle =3D 'SQL, DMY'=0A= #datestyle =3D 'ISO,US'=0A= #timezone =3D unknown # actually, defaults to TZ environment setting=0A= #australian_timezones =3D false=0A= #extra_float_digits =3D 0 # min -15, max 2=0A= #client_encoding =3D sql_ascii # actually, defaults to database encoding=0A= =0A= # These settings are initialized by initdb -- they might be changed=0A= lc_messages =3D 'en_US.UTF-8' # locale for system error message strings=0A= lc_monetary =3D 'en_US.UTF-8' # locale for monetary formatting=0A= lc_numeric =3D 'en_US.UTF-8' # locale for number formatting=0A= #lc_time =3D 'en_US.UTF-8' # locale for time formatting=0A= =0A= # - Other Defaults -=0A= =0A= #explain_pretty_print =3D true=0A= #dynamic_library_path =3D '$libdir'=0A= =0A= =0A= #------------------------------------------------------------------------= ---=0A= # LOCK MANAGEMENT=0A= #------------------------------------------------------------------------= ---=0A= =0A= #deadlock_timeout =3D 1000 # in milliseconds=0A= #max_locks_per_transaction =3D 64 # min 10, ~200*max_connections bytes = each=0A= =0A= =0A= #------------------------------------------------------------------------= ---=0A= # VERSION/PLATFORM COMPATIBILITY=0A= #------------------------------------------------------------------------= ---=0A= =0A= # - Previous Postgres Versions -=0A= =0A= #add_missing_from =3D true=0A= #regex_flavor =3D advanced # advanced, extended, or basic=0A= #sql_inheritance =3D true=0A= #default_with_oids =3D true=0A= =0A= # - Other Platforms & Clients -=0A= =0A= #transform_null_equals =3D false=0A= ------=_NextPart_000_0035_01C6994C.7C583E80--