Horton

Thread

  1. CREATE TABLE ... PRIMARY KEY kills backend

    Oliver Elphick <olly@lfix.co.uk> — 2000-01-11T22:29:28Z

    Using the cvs version updated this morning, this query kills the backend,
with no explanation in the log (-d 3):

  create table junk (id char(4) primary key, name text not null)

If the primary key constraint is omitted, it is OK.

This worked yesterday.  Is this a solved problem, or do I need to trace it?

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
               PGP key from public servers; key ID 32B8FAA1
                 ========================================
     "Enter into his gates with thanksgiving, and into his 
      courts with praise. Be thankful unto him, and bless 
      his name."           Psalms 100:4

  2. Re: [HACKERS] CREATE TABLE ... PRIMARY KEY kills backend

    Bruce Momjian <pgman@candle.pha.pa.us> — 2000-01-12T00:01:17Z

    > Using the cvs version updated this morning, this query kills the backend,
> with no explanation in the log (-d 3):
> 
>   create table junk (id char(4) primary key, name text not null)
> 
> If the primary key constraint is omitted, it is OK.
> 
> This worked yesterday.  Is this a solved problem, or do I need to trace it?
> 

Works for me on current sources:

test=>   create table junk (id char(4) primary key, name text not null);
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'junk_pkey'
for table 'junk'
CREATE

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

  3. Re: [HACKERS] CREATE TABLE ... PRIMARY KEY kills backend

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-12T00:09:00Z

    "Oliver Elphick" <olly@lfix.co.uk> writes:
> Using the cvs version updated this morning, this query kills the backend,
> with no explanation in the log (-d 3):
>
>   create table junk (id char(4) primary key, name text not null)

Works for me:

regression=# create table junk (id char(4) primary key, name text not null);
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'junk_pkey' for table 'junk'
CREATE

Are you sure you have a complete update of the INDEX_MAX_KEYS changes?
I committed the last of them about 1am EST (6am GMT) this morning, and
it was a change to config.h.in ---- you would need to do a *full*
configure, build, initdb cycle to be sure you have working code.

If that doesn't do it for you, there may be a platform-dependent bug
still lurking; can you provide a debugger backtrace of the crashed
backend?

I'd also suggest running the regress tests ... they pass here, with
the exception of the 'array' test ...

			regards, tom lane

  4. Re: [HACKERS] CREATE TABLE ... PRIMARY KEY kills backend

    Oliver Elphick <olly@lfix.co.uk> — 2000-01-12T10:31:43Z

    Tom Lane wrote:
  >"Oliver Elphick" <olly@lfix.co.uk> writes:
  >> Using the cvs version updated this morning, this query kills the backend,
  >> with no explanation in the log (-d 3):
  >>
  >>   create table junk (id char(4) primary key, name text not null)
  >
  >Works for me:
  >
  >regression=# create table junk (id char(4) primary key, name text not null);
  >NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'junk_pkey' for
      > table 'junk'
  >CREATE
  >
  >Are you sure you have a complete update of the INDEX_MAX_KEYS changes?
  >I committed the last of them about 1am EST (6am GMT) this morning, and
  >it was a change to config.h.in ---- you would need to do a *full*
  >configure, build, initdb cycle to be sure you have working code.
  
./configure --with-tcl --with-mb=UNICODE --with-odbc --enable-locale 
--with-maxbackends=64 --with-pgport=5431 --program-prefix=pg7.

(The program-prefix doesn't seem to do anything.)

Database destoyed and initdb run...

  >If that doesn't do it for you, there may be a platform-dependent bug
  >still lurking; can you provide a debugger backtrace of the crashed
  >backend?

Segmentation fault (in the end):
#0  0x400f068d in _IO_default_xsputn () from /lib/libc.so.6
#1  0x400e0126 in vfprintf () from /lib/libc.so.6
#2  0x400edf23 in vsnprintf () from /lib/libc.so.6
#3  0x80a8e82 in appendStringInfo ()
#4  0x80c244d in _outTypeName ()
#5  0x80c43da in _outNode ()
#6  0x80c2391 in _outColumnDef ()
... 
#157128 0x80c23f6 in _outColumnDef ()
#157129 0x80c43ca in _outNode ()
#157130 0x80c407c in _outNode ()
#157131 0x80c3f7a in _outConstraint ()
#157132 0x80c475a in _outNode ()
#157133 0x80c407c in _outNode ()
#157134 0x80c23f6 in _outColumnDef ()
#157135 0x80c43ca in _outNode ()
#157136 0x80c407c in _outNode ()
#157137 0x80c219c in _outCreateStmt ()
#157138 0x80c43aa in _outNode ()
#157139 0x80c2578 in _outQuery ()
#157140 0x80c43fa in _outNode ()
#157141 0x80c47fd in nodeToString ()
#157142 0x80ed791 in pg_parse_and_plan ()
#157143 0x80eda46 in pg_exec_query_dest ()
#157144 0x80eda01 in pg_exec_query ()
#157145 0x80eeb82 in PostgresMain ()
#157146 0x80d7ee7 in DoBackend ()
#157147 0x80d7abe in BackendStartup ()
#157148 0x80d6cc9 in ServerLoop ()
#157149 0x80d66ae in PostmasterMain ()
#157150 0x80ae2cb in main ()
#157151 0x400bc7e2 in __libc_start_main () from /lib/libc.so.6

I don't have any line number info, so I'll have to rebuild in order to
do more detailed tracing.

  >I'd also suggest running the regress tests ... they pass here, with
  >the exception of the 'array' test ...

The regression test doesn't seem to work at all with multibyte enabled:

=============== creating new regression database...   =================
createdb: "UNICODE" is not a valid encoding name.
createdb failed
ACTUAL RESULTS OF REGRESSION TEST ARE NOW IN FILE regress.out

The reason is that regress.sh uses createdb; createdb has a bad test for
the encoding value (I have sent a patch separately).  So I assume no-one
has run regression tests with multibyte-encoding enabled?

I fixed the createdb bug and ran the regression test.  The constraints
test failed when trying to create a table with a primary key.  Every
test thereafter failed immediately [pqReadData() -- backend closed the
channel unexpectedly]; it appears that the primary key error messes up
the postmaster in some way.


-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
               PGP key from public servers; key ID 32B8FAA1
                 ========================================
     "For the LORD is good; his mercy is everlasting; and 
      his truth endureth to all generations."         
                                     Psalms 100:5

  5. Re: [HACKERS] CREATE TABLE ... PRIMARY KEY kills backend

    Oliver Elphick <olly@lfix.co.uk> — 2000-01-12T12:24:14Z

    "Oliver Elphick" wrote:
  >I don't have any line number info, so I'll have to rebuild in order to
  >do more detailed tracing.

QUERY: create table oljunk (id char(2) primary key, name text);

(gdb) n
134             _outNode(str, node->raw_default);
(gdb) p *str
$25 = {
  data = 0x81dd788 "{ QUERY :command 5  :create oljunk { CREATE :relname oljunk  :istemp false \t:columns ({ COLUMNDEF :colname id :typename { TYPENAME :name bpchar :timezone false :setof false typmod 6 :arrayBounds  :arr"..., len = 263, 
  maxlen = 512}
(gdb) n
135             appendStringInfo(str, " :cooked_default %s :constraints ",
(gdb) n
137             _outNode(str, node->constraints);
(gdb) n

Program received signal SIGSEGV, Segmentation fault.
0x400f068a in _IO_default_xsputn () from /lib/libc.so.6

This is the backtrace before calling _outNode() at 137:
#0  _outColumnDef (str=0xbfffe8cc, node=0x81dd610) at outfuncs.c:137
        str = 0xbfffe8cc
        node = (ColumnDef *) 0x81dd610
#1  0x80c43da in _outNode (str=0xbfffe8cc, obj=0x81dd610) at outfuncs.c:1355
        str = 0xbfffe8cc
        obj = (void *) 0x81dd610
#2  0x80c408c in _outNode (str=0xbfffe8cc, obj=0x81dd970) at outfuncs.c:1336
        l = (List *) 0x81dd970
        str = 0xbfffe8cc
        obj = (void *) 0x81dd970
#3  0x80c21ac in _outCreateStmt (str=0xbfffe8cc, node=0x81dd7b8)
    at outfuncs.c:74
        str = 0xbfffe8cc
        node = (CreateStmt *) 0x81dd7b8
#4  0x80c43ba in _outNode (str=0xbfffe8cc, obj=0x81dd7b8) at outfuncs.c:1348
        str = 0xbfffe8cc
        obj = (void *) 0x81dd7b8
#5  0x80c2588 in _outQuery (str=0xbfffe8cc, node=0x81dd8e8) at outfuncs.c:185
        str = 0xbfffe8cc
        node = (Query *) 0x81dd8e8
#6  0x80c440a in _outNode (str=0xbfffe8cc, obj=0x81dd8e8) at outfuncs.c:1364
        str = 0xbfffe8cc
        obj = (void *) 0x81dd8e8
#7  0x80c480d in nodeToString (obj=0x81dd8e8) at outfuncs.c:1570
        obj = (void *) 0x81dd8e8
        str = {
  data = 0x81ddc20 "{ QUERY :command 5  :create oljunk { CREATE :relname oljunk  :istemp false \t:columns ({ COLUMNDEF :colname id :typename { TYPENAME :name bpchar :timezone false :setof false typmod 6 :arrayBounds  :arr"..., len = 298, 
  maxlen = 512}
#8  0x80ed7a1 in pg_parse_and_plan (
    query_string=0x8184da0 "create table oljunk (id char(2) primary key, name text)", typev=0x0, nargs=0, queryListP=0xbfffe97c, dest=Remote, 
    aclOverride=0 '\000') at postgres.c:435
        query_string = 0x81dd8e8 "X\002"
        aclOverride = 0 '\000'
        querytree_list = (List *) 0x81dd8e8
        plan_list = (List *) 0x0
        querytree_list_item = (List *) 0x81dda60
        querytree = (Query *) 0x81dd8e8
        plan = (Plan *) 0x81dd8e8
        new_list = (List *) 0x0
        rewritten = (List *) 0xf5


Now we run on a bit, and we go into a recursive loop inside _outNode:

_outNode (str=0xbfffe8cc, obj=0x81dd9a0) at outfuncs.c:1323
1323            if (obj == NULL)
(gdb) p *str
$21 = {
  data = 0x81dde28 "{ QUERY :command 5  :create oljunk { CREATE :relname oljunk  :istemp false \t:columns ({ COLUMNDEF :colname id :typename { TYPENAME :name bpchar :timezone false :setof false typmod 6 :arrayBounds  :arrayBounds <>} :is_not_null true :is_sequence false :raw_default <> :cooked_default <> :constraints ({ oljunk_pkey :type PRIMARY KEY ({ COLUMNDEF :colname id :typename { TYPENAME :name bpchar :timezone false :setof false typmod 6 :arrayBounds  :arrayBounds <>} :is_not_null true :is_sequence false :raw_default <> :cooked_default <> :constraints ({ oljunk_pkey :type PRIMARY KEY ({ COLUMNDEF :colname id :typename { TYPENAME :name bpchar :timezone false :setof false typmod 6 :arrayBounds  :arrayBounds <>} :is_not_null true :is_sequence false :raw_default <> :cooked_default <> :constraints ({ oljunk_pkey :type PRIMARY KEY ", len = 823, maxlen = 1024}
(gdb) 

(gdb) bt
#0  _outNode (str=0xbfffe8cc, obj=0x81dd9a0) at outfuncs.c:1323
#1  0x80c3f8a in _outConstraint (str=0xbfffe8cc, node=0x81dd5e8)
    at outfuncs.c:1283
#2  0x80c476a in _outNode (str=0xbfffe8cc, obj=0x81dd5e8) at outfuncs.c:1528
#3  0x80c408c in _outNode (str=0xbfffe8cc, obj=0x81dd660) at outfuncs.c:1336
#4  0x80c2406 in _outColumnDef (str=0xbfffe8cc, node=0x81dd610)
    at outfuncs.c:137
#5  0x80c43da in _outNode (str=0xbfffe8cc, obj=0x81dd610) at outfuncs.c:1355
#6  0x80c408c in _outNode (str=0xbfffe8cc, obj=0x81dd9a0) at outfuncs.c:1336
#7  0x80c3f8a in _outConstraint (str=0xbfffe8cc, node=0x81dd5e8)
    at outfuncs.c:1283
#8  0x80c476a in _outNode (str=0xbfffe8cc, obj=0x81dd5e8) at outfuncs.c:1528
#9  0x80c408c in _outNode (str=0xbfffe8cc, obj=0x81dd660) at outfuncs.c:1336
#10 0x80c2406 in _outColumnDef (str=0xbfffe8cc, node=0x81dd610)
    at outfuncs.c:137
#11 0x80c43da in _outNode (str=0xbfffe8cc, obj=0x81dd610) at outfuncs.c:1355
#12 0x80c408c in _outNode (str=0xbfffe8cc, obj=0x81dd9a0) at outfuncs.c:1336
#13 0x80c3f8a in _outConstraint (str=0xbfffe8cc, node=0x81dd5e8)
    at outfuncs.c:1283
#14 0x80c476a in _outNode (str=0xbfffe8cc, obj=0x81dd5e8) at outfuncs.c:1528
#15 0x80c408c in _outNode (str=0xbfffe8cc, obj=0x81dd660) at outfuncs.c:1336
#16 0x80c2406 in _outColumnDef (str=0xbfffe8cc, node=0x81dd610)
    at outfuncs.c:137
#17 0x80c43da in _outNode (str=0xbfffe8cc, obj=0x81dd610) at outfuncs.c:1355
#18 0x80c408c in _outNode (str=0xbfffe8cc, obj=0x81dd970) at outfuncs.c:1336
#19 0x80c21ac in _outCreateStmt (str=0xbfffe8cc, node=0x81dd7b8)
    at outfuncs.c:74
#20 0x80c43ba in _outNode (str=0xbfffe8cc, obj=0x81dd7b8) at outfuncs.c:1348
#21 0x80c2588 in _outQuery (str=0xbfffe8cc, node=0x81dd8e8) at outfuncs.c:185
#22 0x80c440a in _outNode (str=0xbfffe8cc, obj=0x81dd8e8) at outfuncs.c:1364
#23 0x80c480d in nodeToString (obj=0x81dd8e8) at outfuncs.c:1570
#24 0x80ed7a1 in pg_parse_and_plan (
    query_string=0x8184da0 "create table oljunk (id char(2) primary key, name text)", typev=0x0, nargs=0, queryListP=0xbfffe97c, dest=Remote, 
    aclOverride=0 '\000') at postgres.c:435


-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
               PGP key from public servers; key ID 32B8FAA1
                 ========================================
     "For the LORD is good; his mercy is everlasting; and 
      his truth endureth to all generations."         
                                     Psalms 100:5

  6. Re: [HACKERS] CREATE TABLE ... PRIMARY KEY kills backend

    Tatsuo Ishii <t-ishii@sra.co.jp> — 2000-01-12T13:14:01Z

    > ./configure --with-tcl --with-mb=UNICODE --with-odbc --enable-locale 
> --with-maxbackends=64 --with-pgport=5431 --program-prefix=pg7.

I didn't see your problem here. My configuratuion is:

	./configure --with-mb=EUC_JP

> The reason is that regress.sh uses createdb; createdb has a bad test for
> the encoding value (I have sent a patch separately).  So I assume no-one
> has run regression tests with multibyte-encoding enabled?

Thank you for the fix. I have just committed your changes.

I already have fixed that too with my private working files, but
forgot to commit.
--
Tatsuo Ishii

  7. Re: [HACKERS] CREATE TABLE ... PRIMARY KEY kills backend

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-01-12T15:31:29Z

    "Oliver Elphick" <olly@lfix.co.uk> writes:
> Segmentation fault (in the end):
> #0  0x400f068d in _IO_default_xsputn () from /lib/libc.so.6
> #1  0x400e0126 in vfprintf () from /lib/libc.so.6
> #2  0x400edf23 in vsnprintf () from /lib/libc.so.6
> #3  0x80a8e82 in appendStringInfo ()
> #4  0x80c244d in _outTypeName ()
> #5  0x80c43da in _outNode ()
> #6  0x80c2391 in _outColumnDef ()
> ... 
> #157128 0x80c23f6 in _outColumnDef ()
> #157129 0x80c43ca in _outNode ()
> #157130 0x80c407c in _outNode ()
   ^^^^^^

Hmm, I take it this is a stack-growth-limit-exceeded failure, although
your system isn't reporting it that way.

This is a known bug that's been there for quite a while: PRIMARY KEY
generates a parse tree with circular references, so if you have parse
tree dumping turned on, you get an infinite recursion in the dumper
code.  It needs to be fixed, but hasn't gotten to the top of anyone's
to-do list (and a clean way to fix it isn't obvious).

> I fixed the createdb bug and ran the regression test.  The constraints
> test failed when trying to create a table with a primary key.

If you had -d set high enough, it would...

> Every
> test thereafter failed immediately [pqReadData() -- backend closed the
> channel unexpectedly]; it appears that the primary key error messes up
> the postmaster in some way.

The WAL postmaster takes a few seconds to recover before it will allow
new connections (I have a proposal on the table that it should just
delay accepting the connections, instead of rejecting 'em, but there
hasn't been any discussion about that).  I usually see that the next
three or four regression tests fail after a crash, but if your machine
is fast enough it might be that they all do.

I have noticed that recent versions of libpq fail to display the
connection-refused error message that I assume the postmaster is
returning.  That useta work ... someone broke it ...

			regards, tom lane

  8. Re: [HACKERS] Date/time types: big changeu

    Oliver Elphick <olly@lfix.co.uk> — 2000-02-16T23:20:26Z

    Tom Lane wrote:
  >Bruce Momjian <pgman@candle.pha.pa.us> writes:
  >>> Also, I've changed the default date style to "ISO" (not just in
  >>> time for Y2K, but we'll be ready for "Y3K").
  >
  >> I think we need a consensus on this.  I think this may be a problem for
  >> some people.  Comments?
  >
  >Good point.  Perhaps there should be a way to select the default date
  >style at configure or initdb time.  I don't mind if the "default default"
  >is ISO, but if I had apps that were dependent on the old default setting
  >I'd sure be annoyed by this change...
  >
  >Has anyone thought much about the fact that beginning next year,
  >heuristics to guess which field is the year will become nearly useless?
  >Quick, when is '01/02/03'?  I suspect a lot of people who got away with
  >not thinking hard about datestyles will suddenly realize that they need
  >to set the default datestyle to whatever they are accustomed to using.
 
I have code to let the installer choose the default datestyle in Debian's installation script for PostgreSQL.  It makes its own best guess on
the basis of the timezone and then asks the user with its own guess as
the presented default.

See the attached script; I don't know how generalisable the timezone
guessing would be.

  9. Re: [HACKERS] Date/time types: big changeu

    Michael Meskes <meskes@postgresql.org> — 2000-02-17T18:42:33Z

    On Wed, Feb 16, 2000 at 11:20:26PM +0000, Oliver Elphick wrote:
> I have code to let the installer choose the default datestyle in Debian's installation script for PostgreSQL.  It makes its own best guess on
> the basis of the timezone and then asks the user with its own guess as
> the presented default.

Yes, Oliver's script works nicely on all Debian machines.

Michael

-- 
Michael Meskes                         | Go SF 49ers!
Th.-Heuss-Str. 61, D-41812 Erkelenz    | Go Rhein Fire!
Tel.: (+49) 2431/72651                 | Use Debian GNU/Linux!
Email: Michael@Fam-Meskes.De           | Use PostgreSQL!

  10. Re: [HACKERS] Date/time types: big changeu

    Thomas Lockhart <lockhart@alumni.caltech.edu> — 2000-02-17T18:48:11Z

    >   >>> Also, I've changed the default date style to "ISO" (not just in
>   >>> time for Y2K, but we'll be ready for "Y3K").
>   >... Perhaps there should be a way to select the default date
>   >style at configure or initdb time.  I don't mind if the "default default"
>   >is ISO, but if I had apps that were dependent on the old default setting
>   >I'd sure be annoyed by this change...

Well, that is the joy of a major release; not all backward
compatibility is guaranteed. This has been a *documented change* for
at least a year or two; check the chapter on date/time data types for
more info.

However, istm that we could/should have more "default settings"
traveling in the pg_database table. We've got the encoding, which if
set for template1 will be set for every db. We've got the database
location, which can point to an alternate location.

Wouldn't it be reasonable to allow a "default datestyle", or something
more general to help with other defaults? Hmm, could be a text field
which allows something like "PGDATESTYLE='ISO',LANGUAGE='french',..."
so that it is extensible, but maybe that detail is a bad idea because
it is a bit fragile.

What fields would be appropriate for v7.0? The datestyle and timezone
are two obvious candidates, and if we add them now then we could make
use of them later.

Later, we can get things like 

  ALTER DATABASE SET DEFAULT DATESTYLE='ISO';

etc etc.

For v7.1, I'm hoping to work with Tatsuo and others to get closer to
the general character sets and collation sequences allowed by SQL92.
At that point, the MULTIBYTE hardcoded differences in the backend
might go away and we will need these configurable default values.

                    - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California

  11. Re: [HACKERS] Date/time types: big changeu

    Bruce Momjian <pgman@candle.pha.pa.us> — 2000-02-18T04:52:39Z

    > >   >>> Also, I've changed the default date style to "ISO" (not just in
> >   >>> time for Y2K, but we'll be ready for "Y3K").
> >   >... Perhaps there should be a way to select the default date
> >   >style at configure or initdb time.  I don't mind if the "default default"
> >   >is ISO, but if I had apps that were dependent on the old default setting
> >   >I'd sure be annoyed by this change...
> 
> Well, that is the joy of a major release; not all backward
> compatibility is guaranteed. This has been a *documented change* for
> at least a year or two; check the chapter on date/time data types for
> more info.

> 
> However, istm that we could/should have more "default settings"
> traveling in the pg_database table. We've got the encoding, which if
> set for template1 will be set for every db. We've got the database
> location, which can point to an alternate location.

But we have to store this information in the database because it is
related to how the data is stored.  Do the date/time fields also have
that assumption _in_ that stored data?  If so, we need it stored in the
database, if not, it seems some SET command or psql startup file setting
is enough.  Many people work on the same database from different
locations and may need different settings.  I would only store database
settings that relate to the data, not how the data is displayed.  That
stuff belongs outside the database, I think.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

  12. Re: [HACKERS] Date/time types: big changeu

    Peter Eisentraut <e99re41@docs.uu.se> — 2000-02-18T14:29:34Z

    On Thu, 17 Feb 2000, Thomas Lockhart wrote:

> However, istm that we could/should have more "default settings"
> traveling in the pg_database table. We've got the encoding, which if
> set for template1 will be set for every db. We've got the database
> location, which can point to an alternate location.

I don't think this should be a per database setting. Why not use an
environment variable PGDATESTYLE for it. That's easy enough for now.
Before we throw all kinds of per database defaults around, I'd like to see
some sort of a concept where exactly a "database" stands versus "schema",
etc. What happens if one day queries across databases are allowed?

> For v7.1, I'm hoping to work with Tatsuo and others to get closer to
> the general character sets and collation sequences allowed by SQL92.

Excellent.

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

  13. Re: [HACKERS] Date/time types: big changeu

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-02-18T15:35:01Z

    Peter Eisentraut <e99re41@DoCS.UU.SE> writes:
> I don't think this should be a per database setting. Why not use an
> environment variable PGDATESTYLE for it.

We already have that, and I wouldn't have brought up the issue if I
thought it was sufficient.  The case where you really want to be able
to set a default at the database or installation level is when you have
a ton of client apps running on a bunch of different machines, and you
can't or don't want to fix them all at once.  A client-side fix doesn't
get the job done for a dbadmin faced with that kind of situation.

Or were you talking about a server-side env variable?  That could work
I guess, but I thought you were intent on eliminating env-var
dependencies in initdb and postmaster startup ... for good reasons ...

> Before we throw all kinds of per database defaults around, I'd like to see
> some sort of a concept where exactly a "database" stands versus "schema",
> etc. What happens if one day queries across databases are allowed?

Presumably a client doing that would make sure to request the same
datestyle (or whatever) from each database.  You're right though
that we could use some global thinking about what parameters need
to be settable from where, and what their scopes need to be.

			regards, tom lane

  14. Re: [HACKERS] Date/time types: big changeu

    Thomas Lockhart <lockhart@alumni.caltech.edu> — 2000-02-18T15:48:29Z

    > Or were you talking about a server-side env variable?

fwiw, we've already got that one...

                 - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California

  15. Re: [HACKERS] Date/time types: big changeu

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-02-18T15:52:48Z

    Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
>> Or were you talking about a server-side env variable?

> fwiw, we've already got that one...

We do?  (... examines code ...)  By golly, you're right.

OK, I agree with Peter: this is enough to save the day for a dbadmin
who really really doesn't want to switch to default ISO datestyle,
so I withdraw my complaint.

I do, however, suggest that the backend env var needs to be documented
more prominently than it is now.  One might also ask why its set of
allowed values is inconsistent with the SET command's (probably
postgres.c ought to just call a routine in variable.c, rather than
having its own parsing code)?

			regards, tom lane

  16. Re: [HACKERS] Date/time types: big changeu

    Thomas Lockhart <lockhart@alumni.caltech.edu> — 2000-02-18T16:28:37Z

    > I do, however, suggest that the backend env var needs to be documented
> more prominently than it is now.

Hmm, I thought it was in the Admin Guide, but in fact it shows up only
in the Data Types chapter and in the release notes. Should be added to
runtime.sgml just before (?) "Starting postmaster".

> One might also ask why its set of
> allowed values is inconsistent with the SET command's (probably
> postgres.c ought to just call a routine in variable.c, rather than
> having its own parsing code)?

I'm vaguely recalling that there was a "chicken and egg" problem with
the backend firing up... Ah, in fact I think (still from my sometimes
faulty memory) that it had to do with whether the Postgres memory
management stuff (palloc et al) was available at the time postgres.c
needed to make the call.

Feel free to review it though and make sweeping or small changes.

                  - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California

  17. Re: [HACKERS] Date/time types: big changeu

    Peter Eisentraut <peter_e@gmx.net> — 2000-02-19T14:12:44Z

    On 2000-02-18, Tom Lane mentioned:

> Or were you talking about a server-side env variable?  That could work
> I guess, but I thought you were intent on eliminating env-var
> dependencies in initdb and postmaster startup ... for good reasons ...

Yes, as you noticed.

I don't mind postmaster startup environment variables that much. The ones
for initdb were much more evil. This really seems to be an item for the
Grand Unified Configuration File, but until that happens it's easier to
have a dozen of orthogonal environment variables than having to reorganize
this whole thing later on.

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

  18. Re: [HACKERS] Date/time types: big change

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-02-19T22:25:35Z

    Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
>> One might also ask why its set of
>> allowed values is inconsistent with the SET command's (probably
>> postgres.c ought to just call a routine in variable.c, rather than
>> having its own parsing code)?

> I'm vaguely recalling that there was a "chicken and egg" problem with
> the backend firing up... Ah, in fact I think (still from my sometimes
> faulty memory) that it had to do with whether the Postgres memory
> management stuff (palloc et al) was available at the time postgres.c
> needed to make the call.

Yup, your memory is still working...

> Feel free to review it though and make sweeping or small changes.

OK, I tweaked the code in variable.c to not depend on palloc(), and
made the change.  In the course of doing so, I noticed what I assume
is a bug: RESET DateStyle and SET DateStyle = 'DEFAULT' were still
setting to Postgres style.  Presumably they should reset to ISO style
in the brave new world, no?

What I actually did was to make them reset to whatever the backend's
startup setting is.  Thus, if a postmaster PGDATESTYLE environment
variable exists, it will determine the result of RESET DateStyle as
well as the state of a new backend.  (A client-side PGDATESTYLE setting
cannot affect RESET, of course, since it just causes a SET command to
be issued.)  I think this is appropriate behavior, but it might be open
to debate.

BTW, here is an interesting corner case for you: what happens when
the postmaster is started with PGDATESTYLE="DEFAULT"?  You get ISO
now, but I almost committed code that would have gone into infinite
recursion...

			regards, tom lane

  19. Re: last comma inside "CREATE TABLE ()" statements

    Oliver Elphick <olly@lfix.co.uk> — 2001-04-22T18:44:46Z

    Tom Lane wrote:
  >Louis-David Mitterrand <vindex@apartia.ch> writes:
  >> Is it against the SQL standard to accept a trailing comma in a table
  >> declaration?
  >
  >Yes ...
  >
  >> CREATE TABLE "currency" (
  >>     currency_id varchar(3),
  >>     rate float, <-------------------- BOOM! parse error
  >> );
  >
  >> As in perl, it would make life easier to simply ignore/accept a trailing
  >> comma on table declarations.
  >
  >... however, this seems like a reasonable idea that would not introduce
  >any major problems.  I have no objections, if someone wants to submit
  >a grammar patch.
 
I suppose it isn't a major problem, but enforcing strict grammar 
helps to show up inadvertent errors.  Suppose I have a set of schema
building files for a whole system; the way I do things, there may be fifty
or more files, one per table.  If one of these gets corrupted in editing
(perhaps a line gets deleted by mistake) it would be nice to know about it
through a parser error. Of course, an error may be such that the parser
won't detect it, but why remove protection by gratuitously departing from
the standard?

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "If my people, which are called by my name, shall 
      humble themselves, and pray, and seek my face, and 
      turn from their wicked ways; then will I hear from 
      heaven, and will forgive their sin, and will heal 
      their land."    II Chronicles 7:14

  20. Re: last comma inside "CREATE TABLE ()" statements

    Mitch Vincent <mitch@venux.net> — 2001-04-22T18:48:36Z

    > I suppose it isn't a major problem, but enforcing strict grammar
> helps to show up inadvertent errors.  Suppose I have a set of schema
> building files for a whole system; the way I do things, there may be fifty
> or more files, one per table.  If one of these gets corrupted in editing
> (perhaps a line gets deleted by mistake) it would be nice to know about it
> through a parser error. Of course, an error may be such that the parser
> won't detect it, but why remove protection by gratuitously departing from
> the standard?

    I agree -- while it would be a huge problem, it's a matter of following
the rules.. I don't see any reason why we can't expect users to follow the
proper syntax rules.. I missed the first post so I don't know how the person
who posted this was actually putting the comma there -- perhaps there was a
good reason for it..

    Just my $0.02 worth..

-Mitch

  21. Re: last comma inside "CREATE TABLE ()" statements

    GH <grasshacker@over-yonder.net> — 2001-04-22T19:16:02Z

    On Sun, Apr 22, 2001 at 07:44:46PM +0100, some SMTP stream spewed forth: 
> Tom Lane wrote:
>   >Louis-David Mitterrand <vindex@apartia.ch> writes:
>   >> Is it against the SQL standard to accept a trailing comma in a table
>   >> declaration?
>   >
>   >Yes ...
>   >
>   >> CREATE TABLE "currency" (
>   >>     currency_id varchar(3),
>   >>     rate float, <-------------------- BOOM! parse error
>   >> );
>   >
>   >> As in perl, it would make life easier to simply ignore/accept a trailing
>   >> comma on table declarations.
>   >
>   >... however, this seems like a reasonable idea that would not introduce
>   >any major problems.  I have no objections, if someone wants to submit
>   >a grammar patch.

I can think of no place where this would be even remotely useful.
Is it really that difficult to remove a comma?
If it works now for *all* users of PostgreSQL, why should we change it?


gh

>  
> I suppose it isn't a major problem, but enforcing strict grammar 
> helps to show up inadvertent errors.  Suppose I have a set of schema
> building files for a whole system; the way I do things, there may be fifty
> or more files, one per table.  If one of these gets corrupted in editing
> (perhaps a line gets deleted by mistake) it would be nice to know about it
> through a parser error. Of course, an error may be such that the parser
> won't detect it, but why remove protection by gratuitously departing from
> the standard?
> 
> -- 
> Oliver Elphick                                Oliver.Elphick@lfix.co.uk
> Isle of Wight                              http://www.lfix.co.uk/oliver
> PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
> GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
>                  ========================================
>      "If my people, which are called by my name, shall 
>       humble themselves, and pray, and seek my face, and 
>       turn from their wicked ways; then will I hear from 
>       heaven, and will forgive their sin, and will heal 
>       their land."    II Chronicles 7:14 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html

  22. Re: Re: last comma inside "CREATE TABLE ()" statements

    Neil Conway <nconway@klamath.dyndns.org> — 2001-04-22T20:15:24Z

    On Sun, Apr 22, 2001 at 02:48:36PM -0400, Mitch Vincent wrote:
> > I suppose it isn't a major problem, but enforcing strict grammar
> > helps to show up inadvertent errors.  Suppose I have a set of schema
> > building files for a whole system; the way I do things, there may be fifty
> > or more files, one per table.  If one of these gets corrupted in editing
> > (perhaps a line gets deleted by mistake) it would be nice to know about it
> > through a parser error. Of course, an error may be such that the parser
> > won't detect it, but why remove protection by gratuitously departing from
> > the standard?
> 
>     I agree -- while it would be a huge problem, it's a matter of following
> the rules.. I don't see any reason why we can't expect users to follow the
> proper syntax rules.. I missed the first post so I don't know how the person
> who posted this was actually putting the comma there -- perhaps there was a
> good reason for it..

I've run into this several times, and I agree with those who'd like to see
this added. When creating/editing a schema, I usually use this format:

CREATE TABLE t1 (
    foo TEXT,
    bar TEXT
);

Now let's say I wanted to add another column:

CREATE TABLE t2 (
    foo TEXT,
    bar TEXT,
    baz TEXT
);

I would need to edit 2 lines (to add the trailing comma, and then the
next line). The same applies when deleting the last column in a table.
It would be easier, IMHO, to allow this:

CREATE TABLE t1 (
    foo TEXT,
    bar TEXT,
);

So that you can easily add or delete columns without needing to worry
about commas.

As for following standards, I think we should aim to provide the best
RDBMS possible. Being SQL-compliant is valuable, so we should do that.
But in areas where we can extend the standard, or break it in a minor
way a for major gain, I think the benefits outweigh the costs.

And really, how many errors is this going to prevent? AFAICT, it would
only catch the case where you've deleted the last column in a table
accidentally -- it won't catch mistakes anywhere else. This doesn't
seem to be very useful.

Of course, that's just my opinion. I might be wrong ;-)

Cheers,

Neil

-- 
Neil Conway <neilconway@home.com>
Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc
Encrypted mail welcomed

Violence is to dictatorship as propaganda is to democracy.
        -- Noam Chomsky

  23. Re: last comma inside "CREATE TABLE ()" statements

    Louis-David Mitterrand <vindex@apartia.ch> — 2001-04-23T07:06:47Z

    On Sun, Apr 22, 2001 at 07:44:46PM +0100, Oliver Elphick wrote:
> Tom Lane wrote:
>   >Louis-David Mitterrand <vindex@apartia.ch> writes:
>   >> Is it against the SQL standard to accept a trailing comma in a table
>   >> declaration?
>   >
>   >Yes ...
>   >
>   >> CREATE TABLE "currency" (
>   >>     currency_id varchar(3),
>   >>     rate float, <-------------------- BOOM! parse error
>   >> );
>   >
>   >> As in perl, it would make life easier to simply ignore/accept a trailing
>   >> comma on table declarations.
>   >
>   >... however, this seems like a reasonable idea that would not introduce
>   >any major problems.  I have no objections, if someone wants to submit
>   >a grammar patch.
>
> I suppose it isn't a major problem, but enforcing strict grammar
> helps to show up inadvertent errors.  Suppose I have a set of schema
> building files for a whole system; the way I do things, there may be fifty
> or more files, one per table.  If one of these gets corrupted in editing
> (perhaps a line gets deleted by mistake) it would be nice to know about it
> through a parser error. Of course, an error may be such that the parser
> won't detect it, but why remove protection by gratuitously departing from
> the standard?

After giving it a thought, this seems like a reasonable objection. My
comparison with perl (a trailing comma is accepted in list definitions)
isn't entirely valid as perl is a programming language (doh) in which
most time is spent editing.

On the other hand, building a DB schema more planning and reflexion
(hopefully) and less editing. Removing a trailing comma isn't going to
give increase anybody's carpal tunnel syndrome.

[crawls back into hole]

> --
> Oliver Elphick                                Oliver.Elphick@lfix.co.uk
> Isle of Wight                              http://www.lfix.co.uk/oliver
> PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
> GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
>                  ========================================
>      "If my people, which are called by my name, shall
>       humble themselves, and pray, and seek my face, and
>       turn from their wicked ways; then will I hear from
>       heaven, and will forgive their sin, and will heal
>       their land."    II Chronicles 7:14

--
Religion is something left over from the infancy of our intelligence; it
will fade away as we adopt reason and science as our guidelines.  
-- Bertrand Russell