Thread: libpq-fe: how to determine unique collision ?

Hi,

I am using libpq (with postgreSQL Version: 6.5.3-23 (Debian 2.2r2)). I
have the following questions which, I am afraid, are not answered in the
documentation:

   - how do you detect the error `Can't insert tuple because duplicate' ?

   - how do you get the OID of an insertion (the goal being to get
     the value for the SERIAL type, in order to do something with it)  ?

Thank you.

Example:
   CREATE TABLE file (id SERIAL,
                      creation_time TIMESTAMP
                         NOT NULL DEFAULT CURRENT_TIMESTAMP,
                      last_access_time TIMESTAMP, /* read some data */
                      file_name VARCHAR(100) NOT NULL, /* see servers_defs.h */
                      client_name VARCHAR(30) NOT NULL, /* from auth */
                      server_name VARCHAR(30) NOT NULL, /* from auth */
                      UNIQUE(client_name, server_name, file_name),
                      UNIQUE(id), PRIMARY KEY(id));

#include "libpq-fe.h"

#define BUFFER_LENGTH 1024 /* Is boundary-checked */
BOOL db_create_file_tuple(db_context_t context,
                          const char *file_name,
                          const char *client_name,
                          const char *server_name,
                          db_error_t *error) {
   char buffer[BUFFER_LENGTH];
   BOOL result = FALSE;
   PGconn *conn = (PGconn *) context;

   *error = DB_ERROR_INTERNAL;

   if (snprintf(buffer,
                sizeof(buffer),
                "INSERT INTO file (file_name, client_name, server_name)"
                " VALUES('%s', '%s', '%s')",
                file_name,
                client_name,
                server_name)
       == -1) {
      *error = DB_ERROR_WOULD_OVERFLOW;
   }
   else {
      PGresult *res;

      /* BUGS
       *    - Should protect or bind like in Perl.
       */

      res = PQexec(conn, buffer);
      if (res && (PQresultStatus(res) == PGRES_COMMAND_OK)) {
         result = TRUE;
         *error = DB_ERROR_NONE; /* not really useful, shouldn't be read */
      }
      else {
         debug_log(DEBUG_LEVEL_ERROR,
                   "INSERT query failed: %s",
                   PQerrorMessage(conn));

         /* BUGS
          *    - We are not sure this is it, but we can only guess
          *      for now.
          */
         *error = DB_ERROR_FILE_OBJECT_EXISTS;
      }

      PQclear(res); /* avoid memory leaks */
   }

   return result;
}

Marc SCHAEFER writes:

>    - how do you detect the error `Can't insert tuple because duplicate' ?

if (strcmp(PQerrorMessage(conn), "ERROR:  Can't insert tuple because duplicate") == 0) {
    panic();
}

I know it's not ideal, but it's about the best you can do.

>
>    - how do you get the OID of an insertion

PQoidValue()

> (the goal being to get
>      the value for the SERIAL type, in order to do something with it)  ?

Serial values and oids are not related.

--
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/

On Thu, 4 Jan 2001, Peter Eisentraut wrote:

> >    - how do you get the OID of an insertion
>
> PQoidValue()

Thanks.

> > (the goal being to get
> >      the value for the SERIAL type, in order to do something with it)  ?
>
> Serial values and oids are not related.

No, that right, but do you have a better way of doing the following ? :)

INSERT INTO some_table(name, surname) VALUES('marc', 'moua');
-> creates OID 37492374
SELECT id FROM some_table WHERE oid = 37492374;

assuming

CREATE TABLE some_table(id SERIAL,
                        name VARCHAR(8),
                        surname VARCHAR(8),
                        UNIQUE(id), PRIMARY KEY(id));

What the above does is basically retrieving the id that was created.

Of course you could do:

   BEGIN TRANSACTION
   SELECT next_val('some_table_id_sequence');
   INSERT INTO some_table(id, name, surname)
      VALUES(current_val('some_table_id_sequence'), 'marc', 'moua');
   END TRANSACTION

Which one do you recommend, are they really equivalent, and do you have a
better way ? (hint: name and surname are not unique).

thank you (and, PostgreSQL is great!).

Marc SCHAEFER <schaefer@alphanet.ch> writes:
> No, that right, but do you have a better way of doing the following ? :)

> INSERT INTO some_table(name, surname) VALUES('marc', 'moua');
> -> creates OID 37492374
> SELECT id FROM some_table WHERE oid = 37492374;

This select will get pretty slow as the table gets large, unless you
make an index on its OID.  Which you could do, but why pay the price
of maintaining an index just for this?

> Of course you could do:
>    BEGIN TRANSACTION
>    SELECT next_val('some_table_id_sequence');
>    INSERT INTO some_table(id, name, surname)
>       VALUES(current_val('some_table_id_sequence'), 'marc', 'moua');
>    END TRANSACTION

This is the Right Way To Do It.  You do not need the transaction
(because currval() is backend-private anyway).  I'd not bother with
currval() at all, but just do

   SELECT nextval('some_table_id_sequence');

   -- this returns 4242, say

   INSERT INTO some_table(id, name, surname)
      VALUES(4242, 'marc', 'moua');

Simple, reliable, fast.

            regards, tom lane