Hi,

    I  just  committed  a  patch that turns on FOREIGN KEY. Thus,
    REFERENCES used in CREATE TABLE now automatically creates the
    appropriate  constraint  triggers.  The  implementation  also
    supports  omitting  the  PK   column   definition,   if   the
    corresponding  columns  should  be  the  PRIMARY  KEY  of the
    referenced table.

    Also I completed some more of the generic trigger procs.  For
    MATCH  FULL,  the  key  existence check in PK table and these
    actions are completed:

        ON DELETE RESTRICT
        ON DELETE CASCADE
        ON UPDATE RESTRICT
        ON UPDATE CASCADE

    Still missing are the SET NULL and SET DEFAULT  actions.  The
    former  is easy and will follow soon, the latter looks tricky
    if the  implementation  should  support  ALTER  TABLE/DEFAULT
    (what it IMHO must even if that ALTER TABLE isn't implemented
    yet).

    Anyway, I ran into some  shift/reduce  problem  in  the  main
    parser. The syntax according to SQL3 says

        <constraint attributes> ::=
              <constraint check time> [ [ NOT ] DEFERRABLE ]
            | [ NOT ] DEFERRABLE [ <constraint check time> ]

    <constraint  check time> defines INITIALLY DEFERRED/IMMEDIATE
    and defaults to IMMEDIATE.

    If I allow the <constraint attributes> in column constraints,
    I  get  2 shift/reduce conflicts. Seems the syntax interferes
    with NOT NULL.  Actually I commented that part  out,  so  the
    complete  syntax is available only for table constraints, not
    on the column level.

    Could some yacc-guru please take a look at it?

    Another interesting  question  is  about  inheritance.  If  a
    REFERENCES constraint exists for a table, must another table,
    inheriting this one, also get all the FK checks applied?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #



Mon, 6 Dec 1999 19:20:10 +0100 (MET)
wieck@debis.com (Jan Wieck)
FOREIGN KEY and shift/reduce
I have the following table and view:


create table users (
        user_id                 integer not null primary key,
        first_names             varchar(50) not null,
        last_name               varchar(50) not null,
        password                varchar(30) not null,
        email                   varchar(50) not null unique,

        census_rights_p         boolean default 'f',
        locale_rights_p         boolean default 'f',
        admin_rights_p          boolean default 'f',

        -- to suppress email alerts
        on_vacation_until       date,

        -- set when user reappears at site
        last_visit              datetime,
        -- this is what most pages query against (since the above column
        -- will only be a few minutes old for most pages in a session)
        second_to_last_visit    datetime,

        registration_date       date,
        registration_ip         varchar(50),
        user_state              varchar(100) check(user_state is null or
user_state in ('need_email_verification_and_admin_approv', 'n
eed_admin_approv', 'need_email_verification', 'rejected', 'authorized',
'banned', 'deleted')
),
        deleted_p               boolean default 'f',
        banned_p                boolean default 'f',
        -- who and why this person was banned
        banning_user            integer,
        banning_note            varchar(4000),
        portrait_loaded         boolean default 'f',
        portrait_type           varchar(10) default ''
);

-- Create an "alert table" view of just those users who should
-- be sent e-mail alerts.

create view users_alertable
as
select *
 from users
 where (on_vacation_until is null or
        on_vacation_until < 'now'::date)
 and (deleted_p = 'f');

This has been working for months, just fine.  I've been porting over a bunch
more stuff from Oracle to this Postgres-based system, and bam!  Now any
select from the view dies with:

unknown node tag 600 in apply_RIR_view

I've tried dropping and rebuilding the table and view in a test database
and the problem remains.  I recall running into problems with other
operations many moons ago, where a particular node type wasn't being
handled by a particular operator (the ones I'd seen previously were
fixed by the excellent 6.5.* versions).  

Is this a similar case?  I may do a little digging myself tonight, but
thought I'd ask to see if this rings a bell with anyone.  It's a bit
strange because this view's been working great on this table for so
long.  I added a couple of extra columns to the table recently but
the view worked immediately afterwards.  The stuff I've been porting
creates views willy-nilly and it's almost like there's an interaction
taking place, but that doesn't seem right.

It fails in the same manner if I simply declare the view as:

create view users_alertable as select * from users;




- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Mon, 06 Dec 1999 11:24:43 -0800
Don Baccus <dhogaza@pacifier.com>
A view just stopped working out of the blue...
Don Baccus wrote:

> This has been working for months, just fine.  I've been porting over a bunch
> more stuff from Oracle to this Postgres-based system, and bam!  Now any
> select from the view dies with:
>
> unknown node tag 600 in apply_RIR_view

    Node tag 600 is T_Query.

> I've tried dropping and rebuilding the table and view in a test database
> and the problem remains.  I recall running into problems with other
> operations many moons ago, where a particular node type wasn't being
> handled by a particular operator (the ones I'd seen previously were
> fixed by the excellent 6.5.* versions).
>
> Is this a similar case?  I may do a little digging myself tonight, but
> thought I'd ask to see if this rings a bell with anyone.  It's a bit
> strange because this view's been working great on this table for so
> long.  I added a couple of extra columns to the table recently but
> the view worked immediately afterwards.  The stuff I've been porting
> creates views willy-nilly and it's almost like there's an interaction
> taking place, but that doesn't seem right.
>
> It fails in the same manner if I simply declare the view as:
>
> create view users_alertable as select * from users;

    I  assume  the column with the IN constraint is either new or
    changed recently. Seems the system generates  some  subselect
    for that and the rewriter is unable to handle this case.

    I  don't have the time to tackle that, just some hint to push
    you into the right direction.

    Be careful if  hacking  inside  the  rewriter,  it's  a  very
    sensitive area!


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #



Mon, 6 Dec 1999 20:34:23 +0100 (MET)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] A view just stopped working out of the blue...

On Mon, 6 Dec 1999, Jan Wieck wrote:

> Hi,
> 
>     Another interesting  question  is  about  inheritance.  If  a
>     REFERENCES constraint exists for a table, must another table,
>     inheriting this one, also get all the FK checks applied?

Hi,

This inspire me for next question: What is in PosgreSQL inherited? 

IMHO is problem make inheriting FOREIGN KEY if not is support for UNIQUE 
or PRIMARY KEYs inheriting. (Or is it in CVS tree?).

PostgreSQL 6.5.3:

test=> create table aaa (x int4 UNIQUE);
NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'aaa_x_key' for
table 'aaa'
CREATE
test=> create table y () inherits (aaa);
CREATE
test=> insert into y values (1);
INSERT 590807 1
test=> insert into y values (1);
INSERT 590808 1

							Karel

PS. I very look forward to a FOREIGN KEY, with this feature will 
    life easier and the PgSQL will more tread on the heels of non-GNU
    engines. 


Mon, 6 Dec 1999 21:59:54 +0100 (CET)
Karel Zak - Zakkr <zakkr@zf.jcu.cz>
Re: [HACKERS] FOREIGN KEY and shift/reduce
> -----Original Message-----
> From: owner-pgsql-hackers@postgreSQL.org
> [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Jan Wieck
> 
> Hi,
> 
>     I  just  committed  a  patch that turns on FOREIGN KEY. Thus,
>     REFERENCES used in CREATE TABLE now automatically creates the
>     appropriate  constraint  triggers.  The  implementation  also
>     supports  omitting  the  PK   column   definition,   if   the
>     corresponding  columns  should  be  the  PRIMARY  KEY  of the
>     referenced table.
> 
>     Also I completed some more of the generic trigger procs.  For
>     MATCH  FULL,  the  key  existence check in PK table and these
>     actions are completed:
> 
>         ON DELETE RESTRICT
>         ON DELETE CASCADE
>         ON UPDATE RESTRICT
>         ON UPDATE CASCADE
>

Nice.
I tried a little.

< session 1 >
=> create table ri1 (id int4 primary key);
	NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit
	index 'ri1_pkey' for table 'ri1'
	CREATE
=> insert into ri1 values (1);
	INSERT 92940 1
=>create table ri2 (id int4 references ri1 match full on delete restrict); 
	NOTICE:  CREATE TABLE will create implicit trigger(s) for
	FOREIGN KEY check(s)
	CREATE
=> begin;
	BEGIN
=> delete from ri1 where id=1;
	DELETE 1 

< session 2 >
=> insert into ri2 values (1);
	INSERT 92960 1

< session 1 >
=> commit;
	END
=> select * from ri1;
	id
	--
	(0 rows)
=> select * from ri2;
	id
	--
	 1
	(1 row)

Is this a temporary behavior ?

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

Tue, 7 Dec 1999 13:03:28 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
RE: [HACKERS] FOREIGN KEY and shift/reduce
Hiroshi Inoue wrote:

> Nice.
> I tried a little.
>
> < session 1 >
> => create table ri1 (id int4 primary key);
>    NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit
>    index 'ri1_pkey' for table 'ri1'
>    CREATE
> => insert into ri1 values (1);
>    INSERT 92940 1
> =>create table ri2 (id int4 references ri1 match full on delete restrict);
>    NOTICE:  CREATE TABLE will create implicit trigger(s) for
>    FOREIGN KEY check(s)
>    CREATE
> => begin;
>    BEGIN
> => delete from ri1 where id=1;
>    DELETE 1
>
> < session 2 >
> => insert into ri2 values (1);
>    INSERT 92960 1

Outch,

    I see the shared visibility conflict. So the CHECK constraint
    trigger must get an exclusive  lock  somehow  -  I  think  an
    internal "FOR UPDATE OF" can do it - will try.


Thanks, Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #



Tue, 7 Dec 1999 05:33:14 +0100 (MET)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] FOREIGN KEY and shift/reduce
>     If I allow the <constraint attributes> in column constraints,
>     I  get  2 shift/reduce conflicts. Seems the syntax interferes
>     with NOT NULL.  Actually I commented that part  out,  so  the
>     complete  syntax is available only for table constraints, not
>     on the column level.
>     Could some yacc-guru please take a look at it?

Guru I'm not, but I should be able to track it down if it isn't fixed
when I *finally* get time to finish the join syntax. Maybe a few
weeks...

                    - Thomas

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

Tue, 07 Dec 1999 05:16:06 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] FOREIGN KEY and shift/reduce
Hiroshi Inoue wrote:

> Nice.
> I tried a little.
>
> [...]
> => begin;
>    BEGIN
> => delete from ri1 where id=1;
>    DELETE 1
>
> < session 2 >
> => insert into ri2 values (1);
>    INSERT 92960 1
>
> < session 1 >
> => commit;
>    END
> => select * from ri1;
>    id
>    --
>    (0 rows)
> => select * from ri2;
>    id
>    --
>     1
>    (1 row)
>
> Is this a temporary behavior ?

    Fixed.

    Session 2 waits now until session 1 ends transaction.

    I'm thinking about another enhancement to the regression test
    now.  Something where at least two sessions can  run  queries
    in  a  predefined  order. Otherwise, something like the above
    cannot be checked during regression.

    I'm not sure how that can be done with a standard shell,  and
    that's  a  must.   Maybe something using named pipes and so -
    will play around a little.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #



Tue, 7 Dec 1999 16:42:53 +0100 (MET)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] FOREIGN KEY and shift/reduce
wieck@debis.com (Jan Wieck) writes:
>     I'm thinking about another enhancement to the regression test
>     now.  Something where at least two sessions can  run  queries
>     in  a  predefined  order. Otherwise, something like the above
>     cannot be checked during regression.

Yes, we could really use something like that.

>     I'm not sure how that can be done with a standard shell,  and
>     that's  a  must.   Maybe something using named pipes and so -
>     will play around a little.

Would probably be more portable to write a little bit of C code that
opens several libpq connections and reads a script file with interleaved
commands to send to the different connections.  OTOH, by the time you
got done, it might have much of psql in it (certainly at least the
display code).

Peter, in your slicing and dicing of psql, did you end up with anything
that might make this a feasible approach?

			regards, tom lane

Tue, 07 Dec 1999 12:52:54 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Parallel regress tests (was Re: FOREIGN KEY and shift/reduce)
On Tue, 7 Dec 1999, Tom Lane wrote:

> wieck@debis.com (Jan Wieck) writes:
> >     I'm thinking about another enhancement to the regression test
> >     now.  Something where at least two sessions can  run  queries
> >     in  a  predefined  order. Otherwise, something like the above
> >     cannot be checked during regression.

> Peter, in your slicing and dicing of psql, did you end up with anything
> that might make this a feasible approach?

Um, you could call another psql from within psql, like so:

/* psql script */
create this
select that
\! psql -f 'second-script'
select more

That satisfies the requirement of two separate sessions and a predefined
order. I haven't actually tried it, but if it happens to not work as
desired, it could certainly be fixed.

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



Tue, 7 Dec 1999 19:09:50 +0100 (MET)
Peter Eisentraut <e99re41@DoCS.UU.SE>
Re: [HACKERS] Parallel regress tests (was Re: FOREIGN KEY and
	shift/reduce)
Peter Eisentraut <e99re41@DoCS.UU.SE> writes:
>> Peter, in your slicing and dicing of psql, did you end up with anything
>> that might make this a feasible approach?

> Um, you could call another psql from within psql, like so:

> /* psql script */
> create this
> select that
> \! psql -f 'second-script'
> select more

> That satisfies the requirement of two separate sessions and a predefined
> order.

I assume that the \! command won't continue until the subjob exits?
If so, this doesn't give us any way to verify that query A will wait for
query B to finish ... at least not without locking up the test...

Another possible approach is to accept that a parallel multi-backend
test lashup *doesn't* have to run on every single system that Postgres
runs on, if we keep it separate from the standard regress tests.
For example, it's not much work to build a parallel test driver in Perl
(I have done it), and I think that an auxiliary test package that
requires Perl would be acceptable.

			regards, tom lane

Tue, 07 Dec 1999 13:21:07 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Parallel regress tests (was Re: FOREIGN KEY and
	shift/reduce)
On Tue, 7 Dec 1999, Tom Lane wrote:

> 
> > /* psql script */
> > create this
> > select that
> > \! psql -f 'second-script'
> > select more
> 
> > That satisfies the requirement of two separate sessions and a predefined
> > order.
> 
> I assume that the \! command won't continue until the subjob exits?
> If so, this doesn't give us any way to verify that query A will wait for
> query B to finish ... at least not without locking up the test...

\! psql -f 'second-script' &

will do it.  You may wanna redirect your output.

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH   email: vev@michvhf.com   flame-mail: /dev/null
  # include <std/disclaimers.h>       Have you seen http://www.pop4.net?
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================




Tue, 7 Dec 1999 13:37:50 -0500 (EST)
Vince Vielhaber <vev@michvhf.com>
Re: [HACKERS] Parallel regress tests (was Re: FOREIGN KEY and
	shift/reduce)
On Tue, 7 Dec 1999, Tom Lane wrote:

> > Um, you could call another psql from within psql, like so:
> 
> > /* psql script */
> > create this
> > select that
> > \! psql -f 'second-script'
> > select more
> 
> > That satisfies the requirement of two separate sessions and a predefined
> > order.
> 
> I assume that the \! command won't continue until the subjob exits?
> If so, this doesn't give us any way to verify that query A will wait for
> query B to finish ... at least not without locking up the test...

I'm kind of losing you here. You want parallel execution *and* a
predefined order of execution? In my (limited) book, those contradict each
other a little bit. If it helps you can also do this:
\! psql -f 'second-script' >& output &
and the thus invoked script will happily continue executing even after you
quit the first psql.

I guess you could also do some simple synchronization things, like have
the second psql wait on a file to spring into existence:
/* second-script */
\! while [ ! -f /tmp/lock.file ]; do ;; done
\! rm /tmp/lock.file

Kind of like a simple semaphore. Isn't that what you are getting at?

In the overall view of things it almost seems like these kind of tests
need a human eye supervising them, because how do really determine "query
A waits for query B to finish" otherwise?

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


Tue, 7 Dec 1999 19:40:49 +0100 (MET)
Peter Eisentraut <e99re41@DoCS.UU.SE>
Re: [HACKERS] Parallel regress tests (was Re: FOREIGN KEY and
	shift/reduce)
Peter Eisentraut wrote:

> On Tue, 7 Dec 1999, Tom Lane wrote:
>
> > I assume that the \! command won't continue until the subjob exits?
> > If so, this doesn't give us any way to verify that query A will wait for
> > query B to finish ... at least not without locking up the test...
>
> I'm kind of losing you here. You want parallel execution *and* a
> predefined order of execution? In my (limited) book, those contradict each
> other a little bit. If it helps you can also do this:
> \! psql -f 'second-script' >& output &
> and the thus invoked script will happily continue executing even after you
> quit the first psql.

    Yes,  we  mean  controlled order of execution across multiple
    backends. And a script invoked in background, like the above,
    doesn't  do  the trick - and BTW we already have that kind of
    testing.

> I guess you could also do some simple synchronization things, like have
> the second psql wait on a file to spring into existence:
> /* second-script */
> \! while [ ! -f /tmp/lock.file ]; do ;; done
> \! rm /tmp/lock.file
>
> Kind of like a simple semaphore. Isn't that what you are getting at?

    Kind of, but wasting CPU while waiting. OTOH,  some  sleep(1)
    inside the loop would slow down to a certain degree.

    As  usual,  Tom and I had similar ideas. A little amount of C
    code should do it. Only  that  I  would  like  to  use  pipes
    to/from  psql  instead  of  dealing with libpq and formatting
    myself.

    One problem I noticed so  far  is,  that  the  new  psql  (in
    contrast  to  the  v6.5  one)  doesn't flush it's output when
    doing it to a pipe.  To control  that  one  backend  finished
    execution  of  a  query,  I thought to send a special comment
    line and wait for it to be echoed back. This would only  work
    if psql flushes.

    Peter,  could  you  please  add  some  switch  to  psql  that
    activates an fflush() whenever psql would like to  read  more
    input  commands.  In  the  meantime  I can start to write the
    driver using the v6.5 psql.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #



Tue, 7 Dec 1999 19:59:15 +0100 (MET)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] Parallel regress tests (was Re: FOREIGN KEY and
On Tue, 7 Dec 1999, Jan Wieck wrote:

> > I guess you could also do some simple synchronization things, like have
> > the second psql wait on a file to spring into existence:
> > /* second-script */
> > \! while [ ! -f /tmp/lock.file ]; do ;; done
> > \! rm /tmp/lock.file
> >
> > Kind of like a simple semaphore. Isn't that what you are getting at?
> 
>     Kind of, but wasting CPU while waiting. OTOH,  some  sleep(1)
>     inside the loop would slow down to a certain degree.

Well, we're testing and not benchmarking...

>     code should do it. Only  that  I  would  like  to  use  pipes
>     to/from  psql  instead  of  dealing with libpq and formatting
>     myself.

If you like, the print routines in psql are competely isolated from the
rest of the code and you can just #include and use them.

>     Peter,  could  you  please  add  some  switch  to  psql  that
>     activates an fflush() whenever psql would like to  read  more
>     input  commands.  In  the  meantime  I can start to write the
>     driver using the v6.5 psql.

I'm going to go an a flush hunt...

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



Tue, 7 Dec 1999 22:56:51 +0100 (MET)
Peter Eisentraut <e99re41@DoCS.UU.SE>
Re: [HACKERS] Parallel regress tests (was Re: FOREIGN KEY and
Peter Eisentraut <e99re41@DoCS.UU.SE> writes:
>> Peter,  could  you  please  add  some  switch  to  psql  that
>> activates an fflush() whenever psql would like to  read  more
>> input  commands.  In  the  meantime  I can start to write the
>> driver using the v6.5 psql.

> I'm going to go an a flush hunt...

Offhand I see no reason why psql shouldn't *always* fflush its output
before reading a new command.  It'd waste a few microseconds per command
when reading from a file, but that'd hardly be noticeable in the great
scheme of things.  Adding another switch is just offering another way
for people to make a mistake...

			regards, tom lane

Tue, 07 Dec 1999 18:04:51 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Parallel regress tests (was Re: FOREIGN KEY and 
> > ... not sure how that can be done with a standard shell,  and
> > that's  a  must.   Maybe something using named pipes and so -
> > will play around a little.
> Would probably be more portable to write a little bit of C code that
> opens several libpq connections and reads a script file with 
> interleaved commands to send to the different connections.

There are tools I'd consider using for this such as tcl/expect which
help with simulating interactive sessions and with coordinating
multiple actions. Forget the "sh only" approach if it makes things
difficult; some platforms ship with a much richer toolset, and these
tools might be well suited to your problem.

If it works and there is a big demand for the "sh tools" then someone
can recode it later, and it isn't holding you up now.

... my 2 cents...

                      - Thomas

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

Wed, 08 Dec 1999 15:57:19 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] Parallel regress tests (was Re: FOREIGN KEY and
	shift/reduce)
> I guess you could also do some simple synchronization things, like have
> the second psql wait on a file to spring into existence:
> /* second-script */
> \! while [ ! -f /tmp/lock.file ]; do ;; done
> \! rm /tmp/lock.file
> Kind of like a simple semaphore. Isn't that what you are getting at?

LISTEN/NOTIFY might be useful for this...

                  - Thomas

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

Wed, 08 Dec 1999 15:59:30 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] Parallel regress tests (was Re: FOREIGN KEY
	andshift/reduce)
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
>> Kind of like a simple semaphore. Isn't that what you are getting at?

> LISTEN/NOTIFY might be useful for this...

LISTEN/NOTIFY would be one of the things we were trying to *test*.
It's not helpful for checking intra-transaction-block behavior anyway,
since notifys are only sent at commit and only heard between
transactions.

I like the tcl/expect idea a lot.  We'd have to upgrade our tcl
interface to support asynchronous queries (ie, send query then do other
stuff until answer arrives); AFAIR it can't handle that now.  But that'd
be well worth doing anyway...

			regards, tom lane

Wed, 08 Dec 1999 11:02:32 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Parallel regress tests (was Re: FOREIGN KEY
	andshift/reduce)
Tom Lane wrote:

> Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> >> Kind of like a simple semaphore. Isn't that what you are getting at?
>
> > LISTEN/NOTIFY might be useful for this...
>
> LISTEN/NOTIFY would be one of the things we were trying to *test*.
> It's not helpful for checking intra-transaction-block behavior anyway,
> since notifys are only sent at commit and only heard between
> transactions.
>
> I like the tcl/expect idea a lot.  We'd have to upgrade our tcl
> interface to support asynchronous queries (ie, send query then do other
> stuff until answer arrives); AFAIR it can't handle that now.  But that'd
> be well worth doing anyway...

    That  would  be  really  nice,  especially  because Tcl is my
    preferred tool language. But I wouldn't use libpgtcl, instead
    it  should  control  a  number  of psql sessions over command
    pipelines. These  can  already  handle  asynchronous  actions
    across all supported platforms.

    I'll  build the first version of the tool using standard Tcl,
    and just keep in mind  that  the  command  language  must  be
    easily  implementable with lex/yacc so someone can convert it
    later into a C version.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #



Wed, 8 Dec 1999 17:51:42 +0100 (MET)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] Parallel regress tests (was Re: FOREIGN KEY
	andshift/reduce)
Tom Lane wrote:

> I like the tcl/expect idea a lot.  We'd have to upgrade our tcl
> interface to support asynchronous queries (ie, send query then do other
> stuff until answer arrives); AFAIR it can't handle that now.  But that'd
> be well worth doing anyway...

O.K.

    The  multi session test tool, written in Tcl, is ready. Where
    should it go and how should it be named? I think it  wouldn't
    be a bad idea to have it in a place where it can be used from
    any location, instead of putting it into the regression  dir.
    Maybe install it into bin?

    Just to show a little:

    # ----
    # Multi session test suite
    # ----

    # ----
    # Session 1 starts a transaction and deletes all rows from t1
    # ----
    / connect A
    begin;
    select * from t1;
    delete from t1;
    / wait A

    # ----
    # Session 2 tries to select all from t1 for update - should block
    # ----
    / connect B
    begin;
    select * from t1 for update of t1;
    commit;
    / wait B

    # ----
    # Now session 1 rolls back ...
    # ----
    / use A
    rollback;
    / wait A

    # ----
    # ... what must release session 2
    # ----
    / wait B

    / close A
    / close B

    The above input produces this output:

    # ----
    # Multi session test suite
    # ----
    # ----
    # Session 1 starts a transaction and deletes all rows from t1
    # ----
    (A) QUERY: begin;
    (A) QUERY: select * from t1;
    (A) a1|b1|c1
    (A) --+--+-----
    (A)  2| 2|key 2
    (A)  3| 3|key 3
    (A)  8| 8|key 7
    (A) (3 rows)
    (A)
    (A) QUERY: delete from t1;
    # ----
    # Session 2 tries to select all from t1 for update - should block
    # ----
    (B) QUERY: begin;
    (B) QUERY: select * from t1 for update of t1;
    *** Session of connection B seems locked
    # ----
    # Now session 1 rolls back ...
    # ----
    (A) QUERY: rollback;
    # ----
    # ... what must release session 2
    # ----
    (B) a1|b1|c1
    (B) --+--+-----
    (B)  2| 2|key 2
    (B)  3| 3|key 3
    (B)  8| 8|key 7
    (B) (3 rows)
    (B)
    (B) QUERY: commit;

    The default time for the "wait" command is 5 seconds, but can
    be specified explicitly as "wait A 10". Session names can  of
    course be longer than one character.

    The  script stopped at session B's SELECT ... FOR UPDATE, and
    correctly continued with the *** message 5 seconds later.  As
    you  might have noticed, it doesn't matter that the COMMIT of
    session 2 was  placed  directly  after  the  SELECT.  If  the
    session hangs, all queries are internally buffered.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #



Wed, 8 Dec 1999 20:45:20 +0100 (MET)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] Parallel regress tests (was Re: FOREIGN KEY
	andshift/reduce)
wieck@debis.com (Jan Wieck) writes:
>    The  multi session test tool, written in Tcl, is ready.

Looks way cool.

>    Where should it go and how should it be named?

Why not throw it in as another src/bin/ subdirectory, or maybe put it
in Peter's new "src/bin/scripts/" directory?  No great ideas for
a name here.

>     The default time for the "wait" command is 5 seconds, but can
>     be specified explicitly as "wait A 10".

It makes me uncomfortable that there are any explicit times at all.
A developer might set up a test script with delays that seem ample
on his hardware, yet will fail when someone tries to use the script
on a much slower and/or heavily loaded system.

Can we find a way to avoid needing explicit times in the scripts?
If not, there should probably be a command-line switch that allows all
the times to be scaled by some amount.  (Ugly, but could be really
handy.)

			regards, tom lane

Wed, 08 Dec 1999 20:46:50 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Parallel regress tests (was Re: FOREIGN KEY
	andshift/reduce)
Tom Lane wrote:

> wieck@debis.com (Jan Wieck) writes:
> >    The  multi session test tool, written in Tcl, is ready.
>
> Looks way cool.

    Did I ever say that I love Tcl :-) ?

    It  took me less that 5 hours to create a full featured tool,
    that already discovered another bug (not yet fixed) in the RI
    procs,  close to that Hiroshi reported (just do the violation
    the other way round and it will be accepted).

    I know, that there are similar powerful  languages  availabel
    (perl for one).  It's just that I looked for a good scripting
    language some years ago and found Tcl (version  7.4  at  that
    time).  Today  it  is such a magic tool for someone, familiar
    with the C language, that I think it  was  one  of  the  best
    coices  I  ever  made.  Tcl  was  the  first  language I ever
    embedded as a PL, and  the  difficulties  reported  (and  yet
    missing results) on approaches to embedd other languages tell
    the entire story.

> >    Where should it go and how should it be named?
>
> Why not throw it in as another src/bin/ subdirectory, or maybe put it
> in Peter's new "src/bin/scripts/" directory?  No great ideas for
> a name here.

    Exactly what I intended to do.

    I prefer src/bin, since it isn't a shell script. I think that
    "pgmultitest" (my internal development name) wouldn't be such
    a bad decision.

> >     The default time for the "wait" command is 5 seconds, but can
> >     be specified explicitly as "wait A 10".
>
> It makes me uncomfortable that there are any explicit times at all.
> A developer might set up a test script with delays that seem ample
> on his hardware, yet will fail when someone tries to use the script
> on a much slower and/or heavily loaded system.
>
> Can we find a way to avoid needing explicit times in the scripts?
> If not, there should probably be a command-line switch that allows all
> the times to be scaled by some amount.  (Ugly, but could be really
> handy.)

    Once again, similar thoughts and feelings.

    The third parameter should indeed be a value used  internally
    to  ESTIMATE  the real time require, to tell that the session
    is blocked in a lock operation. And the estimation should  be
    based on some prior analyzed system performance.

    Load  peak's might still confuse the estimation. I don't have
    any clue right now, how to estimate, but anyway,  load  peaks
    lead  to  estimator  confusions when it comes to estimate the
    execution time of some operation.

    I don't think, that a  few  (1-2)  seconds  of  delay  at  an
    expected  lock would really hurt.  Well, a test stressing the
    locking mechanism (like the RI test I want  to  have),  might
    take  a  while,  even if executed on high end hardware. But I
    cannot imagine any other way, than  using  multiple  sessions
    and  response  timeouts,  to  detect  from the outside that a
    query ended in a blocking lock request.

    Except we extend the entire FE/BE protocol with  information,
    telling   "I'm   blocked"   /  "I'm  resuming"  (plus  adding
    appropriate messages to psql), there is absolutely no way  to
    avoid the above timeouts. And we don't want to add regression
    test requirements to the FE/BE protocol and psql  -  no?  The
    already  discussed  flushing feature is a requirement, but it
    might be useful  in  other  situations  too  and  thus  worth
    anyway.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #



Thu, 9 Dec 1999 04:57:00 +0100 (MET)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] Parallel regress tests (was Re: FOREIGN KEY
	andshift/reduce)
At 04:57 AM 12/9/99 +0100, Jan Wieck wrote:

>    I know, that there are similar powerful  languages  availabel
>    (perl for one).  It's just that I looked for a good scripting
>    language some years ago and found Tcl (version  7.4  at  that
>    time).  Today  it  is such a magic tool for someone, familiar
>    with the C language, that I think it  was  one  of  the  best
>    coices  I  ever  made. 

And this is the scripting language embedded in the NaviSoft, later
AOLserver web server.  Your comments pretty much sum things up.

There's an official port of the ArsDigita Community System to postgres
(from Oracle) underway.

This is in some sense the wrong forum to mention such things, but in
another sense it is the right forum, because mainstream database server
companies are pouring everything they've got into "web-ifying" their
tools.

But, really, with a lightweight threaded server like AOLserver and an
excellent Tcl API, middleware and such is really not necessary.  We think
the port's going to be excellent, though I'm steadily building up a list
of Postgres bugs to report (I'll wait until I have a dozen or so).  One
symptom of PotgreSQL's stability in 6.5.* is that the bugs are of the
reproducible, mostly language-oriented kind and because of this they're
easy to isolate and work-around (not always the truth in a multi-threaded
environment such as typifies AOLserver).

The enthusiasm for this in-progress port's pretty surprising, given that
we only announced it four-five days ago and even then more or less under
duress.

PostgreSQL, though, has the potential to be an ideal web db server for
small-to-mid-range sites.  Given that Oracle wants like $22,500 for a
fully paid up license for a single-processor P500, you can understand
the interest for integrating these tools, which have been used to build
a number of high-profile sites.



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Wed, 08 Dec 1999 20:30:30 -0800
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] Parallel regress tests (was Re: FOREIGN KEY
	andshift/reduce)
[cc: list trimmed, and subject changed....]

On Wed, 08 Dec 1999, Don Baccus wrote:
> At 04:57 AM 12/9/99 +0100, Jan Wieck wrote:
> 
> >    I know, that there are similar powerful  languages  availabel
> >    (perl for one).  It's just that I looked for a good scripting
> >    language some years ago and found Tcl (version  7.4  at  that
> >    time).  Today  it  is such a magic tool for someone, familiar
> >    with the C language, that I think it  was  one  of  the  best
> >    coices  I  ever  made. 
> 
> And this is the scripting language embedded in the NaviSoft, later
> AOLserver web server.  Your comments pretty much sum things up.

I replied to Jan privately about that, but, since you brought it up on-list....

> This is in some sense the wrong forum to mention such things, but in
> another sense it is the right forum, because mainstream database server
> companies are pouring everything they've got into "web-ifying" their
> tools.

Vince almost finished the switch, but got tangled up in the high latency of the
AOLserver developers for 3.0beta3.  His comments about the ease of setup were
on the money, but he wanted to use CGI, and CGI is far from AOLserver's strong
suite.  The tcl API is the preferred way to do AOLserver hacking.

> symptom of PotgreSQL's stability in 6.5.* is that the bugs are of the
> reproducible, mostly language-oriented kind and because of this they're
> easy to isolate and work-around (not always the truth in a multi-threaded
> environment such as typifies AOLserver).

As opposed to PostgreSQL 6.2.1, the first version that was officially supported
by the AOLserver crew.  PostgreSQL has come a long way!

> PostgreSQL, though, has the potential to be an ideal web db server for
> small-to-mid-range sites. 

The use of AOLserver+Tcl+PostgreSQL has the potential to far surpass the speed
of Apache+mod_perl+mySQL -- or Apache+mod_php+mySQL.

To quote Philip Greenspun, AOLserver is now handling 28 thousand hits at AOL. 
Oh, that's 28K hits _per_second_ aggregate over AOL's server farm.

IMO,. AOLserver is far and away the best web front end for PostgreSQL. And
that's my final answer.

--
Lamar Owen
WGCR Internet Radio

Wed, 8 Dec 1999 23:47:08 -0500
Lamar Owen <lamar.owen@wgcr.org>
PostgreSQL front ends (was Re: [HACKERS] Parallel regress tests (was
	Re: FOREIGN KEY andshift/reduce))
Don Baccus <dhogaza@pacifier.com> writes:
> the port's going to be excellent, though I'm steadily building up a list
> of Postgres bugs to report (I'll wait until I have a dozen or so).

Actually, I think it's easier to keep track of things if you file bug
reports as separate messages, rather than one big message that lists a
bunch of unrelated problems.  So feel free to send 'em in as you find
'em.

			regards, tom lane

Thu, 09 Dec 1999 01:30:19 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Parallel regress tests (was Re: FOREIGN KEY
	andshift/reduce)
At 11:47 PM 12/8/99 -0500, Lamar Owen wrote:

>Vince almost finished the switch, but got tangled up in the high latency
of the
>AOLserver developers for 3.0beta3.  His comments about the ease of setup were
>on the money, but he wanted to use CGI, and CGI is far from AOLserver's
strong
>suite.  The tcl API is the preferred way to do AOLserver hacking.

The PHP folks have claimed that they'll have an embedded interpreter
for AOLserver for their next release.  It's not clear that they'll
duplicate the rich API the server provides Tcl though.  If they do,
this will be an alternative for those who don't like Tcl or AOLserver's
ADPs (HTML with embedded Tcl snippets)


- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Thu, 09 Dec 1999 07:44:30 -0800
Don Baccus <dhogaza@pacifier.com>
Re: PostgreSQL front ends (was Re: [HACKERS] Parallel regress
	tests (was Re: FOREIGN KEY andshift/reduce))
As I've mentioned previously, I'm porting over a web toolkit
from Oracle to Postgres.

One portion of the toolkit creates and alters tables to add
user-defined fields into what's being used essentially as a
meta-table (used to later define real tables).  One page
forgets to check for an empty form before issuing its
"alter table" command, and it crashed the backend.  I'm
correcting its forgetfulness, but am also reporting the
problem:

alter table foo add;

gives a parser error, which is fine.

alter table foo add();

crashes the backend.

I'd say it's really low priority, but should be fixed.




- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Thu, 09 Dec 1999 07:54:31 -0800
Don Baccus <dhogaza@pacifier.com>
alter table crashes back end 
On 1999-12-08, Jan Wieck mentioned:

>     The  multi session test tool, written in Tcl, is ready. Where
>     should it go and how should it be named? I think it  wouldn't
>     be a bad idea to have it in a place where it can be used from
>     any location, instead of putting it into the regression  dir.
>     Maybe install it into bin?

Please consider not doing that. Bin is for user programs. I don't see why
it shouldn't at least go under the test tree, if not under regress.

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



Fri, 10 Dec 1999 02:28:29 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: [HACKERS] Parallel regress tests (was Re: FOREIGN KEY
	andshift/reduce)
Peter Eisentraut wrote:

> On 1999-12-08, Jan Wieck mentioned:
>
> >     The  multi session test tool, written in Tcl, is ready. Where
> >     should it go and how should it be named? I think it  wouldn't
> >     be a bad idea to have it in a place where it can be used from
> >     any location, instead of putting it into the regression  dir.
> >     Maybe install it into bin?
>
> Please consider not doing that. Bin is for user programs. I don't see w=
> hy
> it shouldn't at least go under the test tree, if not under regress.

You're right,

    I'll  place it into the regression tree. Those of us hackers,
    who need it handy in a more central  place  can  copy  it  to
    whereever they like.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #



Fri, 10 Dec 1999 02:42:38 +0100 (MET)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] Parallel regress tests (was Re: FOREIGN KEY
	andshift/reduce)
Peter Eisentraut wrote:

> On 1999-12-08, Jan Wieck mentioned:
>
> >     The  multi session test tool, written in Tcl, is ready. Where
> >     should it go and how should it be named? I think it  wouldn't
> >     be a bad idea to have it in a place where it can be used from
> >     any location, instead of putting it into the regression  dir.
> >     Maybe install it into bin?
>
> Please consider not doing that. Bin is for user programs. I don't see w=
> hy
> it shouldn't at least go under the test tree, if not under regress.

    BTW:  I added one fflush(stdout) to psql/commands.c where the
    query buffer is printed for the \p command.  After  that,  my
    test tool is totally happy with it.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #



Fri, 10 Dec 1999 02:44:59 +0100 (MET)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] Parallel regress tests (was Re: FOREIGN KEY
	andshift/reduce)
wieck@debis.com (Jan Wieck) writes:
>     If I allow the <constraint attributes> in column constraints,
>     I  get  2 shift/reduce conflicts. Seems the syntax interferes
>     with NOT NULL.  Actually I commented that part  out,  so  the
>     complete  syntax is available only for table constraints, not
>     on the column level.

>     Could some yacc-guru please take a look at it?

Well, I'm not a guru, but I looked anyway.  It's a mess.  The problem
is that when NOT is the next token, the grammar doesn't know whether
the NOT is starting NOT NULL, which would be a new ColConstraintElem,
or starting NOT DEFERRABLE, which would be part of the current
ColConstraintElem.  So it can't decide whether it's time to reduce
the current stack contents to a finished ColConstraintElem or not.
The only way to do that is to look ahead further than the NOT.

In short, we no longer have an LR(1) grammar.  Yipes.

After a few minutes' thought, it seems that the least unclean way
to attack this problem is to hack up the lexer so that
"NOT<whitespace>NULL" is lexed as a single keyword.  Assuming that
that's doable (I haven't tried, but I think it's possible), the
required changes in the grammar would be small.  The shift/reduce
problem would go away, since we'd essentially have pushed the
required lookahead into the lexer.

It's possible that making this change would even allow us to use
full a_expr rather than b_expr in DEFAULT expressions.  I'm not
sure about it, but that'd be a nice side benefit if so.

Does anyone see a better answer?  This'd definitely be a Big Kluge
from the lexer's point of view, but I don't see an answer at the
grammar level.

BTW --- if we do this, it'd be a simple matter to allow "NOTNULL"
with no embedded space, which is something that I think a number
of other DBMSes accept.  (Which may tell us something about how
they solved this problem...)  It's not a keyword according to 
SQL92, so I'm inclined *not* to accept it, but perhaps someone
else wants to argue the case.

			regards, tom lane

Thu, 09 Dec 1999 21:22:20 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] FOREIGN KEY and shift/reduce 
Don Baccus <dhogaza@pacifier.com> writes:
> alter table foo add();
> crashes the backend.

> I'd say it's really low priority, but should be fixed.

A crash is never a good thing.  If you feel like patching your
copy, the problem is in backend/parser/gram.y:

***************
*** 759,769 ****
  				}
  			| ADD '(' OptTableElementList ')'
  				{
- 					Node *lp = lfirst($3);
- 
  					if (length($3) != 1)
  						elog(ERROR,"ALTER TABLE/ADD() allows one column only");
! 					$$ = lp;
  				}
  			| DROP opt_column ColId
  				{	elog(ERROR,"ALTER TABLE/DROP COLUMN not yet implemented"); }
--- 759,767 ----
  				}
  			| ADD '(' OptTableElementList ')'
  				{
  					if (length($3) != 1)
  						elog(ERROR,"ALTER TABLE/ADD() allows one column only");
! 					$$ = (Node *) lfirst($3);
  				}
  			| DROP opt_column ColId
  				{	elog(ERROR,"ALTER TABLE/DROP COLUMN not yet implemented"); }
***************

Line numbers certainly not right for 6.5 ...

			regards, tom lane

Thu, 09 Dec 1999 21:42:30 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] alter table crashes back end 
> wieck@debis.com (Jan Wieck) writes:
> >     If I allow the <constraint attributes> in column constraints,
> >     I  get  2 shift/reduce conflicts. Seems the syntax interferes
> >     with NOT NULL.  Actually I commented that part  out,  so  the
> >     complete  syntax is available only for table constraints, not
> >     on the column level.
> 
> >     Could some yacc-guru please take a look at it?
> 
> Well, I'm not a guru, but I looked anyway.  It's a mess.  The problem
> is that when NOT is the next token, the grammar doesn't know whether
> the NOT is starting NOT NULL, which would be a new ColConstraintElem,
> or starting NOT DEFERRABLE, which would be part of the current
> ColConstraintElem.  So it can't decide whether it's time to reduce
> the current stack contents to a finished ColConstraintElem or not.
> The only way to do that is to look ahead further than the NOT.
Tom and I talked about moving NOT DEFERED up into the main level with
NOT NULL.

In gram.y, line 949 and line, could there be a test that if the last
List element of $1 is a constraint, and if $2 is NOT DEFERED, we can set
the bit in $1 and just skip adding the defered node?  If not, we can
throw an error.

Also, ColQualList seems very strange.  Why the two actions?  I have
removed it and made ColQualifier work properly.

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

Fri, 10 Dec 1999 00:16:28 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] FOREIGN KEY and shift/reduce
> > Well, I'm not a guru, but I looked anyway.  It's a mess.  The problem
> > is that when NOT is the next token, the grammar doesn't know whether
> > the NOT is starting NOT NULL, which would be a new ColConstraintElem,
> > or starting NOT DEFERRABLE, which would be part of the current
> > ColConstraintElem.  So it can't decide whether it's time to reduce
> > the current stack contents to a finished ColConstraintElem or not.
> > The only way to do that is to look ahead further than the NOT.
> Tom and I talked about moving NOT DEFERED up into the main level with
> NOT NULL.
> 
> In gram.y, line 949 and line, could there be a test that if the last
> List element of $1 is a constraint, and if $2 is NOT DEFERED, we can set
> the bit in $1 and just skip adding the defered node?  If not, we can
> throw an error.

Also, I am using flex 2.5.4, and an seeing no shift-reduce errors from
the current gram.y.


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

Fri, 10 Dec 1999 00:31:04 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] FOREIGN KEY and shift/reduce
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Also, I am using flex 2.5.4, and an seeing no shift-reduce errors from
> the current gram.y.

You wouldn't, because the critical item is still commented out.

            | REFERENCES ColId opt_column_list key_match key_actions 
                {
                    /* XXX
                     *    Need ConstraintAttributeSpec as $6 -- Jan
                     */

If you add ConstraintAttributeSpec to the end of this production,
it fails...

			regards, tom lane

Fri, 10 Dec 1999 01:42:01 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] FOREIGN KEY and shift/reduce 
> >     If I allow the <constraint attributes> in column constraints,
> >     I  get  2 shift/reduce conflicts. Seems the syntax interferes
> >     with NOT NULL.  Actually I commented that part  out,  so  the
> >     complete  syntax is available only for table constraints, not
> >     on the column level.
> Well, I'm not a guru, but I looked anyway.  It's a mess.  The problem
> is that when NOT is the next token, the grammar doesn't know whether
> the NOT is starting NOT NULL, which would be a new ColConstraintElem,
> or starting NOT DEFERRABLE, which would be part of the current
> ColConstraintElem.  So it can't decide whether it's time to reduce
> the current stack contents to a finished ColConstraintElem or not.
> The only way to do that is to look ahead further than the NOT.
> 
> In short, we no longer have an LR(1) grammar.  Yipes.
> 
> After a few minutes' thought, it seems that the least unclean way
> to attack this problem is to hack up the lexer so that
> "NOT<whitespace>NULL" is lexed as a single keyword.  Assuming that
> that's doable (I haven't tried, but I think it's possible), the
> required changes in the grammar would be small.  The shift/reduce
> problem would go away, since we'd essentially have pushed the
> required lookahead into the lexer.
> 
> It's possible that making this change would even allow us to use
> full a_expr rather than b_expr in DEFAULT expressions.  I'm not
> sure about it, but that'd be a nice side benefit if so.
> 
> Does anyone see a better answer?  This'd definitely be a Big Kluge
> from the lexer's point of view, but I don't see an answer at the
> grammar level.

I'd like a chance to fix it at the grammar level. It involves mixing
NOT DEFERRABLE and NOT NULL into the same clauses, but if I can work
it out I'd rather isolate the Big Kluges in gram.y, which seems to
collect that kind of stuff. scan.l is still fairly clean...

                  - Thomas

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

Fri, 10 Dec 1999 15:56:28 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] FOREIGN KEY and shift/reduce
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
>> Does anyone see a better answer?  This'd definitely be a Big Kluge
>> from the lexer's point of view, but I don't see an answer at the
>> grammar level.

> I'd like a chance to fix it at the grammar level. It involves mixing
> NOT DEFERRABLE and NOT NULL into the same clauses, but if I can work
> it out I'd rather isolate the Big Kluges in gram.y, which seems to
> collect that kind of stuff. scan.l is still fairly clean...

Bruce and I were talking about that last night.  I think it could be
fixed by having the grammar treat
	NOT DEFERRABLE
	DEFERRABLE
	INITIALLY IMMEDIATE
	INITIALLY DEFERRED
as independent ColConstraintElem clauses, and then have a post-pass in
analyze.c that folds the flags into the preceding REFERENCES clause
(and complains if they don't appear right after a REFERENCES clause).
Pretty grotty, especially since you probably wouldn't want to do the
same thing for the other uses of these clauses in TableConstraint
and CreateTrigStmt ... but possibly cleaner than a lexer hack.

Another possible approach is to leave scan.l untouched and put a filter
subroutine between gram.y and scan.l.  The filter would normally just
call the scanner and return the token as-is; but when it gets a NOT
token, it would call the scanner again to see if it gets a NULL token.
If so, it returns a single "NOTNULL" token to the grammar; otherwise
it stashes away the lookahead token to return on the next call.

This last approach probably involves the least amount of dirt, but
it does require being able to get in between yacc and lex.  I'm not
sure whether we'd have portability problems doing that; never tried it.

			regards, tom lane

Fri, 10 Dec 1999 11:11:43 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] FOREIGN KEY and shift/reduce 
> Bruce and I were talking about that last night.  I think it could be
> fixed by having the grammar treat
>         NOT DEFERRABLE
>         DEFERRABLE
>         INITIALLY IMMEDIATE
>         INITIALLY DEFERRED
> as independent ColConstraintElem clauses, and then have a post-pass in
> analyze.c that folds the flags into the preceding REFERENCES clause
> (and complains if they don't appear right after a REFERENCES clause).
> Pretty grotty, especially since you probably wouldn't want to do the
> same thing for the other uses of these clauses in TableConstraint
> and CreateTrigStmt ... but possibly cleaner than a lexer hack.

analyze.c already does a grotty scan of the constraint clauses to push
them out into the place Vadim's implementation expects them. We could
identify the FK clauses and push them somewhere else, no problem
(well, at least in principle ;).

                    - Thomas

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

Fri, 10 Dec 1999 16:36:54 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] FOREIGN KEY and shift/reduce
Thomas Lockhart wrote:

> > Bruce and I were talking about that last night.  I think it could be
> > fixed by having the grammar treat
> >         NOT DEFERRABLE
> >         DEFERRABLE
> >         INITIALLY IMMEDIATE
> >         INITIALLY DEFERRED
> > as independent ColConstraintElem clauses, and then have a post-pass in
> > analyze.c that folds the flags into the preceding REFERENCES clause
> > (and complains if they don't appear right after a REFERENCES clause).
> > Pretty grotty, especially since you probably wouldn't want to do the
> > same thing for the other uses of these clauses in TableConstraint
> > and CreateTrigStmt ... but possibly cleaner than a lexer hack.
>
> analyze.c already does a grotty scan of the constraint clauses to push
> them out into the place Vadim's implementation expects them. We could
> identify the FK clauses and push them somewhere else, no problem
> (well, at least in principle ;).

    I  already  added  my  own  list of constraint clauses, where
    foreign key ones are pushed out of the place until the  index
    stuff  is done. Then the list is processed to add the trigger
    statements to extras_after.  It's  enough  of  crippled  code
    there IMHO.

    I  like the other approach by wrapping around yylex() better.
    We definitely insist on bison, and ship  a  prepared  gram.c.
    And a little test here showed, that having

    static int kludge_yylex_wrapper(void);
    #define yylex() kludge_yylex_wrapper()

    in the top declarations section and defining

    #undef yylex()
    static int
    kludge_yylex_wrapper(void)
    {
        return yylex();
    }

    at  the  very end of gram.y does a fine job, changing totally
    nothing. So that's a perfect place to  do  exactly  what  Tom
    suggested. I don't see any portability issues on that.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #



Fri, 10 Dec 1999 17:53:12 +0100 (MET)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] FOREIGN KEY and shift/reduce