Re: Semi-unable to add new records to table--primary key needed?
Thank you for the confirmation on the need for a primary key. I suspected that, since the GUI needs an easy way to refer to a particular row. I think I saw such a restriction in the Qt documentation on a different project (just to be clear: no Qt involved in this one--just more evidence this is a pretty general pattern). It's interesting that Access does not behave this way if the backend is an Access (.mdb) file; I suppose it knows enough to get some unique identifier in that case. The difference is not that the backend table in Access has a primary key; the reason the PG table lacked a primary key was that the Access table from which it was migrated didn't have one. I've read more about sequences, and it seems mine just aren't in sync with the data, and that using sequences requires some care. Easier to discuss with an example. CREATE TABLE tx ( xid serial, a int8 ); This will produce a sequence tx_xid_seq. If I do INSERT INTO tx VALUES (3, 4); the sequence doesn't know about it. So if I later create a default value with INSERT INTO tx (a) VALUES (7); or INSERT INTO tx VALUES (DEFAULT, 9); I'll just get the next value in the sequence. The 3rd time I use the default value it will return 3, same as the record already there. If there is a PRIMARY KEY (or UNIQUE) constraint on xid the insertion will fail. So 1. When I migrate data, as I have done, I should ensure that the sequences are at safe values. The obvious way to do that would be setval using the max of the values in the data. 2. In operations, the program needs to either be consistent about getting id values from the default, or be very careful. Since I'm using an inherited application, I need to check. When I migrate the data I do NOT want to use the sequence to generate the identifiers, since that will trash the referential integrity of the data Here's the transcript of my tests. BTW, why is log_cnt jumping to 32 on the sequence? testNTB=> CREATE TABLE tx ( testNTB(> xid serial, testNTB(> a int8 testNTB(> ); CREATE TABLE testNTB=> select * from tx_xid_seq; last_value | log_cnt | is_called +-+--- 1 | 0 | f (1 row) testNTB=> INSERT INTO tx VALUES (3, 4); INSERT 0 1 testNTB=> select * from tx_xid_seq; last_value | log_cnt | is_called +-+--- 1 | 0 | f (1 row) testNTB=> select * from tx; xid | a -+--- 3 | 4 (1 row) testNTB=> INSERT INTO tx (a) VALUES (7); INSERT 0 1 testNTB=> select * from tx; xid | a -+--- 3 | 4 1 | 7 (2 rows) testNTB=> select * from tx_xid_seq; last_value | log_cnt | is_called +-+--- 1 | 32 | t (1 row) testNTB=> INSERT INTO tx VALUES (DEFAULT, 9); INSERT 0 1 testNTB=> select * from tx_xid_seq; last_value | log_cnt | is_called +-+--- 2 | 31 | t (1 row) testNTB=> select * from tx; xid | a -+--- 3 | 4 1 | 7 2 | 9 (3 rows) testNTB=> INSERT INTO tx (a) VALUES (77); INSERT 0 1 testNTB=> select * from tx_xid_seq; last_value | log_cnt | is_called +-+--- 3 | 30 | t (1 row) testNTB=> select * from tx; xid | a -+ 3 | 4 1 | 7 2 | 9 3 | 77 (4 rows) --Now with a PRIMARY KEY constraint testNTB=> CREATE TABLE ty ( testNTB(> yid serial, testNTB(> a int8, testNTB(> PRIMARY KEY (yid)); CREATE TABLE testNTB=> INSERT INTO ty VALUES (2, 10); INSERT 0 1 testNTB=> select * from ty; yid | a -+ 2 | 10 (1 row) testNTB=> select * from ty_yid_seq; last_value | log_cnt | is_called +-+--- 1 | 0 | f (1 row) testNTB=> INSERT INTO ty VALUES (DEFAULT, 20); INSERT 0 1 testNTB=> select * from ty; yid | a -+ 2 | 10 1 | 20 (2 rows) testNTB=> select * from ty_yid_seq; last_value | log_cnt | is_called +-+--- 1 | 32 | t (1 row) testNTB=> INSERT INTO ty VALUES (DEFAULT, 30); ERROR: duplicate key value violates unique constraint "ty_pkey" DETAIL: Key (yid)=(2) already exists.
Re: Semi-unable to add new records to table--primary key needed?
On 12/21/19 12:21 PM, Boylan, Ross wrote: Thank you for the confirmation on the need for a primary key. I suspected that, since the GUI needs an easy way to refer to a particular row. I think I saw such a restriction in the Qt documentation on a different project (just to be clear: no Qt involved in this one--just more evidence this is a pretty general pattern). It's interesting that Access does not behave this way if the backend is an Access (.mdb) file; I suppose it knows enough to get some unique identifier in that case. The https://support.office.com/en-us/article/Add-or-change-a-table-s-primary-key-in-Access-07b4a84b-0063-4d56-8b00-65f2975e4379 "When you create a new table in Datasheet view, Access automatically creates a primary key for you and assigns it a field name of "ID" and the AutoNumber data type." difference is not that the backend table in Access has a primary key; the reason the PG table lacked a primary key was that the Access table from which it was migrated didn't have one. My guess is the migration process missed that aliquotid was the PK. I've read more about sequences, and it seems mine just aren't in sync with the data, and that using sequences requires some care. Easier to discuss with an example. CREATE TABLE tx ( xid serial, a int8 ); This will produce a sequence tx_xid_seq. If I do INSERT INTO tx VALUES (3, 4); the sequence doesn't know about it. So if I later create a default value with INSERT INTO tx (a) VALUES (7); or INSERT INTO tx VALUES (DEFAULT, 9); I'll just get the next value in the sequence. The 3rd time I use the default value it will return 3, same as the record already there. If there is a PRIMARY KEY (or UNIQUE) constraint on xid the insertion will fail. So 1. When I migrate data, as I have done, I should ensure that the sequences are at safe values. The obvious way to do that would be setval using the max of the values in the data. 2. In operations, the program needs to either be consistent about getting id values from the default, or be very careful. Since I'm using an inherited application, I need to check. When I migrate the data I do NOT want to use the sequence to generate the identifiers, since that will trash the referential integrity of the data That rather depends on what you using to migrate the data. Postgres pg_dump will not do that if you dump the table. Here's the transcript of my tests. BTW, why is log_cnt jumping to 32 on the sequence? log_cnt is an internal counter: https://www.postgresql.org/message-id/7487.1049476267%40sss.pgh.pa.us and does not really apply to the sequence value. The value is determined by last_value and is_called. For more information see: https://www.postgresql.org/docs/11/sql-createsequence.html You skipped over the sequence by starting with a value of 2 and then you went back and used the sequence, so when it got to 2 it threw a duplicate error which is correct as the field was a PK. Best practice on a sequence is to let it run on its own and not try to override it. If you do then you will need to familiarize yourself with the functions here: https://www.postgresql.org/docs/11/functions-sequence.html --Now with a PRIMARY KEY constraint testNTB=> CREATE TABLE ty ( testNTB(> yid serial, testNTB(> a int8, testNTB(> PRIMARY KEY (yid)); CREATE TABLE testNTB=> INSERT INTO ty VALUES (2, 10); INSERT 0 1 testNTB=> select * from ty; yid | a -+ 2 | 10 (1 row) testNTB=> select * from ty_yid_seq; last_value | log_cnt | is_called +-+--- 1 | 0 | f (1 row) testNTB=> INSERT INTO ty VALUES (DEFAULT, 20); INSERT 0 1 testNTB=> select * from ty; yid | a -+ 2 | 10 1 | 20 (2 rows) testNTB=> select * from ty_yid_seq; last_value | log_cnt | is_called +-+--- 1 | 32 | t (1 row) testNTB=> INSERT INTO ty VALUES (DEFAULT, 30); ERROR: duplicate key value violates unique constraint "ty_pkey" DETAIL: Key (yid)=(2) already exists. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Semi-unable to add new records to table--primary key needed?
My mail interface (Outlook on the Web) really can't quote properly, so I'll just do snips. >"When you create a new table in Datasheet view, Access automatically creates a primary key for you and assigns it a field name of "ID" and the AutoNumber data type." That quote, and the documentation mentioned before it, is not directly relevant, since I'm not creating new tables in Access > My guess is the migration process missed that aliquotid was the PK. Substantively aliquotid is the primary key, but in terms of formal table properties there is no primary key. The export process does create primary keys in PG for tables that have them in Access. My export process also has no foreign key relations. It is quite likely that subjecting the current data to "should be there" constraints on primary and foreign keys will reveal features of the data that shouldn't be there, such as missing or lost references or values. Also, if I impose those constraints when I create the table definitions, the import will become much more order sensitive, I think. If I import a table with a foreign key in a table not yet imported, it will presumably fail. And since tables can refer to each other, there may be no import order that will work. So I guess I better add foreign key constraints after the main import. Of course, that will require valid data, but that's a separate problem. I migrated by using a slightly modified version of ' exportSQL version 3.2-dev ' www.rot13.org/~dpavlin/projects.html#sql ' ' based on exportSQL version 2.0 from www.cynergi.net/prod/exportsql/ ' ' (C) 1997-98 CYNERGI - www.cynergi.net, i...@cynergi.net ' (C) Pedro Freire - pedro.fre...@cynergi.net (do not add to mailing lists without permission) ' (c) 2000-2001 Dobrica Pavlinusic - added PostgreSQL support It needed some tweaks to work with current PG. It does preserve primary key values. Thanks for the references on log_cnt and sequences. I can see that just using the defaults is the easiest path, but I clearly can't do that on import. Cleaning the sequence up after import seems straightforward, though the export code isn't doing it. Whether the main application relies strictly on defaults I don't know. Ross
Re: Semi-unable to add new records to table--primary key needed?
On 12/21/19 1:41 PM, Boylan, Ross wrote: My mail interface (Outlook on the Web) really can't quote properly, so I'll just do snips. "When you create a new table in Datasheet view, Access automatically creates a primary key for you and assigns it a field name of "ID" and the AutoNumber data type." That quote, and the documentation mentioned before it, is not directly relevant, since I'm not creating new tables in Access My guess is the migration process missed that aliquotid was the PK. Substantively aliquotid is the primary key, but in terms of formal table properties there is no primary key. The export process does create primary keys in PG for tables that have them in Access. My export process also has no foreign key relations. It is quite likely that subjecting the current data to "should be there" constraints on primary and foreign keys will reveal features of the data that shouldn't be there, such as missing or lost references or values. Also, if I impose those constraints when I create the table definitions, the import will become much more order sensitive, I think. If I import a table with a foreign key in a table not yet imported, it will presumably fail. And since tables can refer to each other, there may be no import order that will work. So I guess I better add foreign key constraints after the main import. Of course, that will require valid data, but that's a separate problem. I migrated by using a slightly modified version of ' exportSQL version 3.2-dev ' www.rot13.org/~dpavlin/projects.html#sql ' ' based on exportSQL version 2.0 from www.cynergi.net/prod/exportsql/ ' ' (C) 1997-98 CYNERGI - www.cynergi.net, i...@cynergi.net ' (C) Pedro Freire - pedro.fre...@cynergi.net (do not add to mailing lists without permission) ' (c) 2000-2001 Dobrica Pavlinusic - added PostgreSQL support It needed some tweaks to work with current PG. It does preserve primary key values. Thanks for the references on log_cnt and sequences. I can see that just using the defaults is the easiest path, but I clearly can't do that on import. Cleaning the sequence up after import seems straightforward, though the export code isn't doing it. Whether the main application relies strictly on defaults I don't know. This might be easier to figure out if you outline what is going on: 1) The purpose of the migration? 2) A general sense of what the application is and what it does. 3) Have you looked at the Relations tab in Access to see what if any relationships are there? Ross -- Adrian Klaver adrian.kla...@aklaver.com
Re: BigSQL pgc alternative
On Fri, Dec 20, 2019 at 12:15:06PM -0300, Samuel Teixeira Santos wrote: Hi all. BigSQL still allow to install Postgres and others resources as like a portable install. But today, it's only offer your tool (pgc) for newer Postgresql versions. I would like to install as portable option because it's more easy to config and use in my own user in my development station. What you recommend to do to replace pgc tool as alternative to install postgresql 10 and the respective postgis version as like portable option? I'm not familiar with BigSQL, so I'm not sure what exactly you mean when you say "portable option". Can you explain? regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Semi-unable to add new records to table--primary key needed?
>From: Adrian Klaver >Sent: Saturday, December 21, 2019 3:37 PM > This might be easier to figure out if you outline what is going on: Since I seem to have gone on in my responses, let me do one-line answers before the fuller ones. > 1) The purpose of the migration? Primarily to use currently supported software. Secondarily to improve data integrity, security and auditability. > 2) A general sense of what the application is and what it does. A GUI for managing medical specimens and associated information for multiple research studies. >3) Have you looked at the Relations tab in Access to see what if any >relationships are there? Yes and yes. The migration program doesn't currently use that information, and there are some complexities. Now the more elaborate answers: > 1) The purpose of the migration? The immediate purpose of the migration is to use software that is supported. The application currently runs on Windows 7, Office 2010 32 bit. As of Jan 14 we have to be off Win 7 because the University says so (because MS said so) and will be disabling such systems. Office 2010, even now, can't be installed because of licensing. We have to use Win 10, Office 2016 (even though 2019 is available). We can do either 32 or 64 bit office and decided to target 64 bit. We currently use Access's split database configuration, meaning the "backend" is a file on a shared drive. Since we had to go to the pain of migrating anyway, this seemed a good time to switch to a server-based backend. Although the Access split configuration has worked, having multiple users touching the same file always makes me uncomfortable, and a real database server would seem to offer better assurances of data integrity, security, and auditability. Since the databases store sensitive medical information, these are concerns not only for us but for our funders and other oversight bodies. Historically, the requirements have gotten increasingly stringent, and it seems to me there is some possibility that the Access "backend" will fall short of the requirements in the future. Another consideration is that MS is increasingly deemphasizing using Access as a data store. Of course, they want people to go to MS SQL Server. When I visited MS's web page for Access 2016 I couldn't find any statement that it could be used without a server-based backed, even though it can. But depending on a feature that's getting so studiously ignored seems risky. Finally, I had some really bad experiences--that is, lost a day--trying to get queries to work that wouldn't, because MS Access SQL just isn't quite SQL. I was hoping to avoid that in the future. Because of the time pressure, we'll be sticking with the file-based backend for now. The front-end application (described next) is built on Access and is fairly substantial; migrating it to another platform seems not worth it. > 2) A general sense of what the application is and what it does. The application is a GUI for relatively non-computer-technical users. They run medical research studies, and each time someone comes in various tests are performed and recorded, and specimens collected. Other health-related information is also collected. The core function is the management of biological specimens that result. We also serve as a repository for specimens collected at other sites. There are various types of specimens and various procedures that can be performed on each. Researchers then query the database by outlining what kind of specimens they want and getting a list of specimens. Usually they do it by asking me, and I do the queries. The actual amount of data is not trivial, but is not that large by current standards. The file-based backends are around 20MB (after a compact and repair), and the largest tables have around 100K records. I don't think there's anything there that requires us to use 64 bits. The data are very valuable, in that they represent over a decade's work, lots of $ of effort, and without them the physical specimens would be essentially useless. The number of users, esp simultaneous users, is also relatively small, around 10. >3) Have you looked at the Relations tab in Access to see what if any >relationships are there? Yes, but the export program doesn't :) The relations tab documents many, but not all, of the relations in the database. The relations are also a little tricky because sometimes the lack of a relation should not be considered disqualifying for a specimen. Simple example: freezer type is an id to be looked up in a small table of freezer type ids and their names. If the freezer type is missing or nonsense, we may still want the sample. That can be expressed as a left join; the "Access SQL is not SQL" problems centered on left joins. Ross
