Обсуждение: Parser bug?
I think this is a bug in 6.4: bray=> select rcname, rcsrc from pg_relcheck where rcrelid = '115404'::oid and rcrelid in (select min(rcrelid) from pg_relcheck group by rcname); ERROR: parser: Subselect has too many or too few fields. The subselect only produces one column; so I think that the error message is wrong. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID32B8FAA1 ======================================== "The LORD is nigh unto all them that call upon him,to all that call upon him in truth." Psalms 145:18
> I think this is a bug in 6.4: > > bray=> select rcname, rcsrc from pg_relcheck where rcrelid = > '115404'::oid and rcrelid in (select min(rcrelid) from pg_relcheck > group by rcname); ERROR: parser: Subselect has too many or too > few fields. > > The subselect only produces one column; so I think that the > error message is wrong. What is the GROUP BY doing? -- 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, Pennsylvania19026
Bruce Momjian wrote: >> I think this is a bug in 6.4: >> >> bray=> select rcname, rcsrc from pg_relcheck where rcrelid =>> '115404'::oid and rcrelid in (select min(rcrelid) from pg_relcheck >> group by rcname); ERROR: parser: Subselect hastoo many or too >> few fields. >> >> The subselect only produces one column; so I think that the >> error message iswrong. > >What is the GROUP BY doing? This relates to the bug in pg_dump which messes up inherited constraints. The object is to find which is the table in an inheritance hierarchy for which the check constraint is first defined, which must inevitably be the one with the lowest numbered oid. The GROUP BY operates with the aggregate to return the low-numbered oid for each separate rcname. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID32B8FAA1 ======================================== "The LORD is nigh unto all them that call upon him,to all that call upon him in truth." Psalms 145:18
> Bruce Momjian wrote: > >> I think this is a bug in 6.4: > >> > >> bray=> select rcname, rcsrc from pg_relcheck where rcrelid = > >> '115404'::oid and rcrelid in (select min(rcrelid) from pg_relcheck > >> group by rcname); ERROR: parser: Subselect has too many or too > >> few fields. > >> > >> The subselect only produces one column; so I think that the > >> error message is wrong. > > > >What is the GROUP BY doing? > > This relates to the bug in pg_dump which messes up inherited constraints. > > The object is to find which is the table in an inheritance hierarchy for > which the check constraint is first defined, which must inevitably be the > one with the lowest numbered oid. The GROUP BY operates with the aggregate > to return the low-numbered oid for each separate rcname. Maybe I should be clearer. You are grouping by a column that is not in the target list. If you try the subquery on its own, it should fail with a better error message. -- 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, Pennsylvania19026
Bruce Momjian wrote: ... >> >> bray=> select rcname, rcsrc from pg_relcheck where rcrelid = >> >> '115404'::oid and rcrelid in (select min(rcrelid)from pg_relcheck >> >> group by rcname); ERROR: parser: Subselect has too many or too >> >> few fields.>> >> >> >> The subselect only produces one column; so I think that the >> >> error message is wrong. >> > >> >What is the GROUP BY doing? ... >Maybe I should be clearer. You are grouping by a column that is not in >the target list. If you try the subquery on itsown, it should fail >with a better error message. It doesn't fail; it produces the results I want. bray=> select min(rcrelid) from pg_relcheck group by rcname; min ------ 115940 115026 115026 115026 ... etc ... Any way, why should it be an error to group by a column that is not in the results list, if the results list comprises aggregates only? (Mind you, I think I have not yet got a reliable way of finding the ultimate ancestor of an inherited constraint. Is it actually possible to do this with queries or do we have to add a boolean flag to pg_relcheck to be set where the constraint is/is not inherited?) -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID32B8FAA1 ======================================== "Jesus saith unto him, I am the way, the truth, andthe life; no man cometh unto the Father, but by me." John 14:6
> Bruce Momjian wrote: > ... > >> >> bray=> select rcname, rcsrc from pg_relcheck where rcrelid = > >> >> '115404'::oid and rcrelid in (select min(rcrelid) from pg_relcheck > >> >> group by rcname); ERROR: parser: Subselect has too many or too > >> >> few fields. > >> >> > >> >> The subselect only produces one column; so I think that the > >> >> error message is wrong. > >> > > >> >What is the GROUP BY doing? > > ... > > >Maybe I should be clearer. You are grouping by a column that is not in > >the target list. If you try the subquery on its own, it should fail > >with a better error message. > > It doesn't fail; it produces the results I want. > > bray=> select min(rcrelid) from pg_relcheck group by rcname; > min > ------ > 115940 > 115026 > 115026 > 115026 > ... etc ... > > Any way, why should it be an error to group by a column that is not in the > results list, if the results list comprises aggregates only? > > (Mind you, I think I have not yet got a reliable way of finding the > ultimate ancestor of an inherited constraint. Is it actually possible to > do this with queries or do we have to add a boolean flag to pg_relcheck > to be set where the constraint is/is not inherited?) Gee, I didn't know we could do that. Seems like doing that in a subquery messes things up. My guess is that the GROUP BY internally carries the GROUP BY column, and that is not getting stripped when used in a subquery, so it thinks the subquery returns two columns. Perhaps the junknode code needs to be added somewhere for subqueries? Can anyone else comment on this possibility? -- 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, Pennsylvania19026
"Oliver Elphick" <olly@lfix.co.uk> writes:
> (Mind you, I think I have not yet got a reliable way of finding the
> ultimate ancestor of an inherited constraint. Is it actually possible to
> do this with queries or do we have to add a boolean flag to pg_relcheck
> to be set where the constraint is/is not inherited?)
In fact, I was about to point out that the query you were describing
couldn't possibly give you a reliable answer, quite independent of
whether the backend is implementing it properly or not.
Consider
CREATE TABLE parent1 (i1 int4, CONSTRAINT c1 CHECK (i1 > 0));
CREATE TABLE child1 (CONSTRAINT c2 CHECK (i1 > 4)) INHERITS (parent1);
CREATE TABLE child2 (CONSTRAINT c2 CHECK (i1 > 4)) INHERITS (parent1);
This will give us a pg_relcheck like
rcrelid rcname rcbin rcsrc
parent1 c1 gobbledegook i1 > 0child1 c1 gobbledegook i1 > 0child2 c1 gobbledegook
i1 > 0child1 c2 gobbledegook i1 > 4child2 c2 gobbledegook i1 > 4
(where I've written table names in place of numeric OIDs for rcrelid).
Now child2 did not inherit c2 from child1, but child1 has a lower OID
than child2, so your test would mistakenly omit c2 from child2's
definition.
It seems to me that the correct way to do this is to compare each of a
table's constraints against its immediate parent's constraints, and omit
from the child any constraints that have the same rcname AND the same
rcsrc as a constraint of the parent. (You need not look at anything
other than the immediate parent, because constraints inherited from
more distant ancestors will also be listed for the parent.)
There is a case that pg_relcheck does not allow you to distinguish,
and that is whether or not the child definition was actually written
with a redundant constraint:
CREATE TABLE parentx (i1 int4, CONSTRAINT c1 CHECK (i1 > 0));
CREATE TABLE childx (CONSTRAINT c1 CHECK (i1 > 0)) INHERITS (parentx);
Unless we modify pg_relcheck, pg_dump will have to dump this as simply
CREATE TABLE parentx (i1 int4, CONSTRAINT c1 CHECK (i1 > 0));
CREATE TABLE childx () INHERITS (parentx);
since it cannot tell that childx's constraint wasn't simply inherited.
However, it's not clear to me that suppression of redundant constraints
is a bad thing ;-)
regards, tom lane
Tom Lane wrote: >"Oliver Elphick" <olly@lfix.co.uk> writes: >> (Mind you, I think I have not yet got a reliable way of findingthe >> ultimate ancestor of an inherited constraint. Is it actually possible to >> do this with queries or do wehave to add a boolean flag to pg_relcheck >> to be set where the constraint is/is not inherited?) > >In fact, I was aboutto point out that the query you were describing >couldn't possibly give you a reliable answer, quite independent of>whether the backend is implementing it properly or not. Yes, I had been using a concrete example; the results were not sufficiently general. [... skip example ...] >It seems to me that the correct way to do this is to compare each of a >table's constraints against its immediate parent'sconstraints, and omit >from the child any constraints that have the same rcname AND the same >rcsrc as a constraintof the parent. (You need not look at anything >other than the immediate parent, because constraints inheritedfrom >more distant ancestors will also be listed for the parent.) That looks good. I'll see if I can do it that way. >There is a case that pg_relcheck does not allow you to distinguish, >and that is whether or not the child definition wasactually written >with a redundant constraint: [...skip example...] >since it cannot tell that childx's constraint wasn't simply inherited. >However, it's not clear to me that suppressionof redundant constraints >is a bad thing ;-) It seems to be quite reasonable to drop them. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID32B8FAA1 ======================================== "Jesus saith unto him, I am the way, the truth, andthe life; no man cometh unto the Father, but by me." John 14:6
Tom Lane wrote:
... >In fact, I was about to point out that the query you were describing >couldn't possibly give you a reliable
answer,quite independent of >whether the backend is implementing it properly or not.
I think this will do the job; can you please check it out:
select rcname,rcsrc from pg_relcheck, pg_inherits as i where rcrelid = '%s'::oid
and (not exists -- for (select * from
pg_inherits -- non-inherited where inhrel = pg_relcheck.rcrelid) -- tables
or (not exists -- (select * from pg_relcheck as c --
for where c.rcname = pg_relcheck.rcname -- inherited and c.rcrelid =
i.inhparent) -- tables and rcrelid = i.inhrel)); --
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key
ID32B8FAA1 ======================================== "Jesus saith unto him, I am the way, the truth,
andthe life; no man cometh unto the Father, but by me." John 14:6
"Oliver Elphick" wrote: >I think this will do the job; can you please check it out:
It seems to work, at least for my database, so here is a patch:
*** postgresql-6.4/src/bin/pg_dump/pg_dump.c Tue Nov 24 23:01:27 1998
--- postgresql-6.4.orig/src/bin/pg_dump/pg_dump.c Mon Oct 26 01:05:07 1998
***************
*** 1459,1504 **** tblinfo[i].ncheck = atoi(PQgetvalue(res, i, i_relchecks)); tblinfo[i].ntrig =
atoi(PQgetvalue(res,i, i_reltriggers));
! /* Exclude inherited CHECKs from CHECK constraints total */
! if (tblinfo[i].ncheck > 0)
! {
! PGresult *res2;
! int ntups2;
!
! if (g_verbose)
! fprintf(stderr, "%s excluding inherited CHECK constraints "
! "for relation: '%s' %s\n",
! g_comment_start,
! tblinfo[i].relname,
! g_comment_end);
!
! sprintf(query, "SELECT * from pg_relcheck, pg_inherits as i "
! "where rcrelid = '%s'::oid "
! " and exists "
! " (select * from pg_relcheck as c "
! " where c.rcname = pg_relcheck.rcname "
! " and c.rcrelid = i.inhparent) "
! " and rcrelid = i.inhrel",
! tblinfo[i].oid);
! res2 = PQexec(g_conn, query);
! if (!res2 ||
! PQresultStatus(res2) != PGRES_TUPLES_OK)
! {
! fprintf(stderr, "getTables(): SELECT (for inherited CHECK) failed\n");
! exit_nicely(g_conn);
! }
! ntups2 = PQntuples(res2);
! tblinfo[i].ncheck -= ntups2;
! if (tblinfo[i].ncheck < 0)
! {
! fprintf(stderr, "getTables(): found more inherited CHECKs than total for "
! "relation %s\n",
! tblinfo[i].relname);
! exit_nicely(g_conn);
! }
! }
!
! /* Get CHECK constraints originally defined for this table */ if (tblinfo[i].ncheck > 0) {
PGresult *res2;
--- 1459,1465 ---- tblinfo[i].ncheck = atoi(PQgetvalue(res, i, i_relchecks)); tblinfo[i].ntrig =
atoi(PQgetvalue(res,i, i_reltriggers));
! /* Get CHECK constraints */ if (tblinfo[i].ncheck > 0) { PGresult *res2;
***************
*** 1513,1531 **** tblinfo[i].relname, g_comment_end);
! sprintf(query, "SELECT DISTINCT rcname, rcsrc "
! "from pg_relcheck, pg_inherits as i "
! "where rcrelid = '%s'::oid "
! /* allow all checks from tables that do not inherit */
! " and (not exists "
! " (select * from pg_inherits "
! " where inhrel = pg_relcheck.rcrelid)"
! /* and allow checks that are not inherited from other tables */
! " or (not exists "
! " (select * from pg_relcheck as c "
! " where c.rcname = pg_relcheck.rcname "
! " and c.rcrelid = i.inhparent) "
! " and rcrelid = i.inhrel))", tblinfo[i].oid); res2 =
PQexec(g_conn,query); if (!res2 ||
--- 1474,1481 ---- tblinfo[i].relname, g_comment_end);
! sprintf(query, "SELECT rcname, rcsrc from pg_relcheck "
! "where rcrelid = '%s'::oid ", tblinfo[i].oid); res2 =
PQexec(g_conn,query); if (!res2 ||
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key
ID32B8FAA1 ======================================== "Jesus saith unto him, I am the way, the truth,
andthe life; no man cometh unto the Father, but by me." John 14:6
I tried posting a bug report to psql-ports@postgresql.org and it bounced. I redirected the bounce message to hackers@postgreSQL.org (this list) and it never showed up. Hello? Assuming this message got though I'd like to report my problems compiling 6.4 with KTH-KRB, and what I can say about how to fix it. I'm still testing, but basically it looks like if you have kerberos 4 then you need to disable use of the system crypt routines. This *should* be handled in the configure stuff. I fixed it by modifying fe-auth.c and fe-connect.c in libpq to not include <crypt.h> and by modifying Makefile.global to include -lresolv instead of -lcrypt (that gives the right load order). The function des_encrypt exists in both the KTH kerberos and the system crypt libraries with different arguments. Signature failed Preliminary Design Review. Feasibility of a new signature is currently being evaluated. h.b.hotz@jpl.nasa.gov, or hbhotz@oxy.edu
"Henry B. Hotz" <hotz@jpl.nasa.gov> writes:
> I'm still testing, but basically it looks like if you have kerberos 4 then
> you need to disable use of the system crypt routines. This *should* be
> handled in the configure stuff.
That's fairly unpleasant, since it's not out of the question that a
given site might need to support both auth methods to cope with varying
clients.
> The function des_encrypt exists in both the KTH kerberos and the system
> crypt libraries with different arguments.
Not everywhere --- there's no such routine in my crypt library, for
instance. I would not like to see kerberos + crypt disabled everywhere
because it does not work on your machine.
Ideally we'd need an autoconf test to discover whether kerberos and
crypt libraries are compatible on a given machine, and an autoconf
--with switch to allow the user to decide which one to include if
they're not. Do you have any ideas about a simple way to check whether
this problem exists on a given platform?
regards, tom lane
At 4:18 PM -0800 11/25/98, Tom Lane wrote:
>"Henry B. Hotz" <hotz@jpl.nasa.gov> writes:
>> I'm still testing, but basically it looks like if you have kerberos 4 then
>> you need to disable use of the system crypt routines. This *should* be
>> handled in the configure stuff.
>
>That's fairly unpleasant, since it's not out of the question that a
>given site might need to support both auth methods to cope with varying
>clients.
Yeah. I note that if you use the Solaris built-in kerberos support the
conflict should not exist. For Postgres this problem is specific to the
KTH kerberos implementation I think, but it also exists with SSL. I have
no information about MIT kerberos IV or V.
>> The function des_encrypt exists in both the KTH kerberos and the system
>> crypt libraries with different arguments.
>
>Not everywhere --- there's no such routine in my crypt library, for
>instance. I would not like to see kerberos + crypt disabled everywhere
>because it does not work on your machine.
This is Solaris 2.5, presumably 2.6 and 7 have the same problem.
>Ideally we'd need an autoconf test to discover whether kerberos and
>crypt libraries are compatible on a given machine, and an autoconf
>--with switch to allow the user to decide which one to include if
>they're not. Do you have any ideas about a simple way to check whether
>this problem exists on a given platform?
If you include <crypt.h> and <krb.h> from the system and
/usr/athena/include respectively then you get a compile error.
My problem may actually be a bit obscure. I'm using the KTH implementation
of kerberos IV because I want to be able to use the JPL AFS kerberos
server. (AFS kerberos is an incompatable variant of MIT kerberos IV for
those who don't know. Solaris and NetBSD come with MIT kerberos IV support
built-in. MIT kerberos V can support both kerberos IV variants, but
Postgres is a client.)
I will put in a plug for autoconf support for kerberos in any case. We
need a --with-kerberos[={4,5}] option and --with-kerberos-include=..,
--with-kerberos-lib=.., and --with-kerberos-srvtab=.. options.
The administrator guide says support for kerberos IV will disappear when 5
is released. I think there should be a fairly long delay in that. Many
people will need to use kerberos IV in order to use an institutional
capability, like AFS accounting. Many people should prefer to use the
built-in capabilities of their OS and all current bundled kerberos support
is at version IV. This will take a *long* time.
Finally let me put in a big public thank-you to Tom Ivar Helbekkmo for
patiently explaining many things that I should have understood from the
documentation.
Signature failed Preliminary Design Review.
Feasibility of a new signature is currently being evaluated.
h.b.hotz@jpl.nasa.gov, or hbhotz@oxy.edu
On Mon, 30 Nov 1998, Henry B. Hotz wrote: > those who don't know. Solaris and NetBSD come with MIT kerberos IV support > built-in. MIT kerberos V can support both kerberos IV variants, but > Postgres is a client.) Solaris 7 ships with Kerberos V support now. I'll dig up a little info I put together on Solaris 7 and send it on. Dax Kelson Internet Connect, Inc.
"Henry B. Hotz" <hotz@jpl.nasa.gov> writes:
> At 4:18 PM -0800 11/25/98, Tom Lane wrote:
>> Ideally we'd need an autoconf test to discover whether kerberos and
>> crypt libraries are compatible on a given machine, and an autoconf
>> --with switch to allow the user to decide which one to include if
>> they're not. Do you have any ideas about a simple way to check whether
>> this problem exists on a given platform?
> If you include <crypt.h> and <krb.h> from the system and
> /usr/athena/include respectively then you get a compile error.
OK, that seems pretty easy to check.
> I will put in a plug for autoconf support for kerberos in any case. We
> need a --with-kerberos[={4,5}] option and --with-kerberos-include=..,
> --with-kerberos-lib=.., and --with-kerberos-srvtab=.. options.
Where does that information get entered now --- do you have to do it
manually after running configure?
I'd be willing to do the autoconf hacking, but since I have no kerberos
setup here, I can't test it; and I'm not familiar enough with kerberos
to expect to get it right the first time. If you can test but don't
want to hack the code, let's get together off-list and work on it.
> The administrator guide says support for kerberos IV will disappear when 5
> is released. I think there should be a fairly long delay in that.
As long as we have kerb4 support in the code (and I'm not hearing anyone
propose to take that out), it ought to be supported at the autoconf
level too.
regards, tom lane
At 8:25 AM -0800 12/1/98, Tom Lane wrote:
>"Henry B. Hotz" <hotz@jpl.nasa.gov> writes:
>> If you include <crypt.h> and <krb.h> from the system and
>> /usr/athena/include respectively then you get a compile error.
>
>OK, that seems pretty easy to check.
Specifically des_encrypt() is declared with a different number of arguments.
>> I will put in a plug for autoconf support for kerberos in any case. We
>> need a --with-kerberos[={4,5}] option and --with-kerberos-include=..,
>> --with-kerberos-lib=.., and --with-kerberos-srvtab=.. options.
>
>Where does that information get entered now --- do you have to do it
>manually after running configure?
Yes. It's documented in the administrator guide actually. You edit
Makefile.global. The comments in Makefile.global are pretty clear.
The autoconf support should just be a matter of copying the flags I
indicated into Makefile.global.
Signature failed Preliminary Design Review.
Feasibility of a new signature is currently being evaluated.
h.b.hotz@jpl.nasa.gov, or hbhotz@oxy.edu
"Oliver Elphick" <olly@lfix.co.uk> writes:
> It seems to work, at least for my database, so here is a patch:
> [ patch to prevent dumping of inherited constraints snipped ]
I have applied this patch to both the main CVS tree and REL6_4 branch,
along with Constantin Teodorescu's suggestion to improve the formatting
of pg_dump's CREATE TABLE commands, and some work of my own to stop
an occasional coredump in pg_dump -z.
FYI, I was able to simplify your query for fetching non-inherited
checks; it now looks like:
sprintf(query, "SELECT rcname, rcsrc from pg_relcheck " "where rcrelid = '%s'::oid " " and not exists " "
(select* from pg_relcheck as c, pg_inherits as i " " where i.inhrel = pg_relcheck.rcrelid " " and c.rcname
=pg_relcheck.rcname " " and c.rcsrc = pg_relcheck.rcsrc " " and c.rcrelid = i.inhparent) ",
tblinfo[i].oid);
regards, tom lane