Thread: pgAdminII 1.1.66-Dev crash

pgAdminII 1.1.66-Dev crash

From
Paul Jongsma
Date:
Using pgAdminII 1.1.66-Dev on a W2K machine I have a 100% reproducable
crash when I do a "View data in selected table" on a specific table.

Looking at the log file in full debug mode does not show anything alerting
(tail of log):

2001-11-27 13:08:36 - Entering pgSchema:pgView.Property Get Identifier()
2001-11-27 13:08:36 - Entering pgAdmin II:frmSQLOutput.LoadGrid()
2001-11-27 13:08:36 - Loading Data...
2001-11-27 13:08:36 - Done - 0,14 Secs.
2001-11-27 13:08:36 - Entering pgAdmin II:frmSQLOutput.Form_Resize()

The table in question looks like (taken from the Definition pane):

-- Table: in the bubble
CREATE TABLE "in the bubble" (
   "pub_start" timestamp DEFAULT now(),
   "pub_stop" timestamp DEFAULT now(),
   "pub_status" text DEFAULT 'owner',
   "pub_owner_id" oid,
   "title" text,
   "intro" text,
   "body" text,
   "date" date,
   "current" bool DEFAULT 't'::bool,
   "fm_idx" int4
) INHERITS("publication");

The moment I click the "View data" icon Dr. Watson takes over
with a "pgAdmin2.exe has generated errors and will be closed".

The server I am talking to is on the same LAN, running 7.1.3
on FreeBSD 4.4-STABLE.

Logs and Data are available for further investigation if needed,
any hints on how to go on from here are welcome..

Thanks


--
Paul Jongsma
WEBtic Internet Consultancy @ http://www.webtic.nl/


Re: pgAdminII 1.1.66-Dev crash

From
Dave Page
Date:

> -----Original Message-----
> From: Paul Jongsma [mailto:paulj@webtic.com]
> Sent: 27 November 2001 12:23
> To: pgadmin-support@postgresql.org
> Subject: [pgadmin-support] pgAdminII 1.1.66-Dev crash
>
>
>
> Using pgAdminII 1.1.66-Dev on a W2K machine I have a 100%
> reproducable crash when I do a "View data in selected table"
> on a specific table.
>
> Looking at the log file in full debug mode does not show
> anything alerting (tail of log):
>
> 2001-11-27 13:08:36 - Entering pgSchema:pgView.Property Get
> Identifier() 2001-11-27 13:08:36 - Entering pgAdmin
> II:frmSQLOutput.LoadGrid() 2001-11-27 13:08:36 - Loading
> Data... 2001-11-27 13:08:36 - Done - 0,14 Secs. 2001-11-27
> 13:08:36 - Entering pgAdmin II:frmSQLOutput.Form_Resize()
>
> The table in question looks like (taken from the Definition pane):
>
> -- Table: in the bubble
> CREATE TABLE "in the bubble" (
>    "pub_start" timestamp DEFAULT now(),
>    "pub_stop" timestamp DEFAULT now(),
>    "pub_status" text DEFAULT 'owner',
>    "pub_owner_id" oid,
>    "title" text,
>    "intro" text,
>    "body" text,
>    "date" date,
>    "current" bool DEFAULT 't'::bool,
>    "fm_idx" int4
> ) INHERITS("publication");
>
> The moment I click the "View data" icon Dr. Watson takes over
> with a "pgAdmin2.exe has generated errors and will be closed".
>
> The server I am talking to is on the same LAN, running 7.1.3
> on FreeBSD 4.4-STABLE.
>
> Logs and Data are available for further investigation if
> needed, any hints on how to go on from here are welcome..

Hi Paul,

To be honest, if Dr Watson's involved then the chances are that this is a
Microsoft bug :-(

Having said that, if it's not too big, if you can send me (privately, not to
the list) a pg_dump of 'in the bubble', 'publication', their data and any
related indexes etc, I'll gladly take a look.

Regards, Dave.

Casing

From
"Colin Freas"
Date:
For anyone who follows the JDBC mailing list, there was a discussion over
the last day or so about the casing of identifiers in Postgres and SQL92.

I failed to realize that when a table is created with quotations around the
identifiers and the case is mixed, you must then refer to that idenifier
evermore with the same casing sealed in double quotes.

Initially I used pgamin's wizard to create my tables.  When I realized what
was happening with the quotes and the capitalization, I dropped the tables,
and reran the create table queries sans quotes.  The thing is, pgadmin still
seemed to create the tables using the quotes, depite my arbitrary SQL not
containing any.  To achieve the desired affect, I dropped the tables once
again, and recreated the tables with psql.

Now, if the wizard wants to use quotes in creating tables, I say 'Wave your
magic wand away!'  But, for the little green monster to 'helpfully' place
quotes around identifiers when executing my SQL makes me want to execute
him.

Also, now that I have created my tables with psql without the quotes, and my
servlet is running happily without identifiers needing to be enclosed in
quotes, as I view a table's SQL in the definition pane, I think it would be
useful to see the quotes only when appropriate, considering the behavior of
Postgres when "'s are used in table creation vs. when they are not used.

Of course it is possible I'm misinterpreting what's going on, but my
explanation seems to fit the behavior I've observed, and what I've read over
the last day or so about the casing system.

Colin Freas


Re: Casing

From
"Dave Page"
Date:
It's rumoured that Colin Freas once said:
>
> For anyone who follows the JDBC mailing list, there was a discussion
> over the last day or so about the casing of identifiers in Postgres and
> SQL92.
>
> I failed to realize that when a table is created with quotations around
> the identifiers and the case is mixed, you must then refer to that
> idenifier evermore with the same casing sealed in double quotes.

Hi Colin,

pgAdmin quotes identifiers because that should always work. Consider the
following examples of identifiers - on the left is the value entered, on
the right is what the PostgreSQL parser will convert it to for use as (for
example) a table name:

tablename    -> tablename
TableName    -> tablename
"tablename"  -> tablename
"TableName"  -> TableName
table name   -> [will produce an error]
Table Name   -> [will produce an error]
"table name" -> table name
"Table Name" -> Table Name

I think the important thing to note is that lower case names without
special characters like spaces are unaffected by the quotes. Future
queries may omit them or include them to no effect.

On the other hand, if you wish to use upper case or special characters,
you must *always* use quotes.

I'm not saying this is the correct behaviour as per the spec, but this is
how PostgreSQL works.

> Initially I used pgamin's wizard to create my tables.  When I realized
> what was happening with the quotes and the capitalization, I dropped
> the tables, and reran the create table queries sans quotes.  The thing
> is, pgadmin still seemed to create the tables using the quotes, depite
> my arbitrary SQL not containing any.  To achieve the desired affect, I
> dropped the tables once again, and recreated the tables with psql.

pgAdmin doesn't modify any arbitrary SQL you enter except to remove any
comments. It certainly won't convert your hand typed:

CREATE TABLE sheep(baa text)

into

CREATE TABLE "sheep"("baa" text)

> Now, if the wizard wants to use quotes in creating tables, I say 'Wave
> your magic wand away!'  But, for the little green monster to
> 'helpfully' place quotes around identifiers when executing my SQL makes
> me want to execute him.

And it would me. Nicely put btw :-)

> Also, now that I have created my tables with psql without the quotes,
> and my servlet is running happily without identifiers needing to be
> enclosed in quotes, as I view a table's SQL in the definition pane, I
> think it would be useful to see the quotes only when appropriate,
> considering the behavior of Postgres when "'s are used in table
> creation vs. when they are not used.
>
> Of course it is possible I'm misinterpreting what's going on, but my
> explanation seems to fit the behavior I've observed, and what I've read
> over the last day or so about the casing system.

I have to admit I'm confused by your problem. I (and my staff) use pgAdmin
on a number of projects, and *never* quote identifiers or use identifiers
that would require quoting, yet we have no problems.

I would also point out that when PostgreSQL stores identifiers in
pg_class, pg_attribute or whereever, it doesn't store any information that
would enable you to figure out if quotes were used intially or not.

If you can provide an example of the problem you're having, I happily look
into it further for you.

Regards, Dave.



Re: Casing

From
"Colin Freas"
Date:
Dave page wrote:
> I have to admit I'm confused by your problem.

Dang.  I am too.

I swear I ran a test case where I ran this as arbitrary SQL:
create table Test (Test text);

And it presented in the definition pane as:
CREATE TABLE "Test" (
  "Test" text
);

I even ran a servlet querying the table "test" against it, and it didn't
return anything...  but I must have overlooked something...  (And yes, the
table did have a couple of records in it. :)

I see it's behavior now, and obviously this isn't what normally happens.
Sorry.

Colin


Re: pgAdminII 1.1.66-Dev crash

From
Dave Page
Date:

> -----Original Message-----
> From: Paul Jongsma [mailto:paulj@webtic.com]
> Sent: 27 November 2001 14:13
> To: Dave Page
> Subject: Re: [pgadmin-support] pgAdminII 1.1.66-Dev crash
>
>
>
> Dave,
>
> >To be honest, if Dr Watson's involved then the chances are
> that this is
> >a Microsoft bug :-(
>
> Hmm that would move it to the not-so-easy-to-fix department..
>
>
> >Having said that, if it's not too big, if you can send me
> (privately,
> >not to the list) a pg_dump of 'in the bubble', 'publication', their
> >data and any related indexes etc, I'll gladly take a look.
>
> Attached is 'D.gz'; a gzipped dump of the whole database, it
> isn't big I created it with pg_dump -b -Fc DoP_test > D
>

Hi Paul,

Well, I've loaded your database & can view every table with no problems on
my system. I am running the latest ODBC driver though (07.01.0009) - can you
try it from http://odbc.postgresql.org please.

Also, do you have another client PC you can try, just to try to eliminate
anything server end - if not, you could try a dump/reload which might remove
any invalid characters (as might have been the case in the file you sent
me).

Regards, Dave.

Re: pgAdminII 1.1.66-Dev crash

From
Paul Jongsma
Date:
Dave, thanks for your time sofar;

>Well, I've loaded your database & can view every table with no problems on
>my system. I am running the latest ODBC driver though (07.01.0009) - can you
>try it from http://odbc.postgresql.org please.

My system was running the 07.01.0008 version but upgrading to
the newer version does not make any difference.

>Also, do you have another client PC you can try, just to try to eliminate
>anything server end - if not, you could try a dump/reload which might remove
>any invalid characters (as might have been the case in the file you sent
>me).

Hmm; I actually have done this already before mailing, I have 2 different
servers with 2 different workstations accessing the server. The only
thing the 2 setups have in common is that the client is a W2K and
the server runs FreeBSD 4.4-STABLE with Postgres 7.1.3

Transferred dumps from one server to another a number of times
but that does not remove the problem either.

However I just found out that the crash disappears after deleting
random records until there are 3 or 4 left in the table. Perhaps
there is a size limitation of ODBC which is reached. Will investigate
further when I have more time, any suggestions from your end
are more than welcome.

Thanks



--
Paul Jongsma
WEBtic Internet Consultancy @ http://www.webtic.nl/



Re: pgAdminII 1.1.66-Dev crash

From
Dave Page
Date:

> -----Original Message-----
> From: Paul Jongsma [mailto:paulj@webtic.com]
> Sent: 30 November 2001 11:59
> To: Dave Page
> Cc: pgadmin-support@postgresql.org
> Subject: Re: [pgadmin-support] pgAdminII 1.1.66-Dev crash
>
>
>
> Dave, thanks for your time sofar;
>
> >Well, I've loaded your database & can view every table with
> no problems
> >on my system. I am running the latest ODBC driver though
> (07.01.0009) -
> >can you try it from http://odbc.postgresql.org please.
>
> My system was running the 07.01.0008 version but upgrading to
> the newer version does not make any difference.
>
> >Also, do you have another client PC you can try, just to try to
> >eliminate anything server end - if not, you could try a dump/reload
> >which might remove any invalid characters (as might have
> been the case
> >in the file you sent me).
>
> Hmm; I actually have done this already before mailing, I have
> 2 different servers with 2 different workstations accessing
> the server. The only thing the 2 setups have in common is
> that the client is a W2K and the server runs FreeBSD
> 4.4-STABLE with Postgres 7.1.3
>
> Transferred dumps from one server to another a number of
> times but that does not remove the problem either.
>
> However I just found out that the crash disappears after
> deleting random records until there are 3 or 4 left in the
> table. Perhaps there is a size limitation of ODBC which is
> reached. Will investigate further when I have more time, any
> suggestions from your end are more than welcome.
>

Hmmm. There should be no problems with the ODBC driver in that sense - I
often query 10s of thousands of records in the pgAdmin grid. I suspect there
is one (or more) particular record in your table that it doesn't like and
when you deleted most of the records you deleted those and left good ones.
Could there be any non UK/US chars in your data, or any non-printables?

Also, I assume all is OK with other large tables in your database? Perhaps
you could write a quick script to populate a test table with a few 10s of
thousands or rows for testing purposes.

Regards, Dave.

Re: pgAdminII 1.1.66-Dev crash

From
Dave Page
Date:

> -----Original Message-----
> From: Paul Jongsma [mailto:paulj@webtic.com]
> Sent: 30 November 2001 13:25
> To: Dave Page
> Subject: RE: [pgadmin-support] pgAdminII 1.1.66-Dev crash
>
>
>
> Dave,
>
> >That doesn't overly surprise me. I suspect the MS Listview
> control that
> >pgAdmin uses is at fault - I have had trouble with it in the
> past but I
> >thought they fixed that. I'm *very* confident that it's not pgAdmin
> >itself that's falling over, because the VB runtimes *always* catch
> >coding errors and pgAdmin has extensive code to trap these and deal
> >with them nicely. However a control such as the listview which is
> >probably written in C++ probably has far less (and less
> graceful)error
> >handling.
>
> On my homemachine I have Visual Studio installed, and crashes
> of pgAdmin activitate the debugger and now the infamous "Dr Watson".
>
> Any crash thusfar was in "kernel32.dll" and indicated an
> address exception.
>
> Further tested things on my end;
>
> I made a simple "in the bubble" table containing 5 records,
> if you would look at it in CSV format it would be:
>
> idx;title;intro;body;date;current
> "1";"titel";"intro";"body";"26-05-2001";""
> "2";"titel";"intro";"body";"26-05-2001";""
> "3";"titel";"intro";"body";"26-05-2001";""
> "4";"titel";"intro";"body";"26-05-2001";""
> "5";"titel";"intro";"body";"26-05-2001";""
>
> This table causes no problems whatsoever, create 10000+
> entries and it will still work like a charm.
>
> However: extend the "body" field in record 5 to a
> string of exactly 12137 bytes or more and the
> problem will occur. Extract 1 byte and it will
> be fine.
>
> The 5 records is completely arbitrary, the same
> problem occurs with only 1 record in the table.
>
> This explains my earlier "random delete until it works",
> I randomly deleted stuff until only records where
> length(body)<12137 were left.
>
> Now how could I go and fix this? Is this Listview component
> part of VB runtime or is it part of the Windows system?

It ships with VB - it's actually part of mscomctl.ocx which is the
'Microsoft Windows Common Controls 6.0'. What version do you have there (I
have 6.1.83.41)?

/Dave.

Re: pgAdminII 1.1.66-Dev crash

From
Paul Jongsma
Date:
At 13:59 30-11-2001 +0000, Dave Page wrote:
>It ships with VB - it's actually part of mscomctl.ocx which is the
>'Microsoft Windows Common Controls 6.0'. What version do you have there (I
>have 6.1.83.41)?

6.0.88.62 , worth an upgrade I assume, where can I find one?


--
Paul Jongsma
WEBtic Internet Consultancy @ http://www.webtic.nl/