Thread: Re: Can't edit tables with timestamps
> -----Original Message-----
> From: Thomas Sandford [mailto:thomas@paradisegreen.co.uk]
> Sent: 27 March 2002 20:32
> To: pgadmin-support@postgresql.org
> Subject: [pgadmin-support] Can't edit tables with timestamps
>
>
> Given a database created from the following SQL:
>
> CREATE TABLE "testtable" (
> "id" integer NOT NULL,
> "mytext" character varying(32),
> "mytime" timestamp with time zone,
> Constraint "testtable_pkey" Primary Key ("id")
> );
>
> COPY "testtable" FROM stdin;
> 1 \N 2002-03-27 20:15:52.000000+00
> 2 \N 2002-03-27 20:16:05.187532+00
> \.
>
> You will find that whilst the 1st record can be edited using
> pgadmin, any attempt to edit the 2nd results in the message
> "Could not locate the record for updating in the database!"
Yes, this is a bizarre 'feature' that's been bugging me on and off for years
now. Currently, pgAdmin builds an update query by generating a WHERE clause
using all available data. Unfortunately it periodically fails when there are
timestamps involved. I'll put it back on my to-do list.
Regards, Dave.
Le Mercredi 27 Mars 2002 22:56, Dave Page a écrit : > Yes, this is a bizarre 'feature' that's been bugging me on and off for > years now. Currently, pgAdmin builds an update query by generating a WHERE > clause using all available data. Unfortunately it periodically fails when > there are timestamps involved. I'll put it back on my to-do list. Timestamps used to be needed by M$ Access. This might be a special feature provided by ODBC to support M$ Access well. /Jean-Michel POURE
"Dave Page" <dpage@vale-housing.co.uk> wrote: > Thomas Sandford [mailto:thomas@paradisegreen.co.uk] wrote: > > [... database table using timestamp field ...] > > COPY "testtable" FROM stdin; > > 1 \N 2002-03-27 20:15:52.000000+00 > > 2 \N 2002-03-27 20:16:05.187532+00 > > \. > > > > You will find that whilst the 1st record can be edited using > > pgadmin, any attempt to edit the 2nd results in the message > > "Could not locate the record for updating in the database!" > > Yes, this is a bizarre 'feature' that's been bugging me on and off for years > now. Currently, pgAdmin builds an update query by generating a WHERE clause > using all available data. Unfortunately it periodically fails when there are > timestamps involved. I'll put it back on my to-do list. Thought (which may have occured to you and/or be impractical, but I'll mention it anyway) - if the table contains a primary key (as in this case) could you build the update where clause from that alone? Would fix at least some (and quite likely most) instances of this type of problem. -- Thomas Sandford | thomas@paradisegreen.co.uk
> -----Original Message----- > From: Thomas Sandford [mailto:thomas@paradisegreen.co.uk] > Sent: 28 March 2002 11:33 > To: pgadmin-support@postgresql.org > Subject: Re: [pgadmin-support] Can't edit tables with timestamps > > > "Dave Page" <dpage@vale-housing.co.uk> wrote: > > Thomas Sandford [mailto:thomas@paradisegreen.co.uk] wrote: > > > [... database table using timestamp field ...] > > > COPY "testtable" FROM stdin; > > > 1 \N 2002-03-27 20:15:52.000000+00 > > > 2 \N 2002-03-27 20:16:05.187532+00 > > > \. > > > > > > You will find that whilst the 1st record can be edited using > > > pgadmin, any attempt to edit the 2nd results in the > message "Could > > > not locate the record for updating in the database!" > > > > Yes, this is a bizarre 'feature' that's been bugging me on > and off for > years > > now. Currently, pgAdmin builds an update query by generating a WHERE > clause > > using all available data. Unfortunately it periodically fails when > > there > are > > timestamps involved. I'll put it back on my to-do list. > > Thought (which may have occured to you and/or be impractical, > but I'll mention it anyway) - if the table contains a primary > key (as in this case) could you build the update where clause > from that alone? Would fix at least some (and quite likely > most) instances of this type of problem. That should fix *all* problems such as this. In fact, all we need is a Unique column. The problem is that the ISUNIQUE property of each field object in the recordset is null at the moment. I'll look into it some more. Regards, Dave.
> -----Original Message----- > From: Thomas Sandford [mailto:thomas@paradisegreen.co.uk] > Sent: 28 March 2002 12:11 > To: Dave Page > Subject: Re: [pgadmin-support] Can't edit tables with timestamps > > > [ ... problems editing tables containing timestamps ... ] > > > Thought (which may have occured to you and/or be impractical, but > > > I'll mention it anyway) - if the table contains a primary > key (as in > > > this case) could you build the update where clause from > that alone? > > > Would fix at least some (and quite likely > > > most) instances of this type of problem. > > > > That should fix *all* problems such as this. In fact, all > we need is a > > Unique column. The problem is that the ISUNIQUE property of > each field > > object in the recordset is null at the moment. I'll look > into it some > more. > > The exceptions I was thinking of were: > > 1) Where the table doesn't have a primary key defined (or > with your wider strategy any unique fields) In my reply above I was making the assumption that it would, if not we'd fall back to the current method. > 2) where that primary key (/unique field) is itself a timestamp. Yes, this would be a problem. It's a fairly unlikely scenario though I'd have thought. I can't think of any application where this would be a good idea offhand. > It would appear (from viewing the individual columns of a table within > pgadmin) that pgadmin _can_ detect which column(s) is(/are) a > primary key for a table (and of course, in database terms, > the "right" way to specify a record from a table is in terms > of the primary key value for that record). Yes, we can detect it, but the mechanism used by pgAdmin for viewing the schema is totally seperate from the data recordset. What I need to do is not rely on the Recordset object, but go and query pgSchema to find suitable columns. This may slow down the first edit of a table significantly though, if that part of the object hierarchy hasn't been built, or caches haven't been populated yet. That's not normally a problem, but I'd have to figure out the best place for the queries to be made to give the best illusion of speed. Regards, Dave.
There appears to be a minor bug in the handling of open SQL windows when the main pgadmin II window is closed. Normally when an (edited) sql window is shut, a message appears to the effect "This query has ben edited, do you wish to save it [Yes][No][Cancel]". Pressing cancel leaves the SQL window open. If an (edited) SQL window is open when the pgadmin main window is closed, the same message is displayed. Pressing cancel in this case leaves the SQL window open, but the main pgadmin window still closes, leaving the SQL window as an orphan. Attempting to do anything with this window (not surprisingly) causes a VB runtime error. -- Thomas Sandford
A heads up that I received an email with a virus attachment today purporting to come from Dave Page (NB -it did not in fact come from him - this is a stealth feature of this virus/worm). It therefore seems likely that someone on the list is infected with the virus in question, Win32.Klez.H (see http://www3.ca.com/virus/virus.asp?ID=11779 for 3rd party (Computer Associates) information on this virus/worm). This is a particularly nasty beastie, making uses of Mime Type vulnerabilities in unpactched Outlook Express to open attachments without doing any more than viewing the message. I would therefore urge all list members to be particularly vigilant about opening unexpected emails from correspondents on the list, also to ensure that virus scanners are up-to-date, and IE/OE patches are up to date. -- Thomas Sandford | thomas@paradisegreen.co.uk