Обсуждение: Problem with the constraints test and PRIMARY KEY on UnixWare 7.
In the constraints test, a table (primary_tbl) is defined with a primary key.
The table is created, shows up in the list of table using the \dt command, but
does not exist if you use the \d command, or any SQL statement referencing the
table. Here is the output from the \dt and \d command.
regression=>\dt
Database = regression
+------------------+----------------------------------+----------+
| Owner | Relation | Type |
+------------------+----------------------------------+----------+
[...]
| postgres | path_tbl | table |
| postgres | person | table |
| postgres | point_tbl | table |
| postgres | polygon_tbl | table |
| postgres | primary_tbl | table |
| postgres | real_city | table |
| postgres | reltime_tbl | table |
| postgres | road | table |
[...]
+------------------+----------------------------------+----------+
regression=> \d primary_tbl
Couldn't find table primary_tbl!
regression=> \di
Database = regression
+------------------+----------------------------------+----------+
| Owner | Relation | Type |
+------------------+----------------------------------+----------+
| postgres | primary_tbl_pkey | index |
| postgres | unique_tbl_i_key | index |
+------------------+----------------------------------+----------+
regression=> \d primary_tbl_pkey
Table = primary_tbl_pkey
+----------------------------------+----------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------+-------+
| i | int4 | 4 |
+----------------------------------+----------------------------+-------+
regression=>
The problem seems to be restrict to the use of PRIMARY KEY, since unique_table
works fine.
And here is the relavent portions from test output.
QUERY: CREATE TABLE PRIMARY_TBL (i int PRIMARY KEY, t text);
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index
primary_tbl_pkey for table primary_tbl
QUERY: INSERT INTO PRIMARY_TBL VALUES (1, 'one');
ERROR: primary_tbl: Table does not exist.
QUERY: INSERT INTO PRIMARY_TBL VALUES (2, 'two');
ERROR: primary_tbl: Table does not exist.
[...]
QUERY: SELECT '' AS four, * FROM PRIMARY_TBL;
ERROR: primary_tbl: Table does not exist.
QUERY: DROP TABLE PRIMARY_TBL;
ERROR: Relation primary_tbl Does Not Exist!
QUERY: CREATE TABLE PRIMARY_TBL (i int, t text, PRIMARY KEY(i,t));
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index
primary_tbl_pkey for table primary_tbl
ERROR: cannot create primary_tbl
QUERY: INSERT INTO PRIMARY_TBL VALUES (1, 'one');
ERROR: primary_tbl: Table does not exist.
QUERY: INSERT INTO PRIMARY_TBL VALUES (2, 'two');
ERROR: primary_tbl: Table does not exist.
[...]
QUERY: SELECT '' AS three, * FROM PRIMARY_TBL;
ERROR: primary_tbl: Table does not exist.
QUERY: DROP TABLE PRIMARY_TBL;
ERROR: Relation primary_tbl Does Not Exist!
Any help would be appreciated.
Thanks.
--
____ | Billy G. Allie | Domain....: Bill.Allie@mug.org
| /| | 7436 Hartwell | Compuserve: 76337,2061
|-/-|----- | Dearborn, MI 48126| MSN.......: B_G_Allie@email.msn.com
|/ |LLIE | (313) 582-1540 |
Re: [HACKERS] Problem with the constraints test and PRIMARY KEY on UnixWare 7.
От
"Thomas G. Lockhart"
Дата:
> In the constraints test, a table (primary_tbl) is defined with a
> primary key. Any help would be appreciated.
Uh, yeah. This isn't a "primary key" problem, but is the same symptom we
have been seeing on Linux boxes. Bruce's FreeBSD box does not exhibit
the problem.
It seems to involve a trashed index on the pg_class table. Do you also
see a failure in the create_index regression test? afaik the problem
trying to add a second index to the "onek" table is also index-related.
Check the mhonarc archive for the full history, but we are still trying
to get a handle on it and would welcome any help.
There also is a problem with views/rules on the Linux boxes (at least,
some problems clearly span multiple platforms but no problems appear on
all platforms); do you see a failure when doing "select * from pg_user"
on a clean installation?
- Tom
Re: [HACKERS] Problem with the constraints test and PRIMARY KEY on UnixWare 7.
От
David Hartwig
Дата:
This is the same problem as that has been pestering me for two weeks. Billy, please try this simple test: CREATE TABLE foo (bar int); \d foo CREATE INDEX foo_idx ON foo USING btree (bar); \d foo Is foo gone? Billy G. Allie wrote: > In the constraints test, a table (primary_tbl) is defined with a primary key. > The table is created, shows up in the list of table using the \dt command, but > does not exist if you use the \d command, or any SQL statement referencing the > table. Here is the output from the \dt and \d command. > > regression=>\dt > > Database = regression > +------------------+----------------------------------+----------+ > | Owner | Relation | Type | > +------------------+----------------------------------+----------+ > [...] > | postgres | path_tbl | table | > | postgres | person | table | > | postgres | point_tbl | table | > | postgres | polygon_tbl | table | > | postgres | primary_tbl | table | > | postgres | real_city | table | > | postgres | reltime_tbl | table | > | postgres | road | table | > [...] > +------------------+----------------------------------+----------+ > regression=> \d primary_tbl > Couldn't find table primary_tbl! > regression=> \di > > Database = regression > +------------------+----------------------------------+----------+ > | Owner | Relation | Type | > +------------------+----------------------------------+----------+ > | postgres | primary_tbl_pkey | index | > | postgres | unique_tbl_i_key | index | > +------------------+----------------------------------+----------+ > regression=> \d primary_tbl_pkey > > Table = primary_tbl_pkey > +----------------------------------+----------------------------+-------+ > | Field | Type | Length| > +----------------------------------+----------------------------+-------+ > | i | int4 | 4 | > +----------------------------------+----------------------------+-------+ > regression=> > > The problem seems to be restrict to the use of PRIMARY KEY, since unique_table > works fine. > > And here is the relavent portions from test output. > > QUERY: CREATE TABLE PRIMARY_TBL (i int PRIMARY KEY, t text); > NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index > primary_tbl_pkey for table primary_tbl > QUERY: INSERT INTO PRIMARY_TBL VALUES (1, 'one'); > ERROR: primary_tbl: Table does not exist. > QUERY: INSERT INTO PRIMARY_TBL VALUES (2, 'two'); > ERROR: primary_tbl: Table does not exist. > [...] > QUERY: SELECT '' AS four, * FROM PRIMARY_TBL; > ERROR: primary_tbl: Table does not exist. > QUERY: DROP TABLE PRIMARY_TBL; > ERROR: Relation primary_tbl Does Not Exist! > QUERY: CREATE TABLE PRIMARY_TBL (i int, t text, PRIMARY KEY(i,t)); > NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index > primary_tbl_pkey for table primary_tbl > ERROR: cannot create primary_tbl > QUERY: INSERT INTO PRIMARY_TBL VALUES (1, 'one'); > ERROR: primary_tbl: Table does not exist. > QUERY: INSERT INTO PRIMARY_TBL VALUES (2, 'two'); > ERROR: primary_tbl: Table does not exist. > [...] > QUERY: SELECT '' AS three, * FROM PRIMARY_TBL; > ERROR: primary_tbl: Table does not exist. > QUERY: DROP TABLE PRIMARY_TBL; > ERROR: Relation primary_tbl Does Not Exist! > > Any help would be appreciated. > Thanks. > -- > ____ | Billy G. Allie | Domain....: Bill.Allie@mug.org > | /| | 7436 Hartwell | Compuserve: 76337,2061 > |-/-|----- | Dearborn, MI 48126| MSN.......: B_G_Allie@email.msn.com > |/ |LLIE | (313) 582-1540 |
Re: [HACKERS] Problem with the constraints test and PRIMARY KEY on UnixWare 7.
От
"Thomas G. Lockhart"
Дата:
> This is the same problem as that has been pestering me for two weeks.
Just a reminder: you aren't the only one :/
> Billy, please try this simple test:
> CREATE TABLE foo (bar int);
> \d foo
> CREATE INDEX foo_idx ON foo USING btree (bar);
> \d foo
> Is foo gone?
Depending on the outcome, you will fall into one of three camps wrt
indexing problems:
1) no problem, no matter what test you run (Bruce and FreeBSD)
2) problems at some level, but needing a slightly more complicated
sequence of commands to get there (Tom, Tatsuo, and Linux, etc.)
3) problems with a very simple sequence of commands (David and AIX)
Assuming that these all stem from a single underlying problem, then it
seems that David's machine would have the fast-track on finding it,
since he can provoke symptoms so easily. Unfortunately, none of those
affected with the problem are familiar with the areas of code which are
problematic.
Does anyone else see the problem at David's level? Does anyone see these
problems on anything other than Linux and Unixware? Does anyone see no
problems on anything other than FreeBSD?
There is another problem in the code wrt views and rules and freeing
memory after a query. I'm starting to look at that one, figuring that it
will be easier to track down and will reduce the number of problems we
are trying to fight at one time.
- Tom
Re: [HACKERS] Problem with the constraints test and PRIMARY KEY on UnixWare 7.
От
David Hartwig
Дата:
Thomas G. Lockhart wrote: > > This is the same problem as that has been pestering me for two weeks. > > Just a reminder: you aren't the only one :/ > > > Billy, please try this simple test: > > CREATE TABLE foo (bar int); > > \d foo > > CREATE INDEX foo_idx ON foo USING btree (bar); > > \d foo > > Is foo gone? > > Depending on the outcome, you will fall into one of three camps wrt > indexing problems: > 1) no problem, no matter what test you run (Bruce and FreeBSD) > 2) problems at some level, but needing a slightly more complicated > sequence of commands to get there (Tom, Tatsuo, and Linux, etc.) > 3) problems with a very simple sequence of commands (David and AIX) > > Assuming that these all stem from a single underlying problem, then it > seems that David's machine would have the fast-track on finding it, > since he can provoke symptoms so easily. Unfortunately, none of those > affected with the problem are familiar with the areas of code which are > problematic. > > Does anyone else see the problem at David's level? Does anyone see these > problems on anything other than Linux and Unixware? Does anyone see no > problems on anything other than FreeBSD? > > There is another problem in the code wrt views and rules and freeing > memory after a query. I'm starting to look at that one, figuring that it > will be easier to track down and will reduce the number of problems we > are trying to fight at one time. > > - Tom
David Hartwig wrote:
>
> This is the same problem as that has been pestering me for two weeks.
>
> Billy, please try this simple test:
>
> CREATE TABLE foo (bar int);
>
> \d foo
>
> CREATE INDEX foo_idx ON foo USING btree (bar);
>
> \d foo
>
> Is foo gone?
Please apply this patch...
Vadim
*** src/backend/catalog/index.c.orig Wed Sep 9 11:14:40 1998
--- src/backend/catalog/index.c Wed Sep 9 11:15:50 1998
***************
*** 1424,1433 ****
newtup = heap_modifytuple(tuple, pg_class, values, nulls, replace);
heap_replace(pg_class, &tuple->t_ctid, newtup);
- pfree(newtup);
CatalogOpenIndices(Num_pg_class_indices, Name_pg_class_indices, idescs);
CatalogIndexInsert(idescs, Num_pg_class_indices, pg_class, newtup);
CatalogCloseIndices(Num_pg_class_indices, idescs);
}
if (!IsBootstrapProcessingMode())
--- 1424,1433 ----
newtup = heap_modifytuple(tuple, pg_class, values, nulls, replace);
heap_replace(pg_class, &tuple->t_ctid, newtup);
CatalogOpenIndices(Num_pg_class_indices, Name_pg_class_indices, idescs);
CatalogIndexInsert(idescs, Num_pg_class_indices, pg_class, newtup);
CatalogCloseIndices(Num_pg_class_indices, idescs);
+ pfree(newtup);
}
if (!IsBootstrapProcessingMode())
>David Hartwig wrote: >> >> This is the same problem as that has been pestering me for two weeks. >> >> Billy, please try this simple test: >> >> CREATE TABLE foo (bar int); >> >> \d foo >> >> CREATE INDEX foo_idx ON foo USING btree (bar); >> >> \d foo >> >> Is foo gone? > >Please apply this patch... > >Vadim Thanks so much. Now index problems have gone from my LinuxPPC box! Only remaining problem seems somewhat related to view. Still the select_views test and "select * from pg_user" produces core dumps. -- Tatuso Ishii t-ishii@sra.co.jp
> David Hartwig wrote: > > > > This is the same problem as that has been pestering me for two weeks. > > > > Billy, please try this simple test: > > > > CREATE TABLE foo (bar int); > > > > \d foo > > > > CREATE INDEX foo_idx ON foo USING btree (bar); > > > > \d foo > > > > Is foo gone? > > Please apply this patch... > > Vadim > *** src/backend/catalog/index.c.orig Wed Sep 9 11:14:40 1998 > --- src/backend/catalog/index.c Wed Sep 9 11:15:50 1998 > *************** > *** 1424,1433 **** > > newtup = heap_modifytuple(tuple, pg_class, values, nulls, replace); > heap_replace(pg_class, &tuple->t_ctid, newtup); > - pfree(newtup); > CatalogOpenIndices(Num_pg_class_indices, Name_pg_class_indices, idescs); > CatalogIndexInsert(idescs, Num_pg_class_indices, pg_class, newtup); > CatalogCloseIndices(Num_pg_class_indices, idescs); > } > > if (!IsBootstrapProcessingMode()) > --- 1424,1433 ---- > > newtup = heap_modifytuple(tuple, pg_class, values, nulls, replace); > heap_replace(pg_class, &tuple->t_ctid, newtup); > CatalogOpenIndices(Num_pg_class_indices, Name_pg_class_indices, idescs); > CatalogIndexInsert(idescs, Num_pg_class_indices, pg_class, newtup); > CatalogCloseIndices(Num_pg_class_indices, idescs); > + pfree(newtup); > } > > if (!IsBootstrapProcessingMode()) Wow, this is GREAT. Vadim saves me AGAIN. I did write this code as part of the megpatch, because tuple allocations where not being properly de-allocated. This clearly is a bug, and hopefully it will be the fix I have been looking for. I was supposed to get on David Hartwig's AIX machine tomorrow, so if this fixes all our problems, we can move on to more productive items. Again, sorry to have introduced this bug into the code, and thanks again to Vadim for finding it. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
> Thanks so much. Now index problems have gone from my LinuxPPC box!
Whoo hoo! Thanks Vadim! Indices are much happier on my Linux box also :)
> Only remaining problem seems somewhat related to view. Still the
> select_views test and "select * from pg_user" produces core dumps.
Sadly, that is still true also. As I mentioned earlier, the actual
segfault happens when memory is free'd toward the end of a query. But of
course there is some problem earlier when the memory was allocated...
This is probably unrelated, but is it expected that the system views
have rule names prefixed with "_ret" whereas a view I create myself has
a name prefixed with "_RET"? Should initdb surround some of these names
in double quotes while it is doing its brute-force shadowing?
- Tom
Tatsuo Ishii wrote:
>
>
> Only remaining problem seems somewhat related to view. Still the
> select_views test and "select * from pg_user" produces core dumps.
^^^^^^^^^^^^^^^^^^^^^
After some users were added or just after initdb ?
Unfortunately, I still can't compile PG on SPARC/Solaris 2.5.1
and so have only FreeBSD for testing.
Vadim
Thomas G. Lockhart wrote: > > > Only remaining problem seems somewhat related to view. Still the > > select_views test and "select * from pg_user" produces core dumps. > > Sadly, that is still true also. As I mentioned earlier, the actual > segfault happens when memory is free'd toward the end of a query. But of > course there is some problem earlier when the memory was allocated... Could you post gdb' bt output ? Vadim
>> Only remaining problem seems somewhat related to view. Still the
>> select_views test and "select * from pg_user" produces core dumps.
> ^^^^^^^^^^^^^^^^^^^^^
>After some users were added or just after initdb ?
Just after initdb. Here is a backtrace info.
POSTGRES backend interactive interface
$Revision: 1.89 $ $Date: 1998/09/01 04:32:13 $
> select * from pg_user;
blank
1: usename (typeid = 19, len = 32, typmod = -1, byval = f)
2: usesysid (typeid = 23, len = 4, typmod = -1, byval = t)
3: usecreatedb (typeid = 16, len = 1, typmod = -1, byval = t)
4: usetrace (typeid = 16, len = 1, typmod = -1, byval = t)
5: usesuper (typeid = 16, len = 1, typmod = -1, byval = t)
6: usecatupd (typeid = 16, len = 1, typmod = -1, byval = t)
7: passwd (typeid = 25, len = -1, typmod = -1, byval = f)
8: valuntil (typeid = 702, len = 4, typmod = -1, byval = t)
----
1: usename = "t-ishii" (typeid = 19, len = 32, typmod = -1, byval = f)
2: usesysid = "1739" (typeid = 23, len = 4, typmod = -1, byval = t)
3: usecreatedb = "t" (typeid = 16, len = 1, typmod = -1, byval = t)
4: usetrace = "t" (typeid = 16, len = 1, typmod = -1, byval = t)
5: usesuper = "t" (typeid = 16, len = 1, typmod = -1, byval = t)
6: usecatupd = "t" (typeid = 16, len = 1, typmod = -1, byval = t)
7: passwd = "********" (typeid = 25, len = -1, typmod = -1, byval = f)
8: valuntil = "Sat Jan 31 15:00:00 2037 JST" (typeid = 702, len = 4, typmod = -1, byval = t)
----
Program received signal SIGSEGV, Segmentation fault.
0x2ae3b32c in chunk_free ()
(gdb) where
#0 0x2ae3b32c in chunk_free ()
#1 0x2ae3b1e8 in cfree ()
#2 0x18f2f10 in AllocSetFree (set=0x2aeca454, pointer=0x1a093c0 "")
at aset.c:222
#3 0x18f2e14 in AllocSetReset (set=0x19f8700) at aset.c:132
#4 0x18f436c in EndPortalAllocMode () at portalmem.c:920
#5 0x1836a44 in AtCommit_Memory () at xact.c:696
#6 0x1836c60 in CommitTransaction () at xact.c:911
#7 0x1836ec8 in CommitTransactionCommand () at xact.c:1137
#8 0x18bfd70 in PostgresMain (argc=2, argv=0x1920000, real_argc=2,
real_argv=0x7ffff8b4) at postgres.c:1645
#9 0x186ca08 in main (argc=0, argv=0x19f8700) at main.c:103
#10 0x1818228 in _start ()
--
Tatsuo Ishii
t-ishii@sra.co.jp
Life is good once more. :-) Thanks Vadim Vadim Mikheev wrote: > Please apply this patch... > > Vadim
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes:
> Sadly, that is still true also. As I mentioned earlier, the actual
> segfault happens when memory is free'd toward the end of a query. But of
> course there is some problem earlier when the memory was allocated...
Has anyone tried building the backend with a debugging malloc library?
dbmalloc, or Electric Fence, or Purify would probably help smoke out
any remaining coding errors of this kind (using already-freed space,
writing past the end of a malloc'd block, etc).
Purify is a commercial product, but the other two are freeware and
shouldn't be hard to find on the net.
regards, tom lane
> Has anyone tried building the backend with a debugging malloc library?
This has been done in the past by a couple of people who had access to
Purify. I recall that it helped find several problems, but also
generated quite a few spurious messages because of the way we
allocate/deallocate blocks of memory during queries.
Our current best debugging technique is for a bunch of us to get all in
a froth about a problem until Vadim gets fed up with our flailing around
and fixes it himself ;-)
Thanks again Vadim!
- Tom
> > Has anyone tried building the backend with a debugging malloc library? > > This has been done in the past by a couple of people who had access to > Purify. I recall that it helped find several problems, but also > generated quite a few spurious messages because of the way we > allocate/deallocate blocks of memory during queries. > > Our current best debugging technique is for a bunch of us to get all in > a froth about a problem until Vadim gets fed up with our flailing around > and fixes it himself ;-) Excellent description. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
> "Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes: > > Sadly, that is still true also. As I mentioned earlier, the actual > > segfault happens when memory is free'd toward the end of a query. But of > > course there is some problem earlier when the memory was allocated... > > Has anyone tried building the backend with a debugging malloc library? > > dbmalloc, or Electric Fence, or Purify would probably help smoke out > any remaining coding errors of this kind (using already-freed space, > writing past the end of a malloc'd block, etc). > > Purify is a commercial product, but the other two are freeware and > shouldn't be hard to find on the net. Good suggestion. We did have someone use Purify six months ago, and he came up with some nice stuff. Electic Fence is good for over-runs and using already free'ed memory, and was thinking of trying that here, but the idea that this is was some type of alignment problem had me moving in another direction. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
Tatuso Ishii <t-ishii@sra.co.jp> wrote: > >Vadim > > Thanks so much. Now index problems have gone from my LinuxPPC box! > > Only remaining problem seems somewhat related to view. Still the > select_views test and "select * from pg_user" produces core dumps. Here, here... Many thanks Vadim! On the "select * from pg_user" problem: UnixWare 7 does not have the problem. -- ____ | Billy G. Allie | Domain....: Bill.Allie@mug.org | /| | 7436 Hartwell | Compuserve: 76337,2061 |-/-|----- | Dearborn, MI 48126| MSN.......: B_G_Allie@email.msn.com |/ |LLIE | (313) 582-1540 |
Thomas G. Lockhart wrote: > > > Has anyone tried building the backend with a debugging malloc library? > > This has been done in the past by a couple of people who had access to > Purify. I recall that it helped find several problems, but also > generated quite a few spurious messages because of the way we > allocate/deallocate blocks of memory during queries. > > Our current best debugging technique is for a bunch of us to get all in > a froth about a problem until Vadim gets fed up with our flailing around > and fixes it himself ;-) Thanks, Tom -:)) But it seems that Purify (etc) would be very usefull in pg_user bug. (Imho, Purify could also help with indexing bug!) I finally compiled 6.4-current on SPARC/Solaris 2.5.1 (gcc 2.7.2.1) but unfortunately I can't reproduce pg_user bug here -:( So, I would like to switch to primary/foreign/unique key infos... Vadim