Base PostgreSQL Autogenerate PKEY Problems

[PAllen]

Hi all,

Base connected to a PostgreSQL backend has problems submitting forms for tables that have a backend auto generated value, like SERIAL, BIGSERIAL, or uuid_generate_v1(). This seems to be a problem that has been around since the dawn of time. I found a bug report 60643 describing this as a driver based problem.

I have a large/complicated PostgreSQL database that will use UUID extensively and I would really like to use LibreOffice Base since my users can easily create data entry forms. Since there will be some disconnected editing and multi databases merging I cannot eliminate the UUID primary keys most of the time.

Is there a work around this problem yet?
Is it possible to put a PostgreSQL function (like “uuid_generate_v1()” or “NextVal(theserialfunctionname)” ) in a form control’s default value?

regards,
Phil

[Ratslinger]

Hello,

First let me state this answer will not deal with UUID of any sort. Not set to deal with so will talk here of SMALLSERIAL, SERIAL and BIGSERIAL.

It appears the bug report you refer to is only discussing the creation of an auto increment field when creating/editing a table through Base and not its’ generated values when entering data.

Have been using SERIAL now for about 6 to 9 months on a regular basis without problems. Have just tested SMALLSERIAL and BIGSERIAL and each appear to work correctly with a caveat not in SERIAL. This is the display of the value generated as records are entered:

Bottom left is table definition. Table was actually created using pgAdmin3. SQL used was simple:

CREATE TABLE vegies(
   id SMALLSERIAL PRIMARY KEY,
   name VARCHAR NOT NULL
);

Top left is a newly entered record. Notice the id is 0. With that, opened a different form on bottom right and it shows the correct id for the ‘squash’ entry. Top right is a table view with ‘squash’ entry correct & newly entered ‘onion’ record and again id = 0. Each of the three views were opened without modification to the prior to show the problem. That is the actual id value is not known until the current view is closed & re-opened or a new view is opened.

Also, when editing a table, even though set as auto value, only SERIAL displays this in Base:

Can’t say this is a problem or not since this field need not even be present on a form for the form to work.

EDIT:

After posting this, completely shut down the Base files & pgAdmin3. Then re-opened the Base file & all problems regarding id=0 have gone away. Correct auto id is showing in all three types.

[Ratslinger]

@PAllen Was not going to get into uuid but turned it on anyway. The problem you see is not due to 'uuid_generate_v1' nor is it actually a function in this Select statement.

The public.sales2 record when entered is not displaying the actual uuid generated but rather uuid_generate_v1 until the form is closed or reloaded. Unfortunately, the sub form is trying to use this value to filter records. Have tried various macros to get around but no success.

This is a bug and should be reported on → Bugzilla.

Only recommendation at this time is two forms (or close then open as you stated). One to enter master records. The second form is for the linked records - Main form (read only) and sub form for item entries. This is the best I have come up with thus far. Have not found other method to refresh/reload form where the sub form then works correctly after entering new form records.

[PAllen]

I created a similar demo to yours and it worked well. Thanks I stand corrected on the serials.
Then I created a similar test but replaced SERIAL with uuid:

CREATE TABLE public.sales2
(
c_uuid uuid PRIMARY KEY DEFAULT uuid_generate_v1(),
company character varying(25)
);

CREATE TABLE public.sales2_items
(
c_uuid uuid PRIMARY KEY DEFAULT uuid_generate_v1(),
sales2_uuid uuid NOT NULL REFERENCES public.sales2(c_uuid),
item_name character varying(25) NOT NULL
);

In base when I look at the table design it identifies the uuid columns as type MEMO.

I created a sales2 record and all worked well in the table view. Then created a 1:M form and could add child records.
Now things got strange. The first time I created a new parent record in the form it wouldn’t allow me to create a child. The second time I tried to create a parent record it gave me this error when I tabbed into the child control.

Error code: 1

pq_driver: [PGRES_FATAL_ERROR]ERROR: invalid input syntax for type uuid: “uuid_generate_v1()”
LINE 1: …" WHERE ( “public”.“sales2_items”.“sales2_uuid” = 'uuid_gene…
^
(caused by statement ‘SELECT * FROM “public”.“sales2_items” WHERE ( “public”.“sales2_items”.“sales2_uuid” = ‘uuid_generate_v1()’ )’)

It appears that Base is sending the uuid command with single quotes (‘uuid_generate_v1’) when it should not be quoted since it is a function.

Then if I close the form and re-open it, now the form will allow me to add child records to the previously added parent record.

Is there a way to manually modify the update sql command?
Or is there a way to get the parent form the save the record properly when I then tab to the child record once the parent form is modified?

Thanks,
Phil

[Ratslinger]

@PAllen Please edit original question to add additional information or add a comment. Answers are to be used for answering original question only.