Archives de catégorie : PostgreSQL

Debugging PostgreSQL PL/PgSQL functions

Publié le par
Répondre

Debugging long PL/PgSQL function can be a headache because everything is rolled back when an exception is raised « somewhere » within the function and applying all the modifications « by hand » is tedious and error-prone.

One simple way to get some help is by creating a simple « dump » method like so:

create or replace function public.dump(_record record) returns void language plpgsql
as $$
begin
	raise info '%', _record;
end;
$$;

select
    id,
    title,
    dump(todo.items.*)
from todo.items;

Thanks Vedran Bilopavlović for the idea (which you can find fully developed here: https://medium.com/@vbilopav/why-i-rely-on-postgresql-functions-for-everything-pros-cons-and-best-practice-article-review-987eba321234)

You can also pass part of a table with named fields like so:

create or replace function public.dump(_record jsonb) returns void language plpgsql
as $$
begin
	raise info '%', _record;
end;
$$;


SELECT dump(jsonb_agg(to_jsonb(t)))
FROM thirdparty AS t;

And you can even make it « pretty »:

create or replace function public.dump(_record TEXT) returns void language plpgsql
as $$
begin
	raise info '%', _record;
end;
$$;


SELECT dump(jsonb_pretty(jsonb_agg(to_jsonb(t))))
FROM thirdparty AS t;

Bear in mind that these 3 « dump » functions can co-exist and Postgres will use the appropriate one automatically (depending on what parameter type you pass them).

PostgreSQL : changer le propriétaire d’une base de données

Publié le par
Répondre

Une méthode un peu bourrine mais qui marche.

  • Se connecter à la base de données voulue
  • copier/coller les lignes suivantes en changeant newuser et éventuellement public.
\t\a
\o /tmp/hack.sql
select 'ALTER '
      ||CASE WHEN relkind='r' THEN 'TABLE' WHEN relkind='S' THEN 'SEQUENCE' WHEN relkind='v' THEN 'VIEW' END ||' '||relname||' OWNER TO newuser;'
from pg_class c 
join pg_namespace ns ON ns.oid=c.relnamespace 
where relkind in ('r','S','v') 
and ns.nspname='public' 
ORDER BY CASE WHEN relkind='r' THEN 1 ELSE 2 END;
\o
\i /tmp/hack.sql
  • Le ORDER BY est nécessaire pour que les tables soient traitées avant les séquences

Pour remplacer postgres par newuser comme propriétaire de toutes les fonctions « possédées » par postgres :

\t\a
\o /tmp/hack.sql
SELECT 'ALTER FUNCTION '
       || format('%I.%I(%s)', n.nspname, p.proname, oidvectortypes(p.proargtypes))
       ||' OWNER TO newuser;'
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
AND n.nspname NOT IN ('pg_catalog','information_schema')
AND pg_catalog.pg_get_userbyid(p.proowner)='postgres';
\o
\i /tmp/hack.sql

Enjoy !

Migration de NexusDB à PostgreSQL avec pgDAC

Publié le par
Répondre

Ce projet est énorme : il vise à remplacer NexusDB par PostgreSQL dans toutes mes applications (déployées en clientèle) en utilisant les composants pgDAC (qui sont fabuleux). Je recense ici toutes les astuces, trucs à ne pas oublier, etc.

TPgConnection

Ne pas oublier de définir :

Options / UseUnicode = True

Conversion des données

Conversion des DDL (définitions des tables)

Je joue la simplicité : j’ai acquis, il y a quelques années, Database Workbench qui permet de faire Extract DDL et récupérer ainsi les définitions de toutes les tables. Reste à convertir certains types de données :

+-------------+--------------------+
| Type Nexus  | Type PostgreSQL    |
+-------------+--------------------+
| AutoInc     | Serial primary key |
| ShortString | VarChar            |
| DWord       | Int                |
+-------------+--------------------+

Conversion des données

J’utilise le composant TCRBatchMove (dans l’onglet Data Access). Je définis 2 tables, nxMigration reliée à la nxDatabase, l’autre pour pgMigration reliée à la PGConnection.

Dans CRBatchMove1, la source est la table NXmigration, la destination est PGmigration. Attention à définir FieldMappingsMode à mmFieldName au cas où l’ordre ne serait pas rigoureusement le même.

Puis le code suivant pour transférer les données :

procedure TFmain.Button1Click(Sender: TObject);
var sl:TStringList;
    i: Integer;
begin
  sl:=TStringList.Create;
  Try
    DM.dbclefs.GetTableNames(sl);
    for i := 0 to sl.Count - 1 do begin
      DM.nxMigration.TableName:=sl[i];
      DM.pgMigration.TableName:=sl[i];
      DM.CRBatchMove1.Execute;
    end;
    ShowMessage('Done !');
  Finally
    sl.free;
  End;
end;

À peaufiner un peu pour mettre à jour les séquences :

alter sequence clilog_id_seq restart with 81;

Accélérer Locate

pgQuery.IndexFieldNames:='champ1;champ2';