PostGreSQL: Act transation required for op with large object

swierzbicki · Post by **swierzbicki** » Fri 30 Dec 2011 14:21

Hello,

I'm getting this error when trying to insert data :
"Active transaction is required for operation with large objects"

How can I solve this ?
Here is my table structure :

Code: Select all

CREATE TABLE public.agenda (
  "Id_Agenda" INTEGER DEFAULT nextval('public.agenda_id_agenda_seq'::text::regclass) NOT NULL, 
  "Id_Type_Evenement" INTEGER NOT NULL, 
  "Id_Client" INTEGER NOT NULL, 
  "Id_Stade_Intervention" INTEGER, 
  "Date_Debut" TIMESTAMP WITHOUT TIME ZONE, 
  "Date_Fin" TIMESTAMP WITHOUT TIME ZONE, 
  "Date_Creation" TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT now(), 
  "Utilisateur" VARCHAR(50) DEFAULT "current_user"(), 
  "Date_Debut_Reelle" TIMESTAMP(0) WITHOUT TIME ZONE, 
  "Date_Fin_Reelle" TIMESTAMP(0) WITHOUT TIME ZONE, 
  "Legende" VARCHAR(255) DEFAULT ''::character varying, 
  "Couleur_Etiquette" INTEGER, 
  "Lieu" VARCHAR(255), 
  "Message" VARCHAR(255), 
  "Options" INTEGER, 
  "Id_Agenda_Parent" INTEGER, 
  "Id_Repetition" INTEGER, 
  "Informations_Repetition" OID, 
  "Date_Rappel" TIMESTAMP(0) WITHOUT TIME ZONE, 
  "Minutes_Rappel_Avant_Debut" INTEGER, 
  "Donnees_Ressources_Rappel" OID, 
  "Id_Ressource" INTEGER, 
  "Tache_Terminee" INTEGER, 
  "Id_Tache" INTEGER, 
  "Lien_Taches" OID, 
  "Id_Stade_Tache" INTEGER, 
  "Id_Groupe" INTEGER, 
  CONSTRAINT interventions_pkey PRIMARY KEY("Id_Agenda"), 
  CONSTRAINT interventions_fk FOREIGN KEY ("Id_Type_Evenement")
    REFERENCES public.ref_type_evenements("Id_Type_Evenement")
    ON DELETE NO ACTION
    ON UPDATE CASCADE
    NOT DEFERRABLE, 
  CONSTRAINT interventions_fk1 FOREIGN KEY ("Id_Stade_Intervention")
    REFERENCES public.ref_stade_interventions("Id_Stade_Intervention")
    ON DELETE NO ACTION
    ON UPDATE CASCADE
    NOT DEFERRABLE, 
  CONSTRAINT interventions_fk2 FOREIGN KEY ("Id_Client")
    REFERENCES public.clients("Id_Client")
    ON DELETE NO ACTION
    ON UPDATE CASCADE
    NOT DEFERRABLE
) WITHOUT OIDS;

bork · Post by **bork** » Tue 03 Jan 2012 11:49

Hello

It is the PostgreSQL feature. You can use (read and write) Large objects (the OID data type) in the transactions only. So, before reading or writing Large objects you should start transaction:

Code: Select all

  UniConnection1.StartTransaction;

You can find more detailed information about Large objects usage in the PostgreSQL documentation.

swierzbicki · Post by **swierzbicki** » Wed 04 Jan 2012 15:44

Is there any other BLOB type in PostgreSQL that doesn't require to start a transaction ?

AlexP · Post by **AlexP** » Thu 05 Jan 2012 07:53

Hello,

For work with BLOB data without starting transaction you can use the BYTEA type instead of OID

swierzbicki · Post by **swierzbicki** » Thu 05 Jan 2012 17:47

Thank you, will choose this path !