Thread: Feature request - Edit data and tables without a primary key
Hello, I have a feature request for the 'Edit data' window that is available for each table in pgAdmin. As the OID column is not guaranteed to contain unique OIDs it would be nice to have additional/other fallback options ( viacheck box in pgAdmin's general options? ) to identify rows for editing in tables without primary keys. I'd suggest serials ( serial/serial4 and bigserial/serial8 ) or unique columns/constraints. Another drawback of OID columns is that you can't add them afterwards. Thank you very much, Peter
On 12 July 2012 12:20, Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de> wrote: > Hello, > > I have a feature request for the 'Edit data' window that is available for each table in pgAdmin. > > As the OID column is not guaranteed to contain unique OIDs it would be nice to have additional/other fallback options (via check box in pgAdmin's general options? ) to identify rows for editing in tables without primary keys. > I'd suggest serials ( serial/serial4 and bigserial/serial8 ) or unique columns/constraints. > Another drawback of OID columns is that you can't add them afterwards. Use of the CTID system column would work. -- Thom
On Thu, 2012-07-12 at 12:31 +0100, Thom Brown wrote: > On 12 July 2012 12:20, Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de> wrote: > > Hello, > > > > I have a feature request for the 'Edit data' window that is available for each table in pgAdmin. > > > > As the OID column is not guaranteed to contain unique OIDs Oops, sorry? each OID is unique. > it would be nice to have additional/other fallback options ( via check box in pgAdmin's general options? ) to identifyrows for editing in tables without primary keys. > > I'd suggest serials ( serial/serial4 and bigserial/serial8 ) You can't be sure a serial (serial2, serial4, and serial8) column will only contain unique values. > or unique columns/constraints. Yes, this would be interesting to add. I thought we already did it, but I gues I was wrong. I'll add a ticket to work on this later. > > Another drawback of OID columns is that you can't add them afterwards. > You can add OID columns to an already existing table since 8.4. But it doesn't mean you want to (and actually I don't want to add OID columns to my tables). > Use of the CTID system column would work. I don't see how that could work. CTIDs obviously are unique, but anyone, updating a line will get another CTID for this line. If you try to update it afterwards, in the best case, it errors out because there is no line at this CTID. In the worst case, you update a completely different line. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
On 14 July 2012 13:57, Guillaume Lelarge <guillaume@lelarge.info> wrote: > On Thu, 2012-07-12 at 12:31 +0100, Thom Brown wrote: >> Use of the CTID system column would work. > > I don't see how that could work. CTIDs obviously are unique, but anyone, > updating a line will get another CTID for this line. If you try to > update it afterwards, in the best case, it errors out because there is > no line at this CTID. In the worst case, you update a completely > different line. Why can't you just use "RETURNING ctid" at the end of the statement to get the new one and update the grid with that value? -- Thom
On Saturday, July 14, 2012, Guillaume Lelarge wrote:
On Thu, 2012-07-12 at 12:31 +0100, Thom Brown wrote:
> On 12 July 2012 12:20, Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de> wrote:
> > Hello,
> >
> > I have a feature request for the 'Edit data' window that is available for each table in pgAdmin.
> >
> > As the OID column is not guaranteed to contain unique OIDs
Oops, sorry? each OID is unique.
No, the counter can wrap.
> it would be nice to have additional/other fallback options ( via check box in pgAdmin's general options? ) to identify rows for editing in tables without primary keys.
> > I'd suggest serials ( serial/serial4 and bigserial/serial8 )
You can't be sure a serial (serial2, serial4, and serial8) column will
only contain unique values.
> or unique columns/constraints.
Yes, this would be interesting to add. I thought we already did it, but
I gues I was wrong. I'll add a ticket to work on this later.
That method will work only if the columns involved are all not null constrained.
> > Another drawback of OID columns is that you can't add them afterwards.
>
You can add OID columns to an already existing table since 8.4. But it
doesn't mean you want to (and actually I don't want to add OID columns
to my tables).
> Use of the CTID system column would work.
I don't see how that could work. CTIDs obviously are unique, but anyone,
updating a line will get another CTID for this line. If you try to
update it afterwards, in the best case, it errors out because there is
no line at this CTID. In the worst case, you update a completely
different line.
--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
--
Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Sat, 2012-07-14 at 14:06 +0100, Dave Page wrote: > > > On Saturday, July 14, 2012, Guillaume Lelarge wrote: > On Thu, 2012-07-12 at 12:31 +0100, Thom Brown wrote: > > On 12 July 2012 12:20, Jan-Peter Seifert > <Jan-Peter.Seifert@gmx.de> wrote: > > > Hello, > > > > > > I have a feature request for the 'Edit data' window that > is available for each table in pgAdmin. > > > > > > As the OID column is not guaranteed to contain unique OIDs > > Oops, sorry? each OID is unique. > > > > No, the counter can wrap. > Oh yeah, you're right. So we shouldn't allow the use of OID in the edit data window. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
On Sat, 2012-07-14 at 14:01 +0100, Thom Brown wrote: > On 14 July 2012 13:57, Guillaume Lelarge <guillaume@lelarge.info> wrote: > > On Thu, 2012-07-12 at 12:31 +0100, Thom Brown wrote: > >> Use of the CTID system column would work. > > > > I don't see how that could work. CTIDs obviously are unique, but anyone, > > updating a line will get another CTID for this line. If you try to > > update it afterwards, in the best case, it errors out because there is > > no line at this CTID. In the worst case, you update a completely > > different line. > > Why can't you just use "RETURNING ctid" at the end of the statement to > get the new one and update the grid with that value? At the end of which statement? how do you want to make it work? -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
Hello, thank you very much for your feedback everyone! Am 14.07.2012 15:06, schrieb Dave Page: > > it would be nice to have additional/other fallback options ( via > check box in pgAdmin's general options? ) to identify rows for > editing in tables without primary keys. > > > I'd suggest serials ( serial/serial4 and bigserial/serial8 ) > > You can't be sure a serial (serial2, serial4, and serial8) column will > only contain unique values. > > > or unique columns/constraints. > > Yes, this would be interesting to add. I thought we already did it, but > I gues I was wrong. I'll add a ticket to work on this later. > > > That method will work only if the columns involved are all not null > constrained. Sorry - must have been in a hurry ... Then serials with a unique constraint or unique constraints with 'all necessary' 'NOT NULL'-constraints maybe? > You can add OID columns to an already existing table since 8.4. But it > doesn't mean you want to (and actually I don't want to add OID columns > to my tables). That's good - then at least you don't have to recreate the table in order to edit it with pgAdmin ... Otherwise we have no need for OID columns ... Best regards, Peter
Hello, another thing came to my mind. pgAdmin III 1.14 does seem to commit the changes as soon as the new value has been entered. Wouldn't it be nice to display a warning or do a rollback if more than one row had been updated instead of one or none then? Thank you very much, Peter
On Sat, 2012-07-14 at 15:43 +0200, Jan-Peter Seifert wrote: > Hello, > > thank you very much for your feedback everyone! > > Am 14.07.2012 15:06, schrieb Dave Page: > > > > it would be nice to have additional/other fallback options ( via > > check box in pgAdmin's general options? ) to identify rows for > > editing in tables without primary keys. > > > > I'd suggest serials ( serial/serial4 and bigserial/serial8 ) > > > > You can't be sure a serial (serial2, serial4, and serial8) column will > > only contain unique values. > > > > > or unique columns/constraints. > > > > Yes, this would be interesting to add. I thought we already did it, but > > I gues I was wrong. I'll add a ticket to work on this later. > > > > > > That method will work only if the columns involved are all not null > > constrained. > > Sorry - must have been in a hurry ... > > Then serials with a unique constraint or unique constraints with 'all > necessary' 'NOT NULL'-constraints maybe? > Well, if you only want to have unique columns with NOT NULL constraint, you pretty much have a primary key constraint. We could allow updating/removing lines if they do have a . Or we can BEGIN; UPDATE/DELETE; then check how many lines are updated/deleted, and COMMIT if there is only one, and ROLLBACK if there are more. I just don't see how to do that with a nice UI. We can also only allow the update/remove of lines who have unique columns not NULL. > > You can add OID columns to an already existing table since 8.4. But it > > doesn't mean you want to (and actually I don't want to add OID columns > > to my tables). > > That's good - then at least you don't have to recreate the table in > order to edit it with pgAdmin ... > Otherwise we have no need for OID columns ... > Adding OID field will recreate the table. This isn't something you'll do on huge tables. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
On Sat, 2012-07-14 at 15:54 +0200, Jan-Peter Seifert wrote: > Hello, > > another thing came to my mind. > > pgAdmin III 1.14 does seem to commit the changes as soon as the new > value has been entered. > Wouldn't it be nice to display a warning or do a rollback if more than > one row had been updated instead of one or none then? > That could be a good way to do it. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com