Re: [HACKERS] Re: [BUGS] First experiences with Postgresql 7.0

============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================

Your name : Rolf Grossmann
Your email address : grossman(at)securitas(dot)net

System Configuration
---------------------
Architecture (example: Intel Pentium) : AMD-K6 300

Operating System (example: Linux 2.0.26 ELF) : FreeBSD 3.4-STABLE

PostgreSQL version (example: PostgreSQL-6.5.1): PostgreSQL-7.0beta1

Compiler used (example: gcc 2.8.0) : gcc 2.95

Please enter a FULL description of your problem:
------------------------------------------------

First I'd like to say that I'm really impressed with the quality of this
first beta release. Still, when I was trying to set up my old database,
I ran into a bit of a problem: I couldn't specify NOT NULL PRIMARY KEY
anymore. Removing the NOT NULL part solves the problem (and it's implied
by PRIMARY KEY anyway), however all major databases allow that syntax
(and upto the last release Postgresql did too), so I'd like to see it
added back.

Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

Try to create this table:

CREATE TABLE Notes (
Id INT NOT NULL PRIMARY KEY,
Text VARCHAR(1024) NOT NULL
);

Please enter a FULL description of your problem:
------------------------------------------------

There is another problem with the regression tests: If the user running the
tests has a .psqlrc file all regression tests fail, because commands from
that file are echoed to the result file. Additionally, it a transaction
is started from that file, regression tests fail, because they include tests
for error cases and a transaction needs to be aborted after an error.

A possible solution would probably be to add a flag to psql that inhibits
reading the .psqlrc file and using that flag with the regression tests.

On a related note (not a bug of course ;))... would it be possible to add
some option to psql (or even libpq?) to always keep a transaction active?

Bye, Rolf

Rolf Grossmann <grossman(at)securitas(dot)net> writes:
> I ran into a bit of a problem: I couldn't specify NOT NULL PRIMARY KEY
> anymore.

For the moment try the other order: PRIMARY KEY NOT NULL. This is a
known parser deficiency that we chose to leave unfixed for the start of
beta, but it should be fixed for 7.0 ...

regards, tom lane

Hi,

on Wed, 23 Feb 2000 11:56:02 -0500 Tom Lane wrote
concerning "Re: [BUGS] First experiences with Postgresql 7.0 " something like this:

> Rolf Grossmann <grossman(at)securitas(dot)net> writes:
>> I ran into a bit of a problem: I couldn't specify NOT NULL PRIMARY KEY
>> anymore.

> For the moment try the other order: PRIMARY KEY NOT NULL.

That doesn't work either.

> This is a
> known parser deficiency that we chose to leave unfixed for the start of
> beta, but it should be fixed for 7.0 ...

That's good to hear.

Thanks, Rolf

Well, don't I look stupid here. Once upon a time I recall to have fixed
exactly this issue, apparently it snuck back in.

If you run psql in non-interactive mode the psqlrc file shouldn't be read
at all. Unless people want that flag, but I don't like that better.

Perhaps this is a good time to ask when and how any fix to this should be
applied.

On Wed, 23 Feb 2000, Rolf Grossmann wrote:

> There is another problem with the regression tests: If the user running the
> tests has a .psqlrc file all regression tests fail, because commands from
> that file are echoed to the result file. Additionally, it a transaction
> is started from that file, regression tests fail, because they include tests
> for error cases and a transaction needs to be aborted after an error.
>
> A possible solution would probably be to add a flag to psql that inhibits
> reading the .psqlrc file and using that flag with the regression tests.
>
> On a related note (not a bug of course ;))... would it be possible to add
> some option to psql (or even libpq?) to always keep a transaction active?
>
> Bye, Rolf
>
> ************
>
>

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

> There is another problem with the regression tests: If the user running the
> tests has a .psqlrc file all regression tests fail, because commands from
> that file are echoed to the result file. Additionally, it a transaction
> is started from that file, regression tests fail, because they include tests
> for error cases and a transaction needs to be aborted after an error.

Incidentally, this should also be the behaviour of the old psql, so it
shouldn't be all that surprising. Will be fixed of course, though.

> On a related note (not a bug of course ;))... would it be possible to add
> some option to psql (or even libpq?) to always keep a transaction active?

The backend would be the right place for this, and yes, it's possible, but
there seems to be some disagreement whether we should do it.

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

Hi,

on Thu, 24 Feb 2000 13:47:36 +0100 (MET) Peter Eisentraut wrote
concerning "Re: [BUGS] First experiences with Postgresql 7.0" something like this:

> Well, don't I look stupid here. Once upon a time I recall to have fixed
> exactly this issue, apparently it snuck back in.

> If you run psql in non-interactive mode the psqlrc file shouldn't be read
> at all. Unless people want that flag, but I don't like that better.

After doing some more experimenting, I noticed that psql does (indeed)
not read the psqlrc file when given the -f option. Alas, the regression
tests don't use -f but send the file in via stdio. So I think this
behaviour is The Right Thing, but the regression tests should be fixed
(probably to use -f).

Bye, Rolf

On Thu, 24 Feb 2000, Rolf Grossmann wrote:

> not read the psqlrc file when given the -f option. Alas, the regression
> tests don't use -f but send the file in via stdio. So I think this
> behaviour is The Right Thing, but the regression tests should be fixed
> (probably to use -f).

But the output of "-f" vs "<" differs, in particular "-f" gives you error
messages like
psql:inputfile:lineno: ERROR: ...

and I believe no one wants to fix up the regression tests in that
direction, after we already did it once.

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

> Well, don't I look stupid here. Once upon a time I recall to have fixed
> exactly this issue, apparently it snuck back in.
>
> If you run psql in non-interactive mode the psqlrc file shouldn't be read
> at all. Unless people want that flag, but I don't like that better.
>
> Perhaps this is a good time to ask when and how any fix to this should be
> applied.
>
>

I see the same problem here. Also, the regression tests required me to
define PGLIB.

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

> Hi,
>
> on Thu, 24 Feb 2000 13:47:36 +0100 (MET) Peter Eisentraut wrote
> concerning "Re: [BUGS] First experiences with Postgresql 7.0" something like this:
>
> > Well, don't I look stupid here. Once upon a time I recall to have fixed
> > exactly this issue, apparently it snuck back in.
>
> > If you run psql in non-interactive mode the psqlrc file shouldn't be read
> > at all. Unless people want that flag, but I don't like that better.
>
> After doing some more experimenting, I noticed that psql does (indeed)
> not read the psqlrc file when given the -f option. Alas, the regression
> tests don't use -f but send the file in via stdio. So I think this
> behaviour is The Right Thing, but the regression tests should be fixed
> (probably to use -f).

But is it right to not read the psqlrc file with -f? Can psqlrc be
read but not displayed with -q. regress.sh uses -a and -q, which seem
to conflict with each other.

-a Echo all input from script
-q Run quietly (no messages, only query output)

I will admit regress.sh may be using the wrong flags now. Also, PGLIB
is used by createlang. Not sure how it used to work.

CREATE DATABASE
=============== installing PL/pgSQL... =================
createlang: missing required argument PGLIB directory
(This is the directory where the interpreter for the procedural
language is stored. Traditionally, these are installed in whatever
'lib' directory was specified at configure time.)
createlang failed

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

Peter Eisentraut <e99re41(at)DoCS(dot)UU(dot)SE> writes:
> If you run psql in non-interactive mode the psqlrc file shouldn't be read
> at all. Unless people want that flag, but I don't like that better.

> Perhaps this is a good time to ask when and how any fix to this should be
> applied.

This is arguably a bug fix, so you needn't worry about it being beta
phase. However, there seems to be some doubt about exactly how it
*should* work, so you should hold off until there is consensus.

I take it you are considering "only read psqlrc if stdin is a tty",
rather than providing a switch-selectable choice. I think that might
be too inflexible. The regression tests clearly need to be able to
disregard psqlrc, but ordinary users will very likely want to write
scripts that depend on their psqlrc. (For sure, we will get bug reports
"this works by hand but not in a script" that trace back to psqlrc
settings or lack of 'em.)

Using -f would work if you hadn't already overloaded it with another
meaning; but as you say I don't much want to add line numbers to all
the regress test expected outputs. (That would mean that
adding/deleting lines in a test would create many bogus differences
further down in its output, which would be a pain in the neck for the
initial hand-validation of the changed output.)

So I vote for a switch that suppresses reading psqlrc ...

regards, tom lane

> Using -f would work if you hadn't already overloaded it with another
> meaning; but as you say I don't much want to add line numbers to all
> the regress test expected outputs. (That would mean that
> adding/deleting lines in a test would create many bogus differences
> further down in its output, which would be a pain in the neck for the
> initial hand-validation of the changed output.)
>
> So I vote for a switch that suppresses reading psqlrc ...
>

Yes, but are there cases where we would want psqlrc values set? Should
we specifically set all the variables ourselves on startup, just
over-riding what is in psqlrc?

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

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Yes, but are there cases where we would want psqlrc values set? Should
> we specifically set all the variables ourselves on startup, just
> over-riding what is in psqlrc?

No. In the first place, we've already got a dozen or two SET variables
(and the list keeps changing); do you really want to reset all of those
in each regress test? In the second place, a psqlrc script could screw
things up in more creative ways than just issuing SET commands. IIRC,
Rolf's original example was a psqlrc that issued a BEGIN to leave the
system in an open-transaction state. In the third place, the psql echo
output from any commands issued by psqlrc would itself be enough to
cause bogus "failures" of all the tests.

One advantage of using a switch is that if someone *did* want to
experiment with regress test behavior with non-default settings,
he could set up a psqlrc file and then remove that switch from
the regression driver script. Of course he'd have to ignore a
lot of bogus differences...

regards, tom lane

On Thu, 24 Feb 2000, Tom Lane wrote:

> > Perhaps this is a good time to ask when and how any fix to this should be
> > applied.
>
> This is arguably a bug fix, so you needn't worry about it being beta
> phase.

I'm not sure how this works now: Do I just commit it to the tree, so it
will be in when, say, beta2 gets generated?

> I take it you are considering "only read psqlrc if stdin is a tty",

This is how shells work, that's always my default decision for unchartered
territory. (Of course psql is not a shell, but that's why we're discussing
...)

> Using -f would work if you hadn't already overloaded it with another
> meaning;

Huh, "-f" is not a new option. "-f" is different from "<" because of two
reasons: 1) if they were the same, we wouldn't need one of them, and 2) a
program should behave the same independent of what kind of device its
standard input comes from. (That's why "<" doesn't print out error
messages with line numbers.) This is an ideal state of course.

[5 min later ...]

Ah, a tcsh user! ;) I could go for an -X option to suppress reading the
startup file, with default being that it is read in any mode. A pretty
dump option letter, but not all that far-fetched.

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

> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Yes, but are there cases where we would want psqlrc values set? Should
> > we specifically set all the variables ourselves on startup, just
> > over-riding what is in psqlrc?
>
> No. In the first place, we've already got a dozen or two SET variables
> (and the list keeps changing); do you really want to reset all of those
> in each regress test? In the second place, a psqlrc script could screw
> things up in more creative ways than just issuing SET commands. IIRC,
> Rolf's original example was a psqlrc that issued a BEGIN to leave the
> system in an open-transaction state. In the third place, the psql echo
> output from any commands issued by psqlrc would itself be enough to
> cause bogus "failures" of all the tests.
>

Yes, I see. Just asking.

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

On Thu, 24 Feb 2000, Bruce Momjian wrote:

> I see the same problem here. Also, the regression tests required me to
> define PGLIB.

Is that because of createlang or initdb or both? Which regression driver?

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

> On Thu, 24 Feb 2000, Bruce Momjian wrote:
>
> > I see the same problem here. Also, the regression tests required me to
> > define PGLIB.
>
> Is that because of createlang or initdb or both? Which regression driver?

See later message. createlang is causing it.

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

Peter Eisentraut <e99re41(at)DoCS(dot)UU(dot)SE> writes:
>> This is arguably a bug fix, so you needn't worry about it being beta
>> phase.

> I'm not sure how this works now: Do I just commit it to the tree, so it
> will be in when, say, beta2 gets generated?

Right. No real difference in commit procedures at this point.

At some point after the release, we will set up a branch for REL_7.0,
and after that, ordinary commits will only apply to new development
for 7.1, not to the stable release branch. But no need to worry about
that for now.

> Ah, a tcsh user! ;) I could go for an -X option to suppress reading the
> startup file, with default being that it is read in any mode. A pretty
> dump option letter, but not all that far-fetched.

Works for me...

regards, tom lane

Peter Eisentraut wrote:
>
> On Thu, 24 Feb 2000, Bruce Momjian wrote:
>
> > I see the same problem here. Also, the regression tests required me to
> > define PGLIB.
>
> Is that because of createlang or initdb or both? Which regression driver?

Createlang has done this for some time -- at least since I've been
packaging the RPM's with the regression tests. I have had to define
PGLIB in regress.sh -- otherwise, createlang doesn't know where to find
the pl .so. As my 7.0 installation is at home, I can't check the 7.0
regress.sh from here -- however, the 6.5.x regress.sh did it's own PGLIB
definition.

--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

> On Thu, 24 Feb 2000, Tom Lane wrote:
>
> > > Perhaps this is a good time to ask when and how any fix to this should be
> > > applied.
> >
> > This is arguably a bug fix, so you needn't worry about it being beta
> > phase.
>
> I'm not sure how this works now: Do I just commit it to the tree, so it
> will be in when, say, beta2 gets generated?

Betas are not static releases. We live in beta for over a month, with
people making changes to fix user problems.

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

> Peter Eisentraut wrote:
> >
> > On Thu, 24 Feb 2000, Bruce Momjian wrote:
> >
> > > I see the same problem here. Also, the regression tests required me to
> > > define PGLIB.
> >
> > Is that because of createlang or initdb or both? Which regression driver?
>
> Createlang has done this for some time -- at least since I've been
> packaging the RPM's with the regression tests. I have had to define
> PGLIB in regress.sh -- otherwise, createlang doesn't know where to find
> the pl .so. As my 7.0 installation is at home, I can't check the 7.0
> regress.sh from here -- however, the 6.5.x regress.sh did it's own PGLIB
> definition.

For some reason, I didn't need it until recently.

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

Hi,

on Thu, 24 Feb 2000 16:59:48 +0100 (MET) Peter Eisentraut wrote
concerning "Re: [HACKERS] Re: [BUGS] First experiences with Postgresql 7.0 " something like this:

> (Of course psql is not a shell, but that's why we're discussing ...)

Now, be careful with this statement. Personally, I have already tried to
use psql as a shell and I think it would be really cool if you could just
write #!/path/to/psql -f to write sql scripts.
However, that would require psql to treat # as a comment starter and we're
moving away from SQL standards with that. So I'm a bit weary of suggesting
such a thing.

>> Using -f would work if you hadn't already overloaded it with another
>> meaning;

> [5 min later ...]

> Ah, a tcsh user! ;) I could go for an -X option to suppress reading the
> startup file, with default being that it is read in any mode. A pretty
> dump option letter, but not all that far-fetched.

Uhm ... my tcsh manual describes those options differently:

-f The shell ignores ~/.tcshrc, and thus starts faster.
-X Is to -x as -V is to -v.

Of course, as we have noted above, psql is not a shell, so I wonder if
that's the way to go. Personally, I'd say just pick a letter.

Bye, Rolf

Rolf Grossmann <grossman(at)securitas(dot)net> writes:
>> (Of course psql is not a shell, but that's why we're discussing ...)

> Now, be careful with this statement. Personally, I have already tried to
> use psql as a shell and I think it would be really cool if you could just
> write #!/path/to/psql -f to write sql scripts.

[ straying off-topic ]

Have you tried pgbash? I haven't, but it sounds pretty cool if you
think psql and your shell should be the same thing...

regards, tom lane

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
>> PGLIB in regress.sh -- otherwise, createlang doesn't know where to find
>> the pl .so. As my 7.0 installation is at home, I can't check the 7.0
>> regress.sh from here -- however, the 6.5.x regress.sh did it's own PGLIB
>> definition.

> For some reason, I didn't need it until recently.

I have PGDATA and PGLIB defined in .profile for my postgres account,
so I wouldn't have noticed whether the regress tests need it or not :-(
Possibly the same is true for most of the other developers.

IIRC, "make all" to set up the regress tests also needs PGLIB to be set.

regards, tom lane

On Thu, 24 Feb 2000, Bruce Momjian wrote:

> > Using -f would work if you hadn't already overloaded it with another
> > meaning; but as you say I don't much want to add line numbers to all
> > the regress test expected outputs. (That would mean that
> > adding/deleting lines in a test would create many bogus differences
> > further down in its output, which would be a pain in the neck for the
> > initial hand-validation of the changed output.)
> >
> > So I vote for a switch that suppresses reading psqlrc ...
> >
>
> Yes, but are there cases where we would want psqlrc values set? Should
> we specifically set all the variables ourselves on startup, just
> over-riding what is in psqlrc?

IMHO, the regression tests are based on a snapshot where psql is in a
'default state' ... why would we want psqlrc values set?

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org

On Thu, 24 Feb 2000, Peter Eisentraut wrote:

> On Thu, 24 Feb 2000, Tom Lane wrote:
>
> > > Perhaps this is a good time to ask when and how any fix to this should be
> > > applied.
> >
> > This is arguably a bug fix, so you needn't worry about it being beta
> > phase.
>
> I'm not sure how this works now: Do I just commit it to the tree, so it
> will be in when, say, beta2 gets generated?

bug fixes, yes ... but posting a patch against the current beta1 as a sort
of "here's the fix" would be very appreciated.

On 2000-02-24, Lamar Owen mentioned:

> Peter Eisentraut wrote:
> >
> > On Thu, 24 Feb 2000, Bruce Momjian wrote:
> >
> > > I see the same problem here. Also, the regression tests required me to
> > > define PGLIB.
> >
> > Is that because of createlang or initdb or both? Which regression driver?
>
> Createlang has done this for some time

You must provide an -L (--pglib) option to createlang, just as for
initdb. I'm committing a fix for this.

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

On 2000-02-24, Rolf Grossmann mentioned:

> use psql as a shell and I think it would be really cool if you could just
> write #!/path/to/psql -f to write sql scripts.

I considered that briefly, but dismissed it equally fast. psql is a shell
to the PostgreSQL backend, if you will, not to the system. It's optimized
as a batch processor and for being called from shell scripts, not for
being a programming language of it's own. (In the future it would be nice
to have a PL/Pgsql based front-end available for that sort of stuff.)

> Uhm ... my tcsh manual describes those options differently:
>
> -f The shell ignores ~/.tcshrc, and thus starts faster.
> -X Is to -x as -V is to -v.

I wasn't actually implying to have picked -X in accordance with tcsh, I
was just confused about how Tom talked about -f.

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

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>> Uhm ... my tcsh manual describes those options differently:
>>
>> -f The shell ignores ~/.tcshrc, and thus starts faster.
>> -X Is to -x as -V is to -v.

> I wasn't actually implying to have picked -X in accordance with tcsh, I
> was just confused about how Tom talked about -f.

Oh, sorry, I just meant that -f already has one special behavior in
addition to just physically selecting the input source, namely
causing line numbers to get attached to error messages. That's fine,
but adding two special behaviors that aren't really closely related
to the same switch is not so great.

regards, tom lane

Hi,

on Fri, 25 Feb 2000 00:39:15 +0100 (CET) Peter Eisentraut wrote
concerning "Re: [HACKERS] Re: [BUGS] First experiences with Postgresql 7.0 "
something like this:

>> use psql as a shell and I think it would be really cool if you could just
>> write #!/path/to/psql -f to write sql scripts.

> I considered that briefly, but dismissed it equally fast. psql is a shell
> to the PostgreSQL backend, if you will, not to the system. It's optimized
> as a batch processor and for being called from shell scripts, not for
> being a programming language of it's own. (In the future it would be nice
> to have a PL/Pgsql based front-end available for that sort of stuff.)

Well, if you're saying psql is a shell, then maybe we should consider moving
in that direction. Not everything that's called with #! is a shell to the
system. The most notable example is probably perl, but there are other
programs like sed or awk that are being used with #! but certainly nobody
ever considered using awk as a system shell ;)

As for the programming language: You're already going in that direction
by implementing something like pl/sql. Now if that was available from
psql you're already way down the programming language road.

Just some thoughts ...
Rolf